04 深入浅出索引常见索引模型哈希表实现哈希表是一种键 - 值（key-value）存储的数据结构，通过key就能

常见索引模型

哈希表

实现

哈希表是一种键 - 值（key-value）存储的数据结构，通过key就能找到value。实现思路是把值放到数组里，用一个哈希函数把key换算成一个确定的位置，把value放在数组的这个位置。但是不可避免哈希冲突（意思是多个key经过哈希函数的换算，得到相同的值的情况），出现哈希冲突时就会创建一个链表，每次出现冲突时就会把新进来的key向链表末尾添加，所以链表中的数据并不是有序的。哈希表结构适用于等值查询，比如Memcached以及其他的NoSQL。

优点

O(1)的时间复杂度（索引维护与单值查询）

缺点

不支持范围查询

有序数组

实现

优点

log(n)的时间复杂度（二分法查询），且支持范围查询。

缺点

不便于索引维护，新的索引插入和删除都会变动二叉树，适合做静态表

二叉排序树（BST树）

特点

根节点的值大于其左子树任意一个节点的值
根节点的值小于其右节点中任意一节点的值
这一规则适用于二叉查找树中的每一个节点

优点

查询的时间复杂度比链表快，链表的查询时间复杂度是O(n)，二叉排序树平均是O(logN)。二叉排序树越平衡，就越能模拟二分法，所以越能想二分法的查询的时间复杂度O(logn)。

缺点

如果插入的节点的值的顺序是越来越小或者越来越大，那么B树就会退化成一条链表，那么其查询的时间复杂度就会降为O(n)。

平衡二叉树（AVL树）

AVL树的出现是为了解决BST树不足

特点

拥有BST树的特点
AVL树上任意节点的左、右子树的高度差最大为1。此特点可以保证树不会退化成一条链表

缺点

为了保证AVL树结构，所以添加和删除时都会修改树结构，所以AVL的查找、添加、删除的时间复杂度都是O(logn)。

平衡多路查找树（B树）

B+ 树

实现

MySQL 中的 B+Tree 有几个特点：

它的关键字的数量是跟路数相等的；
B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。举个例子：假设一条记录是 1K，一个叶子节点（一页）可以存储 16 条记录。非叶子节点可以存储多少个指针？假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点（一页）可以存储 16384 / 14 = 1170 个这样的单元（键值+指针），代表有 1170 个指针。树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170 * 1170 * 16 = 21902400。在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。
B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。
它是根据左闭右开的区间 [ )来检索数据。

优点

很好的解决了搜索树的问题,高度一般只有2到3层,并且B+树的叶子节点以双向链表的形式接连,更加适合范围查询

缺点

没有缺点

InnoDB的索引模型

表示根据主键顺序以索引的形式存储的，这种存储方式的表称为索引组织表。
索引分为主键索引和非主键索引
主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）
非主键索引的叶子节点存的是主键的值。在InndDB里，非主键索引也被称为二级索引（secondary index）
基于主键索引和普通索引的查询的区别：
- 根据主键索引查询，只需要搜索主键索引这一颗B+树。
- 根据普通索引方式查询行数据，则需要先查到普通索引树，得到id的值，再通过主键索引搜索一次。这个过程称为回表。
- 根据普通索引方式查询id，搜索普通所引述，得到id值，直接返回，不需要回表。

维护索引

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。

若索引是一直增长的，就可以保证新数数据一定是在叶子节点最右边，不会影响前面的数据。所以主键推荐自增长
若在索引中间插入一条数据，那就要挪动新插入值后面的数据，空出位置。如果插入的数据页已经满了，则会申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。
- 页分裂除了性能比较差之外，还会影响数据页的利用率。原本放在一页的数据，分成了两页存储。
- 问题：利用率为50%的原因？原本一个满页，如果要从中插入一条记录，则存储引擎会新建一个页，满页中一半的记录移动到新页上。这两个页存储都只利用了约50%。分页有可能导致连锁反应，下层的页也可能分裂。要注意的是，考虑到效率问题，分裂只会涉及当前页，不会物理上调整当前页之后的页。所以，最好使用自增主键，主键不留空隙，就不会页分裂。

