/

MySQL 实战 45 讲 Part1

MySQL 实战 45 讲 | 林晓斌

01 | 基础架构：一条 SQL 查询语句是如何执行的？

image

连接器：跟客户端建立连接、获取权限、维持和管理连接。

SHOW PROCESSLIST;

137462239  db  10.135.4.7:41261  db  Query  0  starting  show processlist
-- 一个空闲连接
137462293  db  10.130.128.1:47174  db  Sleep  24
-- 客户端如果太长时间没动静，连接器就会自动将它断开
select @@wait_timeout;
show variables like 'wait_timeout';
-- 28800 8h

通过执行 mysql_reset_connection 来重新初始化连接资源，这是个接口函数，不是一个 SQL 语句。

查询缓存：不建议使用。在一个表上有更新的时候，跟这个表有关的查询缓存都会失效。

select @@query_cache_type;
-- OFF

分析器：词法分析、语法分析。进行分词和验证语法规则。解析器和预处理器。

  • 解析器处理语法和解析查询，生成一课对应的解析树。
  • 预处理器进一步检查解析树的合法。比如: 数据表和数据列是否存在，别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。

优化器：决定使用哪个索引，决定各个表的连接顺序。

执行器：有没有执行查询的权限，操作引擎，返回结果。执行器调用的次数（rows_examined）与引擎总共扫描行数可能是不等的，后文有例子。

读写、存取数据在 engine 引擎层，连接、鉴权、计算在 server 服务层。

连接的长短是由客户端来决定的，MySQL 服务端不会主动断开连接，除非到了 waiting_timeout 所设置的时间。

查询一个没用 k 列的表 1054 - Unknown column 'k' in 'field list' 是在哪阶段报错的？

  • 答案是：分析器。分析器在词法分析阶段，需要知道 SQL 中的每个字段代表什么意思，所以在这个阶段就可以判断表中存不存在 k 这一列。
  • 不是执行器的原因：有人说在执行器时才打开表获取数据，但是表的字段不是数据，是事先定义好的，所以可以直接读取的，不需要打开表。

02 | 日志系统：一条 SQL 更新语句是如何执行的？

WAL 的全称是 Write-Ahead Logging，先写日志，再写磁盘。

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1”。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
  • 物理：InnoDB 存储引擎提供接口给执行器调用（操作数据），数据库的数据是存在磁盘上的（或者说硬盘上），那么 redo log 记录存储引擎修改硬盘上的数据的操作就叫做物理操作；物理日志就只有“我”自己能用，别人没有共享我的“物理格式”。
  • 逻辑：binlog 归档日志，有两种模式 1 statement 记录 SQL；2 row 格式记录两条数据，数据修改前的样子，数据修改后的样子。记录的是一种逻辑上的变化 。逻辑日志可以给别的数据库，别的引擎使用，已经大家都讲得通这个“逻辑”。
image

把 binlog 夹在 redo log 中间，就是为了保证如果 redo 提交前的任何失败，都会带来回滚，binlog 的写入也应该不会成功，只有这样，才能保证两个一致。

  • 如果提交了 binlog，提交事务接口崩溃了，恢复时 redo log 有日志记录，binlog 有日志记录，一致，直接自动提交事务，事务完成确认数据修改成功。
  • 如果提交 binlog 前就崩了，redo log 是 prepare 阶段，binlog 没有记录不一致，事务回滚，事务执行失败。

redo log 负责事务、crash-safe；binlog 负责归档恢复。redo log 是物理的，binlog 是逻辑的。

redo log 是顺序写，数据文件是随机写。

MySQL 的记录是以“页”为单位存取的，默认大小 16K。也就是说，你要访问磁盘中一个记录，不会只读这个记录，而会把它所在的 16K 数据一起读入内存。

-- 每个 binlog 文件的大小
SELECT @@max_binlog_size / 1024 / 1024;
-- 256.00000000

-- 一页数据大小
SELECT @@innodb_page_size / 1024;
-- 16.0000

redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

-- 保证 crash-safe 能力；每次事务的 redo log 都直接持久化到磁盘
select @@innodb_flush_log_at_trx_commit;
-- 2

-- 每次事务的 binlog 都持久化到磁盘
select @@sync_binlog;
-- 0

SELECT @@binlog_format;
-- STATEMENT 是记 SQL 语句，但是有风险比如时间函数
-- ROW 记录行的内容，记两条，更新前和更新后都有

03 | 事务隔离：为什么你改了我还看不见？

脏读（dirty read）读到其他事务未提交的数据，仅发生在读未提交的的隔离级别下：

