关于索引必须知道的知识

mysql索引的各种概念

在学习索引的时候,常常会看到回表、覆盖索引、索引下推、页分裂等等概念,本篇就常见概念进行介绍和总结,希望能帮助大家快速掌握这些“高大上”的概念。

目录

索引基于B+树,要想更好地理解这些概念建议先了解谈谈MySQL索引底层实现之数据结构数据结构之B+树

回表

根据叶子节点的内容, 索引类型分为主键索引和非主键索引。(mysql索引的数据结构是B+树,对这方面知识看不懂的可以参考)

  • 主键索引的叶子节点存的是整行数据。 在InnoDB里, 主键索引也被称为聚簇索引(clustered index)。
  • 非主键索引的叶子节点内容是索引字段值+主键的值。 在InnoDB里, 非主键索引也被称为二级索引(secondary index) 。

基于主键索引和普通索引的查询有什么区别?

普通索引查询方式,则需要先搜索索引树,得到主键值,再到主键索引树按主键值搜索一次,这个过程称为回表

也就是说,基于非主键索引的查询需要多扫描一棵索引树。 因此, 我们在应用中应该尽量使用主键查询。

索引维护——页分裂

什么是页分裂?

如果R5所在的数据页已经满了, 根据B+树的算法, 这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。 当然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后, 会将数据页做合并。 合并的过程, 可以认为是分裂过程的逆过程

页分裂的坏处

除了性能外, 页分裂操作还影响数据页的利用率。原本放在一个页的数据, 现在分到两个页中,整体空间利用率降低大约50%。

如何避免页分裂?

使用自增主键。每次插入一条新记录, 都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。

自增主键的其他好处

由于每个非主键索引的叶子节点上都是主键的值。,如果用身份证号做主键, 那么每个二级索引的叶子节点占用约20个字节, 而如果用整型做主键, 则只要4个字节, 如果是长整型(bigint)则是8个字节。显然, 主键长度越小, 普通索引的叶子节点就越小, 普通索引占用的空间也就越小。

有没有什么场景适合用业务字段直接做主键的呢?

比如,有些业务的场景需求是这样的:

  1. 只有一个索引
  2. 该索引必须是唯一索引

由于没有其他索引, 所以也就不用考虑其他索引的叶子节点大小的问题。这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则, 直接将这个索引设置为主键,可以避免每次查询需要搜索两棵树。

覆盖索引

非聚集索引的B+树节点存储的是索引列和主键,假如想要拿到完整数据的话还得根据主键去主键索引树回表,这样性能不好,如果我们要查询得到的数据就是索引列和主键中的数据,就不要回表。这样只需要在一棵索引树上就能获取SQL所需的所有列数据无需回表的索引称为覆盖索引

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

最左匹配原则

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

索引树排序规则:在对联合索引建立索引树时,会按照索引字段的顺序依次排序。以(name,age,address)这个联合索引为例,首先按照name排序完,在name排序值相同时继续按照age排序。

最左匹配:在mysql建立联合索引时还会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配(左边的匹配不上,后面也不会再去匹配了)。同时,索引只能用于查找key是否存在(相等),遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了,后续退化为线性查找。因此,列的排列顺序决定了可命中索引的列数

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

  • 考虑索引的复用能力。 因为可以支持最左前缀, 所以当已经有了(a,b)这个联合索引后, 一般就不需要单独在a上建立索引了。 因此, 第一原则是, 如果通过调整顺序, 可以少维护一个索引, 那么这个顺序往往就是需要优先考虑采用的。
  • 考虑空间。不要无节制的创建索引。

前缀索引

对字符串的前几个字符(具体是几个字符在建立索引时指定)建立索引,这样建立起来的索引占用空间更小

对字符串建立普通索引和前缀索引的语句如下:

# 普通字符串索引
alter table SUser add index index1(email);
# 前缀索引,索引长度为6
alter table SUser add index index2(email(6));

前缀索引的优势和损失

优势:占用的空间会更小

损失:会增加额外的记录扫描次数

使用前缀索引,定义好长度, 就可以做到既节省空间,又不用额外增加太多的查询成本。

当要给字符串创建前缀索引时如何确定应该使用多长的前缀?

前缀索引肯定会损失区分度,我们需要提前预设一个可以接受的损失比列,使用count计算出多种长度的损失比例,选择低于损失比例的最短长度。

第一步:计算算出这个列上有多少个不同的值:

select count(distinct email) as L from SUser

第二步:计算不同长度去重后有多少数据:

select
	count(distinct left(email,4)) as L4,
	count(distinct left(email,5)) as L5,
	count(distinct left(email,6)) as L6,
	count(distinct left(email,7)) as L7,
from SUser;

第三步:选择合适的长度

在返回的L4~L7中,找出不小于 L * 95%的值,假设这里L6、L7都满足,你就可以选择前缀长度为6。

前缀索引对覆盖索引的影响

使用前缀索引就不能使用覆盖索引对查询性能进行优化了。因为索引只包含了字符串的部分数据。

遇到前缀的区分度不够好的情况时,我们要怎么办?

  1. 使用倒序存储:
    • 不会消耗额外的字段,但是每次索引一般不止4个字符,索引树需要多的存储空间
    • 每次写和读的时候,都需要额外调用一次reverse函数
    • 查询时有前缀索引的问题:会增加额外的记录扫描次数
    • 不支持范围查找
  2. 使用hash字段
    • 需要额外添加一个hash字段
    • 每次需要额外调用一次crc32()函数
    • 查询性能相对倒序存储更稳定一些
    • 不支持范围查找
    • 不再是使用前缀索引的方式

索引下推

上一段我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。 这时,你可能要问,那些不符合最左前缀的部分, 会怎么样呢?

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

我们还是以市民表的联合索引(name, age) 为例。 如果现在有一个需求: 检索出表中“名字第一个字是张, 而且年龄是10岁的所有男孩”。 那么, SQL语句是这么写的:

你已经知道了前缀索引规则, 所以这个语句在搜索索引树的时候, 只能用 “张”, 找到第一个满足条件的记录ID3。 当然, 这还不错, 总比全表扫描要好。然后呢?当然是判断其他条件是否满足。

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

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

本文记录比较零散,如果有模棱两可或者不对的地方欢迎大家指正。

发表评论

评论已关闭。

相关文章