mysql大于小于能用between_MySQL中大于小于，IN，OR，BETWEEN性能比较

最新推荐文章于 2024-09-14 10:24:02 发布
最新推荐文章于 2024-09-14 10:24:02 发布
阅读量2.2k 收藏 1
点赞数
文章标签： mysql大于小于能用between
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33930436/article/details/113259784
版权
本文探讨了在MySQL中，使用>=和<=、BETWEEN、IN以及OR作为查询条件时的性能差异。通过实验发现，当索引存在时，BETWEEN和>=、<=的性能相似且优于IN和OR，因为IN和OR导致的range个数较多，增加了一定的CPU验证成本。
摘要由CSDN通过智能技术生成

在MySQL中经常会有朋友问关于大于小于，IN，OR，BETWEEN，哪个性能更好？

我想有很多朋友会用它们但确实不知道大于小于，IN，OR，BETWEEN性能哪个更好吧，下面总结一下关于大于小于，IN，OR，BETWEEN性能。

这里的查询条件有四种：>=和<=；between；or；in

这里id列是索引列，如果不是的话，三个查询都是全表扫描，性能差距应该不大。

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 wherec1bewteen 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和 c1bewteen 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内部的优化流程，可能单独测试的时候语句执行效率差别不是很大。

mysql in和大于性能_MySQL中大于小于，IN，OR，BETWEEN性能比较-Fun言
weixin_30219823的博客
02-01 1097
在MySQL中经常会有朋友问关于大于小于，IN，OR，BETWEEN，哪个性能更好？我想有很多朋友会用它们但确实不知道大于小于，IN，OR，BETWEEN性能哪个更好吧，下面总结一下关于大于小于，IN，OR，BETWEEN性能。这里的查询条件有四种：>=和<=；between；or；in这里id列是索引列，如果不是的话，三个查询都是全表扫描，性能差距应该不大。1 准备环境mysql&...
sql索引优化之日期：between与大于小于
xianrenmodel的博客
09-03 8794
查询的时间是加了默认的betree索引的。在between同一天的时候，索引有效： 当between不在同一天时，索引无效： 而无论是否在同一天，用>和<时，索引都是无效的。
mysql in语句_MySQL的WHERE语句中BETWEEN与IN的使用教程
weixin_32831131的博客
01-18 525
BETWEEN 运算符用于 WHERE 表达式中，选取介于两个值之间的数据范围。BETWEEN 同 AND 一起搭配使用，语法如下：WHERE column BETWEEN value1 AND value2WHERE column NOT BETWEEN value1 AND value2通常 value1 应该小于 value2。当 BETWEEN 前面加上 NOT 运算符时，表示与 BETW...
mysql中between and 和大于等于、小于等于测试
热门推荐
兰亮网络技术团队
08-11 1万+
作者 徐连臣 推荐理由 比较原创，让大家对mysql时间查询是否走索引有一个更好的认知。 mysql中between and和>=、<=的作用相似，网上有的说使用between and查询时同一天索引有效，非同一天时，索引无效。 而使用>=、<=时，不管是同一天还是非同一天，索引均无效。实践是检验真理的唯一标准，今天就测试了一下。使用生产 环境的一张运输记录表（biz_stub），创建时间（gmt_create）字段加了索引： 1、使用between and查询： （1）查询的时间
mysql between and、in
biezanhan0147的博客
09-02 242
#案例，查询员工员工编号在100到120之间的员工信息 SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 200; #顺序不可颠倒 in案例:查询员工工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号 SELECT last_name, job_id FROM employees WHERE job...
mysql 《=，》=和between的性能比较
yaofuyuan的专栏
12-16 1万+
1.测试条件: 服务器和客户机都是：cpu 双核1.6GHZ，内存4G的物理机网络：局域网查询1000张表,记录为10000条，查询包括= 三个条件mysql版本:5.5.72.测试1单个长连接,持续测试60s,关缓存 2.1 =2.2 between2.测试2单个长连接,持续测试60s,开缓存3.2 between4.结论between比=的性能稍微多几十个，但是不是很明显
mysql 优化之 in between and
net@core的博客
05-24 8522
SELECT * FROM a WHERE id IN (1,2,3,4,5,6,7…..100) 这个这种sql 怎么优化 方法between and select * from a where id &gt;=1 and id &lt;=100; 改为列名name *select name from a where id &gt;=1 and id &lt;=10...
MySQL的WHERE语句中BETWEEN与IN的使用教程
01-19
MySQL BETWEEN 用法 MySQL BETWEEN 语法 BETWEEN 运算符用于 WHERE 表达式中，选取介于两个值之间的数据范围。BETWEEN 同 AND 一起搭配使用，语法如下： WHERE column BETWEEN value1 AND value2 WHERE column NOT...
MySQL中BETWEEN子句的用法详解
09-10
在MySQL数据库中，`BETWEEN`子句是一种非常实用的查询工具，它允许你在WHERE子句中指定一个范围，筛选出在这个范围内满足条件的记录。这个子句主要用于选取介于两个值之间的数据，这两个值可以是常量、变量或者列的...
MYSQL查询语句 like——between-and——in
2301_80391641的博客
03-29 302
MYSQL查询语句 like——between-and——in
MYSQL过滤数据（五）IN | BETWEEN AND
有天你会让我妒忌的.
06-06 1221
MySQL IN运算符简介IN运算符允许你确定一个指定字段的值是否在给定列表或子查询中。下面是IN 运算符的语法：SELECT column1,column2,... FROM table_name WHERE (expr|column_1) IN ('value1','value2',...);让我们更详细地检查查询：您可以在WHERE子句中使用IN运算符。列表中的值必须用...
mysql between and 和in效率问题
王威振的博客
11-15 6223
在用between and 和in 进行范围查询的时候， 能用between and就尽量用，亲测了一下，between and 花费的成本比in少， 可以通过 explain format=json 执行计划, 可以清晰的看到数据query_cost explain format=json的用法 后面直接加你查询的sql就可以了
mysql in边界_mysql between and实例及边界值分析
weixin_29061509的博客
02-07 342
mysql中可以使用between and来限定一个数据的范围，between and关键字其实相当于“大于等于和小于等于”的条件。请看下面实例：现有一employee_tbl表，其数据结果如下所示：mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name ...
MySQL学习笔记之条件查询和模糊查询like、between and、in、is null 、is not null...
DongZhaoCheng
02-20 2576
1.条件查询以及筛选条件练习12.模糊查询①`like`也可以使用 `not like`②`between and`③`in`④`is null` 或 `is not null`⑤安全等于符号 `<=>`与`is null`的比较 1.条件查询以及筛选条件 --格式 select 查询列表 from 表名 where 筛选条件; --筛选条件分类： 1.条件表达式 > < =(等于) !=(不等于) <>(不等于) >= &lt.
MySQL数据库select语句详细用法二(in，between and范围查询 ,聚合函数的使用，分组查询)
最新发布
2302_80464795的博客
09-14 548
虽然两者的含义相同但是in查询相比于between and查询，in查询更广泛的应用于子查询。 between后面的数和and后面的数以及in后面括号中的数构成了一个以两操作数为闭区间的一个范围，然后在此范围中查询。 AVG(age)表示的是所有age数据的平均数，输出的不一定是整形会有浮点型的出现 在MySQL中，group by语句是用来将数据表中的数据，根据一列或多列字段的值进行逻辑分组，以便能方便地配合聚合函数（如COUNT(), SUM(), AVG(), MAX(), MIN()等）进行各种分
MySQL查询语句：like、between and、in
riluo2004的博客
03-29 430
数据库名称可以为【schoolDB】，字符集【utf8】，排列顺序【utf8_general_ci】。
mysql in和大于性能_Mysql性能优化之正确使用in和exists
weixin_39669265的博客
02-07 510
我们在平时写sql的时候经常会用到in和exists这两个关键字，并且在大多数时候这两个关键字是可以达到相同的目的。那么在什么情况下使用in，什么情况下使用exists才会使得我们的sql查询效率最高呢？实际上，in关键字是将外表和内表做哈希连接(hash join)，而exists关键是是对外表做一次循环(loop)，然后每次再对内表进行查询。知晓了原理之后，我们再来举个实际的例子：select...
mysql条件查询语句between and和in的效率比较
含笑的依米のBlog
03-29 7691
工作需要，写按sensor_uuid在某范围查询的语句： 1.select * from xz_sensor_data where sensor_time>='2019-03-28 00:00:00.000' and sensor_time<='2019-03-28 08:00:00.000' and sensor_uuid in('20','21','22','23','24','...
mysql 大于小于
08-12
在MySQL中，可以使用以下操作符进行大于小于的过滤条件： - 等于（=） - 不等于（!=） - 大于（>） - 大于等于（>=） - 小于（<） - 小于等于（） - 在指定的值之间（BETWEEN） - 不等于（） 以上操作符在MySQL中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值