SQL性能优化技巧，常见优化方法，一文就让你学会

我熟练应用ctrl c和ctrl v 开发curd代码好多年了。

mysql查询为什么会慢，关于这个问题，在实际开发经常会遇到，而面试中，也是个高频题。

遇到这种问题，我们一般也会想到是因为索引。

那除开索引之外，还有哪些因素会导致数据库查询变慢呢？

有哪些操作，可以提升mysql的查询能力呢？

今天这篇文章，我们就来聊聊会导致数据库查询变慢的场景有哪些，并给出原因和解决方案。

数据库查询流程

我们先来看下，一条查询语句下来，会经历哪些流程。

比如我们有一张数据库表

CREATE TABLE `user` (



  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',



  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字',



  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',



  `gender` int(8) NOT NULL DEFAULT '0' COMMENT '性别',



  PRIMARY KEY (`id`),



  KEY `idx_age` (`age`),



  KEY `idx_gender` (`gender`)



) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我们平常写的应用代码（go或C++之类的），这时候就叫客户端了。

客户端底层会带着账号密码，尝试向mysql建立一条TCP长链接。

mysql的连接管理模块会对这条连接进行管理。

建立连接后，客户端执行一条查询sql语句。比如：

select * from user where gender = 1 and age = 100;

客户端会将sql语句通过网络连接给mysql。

mysql收到sql语句后，会在分析器中先判断下SQL语句有没有语法错误，比如select，如果少打一个l，写成slect，则会报错You have an error in your SQL syntax;。这个报错对于我这样的手残党来说可以说是很熟悉了。

接下来是优化器，在这里会根据一定的规则选择该用什么索引。

之后，才是通过执行器去调用存储引擎的接口函数。

Mysql架构

存储引擎类似于一个个组件，它们才是mysql真正获取一行行数据并返回数据的地方，存储引擎是可以替换更改的，既可以用不支持事务的MyISAM，也可以替换成支持事务的Innodb。这个可以在建表的时候指定。比如

CREATE TABLE `user` (



  ...



) ENGINE=InnoDB;

现在最常用的是InnoDB。

我们就重点说这个。

InnoDB中，因为直接操作磁盘会比较慢，所以加了一层内存提提速，叫buffer pool，这里面，放了很多内存页，每一页16KB，有些内存页放的是数据库表里看到的那种一行行的数据，有些则是放的索引信息。

bufferPool与磁盘

查询SQL到了InnoDB中。会根据前面优化器里计算得到的索引，去查询相应的索引页，如果不在buffer pool里则从磁盘里加载索引页。再通过索引页加速查询，得到数据页的具**置。如果这些数据页不在buffer pool中，则从磁盘里加载进来。

这样我们就得到了我们想要的一行行数据。

索引页与磁盘页的关系

最后将得到的数据结果返回给客户端。

慢查询分析

如果上面的流程比较慢的话，我们可以通过开启profiling看到流程慢在哪。

mysql> set profiling=ON;



Query OK, 0 rows affected, 1 warning (0.00 sec)



 



mysql> show variables like 'profiling';



+---------------+-------+



| Variable_name | Value |



+---------------+-------+



| profiling     | ON    |



+---------------+-------+



1 row in set (0.00 sec)

然后正常执行sql语句。

这些SQL语句的执行时间都会被记录下来，此时你想查看有哪些语句被记录下来了，可以执行 show profiles;

mysql> show profiles;



+----------+------------+---------------------------------------------------+



| Query_ID | Duration   | Query                                             |



+----------+------------+---------------------------------------------------+



|        1 | 0.06811025 | select * from user where age>=60                  |



|        2 | 0.00151375 | select * from user where gender = 2 and age = 80  |



|        3 | 0.00230425 | select * from user where gender = 2 and age = 60  |



|        4 | 0.00070400 | select * from user where gender = 2 and age = 100 |



|        5 | 0.07797650 | select * from user where age!=60                  |



+----------+------------+---------------------------------------------------+



5 rows in set, 1 warning (0.00 sec)

关注下上面的query_id，比如select * from user where age>=60对应的query_id是1，如果你想查看这条SQL语句的具体耗时，那么可以执行以下的命令。

mysql> show profile for query 1;



+----------------------+----------+



| Status               | Duration |



+----------------------+----------+



| starting             | 0.000074 |



| checking permissions | 0.000010 |



| Opening tables       | 0.000034 |



| init                 | 0.000032 |



