【MySQL调优】查询优化

在之前的文章中我们讨论了优化数据库结构与优化数据库索引，在二者的基础上，我们还需要足够好的查询来让上两步的优化发挥其真正的价值。
查询优化、索引优化与数据库结构优化齐头并进，一个不落，才能保证系统的高效性。

查询变差的原因：

查询是由多个子任务所实现的，需要优化查询，要么就是消除其中一些不必要的子任务，要么就是减少子任务的执行次数，要么就是让子任务执行的更快。
查询的生命周期：客户端 -> 服务器 -> 解析 -> 执行计划 -> 执行 -> 返回给服务器 -> 返回给客户端

优化数据访问

两步分析法：a. 不要检索大量不需要的数据，b.MySQL服务层是否分析了大量不需要的数据行
1.不要向数据库请求不需要的数据
可能会选择在应用层进行筛选，会给MySQL服务器带来额外负担，增加网络开销。
多表关联时只需要返回需要的列，杜绝使用SELECT * FROM
重复查询相同的数据也是很危险的，尤其是数据库没有缓存的场景下

2.MySQL是否扫描了过多的数据
通过观察：响应时间、扫描行数、返回行数 可以分析MySQL是否扫描了过多的行
响应时间 = 等待时间 + 服务时间
扫描行数与返回行数如果差距过大，说明筛选条件不能很好的被数据库所支持
扫描的行数与访问类型：可以通过EXPLAIN type来查看，全表扫描，索引扫描，范围扫描，唯一索引查询，常数引用。速度由慢到快，扫描行数由多变少。

WHERE的三种场景:
1. 索引中使用where条件筛选不匹配记录，直接在存储引擎层就能够完成；
2. 使用索引覆盖扫描，直接从索引中过滤不需要的记录并且返回命中的结果，不需要回表查询其他数据，在Extra列中会出现: using index
3. 从数据表返回数据，在MySQL服务器层进行判断。在Extra列会出现：Using Where的字段。这种情况一定会进行了回表操作。

如果存在扫描行数远远大于返回行数，可以尝试：
1. 使用覆盖索引，无需回表操作
2. 改变数据表结构，增加汇总表
3. 重写复杂查询

重构查询的方法：

修改应用逻辑，通过不同的sql达到相同的效果。

一个复杂的查询还是多个简单的查询

MySQL连接创建与断开都很轻量级，返回一个小的查询可以很高效。但是如果是把本来就很小的查询再去进行拆分，就很傻

切分查询

分而治之，把可能会一次性返回大量数据，或者针对大量数据进行删改操作的查询分批次进行

分解关联查询

通过上层应用程序实现关联，而非直接使用数据，具有以下优势：
缓存更高效
减少锁的竞争
应用层关联可以更容易进行数据库拆分，实现高性能和可扩展
使用IN代替关联查询，维护条件有序性可以提升查询本身的效率
减少冗余记录的查询
自己通过计算逻辑实现哈希关联（增加新的哈希列）

查询执行的基础

客户发起查询时，MySQL做了什么：
1. 客户端发送一条查询到服务器
2. 如果命中了服务器缓存，直接返回，否则进入下一步
3. 服务器进行SQL解析、预处理，再由优化器生成执行计划
4. MySQL根据优化器生成的执行计划，调用存储引擎的Api来执行查询
5. 将结果返回给客户端

查询状态： SHOW FULL PROCESSLIST
Sleep：等待客户端发送新的请求
Query：正在执行查询，或者正在将查询结果发送回客户端
Locked：MySQL服务器层该线程在等待表锁。InnoDB的行锁并不会在这里体现
Analyzing and statistics：线程正在收集统计信息并生成查询的执行计划
Copying to tmp table [on disk]：线程执行查询并将结果复制到一个临时表中，可能是发生了group by，文件排序，Union操作中的一种。如果出现了on disk，代表需要将结果复制到磁盘上
Sorting result：线程在对结果集进行排序
Sending data：线程在多个状态之间传送数据，或者生成结果集，或者在向客户端传送数据

查询缓存：
如果开启了查询缓存，MySQL会优先对这个缓存进行检查，看看是否命中了之前的查询缓存中的数据。是通过对大小写敏感的哈希查找来实现的。

查询优化处理：（优化器）
将SQL转换为执行计划，MySQL通过执行计划与存储引擎进行交互。包含了多个子阶段：解析SQL、预处理、优化SQL的执行计划。这个过程中的任意错误都会终止查询。

语法解析与预处理

MySQL通过关键字将SQL进行解析，生成一颗对应的解析树，MySQL解析器将会使用MySQL语法规则验证和解析查询。

查询优化器

