共计 3098 个字符，预计需要花费 8 分钟才能阅读完成。
| 导读 | 不过要注意的是，改写后的 SQL 查询结果和原来并不是完全一致的，实际应用中，可能还要再做进一步筛选或者增加 LIMIT N 来控制。 |
用 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 limit on 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注意到几个变化：
相当于做了 1 万次索引列等值条件查询。
查询效率提升非常显著。
线上生产环境中，各式各样的 SQL 层出不穷，这次可能是一万条 OR 条件，下次可能是其他的，是不能无限度增加数据库内存消耗的。
针对本案中的 SQL，更好的优化办法是找出这些 OR 条件的范围规律，并改写成一条更简单的 SQL，类似下面这样：
mysql> select * from order_line where
ol_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 时，尤其要注意规避这个问题，否则可能造成严重性能问题。
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
