高性能MySQL【筆記】超詳細（一）

高性能MySQL【筆記】超詳細（一）

來自專欄 ZyBlog

一、MySQL架構與歷史

A.並發控制

1.共享鎖（shared lock，讀鎖）：共享的，相互不阻塞的

2.排他鎖（exclusive lock，寫鎖）：排他的，一個寫鎖會阻塞其他的寫鎖和讀鎖

B.事務

1.事務ACID

* 原子性（atomicity）一個事務必須被視為一個不可分割的最小工作單元，整個事務中所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作

* 一致性（consistency）資料庫總是從一個一致性的狀態轉換到另外一個一致性的狀態

* 隔離性（isolation）一個事務所做的修改在最終提交以前，對其他事務是不可見的

* 持久性（durability）一旦事務提交，則其所做的修改就會永久保存到資料庫中

2.四種隔離級別

* READ UNCOMMITTED（未提交讀），事務中的修改，即使沒有提交，對其他事務也都是可見的，事務可以讀取未提交的數據，也被稱為臟讀（Dirty Read），這個級別會導致很多問題

* READ COMMITTED（提交讀），大多數資料庫系統的默認隔離級別，一個事務開始時，只能「看見」已經提交的事務所做的修改，一個事務從開始直到提交之前，所做的任何修改對其他事務都是不可見的，也叫不可重複讀（nonrepeatable read），有可能出現幻讀（Phantom Read），指的是當某個事務在讀取某個範圍內的記錄時，另外一個事務又在該範圍內插入了新的記錄，當之前的事務再次讀取該範圍的記錄時，會產生幻行（Phantom Row）

* REPEATABLE READ（可重複讀），通過InnoDB和XtraDB存儲引擎，是MySQL的默認事務隔離級別

* SERIALIZABLE（可串列化）最高級別，通過強制事務串列執行，避免了幻讀問題，會在讀取的每一行數據上都加鎖，可能導致大量的超時和鎖爭用的問題

3.死鎖：指兩個或多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性循環的現象

4.事務日誌：存儲引擎在修改表的數據時只需要修改其內存拷貝，再把該修改行為記錄到持久在硬碟上的事務日誌中，而不用每次都將修改的數據本身持久到磁碟。事務日誌持久以後，內存中被修改的數據在後台可以慢慢地刷回到磁碟，稱為預寫式日誌（Write-Ahead Logging）

C.多版本並發控制

1.多版本並發控制（MVCC）是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然實現機制有所不同，但大都實現了非阻塞的讀操作，寫操作也只鎖定必要的行

2.MVCC的實現，是通過保存數據在某個時間點的快照來實現的，有樂觀和悲觀兩種，只在REPEATABLE READ和READ COMMITTED兩個隔離級別下工作

D.MySQL的存儲引擎

1.MySQL的.frm文件保存表的定義，SHOW TABLE STATUS顯示錶的相關信息

2.除非有非常特別的原因需要使用其他的存儲引擎，否則應該優先考慮InnoDB引擎

3.不要輕易相信MyISAM比InnoDB快之類的經驗之談，這個結論並不是絕對的

二、MySQL基準測試

A.為什麼需要基準測試

1.基準測試可以觀察系統在不同壓力下的行為，評估系統的容量，掌握哪些是重要的變化，或者觀察系統如何處理不同的數據

B.基準測試的策略

1.兩種主要的策略：

* 針對整個系統的整體測試（集成式full-stack）

* 單獨測試MySQL（單組件式single-component）

2.測試何種指標：

* 吞吐量，指單位時間內的事務處理數，常用的測試單位是每秒事務數（TPS），或每分鐘事務數（TPM）

* 響應時間或者延遲，用於測試任務所需的整體時間，根據具體的應用，測試的時間單位可能是微秒、毫秒、秒或者分鐘。通常使用百分比響應時間（percentile response time）來替代最大響應時間

* 並發性，需要關注的是正在工作中的並發操作，或者是同時工作中的線程數或者連接數，在測試期間記錄MySQL資料庫的Threads_running狀態值

