MySQL 与索引优化

本章目标：能讲清 InnoDB 索引、事务隔离和 MVCC，看懂 EXPLAIN，并说出常见 SQL 的优化方向。 前置知识：SQL 基础、多表查询、窗口函数。 预计用时：60 分钟。

注意：练习环境是 DuckDB，不是 MySQL。本章的建表、索引、EXPLAIN 语句用于理解和面试，不要求本机装 MySQL；可运行的部分用玩具库 project/sql_demo.duckdb 的 demo_* 表在 DuckDB 里完成。两种引擎优化思路相通，但语法和实现细节不同，不要把 DuckDB 的执行计划当成 MySQL 的。

1. 为什么需要它

数据开发每天都会碰 MySQL：业务库的数据从 MySQL 同步到数仓，面试里的数据库题也大多按 MySQL 问。你不一定要会调参，但必须能回答“这条查询为什么慢”“加什么索引”“事务会不会互相影响”。

本章只讲实习面试会问的部分：索引结构、事务、MVCC、EXPLAIN、分库分表概念。存储引擎源码和参数调优不展开。

面试高频：面试官问 MySQL，不是要你背参数，而是要你能从一个慢查询讲到索引结构、回表、事务隔离。

2. 核心概念

2.1 一条 SQL 经过哪三层

客户端
  │
  ▼
连接层：连接池、认证、权限
  │
  ▼
Server 层：解析器 → 优化器 → 执行器（8.0 已移除查询缓存）
  │
  ▼
存储引擎层：InnoDB（默认）、MyISAM、Memory……

• 解析器：做词法和语法分析，生成语法树。
• 优化器：选索引、决定 JOIN 顺序，生成执行计划。
• 执行器：按计划调用存储引擎接口取行。
• 结论：索引选得好不好，主要看优化器拿到的统计信息和你的 SQL 写法。

2.2 InnoDB 和 MyISAM 怎么选

MySQL 5.5 起默认 InnoDB。面试答“默认选 InnoDB，因为要事务和行锁；MyISAM 的 count(*) 快，是拿不支持事务换来的”。

2.3 B+ 树：为什么是它

B+ 树的特点：

• 非叶子节点只存键和指针，不存数据，一页能放更多键，树更矮。
• 数据全在叶子节点，叶子之间用双向链表连接，范围查询可以顺序扫描。
• 所有查询都走到叶子，路径长度一致，查询时间稳定。

量级估算（16KB 页，bigint 主键 8 字节 + 6 字节指针）：一个非叶子页约存 1,170 个键；假设一行 1KB，一个叶子页约 16 行；三层树约 1170 × 1170 × 16 ≈ 2,000 万 行。所以常见说法是“三层 B+ 树支撑千万级数据，查一行只要 2–3 次磁盘 IO”。

注意：这是量级估算，不是精确值；行大小、页大小、压缩都会改变结果。

2.4 聚簇索引、二级索引、回表

• 聚簇索引：InnoDB 按主键建，叶子节点存整行。一张表只有一个。
• 没主键时选第一个非空唯一索引；都没有就生成 6 字节隐藏 row_id。
• 二级索引：叶子节点存“索引列 + 主键值”，不存整行。
• 回表：二级索引找到主键后，再回聚簇索引取整行。

-- 假设 idx_city 是 demo_customers.city 上的二级索引
SELECT * FROM demo_customers WHERE city = '北京';
-- 1) idx_city 找到一批主键
-- 2) 用主键回聚簇索引取整行（回表）

如果查询的列都在索引里，就不用回表，叫覆盖索引，EXPLAIN 的 Extra 显示 Using index：

SELECT city FROM demo_customers WHERE city = '北京';                    -- 覆盖索引
SELECT city, customer_name FROM demo_customers WHERE city = '北京';    -- 索引是 (city, customer_name) 时也覆盖

