相信很多人在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就不会使用到索引了，如下。