《高性能 MySQL》阅读笔记

本文档整理自《高性能 MySQL》（第3版）核心要点。

知识点：
Schema、数据类型、高性能索引、查询性能优化、MySQL 高级特性、MySQL 配置、
复制、可扩展、高可用、备份与恢复

---

第一章：MySQL 架构与历史

1.1 并发控制

• 锁的粒度：
  • 行级锁 (Row Lock)：开销大，并发度高；InnoDB 支持。
  • 表级锁 (Table Lock)：开销小，并发度低；存储引擎（如 MyISAM）在需要时自动创建。
• 锁类型：
  • 共享锁 (S Lock)：读锁。
  • 排他锁 (X Lock)：写锁。

1.2 事务与隔离级别 (ACID)

每种存储引擎实现的隔离级别不尽相同。可以根据选择的存储引擎来查阅资料了解对应的隔离级别。

• READ UNCOMMITTED (未提交读)：可能导致“脏读”。
• READ COMMITTED (提交读)：避免脏读，但可能产生“不可重复读”。
• REPEATABLE READ (可重复读)：避免不可重复读，但可能产生“幻读”(写入新记录，phantom row)。MySQL 默认隔离级别，通过 MVCC 解决。
• SERIALIZABLE (可串行化)：强制事务串行执行。

死锁策略：死锁发生时，目前（MySQL 5.5 中）InnoDB 的策略是：将持有最少行级锁的事务回滚。

1.3 事务日志 (WAL)

• 事务日志可以提高事务的效率。使用事务日志时，存储引擎在修改表数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
• 事务日志采用的是 顺序追加 的方式，所以速度相对会快很多。事务日志持久化后，内存中被修改的数据可以在后台慢慢刷入磁盘。这种方式叫 预写式日志 (Write-Ahead Logging)，修改数据需要写两次磁盘。

1.4 多版本并发控制 (MVCC)

• 实现原理：InnoDB 的 MVCC 是通过增加两个隐藏列实现的：一个记录行的过期时间，一个保持行的创建时间。存储的是 系统版本号（而不是实际时间），每开始一个新事务，系统版本号都会递增。
• 幻读解决：InnoDB 通过 间隙锁 (Next-Key Lock) 解决幻读问题。
• 兼容性：MVCC 只对 READ COMMITTED 和 REPEATABLE READ 生效。READ UNCOMMITTED 总是读取最新行；SERIALIZABLE 则会对所有读取的行都加锁。

---

第三章：为 MySQL 填写性能

定义性能最有效的方法是 响应时间。

3.1 性能分析与诊断

• 量化测量：准确的性能测量量化，可以很好的帮助发现、解决问题。
• 慢查询日志：记录慢查询对 MySQL 的性能影响很低，建议开启。
• 诊断方法：建议诊断问题时先使用以下两种方法，开销很低：
  • SHOW (GLOBAL) STATUS
  • SHOW PROCESSLIST
  • 可以通过简单的 shell 脚本（以较高频率，如 1次/s）交互式收集数据。
• 可视化：对收集到的数据进行可视化处理，最具说服力。
• 统计信息：
  • INFORMATION_SCHEMA.STATISTICS 统计了很多 MySQL 的统计信息。
  • SHOW VARIABLES

---

第四章：Schema 与数据类型优化

4.1 数据类型选择原则

1. 更小的通常更好：占用更少磁盘、内存和 CPU 缓存。
2. 简单就好：整型比字符操作代价更低。
3. 尽量避免 NULL：通常情况下最好指定列为 NOT NULL。
   • 包含 NULL 的列使索引统计和值比较更复杂。
   • 可为 NULL 的列使用更多的存储空间，每个索引记录需要一个额外字节。
   • 但是，通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小。

4.2 常用类型对比

• DATETIME vs TIMESTAMP：
  • TIMESTAMP 空间利用率更高（4 字节 vs 8 字节），且支持时区转换。
  • DATETIME 支持的时间范围更广。
• INT(11)：MySQL 可以为整数类型指定宽度，它 不会限制值的合法范围，只是规定了显示字符的个数。
• 浮点数：
  • FLOAT 和 DOUBLE 使用标准浮点运算进行近似计算。
  • DECIMAL 用于存储精确的小数。CPU 支持原生浮点计算但不支持 DECIMAL 运算，所以浮点运算更快。
