复合索引
复合索引又称组合索引。查询优化器将复合索引用于测试索引中所有列的查询,或者测试第一列,前两列等的查询。
- 创建语句
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%';