| System lock          | 0.000027 |



| optimizing           | 0.000020 |



| statistics           | 0.000058 |



| preparing            | 0.000018 |



| executing            | 0.000013 |



| Sending data         | 0.067701 |



| end                  | 0.000021 |



| query end            | 0.000015 |



| closing tables       | 0.000014 |



| freeing items        | 0.000047 |



| cleaning up          | 0.000027 |



+----------------------+----------+



15 rows in set, 1 warning (0.00 sec)

通过上面的各个项，大家就可以看到具体耗时在哪。比如从上面可以看出Sending data的耗时最大，这个是指执行器开始查询数据并将数据发送给客户端的耗时，因为我的这张表符合条件的数据有好几万条，所以这块耗时最大，也符合预期。

一般情况下，我们开发过程中，耗时大部分时候都在Sending data阶段，而这一阶段里如果慢的话，最容易想到的还是索引相关的原因。

索引相关原因

索引相关的问题，一般能用explain命令帮助分析。通过它能看到用了哪些索引，大概会扫描多少行之类的信息。

mysql会在优化器阶段里看下选择哪个索引，查询速度会更快。

一般主要考虑几个因素，比如：

• 选择这个索引大概要扫描多少行（rows）
• 为了把这些行取出来，需要读多少个16kb的页
• 走普通索引需要回表，主键索引则不需要，回表成本大不大？

回到show profile中提到的sql语句，我们使用explain select * from user where age>=60 分析一下。

explain sql

上面的这条语句，使用的type为ALL，意味着是全表扫描，possible_keys是指可能用得到的索引，这里可能使用到的索引是为age建的普通索引，但实际上数据库使用的索引是在key那一列，是NULL。也就是说这句sql不走索引，全表扫描。

这个是因为数据表里，符合条件的数据行数（rows）太多，如果使用age索引，那么需要将它们从age索引中读出来，并且age索引是普通索引，还需要回表找到对应的主键才能找到对应的数据页。算下来还不如直接走主键划算。于是最终选择了全表扫描。

当然上面只是举了个例子，实际上，mysql执行sql时，不用索引或者用的索引不符合我们预期这件事经常发生，索引失效的场景有很多，比如用了不等号，隐式转换等，这个相信大家背八股文的时候也背过不少了，我也不再赘述。

聊两个生产中容易遇到的问题吧。

索引不符合预期

实际开发中有些情况比较特殊，比如有些数据库表一开始数据量小，索引少，执行sql时，确实使用了符合你预期的索引。但随时时间边长，开发的人变多了，数据量也变大了，甚至还可能会加入一些其他重复多余的索引，就有可能出现用着用着，用到了不符合你预期的其他索引了。从而导致查询突然变慢。

这种问题，也好解决，可以通过force index指定索引。比如

force index指定索引

通过explain可以看出，加了force index之后，sql就选用了idx_age这个索引了。

走了索引还是很慢

有些sql，用explain命令看，明明是走索引的，但还是很慢。一般是两种情况：

第一种是索引区分度太低，比如网页全路径的url链接，这拿来做索引，一眼看过去全都是同一个域名，如果前缀索引的长度建得不够长，那这走索引跟走全表扫描似的，正确姿势是尽量让索引的区分度更高，比如域名去掉，只拿后面URI部分去做索引。

索引前缀区分度太低

第二种是索引中匹配到的数据太大，这时候需要关注的是explain里的rows字段了。

它是用于预估这个查询语句需要查的行数的，它不一定完全准确，但可以体现个大概量级。

当它很大时，一般常见的是下面几种情况。

• 如果这个字段具有唯一的属性，比如电话号码等，一般是不应该有大量重复的，那可能是你代码逻辑出现了大量重复插入的操作，你需要检查下代码逻辑，或者需要加个唯一索引限制下。
• 如果这个字段下的数据就是会很大，是否需要全部拿？如果不需要，加个limit限制下。如果确实要拿全部，那也不能一次性全拿，今天你数据量小，可能一次取一两万都没啥压力，万一哪天涨到了十万级别，那一次性取就有点吃不消了。你可能需要分批次取，具体操作是先用order by id排序一下，拿到一批数据后取最大id作为下次取数据的起始位置。

连接数过小

索引相关的原因我们聊完了，我们来聊聊，除了索引之外，还有哪些因素会限制我们的查询速度的。

我们可以看到，mysql的server层里有个连接管理，它的作用是管理客户端和mysql之间的长连接。