• CHAR：当存储类型为 CHAR 时，MySQL 会删除所有的末尾空格。

4.3 ALTER TABLE 的陷阱

• MySQL 执行大部分修改表结构操作的方法是用新的表结构创建一个空表，锁旧表，从旧表中查出所有数据插入新表，然后删除旧表。
• 对于大表，这可能导致长时间锁表，尤其在内存不足且索引很多的情况下。

---

第五章：创建高性能的索引

5.1 索引的重要性

• 小表：全表扫描通常更高效。
• 中大型表：索引非常有效。
• 特大型表：建立和使用索引的代价随之增长。
• 块级别元数据：对于 TB 级别的数据，定位单条记录意义不大，经常使用块级别元数据技术来替代索引。

5.2 高性能索引策略

• 独立的列：索引列不能是表达式的一部分或函数的参数。
• 最左匹配原则：使用索引的最左匹配原则。
• 索引合并：当 MySQL 可以用两个及以上的索引，对结果进行交集或者并集时，通常意味着表上的索引建得很糟糕。
• 聚簇索引 (Clustered Index)：
  • InnoDB 默认以主键作为聚簇索引。如果没有主键，会选择唯一非空索引或隐式定义主键。
  • 页分裂 (Page Split)：插入新行或主键更新可能导致移动行。当行必须插入已满页时，存储引擎会将页面分裂成两个，并移动部分数据。
• 二级索引 (Secondary Index/非聚簇索引)：
  • 叶子节点存储的是行的主键值，访问数据需要两次查找。
  • 二级索引可能比想象的更大，因为其叶子节点包含了引用行的主键列。
• 索引维护：
  • 重复索引：在相同列上创建多个索引，应尽量避免。
  • 冗余索引：表中的索引越多插入速度会越慢。
• 索引统计信息：
  • InnoDB 通过抽样方式计算：随机读取少量页面（可通过 innodb_stats_sample_pages 设置）。
  • 打开 INFORMATION_SCHEMA 或使用 SHOW TABLE STATUS / SHOW INDEX 时，都有可能触发统计信息更新。对于大数据量且 IO 慢的服务器，这可能导致严重性能问题。

---

第六章：查询性能优化

6.1 衡量开销的指标

1. 响应时间
2. 扫描的行数
3. 返回的行数

优化确认点：

• 确认是否向数据库请求了超过实际需要（大量不需要）的数据。
• 确认 MySQL 服务器层是否在分析大量超过需要的数据行。
• 如果服务器层分析了大量的行但结果很少，可尝试：1. 使用索引覆盖扫描；2. 改变表结构（如建立汇总表）；3. 重写复杂查询。

6.2 执行过程优化

• 通信协议：MySQL 采用 半双工 协议。任何时刻要么服务器向客户端发，要么客户端向服务器发，不能同时。
• 查询状态：通过 SHOW FULL PROCESSLIST 查看。
• 查询成本：通过 SHOW STATUS LIKE 'last_query_cost'; 查看。
• 多核特性：MySQL 无法利用多核特性来并行执行查询。
• LIMIT 分页优化：偏移量大时，使用索引覆盖扫描后再关联。
• SELECT FOR UPDATE：一般的，我们要尽量避免使用。
• COUNT()：
  • COUNT(*) 统计行数。
  • COUNT(column) 统计非 NULL 列值。
  • COUNT(1) 仅在第一列定义为 NOT NULL 时才有相同的优化。
• 查询执行计划：MySQL 的查询执行计划总是 左侧深度优先树。

---

第七章：MySQL 高级特性

7.1 常用对象

• 分区表、视图、临时表、游标。
• XA 事务：分布式事务。参数 innodb_support_xa 还会控制内部存储引擎和二进制日志之间的一致性。

7.2 查询缓存 (Query Cache)

