MySQL核心知识学习之路(3)

作为一个后端工程师,想必没有人没用过数据库,跟我一起复习一下MySQL吧,本文是我学习《MySQL实战45讲》的总结笔记的第三篇,总结了MySQL的索引相关知识。

上一篇:MySQL核心知识学习之路(2)

1 索引的模型

我们都知道索引的出现是为了提高数据查询的效率,就跟书的目录一样,对于数据库的表而言,索引就是它的“目录”。

为了了解索引,我们先从其模型入手,这里的模型是实现索引的数据结构,三种常见的模型如下所示:

  • 哈希表

    • Key-Value方式,简单高效,但只适用于等值查询的场景,例如Memcached等NoSQL引擎。

  • 有序数组

    • 相较于哈希表,在等值查询和范围查询场景都表现优秀,但是对于更新需要挪动大量元素。因此,只适用于静态存储引擎,不会怎么修改的那种。

  • 搜索树

    • 查找和更新的时间复杂度均为O(logN),但对于机械磁盘读写来说,需要放弃二叉树而使用N叉树。

在InnoDB引擎中,索引的模型采用了B+树结构。每一个索引在InnoDB里面都对应一棵B+树。

那么,问题来了,为何使用B+树

因为,B+树可以很好地配合磁盘的读写特性,减少单次查询的磁盘访问次数

2 主键索引和普通索引

设置主键(Primary Key)后会自动为主键创建主键索引,而为非主键的列创建的索引则是普通索引。

主键索引的叶子结点存放的是整行数据,而普通索引的叶子结点存放的是主键的值。

主键索引和普通索引最大的区别在于:基于普通索引的查询需要多扫描一棵索引树,这个过程称之为回表

因此,在实际应用中应尽量使用主键查询。

那么,问题来了:如果避免不了使用非主键查询,如何避免或减少回表

答案:使用覆盖索引。示例如下所示:

-- 需要回表 
select * from T where k between 3 and 5
-- 无需回表 
select ID from T where k between 3 and 5

需要注意的是:建立冗余索引来支持覆盖索引时需要权衡考虑,索引维护总是有代价的。

3 索引的维护

在MySQL中,B+树为了维护索引的有序性,在新插入值时会做必要的维护,常常需要逻辑上挪动后面的数据以腾出位置。

在挪动过程中,会出现页分裂与页合并。

页分裂与页合并

页分裂:申请新的数据页,挪动部分数据从旧数据页到新数据页。

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

自增主键

使用自增主键,每次插入新纪录都是追加,不涉及挪动其他记录,因此效率最高(性能),非主键索引占用的空间也最小(存储空间)。

最左前缀原则

B+树索引结构可以利用索引的“最左前缀”,来定位记录。因为,索引项是通过索引定义里面出现的字段顺序排序的。

图片来源:林晓斌《MySQL实战45讲》

在建立联合索引时的两个原则:

(1)第一原则:如果通过调整顺序,可以少维护一个索引,那么这个顺序需要优先考虑采用。

(2)第二原则:如果不得不维护另外的索引,那么需要考虑存储空间的大小。

重建主键索引

直接重建逐渐索引会导致整个表重建,建议可以使用此语句代替:

alter table T engine=InnoDB

4 索引的下推

在MySQL 5.6之前,需要一个一个回表。

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

示例(1)无索引下推

图片来源:林晓斌《MySQL实战45讲》

示例(2)有索引下推

图片来源:林晓斌《MySQL实战45讲》

5 小结

本文总结了MySQL的索引相关知识,包括常见模型结构、主键和普通索引、索引的维护 及 MySQL 5.6新增的索引下推优化,理解他们可以帮助我们更好地理解MySQL的索引。

参考资料

林晓斌(丁奇),《MySQL实战45讲》

👇扫码订阅《MySQL实战45讲》

 

posted @ 2021-01-29 10:16  EdisonZhou  阅读(123)  评论(0编辑  收藏  举报