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

尼恩说在前面

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

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

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

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

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

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

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

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

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

本文目录

- 尼恩说在前面

- 联合索引和 mysql 调优的关系

- 基础知识：MySQL索引机制

 - 索引的创建

 - 索引的查询

 - 索引的查询

- MySQL联合索引

 - 什么是联合索引

 - 联合索引存储结构

- 最左前缀匹配原则

 - 1 全值匹配查询时

 - 2 匹配左边的列时

 - 3 匹配列前缀

 - 4 匹配范围值

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

 - 6 排序

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

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

- 联合索引注意事项

- 说在最后：有问题找老架构取经

联合索引 和 mysql 调优的关系

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

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

1. 选择合适的列： 联合索引的列顺序非常重要。应该优先选择最频繁用于查询条件的列，以提高索引的效率。其次考虑选择性高的列，这样可以过滤出更少的数据。

2. 避免冗余列： 联合索引的列应该尽量避免包含冗余列，即多个索引的前缀相同。这样会增加索引的维护成本，并占用更多的存储空间。

3. 避免过度索引： 不要为每个查询都创建一个新的联合索引。应该根据实际情况，分析哪些查询是最频繁的，然后创建针对这些查询的索引。

4. 覆盖索引： 如果查询的列都包含在联合索引中，并且不需要访问表的其他列，那么MySQL可以直接使用索引来执行查询，而不必访问表，这种索引称为覆盖索引，可以提高查询性能。

5. 使用EXPLAIN进行查询计划分析： 使用MySQL的EXPLAIN语句可以查看MySQL执行查询的执行计划，以便优化查询语句和索引的使用。

6. 定期优化索引： 随着数据库的使用，索引的效率可能会下降，因此需要定期进行索引的优化和重建，以保持查询性能的稳定性。

7. 分析查询日志： 监控数据库的查询日志，分析哪些查询是最频繁的，以及它们的查询模式，可以帮助确定需要创建的联合索引。

8. 避免过度索引更新： 避免频繁地更新索引列，因为每次更新索引都会增加数据库的负载和IO操作。

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

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

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

基础知识：MySQL索引机制

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

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

索引的创建

1. 使用CREATE语句创建

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

2. 使用ALTER语句创建

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

3. 建表时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联合索引

什么是联合索引

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

联合索引存储结构

最左前缀匹配原则

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 匹配列前缀

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 精确匹配某一列并范围匹配另外一列

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

6 排序

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

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;

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

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

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

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

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";

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

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

联合索引注意事项

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

说在最后：有问题找老架构取经

• 大龄男的最佳出路是 架构+ 管理
• 大龄女的最佳出路是 DPM，

部分历史案例

• 被裁太爽：每月大涨1W，10年CRUD小伙，靠转架构一个月上岸，年薪50W爽歪了！

• 小白转架构，逆涨30%：CRUD小伙7年，狠卷3个月升级为架构师，爽爆了

• 绝地逢生：9年经验自考小伙伴，跟着尼恩狠卷3月硬核技术，面试机会爆表，2周后收3个offer ，满血复活

• 硬转架构：35岁被裁6个月 职业绝望，转架构急救上岸，DDD和3高项目太重要了
  

• 逆天改命：3年经验2本，卷3个月涨薪60%，进准大厂(得物、乐信、58集团等)，年薪36W

• 女35岁CRUD程序媛，12月被裁后，拿30个机会2个offer上岸，年底大裁员太不容易了

• 38还有救吗？有。42岁被裁2年天快塌了，急救1个月上岸，成开发经理offer，起死回生

• 被裁不慌，9年小伙1个月喜提年薪60W offer，做中间件架构，爽歪了

• 12年小伙转架构，1小时蜕变收年薪55万offer，秘诀：GO+Java双栖架构

• 年薪百万：40岁小伙被裁6个月，跟着尼恩猛卷3月硬核技术，100W年薪逆袭 ，上岸秘诀：首席架构/总架构

• 不怕裁员，8年小伙去中年危机，秘诀：换架构师赛道，上得厅堂，下得厨房，越早越好

• 被裁员后因祸得福，拿N+1后，逆涨30%，如何实现？

• 逆天啦：4年crud小伙收shein+银行两优质offer，狠卷1月收年薪43万大涨30%

• 原来，拿大厂offer 有捷径......

• 太劲爆.... 被裁4个月，38岁Android转Java，2个月提架构offer

• 起死回生：8年小伙高中毕业+频繁跳槽，狠卷2月，提Java高级offer

• 爽翻了：指导3轮，5年小伙收5大offer，涨50%，领路模式太牛

• 降维攻击，37年大龄老伙喜提60W年薪offer，1个月顺利上岸

• 架构速成：从一面杀到一次过，记7年女架构的速成经历

• 极速拿offer：阿里P6被裁后极速上岸，1个月内喜提2优质offer(含滴滴)

• 大逆袭：做化工12年，35岁转Java，被裁4个月后，1个月收架构Offer，大龄跨行，超级牛

• 被裁后炸爽：10年小伙12天火速上岸，反涨20%，爽暴了

• 裁就裁，6年小伙60W极速上岸，白拿20W还游一圈拉萨，真香

• 极速拿offer：被毕业3个月，11年经验小伙0.5个月极速拿offer，领路模式的巨大威力

• 惊天大逆袭：8年小伙20天时间提75W年薪offer，逆涨50%，秘诀在这