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 where

UNION 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 where

explain 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 where
UNION  employees  ref  index_firstname  index_firstname  44  const  228  Using where
UNION 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 where

EXPLAIN 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: 以上主要针对的是单表,而多表联合查询来说,考虑的地方就比较多了,比如连接方式,查询表数据量分布、索引等,再结合单表的策略选择合适的关键字。

(0)

相关推荐

  • MySQL大表中重复字段的高效率查询方法

    MySQL大表重复字段应该如何查询到呢?这是很多人都遇到的问题,下面就教您一个MySQL大表重复字段的查询方法,供您参考. 数据库中有个大表,需要查找其中的名字有重复的记录id,以便比较.如果仅仅是查找数据库中name不重复的字段,很容易 复制代码 代码如下: SELECT min(`id`),`name` FROM `table` GROUP BY `name`; 但是这样并不能得到说有重复字段的id值.(只得到了最小的一个id值)查询哪些字段是重复的也容易  复制代码 代码如下: SELEC

  • C++操作MySQL大量数据插入效率低下的解决方法

    通常来说C++操作MySQL的时候,往Mysql中插入10000条简单数据,速度非常缓慢,居然要5分钟左右, 而打开事务的话,一秒不到就搞定了! 具体实现代码如下: #include <iostream> #include <winsock2.h> #include <string> #include "mysql.h" #pragma comment(lib, "libmysql.lib"); using namespace s

  • MySQL优化之如何查找SQL效率低的原因

    查询到效率低的 SQL 语句 后,可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序,比如我们想计算 2006 年所有公司的销售额,需要关联 sales 表和 company 表,并且对 profit 字段做求和( sum )操作,相应 SQL 的执行计划如下: mysql> explain select sum(profit) from sales a,company b where a

  • mysql in语句子查询效率慢的优化技巧示例

    表结构如下,文章只有690篇. 文章表article(id,title,content) 标签表tag(tid,tag_name) 标签文章中间表article_tag(id,tag_id,article_id) 其中有个标签的tid是135,查询标签tid是135的文章列表. 690篇文章,用以下的语句查询,奇慢: select id,title from article where id in( select article_id from article_tag where tag_id=

  • 查询mysql中执行效率低的sql语句的方法

    一些小技巧1. 如何查出效率低的语句?在MySQL下,在启动参数中设置 --log-slow-queries=[文件名],就可以在指定的日志文件中记录执行时间超过long_query_time(缺省为10秒)的SQL语句.你也可以在启动配置文件中修改long query的时间,如: 复制代码 代码如下: # Set long query time to 8 seconds    long_query_time=8 2. 如何查询某表的索引?可使用SHOW INDEX语句,如: 复制代码 代码如下

  • 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' 这条语

  • MySQL中or、in、union与索引优化详析

    本文缘起自<一分钟了解索引技巧>的作业题. 假设订单业务表结构为: order(oid, date, uid, status, money, time, -) 其中: oid,订单ID,主键 date,下单日期,有普通索引,管理后台经常按照date查询 uid,用户ID,有普通索引,用户查询自己订单 status,订单状态,有普通索引,管理后台经常按照status查询 money/time,订单金额/时间,被查询字段,无索引 - 假设订单有三种状态:0已下单,1已支付,2已完成 业务需求,查询

  • MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

    在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦. 在优化这个语句之前,我们先了解下SQL查询的基本执行过程: 1.应用通过MySQL API把查询命令发送给MySQL服务器,然后被解析 2.检查权限.MySQL optimizer进行优化,经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划(quer

  • mysql中取系统当前时间,当前日期方便查询判定的代码

    php中常通过下面的代码,得到判定日期的sql查询语句 复制代码 代码如下: $now = time(); //获取当期的日期 $sql="select * from `team` where end_time>$now ORDER BY sort_order limit 0,4"; 获取当前时间的MySql时间函数 处理MySql时间日期的函数有很多,下面为您介绍的就是用于获取当前时间的MySql时间函数,如果您对此感兴趣的话,不妨一看 下面为您介绍的MySql时间函数用于获取

  • MySQL中使用case when 语句实现多条件查询的方法

    举例如下: 数据表为DemoTable,字段有id, condition1,condition2,condition3,condition4,condition5 要求是查询DemoTable中,condition1,condition2,condition3,condition4,condition5五个字段中符合任意两个或两个以上的条件的内容. 可使用case when来实现这个条件,需要嵌套子查询语句 sql语句代码示例如下: 复制代码 代码如下: SELECT * FROM DemoTa

  • 有关mysql中ROW_COUNT()的小例子

    注:mysql中的ROW_COUNT()可以返回前一个SQL进行UPDATE,DELETE,INSERT操作所影响的行数. MySQL上的测试(数据库版本为:5.1.22): 1.创建数据库表: 复制代码 代码如下: create table t(  id int,  name varchar(50),  address varchar(100),  primary key(id,name)  )engine =InnoDB; 2.插入测试数据: 复制代码 代码如下: insert into t

  • 深入分析Mysql中limit的用法

    Mysql中limit的用法:在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能. SELECT * FROM table   LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT 接受一个或两个数字参数.参数必须是一个整数常量.如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行

  • mysql中常用日期比较与计算函数

    MySql中时间比较的实现 unix_timestamp() unix_timestamp 函数可以接受一个参数,也可以不使用参数. 它的返回值是一个无符号的整数.不使用参数,它返回自1970年1月1日0时0分0秒到现在所经过的秒数, 如果 使用参数,参数的类型为时间类型或者时间类型的字符串表示,则是从1970-01-01 00:00:00到指定时间所经历的秒数. 有了这个函数,就可以很自然地把时间比较转换为一个无符号整数的比较. 例如,判断一个时间是否在一个区间内 unix_timestamp

  • 详解一条sql语句在mysql中是如何执行的

    概览 最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在mysql中的执行流程,包括sql的查询在mysql内部会怎么流转,sql语句的更新是怎么完成的. 一.mysql架构分析 下面是mysql的一个简要架构图: mysql主要分为Server层和存储引擎层 Server层:主要包括连接器.查询缓存.分析器.优化器.执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程.触发器.视图,函数等,还有一个通用的日志模块 bing

  • 聊聊MySQL中的存储引擎

    基础知识 在关系型数据库中每一个数据表相当于一个文件,而不同的存储引擎则会构建出不同的表类型. 存储引擎的作用是规定数据表如何存储数据,如何为存储的数据建立索引以及如何支持更新.查询等技术的实现. 在Oracle以及SqlServer等数据库中只支持一种存储引擎,故其数据存储管理机制都是一样的,而MySQL中提供了多种存储引擎,用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的存储引擎. 如处理文本文件可使用txt类型,处理图片可使用png类型 存储引擎 在My

随机推荐