面试怎么回答MySQL索引问题，看这里

前言

小A在宿舍里跟哥们开五黑打排位中，突然收到女神小美的消息：“小A，我今天面试碰到索引问题了，我没回答好”。小A顾不上游戏抓紧回复到：“到你宿舍某某咖啡店吧，我帮你一起看下”。

小A抓紧时间换了衣服， 就狂奔而去。电脑上传来了哥们的骂声：”去你大爷的小A，老子晋级赛呢！“。小A心想，不好意思，在女神面前，兄弟如浮云。

小A一路火花带闪电的跑到咖啡店，看到小美也刚到。立马到前台点了两杯咖啡，坐到到了小美对面说到：没事，你用今天面试官问你的问题来问我，我回答一遍给你看看，晚上再给你整理下笔记。

正文

小美认真回想了一下今天被面试官问到的索引问题，开始提问。

索引类型

先简单讲下什么是索引？

索引出现是为了提升数据查询效率，就像字典目录一样。一本一两千页的字典，如果我们只是想查询某个字所在的页数，如果没有目录估计得找一会儿。而目录的存在就是帮助我们快速定位到其具体的页数，而索引就是数据库中的目录，帮助存储引擎快速查询到数据。

MySQL 中索引有哪些类型?

常见有以下三种类型：

• 主键索引
• 唯一索引
• 普通索引

还有其他几种不常用索引类型：

• 全文索引
• 空间索引

InnoDB 索引的实现方式有哪些？

有两种方式，分别为 BTree 和 Hash，BTree 在实际使用中更为普遍。主要是 Hash 实现方式类似 HashMap ，有以下几个缺点很难满足业务需求：

1. 只支持等值查询，无法实现范围查询
2. 不支持使用索引排序
3. 不支持模糊查询和前缀匹配
4. 每次查询都需要回表，无法使用覆盖索引特性
5. 随着数据量增大，Hash 冲突的概率加大，进而影响效率

索引区别

讲一下常用索引类型之间的区别

常见索引：

• 主键索引：在 InnoDB 里，也被称为聚簇索引。每个表都只能建一个主键索引（某一个字段），且主键索引字段值不能为 NULL，不能重复。主键索引的B+树叶子结点存储的是数据内容，所以根据主键索引查询时不用涉及到回表。

• 唯一索引：每个表可以创建多个唯一索引，且可以由多个字段组合而成。唯一索引可以为 NULL，唯一索引整体不能重复。

• 普通索引：整体和唯一索引类型类似，唯一的区别就是普通索引可以重复。

其中多个字段组成的普通/唯一索引也叫联合索引

而剩余两种索引，大多数场景下不推荐使用：

• 全文索引：就是将各种信息，文档中所有的文字序列都作为检索对象导致索引文件较大，查询是根据全文索引找出包含检索词汇的信息或文档。
• 空间索引：对空间数据类型的字段建立的索引，MYSQL 使用 SPATIAL 关键字进行扩展，使其能够在空间数据类型的语法上创建空间索引。

主键索引有哪些点需要注意？

主键索引一般建议采用自增主键的方式，可以避免叶子结点的分裂和合并动作。如果是采用业务字段无法保证有序插入，从而导致频繁的页分裂与页合并，进而影响写入效率。

主键索引占用的空间需要尽可能的小，最好使用 int 或 bigint 类型，因为每个非主键索引的叶子节点都需要存储主键索引值从而进行回表。而主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

以上只针对 BTree 实现方式，从性能和存储上考量自增主键更合适。另外如果频繁只使用某个字段进行查询，那建议直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

索引优化

什么是回表？

在根据非主键索引查询数据过程中，需要搜索两棵树：

• 第一次对应非主键索引树的查询，从对应的叶子结点获取到主键ID
• 第二次为主键索引树的查询，根据前面获取到的主键ID查询到对应数据。

其中回到主键索引树搜索的过程，我们称为回表。

回表有什么方式可以避免？
可以通过覆盖索引来避免回表，比如 SQL 语句如下：

select id from user where name = '小A';

其中id字段为主键，name为非主键索引。那么如上 SQL 语句我们就不需要进行回表，因为id值已经在非主键索引树上存储，可以直接返回查询结果。如果某个非主键索引，已经覆盖了我们的所有查询字段，我们成为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

你知道最左匹配原则嘛？

上述提到过联合索引的定义，比如我们定义了一个联合索引为(a,b,c)三个字段构成的。当我们执行的查询语句如下：

