【踩坑】工作中真实踩坑，一个or让sql变慢7倍

以下问题均在工作中, 在生产环境发现的问题, 可能有些看起来是常识但是确实出现了而且还不少,因此将其记录下来

最新更新时间 2024年3月15日

一.or导致问题

1. 背景

1. 测试环境，有两张表，分别是讲师表t_train_lecturer(后面简称B表）,和讲师的授课时长表t_train_activity（后面简称A表）
2. B表有1w多条数据，A表10w条数据，但是A是个混合表，其中和B有关系的数据也是1w左右
3. B表讲师分为内部和外部的讲师(坑就坑在这了），在A表中用lecturer_source的0和1表示，其中内部则关联lecturer_num工号，外部则关联telephone_num手机号.
4. A表lecturer_id,B表的lecturer_num，telephone_num均为索引

于是就有了这么一条sql，分组聚合查出讲师的授课时长：

select
	a.lecturer_id,
	sum(a.course_hour) totalCourseHour
from
	t_train_lecturer l
left join t_train_activity a on
	a.lecturer_id is not null
	-- 内部
	and ((a.lecturer_id = l.lecturer_num
		and a.lecturer_source = 0)
    -- 外部		
	or (a.lecturer_id = l.telphone_num
		and a.lecturer_source = 1))
where
	l.delete_flag = 0
group by
	a.lecturer_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

如上，我在left join的条件中用or将内部外部一起查，执行，结果sql达到10s

2.解释计划

解释计划如下： 可以看到，两个表全表扫描了

再用dbeaver的解释计划看执行成本

3.or改成union

为了避免其他地方改动的影响，这里就单单将or的条件拆成两个sql后union

select
	a.lecturer_id,
	sum(a.course_hour) totalCourseHour
from
	t_train_lecturer l
left join t_train_activity a on
	 a.lecturer_id is not null
	 -- 内部
	and (a.lecturer_id = l.lecturer_num and a.lecturer_source = 0)
where
	l.delete_flag = 0
group by
	a.lecturer_id

union all

select
	a.lecturer_id,
	sum(a.course_hour) totalCourseHour
from
	t_train_lecturer l
left join t_train_activity a on
	a.lecturer_id is not null
	-- 外部
	and (a.lecturer_id = l.telphone_num and a.lecturer_source = 1)
where
	l.delete_flag = 0
group by
	a.lecturer_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

执行耗时： 100ms
解释计划： 可以看到用到了A表的key，因为B有个筛选条件delete_flag=0,这个字段没有索引, 所以B表是全表

查看dbeaver的执行成本

4.总结

虽然一直都知道or会让索引失效，但是真正遇到的时候才发现可怕之处，这才测试环境1w到10w的数据，就已经是10s的查询时间，正式环境可想而知。

当然最终的sql肯定也不是100ms， 因为只是需要讲师和它的时长，比如下面这个，先单独聚合A表得到时长，再分别通过exist来关联内部和外部B表数据筛选

select
	lecturer_id,
	sum(course_hour)
from
	t_train_activity a
where
	lecturer_id is not null
	and lecturer_source = 0
	and exists (
	select
		1
	from
		t_train_lecturer l
	where
		l.delete_flag = 0
		and l.lecturer_num = a.lecturer_id)
group by
	lecturer_id
union
select
	lecturer_id,
	sum(course_hour)
from
	t_train_activity a
where
	lecturer_id is not null
	and lecturer_source = 1
	and exists (
	select
		1
	from
		t_train_lecturer l
	where
		l.delete_flag = 0
		and l.telphone_num = a.lecturer_id)
group by
	lecturer_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

执行时间20ms

二. update中子查询导致效率很慢

项目中有一个需要绑定和移除学员的功能, 添加没啥问题, 但是移除却很慢经常要几十秒

这个问题出现很久, 但是由于优先级没那么高, 以为是单纯的数据量太大导致的. 捞出sql看了下也没啥事问题:
大概是这样:
update 表A set delete_flag = 1 where user_id in (select user_id from 表B where xxx)

因为update 是不能explain的, 所以转换成select来查, select * from 表A where user_id in (select user_id from 表B where xxx) 结果速度很快, explain显示也用到了索引.

问题所在

后面查了一些资料, 发现update的语句有个问题, 那就是当子查询结果为空的时候, 即 user_id in (null) 后, 会导致类似索引失效的效果, 数据越大执行时间越久.

解决方法

1. 把子查询in 可能查不到的情况, 拆到代码里分成两步去执行. 加个判断, 如果子查询没有值就没必要执行update了
2. 或者在子查询加一层嵌套虚拟表. 比如: select * from 表A where user_id in (select * from (select user_id from 表B where xxx) t)
3. 如果字段是null. 看看能不能设置为not null, 或者where条件加上not null判断

其次就算result返回不是空, 本身这种写法也不是最优解, 可以考虑使用join替代子查询

查了下mysql文档, 有一些说法可能和这个相关 https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization-with-exists.html:

当优化器使用触发条件创建某种基于索引查找的访问时（对于前面列表的前两项），它必须针对条件关闭时的情况制定回退策略。这种后备策略始终相同：进行全表扫描。在 EXPLAIN输出中，后备显示如列Full scan on NULL key中 所示Extra：
使用触发条件会对性能产生一些影响。表达式NULL IN (SELECT …) 现在可能会导致全表扫描（速度很慢），而以前不会。这是为正确结果付出的代价（触发条件策略的目标是提高合规性，而不是速度）。
对于多表子查询， 的执行NULL IN (SELECT …)特别慢，因为连接优化器不会针对外部表达式为 的情况进行优化NULL。它假设NULL左侧的子查询评估非常罕见，即使有统计数据表明情况并非如此。另一方面，如果外部表达式可能是 NULL但实际上从未是，则不会造成性能损失。
为了帮助查询优化器更好地执行查询，请使用以下建议：
声明一个列，就NOT NULL好像它确实是一样。通过简化色谱柱的条件测试，这也有助于优化器的其他方面。
如果您不需要NULL区 FALSE分子查询结果，则可以轻松避免缓慢的执行路径。替换如下所示的比较：
outer_expr [NOT] IN (SELECT inner_expr FROM ...)
用这个表达式：
(outer_expr IS NOT NULL) AND (outer_expr [NOT] IN (SELECT inner_expr FROM ...))
thenNULL IN (SELECT …)永远不会被计算，因为 AND一旦表达式结果明确，MySQL 就会停止计算各部分。
另一种可能的重写：
[NOT] EXISTS (SELECT inner_expr FROM ... WHERE inner_expr=outer_expr)

三. 索引失效

这个是老生常谈的问题了, 就不一一列举了, 只讲工作中遇到的情况

a.类型转换

我们都知道索引查询的时候, 对查询字段(不是查询的值)使用函数会索引失效, 也知道类型转换会, 但是这种情况在工作中发现了:

有一个配置表, 为了兼容更多场景, type字段设置成了varchar类型, 但是存储的时候一开始是存储的是数字
select * from t_config where type = 3, 这样确实能查到数据, 但是发现要几百毫秒. explain一看果然没有用到索引. 乖乖的改成字符串:
select * from t_config where type = '3', 同理, 这种情况对于工号字段是字符串, 直接查询数字工号也会出现

四. 查询刺客

明明是一个很简单的查询, 也大概清楚查询的表的数量, 结果一查直接差点数据库搞崩, 真实的案例:

4.1 笛卡尔积的子查询(别名的重要性)

收到运维的告警, 说数据库在某个时间点的磁盘占用和io一直在飙升, 具体怎么回事呢

已知A表是个千万级别的大表, B表是几w级别的小表
select * from A where bid in (select bid from B where 条件)
看上去平平无奇, 结果后台执行了一个多小时还没执行完
解释执行计划一看:
两全表查询, joining后放到buffer里, 然后嵌套查询, 每查询一次A表都去查询一下B表, 这也就不奇怪为啥数据库直接告警了

这和预期中不一样, 应该是先先执行子查询语句, 得到结果后作为in的条件查询外表啊

问题出在bid in (select bid, 这个bid字段是A表的, 但是我们想要取的B表的字段其实是id, 这就导致结果是这样的: 将查到的A表每条记录都放到B表子查询再查一次.

纠正后select * from A where bid in (select b.id from B where 条件)再看就正常了