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:3170
Message: 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 这个选项是干嘛用的：

文档出处：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_optimizer_max_mem_size

The limiton memory consumption for the range optimizer. A value of 0 means “no limit.” 
If an execution plan considered by the optimizer uses the range access method but 
the optimizer estimates that the amount of memory needed for this method would 
exceed the limit, it abandons the plan and considers other plans. For more 
information, see Limiting Memory Use for Range Optimization.

这个选项是从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 where
ol_w_id =1and ol_d_id =1and(ol_o_id between2007and2997)
and(ol_number between1and15);

新的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时，尤其要注意规避这个问题，否则可能造成严重性能问题。

延伸阅读

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