学习笔记《MySql实战45讲》

如何写出高性能的 SQL 语句一直是后端开发需要关注的问题，Mysql 实战 45 讲详细介绍了 SQL 执行过程、数据组织方式、事务隔离等核心特性，同时结合答疑篇对具体案例进行详细剖析 如 mysql 如何保证数据完整性、为什么会选错索引等等。

TOC

mysql 分层

server 层和存储引擎层。server 层负责连接，权限校验，词法语法分析等；存储引擎负责数据的存储和提取。 一条查询和更新 SQL 流程如下:

WAL(Write-Ahead Logging 预写日志系统)

• 指 MySQL 在执行写操作的时候并不是立刻更新到磁盘上，而是先记录在日志中，之后在合适的时间更新到磁盘中。日志主要分为 undo log、redo log、binlog。
• 两阶段提交，先写 redolog，后写 binlog 再提交，保证日志一致性(考虑反证法，确认是否能实现恢复任意时刻数据)。
• 日志都是顺序写，比直接写随机写磁盘更快

Redo log(innodb 特有,保证了 Crash-Safe)

• 记录物理日志，eg:在某个数据页修改了 xx
• 固定大小，循环写，分 write pos(记录当前位置)和 check point(要擦除的位置)

Binlog(归档日志,server 层负责)

• 记录逻辑日志,eg:给 ID=2 这行 c 字段+1
• 追加写，不会覆盖之前文件
• 日志格式 STATMENT(空间小，可能数据不一致)和 ROW(做数据服务常用,占用空间大)

索引

• 按叶子节点内容分主键索引(聚簇索引，存储整行数据)和非主键索引(二级索引，存储主键 id)
• 非主键索引使用，可能会回表(再查主键索引)
• B+树存储索引，左右有序，如果不是有序插入会导致页分裂(需要移动数据)，影响插入数据性能
• 主键大小也会影响二级索引大小
• 最左匹配和索引下推
  • 联合索引，按索引字段顺序去匹配过滤
  • mysql5.6 引入，可以再索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数（如下图二，否则 ID3,ID6 也需要回表）
• 索引失效
  • 条件字段有函数操作，包含 id+1=10 这种，破坏了索引值的有序性
  • 隐式类型转换，规则:字符串和数字做比较的话，是将字符串转换成数字
  • 隐式字符编码转换，如 utf8 和 utf8mb4 的两个字段做 join

锁(按加锁范围分)

• 全局锁
  • 全局读锁Flush tables with read lock (FTWRL)，整库处于只读状态，其他操作被阻塞，常用来做全库逻辑备份
  • 支持事务引擎，可以使用 msyqldump 开启–single-transaction开始事务，确保一致性视图
• 表级锁
  • 表锁
    • lock tables … read/write和unlock tables配合使用
  • 元数据锁(meta data lock,MDL)
    • 不需要显示使用，在访问表时自动加上，保证读写的正确性
    • mysql5.5 引入，对表增删改查走 MDL 读锁；变更表结构走 MDL 写锁
    • 安全的给小表加字段?
      • 首先解决长事务，事务不提交会一直占用 MDL 锁，information_schema.innodb_trx 表可查看执行事务
      • ALTER TABLE 设定等待时间

ALTER TABLE tbl_name NOWAIT add column ...;
ALTER TABLE tbl_name WAIT N add column ...;

• 行锁(引擎层实现,innodb)
  • 在需要时加锁，等事务结束后释放(两阶段锁协议)
  • 如果事务需要加锁，把最有可能冲突、最可能影响并发度的往后放
  • 死锁: 当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态
  • 死锁检测:等待超时和主动检查
    • innodb_lock_wait_timeout 锁超时时间，默认 50s
    • innodb_deadlock_detect 发起死锁检测，主动回滚死锁中某一个事务(特别耗 CPU)

事务隔离(一致性读)

• 可重复隔离级别(RR)，事务启动会创建一个 read-view

• begin/start transaction 命令并不是一个事务的起点，执行他们后第一个操作 innodb 表语句时，事务才启动

• start transaction with consistent snapshot 语句马上启动事务

