MySQL实战笔记（四）数据库索引

数据库索引

索引的出现其实就是为了提高数据查询的效率你，就像书的目录一样。一本500页的书，如果你想找到其中的某个知识点，在没有目录的情况下，需要找好久。同样，对于数据库的表而言，索引其实就是它的目录。

索引的常见模型

索引的出现是为了提高查询效率，但是实现索引的方式和数据结构有多种，先学习其中的三种，它们分别是哈希表、有序数组和搜索树。

从使用者的角度简要分析一下这三种模型的区别。

哈希表是一种以键-值(key-value)存储数据的结构，我们只要输入待查找的值即key,就可以找到其对应的值即value.哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方
法是，拉出一个链表。

缺点：添加快，但是使用哈希索引做区间查询速度很慢。

所以，哈希表这种结构只适用于等值查询的场景。

有序数组在等值查询和范围查询场景中的性能都非常优秀。

可以使用二分发查找其中的数据，但是插入一个记录必须移动后面所有的记录，成本太高，所以

有序数组索引只适用于静态存储引擎。比如2017年某市人口信息，这类不会再修改的数据。

二叉搜索树:平衡二叉树   O(log(N))复杂度

树可以二叉，也可以多叉。二叉搜索树是搜索效率最高的，但是实际上大多数的数据库存储不使用二叉树，原因是索引不止存在内存中，还要写到磁盘上。

在机械硬盘时代，从磁盘随机读一个数据块需要10ms左右的寻址空间。如果有一颗100万节点的平衡二叉树，树高20层，，一次查询可能访问20个数据块，单独访问一个记录可能需要20*10ms的时间。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该
使用二叉树，而是要使用 “N 叉 ” 树。这里， “N 叉 ” 树中的 “N” 取决于数据的多少。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200 。这棵树高是 4 的时候，就可以存
1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一
个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内
存中，那么访问磁盘的平均次数就更少了。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中
了。

InnoDB  的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表，InnoDB使用了B+树索引模型，数据都是存储在B+树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

假设，我们有一个主键列为 ID 的表，表中有字段 k ，并且在 k 上有索引。

建表语句为：

mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

表中记录R1~R5 的 (ID,k) 值分别为 (100,1) 、 (200,2) 、 (300,3) 、 (500,5) 和 (600,6) ，两棵树的示例示意
图如下。

根据叶子节点的内容，索引分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。非主键索引的叶子节点内容是主键的值。

基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主
键查询。

索引维护

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

如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的
数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，
整体空间利用率降低大约 50% 。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合
并的过程，可以认为是分裂过程的逆过程。