mysql 查询模型_DB | MySQL核心知识学习之路（3）

【MySQL】| 总结/Edison Zhou

---

作为一个后端工程师，想必没有人没用过数据库，跟我一起复习一下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的索引。

6上一篇的笔误及补充

上一篇总结了MySQL的事务隔离级别，默认隔离级别应该是 可重复读(Repeatable Read)，即一个事务执行过程中看到的数据是一致的。未提交的更改对其他事务是不可见的。

如果要修改默认隔离级别为读提交：(以下为修改全局事务隔离级别)

mysql> set global transaction isolation level read committed;

事务隔离是为了解决脏读、不可重复读、幻读这几个问题，下图展示了这四种隔离级别对这三个问题的解决程度：

可以看到，只有串行化的隔离级别解决了全部问题，其他的隔离级别都各有缺陷。不过，串行化虽然可以解决所有问题，但是并发性能最差。

参考资料

林晓斌，《MySQL实战45讲》(推荐订阅学习)

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

年终总结：Edison的2020年终总结

数字化转型：我在传统企业做数字化转型

C#刷题：C#刷剑指Offer算法题系列文章目录

技术管理：IT技术人的技术管理学习进阶

商业知识：IT技术人的底层商业知识兵器库

.NET大会：2020年中国.NET开发者大会PDF资料

---

?扫码关注EdisonTalk

不变的依旧是分享！