贝壳面试：MySQL联合索引，最左匹配原则是什么？

尼恩说在前面

在40岁老架构师尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团的面试资格，遇到很多很重要的面试题：

1.谈谈你对MySQL联合索引的认识？

2.在MySQL中，联合索引是如何实现的？请简述其工作原理。

3.什么是最左前缀匹配原则？为什么要遵守？

4.MySQL一定要遵循最左前缀匹配吗？

最近有小伙伴在面试贝壳、soul，又遇到了相关的面试题。小伙伴懵了，因为没有遇到过，所以支支吾吾的说了几句，面试官不满意，面试挂了。

所以，尼恩给大家做一下系统化、体系化的梳理，使得大家内力猛增，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

当然，这道面试题，以及参考答案，也会收入咱们的《尼恩Java面试宝典PDF》V171版本，供后面的小伙伴参考，提升大家的 3高架构、设计、开发水平。

最新《尼恩架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请关注本公众号【技术自由圈】获取，回复：领电子书

联合索引和 mysql 调优的关系

mysql 调优的一个核心动作，就是通过联合索引实现索引覆盖。

在MySQL中，合理使用联合索引可以提高查询效率，通过联合索引实现索引覆盖，常常需要注意一些技巧：

选择合适的列： 联合索引的列顺序非常重要。应该优先选择最频繁用于查询条件的列，以提高索引的效率。其次考虑选择性高的列，这样可以过滤出更少的数据。
避免冗余列： 联合索引的列应该尽量避免包含冗余列，即多个索引的前缀相同。这样会增加索引的维护成本，并占用更多的存储空间。
避免过度索引： 不要为每个查询都创建一个新的联合索引。应该根据实际情况，分析哪些查询是最频繁的，然后创建针对这些查询的索引。
覆盖索引： 如果查询的列都包含在联合索引中，并且不需要访问表的其他列，那么MySQL可以直接使用索引来执行查询，而不必访问表，这种索引称为覆盖索引，可以提高查询性能。
使用EXPLAIN进行查询计划分析： 使用MySQL的EXPLAIN语句可以查看MySQL执行查询的执行计划，以便优化查询语句和索引的使用。
定期优化索引： 随着数据库的使用，索引的效率可能会下降，因此需要定期进行索引的优化和重建，以保持查询性能的稳定性。
分析查询日志： 监控数据库的查询日志，分析哪些查询是最频繁的，以及它们的查询模式，可以帮助确定需要创建的联合索引。
避免过度索引更新： 避免频繁地更新索引列，因为每次更新索引都会增加数据库的负载和IO操作。

综上所述，联合索引是mysql 调优的一个核心动作，通过联合索引进行mysql 调优时，需要综合考虑列的选择、索引的覆盖、查询的频率和模式等因素，以提高MySQL数据库的查询性能。

正因为如此，联合索引是面试的重点和难点。

回答这个面试题，我们可以从最为基础的MySQL索引机制开始讲起。

基础知识：MySQL索引机制

数据库索引，官方定义如下

在关系型数据库中，索引是一种单独的、物理的数据，对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合，以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。

通俗的理解为

在关系型数据库中，索引是一种用来帮助快速检索目标数据的存储结构。

索引的创建

MySQL可以通过CREATE、ALTER、DDL三种方式创建一个索引。

使用CREATE语句创建

 CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);

使用ALTER语句创建

ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

建表时DDL语句中创建

CREATE TABLE tableName(  
     columnName1 INT(8) NOT NULL,   
     columnName2 ....,
     .....,
     INDEX [indexName] (columnName(length))  
   );

索引的查询

  SHOW INDEX from tableName;

索引的删除

  ALTER  TABLE  table_name   DROP  INDEX  index_name;
  DROP   INDEX  index_name   ON  table_name;

MySQL联合索引

什么是联合索引

联合索引（Composite Index）是一种索引类型，它由多个列组成。

MySQL的联合索引（也称为复合索引）是建立在多个字段上的索引。这种索引类型允许数据库在查询时同时考虑多个列的值，从而提高查询效率和性能。

联合索引：也称复合索引，就是建立在多个字段上的索引。联合索引的数据结构依然是 B+ Tree。
当使用(col1, col2, col3)创建一个联合索引时，创建的只是一颗B+ Tree，在这棵树中，会先按照最左的字段col1排序，在col1相同时再按照col2排序，col2相同时再按照col3排序。

联合索引存储结构

联合索引是一种特殊类型的索引，它包含两个或更多列。

在MySQL中，联合索引的数据结构通常是B+Tree，这与单列索引使用的数据结构相同。

当创建联合索引时，需要注意列的顺序，因为这将影响到索引的使用方式。

如下图所示，表的数据如右图，ID 为主键，创建的联合索引为 (a，b)，注意联合索引顺序，下图是模拟的联合索引的 B+ Tree 存储结构

在这里插入图片描述

最左前缀匹配原则

联合索引还是一颗B+树，只不过联合索引的健数量不是一个，而是多个。

构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

假如创建一个（a,b)的联合索引，联合索引B+ Tree结构如下：

在这里插入图片描述

结合上述联合索引B+ Tree结构，可以得出如下结论：

1.a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。

所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

2.当a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。

所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。

例如a = 1 and b = 2 ，a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

最左匹配原则：

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

下面我们以建立联合索引（a,b,c）为例，进行详细说明

