慢SQL分析与优化

背景介绍

从系统设计角度看，一个系统从设计搭建到数据逐步增长，SQL执行效率可能会出现劣化，为继续支撑业务发展，我们需要对慢sql进行分析和优化，严峻的情况下甚至需要对整个系统进行重构。所以我们往往需要在系统设计前对业务进行充分调研、遵守系统设计规范，在系统运行时定期结合当前业务发展情况进行系统瓶颈的分析。

从数据库角度看，每个SQL执行都需要消耗一定I/O资源，SQL执行的快慢，决定了资源被占用时间的长短。假如有一条慢SQL占用了30%的资源共计1分钟。那么在这1分钟时间内，其他SQL能够分配的资源总量就是70%，如此循环，当资源分配完的时候，所有新的SQL执行将会排队等待。所以往往一条慢SQL会影响到整个业务。

本文仅讨论MySQL-innodb的情况。

优化方式

SQL语句执行效率的主要因素

数据量

SQL执行后返回给客户端的数据量的大小；

数据量越大需要扫描的I/O次数越多，数据库服务器的IO更容易成为瓶颈。

取数据的方式

数据在缓存中还是在磁盘上；

是否能够通过全局索引快速寻址；

是否结合谓词条件命中全局索引加速扫描。

数据加工的方式

排序、子查询、聚合、关联等，一般需要先把数据取到临时表中，再对数据进行加工；

对于数据量比较多的计算，会消耗大量计算节点的CPU资源，让数据加工变得更加缓慢；

是否选择了合适的join方式

优化思路

减少数据扫描（减少磁盘访问）

尽量在查询中加入一些可以提前过滤数据的谓词条件，比如按照时间过滤数据等，可以减少数据的扫描量，对查询更友好；

在扫描大表数据时是否可以命中索引，减少回表代价，避免全表扫描。

返回更少数据（减少网络传输或磁盘访问）

减少交互次数（减少网络传输）

将数据存放在更快的地方

某条查询涉及到大表，无法进一步优化，如果返回的数据量不大且变化频率不高但访问频率很高，此时应该考虑将返回的数据放在应用端的缓存当中或者Redis这样的缓存当中，以提高存取速度。

减少服务器CPU开销（减少CPU及内存开销）

避免大事务操作

利用更多资源（增加资源）

优化案例

1.数据分页优化

select * from table_demo where type = ?  limit ?,?;

优化方式一：偏移id

