高薪程序员&面试题精讲系列89之MySQL有哪些索引？

一. 面试题及剖析 1. 今日面试题

MySQL有哪些索引？

索引的底层原理你熟悉吗？

组合索引了解吗？

聚簇(集)索引和非聚簇(集)索引有什么区别？

什么情况下索引会失效？

2. 题目剖析

壹哥今天会给大家讲解关于数据库优化时非常重要的一个内容，这就是数据库的索引。可以说，这是我们出去面试时必问必考的一块内容，无论哪个公司，哪个行业，这一块的内容都是考察的重中之重。如果你对数据库索引不熟悉，那基本就会被面试官贴上一个对数据库不熟悉的标签，如果他们认为你对数据库不熟悉，那这次面试的结果就不会很理想。所以在我们的线下课程里面，壹哥从来都是要求自己的学生，对索引这一块必须熟练掌握，当然我讲课时更会特别深入的讲解这一块的内容。

如果今天的内容你不弄明白，那就相当于高考前明确给了你必考题目和答案，你还不背，这就是作死了。所以今天的内容，大家务必要认真掌握！！！

二. MySQL索引 1. 背景

我们可以开发各种类型的项目，比如电商、医疗、物流、金融、政务等，其实不管是什么类型的项目，也不管这个项目的规模有多大，绝大多数情况下，这个项目中都会用到数据库。这些项目中都需要进行数据的 *** 作和渲染，而在这些数据 *** 作的过程中，大约90%的 *** 作都是对数据的读 *** 作，剩余的10%才是写 *** 作。我们可以想象一下，真正影响项目性能的，是读 *** 作还是写 *** 作呢？相信各位可以很容易得出结论，那就是读比写更影响项目的性能！

所以，我们要想提升项目的性能，自然就有了明确的方向，那就是想办法对读 *** 作进行优化即可！通俗地说，就是提高数据库查询的性能就好了！

那该怎么提高数据库查询性能呢？这就需要用到一种特殊的数据结构--索引了！

2. 概念

索引是一种被存储引擎用来快速找到内容记录的数据结构，在MySQL中有时也会把索引称为”键“。通俗地说，我们可以把数据库理解成是一本汉语字典，里面存储了非常多的字。那我们该怎么快速地找到某个我们想要的汉字呢？聪明如你，肯定会想到这个字典的前面有”目录“，我们可以按照拼音、笔画等目录中的”键“来找到对应页面上的具体汉字！所以这里数据库相当于是”字典“，索引就相当于是字典里的”目录“，所以有了索引，我们就可以快速的找到数据库里的某个具体数据了。

3. 作用

相信各位听了壹哥对索引的基本介绍之后，就可以知道索引的作用了。没错，索引可以大大减小服务器需要扫描的数据量，进而帮助我们快速查找数据库里存储的数据，对于提升查询效率有着至关重要的作用，尤其是表中的数据量越大，索引的作用就越明显。可以说，对索引的优化就是对数据库查询性能的优化，创建一个良好有效的索引，可以将数据库的查询性能提高好几个数量级。

另外索引还可以帮助服务器避免排序和创建临时表，将随机IO变成顺序IO等，总之索引具有很多优点。

4. 缺点

虽然索引可以提高查询效率，但索引也有自己的不足之处，就是索引自身也需要一些额外开销成本，这些成本如下：

空间成本：创建索引会产生索引文件，这些索引文件需要占用一定的磁盘空间，当索引很多时甚至占用的空间还很大；

时间成本：查询索引本身也是需要一些时间的，所以索引越大，查询索引本身花费的时间就越多；

维护成本：索引文件是一个二叉树类型的文件，而我们进行DML *** 作(insert/update/delete)后同样也会对索引文件进行修改，尤其是当数据大量更新后更需要对索引进行维护，所以DML *** 作后索引的性能会下降。

所以索引并不都是优点，缺点也很明显，对一些数据流非常小的表，大部分情况下全表扫描比索引更高效。所以就没有十全十美的技术，索引也不例外！

5. 原理(重点)

那么为什么索引可以提高数据库的查询效率呢？它是基于什么样的原理能够拥有这样的能力？接下来壹哥就给大家简单说一下索引的工作原理，这也是面试的一个必问点。

5.1 原理概述

