彻底根除MySQL慢查询，这12个问题都不能落下

日常开发中，我们经常会遇到数据库慢查询。那么导致数据慢查询都有哪些常见的原因呢？今天田螺哥就跟大家聊聊导致MySQL慢查询的12个常见原因，以及对应的解决方法。

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

我们创建一个用户user表。

CREATE TABLE user (  id int(11) NOT NULL AUTO_INCREMENT,  userId varchar(32) NOT NULL,  age  varchar(16) NOT NULL,  name varchar(255) NOT NULL,  PRIMARY KEY (id),  KEY idx_userid (userId) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;

userId字段为字串类型，是B+树的普通索引，如果查询条件传了一个数字过去，会导致索引失效。如下：

如果给数字加上'',也就是说，传的是一个字符串呢，当然是走索引，如下图：

我们还是用这个表结构：

CREATE TABLE user (  id int(11) NOT NULL AUTO_INCREMENT,  userId varchar(32) NOT NULL,  age  varchar(16) NOT NULL,  name varchar(255) NOT NULL,  PRIMARY KEY (id),  KEY idx_userid (userId) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中userId加了索引，但是age没有加索引的。我们使用了or，以下SQL是不走索引的，如下：

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

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

like查询以%开头，索引失效。

explain select * from user where userId like '%123';

把%放后面，发现索引还是正常走的，如下：

explain select * from user where userId like '123%';

既然like查询以%开头，会导致索引失效。我们如何优化呢？

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

假设有以下表结构：

CREATE TABLE user (  id int(11) NOT NULL AUTO_INCREMENT,  user_id varchar(32) NOT NULL,  age  varchar(16) NOT NULL,  name varchar(255) NOT NULL,  PRIMARY KEY (id),  KEY idx_userid_name (user_id,name) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;

有一个联合索引idx_userid_name，我们执行这个SQL，查询条件是name，索引是：

explain select * from user where name ='dbaplus社群';

因为查询条件列name不是联合索引idx_userid_name中的个列，索引不生效

在联合索引中，查询条件满足左匹配原则时，索引才正常生效。

表结构：

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `userId` varchar(32) NOT NULL,  `login_time` datetime NOT NULL,  PRIMARY KEY (`id`),  KEY `idx_userId` (`userId`) USING BTREE,  KEY `idx_login_time` (`login_Time`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

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

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

explain  select * from user where login_time = DATE_ADD('2022-05-22 00:00:00',INTERVAL -1 DAY);

表结构：

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `userId` varchar(32) NOT NULL,  `age` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx_age` (`age`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然age加了索引，但是因为它进行运算，索引直接迷路了。如图：

所以不可以对索引列进行运算，可以在代码处理好，再传参进去。

表结构：

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `userId` int(11) NOT NULL,  `age` int(11) DEFAULT NULL,  `name` varchar(255) NOT NULL,  PRIMARY KEY (`id`),  KEY `idx_age` (`age`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

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

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

表结构：

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `card` varchar(255) DEFAULT NULL,  `name` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx_name` (`name`) USING BTREE,  KEY `idx_card` (`card`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

单个name字段加上索引，并查询name为非空的语句，其实会走索引的，如下：

单个card字段加上索引，并查询name为非空的语句，其实会走索引的，如下：

但是它两用or连接起来，索引就失效了，如下：

很多时候，也是因为数据量问题，导致了MySQL优化器放弃走索引。同时，平时我们用explain分析SQL的时候，如果type=range,要注意一下哈，因为这个可能因为数据量问题，导致索引。

新建两个表，一个user，一个user_job：

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,  `age` int(11) NOT NULL,  PRIMARY KEY (`id`),  KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `user_job` (  `id` int(11) NOT NULL,  `userId` int(11) NOT NULL,  `job` varchar(255) DEFAULT NULL,  `name` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user表的name字段编码是utf8mb4，而user_job表的name字段编码为utf8。

执行左外连接查询,user_job表还是走全表扫描，如下：

如果把它们的name字段改为编码一致，相同的SQL，还是会走索引。

所以大家在做表关联时，注意一下关联字段的编码问题哈。

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

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

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

limit深分页为什么会导致SQL变慢呢？假设我们有表结构如下：

CREATE TABLE account (  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',  name varchar(255) DEFAULT NULL COMMENT '账户名',  balance int(11) DEFAULT NULL COMMENT '余额',  create_time datetime NOT NULL COMMENT '创建时间',  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY (id),  KEY idx_name (name),  KEY idx_create_time (create_time) //索引) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

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

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

这个SQL的执行流程：

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

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

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

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

大家是否还记得，一个B+树大概可以存放多少数据量呢？

InnoDB存储引擎小储存单元是页，一页大小就是16k。

B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。

如果B+树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

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

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

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

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

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

join过多的问题：

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

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

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

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

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

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

如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。尤其有时候，我们是用的子查询，in后面的子查询，你都不知道数量有多少那种，更容易采坑（所以我把in元素过多抽出来作为一个小节）。如下这种子查询：

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

分批进行，每批500个：

select user_id,name from user where user_id in (1,2,3...500);

如果传参的ids太多，还可以做个参数校验什么的：

if (userIds.size() > 500) {    throw new Exception("单次查询的用户Id不能超过200");}

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

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

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

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

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

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

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

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

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

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

我们平时经常需要用到order by ，主要就是用来给某些字段排序的。比如以下SQL:

select name,age,city from staff where city = '深圳' order by age limit 10;

它表示的意思就是：查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。

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

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;

2）全字段排序

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

select name,age,city from staff where city = '深圳' order by age limit 10;

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

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

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

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

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

之前见到过一个生产慢SQL问题，当delete遇到in子查询时，即使有索引，也是不走索引的。而对应的select + in子查询，却可以走索引。

MySQL版本是5.7，假设当前有两张表account和old_account,表结构如下：

CREATE TABLE `old_account` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',  `name` varchar(255) DEFAULT NULL COMMENT '账户名',  `balance` int(11) DEFAULT NULL COMMENT '余额',  `create_time` datetime NOT NULL COMMENT '创建时间',  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY (`id`),  KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='老的账户表';

CREATE TABLE `account` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',  `name` varchar(255) DEFAULT NULL COMMENT '账户名',  `balance` int(11) DEFAULT NULL COMMENT '余额',  `create_time` datetime NOT NULL COMMENT '创建时间',  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY (`id`),  KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

执行的SQL如下：

delete from account where name in (select name from old_account);

查看执行计划，发现不走索引：

但是如果把delete换成select，就会走索引。如下：

为什么select + in子查询会走索引，delete + in子查询却不会走索引呢？

我们执行以下SQL看看：

explain select * from account where name in (select name from old_account);show WARNINGS; //可以查看优化后,终执行的sql

结果如下：

select `test2`.`account`.`id` AS `id`,`test2`.`account`.`name` AS `name`,`test2`.`account`.`balance` AS `balance`,`test2`.`account`.`create_time` AS `create_time`,`test2`.`account`.`update_time` AS `update_time` from `test2`.`account` semi join (`test2`.`old_account`)where (`test2`.`account`.`name` = `test2`.`old_account`.`name`)

可以发现，实际执行的时候，MySQL对select in子查询做了优化，把子查询改成join的方式，所以可以走索引。但是很遗憾，对于delete in子查询，MySQL却没有对它做这个优化。

日常开发中，大家注意一下这个场景。

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

假设有表结构：

CREATE TABLE `staff` (  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',  `id_card` varchar(20) NOT NULL COMMENT '身份证号码',  `name` varchar(64) NOT NULL COMMENT '姓名',  `age` int(4) NOT NULL COMMENT '年龄',  `city` varchar(64) NOT NULL COMMENT '城市',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';

我们查看一下这个SQL的执行计划：

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

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

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

1）创建内存临时表，表里有两个字段city和num；

2）全表扫描staff的记录，依次取出city = 'X'的记录。

3）遍历完成后，再根据字段city做排序，得到结果集返回给客户端。

这个流程的执行图如下：

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

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

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

从哪些方向去优化呢？

我们一起来想下，执行group by语句为什么需要临时表呢？group by的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦?

可以有这些优化方案：

如果数据库服务器内存、硬件资源，或者网络资源配置不是很好，就会慢一些哈。这时候可以升级配置。这就好比你的计算机有时候很卡，你可以加个内存条什么的一个道理。

如果数据库压力本身很大，比如高并发场景下，大量请求到数据库来，数据库服务器CPU占用很高或者IO利用率很高，这种情况下所有语句的执行都有可能变慢的哈。

如果测试环境数据库的一些参数配置，和生产环境参数配置不一致的话，也容易产生慢SQL哈。之前见过一个慢SQL的生产案例，就是测试环境用了index merge，所以查看explain执行计划时，是可以走索引的，但是到了生产，却全表扫描，后排查发现是生产环境配置把index merge关闭了。

• MySQL实战45讲

  https://time.geekbang.org/column/article/72775?cid=100020801