My Github

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  阅读(352)  评论(0)  编辑  收藏  举报