数据库的索引

山涛
175 阅读8分钟

1 索引的类型

  • 根据数据结构: 哈希索引(Memory引擎)、B+树、全文索引(MyISAM)。
  • 根据叶子节点是否存放data,在InnoDB存储引擎中,可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引)。
  • 根据类型分,有主键索引,唯一索引,二级索引。
  • 从数量上分,有单个索引和联合索引。

MyISAM索引文件和数据文件是分离的，索引文件的data域保存记录所在页的地址（物理存储位置），通过这些地址来读取页，进而读取被索引的行数据。是典型的非聚集索引。 image.png

Memory引擎的数据和索引是分开的。且主键采用哈希索引的方式 image.png

InnoDB主键索引是典型的聚集索引。 image.png InnoDB的二级索引MyISAM的主键索引是非聚集索引。 image.png

2 B+树索引的前世今生

  1. 哈希索引只适用于等值查询场景
  2. 数组既可以适用于等值查询又可以适用于范围查询,但是数组发生更新维护的成本高
  3. 二叉平衡树维护和查询的时间复杂度都是 O(log(N)), 为了更更进一步地降低磁盘IO（树高度）,不得不采用多叉树。
  4. 一棵m阶B树(balanced tree of order m)是一棵平衡的m路搜索树,他的特点是多阶平衡,所有节点上都有卫星数据。
  5. B+树是B树的变体,有着比B树更高的查询性能。主要的特点在于叶子节点存在全部索引,只有叶子节点上有卫星数据,并且叶子节点有序并以链表链接。

1970年，R.Bayer和E.mccreight提出了一种适用于外查找的树，它是一种平衡的多叉树，称为B树（或B-树、B_树）。敲黑板，绝不能称之为B减树，会被笑话的。

一棵m阶B树(balanced tree of order m)是一棵平衡的m路搜索树。它或者是空树，或者是满足下列性质的树：

  • 1.根结点至少有两个子女；
  • 2、每个非根节点包含 k-1个元素和k个孩子，其中m/2 <= k <= m；
  • 4、所有的叶子结点都位于同一层。
  • 5.每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划

image.png 一个m阶的B+树具有如下几个特征：

  1. 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
  2. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
  3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。 image.png

3 聚集索引和非聚集索引

索引即数据、二次查询 两者主要区别是数据和索引是否分离。

  • 聚簇索引是将数据与索引存储到一起,找到索引也就找到了数据；
  • 非聚簇索引是将数据和索引存储分离开,索引树的叶子节点存储了数据行的地址。
  1. 在InnoDB中,一个表有且仅有一个聚簇索引（因为原始数据只留一份,而数据和聚簇索引在一起）,并且该索引是建立在主键上的,即使没有指定主键,也会特殊处理生成一个聚簇索引；其他索引都是辅助索引,使用辅助索引访问索引外的其他字段时都需要进行二次查找。
  2. 在MyISAM中,所有索引都是非聚簇索引,叶子节点存储着数据的地址,对于主键索引和普通索引在存储上没有区别。
  • 在InnoDB存储引擎中,可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引）。
  • 因为辅助索引不包含行记录的所有数据,这就意味着每页可以存放更多的键值,因此其高度一般都要小于聚簇索引。
  • 通过辅助索引来查找数据,那么当找到辅助索引的叶子节点后,很有可能还需要根据主键值查找聚簇索引来得到数据,这种查找方式又被称为书签查找,也叫回表。不需要回表的情况比如正好查询的属性就建立了索引,这种情况又称为覆盖索引。
  • 无论是何种索引,每个页的大小都为16KB,且不能更改。

image.png

4 索引的维护

mysql 索引的维护分两种情况

  • 如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。
  • 如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。 image.png

页分裂, R5所在的数据页已经满了,申请一个新的数据页，然后挪动部分数据过去。 页合并, 中间的数据页删除后,数据页利用率会下降,就会进行页合并。合并是分裂的逆过程。

4.1 自增主键

主键长度越小，普通(二级、辅助)索引的叶子节点就越小，普通索引占用的空间也就越小。从性能和存储空间方面考量，自增主键往往是更合理的选择。 但是也有场景是可以不设置自增主键的: 1.只有一个索引；2.该索引必须是唯一索引。这种情况下设置唯一索引为主键,避免了查询时需要在两颗树上进行搜索。

5 索引的查询过程

image.png

select * from T where k between 3 and 5;

以上查询语句索引查询的过程

  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，不满足条件，循环结束。 从查询过程中我们可以知道回表了两次。

5.1 覆盖索引,不需要回表

不需要回表的情况比如正好查询的属性就已经在索引上了,这种情况又称为覆盖索引。也就是说上面的例子查询语句更换为 select ID from T where k between 3 and 5;

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

6 联合索引

