数据库
MySQL

MySQL 45讲

# MySQL 45讲

# 基础篇

# 1.基础架构

下面是 MySQL 的基本架构示意图，从中可以看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。

MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。

具体SQL执行流程中每个组件的作用：

连接器：

连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的：

mysql -h$ip -P$port -u$user -p

在完成经典的 TCP 握手后，连接器就要开始认证身份，就是输入的用户名和密码。

如果用户名或密码不对，会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
如果用户名密码认证通过，连接器会到权限表里面查出用户拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

这就意味着，一个用户成功建立连接后，即使用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

连接完成后，如果没有后续的动作，这个连接就处于空闲状态，可以通过 show processlist 命令中看到它,其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

客户端长时间没动静，连接器就会自动断开。这个时间是由参数 wait_timeout 控制的，默认是 8 小时。

建立连接的过程通常是比较复杂的，但是全部使用长连接后，有些时候 MySQL 占用内存涨得特别快。这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

考虑以下两种方案来解决。

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
如果使用的是 MySQL 5.7 或更高版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存：

MySQL 拿到一个查询请求后，会先查询缓存。之前执行过的语句及其结果会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

但是大多数情况下我会建议你不要使用查询缓存，因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能把结果存起来还没使用，就被全清空了。对于更新频繁的数据库，查询缓存的命中率非常低。除非是一张静态表，很长时间才会更新一次。比如，系统配置表，那这张表上的查询才适合使用查询缓存。

MySQL 也提供了这种“按需使用”的方式。可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定：
mysql> select SQL_CACHE * from T where ID=10；
1
但MySQL 8.0 版本直接将查询缓存的功能删掉了。

分析器：

分析器首先会做“词法分析”。输入的SQL 语句是由多个字符串和空格组成的，MySQL 需要识别出里面的字符串分别是什么，代表什么。做完这些识别以后，会对SQL做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则判断该 SQL 语句是否满足 MySQL 语法。

优化器：

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

执行器：

开始执行的时候，要先判断你对该表有没有执行权限，如果没有，就会返回没有权限的错误。如果有权限，就继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

# 2.日志系统

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）

重要的日志模块：redo log：

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos 和 checkpoint 之间的是还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，这时候不能再执行新的更新，得停下来把 checkpoint 推进一下。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

重要的日志模块：binlog：

MySQL 整体来看，Server 层主要做功能层面的事情；引擎层负责存储相关的具体事宜。redo log 是 InnoDB 引擎特有的日志，而binlog是 Server 层的日志。

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

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

执行器和 InnoDB 引擎在执行update 语句时的内部流程。

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

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

浅色框表示在 InnoDB 内部执行的，深色框表示在执行器中执行的。

两阶段提交:

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。使用反证法来证明：

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

# 3. 事务隔离

隔离性与隔离级别：

当数据库上有多个事务同时执行时，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

用一个例子说明这几种隔离级别。假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

1
2

来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

若隔离级别是“读未提交”，则 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。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

事务隔离的实现:

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，可以得到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

# 4.索引（上）

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引的常见模型：

这里介绍三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树。下面我主要从使用的角度，简单分析一下这三种模型的区别。

哈希表是一种key-value存储数据的结构，只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路就是把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是拉出一个链表。但缺点是，链表插入不是有序的，所以哈希索引做区间查询的速度是很慢的。哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

有序数组在等值查询和范围查询场景中的性能就都非常优秀。如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候如果往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎。

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。查询复杂度为O(log(N)) ，为了维持 O(log(N)) 的查询复杂度，就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间。

InnoDB 的索引模型：

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

假设，有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

1
2
3
4
5

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。在 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 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，那就需要挪动后面的数据，空出位置。更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

在一些建表规范要求建表语句里一定要有自增主键。

自增主键的插入数据模式，正符合了递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。除了考虑性能外，还可以从存储空间的角度来看。假设表中确实有一个唯一字段，比如字符串类型的身份证号。由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢？

只有一个索引；

该索引必须是唯一索引。

