MySQL优化入门

lazydba 2020-03-26

mysql SQL 数据库 排序 索引 test index html Access 执行计划 数据库优化

数据库优化是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中每个开关的作用,大致了解对应的算法和适用场景
登录 后评论
下一篇
云栖号资讯小编
1320人浏览
2020-05-25
相关推荐
FAQ系列 | MySQL DBA修炼秘籍
1501人浏览
2017-08-02 13:47:00
NoSql入门概述
732人浏览
2016-10-21 19:51:00
0
0
0
449