【MySQL优化(八)】InnoDB查询优化理论与实践(SQL优化)

序

前面已经介绍了表结构优化与索引优化，本文继续分析SQL优化
如果SQL写的很糟糕，即使表结构再合理，索引再适合也无法实现高性能

SQL优化的本质

将一次查询看做是一个任务，本质是优化其中的子任务，要么提高子任务执行速度，要么减少子任务运行次数，要么消除一些子任务

查询优化排查思路

优化数据访问

1. 检查是否查询了不需要的数据，导致访问的过多的行或者列

1. 查询不需要的记录

   例如查询了大量结果，只获取了前N行数据后关闭结果集，丢弃了大部分数据，实际上服务器、客户端已经返回接收了全部的数据。加上Limit条件即可解决该问题

2. 查询了多余的列

   1. 联合查询或者单表查询时执行select *，大多数时候都应该避免select *，查询多余的列没有意义，并且无法使用覆盖索引
   2. 除非是考虑数据的重用行，select * 后缓存数据，这样可以让多个不同的查询都使用该缓存

3. 查询重复的数据

   1. 在同一个请求中，重复查询相同的数据。有时候开发人员为了复用方法，在同一个请求处理中，重复调用相同的方法获取数据，导致重复查询，例如根据用户ID用户用户信息
   2. 在多个相同的请求中，重复查询相同的数据。此时应该考虑加入数据缓存

2. 检查MySQL服务器是否在分析大量超过需要的数据行

检查查询为了返回结果是否扫描了过多的数据，衡量查询开销的三个指标如下：

1. 响应时间
2. 扫描行数
3. 返回的行数

这三个指标都会记录到MySQL慢查询日志中，所以检查慢查询日志是找出扫描行数过多的查询的好办法。

通过合适的where条件减少扫描行数，MySQL应用where条件的三种方式，从好到坏依次是：

1. 完全覆盖索引

   where条件和索引完全匹配，在索引中使用where条件来过滤不匹配的记录。完全在存储引擎层完成（Explain Extra 显示 Using index）

2. 索引下推

   where条件和索引部分匹配，尝试只使用二级索引中的列进行where条件判断，满足才回表查询整行数据，进行其他where条件判断，减少回表次数。部分在存储引擎层完成，部分在服务器层完成（Explain Extra 显示 Using index condition）

3. 从数据表中返回数据，然后过滤不满足条件的行。完全在服务器层完成。（Explain Extra 显示 Using Where）

3. 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询

切分为多个小查询，在应用中做关联，好处如下

1. 可以更好的利用缓存，小表的缓存结果更容易被其他查询使用到，可以重复利用
2. 减少锁竞争
3. 应用层的资源相对来说更容易水平扩展，从而更容易做到高性能和可伸缩
4. 查询本身效率也可能提升

各种查询语句优化解析

分页查询

大多数时候业务系统实现的分页功能SQL如下

select * from table limit 10000, 10

看似只查询了10条记录，实际上这条SQL先读取10010条记录，然后抛弃掉前面10000条记录，当这个数字继续增大时，抛弃掉的数据就越多，效率非常低

优化方案

按照主键排序时

不带排序条件时，默认按照主键升序排列

select * from table where id > 10000 limit 10

按照非主键排序时

通过延迟关联的方式

1. 先给需要排序的非主键列创建二级索引
2. 获取分页记录的主键
3. 再关联查询对应的行信息

SELECT
	* 
FROM
	TABLE t
	INNER JOIN (
		SELECT id FROM TABLE ORDER BY NAME LIMIT 10000, 10
	) tid ON t.id = tid.id

联合查询

先说结论，对于关联sql的优化：

1. 关联字段加索引，让MySQL做join操作时尽量选择NLJ算法
2. 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

对于小表定义的明确：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

关于优化联合查询的注意点：

1. 只需要在第二张表上键索引
2. 确保 Group By 和 Order By 只涉及一个表上的列
3. 分解多表（三表以上）关联查询（具体见上一章【InnoDb索引优化与索引规约 】索引规约中关于优化联合查询的部分）

以下分析原因

MySQL的表关联常见有两种算法：

• Nested-Loop Join 算法
• Block Nested-Loop Join 算法

先解释下驱动表概念，在联合查询中，MySQL先查询的第一张表叫做驱动表，后关联查询的表较被驱动表

嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集

explain select * from userinfo inner join hobby on userinfo.id = hobby.user_id

