UP | HOME

MySQL实战45讲

Table of Contents

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

mysql-architecture.png

Mysql 大体分为 Server 层和 存储引擎 两部分,Server层分为连接器、分析器、优化器和执行器, 存储引擎是插件式的,现在最常用的存储引擎是 InnoDB。

连接器

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

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

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

查询缓存

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

分析器

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

优化器

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

执行器

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

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

redo log

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

WAL.jpg

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

binlog

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

对比:

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

update 语句执行流程

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

update.png

关键逻辑在 两阶段提交 ,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两种情况各会遇到什么问题

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

隔离级别

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

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

session.png

可通过上图,理解各个隔离级别下 V1V2V3 的值来加深理解。

事务隔离的实现

read-view.png

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), 等同于 commitcompletion_type=1 时提交事务并自动开启下一个事务, completion_type=2 时等同于 commit and release ,提交事务并断开 连接。

复习用-思考:

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

04 | 深入浅出索引(上)

索引

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

常见的索引模型

  • 哈希表key-value 形式存储数据结构,使用哈希函数把 Key 换算成一个具体的位置,将 value 放到此位置上。 多个 key 会出现计算后值一样的情况,使用链表来处理这种情况。查询时首先更加 key 计算出位置,然后遍历链表顺序查找目标数据。链表数据是向后追加,所以是无序的,所以做区间查询就会很慢。 所以, 哈希表适用于等值查询 , 如:Memcached 及其他一些 NoSQL 引擎。
  • 有序数组 有序数组在 等值查询范围查询 中性能都非常优秀,可以充分利用二分法。但是要维护数据的有序,在新增数据时,数据挪动成本较高。 所以, 有序数组适用于静态存储引擎
  • 二叉搜索树 二叉搜索树的特点是:每个节点的左儿子小于父节点,父节点又小于右儿子。为了维持 O(log(N)) 的查询复杂度,需要保持这棵树是平衡二叉树,更新时间复杂度也为 O(log(N)) ,数据库存储实际上使用的不是二叉树,因为索引不仅存在内存中,还有写到磁盘上,如果平衡二叉树树高过高,从磁盘中读取数据次数过多,就会导致查询变慢,所以要使用 N 叉树 ,N 叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了, N 叉树 中的 N 取决于数据块的大小。

InnoDB 索引模型

InnoDB 使用了 B+ 树索引模型,每一个索引对应一棵 B+ 树。

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

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

4.1.png

  • 主键索引,叶子节点存储的是整行数据,也叫做 聚簇索引(clustered index)
  • 非主键索引,叶子节点存储的是主键的值,也叫做 二级索引(secondary index)

区别:

  • select * from T where ID=500 ,主键查询只搜索主键这课 B+ 树。
  • select * from T where k=500 , 先搜索 k 索引树,拿到主键,在搜索主键索引树。

B+ 树为了维护有序性,在数据插入、删除时,可能会涉及到磁盘数据页的分裂或合并,所以一般会创建自增主键,这样每次插入都是追加数据,不会触发叶子节点分裂。 主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也越小。从性能和存储空间方面考量,自增主键往往是更合理的选择。在 Key-Value 的场景下,只有一个索引且是唯一索引,则适合直接使用业务字段作为主键索引。

05 | 深入浅出索引(下)

覆盖索引

插入数据 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磁盘读写读取正行数据。

最左前缀原则

4.5.png

(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),在索引遍历过程中对索引包含的字段 优先判断,直接过滤不符合条件的数据,减少回表次数。

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

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

数据库锁的设计主要是为了处理并发问题,MySQL 的锁大致分为三类:全局锁、表级锁和行锁。

全局锁

全局锁的典型应用场景是*全库逻辑备份*,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 锁的机制很重要,下面是给小表加字段,导致整个库挂了的案例。

6.1.png

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 之前的表结构