面试高频：为什么推荐自增 bigint 主键？主键有序，插入基本只追加，减少页分裂；主键短，而二级索引叶子要存主键，主键越长所有二级索引越大。UUID 做主键会导致随机插入和页分裂。

2.5 联合索引与最左前缀

CREATE INDEX idx_cust_status_date ON demo_orders (customer_id, status, created_at);

能用的条件（从左往右连续匹配）：

• 范围列后面的列一般不能继续用于缩小扫描范围（仍可能用于覆盖索引或 ICP 过滤）。
• MySQL 8.0.13 起有索引跳跃扫描（skip scan），最左列基数很低时可能用上，但不要依赖它，面试常把它当例外提一句。
• 联合索引顺序：等值条件在前，范围条件在后，排序/分组列尽量跟上。

2.6 事务 ACID 和三种日志

• undo log：记录反向操作，用于回滚，同时构成 MVCC 的版本链。
• redo log：物理日志，循环写；先写日志再刷数据页，崩溃后重放。
• binlog：Server 层逻辑日志，用于主从复制和时间点恢复。
• 提交时要保证 redo 和 binlog 一致，MySQL 用两阶段提交（redo prepare → 写 binlog → redo commit）。

2.7 隔离级别与幻读

• 脏读：读到别人未提交的数据。
• 不可重复读：同一事务两次读同一行，值变了。
• 幻读：同一事务两次按条件查，行数变了。

2.8 MVCC：快照读为什么能重复读

InnoDB 每行有隐藏字段：DB_TRX_ID（最后修改的事务 id）、DB_ROLL_PTR（指向 undo 版本链）、DB_ROW_ID（无主键时使用）。

• 每次修改都把旧版本写进 undo log，形成版本链。
• ReadView 记录当前活跃事务：m_ids、min_trx_id、max_trx_id、creator_trx_id。
• 读一行时沿版本链找“对当前事务可见”的版本：自己改的可见；早于最小活跃事务的可见；活跃事务的不可见。
• RC：每条 SELECT 新建 ReadView；RR：事务第一次快照读时建一次，之后复用，所以能重复读。

幻读怎么处理：

• 快照读（普通 SELECT）：MVCC 看不到新插入的行。
• 当前读（SELECT ... FOR UPDATE、UPDATE、DELETE）：读最新数据，用 next-key lock（记录锁 + 间隙锁）锁住范围，阻止别的事务在间隙插入。

面试高频：不要答“RR 完全解决了幻读”。准确说法是“快照读靠 MVCC，当前读靠 next-key lock；两种读语义下表现不同”。

2.9 锁的层次

• 共享锁 S / 排他锁 X；意向锁 IS/IX 是表级标记，表示“表里有人锁了行”。
• 间隙锁在 RR 下明显；RC 基本不加间隙锁（外键和唯一性检查除外）。
• 死锁：两个事务互相等对方的锁。InnoDB 会检测并回滚代价小的事务；预防方法是按固定顺序访问、缩短事务、走索引减少锁范围。

2.10 EXPLAIN 看什么

EXPLAIN SELECT * FROM demo_orders
WHERE customer_id = 2
ORDER BY created_at DESC
LIMIT 20;

重点字段：

• type=ALL 且 rows 很大，大概率是全表扫描。
• Using filesort 不代表一定慢，但 ORDER BY 没吃到索引顺序时会出现。
• Using index condition 是索引条件下推（ICP），在存储引擎层先过滤。

注意：rows 是优化器估算值，不是真实行数；统计信息过期时可能偏差很大，可 ANALYZE TABLE 更新。

2.11 慢查询与优化步骤

开启慢日志（MySQL 配置）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

排查顺序：

1. 从慢日志找出耗时高、扫描行数多的 SQL（mysqldumpslow 或 pt-query-digest）。
2. EXPLAIN 看 type、key、rows、Extra。
3. 判断是缺索引、索引失效、回表太多、排序/临时表，还是数据量本身太大。
4. 改 SQL 或加索引，再 EXPLAIN 对比。
5. 上线后观察，防止新索引拖慢写入。