-- session1 当数据库中一个事务A正在修改一个数据但是还未提交或者回滚
BEGIN;
SELECT k FROM T1 WHERE id=1;
-- 1
UPDATE T1 SET k=2 WHERE id=1;

-- session2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT k FROM T1 WHERE id=1;
-- 2 session2 出现脏读

不可重复读（non-repeatable read）前后读取的记录内容不一致，发生在读未提交、读提交的隔离级别：

-- session1 在一个事务A中多次操作数据，在事务操作过程中(未最终提交)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT k FROM T1 WHERE id=1;
-- 1

-- session2 修改改值
UPDATE T1 SET k=2 WHERE id=1;

-- session1
SELECT k FROM T1 WHERE id=1;
-- 2 session1 出现不可重复读

幻读（phantom read）前后读取的记录数量不一致，发生在读未提交、读提交的隔离级别，InnoDB RR 不发生幻读：

-- session1 获取当前行数量
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM T1 WHERE k=1;
-- 1

-- session2 插入一行数
INSERT INTO T1 (k) VALUES (1);

-- session1
SELECT count(*) FROM T1;
-- 2
-- session1 表数据
BEGIN;
SELECT * FROM T1;
-- id,k 1,1 只有一行数据

-- session2 更新 id 主键
UPDATE T1 SET id=5 WHERE id=1;

-- session1
SELECT * FROM T1;
-- id,k 1,1
UPDATE T1 SET id=id+1;
-- ok
SELECT * FROM T1;
-- 竟然看到了两行 1,1 6,1
-- 1,1 MVCC 保证了 1,1 的存在
-- 因为主键的 UPDATE 在 MySQL 里是以 insert+delete 方式执行的。这个 6 和 1 在 MySQL 看来已经不是同一行数据了，1 的 delete version 是在事务 1 的可见范围，所以才能看得到。如果是非主键就只用一行，就是下面的例子.
-- 这个例子不能被归类为幻读，只不过是当前最新读带来的问题。
-- session1 获取 id=1 的 k
BEGIN;
SELECT k FROM T1;
-- 1 一行数据

-- session2 更新
UPDATE T1 SET k=2;

-- session1
SELECT k FROM T1;
-- 1
UPDATE T1 SET k=k+1;
-- ok
SELECT k FROM T1;
-- 3

不可重复读和幻读区别：由于在 InnoDB RR 下模拟不出幻读的场景，退回到 RC 隔离级别的话就容易把幻读和不可重复读搞混淆。理论上 RR 级别是无法解决幻读的问题, 但是由于 InnoDB 引擎的 RR 级别还使用了 MVCC，所以也就避免了幻读的出现。

后文有讲到 FOR UPDATE 下的幻读。

事务隔离级别：

  • 读未提交（READ UNCOMMITTED）一个事务还没提交时，它做的变更就能被别的事务看到。直接返回记录上的最新值，没有视图概念。
  • 读提交（READ COMMITTED）一个事务提交之后，它做的变更才会被其他事务看到。这个视图是在每个 SQL 语句开始执行的时候创建的。
  • 可重复读（REPEATABLE READ）一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的 read-view。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。这个视图是在事务启动时（第一个 SQL 执行时）创建的，整个事务存在期间都用这个视图。
  • 串行化（SERIALIZABLE）对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。用加锁的方式来避免并行访问。
show variables like '%tx_isolation%';
show variables like 'transaction_isolation';
-- tx_isolation  REPEATABLE-READ

select @@tx_isolation;
-- REPEATABLE-READ

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 修改 session 事务隔离级别

SET TRANSACTION Statement | mysql

多版本并发控制（MVCC）

set autocommit=0，这个命令会将这个线程的自动提交关掉。

-- 持续时间超过 60s 的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
commit work and chain;
-- 等价于 commit; begin;

如何避免长事务对业务的影响？

  • 确认是否使用了 set autocommit=0，如果没有，则可以使用 set autocommit=1 来避免长事务对业务的影响。
  • 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。
  • 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。
  • 监控 information_schema.Innodb_trx 表，设置长事务阈值。
  • 开发测试阶段输出所用 general_log，分析日志提前发现问题。

04 | 深入浅出索引（上）

  • 哈希表：这种结构适用于只有等值查询的场景。
  • 有序数组：有序数组在等值查询和范围查询场景中的性能就都非常优秀。但有序数组索引只适用于静态存储引擎
  • 搜索树：平衡二叉树是 O(log(N)) 的查询复杂度；N 叉树，以 InnoDB 整数字段索引为例，这个 N 差不多是 1200。

MySQL 默认一个节点的长度为 16K，一个整数（bigint）字段索引的长度为 8B，另外每个索引还跟着 6B 的指向其子树的指针；所以 16K/14B ≈ 1170