• 机制：在查询开始时，MySQL 首先检查查询缓存是否命中。
• 限制：包含任何不确定函数的语句不会被缓存。
• 风险：缓存空间过大（query_cache_size）可能导致过期操作时服务器僵死。建议通过控制缓存空间大小来规避。
• 开销：
  • 读：必须先检查命中。
  • 写：必须将对应表的所有缓存设置为失效。如果碎片很多，消耗很大。
• 建议：写密集的应用建议直接禁用。
• 如何分析和配置查询缓存

---

第十章：复制 (Replication)

• 基本问题：让一台服务器的数据与其他服务器保持同步。
• 方式：基于行的复制 (RBR) 和 基于语句的复制 (SBR)。主库记录二进制日志，备库重放。

---

第十一章 & 第十二章：扩展性与高可用

• 扩展方式：数据分片 (Sharding)、多实例、集群、数据切分（保持活跃数据独立，隔离活跃/不活跃数据）。
• 负载均衡：配合负载均衡让扩展对客户端无感。
• 高可用指标：提升平均失效时间 (MTBF)，降低平均恢复时间 (MTTR)。

---

第十五章：备份与恢复

• InnoDB 恢复：由于 ACID 特性，还原快照后启动，InnoDB 会自动运行恢复进程，重放日志中提交但未应用的事务。
• 调试：复制配置时观察错误日志：tail -f /var/log/mysql/mysql.err

---

EXPLAIN 执行计划详解

基本说明

• 每个表在输出中只有一行。
• EXPLAIN EXTENDED：显示“逆向编译”后的 SELECT 语句（配合 SHOW WARNINGS）。
• EXPLAIN PARTITIONS：显示访问的分区。
• 注意：如果 FROM 子句包含子查询，MySQL 实际上会执行该子查询。
• 建议：建议重载/重写非 SELECT 语句，以利用 EXPLAIN 进行分析。

列详解

• id：SELECT 识别符。
• select_type：
  • SIMPLE：简单查询。
  • PRIMARY：最外层查询。
  • SUBQUERY：SELECT 列表中的子查询。
  • DERIVED：FROM 子句中的子查询（派生表）。
  • UNION / UNION RESULT。
  • DEPENDENT：依赖外层数据。
  • UNCACHEABLE：不可缓存。
• table：访问的表名。
• type (访问类型)：
  • ALL：全表扫描。
  • index：按索引次序扫描全表。主要优点是避免了排序，最大缺点是按索引次序读取整个表的开销，尤其在随机访问行时开销巨大。
  • range：范围扫描。
  • ref：非唯一索引访问（或唯一索引的非唯一性前缀）。索引要跟某个参考值（reference）相比较，故名。
  • eq_ref：唯一索引访问。
  • const / system：常量优化。
  • NULL：优化阶段即可完成。
• possible_keys：可能使用的索引。
• key：实际采用的索引。
• key_len：索引字段可能的最大长度。
• ref：查找值所使用的列或常量。
• rows：MySQL 估计为了找到符合查询标准而必须读取的行的 平均数（内嵌循环关联计划里的循环数目），并非最终结果行数。
• filtered：符合条件的记录百分比的一个 悲观估算。
• Extra：
  • Using index：覆盖索引。
  • Using where：引擎检索后再过滤。
  • Using temporary：使用了临时表。
  • Using filesort：外部索引排序。
  • Range checked for each record (index map:N)：没有好索引，新的索引将在联接的每一行上重新估算。

---

锁的调试与分析

服务器级别锁

• 表锁：
  • 显式：LOCK TABLES。
  • 隐式：服务器自动创建。
  • 查看：mysqladmin debug。
• 全局锁：FLUSH TABLES WITH READ LOCK 或 read_only=1。
  • 示例：

    -- 会话1
    FLUSH TABLES WITH READ LOCK;
    -- 会话2
    LOCK TABLES test WRITE; -- 会挂起，显示 "Waiting for release for readlock"

• 命名锁：重命名或删除表时创建。
• 字符锁：GET_LOCK()。

存储引擎级别锁

• InnoDB：在 SHOW ENGINE INNODB STATUS 的 TRANSACTIONS 部分显露锁信息。如果事务正在等待某个锁，该锁会显示在此处。

---

相关资源

• 执行路径图：
• 查询缓存分析：