05 深入浅出索引（下）

假设有个表其中主键 ID，表中含有字段 k，k 上添加索引。表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下，接下来要使用索引 k 进行查询，其 SQL 语句为：select * from T where k between 3 and 5，要经历几次 B+树 的搜索呢？扫描了多少行呢？

5.1、查询流程

• 1.在 k 索引的 B+树 上进行搜索获取到 k=3 所对应的 ID=300 的值。
• 2.在 ID 索引的 B+ 树 上进行搜索获取到 ID=3 的 R3 整行数据。
• 3.在 k 索引的 B+树 上取下一个值 k=5 的叶子节点中存储的 ID=5 的值。
• 4.在 ID 索引的 B+树 上进行搜索获取到 ID=5 的 R5 整行数据。
• 5.在 k 索引的 B+树 上取得下一个值 k=6，发现不满足条件，结束循环。
• 6.可以看出查询过程中在 k 索引的 B+树 上访问了三行数据，一共回表了2次，但需要注意的是，虽然引擎在索引 k 上读取了三行数据，但是由于 Mysql 的 Server 层只收到了两条记录，因此 Mysql 任务扫描的行数只有两条。
• 然而由于数据只有在主键索引 B+树 上才有，因此不得不进行回表操纵，如何进行优化，避免回表过程？

5.2、使用覆盖索引

• select ID from T where k between 3 and 5; 需要查询的 ID 的值在索引 k B+树上存储着，因此不需要进行回表操作。
• 例如：一个用户表，其中有用户的身份证和姓名的两个字段，其中身份证是具有唯一性的字段，使用它作为索引就够了，但是如果需要频繁的进行根据身份证查询其对应的用户名称的场景下，使用身份证和用户名的联合索引虽然回损耗些空间资源，但是可以在这种高频请求需求上提高语句执行速率，提高查询效率。

5.3、最左前缀原则

• 如果我们为每一种查询创建一个索引或者联合索引，甚至为一个不频繁的查询建立一个索引会显得太过累赘，但是如果数据量比较大，不使用索引导致要全表搜索又特别耗时，但是在 InnoDB 引擎中，使用的 B+树 这种索引结构，可以利用索引的最左前缀来为查找的数据进行定位。
• 当创建的联合索引后，一般就不用再为联合索引中单独的字段创建一个索引，原因是可以利用最左前缀的第一条原则，通过调整联合索引中的索引顺序达到既可以使用联合索引的功能又能使用联合索引中最左的字段的单个索引的功能，这样做可以减少一个索引的维护。例如：创建了 (a,b) 这个联合索引，之后可以利用联合索引的最左前缀原则，在条件只有 a 字段的查询中仍然可以使用，相当于少创建一个单独的 a 索引的目的。
• 如果需要创建 (a,b) 联合查询，但是由于基于 a, b 各自的查询，此时 a 和 b 在联合查询的顺序就需要从空间方面进行衡量，选其字段占用空间小的单独创建一个索引。

5.4、索引下推

• 前面讲了联合索引中的最左前缀原则，当执行查询语句：select * from user where name like ‘张%’ and age=10 and ismale=1; 时，首先在 (name, age) 联合索引 B+树中通过判断满足 name like ‘张%’ 给需要查找的结果在表中进行定位，在从查找到的位置向下遍历，之后根据遍历满足 name 条件的结点获取其叶子结点中存储的 ID 主键的值，之后再向主键索引上根据 ID 找到其叶子节点上对应的行，根据行中的字段在对其 age 进行判断，满足条件的用于结果返回。以上是 Mysql5.6 以前的做法，该方法使得可能需要为一些不满足查询条件的行进行回表到主键索引上进行二次判断，因此在 Mysql5.6 及其以后的版本中采用了索引下推的方式来减少回表的次数，首先根据 name 定位到 (name, age) 联合索引上对查找的数据进行主键定位，之后再对联合索引中包含的字段进行判断，筛选出都满足条件的 ID 主键，之后根据 ID 主键回表到主键索引上查找获取要查找的数据行，这样在回表前对联合索引中所有的字段进行判断，达到减少回表的次数提高查询速率。

5.5、问题

• 其中有个表创建的结构如图所示：其中创建了主键联合索引（a, b），只要单独创建索引 c 不就等于有了单独的 a, b, c 三个索引吗，为什么还要创建联合索引（c, a）和（c, b）?其中给出的解释是因为业务中有两条语句，这样的解释合理嘛？即使有这两个业务操作创建（c, a）和（c, b）联合索引是必要的嘛？

  • select * from geek where c=N order by a limit 1;
  • select * from geek where c=N order by b limit 1;
    -

• 创建聚簇索引（a,b），在其数据存储中会先对每一条数据行中的 a 字段进行排序，之后在对 b 字段进行排序，而 c 是乱序的。

• 创建联合索引（c,a），在其数据存储中会先对每一条数据中的字段 c 进行排序，之后在对其字段 a 进行排序，其叶子节点中存储的只是字段 b，这与单独创建一个索引 c 的结果是一样的，对其字段 c 进行排序，之后其每个叶子节点中存储的主键也是其主键索引 B+树种的存储顺序，先对其 a 进行排序，因此单独创建联合索引（c ,a）与创建索引 c 是重复的作用，因此可以不创建其（c, a）索引。

• 创建联合索引（c, b），在其数据存储种会先对每一条数据中的字段 c 进行排序，之后在于其字段 b 进行排序，其叶子结点存储的主键只有 a，这与单独创建索引 c 的存储顺序不一致，因此可以保留。

• 最终得到的判断就是保留其联合索引（c, b），但是其联合索引（c, a）与索引 c 存储的顺序一致，并且创建索引 c 需要的存储空间少于创建联合索引（c, a）因此可以将其索引删除。