盘点Mysql慢查询的12个原因（全）

1、sql没加索引

很多时候，我们的慢查询，都是因为没有加索引，如果没有加索引的话，会导致全表扫描，因此在where的条件列建立索引，尽量避免全表扫描

反例：

正例：

2、sql索引不生效

有时候明明添加了索引却不生效，在以下场景不生效，如下图

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

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;

userld是字符串类型，是B＋树普通索引，而这是传一个数字类型，就会导致索引失效

为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。

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

对于or＋没有索引的age这种情况，假设它走了userld的索引，但是走到age查询条件时，还得全表扫描，也就是需要三步过程：全表扫描＋索引扫描＋合并，如果他一开始就走全表扫描，直接一遍扫描就完事，Mysql优化器出于效率与成本考虑，遇到or条件，让索引失效，但是倮or条件列都加了索引，索引可能走可能不走，可以考虑拆成两条sql

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

这里指like查询以％开头，才会导致索引失效

将％放后面，还是正常走索引

2.3.1避免索引失效

1．使用覆盖索引（查询方式）

主键索引：叶子节点存储数据、辅助索引（非主键索引）：叶子节点存储主键＝回表

2．将％放后面

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

mysql建立联合索引时，会遵循最左匹配规则，即最左优先，如建立一个（userld，name）联合索引，相当于建立（userld）、（userld，name）三个索引

因为name列不满足mysql最左原则匹配（不是联合索引的第一个列），导致索引失效

2.5在索引上使用mysql内置函数

DROP TABLE USER;
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,
KEYidx_login_time( login_Time) USING BTREE
) ENGINE = INNODB AUTO_I$I N C R M N T 2$DEFAULT$C H A R S E T = u t f 8 ;$

虽然login＿time加了索引，但是使用了mysql内置函数：DATE＿ADD()，直接凉凉，如图

1 EXPLAIN select*from user where DATE_ADD(login_time,INTERVAL 1 DAY)= '2022-06-20 00:00:00

信息	Result 1	概况	状态
id	select_type	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	SIMPLE	user	(Null)		(Null)	(Null)	(Null)	(Null)	1	100	Using where

可以将内置函数的逻辑转移到右边索引就生效了，如下：

2.6对索引进行列运算（如，＋、-、＊、／），索引不生效

虽然age加索引了，但是对他进行运算，导致索引失效，所以不可以对索引列进行运算，可以在代码处理好，再传参进去

2.7 索引字段上使用（！＝或者<>) ，索引可能失效

避免拿索引列作为条件查询使用！＝、＜＞、not in，这和mysql优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的话，不如直接不走索引

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

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和card字段加上索引，加上is not null 或is null 其实是会走索引的，但是用or连接索引就会失效

explain select * from user where name is not null;
explain select * from user where card is not null;
explain select * from user where name is null;
explain select * from user where card is null;
explain select* from user where name is not null or card is not null;
explain select * from user where name is null or card is null;

很多时候，也是因为数据量问题，导致mysql优化器放弃走索引，同时我们用explain分析sql时，如果:type=range时这个可能因为数据量问题导致索引无效

2.9左右连接，关联的字段编码格式不一样

