mysql个人理解博客园_深入理解MySQL索引(上)

简单来说，索引的出现就是为了提高数据查询的效率，就像字典的目录一样。如果你想快速找一个不认识的字，在不借助目录的情况下，那我估计你的找好长时间。索引其实就相当于目录。

几种常见的索引模型

索引的出现是为了提高查询效率，但是实现索引的而方式有很多种，所以这里也就引入了索引模型的概念。可以用于提高查询效率的数据结构有好多种，这里先介绍常见的三种。分别是哈希表、有序数组、搜索树。

哈希表是一种以键值对形式存储的数据结构，我们只需要输入待查找的值即key，就可以找到相对应的值value。哈希表思路很简单，把值放在数组中，用哈希函数把key换算成一个数字，确定在数组中的位置，再将value放入这个位置。

但是不可避免的，会出现哈希冲突现象。不同的key传入哈希函数计算出的结果是同一个。处理这种情况的一种方法是，在冲突的位置上建立一个链表。

图中User2和User4根据身份证号算出的值都是N，但没关系，后面还跟了一个链表。假设，这时候你需要查ID_card_n2对应的名字是什么，处理步骤是：首先，将ID_card_n2通过哈希函数算出N；然后顺序遍历链表找到User2。

需要注意的是，图中的四个ID_card_n的值并不是递增的，这样做的好处是增加新的User是速度很快，但是缺点是，因为不是有序的对于区间查询的速度是很慢的，需要一个一个遍历完。

所以，哈希表这种结构适用于只有等值查询的场景，比如Memcached以及一些NoSQL引擎。

有序数组在等值查询和范围查询场景中的性能都是十分优秀的还是和上面的身份证查找的例子一样，如果用有序数组来实现，会是这个样子。

这里我们假设身份证号不重复，这个数组就是按照身份证号递增的顺序保存的。这时候要是查找ID_card_n2对应的名字，用二分法就可以快速得到，时间复杂度为O(logn)。

同时有序数组这种结构支持范围查询。你要查身份证号在[x,y] 区间的user，可以先用二分查找找到x,如果找不到x就找x的前一个User，依次向后遍历，直到找到第一个大于Y的身份证号，退出循环。

如果只关注查询效率，有序数组是最好的数据结构了。但是在需要更新数据的时候就麻烦了，你往中间插入一个记录必须挪动后面的所有记录，成本太高。

所以，有序数组只适用于静态存储，比如你要保存的是2017年某个城市的所有人口信息，这类不会在修改的数据。

如果上面的例子采用二叉搜索树来实现的话。

二叉搜索树的特点是：左孩子小于父节点，父节点小于右孩子。这样如果你要查ID_card_n2的话。按照图中的搜索顺序是userA->userC->userF->user2。时间复杂度是O(logn)。

当然为了维持O(logN)的查询复杂度，你需要保持这棵树是平衡二叉树。这样做更新的时间复杂度也是O(logN)。

二叉树是搜索效率最高的，但是实际上大多数数据库存储引擎并不使用二叉树，原因是索引布置存在内存中还存在硬盘上。

如果一个二叉树的树高是20，依次查询可能需要访问20个数据块，对磁盘操作需要多次IO，这样效率低下。为了让查询尽量的少读磁盘，就必须让查询过程访问尽量少的数据块。那么我们不应该使用二叉树，应该换成N叉树。N的大小取决于数据块的大小。

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

MySQL的索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同的存储引擎的索引工作方式也不一样，而即使多个存储引擎支持同一种类型的索引，那他们的底层实现也可能不同。现在来说说InnoDB的索引模型。

InnoDB的索引模型

在InnoDB中，表都会根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB使用了B+树索引，所以数据都是存储在B+树中的。

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

例如现在有两个索引，一个是主键索引ID，另一个是非主键索引又称为非聚簇索引 k。

从图中不难看出，根据叶子结点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子结点存的是整行数据，**在InnoDB里面，主键索引也被称为聚簇索引。非主键索引也被称为非聚簇索引。**非聚簇索引的叶子结点内容是主键的值。在InnoDB里面，非主键索引也被称为二级索引。

基于主键索引和普通索引的查询有什么区别？

如果select * from T where ID=500 ,即主键查询方式，则只需要搜索ID这颗B+树。

如果select * from T where k=5 即走普通索引查询方式，则先搜索k索引树。得到ID=500,在到ID索引树搜索一次。这个过程叫做回表。

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

索引维护

B+树为了维护索引你的有序性，在插入新的值时需要做必要的维护。以上图为例，如果插入行的一行的ID值是700，者只需要在R5后面新增一个值700.如果新插入的ID是400，那就麻烦了，需要逻辑上挪动后面的数据，空出位置。

更糟糕的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动剩余部分，这个过程叫做页分裂。这种情况下，性能自然会受到影响。

页分裂除了影响效率还影响着页的利用率。原本放在一个页的数据，现在分到两个页上，利用率降低了50%。

当相邻的两个页删除了数据，利用率很低后，会将数据页进行合并。合并过程就是叶分裂的逆向过程。

自增主键什么时候用

建表时定义自增主键：

NOT NULL PRIMARY KEY AUTO_INCREMENT

定义完成后，在插入数据的时候可以不用指定ID的值，系统会获取当前ID最大值+1作为下一个记录的ID值。

也就是说，自增主键的插入数据模式，符合前面提到的递增插入的场景。每次插入一条新纪录，都是追加操作，不涉及其他挪动操作，也不会触发页分裂。

再从存储空间上思考，假设你的表中有唯一一个字段，比如字符串类型的身份证号，那应该使用身份证号码作为主键还是使用自增主键。

由于每个非主键索引的叶子结点都包含主键的值，如果用整形作为主键需要4字节，如果是长整形需要8字节，如果是身份证号需要20个字节。

**显然，主键长度越小，不同索引的叶子结点就越小，普通索引占用的空间也就越小。

**

适合业务字段作为主键的场景

只有一个索引

该索引必须是唯一索引

平时创建索引，如果需要建立多个索引建议使用自增主键，如果是只有一个索引并且是唯一索引那就是业务字段作为索引。