《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限制，进而拒绝新连接。
• 解决方案：
  • 方法一：主动断开空闲连接。优先断开事务外空闲的连接，再考虑断开事务内空闲的连接。可以通过kill connection命令手动断开连接。
  • 方法二：减少连接过程的消耗。通过--skip-grant-tables参数重启数据库，跳过权限验证，但这种方法风险极高，尤其是在外网可访问的情况下。
• 风险：断开连接可能导致应用端未正确处理连接丢失，进而引发更多问题。

慢查询性能问题

• 慢查询的三种可能原因：
  1. 索引没有设计好：通过紧急创建索引来解决，建议在备库先执行alter table语句，再进行主备切换。
  2. SQL 语句没写好：通过改写 SQL 语句来优化，MySQL 5.7 提供了query_rewrite功能，可以自动重写 SQL 语句。
  3. MySQL 选错了索引：通过force index强制使用正确的索引。
• 预防措施：在上线前，通过慢查询日志和回归测试，提前发现并解决潜在的慢查询问题。

QPS 突增问题

• 问题：由于业务高峰或应用 bug，某个 SQL 语句的 QPS 突然暴涨，导致数据库压力过大。
• 解决方案：
  1. 下掉新功能：如果新功能有 bug，可以直接从数据库端去掉白名单或删除相关用户。
  2. 重写 SQL 语句：将高 QPS 的 SQL 语句重写为select 1，但这种方法风险较高，可能会误伤其他功能或导致业务逻辑失败。
• 风险：重写 SQL 语句可能导致业务逻辑错误，应作为最后的手段。

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

binlog 的写入机制

• 事务执行过程中，日志先写入 binlog cache，事务提交时再将 binlog cache 写入 binlog 文件。
• binlog cache 是每个线程独有的，而 binlog 文件是共享的。
• 写入操作分为 write（写入文件系统的 page cache）和 fsync（持久化到磁盘）。
• 参数 sync_binlog 控制 fsync 的时机：
  • sync_binlog=0：每次提交事务只 write，不 fsync。
  • sync_binlog=1：每次提交事务都 fsync。
  • sync_binlog=N：每 N 个事务提交后 fsync。

redo log 的写入机制

• 事务执行过程中，redo log 先写入 redo log buffer。
• redo log 的三种状态：
  • 在 redo log buffer 中（内存）。
  • 写入文件系统的 page cache（write）。
  • 持久化到磁盘（fsync）。
• 参数 innodb_flush_log_at_trx_commit 控制 redo log 的写入策略：
  • 0：事务提交时只写入 redo log buffer。
  • 1：事务提交时将 redo log 持久化到磁盘。
  • 2：事务提交时只写入 page cache。

redo log 写入磁盘的触发时机

• 后台线程每秒会将 redo log buffer 中的日志写入磁盘。
• redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。
• 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。

组提交（Group Commit）机制：

• 通过延迟 fsync 操作，将多个事务的 redo log 或 binlog 合并写入磁盘，减少磁盘 I/O 操作。
• 组提交可以显著提升性能，尤其是在高并发场景下。

WAL 机制的优势：

• redo log 和 binlog 都是顺序写入，顺序写比随机写速度快。
• 组提交机制减少了磁盘 I/O 操作的次数。

性能优化建议：

• 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。
• 将 sync_binlog 设置为大于 1 的值（如 100~1000），减少 fsync 次数，但主机掉电时会丢 binlog 日志。
• 将 innodb_flush_log_at_trx_commit 设置为 2，减少 redo log 的 fsync 次数，但主机掉电时会丢失数据。

数据一致性与可靠性：

• MySQL 通过 redo log 和 binlog 的持久化来保证 crash-safe。
• 即使事务未提交，redo log 和 binlog 的丢失也不会导致数据不一致，因为事务未提交的数据不会被应用到数据库中。

常见问题解答：

• 解释了为什么 binlog cache 是线程独有，而 redo log buffer 是全局共享的。
• 讨论了事务执行期间发生 crash 时，redo log 和 binlog 的丢失不会导致主备不一致。
• 解释了 binlog 写入后发生 crash 的情况，客户端重连后事务已提交成功是正常现象。

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