innodb B+树主键索引的叶子节点存的是什么：

回复 1：

InnoDB 磁盘管理的最小单位就是“页”，也就是说无论是叶子节点、非叶子节点和行数据，都是存放在页当中。页组成结构有头部数据、主体数据和尾部数据。头部数据主要存的是页相关数据，例如上一页、下一页、当前页号等。是一个双向链表结构。主体数据主要关注索引和数据的存储，也就是我们常说的索引和数据的存储位置。主体数据当中有一个“User Records”的概念，用来存储索引和数据，是一个单链表结构。
User Records 根据节点的不同，User Records 又分为四种不同类型：主键索引树叶子节点和非叶子节点，二级索引树叶子节点和非叶子节点。
有了页和 User Records 的认识，其实说叶子节点存的是页是一种笼统的回答，基于我的理解，我认为叶子节点（主键索引树叶子节点）存放的是行数据更为贴切。

回复 2：

B+树的叶子节点是 page（页），一个页里面可以存多个行。 B+树的结点跟 innoDB 的“页”都属于一种抽象逻辑概念。如果你要问“存”的是什么？我觉得回答行数据没毛病。因为存的不可能是“页”。这一逻辑概念，只能说这个叶结点大小等于 innoDB 里设置的页大小，或者说这个叶结点其实就是“页”。但存的是什么？那当然是数据，什么数据？当然是表中的行数据。

索引类型分为主键索引和非主键索引。

  • 主键索引也被称为聚簇索引（clustered index）
  • 非主键索引也被称为二级索引（secondary index）

基于非主键索引的查询需要多扫描一棵索引树，也就是主键索引树，也就是回表操作。

自增主键的意义：

  • 性能方面：每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂（页分裂）。
  • 存储空间方面：主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

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

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

即典型的 KV 场景。

非聚集索引上为啥叶子节点的 value 为什么不是地址，这样可以直接定位到整条数据，而不用再次对整棵树进行查询？

这个叫作“堆组织表”，MyISAM 就是这样的，各有利弊。你想一下如果修改了数据的位置的情况，InnoDB 这种模式是不是就方便些。主键索引页分裂的场景，就可能会导致主键记录的地址发生变化，这时候需要更新每一个索引上面对主键记录地址的引用。

05 | 深入浅出索引（下）

覆盖索引：在查询里索引已经覆盖了查询需要的列。覆盖索引可以减少树的搜索次数，显著提升查询性能，是一个常用的性能优化手段。

select * from T where k between 3 and 5;

表中 k 的值是：1，2，3，5，6，7。引擎内部使用覆盖索引在索引 k 上其实读了三个记录（第一次 3，第二次 3 的下一个 5，第三次 5 的下一个 6 不满足 ），但是对于 MySQL 的 Server 层来说，它只是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。这就是引擎实际扫描条数不等于 MySQL explain 语句中的 rows 字段的原因。

最左前缀原则：B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

如果需要常查人的名字与年龄。可以建立索引 (name,age) 与 (age)，而不是 (age,name) 与 (age)，考虑的原则就是空间。

索引下推优化（index condition pushdown)：

联合索引（name,age）为例，名字第一个字是张，而且年龄是 10 岁的所有男孩：

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

这个语句在搜索索引树的时候，只能用 “张”。

  • 在 MySQL 5.6 之前，只能从符合 ‘张%’ 开始一个个回表。到主键索引上找出数据行，再对比字段值。
  • 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
image

InnoDB 在（name,age）索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。

联合索引中是按索引列的顺序，来排序的。（name,age）先排 name 列的值，再排 age 列的值。

在满足语句需求的情况下，尽量少地访问资源是数据库设计的重要原则之一。

06 | 全局锁和表锁：给表加个字段怎么有这么多阻碍？

全局锁：

-- 加全局读锁 FTWRL，整个库处于只读状态；做全库逻辑备份
FLUSH TABLES WITH READ LOCK;

-- 插入一行数据

SHOW PROCESSLIST;
-- Waiting for global read lock

-- 释放锁
UNLOCK TABLES;

但是让整库都只读，听上去就很危险：

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

在可重复读隔离级别下开启一个事务，来确保拿到一致性视图。mysqldump 使用参数 -single-transaction 就是如此执行的。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

有了可重复读的事务隔离级别却还需要 FTWRL 的原因是：引擎不都支持这个事务隔离级别。

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

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

表级锁：

第一类表锁：lock-tables | mysql

LOCK TABLES T1 READ, T2 WRITE;
-- 其他线程 写 T1、读写 T2 的语句都会被阻塞
-- 当前线程也只能执行 读 T1、读写 T2 的操作，其他表都不能访问

-- 当前线程读 T2
-- Waiting for table metadata lock