* 可擴展性，給系統增加一倍的工作，在理想情況下就能獲得兩倍的效果（即吞吐量增加一倍），對於容量規範非常有用，可以提供其他測試無法提供的信息，來幫助發現應用的瓶頸

C.基準測試方法

1.需要避免的一些常見錯誤：

* 使用真實數據的子集而不是全集

* 使用錯誤的數據分布

* 使用不真實的分布參數

* 在多用戶場景中，只做單用戶測試

* 在單伺服器上測試分散式應用

* 與真實用戶行為不匹配

* 反覆執行同一個查詢

* 沒有檢查錯誤

* 忽略了系統預熱（warm up）的過程

* 使用默認的伺服器配置

* 測試時間太短

2.應該建立將參數和結果文檔化的規範，每一輪測試都必須進行詳細記錄

3.基準測試應該運行足夠長的時間，需要在穩定狀態下測試並觀察

4.在執行基準測試時，需要儘可能多地收集被測試系統的信息

5.自動化基準測試可以防止測試人員偶爾遺漏某些步驟，或者誤操作，另外也有助於歸檔整個測試過程，可以選擇shell、php、perl等，要儘可能使所有測試過程都自動化，包括裝載數據、系統預熱、執行測試、記錄結果等

D.基準測試工具

1.集成式測試工具：

* ab，測試HTTP伺服器每秒最多可以處理多少請求

* http_load，和ab類似，但更加靈活

* jMeter，可以載入其他應用並測試其性能

2.單組件式測試工具

* mysqlslap，可以模擬伺服器的負載，並輸出計時信息

* MySQL Benchmark Suite(sql-bench)，單線程的，主要用於測試伺服器執行查詢的速度

* Super Smack，提供壓力測試和負載生成，是一個複雜而強大的工具，可以模擬多用戶訪問，可以載入測試數據到資料庫，並支持使用隨機數據填充測試表

* Database Test Suite，類似某些工業標準測試的測試工具集

* Perconas TPCC-MySQWL Tool

* sysbench，多線程系統壓測工具，可以根據影響資料庫伺服器性能的各種因素來評估系統的性能

三、伺服器性能剖析

A.性能優化簡介

1.性能，為完成某件任務所需要的時間度量，性能即響應時間，這是非常重要的原則

2.如果目標是降低響應時間，就需要理解為什麼伺服器執行查詢需要這麼多時間，然後去減少或者消除那些對獲得查詢結果來說不必要的工作。無法測量就無法有效地優化

3.性能剖析（profiling）是測量和分析時間花費在哪裡的主要方法，一般有兩個步驟：測量任務所花費的時間，對結果進行統計和排序

B.對應用程序進行性能剖析

1.性能瓶頸可能的影響因素：

* 外部資源

* 應用需要處理大量的數據

* 在循環中執行昂貴的操作

* 使用了低效的演算法

2.PHP性能剖析工具：New Relic、xhprof、Ifp

C.剖析MySQL查詢

1.剖析伺服器負載

* 慢查詢日誌：5.1後long_query_time為0可以捕獲所有的查詢，查詢的響應時間單位可以做到微秒級

* 生成剖析報告：pt-query-digest

2.剖析單條查詢：

* SHOW PROFILES;

* SHOW [GLOBAL] STATUS;，返回一些計數器

D.診斷間歇性問題

1.盡量不要用試錯的方式來解決問題，如果一時無法定位，可能是測量的方式不正確，或者測量的點選擇有誤，或者使用的工具不合適

2.確定單條查詢問題還是伺服器問題

* 使用SHOW GLOBAL STATUS

* 使用SHOW PROCESSLIST

* 使用查詢日誌

* 理解發現的問題：使得gnuplot或R，或其他繪圖工具將結果繪製成圖形

3.捕獲診斷數據

* 診斷觸發器：在問題出現時能夠捕獲數據的基礎，有兩個常見問題可能導致無法達到預期的結果：誤報（false positive）或者漏檢（false negative）,pt-stalk工具

* 收集數據：儘可能收集所有能收集的數據，但只在需要的時間段內收集，oprofile、strace、tcpdump、GDB堆棧跟蹤、pt-collect、pt-stalk