MySQL 主备同步的基本原理：

• 主库（节点 A）负责处理客户端的读写请求，备库（节点 B）通过同步主库的 binlog 来保持数据一致。
• 主备切换时，客户端会从主库切换到备库，备库变为新的主库。
• 备库通常设置为只读模式，防止误操作和双写问题，但同步线程拥有超级权限，可以绕过只读限制。

主备同步的流程：

• 备库通过 change master 命令设置主库的连接信息，并通过 start slave 命令启动两个线程：io_thread 和 sql_thread。
• io_thread 负责从主库读取 binlog 并写入备库的中转日志（relay log）。
• sql_thread 负责解析并执行中转日志中的命令，保持备库与主库的数据一致。

binlog 的三种格式：

• statement：记录 SQL 语句的原文。优点是日志量小，缺点是某些情况下可能导致主备数据不一致（如使用了 LIMIT 或 NOW() 函数）。
• row：记录每一行数据的变更。优点是保证主备数据一致，缺点是日志量大，尤其是批量操作时。
• mixed：MySQL 自动选择 statement 或 row 格式，结合两者的优点，避免数据不一致问题。

binlog 格式的选择：

• statement 格式可能导致主备数据不一致，尤其是在使用不确定函数（如 NOW()）或 LIMIT 时。
• row 格式记录了每一行数据的变更，确保主备数据一致，但日志量较大。
• mixed 格式是 MySQL 的折中方案，自动选择 statement 或 row 格式，避免数据不一致问题。

binlog 的数据恢复：

• row 格式的 binlog 记录了每一行数据的变更，可以用于数据恢复。例如，误删数据后可以通过 binlog 恢复删除的行。
• insert、update 和 delete 操作都可以通过 binlog 进行恢复，尤其是 row 格式的 binlog 记录了完整的行数据。

循环复制问题：

• 在双 M 结构（主备互为主备）中，可能会出现循环复制问题，即主库和备库互相同步 binlog，导致无限循环。
• MySQL 通过 server id 解决循环复制问题：每个库在收到 binlog 时，会检查 server id，如果与自己的相同，则丢弃该日志，避免循环复制。

binlog 的其他用途：

• binlog 不仅可以用于主备同步，还可以用于数据恢复、审计、数据同步等场景。
• 通过 mysqlbinlog 工具可以解析 binlog，并将其用于数据恢复或重放。

总结：

• binlog 是 MySQL 主备同步的核心机制，通过不同的格式（statement、row、mixed）来平衡日志大小和数据一致性。
• 主备同步通过 io_thread 和 sql_thread 实现，确保备库与主库的数据一致。
• 双 M 结构中的循环复制问题通过 server id 机制解决，避免无限循环。

25 Mysql 是怎么保证高可用的

主备同步与最终一致性：

• MySQL 通过 binlog 实现主备同步，备库接收并执行主库的 binlog，最终达到与主库一致的状态。
• 最终一致性是主备同步的基础，但要实现高可用性，还需要解决主备延迟等问题。

主备延迟的来源：

• 备库性能不足：备库所在机器的性能较差，导致同步速度慢。
• 备库压力大：备库承担了过多的读请求，消耗了大量 CPU 资源，影响了同步速度。
• 大事务：主库上的大事务（如大量数据删除或大表 DDL）会导致备库延迟，因为备库需要等待主库的事务完成后才能同步。
• 备库的并行复制能力：备库的并行复制能力不足也会导致延迟。

主备切换策略：

• 可靠性优先策略：
  • 在主备切换时，确保备库的数据与主库完全一致后再切换。
  • 切换过程中会有短暂的不可用时间，但能保证数据的一致性。
• 可用性优先策略：
  • 在主备切换时，优先保证系统的可用性，允许短暂的数据不一致。
  • 这种策略可能会导致数据不一致，尤其是在使用 statement 或 mixed 格式的 binlog 时。

