《MySQL 实战 45 讲》笔记二

极客时间教程 - MySQL 实战 45 讲 学习笔记

16 order by 是怎么工作的？

用 explain 命令查看执行计划时，Extra 这个字段中的“Using filesort”表示的就是需要排序。

全字段排序

select city,name,age from t where city='杭州' order by name limit 1000;

这个语句执行流程如下所示 ：

执行流程：

• 初始化 sort_buffer，确定放入需要排序的字段（如 name、city、age）。
• 从索引中找到满足条件的记录，取出对应的字段值存入 sort_buffer。
• 对 sort_buffer 中的数据按照排序字段进行排序。
• 返回排序后的结果。

内存与磁盘排序：

• 如果排序数据量小于 sort_buffer_size，排序在内存中完成。
• 如果数据量过大，MySQL 会使用临时文件进行外部排序（归并排序）。MySQL 将需要排序的数据分成 N 份，每一份单独排序后存在这些临时文件中。然后把这 N 个有序文件再合并成一个有序的大文件。

优化器追踪：通过 OPTIMIZER_TRACE 可以查看排序过程中是否使用了临时文件（number_of_tmp_files）。

rowid 排序

• 执行流程：
  • 当单行数据过大时，MySQL 会采用 rowid 排序，只将排序字段（如 name）和主键 id 放入 sort_buffer。
  • 排序完成后，根据 id 回表查询其他字段（如 city、age）。
• 性能影响：rowid 排序减少了 sort_buffer 的内存占用，但增加了回表操作，导致更多的磁盘 I/O。

全字段排序 VS rowid 排序

• 内存优先：
  • 如果内存足够大，MySQL 优先使用全字段排序，以减少磁盘访问。
  • 只有在内存不足时，才会使用 rowid 排序。
• 设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

并不是所有的 order by 语句，都需要排序操作的。MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。如果查询的字段和排序字段可以通过联合索引覆盖，MySQL 可以直接利用索引的有序性，避免排序操作。

17 如何正确地显示随机消息？

ORDER BY RAND() 的执行流程

• 使用 ORDER BY RAND() 时，MySQL 会创建一个临时表，并为每一行生成一个随机数，然后对临时表进行排序。
• 排序过程可能使用内存临时表或磁盘临时表，具体取决于数据量和 tmp_table_size 的设置。

ORDER BY RAND() 的性能问题：ORDER BY RAND() 需要扫描全表并生成随机数，排序过程消耗大量资源，尤其是在数据量大时，性能较差。

内存临时表与磁盘临时表

内存临时表：当临时表大小小于 tmp_table_size 时，MySQL 使用内存临时表，排序过程使用 rowid 排序算法。

磁盘临时表：当临时表大小超过 tmp_table_size 时，MySQL 会使用磁盘临时表，排序过程使用归并排序算法。

优先队列排序：当只需要返回少量数据（如 LIMIT 3）时，MySQL 5.6 引入了优先队列排序算法，避免对整个数据集进行排序，减少计算量。

随机排序的优化方法

• 随机算法 1：通过 max(id) 和 min(id) 生成随机数，然后使用 LIMIT 获取随机行。问题是：ID 不连续时，某些行的概率不均匀。

• 随机算法 2：先获取表的总行数 C，然后生成随机数 Y，使用 LIMIT Y, 1 获取随机行。优点：解决了概率不均匀的问题，但需要扫描 C + Y + 1 行。

• 随机算法 3：扩展随机算法 2，生成多个随机数 Y1, Y2, Y3，分别使用 LIMIT Y, 1 获取多行随机数据。优点：适用于需要返回多行随机数据的场景。

  总结

• 避免使用 ORDER BY RAND()：ORDER BY RAND() 的性能较差，尤其是在数据量大时，应尽量避免使用。

• 应用层处理随机逻辑：将随机逻辑放在应用层处理，数据库只负责数据读取，减少数据库的计算压力。

• 优化扫描行数：通过合理的随机算法，减少扫描行数，提升查询性能。