* 解釋結果數據：pt-mysql-summary、pt-summary輸出結果打包，pt-sift得到樣本匯總信息，pt-pmp

E.其他剖析工具

1.使用USER_STATISTICS表

2.使用strace，可以調查系統調用的情況

四、Schema與數據類型優化

A.選擇優化的數據類型

1.數據類型的選擇原則：

* 更小的通常更好

* 簡單就好

* 盡量避免NULL

2.應該盡量只在對小數進行精確計算時才使用DECIMAL，使用int類型通過程序控制單位效果更好

3.使用VARCHAR合適的情況：字元串列的最大長度比平均長度大很多；列的更新很少，所以碎片不是問題；使用了像UTF-8這樣複雜的字符集，每個字元都使用不同的位元組數進行存儲

4.CHAR適合存儲很短的字元串，或者所有值都接近同一個長度；不容易產生碎片，在存儲空間上更有效率

5.通常應該盡量使用TIMESTAMP，它比DATETIME空間效率更高

B.MySQL schema設計中的陷阱

1.不好的設計：

* 太多的列

* 太多的關聯

* 全能的枚舉

* 變相的枚舉

* 非此發明（Not Invent Here）的NULL

C.範式和反範式

1.範式的優點：

* 範式化的更新操作通常比反範式化要快

* 當數據較好地範式化時，就只有很少或者沒有重複數據，所以只需要修改更少的數據

* 範式化的表通常更小，可以更好地放在內存里，所以執行操作會更快

* 很少有多餘的數據意味著檢索列表數據時更少需要DISTINCT或者GROUP BY語句

2.範式化設計的缺點是通常需要關聯

3.反範式的優點：避免關聯，避免了隨機I/O，能使用更有效的索引策略

D.緩存表和匯總表

1.有時提升性能最好的方法是同一張表中保存衍生的冗餘數據，有時也需要創建一張完全獨立的匯總表或緩存表

2.物化視圖，MySQL並不原生支持，Flexviews

3.如果應用在表中保存計數器，則在更新計數器時可能踫到並發問題，創建一張獨立的表存儲計數器，可以幫助避免緩存失效

* 解決獨立表並發問題可以建多行，根據id隨機更新，然後統計時sum()

* 按天或小時可以單獨建行，舊時間可定時任務合併到統一的一行

E.加快ALTER TABLE操作的速度

1.兩種方式：

* 一是在一台不提供服務的機器上執行ALTER TABLE操作，然後和提供服務的主庫進行切換

* 二是通過「影子拷貝」，創建一張新表，然後通過重命名和刪表操作交換兩張表及裡面的數據

2.快速創建MyISAM索引，先禁用索引，導入數據，然後重新啟用索引

五、創建高性能的索引

A.索引基礎

1.索引可以包含一個或多個列的值，如果索引包含多個列，那麼列的順序也十分重要，因為MySQL只能高效地使用索引的最左前綴列

2.ORM工具能夠產生符合邏輯的、合法的查詢，除非只是生成非常基本的查詢，否則它很難生成適合索引的查詢

3.在MySQL中，索引是在存儲引擎層而不是伺服器層實現的，所以，並沒有統一的索引標準：不同存儲引擎的索引的工作方式並不一樣，也不是所有的存儲引擎都支持所有類型的索引

4.B-Tree意味著所有的值都是按順序存儲的，並且每一個葉子頁到根的距離相同，能夠加快訪問數據的速度，從索引的根節點開始進行搜索，適用於全鍵值、鍵值範圍或鍵前綴查找

5.B-Tree索引的限制：

* 如果不是按照索引的最左列開始查找，則無法使用索引

* 不能跳過索引中的列

* 如果查詢中有某個列的範圍查詢，則其右邊所有列都無法使用索引優化查找

6.哈希索引（hash index）基於哈希表實現，只有精確匹配索引所有列的查詢才有效，只有Memory引擎顯式支持哈希索引

7.哈希索引的限制：

* 哈希索引只包含哈希值和行指針，而不存儲欄位值，所以不能使用索引中的值來避免讀取行

