mysql ： 事务隔离，深入浅出索引（三）

感谢文章出处：极客邦科技

================

事务隔离文章较多，此处不详细解析；

深入浅出索引

三种模型的区别：

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，

就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数

把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

 

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

NoSQL 引擎。

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

 

有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有

人口信息，这类不会再修改的数据。

 

二叉搜索树、N 叉树

 

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组

织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+

树中的。

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

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

这个表的建表语句是：

 

表中 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，则只需要在 R5 的记录后面插入一个新记录。如果新插入的

ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请

一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自

然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个

页中，整体空间利用率降低大约 50%。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合

并。合并的过程，可以认为是分裂过程的逆过程。

基于上面的索引维护过程说明，我们来讨论一个案例：

你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自

增主键。当然事无绝对，我们来分析一下哪些场景下应该使用自增主键，而

哪些场景下不应该。

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL

PRIMARY KEY AUTO_INCREMENT。

插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的

ID 值。

也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插

入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字

段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级

索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整

型（bigint）则是 8 个字节。

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

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求

是这样的：

1. 只有一个索引；

2. 该索引必须是唯一索引。

你一定看出来了，这就是典型的 KV 场景。

由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置

为主键，可以避免每次查询需要搜索两棵树。

 

 

在下面这个表 T 中，如果我执行 select * from T where k between 3 and 5，需要执行

几次树的搜索操作，会扫描多少行？

下面是这个表的初始化语句。

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'),(7

 

现在，我们一起来看看这条 SQL 查询语句的执行流程：

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；

2. 再到 ID 索引树查到 ID=300 对应的 R3；

3. 在 k 索引树取下一个值 k=5，取得 ID=500；

4. 再回到 ID 索引树查到 ID=500 对应的 R4；

5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读

了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，

有没有可能经过索引优化，避免回表过程呢？

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的

值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是

说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

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

的性能优化手段。

需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5（对应的

索引 k 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记

录，因此 MySQL 认为扫描行数是 2。

基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身

份证号和名字建立联合索引？

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的

需求，我们只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的

联合索引，是不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义

了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行

时间。

当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权

衡考虑了。这正是业务 DBA，或者称为业务数据架构师的工作。

最左前缀原则

看到这里你一定有一个疑问，如果为每一种查询都设计一个索引，索引是不是太多了。如

果我现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的

概率不高，但总不能让它走全表扫描吧？反过来说，单独为一个不频繁的请求创建一个

（身份证号，地址）的索引又感觉有点浪费。应该怎么做呢？

这里，我先和你说结论吧。B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位

记录。

 

索引下推

上一段我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这时，

你可能要问，那些不符合最左前缀的部分，会怎么样呢？

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过

程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

在图 3 和 4 这两个图里面，每一个虚线箭头表示回表一次。

图 3 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看

age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，

需要回表 4 次。

图 4 跟图 3 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对

于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两

条记录回表取数据判断，就只需要回表 2 次。

====================

感谢原作者文档，搬录于--- 微信 ixuexi66