binlog 格式对数据一致性的影响：

• statement 格式：记录 SQL 语句的原文，可能导致主备数据不一致（如使用 LIMIT 或 NOW() 函数时）。
• row 格式：记录每一行数据的变更，确保主备数据一致，但日志量较大。
• mixed 格式：MySQL 自动选择 statement 或 row 格式，结合两者的优点，避免数据不一致问题。

高可用性与数据一致性的权衡：

• 大多数情况下，建议使用可靠性优先策略，确保数据的准确性。
• 在某些特殊场景下（如操作日志记录），可用性优先策略可能更为合适，因为短暂的数据不一致可以通过 binlog 修复，且不会对业务造成严重影响。

异常切换与主备延迟：

• 在主库故障时，主备延迟会影响系统的可用性。延迟越小，系统恢复的时间越短，可用性越高。
• 如果主备延迟较大，切换时可能会导致系统不可用或数据不一致。

总结：

• MySQL 的高可用性依赖于主备同步机制，主备延迟是影响高可用性的关键因素。
• 通过优化备库性能、减少大事务、提升并行复制能力等手段，可以减少主备延迟。
• 在主备切换时，应根据业务需求选择可靠性优先或可用性优先策略，确保在数据一致性和系统可用性之间找到平衡。

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

备库延迟的原因：

• 备库执行日志的速度持续低于主库生成日志的速度，导致延迟可能达到小时级别。
• 单线程复制是备库延迟的主要原因之一，尤其是在主库并发高、TPS 高的情况下。

并行复制的核心原则：

• 不能造成更新覆盖：更新同一行的两个事务必须被分发到同一个 worker 中。
• 同一个事务不能被拆开：同一个事务的多个更新语句必须放到同一个 worker 中执行。

多线程复制的演进：

• MySQL 5.5 及之前版本：只支持单线程复制，导致备库延迟问题严重。
• MySQL 5.6 版本：支持了并行复制，允许不同数据库的事务在备库上并行执行。
• MariaDB 的并行复制策略：基于组提交（group commit）特性，相同 commit_id 的事务可以在备库上并行执行，但存在大事务拖后腿的问题。
• MySQL 5.7 版本：引入了基于 LOGICAL_CLOCK 的并行复制策略，允许处于 prepare 状态的事务在备库上并行执行，提升了并行度。
• MySQL 5.7.22 版本：引入了基于 WRITESET 的并行复制策略。算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。

不同并行复制策略的优缺点：

• 按库并行策略：适用于多数据库场景，但在单数据库或热点表场景下效果不佳。
• 按表并行策略：适用于多表场景，但在热点场景下会退化为单线程复制。
• 按行并行策略：并行度最高，但消耗更多的内存和 CPU 资源，适用于大事务较少的场景。
• MariaDB 的组提交策略：基于 commit_id 的并行复制，简单易实现，但容易受大事务影响。
• MySQL 5.7 的 LOGICAL_CLOCK 策略：基于 prepare 状态的并行复制，提升了并行度，但依赖于主库的 binlog 组提交机制。
• MySQL 5.7.22 的 WRITESET 策略：基于行 hash 值的并行复制，减少了计算量和内存消耗，支持 statement 格式的 binlog。

大事务对备库延迟的影响：

• 大事务（如大表 DDL 或大量数据删除）会导致备库延迟增加，因为备库需要等待大事务完成后才能继续执行其他事务。
• 建议将大事务拆分为小事务，以减少对备库同步的影响。

总结：

• 多线程复制是解决备库延迟问题的关键，MySQL 通过不同版本的演进逐步提升了并行复制的效率和灵活性。
• 不同的并行复制策略适用于不同的业务场景，DBA 需要根据实际情况选择合适的策略。
• 大事务是造成备库延迟的主要原因之一，开发人员应尽量避免大事务操作，将其拆分为小事务。

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

一主多从架构

• 一主多从架构通常用于读写分离，主库负责写操作和部分读操作，从库分担读请求。
• 当主库发生故障时，需要进行主备切换，从库需要重新指向新的主库，增加了切换的复杂性。