* 哈希索引數據並不是按照索引值順序存儲的，所以也就無法用於排序

* 哈希索引也不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的全部內容來計算哈希值的

* 只支持等值比較查詢，不支持任何範圍查詢

* 訪問哈希索引的數據非常快，除非有很多哈希衝突

* 如果哈希衝突很多的話，一些索引維護操作的代價也會很高

8.空間數據索引（R-Tree），MyISAM表支持空間索引，可以用作地理數據存儲，開源資料庫系統中對GIS的解決方案做得比較好的是PostgreSQL的PostGIS

9.全文索引，適用於MATCH AGAINST操作，而不是普通的WHERE條件操作

B.索引的優點

1.三個優點：

* 索引大大減少了伺服器需要掃描的數據量

* 索引可以幫助伺服器避免排序和臨時表

* 索引可以將隨機I/O變為順序I/O

2.索引三星系統：

* 索引將相關的記錄放到一起則獲得一星

* 如果索引中的數據順序和查找中的排序一致則獲得二星

* 如果索引中的列包含了查詢中需要的全部列則獲得三星

C.高性能的索引策略

1.獨立的列：如果查詢中的列不是獨立的，則MySQL不會使用索引。「獨立的列」是指索引列不能是表達式的一部分，也不能是函數的參數

2.前綴索引和索引選擇性

* 通常可以索引開始的部分字元，可以大大節約索引空間，但也會降低索引的選擇性

* 索引的選擇性是指，不重複的索引值（也稱為基數，cardinality）和數據表的記錄總數（#T）的比值，範圍從1/#T到1之間，選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的行

* MySQL無法使用前綴索引做ORDERY BY和GROUP BY，也無法做覆蓋掃描

3.選擇合適的索引列順序

* 正確的索引列順序依賴於使用該索引的查詢，並且同時需要考慮如何更好地滿足排序和分組的需要

* 在一個多列B-Tree索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列

* 將選擇性最高的列放到索引最前列

4.聚簇索引：並不是一種單獨的索引類型，而是一種數據存儲方式

* 最好避免隨機的（不連續且值的分布範圍非常大）聚簇索引，特別是對於I/O密集型的應用

5.覆蓋索引：如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，就稱為覆蓋索引

* 覆蓋索引必須要存儲索引列的值，

6.如果EXPLAIN出來的type列的值為「index」，則說明MySQL使用了索引掃描來做排序

7.壓縮（前綴）索引，默認只壓縮字元串，減少索引大小，對於CPU密集型應用，因為掃描需要隨機查找，壓縮索引在MyISAM上要慢好幾倍

8.重複索引是指在相同的列上按照相同的順序創建的相同類型的索引，應該避免這樣創建重複索引

9.索引可以讓查詢鎖定更少的行

D.維護索引和表

1.CHECK TABLE檢查表是否損壞，ALTER TABLE innodb_tb1 ENGINE=INNODB;修復表

2.records_in_range()通過向存儲引擎傳入兩個邊界值獲取在這個範圍大概有多少條記錄，對於innodb不精確

http://3.info()返回各種類型的數據，包括索引的基數

4.可以使用SHOW INDEX FROM命令來查看索引的基數

5.B-Tree索引可能會碎片化，這會降低查詢的效率

六、查詢性能優化

A.為什麼查詢速度會慢

1.如果要優化查詢，實際上要優化其子任務，要麼消除其中一些子任務，要麼減少子任務的執行次數，要麼讓子任務運行得更快

2.查詢的生命周期大致可以按照順序來看：從客戶端，到伺服器，然後在伺服器上進行解析，生成執行計劃，執行，並返回結果給客戶端

B.慢查詢基礎：優化數據訪問

1.兩個分析步驟：

* 確認應用程序是否在檢索大量超過需要的數據

* 確認MySQL伺服器層是否在分析大量超過需要的數據行

2.是否向資料庫請求了不需要的數據

* 查詢不需要的記錄

* 多表關聯並返回全部列

* 總是取出全部列

* 重複查詢相同的數據

3.MySQL是否在掃描額外的記錄

