MySQL 查询优化相关

如何根治MySQL慢查询

导致MySQL慢查询的12个常见原因，以及对应的解决方法。

一、SQL没加索引

二、SQL索引没生效

明明加了索引了，但是索引却不生效。在哪些场景，索引会不生效呢？主要有以下十大经典场景：

1、隐式的类型转换，索引失效

如果SQL种WHERE条件类型不匹配时，MySQL会做隐式的类型转换，导致索引失效；

2、查询条件包含or，可能导致索引失效

对于where name = '张三' or age = '99' ，如果name(有索引)，age(无索引) 假设它走如果name的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就完事。Mysql优化器出于效率与成本考虑，遇到or条件，让索引失效，看起来也合情合理嘛。

注意：如果or条件的列都加了索引，索引可能会走也可能不走，大家可以自己试一试哈。但是平时大家使用的时候，还是要注意一下这个or，学会用explain分析。遇到不走索引的时候，考虑拆开两条SQL。

3、like通配符可能导致索引失效

并不是用了like通配符，索引一定会失效，而是like查询是以%开头，才会导致索引失效。

备注：既然like查询以%开头，会导致索引失效。我们如何优化呢？(1、使用覆盖索引，2、把%放后面)

4、查询条件不满足联合索引的最左匹配原则

MySQl建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（userId,name,age）的联合索引，相当于建立了(userId)、(userI,name)、(userId,name,age)三个索引。

假设where name = '张三';此时索引是不生效的。因为查询条件列name不是联合索引中的第一个列，索引不生效； 在联合索引中，查询条件满足最左匹配原则时，索引才正常生效。

5、在索引列上使用mysql的内置函数

login_time加了索引，但是因为使用了mysql的内置函数Date_ADD()，索引直接GG，

一般这种情况怎么优化呢？可以把内置函数的逻辑转移到右边，如下：

6、对索引进行列运算（如，+、-、*、/）,索引不生效

虽然age加了索引，但是因为它进行运算，索引也是直接GG。所以不可以对索引列进行运算，可以在代码处理好，再传参进去。

7、索引字段上使用（！= 或者 < >），索引可能失效

虽然age加了索引，但是使用了！= 或者< >，not in这些时，索引如同虚设。

其实这个也是跟mySQL优化器有关，说白了还是考虑到 '成本'，对于使用二级索引进行查询来说，成本组成主要有两个方面：

1、读取二级索引记录的成本 2、将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。

很显然，要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本。

所以，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，它觉得不划算，不如直接不走索引。平时我们用！= 或者< >，not in的时候，留点心眼哈。

8、索引字段上使用is null， is not null，索引可能失效

原理其实同上

9、order by导致索引失效
- 1、如果order by语句中没有加where或limit关键字，该sql语句将不会走索引。
- 2、对不同的索引做order by
- 3、不满足最左匹配原则
- 4、不同的排序
10、优化器选错了索引

MySQL 中一张表是可以支持多个索引的。你写SQL语句的时候，没有主动指定使用哪个索引的话，用哪个索引是由MySQL来确定的。

我们日常开发中，不断地删除历史数据和新增数据的场景，有可能会导致MySQL选错索引。那么有哪些解决方案呢？

使用force index 强行选择某个索引；
修改你的SQl，引导它使用我们期望的索引；
优化你的业务逻辑；
优化你的索引，新建一个更合适的索引，或者删除误用的索引。

三、limit深分页问题

limit深分页问题，会导致慢查询，应该大家都司空见惯了吧。

1、limit深分页为什么会变慢

你知道以下SQL，执行过程是怎样的嘛？

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;

#主键 id     普通索引 create_time  name

这个SQL的执行流程：

通过普通二级索引树idx_create_time，过滤create_time条件，找到满足条件的主键id。
通过主键id，回到id主键索引树，找到满足记录的行，然后取出需要展示的列（回表过程）。
扫描满足条件的100010行，然后扔掉前100000行，返回。

limit深分页，导致SQL变慢原因有两个：

limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。
limit 100000,10 扫描更多的行数，也意味着回表更多的次数。
2、如何优化深分页问题

我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。

1、标签记录法

标签记录法**，标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描**。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到。

假设上一次记录到100000，则SQL可以修改为：

select id,name,balance FROM account where id > 100000 limit 10;

这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。

2、延迟关联法