建立了一个联合索引 id_name_age_index，实际上相当于建立了三个索引（id）（id_name）（id_name_age)。满足最左前缀的where条件查询都可以用上这个联合索引,不论字段在查询语句中排列的顺序。下列查询语句能用上联合索引id_name_age_index

  1. where id = ？
  2. where id = ？and name = ? 、where name = ？and id = ?
  3. where id = ？and name = ? and age =? 三字段组合排列 以下查询语句不能用上联合索引
  4. where name = ? and age = ? where后面的查询条件，不论是使用（id，age，name）（name，id，age）还是（age，name，id）顺序，在查询时都使用到了联合索引,这是因为mysql有查询优化器会选择最优的执行计划。

由于覆盖索引减少了回表，可以提升查询效率。因此建立冗余的联合索引可以帮助提升查询的效率。 比如建立 (身份证ID,姓名) 的联合索引在根据身份证查姓名的场景下就会更有用。当然建立冗余的联合索引来做覆盖索引的方式需要考虑权衡维护和查询性能提升。

7 索引下推

每一个虚线箭头表示回表一次 image.png 索引下推会先根据二级索引上的字段信息进行预先过滤，减少了回表的次数。 image.png

8 普通索引和唯一索引

8.1 changebuffer

对于一个二级普通索引,update sql：update xx set name = "赐我白日梦" where name = “白日梦”打过来之后（name是索引列)，执行流程大概就像下面这样：

  1. 检查需要被update的数据是否在buffer pool中。
  2. 如果在buffer pool中直接将其update。
  3. 如果不在buffer pool中，进行磁盘的IO操作，将其读取内存中，再把它update。 image.png 因为name是个索引列,那么总的流程可以分为下面两步
  • Step1: 对buffer pool中的数据页中的数据进行update。
  • Step2: 维护为name单独创建的B+Tree。

如果要更新的数据页正好没在buffer pool内存中,那么数据页到buffer pool的磁盘IO是避免不了的。但是第二步骤其实也可以效仿buffer pool的思路,将更新操作先缓存起来,后面有时间再整理。那么这个缓存就叫 changebuffer。 这个时候完整的流程就变成了:

  1. 对buffer pool中的数据页中的数据进行update。
  2. 对二级索引B+树的更新先缓存到changebuffer。数据库在访问这个索引数据页的时候,还有定时也会changebuffer中的操作更新到二级索引,维护二级非唯一索引那棵B+树，这个后台维护的过程也叫merge。

所以,changebuffer对二级索引列的更新有一些优化。因为主键索引不需要Step2也就不需要这个优化。如果是唯一索引，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用changebuffer了。因此changebuffer是对于普通二级索引的一种优化,对于这类索引来说,更新操作经过buffer pool和changebuffer两处缓冲。

#全网最清楚的：MySQL的insert buffer和change buffer 串讲

8.2 从changebuffer看普通索引和唯一索引的选择

因为在insert的场景下,唯一索引用不上changebuffer,相对于普通索引来说IO性能会差一些，所以大部分时候选择普通索引加上changebuffer性能通常都会好一些,并且最佳实践是调大changebuffer。但是事情也并不绝对，需要根据实际的使用场景再来选择相应的索引类型。

普通索引的所有场景，使用changebuffer都可以起到加速作用吗？
因为merge的时候是真正进行数据更新的时刻，而changebuffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，changebuffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时changebuffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。 反过来写完立马读的场景，changebuffer并不会带来明显的IO性能提升反而提升了changebuffer维护的成本。

9 mysql选错索引？

Mysql优化器会根据扫描行数、扫描行数、是否使用临时表、是否排序综合地选择索引。 其中预估的扫描行数很有可能不准(基于基数和采样), image.png 数据库选错索引的情况下可以强制指定索引。

select * from t force index(a) where ...

10 字符串索引

mysql>create table SUser（
ID bigint unsigned primarykey,
email varchar(64),
...)
engine=innodb;

在SUser表中我们对email字段创建索引,有两种方式

 mysql> alter table SUser add index index1(email); 
 或 mysql> alter table SUser add index index2(email(6));

其中第二种方式由于只索引前缀，对应二级索引占的空间会小很多。但是同时带来了更多的回表次数和扫描行数。但是如果前缀长度既包括邮箱前缀又包含后缀标示信息，那么是可以做到既节省空间又不用额外增加更多查询的成本。

10.1 字符串前缀索引对覆盖索引的影响

前缀索引对覆盖索引有一定的影响。因为如果采用全部字符串的方式,查询的字段正好在索引上,那么就可以利用到覆盖索引。但是前缀索引必须回表。所以前缀索引和覆盖索引两种优化只能取其一。

10.2 其他字符串索引

前缀索引对于邮箱这种前缀区分度比较高的场景很有用。但是在区分度不高的场景下,怎么对字符串建立索引呢。

  • 倒序存储 对于身份证这种可以采用倒序存储的方式,使得索引更有区分度。
  • 新增hash字段 新增哈希字段,对等待索引的字段进行哈希。

这两种方式都不支持范围查询,hash字段还需要额外的空间。

avatar
文章
阅读
粉丝