* 查詢開銷三個指標：響應時間、掃描的行數、返回的行數

* 響應時間：服務時間和排隊時間之和，「快速上限估計」法

* 掃描的行數：較短的行的訪問速度更快，內存中的行也比磁碟中的行的訪問 速度要快得多

* 訪問類型：EXPLAIN中的type列反應了訪問類型；通過增加合適的索引；

* 三種方式應用WHERE條件：在索引中使用WHERE條件來過濾不匹配的記錄；使用索引覆蓋掃描（Extra中出現Using index）來返回記錄，直接從索引中過濾不需要的記錄並返回命中結果；從數據表中返回數據，然後過濾不滿足條件的記錄（Extra中出現Using Where）

* 需要掃描大量數據但只返回少數的行的優化技巧：使用索引覆蓋掃描，改變庫表結構，重寫複雜的查詢

C.重構查詢的方式

1.MySQL從設計上讓連接和斷開連接都很輕量級，在返回一個小的查詢結果方面很高效

2.切分查詢，將大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果，可以避免鎖住很多數據、佔滿事務日誌、耗盡系統資源、阻塞很多小的但重要的查詢

3.分解關聯查詢優勢：

* 讓緩存的效率更高

* 將查詢分解後，執行單個查詢可以減少鎖的競爭

* 在應用層做關聯，可以更容易對資料庫進行拆分，更容易做到高性能和可擴展

* 查詢本身效率也可能會有所提升

* 可以減少冗餘記錄的查詢

* 相當於在應用中實現了哈希關聯，而不是使用MySQL的嵌套循環關聯

4.分解關聯查詢的場景：

* 當應用能夠方便地緩存單個查詢的結果的時候

* 當可以將數據分布到不同的MySQL伺服器上的時候

* 當能夠使用IN()的方式代替關聯查詢的時候

* 當查詢中使用同一個數據表的時候

D.查詢執行的基礎

1.查詢執行路徑

* 客戶端發送一條查詢給伺服器

* 伺服器先檢查查詢緩存，如果命中則立刻返回，否則進入下一階段

* 伺服器端進行SQL解析、預處理，再由優化器生成對應的執行計劃

* MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢

* 將結果返回給客戶端

2.MySQL客戶端和伺服器之間的通信協議是「半雙工」的，無法將一個消息切成小塊獨立來發送，沒法進行流量控制，一旦一端開始發生消息，另一端要接收完整個消息才能響應它

3.MySQL通常需要等所有的數據都已經發送給客戶端才能釋放這條查詢所佔用的資源，所以接收全部結果並緩存通常可以減少伺服器的壓力

4.查詢狀態，SHOW FULL PROCESSLIST命令查看：

* Sleep，線程正在等待客戶端發送新的請求

* Query，線程正在執行查詢或者正在將結果發送給客戶端

* Locked，在MySQL伺服器層，該線程正在等待表鎖

* Analyzing and statistics，線程正在收集存儲引擎的統計信息，並生成查詢的執行計劃

* Copying to tmp table [on disk]，線程正在執行查詢，並且將其結果集都複製到一個臨時表中，要麼是在做GROUP BY操作，要麼是文件排序操作，或者是UNION操作

* Sorting result，線程正在對結果集進行排序

* Sending data，線程可能在多個狀態之間傳送數據，或者在生成結果集，或者在向客戶端返回數據

5.語法解析器和預處理，通過關鍵字將SQL語句進行解析，並生成一棵對應的「解析樹」，解析器將使用MySQL語法規則驗證和解析查詢，預處理器則根據一些MySQL規則進一步檢查解析樹是否合法

6.查詢優化器，找到最好的執行計劃，使用基本成本的優化器，將嘗試預測一個查詢使用某種執行計劃時的成本，並選擇其中成本最小的一個，使用SHOW STATUS LIKE Last_query_cost;查看需要多少個數據頁的隨機查找

7.導致MySQL查詢優化器選擇錯誤的原因：

* 統計信息不準確，Innodb不能維護一個數據表的行數的精確統計信息

* 執行計劃中的成本估算不等同於實際執行的成本

