MySQL 5.7使用or是否会用到索引并提高查询效率的理解

栏目: 数据库 · Mysql · 发布时间: 4年前

内容简介:相信很多人在MySQL中看到了where条件中使用到了or就会以为这样是不会走索引的,通常会使用union all或者in 来进行优化,事实并不是想象的这样具体问题具体分析。下面我们来看看首先我们用sysbench生成两个100w行的表

相信很多人在 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来改写扫描行数减少且会走索引。

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2019-03/157215.htm


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

软件调试

软件调试

张银奎 / 电子工业出版社 / 2008-6 / 128.00元

围绕如何实现高效调试这一主题,本书深入系统地介绍了以调试器为核心的各种软件调试技术。本书共30章,分为6篇。第1篇介绍了软件调试的概况和简要历史。第2篇以英特尔架构(IA)的CPU为例,介绍了计算机系统的硬件核心所提供的调试支持,包括异常、断点指令、单步执行标志、分支监视、JTAG和MCE等。第3篇以Windows操作系统为例,介绍了计算机系统的软件核心中的调试设施,包括内核调试引擎、用户态调试子......一起来看看 《软件调试》 这本书的介绍吧!

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具