MySQL 索引底层结构、聚集索引、非聚集索引、索引文件

一、索引底层结构

1. 索引

假设表中数据如上图，如果不使用索引，想查找 id = 7 的数据，需要在全部数据中依次比对 7 次才能找到，因为数据在磁盘中是分散存储的，每次比对都需要不停的 IO（移动硬盘机械臂、寻道、寻找扇面等），非常耗时，所以减少比对次数，就可以增加查询性能。

而索引就是一种在新增数据时就排好序的数据结构，可以更快的找到目标数据，目的就是减少磁盘 IO 次数，从而提升查询性能。

2. MySQL 中索引的底层数据结构

在 MySQL 中，主要使用的存储引擎是 InnoDB，而 InnoDB 使用的索引的底层结构是 B+ 树（听说也可以选择 Hash 结构，但是 Hash 结构不支持范围查询）。

据说在选择索引的底层结构时，也有考虑过 二叉树、红黑树、B 树等，现在分析一下这些数据结构被放弃的原因。

二叉树

二叉树在每次插入时会从根元素开始依次向下比较，小于比较对象时新元素放在左边，大于比较对象时新元素放在右边。上图的插入顺序为：5→3→6→2→4→7→1

此时仍然想查找 id = 7 的数据，那么会走 5→6→7 这个分支，只需要比对 3 次就可以，明显比没有索引时减少了比对次数。但是二叉树会有一种极端的情况，假设插入顺序为 1→2→3→4→5→6→7，则就会变成如下结构：

此时想查找 id = 7 的数据，路径是 1→2→3→4→5→6→7，仍然需要比对7次，和没有索引的效果一样，所以二叉树被放弃了。

红黑树

红黑树是一个具有自平衡功能的二叉树，它能控制最长路径不能大于最短路径的两倍。上图的插入顺序是 1→2→3→4→5→6→7，可以看到不会出现普通二叉树的那种极端倾斜的情况，但是红黑树这种自平衡二叉树有一个缺点就是当数据量特别大的时候，树的高度会特别高，这样查找叶子节点时同样需要多次 IO，所以因为树高问题，红黑树也被放弃了。

B+ 树

B+ 树是 B 树 的变种，先弄清 B+ 树，最后在谈一下它和 B 树 的区别，B+ 树特点：

• 每个非叶子节点，不再是单独的一个索引字段，而是多组（索引字段 + 子节点指针地址）的组合构成
• 整个 B+ 树，是有序排列的
• 叶子节点存储真实数据，并且叶子节点间互相关联

在 MySQL 中，B+ 树 的节点被称为数据页，默认一个数据页的大小是 16 KB（16384 Byte）

假设一个索引字段是 8 Byte，指针地址是 6 Byte，则一个组合需要 14 Byte，则 B+ 树 的根节点可以存储 16384 / ( 8 + 4 ) = 1170 个指针地址，B+ 树 的第二层则可以存放 1170 * 1170 = 1368900 个地址，假设数据库表中一行的数据为 1KB，则非叶子节点中一页可以存储 16KB / 1KB = 16 条数据，则 B+ 树 的非叶子节点总共可以存储 1368900 * 16 = 21902400 条数据。换句话说就是 2000W 的数据量，B+ 树可以将其树高控制在 3 以内，只需要三次磁盘 IO 就可以在 2000W 数据量的表中找到目标数据。

B 树和 B+ 树的明显区别：

• B 树的非叶子节点也会存储数据（这会导致同样树高的情况下，B 树 没有 B+ 树 存储的数据量大）
• 叶子节点之间没有关联（这会导致范围查询时，效率要比 B+ 树 差）
  
  

二、索引分类

1. 聚集索引

聚集索引，简单说就是在索引树的叶子节点中存储了整行全部数据。

在 MySQL 的 InnoDB 存储引擎中使用 B+ 树 时，每个表必须有聚集索引，当我们创建主键后，主键索引生成的索引树就是聚集索引。就算我们不设置主键，MySQL 也会用隐藏的 RowID 之类的隐藏字段帮我们生成一个聚集索引。

假设数据库表中数据如上图，其中 id 是主键，那么聚集索引树就是：

2. 非聚集索引

非聚集索引，也叫二级索引，简单说就是在索引树的叶子节点中只有索引字段和主键，没有行中的其他数据。

假设数据库表中数据如上图，其中 id 是主键，number 是非聚集索引，那么非聚集索引树就是：

可以看到非聚集索引树中的叶子节点只包含 索引字段 number 和 主键字段 id，没有 普通字段 name。正是因为非聚集索引中不包含其他字段的这个特点，又衍生出两个概念 回表和覆盖索引

回表

接着上面的例子，假设有 SQL 语句为：

SELECT id, number, name From test WHERE number = 5;

因为在查询条件中使用了 number，所以会使用 二级索引，但是在二级索引树的叶子节点中并不存在 name 数据，所以查询时，会先在 二级索引树中找到 number = 5 的记录，然后拿着主键 id 回到聚集索引中再次根据主键 id 在聚集索引树中查找 name 的值， 这个过程称为回表。

覆盖索引

覆盖索引指的是要查询的字段可以在二级索引树的叶子节点中全部找到，不需要回表。

覆盖索引是一种优化目的，在建二级索引时尽量使用联合索引，让叶子节点上的索引字段能够覆盖上 SQL 中要查询的信息，这样 SQL 执行时，在二级索引树上就可以找到要查询的信息，就不会发生回表。

比如上面例子中，将 number 和 name 设置为联合索引，这样查询 name 时就不会发生回表。

三、索引文件

可以通过 SHOW VARIABLES LIKE 'datadir'; 来查看 MySQL 的数据目录。在目录中，找到和数据库名对应的目录后进入，就可以看到数据库的所有信息。

InnoDB

表的存储引擎是 InnoDB 时，表的数据信息和索引信息都在同一个文件中，名称格式为：表名.ibd

MyISAM

表的存储引擎是 MyISAM 时（只有表锁，没有行锁，而且不支持事务，很少使用），一张表需要对应三个文件：

• 表结构文件：表名.sdi
• 表数据文件：表名.MYD
• 表索引文件：表名..MYI