* MySQL的最優可能和你想的最優不一樣

* MySQL從不考慮其他並發執行的查詢

* MySQL也並不是任何時候都是基於成本的優化

* MySQL不會考慮不受其控制的操作的成本

* 優化器有時候無法去估算所有可能的執行計劃

8.MySQL能處理的優化類型：

* 重新定義關聯表的順序

* 將外鏈接轉化成內鏈接

* 使用等價變換規則

* 優化COUNT()、MIN()和MAX()，在EXPLAIN中可以看到「Select tables optimized away」

* 預估並轉化為常數表達式，當檢測到一個表達式可以轉化為常數的時候，就會一直把該表達式作為常數進行優化處理

* 覆蓋索引掃描，當索引中的列包含所有查詢中需要使用的列的時候，就可以使用索引返回需要的數據，而無須查詢對應的數據行

* 子查詢優化

* 提前終止查詢，在發現已經滿足查詢需求的時候，MySQL總是能夠立刻終止查詢

* 等值傳播，如果兩個列的值通過等式關聯，那麼MySQL能夠把其中一個列的WHERE條件傳遞到另一列上

* 列表IN()的比較，MySQL將IN()列表中的數據先進行排序，然後通過二分查找的方式來確定列表中的值是否滿足條件

9.在伺服器層有查詢優化器，卻沒有保存數據和索引的統計信息，統計信息由存儲引擎實現，不同的存儲引擎可能會存儲不同的統計信息

10.在MySQL中，每一個查詢，每一個片段（包括子查詢，甚至基於單表的SELECT）都可能是關聯

11.對於UNION查詢，MySQL先將一系列的單個查詢結果放到一個臨時表中，然後再重新讀出臨時表數據來完成UNION查詢

12.MySQL對任何關聯都執行「嵌套循環關聯」操作，即MySQL先在一個表中循環取出單條數據，然後再嵌套到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止

13.全外連接就無法通過嵌套循環和回溯的方式完成，當發現關聯表中沒有找到任何匹配行的時候，則可能是因為關聯恰好從一個沒有任何匹配的表開始，MySQL不支持全外連接

14.關聯查詢優化器，會嘗試在所有的關聯順序中選擇一個成本最小的來生成執行計劃樹，如果可能，優化器會遍歷每一個表然後逐個做嵌套循環計算每一棵可能的執行樹的成本，最後返回一個最優的執行計劃

15.如果有超過n個表的關聯，那麼需要檢查n的階乘關聯順序，稱為「搜索空間」，搜索空間的增長速度非常快

16.無論如何排序都是一個成本很高的操作，所以從性能角度考慮，應儘可能避免排序或者儘可能避免對大量數據進行排序

17.當不能使用索引生成排序結果的時候，MySQL需要自己進行排序，如果數據量小則在內存中進行，如果數據量大則需要使用磁碟，MySQL將這個過程稱為文件排序（filesort），即使完全是內存排序不需要任何磁碟文件時也是如此

E.MySQL查詢優化器的局限性

1.關聯子查詢：MySQL的子查詢實現得非常糟糕，最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句，使用GROUP_CONCAT()在IN()中構造一個由逗號分隔的列表，或者使用EXISTS()來改寫

2.UNION的限制：有時，MySQL無法將限制條件從外層「下推」到內層，這使得原本能夠限制部分返回結果的條件無法應用到內層查詢的優化上

3.MySQL無法利用多核特性來並行執行查詢

4.MySQL不支持哈希關聯，MariaDB已經實現了哈希關聯

5.MySQL不支持鬆散索引掃描，5.0後版本在分組查詢中需要找到分組的最大值和最小值時可以使用鬆散索引掃描

6.對於MIN()和MAX()查詢，MySQL的優化做得並不好

F.查詢優化器的提示（hint）

1.HIGH_PRIORITY和LOW_PRIORITY，當多個語句同時訪問某一個表的時候，哪些語句的優先順序相對高些、哪些語句的優先順序相對低些

