• 0

  • 467

  • 收藏

【大白话mysql】你真的了解 mysql 索引吗?

云哥

关注云计算

1星期前

本文来源于公众号: 跬步匠心

原文链接:mp.weixin.qq.com/s... 作者:跬步匠心


什么是索引?

当我们使用汉语字典查找某个字时,我们会先通过拼音目录查到那个字所在的页码,然后直接翻到字典的那一页,找到我们要查的字,通过拼音目录查找比我们拿起字典从头一页一页翻找要快的多,数据库索引也一样,索引就像书的目录,通过索引能极大提高数据查询的效率。

索引的实现方式

在数据库中,常见的索引实现方式有哈希表、有序数组、搜索树。

哈希表

哈希表是通过键值对(key-value)存储数据的索引实现方式,可以将哈希表想象成是一个数组,将索引通过哈希函数计算得到该行数据在数组中的位置,然后将数据存到数组中,容易发现一个问题,如果两个索引通过哈希函数计算后得到的数组位置相同要怎么办?我们可以采用哈希链表,数组的每个 value 都是一个链表,新数据直接添加到链表尾部。 所以数据库查询过程为:索引通过哈希函数计算数据所在位置 --> 遍历指定位置的链表,找到满足条件的数据。

每次有新数据加入时,新数据时直接添加到链表尾部,所以添加数据时很方便。

哈希表不擅长进行区间查询,一般都用于等值查询,因为两个相邻索引通过 hash 函数后计算得到的数组位置不一定还保持相邻,需要哈希多次才能把区间的数据全查出来。

有序数组

顾名思义,有序数组是按索引大小将数据保存在一个数组上,因为该数组是有序的,可以通过二分法很容易查到位置,找到第一个位置后,通过向左或者向右遍历很容易得到所求区间的数据。因此,无论是等值查询还是区间查询,效率都极高。

但缺陷也是显而易见的,当向数组中间 n 位置插入一条数据时,需将 n 后面的数据全部往后移动,所以,这种索引一般用于静态存储引擎。

搜索树

二叉搜索树:一棵空树,或者是具有下列性质的二叉树:若它的左子树不空,则左子树上所有结点的值均小于它的根结点的值;若它的右子树不空,则右子树上所有结点的值均大于它的根结点的值;二叉搜索树的左、右子树也分别为二叉搜索树。

平衡二叉树:平衡二叉树是在二叉搜索树的基础上引入的,指的是结点的左子树和右子树的深度差不超过 1。

多叉树:每个结点可以有多个子结点,子节点的大小从左到右依次递增。

数据库一般使用平衡树来当索引的存储数据结构,当使用平衡二叉实现索引时,结构如下图。 从图中可发现,每次查询最多需要访问 4 个节点必能得到所要数据。例如查询 user2 时,查询过程为:userA-->userC-->userF-->user2。所以查询速度很高,复杂度为 O (log (n));平衡二叉树的更新复杂度也为 O (log (n))。

区间查询时,由于搜索树的特性(左子树小于右子树),可以很快的排除掉不满足条件的节点,查起来速度也是很快的。

思考下为什么用平衡搜索树呢?

因为普通的二叉树可能因为插入的数据最后变成一个很长的链表,查询复杂度退化成 O (n)。

如果搜索树存于内存中,与多叉树相比,二叉树的搜索速率是最高的,但实际上数据库使用的是 n 叉树而不是二叉树。

  • 索引不仅存于内存,还是写到磁盘上,搜索树上的每个结点在磁盘上表现为一个数据块。

  • 多叉树每个结点下可以有多个子节点,所以存储相同数据量时多叉树的树高比二叉树小,查询一个数据需要访问的结点数更少,即查询过程访问更少的数据块。查询速度较高。

在 mysql 的 innodb 引擎中,使用 B + 树来存储数据,B + 树是一种多叉平衡查找树。

innodb 的索引模型

在 B + 树中,我们将节点分为叶子结点和非叶子结点,非叶子结点上保存的是索引,而且一个节点可以保存多个索引;数据全部存于叶子结点上,并且叶子结点之间通过指针连接起来。

