MySQL优化入门

2022-02-03 01:06:11

数据库优化是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中每个开关的作用，大致了解对应的算法和适用场景

码农公寓