MySQL優化入門

資料庫優化是DBA日常工作中很重要的職責。能在各種場景下優化好資料庫，也是DBA能力的重要展現。而SQL優化，是資料庫優化中的一項核心任務。

如何真正掌握MySQL的SQL優化呢？

MySQL優化

了解執行計劃

MySQL中使用explain檢視執行計劃，需要對執行計劃輸出中的每一項内容都非常熟悉。

官方文檔中對此有詳細的描述：

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

《深入了解MariaDB與MySQL》中第四章，第五章對MySQL執行計劃和SQL優化的各種方法有詳細介紹，建議詳細閱讀。

執行計劃中的幾項關鍵内容： possible_keys, key, key_len, rows。

extra列中有時候也會有一些重要的資訊，官方文檔中對此有較長的描述。

Explain extended

執行explain extended之後，再執行show warnings，可以看到一些額外的資訊。如字段類型隐式轉換導緻索引不可用。

+---------+------+-----------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'ind' due to type or collation conversion on field 'a'   |
| Warning | 1739 | Cannot use range access on index 'ind' due to type or collation conversion on field 'a' |
| Note    | 1003 | /* select#1 */ select `test`.`a`.`a` AS `a` from `test`.`a` where (`test`.`a`.`a` = 1)  |           

了解索引

索引在SQL優化中占有比較重要的作用，需要深入了解索引、聯合索引對各類SQL的作用。

• 了解單表通路路徑：全表掃描，索引掃描。
• 了解索引掃描的過程
• 了解覆寫索引和非覆寫索引的差别
• 了解最基本的分頁SQL優化方法
• 不走索引的幾種情況
• 隐式轉換的規律
• 了解InnoDB Cluster Index

學習官方文檔關于Index的部分

Optimization and indexes

SQL優化案例

MySQL的優化器基于COST和一些規則來選擇具體的執行路徑。學習使用optimizer_trace來觀察優化器的優化過程。從官方文檔學習optimizer trace的幾個相關參數的作用

• optimizer_trace
• optimizer_trace_features
• optimizer_trace_limit
• optimizer_trace_max_mem_size
• optimizer_trace_offset

MySQL SQL 優化

仔細閱讀官方文檔中的

Optimizing SQL Statements

章節的内容

搞清楚下面這些内容的含義

• Range Optimization， 參數range_optimizer_max_mem_size的作用。參數eq_range_index_dive_limit的作用。
• Index Merge
• Engine Condition Pushdown 和 Index Condition Push Down
• 表關聯的方法, nested loop，
• join buffer的作用，了解參數join_buffer_size
• order by的優化，排序相關幾個參數的作用： max_length_for_sort_data， max_sort_length，sort_buffer_size
• group by, distinct
• limit對執行計劃的影響

學習子查詢、派生表和視圖等相關的優化内容

Optimizing Subqueries, Derived Tables, and View References

到官方文檔查找排序相關參數的作用

show global variables like '%sort%'
| Variable_name                  | Value               |
+--------------------------------+---------------------+
| innodb_disable_sort_file_cache | OFF                 |
| innodb_ft_sort_pll_degree      | 2                   |
| innodb_sort_buffer_size        | 1048576             |
| max_length_for_sort_data       | 1024                |
| max_sort_length                | 1024                |
| myisam_max_sort_file_size      | 9223372036853727232 |
| myisam_sort_buffer_size        | 8388608             |
| sort_buffer_size               | 262144              |           

其它優化相關的參數

• optimizer_search_depth
• optimizer_switch中每個開關的作用，大緻了解對應的算法和适用場景