mysql复合索引abcac可以索引吗_mysql组合索引，abc索引命中

女爷i 2023-01-11 10:58 197阅读 0赞

mysql联合索引，abc的争议实践

原因：

在一次和同事讨论mysql联合索引的面试题时出现了争议。主要问题是：a、b、c三个字段作为联合索引，b、c；和a、c情况到底会不会命中索引？

网上查阅相关博客发现很多答案不一样，于是我干脆亲手操作实验一下 ，我使用的mysql版本是5.6

一:创建表

为了更直接贴合面试题，字段直接用AA,BB,CC表示

create table IF NOT EXISTS TEST\_COMPOSITE\_INDEX

(

\`TID\` BIGINT NOT NULL AUTO\_INCREMENT,

\`AA\` VARCHAR(50) NOT NULL DEFAULT '' ,

\`BB\` VARCHAR(50) NOT NULL DEFAULT '',

\`CC\` VARCHAR(50) NOT NULL DEFAULT '',

\`DD\` VARCHAR(50) NOT NULL DEFAULT '',

\`create\_time\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP,

PRIMARY KEY (\`TID\`),

KEY \`index\_comp\` (\`AA\`,\`BB\`,\`CC\`)

)ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

sql中我们有两个索引，一：主键TID，二：AA、BB、CC组成的联合索引：index\_comp

二:插入数据

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A1','B1','C1','D1');

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A2','B2','C2','D2');

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A3','B3','C3','D3');

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A4','B4','C4','D4');

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A5','B5','C5','D5');

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A6','B6','C6','D6');

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A7','B7','C7','D7');

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A8','B8','C8','D8');

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A9','B9','C9','D9');

insert into TEST\_COMPOSITE\_INDEX (AA,BB,CC,DD)VALUE('A10','B10','C10','D10');

三:查看执行计划

我们分别查看以下执行计划：

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.tid='3' ;

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.AA='A1';

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.BB='B1';

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.CC='C1' ;

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.AA='A1' AND tci.CC='C1';

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.BB='B1' AND tci.CC='C1';

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.AA='A1' AND tci.BB='B1';

以下结果按顺序展示，我们一个个看：

第一个：

![5bf3514b4697cd67f6f63daaede3beef.png][]

首先第一个使用主键查询，可以看到执行计划中说明使用了主键，扫描行数是1，非常高效

第二个：

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.AA='A1';

![c4d5250a428799ea6a1caa4335fdbd24.png][]

可以看到同样使用了索引，这次使用的是我们创建的联合索引索引：index\_comp。注意key\_len:索引使用的字节数，这个后面会用作对比

第三、四个：

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.BB='B1';

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.CC='C1' ;

![3fed689b6bc393da69da902ff0b41cfb.png][]

![7653c79bb7c5c743afce74d127a48c32.png][]

只是使用BB或者CC字段，并不会使用索引，扫描行数10

第五个：

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.AA='A1' AND tci.CC='C1';

![de85b33b6f16efd0657ad81c1c739068.png][]

这里是我们有争议的一个点，可以看到的确使用了索引，但是key\_len索引使用的字节数是152和第二个语句只用A查询，使用的字节数是一致的，所以这句的结论是：A、C查询的时候虽然使用了索引，实际其实只用了A，而不是AC

第六个：

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.BB='B1' AND tci.CC='C1';

![a65dfb913488cf4dbf4f2bb555753d60.png][]

显而易见，B、C查询的时候并不会使用索引

第七个

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.AA='A1' AND tci.BB='B1';

![867e531a6244adc40ca9904722cf837b.png][]

最后是正常使用的联合索引，遵循最左匹配，索引使用的字节数：304，正好是之前命中单个字段的双倍

最后补充说明面试中可能碰到的问题：

如果是组合索引，且遵循最左匹配，如果其中有字段是范围查询，那么：命中的字段只会到范围查询那个字段，比如：

EXPLAIN select \* FROM TEST\_COMPOSITE\_INDEX tci where tci.AA>'A1' AND tci.BB='B1';

那么这次查询使用的索引字段只有：AA,而不会使用BB

[5bf3514b4697cd67f6f63daaede3beef.png]: /images/20221119/062bb24744a2493db7f3ede5970ffd03.png
[c4d5250a428799ea6a1caa4335fdbd24.png]: https://img-blog.csdnimg.cn/img_convert/c4d5250a428799ea6a1caa4335fdbd24.png
[3fed689b6bc393da69da902ff0b41cfb.png]: https://img-blog.csdnimg.cn/img_convert/3fed689b6bc393da69da902ff0b41cfb.png
[7653c79bb7c5c743afce74d127a48c32.png]: https://img-blog.csdnimg.cn/img_convert/7653c79bb7c5c743afce74d127a48c32.png
[de85b33b6f16efd0657ad81c1c739068.png]: https://img-blog.csdnimg.cn/img_convert/de85b33b6f16efd0657ad81c1c739068.png
[a65dfb913488cf4dbf4f2bb555753d60.png]: https://img-blog.csdnimg.cn/img_convert/a65dfb913488cf4dbf4f2bb555753d60.png
[867e531a6244adc40ca9904722cf837b.png]: https://img-blog.csdnimg.cn/img_convert/867e531a6244adc40ca9904722cf837b.png