文档章节

MySQL的or/in/union与索引优化 | 架构师之路

kim_o
 kim_o
发布于 2017/07/21 17:58
字数 661
阅读 57
收藏 0

原创 2017-07-15 58沈剑 架构师之路

本文缘起自《一分钟了解索引技巧》的作业题。

 

假设订单业务表结构为:

order(oid, date, uid, status, money, time, …)

其中:

  • oid,订单ID,主键

  • date,下单日期,有普通索引,管理后台经常按照date查询

  • uid,用户ID,有普通索引,用户查询自己订单

  • status,订单状态,有普通索引,管理后台经常按照status查询

  • money/time,订单金额/时间,被查询字段,无索引

 

假设订单有三种状态:0已下单,1已支付,2已完成

业务需求,查询未完成的订单,哪个SQL更快呢?

  • select * from order where status!=2

  • select * from order where status=0 or status=1

  • select * from order where status IN (0,1)

  • select * from order where status=0

    union all

    select * from order where status=1

 

结论:方案1最慢,方案2,3,4都能命中索引

 

但是... 

 

一:union all 肯定是能够命中索引的

select * from order where status=0

union all

select * from order where status=1

说明:

  • 直接告诉MySQL怎么做,MySQL耗费的CPU最少

  • 程序员并不经常这么写SQL(union all)

 

二:简单的in能够命中索引

select * from order where status in (0,1)

说明:

  • 让MySQL思考,查询优化耗费的cpu比union all多,但可以忽略不计

  • 程序员最常这么写SQL(in),这个例子,最建议这么写

 

三:对于or,新版的MySQL能够命中索引

select * from order where status=0 or status=1

说明:

  • 让MySQL思考,查询优化耗费的cpu比in多,别把负担交给MySQL

  • 不建议程序员频繁用or,不是所有的or都命中索引

  • 对于老版本的MySQL,建议查询分析下

 

四、对于!=,负向查询肯定不能命中索引

select * from order where status!=2

说明:

  • 全表扫描,效率最低,所有方案中最慢

  • 禁止使用负向查询

 

五、其他方案

select * from order where status < 2

这个具体的例子中,确实快,但是:

  • 这个例子只举了3个状态,实际业务不止这3个状态,并且状态的“值”正好满足偏序关系,万一是查其他状态呢,SQL不宜依赖于枚举的值,方案不通用

  • 这个SQL可读性差,可理解性差,可维护性差,强烈不推荐

 

六、作业

这样的查询能够命中索引么?

  • select * from order where uid in (

             select uid from order where status=0

    )

  • select * from order where status in (0, 1) order by date desc

  • select * from order where status=0 or date <= CURDATE()

 

注:此为示例,别较真SQL对应业务的合理性。

本文转载自:

共有 人打赏支持
kim_o
粉丝 2
博文 79
码字总数 28082
作品 0
深圳
程序员
如何高效快速地优化MySQL、SQL语句(附源码)

作者介绍 韩锋,宜信技术研发中心数据库架构师。精通多种关系型数据库,曾任职于当当网、TOM在线等公司,曾任多家公司首席DBA、数据库架构师等职,多年一线数据库架构、设计、开发经验。著有...

Yomut
2016/10/10
135
0
如何用一款小工具大大加速MySQL SQL语句优化(附源码)

作者介绍 韩锋,宜信技术研发中心数据库架构师。精通多种关系型数据库,曾任职于当当网、TOM在线等公司,曾任多家公司首席DBA、数据库架构师等职,多年一线数据库架构、设计、开发经验。著有...

韩锋
2016/09/26
0
0
令仔学MySql系列(一)----explain详解

explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。下面是一个例子: 然后咱们来说一说表格中每一列的具体含义。 SELECT识别符。这...

令仔很忙
2017/03/07
0
0
[转]关于mysql中explain的那些事儿

explain语法 有两种用法:1.EXPLAIN tbl_name2.EXPLAIN [EXTENDED] SELECT select_options 为了更好的说明它,我们需要建两张表,下面的语句用于创建一张测试用的订单表: CREATE TABLE t_o...

小小人故事
2015/12/15
45
0
mysql explain用法和结果的含义

//正文开始 重点是第二种用法,需要深入的了解。 先看一个例子: 加上extended后之后: 有必要解释一下这个长长的表格里每一列的含义: id SELECT识别符。这是SELECT的查询序列号 select_typ...

雾妄
2016/12/22
40
0

没有更多内容

加载失败,请刷新页面

加载更多

Java Lock接口分析之ReentantReadWriteLock

ReentantReadWriteLock读写锁,在读线程多余写线程的并发环境中能体现出优异的性能,相比于synchronized与ReentrantLock这种独占式锁的模型,ReentantReadWriteLock采用独占式写锁与共享式读...

我爱春天的毛毛雨
35分钟前
1
0
EFK (Fluentd ElasticSearch Kibana) 采集nginx日志

本文描述如何通过FEK组合集中化nginx的访问日志。本人更喜欢按顺序来命名,所以使用FEK而不是EFK. 首先在nginx服务器上执行以下操作. 安装ruby http://blog.csdn.net/chenhaifeng2016/artic...

xiaomin0322
36分钟前
1
0
一键下载:将知乎专栏导出成电子书

老是有同学问,学了 Python 基础后不知道可以做点什么来提高。今天就再用个小例子,给大家讲讲,通过 Python 和爬虫,可以完成怎样的小工具。 在知乎上,你一定关注了一些不错的专栏(比如 ...

crossin
46分钟前
2
0
synchronized 之 对象锁 和 类锁

一、synchronized(object) 如果object没有被加锁,则获取object的锁;如果object已经被加锁则等待object的锁被释放。 二、需要加锁的情景 多线程共享同一资源会引起线程安全的情况下,才需要...

MyOldTime
47分钟前
7
0
tomcat 单机/多机 部署多应用

一.单机部署多应用: 1.在 linux 下解压安装两个 tomcat:tomcat1, tomcat2; 2.修改 /etc/profile, 增加 tomcat 环境变量: path 中加上 重新加载配置文件 source /etc/profile 3.修改 tomc...

imbiao
58分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部