mysql between和in_mysql between and、>=<=、in性能实例分析

首先向大家介绍mysql between and、>=<=和in关键字在where查询中的使用方法和实例。

mysql between and用法

BETWEEN 运算符用于 WHERE 表达式中，选取介于两个值之间的数据范围。除了数值类型外，BETWEEN 也支持字符串范围。BETWEEN 同 AND 一起搭配使用，语法如下：

WHERE column BETWEEN value1 AND value2

WHERE column NOT BETWEEN value1 AND value2

通常 value1 应该小于 value2。当 BETWEEN 前面加上 NOT 运算符时，表示与 BETWEEN 相反的意思，即选取这个范围之外的值。

实例1：选取 uid 在 2 到 5 之间的用户数据，sql代码如下：

SELECT * FROM user WHERE uid BETWEEN 2 AND 5

实例2：选择出所有 username 介于 a 至 j 之间的用户(包括单字母a和j)：

SELECT * FROM user WHERE username BETWEEN 'a' AND 'k'

MySQL IN 用法

mysql IN 运算符用于 WHERE 表达式中，以列表项的形式支持多个选择，语法如下：

WHERE column IN (value1,value2,...)

WHERE column NOT IN (value1,value2,...)

当 IN 前面加上 NOT 运算符时，表示与 IN 相反的意思，即不在这些列表项内选择。

实例1：选取 uid 为 2、3、5 的用户数据，代码如下

SELECT * FROM user WHERE uid IN (2,3,5)

更多情况下，IN 列表项的值是不明确的，而可能是通过一个子查询得到的：

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=0)

在这个 SQL 例子里，我们实现了查出所有状态为 0 的用户(可能是被禁止)的所有文章。首先通过一个查询得到所有所有 status=0 的用户：

SELECT uid FROM user WHERE status=0

然后将查询结果作为 IN 的列表项以实现最终的查询结果，注意在子查询中返回的结果必须是一个字段列表项。

mysql大于小于运算符

mysql >=<=运算符用于查询大于小于某值的数据。

比如我们要查询分数在70到80之间的学生，SQL代码如下：

select *from student where score>=70 and score<=80;

和between and具有相同的功能，因此下面语句与上面语句等价：

SELECT * FROM student WHERE score BETWEEN 70 AND 80

MySQL中大于小于，IN，BETWEEN性能比较

1  准备环境

mysql> show create table tinG

*************************** 1. row ***************************

Table: tin

Create Table: CREATE TABLE `tin` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` varchar(256) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=5002 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> show create procedure init_tinG

*************************** 1. row ***************************

Procedure: init_tin

sql_mode: NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `init_tin`(cnt int)

begin

declare i int default 0;

repeat

insert into tin(c2) values(repeat(‘a’, 100));

set i:= i+1;

until i > cnt

end repeat;

end

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

mysql> call init_tin(5000)G

2  查看执行计划

为了简单起见，选择[100,104]这个区间。

查询语句为：

SELECT * FROM tin where c1 >= 100 and c1 <= 104;

SELECT * FROM tin where c1 bewteen 100 and 104;

SELECT * FROM tin where c1 in (100, 101, 102, 103, 104);

SELECT * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 = 104;

其中，在MySQL中 c1 >= 100 and c1 <= 104 和 c1 bewteen 100 and 104 是等价的，下面选用前者进行比较。

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

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tin

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: NULL

rows: 5

filtered: 100.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

MySQL5.6在information_schema中增加了optimizer_trace表，用于跟踪语句生成的执行计划的具体步骤，包含各种关键的优化步骤。

分别看下三种不同语句的执行代价：

1. SELECT * FROM tin where c1 >= 100 and c1 <= 104;

SELECT * FROM tin where c1 >=100 and c1 <=104;

“chosen_range_access_summary”: {

“range_access_plan”: {

“type”: “range_scan”,

“index”: “PRIMARY”,

“rows”: 5,

“ranges”: [

“100 <= c1 <= 104″

]

},

“rows_for_plan”: 5,

“cost_for_plan”: 2.0188,

“chosen”: true

}

2. SELECT * FROM tin where c1 in (100, 101, 102, 103, 104);

SELECT * FROM tin where c1 in (100, 101, 102, 103, 104);

“chosen_range_access_summary”: {

“range_access_plan”: {

“type”: “range_scan”,

“index”: “PRIMARY”,

“rows”: 5,

“ranges”: [

“100 <= c1 <= 100″,

“101 <= c1 <= 101″,

“102 <= c1 <= 102″,

“103 <= c1 <= 103″,

“104 <= c1 <= 104″

]

},

“rows_for_plan”: 5,

“cost_for_plan”: 6.0188,

“chosen”: true

}

3. SELECT * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 = 104;

SELECT * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 =104;

“chosen_range_access_summary”: {

“range_access_plan”: {

“type”: “range_scan”,

“index”: “PRIMARY”,

“rows”: 5,

“ranges”: [

“100 <= c1 <= 100″,

“101 <= c1 <= 101″,

“102 <= c1 <= 102″,

“103 <= c1 <= 103″,

“104 <= c1 <= 104″

]

},

“rows_for_plan”: 5,

“cost_for_plan”: 6.0188,

“chosen”: true

}

从上面可以看出执行代价最小的语句为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内部的优化流程，可能单独测试的时候语句执行效率差别不是很大。