内容简介：相信很多人在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
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Beautiful Code
Greg Wilson、Andy Oram / O'Reilly Media / 2007-7-6 / GBP 35.99
In this unique work, leading computer scientists discuss how they found unusual, carefully designed solutions to difficult problems. This book lets the reader look over the shoulder of major coding an......一起来看看 《Beautiful Code》 这本书的介绍吧!
