MySQL 优化——为什么你的查询总是那么慢 / MySQL Query and Storage Optimization
📅 创建时间：2026-05-08 🏷️ 标签：#MySQL #慢查询 #EXPLAIN #索引优化 #执行计划 #锁 📚 前ore置知识：[[00-backend-overview]] [[/03-web/06-databases-and-data-access/01-mysql]]（MySQL 基础） 📚 相关知识：[[08-concurrency]]（并发安全） [[10-cache-strategy]]（缓存一致性）
场景：用户说"商品列表页加载要 5 秒"
┌─────────────────────────────────────────────────────────────┐
│ │
│ 运营后台，点击"商品列表"。 │
│ │
│ 页面加载：5.2 秒 │
│ │
│ 监控显示： │
│ 接口耗时：5.1 秒 │
│ 其中 MySQL 查询耗时：5.0 秒 │
│ │
│ EXPLAIN一看： │
│ SELECT * FROM products WHERE status = 'active' │
│ ORDER BY created_at DESC LIMIT 20 OFFSET 10000; │
│ │
│ → 全表扫描！30 万行全部扫描！ │
│ │
└─────────────────────────────────────────────────────────────┘这一章，我们从慢查询出发，理解 MySQL 的查询优化。
第1节：慢查询分析——先找到是谁
开启慢查询日志
sql
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 查看当前正在执行的查询
SHOW PROCESSLIST;
-- 或
SELECT * FROM information_schema.processlist WHERE command != 'Sleep';EXPLAIN——查询执行计划
sql
EXPLAIN SELECT
u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id
HAVING order_count > 5
ORDER BY order_count DESC
LIMIT 10;EXPLAIN 输出解读
┌─────────────────────────────────────────────────────────────┐
│ EXPLAIN 字段解读 │
├─────────────────────────────────────────────────────────────┤
│ │
│ id: 执行顺序，id 越大越先执行 │
│ │
│ select_type: 查询类型 │
│ SIMPLE：简单查询 │
│ PRIMARY：最外层查询 │
│ DERIVED：子查询（FROM 里的子查询） │
│ SUBQUERY：独立子查询 │
│ UNION：UNION 操作 │
│ │
│ type（重要！从好到坏）： │
│ const > eq_ref > ref > range > index > ALL │
│ │
│ const：主键/唯一索引等值查询（最快） │
│ eq_ref：JOIN 时，被驱动表用主键/唯一索引 │
│ ref：非唯一索引等值查询 │
│ range：索引范围查询（> < BETWEEN） │
│ index：全索引扫描（比 ALL 好，但仍需遍历索引） │
│ ALL：全表扫描（最慢，尽量避免！） │
│ │
│ key：实际使用的索引 │
│ key_len：索引长度，越短越好（节省空间） │
│ rows：预计扫描行数，越少越好 │
│ Extra：额外信息 │
│ Using filesort：文件排序（需要优化！） │
│ Using temporary：使用临时表（需要优化！） │
│ Using index：覆盖索引，无需回表 │
│ │
└─────────────────────────────────────────────────────────────┘第2节：索引失效——为什么有索引却不用
场景：商品列表页按状态查询，用了索引还是慢
sql
-- 创建了索引
CREATE INDEX idx_status_created ON products(status, created_at);
-- 查询
SELECT * FROM products
WHERE status = 'active'
AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;索引失效的常见场景
┌─────────────────────────────────────────────────────────────┐
│ 索引失效的 7 种场景 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. 函数/计算： │
│ WHERE YEAR(created_at) = 2026 ← 索引字段用了函数 │
│ ✅ WHERE created_at >= '2026-01-01' │
│ │
│ 2. 类型转换： │
│ WHERE phone = 13800138000 ← phone 是 VARCHAR， │
│ MySQL 自动 CAST │
│ ✅ WHERE phone = '13800138000' │
│ │
│ 3. 最左前缀原则（复合索引）： │
│ 复合索引：(status, created_at) │
│ ❌ WHERE created_at > '2026-01-01' ← 跳过 status │
│ ✅ WHERE status = 'active' │
│ ✅ WHERE status = 'active' AND created_at > '...' │
│ ❌ WHERE created_at > '...' AND status = 'active' │
│ ⚠️ MySQL 8.0+ 可以优化器重排，但不保证顺序 │
│ │
│ 4. OR 连接： │
│ ❌ WHERE status = 'active' OR stock = 0 ← OR 会导致索引失效 │
│ ✅ 拆成 UNION：WHERE status = 'active' UNION WHERE stock = 0 │
│ │
│ 5. LIKE 前缀匹配： │
│ ❌ WHERE name LIKE '%手机' ← 不能用 B+ 树 │
│ ✅ WHERE name LIKE '手机%' ← 可以用前缀索引 │
│ ✅ 全文索引：WHERE MATCH(name) AGAINST('手机') │
│ │
│ 6. IS NULL / IS NOT NULL： │
│ ⚠️ MySQL 5.7：IS NULL 可能走索引，IS NOT NULL 不一定│
│ ✅ 尽量给字段设默认值，避免 NULL │
│ │
│ 7. NOT IN / NOT EXISTS： │
│ ❌ WHERE status NOT IN ('deleted', 'archived') │
│ ✅ WHERE status NOT IN ('deleted', 'archived') │
│ ⚠️ 大数据量下慎用，改为 > 或 BETWEEN │
│ │
└─────────────────────────────────────────────────────────────┘第3节：分页优化——为什么越往后翻页越慢
场景：商品列表第 10000 页
sql
-- 第 1 页：快
SELECT * FROM products
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- 耗时：0.01 秒
-- 第 10000 页：慢
SELECT * FROM products
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20 OFFSET 200000;
-- 耗时：2.5 秒
-- 为什么慢？
-- OFFSET 200000：MySQL 先扫描前 200020 行，丢弃前 200000 行
-- 数据量越大，OFFSET 越大，越慢方案一：延迟关联
sql
-- 优化：先查 ID，再关联查询
SELECT p.*
FROM products p
INNER JOIN (
SELECT id FROM products
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20 OFFSET 200000
) AS t ON p.id = t.id;
-- 优势：子查询只查主键 ID（不回表），快很多方案二：游标分页（推荐）
sql
-- 上一页最后一条的 created_at = 2026-05-01 12:00:00, id = 12345
SELECT * FROM products
WHERE status = 'active'
AND (created_at < '2026-05-01 12:00:00'
OR (created_at = '2026-05-01 12:00:00' AND id < 12345))
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 优势：
-- 1. 每页查询都走索引，耗时稳定
-- 2. 数据不新增时，不会有遗漏/重复
-- 3. 适合超深分页场景
-- 缺点：
-- 1. 不能跳页（只能上一页/下一页）
-- 2. 需要前端传递上一页的游标第4节：JOIN 优化——小表驱动大表
问题：LEFT JOIN 的顺序影响性能
sql
-- 错误写法：大表在前面
SELECT * FROM orders o
LEFT JOIN users u ON o.user_id = u.id -- orders 是大表
WHERE u.city = '北京';
-- MySQL 先扫描 orders，再去 users 匹配 → 慢
-- 正确写法：小表在前面
SELECT * FROM users u
LEFT JOIN orders o ON o.user_id = u.id -- users 是小表
WHERE u.city = '北京';
-- MySQL 先扫描 users，再用 id 找 orders → 快
-- 或者用 STRAIGHT_JOIN 强制顺序
SELECT STRAIGHT_JOIN *
FROM users u
JOIN orders o ON o.user_id = u.id;
-- 强制按照 SQL 写的顺序执行多表 JOIN 的优化原则
┌─────────────────────────────────────────────────────────────┐
│ 多表 JOIN 优化原则 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. 驱动表选择： │
│ 驱动表 = 先扫描的表（尽量选小表） │
│ 驱动表的每行都要去被驱动表匹配 │
│ → 驱动表越小，总匹配次数越少 │
│ │
│ 2. 尽量在被驱动表的 JOIN 字段上建索引： │
│ users.id 是主键（自带索引） │
│ orders.user_id 建了索引 → JOIN 时可以用索引 │
│ │
│ 3. 避免 SELECT *： │
│ 只查需要的字段 │
│ → 减少回表次数 │
│ → 减少网络传输量 │
│ │
│ 4. JOIN 字段类型要一致： │
│ ❌ WHERE o.user_id = u.phone ← INT JOIN VARCHAR │
│ ✅ WHERE o.user_id = u.id ← INT JOIN INT │
│ │
└─────────────────────────────────────────────────────────────┘第5节：锁——并发下的数据安全
场景：两个人同时买同一件商品
sql
-- 线程 A：查库存
SELECT stock FROM products WHERE id = 1; -- stock = 1
-- 线程 B：查库存
SELECT stock FROM products WHERE id = 1; -- stock = 1
-- 线程 A：扣库存
UPDATE products SET stock = stock - 1 WHERE id = 1; -- stock = 0
-- 线程 B：扣库存
UPDATE products SET stock = stock - 1 WHERE id = 1; -- stock = -1，超卖了！锁的分类
┌─────────────────────────────────────────────────────────────┐
│ MySQL 锁分类 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 按粒度： │
│ • 表锁：锁整张表（MyISAM 默认） │
│ • 行锁：只锁一行（InnoDB 支持） │
│ │
│ 按类型： │
│ • 共享锁（S）：读锁，多个事务可以同时持有 │
│ • 排他锁（X）：写锁，独占 │
│ │
│ InnoDB 行锁的四种类型： │
│ • Record Lock：只锁主键/唯一索引对应的记录 │
│ • Gap Lock：锁两个索引值之间的间隙（防止插入新数据） │
│ • Next-Key Lock：Record Lock + Gap Lock（临键锁） │
│ • Insert Intention Lock：插入新数据前的间隙锁 │
│ │
│ 隔离级别 vs 锁行为： │
│ • READ UNCOMMITTED：不加锁（脏读） │
│ • READ COMMITTED：Record Lock（不可重复读） │
│ • REPEATABLE READ（默认）：Next-Key Lock（解决幻读） │
│ • SERIALIZABLE：全表锁 │
│ │
└─────────────────────────────────────────────────────────────┘悲观锁 vs 乐观锁
sql
-- 悲观锁：先加锁再操作（适合写多的场景）
BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE; -- 加排他锁
-- 其他事务的 SELECT FOR UPDATE 会阻塞
IF stock > 0 THEN
UPDATE products SET stock = stock - 1 WHERE id = 1;
END IF;
COMMIT;
-- 问题：并发时，后面的事务要等前面的事务释放锁
-- 乐观锁：版本号控制（适合读多的场景）
-- 表设计：ALTER TABLE products ADD COLUMN version INT DEFAULT 0;
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1
AND version = 5; -- 只有版本号是 5 才能更新
-- 影响行数 = 0？说明其他事务先更新了，version 变了
-- 影响行数 = 1？说明更新成功升华：MySQL 优化的顺序
┌─────────────────────────────────────────────────────────────┐
│ MySQL 优化顺序（从高到低） │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. 索引优化（效果最明显） │
│ → EXPLAIN 找全表扫描，加索引 │
│ │
│ 2. SQL 改写 │
│ → 避免 SELECT *、减少 JOIN、改写分页 │
│ │
│ 3. 表结构优化 │
│ → 字段类型、垂直拆分、冷热分离 │
│ │
│ 4. 架构优化 │
│ → 读写分离、分库分表、引入缓存 │
│ │
│ 一句话：先问自己"索引建对了吗"，再想"架构要不要改"。 │
│ │
└─────────────────────────────────────────────────────────────┘"AI 可查 vs 必须理解"清单
AI 可查：
✅ EXPLAIN 字段的每个值是什么意思
✅ 各种 SQL 函数对索引的影响
✅ 具体的 JOIN 语法优化
必须理解：
🔴 type 字段从好到坏的排序（const > ... > ALL）
🔴 最左前缀原则和 OR 导致索引失效的原因
🔴 OFFSET 分页慢的原因和游标分页的原理
🔴 乐观锁 vs 悲观锁的适用场景
🔴 RR 隔离级别下 Next-Key Lock 如何解决幻读学习状态：🟡 开始学习