在我们日常的开发中,都会涉及到给mysql建索引来提高sql性能。而使用联合索引时要遵循最左匹配原则,意思就是在建联合索引的时候,索引字段按照先后顺序排列,然后在使用的时候也要按照这个顺序来使用。
比如说有一个user表,里面有三个字段:name、sex、 age;我们用这三个字段建了一个联合索引,在使用的时候我们就只能这样去用:where name = "" and sex = "" and age = "" 或 where name = "" and sex = ""。以下情况,sql执行时索引就会失效:where sex= "" and name = "" and age = "" 或 where sex = "" and age = "" 或 where age = ""。这是为什么呢?
要回答这个问题,先要弄明白索引是什么东西,索引其实就是排好序的数据结构,索引的作用相当于书的目录,可以根据目录中的页码快速找到所需的内容。mysql用的是b+tree的数据结构来存储数据,而b+tree的根节点和子节点存的都是冗余的索引数据,主键索引的叶子节点存的是索引和原始数据,其他索引的叶子节点存的是索引和主键索引地址。如果是联合索引的情况,索引排序就是按照联合字段的顺序来逐一排序,比如上面说的情况,就是先根据name来排第一次序,然后sex第二次,age第三次,如果有更多字段,以此类推;索引生成好之后,b+tree的结构图大致如下:
如图所示,联合索引是按照索引字段的先后顺序逐一排序的,查找数据的时候,也是按照索引字段顺序来查找。比如,要查找图中name=rose,sex=man,age=27的数据,那先根据根节点的name字段找到子节点里面包含rose的所有数据,然后根据man字段找到name=rose,而且sex=man的数据,最后根据age=27找到最终需要返回的数据。现在再看看刚刚提出的问题,就会明白如果在使用索引的过程中,打乱了联合索引的顺序。那么在查找数据时,就无法按照索引排好的顺序去查找数据,需要查找所有的子节点和叶子节点,此时产生全表扫描,造成索引失效,这就是最左匹配原则产生的原理。