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来改写扫描行数减少且会走索引。
上一篇: MySQL数据库修改密码