# 5.索引（下）

在下面这个表 T 中，如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

下面是这个表的初始化语句。

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
 
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

1
2
3
4
5
6
7
8

这条 SQL 查询语句的执行流程：

在 k 索引树上找到 k=3 的记录，取得 ID = 300；

再到 ID 索引树查到 ID=300 对应的 R3；

在 k 索引树取下一个值 k=5，取得 ID=500；

再回到 ID 索引树查到 ID=500 对应的 R4；

在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

覆盖索引：

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”查询需求，称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5（对应的索引 k 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。

最左前缀原则：

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。为了直观地说明这个概念，用（name，age）这个联合索引来分析。

当需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

当SQL 语句的条件是"where name like ‘张 %’"。也能够用上这个索引查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

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

这里评估标准是索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候就需要同时维护 (a,b)、(b) 这两个索引。

索引下推：

还是以联合索引（name, age）为例。如果现在需要检索出表中“名字第一个字是张，年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

由于前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。

然后呢判断其他条件是否满足。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

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

在图 3 和 4 这两个图里面，每一个虚线箭头表示回表一次。图 3 中，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。图 4中InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。所以只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

# 6.全局锁和表锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁：

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

**全局锁的典型使用场景是，做全库逻辑备份。**也就是把整库每个表都 select 出来存成文本。

以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

但是让整库都只读，很危险：

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

备份为什么要加锁呢？不加锁会有什么问题？

假设你现在要维护购买系统，关注的是用户账户余额表和用户课程表。

现在发起一个逻辑备份。假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。

如果时间顺序上是先备份账户余额表 (u_account)，然后用户购买，然后备份用户课程表 (u_course)，会怎么样呢？

可以看到，这个备份结果里，用户 A 的数据状态是“账户余额没扣，但是用户课程表里面已经多了一门课”。如果后面用这个备份来恢复数据的话，用户 A 就发现，自己赚了。如果备份表的顺序反过来，用户又会亏了。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。有了这个功能，为什么还需要 FTWRL 呢？一致性读是好，但前提是引擎要支持这个隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时就需要使用 FTWRL 命令了。

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

主要有两个原因：

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议使用。
二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。

表级锁：

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

**表锁的语法是 lock tables … read/write。**与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

**另一类表级的锁是 MDL（metadata lock)。**MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

虽然 MDL 锁是系统默认会加的，但不能忽略的一个机制。比如下面这个例子：给一个小表加个字段，导致整个库挂了。

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出问题。假设表 t 是一个小表。

可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。如果只有 session C 被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。所有对表的增删改查操作都需要先申请 MDL 读锁，都被锁住，等于这个表现在完全不可读写了。

如何安全地给小表加字段？

首先要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

如果变更的表是一个热点表，虽然数据量不大，但是请求很频繁，该怎么做呢？

这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

# 7.行锁

顾名思义，行锁就是针对数据表中行记录的锁。比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

从两阶段锁说起：

事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

**在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。**如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放.

假设顾客 A 要在影院 B 购买电影票。这个业务需要涉及到以下操作：

从顾客 A 账户余额中扣除电影票价；
给影院 B 的账户余额增加这张电影票价；
记录一条交易日志。

也就是说，要完成这个交易，需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，要把这三个操作放在一个事务中。那么怎样安排这三个语句在事务中的顺序呢？

试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

根据两阶段锁协议，不论怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。影院余额这一行的行锁在一个事务中不会停留很长时间。

死锁和死锁检测：

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

所以，正常情况下还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

那如果是所有事务都要更新同一行的场景呢？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

怎么解决由这种热点行更新导致的性能问题呢？问题的症结在于，死锁检测要耗费大量的 CPU 资源。

**（1）如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。**但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

**（2）控制并发度。**如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。我见过一个应用，有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。因此，这个并发控制要做在数据库服务端。如果有中间件，可以考虑在中间件实现；如果团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

如果团队里暂时没有数据库方面的专家，不能实现这样的方案，能不能从设计上优化这个问题呢？

