常见⾯试题——索引最左前缀匹配原则源码论坛全站打包
在⾯试中，经常会遇到这种问题，如果我们设置联合索引的顺序是(a, b, c)， 那么如果我们在查询时的顺序却是(a, c, b) 会⾛索引吗？这个问题被问到的频率之⾼，令⼈乍⾆，在这篇⽂章中，我们就深⼊探究⼀下，应该如何回答这⼀类问题，并且探寻他背后的原理，那就是最左匹配原则。
最左前缀匹配原则：在MySQL建⽴联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是⼀颗B+树，那么联合索引的底层也就是⼀颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建⼀棵B+树只能根据⼀个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。
举例：创建⼀个（a,b）的联合索引，那么它的索引树就是下图的样⼦。
可以看到a的值是有顺序的，1，1，2，2，3，3，⽽b 的值是没有顺序的1，2，1，4，1，2。但是我们⼜可发现a在等值的情况下，b值⼜是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是⾸先会对联合索引的最左边第⼀个字段排序，在第⼀个字段的排序基础上，然后在对第⼆个字段进⾏排序。所以b=2这种查询条件没有办法利⽤索引。
由于整个过程是基于explain结果分析的，那接下来在了解下explain中的type字段和key_lef字段。
system：表只有⼀⾏记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
    const：表⽰通过索引⼀次就到了，const⽤于⽐较primary key 或者 unique索引。因为只需匹配⼀⾏数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为⼀个const
    eq_ref：唯⼀性索引扫描，对于每个索引键，表中只有⼀条记录与之匹配。常见于主键 或 唯⼀索引扫描。
    注意：ALL全表扫描的表记录最少的表如t1表
ref：⾮唯⼀性索引扫描，返回匹配某个单独值的所有⾏。本质是也是⼀种索引访问，它返回所有匹配某个单独值的⾏，然⽽他可能会到多个符合条件的⾏，所以它应该属于查和扫描的混合体。
range：只检索给定范围的⾏，使⽤⼀个索引来选择⾏。key列显⽰使⽤了那个索引。⼀般就是在where语句中出现了bettween、<、>、in 等的查询。这种索引列上的范围扫描⽐全索引扫描要好。只需要开始于某个点，结束于另⼀个点，不⽤扫描全部索引。qt和pyqt区别
index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引⽂件通常⽐数据⽂件⼩。（Index与ALL虽然都是读全表，但index是从索引中读取，⽽ALL是从硬盘读取）
    ALL：Full Table Scan，遍历全表以到匹配的⾏
