Mysql索引机制

索引用来做什么

  数据库索引的出现用来提高查询效率。像查字典的目录一样,根据目录去拿到相应的内容能大大提高查询效率。

索引是什么

  索引是一种有序的数据结构。可以用B+Tree来实现。对应mysql的内核,事务和索引都是在存储引擎层实现的。

为什么用B+Tree来实现呢?

我们以下分析用于提高读写效率的数据结构

哈希表
哈希表构成

哈希表是一种哈希结构,存的是键 - 值(key-value)对。
前面是数组,后面跟一个链表。

哈希表查询

Key value都放在链表里面,用key算出的哈希值放在数组里。当需要用key查value时,先调用哈希函数算出哈希值,找到指定数组,再用key去遍历后面的链表,因为链表中同时包含key value,就可以找到指定的value了。
在这里插入图片描述

哈希表优缺点

优点:哈希表在数组中的顺序并不是递增的,是由哈希函数算出来的。所以追加新数据的时候执行效率快,只需要在后面追加就可以了。

缺点:由于不是递增的,所以哈希索引做区间查询效率慢,不适合区间查询(需要全表遍历),适合适合等值查询,等值查询时间复杂度为O(1)。

适用场景

hash表这种结构适用只有等值查询的场景
比如Memchached引擎以及一些NoSql引擎(Redis)

有序数组
有序数组查询

有序数组按照某顺序依次进行保存,所以

有序数组在等值查询和范围查询场景中的性能非常优秀

等值查询的时候,采用二分法,时间复杂度为O(Log(N))
区间查询的时候,采用二分法查询到区间开始位置,从该位置向后遍历直至找到结尾位置。
在这里插入图片描述

有序数组优缺点

如果仅看查询效率,有序数组是最好的结构。

但是当对存储进行增删的时候,二分查找到定位后挪动后面的所有数据,消耗成本大。

有序数组适用场景

有序数组适用于静态存储引擎场景。也就是,静态数组静态数据。

搜索树
搜索树特点

二叉搜索树的特点是:父节点左子树所有结点的值小于父节点的值,右子树所有结点的值大于父节点的值

为了维持 O(log(N)) 的查询复杂度,你就需要保持这棵树是平衡二叉树(概念之后补)。为了做这个保证,更新的时间复杂度也是 O(log(N))。

二叉树也会畸变成单链表,所以才有了AVL树通过旋转的方式来维持树的平衡,但后来发现大量的旋转实在是性能不好,所以有了红黑树。都是二叉树,只是约束条件不一样。没有最好的方法只有更适合的方法,要维持某一方面的优势需要牺牲另一方面的优势,就看如何选择了。

搜索树查询

为什么数据库存储使用b+树而不是二叉树
  因为二叉树树高过高,需要高成本的磁盘 IO。每次查询都需要访问过多节点,即访问数据块过多,而从磁盘随机读取数据块过于耗时。

  索引不止存在内存中,还要写到磁盘上,所以实际上大多数的数据库存储并不使用二叉树

  搜索树每访问一层就读取一个数据块,不同的数据块之间切换需要进行磁盘IO(耗时),所以层数决定了访问数据块的多少。为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N 叉”树。这里,“N 叉”树中的“N”取决于数据块的大小。

N叉树如何进行计算

  MySql默认一个节点的长度为16K,当在mysql的InnoDB引擎中申明一个整数字段的索引,那么一个整数(bigint)字段索引的长度为 8B,另外每个索引还跟着6B的指向其子树的指针;所以16K/14B ≈ 1170,也就是说,一个整数字段的索引,N约等于1200 。
  也就是说,树的第四层可以存到1200的三次方,17亿个值。由于树根的数据块总是在内存中,所以一个 10 亿行的表上一个整数字段的索引,查找一个值最多只需要访问 3 次磁盘。而且树的第二层也很大概率在内存中,所以访问磁盘的次数可以更少。

搜索树适用场景

N 叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了。对于最广泛应用的InnoDB引擎来说,每一个索引在存储引擎中对应一棵B+树
在这里插入图片描述
数据库技术发展到今天,跳表、LSM 树等数据结构也被用于引擎设计中 。

怎么给字段加索引

当有一个表T要给列K加索引,建表语句如下:

mysql> create table T(
id int primary key,
 k int not null,
  name varchar(16),
  index (k))
  engine=InnoDB;

关于 InnoDB 的表结构:
1.在 InnoDB 中,每一张表其实就是多个 B+ 树,即一个主键索引树和多个非主键索引树。
2.执行查询的效率,使用主键索引 > 使用非主键索引 > 不使用索引
3.如果不使用索引进行查询,则从主索引 B+ 树的叶子节点进行遍历

索引如何工作

每一张表其实就是好几个B+树(每一个索引是一个B+树),树结点的key值就是某一行的主键,value是该行的其他数据。新建索引就是新增一个B+树,查询不走索引就是遍历主B+树。
在这里插入图片描述

聚簇索引和非聚簇索引

如上所示,索引分为两种类型:聚簇索引(主键索引)和非聚簇索引(非主键索引)

  1. 主键索引: key:主键的值,value:整行数据
  2. 非主键索引(二级索引): key:索引列的值, value:主键的值。

