第四、五讲笔记——MySQL中的索引

索引的目的就是为了提高数据的查询效率，就像字典的目录一样。

索引的常见模型

常见的有哈希表、有序数组、搜索树。

哈希表

哈希表是一种键值对的形式存储的数据结构。当遇到哈希冲突的时候，会在当前位置，拉出一个链表。

由于接在位置上的链表不是有序的，所以哈希索引做区间查询的速度很慢。例如：找到 [ID_card_X, ID_card_Y] 这个区间的所有用户，就必须全部扫一遍。

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

有序数组

有序数组在等值查询和范围查询场景中的性能都非常优秀。用上面那个找用户 ID 的例子来说明，在有序数组的索引中，可以用二分法就能查到，时间复杂度是 o(logn)。

但是，在更新数据的时候就麻烦了，插入数据往后的记录都要后移，开销太大。所以，有序数组索引只适用于静态存储引擎。

二分搜索树

二分搜索树的特点：每个节点的左子树值均小于父节点，父节点又均小于右节点。

搜索的时间复杂度是 O(log n)。为了平衡这个二叉树，更新的时间复杂度也是 o(log n)。

但大多数数据库不会使用二叉树，这样会导致树太高了。索引不仅在内存，还会写在硬盘上，在搜索时访问多次硬盘，时间消耗太长了。我们会使用“N叉数”，这里的“N”取决于数据块的大小。

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

其他

还有跳表、LSM 数等等。数据库底层存储就是基于这些数据模型，所以碰见一个新的数据库就要去关心他的随机模型，这样才能分析出这个数据库的使用场景。

InnoDB的索引模型

在 MySQL 中，索引时在存储引擎层实现的，不同的存储引擎索引方式不一样，下面以 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 中，主键索引也叫聚簇索引。
• 非主键的叶子结点存的是主键值，在 InnoDB 中，非主键索引也叫二级索引。

༼ つ ◕_◕ ༽つ那么，用主键索引和普通索引查询有什么区别呢？

• 如果语句是 select * from T where ID = 500 ，就是使用主键查询方式，则只需要搜索 ID 这课 B+ 树；
• 如果语句是 select * from T where k = 5，就是使用普通索引查询方式，则需要先搜索 K 索引树，得到对应的 ID 值为 500，再用 ID 搜索树搜索一次。这个过程叫做回表。

所以推荐尽量使用主键查询。

索引维护

B+ 树为了维护索引有序性，在插入新值时需要做必要的维护。这个过程和 B+ 树的数据结构相关。有页分裂、分裂过程的逆过程等。

为什么要推荐使用自增主键？

自增主键是指增列上定义的主键，建表时设置一个字段为自增主键用 NOT NULL PRIMARY KEY AUTO_INCREAMENT。

插入新记录时，系统会获得当期 ID 最大值加 1 作为下一条记录的 ID 值。

所以，自增主键的插入数据模式符合递增插入场景。这样，每次添加一条记录都是追加操作，不会挪动其他叶子结点，就不会触发叶子结点的分裂。而用业务逻辑上的字段做主键，不容易保证有序插入，开销过大。

除了考虑性能外，在存储空间方面也是推荐用自增主键。

使用自增主键（ int ）的话往往占用空间比较小，那么在普通索引树中，叶子结点也会比使用其他字段为主键的空间更小。主键长度越小，普通索引的叶子结点就越小，普通索引占用的空间就越小。

当然，也不是所有的场景不适合用业务字段做主键。比如：只有一个索引，并且该索引必须是唯一索引。

这就是典型的 KV 场景。（key -Value）

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

范围查询的流程

执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

这条语句的执行流程是：

1. 在 K 树上找到 k = 3 的记录，取到对应的 ID = 300；
2. 拿着 ID = 300 到 ID 树上找到对应的 R3；
3. 再去 K 树上去取下一个值 K = 5，取到 ID = 500；
4. 拿着 ID = 500 到 ID 树上找到 R4；
5. 回到 K 树上取下一个值，发现 k == 6 不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。

