mysql中IN，OR，BETWEEN性能比较

骑猪看日落 2022-08-21 11:41 128阅读 0赞

这里的查询条件有三种：between，or 和 in。这里id列是索引列，如果不是的话，三个查询都是全表扫描，性能差距应该不大。

1  准备环境

2  查看执行计划  
为了简单起见，这里并没有选择\[100,200\]这个区间进行查询，而是只选择了\[100,104\]这个区间。 查询语句为：

首先查看explain输出，会发现三个语句的explain输出是一样的：

MySQL5.6在information\_schema中增加了optimizer\_trace表，用于跟踪语句生成的执行计划的具体步骤，包含各种关键的优化步骤。 分别看下三种不同语句的执行代价：

<table style="color:rgb(68,68,68); font-family:Simsun; font-size:15px; line-height:26px; background:rgb(255,187,119)"> 
 <tbody> 
  <tr> 
   <td style="color:rgb(0,0,0); list-style:none; margin:0px; padding:0px"> &nbsp;代码如下</td> 
   <td align="center" style="color:rgb(0,0,0); list-style:none; margin:0px; padding:0px"> 复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="color:rgb(0,0,0); list-style:none; margin:0px; padding:10px"> SELECT * FROM tin where c1 &gt;=100 and c1 &lt;=104;<br> "chosen_range_access_summary": { <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "range_access_plan": { <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "type": "range_scan",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "index": "PRIMARY",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "rows": 5,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "ranges": [<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "100 &lt;= c1 &lt;= 104"<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ]<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "rows_for_plan": 5,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "cost_for_plan": 2.0188,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "chosen": true<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</td> 
  </tr> 
 </tbody> 
</table>

<table style="color:rgb(68,68,68); font-family:Simsun; font-size:15px; line-height:26px; background:rgb(255,187,119)"> 
 <tbody> 
  <tr> 
   <td style="color:rgb(0,0,0); list-style:none; margin:0px; padding:0px"> &nbsp;代码如下</td> 
   <td align="center" style="color:rgb(0,0,0); list-style:none; margin:0px; padding:0px"> 复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="color:rgb(0,0,0); list-style:none; margin:0px; padding:10px"> SELECT * FROM tin where c1 in (100, 101, 102, 103, 104);<br> "chosen_range_access_summary": { <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "range_access_plan": { <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "type": "range_scan",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "index": "PRIMARY",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "rows": 5,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "ranges": [<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "100 &lt;= c1 &lt;= 100",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "101 &lt;= c1 &lt;= 101",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "102 &lt;= c1 &lt;= 102",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "103 &lt;= c1 &lt;= 103",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "104 &lt;= c1 &lt;= 104"<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ]<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "rows_for_plan": 5,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "cost_for_plan": 6.0188,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "chosen": true<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</td> 
  </tr> 
 </tbody> 
</table>

<table style="color:rgb(68,68,68); font-family:Simsun; font-size:15px; line-height:26px; background:rgb(255,187,119)"> 
 <tbody> 
  <tr> 
   <td style="color:rgb(0,0,0); list-style:none; margin:0px; padding:0px"> &nbsp;代码如下</td> 
   <td align="center" style="color:rgb(0,0,0); list-style:none; margin:0px; padding:0px"> 复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="color:rgb(0,0,0); list-style:none; margin:0px; padding:10px"> SELECT * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 =104;<br> "chosen_range_access_summary": { <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "range_access_plan": { <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "type": "range_scan",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "index": "PRIMARY",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "rows": 5,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "ranges": [<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "100 &lt;= c1 &lt;= 100",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "101 &lt;= c1 &lt;= 101",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "102 &lt;= c1 &lt;= 102",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "103 &lt;= c1 &lt;= 103",<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "104 &lt;= c1 &lt;= 104"<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ]<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "rows_for_plan": 5,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "cost_for_plan": 6.0188,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "chosen": true<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</td> 
  </tr> 
 </tbody> 
</table>

从上面可以看出执行代价最小的语句为SELECT \* FROM tin WHERE c1 >= 100 and c1 <=104，代价为2.0118，其他两个计划的代价 为6.0118。

3  计划分析  
看了上面的代价结果，是不是就理所当然的任务第一个语句的代价真的是最小呢？这就需要知道MySQL代价计算的方法， 一个计划的代价体现在硬件上就是I/O+CPU，I/O就是将所需的物理页载入内存的时间，CPU则是数据计算所消耗的时间， 有些语句是I/O密集的，有些语句是CPU运算密集的。

为什么MySQL计算出来的代价会差别这么大呢？ MySQL在计算上面三个语句的代价时，I/O代价的计算是由range的个数n\_ranges和最终的结果集的行数total\_rows得出来的， 语句1的n\_ranges=1，语句2和语句3的n\_ranges=5，totol\_rows都为5，故语句1的在I/O上的代价明显小于语句2和语句3（具体的函数 参见ha\_innobase::read\_time）。至于CPU的代价，由于返回的行数一致，故CPU的代价一致，CPU的代价主要体现在获取数据后，进行WHERE 条件的匹配操作。

这只是MySQL的对于上面三个语句的代价模型，而实际上，上面三个语句所进行的I/O操作其实是一致的，因为数据范围是一样的。所以，仅凭 MySQL给出的代价结果还是不能立刻判断出语句1就肯定好。

既然I/O操作的代价可以考虑是一致的，那么只能来看三条语句执行时的区别了。语句2和语句3的range个数都为5个，而且range的范围都是一致的， 这其实是MySQL的优化结果，IN和OR都被优化成了相同的结果。只有语句1只有1个range。MySQL执行时是遍历每个range，而每个range遍历时其实 是两种操作，read\_first和read\_next，read\_first是根据每个range的start key定位到相应的位置，read\_next则是根据上次BTREE读到的位置， 继续往后读，read\_next是以end key为结束。

对于语句1，只有一个range，故需要1次read\_first和5次read\_next（最后一次read\_next不符合end\_key，返回结束）,对于语句2和语句3， 有5个range，每个range需要1此read\_first和一次read\_next,总共需要5此read\_first和5次read\_next。从数据获取的次数来看，语句2和语句3基本是语句1的调用次数的两倍。

除了获取数据调用次数的区别外，在获取数据之后，还需要进行数据合法性的验证，即匹配WHERE条件，对于语句1的WHERE条件十分简单，匹配 上下界限即可，即对于每返回的一行数据需要两次验证，时间复杂度为常量O（2）。 而对于语句2和语句3，则需要对IN或OR中的每个条件进行验证，知道找到某一匹配项为止，时间复杂度为O（n）。 但是MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的，故匹配的时候是二分查找， 时间复杂度为O（lgn）。

在忽略I/O的情况下，仅仅从CPU的耗时来看，语句1应该是最少的，其次是IN，最差的就是OR了。

先就分析到这吧，具体的执行时间的数据我就不测试了，主要是想通过测试了解MySQL内部的优化流程。可能单独测试的时候语句执行效率 差别不是很大。