DROP table user;
CREATE TABLE 'user' (
'id'int(11) NOT NULL AUTO_INCREMENT,
namevarchar(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表还是走全表扫描，如下：

1 explain select u.name,j.name,j.job

2FROM user u

3 LEFT JOIN user_job j

4 on u.name=j.name;

信息	结果1 剖析	状态
id	select_type	table	partitions	type	posible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	u	(Null)	index	(N m)	idx_nai	1023	(Null)	1	100	Using index
1	SIMPLE	j	(Null)	ALL	(N ll)	(Null)	(Null)	(Null)	1	100	Using where;Us

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

信息	结果1	剖析	状态
id	select_t	ype	table	partitions
1	SIMPLE		u	(Null)
1	SIMPLE	SIMPLE	j	(Null)

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

2.10优化器选错了索引

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

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

·使用force index 强行选择某个索引

·修改你的SQI，引导它使用我们期望的索引

·优化你的业务逻辑

·优化你的索引，新建一个更合适的索引，或者删除误用的索引。

3、limit深分页

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

3.1 limit深分页为什么会变慢

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

1 CREATE TABLE account (

2 id int（11） NOT NULL AUTO＿INCREMENT COMMENT ＇主键Id＇，

3 name varchar（255）DEFAULT NULL COMMENT ＇账户名”，

4 balance int（11） DEFAULT NULL COMMENT ＇余额，

5 create＿time datetime NOT NULL COMMENT ＇创建时间＇，

6 update＿time datetime NOT NULL ON UPDATE CURRENT＿TIMESTAMP COMMENT ＇更新时间＇，

7 PRIMARY KEY(id),

8 KEY idx_name (name),

9 KEY idx＿create＿time（create＿time）／／索引

10 ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMEN T＝＇账户表＇；

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

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

这个SQL的执行流程：

1．通过普通二级索引树idx＿create＿time，过滤create＿time条件，找到满足条件的主键id。

2．通过主键id，回到id主键索引树，找到满足记录的行，然后取出需要展示的列（回表过程）

3．扫描满足条件的100010行，然后扔掉前100000行，返回。

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

·limit语句会先扫描offset＋n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。

·limit 100000,10扫描更多的行数，也意味着回表更多的次数。

3.2如何优化深分页问题

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

标签记录法

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

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

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

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

延迟关联法

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

1 select acct1.id,acct1.name,acct1.balance

2 FROM account acct1

3 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-

19' limit 100000, 10) AS acct2

4 on\operatorname{arccot}1.id=arctan.id;

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

4、单表数据量太大

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

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

4.2一棵B＋树可以存多少数据量

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

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

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

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

·如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数=16k/1k=16.

·非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节（面试官问你int类型，一个int就是32位，4字节），而指针大小在InnoDB源码中设置为6字节，所以就是88+6=144字节，16k／14B＝16＊1024B／14B＝1170

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

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

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

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

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

·事务问题

·跨库问题

·排序问题

·分页问题

·分布式ID

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

5、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，然后在业务层进行数据的拼装。

6、in元素过多

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

反例：

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

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

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

正例是，分批进行，每批500个：

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

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

1 if (userIds.$\sin ( 2 e ^ { x } ) > 5 0 0 )${

2 throw new Exception（＂单次查询的用户Id不能超过200＂）；

3 }

7、数据库在刷脏页

7.1什么是脏页

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

7.2一条更新语句是如何执行的

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

1 update t setc=c+1whereid=666;

1．对于这条更新SQL，执行器会先找引擎取id=666这一行。如果这行所在的数据页本来就在内存中的话，就直接返回给执行器。如果不在内存，就去磁盘读入内存，再返回。

2．执行器拿到引擎给的行数据后，给这一行C的值加一，得到新的一行数据，再调用引擎接口写入这行新数据。

3．引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，但是此时redo log是处于prepare状态的哈。

4．执行器生成这个操作的binlog，并把binlog写入磁盘。

5．执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

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

redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。

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

7.3为什么会出现脏页呢？

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

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

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

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

1．redo log写满了，要刷脏页。这种情况要尽量避免的。因为出现这种情况时，整个系统就不能再接受更新啦，即所有的更新都必须堵住。

2．内存不够了，需要新的内存页，就要淘汰一些数据页，这时候会刷脏页

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

3．MySQL认为系统空闲的时候，也会刷一些脏页

4．MySQL正常关闭时，会把内存的脏页都flush到磁盘上

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

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

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

8、order by 文件排序

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

8.1 order by的 Using filesort文件排序

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

1 select name,age,city from staff where city=＇深圳l＇ order by age limit 10；

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

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

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

order by用到文件排序时，为什么查询效率会相对低呢？

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

rowid排序

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

1 select name,age,city from staff wherecity=＇深圳＇order by age limit 10；

1．MySQL为对应的线程初始化sort＿buffer，放入需要排序的age字段，以及主键id；

2．从索引树idx＿city，找到第一个满足city＝＇深圳＇条件的主键id，也就是图中的id=9

3．到主键id索引树拿到id＝9的这一行数据，取age和主键id的值，存到sort＿buffer；

4．从索引树idx＿city拿到下一个记录的主键id，即图中的id=13、

5．重复步骤3、4直到city的值不等于深圳为止；

6．前面5步已经查找到了所有city为深圳的数据，在sort＿buffer中，将所有数据根据age进行排序；

7．遍历排序结果，取前10行，并按照id的值回到原表中，取出city、name和age三个字段返回给客户端。

全字段排序

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

1 select name,age,city from staff where city=＇深圳＇order by age limit 10；

1．MySQL为对应的线程初始化sort＿buffer，放入需要查询的name、age、city字段；

2．从索引树idx＿city，找到第一个满足city＝“深圳”条件的主键id，也就是图中的iid=9;

3．到主键id索引树拿到id＝9的这一行数据，取name、age、city三个字段的值，存到sort＿buffer；

4．从索引树idx＿city拿到下一个记录的主键id，即图中的id=13;

5．重复步骤3、4直到city的值不等于深圳为止；

6．前面5步已经查找到了所有city为深圳的数据，在sort＿buffer中，将所有数据根据。agei进行排序；

7．按照排序结果取前10行返回给客户端。

sort＿buffer的大小是由一个参数控制的：sort＿buffer＿size。

·如果要排序的数据小于sort＿buffer＿size，排序在sort＿buffer内存中完成

·如果要排序的数据大于sort＿buffer＿size，则借助磁盘文件来进行排序。

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

8.3如何优化order by的文件排序

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

·因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化

order by语句。

·我们还可以通过调整max＿length＿for＿sort＿data、sort＿buffer＿size等参数优化；

9、拿不到锁

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

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

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

10、delete＋in 子查询不走索引

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

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

1 CREATE TABLE old_account(

2 id＇ int（11） NOT NULL AUTO＿INCREMENT COMMENT ＇主键Id＇，

3 ｀name＇ varchar（255）DEFAULT NULL COMMENT ＇账户名＇，

4 ＇balance＇ int（11） DEFAULT NULL COMMENT＇余额”，

5 ＇create＿time＇datetime NOT NULL COMMENT ＇创建时间＇，

6 ＇update＿time＇ datetime NOT NULL ON UPDATE CURRENT＿TIMESTAMP COMMENT＇更新时间＇，

7 PRIMARY KEY(`id),

8 KEY 'idx_name'('name`) USING BTREE

9 ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMEN T＝＇老的账户表＇；

10

11 CREATE TABLE account(

12 id int（11） NOT NULL AUTO＿INCREMENT COMMENT ＇主键Id＇，

13 ｀name＇varchar（255）DEFAULT NULL COMMENT ＇账户名＇，

14 ＇balance＇ int（11） DEFAULT NULL COMMENT＇余额＇，

15 ＂create＿time｀datetime NOT NULL COMMENT ＇创建时间＇，

16 update＿time datetime NOT NULL ON UPDATE CURRENT＿TIMESTAMP COMMENT ＇更新时间＇，

17 PRIMARY KEY(`id),

18 KEY 'idx_name(name`) USING BTREE

19 ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMEN T＝＇账户表＇；

执行的SQL如下：

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

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

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

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

我们执行以下SQL看看：

1 explain select * from account where name in (select name from old_account);

2 show WARNINGS；／／可以查看优化后，最终执行的sq1

结果如下：

1 select test2.account.id AS id, test2.account.nameAS name,test2.accou nt.balanceAS balance,test2.account.create_timeAS create_time,test2`.ac count. update_time AS update_time

2from 'test2'.'account semi

3 join (test2'.old_account`)

4 where (test2.account.name= test2'.old_account.name`)

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

11、group by 使用临时表

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

11.1 group by的执行流程

假设有表结构：

1 CREATE TABLE 'staff'(

2 id＇ bigint（11）NOT NULL AUTO＿INCREMENT COMMENT ＇主键id＇，

3 ｀id＿card＇ varchar（20） NOT NULL COMMENT ＇身份证号码”，

4 ｀name｀varchar（64）NOT NULL COMMENT ＇姓名＇，

5 ＇age｀int（4）NOT NULL COMMENT ＇年龄＇，

6 city＇ varchar（64）NOT NULL COMMENT ＇城市＇，

7 PRIMARY KEY (`id)

8 ) ENGINE=InnoDB AUTO_INCREMEINT=15DEFAULT CHARSET=utf8COMMENT=员工表；

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

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

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

id	select_type	select_type	table	partitions	type	possible keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	SIMPLE	staff	(Null)	ALL	(Null)	(Null)	(Null)	(Nul	l 12	100	Using temporary;Using filesort

·Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表

·Extra 这个字段的Using filesort表示使用了文件排序

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

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

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

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

2．全表扫描staff的记录，依次取出city=x的记录。

·判断临时表中是否有为$c i t y = ^ { \prime } x ^ { \prime }$的行，没有就插入一个记录（X，1）；

·如果临时表中有$c i t y = ^ { \prime } x$的行，就将X这一行的num值加1；

3．遍历完成后，再根据字段city做排序，得到结果集返回给客户端。这个流程的执行图如下：

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

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

·如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回

·如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。

11.2 group by可能会慢在哪里？

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

·如果执行过程中，会发现

内存临时表大小到达了上限（控制这个上限的参数就是tmp＿table＿size），会把内存临时表转成磁盘临时表。

·如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。

11.3 如何优化group by呢？

从哪些方向去优化呢？

·方向1：既然它默认会排序，我们不给它排是不是就行啦。

·方向2：既然临时表是影响

group by性能的X因素，我们是不是可以不用临时表？

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

可以有这些优化方案：

·group by后面的字段加索引

·order by null 不用排序

·尽量只使用内存临时表

·使用SQL＿BIG＿RESULT

12、系统硬件或网路资源

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

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

最后

如果测试环境数据库的一些参数配置，和生产环境参数配置不一致的话，也容易产生慢SQL哈。之前见过一个慢SQL的生产案例，就是测试环境用了index merge，所以查看explain执行计划时，是可以走索引的，但是到了生产，却全表扫描，最后排查发现是生产环境配置把index merge关闭了。大家是否还遇到其他场景的慢SQL呢？如果有的话，欢迎评论区留言交流哈、