基于位点的主备切换：

• 在切换过程中，从库需要找到与新主库同步的位点（binlog 文件名和偏移量），以确保数据一致性。
• 位点的获取通常是通过解析新主库的 binlog 文件，找到故障时刻的大致位置。
• 由于位点不精确，可能会导致从库重复执行某些事务，出现主键冲突等问题。
• 解决方法包括：
  • 使用 sql_slave_skip_counter 跳过重复事务。
  • 设置 slave_skip_errors 参数，跳过常见的错误（如 1062 主键冲突和 1032 删除数据找不到行）。

GTID（全局事务标识符）

• GTID 是 MySQL 5.6 引入的机制，用于唯一标识每个事务，格式为 server_uuid:gno。
• GTID 模式简化了主备切换过程，不再需要手动指定位点，系统会自动处理同步问题。
• GTID 的生成方式有两种：
  • 自动生成：事务提交时分配 GTID。
  • 手动指定：通过 set gtid_next 指定 GTID，适用于跳过某些事务的场景。

基于 GTID 的主备切换：

• 在 GTID 模式下，从库只需要执行 CHANGE MASTER TO 命令，并设置 master_auto_position=1，系统会自动计算需要同步的事务。
• 新主库会计算自己与从库的 GTID 集合差集，确保从库获取到所有缺失的事务。
• 如果新主库缺少从库所需的事务，会直接报错，确保数据完整性。

GTID 与在线 DDL：

• 在双 M 结构下，备库执行的 DDL 语句可以通过 GTID 机制确保不会在主库上重复执行。
• 通过手动设置 GTID，可以确保 DDL 操作的 binlog 记录不会影响主库。

总结：

• 基于位点的主备切换复杂且容易出错，而 GTID 模式简化了这一过程，提升了主备切换的效率和可靠性。
• 如果 MySQL 版本支持 GTID，建议使用 GTID 模式进行主备切换。
• GTID 模式不仅适用于主备切换，还可以用于在线 DDL 操作，确保数据一致性。

28 读写分离有哪些坑

读写分离的基本架构：

一主多从架构通常用于读写分离，主库负责写操作，从库分担读请求。

读写分离的两种常见架构：

• 客户端直连：客户端直接连接数据库，性能较好，但主备切换时客户端需要调整连接信息。
• 带 Proxy 的架构：客户端连接 Proxy，由 Proxy 负责路由请求，对客户端友好，但架构复杂。

过期读问题：

由于主从延迟，客户端在从库上可能会读到过期的数据，这种现象称为“过期读”。过期读的常见场景是主库更新后，从库还未同步完成，客户端查询从库时读到旧数据。

解决过期读的几种方案：

• 强制走主库：对于必须读到最新数据的请求，强制查询主库。适用于对数据实时性要求高的场景，但会增加主库的压力。
• Sleep 方案：在查询从库前先 sleep 一段时间，假设主从延迟在 1 秒内。虽然简单，但不精确，可能导致等待时间过长或仍然读到过期数据。
• 判断主备无延迟：通过 show slave status 判断主从延迟，确保从库同步完成后再查询。可以通过 seconds_behind_master、位点对比或 GTID 集合对比来判断。
• 配合 semi-sync：使用半同步复制（semi-sync），确保主库在事务提交后，至少有一个从库收到 binlog 后才返回确认。可以减少过期读的概率，但在多从库场景下仍可能有问题。semi-sync 流程：
  • 事务提交的时候，主库把 binlog 发给从库；
  • 从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
  • 主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。
• 等主库位点方案：使用 select master_pos_wait(file, pos, timeout) 命令，等待从库同步到指定位点后再查询。可以精确控制查询时机，避免过期读。
• 等 GTID 方案：使用 select wait_for_executed_gtid_set(gtid_set, timeout) 命令，等待从库执行到指定 GTID 后再查询。适用于 GTID 模式，减少了查询主库位点的开销。