-- 当前线程读 T3
-- 1100 - Table 'T3' was not locked with LOCK TABLES
-- 查看表锁
SHOW OPEN TABLES WHERE In_use > 0;

第二类 MDL（metadata lock)：

MDL 不需要显式使用：

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

给一个小表加个字段，导致整个库挂了：

-- session1
SET autocommit = 0;
-- 关闭自动提交

BEGIN;
-- 开启使用

SELECT * FROM T;
-- 查询 T 表，一切 ok
-- 获取 MDL 读锁 ok，因为在事务中，查询结束后没有释放读锁。

-- 事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

SELECT * FROM information_schema.innodb_trx;
-- 查询事务有个 RUNNING
-- session2
SELECT * FROM T;
-- 查询 T 表，一切 ok
-- 获取 MDL 读锁 ok，查询结束后释放读锁。
-- session3
ALTER TABLE `T` ADD COLUMN `c2` varchar(255) NULL;
-- 修改字段，卡住
-- blocked 原因：是因为 session1 的 MDL 读锁还没有释放，而 session3 需要 MDL 写锁，因此只能被阻塞。

SHOW OPEN TABLES WHERE In_use > 0;
-- In_use 1
-- session4
SELECT * FROM T;
-- 查询 T 表，卡住
-- blocked 原因：对表的增删改查操作都需要先申请 MDL 读锁，都被 session3 阻塞。
image

这里自己想了一个问题：session4 卡住后，session1 COMMIT 后，session4 的 SELECT 执行结果是否有 session3 的新列呢？

从结果上看是没有的。开始以为是事务级别，但是调整为 READ UNCOMMITTED，session4 仍然没有新列。后来了解到 online DDL 意识到应该有关系。测试后得出 session3 在获取写锁后，在做 DDL 前会释放写锁加读锁，这时 session4 就可以执行了，但是这时 DDL 还没有执行，所以 session4 的执行结果没有新列。

如果 session3 执行语句是：

ALTER TABLE `T` ADD COLUMN `c2` varchar(255) NULL, LOCK=EXCLUSIVE;
-- NONE：允许并发查询和DML操作；
-- SHARED：允许并发查询，但不允许DML操作；
-- DEFAULT：允许尽可能多的并发查询或DML操作(或两者都允许)，没指定LOCK选项默认就为DEFAULT；
-- EXCLUSIVE：不允许并发查询和DML操作。

EXCLUSIVE 时写锁会一直持有，一直等到 DDL 完毕 session4 才开始执行，所以 session4 的执行结果有新列。

衍生问题：session3 如果没有 LOCK=EXCLUSIVE，session4 查询前 BEGIN; 开启事务那结果是如何呢？实验结果：session1 提交后，session3 session4 依然阻塞。

衍生问题：session3 添加 LOCK=EXCLUSIVE，session4 查询前 BEGIN; 开启事务那结果是如何呢？实验结果：ssession1 提交后，session3 session4 正常结束。

分析猜测 session3 阻塞的原因是：session1 提交后释放了读锁，session3 session4 拿到读锁，session3 DDL 操作完成后又要拿写锁，但是 session4 的读锁未释放，所以 session3 阻塞。那 session4 为啥阻塞呢？session4 可以获取读锁就应该可以执行完毕，遗留问题。

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

  • 解决长事务，事务不提交，就会一直占着 MDL 锁。
  • 在 ALTER TABLE 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

实测：MySQL 5.7.30 不支持。腾讯云 MySQL 5.7.18-txsql-log 支持。

07 | 行锁功过：怎么减少行锁对性能的影响？

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

举例：

  1. 从顾客 A 账户余额中扣除电影票价；UPDATE
  2. 给影院 B 的账户余额增加这张电影票价；UPDATE 最容易发送锁等待的地方
  3. 记录一条交易日志。INSERT

以 3、1、2 顺序执行可以最大程度地减少了事务之间的锁等待，提升了并发度。

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

image

上图 session1 在等 session2 id=2 的锁，session2 在等 session1 id=1 的锁，进入死锁状态。

-- 所等待超时时间 s
SELECT @@innodb_lock_wait_timeout;
-- 7200 2h

-- 死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务
SELECT @@innodb_deadlock_detect;
-- 1213 - Deadlock found when trying to get lock; try restarting transaction

主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的：

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n)的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。这期间要消耗大量的 CPU 资源。即：热点行更新导致的性能问题。

可以通过尝试将热点数据拆分多行进行处理，提高并发处理。

08 | 事务到底是隔离的还是不隔离的？

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

START TRANSACTION WITH CONSISTENT SNAPSHOT;

有两个“视图”的概念：

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

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。