背景：使用tpcc-mysql工具生成50个仓库的测试数据，order_line表共有37,970,973条记录。某工具运行过程中，会生成如下SQL进行查询，WHERE后跟N多条件：mysql>select*fromorder_linewhere(ol_w_id='1'andol_d_id='1'andol_o_id='2221'andol_number='5')or(ol_w_id='1'andol_d_id='1'andol_o_id='2225'andol_number='1')or(ol_w_id='1'andol_d_id='1'andol_o_id='2155'andol_number='2')...这里说的N个数是指OR条件一共有10000个，这条SQL的长度大概是800KB。这条SQL在我的测试服务器上运行了大约56秒（在另一台性能稍差的机器上用了大约1800秒完成），一共扫描了75563行记录，返回了8192行结果：#Query_time:56.031955Lock_time:0.047795Rows_sent：8129Rows_examined：75563...Read_first：0Read_last：0Read_key：1Read_next：75563Read_prev：0Read_rnd：0Read_rnd_next：0......#InnoDB_pages_distinct：501...在这里选择*from或..相当于只做了一次索引范围查询，但是一共需要扫描75000条数据。问题分析只需要扫描75000行记录，501页，返回8192行结果。正常情况下，应该不会花这么长时间。一定有问题。再次手动执行这条SQL，发现确实好慢，最后还有warnings提醒，查看内容：mysql>showwarnings\G...Level:WarningCode:3170Message:Memorycapacityof8388608bytes超过“range_optimizer_max_mem_size”。此查询未进行范围优化。第一次看到这种警告时，请查看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范围优化器的内存消耗限制。值0表示“无限制”。如果优化器考虑的执行计划使用范围访问方法，但优化器估计此方法所需的内存量将超过限制，则会放弃该计划并考虑其他计划。有关详细信息，请参阅限制内存使用以进行范围优化。该选项从MySQL5.7.9开始引入，用于控制优化器采用范围（RANGE）查询优化方案时使用的内存消耗限制。其默认值为8MB（5.7.12及以上版本）。当设置为0时，表示没有限制。当WHERE查询条件由很多OR和AND组成，优化器判断内存消耗超过限制时，会将SQL执行计划调整为其他执行计划，甚至可能是全表扫描。这就是为什么MySQL执行完上面的大SQL后会有这样的警告提示。经过简单的几次尝试，将range_optimizer_max_mem_size选项的值增加到24MB后，SQL可以正常执行，而且速度非常快：#Query_time:6.721209Lock_time:0.044637Rows_sent:8129Rows_examined:8129Read_first:0Read_last:0Read_key:10000Read_next:0Read_prev:0Read_rnd:0Read_rnd_next:0......#InnoDB_pages_distinct:81注意到几个变化：耗时从56秒下降到6.7秒；扫描行数从75000下降到8192（返回结果数不变）；read_key从1增加到10000；read_next从75563减少到0；扫描页数从501减少到81，相当于做了10000个索引列等效条件查询。查询效率的提升非常显着。为了进一步优化线上生产环境，各种SQL层出不穷。这次可能有10000个OR条件，下一次可能是other。不可能无限制地增加数据库的内存消耗。对于本例中的SQL，更好的优化方法是找出这些OR条件的范围，改写成更简单的SQL，类似如下：mysql>select*fromorder_linewhereol_w_id=1andol_d_id=1and(ol_o_id在2007和2997之间）和（ol_number在1和15之间）；新的SQL执行成本：#Query_time:0.006338Lock_time:0.000084Rows_sent:9883Rows_examined:9883...Read_first:0Read_last:Read0Read_key:1_pre9Read830Read_rnd:0Read_rnd_next:0......#InnoDB_pages_distinct:81is相当于只做一次索引范围查询，只需要扫描9883条记录。与上面增加内存上限的优化方案相比，这次的方法更加彻底，耗时直接从6.7秒减少到6.3毫秒，提升了1000倍；扫描的行数、次数和页数也下降了很多。但是需要注意的是，改写后的SQL查询结果与原来的查询结果并不完全相同。在实际应用中，可能需要进一步筛选或加入LIMITN来控制。最后再次提醒大家，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_sizeLimitingMemoryUseforRangeOptimization，https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-optimization-memory-use