正常情况下，客户端与server层如果只有一条连接，那么在执行sql查询之后，只能阻塞等待结果返回，如果有大量查询同时并发请求，那么后面的请求都需要等待前面的请求执行完成后，才能开始执行。

连接过少会导致sql阻塞

因此很多时候我们的应用程序，比如go或java这些，会打印出sql执行了几分钟的日志，但实际上你把这条语句单独拎出来执行，却又是毫秒级别的。这都是因为这些sql语句在等待前面的sql执行完成。

怎么解决呢？

如果我们能多建几条连接，那么请求就可以并发执行，后面的连接就不用等那么久了。

增加连接可以加快执行sql

而连接数过小的问题，受数据库和客户端两侧同时限制。

数据库连接数过小

Mysql的最大连接数默认是100, 最大可以达到16384。

可以通过设置mysql的max_connections参数，更改数据库的最大连接数。

mysql> set global max_connections= 500;



Query OK, 0 rows affected (0.00 sec)



 



mysql> show variables like 'max_connections';



+-----------------+-------+



| Variable_name   | Value |



+-----------------+-------+



| max_connections | 500   |



+-----------------+-------+



1 row in set (0.00 sec)

上面的操作，就把最大连接数改成了500。

应用侧连接数过小

数据库连接大小是调整过了，但貌似问题还是没有变化？还是有很多sql执行达到了几分钟，甚至超时？

那有可能是因为你应用侧（go，java写的应用，也就是mysql的客户端）的连接数也过小。

应用侧与mysql底层的连接，是基于TCP协议的长链接，而TCP协议，需要经过三次握手和四次挥手来实现建连和释放。如果我每次执行sql都重新建立一个新的连接的话，那就要不断握手和挥手，这很耗时。所以一般会建立一个长连接池，连接用完之后，塞到连接池里，下次要执行sql的时候，再从里面捞一条连接出来用，非常环保。

连接池原理

我们一般写代码的时候，都会通过第三方的orm库来对数据库进行操作，而成熟的orm库，百分之一千万都会有个连接池。

而这个连接池，一般会有个大小。这个大小就控制了你的连接数最大值，如果说你的连接池太小，都还没有数据库的大，那调了数据库的最大连接数也没啥作用。

一般情况下，可以翻下你使用的orm库的文档，看下怎么设置这个连接池的大小，就几行代码的事情，改改就好。比如go语言里的gorm里是这么设置的

func Init() {



  db, err := gorm.Open(mysql.Open(conn), config)



    sqlDB, err := db.DB()



    // SetMaxIdleConns 设置空闲连接池中连接的最大数量



    sqlDB.SetMaxIdleConns(200)



    // SetMaxOpenConns 设置打开数据库连接的最大数量



    sqlDB.SetMaxOpenConns(1000)



}

buffer pool太小

连接数是上去了，速度也提升了。

曾经遇到过面试官会追问，有没有其他办法可以让速度更快呢？

那必须要眉头紧锁，假装思考，然后说：有的。

我们在前面的数据库查询流程里，提到了进了innodb之后，会有一层内存buffer pool，用于将磁盘数据页加载到内存页中，只要查询到buffer pool里有，就可以直接返回，否则就要走磁盘IO，那就慢了。

也就是说，如果我的buffer pool 越大，那我们能放的数据页就越多，相应的，sql查询时就更可能命中buffer pool，那查询速度自然就更快了。

可以通过下面的命令查询到buffer pool的大小，单位是Byte。

mysql> show global variables like 'innodb_buffer_pool_size';



+-------------------------+-----------+



| Variable_name           | Value     |



+-------------------------+-----------+



| innodb_buffer_pool_size | 134217728 |



+-------------------------+-----------+



1 row in set (0.01 sec)

也就是128Mb。

如果想要调大一点。可以执行

mysql> set global innodb_buffer_pool_size = 536870912;



Query OK, 0 rows affected (0.01 sec)



 



mysql> show global variables like 'innodb_buffer_pool_size';



+-------------------------+-----------+



| Variable_name           | Value     |



+-------------------------+-----------+



| innodb_buffer_pool_size | 536870912 |



+-------------------------+-----------+



1 row in set (0.01 sec)

这样就把buffer pool增大到512Mb了。

但是吧，如果buffer pool大小正常，只是别的原因导致的查询变慢，那改buffer pool毫无意义。