注1:MySQL中聚簇索引一般是主键,没有主键一般是第一个非空字段,没有非空字段会有一个隐式的自增长的值做聚簇索引。
注2: B+树主键索引的叶子结点,应该是页,每个页包含多个行
注3: 叶子节点是双向链表形式的页,单个页内部是顺序链表,链表每个节点是一行数据

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

  如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;
  如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表

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

索引如何维护

B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护。

  • 如果插入新的行 ID 在页最后,则只需要在最后一条记录后面插入一个新记录。
  • 如果新插入的 ID 值在中间,就相对麻烦了,需要逻辑上挪动后面的数据,空出位置。
页分裂
  • 如果 该ID所在的数据页已经满了,根据 B+ 树的算法(b+树的语法设计规则,如果最大阶数为n那么当一个节点中存放的数据页达到n的时候,b+就会自平衡,保持他的属性),这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。这也是索引推荐自增的原因。
  • 除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,插入了一条数据,现在分到两个页中,整体空间利用率降低大约 50%。

所以,
频繁插入和修改的列最好不要建索引;推荐使用自增主键,就可以保证新的ID一定是在叶子节点最右边,不会影响前面的数据。(防止页分裂)

页合并

当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。所以,很多数据表删除数据都是逻辑删除而非物理删除(防止页合并)。

b+树,原本的规则,插入数据是会可能导致分裂的,因为一个节点只能存储m个元素,超过m个,就会分裂,这里老师讲的不会触发叶子节点的分裂,主要是因为,innoDB对于新插入操作,做了特殊处理,如果判断需要发生分裂时,先判断左(右)兄弟节点是否还有空位,如果有就把最左(右)边的数据迁移到左(右)节点,然后更新维护索引页,最后当前节点插入,从而阻止了分裂发生,如果左右都满了,还是会发生分裂

哪些场景下应该使用自增主键,而哪些场景下不应该?

  自增主键是指自增列上定义的主键,在建表语句中一般是这么定义的: NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定 ID 的值,系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。

从性能和存储空间方面考量,自增主键往往是更合理的选择。

性能层面
  • 自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。
  • 而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。
存储空间层面
  • 二级索引存储的值是主键,如果使用业务字段占用大小不好控制,如果业务字段过长可能会导致二级索引占用空间过大,利用率不高。
  • 由于每个非主键索引的叶子节点上都是主键的值。主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小(如果用身份证号做主键,那么每个二级索引的叶子节点占用约 20 个字节,而如果用整型做主键,则只要 4 个字节,如果是长整型(bigint)则是 8 个字节。)

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

  • 只有一个索引;
  • 该索引必须是唯一索引。

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

如果重建(删除,新建)主键索引,会同时去修改普通索引对应的主键索引,性能消耗比较大。
删除重建普通索引貌似影响不大,不过要注意在业务低谷期操作,避免影响业务。

索引的查询-回表

当我们有一个查询是根据索引查询k的值在3到5之间的数据时,mysql内核是这样执行的:

  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,不满足条件,循环结束。

以上从非主键索引树回到主键索引树的过程叫做回表

这个查询过程读了 k 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)。
注:取下一个值的过程,由于叶子结点是由链表串起来的,实际上是找的下一个指针指向的位置。
在这里插入图片描述

如何经过索引的优化拒绝回表的过程?
覆盖索引

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

当二级索引查询结果仅仅是主键(或者所要的信息已经存在于这棵树的叶子结点上),此时不需要回表查主键索引–这样的非主键索引称为覆盖索引

比如:如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。

在一个人员信息表,有必要将证件号和姓名建立联合索引吗?

  当访问频次不高时,没有必要。因为可以根据证件号唯一找到一个人,这样只需要将证件号建立索引,再通过证件号索引进行回表查询姓名即可。建立联合索引将发生空间的浪费。
  当有高频的访问请求的时候就有意义了。给id_card 和 name建立联合索引后,name的值也会被保存在id_card索引树的节点上,这样根据给定id_card的值找到的对应行时,就可以直接获取到name了,而不需要拿着对应的主键再进行回表操作。这将会提升效率,但是也会带来字段维护的代价,所以建立冗余索引来支持覆盖索引需要权衡考虑。

最左前缀原则
最左前缀原则解决的问题

单独为一个不频繁的请求创建一个索引,有点浪费;

不创建索引的话,虽然查询频率不高,但又不能让它全表扫描;

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

最左前缀原则是什么

联合索引先根据第一个字段排序,如果第一个字段有相同的,就按照第二个字段排序,注意,这里仅仅有相同的第一个字段情况下,才会根据第二个字段排序。

有(姓名,年龄)联合索引如下图:

  • 当我们要查所有名字是“张三”的人时,可以快速定位到 ID4,然后向后遍历得到所有需要的结果。
  • 当要查的是所有名字第一个字是“张”的人,也能够用上这个索引,查找到第一个符合条件的记录是 ID3,然后向后遍历,直到不满足条件为止。

由如上例子可以看出:索引项是按照索引定义里面出现的字段顺序排序的。只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。
在这里插入图片描述

为什么可以用最左前缀原则

MYSQL做词法分析语法分析的时候是通过建立最左子树来建立语法树的,解析的过程也是从左到右所以遵循最左前缀的原则。

在建立联合索引的时候,如何安排索引内的字段顺序。
  1. 如果能通过调整顺序少维护一个索引,那么此顺序优先考虑
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值