MySQL 实战 45 讲笔记

[EruDev]

基础架构

• 连接器：负责跟客户端建立连接、获取权限、维持和管理连接；
• 查询缓存：查询请求先访问缓存（key 是查询的语句，value 是查询的结果），命中直接返回。不推荐使用缓存，更新会把缓存清除（关闭缓存：参数 query_cache_type 设置为 DEMAND）；
• 分析器：对 SQL 语句解析，判断 sql 是否正确；
• 优化器：决定使用哪个索引，多表（join）的时候，决定各表的连接顺序；
• 执行器：执行语句，先判断用户有无权限，使用表定义的存储引擎。

[EruDev]

日志系统

redo log (重做日志)，binlog（归档日志）

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

[EruDev]

事务

• 原子性 Atomicity
• 一致性 Consistency
• 隔离性 Isolation
• 持久性 Durability

事务隔离级别：

• 读未提交 read uncommitted
• 读已提交 read committed
• 可重复读 repeatable read
• 串行化 serializable

事务隔离的实现

• 每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中存在多个版本，这就是数据库的多并发版本控制（MVCC）。
• 回滚日志会在系统判断当前没有事务用到这些回滚日志的时候删除。

尽量不要使用长事务

长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致占用大量存储空间。除此之外，长事务还会占用锁资源，可能会拖垮库。

[EruDev]

索引

索引模型

• 哈希
  • 等值查询
• 有序数组
  • 静态存储引擎。如 2020 年全国人口。按顺序存储，查询使用二分查找，O(log(N))
• 搜索树（N叉树）
  • 查询、更新时间复杂度 O(log(N))

索引类型

• 主键索引
  • 叶子节点存的是正行数据（聚簇索引）
• 非主键索引
  • 叶子节点存储的是主键的值（二级索引）

区别：主键索引只要搜索 id 这个 B+ tree 即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次（回表）。

回表：通过主键搜索的过程，称为回表。

覆盖索引

某索引已经覆盖了查询需求，称为覆盖索引。比如：select ID from T where k between 3 and 5。
在引擎内部使用覆盖索引在索引 K 上其实读了三条记录，R3 ~ R5 (对应的索引 k 上的记录项)，但对于 MySQL 的 Server层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。

最左前缀

• B+ tree 这种索引结构，可以利用最左前缀来定位记录；
• 只要满足最左前缀，就可以利用索引来加速检索；
• 最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

索引下推

• 在 MySQL5.6 之前，只能从根据最左前缀查询到 ID 开始一个个回表。到主键索引上找出数据行，再对比字段值。
• MySQL5.6 引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

无索引下推执行流程

有索引下推执行流程

[EruDev]

锁

全局锁

FLUSH tables with lock(FTWRL)

整个库处于只读状态，一般用于备份

用可重复读事务隔离级别代替

mysqldump -single-transaction

不使用

set global readonly = true

表锁

lock tables rad/write

unlokc tables

MDL(metadata lock)
对表做增删改时 加 mdl 读锁 对表结构变动时 加 mdl 写锁

读写 和 写之间互斥

給表加字段索引 会扫描全表

注意 对表结构做变更 可能导致会话拿不到读锁 卡死 客户端超时打爆数据库

解决方法

ALTER TABLE xxx WAIT N add colum
ALTER TABLE xxx NOWAIT add colum