关于MySQL复合索引的使用方法

前言

MySQL的复合索引可以创建多个，每个复合索引可以包含一列或多列。复合索引使用的基本原则是左侧对齐原则。例如，复合索引包含A，B，C字段，实际相当于创建了5个索引，即：

索引1：A；
索引2：B;
索引3：C；
索引4：A，B；
索引5：A，B，C;

那么问题来了，如果我们创建两个复合索引，复合索引1：包含A，B，C列和复合索引2：包含B，C列，MySQL如何执行呢？

验证符合索引的最左匹配原则

数据库版本

SELECT VERSION();

MySQL版本.png

创建数据表

CREATE TABLE IF NOT EXISTS `test_index`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `A` VARCHAR(100) NOT NULL,
   `B` VARCHAR(40) NOT NULL,
   `C` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建复合索引，包含列A，列B，列C

CREATE INDEX A_B_C_index ON test_index (A,B,C);

查询索引信息

SHOW INDEX FROM test_index;

复合索引查询结果.png

验证查询

查询条件为A列

EXPLAIN SELECT * FROM   test_index WHERE A = 'a';

查询条件为A列结果.png

结果：命中复合索引A_B_C_index.

\color{red}{分析：type=ref，完美命中。}

查询条件为B列

EXPLAIN SELECT * FROM   test_index WHERE B = 'a';

查询条件为B列结果.png

结果：命中复合索引A_B_C_index.

\color{red}{分析：type=index，命中复合索引，但需要对复合索引全部扫描。}

查询条件为C列

EXPLAIN SELECT * FROM   test_index WHERE C = 'a';

查询条件为C列结果.png

结果：命中复合索引A_B_C_index.

\color{red}{分析：type=index，命中复合索引，但需要对复合索引全部扫描。}

查询条件为A AND B列

EXPLAIN SELECT * FROM   test_index WHERE A = 'a' AND B = 'a';

查询条件为A AND B列结果.png

结果：命中复合索引A_B_C_index.

\color{red}{分析：type=index，完美命中。}

查询条件为A AND B AND C列

EXPLAIN SELECT * FROM   test_index WHERE A = 'a' AND B = 'a' AND C ='c';

查询条件为A AND B AND C列结果.png

结果：命中复合索引A_B_C_index.

\color{red}{分析：type=index，完美命中。}

查询条件为A AND C列

EXPLAIN SELECT * FROM   test_index WHERE A = 'a' AND C ='c';

查询条件为A AND C列结果.png

结果：命中复合索引A_B_C_index.

\color{red}{分析：这里条件是“与”，命中的原因是，查询条件包含A列，而A列是复合索引的一部分，所以完美命中。假如，这里的条件是“或”，则不会命中。}

查询条件为B AND C列

EXPLAIN SELECT * FROM   test_index WHERE B = 'a' AND C ='c';

查询条件为B AND C列.png

\color{red}{分析：type=index，命中复合索引，但需要对复合索引全部扫描。}

验证我们一开始的问题

增加复合索引，包含B列C列

CREATE INDEX B_C_index ON test_index (B,C);

查看建立后的结果

SHOW INDEX FROM test_index;

图像_2021-12-05_114757.png

$\color{red}{注意：A\_B\_C\_index索引在B\_C\_index 索引前面}$

选取上述测试中的一条，查询条件包含A列，B列，C列

EXPLAIN SELECT * FROM   test_index WHERE A = 'a' AND B = 'a' AND C = 'a' ;

按照正常的逻辑，和复合索引的原则，应该能命中的索引是A_B_C_index，让我们拭目以待吧！

查询条件包含A列，B列，C列的结果.png

结果：不负众望（嘿嘿）

（大戏上场）首先，删除所有复合索引

ALTER TABLE test_index DROP INDEX A_B_C_index;
ALTER TABLE test_index DROP INDEX B_C_index;

查看删除结果

SHOW INDEX FROM test_index;

图像_2021-12-05_113311.png

结果：删除成功

重新建立复合索引，而且和上述两个完全一样。
$\color{red}{注意：这次建立复合索引的前后顺序调换一下。}$

CREATE INDEX B_C_index ON test_index (B,C);
CREATE INDEX A_B_C_index ON test_index (A,B,C);

查看调换后的结果

SHOW INDEX FROM test_index;

图像_2021-12-05_113950.png

结果：两个索引创建成功。

重新测试查询条件包含A列，B列，C列

EXPLAIN SELECT * FROM   test_index WHERE A = 'a' AND B = 'a' AND C = 'a' ;

图像_2021-12-05_114238.png

结果：和上次测试的不一致，这次虽然包含ABC三个列，但命中的索引是B_C_index

重要结论：当命中两个或者多个不同的复合索引时，按照创建顺序不同，MySQL会有不同策略来选取其中的一个复合索引。