mysql執行計劃是優化sql性能的關鍵工具，使用explain命令可查看其詳細信息。1. id字段表示查詢順序，相同則從上到下執行，不同則值越大越先執行；2. select_type說明查詢類型，如simple為簡單查詢，subquery為子查詢，建議改寫為join；3. table字段顯示訪問的表名；4. partitions顯示分區表的命中情況；5. type為訪問類型，all和index應避免，優先提升至eq_ref或ref；6. possible_keys列出可能使用的索引；7. key顯示實際使用的索引，若為NULL需檢查索引有效性；8. key_len用于判斷是否使用組合索引全部列；9. ref顯示索引匹配的具體列；10. rows表示預估掃描行數，越少越好；11. filtered表示過濾比例，越高越優；12. extra提供額外信息，如using index為覆蓋索引，using filesort和using temporary應盡量避免。此外，索引失效常見于where中使用函數、類型不匹配、like以%開頭、or條件未全用索引、組合索引未使用最左前綴等場景，可通過改寫sql、添加索引等方式應對。結合慢查詢日志分析可進一步優化數據庫性能。
mysql執行計劃，簡單來說，就是Mysql優化器對于sql語句執行過程的預估。它能告訴你MySQL將如何使用索引、連接表，以及整個查詢的執行順序。理解執行計劃是優化SQL性能的關鍵一步。
EXPLaiN命令是查看執行計劃的利器。在SELECT語句前加上EXPLAIN，就能得到MySQL對該查詢的執行計劃報告。
EXPLAIN結果深度解析
EXPLAIN 語句會返回多行數據，每一行代表查詢中的一個操作。以下是EXPLAIN結果中各個字段的詳細解釋以及如何利用它們來優化SQL語句：
1. id：查詢的標識符
- 含義：表示SELECT查詢的序列號，用于標識查詢中操作的執行順序。
- 值：
- 如果id相同，則執行順序從上到下。
- 如果id不同，值越大優先級越高，越先被執行。
- 如果id為NULL，則表示這是一個union查詢的結果。
- 優化思路：關注id的順序，確保連接順序合理，避免不必要的全表掃描。
2. select_type：查詢的類型
- 含義：描述查詢的類型，例如簡單查詢、子查詢或UNION查詢。
- 常見值：
- SIMPLE：簡單查詢，不包含子查詢或UNION。
- PRIMARY：最外層的SELECT查詢。
- SUBQUERY：SELECT或WHERE列表中包含的子查詢。
- DERIVED：在FROM子句中出現的子查詢。MySQL會將結果存放在一個臨時表中，也稱為派生表。
- UNION：UNION語句中的第二個或后面的SELECT查詢。
- UNION RESULT：從UNION的臨時表中檢索結果。
- 優化思路：盡量避免SUBQUERY和DERIVED，因為它們通常會導致性能問題。可以嘗試將子查詢改寫成JOIN。
3. table：查詢訪問的表
- 含義：表示查詢訪問的表名或別名。
- 值：直接顯示表名或者表的別名。
- 優化思路：確認是否訪問了正確的表，是否存在不必要的表連接。
4. partitions：表分區
- 含義：如果表是分區表，則顯示查詢訪問的分區。
- 值：顯示命中的分區。
- 優化思路：如果查詢沒有用到分區索引，可能會導致全部分區掃描，需要檢查SQL語句和分區策略。
5. type：訪問類型
- 含義：描述MySQL如何查找表中的行，是性能優化的關鍵指標。
- 常見值（從最佳到最差）：
- system：表只有一行記錄，是const類型的特殊情況。
- const：通過主鍵或唯一索引一次就能找到。
- eq_ref：使用唯一索引查找，常見于主鍵或唯一索引的關聯查詢。
- ref：使用非唯一索引查找。
- fulltext：使用全文索引。
- ref_or_null：類似于ref，但是MySQL會對包含NULL值的列進行額外的搜索。
- index_merge：使用了索引合并優化策略。
- unique_subquery：用于替換IN子查詢的一種形式，返回不重復值字段。
- index_subquery：類似于unique_subquery，但返回的是非唯一值字段。
- range：使用索引范圍掃描。
- index：全索引掃描。
- ALL：全表掃描。
- 優化思路：盡量避免ALL和index，盡可能提升到ref或eq_ref。
6. possible_keys：可能使用的索引
- 含義：MySQL在查詢中可能使用的索引。
- 值：列出可能用到的索引。
- 優化思路：即使possible_keys中有索引，MySQL也可能不使用。需要結合key字段來判斷。
7. key：實際使用的索引
- 含義：MySQL實際使用的索引。
- 值：顯示實際使用的索引名。
- 優化思路：如果key為NULL，但possible_keys不為NULL，說明MySQL認為沒有合適的索引可用。需要檢查索引是否有效，或者考慮創建新的索引。
8. key_len：索引的長度
- 含義：使用的索引的長度。在不損失精確性的情況下，長度越短越好。
- 值：計算得到索引長度。
- 優化思路：可以通過計算key_len來判斷是否使用了組合索引的所有列。
9. ref：索引的哪一列被使用了
- 含義：顯示索引的哪一列被使用了，常用于關聯查詢。
- 值：顯示具體的列名或const。
- 優化思路：檢查是否使用了正確的列進行索引匹配。
10. rows：估計需要檢查的行數
- 含義：MySQL估計為了找到所需的行而需要讀取的行數。
- 值：估計的行數。
- 優化思路：rows越小越好，說明MySQL需要掃描的行數越少。
11. filtered：過濾比例
- 含義：表示經過搜索條件過濾后剩余記錄的百分比。
- 值：百分比。
- 優化思路：filtered越高越好，說明搜索條件過濾性越好。
12. Extra：額外信息
- 含義：包含MySQL解決查詢的額外信息。
- 常見值：
- Using index：使用了覆蓋索引，避免了回表查詢。
- Using where：使用了WHERE子句過濾結果。
- Using temporary：MySQL需要創建臨時表來存儲結果，常見于ORDER BY和GROUP BY。
- Using filesort：MySQL需要使用文件排序，而不是索引排序。
- Using join buffer (Block Nested Loop)：使用了連接緩存。
- Impossible WHERE noticed after reading const tables：WHERE子句總是false，導致沒有符合條件的行。
- Select tables optimized away：使用了某些優化策略，例如直接從索引中獲取數據，而不需要訪問表。
- Distinct：優化DISTINCT操作，當找到第一匹配的元組后停止搜索。
- 優化思路：
- Using temporary和Using filesort通常是性能瓶頸，應該盡量避免。可以通過添加索引來優化排序。
- Using index是好的，說明使用了覆蓋索引。
索引失效的常見情況與應對
索引失效是導致查詢性能下降的常見原因。以下是一些常見的索引失效情況以及相應的應對策略：
- WHERE子句中使用函數或表達式：
- 例如：WHERE date(order_date) = ‘2023-10-26’。
- 應對：盡量避免在WHERE子句中使用函數或表達式，可以將函數或表達式移到等號的另一邊。例如：WHERE order_date = STR_TO_DATE(‘2023-10-26’, ‘%Y-%m-%d’)。
- 類型不匹配：
- LIKE語句以%開頭：
- 例如：WHERE column LIKE ‘%abc’。
- 應對：盡量避免使用以%開頭的LIKE語句，如果必須使用，可以考慮使用全文索引。
- OR條件：
- 如果OR連接的多個條件中，只有一個條件使用了索引，則MySQL可能會放棄使用索引。
- 應對：盡量使用UNION ALL代替OR，或者確保OR連接的所有條件都使用了索引。
- 組合索引未使用最左前綴：
- 例如：組合索引是(a, b, c)，但WHERE子句中只使用了b和c。
- 應對：確保WHERE子句中使用了組合索引的最左前綴列。
- MySQL認為全表掃描更快：
- 當MySQL估計全表掃描比使用索引更快時，它可能會放棄使用索引。
- 應對：可以通過ANALYZE TABLE命令更新表的統計信息，或者強制使用索引（FORCE INDEX）。
慢查詢日志分析
MySQL慢查詢日志可以記錄執行時間超過指定閾值的SQL語句。通過分析慢查詢日志，可以找到需要優化的SQL語句。
-
開啟慢查詢日志：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 設置閾值為1秒
-
查看慢查詢日志文件位置：
SHOW VARIABLES LIKE 'slow_query_log_file';
-
分析慢查詢日志：
可以使用mysqldumpslow工具或者其他日志分析工具來分析慢查詢日志。
總結
理解MySQL執行計劃是SQL優化的基礎。通過EXPLAIN命令，我們可以了解MySQL如何執行查詢，并根據執行計劃中的信息來優化SQL語句，例如添加索引、改寫SQL語句等。同時，結合慢查詢日志，可以找到需要優化的SQL語句，從而提升數據庫的整體性能。