2.DELAYED，對INSERT和REPLACE有效，會將使用該提示的語句立即返回給客戶端，並將插入的行數據放入到緩衝區，然後在表空閑時批量將數據寫入，並不是所有的存儲引擎都支持，並且該提示會導致函數LAST_INSERT_ID()無法正常工作

3.STRAIGHT_JOIN，可以放置在SELECT語句的SELECT關鍵字之後，也可以放置在任何兩個關聯表的名字之間。第一個用法是讓查詢中所有的表按照在語句中出現的順序進行關聯，第二個用法則是固定其前後兩個表的關聯順序

4.SQL_SMALL_RESULT和SQL_BIG_RESULT，只對SELECT語句有效，它們告訴優化器對GROUP BY或者DISTINCT查詢如何使用臨時表及排序

5.SQL_BUFFER_RESULT，告訴優化器將查詢結果放入到一個臨時表，然後儘可能快地釋放表鎖

6.SQL_CACHE和SQL_NO_CACHE，告訴MySQL這個結果集是否應該緩存在查詢緩存中

7.SQL_CALC_FOUND_ROWS，會計算除去LIMIT子句後這個查詢要返回的結果集的總數，而實際上只返回LIMIT要求的結果集，可以通過函數FOUND_ROW()獲得這個值

8.FOR UPDATE和LOCK IN SHARE MODE，主要控制SELECT語句的鎖機制，但只對實現了行級鎖的存儲引擎有效，僅InnoDB支持

9.USE INDEX、IGNORE INDEX和FORCE INDEX，告訴優化器使用或者不使用哪些索引來查詢記錄

10.MySQL5.0後新增的用來控制優化器行為的參數：

* optimizer_search_depth，控制優化器在窮舉執行時的限度

* optimizer_prune_level，讓優化器會根據需要掃描的行數來決定是否跳過某些執行計劃

* optimizer_switch，包含了一些開啟/關閉優化器特性的標誌位

G.優化特定類型的查詢

1.優化COUNT()查詢

* COUNT()是一個特殊的函數，有兩種非常不同的作用：可以統計某個列值的數量，也可以統計行數，在統計列值時要求列值是非空的（不統計NULL）

* COUNT(*)並不是會像我們猜想的那樣擴展成所有的列，實際上，它會忽略所有的列而直接統計所有的行數，當MySQL確認括弧內的表達值不可能為空時，實際上就是在統計行數

* MyISAM的COUNT()函數只有沒有任何WHERE條件下的COUNT(*)才非常快

* 使用近似值，如EXPLAIN出來的優化器估算行數

* 使用索引覆蓋

* 使用匯總表

* 使用外部緩存系統

2.優化關聯查詢

* 確保ON或者USING子句中的列上有索引

* 確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列

* 當升級MySQL的時候需要注意：關聯語法、運算符優先順序等其他可能會發生變化的地方

3.優化子查詢：儘可能使用關聯查詢代替，如果使用MySQL5.6以上或MariaDB則可以忽略這個建議

4.優化GROUP BY和DISTINCT

* 使用索引優化

* 當無法使用索引時，GROUP BY使用兩種策略來完成：使用臨時表或者文件排序來做分組

* 儘可能的將WITH ROLLUP（超級聚合）功能移動應用程序中處理

5.優化LIMIT分頁

* 最簡單的辦法是儘可能地使用索引覆蓋掃描，而不是查詢所有的列，然後根據需要做一次關聯操作再返回所需的列，select id,name,…… from table innert join (select id from table order by xxx limit 5000,5) as table1 USING(id);

* offset會導致MySQL掃描大量不需要的行然後再拋棄掉，如果可以記錄上次取數據的位置，下次就可以直接從該記錄的位置開始掃描，可以避免使用offset

* 使用預先計算的匯總表，或者關聯到一個冗餘表

6.優化UNION查詢

* 通過創建並填充臨時表的方式來執行UNION查詢，因此很多優化策略在UNION查詢中都沒法很好地使用，經常需要手工地將WHERE、LIMIT、ORDER BY等子句下推到UNION的各個子查詢中

* 除非確實需要伺服器消除重複的行，否則就一定要使用UNION ALL

推薦閱讀：