MySQL 索引介绍

Question

索引的作用是加快查询的速度，其实上就 将无序的数据变成有序，而 InnoDB 引擎的底层索引底层结构是 B+树。之所以不使用红黑树或 B 树，原因在于 MySQL 的数据是存储在硬盘的，在查询时⼀ 般是不能「⼀次性」把全部数据加载到内存中，红⿊树是「⼆叉查找树」的变种，⼀个 Node 节点只能存储⼀个 Key 和⼀个 Value。B 和 B+树跟红⿊树不 ⼀样，它们算是「多路搜索树」，相较于「⼆叉搜索树」⽽⾔，⼀个 Node 节点可以存储的信息会更多，「多路搜索树」的⾼度会⽐「⼆叉搜索树」更低。了解了 区别之后，其实就很容易发现，在数据不能⼀次加载⾄内存的场景下，数据需要被检索出来，选择 B 或 B+树的理由就很充分了（⼀个 Node 节点存储信息更多 （相较于⼆叉搜索树），树的⾼度更低，树的⾼度影响检索的速度）。

B+树相对于 B 树⽽⾔，它⼜有两种特性。

⼀、B+树⾮叶⼦节点不存储数据，在相同的数据量下，B+树更加矮壮。（这个应该不⽤多解释了，数据都存储在叶⼦节点上，⾮叶⼦节点的存储能存储更多的索引，所以整棵树就更加矮壮）

⼆、B+树叶⼦节点之间组成⼀个链表，⽅便于遍历查询（遍历操作在 MySQL 中⽐较常⻅）。

我们在 MySQL InnoDB 引擎下，每创建⼀个索引，相当于⽣成了⼀颗 B+树。如果该索引是「聚集（聚簇) 索引」，那当前 B+树的叶⼦节点存储着「主键和当前⾏的数 据」。如果该索引是「⾮聚簇索引」，那当前 B+树的叶⼦节点存储着「主键和当前索引列值」。⽐如写了⼀句 sql：select * from user where id >=10，那只要定位到 id 为 10 的记录，然后在叶⼦节点之

间通过遍历链表（叶⼦节点组成的链表)，即可找到往后的记录了。基于树的层级以及业务使⽤场景的特性，所以 MySQL 选择了 B+树作为索引的底层数 据结构。对于哈希结构，其实 InnoDB 引擎是「⾃适应」哈希索引的（hash 索引的创建由 InnoDB 存储引擎引擎⾃动优化创建，我们是⼲预不了）。所 谓的回表其实就是，当我们使⽤索引查询数据时，检索出来的数据可能包含其他列，但⾛的索引树叶⼦节点只能查到当前列值以及主键 ID，所以需要根据主键 ID 再去查⼀遍数据，得到 SQL 所需的列。举个例⼦，我这边建了给订单号 ID 建了个索引，但我的 SQL 是：select orderId,orderName from orderdetail where orderId = 123。SQL 都订单 ID 索引，但在订单 ID 的索引树的叶⼦节点只有 orderId 和 Id，⽽我们还想检索出 orderName，所以 MySQL 会拿到 ID 再去查出 orderName 给我们返回，这种操作就叫回表。

想要避免回表，也可以使⽤覆盖索引（能使⽤就使⽤，因为避免了回表操作）。所谓的覆盖索引，实际上就是你想要查出的列刚好在叶⼦节点上都存在，⽐如 我建了 orderId 和 orderName 联合索引，刚好我需要查询也是 orderId 和 orderName，这些数据都存在索引树的叶⼦节点上，就不需 要回表操作了。

最左匹配原则：如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中 a、b、c，⽆法命中 d，先匹配最左边的，索引只能⽤于查找 key 是否存在（相等），遇到范围查询 (>、<、between、like 左匹配) 等就不能进⼀步匹配了，后续退化为线性查找。

使用 Mysql 自增主键的原因是⾸先主键得保证它的唯⼀性和空间尽可能短吧，这两块是需要考虑的。由于索引的特性（有序），如果⽣成像 uuid 类似 的主键，那插⼊的的性能是⽐⾃增的要差的，因为⽣成的 uuid，在插⼊时有可能需要移动磁盘块（⽐如，块内的空间在当前时刻已经存储满了，但新⽣成的 uuid 需要插⼊已满的块内，就需要移动块的数据）。

总结

• 为什么 B+ 树？数据⽆法⼀次 load 到内存，B+树是多路搜索树，只有叶⼦节点才存储数据，叶⼦节点之间链表进⾏关联。（树矮，易遍历）
• 什么是回表？⾮聚簇索引在叶⼦节点只存储列值以及主键 ID，有条件下尽可能⽤覆盖索引避免回表操作，提⾼查询速度
• 什么是最左匹配原则？从最左边为起点开始连续匹配，遇到范围查询终⽌
• 主键⾮⾃增会有什么问题？插⼊效率下降，存在移动块的数据问题