延迟关联法，就是把条件转移到主键索引树，然后减少回表。如下：

select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;

优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。

四、单表数据量太大

1、单表数据量太大为什么会变慢？

一个表的数据量达到好几千万或者上亿时，加索引的效果没那么明显啦。性能之所以会变差，是因为维护索引的B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

2、如何解决单表数据量太大，查询变慢的问题

一般超过千万级别，我们可以考虑分库分表了。

分库分表可能导致的问题：

事务问题
跨库问题
排序问题
分页问题
分布式ID 因此，大家在评估是否分库分表前，先考虑下，是否可以把部分历史数据归档先，如果可以的话，先不要急着分库分表。如果真的要分库分表，综合考虑和评估方案。比如可以考虑垂直、水平分库分表。水平分库分表策略的话，range范围、hash取模、range+hash取模混合等等。

五、join 或者子查询过多

一般来说，不建议使用子查询，可以把子查询改成join来优化。而数据库有个规范约定就是：尽量不要有超过3个以上的表连接。为什么要这么建议呢? 我们来聊聊，join哪些方面可能导致慢查询吧。

MySQL中，join的执行算法，分别是：Index Nested-Loop Join和Block Nested-Loop Join。

Index Nested-Loop Join：这个join算法，跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引。
Block Nested-Loop Join：这种join算法，被驱动表上没有可用的索引,它会先把驱动表的数据读入线程内存join_buffer中，再扫描被驱动表，把被驱动表的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。 join过多的问题：

一方面，过多的表连接，会大大增加SQL复杂度。

另外一方面，如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。如果被驱动表没有可用的索引，join是在join_buffer内存做的，如果匹配的数据量比较小或者join_buffer设置的比较大，速度也不会太慢。

但是，如果join的数据量比较大时，mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的 IO 就不快，还要关联。

一般情况下，如果业务需要的话，关联2~3个表是可以接受的，但是关联的字段需要加索引。

如果需要关联更多的表，建议从代码层面进行拆分，在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成map，然后在业务层进行数据的拼装。

六、in元素过多

如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过500个，如果超过了，建议分组，每次500一组进行哈。

七、数据库在刷脏页

1、什么是脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。一般有更新SQL才可能会导致脏页，我们回忆一下：一条更新语句是如何执行的。

2、一条更新语句是如何执行的？

以下的这个更新SQL，如何执行的呢？

update t set c=c+1 where id=666;

对于这条更新SQL，执行器会先找引擎取id=666这一行。如果这行所在的数据页本来就在内存中的话，就直接返回给执行器。如果不在内存，就去磁盘读入内存，再返回。
执行器拿到引擎给的行数据后，给这一行C的值加一，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，但是此时redo log 是处于prepare状态的哈。
执行器生成这个操作的binlog，并把binlog写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作redo log（重做日志）。平时更新SQL执行得很快，其实是因为它只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。

有些小伙伴可能有疑惑，redo log日志不是在磁盘嘛？那为什么不慢？其实是因为写redo log的过程是顺序写磁盘的。磁盘顺序写会减少寻道等待时间，速度比随机写要快很多的。

3、为什么会出现脏页呢？

更新SQL只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致,就出现脏页。

4、什么时候会刷脏页（flush）？

InnoDB存储引擎的redo log大小是固定，且是环型写入的;

那什么时候会刷脏页？有几种场景：

redo log写满了，要刷脏页。这种情况要尽量避免的。因为出现这种情况时，整个系统就不能再接受更新啦，即所有的更新都必须堵住。内存不够了，需要新的内存页，就要淘汰一些数据页，这时候会刷脏页。 InnoDB 用缓冲池（buffer pool）管理内存,而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

MySQL 认为系统空闲的时候，也会刷一些脏页。 MySQL 正常关闭时，会把内存的脏页都 flush 到磁盘上。

5、为什么刷脏页会导致SQL变慢呢？

redo log写满了，要刷脏页，这时候会导致系统所有的更新堵住，写性能都跌为0了，肯定慢呀。一般要杜绝出现这个情况。

一个查询要淘汰的脏页个数太多，一样会导致查询的响应时间明显变长。

八、order by 文件排序

order by就一定会导致慢查询吗？不是这样的哈，因为order by平时用得多，并且数据量一上来，还是走文件排序的话，很容易有慢SQL的;

1、order by 的 Using filesort文件排序