18 为什么这些 SQL 语句逻辑相同，性能却差异巨大？

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

案例一：条件字段函数操作

• 问题：在 WHERE 条件中对索引字段使用函数（如 month(t_modified)），会导致 MySQL 无法使用索引的快速定位功能，转而进行全索引扫描。
• 原因：对索引字段进行函数操作会破坏索引值的有序性，优化器会放弃树搜索功能，转而进行全索引扫描。
• 解决方案：避免在索引字段上使用函数操作，改为基于字段本身的范围查询。例如，将 month(t_modified)=7 改为 t_modified 的范围查询。

案例二：隐式类型转换

• 问题：当查询条件中的字段类型与索引字段类型不一致时（如 varchar 和 int），MySQL 会进行隐式类型转换，导致无法使用索引。
• 原因：隐式类型转换相当于对索引字段进行了函数操作（如 CAST），优化器会放弃树搜索功能，转而进行全表扫描。
• 解决方案：确保查询条件中的字段类型与索引字段类型一致，避免隐式类型转换。

案例三：隐式字符编码转换

• 问题：当两个表的字符集不同时（如 utf8 和 utf8mb4），在进行表连接查询时，MySQL 会对被驱动表的索引字段进行字符集转换，导致无法使用索引。
• 原因：字符集转换相当于对索引字段进行了函数操作（如 CONVERT），优化器会放弃树搜索功能，转而进行全表扫描。
• 解决方案：
  • 统一字符集：将两个表的字符集统一为 utf8mb4，避免字符集转换。
  • 手动转换：在 SQL 语句中手动进行字符集转换，确保转换操作发生在驱动表上，而不是被驱动表的索引字段上。

19 为什么我只查一行的语句，也执行这么慢？

查询长时间不返回的可能原因

• 等 MDL 锁：当查询需要获取表的 MDL 读锁，而其他线程持有 MDL 写锁时，查询会被阻塞。
  • 解决方案：通过 sys.schema_table_lock_waits 表找到持有 MDL 写锁的线程，并 KILL 掉该线程。
• 等 flush：当有线程正在对表执行 flush tables 操作时，其他查询会被阻塞。
  • 解决方案：找到阻塞 flush 操作的线程并 KILL 掉。
• 等行锁：当查询需要获取某行的读锁，而其他事务持有该行的写锁时，查询会被阻塞。
  • 解决方案：通过 sys.innodb_lock_waits 表找到持有写锁的线程，并 KILL 掉该连接。

查询慢的可能原因

• 全表扫描：当查询条件中的字段没有索引时，MySQL 会进行全表扫描，导致查询缓慢。
  • 解决方案：为查询条件中的字段添加索引。
• 一致性读与当前读：
  • 一致性读：当查询使用一致性读时，如果该行有大量 undo log（如被频繁更新），MySQL 需要依次执行这些 undo log 才能返回结果，导致查询缓慢。
  • 当前读：使用 lock in share mode 或 for update 进行当前读时，MySQL 会直接读取最新的数据，因此速度较快。
  • 解决方案：理解一致性读和当前读的区别，根据业务需求选择合适的查询方式。

20 幻读是什么，幻读有什么问题？

幻读的定义

• 幻读指的是一个事务在前后两次查询同一个范围时，后一次查询看到了前一次查询没有看到的行。
• 幻读仅在“当前读”（如select ... for update）时出现，普通的快照读不会出现幻读。

幻读的问题

• 语义问题：事务 A 声明要锁住所有满足条件的行，但由于幻读的存在，其他事务可以插入或修改这些行，破坏了事务 A 的加锁声明。
• 数据一致性问题：幻读可能导致数据和日志在逻辑上不一致，尤其是在使用 binlog 进行数据同步或恢复时，可能会导致数据不一致。

幻读的解决方案

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