可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以账户为例，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理。

# 8.事务到底是隔离的还是不隔离的？

首先举个例子：

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

1
2
3
4
5
6

这里需要注意的是事务的启动时机。

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

第一种启动方式，一致性视图是在第执行第一个快照读语句时创建的；第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。

在这个例子中，事务 C 没有显式地使用 begin/commit，表示这个 update 语句本身就是一个事务，语句完成的时候会自动提交。事务 B 在更新了行之后查询 ; 事务 A 在一个只读事务中查询，并且时间顺序上是在事务 B 的查询之后。

以上例子事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1。后续将解释为什么是这样的结果。

在 MySQL 里，有两个“视图”的概念：

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

“快照”在 MVCC 里是怎么工作的？

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。这个视图数组把所有的 row trx_id 分成了几种不同的情况。

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况 a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见； b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

比如，对于图 2 中的数据来说，如果有一个事务，它的低水位是 18，那么当它访问这一行数据时，就会从 V4 通过 U3 计算出 V3，所以在它看来，这一行的值是 11。

有了这个声明后，系统里面随后发生的更新，是不是就跟这个事务看到的内容无关了呢？因为之后的更新，生成的版本一定属于上面的 2 或者 3(a) 的情况，而对它来说，这些新的数据版本是不存在的，所以这个事务的快照，就是“静态”的了

所以InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

现在我们来看开头图中的三个事务，分析下事务 A 的语句返回的结果，为什么是 k=1。

这里，不妨做如下假设：