其实索引之所以能够提高查询效率，其运行机制与我们查字典是相同的道理。我们把数据库比喻成一本汉语字典，里面存储了非常多的字，为了能够迅速地找到某个汉字，字典中就设计了目录这样的章节。我们可以按照目录里的拼音、笔画等来查找对应页面上的具体汉字，目录里的内容等于是key，具体页面上的文字等于是value！所以数据库相当于是”字典“，索引就相当于是字典里的”目录“。总之，就是通过不断地缩小要获取的数据范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件。也就是说，有了索引，我们就可以总是用同一种查找方式来锁定数据。

大家想一下，如果这个字典很大，里面有很多文字，那也意味着我们要对应创建的目录也会很大，在这种情况下，即使我们去翻找这个目录也会花费很长的时间，先得一个个从目录中查找，再根据目录定位具体内容。所以要想进一步提高查找效率，这个目录的长度也不是越大越好。如果目录很大，那该目录就会占据很大的磁盘空间，而且查找目录时就要花费很长的时间，有点不划算了。数据库里的索引同样存在相同的问题！

所以为了更优化，MySQL等数据库针对索引结构，设计出了FULLTEXT，HASH，BTREE，RTREE等数据结构来管理索引的内容。比如InnoDB中就是使用了B+Tree数据结构来存储索引，B+Tree会根据索引的物理结构将索引划分为聚簇索引和非聚簇索引，这里壹哥先不细说，大家先知道就行，后面我们再说。

以上就是索引原理的大致概述，当然如果想要解释的更明白，我们必须要把B+Tree等数据结构如何查找、更新数据的过程展开细说。这一块的内容，其实壹哥在之前讲解数据结构时就详细讲过，大家可以往前翻翻。

高薪程序员&面试题精讲系列54之你熟悉B树吗？有哪几种B树？

5.2 原理详述

5.2.1 为什么创建完索引后查询速度会变快？

其实索引的原理，就是搞清楚为什么索引会加快查询速度，对于面试而言，把这一点说清楚就可以了。 接下来壹哥就把这一块重点讲解一下。

在传统的查询方法中，是按照表的顺序遍历的，不论查询几条数据，MySQL都会将表的数据从头到尾遍历一遍。

而在创建完索引之后，MySQL一般会通过B-TREE的相关算法生成一个索引文件。接着在查询数据库时，会先按照索引文件进行遍历(折半查找可以大幅提高查询效率)，找到相应的键，进而获取到数据。如下图所示：

所以这里我们要弄明白索引的工作原理，必须搞清楚B-Tree与B+Tree的结构，接下来就需要去了解B树的内容了，很多面试官也会问二叉树原理时顺道就考察我们二叉树的内容。

5.2.2 索引的数据结构

实际的数据库系统中，几乎没有使用二叉查找树或其进化品种红黑树(red-black tree)来进行查询实现，目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

B-Tree：B树(Balance)是一种自平衡的树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除数据的动作，都可以在对数时间内完成。概括来说，B树是一个一般化的二叉查找树(binary search tree)，可以拥有多于2个子节点。B树为系统大块数据的读写 *** 作做了优化，减少了定位记录时所经历的中间过程，从而加快了存取速度。下面是一个B-Tree的数据结构:

B-Tree是满足下列条件的数据结构：

• B-Tree的每个节点都是【指针+“键值对”】组成，key和指针互相间隔，节点两端是指针；
• 每个非叶子节点由n-1个key和n个指针组成，每个叶子节点最少包含一个key和两个指针；
• 每个指针要么为null，要么指向另外一个节点，叶子节点的指针均为null；
• 一个节点中的key从左到右递减排列；
• 指针指向的节点的所有key大于指针左边的key，小于指针右边的key；
• 所有的叶子节点具有相同的深度，等于树高h(树高，就是树的层数)。

在B-Tree中按key检索数据的算法：

首先从根节点开始进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，返回null则表示查找失败。

一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)。从这点可以看出，B-Tree是一个非常有效率的索引数据结构。

【度，用来约束一个节点key和指针的个数，每个非叶子结点由n-1个key和n个指针组成，其中d<=n<=2d，由于key的个数至少为1，所以d>=2】。

B+Tree：B+Tree是B树中最常见的的一种变种(B-Tree有许多变种)，MySQL普遍使用B+Tree来实现其索引结构。

