MySQL之索引模块

1. 为什么MySQL数据库要用B+树存储索引？

如果只选一个数据，那确实是hash更快。但是数据库中经常会选择多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。
文件系统和数据库的索引都是存在硬盘上的，并且如果数据量大的话，不一定能一次性加载到内存中。如果是在内存中，红黑树比b树效率更高，但是涉及到磁盘操作，b树就更优了。
而且数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次装入内存，B+树的设计可以允许数据分批加载，同时树的高度较低，提高查找效率。
1、减少磁盘操作：B+树非叶子不存 Value，只存放 key，所以一次更可以加载更多节点，减少磁盘加载到内存的次数。B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

2、范围查找更多优势：mysql经常有范围查找，B+树的叶子结点之间是一个双向链表，很好支持范围查找。B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

3、查找速度快：B+树类似一个多路的树了，所以同样节点，树的高度更低，查找速度更快（当然，更重要的点依然是减少）。B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化

2 什么是B树？为啥文件索引要用B树而不用二叉查找树？

树形结构例如想 B 树，B+ 树，二叉查找树都是有序的，所以查询效率很高，可以再 O(logn) 的时间复杂度查找到目标数据。

哈希表虽然能够再 O(1) 查找到目标数据，不过如果我们要进行模糊查找的话，却只能遍历所有数据，并且如果出现了极端情况，哈希表冲突的元素太多，也会导致线性时间的查找效率的。

如果是查找效率（即比较次数）的话，实际上二叉树可以说是最快的了，但是，我们的文件索引是存放在磁盘上的，所以我们不仅要考虑查找效率，还要考虑磁盘的寻址加载次数哦，而这也是我们为什么要用 B 树的原因。

在把磁盘里的数据加载到内存中的时候，是以页为单位来加载的，而我们也知道，节点与节点之间的数据是不连续的，所以不同的节点，很有可能分布在不同的磁盘页中。

而对于 B 树，由于 B 树的每一个节点，可以存放多个元素，所以磁盘寻址加载的次数会比较少，在内存的运算速度是非常快的，至少比磁盘的寻址加载速度，快了几百倍，而我们进行数值比较的时候，是在内存中进行的，虽然 B 树的比较次数可能比二叉查找树多，但是磁盘操作次数少，所以总体来说，还是 B 树快的多，这也是为什么我们用使用 B 树来存储的原因。

实际上磁盘的加载次数，基本上是和树的高度相关联的，高度越高，加载次数越多，越矮，加载次数越少。所以对于这种文件索引的存储，我们一般会选择矮胖的树形结构。例如有 1000 个元素，如果是二叉查找树的话，高度可能高达 10 层,而如果用 10 阶 B 树的话，只需要三四层即可。

B 树除了会用在少部分的文件索引（数据库索引）外，应用的最多的就是文件系统了。

3. 索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。
常见索引模型：哈希表、有序数组、搜索树

哈希表：键 – 值(key – value)。把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。哈希冲突的处理办法：链表。哈希表适用场景：只有等值查询的场景

有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))。有序数组查询效率高，更新效率低。有序数组的适用场景：静态存储引擎。

二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子。查询时间复杂度O(log(N))，更新时间复杂度O(log(N))。数据库存储大多不适用二叉树，因为树高过高，会适用N叉树。

InnoDB：B+树

索引类型：主键索引、非主键索引

主键索引的叶子节点存的是整行的数据(聚簇索引)，非主键索引的叶子节点内容是主键的值(二级索引)
主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)
一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。

覆盖索引

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

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

最左前缀

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录，在建立联合索引的时候，如何安排索引内的字段顺序。

这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。这时候，我们要考虑的原则就是空间了。

索引下推

select * from tuser where name like ‘张%’ and age=10 and ismale=1;
联合索引（name, age）

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

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

4. MyISAM与InnoDB的索引，究竟有什么差异？

MyISAM

MyISAM的索引与行记录是分开存储的，叫做非聚集索引（UnClustered Index）。
其主键索引与普通索引没有本质差异：

（1）有连续聚集的区域单独存储行记录；

（2）主键索引的叶子节点，存储主键，与对应行记录的指针；

（3）普通索引的叶子结点，存储索引列，与对应行记录的指针；

主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。

InnoDB

InnoDB的主键索引与行记录是存储在一起的，故叫做聚集索引（Clustered Index）：

（1）没有单独区域存储行记录；

（2）主键索引的叶子节点，存储主键，与对应行记录（而不是指针）

因为这个特性，InnoDB的表必须要有聚集索引：

（1）如果表定义了PK，则PK就是聚集索引；

（2）如果表没有定义PK，则第一个非空unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

聚集索引，也只能够有一个，因为数据行在物理磁盘上只能有一份聚集存储。

MyISAM和InnoDB都使用B+树来实现索引：

（1）MyISAM的索引与数据分开存储；

（2）MyISAM的索引叶子存储指针，主键索引与普通索引无太大区别；

（3）InnoDB的聚集索引和数据行统一存储；

（4）InnoDB的聚集索引存储数据行本身，普通索引存储主键；

（5）InnoDB一定有且只有一个聚集索引；

（6）InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK；
nnoDB 支持行锁和事务，而MyISAM不支持