MySQL查询优化之道

01

查询优化器模块

查询优化器的任务是发现执行 SQL 查询的最佳方案。大多数查询优化器，要么基于规则、要么基于成本。

大多数查询优化器，包含 MySQL 的查询优化器，总或多或少地在所有可能的查询评估方案中搜索最佳方案。

MySQL 中 MySQL Query Optimizer 是优化器的核心，当 MySQL 数据拿到一个 Query 语句之后会交给 Query Optimizer 去解析，并产生一个最优的执行计划（这个是 Optimizer 认为是最优的，但不一定是真正最优的，就跟 Oracle 数据库会估算错 rows 一样）。

然后数据库按照这个执行计划去执行查询语句。

在 SQL 语句整个执行过程中，Optimizer 是最耗时的，但是也有第三方工具为了提高性能绕开 MySQL 的 Query Optimizer 模块，比如：handlersocket。

对于多表关联查询，MySQL 优化器所查询的可能方案数随查询中引用的表的数目成指数增长。

对于小数量的表，这不是一个问题。

然而，当提交的查询需要的结果集很大时，查询优化所花的时间会很容易地成为服务器性能的瓶颈。

查询优化的一个更加灵活的方案时容许用户控制优化器详细地搜索最佳查询评估方案。一般思想是调查的方案越少，它编译一个查询所花费的时间越少。

另外，由于优化器跳过一些方案，它可能错过一个最佳方案。优化器关于方案数量评估的行为可以通过两个系统变量来控制：

optimizer_prune_level 变量告诉优化器根据对每个表访问的行数的估计跳过一些方案。我们的试验显示该类 “有根据的猜测” 很少错过最佳方案，并且可以大大降低查询编辑次数。

这就是为什么默认情况该选项为on(optimizer_prune_level=1)。

然而，如果你认为优化器错过了一个更好的查询方案，则该选项可以关闭(optimizer_prune_level=0)，风险是查询编辑花费的时间更长。

请注意即使使用该启发，优化器仍然可以探测呈指数数目的方案。

timizer_search_depth 变量告诉优化器对于每个未完成的 “未来的” 方案，应查看多深，以评估是否应对它进一步扩大。

optimizer_search_depth 值较小会使查询编辑次数大大减小。

例如，如果optimizer_search_depth 接近于查询中表的数量，对 12、13 或更多表的查询很可能需要几小时甚至几天的时间来编译。

同时，如果用 optimizer_search_depth 等于 3 或 4 编辑，对于同一个查询，编译器编译时间可以少于 1 分钟。

如果不能确定合理的 optimizer_search_depth 值，该变量可以设置为 0，告诉优化器自动确定该值。

02

查询优化的基本思路

不管做项目设计还是产品设计都需要先有思路，才能规避一些问题。

当然 MySQL 查询优化也需要研发或者 DBA 拥有一些思路，唯有思路指导书写，才会更加合理。

1. 优化更需要优化的 Query 语句

应该优化并发高的 Query 语句，不至于高并发下，由于 SQL 导致应用程序卡死，比如 php-fpm 的大量等待，而且一个高并发的 Query 语句，如果走错执行计划，本来只需要扫描几百行，结果扫描了几百万行，可能会有灾难性的后果，更加会导致业务卡顿，尤其是核心业务下出现的高并发 Query 语句。

2. 查看执行计划调整 Query 语句

根据 explain extended SQL 分析查询语句，就能查看执行计划，这个时候需要关注执行计划中的一些要素：

• id：查询的序列化

select type

• depent subquery：说明该查询是子查询中的第一个 Select, 依赖与外部查询的结果集
• PRIMARY：子查询的最外层查询，注意不是主键查询
• simple：除子查询或者 UNION 之外的其它查询
• table：访问数据表的名称，书写 SQL 的人，需要明确此表是否是核心表、是否是大数据量表等

type 扫描方式

• all：全表扫描
• const：读常量，且最多只有一条记录匹配。由于是常量只需要读一次
• index：全索引扫描
• eq_ref：最多只有一条匹配结果 通过主键和唯一索引来访问的
• range：索引范围扫描
• possible_keys：该查询可以利用到的索引有哪些
• key：优化器模块选择用了哪个索引，有索引不一定就会用到，看执行计划才知道用了哪个。
• key_len：索引长度
• rows：返回的行数
• extra：附加信息，比如 using filesort---> 说明用了排序算法
• filtered：列给出了一个百分比的值，这个百分比值和 rows 列的值一起使用，可以估计出那些将要和 QEP 中的前一个表进行连接的行的数目。前一个表就是指 id 列的值比当前表的 id 小的表。这一列只有在 EXPLAIN EXTENDED 语句中才会出现。

3. 学会查看性能损耗（cpu 消耗、io 消耗）

当发现有慢 Query 语句时，需要定位到底是哪里慢，CPU 还是 IO 等：