select name,age,city from staff where city = '武汉' order by age limit 10;

查看explain执行计划的时候，可以看到Extra这一列，有一个Using filesort，它表示用到文件排序。

2、order by文件排序效率为什么较低

order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。

1）rowid排序

rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点。以下这个SQL，使用rowid排序，执行过程是这样：

select name,age,city from staff where city = '武汉' order by age limit 10;

MySQL 为对应的线程初始化sort_buffer，放入需要查询的name、age、city字段；
从索引树idx_city，找到第一个满足 city='武汉'条件的主键 id，也就是图中的id=9；
到主键id索引树拿到id=9的这一行数据，取name、age、city三个字段的值，存到sort_buffer；
从索引树idx_city 拿到下一个记录的主键id，即图中的id=13；
重复步骤 3、4 直到city的值不等于武汉为止；前面5步已经查找到了所有city为武汉的数据，在sort_buffer中，将所有数据根据age进行排序；
按照排序结果取前10行返回给客户端。

2）全字段排序

同样的SQL，如果是走全字段排序是这样的：

select name,age,city from staff where city = '武汉' order by age limit 10;

MySQL 为对应的线程初始化sort_buffer，放入需要查询的name、age、city字段；
从索引树idx_city，找到第一个满足 city='武汉'条件的主键 id，也就是图中的id=9；
到主键id索引树拿到id=9的这一行数据，取name、age、city三个字段的值，存到sort_buffer；
从索引树idx_city 拿到下一个记录的主键id，即图中的id=13；
重复步骤 3、4 直到city的值不等于武汉为止；前面5步已经查找到了所有city为武汉的数据，在sort_buffer中，将所有数据根据age进行排序；
按照排序结果取前10行返回给客户端。

sort_buffer的大小是由一个参数控制的**：sort_buffer_size**。

如果要排序的数据小于sort_buffer_size，排序在sort_buffer内存中完成。
如果要排序的数据大于sort_buffer_size，则借助磁盘文件来进行排序。 *

借助磁盘文件排序的话，效率就更慢一点。因为先把数据放入sort_buffer，当快要满时。会排一下序，然后把sort_buffer中的数据，放到临时磁盘文件，等到所有满足条件数据都查完排完，再用归并算法把磁盘的临时排好序的小文件，合并成一个有序的大文件。

3、如何优化order by的文件排序

order by使用文件排序，效率会低一点。我们怎么优化呢？

因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化order by语句。
我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化。

九、拿不到锁

有时候，我们查询一条很简单的SQL，但是却等待很长的时间，不见结果返回。一般这种时候就是表被锁住了，或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放。

举一个生活的例子哈，你和别人合租了一间房子，这个房子只有一个卫生间的话。假设某一时刻，你们都想去卫生间，但是对方比你早了一点点。那么此时你只能等对方出来后才能进去。

这时候，我们可以用show processlist命令，看看当前语句处于什么状态哈。

十、group by使用临时表

group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。日常开发中，我们使用得比较频繁。如果不注意，很容易产生慢SQL。

1、group by的执行流程

select city ,count(*) as num from staff group by city;

Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表。
Extra 这个字段的Using filesort表示使用了文件排序。

group by是怎么使用到临时表和排序了呢？我们来看下这个SQL的执行流程：

1）创建内存临时表，表里有两个字段city和num；
2）全表扫描staff的记录，依次取出city = 'X'的记录。
- 判断临时表中是否有为 city='X'的行，没有就插入一个记录 (X,1);
- 如果临时表中有city='X'的行，就将X这一行的num值加 1；
3）遍历完成后，再根据字段city做排序，得到结果集返回给客户端。

临时表的排序是怎样的呢？

就是把需要排序的字段，放到sort buffer，排完就返回。在这里注意一点哈，排序分全字段排序和rowid排序。

如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回。
如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。
2、group by可能会慢在哪里？

group by使用不当，很容易就会产生慢SQL 问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。

如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。
如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。
3、如何优化group by呢？

从哪些方向去优化呢？

方向1：既然它默认会排序，我们不给它排是不是就行啦。
方向2：既然临时表是影响group by性能的X因素，我们是不是可以不用临时表？我们一起来想下，执行group by语句为什么需要临时表呢？group by的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦?

可以有这些优化方案：

group by 后面的字段加索引
order by null 不用排序
尽量只使用内存临时表
使用SQL_BIG_RESULT