MySQL中WHERE后跟着N多个OR条件会怎样...
背景交代
用 tpcc-mysql 工具生成 50个仓库 的测试数据,表 order_line 共有 37970973 条记录。
某工具在运行过程中,会产生下面的SQL进行查询,WHERE后跟了N多个条件:
这里说的N多个,是指总共有10000个OR条件,这条SQL的长度大概将近800KB。
这条SQL在我的测试服务器上,运行了约56秒(另一个性能略差的机器上跑了1800秒左右才完成),共扫描75563行记录,返回8192行结果:
相当于只做了1次索引范围查询,但总共要扫描7.5万条数据。
问题分析
只需要扫描 7.5万行记录,501个page,返回8192行结果,正常情况下不应该需要这么久才对,肯定是哪里有问题。
再次手动执行这条SQL,发现的确是这么慢,并且在最后还有个 warnings 提醒,查看下是啥内容:
第一次见到这种告警,先检查MySQL手册,看看 range_optimizer_max_mem_size 这个选项是干嘛用的:
这个选项是从MySQL 5.7.9开始引入的,用于控制当优化器采用范围(RANGE)查询优化方案时使用的内存消耗限制。
其默认值为8MB(5.7.12及以上版本),当设置为0时,表示不做任何限制。当WHERE查询条件里有很多OR、AND组成时,优化器判断超过内存消耗限制,则会调整SQL执行计划,变成其他执行方案,甚至可能是全表扫描。
这也就是为什么执行上面的大SQL后,MySQL会有这样的告警提示了。
经过几次简单尝试,把 range_optimizer_max_mem_size 选项值调大到 24MB 后,这个SQL就可以正常执行,并且运行速度很快:
注意到几个变化:
- 耗时从56秒降到6.7秒;
- 扫描行数从7.5万行降到8192行(返回结果数不变);
- Read_key从1增加到10000;
- Read_next从75563降到0;
- 扫描的page数从501降到81。
相当于做了1万次索引列等值条件查询。
查询效率提升非常显著。
进一步优化
线上生产环境中,各式各样的SQL层出不穷,这次可能是一万条OR条件,下次可能是其他的,是不能无限度增加数据库内存消耗的。
针对本案中的SQL,更好的优化办法是找出这些OR条件的范围规律,并改写成一条更简单的SQL,类似下面这样:
新的SQL执行代价:
相当于只做了1次索引范围查询,且只需扫描9883条记录。
相比上面调高内存上限的优化方案,本次的做法则更为彻底,耗时从6.7秒直接降为6.3毫秒,提升了1000倍;扫描行数、次数和page数也下降了很多。
不过要注意的是,改写后的SQL查询结果和原来并不是完全一致的,实际应用中,可能还要再做进一步筛选或者增加 LIMIT N 来控制。
最后再次提醒,WHERE条件后跟着N多个OR/AND条件的写法非常不可取,尤其是在用一些开发框架构造查询SQL时,尤其要注意规避这个问题,否则可能造成严重性能问题。
延伸阅读
sysvars-range_optimizer_max_mem_size,https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_optimizer_max_mem_size
Limiting Memory Use for Range Optimization,https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-optimization-memory-use
相关文章
- 数据孤岛是业务效率的无声杀手
- 2023展望:新的一年将给大数据分析领域带来什么?
- 阿里云ADB基于Hudi构建Lakehouse的实践
- 大数据在医疗保健领域的使用案例
- 微软增加说明:KB5021751 更新扫描已经 / 即将过时 Office 过程中不会触碰用户隐私
- 2022 Gartner全球云数据库管理系统魔力象限发布 腾讯云数据库入选
- 场景化、重实操,分享一个实时数仓实践案例
- Arctic的湖仓一体践行之路
- 分布式计算MapReduce究竟是怎么一回事?
- 淘系数据模型治理优秀实践
- 大数据分析对医疗保健的影响
- 当我们说大数据Hadoop,究竟在说什么?
- 2022年及以后大数据的五个发展趋势
- 网易严选离线数仓治理实践
- 2023 年数据治理趋势
- 一份“靠谱”的年度经营计划,你学会了吗?
- 漫谈对大数据的思考
- 测试一下,读懂数据的能力,你有吗?
- 用艺术的眼光探索数据之美
- 聊聊数据分析成果如何落地