事务 A 开始前，系统里面只有一个活跃事务 ID 是 99；
事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务；
三个事务开始前，(1,1）这一行数据的 row trx_id 是 90。

这样，事务 A 的视图数组就是 [99,100], 事务 B 的视图数组是 [99,100,101], 事务 C 的视图数组是 [99,100,101,102]。

为了简化分析，先把其他干扰语句去掉，只画出跟事务 A 查询逻辑有关的操作：

从图中可以看到，第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。这时候，这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本。

第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本。

在事务 A 查询的时候，其实事务 B 还没有提交，但是它生成的 (1,3) 这个版本已经变成当前版本了。但这个版本对事务 A 必须是不可见的，否则就变成脏读了。

现在事务 A 要来读数据了，它的视图数组是 [99,100]。当然了，读数据都是从当前版本读起的。所以，事务 A 查询语句的读数据流程是这样的：

找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见；
接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；
再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。

这样执行下来，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，称之为一致性读。

更新逻辑：

事务 B 的 update 语句，如果按照一致性读，结果不对不是一个不对吗？

是的，如果事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1。但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务 C 的更新就丢失了。因此，事务 B 此时的 set k=k+1 是在（1,2）的基础上进行的操作。

所以，这里就用到了这样一条规则：**更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。**因此，在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的 row trx_id 是 101。

所以，在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用，所以查询得到的 k 的值是 3。

其实，除了 update 语句外，select 语句如果加锁，也是当前读。

如果把事务 A 的查询语句加上 lock in share mode 或 for update，也可以读到版本号是 101 的数据，返回的 k 的值是 3。下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）。
mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;
1
2

再往前一步，假设事务 C 不是马上提交的，而是变成了下面的事务 C’，会怎么样呢？

事务 C’的不同是，更新后并没有马上提交，在它提交前，事务 B 的更新语句先发起了。前面说过了，虽然事务 C’还没提交，但是 (1,2) 这个版本也已经生成了，并且是当前的最新版本。那么，事务 B 的更新语句会怎么处理呢？

这时候，“两阶段锁协议”就要上场了。事务 C’没提交，也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C’释放这个锁，才能继续它的当前读。

到这里，我们把一致性读、当前读和行锁就串起来了

事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

在读提交隔离级别下，事务 A 和事务 B 的查询语句查到的 k，分别应该是多少呢？

事务 A 的查询语句的视图数组是在执行这个语句的时候创建的，时序上 (1,2)、(1,3) 的生成时间都在创建这个视图数组的时刻之前。但是，在这个时刻：

(1,3) 还没提交，属于情况 1，不可见；
(1,2) 提交了，属于情况 3，可见。

所以，这时候事务 A 查询语句返回的是 k=2。事务 B 查询结果 k=3。

# 实践篇

# 1.普通索引和唯一索引，应该怎么选择？

以基础篇4的例子来说（假设字段 k 上的值都不重复）

查询过程：

假设，执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

这个不同带来的性能差距微乎其微。InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

当然，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

但是，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。所以，计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

更新过程：

为了说明普通索引和唯一索引对更新语句性能的影响这个问题，需要先了解一下 change buffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

change buffer 的使用场景

通过上面的分析，已经清楚了使用 change buffer 对更新过程的加速作用，也清楚了 change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。那么，现在有一个问题就是：普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

索引选择和实践：

其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以建议尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。在实际使用中，普通索引和 change buffer 的配合使用对于数据量大的表的更新优化还是很明显的。

特别在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

change buffer 和 redo log：

表上执行这个插入语句：

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

这里假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。如图所示是带 change buffer 的更新状态图。

这条更新语句涉及了四个部分：内存、redo log（ib_log_fileX）、数据表空间（t.ibd）、系统表空间（ibdata1）。

有如下操作：

Page 1 在内存中，直接更新内存；
Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
将上述两个动作记入 redo log 中（图中 3 和 4）

执行这条更新语句的成本很低，就是写两处内存，然后写一处磁盘（两次操作合在一起写了一次磁盘），而且是顺序写的。同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间的读请求，要怎么处理呢

后续的读请求，如何处理？

比如，执行 select * from t where k in (k1, k2)。两个读请求的流程图：

从图中可以看到：

读 Page 1 的时候，直接从内存返回。
要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。直到需要读 Page 2 的时候，这个数据页才会被读入内存

简单地对比这两个机制在提升更新性能上的收益，redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

# 2.MySQL为什么有时候会选错索引？

先看一个例子：

建一个简单的表，表里有 a、b 两个字段，并分别建上索引：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB；

1
2
3
4
5
6
7
8

然后，往表 t 中插入 10 万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到 (100000,100000,100000)。

使用用存储过程来插入数据的：

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

1
2
3
4
5
6
7
8
9
10
11
12

接下来，分析一条 SQL 语句：

mysql> select * from t where a between 10000 and 20000;

从sql上观察会认为a 上有索引，会使用索引 a 。图显示的是使用 explain 命令查询的这条语句的执行情况。

从图看上去，这条查询语句的执行确实符合预期，key 这个字段值是’a’，表示优化器选择了索引 a。

在已经准备好的包含了 10 万行数据的表上，再做如下操作：

session A 的操作开启了一个事务。随后，session B 把数据都删除后又调用 idata 这个存储过程插入了 10 万行数据。这时候，session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了。可以通过慢查询日志（slow log）来查看一下具体的执行情况。为了说明优化器选择的结果是否正确，增加了一个使用 force index(a) 来让优化器强制使用索引 a的对照。

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

1
2
3

第一句将慢查询日志的阈值设置为 0，表示这个线程接下来的语句都会被记录入慢查询日志中；
第二句Q1 是 session B 原来的查询；
第三句Q2 是加了 force index(a) 来和 session B 原来的查询语句执行情况对比。

图1

可以看到，Q1 扫描了 10 万行，显然是走了全表扫描，执行时间是 40 毫秒。Q2 扫描了 10001 行，执行了 21 毫秒。也就是说，我们在没有使用 force index 的时候，MySQL 用错了索引，导致了更长的执行时间。

这个例子对应的是平常不断地删除历史数据和新增数据的场景。这时，MySQL 竟然会选错索引。

优化器的逻辑：

选择索引是优化器的工作，而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。上面这个简单的查询语句并没有涉及到临时表和排序，所以 MySQL 选错索引肯定是在判断扫描行数的时候出问题了。

扫描行数是怎么判断的？

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。可以使用 show index 方法，看到一个索引的基数。如图所示，就是表 t 的 show index 的结果。虽然这个表的每一行的三个字段值都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。

MySQL 是怎样得到索引的基数的呢？

简单介绍一下 MySQL 采样统计的方法。

为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

由于是采样统计，所以不管 N 是 20 还是 8，这个基数都是很容易不准的。但，这还不是全部。可以从图中看到，这次的索引统计值（cardinality 列）虽然不够精确，但大体上还是差不多的，选错索引一定还有别的原因。其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。接下来，再看看优化器预估的，这两个语句的扫描行数是多少。

rows 这个字段表示的是预计扫描行数

Q1 的结果还是符合预期的，rows 的值是 104620；但是 Q2 的 rows 值是 37116，偏差就大了。而图1我们用 explain 命令看到的 rows 是只有 10001 行，是这个偏差误导了优化器的判断。

到这里，可能疑问不是为什么不准，而是优化器为什么放着扫描 37000 行的执行计划不用，却选择了扫描行数是 100000 的执行计划呢？

这是因为如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。使用普通索引需要把回表的代价算进去，在图1开始执行 explain 的时候，也考虑了这个策略的代价，但图 1 的选择是对的。也就是说，这个策略并没有问题。所以MySQL 选错索引，这件事儿还得归咎到没能准确地判断出扫描行数。既然是统计信息不对，那就修正。analyze table t 命令，可以用来重新统计索引信息。再来看一下执行效果。

所以在实践中，如果发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。其实，如果只是索引统计不准确，通过 analyze 命令可以解决很多问题，但是优化器可不止是看扫描行数。

再看看另外一个语句：

mysql> select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;

从条件上看，这个查询没有符合条件的记录，因此会返回空集合。那么会选择哪一个索引呢？

为了便于分析，先来看一下 a、b 这两个索引的结构图。

如果使用索引 a 进行查询，那么就是扫描索引 a 的前 1000 个值，然后取到对应的 id，再到主键索引上去查出每一行，然后根据字段 b 来过滤。显然这样需要扫描 1000 行。如果使用索引 b 进行查询，那么就是扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行。所以会认为如果使用索引 a 的话，执行速度明显会快很多。那么，下面来看看到底是不是这么一回事儿。

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

可以看到，返回结果中 key 字段显示，这次优化器选择了索引 b，而 rows 字段显示需要扫描的行数是 50198。

从这个结果中，可以得到两个结论：

扫描行数的估计值依然不准确；

这个例子里 MySQL 又选错了索引。

索引选择异常和处理：

其实大多数时候优化器都能找到正确的索引，但偶尔还是会碰到我们上面举例的这两种情况：原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，那么此时应该怎么办呢？

一种方法是，像第一个例子一样，采用 force index 强行选择一个索引。MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

# 3.怎么给字符串字段加索引？

MySQL 支持前缀索引，也就是说可以定义字符串的一部分作为索引。默认地如果创建索引的语句不指定前缀长度，那么索引就会包含整个字符串

在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此可以通过统计索引上有多少个不同的值来判断要使用多长的前缀

select 
	count(distinct email) as L0,
  count(distinct left(email,1)) as L1,
  count(distinct left(email,2)) as L2,
  count(distinct left(email,3)) as L3,
  count(distinct left(email,4)) as L4
from sys_user;

1
2
3
4
5
6
7

前缀索引对覆盖索引的影响

select id,email from SUser where email='zhangssxyz@xxx.com';

select id,name,email from SUser where email='zhangssxyz@xxx.com';

第一个语句只要求返回 id 和 email 字段。

所以如果使用 email 整个字符串的索引结构的话，可以利用覆盖索引，从 index1查到结果后直接就返回了，不需要回到 ID 索引再去查一次。而如果使用email(6) 索引结构的话，就不得不回到 ID 索引再去判断 email 字段的值。

即使将索引的定义修改为 email(18) 的前缀索引，这时候已经包含了字符串的所有的信息，但 InnoDB 还是会回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了。

对于类似于邮箱这样的字段来说，使用前缀索引的效果可能不错。但是，遇到前缀的区分度不够好的情况时，要怎么办呢？

比如身份证号，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。假设维护的数据库是一个市的公民信息系统，这时候如果对身份证号做长度为 6 的前缀索引的话，这个索引的区分度就非常低了。这个时候可能需要创建长度为 12 以上的前缀索引，才能够满足区分度要求。但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

如果能够确定业务需求里面只有按照身份证进行等值查询的需求，还有没有别的处理方法既可以占用更小的空间，也能达到相同的查询效率。

**第一种方式是使用倒序存储。**存储身份证号的时候把它倒过来存，每次查询的时候可以这么写：

mysql> select field_list from t where id_card = reverse('input_id_card_string');

身份证号的最后这 6 位能提供了足够的区分度。。

**第二种方式是使用 hash 字段。**可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以查询语句 where 部分要判断 id_card 的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

使用倒序存储和使用 hash 字段这两种方法的异同点。

相同点：

都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的数据。同样地，hash 字段的方式也只能支持等值查询。

不同点：

从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。
在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

总结

字符串字段创建索引的场景可以使用的方式有：

直接创建完整索引，这样可能比较占用空间；
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

# 4.为什么我的MySQL会“抖”一下？

WAL 技术：全称是 Write-Ahead Logging ，它的关键点就是先写日志，再写磁盘。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

利用 WAL 技术，数据库将随机写转换成了顺序写，大大提升了数据库的性能，但是由此也带来了内存脏页的问题。

脏页：当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为 “脏页”
干净页：内存数据写入磁盘后，内存和磁盘上的数据页的内容就一致了，称为 “干净页”

平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔 “抖” 一下的那个瞬间，可能就是在刷脏页（flush）

引发数据库 flush 的情况：

InnoDB 的 redo log 写满了，这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写（redo log 的空间是循环使用的，无所谓释放。对应的内存页会变成干净页，但是等淘汰的时候才会逐出内存）
系统空间不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是脏页，就要先将脏页写到磁盘。
MySQL 认为系统 “空闲” 的时候，但即使是不空闲的时候，MySQL 也会见缝插针地找时间，只要有机会就刷一点脏页
MySQL 正常关闭的情况下，这时候，MySQL 会把内存的脏页都 flush 到磁盘上。

对于脏页，脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，就有可能会让你的更新和查询语句的响应时间长一些。

# 5. 为什么表数据删掉一半，表文件大小不变？

假设要删除某条数据，InnoDB 引擎只会把这个记录标记为删除，如果再插入一条符合范围条件的数据，就可能会复用这个位置。但是磁盘文件的大小并不会缩小。假如说删掉了一个数据页上的所有记录，那整个数据页就可以被复用了

记录复用与页复用的区别：

记录的复用，只限于符合范围条件的数据；数据页可以复用到任何位置，插入数据页进行分页后也会造成 “空洞”

如何重建表 经过大量增删改查的表，都是有可能存在空洞的，所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

1.alter table t engine = InnoDB；

2.analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；

3.optimize table t 等于 recreate+analyze。

# 6.count(*)很慢

首先要明确的是，在不同的 MySQL 引擎中，count(*) 有不同的实现方式。

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
InnoDB 引擎执行 count(*) 时需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？

因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”是不确定的。

优化count(*)慢：将行数存起来，查的时候直接返回

使用缓存系统保存计数：由于写数据库和缓存（Redis）不是原子性操作，会存在丢失更新的问题，而且即使 Redis 正常，这个值还是逻辑上不精确的
在数据库保存计数：把这个计数直接放到数据库里单独的一张计数表 C 中，这样做是行得通的，利用事务这个特性，可以将问题解决掉

不同的 count 用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

**count(主键 id) ：**InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

**count(1) ：**InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

这两个用法能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

count(字段) ：：

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，还要把值取出来再判断一下，不是 null 才累加。

**count(*) ：**并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

按照效率排序: count(字段)<count(主键 id)<count(1)≈count(*)，建议尽量使用 count(*)。

# 7.“order by”是怎么工作的？

全字段排序

取出想要查询的所有列的值放入 sort_buffer 中，然后通过快速排序返回给客户端。如果要排序的数据量小于 sort_buffer_size ，排序就在内存中完成。但如果排序数据量太大，则不得不利用磁盘临时文件归并排序

rowid 排序

如果单行长度太大，全字段索引的效率就不够好，这时候可以采用 rowid 排序，只将要排序的列以及主键 id 放入 sort_buffer ，等排序完再按照 id 值回到原表中取出所有要查询的字段再返回给客户端。如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表中去取数据。如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了，不用再回到原表中去取数据。

这也体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问

Mysql的排序成本很高，所以推荐给排序字段加索引，也可以考虑使用覆盖索引，直接返回的就是排序好的数据，不用再排序了。

# 8. SQL语句逻辑相同，性能却差异巨大？

1.对索引字段做函数操作，可能会破坏索引值的有序性，优化器就决定放弃走树搜索功能

mysql> select count(*) from tradelog where month(t_modified)=7;
mysql> select count(*) from tradelog where t_modified='2018-7-1’;

1
2

注意：虽然优化器权衡后依然选择了走索引，但是这个索引没有办法通过树搜索功能快速定位，只能全索引扫描(遍历索引树)。

2.隐式类型转换，索引字段被进行了隐式类型转换，就相当于使用了 CAST() 函数

mysql> select * from tradelog where tradeid=110717;

注意：字符串和数字做比较的话，是将字符串转换成数字再进行比较

3.隐式字符编码转换，类似于第二条，就相当于使用了 CONVERT() 函数，对应第一点

tradelog utf8mb4编码,trade_detail utf8编码

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

实际执行的时候从tradelog取出了id=2这一行的tradeid，然后根据tradeid去trade_detail表找，sql如下

select * from trade_detail d where d.tradeid=(id=2);

由于id=2的值是utf8mb4不能向小的编码转，sql又变为

select * from trade_detail d where CONVERT(d.tradeid USING utf8mb4)=(id=2);

这样又回到了第一种情况,但是这样写就会走索引

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and d.id=2;

实际执行的时候从trade_detail取出了id=2这一行的tradeid，然后根据tradeid去tradelog表找，sql如下：

select * from tradelog l where l.tradeid=(id=2);

utf8是utf8mb4的的子集，所以不用转型，这样就可以用到索引了

# 9. 为什么只查一行数据，也执行这么慢？

第一类：查询长时间不返回

等 MDL 锁

状态：使用 show processlist 命令查看到状态为 Waiting for table metadata lock

原因：可能有一个线程正在表上请求或者持有 MDL 写锁，把 select 语句堵住了

解决办法：找到谁持有 MDL 写锁，然后把它 kill 掉。通过查询 sys.schema_table_lock_waits 这张表，就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。
等 flush

状态：查询出来线程的状态是 Waiting for table flush

原因：这个状态表示现在有一个线程正要对这个表做 flush 操作

解决办法：也是使用 show processlist ，然后就跟上一种解决方式一样
等行锁

状态：加共享锁的查询语句被阻塞住

原因：有另一个事务在这行记录上持有一个写锁

解决办法：MySQL 5.7 之后可以通过 sys.innodb_lock_waits 查到是谁占着这个写锁，然后 kill 掉

第二类：查询慢

没加索引

回滚日志多

select * from t where id=1; # 800ms 查询结果：1
select * from t where id=1 lock in share mode; # 0.2ms，查询结果：1000001

1
2

因为 session B 更新完 100 万次，生成了 100 万个回滚日志（undo log）。带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行 100 万次以后，才将 1 这个结果返回。

帮助我们改善此页面！

上次更新: 2024/08/13, 09:07:12

← MySQL核心知识小结 Redis核心入门知识简记→