欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-17 16:17:36
相信很多人在mysql中看到了where条件中使用到了or就会以为这样是不会走索引的,通常会使用union all或者in 来进行优化,事实并不是想象的这样具体问题具体分析。 下面我们来看看 首先我们用sysbench生成两个100w行的表 表结构如下 1.首先我们使用同一列带索引字段的进行查询。 ......

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