MySQL｜聊完了MySQL索引，面试官直接给我涨了2000！

前言

mysql索引真的是一个让人不得不说的话题，这个东西你在面试中会用到，在实际的工作中也会用到，这更是一个专业的DBA所必须掌握的内容，它的重要性体你在大厂的面试题汇总也可以看到，属于必问的一个内容。

这篇文章的内容可能有点多，也有点晦涩难懂，没有基础的小伙伴需要多读几遍才能读懂，但是希望你能沉下心来读完这篇文章，你会得到更多：

什么是索引?

常见的索引模型有哪些?什么是回表?什么是覆盖索引?什么是最左前缀原则?什么是索引下推?

..............

正文

什么是索引?

相信大家小时候学习汉字的时候都会查字典，想想你查字典的步骤，我们是通过汉字的首字母a～z一个一个在字典目录中查找，最终找到该字的页数。想想，如果没有目录会怎么样，最差的结果是你有可能翻到字典的最后一页才找到你想要找的字。

索引就相当于我们字典中的目录，可以极大的提高我们在数据库的查询效率。

常见的索引模型有哪些?

①有序数组

如图： 

我们按照IDCard从小到大排列：

在我们想要根据IDCard查找某一条数据时，就可以通过二分法查找。

在我们想要根据IDCard查找IDCard在 10 到 1000 内的的数据时，就可以先通过二分法先查找10，然后向递增的方向遍历，找到IDCard为1000，再继续遍历，直到找到的IDCard大于1000时就完成了整个范围查询。

我们仅仅从查询的角度来看有序数组已经很优秀了，但是我们都知道，有序数组在插入一条数据时是非常麻烦的，你需要将你插入位置后面的数据整体向后移一位，这是非常消耗性能的。

• 优点：查询效率很高，也很适合范围查询。
• 缺点：当新数据插入时会影响效率。

②哈希表

如图学过hashmap 的朋友应该都比较了解了，它的原理其实就是将IDCard通过哈希算法计算出一个特定的值，然后存储地址，这样在你找数据的时候直接可以通过IDCard去找了，当然也会有种情况，就是两个元素选中了相同的空间，我们通常会引出一个链表去存储。

我们可以看到hash表在等值查询的效率是很高的，但是由于hash表是无序的，所以在范围查询的时候只能遍历所有了，效率会很低。

感兴趣的朋友可以去看看lru，是如何解决范围查询的问题的，后续我也会和大家讲讲。

• 优点：等值查询效率高，插入效率高
• 缺点：不适合范围查询

③二叉树

如图：

二叉树是比较经典的数据结构了，它的特点是每个节点的左儿子小于父节点，父节点又小于右儿子。

二叉树是有序的，查找的时间复杂度为O(logn)

二叉树可以说在插入查询方面都是比较优秀的，但是在数据的索引选择方面我们并不会选择二叉树，我们按照上图来讲：

假如我要访问IDCard5，那么我要先访问IDCard1，然后访问IDCard3，最后才能访问到IDCard5，我们总会访问了3个数据块，每一次访问都是一次磁盘寻址的过程，假设树高30，那么我们最差的情况下寻找一个数据要访问30次磁盘，这在效率上是不能忍受的。

④B+树

如图 

我们数据库innodb默认的索引引擎就是B+树。

B+树其实是就是一个N叉树，只在子节点上存储数据，并且子节点用链表维护，而且是有序的，在范围查询(链表更高效)，等值查询，插入新数据上来说都是很高效的。并且作为N叉树，在树的每一层都可以存储很多数据，这样在数据库查询数据的时候最差也只需要几次磁盘寻址就可以了。

在mysql中有主键索引和非主键索引之分，主键索引上存储的是数据行信息，非主键索引上存储的是主键信息。

如下

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `application` varchar(64) NOT NULL DEFAULT '' COMMENT '所属应用名称',
  `owner` varchar(32) NOT NULL DEFAULT '' COMMENT '负责人',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_application_id` (`application`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

主键为id，那么id这棵B+树上就会存储该行所有的信息字段，包括application，owner。

对于没有主键的表，innodb会给默认创建一个Rowid做主键。

唯一索引是application，那么在application这颗索引树上存储的信息就是id。

优点：

♦第一层只放索引信息，存放的索引信息更多。

♦树高更低，故磁盘寻址带来的损耗更小。♦链表维护，范围查询效率更高。

⑤B-树

如图B-树和B+树的区别是每一个节点都会存储数据，叶子节点之间不用链表链接。

相比B+树来说做范围查询的效率会低一点，如果空间大小固定的话，第一层存放的索引信息更少(想想目录，我们都希望第一层是只用来存储目录信息的)。

什么是回表?

还按这个表举例：

CREATE TABLE `user` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `a

来一条查询语句

select * from user where application = 'wechat';

我们来看看，针对这张user表，上述的查询语句要经过哪些步骤：

①由于application是索引，所以先搜索application这颗索引树，找到application='wechat'这条数据，取得主键id②通过取得的主键id，去主键id这颗B+树找到该条数据③找到该条数据后，取得该数据行的值，并且返回④结束

刚刚的第一步到第二步，其实就是一个回表操作，我们定义一下回表:

回表就是在普通索引树上取得主键信息，再返回到主键索引树去搜索数据信息，这就是回表操作。

覆盖索引

moon这里再举个例子你就能明白覆盖索引的含义了，还是刚刚的表

select id from user where application = 'wechat';

这条语句就可以用到覆盖索引这个特性了，我们再来看下步骤：

①由于application是索引，所以先搜索application这颗索引树，找到application='wechat'这条数据，取得主键id②mysql发现id 就是select要查询的数据，并且application是唯一索引，于是直接返回

这就是覆盖索引的效果，可以减少我们的回表次数，甚至可以不用回表。

当然，在实际开发场景中也不要轻易只是为了能用到覆盖索引就建立冗余字段索引，还是要根据实际开发场景来的。

最左前缀原则

还是之前的表，索引变成了(application，owner)的联合索引，我们再来写个sql：

select owner from user where application like "w%";

当你的查询条件是application以w开头的数据时，就可以用到最左前缀原则了。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左N个字符。

聪明的朋友已经发现了，最左前缀还有一层优化，比如(application，owner)这个联合索引中，我们用到了最左前缀，可以少维护一个application的单独索引，因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

索引下推

联合索引(application，owner)

select * from user where application like "w%" and owner = "老王";

这个语句在搜索索引树的时候，只能用 “w”，找到第一个满足条件的记录，然后判断其他条件是否满足。

在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。

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

也就是owner在联合索引中，‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍判断owner是否等于'老王'，会直接过滤掉不等于'老王'的数据。

结语 

今天这篇文章内容确实够多的，聊了很多和数据库索引相关的知识，包括了什么是索引，索引模型，覆盖索引，前缀索引，索引下推，回表。

‍‍‍‍‍‍‍‍‍‍‍‍‍你可以看到，在满足语句需求的情况下， 我们要利用数据库的特性尽量的减少资源的损耗。我们在使用数据库的时候，尤其是在设计表结构时，也要以减少资源消耗作为目标。

本文转载自微信公众号「moon聊技术」