但问题又来了。

怎么知道buffer pool是不是太小了？

这个我们可以看buffer pool的缓存命中率。

查看buffer pool命中率

通过 show status like ‘Innodb_buffer_pool_%’;可以看到跟buffer pool有关的一些信息。

Innodb_buffer_pool_read_requests表示读请求的次数。

Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数。

所以buffer pool的命中率就可以这样得到：

buffer pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

比如我上面截图里的就是，1 - (405/2278354) = 99.98%。可以说命中率非常高了。

一般情况下buffer pool命中率都在99%以上，如果低于这个值，才需要考虑加大innodb buffer pool的大小。

当然，还可以把这个命中率做到监控里，这样半夜sql变慢了，早上上班还能定位到原因，就很舒服。

还有哪些骚操作？

前面提到的是在存储引擎层里加入了buffer pool用于缓存内存页，这样可以加速查询。

那同样的道理，server层也可以加个缓存，直接将第一次查询的结果缓存下来，这样下次查询就能立刻返回，听着挺美的。

按道理，如果命中缓存的话，确实是能为查询加速的。但这个功能限制很大，其中最大的问题是只要数据库表被更新过，表里面的所有缓存都会失效，数据表频繁的更新，就会带来频繁的缓存失效。所以这个功能只适合用于那些不怎么更新的数据表。

另外，这个功能在8.0版本之后，就被干掉了。所以这功能用来聊聊天可以，没必要真的在生产中使用啊。

查询缓存被删除

• 数据查询过慢一般是索引问题，可能是因为选错索引，也可能是因为查询的行数太多。
• 客户端和数据库连接数过小，会限制sql的查询并发数，增大连接数可以提升速度。
• innodb里会有一层内存buffer pool用于提升查询速度，命中率一般>99%，如果低于这个值，可以考虑增大buffer pool的大小，这样也可以提升速度。
• 查询缓存（query cache）确实能为查询提速，但一般不建议打开，因为限制比较大，并且8.0以后的Mysql里已经将这个功能干掉了。

SQL性能优化技巧，常见优化10经验

一、常见优化十经验

其实这个十经验不一定准确，通过上一篇 MySQL再深入执行计划之trace工具 我们已经知道 MySQL 执行查询语句时会进行成本分析，数据量和实际数据值会影响到 MySQL 的实际查询过程，所以要是小伙伴们根据这常见十经验写了后发现和小二文章中的不一样也不要奇怪。

所以…小二纠结了挺久要不要写这 SQL 优化常见优化十经验，毕竟这些很多博客已经提到过了，想了想，还是写出来吧。

-- 示例表



CREATE TABLE `employees` (



  `id` int(11) NOT NULL AUTO_INCREMENT,



  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',



  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',



  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',



  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',



  PRIMARY KEY (`id`),



  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE



) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';



 



INSERT INTO employees(name,age,position,hire_time) VALUES('itwxe',22,'manager',NOW());



INSERT INTO employees(name,age,position,hire_time) VALUES('weiwei', 23,'test',NOW());



INSERT INTO employees(name,age,position,hire_time) VALUES('leilei',23,'dev',NOW());



 



-- 插入10w条测试数据



drop procedure if exists insert_employees; 



delimiter $$



create procedure insert_employees()        



begin



  declare i int;                    



  set i = 1;                          



  while(i <= 100000)do                 



    insert into employees(name, age, position) values(CONCAT('itwxe', i), rand() * 42 + 18, 'dev');  



    set i = i + 1;                       



  end while;



end$$



delimiter ;



call insert_employees();

1. 尽量全值匹配

explain select * from employees where name = 'itwxe';



explain select * from employees where name = 'itwxe' and age = 22;



explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';

记住这三个 key_len 的值，idx_name_age_position (name,age,position)由这三个字段组成，74代表使用了 name 列；78代表使用了 name,age 列；140代表使用了 name,age,position 列。

2. 最左前缀原则

在使用联合索引的时候要特别注意最左前缀原则，即查询从联合索引的最左前列开始并且不跳过索引中的列。

explain select * from employees where name = 'itwxe' and age = '18';



explain select * from employees where name = 'itwxe' and position = 'manager';



explain select * from employees where position = 'manager';

应该也是比较好理解的，不过需要注意的是和查询 SQL 书写的顺序无关，最左指的是联合索引创建时列的顺序。例如 where 中颠倒顺序还是会使用 idx_name_age_position (name,age,position) 中的三个列索引查询。

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';