常见手段：覆盖索引、联合索引、去掉 SELECT *、深分页改写、分批处理、避免在索引列上做函数运算。

2.12 分库分表与读写分离（概念）

• 读写分离：主库写，从库读，靠 binlog 复制。问题是主从延迟，写完立刻读可能读不到；关键读走主库。
• 垂直拆分：按业务拆库（订单库、用户库），按列拆表（大字段单独放）。
• 水平拆分：同一张表按分片键（如 user_id 取模）拆到多个库/表。
• 代价：跨库 JOIN、跨库事务、全局唯一 ID、聚合查询、扩容迁移都变复杂。
• 原则：能不分就不分；先索引、缓存、归档、读写分离，最后才分库分表。常见中间件有 ShardingSphere、MyCat。

3. 动手示例

3.1 用玩具数据算“选择性”

索引是否有效，先看列的区分度。打开玩具库（见 01 章）执行：

SELECT COUNT(*) AS rows_cnt,
       COUNT(DISTINCT city) AS city_cnt,
       ROUND(COUNT(DISTINCT city) * 1.0 / COUNT(*), 4) AS selectivity
FROM demo_customers;

demo_customers 共 7 行、4 个城市，选择性 4 / 7 ≈ 0.5714，平均每个城市不到 2 行。

再看组合列：

SELECT COUNT(*) AS rows_cnt,
       COUNT(DISTINCT (city, referrer_id)) AS combo_cnt,
       ROUND(COUNT(DISTINCT (city, referrer_id)) * 1.0 / COUNT(*), 4) AS selectivity
FROM demo_customers;

(city, referrer_id) 有 6 种组合，选择性升到 6 / 7 ≈ 0.8571——三个北京客户里有两个没有推荐人，这两行去重后合并成一种组合。

最后看低基数列：

SELECT COUNT(*) AS rows_cnt,
       COUNT(DISTINCT status) AS status_cnt,
       ROUND(COUNT(DISTINCT status) * 1.0 / COUNT(*), 4) AS selectivity
FROM demo_orders;

demo_orders 12 行、3 种状态，选择性 0.25；但 status = 'paid' 会留下 10 行，占 83%。这种列单独建索引，过滤效果差，优化器很可能直接全表扫描。

结论：选择性 = 不同值个数 / 总行数，越接近 1 越适合建索引；但真正决定收益的是这条查询过滤后还剩多少行。city 选择性只有 0.5714，过滤后不到 2 行，够用；status 选择性 0.25，过滤后仍剩 83%，单独建索引意义不大。玩具表只有十几行，优化器会直接全表扫，上面的数字只用来说明算法，真实判断要看百万行级别的数据。

注意：选择性只是一个起点。最终是否走索引还由优化器按代价决定，统计信息过期时判断也会失真。

3.2 在 MySQL 里验证（可选）

如果本机装了 MySQL 8.0，可以建一张小表观察执行计划：

CREATE TABLE t_order (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id VARCHAR(32) NOT NULL,
    order_status VARCHAR(16) NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    KEY idx_cust_status_date (customer_id, order_status, order_date)
) ENGINE=InnoDB;

EXPLAIN SELECT id, amount FROM t_order
WHERE customer_id = 'C100229' AND order_status = 'paid'
ORDER BY order_date DESC
LIMIT 20;

观察 key 是否为 idx_cust_status_date、Extra 是否还有 Using filesort。没有 MySQL 不影响学习：把 EXPLAIN 当成“读优化器给出的计划”，重点是四个字段——type、key、rows、Extra。

4. 常见坑

