不等于和范围对查询使用索引的影响
不等于条件和范围条件对查询的影响
如果一条 SQL 的 where 条件是 i0=0 and i1=1 and i2=2，并且存在着联合索引 (i0,i1,i2)，那么索引的三个列都会被用到。想一个另一种情况 i0=0 and i1 != 1 and i2=2 这种情况下索引的三个列还都会被用到吗？
准备环境
准备测试环境，创建表并随机的填充数据。
mysql> show create table t;
+---------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` int NOT NULL AUTO_INCREMENT,
`i0` int NOT NULL,
`i1` int NOT NULL,
`i2` int NOT NULL,
`i3` int NOT NULL,
`c0` varchar(128) NOT NULL,
`c1` varchar(128) NOT NULL,
`f0` float NOT NULL,
`f1` float NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_i012` (`i0`,`i1`,`i2`)
) ENGINE=InnoDB |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
--
mysql> udpate t set i0 = i0 % 10;
mysql> udpate t set i1 = i1 % 10;
mysql> udpate t set i2 = i2 % 10;
--
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 1120000 |
+----------+
1 row in set (1.67 sec)
在表 t 上建立了联合索引 idx_i012 (i0,i1,i2),下面我们就要看一下不同的情况，分别能用到索引的哪些部分。
等值的情况
当联合索引的所有列都以等值的方式出现在 where 条件里的情况。
mysql> explain select i3 from t where i0=0 and i1=1 and i2 = 2;
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | t | NULL | ref | idx_i012 | idx_i012 | 12 | const,const,const | 110 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可以看到 key_len 等于 12，说明联合索引的各个列都有用到。
范围的情况
当索引的中间列是一个范围时。
mysql> explain select i3 from t where i0=0 and i1 between 1 and 5 and i2 = 2;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t | NULL | range | idx_i012 | idx_i012 | 12 | NULL | 4544 | 10.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
从执行计划可以看到 key_len 为 12 ，也就是说 MySQL 用到全部的索引列。
不等于的情况
当索引的中间列是不等于的情况。
mysql> explain select i3 from t where i0=0 and i1!=1 and i2 = 2;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
| 1 | SIMPLE | t | NULL | range | idx_i012 | idx_i012 | 8 | NULL | 19196 | 10.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------+
1 row in set, 1 warning (0.01 sec)
当中间列是不等于时 key_len 只有 8 ，说明只用到了 i0 和 i1 这两个列。
结论
如果“不等于条件”出现在联合索引中间列，那么将导致查询无法使用联合索引后面那些的列。