Mysql复合索引的创建以及失效情况

复合索引

复合索引又称组合索引。查询优化器将复合索引用于测试索引中所有列的查询，或者测试第一列，前两列等的查询。

• 创建语句

CREATE INDEX index_name ON table_name(c2,c3,c4);

复合索引的优点

• 减少开销：

一个联合索引，等价与（c2）,(c2,c3)，(c2,c3,c4)三个索引。每创建一个索引都会增加写操作和磁盘的开销。

• 覆盖索引

符合索引在很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率同时也是一种常用的优化手段。

复合索引的缺点

• 索引字段越多，创建的索引越多，增加磁盘的开销。
• 需要满足很多原则（左前缀法则等），容易失效。

符合索引失效的场景

数据准备

准备一张表名为words，约有12万条数据。为word_spell，word_meaning，word_level三个创建一个复合索引名为index_sml。查看结果

最左前缀法则

如果索引了多列，要遵守最左前缀法则。意思是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则。

遵守最左前缀法则

explain select * from words where word_spell='dog';

explain select * from words where word_spell='street' and word_meaning='n.街道';

 

explain select * from words where word_spell='street' and word_meaning='n.街道' and word_level='CET4';

索引失效违反最左前缀法则

explain select * from words where word_meaning='n.街道' and word_level='CET4';

范围查询右边的列，索引失效

如下，word_level索引失效。

explain select * from words where word_spell='street' and word_meaning>'1' and word_level ='CET4';

在索引列上进行运算操作，该索引列失效

explain select * from words where word_spell='street' and substring(word_meaning,0,3)='n.街';

字符串不加单引号，造成索引失效

explain select * from words where word_spell='street' and word_meaning=0;

以%开头的模糊查询，索引失效

注：如果仅仅是尾部模糊查询，索引不失效，如果是头部模糊查询，索引失效。

•  头部模糊查询，索引失效

explain select * from words where word_spell like '%reet';

 

• 尾部模糊查询，索引不会失效。

explain select * from words where word_spell like 'stre%';