1 全值匹配查询时

下述SQL会用到索引，因为where子句中，几个搜索条件顺序调换不影响查询结果，因为MySQL中有查询优化器，会自动优化查询顺序。

select * from table_name where a = '1' and b = '2' and c = '3' 
select * from table_name where b = '2' and a = '1' and c = '3' 
select * from table_name where c = '3' and b = '2' and a = '1'

2 匹配左边的列时

下述SQL，都从最左边开始连续匹配，用到了索引。

select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'

下述SQL中，没有从最左边开始，最后查询没有用到索引，用的是全表扫描。

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3'

下述SQL中，如果不连续时，只用到了a列的索引，b列和c列都没有用到

select * from table_name where  a = '1' and c = '3'

3 匹配列前缀

如果列是字符型的话它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相通，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。

如果a是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了

select * from table_name where  a like 'As%';   //前缀都是排好序的，走索引查询
select * from table_name where  a like '%As';   //全表查询
select * from table_name where  a like '%As%';  //全表查询

4 匹配范围值

下述SQL，可以对最左边的列进行范围查询

select * from table_name where  a > 1 and a < 3

多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+树索引，也就是只有a用到索引。

在1<a<3的范围内b是无序的，不能用索引，找到1<a<3的记录后，只能根据条件 b > 1继续逐条过滤。

select * from table_name where  a > 1 and a < 3 and b > 1;

5 精确匹配某一列并范围匹配另外一列

如果左边的列是精确查找的，右边的列可以进行范围查找，如下SQL中，a=1的情况下b是有序的，进行范围查找走的是联合索引

select * from table_name where  a = 1 and b > 3;

6 排序

一般情况下，我们只能把记录加载到内存中，再用一些排序算法，比如快速排序，归并排序等在内存中对这些记录进行排序，有时候查询的结果集太大不能在内存中进行排序的话，还可能暂时借助磁盘空间存放中间结果，排序操作完成后再把排好序的结果返回客户端。

Mysql中把这种再内存中或磁盘上进行排序的方式统称为文件排序。文件排序非常慢，但如果order子句用到了索引列，就有可能省去文件排序的步骤

select * from table_name order by b,c,a limit 10;

因为b+树索引本身就是按照上述规则排序的，所以可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了，order by的子句后面的顺序也必须按照索引列的顺序给出，比如下SQL：

select * from table_name order by b,c,a limit 10;

在以下SQL中颠倒顺序，没有用到索引

select * from table_name order by a limit 10;
select * from table_name order by a,b limit 10;

以下SQL中会用到部分索引，联合索引左边列为常量，后边的列排序可以用到索引

select * from table_name where a =1 order by b,c limit 10;

为什么要遵循最左前缀匹配？

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

如下，我们以age，name两个字段建立一个联合索引，非叶子节点中记录age，name两个字段的值，而叶子节点中记录的是age，name两个字段值及主键Id的值，在MySQL中B+ Tree 索引结构如下：

在这里插入图片描述

在上述联合索引存储数据过程中，首先会按照age排序，当age相同时则按照name排序。

结合上述索引结构，可以看出联合索引底层也是一颗B+Tree，在联合索引中构造B+Tree的时候，会先以最左边的key进行排序，如果左边的key相同时，则再依次按照右边的key进行排序。
所以在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。

一定要遵循最左前缀匹配吗？

最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询，但实际上这条规则也并不是100%遵循的。

因为在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

我们来看如下例子，理解一下索引跳跃式扫描如何实现的。

比如此时通过(A、B、C)三个列建立了一个联合索引，此时有如下一条SQL：

SELECT * FROM table_name WHERE B = `xxx` AND C = `xxx`;

按正常情况来看，这条SQL既不符合最左前缀原则，也不具备使用索引覆盖的条件，因此绝对是不会走联合索引查询的。

但这条SQL中都已经使用了联合索引中的两个字段，结果还不能使用索引，这似乎有点亏啊？

因此MySQL8.x推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了SQL，比如上述这条SQL则会重构成如下情况：

SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
......
SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";

通过MySQL优化器处理后，虽然你没用第一个字段，但我（优化器）给你加上去，今天这个联合索引你就得用，不用也得给我用。

但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发等等，总之有很多限制条件，具体的可以参考《MySQL官网8.0-跳跃扫描》。

最后，可以通过通过如下命令来选择开启或关闭跳跃式扫描机制。

set @@optimizer_switch = 'skip_scan=off|on';

联合索引注意事项

选择合适的列：应选择那些经常用于查询条件的列来创建联合索引。
考虑列的顺序：在创建联合索引时，应该根据实际的查询需求来安排列的顺序，以确保索引能够被有效利用。
避免过长的索引：虽然联合索引可以包含多个列，但过长的索引可能会增加维护成本，并且在某些情况下可能不会带来预期的性能提升。
避免范围查询：如果查询中包含范围操作符（如BETWEEN, <, >, LIKE），则MySQL可能无法有效地利用联合索引，因为它需要检查索引中的每个范围边界。
考虑索引的区分度：如果某个列的值重复率很高，那么该列作为联合索引的一部分可能不会提供太大的性能提升，因为它不能有效地区分不同的记录。

联合索引作为数据库中的一种索引类型，它由多个列组成，在使用时，一般遵循最左匹配原则，以加速数据库查询操作。