查询优化排查
查询优化的排查意味着我们需要先了解Mysql的各个组件在各步骤中做了哪些事情，下面这张图来自于《高性能Mysql》，对于一次客户端的请求，大致分为下面的流程：
- 客户端发送请求
- 服务器查询执行缓存
- 不重要，8.0之后已经删除
- 服务端进行SQL解析和预处理
- 权限检查
- 词法解析
- 语法树
- 优化器生成执行计划
- 优化器存在的问题？
- 优化器如何工作？
- 根据执行计划调用存储引擎的APi接口执行查询
- 结果返回客户端
对于关系型的数据库来说，核心部分在于查询优化器和执行计划的部分，因为不管我们如何编写SQL语句，如果没有强大的优化器和执行计划那么一切都是空谈，所以本部分的重点也会围绕优化器进行讲解，在此之前我们先看看其他组件的工作：
首先查询缓存不需要过多解释，他的作用是当用户重复执行一个查询的时候会内部对于结果进行缓存，但是一旦用户修改查询条件，缓存就失效了，在早期的互联网环境中这种处理很不错，可以减少磁盘IO和CPU的压力，但是到了现在的环境下显然不适合，所以8.0删除也是可以理解的。
接着是解析器，解析器这部分主要工作是通过解析语法形成解析树对于语句进行预处理，预处理可以类比左我们编译器把我们写的编程语句“翻译”为机器代码的过程，让下一步的优化器可以认识这颗解析树去进行解析，
如果想要了解SQL解析优化的底层过程，可以从这篇文章入手：
SQL解析在美团的应用 - 美团技术团队 (meituan.com)
在上面的博客中提到了一个DBA必须掌握的工具pt-query-digest，分析慢查询日志，下面这个文章中提供了一个实际的案例来排查和优化，案例较为简单适合刚接触这个工具的人进行学习和思考，这里一并列出来了。
使用 pt-query-digest 分析 RDS MySQL 慢查询日志 | 亚马逊AWS官方博客 (amazon.com)
SQL解析部分笔记：
词法分析：核心代码在sql/sql_lex.c文件中的，
MySQLLex→lex_one_TokenMySQL语法分析树生成过程：全部的源码在
sql/sql_yacc.yy中，在MySQL5.6中有17K行左右代码最核心的结构是SELECT_LEX，其定义在
sql/sql_lex.h中
下面我们来深入看看优化器的部分工作内容以及Mysql优化历史：
由于讲述优化器的内容较少，这里直接总结《高性能Mysql》的内容，优化器也不需要研究和记忆，因为随着版本的迭代不断更新优化器会不断调整，一切要以真实实验为准：
1. 子查询关联：
下面的查询在通常情况下我们会认为先进行子查询，然后通过for循环扫描film表进行匹配操作，然后从explain的结果中可以看到这里的查询线进行了全表扫描，然后通过关联索引进行第二层的for循环查询，这样的写法类似exists。
explain select * from sakila.film where film_id in (select film_id from film_actor where actor_id) -- 1 SIMPLE film ALL PRIMARY 1000 100.00 -- 1 SIMPLE film_actor ref idx_fk_film_id idx_fk_film_id 2 sakila.film.film_id 5 90.00 Using where; Using index; FirstMatch(film)
优化这个子查询的方式使用关联查询替代子查询，但是需要注意这里存在where条件才会走索引，否则和上面的结果没有区别：
explain select film.* from sakila.film film join film_actor actor using (film_id) where actor.actor_id = 1
另一种是使用exists的方式进行关联匹配。
explain select * from film where exists (select * from film_actor actor where actor.film_id = film.film_id and actor.actor_id = 1);
可以看到哪怕到了5.8的版本，Mysql的子查询优化既然没有特别大的改进，所以通常情况下如果不确定in查询的内容大小，建议用exists或者join进行查询，另外也不要相信什么in查询就一定慢点说法，在不同的mysql优化器版本中可能会有不同的效果。
2. union查询
虽然多数情况下我们会用union替换or，但是更多的情况是应该尽量避免使用union，因为union查询会产生临时表和中间结果集容易导致优化索引失效，需要注意的是 union会触发内部的排序动作，也就是说union会等价于order by的排序，如果数据不是强烈要求不能重复，那么更建议使用union all，对于优化器来说这样工作更加简单，直接把两个结果集凑在一起就行，也不会进行排序。
union查询能不用就不用，除非是用来代替or查询的时候酌情考虑是否有必要使用。
最后注意union的产生排序不受控制的，可能会出现意料之外的结果。
3. 并行查询优化
并行查询优化在8.0中终于有了实现，可以根据参数：innodb_parallel_read_threads =并行数来验证。
由于个人是M1的CPU，读者可以根据自己的实际情况进行实验。
set local innodb_parallel_read_threads = 1; select count(*) from payment; set local innodb_parallel_read_threads = 6; select count(*) from payment;
从执行结果可以看到仅仅是1万多条数据的count(*)查询就有明显直观的差距：
4. 哈希关联
官方文档的介绍地址：Mysql官方文档哈希关联
在MySQL 8.0.18中Mysql终于增加了哈希关联的功能。在此之前的版本中，Mysql的优化器通常只支持for循环嵌套关联，曲线救国的方法是建立一个哈希索引或者使用Memory存储引擎，而新版本提供的哈希关联则提供了一种新的对关联方式，哈希关联的方式如下：
把一张小表数据存储到内存中的哈希表里，通过匹配大表中的数据计算哈希值，并把符合条件的数据从内存中返回客户端。
对于Mysql的哈希关联，我们直接使用官方的例子：
CREATE TABLE t1 (c1 INT, c2 INT); CREATE TABLE t2 (c1 INT, c2 INT); CREATE TABLE t3 (c1 INT, c2 INT); EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1; -- Using where; Using join buffer (hash join)
除开等值查询以外，Mysql的8.0.20之后提供了更多的支持，比如在 MySQL 8.0.20 及更高版本中，连接不再需要包含至少一个等连接条件才能使用哈希连接，除此之外它还包括下面的内容：
-- 8.0.20 支持范围查询哈希关联 EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1; -- 8.0.20 支持 in关联 EXPLAIN SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c2 FROM t2); -- 8.0.20 支持 not exists 关联 EXPLAIN SELECT * FROM t2 WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c2); -- 8.0.20 支持 左右外部连接 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1; EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;
注意8.0.18版本的哈希关联仅仅支持join查询，对于可能会带来笛卡尔积的左连和右连接查询是不支持的。但是在后续的版本中提供了更多查询条件支持
另外，8.0.20版本之前想要查看是否使用hash join，需要结合
format=tree选项。
最终Mysql在8.0.18版本中曾经提供过开关哈希索引和设置优化器提示optimizer_switch等参数来判定是否给予hash join的提示，真是闲的蛋疼（官方自己也这么认为）所以在8.0.19立马就把这些参数给废弃。
注意哈希连接不是没有限制的，了解哈希关联的流程就会发现如果哈希表过大，会导致整个哈希关联过程在磁盘中完成其速度可想而知，所以官方提供了下面的建议：
- 增加
join_buffer_size，也就是增加哈希关联的哈希表缓存大小，防止进入磁盘关联。 - 增加
open_files_limit数量，这个参数什么意思这里就不介绍了，意义是增加这个参数可以增加关联的时候关联次数。
吐槽：说句心里话自Mysql被Oracle收购之后，越来越商业化的同时进步也越来越小，in查询优化这一点其实在很多开源库甚至Mysql的原作者给解决了，但是Mysql到了8.0依然和多年前的《高性能Mysql》结果没有差别。哎。。。。。
Mysql数据库的发展也告诉我们时刻保持开放的心态，吸取教训正视不足和改进，才不会被时代逐渐淘汰。
5. 松散索引
松散索引在Mysql5.6之后已经支持，松散索引简单理解就是在进行多列索引扫描的时候，即使次索引不是有序的，但是跳过索引是有序的，也可以走索引来快速匹配数据。
松散索引的优化细节放到了下半部分的文章，这里简单讲述一下大致的工作原理。
- 查询同时更新数据
在Postgresql中，支持下面的语法：
update tbl_info set name = tmp.name from (select name from tbl_user where name ='xxx') tmp [where ....] -- 比如下面的写法： UPDATE `sakila`.`actor` SET `first_name` = 'PENELOPE' from (select address,address_id from address where address_id = 1) tmp WHERE `actor_id` = 1 and actor.actor_id = tmp.address_id;
但是很可惜这种语法在Mysql是没有办法实现也是不支持的，哪怕到了8.0.26依然没有支持，这和Mysql的优化器设计有着本质的关系。
- 优化器提示设置
优化器提示没有多少意义，这里直接略过了。
- 最大值和最小值优化
从实际的情况来看Mysql最大值和最小值这两个函数使用并不是很多所以不再进行介绍了，另外无论什么样的数据库都不是很建议频繁使用函数，而是该用业务+简单SQL实现高效索引优化。
其他慢查询优化
对于慢查询的优化我们需要清楚优化是分为几种类别的，在Mysql中优化策略分为动态优化和静态优化：静态优化主要为优化更好的写法，比如常数的排序和一些固定的优化策略等，这些动作通常在一次优化过程中就可以完成。而动态优化策略要复杂很多，可能会在执行的过程中优化，有可能在执行过后重新评估执行计划。
静态优化是受优化器影响的，不同版本有不同情况，所以这里讲述动态优化的情况，而动态优化主要包含下面的内容：
- 关联表顺序，有时候关联表顺序和查询顺序不一定相同。
- 重写外连接为内连接：如果一个外连接关联是没有必要的就优化掉外连接关联。
- 等价替换，比如 a>5 and a= 5被优化为a >= 5 ，类似数学的逻辑公式简化
- 优化count()、max()、min()等函数：有时候找最大和最小值只需要找最大和最小的索引记录，这时候由于不需要遍历，可以认为直接为哈希的获取记录的方式，所以在查询分析的 extra 里面进行体现（Select tables optimized away），比如：explain select max(actor_id) from actor;
- 预估和转化常数：以连接查询为例，如果在查询条件中可以实现预估关联的记录条数，那么对于一个关联查询来说就有可能被优化器作为常数进行优化，因为事先取出记录的条数被优化器知晓。所以优化起来十分简单。
- 子查询优化：子查询虽然有可能被索引优化但是需要尽量避免使用。
- 覆盖索引扫描：让索引和查询列一致时非常高效的优化和执行方式
- 提前终止查询：提前终止查询指的是当遇到一些查询条件会让查询提前完成的语句，优化器会提前判断加快数据的匹配和搜索速度
- 等值传递，如果范围查询可以根据关联表查询优化，那么无需 显式的提示则可以直接搜索数据。
参考资料：
这里汇总了文章中出现的一些参考资料：
- Mysql官方文档哈希关联
- SQL解析在美团的应用 - 美团技术团队 (meituan.com)
- 使用 pt-query-digest 分析 RDS MySQL 慢查询日志 | 亚马逊AWS官方博客 (amazon.com)
写在最后
上半部分以理论为主，下半部分将会着重实战内容进行介绍。