• 一致性读视图(consistent read view)，用于支持 RC(读提交)和 RR 隔离级别

  • 每个事务有唯一 ID，transaction id(向事务系统申请，严格递增)
  • 每行数据有多个版本,每次事务更新都会生成一个新数据版本，记 row trx_id(事务 id)，如图 1
  • innodb 为每个事务构建一个数组，保存这个事务启动瞬间，当前正在活跃(启动但未提交)的所有事务 ID，图 2 即数据版本可见性，低高水位分别为事务数组里最小最大 id

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

  • 下图 2 事务 A 的查询数据逻辑，图 3 事务 B 更新逻辑
  • 详细过程分析(https://time.geekbang.org/column/article/70562)
    • 版本未提交，不可见；
    • 版本已提交，但是是在视图创建后提交的，不可见；
    • 版本已提交，而且是在视图创建前提交的，可见。
  • 事务 B: - 更新数据都是先读后写，这个读(系统内读)只能读当前值，当前读(current read) - 因此事务 B 拿到的是(1,2) 的数据更新后为(1,3) - 除了 update 语句外，select 语句如果加锁，也是当前读。

Change buffer(提升更新性能，会定期 merge 和读 merge)

• 只有普通索引能使用，唯一索引不能使用 change buffer，因为每次都要判断数据唯一性
• 适用于读多写少场景，如果写后立即读，性能反而会降低(需要 merge)
• 图 1 带 change buffer 更新过程：page1 在内存中直接更新，page2 不在内存中，因此在 change buffer 中记录操作逻辑，两个操作都记录 redo log(2 会多个 new change buffer item)，一切都是顺序写
• 图 2 带 change buffer 读过程

count(*)实现

• myisam 将总行数存储起来，使用时直接返回
• innodb 有 mvcc，应该返回多少行不固定，每次都是扫描所有行
• Innodb mysql 优化器会使用最小的树遍历(主键宝行全部数据，普通索引比主键索引小)
• 通过 show table status 里 table_rows 近似获取(准确度不高，误差可达 50%)
• 查询效率 count(*) ≈count(1) >count(id) >count(字段) （count(字段)会过滤 null）

order by

(Extra 里 using filesort 表示需要排序，使用线程内存(sort_buffer)排序)

• 图 1 全字段排序，会使用临时空间(sort_buffer)，超过大小使用磁盘空间进行外部排序(使用归并算法)
  • 原表只读一次，剩下操作在 sort_buffer 和临时文件里完成
• 图 2 rowid 排序，当行长度过长，超过 max_length_for_sort_data 启用，多一次主键访问
• orderby 字段里有索引，则只有图 1 回表操作，如果是覆盖索引则回表都不需要(Extra 里 using index 就是使用了覆盖索引)

Join

应小表驱动大表，但驱动表和被驱动表关系不一定和声明顺序一致

• 图 1：Index Nested-Loop Join(NLJ)，遍历表 t1 去除每行数据中 a 值，从表 t2 中查找满足条件的记录，索引嵌套查询，查询复杂度 N + N2log2M N 驱动表行数，M 被驱动表行数，查找有索引的单行效率近似为(Log2M)
• 图 2: Block Nested-Loop Join(BNL)，无索引场景，驱动表大小超过 join_buffer 会有多次匹配，其中扫描行数 N+aNM ，内存判断 N*M 其中 a 为分段次数，因此调整 join_buffer_size 可影响性能
  • BNL 在冷数据表大量读取时，会导致内存数据也频繁换出，IO 增加,Buffer Pool 命中率降低
• 选择
  • 使用 NLJ，可以走 join
  • 使用 BNL，扫描行数过多，尤其大表，尽量不用 join(可以通过 Extra 里是否有 Block Nested Loop 来判断)
  • 使用 BNL 且 Join_buffer 足够大，谁驱动谁都一样，否则应小表驱动大表
  • 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
• 图 3: Batched Key Access(BKA)，对 NLJ 优化(mysql 5.6 以上)，Extra 里有 using mrr

总结

上面总结了 Mysql 45 讲里数据完备性保证、索引失效、锁、Join 过程等的实现和注意事项。 举一个常见的 join 查询来回顾总结下：通常不使用 join 是我们觉得他性能差，但是通过上面 join 实现方式我们可以知道，如果 join 带索引，那么就会走 NLJ 查询，性能比两次查询还要快。而如果带索引的 join 出现慢查时，就需要去查看字段类型、字符集是否一致 导致索引失效。当然如果没有索引的场景，就不建议走 join 连表查询了，因为会走到 BNL 查询。

推荐章节 介绍什么场景不适用读写分离：

• 28 | 读写分离有哪些坑？-极客时间 介绍 Mysql 返回数据方式：
• 33 | 我查这么多数据，会不会把数据库内存打爆？-极客时间 Join 优化思路：BKA>NLJ>BNL
• 35 | join 语句怎么优化？-极客时间 两个高质量问答解疑：
• 15 | 答疑文章（一）：日志和索引相关问题-极客时间
• 44 | 答疑文章（三）：说一说这些好问题-极客时间