mysql5.7关于使用到OR是否会用到索引并提高查询效率的探讨

Wesley13
• 阅读 314

原文: mysql5.7关于使用到OR是否会用到索引并提高查询效率的探讨

相信很多人在mysql中看到了where条件中使用到了or就会以为这样是不会走索引的,通常会使用union all或者in 来进行优化,事实并不是想象的这样具体问题具体分析。

下面我们来看看

首先我们用sysbench生成两个100w行的表

表结构如下

mysql> show create table sbtest1 \G;
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c_1` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table sbtest2 \G;
*************************** 1. row ***************************
       Table: sbtest2
Create Table: CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`),
  KEY `c_2` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

1.首先我们使用同一列带索引字段的进行查询。

mysql> explain select * from sbtest1 where k='501462' or k='502480';   
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range | k_1           | k_1  | 4       | NULL |  214 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

从执行计划中看出这样是可以使用到索引的,另外我们使用in 或者union all来看。

mysql> explain select pad from sbtest1 where k in ('501462','502480');
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range | k_1           | k_1  | 4       | NULL |  214 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

in的执行计划和or相同。

mysql>  explain select pad from sbtest1 where k='501462' union all select pad from sbtest1 where k='502480';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | sbtest1 | NULL       | ref  | k_1           | k_1  | 4       | const |  113 |   100.00 | NULL  |
|  2 | UNION       | sbtest1 | NULL       | ref  | k_1           | k_1  | 4       | const |  101 |   100.00 | NULL  |

虽然执行计划不通但union all估计的查询行数和上面相同。

2.我们再来看看不同列带索引字段的进行查询

mysql> explain select pad from sbtest1 where  k='501462' or c='68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441';
+----+-------------+---------+------------+-------------+---------------+---------+---------+------+------+----------+-----------------------------------+
| id | select_type | table   | partitions | type        | possible_keys | key     | key_len | ref  | rows | filtered | Extra                             |
+----+-------------+---------+------------+-------------+---------------+---------+---------+------+------+----------+-----------------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | index_merge | k_1,c_1       | k_1,c_1 | 4,120   | NULL |  114 |   100.00 | Using union(k_1,c_1); Using where |
+----+-------------+---------+------------+-------------+---------------+---------+---------+------+------+----------+-----------------------------------

这样的情况也会使用索引

如果or的条件中有个条件不带索引的话,那这条sql就不会使用到索引了,如下。

mysql> explain select pad from sbtest1 where  k='501462' or pad='00592560354-80393027097-78244247549-39135306455-88936868384';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | k_1           | NULL | NULL    | NULL | 986400 |    19.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

pad列没索引所以整条的sql就不会使用到索引

假设使用union all来改写一样需要全表扫描所以意义也不大,如下

mysql>  explain select pad from sbtest1 where  k='501462' union all select pad from sbtest1 where pad='00592560354-80393027097-78244247549-39135306455-88936868384';
+----+-------------+---------+------------+------+---------------+------+---------+-------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+-------+--------+----------+-------------+
|  1 | PRIMARY     | sbtest1 | NULL       | ref  | k_1           | k_1  | 4       | const |    113 |   100.00 | NULL        |
|  2 | UNION       | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL  | 986400 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+-------+--------+----------+-------------+

3.接下来我们看看多表关联查询

mysql> explain select a.pad,b.pad from sbtest1 a,sbtest2 b where a.id=b.id and (a.c='123' or b.c='1234');                 
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY,c_1   | NULL    | NULL    | NULL      | 986400 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY,c_2   | PRIMARY | 4       | test.a.id |      1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select a.pad,b.pad from sbtest1 a,sbtest2 b where a.id=b.id and (a.c='123' or a.c='1234'); 
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-----------------------+
|  1 | SIMPLE      | a     | NULL       | range  | PRIMARY,c_1   | c_1     | 120     | NULL      |    2 |   100.00 | Using index condition |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.id |    1 |   100.00 | NULL                  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

可以看出在多表查询的情况下or条件如果不在同一个表内执行计划表a的查询不走索引。

我们试试看用union all来进行改写

mysql> explain select a.pad,a.c from sbtest1 a,sbtest2 b where a.id=b.id and a.c='123' union all select a.pad,a.c from sbtest1 a,sbtest2 b where a.id=b.id and b.c='1234';
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
|  1 | PRIMARY     | a     | NULL       | ref    | PRIMARY,c_1   | c_1     | 120     | const     |    1 |   100.00 | NULL        |
|  1 | PRIMARY     | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.id |    1 |   100.00 | Using index |
|  2 | UNION       | b     | NULL       | ref    | PRIMARY,c_2   | c_2     | 120     | const     |    1 |   100.00 | Using index |
|  2 | UNION       | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.b.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

在or的条件不在同一个表的情况下 使用union all来改写扫描行数减少且会走索引。

点赞
收藏
评论区
推荐文章
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Stella981 Stella981
1年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
1年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
1年前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序selectfromtable_nameorderiddesc;2.按照指定(多个)字段排序selectfromtable_nameorderiddesc,statusdesc;3.按照指定字段和规则排序selec
Stella981 Stella981
1年前
Angular material mat
IconIconNamematiconcode_add\_comment_addcommenticon<maticonadd\_comment</maticon_attach\_file_attachfileicon<maticonattach\_file</maticon_attach\
Wesley13 Wesley13
1年前
PHP中的NOW()函数
是否有一个PHP函数以与MySQL函数NOW()相同的格式返回日期和时间?我知道如何使用date()做到这一点,但是我问是否有一个仅用于此的函数。例如,返回:2009120100:00:001楼使用此功能:functiongetDatetimeNow(){
Wesley13 Wesley13
1年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
京东云开发者 京东云开发者
1个月前
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
helloworld_34035044 helloworld_34035044
6个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
3A网络 3A网络
4个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
开发一个不需要重写成HiveQL的大数据SQL引擎学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。各种知识表象看杂乱无章,若只是学习
3A网络 3A网络
4个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
理解virt、res、shr之间的关系(linux系统篇)前言想必在linux上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题——你的程序在运行时占用了多少内存(物理内存)?通常我们可以通过t