MySQL 索引

By | 2月 14, 2021

索引分类

索引类型分为 主键索引非主键索引

主键索引的叶子节点存储的是整行数据。在 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+ 树的算法,这个时候需要申请一个新的数据页,然后挪动部分数据过去,这个过程称为页分裂,性能自然会受到影响。

从存储空间角度看,如果你的表中确实有一个唯一字段,比如字符串类型的身份证号,那么应该使用身份证号作为主键,还是用自增字段做主键呢?

由于每个非主键索引的叶子结点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子结点占用约 20 个字节,而如果用整型做主键,只需要 4 个字节,如果是长整形(BIGINT)则是 8 个字节。

主键长度越小,普通索引的叶子结点就越小,普通索引占用的空间也就越小

什么情况下适合用业务字段直接做主键?

满足:1. 只有一个索引 2. 该索引必须是唯一索引 的条件

这就是典型的 KV 场景。由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小问题。

由于普通索引的叶子节点存储的是主键索引,所以主键索引的大小会直接影响其他普通索引(如果存在很多其他索引,而且数据量很大,会导致索引所占空间偏大)的叶子节点的大小。但是如果此时该表只有一个索引,就不用考虑这个问题了。而且经常需要使用这个业务字段查询数据,该字段值也唯一,这时我们就可以设置其为主键。即使该字段长度不是整型递增,索引维护较为困难一点,但是避免搜索两棵树,大大地提高了查询效率。查询往往比更新多。

这个时候就要优先考虑“尽量使用主键查询”的原则,直接将这个索引设置为主键,避免每次查询需要搜索两棵树

覆盖索引

什么是覆盖索引?考虑下面的两条 SQL 语句:

select * from T where k between 3 and 5;
select ID from T where k between 3 and 5;

第一条语句通过索引 k 这棵树找到 3 和 5 后,还需要分别再用找到的 ID 去 ID 树中查询对应的行,也就是回表。

第二条语句因为只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,所以可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,这被称为 覆盖索引

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

覆盖索引的应用

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

市民表定义如下:

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

身份证号是市民的唯一标识,也就是说,如果有根据身份证号查询市民信息的需求,只需要在身份证号字段上建立索引就够了。

那么再建立一个(身份证号、姓名)的联合索引,是不是浪费空间?

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

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

最左前缀原则

B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。

那么在建立联合索引的时候,如何安排索引内的字段顺序?

第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先采用的。

索引下推

在满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。那如果不满足的情况下呢?

比如在上面的市民表中的联合索引(name, age),如果现在有一个 SQL:

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

因为前缀索引规则,所以这个语句在搜索索引树的时候,只能用“张”,找到第一个满足条件的记录。之后呢?判断剩下的条件是否满足。

MySQL 5.6 之前,只能从查找的记录开始一个个回表查询,然后再去对比字段值,最终得到结果。

MySQL 5.6 开始,引入了索引下推优化(index condition pushdown),可以 在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注