与B-Tree相比，B+Tree有以下不同点:

• B+树的每个非叶子节点都是由【key+指针】组成，B树是【键值对+指针】;
• B+树的每个叶子节点只包含键值对，B树的叶子节点【键值对+指针】;
• B+树的每个非叶子节点的指针的数量等于key的数量，B树(指针数量=键值对数量+1);
• B+树的叶子节点包含所有的key。

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如上图中，如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

5.2.3 MySQL索引中B+Tree解析

了解完B+Tree的内容之后，我们就可以对MySQL索引中的B+Tree进行分析了，如下图所示：

这里壹哥以InnoDB存储引擎为例进行讲解。

InnoDB中是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 创建一个索引 B+Tree，如上图所示。而 B+Tree 的叶子节点存储的是主键 ID 对应的数据存储位置，比如在执行 select * from user_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name='Bob'。

在建表时 InnoDB 就会自动建立好主键 ID 的索引树，这也是为什么 MySQL 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时，InnoDB 会怎么建立索引树呢？比如我们要给 user_name 这个字段加索引，那么 InnoDB 就会建立 user_name 的索引 B+Tree，节点里存的是 user_name 这个 KEY，叶子节点存储的数据是主键 KEY。得到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据。

问题来了，为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据呢，而要多此一举先找到主键，再在主键索引树找到对应的数据呢?

其实很简单，因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大(数据极度冗余了)。从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间，这是非常有值得的。

在进行 InnoDB 和 MyISAM 特点对比时谈到，MyISAM 查询性能更好，从上面索引文件数据文件的设计来看也可以看出原因：MyISAM 直接找到物理地址后就可以直接定位到数据记录，但是 InnoDB 查询到叶子节点后，还需要再查询一次主键索引树，才可以定位到具体数据。等于 MyISAM 一步就查到了数据，但是 InnoDB 要两步，那当然 MyISAM 查询性能更高。

本文首先探讨了哪种数据结构更适合作为 MySQL 底层索引的实现，然后再介绍了 MySQL 两种经典数据引擎 MyISAM 和 InnoDB 的底层实现。最后再总结一下什么时候需要给你的表里的字段加索引吧：

1. 较频繁的作为查询条件的字段应该创建索引；
2. 唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件；
3. 更新非常频繁的字段不适合创建索引。

6. 分类(重点)

在MySQL中，索引的分类有很多种，而且还可以从逻辑和物理两个方面进行划分，并且这两个不同角度划分的索引名称也不相同。

根据逻辑中的功能分类，索引可以分为如下几种：

• 主键索引：一张表只能有一个主键索引，不允许重复、不允许有NULL；
• 唯一索引：索引列的值必须唯一，即数据列不允许重复，但允许有NULL值。一张表可以有多个唯一索引。如果是组合索引，则列值的组合必须唯一。
• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 插入NULL值；
• 全文索引：可以查找文本中的关键词，主要用于全文检索。

根据逻辑中的列数分类，索引可以分为如下两种：

• 单例索引：一个索引只包含一列，一个表可以有多个单例索引；
• 组合索引：一个组合索引可以包含两个或两个以上的列。查询时要遵循组合索引的 “最左前缀”原则，即在where语句的条件中，要按照创建索引时字段的排列顺序放置条件字段该索引才会生效。

而如果按照物理分类，索引可以分为如下两种：

• 聚簇索引(clustered index)：这不是一种单独的索引类型，而是一种数据存储方式，InnoDB的主键索引就是聚簇索引。该存储方式是依靠B+Tree 来实现的，先根据表的主键构造一棵B+树，且该B+树的叶子节点中存放的都是表的行记录数据时，则该主键索引为聚簇索引。聚簇索引可以理解为是将存储数据与索引放到了一块，找到索引也就找到了数据。因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快，另外对主键的排序查找和范围查找速度也非常快。
• 非聚簇索引：数据和索引是分开的，B+树的叶子节点存放的不是数据表的行记录。InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引，每张表最多只能拥有一个聚簇索引。

这里的聚簇指的是，为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块上。

现在壹哥把这些分类给大家简单地介绍了一下，下一篇文章中，我会讲解如何创建这些索引，以及什么情况会导致索引失效，希望各位继续关注哦。