1. “快速、精確和實現簡單”

1.1. 三者永遠只能滿足其二，必須舍掉一個

2. 排序優化

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

2.2. 文件排序（filesort）

2.2.1. MySQL需要自己進行排序，如果數據量小則在記憶體中進行，如果數據量大則需要使用磁碟

2.2.2. 完全是在記憶體中排序不需要任何磁碟文件時也是如此

2.3. 排序演算法

2.3.1. 兩次傳輸排序（舊版本使用）

2.3.1.1. 讀取行指針和需要排序的欄位，對其進行排序，然後再根據排序結果讀取所需要的數據行

2.3.1.2. 即需要從數據表中讀取兩次數據，第二次讀取數據的時候，因為是讀取排序列進行排序後的所有記錄，這會產生大量的隨機I/O，所以兩次傳輸排序的成本非常高

2.3.2. 單次傳輸排序（新版本使用）

2.3.2.1. 先讀取查詢所需要的所有列，然後再根據給定列進行排序，最後直接返回排序結果

2.3.2.2. 不再需要從數據表中讀取兩次數據，對於I/O密集型的應用來說，這樣做的效率高了很多

2.3.2.3. 只需要一次順序I/O就可讀取所有的數據，而無須任何的隨機I/O

2.4. MySQL在進行文件排序時需要使用的臨時存儲空間可能會比想象的要大得多

2.5. 如果使用utf8mb4字元集，那麼MySQL將會為每個字元預留4位元組

2.6. 如果查詢中有LIMIT的話，LIMIT也會在文件排序之後應用，所以即使需要返回較少的數據，臨時表和需要排序的數據量仍然會非常大

3. 將結果返回給客戶端

3.1. 執行查詢的最後一個階段是將結果返回給客戶端

3.2. MySQL將結果集返回客戶端是一個增量且逐步返回的過程

3.2.1. 伺服器端無須存儲太多的結果，也就不會因為要返回太多結果而消耗太多記憶體

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

4.1. MySQL查詢優化器只對少部分查詢不適用，而且我們往往可以通過改寫查詢讓MySQL高效地完成工作

4.2. UNION的限制

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

4.2.2. 從臨時表中取出數據的順序並不是一定的，所以如果想獲得正確的順序，還需要在最後的LIMIT操作前加上一個全局的ORDER BY操作

4.3. 等值傳遞

4.3.1. 優化器通過將列表複製到所有相關表中的相應列來“共用”列表

4.4. 並行執行

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

5. 優化特定類型的查詢

5.1. 多數優化技巧都和特定的版本有關，所以對於未來MySQL的版本未必適用

5.2. 優化COUNT()查詢

5.2.1. COUNT()是一個特殊的函數

5.2.1.1. 可以統計某列的值的數量

5.2.1.2. 可以統計行數

5.2.2. 用COUNT（）的時候，這種情況下通配符並不會像我們猜想的那樣擴展成所有的列，實際上，它會忽略所有的列而直接統計所有的行數

5.2.2.1. 如果想要知道結果中的行數，應該始終使用COUNT（*），這樣可以更清晰地傳達意圖，避免糟糕的性能表現

5.2.3. 常見的錯誤之一是當需要統計行數時，在COUNT()函數的括弧內指定了列名

5.2.4. 計算精確值非常複雜，而計算近似值則非常簡單

5.2.4.1. 可以增加類似Memcached這樣的外部緩存系統

5.3. 優化聯接查詢

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

5.3.1.1. 沒有用到的索引只會帶來額外的負擔

5.3.2. 確保任何GROUP BY和ORDER BY中的表達式只涉及一個表中的列，這樣MySQL才有可能使用索引來優化這個過程

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

5.3.4. 使用WITH ROLLUP優化GROUP BY

5.3.4.1. 分組查詢的一個變種就是要求MySQL對返回的分組結果再做一次超級聚合

5.3.4.2. 在應用程式中做超級聚合是更好的，雖然這需要給客戶端返回更多的結果

5.3.4.3. 最好的辦法是儘可能地將WITH ROLLUP功能轉移到應用程式中處理

5.4. 優化LIMIT和OFFSET子句

5.4.1. 在系統中需要進行分頁操作的時候，我們通常會使用LIMIT加上偏移量的辦法實現，同時加上合適的ORDER BY子句

5.4.2. 在頁面中限制分頁的數量

5.4.3. 優化大偏移量的性能

5.4.4. 儘可能地使用索引覆蓋掃描，而不是查詢所有的行

5.4.5. 將LIMIT查詢轉換為已知位置的查詢，讓MySQL通過範圍掃描獲得對應的結果

5.4.6. OFFSET的問題

5.4.6.1. 會導致MySQL掃描大量不需要的行然後再拋棄掉

5.4.6.2. 可以使用書簽記錄上次取數據的位置，那麼下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET

5.4.6.2.1. 無論翻頁到多麼靠後，其性能都會很好

5.4.6.3. 冗餘表，冗餘表只包含主鍵列和需要做排序的數據列

5.5. 更好的設計

5.5.1. 將具體的頁數換成“下一頁”按鈕

5.5.1.1. 假設每頁顯示20條記錄，那麼我們每次查詢時都是用LIMIT返回21條記錄並只顯示20條

5.5.1.2. 如果第21條存在，那麼就顯示“下一頁”按鈕，否則就說明沒有更多的數據，也就無須顯示“下一頁”按鈕了

5.5.2. 先獲取並緩存較多的數據

5.5.2.1. 緩存1000條——然後每次分頁都從這個緩存中獲取

5.5.2.2. 如果結果集小於1000，就可以在頁面上顯示所有的分頁鏈接，因為數據都在緩存中，所以這樣做不會對性能造成影響

5.5.2.3. 如果結果集大於1000，則可以在頁面上設計一個額外的“找到的結果多於1000條”之類的按鈕

5.5.3. 比每次生成全部結果集再拋棄不需要的數據的效率高很多

5.6. 優化SQL CALC FOUND ROWS

5.6.1. 在LIMIT語句中加上SQL_CALC_FOUND_ROWS提示（hint），這樣就可以獲得去掉LIMIT以後滿足條件的行數，因此可以作為分頁的總數

5.6.2. 加上這個提示以後，不管是否需要，MySQL都會掃描所有滿足條件的行，然後再拋棄掉不需要的行，而不是在滿足LIMIT的行數後就終止掃描

5.6.3. 該提示的代價可能非常高

5.6.4. 當需要精確結果的時候，再單獨使用COUNT（*）來滿足需求，這時如果能夠使用索引覆蓋掃描則通常也會比SQL_CALC_FOUND_ROWS快得多

5.7. 優化UNION查詢

5.7.1. 經常需要手工地將WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各個子查詢中，以便優化器可以充分利用這些條件進行優化

5.7.2. 除非你確實需要伺服器消除重覆的行，否則一定要使用UNION ALL