上述结果表名：

1. hobby是驱动表，userinfo是被驱动表（id相同的按从上到下顺序执行sql），优化器一般会优先选择小表做驱动表，所以使用inner join时，排在前面的不一定是驱动表
2. 使用left join时，左表示驱动表，right join时，右表是驱动表
3. 使用了NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ

NLJ执行流程：

1. 从 hobby 表（where条件过滤后的结果集）读取一行数据
2. 从第1步的数据中，取出关联字段user_id，到userinfo表查找
3. 取出 userinfo 表中满足条件的行，跟hobby中获取到的结果合并，作为结果返回给客户端
4. 重复第3步

整个过程会读取hobby表321行，然后遍历每行数据中的user_id字段的值，到userinfo表中的扫描对应的行（由于关联的是userinfo的主键索引id字段，所以每次只会扫描一行完整记录），总共扫描321次，整个扫描过程是 321 + 321 = 642 次

基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

如果被驱动表的关联字段没有索引，MySQL 会选择使用 Block Nested-Loop Join 算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

explain select * from userinfo inner join hobby on userinfo.username = hobby.username

Extra 中的 Using join buffer（Block Nested Loop）说明该关联查询使用的是 BNL 算法

BNL执行流程：

1. 把 hobby 中的所有数据放入到 join buffer 中
2. 表 uerinfo 中每一行取出来，跟 join buffer 中的数据做对比
3. 返回满足 join 条件的数据

整个过程对表 hobby 和 userinfo 都做了一次全表扫描，因此扫描的总行数为 100（hobby表） + 10000（userinfo表） = 10100。并且 join_buffer 里的数据是无序的，因此对表 userinfo 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000 = 100 万次

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果一次放不下表 hobby 表的所有数据话，就分段放。

比如 hobby 表有1000行记录，join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 userinfo 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 hobby 表剩余200行记录，再次从 userinfo 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 userinfo 表。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢?
如果使用NLJ算法，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。

因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有 索引的情况下 NLJ 算法比 BNL算法性能更高。

ORDER BY 排序优化

当不指定ORDER BY条件时，默认按照主键升序排列

MySQL支持两种排序方式 filesort 和 index
当Explain结果的Extra列为Using Index时表示MySQL扫描索引完成排序，Using filesort时表示MySQL使用文件排序

索引排序

1. 只有当联合索引的顺序和Where + Order By子句顺序相同时，并且所有列的排序方向相同（都正序或都倒序），才可以使用联合索引完成查询和排序，即要求满足联合索引的最左前缀原则
2. 当执行联合查询时，还必须满足只有当Order By子句引用的字段全部为第一个表时，才能使用索引排序

总结下不能使用索引排序的CASE，其实和不能完全使用联合索引所有列一个原理：

假设存在联合索引 idx_date_age_height(login_date, age, height)

1. 使用了两种不同的排序方向

... WHERE login_date = '2022-04-05' Order by age ASC, height DESC

2. 不满足最左前缀原则

... WHERE login_date = '2022-04-05' Order by weight ASC, height ASC

3. 第一列是范围查询，所以MySQL无法使用索引的其余部分

... WHERE login_date > '2022-04-05' Order by age ASC, height ASC

4. IN查询 对于排序来说也是范围查询，不能使用索引的其余部分

... WHERE login_date = '2022-04-05' AND age IN (18,24) Order by height ASC

文件排序（filesort）

filesort的两种排序方式：

1. 单路排序：
   是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序
2. 双路排序：
   是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段

区别是单路排序一次把所有数据读取到内存，所以需要更大的内存，但只会执行一次I/O，性能更快。双路排序需要两次I/O，但第一次只需要读取主键和排序列到内存，相对节约内存

filesort 使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块
当排序记录太多 sort_buffer_size 不够用时，MySQL会使用临时文件来存放各个分块，然后各个分块排序后再多次合并分块最终全局完成排序

MySQL通过比较系统变量max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用哪种排序模式

1. 如果字段的总长度小于max_length_for_sort_data，那么使用单路排序模式
2. 如果字段的总长度大于max_length_for_sort_data，那么使用双路排序模式

实际应用中，如果服务器资源充足，可以适当增大max_length_for_sort_data，尽可能多使用单路排序，提高性能，反之降低max_length_for_sort_data，使用双路排序，通常情况下可以不做调整，保持默认即可

GROUP BY