mysql>set profiling=1;mysql>show profiles;mysql>show profile cpu,block io for query n;

03

查询的基本原则

1. 永远用小结果集驱动大结果集

做 join 查询时，驱动表，一定是条件限定后记录较少的表。

MySQL 的 join 只有一种算法 nested loop 也就是程序中的 for 循环，通过嵌套循环实现，驱动结果集越大，所需要循环的次数越多，访问被驱动表的次数也越多。

降低 IO 同时降低 CPU。

2. 只查询需要的列

只查询需要的列，可以让 IO 降低，列和排序算法也有关系。

3. 仅仅使用最有效的过滤条件

前提是用 a 条件 查询出结果 用 b 条件查询出结果，a、b 都用查询出结果，这三次结果都一样。

到底是用 a 条件还是 b 条件，还是两个条件都限定，只能看执行计划。

4. 尽量避免复杂的 join 和子查询

5. 尽量在索引列上完成排序和查询

• 在索引列上排序：索引列上是排好序的，不需要启动额外的排序的算法降低了 CPU 的损耗。
• 在索引列上查询：降低了 IO 的损耗
• 创建索引，优化器模块并不一定会用，但可以 SQL 中加上 force index（强制走那个索引）.

04

索引利弊及索引分类

万事万物都有利弊，一个东西的出现，比如会在不同场景下有好好坏，就看如何权衡。

好处：

• 通过索引列查询数据，能够提高数据检索的效率，降低数据库的 IO 成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。

坏处：

• 假设表 a 其中有列 column ca 给其创建索引 indxaca：
• 每次更新 ca 的操作，都会调整因为更新所带来的键值变化后的索引信息，这样就会增加 IO 损耗，索引列也是要占用空间的，a 列数据的增多，indxaca 索引占用的空间也会不断增长。所以索引还会带来存储空间资源的消耗。

05

索引分类

• b-tree 索引：根据平衡二叉树演变来的
• hash 索引：
• hash 索引只能满足 "="、"in" <> 查询，不能支持范围查询
• hash 索引无法被利用进行排序操作
• hash 索引不能利用部分索引键查询
• hash 索引不能避免表扫描
• full-text 索引：只有 myisam 存储引擎支持 ---> 只有 char 、varchar、text 支持，但是在 MySQL 5.7，innodb 存储引擎也支持啦。
• R-Tree 索引：主要解决空间数据检索问题，极少使用。

06

索引相关优化

1. 如何判断是否需要创建索引

• 频繁作为查询条件的字段应该创建索引。
• 唯一性太差的字段不适合单独创建索引。比如该字段重复上千万；即使你创建了索引优化器模块是不会选择使用的；会有极大的性能问题 有很多重复值，会带来大量的随机 IO 甚至是重复 IO。
• 更新非常频繁的字段不适合创建索引：不仅仅更新表中的数据，还需要更新索引数据 IO 访问增大。
• 不会出现在 where 字句中的字段不该创建索引。
• 单键索引还是组合索引。

2. MySQL 中索引的限制

• 是否用到了索引可以查看执行计划
• 在任何索引列上做计算、函数、类型转换（哪怕是自动的）都会使得索引失效而转向全表扫描操作：不要在索引列上做任何操作因为可能为导致索引失效。
• MySQL 在使用不等于 (!= or <>) 的时候无法使用索引会导致全表扫描。
• is null ,is not null 也无法使用索引。
• join 语句中 join 条件字段类型不一致的时候 MySQL 无法使用索引。
• 模糊查询的时候 (like 操作) 如果以通配符开头 ('%abc...')MySQL 索引失效会变成全表扫描的操作。
• 如果使用的是 hash 索引，在做非等值连接时候无法使用索引，会是全表扫描的操作。
• 在 MySQL 中 BLOB 和 Text 类型的列只能创建前缀索引。
• MyISAM 存储引擎的话索引键长度总和不能超过 1000 字节。（好像从 5.7 之后，大多默认 innodb 存储引擎）
• 当有唯一性索引和非唯一性索引都存在时，往往只会选择唯一性索引。
• 组合索引，查询时组合索引第一列出现的时候会使用索引。

3. 使用索引的一些建议

• 对于单键索引，尽量选择针对当前 Query 过滤性更好的索引。
• 在选择组合索引的时候，当前 Query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择可以能够包含当前 Query 中的 where 字句中更多字段的索引。
• 尽可能通过分析统计信息和调整 Query 的写法来达到选择合适索引的目的。减少通过使用 Hint 认为控制索引的选择，如果使用 Hint 会使得后期维护成本比较高。

综上所述，大致简单明了的阐述了 MySQL 查询优化一些相关的东西，至少对于中小型企业，可以作为研发人员的数据库规范，避免后期迁移或扩容时的一些问题。