༼ つ ◕_◕ ༽つ在这个例子中，我们要的数据只有主键索引上面有，所以不得不回表。那么怎么优化呢？

覆盖索引

如果我们要查的数据在普通索引上，就可以不用回表去查了。比如：select ID from T where k between 3 and 5，这个时候就可以只查普通索引，无需回表。

在这个查询里面，索引 K 已经覆盖了我们要查的信息，我们称为覆盖索引。

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

注意：虽然在上面的查询流程中，在 k 树上读了三条记录，但是对于 MySQL 的 Server 层来说，它就是找引擎哪了两条数据，因此 MySQL 认为扫描行数是 2。

在一个市民信息表上，是否有必要将身份 证号和名字建立联合索引？

假设这个市民表的定义是这样的：

CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求， 我们只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联合索引，是 不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它 可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

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

最左前缀原则

感觉有点像复用联合索引一样。

对于联合索引：MySQL 从左到右使用索引中的字段，一个查询只能使用其中的一部分，但是必须是最左侧部分。

例如：key index （a，b，c）。可以支持 Sql 语句的条件在 a | a,b | a,b,c 三种情况下查找。不支持 b,c 部分的查找。

为什么会有最左前缀原则呢？ 原因是 B+ 树的数据结构，可以利用“最左前缀”，来定位记录。

例如：使用（name，age）这个联合索引来分析。

在索引中，索引项是按照索引定义时的字段排序的。

如果查找所有叫“张三”的人，可以快速定位到 ID4 ，然后向后遍历得到所有结果。

如果查找姓“张”的人，你的 SQL 条件是 where name like '张%'。这时，也可以用上这个索引。

可见，只要满足“最左前缀”，就可以用索引加快检索。

༼ つ ◕_◕ ༽つ那么问题来了：在建立联合索引时，如何安排索引内的字段顺序？

这里评估方式是：索引的复用能力。

因为支持“最左前缀”原则，当有（a，b）这个索引之后，就无须单独为 a 做一个索引了。因此：第一原则是，通过调整循序，可以少维护一个索引，那么这个顺序是可以被优先考虑的。

其次考虑空间。既要查 a 和 b， 也要这单独查这两个字段。而且 b 这个字段的值普遍比 a 这个字段的值更消耗空间，那么将 b 这个字段，换到前面去，建立两个索引（b，a）和 a 。这样消耗的空间更小

索引下推

有这么个索引（name，age），有个需求：检索出姓“张”的，并且年龄是 10 岁的所有男孩。那么 SQL 语句就会这么写：

select * from tuser where name like '张%' and age = 10 and ismale = 1;

这个语句在使用索引时，用“张”找到第一个满足条件的记录 ID3。然后回表判断是否 age = 10。

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

在 MySQL 5.6 引入索引的下推优化，在索引的遍历中，先对索引中包含的字段进行判断，直接过滤掉不满足条件的记录，减少回表次数。

以下是 MySQL 5.6 之前无索引下推执行流程

以下是 MySQL 5.6 的索引下推执行流程

可见无索引下推时需要回表四次，有了之后就只要回表两次。

小结

从课后问题中总结出来的：

当一个表的主键是联合主键时，普通主键中的值都会是这个联合主键中的所有字段，并按照定义时进行排列。

CREATE TABLE `geek` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `c` (`c`),
KEY `ca` (`c`,`a`),
KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

这里，a 和 b 是联合主键，所以主键 a，b的主键索引结构顺序相当于 order by a, b，先按 a 排序，再按 b 排序，c 无序。

在索引 ca 中，先按 c 排序，再按 a 排序，而接下来的部分是 b。

在索引 c 中，按 c 排序，接下来的部分就是主键，先按 a 排序，再按 b 排序。（是不是和索引 ca 一样的结构？）