深入浅出mysql索引总结(上) 一文就OK

往期文章推荐

• mysql那些事儿|深入浅出mysql索引(上)
• mysql那些事儿|深入浅出mysql索引(下)
• 为什么Mysql用B+树做索引而不用B-树或红黑树？
• mysql那些事儿|mysql事务隔离机制及其原理
• 校招mysql那些事儿|日志模块binlog/redolog/undolog
• 校招mysql那些事|MVCC原理机制
• mysql那些事儿|mysql锁总结

目录

• 前言
• 索引

• 索引常用模型

  • 哈希表
  • 有序数组
  • 平衡二叉树
• InnoDB索引模型
• 主键索引和普通索引
• 页分裂和页合并
• 主键为什么建议选择自增主键？

前言

当提到mysql数据库时，脑海里本能反应蹦出几个关键词：数据结构(B+树)、索引、事务、锁、日志等等，今天就来说一说索引那些事儿，我会把索引分为上下两集来进行阐述。

可能你了解mysql索引底层采用数据结构B+树实现的，在某个字段中建立索引，会加快查询效率，但是在面试中是远远不够的，在这里，先抛出几个关于索引的面试题：

• 索引为什么使用B+树，而不使用B树，二叉搜索树？
• 那些数据结构可以作为索引？他们的优缺点是什么？
• 普通索引和主键索引的区别？
• 聚集索引和非聚集索引的区别？
• 索引什么时候会失效，最左匹配原则是什么？
• 索引下推了解吗？
• 谈一谈你对回表的理解，可以举个例子吗？
• 主键为什么建议使用自增主键？
• 页分裂和页合并

这几个面试题都会在索引这两集中一一揭晓。
第一个面试题，我在上篇文章就解答了(https://mp.weixin.qq.com/s/1sy-jLCuWgpci-56vxdiLA)

索引

一句话简单的来说，索引的出现就是为了加快数据库的查询效率，就好比书的目录一样。

举个生活中的例子，在你眼前有一本500页的书籍，如果你想要查找某个知识点，在没有目录的情况下，你可能需要一页一页翻书查找；假如这本数据有目录功能，你只需在目录上找到对应知识点的页数即可，效率大幅度提高。同样mysql也是，索引就是他的"目录"。

索引常见模型

索引的出现为了加快数据库的查询效率，但是索引的实现有多种方式，比如哈希表、有序数组、搜索树、B+树。这几种数据结构比较简单但又是面试常问的问题，希望重点关注一下。下面
我会去介绍一下这几个数据结构，他们的优缺点是什么。

哈希表

哈希表是关于键值对(key-value)存储的数据结构，使用起来比较简单，我们只需要输入特定的key值就可找到对应的value值。实现起来也比较简单，通过哈希函数把key值转换成一个固定的值，作为桶位，然后将value值存储在该桶位下。

但使用哈希表作为索引数据结构的话，存在一个问题，多个key经过哈希函数的换算，会出现同一值的情况。处理这种情况，就是在某个桶位上拉出链表进行实现，结构如下所示：

因此使用哈希表作为索引存储结构，比较适用于只有等值查询的场景。比如Memcached 及其他一些 NoSQL 引擎。

有序数组

有序数组和哈希表相比，有序数组在等值查询和区间查询场景中的性能非常优秀。由于数组为有序的，因此在区间查询的时候，定位起始值和末尾值即可，效率非常高。等值查询和哈希表类似，通过key值找到对应的value值。

但若使用有序数组作为索引数据结构，如果要往有序数组中插入某条记录，则需要挪动该记录后面的所有记录，成本较高。

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

平衡二叉树

平衡二叉树是比较常见也是常用的数据结构，他们的特点如下：

• 平衡二叉树又被称为AVL树
• 平衡二叉树是一颗空树或者它的左右两个子树的高度差的绝对值不超过1，并且左右子树也是平衡树
• 非叶子节点值大于左子节点值而小于右子节点值
• 非叶子节点最多拥有两个子节点

使用平衡二叉树作为索引数据结构，有几点不足之处：

考虑到极端情况下，每次插入的数据都比上一次插入的数据大，那么用平衡二叉树就会以线性方式进行存储，时间复杂度为O(n)。数据量很大时，在mysql中一张表存储百万条数据是很正常的一件事，这样会导致树的深度更深，mysql读取时消耗大量io。

mysql进行过磁盘读取时，是以页为单位进行读取，每个节点表示一页。而平衡二叉树每个节点存储一个关键词，导致存储空间被浪费。

InnoDB索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB引擎默认使用B+树作为索引数据结构，所有的数据都存储在B+树中的。

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

B+树特点

• 非叶子节点中不存储data，只存储索引，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点包含数据(key和data域)和指针
• 叶子节点用指针连接，提高区间访问的性能

图片:

主键索引和普通索引

在InnoDB引擎中，索引类型可以分为主键索引和普通索引。

假设，我们有一个主键列为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)，两棵树的示例示意图如下

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

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？
如果语句是 select * from T where ID=500 ，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5 ，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

页分裂和页合并

B+树为了维护索引的有序性，在插入新值的时候会做必要的维护。以上图为例，现在要插入一个ID值为700的记录，则只需要在ID为600的记录后面新增即可。但如果新增一条ID值为400的记录，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

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

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

主键为什么建议选择自增主键？

如果选用自增主键的话，每次新增数据时，都是以追加的形式进行存储的，这样就不会涉及挪动数据操作，也不会出现上述所说的页分裂问题。

如果说采用业务字段作为主键的话，每次新增数据时，都会进行索引维护，保持索引的有序性，造成写数据成本较高。

除了性能方面有区别外，再来看看存储方面。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

表中如果使用自增主键的话，若使用bigint做主键，占8个字节；如果使用身份证号作为主键的话，占用20字节。

所以，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，页存储索引越多。

上述从性能和存储空间进行分析采用自增主键和采用业务字段作为主键的优缺点，也推断出采用自增主键是比较合理的方案。

文章也会持续更新，可以微信搜索「 迈莫coding 」第一时间阅读。每天分享优质文章、大厂经验、大厂面经，助力面试，是每个程序员值得关注的平台。