文档章节

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
粉丝 3
博文 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用法和结果的含义

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

雾妄
2016/12/22
40
0
[转]关于mysql中explain的那些事儿

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

小小人故事
2015/12/15
45
0

没有更多内容

加载失败,请刷新页面

加载更多

Hive的三种Join方式

Hive中就是把Map,Reduce的Join拿过来,通过SQL来表示。 参考链接:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins Common/Shuffle/Reduce Join Reduce Join在Hiv......

GordonNemo
9分钟前
0
0
Spark学习记录(三)核心API模块介绍

spark ------------- 基于hadoop的mr,扩展MR模型高效使用MR模型,内存型集群计算,提高app处理速度。 spark特点 ------------- 速度:在内存中存储中间结果。 支持多种语言。Scala、Java、P...

我爱春天的毛毛雨
14分钟前
0
0
PHP5、PHP7安装

11月13日任务 11.10/11.11/11.12 安装PHP5 11.13 安装PHP7 PHP官网www.php.net 当前主流版本为5.6/7.1 cd /usr/local/src/ wget http://cn2.php.net/distributions/php-5.6.32.tar.bz2 tar z......

zgxlinux
15分钟前
0
0
React 项目结构和组件命名之道

摘要: > * 原文地址:[structuring projects and naming components in react](https://hackernoon.com/structuring-projects-and-naming-components-in-react-1261b6e18d76) > * 原文作者:......

阿里云官方博客
15分钟前
2
0
无维护地稳定运行了8 年的 Hyperic HQ

最近在诊断一个系统意外停机时, 发现一个8年前部署部署的Hypeirc HQ 4.2,已经免维护,稳定运行了8年多。提供了及时的诊断信息。单击右下角的蓝色泡泡,可显示报警信息。

MartinKing
30分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部