语句1：select * from user where a = 1 and b = 2 and c = 3;
语句2：select * from user where a = 1 and b = 2;
语句3：select * from user where a = 1 and c = 3;
语句4：select * from user where b = 2 and c = 3;

上述四个语句中，只有语句1、2会使用到联合索引，而语句3，4因为不满足最左匹配原则导致无法使用到联合索引，因为索引项是按照索引定义里面出现的字段顺序排序的。

在建立联合索引的时候，如何安排索引内的字段顺序是需要根据业务实际情况来安排。如有上诉的联合索引(a,b,c)，我们就不需要再额外创建索引(a),(a,b)。但是如果你想使用(b,c)的联合索引就需要再额外创建，这时候你应该考虑(a),(a,b)索引顺序是否有业务需求，是否通过修改索引的顺序为(b,c,a)，就可以减少维护一个索引。

如果通过调整联合索引顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的，其次需要考虑的是空间。如果索引(a),(b),(a,b)业务上都需要使用，就根据a和b的字段大小来判断。如果b字段更大，那就创建索引为(b,a)的联合索引和(a)的单字段索引。

那索引下推呢？

索引下推是 MySQL 5.6 引入的优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

还是以联合索引为(a,b,c)为例，执行如下查询语句：

select * from user where a = 1 and c = 3;

通过上面的最左匹配原则我们知道，这里只会使用到联合索引(a,b,c)的a字段，在5.6版本之前就需要将满足a的所有数据根据主键开始一个个回表。到主键索引上找出数据行，再对比字段值。

而有了索引下推，就可以直接在使用联合索引(a,b,c)先通过索引查找到所有满足 a = 1 的数据再依次遍历过滤掉 c != 3 的数据，通过减少回表次数从而优化查询效率。

前缀匹配和前缀索引又是什么呢？

前缀匹配其实是包含在最左匹配原则中的，针对索引字段是字符串。例如普通索引(name)，name字段类型为 varchar(32) ，执行如下查询语句：

语句一：select * from user where name = "龙傲天";
语句二：select * from user where name like "龙%";
语句三：select * from user where name like "%傲天";

其中语句一是完全匹配，我们已经知道了。而语句二就是满足最左匹配原则中的前缀匹配，一样可以使用索引查询姓为龙的人。语句三就不满足原则，导致索引失效。

前缀索引：如果列是长字符串，则索引可能占用大量磁盘空间并可能减慢 INSERT 操作速度。根据查询区分度，对字符串的前几个字符创建索引。

获取区分度：select count(distinct left(name, 4))/count(*) from user

就想姓名，中国绝大部分名字都在四个字以内。我们通过查询区分度发现满足当前系统99%的人，就可以创建前缀索引为name(4)，而不需要将名字整个字段设为索引，查询效率和索引存储空间都有一定的提升。

索引失效

你知道有那些情况会导致索引失效？

• 字段判断时使用函数，破坏索引值的有序性或产生隐私转换，导致索引失效
• 不满足最左匹配原则，联合索引顺序问题导致索引失效
• IS NULL 可以使用索引，IS NOT NULL 无法使用索引
• 不等于 != 或者 <> 会导致索引失效
• OR 前后只要存在非索引的列，都会导致索引失效
• 不同的字符集进行比较前需要进行转换，会造成索引失效

日常工作中的SQL优化小技巧

• 尽量不使用 * 查询，尽可能触发索引覆盖，减少回表
• 不要无限创建索引，索引并不少越多越好，会降低SQL写入性能
• 不要使用全文模糊搜索，使用前缀匹配
• 查询尾缀可通过倒序存储，再使用前缀匹配来满足搜索（使用8.0版本的倒序索引就无须创建重复字段）
• 通过区分度增加前缀索引，降低索引长度
• 数据库使用的索引不一定是最优解，使用force index强制使用索引（慎用）
• 尽量避免超过三个表的Join语句，可以通过冗余部分字段解决

结束语

小美对小A不禁又高看了一眼，而小A继续说到：“天色有点晚了，关于索引的BTree数据结构我晚上回去整理发给你”。小美为了答谢想请小A一起吃顿晚饭，而小A义正言辞拒绝到：“不了，我还是抓紧回去整理文章发给你。我怕你明天被问到”，说完就飞奔回宿舍。

各位小A读者，索引你学会了嘛？本文对索引的大部分面试常见问题都讲述到了，篇幅较长，遗留的BTree数据结构后面另起文章补上。