不同的方案适用于不同的业务场景，需要根据业务需求选择。在实际应用中，可以混合使用多种方案，根据请求的类型选择不同的处理方式。

总结：

• 过期读是读写分离架构中常见的问题，主从延迟是主要原因。
• 通过合理的方案选择，可以在保证读写分离的同时，尽量减少过期读的发生。
• 对于高一致性要求的场景，建议使用等主库位点或等 GTID 方案，确保查询结果的准确性。

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

select 1 判断：

• select 1 只能检测数据库进程是否存活，无法检测数据库内部的并发线程数是否过高或是否存在其他问题。
• 当并发线程数达到 innodb_thread_concurrency 设置的上限时，数据库可能无法处理新请求，但 select 1 仍然可以成功返回，导致误判。

查表判断：

• 在系统库（如 mysql 库）中创建一个健康检查表（如 health_check），里面只放一行数据，然后定期执行 select * from mysql.health_check。
• 这种方法可以检测到由于并发线程过多导致的数据库不可用情况，但无法检测磁盘空间满等问题。

更新判断：

• 在健康检查表中加入一个 timestamp 字段，定期执行更新操作（如 update mysql.health_check set t_modified=now()）。
• 这种方法可以检测到磁盘空间满等问题，因为更新操作需要写 binlog，如果磁盘空间满，更新操作会失败。为了避免主备冲突，可以在健康检查表中使用 server_id 作为主键，确保主库和备库的更新操作不会冲突。
• 更新判断的局限性：
  • 更新判断存在“判定慢”的问题，即在系统 IO 资源紧张时，更新操作可能仍然成功返回，导致误判。
  • 外部检测的随机性可能导致问题无法及时被发现，尤其是在定时轮询的间隔期间。

内部统计：

• MySQL 5.6 版本以后提供了 performance_schema 库，可以统计每次 IO 请求的时间。
• 通过监控 performance_schema.file_summary_by_event_name 表中的 IO 请求时间，可以更准确地判断数据库是否出现性能问题。
• 可以设置阈值，当单次 IO 请求时间超过一定值（如 200 毫秒）时，认为数据库出现异常。

总结：

• 不同的检测方法各有优缺点，select 1 简单但不精确，查表和更新判断可以检测更多问题，但仍存在局限性。
• 内部统计方法（如 performance_schema）可以提供更精确的数据库状态信息，但会带来一定的性能损耗。
• 在实际应用中，可以根据业务需求选择合适的检测方法，通常建议结合更新判断和内部统计方法，以提高检测的准确性。

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

加锁规则回顾：

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

不等号条件里的等值查询：

• 在不等号查询中，虽然条件是不等号，但在索引树的搜索过程中，引擎内部会使用等值查询来定位记录。
• 例如，select * from t where id>9 and id<12 order by id desc for update; 的加锁范围是 (0,5]、(5,10] 和 (10,15)，其中 id=15 不满足条件，next-key lock 退化为间隙锁 (10,15)。

等值查询的过程：

• 对于 select id from t where c in(5,20,10) lock in share mode;，加锁过程是逐个进行的，先加 c=5 的记录锁，再加 c=10 的记录锁，最后加 c=20 的记录锁。
• 如果并发执行 select id from t where c in(5,20,10) order by c desc for update;，加锁顺序相反，可能导致死锁。

死锁分析：

• 死锁发生时，InnoDB 会选择回滚成本较小的事务来解除死锁。
• 通过 show engine innodb status 可以查看死锁信息，了解哪些事务持有锁、等待锁，以及最终回滚了哪个事务。

锁等待分析：

• 通过 show engine innodb status 可以查看锁等待信息，了解哪些事务在等待锁，以及等待的锁类型（如间隙锁、插入意向锁等）。
• 间隙锁的范围是由间隙右边的记录定义的，删除记录后，间隙锁的范围可能会发生变化。

update 语句的加锁行为：

• update 语句的加锁范围可以通过语句的执行逻辑来分析。例如，update t set c=1 where c=5 会先插入新记录，再删除旧记录，可能会被间隙锁阻塞。

参考资料

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