性能优化：MySQL使用优化（2）-CSDN博客

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

select * from T where k between 3 and 5

在该示例中，查询步骤如下：

在 k 索引树上找到 k=3 的记录，取得 ID = 300；
再到 ID 索引树查到 ID=300 对应的 R3；
在 k 索引树取下一个值 k=5，取得 ID=500；
再回到 ID 索引树查到 ID=500 对应的 R4；
在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。索引包含所有需要查询的字段的值，称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

2、索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出且默认开启，用于优化查询。

在前文中，我们介绍最左前缀原则时有一段补充，最左前缀原则的限制里，是不允许使用范围查询的，即如果对第一个索引使用了例如<、>或者like 以%结尾都会导致该列后续的索引失效。如下图中第二例，第三列address就不会生效。

第二例中的查询过程如下：

利用联合索引中的name、status字段找出复合这个条件的所有索引树中的叶子节点。
返回索引节点存储的值给Server层，然后去逐一做回表扫描。
在Server层中根据address="北京市"这个条件逐条判断，最终筛选到满足条件的数据。

可以看出这里之所以没办法利用到第三列索引时因为引擎层未对数据进行判断，而是把根据前两列索引查询出来的结果交给了server层去做判断。所以第三列没有用到索引。

为了优化这一问题，MySQL引入了索引下推，也就是将Server层筛选数据的工作，下推到引擎层处理。

利用联合索引中的name、status字段找出复合这个条件的所有索引树中的叶子节点。
根据address="北京市"这个条件在索引节点中逐个判断，从而得到满足条件的叶子节点。
最终将这些数据返回给Server层，然后聚簇索引中回表拿数据。

相较于没有索引下推之前，因为第三列索引没有办法被利用起来，因此回表扫描可能带有较多不满足条件的数据，而使用了索引下推后，由于在返回server层前就进行了数据过滤，有效减少了回表的次数。

使用了索引下推的语句，在使用explain分析时extra列会提示Using index condition。

3、MRR(Multi-Range Read)机制

Multi-Range Read简称为MRR机制，这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施，同样是默认开启的。

在二级索引上使用范围扫描读取行可能会导致大量的随机磁盘访问。使用Multi-Range Read新特性，mysql可以减少对磁盘的随机读的次数：首先，mysql只是扫描索引，收集相关行的keys；然后，将收集到的keys进行排序；最后通过有序的主键去访问基表。

比如我们使用这样一条sql进行查找（假设此时有单列索引age）

select * from test where age between 18 and 60;

这条SQL的执行流程是什么样的呢？

先在age字段的索引上找到等于18的节点，然后拿着ID去回表得到所有age=18数据。
再次回到age索引，继续找到所有等于19的节点，继续回表得到age=19的数据。
再次回到age索引，继续找到所有等于20的节点......
周而复始，不断重复这个过程，直到将18~60岁的所有人员信息全部拿到为止。

假设此时age在18~20的表数据，位于磁盘空间的page_01页上，而age在21~25的数据，位于磁盘空间的page_02页上，而age在25~30的数据，又位于磁盘空间的page_01页上。此时回表查询时就会导致在page_01、page_02两页空间上来回切换，但age在18~20、25~30的数据完全可以合并，然后读一次page_01就可以了，既能减少IO次数，同时还避免了离散IO。

而MRR机制就主要是解决这个问题的，针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率。

MRR机制中，对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据。

4、Index Skip Scan索引跳跃式扫描

最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询，但实际上这条规则也并不是100%遵循的。因为在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

比如此时有一张test表，有复合索引（A,B,C），如果在5.6、5.7的版本中，下面这条sql自然是无法命中索引的

select * from test where B='XXX' and C='XXX';

按理来说，这条SQL既不符合最左前缀原则，也不具备使用索引覆盖的条件，因此绝对是不会走联合索引查询的，但思考一个问题，这条SQL中都已经使用了联合索引中的两个字段，结果还不能使用索引，这似乎有点亏啊对不？因此MySQL8.x推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了SQL，比如上述这条SQL则会重构成如下情况：

