Java 学习网站

主题

MySQL 调优基础 ​

Java 中级 · 存储与缓存

线上慢查询大多源于索引没走对或SQL 写法拖累。本篇从 InnoDB 的 B+ 树索引结构说起，讲清为什么索引能加速、回表是什么、最左前缀为什么成立、EXPLAIN 每个字段怎么看，最后给出可落地的优化套路。

一、B+ 树：InnoDB 索引的底层结构 ​

1.1 为什么是 B+ 树 ​

结构查找复杂度问题
二叉搜索树O(log n)可能退化成链表
红黑树O(log n)树高仍偏高，磁盘 IO 次数多
B 树O(log n)非叶子节点也存数据，单页能放的索引少
B+ 树O(log n)非叶子只存键、数据全在叶子、叶子有链表 ✅

B+ 树的两个关键优势：

  1. 非叶子节点只存键值，一页（16KB）能放几百上千个键，树更矮（3-4 层可存千万级数据），磁盘 IO 次数极少。
  2. 叶子节点用双向链表串联，天然支持范围查询和排序（BETWEEN、ORDER BY）。

1.2 聚簇索引与二级索引 ​

  • 聚簇索引（主键索引）：叶子节点存整行数据。所以 InnoDB 表必须有序，这就是「主键自增」最佳实践的原因。
  • 二级索引（普通索引）：叶子节点存索引列 + 主键值。
sql
-- 在 name 上建索引
CREATE INDEX idx_name ON user(name);

-- 查询：先在 idx_name 找到主键 id，再回聚簇索引取整行 → 这一步叫「回表」
SELECT * FROM user WHERE name = 'Tom';

覆盖索引：消灭回表的利器

如果查询的列都在索引里，就无需回表。例如 SELECT name FROM user WHERE name='Tom'，idx_name 已含 name（查询列）+ id（主键），直接返回。 实践：把常用查询列放进联合索引，或显式只查索引内的列，能显著提速。

二、最左前缀原则 ​

联合索引 idx(a, b, c) 的排序是先按 a、再按 b、再按 c。因此查询条件必须从最左列开始连续匹配才能用上。

查询条件是否用索引用了哪些列
a=1✅a
a=1 AND b=2✅a, b
a=1 AND b=2 AND c=3✅a, b, c
b=2（跳过 a）❌全表扫描
a=1 AND c=3（跳过 b）⚠️只用到 a

范围查询会「中断」后续列

WHERE a=1 AND b>2 AND c=3：a 用到索引，b 用了范围查找，c 就用不上索引了（因为 b 是范围，后续无序）。所以联合索引列顺序要把等值列放前、范围列放后。

三、EXPLAIN：看懂执行计划 ​

sql
EXPLAIN SELECT * FROM user WHERE name = 'Tom' AND age > 18;
字段含义关注点
id查询序号越大越先执行
select_type查询类型SIMPLE/PRIMARY/SUBQUERY
type访问类型（最重要）性能：system > const > eq_ref > ref > range > index > ALL
possible_keys可能用的索引—
key实际用的索引为 NULL 说明没走索引
key_len索引使用长度判断联合索引用了几列
rows预估扫描行数越小越好
Extra额外信息Using index(覆盖索引) / Using filesort(额外排序) / Using temporary(临时表)

看到这些就要优化

  • type = ALL：全表扫描，加索引。
  • Extra = Using filesort：文件排序，考虑给 ORDER BY 列加索引。
  • Extra = Using temporary：用了临时表，常见于 GROUP BY 无索引列。
  • rows 巨大但实际返回很少：索引选择性差。

四、常见慢 SQL 与优化 ​

sql
-- ❌ 索引失效：对索引列做运算
SELECT * FROM user WHERE YEAR(create_time) = 2024;
-- ✅ 改为范围
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- ❌ 索引失效：隐式类型转换（phone 是 varchar，传数字）
SELECT * FROM user WHERE phone = 13800138000;
-- ✅ 传字符串
SELECT * FROM user WHERE phone = '13800138000';

-- ❌ 前导模糊查询，用不上索引
SELECT * FROM user WHERE name LIKE '%Tom';
-- ✅ 后缀模糊可以用
SELECT * FROM user WHERE name LIKE 'Tom%';

-- ❌ SELECT *：可能无法覆盖索引，触发回表
SELECT * FROM user WHERE name = 'Tom';
-- ✅ 只取需要的列
SELECT id, name FROM user WHERE name = 'Tom';

-- ❌ 深分页：扫描并丢弃前 100 万行
SELECT * FROM user ORDER BY id LIMIT 1000000, 10;
-- ✅ 游标/延迟关联
SELECT * FROM user WHERE id > 1000000 ORDER BY id LIMIT 10;

4.1 索引失效的常见原因 ​

text
1. 对索引列使用函数或运算        WHERE YEAR(dt)=...
2. 隐式类型转换（字符串 vs 数字）
3. LIKE 以 % 开头
4. OR 两侧有一侧无索引
5. 联合索引违反最左前缀
6. 使用 != / NOT IN 有时放弃索引（优化器判断全表更快）
7. 索引列参与计算或排序方向不一致

五、其他调优要点 ​

方向做法
索引为 WHERE/ORDER BY/JOIN 列建索引；区分度高的列优先
表结构字段尽量 NOT NULL；用合适类型（int vs varchar）
分页游标/延迟关联；避免深分页
JOIN小表驱动大表；关联列建索引；别 JOIN 太多表
事务事务尽量短；避免大事务锁表
架构读写分离、分库分表（见高级篇）

索引不是越多越好

每个索引都要占空间，且增删改要维护所有索引，写性能下降。一般单表索引不超过 5-6 个，优先联合索引覆盖多条件。

六、最佳实践 ​

  • 建索引前先 EXPLAIN，看 type、key、Extra 三个字段。
  • 联合索引把等值列放前、范围列放后，遵循最左前缀。
  • 能用覆盖索引就别回表（SELECT 只取索引内列）。
  • 避免对索引列做运算/函数/隐式转换。
  • 深分页用游标（WHERE id > ? LIMIT n）。
  • 慢查询日志：slow_query_log = ON + long_query_time = 1，定期分析 pt-query-digest。

📚 官方文档与参考资料 ​

✏️ 练习与自测 ​

  1. 为什么 InnoDB 用 B+ 树而不是 B 树/红黑树？B+ 树的叶子链表有什么用？
  2. 什么是回表？覆盖索引为什么能避免回表？
  3. 联合索引 idx(a,b,c)，以下哪些查询能用上索引？a=1、b=2、a=1 AND c=3、a=1 AND b>2 AND c=3。
  4. 给出一段慢 SQL，用 EXPLAIN 分析并写出优化后的 SQL（至少涉及索引失效与深分页两个点）。

本节小结

MySQL 调优的根是 B+ 树索引：非叶子只存键（树矮）、叶子存数据+链表（范围快）。二级索引要回表，覆盖索引可免；最左前缀决定联合索引能用几列；EXPLAIN 看 type/key/Extra 三个字段就能定位大部分问题。索引列别做运算、别隐式转换、别前置 %、深分页用游标。