慢 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 及内存开销）

• 避免大事务操作

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

优化案例

数据分页优化

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

优化方式一：偏移 id

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

优化方式二：分段查询

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

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;
}
1
2
3
4

优化 GROUP BY

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

低效:

select?job?,?avg(sal)?from?table_demo?group?by?job?having??job?=?‘manager'
1

高效:

select?job?,?avg(sal)?from?table_demo?where??job?=?‘manager'?group?by?job
1

范围查询

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

explain?select?count(1)?from?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?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,?100
explain?select?*?from?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
2
3

• 使用单键索引 trade_date_time 的情况下

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

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

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

• 使用联合索引 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’为例：

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

2. 基于 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

索引：

KEY?`idx_account_trade_date_time`?(`account_number`,`trade_date_time`),
??KEY?`idx_trade_date_times`?(`trade_date_time`)
??KEY?`idx_createtime`?(`create_time`),
1
2
3

慢 SQL:

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

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

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

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

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

业务拆分

select?*?from?order?where?status='S'?and?update_time?

date?=?now;?minDate?=?now?-?10?days
while(date?>?minDate)?{
select?*?from?order?where?order_date={#date}?and?status='S'?and?update_time?