MySQL之索引：索引字段使用顺序对复合索引有效性的影响

整理下复合索引中，使用顺序对索引有效性的影响

首先这里建立一张数据表，并建立符合索引（ index_A,index_B,index_C）

CREATE TABLE `test_index_sequence` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `index_A` varchar(255) DEFAULT NULL,
  `index_B` varchar(255) DEFAULT NULL,
  `index_C` varchar(255) DEFAULT NULL,
  `field_D` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `complex_index` (`index_A`,`index_B`,`index_C`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

并插入若干数值：

数据插入完成后，下面开始逐步验证索引的有效性。

 

1、单独使用字段index_A：

EXPLAIN
SELECT *
FROM test_index_sequence
WHERE index_A='a' 

通过EXPLAIN分析可以看到访问类型为ref，扫描行数为3行，索引有效；

2、单独使用字段index_B：

EXPLAIN
SELECT *
FROM test_index_sequence
WHERE index_B='b'

通过EXPLAIN分析可以看到访问类型为ALL即全表扫描，扫描行数为9行即全部行数，索引失效；

3、单独使用字段index_C：

EXPLAIN
SELECT *
FROM 
test_index_sequence
WHERE index_C='c'

查看分析结果同单独使用使用字段 index_B，索引失效；

4 、联合使用字段 index_A,index_B ：

EXPLAIN
SELECT *
FROM test_index_sequence
WHERE index_A='a' AND index_B='b' 

查看分析结果，索引有效；

5 、联合使用字段 index_A,index_C ：

EXPLAIN
SELECT *
FROM test_index_sequence
WHERE index_A='a' AND index_C='c' 

查看分析结果， 索引有效；

6、联合使用字段index_B,index_C：

EXPLAIN
SELECT *
FROM test_index_sequence
WHERE index_B='b' AND index_C='c' 

查看分析结果，索引失效；

7、联合使用字段index_A,index_B,index_C：

EXPLAIN
SELECT *
FROM test_index_sequence
WHERE index_A='a' AND index_B='b' AND index_C='c'

查看分析结果，索引有效；

综述：MySQL的复合索引遵循了最左前缀原则，当建立索引（index_A,index_B,index_C）时：

使用索引有效的字段为：'index_A’，'index_A,index_C’，'index_A,index_B’，'index_A,index_B,index_C’；

使用索引失效的字段为：'index_B’，'index_C’，'index_C,index_B’；