MySQL实战45讲

经过经典的 TCP 握手后，认证身份建立连接，建立连接后即使通过管理员修改用户权限，连接也不会断开，会在下一次连接生效。 建立连接后长时间没有操作，连接就会自动断开，默认 8 小时（参数 wait_timeout ）

• 长连接：客户端持续有请求，一直使用一个连接
• 短连接：每次执行完几次操作后断开，下一次重新建立

连接的建立比较复杂，应尽量减少建立连接的动作，也就是使用长连接。但是 MySQL 在执行过程中临时使用的内存是管理在连接对象中的，在连接断开后， 资源才会释放，所以要定期断开长连接，或者在执行比较大的操作后通过 mysql_reset_connection 重置连接，这个过程不需要重连和重新做权限验证， 但是会将连接恢复到刚刚创创建完时的状态。

建立连接后，执行语句前会先查询缓存，检查相同语句是否执行过，命中缓存则直接返回，否则继续执行。查询缓存失效非常频繁，表的任何 更新都会导致缓存失效，所以一般不使用，MySQL 8.0 后，不再支持查询缓存。

MySQL 通过关键字将 SQL 语句解析，生成一课对应的“解析树”， MySQL 解析器 使用 MySQL 语法规则，验证和解析查询，比如关键字是否正确、 关键字顺序、前后匹配等。 预处理器 通过 MySQL 语法规则进一步检查语法树是否正确，比如表和数据列是否存在，名字和别名是否有歧义。

语法树合法后，会交由优化器转换为执行计划，一条查询有多种执行方式，结果都相同，但可能性能不一，比如索引的选择等，优化器就是要制定成本最低的执行计划。

执行器根据优化器生成的执行计划调用存储引擎接口执行，执行计划是一个数据结构，并非字节码，MySQL 只是简单的根据执行计划逐步执行指令。

如果每次更新操作都是直接写磁盘，IO 成本和查找成本都很高，所以 MySQL 采用 WAL(Write-Ahead Logging) 技术先写日志，并在系统比较空闲时再写磁盘。

write pos 边写边后移，写到文件末尾后继续从头开始写； checkpoint 擦除记录，往后推移也是循环的，擦除之前需要更新数据到磁盘； checkpoint 追赶上 write pos 时，不能再执行新的更新操作，需要停下来擦除一些记录，因为 InnoDB redo log 是固定大小的。有了 redo log，InnoDB 就 可以在数据库异常重启时，保证数据不丢失，这个能力成为 crash-safe 。

MySQL server 层提供 binlog 功能，用以归档，没有 crash-safe 功能。

对比：

• redo log 是 InnoDB 引擎特有的，binlog 是 MySQL server 层提供的；
• redo log 是物理日志，记录了“在数据页做了什么修改”；binlog 是逻辑日志，有两种模式， statement 模式记录 sql 语句， row 模式记录行的内容，记两条，更新前和更新后。
• redo log 是循环写的，空间固定会用完；binlog 是追加写，文件写到一定大小后会切换下一个，不会覆盖之前的日志。
• redo log 和 binlog 通过事务 ID 关联。

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

关键逻辑在 两阶段提交 ，InnoDB 将新行写入到内存后，更新 redo log 为 prepare 状态，server 层写入 binlog，事务提交后更新 redo log 为 commit 状态。

1. redo log prepare 2. binlog 3. redo log commit

• 如果在 2 之前异常，恢复备份时没有 binlog，数据一致；
• 如果在 3 之前异常，恢复备份时虽然没有 commit，但是 prepare 和 binlog 完整，重启后自动 commit，数据一致。

复习用-思考：