2.key_len：显⽰MySQL实际决定使⽤的索引的长度。如果索引是NULL，则长度为NULL。如果不是NULL，则为使⽤的索引的长度。所以通过此字段就可推断出使⽤了那个索引。
计算规则：
1.定长字段，int占⽤4个字节，date占⽤3个字节，char(n)占⽤n个字符。
2.变长字段varchar(n)，则占⽤n个字符+两个字节。
3.不同的字符集，⼀个字符占⽤的字节数是不同的。Latin1编码的，⼀个字符占⽤⼀个字节，gdk编码的，⼀个字符占⽤两个字节，utf-8编码的，⼀个字符占⽤三个字节。
（由于我数据库使⽤的是Latin1编码的格式，所以在后⾯的计算中，⼀个字符按⼀个字节算）
4.对于所有的索引字段，如果设置为NULL，则还需要1个字节。
mysql面试题索引接下来进⼊正题
⽰例：
⾸先创建⼀个表
该表中对id列.name列.age列建⽴了⼀个联合索引 id_name_age_index，实际上相当于建⽴了三个索引（id）（id_name）
（id_name_age）。
下⾯介绍下可能会使⽤到该索引的⼏种情况：
1.全值匹配查询时
通过观察上⾯的结果图可知，where后⾯的查询条件，不论是使⽤（id，age，name）（name，id，age）还是（age，name，id）顺序，在查询时都使⽤到了联合索引，可能有同学会疑惑，为什么底下两个的搜索条件明明没有按照联合索引从左到右进⾏匹配，却也使⽤到了联合索引？ 这是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执⾏效率⾼，最后才能⽣成真正的执⾏计划，所以不论以何种顺序都可使⽤到联合索引。另外通过观察上⾯三个图中的key_len字段，也可说明在搜索时使⽤的
联合索引中的（id_name_age）索引，因为id为int型，允许null，所以占5个字节，name为char(10)，允许null，⼜使⽤的是latin1编码，所以占11个字节，age为int型允许null，所以也占⽤5个字节，所以该索引长度为21（5+11+5），⽽上⾯key_len的值也正好为21，可证明使⽤的（id_name_age）索引。
2.匹配最左边的列时
该搜索是遵循最左匹配原则的，通过key字段也可知，在搜索过程中使⽤到了联合索引，且使⽤的是联合索引中的（id）索引，因为key_len 字段值为5，⽽id索引的长度正好为5（因为id为int型，允许null，所以占5个字节）。
js有没有类似sql必知必会的书由于id到name是从左边依次往右边匹配，这两个字段中的值都是有序的，所以也遵循最左匹配原则，通过key字段可知，在搜索过程中也使⽤到了联合索引，但使⽤的是联合索引中的（id_name）索引，因为key_len字段值为16，⽽(id_name)索引的长度正好为16（因为id为int型，允许null，所以占5个字节，name为char(10)，允许null，⼜使⽤的是latin1编码，所以占11个字节）。
由于上⾯三个搜索都是从最左边id依次向右开始匹配的，所以都⽤到了id_name_age_index联合索引。
那如果不是依次匹配呢？
通过key字段可知，在搜索过程中也使⽤到了联合索引，但使⽤的是联合索引中的（id）索引，从key_len字段也可知。因为联合索引树是按照id字段创建的，但age相对于id来说是⽆序的，只有id只有序的，所以他只能使⽤联合索引中的id索引。
通过观察发现上⾯key字段发现在搜索中也使⽤了id_name_age_index索引，可能许多同学就会疑惑它并没有遵守最左匹配原则，按道理会索引失效，为什么也使⽤到了联合索引？因为没有从id开始匹配，且name单独来说是⽆序的，所以它确实不遵循最左匹配原则，然⽽从type字段可知，它虽然使⽤
了联合索引，但是它是对整个索引树进⾏了扫描，正好匹配到该索引，与最左匹配原则⽆关，⼀般只要是某联合索引的⼀部分，但⼜不遵循最左匹配原则时，都可能会采⽤index类型的⽅式扫描，但它的效率远不如最做匹配原则的查询效率⾼，index类型类型的扫描⽅式是从索引第⼀个字段⼀个⼀个的查，直到到符合的某个索引，与all不同的是，index是对所有索引树进⾏扫描，⽽all 是对整个磁盘的数据进⾏全表扫描。
这两个结果跟上⾯的是同样的道理，由于它们都没有从最左边开始匹配，所以没有⽤到联合索引，使⽤的都是index全索引扫描。
3.匹配列前缀
如果id是字符型，那么前缀匹配⽤的是索引，中坠和后缀⽤的是全表扫描。
select * from staffs where id like ‘A%’;//前缀都是排好序的，使⽤的都是联合索引 select * from staffs where id like
‘%A%’;//全表查询 select * from staffs where id like ‘%A’;//全表查询
4.匹配范围值
织梦cms模版
在匹配的过程中遇到<>=号，就会停⽌匹配，但id本⾝就是有序的，所以通过possible_keys字段和key_len 字段可知，在该搜索过程中使⽤了联合索引的id索引（因为id为int型，允许null，所以占5个字节），且进⾏的是rang范围查询。
c语言程序设计实训教程答案张玉生由于不遵循最左匹配原则，且在id<4的范围中，age是⽆序的，所以使⽤的是index全索引扫描。
  不遵循最左匹配原则，但在数据库中id<2的只有⼀条（id），所以在id<2的范围中，age是有序的，所以使⽤的是rang范围查询。
  不遵循最左匹配原则，⽽age⼜是⽆序的，所以进⾏的全索引扫描。
5.准确匹配第⼀列并范围匹配其他某⼀列
  由于搜索中有id=1，所以在id范围内age是⽆序的，所以只使⽤了联合索引中的id索引。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。