MySQL 实战 45 讲（01-05）

本文为笔者学习林晓斌老师《MySQL 实战 45 讲》课程的学习笔记，并进行了一定的知识扩充。

sql 查询语句的执行流程

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器和执行器。

连接器负责接收客户端的请求，并对权限进行验证，对连接进行管理。确认有权限后进行数据查询，首先会查询缓存，缓存中存的是 sql 语句与结果集的映射关系，如果缓存命中则直接返回数据。如果缓存未命中，则开始真正执行 sql 语句，这就需要分析器对传过来的 sql 进行语法分析，之后再经由优化器进行优化，生成最终的执行计划。最后由执行器去调用操作引擎，返回结果集。

另外，需要注意的是，由于对于经常需要更新的非静态表，缓存命中率是非常低的。因此，MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

日志系统：sql 更新语句是怎么执行的

查询语句的那一套流程，更新语句也是同样会走一遍。不过在此基础上还涉及到两个日志模块，即 redo log（重做日志）和 binlog（归档日志）。redo log 用于数据库突然崩溃时的恢复（crash-safe 能力），binlog 用于恢复误操作时间节点前的数据，或者数据库节点扩容时保证主从数据库数据一致性。

这两种日志有以下三点不同。

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

mysql> update Table set c=c+1 where ID=2;

上面更新语句的执行逻辑：

1. 执行器先找到引擎，取出 ID = 2 这一行。
2. 执行器拿到引擎给的行数据，并 + 1，得到新的行数据，随后再调用引擎接口写入这行新数据。
3. 引擎将这个更新操作记录到 redo log 里，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器再调用引擎的提交事务接口，引擎把刚给写入的 redo log 改成 commit 状态，完成更新。

下面给出这个 update 语句更加具体的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

从上面的描述我们可以发现，redo log 的写入拆成了两个步骤：prepare 和 commit，其实这就是我们常说的”两阶段提交”。它保证了数据的一致性。

误操作后恢复数据，以及搭建一些备库来增加系统的读能力的时候。现在常见的做法是用全量备份加上应用 binlog 来实现。如果不采用“两阶段提交”就可能导致数据恢复后和历史数据不同，或者搭建备库的时候出现主从数据库数据不一致的问题。

事务隔离级别

事务的实现是基于存储引擎的，MySQL 的 Innodb 存储引擎支持四种隔离级别：读未提交、读已提交、可重复读、串行化。为的是解决多个事务产生的问题：脏读、不可重复读、幻读。

不同隔离级别下，事务 A 读到的值不同：

	V1	V2	V3
读未提交	2	2	2
读已提交	1	2	2
可重复读	1	1	2
串行化	1	1	2

解释：

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。且在“可重复读”隔离级别下，只会读到已经提交的数据。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

数据库索引

MySQL 数据库的索引是在存储引擎中实现的，不同的存储引擎支持的索引类型不同，且即使是同一种索引类型其实现方式也可能不同。

索引的常见实现方式有 3 种，即哈希、有序数组、搜索树。

哈希：适合精确查询、数据插入速度快（因为写入数据时只需要追加）、范围查询慢（因为不是有序的）

有序数组：精确查询和范围查询速度都快，但是写入速度慢。因为在数组中写入一个中间值时，需要把大于它的值都后移。

搜索树：搜索效率很高，但数据库的索引不仅仅存在内存中，还存在磁盘中。如果采用二叉树，树的层级会很深，而层级深意味着与磁盘间的 IO 操作数量更多，而 IO 操作是十分耗时的。为此，大多数数据库用的都是 N 叉树。

InnoDB 索引模型

在 InnoDB 中，表中的数据都是根据主键顺序以索引的形式存放的（即使不指定主键，也生成一个默认的主键），这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

• 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

因此，为了找到数据：

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

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

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。如果插入的新数据对应的主键 id 比原来的大，那只需要追加就行。但如果比原来的小，就需要挪动后面的数据，再进行插入。这个过程中还可能发生页分裂。此时，对性能就会受到影响。

因此，你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自增主键，这保证了新插入的数据只要追加就行，避免了数据挪动和页分裂带来的性能影响。

除此之外，我们还可以从存储的角度来看看使用递增主键的好处。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

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

有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：

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

由于没有其他索引，所以也就不用考虑其他索引的叶子节点存储空间大小的问题。

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

覆盖索引

select ID from T where k between 3 and 5

对于上面的 sql 语句，如果查询值仅为主键 ID，且 k 加了索引，那我们就称它为覆盖索引。

因为 ID 的值已经在 k 索引树上了，可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，因此我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀匹配原则

最左匹配原则的底层原理

我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

例子：假如创建一个（a,b）的联合索引，那么它的索引树是这样的：

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

由此，我们可以推出最左前缀原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

例如：如果建立(a,b)顺序的索引，我们的条件只有b=xxx，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者b=2 and a=1就可以，因为优化器会自动调整a,b的顺序，并不需要严格按照索引的顺序来；再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，a、b、c能用到索引，但d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

联合索引建立原则

在建立联合索引的时候，如何安排索引内的字段顺序?

由于最左前缀原则，在创建联合索引时，将过滤能力强的列放在前面。对于需要频繁排序的列也是放在前面（因为索引是有序的，对于查询时需要排序的列，如果能走索引，能提高查询性能）。

select * from staffs where id like 'A%';//前缀都是排好序的，使用的都是联合索引
select * from staffs where id like '%A%';//全表查询
select * from staffs where id like '%A';//全表查询

联合索引场景分析

假设我们创建一个联合索引 (id,name,age)：

create table `staffs` (`id` int default null,`name` char(32) default null,`age` int default null,KEY `id_name_age_index` (`id`,`name`,`age`)
)

1.全值匹配查询：

select * from staffs where id = 1 and name = 'jack' and age = 20;
select * from staffs where age = 20 and id = 1 and name = 'jack';
select * from staffs where name = 'jack' and id = 1 and age = 20;

过滤条件同时出现 id，name，age，且为精确查询，则不管三者的顺序如何，都能走整个联合索引。

2.匹配最左列：

符合最左匹配原则的场景：

select * from staffs where id = 1; // 使用联合索引中的 id 索引
select * from staffs where id = 1 and name = 'jack'; // 使用联合索引中的 id,name 索引
select * from staffs where id = 1 and name = 'jack' and age = 20; // 命中整个联合索引

不符合最左匹配原则的场景：

select * from staffs where name = 'jack'; // 对整个索引树进行扫描（与全表扫描不同，但也慢）
select * from staffs where age = 20; // 同上
select * from staffs where name = 'jack' and age = 20; // 同上

3.匹配列前缀：

如果 id 是字符类型：

select * from staffs where id like 'A%';//前缀都是排好序的，使用的都是联合索引
select * from staffs where id like '%A%';//全表查询
select * from staffs where id like '%A';//全表查询

4.匹配范围值：

select * from staffs where id > 1 and id < 3; // 联合索引中的 id 走索引
select * from staffs where id < 4 and age > 20 and age < 50; // id 走索引，age 不走索引
select * from staffs where age > 20 and age < 50; // 不走索引

5.精确匹配第一列并范围匹配其他列

select * from staffs where id = 1 and age < 50;

参考

1. MySQL 实战45讲
2. 数据库常见知识点总结