MySQL使用基于成本的优化器，尝试预测一个查询使用某种执行计划的成本，选择其中最小的一个来进行执行。通过SHOW STATUS LIKE ‘Last_query_cost’;可以查询上一次当前查询的成本。
优化器不会考虑任何缓存，它假设读取任何数据都需要一次磁盘I/O操作。
可能会导致MySQL优化器选择错误的执行计划的原因：
统计信息不准确。
执行计划中的成本估算不等同于实际执行的成本。（MySQL无法区分哪些数据在内存中，那些数据在磁盘中）
MySQL最优可能和想象中的最优不一样。
MySQL从不考虑其他并发执行的查询情况
MySQL并不会任何时候都基于成本进行优化
MySQL不会考虑诸如用户自定义函数一类的成本
如果执行计划过多，MySQL不会进行完整的执行计划消耗估算，因为这可能会得不偿失

优化器十分复杂，可以完成静态优化与动态优化两个行为，静态优化只会执行一次，而动态优化随着上下文的转换，每次查询时都需要重新评估，可以看成是一种运行时优化。
MySQL常见优化类型：
1. 重新定义关联表的顺序
2. 将外连接转换为内连接
3. 使用等价变换规则
4. 优化COUNT()、MIN()、MAX()
5. 预估并转化为常数表达式
6. 覆盖索引扫描
7. 子查询优化
8. 提前终止查询，比如LIMIT的场景
9. 等值传播
10. 列表IN()比较
MySQL的IN()操作复杂度为O(nlogn)，而OR操作为O(n)，MySQL会先对IN中的内容进行排序，然后通过二分查找进行查找。

数据表和索引的统计信息
统计信息是由存储引擎来实现的。所以在每次MySQL优化之前，都会向存储引擎询问诸如：每个表或者索引有多少个页面、每个表的每个索引基数是多少、数据行和索引的长度、索引的分布信息等。

MySQL的关联查询
UNION查询，MySQL先将一些列的单个查询结果放在临时表，然后重新读取临时表数据完成UNION查询。
MySQL对任何的关联执行都是通过嵌套循环的操作来实现的。可以将这种查询生动的用泳道图来进行描述。
需要注意的是，这个派生表，也就是临时表是没有索引的。

执行计划
与很多其他的关系型数据库不同，MySQL不会生成字节码的查询计划。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行了EXPLAIN EXTENDED之后，再去执行SHOW WARNINGS，就可以看到重构出的全部查询。
MySQL的执行计划是一颗左侧深度优先的树形结构。

关联查询优化器
MySQL优化器最重要的一个部分就是关联查询优化，他决定了多个表关联时候的顺序。
通过STRAIGHT_JOIN关键字可以强制指定关联顺序，但往往并没有关联优化器给我们优化之后的结果好。通常关联优化器通过调整关联顺序，可以让查询进行更少的嵌套循环和回溯操作。
通过Last_query_cost的值，可以看到不同的关联顺序查询后带来的预估成本。
当需要关联的表超过optimizer_search_depth之后，关联优化器就会工作在“贪婪模式”

排序优化
无论如何排序都是一个很耗费性能的工作，所以应当尽量避免排序，或者尽量避免对大量数据进行排序。
对于排序，MySQL统一称之为filesort，只不过有时会发生在内存中，更差的情况下会发生在磁盘中。
通过EXPLAIN可以看到MySQL在关联查询过程中的两种排序模式，一种是所有ORDER BY的所有列都来自第一个关联表，那么MySQL在处理第一个表的时候就会完成排序，然后进行后续的关联操作，这时候Extra字段就会有"Using filesort"。除此之外的任何一种情况，MySQL都会先将关联结果放在一个表里面，然后在所有的关联都结束之后，进行文件排序。这时候在Extra字段就会出现"Using temporary; Using filesort"。如果查询中出现了LIMIT字段，LIMIT也会在排序之后再进行限制，所以即使返回很少的数据，可能也会占用大量的内存空间。

查询执行引擎：
查询大部分情况下就是按照优化器给出的顺序调用存储引擎的"handler API"。这种简单的接口模式让MySQL的存储引擎插件式架构成为可能，同时也会为优化器带来一定的限制。

返回数据给客户端：
即使不需要返回数据，MySQL也会把影响的行数返回给客户端。

MySQL查询优化器的局限性
关联子查询
MySQL针对关联子查询的优化非常糟糕，尤其是Where条件中包含In()的子查询。

如何用好关联子查询
可以通过Not Exists提前中断查询，也就是MySQL的提前终止算法。
通过压力测试可以看到Not Exists子查询的性能是优于Left outer join的。
这个说明了不要通过自己猜想来进行验证，而是通过实验，benchmard来验证自己的猜想。

UNION的限制
如果是 (SELECT table1) UNION ALL (SELECT table2) LIMIT 20的操作，会先从两个表中查出所有的数据，然后执行UNION ALL然后再去LIMIT筛选，性能很差，建议根据order by条件，分别查处需要的数据然后 UNION ALL