• 间隙锁（Gap Lock）：为了解决幻读问题，InnoDB 引入了间隙锁。间隙锁锁住的是索引记录之间的间隙，防止新记录的插入。
• Next-Key Lock：间隙锁和行锁合称 Next-Key Lock，它锁住的是一个前开后闭的区间，确保在锁定范围内无法插入新记录。

间隙锁的影响：

• 间隙锁虽然解决了幻读问题，但也带来了并发度下降和死锁的风险。特别是在高并发场景下，间隙锁可能会导致更多的锁冲突和死锁。

隔离级别的选择

• 在可重复读隔离级别下，间隙锁生效，可以有效防止幻读。
• 在读提交隔离级别下，间隙锁不生效，幻读问题可能会出现，但可以通过将 binlog 格式设置为row来解决数据一致性问题。

实际应用中的考虑

• 业务开发人员在设计表结构和 SQL 语句时，不仅要考虑行锁，还要考虑间隙锁的影响，避免因间隙锁导致的死锁问题。
• 隔离级别的选择应根据业务需求来决定，如果业务不需要可重复读的保证，读提交隔离级别可能是一个更合适的选择。

21 为什么我只改一行的语句，锁这么多？

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

1. 原则 1：加锁的基本单位是 Next-Key Lock，即前开后闭区间。
2. 原则 2：查找过程中访问到的对象才会加锁。
3. 优化 1：索引上的等值查询，给唯一索引加锁时，Next-Key Lock 退化为行锁。
4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，Next-Key Lock 退化为间隙锁。
5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

锁的范围与隔离级别：

• 在可重复读隔离级别下，Next-Key Lock 和间隙锁生效，防止幻读。
• 在读提交隔离级别下，间隙锁不生效，锁的范围更小，锁的时间更短。

22 MySQL 有哪些“饮鸩止渴”提高性能的方法？

短连接风暴

• 问题：高峰期连接数暴涨，超过 max_connections 限制
• 解决：
  • 主动断开空闲连接（优先断开事务外空闲的）
  • --skip-grant-tables 重启跳过权限验证（风险极高）

慢查询性能问题

• 索引没设计好：紧急创建索引（先在备库执行，再主备切换）
• SQL 没写好：改写 SQL（MySQL 5.7 可用 query_rewrite）
• 选错索引：force index 强制使用正确索引
• 预防：上线前通过慢查询日志和回归测试发现

QPS 突增问题

• 业务高峰或应用 bug 导致 QPS 暴涨
• 解决：下掉新功能或重写 SQL 为 select 1（风险高，作为最后手段）

23 MySQL 是怎么保证数据不丢的

binlog 写入机制：

• 事务执行时先写入 binlog cache（线程独有），提交时再写入 binlog 文件
• write（写入 page cache）vs fsync（持久化到磁盘）
• sync_binlog 控制 fsync 时机：
  • 0：只 write，不 fsync
  • 1：每次提交都 fsync
  • N：每 N 个事务提交后 fsync

redo log 写入机制：

• 先写入 redo log buffer（内存）
• 三种状态：buffer → page cache(write) → 磁盘(fsync)
• innodb_flush_log_at_trx_commit 控制写入策略：
  • 0：提交时只写 buffer
  • 1：提交时持久化到磁盘
  • 2：提交时只写 page cache

刷盘触发时机：后台线程每秒刷盘、buffer 占用达上限一半时刷盘、并行事务提交时顺带刷盘

组提交（Group Commit）：延迟 fsync，将多个事务的日志合并写入磁盘，减少 I/O

性能优化建议：

• binlog_group_commit_sync_delay：减少 binlog 写盘次数
• sync_binlog > 1（如 100~1000）：减少 fsync，但掉电可能丢日志
• innodb_flush_log_at_trx_commit=2：减少 redo log fsync，但掉电可能丢数据

24 MySQL 是怎么保证主备一致的

主备同步原理：主库处理读写，备库通过同步 binlog 保持数据一致。备库通常设为只读模式。

同步流程：

• change master 设置连接信息 → start slave 启动两个线程
• io_thread：从主库读取 binlog → 写入备库 relay log
• sql_thread：解析并执行 relay log 中的命令

