MySQL慢查询优化思想总结

微信公众号：molashaonian

1.慢查一

• 索引情况
  PRIMARY KEY (id),
  INDEX idx_status_ct(status, created_time)
• 优化前

SELECT *
FROM rop
WHERE `status` IN (0, 3)
    AND is_delete = 0
LIMIT 112000, 1000

显然，这条SQL之所以慢是因为分页查询，到后面需要跳过的数据太多了，而且 status 状态的区分的也不是很高，所以越往后查询就越慢

• 优化后

SELECT *
FROM rop
WHERE `status` IN (0, 3)
    AND is_delete = 0
    AND created_time >= '2020-05-13 18:31:35.0'
    AND id > 17735072
LIMIT 1000

优化点：
a. 对于这种分页慢查，一般采取分页得到的最后一条数据的自增ID作为查询条件来替代 offset，避免跳过数据；
b. 同时时间范围的比较，创建时间 created_time 等同于 自增ID，而 rop 表刚好有联合索引 (status, created_time) ，所以此处还增加 created_time 作为查询条件，使得 id 字段不需要 索引下推 就能过滤出来，直接通过联合索引过滤出需要回表的数据，大大减少了回表的数据量

2.慢查二

• 索引情况
  PRIMARY KEY (id),
  INDEX idx_pc_code_status(pc_code, status) ,
  INDEX idx_last_update_time(last_update_time)
• 字段备注
  is_done 是否完成 1 是 0 否
• 优化前

SELECT *
FROM apc
WHERE pc_code = 'H11067'
  AND is_delete = 0
  AND is_done = 0
  AND STATUS <> 64

从索引，SQL来看这条语句只能走到联合索引 idx_pc_code_status 的第一节点索引；status 范围查询索引失效

• 优化后

SELECT MAX(id)
FROM apc
WHERE last_update_time = '2020-05-13 18:31:35';

SELECT *
FROM apc
WHERE pc_code = 'H11067'
  AND is_delete = 0
  AND is_done = 0
  AND status IN (65, 71, 73, 72, 74, 76, 75, 81, 82, 85, 86, 87, 88, 89, 90, 91)
  AND id > 19331461

优化点：
a. 首先业务场景同一个 pc_code 下 is_done 为非完成的 肯定是只有一个，所以先通过 Redis 缓存上一次该 pc_code 完成的最后 last_update_time，id； 当然第一次查询肯定还没缓存，所以 id 作为查询条件得先判空，但是之后的查询就可以获得上一次的 id，并且把最新查询获取的 id 更新到缓存中，这里还需注意 MAX(id) 可能为 null，这里可以重试多几个时间节点就可以了，第一个SQL走索引 (last_update_time) 不用回表；
b. status <> 64，不等于 64，但是范围查询索引失效，所以就替换成 IN (64以外的其他状态)，加上 id 这样就可以把索引 (pc_code, status) 用到极致

3.慢查三

• 索引情况
  PRIMARY KEY (id),
  INDEX idx_pc_code(pc_code)
• 优化前

SELECT *
FROM apc
WHERE pc_code = 'H19941'
  AND is_delete = 0
ORDER BY create_time DESC
LIMIT 1

该SQL走到索引 idx_pc_code，由于进行了 ORDER BY create_time，导致了文件排序 Using filesort

• 优化后

SELECT MAX(id)
FROM apc
WHERE (pc_code = 'H19941' AND is_delete = 0);

SELECT *
FROM apc
WHERE id = 19448001

优化点：a. 先根据条件查询出 MAX(id)，再根据 id 查询数据，规避文件排序

4.慢查四

• 索引情况
  PRIMARY KEY (id),
  KEY idx_pno (pno),
  KEY idx_create_time (create_time),
  KEY idx_aid (aid),
  KEY idx_flo (flo)
• 优化前

SELECT MIN(pe.pno)
FROM ape pe
WHERE pe.create_time >= '2020-03-06 00:00:00'
  AND pe.create_time <= '2020-03-08 23:59:59'
  AND pe.aid = 50
  AND pe.flo = '1'
  AND pe.is_delete = 0

该SQL的查询条件涉及到三个单列索引，create_time，aid，flo，这里边有可能走到索引情况是 create_time 或者 aid 与 flo 的索引合并 index merge

• 优化后

SELECT MIN(pe.id), MAX(pe.id)
FROM ape pe
WHERE pe.create_time >= '2020-03-06 00:00:00'
  AND pe.create_time <= '2020-03-08 23:59:59'

SELECT MIN(pe.pno)
FROM ape pe
WHERE pe.create_time >= '2020-03-06 00:00:00'
  AND pe.create_time <= '2020-03-08 23:59:59'
  AND pe.aid = 50
  AND pe.flo = '1'
  AND pe.is_delete = 0
  AND pe.id >= minId AND pe.id <= maxId

优化点：a. 先根据 create_time 查询出 MIN(id)，MAX(id)；再把 minId, maxId 作为查询条件，使得 索引合并（aid & flo）后还能再通过 id 过滤减少回表的数据；
当然它还是有可能走 create_time 索引，这就看走哪个索引的区分度更高，选择更优的索引。

5.慢查五

• 索引情况
  PRIMARY KEY (id),
  INDEX idx_wc_sw(wc, sw)
• 优化前

SELECT MIN(create_time)
FROM apcd
WHERE sw IN (1, 2, 3, 27, 30)
  AND wc IN (
    'S0887', 
    'S0888', 
    'S0889', 
    'S1095'
  )
GROUP BY wc, sw

该SQL的查询条件，group by 都使用了联合索引 idx_wc_sw，但查询的是 MIN(create_time)，所以还是要回表

• 优化后

SELECT MIN(id)
FROM apcd
WHERE sw IN (1, 2, 3, 27, 30)
  AND wc IN (
    'S0887', 
    'S0888', 
    'S0889', 
    'S1095'
  )
GROUP BY wc, sw;

SELECT create_time WHERE id IN(minId1,minId2,minId3)

优化点：a. 因为 create_time 等同于 自增ID，所以先根据联合索引 idx_wc_sw 查询出 MIN(id)，无需回表；再根据这些 minId 查询出 create_time

补充总结

1. 不改变取数逻辑的前提下，增加查询条件使得走到区分度更高的索引
2. order by xx asc limit 1 替换成 min(xx)，order by xx desc limit 1 替换成 max(xx)，规避文件排序
3. count(0) 通过 group by xx 分组统计总数的，可以替换成 count(distinct xx)，规避临时表
4. join 表关联，可以改变驱动表，以查询条件区分度更高的表作为主表驱动，而不是看表的数据量大小定义"小表驱动大表"
5. 等同效果的查询条件字段是否可以替换，使得索引更优
6. 缩小查询的范围，IN 的量减少，之后程序上做聚合，当然这并不是优化SQL和索引，它还是没解决慢的问题，只是每次查询的区分度提高了一点
7. 业务层面上增加条件限制，以适应现有的索引
8. 已经没有优化空间，业务层面也无法修改，那就考虑增加索引；新建索引特别是联合索引，需结合实际业务建立可用性高，区分度高的索引
9. 表太大，加索引也加不动了，或者加了效果也明显，那就考虑归档，分库分表

推荐阅读

Java 全栈知识体系

下篇笔录

ThreadPoolExecutor

molashaonian