`

MySQL的or/in/union与索引优化

 
阅读更多
https://mp.weixin.qq.com/s/ZWez27EmVw_u7GzNbvXuYw

假设订单业务表结构为:
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可读性差,可理解性差,可维护性差,强烈不推荐
分享到:
评论

相关推荐

    MySQL的or、in、union与索引优化

    ### MySQL的or、in、union与索引优化 在数据库查询优化中,索引的使用至关重要,它能显著提升查询速度。本文将基于一个具体的业务场景来探讨在MySQL中使用`union all`、`in`、`or`以及负向查询(如`!=`)时如何有效...

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

    在上述例子中,当`first_name`列有索引时,`UNION ALL`的执行计划显示了两次`ref`,这表示对索引进行了两次非唯一性扫描,虽然执行时间较之前未建索引时有所降低,但与`OR`和`IN`相比,可能并不占优势。 当我们使用...

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

    本文主要探讨了`OR`、`IN`、`UNION`操作符以及它们如何与索引交互,以优化查询效率。 首先,我们来看`OR`条件。在旧版的MySQL中,使用`OR`连接的查询通常不会有效利用索引,导致全表扫描,从而降低性能。然而,在...

    mysql中or是否走索引详解

    本文将深入探讨`OR`语句在MySQL中的索引处理,以及如何优化这类查询以提高效率。 首先,我们需要理解索引的基本原理。索引是一种数据结构,它允许数据库快速定位到表中的特定行。B-Tree、Hash和R-Tree等是常见的...

    MySQL 索引及优化实战1

    2. `UNION`、`IN` 和 `OR` 查询:`UNION` 和 `IN` 均能利用索引,`OR` 在新版 MySQL 中也可以,但`IN` 通常更高效,推荐使用 `IN` 代替 `OR`。 3. 负向条件查询:如 `!=`、`&lt;&gt;`、`NOT IN`、`NOT EXISTS`、`NOT LIKE`...

    mysql数据库sql优化

    ### MySQL数据库SQL优化 #### 一、SQL优化 在MySQL数据库管理中,SQL查询的性能直接影响到系统的响应时间和资源消耗。通过合理的SQL优化,可以显著提高数据处理速度,降低服务器负载,提升用户体验。 ##### 1.1 ...

    mysql性能的优化

    - **index_subquery**:替代某些IN子查询的优化方法,适用于非唯一索引。 - **range**:检索给定范围内的行。 - **index**:仅扫描索引树。 - **ALL**:对每个前面表的行组合,进行全面的表扫描。 - **possible...

    MySql性能优化集合--满满的干货

    ### MySQL性能优化集合 #### 一、MySQL优化概述 MySQL作为一种广泛使用的开源关系型数据库管理系统,在数据处理方面表现优秀。然而,在高并发、大数据量的情况下,可能会遇到性能瓶颈。因此,进行MySQL性能优化是...

    查看mySQL数据库索引

    从这个例子中可以看出,MySQL使用了两个索引来优化查询:`idx_t4_id` 和 `idx_t4_accountid`。 通过以上内容的学习,我们可以了解到如何在MySQL中查看索引信息以及如何使用`EXPLAIN`命令来检查索引是否被正确地应用...

    mysql优化步骤方法

    例如,避免在WHERE子句中使用NOT IN、OR等操作,尽量改写为IN或UNION ALL;尽量避免在索引列上使用函数,因为这会阻止索引的使用。 2. **索引优化**:创建合理的索引可以显著提升查询速度。应根据查询模式选择合适...

    MySQL 通过索引优化含ORDER BY的语句

    11. **避免`OR`和`UNION`操作**:这些操作可能导致索引无法有效利用,可以尝试通过构造多个查询或使用`IN`子句来代替。 在进行索引优化时,应始终关注实际应用中的查询模式,并结合`EXPLAIN`结果进行调整。同时,...

    mysql优化笔记(加强版)

    尽量避免`OR`条件,可以尝试用`UNION`代替。 2. **索引设计**:合理创建单列索引、复合索引和覆盖索引。复合索引适用于多条件查询,覆盖索引能减少磁盘I/O,提高查询效率。 3. **避免使用SELECT ***:只选择需要的...

    mysql百万量级优化

    - `OR`操作符会使得MySQL无法使用索引来优化查询。可以采用多个`SELECT`语句联合查询的方式替代。 - 示例:将`SELECT id FROM t WHERE num = 10 OR num = 20;`改为`SELECT id FROM t WHERE num = 10 UNION ALL ...

    mysql数据库query的优化

    同时,避免在WHERE子句中使用OR条件,可以考虑使用UNION替换多个OR条件,以利用索引。 再者,合理使用JOIN操作。减少JOIN的数量,尽量使用内连接(INNER JOIN)代替外连接(LEFT JOIN、RIGHT JOIN),因为外连接可能...

    基于Mysql数据库的SQL优化

    - **避免使用OR连接条件**:使用`UNION`代替`OR`可以提高查询效率。 - **优化IN和NOT IN的使用**:对于连续数值,使用`BETWEEN`而非`IN`。 - **LIKE操作的优化**:使用前缀匹配而不是后缀匹配可以提高查询速度。 - *...

    Mysql数据库性能优化

    MySQL数据库性能优化是一个涵盖多个方面的主题,涉及到数据库配置、索引优化、查询优化、存储引擎选择、内存管理以及数据模型设计等多个环节。以下是对这些关键领域的详细解释: 1. **数据库配置**: - `my.cnf` ...

Global site tag (gtag.js) - Google Analytics