select * from test where B='XXX' and C='XXX' 
union all 
select * from test where B='XXX' and C='XXX' and a='XXX' 
union all 
select * from test where B='XXX' and C='XXX'and a='YYY' 
...

其实也就是MySQL优化器会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查一遍，相当于自动补全了被跳跃了的索引列。

但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发。

5、Filesort的优化

对于Filesort ， MySQL 有两种排序算法：两次扫描法和一次扫描法。比如有如下sql

select * from emp where age=1 order by salary;

5.1、两次扫描算法

MySQL4.1 之前，使用该方式排序。

首先根据where条件，过滤得到相应的满足age=1的salary，取出排序字段salary和对应的行指针信息(用于回表)，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。

完成排序之后，再根据行指针回表读取所有字段，而次该操作可能会导致大量随机I/O操作，是我们需要改进的地方。

这就是所谓的两次扫描，第一次扫描，我们拿到的只是排序字段，然后在sort buffer排好序；第二次扫描，才去回表读取所有字段，最终返回。分成两次主要是为了避免空间不足的情况。

5.2、一次扫描算法

一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高，典型的以空间换时间的思想。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和 Query语句取出的字段总大小，来判定使用那种排序算法，如果max_length_for_sort_data 更大，那么使用一次扫描算法；否则使用两次扫描算法。

二、SQL使用优化

1、order by

在之前的文章中，我们介绍了额外排序的提示Using filesort，即按照索引读出的数据顺序不满足我们的要求，于是只能将数据读出后再进行排序。这个提示一般出现在以下几种情况（假设有复合索引create index idx_emp_age_salary on emp(age,salary)）：

order by的字段不是索引
order by 字段是索引字段，但是 select 中没有使用覆盖索引
order by 中同时存在 ASC 升序排序和 DESC 降序排序

order by中用到的是复合索引，但没有保持复合索引中字段的先后顺序（即违背了最左前缀原则）

优化方案

对于Filesort的排序方式上文已经解释了，这里介绍下我们能够调整的配置。

增大前者 max_length_for_sort_data：可以适当 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率，这是典型的空间换时间的思想。

减小后者 Query语句取出的字段总大小：如果内存实在不够富裕的话，我们可以减少查询的字段，避免select *。

提高 sort_buffer_size ：由上文可知，通过增大该参数，可以让 MySQL 尽量减少在排序过程中对须要排序的数据进行分段，避免需要使用到临时表 temporary table 来存储排序结果，再把多次的排序结果串联起来。

不过MySQL无法查看它用了哪个算法。如果增加了max_Length_for_sort_data变量的值，磁盘使用率上升了，CPU使用率下降了，并且Sort_merge_passes状态变量相对于修改之前开始很快地上升，也许是MySQL强制让很多的排序使用了一次扫描算法。

2、group by

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

2.1、添加索引

当无索引时：using temporary；using filesort

创建索引create index idx_emp_age_salary on emp(age,salary)；

2.2、加上order by null 禁止排序

如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行order by null 禁止排序。

3、子查询与连接

3.1、子查询优化

如果使用子查询的话，建议采用小表驱动大表，也就是说用小表的数据集驱动大表的数据集。

假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。这时如果想查一下，所有有效的用户下过的订单列表。

可以使用in关键字实现：

select * from order
where user_id in (select id from user where status=1)

也可以使用exists关键字实现：

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。

而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。

总结一下：

in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。

不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

3.2、连接代替子查询

mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询和连接查询。

子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。子查询语句的优点是简单，结构化，如果涉及的表数量不多的话效果不错。

但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。这时可以改成连接查询。

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

3.3、连接的注意点

join使用最多的是left join和inner join

left join：求两个表的交集外加左表剩下的数据。
inner join：求两个表交集的数据。

如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

select o.id,o.code,u.name 
from order o 
left join user u on o.user_id = u.id
where u.status=1;

要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

4、or

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。

当然，索引也不是随便加的，也要判断是否应该加，对于不应该加的索引列，我们可以使用union来代替or。

5、limit

当我们在做分页时，可能使用这样的语句，

select id,name,age 
from user limit 1000000,20;

mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。

我们可以在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。