在删除值的时候可能会触发页合并

当相邻的两个页由于删除了数据，利用率很低的之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。这里一般分页的大小等于磁盘页的大小一致，这样分页逻辑性比较好。删除（delete），其实只是标记被删除，并不释放磁盘空间，以备复用改磁盘空间。（线上有很多次磁盘快满了，就是释放不掉的问题）
1.InnoDB默认当空间使用下降到50%时，会触发页合并，5.6后这个值可配置，配置名称为MERGE_THRESHOLD，默认值为50。 2.另外执行重建表命令可以回收表空间，alter table A engine=InnoDB。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间就越小

若使用Int做主键，只需要4个字节，BigInt需要8个字节，varchar占用占用字节更多。
从性能和存储空间方面考量，自增主键是大部分场景最优的选择。
若只有一个唯一索引，没有其他索引，则也不用考虑其他索引的叶子节点大小的问题

重建索引

重建普通索引对整体没有太大影响
重建主键索引会重建整个表。一般推荐使用语句： alter table T engine=InnoDB

一个普通索引查询的执行过程

假设我们的表结构：

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

查询的sql语句为：

select * from T where k between 3 and 5

在InnoDB中的索引组织结构：

SQL的执行流程：

在 k 索引树上找到 k=3 的记录，得到 ID=300；
再到 ID 索引树查到 ID=300 对应的行数据；
在 k 索引树取到下一个值 k=5；得到 ID=500；
再回到 ID 索引树查到 ID=500 对应的R4；
在 K 索引树取到下一个 =6，不满足条件，循环结束。

整个过程中，读取了 k 索引树了3次（1,3,5），回表了2次（2,4）。

解释一下为什么不先在k获取到所有的主键，再回表查询？因为找完排序主键，大概率是不连续的，还需要一个个回表。

覆盖索引

如果上述的表查询语句是：

select ID from T where k between 3 and 5

这时候我们只需要查询id的数据，而id的数据已经在索引 k 索引树上，因此可以直接返回查询结果，不需要回表。也就是说，在一个查询中，索引内容已经覆盖了我们的查询需求，我们称为覆盖索引。

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

最左前缀原则

基于B+树的这种数据结构，可以利用索引的”最左前缀“，来定位记录。

假如我们有一个组合索引（name,age），索引项是按照索引定义里面出现的字段顺序排序的：

由于B+树结构是按顺序存放的，所以不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个前缀可以联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。例如：where name like '张%' and age = 10；name是可以用到联合索引，但是age就使用不到联合索引了。

建立联合索引时，需要考虑到索引的复用能力，因为可以支持最左前缀。

所以索引的顺序很重要！第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要有限采用的。

例如：联合索引(A, B)意味着不需要建立A的索引了，因为这个联合索引意味着建立了(A,B)和(A)这两种索引。

假设字段a和b都有各自的查询，我们必须要建立两个索引的情况下，就要考虑空间的原则。也就是字段长的只建立一次索引，字段短的建立两次索引。

索引下推

在查询中不符合最左前缀原则的部分，查询是怎么样的呢？

假设我们的sql：where name like '张%' and age=10；

根据最左前缀索引规则，所以这个语句搜索索引树时，只能用到”张“；然后需要判断其他条件是否满足。

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

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

无索引下推的执行流程：

索引下推的执行流程：

无索引下推的查询，在索引阶段不会处理age的值，只是按顺序把name第一个字是”张“的记录一条条取出来去回表，因此需要回表4次。

有索引下推的查询，在索引内部就判断了age是否=10，对于不符合条件的数据判断完直接跳过，所以在这个例子中只需要回表2次。

总结：在5.6版本引入了索引下推，对于不满足最左前缀查询，会先过滤掉不满足索引条件的数据，再回表查询，减少回表率，提升检索速度。

04 深入浅出索引