explain select * from employees where age = 22 and position = 'manager' and name = 'itwxe';



explain select * from employees where position = 'manager' and name = 'itwxe' and age = 22;

可以看到即使颠倒了顺序，三个的执行计划也是一毛一样的。

3. 不在索引列上做任何操作(计算、函数、(自动/手动)类型转换)，会导致索引失效而转向全表扫描

需要注意的是这里说的索引列任何操作(计算、函数、(自动/手动)类型转换)不做操作指的是 where 条件之后的，而不是查询结果字段里面的。

例如对 name 列进行 left 函数操作。

explain select * from employees where name = 'weiwei';



explain select * from employees where left(name,6) = 'weiwei';

4. 存储引擎不能使用索引中范围条件右边的列

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';



explain select * from employees where name = 'itwxe' and age > 22 and position = 'manager';

可以看到第二条 SQL 使用了 name,age 列作为索引来查询，position 并没有使用。

5. 尽量使用覆盖索引，减少select *语句

覆盖索引前面的文章提到过，不赘述了。

6. MySQL 在使用不等于( != 或者 <> )，not in，not exists 的时候无法使用索引会导致全表扫描

<、>、<=、>= 这些，MySQL 内部优化器会根据检索比例、表大小等多个因素计算查询成本是否使用索引。

explain select * from employees where name != 'itwxe';

7. is null 和 is not null 一般情况下也无法使用索引

explain select * from employees where name is null;



explain select * from employees where name is not null;

8. like 以通配符开头(’%itwxe…’) MySQL 索引失效会变成全表扫描操作

explain select * from employees where name like 'wei%';



explain select * from employees where name like '%wei';

相信理解 B+tree 底层数据结构的小伙伴都很容易就知道为啥了，解决办法：

• 根据业务建立联合索引，使用覆盖索引查询。
• 不能使用覆盖索引的则借助 ES 等搜索引擎。

查询中可以简单的把 like KK% 理解为 = 常量，%KK和%KK% 理解为 范围查询。

这里引入一个索引下推的概念：

explain select * from employees where name like 'weiwei%' and age = 22 and position = 'manager';



explain select * from employees where name like 'itwxe%' and age = 22 and position = 'manager';

可以看到第一条SQL中 name = ‘weiwei%’ ，根据 B+tree 的结构可以知道其后的 age,position 列是无序的，应该无法使用 age,position 列过滤数据才对，但是最后的 key_len 确是140，也就意味着 MySQL 利用到了 name,age,postion 三个列来查询，这是因为 MySQL 在5.6版本做的优化，引入了索引下推。

索引下推可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

即当 name = ‘weiwei%’ 过滤时，使用了索引下推优化，过滤 name 时同时还会在索引里过滤 age,position 两个列的条件，拿着过滤完剩下的索引对应的主键 id 再回表查整行数据。

那么很简单，第二条 SQL 没有使用索引的原因是因为 MySQL 计算使用索引下推过滤出数据后，查询二级索引+回表的查询成本大于全表扫描，所以 MySQL 选择全表扫描。

9. 类型不匹配 MySQL 自动转型导致索引失效

字符串不加单引号索引失效，亦或是数值类型加单引号索引失效，也就是第三点中提到的自动类型转换(也叫隐式转换)导致索引失效。

10. 少用 or 或 in ，用它查询时，MySQL 不一定使用索引

MySQL 内部优化器会根据检索比例、表大小等多个因素计算查询成本是否使用索引。

二、order by 和 group by 优化

1. order by 优化

在前面的文章中小二给大家介绍了索引的底层数据结构，知道了索引本身就是一种排好序的数据结构，所以排序优化最好的办法就是落实到索引上，这样查询出来的数据就已经排好序了，这种排序在 MySQL 中被称之为 Using Index，即覆盖索引。那么如果查询出来后的数据本身没有按所需字段排序，那么就会出现 Using filesort，即文件排序。

所以，我们要优化 order by，那么主要就是消灭低效的 Using filesort，建立合适的联合索引使用覆盖索引来排序。

Using filesort文件排序原理详解

Using filesort 分为单路排序和双路排序(又叫回表排序模式)。

• 单路排序：一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序；用 trace 工具可以看到 sort_mode 信息里显示 < sort_key, additional_fields > 或者 < sort_key, packed_additional_fields >
• 双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用 trace 工具可以看到sort_mode 信息里显示 < sort_key, rowid >

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