binlog 三种格式：

格式	优点	缺点
statement	日志量小	可能主备不一致（LIMIT/NOW()）
row	保证主备一致	日志量大
mixed	自动选择 statement/row	结合两者优点

• row 格式 binlog 可用于数据恢复（误删可通过 binlog 恢复）
• 双 M 结构通过 server id 解决循环复制问题

25 MySQL 是怎么保证高可用的

主备延迟来源：

• 备库性能不足 / 备库压力大 / 大事务 / 并行复制能力不足

主备切换策略：

• 可靠性优先：确保数据完全一致再切换（短暂不可用）
• 可用性优先：优先保证可用（允许短暂数据不一致）

大多数场景建议使用可靠性优先策略。

26 备库为什么会延迟好几个小时

备库执行速度持续低于主库生成速度，单线程复制是主要原因。

并行复制核心原则：

• 更新同一行的两个事务必须分发到同一 worker
• 同一事务不能被拆开

并行复制演进：

版本	策略	特点
MySQL 5.5 及之前	单线程	延迟严重
MySQL 5.6	按库并行	不同库的事务可并行
MariaDB	组提交	相同 commit_id 的事务可并行
MySQL 5.7	LOGICAL_CLOCK	prepare 状态的事务可并行
MySQL 5.7.22	WRITESET	未操作相同行的事务可并行

大事务影响：大事务（大表 DDL/大量删除）导致延迟增加，建议拆分为小事务。

27 主库出问题了，从库怎么办？

一主多从架构：主库负责写，从库分担读请求。主库故障时需主备切换，从库需重新指向新主库。

基于位点的主备切换：

• 从库需找到与新主库同步的位点（binlog 文件名+偏移量）
• 位点不精确可能导致重复执行，用 sql_slave_skip_counter 或 slave_skip_errors 跳过

GTID（全局事务标识符）（MySQL 5.6+）：

• 格式：server_uuid:gno，唯一标识每个事务
• 简化主备切换，无须手动指定位点
• CHANGE MASTER TO master_auto_position=1 自动计算同步事务
• 新主库缺少从库所需事务时直接报错
• 支持 GTID 时建议使用 GTID 模式

28 读写分离有哪些坑

一主多从架构用于读写分离。架构分为客户端直连和带 Proxy 两种。

过期读问题：主从延迟导致从库读到旧数据。

解决方案：

• 强制走主库：实时性要求高的请求查主库
• Sleep 方案：查询前 sleep 一段时间（不精确）
• 判断主备无延迟：seconds_behind_master / 位点对比 / GTID 对比
• semi-sync：主库事务提交后至少一个从库收到 binlog 才返回确认
• 等主库位点：master_pos_wait(file, pos, timeout)
• 等 GTID：wait_for_executed_gtid_set(gtid_set, timeout)

29 如何判断一个数据库是不是出问题了

方法	能力	局限
select 1	检测进程存活	无法检测并发线程过多
查表	可检测并发线程过多	无法检测磁盘空间满
更新判断	可检测磁盘空间满	存在“判定慢”问题
performance_schema	精确监控 IO 请求时间	有一定性能损耗

30 答疑文章（二）：用动态的观点看加锁

加锁规则回顾：

• 原则 1：加锁基本单位是 next-key lock（前开后闭区间）
• 原则 2：查找过程中访问到的对象才加锁
• 优化 1：唯一索引等值查询 → next-key lock 退化为行锁
• 优化 2：等值查询向右遍历且最后值不满足时 → 退化为间隙锁
• 一个 bug：唯一索引范围查询会访问到不满足条件的第一个值

动态加锁分析：

• 不等号查询内部仍使用等值定位
• IN 查询逐个加锁，并发时顺序不同可能导致死锁
• 死锁时 InnoDB 回滚成本较小的事务
• 间隙锁范围由间隙右边记录定义，删除记录后范围可能变化

参考资料

• 极客时间教程 - MySQL 实战 45 讲