MySQL中使用or、in与union all在查询命令下的效率对比
OR、in和union all 查询效率到底哪个快？
网上很多的声音都是说union all 快于 or、in，因为or、in会导致全表扫描，他们给出了很多的实例。
但真的union all真的快于or、in？
EXPLAIN SELECT * from employees where employees.first_NAME ='Georgi' UNION ALL SELECT * from employees where employees.first_NAME ='Bezalel'
这条语句执行结果481条，执行时间为0.35s
PRIMARY employees ALL 300141 Using whereUNION employees ALL 300141 Using where UNION RESULT <union1,2> ALL explain SELECT * FROM employees WHERE employees.first_name IN ('Georgi','Bezalel')这条语句的执行结果时间为0.186s
SIMPLE employees ALL 300141 Using whereexplain SELECT * FROM employees WHERE employees.first_name ='Georgi' or employees.first_name='Bezalel'
这条语句的执行结果和in的结果差不多
难道是网上的说法有误？难道和索引有关？在firstname上建立了一个索引
重新执行
union的执行执行计划如下，执行时间为0.004s
PRIMARY employees ref index_firstname index_firstname 44 const 253 Using whereUNION employees ref index_firstname index_firstname 44 const 228 Using whereUNION RESULT <union1,2> ALL
in的执行计划如下，执行时间也为0.004s
SIMPLE employees range index_firstname index_firstname 44 481 Using where
or的执行计划如下，执行时间也为0.004s
SIMPLE employees range index_firstname index_firstname 44 481 Using where
感觉性能差不多啊。但是注意执行计划中的type，ref要好于range哦(ref为非唯一性索引扫描，range为索引范围扫描）
突然感觉好像和网上说的差不多了，但是第一个语句走了两个ref扫描 会不会效率比走一次range的扫描低啊。
要不我再试试主键，这个是唯一的，会不会和网上的效果一直呢？
EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO=100001 UNION ALL SELECT * FROM employees WHERE employees.EMP_NO=101100
union的执行计划如下
PRIMARY employees const PRIMARY PRIMARY 4 const 1 UNION employees const PRIMARY PRIMARY 4 const 1 UNION RESULT <union1,2> ALL EXPLAIN SELECT * FROM employees WHERE employees.EMP_NO IN (100001 ,101100)
in的执行计划如下
SIMPLE employees range PRIMARY PRIMARY 4 2 Using whereEXPLAIN SELECT * FROM employees WHERE employees.EMP_NO=100001 OR emp_no=101100
or的执行计划如下
SIMPLE employees range PRIMARY PRIMARY 4 2 Using where
感觉结果和第二个实验还是差不多。
下面本文就采用实例来探讨在实际的查询命令下它们之间的效率对比究竟如何。
1：创建表，插入数据、数据量为1千万【要不效果不明显】。
drop table if EXISTS BT; create table BT( ID int(10) NOT NUll, VName varchar(20) DEFAULT '' NOT NULL, PRIMARY key( ID ) )ENGINE=INNODB;
该表只有两个字段 ID为主键【索引页类似】，一个是普通的字段。（偷懒就用简单的表结构呢）
向BT表中插入1千万条数据
这里我写了一个简单的存储过程【所以你的mysql版本至少大于5.0，俺的版本为5.1】，代码如下。
注意：最好
INSERT INTO BT ( ID,VNAME ) VALUES( i, CONCAT( 'M', i ) );---1
修改为
INSERT INTO BT ( ID,VNAME ) VALUES( i, CONCAT( 'M', i, 'TT' ) );---2
修改原因在
非索引列及VNAME使用了联合进行完全扫描请使用1 。
非索引列及VNAME使用了全表扫描请使用2 。
DROP PROCEDURE IF EXISTS test_proc; CREATE PROCEDURE test_proc() BEGIN declare i int default 0; set autocommit = 0; while i<10000000 do INSERT INTO BT ( ID,VNAME ) VALUES( i, CONCAT( 'M', i ) ); set i = i+1; if i%2000 = 0 then commit; end if; end while; END;
就不写注释呢，挺简单的。
存储过程是最好设置下innob的相关参数【主要和日志、写缓存相关这样能加快插入】，俺没有设置插入1千万条数据插了6分钟。
部分数据如下：1千万数据类似
2：实战
2.1 :分别在索引列上使用 or、in、union all
我们创建的表只有主键索引，所以只能用ID做查询呢。我们查 ID 为 98，85220，9888589的三个数据各个耗时如下：
时间都为0.00，怎么会这样呢，呵呵所有查询都是在毫秒级别。
我使用其他的工具--EMS SQL Manager for mysql
查询显示时间为
93 ms， 94ms，93 ms，时间相差了多少几乎可以忽略。
然后我们在看看各自的执行计划
这里要注意的字段type 与ref字段
我们发现union all 的所用的 type【type为显示连接使用了何种类型】 为ref 而or和in为range【ref连接类型优于range，相差不了多少】，而查询行数都一样【看rows字段都是为3】。
从整个的过程来看，在索引列使用常数or及in和union all查询相差不了多少。
但为什么在有的复杂查询中，再索引列使用or及in 比union all 速度慢很多呢，这可能是你的查询写的不够合理，让mysql放弃索引而进行全表扫描。
2.2：在非索引列中使用 or、in及union all。
我们查 VNAME 为 M98，M85220，M9888589的三个数据各个耗时如下：
我们发现为啥union all查询时间几乎为 or 和in的三倍。
这是为什么呢，我们先不说，先看看三个的查询计划。
这里我们发现计划几乎一样。
但我们要注意扫描的此时对于 or及in 来说 只对表扫描一次即rows是列为9664782。
而对于union all 来说对表扫描了三次即rows的和为9664782*3。
这也是为什么我们看到union all 为几乎为三倍的原因。
备注： 如果使用存储过程使用第二sql该执行计划所有的type列 为 all，其实这个是我最想演示的，但现在已经快写完毕了才发现问题将错就错呢。
3:总结
3.1：不要迷信union all 就比 or及in 快，要结合实际情况分析到底使用哪种情况。
3.2：对于索引列来最好使用union all，因复杂的查询【包含运算等】将使or、in放弃索引而全表扫描，除非你能确定or、in会使用索引。
3.3：对于只有非索引字段来说你就老老实实的用or 或者in，因为 非索引字段本来要全表扫描而union all 只成倍增加表扫描的次数。
3.4：对于及有索引字段【索引字段有效】又包含非索引字段来时，按理你也使用or 、in或者union all 都可以，
但是我推荐使用or、in。
如以下查询：
select * from bt where bt.VName = 'M98' or bt.id ='9888589' select * from bt where bt.VName = 'M98' UNION ALL select * from bt where bt.id = '9888589'
该两个查询速度相差多少 主要取决于 索引列查询时长，如索引列查询时间太长的话，那你也用or或者in代替吧。
3.5： 以上主要针对的是单表，而多表联合查询来说，考虑的地方就比较多了，比如连接方式，查询表数据量分布、索引等，再结合单表的策略选择合适的关键字。
相关文章
- 这篇文章主要介绍了Mybatis Plus select 实现只查询部分字段的操作，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-09-01
MyBatisPlus-QueryWrapper多条件查询及修改方式
这篇文章主要介绍了MyBatisPlus-QueryWrapper多条件查询及修改方式，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2022-06-27使用percona-toolkit操作MySQL的实用命令小结
1.pt-archiver 功能介绍： 将mysql数据库中表的记录归档到另外一个表或者文件 用法介绍： pt-archiver [OPTION...] --source DSN --where WHERE 这个工具只是归档旧的数据，不会对线上数据的OLTP查询造成太大影响，你可以将...2015-11-24- 这篇文章主要介绍了Oracle使用like查询时对下划线的处理方法，本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-16
- grep命令是Linux系统中最重要的命令之一，功能是从文本文件或管道数据流中筛选匹配的行和数据，如果再配合正则表达式，功能十分强大，是Linux运维人员必备的命令，这篇文章主要介绍了Linux中grep详解,需要的朋友可以参考下...2023-02-15
- 这篇文章主要介绍了解决mybatis-plus 查询耗时慢的问题，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-07-04
- 关于mysql效率优化一般通过以下两种方式定位执行效率较低的sql语句。通过慢查询日志定位那些执行效率较低的 SQL 语句，用 --log-slow-queries[=file_name] 选项启动时， mysqld 会 写一个包含所有执行时间超过 long_quer...2015-11-08
- 这篇文章主要介绍了C#隐式运行CMD命令（隐藏命令窗口）,本文实现在winform窗口中运行CMD命令,需要的朋友可以参考下...2020-06-25
- 本文实例讲述了PHP实现连接设备、通讯和发送命令的方法。分享给大家供大家参考。具体如下：开发的BS架构的软件（PHP），需要跟设备进行通讯，在此记录一下，欢迎各位指正：1. 采用php socket技术使用TCP/IP连接设备参数$service_po...2015-10-21
- MySQL日志主要包含：错误日志、查询日志、慢查询日志、事务日志、二进制日志；日志是mysql数据库的重要组成部分。日志文件中记录着mysql数据库运行期间发生的变化；也就是说用来记录mysql数据库的客户端连接状况、SQL语句...2015-11-24
- 如果需要在查询语句返回的列中包含一列表示该条记录在整个结果集中的行号， ISO SQL:2003 标准提出的方法是提供 ROW_NUMBER() / RANK() 函数。 Oracle 中可以使用标准方法（8i版本以上），也可以使用非标准的 ROWNUM ； MS SQL...2015-03-15
- 一、连接Mysql格式： mysql -h主机地址 -u用户名 －p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口，然后进入目录mysql/bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密...2015-11-08
- 这篇文章主要为大家详细介绍了Node实现搜索框进行模糊查询，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2021-06-28
Element-ui 自带的两种远程搜索(模糊查询)用法讲解
这篇文章主要介绍了Element-ui 自带的两种远程搜索(模糊查询)用法讲解,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2021-01-29- 这篇文章主要介绍了Mybatis用注解写in查询的实现方式，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-07-13
PHP+Mysql+jQuery查询和列表框选择操作实例讲解
本文讲解如何通过ajax查询mysql数据，并将返回的数据显示在待选列表中，再通过选择最终将选项加入到已选区，可以用在许多后台管理系统中。本文列表框的操作依赖jquery插件。HTML <form id="sel_form" action="post.php" me...2015-10-23- Redis 是一个开源、高性能的Key-Value数据库，被广泛应用在服务器各种场景中。本文介绍几个查看Redis内存信息的命令，包括常用的info memory、info keyspace、bigkeys等。...2021-01-15
- 这篇文章主要介绍了Mybatis和Mybatis-Plus时间范围查询方式，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-08-06
- 这篇文章主要介绍了解决jmap命令打印JVM堆信息异常的问题，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-04
- 下面是我们经常会用到且非常有用的MySQL命令。下面你看到#表示在Unix命令行下执行命令，看到mysql>表示当前已经登录MySQL服务器，是在mysql客户端执行mysql命令。 登录MySQL,如果连接远程数据库，需要用-h指定hostname。登...2015-11-24