开发者社区> 技术小达人> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 可能会执行非常慢,线上生产环境千万别写出这种SQL ...
+关注继续查看

背景交代

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

注意到几个变化:

  • 耗时从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 = 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时,尤其要注意规避这个问题,否则可能造成严重性能问题。

Enjoy MySQL :)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql数据分组 group by 多条件分组但条件不并列的分析
mysql数据分组 group by 多条件分组但条件不并列的分析
0 0
有条件了!筛选,MySQL条件查询SELECT-WHERE
有条件了!筛选,MySQL条件查询SELECT-WHERE
0 0
【ElasticSearch实战】——ES常用查询条件与mysql对比
【ElasticSearch实战】——ES常用查询条件与mysql对比
0 0
一种解决MySQL多表查询在多个条件下缺少数据集的方法,附带解决查询慢的问题
一种解决MySQL多表查询在多个条件下缺少数据集的方法,附带解决查询慢的问题
0 0
Semi-join使用条件,派生表优化 (3)—mysql基于规则优化(四十六)
Semi-join使用条件,派生表优化 (3)—mysql基于规则优化(四十六)
0 0
条件简化&子查询(1)--Mysql基于规则优化(四十四)
条件简化&子查询(1)--Mysql基于规则优化(四十四)
0 0
MySql 别犯糊涂了! LEFT JOIN 的 ON 后接上筛选条件,多个条件会出事!
MySql 别犯糊涂了! LEFT JOIN 的 ON 后接上筛选条件,多个条件会出事!
0 0
MySQL - ON & WHERE 条件区别和执行顺序
MySQL - ON & WHERE 条件区别和执行顺序
0 0
JavaWeb+Druid连接池+MySQL实现商品的多条件搜索与分页(超详细的~)
JavaWeb+Druid连接池+MySQL实现商品的多条件搜索与分页(超详细的~)
0 0
Linux安装Mysql(图文解说详细版,安装包tar包版)
Linux安装Mysql(图文解说详细版,安装包tar包版)
0 0
文章
问答
来源圈子
更多
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
让 MySQL 原生分布式触手可及
立即下载
好的 MySQL 兼容可以做到什么程度
立即下载
云数据库RDS MySQL从入门到高阶
立即下载