微信公众号:molashaonian
1.慢查一
- 索引情况
PRIMARY KEY (id
),
INDEXidx_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
),
INDEXidx_pc_code_status
(pc_code
,status
) ,
INDEXidx_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
),
INDEXidx_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
),
KEYidx_pno
(pno
),
KEYidx_create_time
(create_time
),
KEYidx_aid
(aid
),
KEYidx_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
),
INDEXidx_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
补充总结
- 不改变取数逻辑的前提下,增加查询条件使得走到区分度更高的索引
- order by xx asc limit 1 替换成 min(xx),order by xx desc limit 1 替换成 max(xx),规避文件排序
- count(0) 通过 group by xx 分组统计总数的,可以替换成 count(distinct xx),规避临时表
- join 表关联,可以改变驱动表,以查询条件区分度更高的表作为主表驱动,而不是看表的数据量大小定义"小表驱动大表"
- 等同效果的查询条件字段是否可以替换,使得索引更优
- 缩小查询的范围,IN 的量减少,之后程序上做聚合,当然这并不是优化SQL和索引,它还是没解决慢的问题,只是每次查询的区分度提高了一点
- 业务层面上增加条件限制,以适应现有的索引
- 已经没有优化空间,业务层面也无法修改,那就考虑增加索引;新建索引特别是联合索引,需结合实际业务建立可用性高,区分度高的索引
- 表太大,加索引也加不动了,或者加了效果也明显,那就考虑归档,分库分表
推荐阅读
下篇笔录
ThreadPoolExecutor
molashaonian