• 如果字段的总长度小于 max_length_for_sort_data ，那么使用单路排序模式
• 如果字段的总长度大于 max_length_for_sort_data ，那么使用双路排序模式

接下来使用 trace 工具来瞅瞅单路排序和双路排序：

-- 单路排序



mysql> set session optimizer_trace="enabled=on", end_markers_in_json=on;



 



mysql> select * from employees where name = 'itwxe' order by position;



 



mysql> select * from information_schema.OPTIMIZER_TRACE;



 



"join_execution": {  -- sql执行阶段



  "select#": 1,



  "steps": [



    {



      "filesort_information": [



        {



          "direction": "asc",



          "table": "`employees`",



          "field": "position"



        }



      ] /* filesort_information */,



      "filesort_priority_queue_optimization": {



        "usable": false,



        "cause": "not applicable (no LIMIT)"



      } /* filesort_priority_queue_optimization */,



      "filesort_execution": [



      ] /* filesort_execution */,



      "filesort_summary": {  -- 文件排序信息



        "rows": 1,  -- 预计扫描行数



        "examined_rows": 1,  -- 参与排序的行数



        "number_of_tmp_files": 0,  -- 使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的 磁盘文件排序



        "sort_buffer_size": 262056,  -- 排序缓存的大小，单位Byte



        "sort_mode": "<sort_key, packed_additional_fields>"  -- 排序方式，这里用的单路排序



      } /* filesort_summary */



    }



  ] /* steps */



} /* join_execution */



 



-- 双路排序



mysql> set max_length_for_sort_data = 10;  -- employees表所有字段长度总和哪怕一条记录也肯定大于10字节



 



mysql> select * from employees where name = 'itwxe' order by position;



 



mysql> select * from information_schema.OPTIMIZER_TRACE;



 



"join_execution": {



  "select#": 1,



  "steps": [



    {



      "filesort_information": [



        {



          "direction": "asc",



          "table": "`employees`",



          "field": "position"



        }



      ] /* filesort_information */,



      "filesort_priority_queue_optimization": {



        "usable": false,



        "cause": "not applicable (no LIMIT)"



      } /* filesort_priority_queue_optimization */,



      "filesort_execution": [



      ] /* filesort_execution */,



      "filesort_summary": {



        "rows": 1,



        "examined_rows": 1,



        "number_of_tmp_files": 0,



        "sort_buffer_size": 262136,



        "sort_mode": "<sort_key, rowid>"  -- 排序方式，这里用的双路排序



      } /* filesort_summary */



    }



  ] /* steps */



} /* join_execution */

单路排序的详细过程：

1. 从索引 name 找到第一个满足 name = ‘itwxe’ 条件的主键 id
2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
3. 从索引 name 找到下一个满足 name = ‘itwxe’ 条件的主键 id。
4. 重复步骤 2、3 直到不满足 name = ‘itwxe’
5. 对 sort_buffer 中的数据按照字段 position 进行排序
6. 返回结果给客户端

双路排序的详细过程：

1. 从索引 name 找到第一个满足 name = ‘itwxe’ 的主键id
2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
3. 从索引 name 取下一个满足 name = ‘itwxe’ 记录的主键 id
4. 重复 3、4 直到不满足 name = ‘itwxe’
5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出所有字段的值返回给客户端

总结：单路排序会将整行所有数据缓存到 sort buffer 中，双路排序只将主键id和排序字段放入到 sort buffer 中排序，在根据排序好的数据，从原来表中根据id查询数据返回给客户端。

如何选项单路排序还是多路排序？

MySQL 优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于 max_length_for_sort_data 变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加 max_length_for_sort_data 变量的大小。

不过需要注意，如果全部使用 sort_buffer 内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大 sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

2. group by 优化

group by 与 order by 很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于 group by 的优化如果不需要排序的可以加上 order by null 禁止排序。

三、分页查询优化

分页查询优化示例表仍为 employees。

select * from employees limit 90000,10;

很多时候我们业务系统实现分页功能可能会用如下 SQL 实现，看似 MySQL 是取90001行开始的10条记录，但是实际上 MySQL 在处理这个分页的时候是先读取前 90010 条记录，然后把前90000条记录舍弃，取出90001-90010的数据返回给客户端。因此如果要查询一张大表比较靠后的数据，执行效率是很低的。

1. 根据自增且连续的主键排序的分页查询

