MySQL中WHERE后跟着N多个OR条件会怎样...

MySQL中WHERE后跟着N多个OR条件会怎样...

背景交代

用 tpcc-mysql​ 工具生成 50个仓库 的测试数据，表 order_line 共有 37970973 条记录。

某工具在运行过程中，会产生下面的SQL进行查询，WHERE后跟了N多个条件：

mysql> select * from order_line where (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2221' and ol_number = '5') or (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2225' and ol_number = '1')or (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2155' and ol_number = '2')...

这里说的N多个，是指总共有10000个OR条件，这条SQL的长度大概将近800KB。

这条SQL在我的测试服务器上，运行了约56秒（另一个性能略差的机器上跑了1800秒左右才完成），共扫描75563行记录，返回8192行结果：

# Query_time: 56.031955 Lock_time: 0.047795 Rows_sent: 8129 Rows_examined: 75563 ... Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 75563 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 ......# InnoDB_pages_distinct: 501...select * from order_line where ...

相当于只做了1次索引范围查询，但总共要扫描7.5万条数据。

问题分析

只需要扫描 7.5万行记录，501个page，返回8192行结果，正常情况下不应该需要这么久才对，肯定是哪里有问题。

再次手动执行这条SQL，发现的确是这么慢，并且在最后还有个 warnings 提醒，查看下是啥内容：

mysql> show warnings\G... Level: Warning Code: 3170Message: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.

第一次见到这种告警，先检查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就可以正常执行，并且运行速度很快：

# Query_time: 6.721209 Lock_time: 0.044637 Rows_sent: 8129 Rows_examined: 8129 Read_first: 0 Read_last: 0 Read_key: 10000 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 ......# InnoDB_pages_distinct: 81

注意到几个变化：

耗时从56秒降到6.7秒；扫描行数从7.5万行降到8192行（返回结果数不变）；Read_key从1增加到10000；Read_next从75563降到0；扫描的page数从501降到81。

相当于做了1万次索引列等值条件查询。

查询效率提升非常显著。

进一步优化

线上生产环境中，各式各样的SQL层出不穷，这次可能是一万条OR条件，下次可能是其他的，是不能无限度增加数据库内存消耗的。

针对本案中的SQL，更好的优化办法是找出这些OR条件的范围规律，并改写成一条更简单的SQL，类似下面这样：

mysql> select * from order_line whereol_w_id = 1 and ol_d_id = 1 and (ol_o_id between 2007 and 2997) and (ol_number between 1 and 15 );

新的SQL执行代价：

# Query_time: 0.006338 Lock_time: 0.000084 Rows_sent: 9883 Rows_examined: 9883...Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 9883 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0......# InnoDB_pages_distinct: 81

相当于只做了1次索引范围查询，且只需扫描9883条记录。

相比上面调高内存上限的优化方案，本次的做法则更为彻底，耗时从6.7秒直接降为6.3毫秒，提升了1000倍；扫描行数、次数和page数也下降了很多。

不过要注意的是，改写后的SQL查询结果和原来并不是完全一致的，实际应用中，可能还要再做进一步筛选或者增加 LIMIT N 来控制。

最后再次提醒，WHERE条件后跟着N多个OR/AND条件的写法非常不可取，尤其是在用一些开发框架构造查询SQL时，尤其要注意规避这个问题，否则可能造成严重性能问题。

延伸阅读

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。