搞定这5大优化思路+10个案例，哪里还有慢SQL？ - 网站源码_资源分享

一、慢SQL强化路子

1、慢查阅笔记历史记录慢SQL

怎样功能定位慢SQL呢、他们能透过慢查阅笔记来查阅慢SQL。预设的情况下呢，MySQL资料库是不迈入慢查阅笔记（slow query log）呢。因此他们需要全自动把它关上。

查阅下慢查阅笔记实用性，他们能采用show variables like slow_query_log%指示，如下表所示：

slow query log则表示慢查阅迈入的状况slow_query_log_file则表示慢查阅笔记放置的边线

他们还能采用show variables like long_query_time指示，查阅少于啥天数，才历史记录到慢查阅笔记，如下表所示：

long_query_time则表示查阅少于啥秒才历史记录到慢查阅笔记。

2、explain查阅分析SQL的执行计划

当功能定位出查阅效率低的SQL后，能采用explain查阅SQL的执行计划。

当explain与SQL一起采用时，MySQL将显示来自强化器的有关语句执行计划的信息。即MySQL解释了它将怎样处理该语句，包括有关怎样连接表以及以何种顺序连接表等信息。

一条简单SQL，采用了explain的效果如下表所示：

1）type

type则表示连接类型，查阅索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：这种类型要求资料库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。const：透过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。eq_ref：常用于主键或唯一索引扫描，一般指采用主键的关联查阅ref : 常用于非主键和唯一索引扫描。ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行index_merge：采用了索引合并强化方法，查阅采用了两个以上的索引。unique_subquery：类似于eq_ref，条件用了in子查阅index_subquery：区别于unique_subquery，用于非唯一索引，能返回重复值。range：常用于范围查阅，比如：between … and 或 In 等操作index：全索引扫描ALL：全表扫描

2）rows

该列则表示MySQL估算要找到他们所需的历史记录，须要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值。

3）filtered

该列是一个百分比的值，表里符合条件的历史记录数的百分比。简单点说，这个字段则表示存储引擎返回的数据在经过过滤后，剩下满足条件的历史记录数量的比例。

4）extra

该字段包含有关MySQL怎样解析查阅的其他信息，它一般会出现这几个值：

Using filesort：则表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句Using index ：则表示是否用了覆盖索引。Using temporary: 则表示是否采用了临时表,性能特别差，须要重点强化。一般多见于group by语句，或者union语句。Using where : 则表示采用了where条件过滤.Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

5）key

该列则表示实际用到的索引。一般配合possible_keys列一起看。

3、profile 分析执行耗时

explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状况及消耗的天数，须要采用profiling。迈入profiling参数后，后续执行的SQL语句都会历史记录其资源开销，包括IO，上下文切换，CPU，内存等等，他们能根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行强化。

profiling预设是关闭，他们能采用show variables like %profil%查阅是否迈入，如下表所示：

能采用set profiling=ON迈入。迈入后，能运行几条SQL，然后采用show profiles查阅一下。

show profiles会显示最近发给服务器的多条语句，条数由变量profiling_history_size定义，预设是15。如果他们须要看单独某条SQL的分析，能show profile查阅最近一条SQL的分析，也能采用show profile for query id（其中id就是show profiles中的QUERY_ID）查阅具体一条的SQL语句分析。

除了查阅profile ，还能查阅cpu和io，如上图。

4、Optimizer Trace分析详情

profile只能查阅到SQL的执行耗时，但是无法看到SQL真正执行的过程信息，即不知道MySQL强化器是怎样选择执行计划。这时候，他们能采用Optimizer Trace，它能跟踪执行语句的解析强化执行的全过程。

他们能采用set optimizer_trace=”enabled=on”关上开关，接着执行要跟踪的SQL，最后执行select * from

information_schema.optimizer_trace跟踪，如下表所示：

大家能查阅分析其执行树，会包括三个阶段：

join_preparation：准备阶段join_optimization：分析阶段join_execution：执行阶段

5、确定问题并采用相应的措施

最后确认问题，就采取对应的措施。

多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，他们能强化索引他们还能强化SQL语句，比如一些in元素过多问题（分批），深分页问题（基于上一次数据过滤等），进行天数分段查阅SQl没办法很好强化，能改用ES的方式，或者数仓如果单表数据量过大导致慢查阅，则能考虑分库分表如果资料库在刷脏页导致慢查阅，考虑是否能强化一些参数，跟DBA讨论强化方案如果存量数据量太大，考虑是否能让部分数据归档

二、慢查阅经典事例分析

事例1：隐式转换

他们创建一个用户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+树的普通索引，如果查阅条件传了一个数字过去，会导致索引失效。如下表所示：

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

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

事例2：最左匹配

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=捡田螺的小男孩;