索引下推
可以直接把where条件中的内容下推到索引中，即使索引没有使用这个字段，这样可以减少回表次数

并行执行
MySQL无法通过多核处理器来完成并行查询

哈希关联
InnoDB存储引擎不支持哈希索引，通过人工进行哈希字段的方式可以帮助MySQL实现哈希关联，有助于加快数据查询速度

松散索引扫描
MySQL在扫描过程中一定要指明一个起点和一个终点。

最大值与最小值优化
MySQL对MIN()、MAX()函数优化的很差

同一个表上进行查询与更新
必须通过子查询才能实现

查询优化器提示（hint）
通过提示可以指引优化器完成操作行为，提示的用法可以参考MySQL官方手册。

HIGH_PRIORITY和LOW_PRIORITY
控制查询对于表的访问顺序，仅针对表级锁存储引擎有效，在InnoDB存储引擎使用会造成问题。

DELAYED
针对INSERT和REPLACE有效，代表操作可以延迟写入，会影响LAST_INSERT_ID()的工作行为

STRAIGHT_JOIN
可以放置在SELECT语句和SELECT关键字之后，也可以放置在任何两个关联表的名字之间。用于固定数据表之间的关联顺序。

SQL_SMALL_RESULT和SQL_BIG_RESULT
只针对select语句有效，告诉优化器通过group by或者distince查询的时候，使用的是内存临时表还是磁盘临时表进行排序

SQL_BUFFER_RESULT
告诉优化器将查询结果放入一个临时表，然后尽可能快的释放表锁。

SQL_CAHCE和SQL_NO_CACHE
告诉MySQL的查询结果是否需要放入查询缓存中

SQL_CALC_FOUND_ROWS
加上会去计算limit之后具体的总行数

FOR UPDATE和LOCK IN SHARE MODE
控制SELECT语句的锁机制，只针对行锁级存储引擎有效。唯一支持这两个提示的存储引擎就是InnoDB。

USE INDEX、IGNORE INDEX和FORCE INDEX
告诉优化器使用或者不使用哪些索引来进行记录查询。FORCE INDEX告诉优化器全表扫描的成本会远远高于索引扫描。

Optimizer_search_depth
控制优化器穷举执行计划的限度，如果查询长时间处在"statistics"状态，就可以考虑调低这个参数

Optimizer_prune_level
默认打开，优化器根据扫描行数决定是否跳过某些执行计划

Optimizer_switch
开关优化器特性的标志位

优化特定类型的查询

优化COUNT()
Count()是聚合函数，可以统计所有count字段非null的行，所以没有特殊情况需要统计所有行数的话，建议优先使用count(*)。
MyISAM只有在查找没有where的count才会特别快，否则还是会通过where条件进行全表扫描和统计
某些场景下可能只是需要一个近似值而非那么准确的值，这时候直接使用explain中的统计估算结果也是一个不错的选择
通过修改应用层逻辑可以避免针对大范围数据进行count扫描操作。
时刻记住：快速、准确与实现简单，往往只能实现其中的两个。

优化关联查询
1. 确保ON或者USING的字句中的列上面都有索引
2. 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列
3. 当升级MySQL的时候需要注意：关联语法，运算符优先级等其他可能会在不同MySQL版本中发生变化的地方

优化子查询
尽可能用关联去替代掉子查询

优化Group By和Distinct
在无法使用索引时，MySQL会通过临时表或者文件排序来完成分组操作。
建议使用ONLY_FULL_GROUP_BY的SQL_MODE来对数据库的分组策略进行约束。

优化LIMIT分页
如果在进行大范围数据分页的后几页查询的时候，可能会导致大量的回表，回表的过程中会造成大量的磁盘I/O和内存操作，比如：
Select film_id, description from sakila.film order by title limit 50, 5;
可以通过"延迟关联"来解决此类问题：
Select film.film_id, film.description from sakila.film INNER JOIN (SELECT film_id from sakila.film ORDER BY title LIMIT 50, 5) AS lim USING(film_id);
延迟关联可以大大提升查询效率，减少回表次数。

优化SQL_CALC_FOUND_ROWS
会扫描所有的行，统计出一个结果。

优化UNION查询
MySQL总是通过创建并填充临时表的方式来完成UNION查询，可以手工将where, limit, order by等子句下推到UNION的各个子句中完成。

静态查询分析
通过pt-query-advisor能够解析查询日志，分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议。

使用用户自定义变量
用户自定义变量的生命周期是一次连接，使用连接池可能会造成问题。
作用：
优化排名语句，实现行号功能
避免重复查询刚刚更新的数据
统计更新和插入的数量
确定取值的顺序
编写偷懒的UNION