lastId = 0 or min(id)do {select * from table_demo where type = ? and id >{#lastId}  limit ?;lastId = max(id)} while (isNotEmpty)

优化方式二：分段查询

该方式较方式一的优点在于可并行查询，每个分段查询互不依赖；较方式一的缺点在于较依赖数据的连续性，若数据过于分散，代价较高。

minId = min(id) maxId = max(id)for(int i = minId; i<= maxId; i+=pageSize){select * from table_demo where type = ? and id between i and i+ pageSize;}

2.优化GROUP BY

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.

低效:

select job , avg(sal) from emp group by job having job = ‘president' or job = ‘manager'

高效:

 select job , avg(sal) from emp where job = ‘president' or job = ‘manager' group by job

3.范围查询

联合索引中如果有某个列存在范围（大于小于）查询，其右边的列是否还有意义？

explain select count(1) from bank_statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00' explain select * from bank_statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'  limit 0, 100explain select * from bank_statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'

（1）使用单键索引trade_date_time的情况下

从索引里找到所有trade_date_time在'2019-05-01' 到'2020-05-01' 区间的主键id。假设有100万个。

对这些 id 进行排序（为的是在下面一步回表操作中优化 I/O 操作，因为很多挨得近的主键可能一次磁盘 I/O 就都取到了）

回表，查出100万行记录，然后逐个扫描，筛选出org_code='1020'的行记录

（2）使用联合索引 trade_date_time, org_code

联合索引 trade_date_time, org_code底层结构推导如下：

以查找trade_date_time >='2019-05-01' and trade_date_time <='2020-05-01' and org_code='1020'为例：

在范围查找的时候,直接找到最大,最小的值,然后进行链表遍历，故仅能用到trade_date_time的索引，无法使用到org_code索引

基于Mysql5.6+的索引下推特性，虽然org_code字段无法使用到索引树，但是可以用于过滤回表的主键id数。

小结：对于该case, 索引效果[org_code,trade_date_time] > [trade_date_time, org_code]>[trade_date_time]。 实际业务场景中，检索条件中trade_date_time基本上肯定会出现，但org_code却不一定，故索引的设计还需要结合实际业务需求。

优化Order by

索引：

PRIMARY KEY (`id`),  UNIQUE KEY `uniq_code` (`unique_code`) USING BTREE,  KEY `idx_account_trade_date_time` (`account_number`,`trade_date_time`),  KEY `idx_trade_date_times` (`trade_date_time`)

慢sql:

SELECT  id,....,creator,modifier,create_time,update_time  FROM statementWHERE (account_number = 'XXX' AND create_time >= '2022-04-24 06:03:44' AND create_time <= '2022-04-24 08:03:44' AND money_way = 'C') ORDER BY trade_date_time DESC,id DESC LIMIT 0,1000;

优化前：SQL 执行超时被 kill 了

SELECT  id,....,creator,modifier,create_time,update_time  FROM statementWHERE (account_number = 'XXX' AND create_time >= '2022-04-24 06:03:44' AND create_time <= '2022-04-24 08:03:44' AND money_way = 'C') ORDER BY create_time DESC,id DESC LIMIT 0,1000;

优化后：执行总行数为:6行，耗时34ms。

mysql使不使用索引与所查列无关，只与索引本身，where条件，order by 字段，group by 字段有关。索引的作用一个是查找，一个是排序。

业务拆分

select * from order where status='S' and update_time < now-5min  limit 500

拆分优化：

随着业务数据的增长status='S'的数据基本占据数据的90%以上，此时该条件无法走索引。我们可以结合业务特征，对数据获取按日期进行拆分。

lastId = 0 or min(id)do {select * from table_demo where type = ? and id >{#lastId}  limit ?;lastId = max(id)} while (isNotEmpty)0

数据库结构优化

1）范式优化：表的设计合理化（符合3NF），比如消除冗余（节省空间）；

2）反范式优化：比如适当加冗余等（减少join）

3）拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的表可采取此方法，可按月建表分区。

SQL语句优化

SQL检查状态及分数计算逻辑

尽量避免使用子查询

用IN来替换OR

读取适当的记录LIMIT M,N，而不要读多余的记录

禁止不必要的Order By排序

总和查询可以禁止排重用union all

避免随机取记录

将多次插入换成批量Insert插入

只返回必要的列，用具体的字段列表代替 select * 语句

区分in和exists

优化Group By语句

尽量使用数字型字段

优化Join语句

大表优化

分库分表（水平、垂直）

读写分离

数据定期归档

原理剖析

mysql逻辑架构图：

索引的优缺点

优点

提高查询语句的执行效率，减少 IO 操作的次数

创建唯一性索引，可以保证数据库表中每一行数据的唯一性

加了索引的列会进行排序，在使用分组和排序子句进行查询时，可以显著减少查询中分组和排序的时间

缺点

索引需要占物理空间

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

当对表中的数据进行增删改查时，索引也要动态的维护，这样就降低了数据的更新效率

索引的数据结构

主键索引

普通索引

组合索引

索引页结构

索引页由七部分组成，其中Infimum和Supremum也属于记录，只不过是虚拟记录，这里为了与用户记录区分开，还是决定将两者拆开。

数据行格式：

mysql有4种存储格式：

1）Compact

Redundant （5.0版本以前用，已废弃）

Dynamic （mysql5.7默认格式）

Compressed

Dynamic行存储格式下，对于处理行溢出（当一个字段存储长度过大时，会发生行溢出）时，仅存放溢出页内存地址。

索引的设计原则

哪些情况适合建索引

数据又数值有唯一性的限制

频繁作为where条件的字段

经常使用group by 和order by的字段，既有group by 又有order by的字段时，建议建联合索引

经常作为update或delete条件的字段

经常需要distinct的字段

多表连接时的字段建议创建索引，也有注意事项

连接表数量最好不要超过3张，每增加一张表就相当于增加了一次嵌套循环，数量级增长会非常快

对多表查询时的where条件创建索引

对连接字段创建索引，并且数据类型保持一致

在确定数据范围的情况下尽量使用数据类型较小的，因为索引会也会占用空间

对字符串创建索引时建议使用字符串的前缀作为索引

这样做的好处是：1. 能节省索引的空间，2.虽然不能精确定位，但是能够定位到相同的前缀，然后通过主键查询完整的字符串，这样既能节省空间，又减少了字符串的比较时间，还能解决排序问题。

区分度高（散列性高）的字段适合作为索引。

在多个字段需要创建索引的情况下，联合索引优先于单值索引。使用最频繁的列作为索引的最左侧 。

哪些情况下不需要使用索引

在where条件中用不到的字段不需要。

数据量小的不需要建索引，比如数据少于1000条。

由大量重复数据的列上不要建索引，比如性别字段中只有男和女时。

避免在经常更新的表或字段中创建过多的索引。

不建议主键使用无序的值作为索引，比如uuid。

不要定义冗余或重复的索引

例如：已经创建了联合索引key(id,name)后就不需要再单独建一个key(id)的索引

索引优化之MRR

例如有一张表user，主键id，普通字段age，为age创建非聚集索引，有一条查询语句select user from table where age > 18;(注意查询语句中的结果是)

在Mysql5.5以及之前的版本中如何查询呢？先通过非聚集索引查询到age>18的第一条数据，获取到了主键id；然后根据非聚集索引中的叶子节点存储的主键id去聚集索引中查询行数据；根据age>18的数据条数每次查询聚集索引，这个过程叫做回表。

上述的步骤有什么缺点呢？如何age>18的数据非常多，那么每次回表都需要经过3次IO(假设B+树的高度是3)，那么会导致查询效率过低。

在Mysql5.6时针对上述问题进行了优化，优化器先查询到age>3的所有数据的主键id，对所有主键的id进行排序，排序的结果缓存到read_rnd_buffer，然后通过排好序的主键在聚簇索引中进行查询

如果两个主键的范围相近，在同一个数据页中就可以之间按照顺序获取，那么磁盘io的过程将会大大降低。这个优化的过程就叫做Multi Range Read(MRR) 多返回查询。

索引下推

假设有索引(name, age), 执行sql: select * from tuser where name like '张%' and age=10;

MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到，由于联合索引中包含列，所以存储引擎直接在联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

索引下推使用条件

只能用于range、 ref、 eq_ref、ref_or_null访问方法；

只能用于InnoDB和 MyISAM存储引擎及其分区表；

对存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

引用了子查询的条件不能下推；

引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

思考：

mysql一张表到底能存多少数据？

为什么要控制单行数据大小?

优化案例4中优化前的sql为什么走不到索引？

总结

抛开数据库硬件层面，数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都对SQL执行效率有影响，我们只有在整个设计、开发、运维阶段保持高度敏感、追求极致，才能让我们系统的可用性、伸缩性不会随着业务增长而劣化。

附-执行计划分析

id SELECT识别符。这是SELECT的查询序列号1. id相同时，执行顺序由上至下2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行-- 查看在研发部并且名字以Jef开头的员工，经典查询 explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部'; select_type 示查询中每个select子句的类型(1) SIMPLE(简单SELECT，不使用UNION或子查询等)(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)(3) UNION(UNION中的第二个或后面的SELECT语句)(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)(8) DERIVED(派生表的SELECT, FROM子句的子查询)(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行) table 显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称 type 对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行index: Full Index Scan，index与ALL区别为index类型只遍历索引树range:只检索给定范围的行，使用一个索引来选择行ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用systemNULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。 possible_keys 指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询 Key key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。 key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好 ref 列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 rows 算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数 Extra 该列包含MySQL解决查询的详细信息,有以下几种情况：Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order byUsing filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”```

-- 测试Extra的filesort explain select * from emp order by name; **Using join buffer**：该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。**Impossible where**：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。**Select tables optimized away**：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行**No tables used**：Query语句中使用from dual 或不含任何from子句 -- explain select now() from dual;

lastId = 0 or min(id)do {select * from table_demo where type = ? and id >{#lastId}  limit ?;lastId = max(id)} while (isNotEmpty)1

原文：https://juejin.cn/post/7101581835527782414