因为查阅条件列name不是联合索引idx_userid_name中的第一个列，不满足最左匹配原则，因此索引不生效。在联合索引中，只有查阅条件满足最左匹配原则时，索引才正常生效。如下表所示，查询条件列是user_id

事例3：深分页问题

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

limit深分页为什么会变慢呢？ 假设有表结构如下表所示：

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), KEYidx_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的执行流程酱紫：

透过普通二级索引树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 扫描更多的行数，也意味着回表更多的次数。

怎样强化深分页问题？

他们可以透过减少回表次数来强化。一般有标签历史记录法和延迟关联法。

1）标签历史记录法

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

假设上一次历史记录到100000，则SQL能修改为：

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

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

2）延迟关联法

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

selectacct1.id,acct1.name,acct1.balanceFROM 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元素一般建议不要少于200个，如果少于了，建议分组，每次200一组进行哈。

反例：

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

如果他们对in的条件不做任何限制的话，该查阅语句一次性可能会查阅出非常多的数据，很容易导致接口超时。尤其有时候，他们是用的子查阅，in后面的子查阅，你都不知道数量有啥那种，更容易采坑。如下表所示这种子查阅：

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

如果type = 1有1一千，甚至上万个呢？肯定是慢SQL。索引一般建议分批进行，一次200个，比如：

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

in查阅为什么慢呢？

这是因为in查阅在MySQL底层是透过n*m的方式去搜索，类似union。

in查阅dive_limit)，5.6之后少于这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。预设是200，即in条件少于了200个数据，会导致in的代价计算存在问题，可能会导致Mysql选择的索引不准确。

事例5：order by 走文件排序

如果order by 采用到文件排序，则会可能会产生慢查阅。他们来看下下面这个SQL：

select name,age,city fromstaffwhere 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 wherecity =深圳 order by age limit 10;MySQL为对应的线程初始化sort_buffer，放入须要排序的age字段，以及主键id；从索引树idx_city，找到第一个满足 city=深圳’条件的主键id,假设id为X；到主键id索引树拿到id=X的这一行数据，取age和主键id的值，存到sort_buffer；从索引树idx_city拿到下一个历史记录的主键id，假设id=Y；重复步骤 3、4 直到city的值不等于深圳为止；前面5步已经查找到了所有city为深圳的数据，在sort_buffer中，将所有数据根据age进行排序；遍历排序结果，取前10行，并按照id的值回到原表中，取出city、name 和 age三个字段返回给客户端。

2）全字段排序

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

select name,age,city from staff where city = 深圳 order byagelimit 10;MySQL 为对应的线程初始化sort_buffer，放入须要查阅的name、age、city字段；从索引树idx_city，找到第一个满足 city=深圳’条件的主键 id，假设找到id=X；到主键id索引树拿到id=X的这一行数据，取name、age、city三个字段的值，存到sort_buffer；从索引树idx_city 拿到下一个历史记录的主键id，假设id=Y；重复步骤 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等参数强化。

事例6：索引字段上采用is null， is not null

表结构：

CREATE TABLE `user` ( `id` int(11) NOT NULLAUTO_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,要注意一下哈，因为这个可能因为数据量问题，导致索引无效。

事例7：索引字段上采用（！= 或者 < >）

假设有表结构：

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的时候，留点心眼哈。

事例8：关联的字段编码格式不一样

新建两个表，一个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, PRIMARYKEY (`id`), KEY `idx_name` (`name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

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

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

事例9：group by采用临时表

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

1）group by执行流程

假设有表结构：

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;

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

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

select city ,count(*) as num from staff group by city;创建内存临时表，表里有两个字段city和num；全表扫描staff的历史记录，依次取出city = X的历史记录。

a. 判断临时表中是否有为city=X的行，没有就插入一个历史记录 (X,1);

b. 如果临时表中有city=X的行，就将X这一行的num值加 1；

遍历完成后，再根据字段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

案例10：delete + in子查阅

之前见到过一个生产慢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`datetimeNOT 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=REDUNDANTCOMMENT=老的账户表; 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=REDUNDANTCOMMENT=账户表;

执行的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却没有对它做这个强化。

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

>>>>

参考资料

慢SQL强化一点小路子https://juejin.cn/post/7048974570228809741#heading-7SQL强化万能公式：5 大步骤 + 10 个事例https://developer.aliyun.com/article/980780

作者丨捡田螺的小男孩

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：[email protected]

11月9日晚8点，eBay运维经理-杨胜辉老师将带大家解决网站运维复杂度高且风险度高、运维工作长尾化且碎片化这一核心矛盾，并将运维变更工作引向良性循环；沉淀运维经验至自动化系统，在提高通用化能力和效率的同时，怎样减少自动化运维带来的风险；突破“自动化开发的速度永远赶不上新任务出现的速度”怪圈，制定自动化和安全保障机制，并透过基础架构强化提升高可靠能力。

直播地址：http://z-mz.cn/5tTK7