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';
以下结果按顺序展示,我们一个个看:
第一个:
首先第一个使用主键查询,可以看到执行计划中说明使用了主键,扫描行数是1,非常高效
第二个:
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA='A1';
可以看到同样使用了索引,这次使用的是我们创建的联合索引索引: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' ;
只是使用BB或者CC字段,并不会使用索引,扫描行数10
第五个:
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA='A1' AND tci.CC='C1';
这里是我们有争议的一个点,可以看到的确使用了索引,但是key_len索引使用的字节数是152和第二个语句只用A查询,使用的字节数是一致的,所以这句的结论是:A、C查询的时候虽然使用了索引,实际其实只用了A,而不是AC
第六个:
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.BB='B1' AND tci.CC='C1';
显而易见,B、C查询的时候并不会使用索引
第七个
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA='A1' AND tci.BB='B1';
最后是正常使用的联合索引,遵循最左匹配,索引使用的字节数:304,正好是之前命中单个字段的双倍
最后补充说明面试中可能碰到的问题:
如果是组合索引,且遵循最左匹配,如果其中有字段是范围查询,那么:命中的字段只会到范围查询那个字段,比如:
EXPLAIN select * FROM TEST_COMPOSITE_INDEX tci where tci.AA>'A1' AND tci.BB='B1';
那么这次查询使用的索引字段只有:AA,而不会使用BB