select * from employees where id > 90000 limit 10;

原理：根据主键索引 id 排除 <90000 的数据，取后10条数据避免全表扫描。

缺点：如果主键 id 不连续，或者中间有删除数据，则无法实现效果，所以通常使用下面的第二种方式。

2. 根据非主键字段排序的分页查询

select * from employees order by name limit 90000,10;



select * from employees ed_all inner join (select id from employees order by name limit 90000,10) ed_id on ed_all.id = ed_id.id;

可以看到查询相同的结果，但是非常大的查询速度差距，这还只是10w+的测试数据，加入是100w呢，差距得多大嘞！

阿里Java开发手册中也对这种情况进行了说明，非常推荐各位小伙伴们看下阿里Java开发手册，其中专门有一个章节对 MySQL 开发规范进行了说明。

四、join关联查询优化

-- 示例表



CREATE TABLE `t1` (



  `id` int(11) NOT NULL AUTO_INCREMENT,



  `a` int(11) DEFAULT NULL,



  `b` int(11) DEFAULT NULL,



  PRIMARY KEY (`id`),



  KEY `idx_a` (`a`)



) ENGINE=InnoDB DEFAULT CHARSET=utf8;



 



create table t2 like t1;



 



-- 往t1表插入1w行测试数据



drop procedure if exists insert_t1;



delimiter $$



create procedure insert_t1()



begin



  declare i int;



  set i = 1;



  while(i <= 10000)do



    insert into t1(a,b) values(i,i);



    set i = i + 1;                  



  end while;



end$$



delimiter ;



call insert_t1();



 



-- 往t2表插入100行测试数据



drop procedure if exists insert_t2;



delimiter $$



create procedure insert_t2()



begin



  declare i int;



  set i = 1;



  while(i <= 100)do



    insert into t2(a,b) values(i,i);



    set i = i + 1;                  



  end while;



end$$



delimiter ;



call insert_t2();

1. 表关联常见的两种算法

嵌套循环连接Nested-Loop Join(NLJ) 算法

NLJ 算法一次一行循环地从第一张表(驱动表)中读取行，在这行数据中取到关联字段，根据关联字段在另一张表(被驱动表)里取出满足条件的行，然后取出两张表的结果合集。

explain select * from t1 inner join t2 on t1.a = t2.a;

从执行计划可以看出，t2 作为驱动表，t1 作为被驱动表。先执行的就是驱动表(执行计划结果的 id如果一样则按从上到下顺序执行 SQL)，优化器通常会优先选择小表做驱动表，用 where 条件过滤完驱动表，然后再跟被驱动表做关联查询。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

• 当使用 left join 时，左表是驱动表，右表是被驱动表。
• 当使用 right join 时，右表时驱动表，左表是被驱动表。
• 当使用 inner join 时，MySQL 通常会选择数据量比较小表作为驱动表，大表作为被驱动表。

注意：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。而不是简单的比较两个表的总数据量。

上面 SQL 的大致流程如下：

• 从表 t2 中读取一行数据(如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据)。
• 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找。
• 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端。
• 重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了200 行。

如果连接查询的列上没有索引，NLJ 算法性能会比较低，那么 MySQL 则会选择 BNL 算法。

基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法

BNL 算法把驱动表的数据读入到 join_buffer(连接查询缓存) 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

explain select * from t1 inner join t2 on t1.b = t2.b;

Extra 中的 Using join buffer (Block Nested Loop) 说明该关联查询使用的是 BNL 算法。同时可以看到 t2 仍然作为驱动表，t1 作为被驱动表。

上面 SQL 的大致流程如下：

• 把 t2 的所有数据放入到 join_buffer 中。
• 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比。
• 返回满足 join 条件的数据。

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为 10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100万次。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。流程如下：

• 取驱动表的一部分数据放入 join_buffer，直至 join_buffer 放不了。(比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录)
• 扫描被驱动表的每一行数据，跟 join_buffer 中的数据做对比，满足 join 条件的数据作为结果集的一部分返回。(从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果)
• 清空 join_buffer。(清空 join_buffer)
• 继续读取驱动表剩下的数据，重复前三个步骤，一直驱动表的数据被扫描完。(再将 t2 表剩余200行记录记录放到 join_buffer 中，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以多扫了一次 t1 表和多扫描分段次数的 t2 表)