根据叶子结点的内容不同,innodb 索引分为主键索引和非主键索引。非主键索引也称为二级索引。主键索引的叶子结点中保存的数据为整行数据,而非主键索引叶子节点保存的是主键的值。 主键索引 非主键索引 通过主键索引查询数据时,我们只需查找主键索引树便可以获取数据;通过非主键索引查询数据时,我们先通过非主键索引树查找到主键值,然后再在主键索引树搜索一次,这个过程称为回表,也就是说非主键索引查询会比主键查询多搜索一棵树。所以我们应尽可能使用主键查询。

B + 树是一颗 N 叉树,N 是由什么决定的?能否调整?

  • 通过修改 page 的大小来间接调整 N 的大小。一个节点上的所有数据都在一个 page 中,页越大,每页存放的索引就越多,N 就越大。数据页调整后,如果数据页太小层数会太深,数据页太大,加载到内存的时间和单个数据页查询时间会提高,需要达到平衡才行。

  • 修改索引的大小。每个索引包括固定字节数的 Point 指针和索引字段内容,索引字段越小,每页能存的索引就越多,N 就越大。

索引维护

添加新行时,将会在索引表上添加一条记录,如果是索引递增插入时,数据都是追加在当前最大索引之后,不会对树中其他数据造成影响;如果新加入的数据的索引值位于节点的中间,需要挪动部分节点的位置,从而保持索引树的有序性。

而且,相邻多个节点是存储在同一个数据页上的,此时,如果是在已经存储满状态的数据页中插入节点,会申请新的数据页,将部分数据挪动到新的数据页,这个过程称为页分裂,页分裂除了会影响性能,还会降低磁盘空间利用率。不规则数据插入时,会造成频繁的页分裂。所以,一般情况下会采用递增主键,使新数据递增插入。

当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。

什么情况下应该使用业务逻辑字段做主键?有什么优缺点?

  • 业务逻辑字段不容易保证索引树结点有序插入,这样写入成本较高。

  • innodb 默认使用整数类型作为主键,主键长度较小,二级索引的叶子结点中保存的是主键值,主键长度越小,二级索引的叶子结点占用空间也就越小。

  • 当然,使用业务逻辑字段做主键也有好处,可以避免回表,每次只需扫描一次主键索引树即可。

综上,从性能和存储空间方面考量,自增主键往往是更合理的选择,但是当业务场景有且只有一个索引,而且该索引为唯一索引时,此时更适合使用业务逻辑字段作为主键,一个是避免回表,还有一个是只有一个索引也不需要考虑二级索引的空间占用情况了。

索引重建

因为数据修改、删除、页分裂等原因,会导致数据页空间利用率降低,此时,可以考虑重建索引,将数据按顺序插入,提高磁盘空间利用率。

重建普通索引时,直接先删除索引,再重新创建即可。

alter table T drop index k; 
alter table T add index(k);
复制代码

主键索引不能通过上面的语句去重建,因为删除主键索引后,innodb 会如下处理:

  • 如果存在非空且字段类型为数值的唯一索引(INT and NOT NULL and UNIQUE INDEX), 会将第一个满足条件的索引作为主键索引 , _rowid 为对应主键,值与唯一索引相同。(可通过 select _rowid from table 查询)。

  • 如果找不到合适的索引,那么 InnoDB 会自动生成一个不可见的名为 ROW_ID 的列名为 GEN_CLUST_INDEX 的主键索引,该列是一个 6 字节的自增数值,随着插入而自增。

所以删除主键索引的结果其实是修改了主键字段,而普通索引的叶子节点存的是主键的值,所以,一旦修改了主键字段,普通索引也会有影响,叶子节点的值将被修改成新的主键字段。

当主键索引需要重建时,更好的做法是直接使用 alter table t engine=innodb 重建表。

写在最后

喜欢本文的朋友,欢迎关注公众号「跬步匠心」,专注大白话分享实用技术

免责声明:文章版权归原作者所有,其内容与观点不代表Unitimes立场,亦不构成任何投资意见或建议。

云计算

467

相关文章推荐

未登录头像

暂无评论