盘点慢查询原因及优化方法

目录

• 一，前言
• 二，准备
• • type重点看
• 三，慢查询原因和解决
• • 1，sql未加索引
  • 2，索引失效
  • 3，limit深分页问题
  • • （1）limit深分页为什么会慢
    • （2）深分页优化
  • 4，in元素过多
  • 5， join 或者子查询过多
  • 6，order by文件排序
  • • （1）为什么查询效率低
    • （2）优化order by
  • 7，拿不到锁
  • 8，数据库出现脏页
  • • (1)什么是脏页
    • (2)一条更新语句是如何执行的？
    • (3)为什么会出现脏页呢
    • (4)什么时候会刷脏页（flush）
    • (5)为什么刷脏页会导致SQL变慢呢
  • 9，优化

一，前言

在日常开发中，我们往往会给表加各种索引，来提高 MySQL 的检索效率。但我们有时会遇到明明给字段加了索引，并没有走索引的Case。 进而导致 MySQL 产生慢查询。
严重场景下，甚至出现主从延迟、数据库拖垮的极端事故

二，准备

1，建立user表并初始化

use usermanager;create table userinfo(uid int primary key auto_increment,username varchar(250) not null,loginname varchar(250) unique not null,password varchar(65) not null,sex varchar(2) default '男',age int default 0,address varchar(250) default '',qq varchar(250) default '',email varchar(250) default '',isadmin bit default 0,state int default 1,createtime datetime default now(),updatetime datetime default now()
) default charset='utf8mb4';insert into userinfo(username,loginname,password,isadmin)values('超级管理员','admin','admin',1);
insert into userinfo(username,loginname,password,isadmin)values('张三','zhangsan','123456',0);

2，explain命令的使用
只要我们在 SQL 前加上 explain，就可以分析出，当前环境下 MySQL 的“查询方式”以及“索引选择”。
首先大致看下每个字段的含义：

type重点看

type 列表示了 MySQL 关联的类型，它代表了mysql是如何在表里找数据的。
下面按性能从高到低的顺序介绍type类型：以下四种类型，说明 “性能很好，一般无需优化” ：

• system：表里就一条数据
• const：一般是针对主键/唯一键的等值查询，mysql可以把这类查询优化为一个常量表达式
• eq_ref：一般出现在多表join时，针对主键/唯一键的等值查询，mysql知道只需要返回一条记录
• ref：多表 join 时，针对索引字段的查询
  以下几种类型，需要 “看具体情况，决定是否要优化” ：
• fulltext：关联使用了全文索引
• ref_or_null：查询走了索引，但是除此之外还要判断字段是不是null，如果出现这种类型，可以考虑这个字段是否有为空的必要
• index_merge：使用了索引合并优化，如果高频出现，可以考虑是不是索引设计有问题。
• unique_subquery：in 子句中的子查询，如果只访问主键/唯一键可能会出现这种 type，并不常见
• index_subquery：同样是 in 里的子查询，访问了索引列，并不常见
• range：对索引字段的范围扫描，一般出现在带有比较的查询语句中，一些in和or的查询也会导致这种类型的扫描
  以下两种类型，需要 “优化 & 避免出现” ：
• index：按索引进行全表扫描，如果查询不是覆盖索引的，可能会产生很大量的随机IO
• all：全表扫描

三，慢查询原因和解决

1，sql未加索引

explain select * from userinfo where username=“张三”;

优化：根据业务场景，合理的建立相应的索引。

2，索引失效

具体：索引使用和索引失效

3，limit深分页问题

（1）limit深分页为什么会慢

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

这个SQL的执行流程：

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

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

• limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。
• limit 100000,10 扫描更多的行数，也意味着回表更多的次数

（2）深分页优化

• 标签记录法

就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描

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

• 延迟关联法

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内连接，这样后面直接走了主键索引了，同时也减少了回表。

4，in元素过多

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

反例：
select user_id,name from user where user_id in (1,2,3...1000000);

如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。尤其有时候，我们是用的子查询。如下这种子查询：

select * from user where user_id in (select author_id from artilce where type = 1);

5， join 或者子查询过多

一般来说，不建议使用子查询，可以把子查询改成join来优化。而数据库有个规范约定就是：尽量不要有超过3个以上的表连接。

• join过多的问题：

一方面，过多的表连接，会大大增加SQL复杂度。另外一方面，如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。如果被驱动表没有可用的索引，join是在join_buffer内存做的，如果匹配的数据量比较小或者join_buffer设置的比较大，速度也不会太慢。但是，如果join的数据量比较大时，mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的 IO 就不快，还要关联。

一般情况下，如果业务需要的话，关联2~3个表是可以接受的，但是关联的字段需要加索引哈。如果需要关联更多的表，建议从代码层面进行拆分，在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成map，然后在业务层进行数据的拼装。

6，order by文件排序

（1）为什么查询效率低

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

（2）优化order by

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

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

7，拿不到锁

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

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

8，数据库出现脏页

(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日志里的数据同步到磁盘中。

(3)为什么会出现脏页呢

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

(4)什么时候会刷脏页（flush）

InnoDB存储引擎的redo log大小是固定，且是环型写入的，如下图（图片来源于MySQL 实战 45 讲）：

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

• redo log写满了，要刷脏页。这种情况要尽量避免的。因为出现这种情况时，整个系统就不能再接受更新啦，即所有的更新都必须堵住。
  内存不够了，需要新的内存页，就要淘汰一些数据页，这时候会刷脏页

• InnoDB 用缓冲池（buffer pool）管理内存,而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

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

(5)为什么刷脏页会导致SQL变慢呢

redo log写满了，要刷脏页，这时候会导致系统所有的更新堵住，写性能都跌为0了，肯定慢呀。一般要杜绝出现这个情况。
一个查询要淘汰的脏页个数太多，一样会导致查询的响应时间明显变长

9，优化

• 使用explain查看SQL语句的执行计划
• 如果有告警信息,查看告警信息的show warnings
• 查看SQL语句涉及的表结构和索引信息
• 根据执行计划对SQL语句需要优化的地方进行优化
• 根据需要优化的情况执行表结构的修改,索引的添加 ,SQL语句的改写等操作
• 再次使用explain查看优化后的执行时间和执行计划
• 根据优化效果选择继续优化,还是优化成功