1. 在索引列上做函数/运算：WHERE YEAR(order_date) = 2018 用不上索引，改成 order_date >= '2018-01-01' AND order_date < '2019-01-01'。
2. 隐式类型转换：phone 是 varchar，写 WHERE phone = 13800138000 会转成数字比较，索引失效；要加引号。
3. 前导模糊：LIKE '%深圳%' 用不上 B+ 树；LIKE '深圳%' 可以。
4. OR 连接非索引列：一边没索引就可能全表扫；可拆成 UNION ALL，或给两边都建索引。
5. 联合索引缺最左列：WHERE customer_name = '张三' 用不上 (city, customer_name)。
6. 范围条件后的列：WHERE city = '北京' AND customer_name > '李' AND signup_date = DATE '2024-03-01' 中，signup_date 一般不参与缩小扫描。
7. !=、NOT IN、IS NOT NULL：可能走全表扫，取决于数据比例和优化器。
8. 深分页：LIMIT 100000, 20 要扫 100020 行。用延迟关联或游标。
9. 大事务：一次更新几十万行会长时间持锁、undo 膨胀、主从延迟；要分批提交。
10. 只看 rows 不看真实数据：统计信息过期会误判，必要时 ANALYZE TABLE。

5. 在线练习

自测：

• 能写出 MySQL 日期范围过滤，并说明为什么不用 YEAR(col) = ...。
• 能用 CASE WHEN 做条件聚合。
• 能解释 LIKE 和正则的性能差异。

6. 面试延伸

面试题：为什么 MySQL 索引用 B+ 树，不用 B 树、哈希或二叉树？

是什么：B+ 树非叶子节点只存键和指针，叶子存数据并用链表相连；B 树每个节点都存数据；哈希只支持等值；二叉树层数太高。 解决什么问题：磁盘按页读，B+ 树更矮，一次查询 IO 次数少且稳定；叶子链表让范围查询和排序友好；哈希不支持范围和排序。 我的实操边界：能讲清结构和 IO 次数估算；没有在生产环境做过索引页分裂、填充因子的调优。

面试题：最左前缀原则是什么？

是什么：联合索引 (a, b, c) 按 a、b、c 排序，查询必须从最左列开始连续匹配才能充分利用索引。 解决什么问题：解释为什么 WHERE b = ? 用不上索引、为什么范围列后面的列失效，指导联合索引的列顺序。 我的实操边界：练习里能手写联合索引并判断命中列；真实业务中索引评审、和 DBA 对齐的经验还没有。

面试题：MVCC 怎么实现？RR 下还有幻读吗？

是什么：隐藏事务 id + undo 版本链 + ReadView 判断可见性；RC 每条语句新建 ReadView，RR 事务内复用。 解决什么问题：让读不加锁也能得到一致快照，提高并发；当前读再用 next-key lock 控制幻读。 我的实操边界：能画版本链和 ReadView 判断流程；没有排查过线上长事务导致 undo 膨胀的案例。

面试题：线上一条 SQL 变慢，你怎么排查？

是什么：先看慢日志确认 SQL 和耗时，再 EXPLAIN 看 type/key/rows/Extra，最后判断是缺索引、索引失效、回表、排序还是数据量增长。 解决什么问题：避免上来就加索引；先定位瓶颈再改，改完用执行计划和耗时验证。 我的实操边界：在本地数据和 DuckDB 上做过执行计划对比；线上慢查询平台、主从延迟处理只有了解，没有实操。

7. 本章验收

• 能画出 MySQL 三层架构，说出优化器和执行器各做什么
• 能解释聚簇索引、二级索引、回表和覆盖索引，并举一个覆盖索引的例子
• 给定一个联合索引，能判断哪些 WHERE/ORDER BY 能用上、用到哪几列
• 能讲清 ACID 分别靠什么实现，RR 下快照读和当前读如何处理幻读
• 能读懂 EXPLAIN 的 type、key、rows、Extra，并说出至少 3 种索引失效场景
• 能写出深分页的两种优化写法

下一章

数仓概念与分层：从业务库走向数仓，理解 ODS、DWD、DWS、ADS 各层职责。