GROUP BY在利用索引的方面和ORDER BY几乎相同，都需要满足最左侧前缀
下面说下需要注意的点

1. 当无法使用索引时，GROUP BY使用两种策略来完成，使用临时表或者文件排序来分组
2. 不要查询非分组列，当索引改变或者优化器选择不同的优化策略时都可能导致结果不一样。建议将MySQL的SQL_MODE设置为ONLY_FULL_GROUP_BY，这样MYSQL会对该类查询直接返回一个错误
3. 如果没有通过ORDER BY子句显示指定排序列，结果集会自动按照分组的字段进行排序。如果不关心结果集的排序，而这种默认排序又导致需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序

子查询

1. 尽量避免子查询，必要时使用联合查询替代
2. 但不是所有子查询都需要改为联合查询，具体情况需要进行基准测试来查看结果

IN查询

先将IN列表中的数据排序，然后用二分查找的方式来确定列表中的值是否满足条件，复杂度是O(log n)而非O(n)

IN 和 Exists 优化

原则：小表驱动大表，即小的数据集驱动大的数据集

IN
当B表的数据集小于A表的数据集时，IN优于Exists

select * from A where id in (select id from B)
# 等价于
	for (select id from B) {
		select * from A where A.id = B.id
    }

Exists
当A表的数据集小于B表的数据集时，Exists优于IN

将主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定主查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id)
# 等价于
	for (select * from A) {
		select * from B where B.id = A.id
	}

1. EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会 忽略SELECT清单,因此没有区别
2. EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3. EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

COUNT

count(1)，count(*)，count(字段)，count(id) 区别（假设id为主键）

1. count(列)，例如上述的 count(字段) 不会统计值为NULL值的数据行，其余会统计所有行
2. MySQL对count(*)专门做了优化，不会取出所有行，会当做统计行处理，效率很高

效率对比
3. 字段有二级索引时

count(*) ≈ count(1) > count(字段) > count(id) 

分析：字段有索引时，count(字段)统计走二级索引，二级索引存储数据比主键索引少，更小，所以 count(字段) > count(主键 id) 

4. 字段无二级索引时

   count(*) ≈ count(1) > count(主键 id) > count(字段)

   分析：count(主键 id) 可以走主键索引，所以 count(id) > count(字段)

优化方案

1. 近似值

   如果只需要统计近似值，可以使用如下SQL替代

show table status like 'userinfo'

该表实际数量为10000，当然也可能统计为比实际数量小的值

2. 汇总表

   使用单独的汇总表某个字段统计数量，每次写操作时维护该数量，统计时直接查该字段（当写多读少不适用该方案）

3. 缓存中间件

   可以使用缓存中间件来统计数量，例如Redis

MIN

MySQL对于MIN()和MAX()的优化做的并不好

例如
表: hobby 有20万数据，sort 字段有一个单列索引 idx_sort

select max(sort) from hobby where username = 'Rita'

这个查询MySQL会做一次全表扫描，因为username字段没有索引

执行查询，耗时61毫秒

下面对该查询做一次优化

explain select sort from hobby where username = 'Rita' order by sort desc limit 1

改为按照sort倒序排列，取第一条即最大值，此时由于 username = ‘Rita’ 是一个常量，且 idx_sort 索引是按顺序排列的，所以可以直接查询 idx_sort所以获得结果，几乎不耗时

MIN() 同理

UNION 与 UNION ALL

尽量避免 UNION，因为UNIO

LIMIT

能使用limit

附录

SQL查询状态

通过show processlist可以查看当前连接的执行状态，重点关注Command列，其可能的值及含义如下：

Sleep

线程正在等待客户端发送新的请求

Query

线程正在执行查询或者正在将结果发送给客户端

Locked

在MySQL服务器层，该线程正在等待表锁。在存储引擎层实现的锁，例如Innodb的行锁，并不会体现在线程状态中。

Analyzing and statistics

线程正在收集存储引擎的统计信息，并生成查询的执行计划。

Copying to tmp table [on disk]

线程正在执行查询，并且将其结果都复制到一个临时表中，这种状态一般要么是在做Group By操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有"on disk"标记，那表示Mysql正在将一个内存临时表放到磁盘是。

Sorting result

线程正在对结果集进行排序

Sending data

这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

系列文章

上一篇：【MySQL优化(七)】MySQL Explain详解
下一篇：【MySQL优化(九)】MySQL锁机制