• redo log 的概念是什么? 为什么会存在
• 什么是 WAL(Write-Ahead Logging)机制, 好处是什么
• redo log 为什么可以保证 crash safe 机制
• binlog 的概念是什么, 起到什么作用, 可以做 crash safe 吗?
• binlog 和 redolog 的不同点有哪些?
• 物理一致性和逻辑一直性各应该怎么理解?
• 执行器和 innoDB 在执行 update 语句时候的流程是什么样的?
• 如果数据库误操作, 如何执行数据恢复?
• 什么是两阶段提交, 为什么需要两阶段提交, 两阶段提交怎么保证数据库中两份日志间的逻辑一致性(什么叫逻辑一致性)?
• 如果不是两阶段提交, 先写 redo log 和先写 bin log 两种情况各会遇到什么问题

SQL 标准隔离级别： 读未提交（read uncommited） 、 读提交（read commited） 、 可重复读（repeatable read） 和 串行化（serializable） 。 在实现上，数据库会创建一个视图，访问的时候以视图的逻辑结果为准。

• 读未提交 ：一个事务还未提交，变更就可被其他事务看到 | 直接返回记录最新值，没有视图概念。
• 读提交 ：一个事务提交后，变更才能被其他事务看到 | 视图在 SQL 语句执行时创建。
• 可重复读 ：一个事务在执行过程中看到的数据与启动时一致 | 视图在事务启动时创建。
• 串行化 : 对同一行记录，读加“读锁”，写加“写锁”，读写冲突时，后访问的事务必须等待前一个结束 | 用加锁方式避免并行访问。

可通过上图，理解各个隔离级别下 V1 、 V2 、 V3 的值来加深理解。

MySQL 中每条更新记录都会记录一条回滚操作，记录上的最新值都可以通过回滚操作得到上一个值。不同时刻启动的事务会有不同的 read-view ， 同一条记录在系统中存在多个版本，就是数据库的多版本并发控制(MVCC)。没有比回滚日志更早的 read-view 时，系统会将日志删除。所以要尽量避免使用长事务， 长事务意味着存在很多老的视图，即使在事务提交后，对应的回滚日志也不会删除，浪费存储空间，同时长事务还会占用资源锁，可能会拖垮系统。

事务启动的方式：

• 显示启动： begin 或者 start transaction, 提交 commit=，回滚 =rollback 。
• set autocommit=1

查询持续时间超过 60s 的事务示例:

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started)) > 60;

MySQLLdb 或者 SQLAlchemy 默认 autocommit=0 。

commit work 是用来控制事务结束后的行为，是 chain 还是 release ，可以通过参数 completion_type 来控制，默认为 0(或者 NOCHAIN)， 等同于 commit 。 completion_type=1 时提交事务并自动开启下一个事务， completion_type=2 时等同于 commit and release ，提交事务并断开 连接。

复习用-思考：

• 事务的概念是什么?
• mysql 的事务隔离级别读未提交, 读已提交, 可重复读, 串行各是什么意思?
• 读已提交, 可重复读是怎么通过视图构建实现的?
• 可重复读的使用场景举例? 对账的时候应该很有用?
• 事务隔离是怎么通过 read-view(读视图)实现的?
• 并发版本控制(MCVV)的概念是什么, 是怎么实现的?
• 使用长事务的弊病? 为什么使用常事务可能拖垮整个库?
• 事务的启动方式有哪几种?
• commit work and chain 的语法是做什么用的?
• 怎么查询各个表中的长事务?
• 如何避免长事务的出现?

索引的作用是为了提高数据查询的效率，作用就像书的目录，更快地找到目标数据。

插入数据 insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

sql 语句 select * from T where k betwee 3 and 5 执行时会先定位到 k=3 的记录，拿到主键 ID，然后根据 ID 回到主键索引树搜索，这个过程称为 回表 。继续搜索后面满足条件的值，直至不满足条件。 如果 sql 语句变为 select ID from T where k between 3 and 5 ，那么就无需回表，因为 ID 值已经在 k 索引树上了，覆盖率查询需求，称为 覆盖索引 。覆盖索引可以减少树的搜索次数，优化查询性能，也是比较 常用的优化手段。

如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据。