那么以这个分段放的例子来说，若驱动表的行数是 N，需要分 K 段才能扫描完，被驱动表的行数是 M，则扫描的行数是 N + K * M，即 1000 + 2 * 10000 = 21000 行；总的内存判断次数为 (800 + 200) * 10000 = 100万次。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 NLJ 算法呢？

假设没有索引的情况下选择 NLJ 算法，那么需要扫描的行数为 100 * 10000 = 100w次，但是这个确是磁盘扫描。

很显然，用 BNL 算法磁盘扫描次数少很多，并且相比磁盘扫描，BNL 的内存计算会快得多。

因此 MySQL 对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法；如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高。

2. 对于关联SQL的优化

看完了 NLJ 算法和 BLJ 算法，想必关联 SQL 的优化也有一些思路了。

• 超过三个表禁止 join，多表关联查询时，保证被关联的字段需要有索引，尽量选择 NLJ 算法，同时需要 join 的字段，数据类型必须绝对一致。
• 小表驱动大表，写多表连接 SQL 时如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去mysql优化器自己判断的时间。

straight_join：straight_join 功能同 inner join 类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定 MySQL 选择 t2 表作为驱动表。

• straight_join 只适用于 inner join，并不适用于 left join、right join。(因为 left join、right join 已经代表指定了表的执行顺序)
• 尽可能让优化器去判断，使用 straight_join 一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

五、in和exsits优化

原则：小表驱动大表。

in优化：当B表的数据集小于A表的数据集时，in 优于 exists。

select * from A where id in (select id from B);



# 等价于：



for(select id from B) {



    select * from A where A.id = B.id;



}

exsits优化：当A表的数据集小于B表的数据集时，exists 优于 in。

select * from A where exists (select 1 from B where B.id = A.id);



# 等价于：



for(select * from A) {



    select 1 from B where B.id = A.id;



}

• exists (subquery) 只返回 true 或 false，因此子查询中的 SELECT * 也可以用 SELECT 1 替换，官方说法是实际执行时会忽略 select 清单，因此没有区别。
• exists 子查询往往也可以用 join 来代替，怎么最优查询需要具体问题具体分析。

六、count(*)查询优化

1. count比较

-- 临时关闭MySQL查询缓存



set global query_cache_size=0;



set global query_cache_type=0;



 



-- count



explain select count(*) from employees;



explain select count(1) from employees;



explain select count(id) from employees;



explain select count(name) from employees;

小伙伴们可能都听过 DBA 或者某些博客建议不要使用 count(*) 来统计数据行数，但是实际上并不是这样的，可以发现上面四条 SQL 的执行计划是一毛一样的。

那么既然执行计划是一样的，那么说明这4条语句的执行效率差不多其实，甚至5.7版本后 count(*) 效率还更高。不过需要注意的是 count(name) 不会统计 name 为 null 的数据行。

• 字段有索引：count(*) ≈ count(1) > count(字段) > count(主键id) // 字段有索引，count(字段) 统计走二级索引，二级索引存储数据比主键索引少，所以 count(字段) > count(主键id)。
• 字段无索引：count(*) ≈ count(1)> count(主键id) > count(字段) // 字段没有索引，count(字段) 统计走不了索引，count(主键id) 还可以走主键索引，所以 count(主键id) > count(字段)
• count(1) 跟 count(字段) 执行过程类似，不过 count(1) 不需要取出字段统计，就用常量1做统计，count(字段) 还需要取出字段，所以理论上 count(1) 比 count(字段) 会快一点。
• count() 是例外，MySQL 并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用 count(列名)或 count(常量) 来替代 count()。

为什么对于 count(id)，MySQL 最终选择辅助索引而不是主键聚集索引？

因为二级索引相对主键索引存储数据更少，检索性能应该更高，MySQL5.7 版本内部做了点优化。

2. 常见优化方法

a. 查询MySQL自己维护的总行数

对于 MyISAM 存储引擎的表做不带 where 条件的 count 查询性能是很高的，因为 MyISAM 存储引擎的表的总行数会被 MySQL 存储在磁盘上，查询不需要计算。

select count(*) from test_myisam;



explain select count(*) from test_myisam;

可以看到执行计划表都没有查询。

b. show table status

如果只需要知道表总行数的估计值可以用 show table status like ‘employyees’; 查询，查询结果是个估计值。

c. 将总数维护到Redis里

插入或删除表数据行的时候同时维护 Redis 里的表总行数 key 的计数值(用 incr 或 decr 命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性。

d. 增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作。