mysql索引的最左匹配原则,之前只知道这个原则并不知道为什么会这样,其中的原理并不清楚,在这里记录一下关于这个原则的理解。
最左匹配原则是针对于复合索引的,说的是索引以最左的为起点任何连续的索引都能匹配上,当遇到范围查询(>,<,between,like)就会停止匹配。
例如,假设有一张员工信息表,其他字段我们暂时不去关心,现在我们以name,age,sex建立复合索引。
当B+树的数据项是复合数据结构,如上面的(name,age,sex),B+树按照从左到右的顺讯进行建立搜索树的。
例如:
当类似(Logan,25,F)这样的数据进行检索的时候,B+树会优先通过比较name的值进行确定下一步的搜索方向。如果name相同了在依次比较age和sex,最后得到检索的数据。
但是当类似于(25,F)这样的缺失name的数据进行搜索的时候,B+树就不知道下一步该查哪一个节点,这是因为在建立搜索树的时候name是首要比较因子,必须要先根据name来搜索才能确定下一步去哪里进行查询。
又如类似(Logan,F)这样的数据进行检索的时候,B+树可以先通过name来指定搜索方向,但是下一个age字段缺失,所以在下一步的搜索中只能把name=Logan的数据找到后,在这些数据中匹配sex=F的数据了,这样体现了索引的最左匹配原则。
另外,还有一个值得注意的地方,例如表中有一个联合索引字段index(a,b,c),如下sql:
select * from tbName where a = 25 and b like 'XXX%' and c = 26;
在这个语句中只使用了索引a,b。
插播一个题外话
1)如何让 like %abc 走索引查询?
我们知道要让 like 查询走索引,查询字符不能以通配符 % 开始,如果要让 like %abc 走索引查询,可以使用reverse()函数来创建一个函数索引,查询脚本如下:
select * from tbName where reverse(f) like reverse('%abec');
2)覆盖索引
比如,对于联合索引key(a,b,c),那么实际上等于创建了key(a), key(a,b),key(a,b,c)三个索引,每多一个索引,就会多一些写操作和占用磁盘空间的开销,尤其是对大数据量的表来说,这可以减少一部分不必要的开销。如果使用如下sql查询:
select a, b, c from tbName where a = 1, b =1;
这就可以直接通过遍历索引取到数据,而无需回表查询,减少了随机的IO操作,从而有效的提升数据库的查询性能,这也是非常重要的数据库优化手段之一。
注:
普通索引查询到主键索引后,回到主键索引树搜索的过程,成为回表查询。
在InnDB中主键索引为什么会比普通索引的查询效率高?
这就是因为回表查询,即普通索引的查询会多执行一次检索操作。比如主键查询select * from tbName where id = 100 只需要搜索id的这颗B+树,而普通索引查询select * from tbName where f = 200 会先查询 f 索引树,得到 id 的值之后再去搜索 id 的B+树,因为多执行了一次检索,所以执行效率就比主键索引要低。