用 (name, age) 作联合索引，索引项是按照索引定义出现的字段顺序排列的。 在查询 where name=“张三” 时，可以迅速定位到 ID4，并向后遍历；查询 where name like “张 %” 时，也可以定位到 ID3，并向后遍历。

最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符 。所以在建立联合索引时，要考虑索引的顺序，尽可能的做到复用最大化。

如果查询语句是 like 'hello%’and age >10 ，那么 age 索引不会生效，在 MySQL 5.6 之前会回表查询数据行再比对，在 MySQL 5.6 之后，引入了索引下推(index condition pushdown)，在索引遍历过程中对索引包含的字段 优先判断，直接过滤不符合条件的数据，减少回表次数。

尽量少地访问资源，减少资源消耗是数据库设计的重要原则。

全局锁的典型应用场景是*全库逻辑备份*，MySQL 命令是 Flush tables with read lock(FTWRL) 。 执行此命令后，整个库处于只读状态，数据的增删改及更新，表结构更新都会被阻塞。

整个库都只读听上去很危险：

• 从主库上备份，备份期间不能更新，业务会停摆
• 从从库备份，不能执行从主库同步过来的 =binlog=，导致主从不一致

但如果不加锁的话，备份系统备份得到库不是一个逻辑时间点，这个视图是逻辑不一致的。可以在 可重复读 隔离级别下开启一个事务，来确保拿到一致性视图。 官方逻辑备份工具 mysqldump 使用参数 --single-transaction 支持这种操作，但需要引擎支持这个隔离级别，像 MyISAM 这种不支持事务的，就只能使用 FTWRL 了。

为什么不使用 set global readonly=true 的方式呢？

• 有些系统 readonly 用来做其他逻辑，如判断是主库还是备库，修改 global 影响太大
• 在异常处理机制上有差异，执行 FTWRL 命令之后，客户端异常断开后，MySQL 会自动释放全局锁，回到可更新状态；而 readonly 方式不会释放，风险较高；
• 还有个情况在 slave 上 如果用户有超级权限的话 readonly 是失效的

表级锁有两种：表锁和元数据锁(meta data lock, MDL)。

表锁的语法是 lock tables … read/writ ，lock tables 语法除了限制本其他线程的读写外，也限定了本线程的操作对象。

另一个表级的锁是 MDL，MDL 的作用是防止 DDL 和 DML 并发冲突，不需要显示调用。对一个表增删改查时，加 MDL 读锁；更改表结构时加 MDL 写锁。

• 读锁直接不互斥，多个线程可以对同一个表增删改查
• 读写锁、写锁之间互斥，保证变更表结构的安全性

系统默认加 MDL 锁的机制很重要，下面是给小表加字段，导致整个库挂了的案例。

session A 启动，加 MDL 读锁，session B 也需要读锁，正常运行。session C 会被 blocked，因为 A 的读锁还未释放，而 C 需要写锁，后面的 D 也会阻塞。 如果表上查询频繁就会导致线程数爆满，导致整个库挂掉。

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

• 事务不提交，就会一直占着 MDL 锁， 查询 MySQL 的 information_schema 库的 innodb_trx ，找到长事务，如果有，暂停 DDL 或者 kill 掉长事务。
• 热点表不适合 kill 的情况下，设定等待时间 NOWAIT/WAIT n 。

备份一般都会在备库上执行，在用–single-transaction+方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

Q4 拿表结构，Q5 导数据， Q6 释放 t1 的 MDL 锁。

• Q4 之前到达，没影响，备份拿到 DDL 后的表结构
• 在 时刻2 到达，报 Table definition has changed, please retry transaction ，mysqldump 终止
• 在 时刻2 和 时刻3 之间达到，mysqldump 占着 t1 的 MDL 读锁，binlog 被堵塞，主从延迟，直到 Q6 完成
• 从 时刻4 开始，mysqldump 释放了 MDL 读锁，没有影响，拿到 DDL 之前的表结构