讲师中心 微信公众号

工具推荐

视频效率加速
Laper
Laper

剧本策划到成片协作

SoundView
SoundView

字幕擦除翻译、配音口型同步

立刻MV
立刻MV

低成本歌曲 MV、视频生成

Seko
Seko

AI短剧一键生成成片

MySQL中使用or、in与union all在查询命令下的效率对比_MySQL

梦丽酱_2286

梦丽酱_2286

发布时间:2016-05-27 13:45:34

|

2015人浏览过

|

来源于php中文网

原创

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 RESULTALL 

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 RESULTALL

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 RESULTALL  

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的执行计划如下

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载

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千万数据类似

20151120103034816.jpg (264×198)

2:实战
    2.1 :分别在索引列上使用 or、in、union all
           我们创建的表只有主键索引,所以只能用ID做查询呢。我们查 ID 为 98,85220,9888589的三个数据各个耗时如下:

20151120103126115.jpg (622×560)

时间都为0.00,怎么会这样呢,呵呵所有查询都是在毫秒级别。
我使用其他的工具--EMS SQL Manager  for mysql
查询显示时间为
93 ms, 94ms,93 ms,时间相差了多少几乎可以忽略。
然后我们在看看各自的执行计划

20151120103145800.jpg (925×565)

这里要注意的字段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的三个数据各个耗时如下:

20151120103205820.jpg (638×573)

我们发现为啥union all查询时间几乎为 or 和in的三倍。
这是为什么呢,我们先不说,先看看三个的查询计划。

20151120103224862.jpg (1004×554)

这里我们发现计划几乎一样。
但我们要注意扫描的此时对于 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: 以上主要针对的是单表,而多表联合查询来说,考虑的地方就比较多了,比如连接方式,查询表数据量分布、索引等,再结合单表的策略选择合适的关键字。 

以上就是MySQL中使用or、in与union all在查询命令下的效率对比_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!

热门AI工具

更多
二狗PPT
二狗PPT Hot

一款AI演示文稿工具,主要用于专为中式职场打造的AI PPT生成工具,适合需要提升相关任务效率的用户。

笔灵降AI

一款AI论文写作工具,主要用于论文降AI神器,适配知网及维普!一键降至安全线,100%保留原文格式;无口语化问题,文风更学术,降后字数控制最佳!,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

函子Zion

一款支持AI搭建、可视化编辑和多端发布的全栈无代码开发平台。

火山引擎

火山引擎是一款面向企业的云计算与AI服务平台。

立刻MV
立刻MV Hot

立刻MV是一款AI文本写作工具,AI 音乐视频(MV)创作工具。

SkildArt
SkildArt Hot

SkildArt是一款AI文本写作工具,一站式 AI 视觉创作平台。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

相关专题

更多
Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

160

2026.11.12

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

100

2026.11.12

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

100

2026.11.12

Postman身份认证怎么配置
Postman身份认证怎么配置

本专题从实际API接口调试场景出发,讲解Postman身份认证的完整配置流程,包括常见认证协议介绍、授权参数设置、访问令牌填写、OAuth 2.0授权及认证失败排查,帮助用户正确完成接口授权与访问验证。

180

2026.11.06

Postman请求头Headers怎么设置
Postman请求头Headers怎么设置

围绕Postman HTTP请求头配置展开,讲解Headers选项的具体使用方式,包括Key和Value参数填写、请求头信息添加、常见Header字段设置及请求头修改方法,帮助用户解决接口调试过程中遇到的请求头配置问题。

140

2026.11.06

Postman接口请求发送教程
Postman接口请求发送教程

详细介绍Postman接口请求的创建与发送方法,涵盖GET和POST请求、URL配置、请求参数、Headers请求头、Body请求体设置以及响应结果查看,帮助用户掌握Postman接口请求的完整操作流程。

120

2026.11.06

DBeaver怎么导入数据
DBeaver怎么导入数据

DBeaver数据导入专题,整理Excel、CSV、SQL、TXT等常见文件的数据导入方法,涵盖目标数据库选择、数据表创建、字段映射、数据类型设置及导入参数配置等操作,帮助用户将本地文件中的数据写入数据库,并了解批量导入、重复数据处理和导入失败等常见问题的解决方法。

120

2026.11.05

DBeaver安全防护教程大全
DBeaver安全防护教程大全

DBeaver安全防护专题,围绕数据库连接安全、账号密码保护和访问权限管理展开,整理SSL/TLS加密连接、SSH隧道、数据库凭据保存及证书验证等配置方法,同时介绍敏感信息保护、远程连接风险和高权限账号使用等注意事项,帮助用户提高数据库日常管理的安全性。

100

2026.11.05

DBeaver连接创建与配置教程
DBeaver连接创建与配置教程

本专题聚焦DBeaver数据库连接配置与管理,覆盖从新建连接、选择数据库类型到填写连接参数和验证连接的完整流程。针对MySQL、PostgreSQL、SQL Server等常见数据库,整理相应的连接设置方法,并提供驱动缺失、认证失败和连接异常等问题的排查方向。

120

2026.11.04

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn