前言

“接口突然变慢"十有八九是慢 SQL。这篇面向运维/后端，把 MySQL 索引原理、执行计划分析和慢查询治理套路讲清楚——不需要 DBA 深度，但足够定位和解决 80% 的问题。

一、索引是什么：B+ 树

                    [ 30 | 60 ]                ← 根节点（非叶子, 只存键）
                   /     |     \
          [10|20]      [40|50]      [70|80]    ← 中间节点
          /  |  \      ...          ...
叶子节点（存完整数据或主键指针, 且有链表串联 → 范围查询快）
[5,10,15,20] ⇄ [30,35,40] ⇄ [60,65,70] ...

为什么用 B+ 树而不是二叉树/哈希：

结构 特点
二叉搜索树 树太高（百万数据 20 层 = 20 次磁盘 IO）
哈希 等值快，但不支持范围/排序
B+ 树 矮胖（3~4 层管千万级）、叶子链表利于范围扫描 ✅

聚簇索引 vs 二级索引：

聚簇索引（主键）: 叶子节点存整行数据
二级索引（普通索引）: 叶子存主键值 → 命中后还要"回表"查整行

覆盖索引: 查询列全在索引里 → 免回表（explain 出现 Using index）

二、索引的正确用法

-- 建索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 看表上的索引
SHOW INDEX FROM orders;

-- 删除
DROP INDEX idx_status_created ON orders;

最左前缀原则：联合索引 (a, b, c) 相当于建了 (a)、(a,b)、(a,b,c) 三套排序：

WHERE a = 1                    -- ✅ 用上
WHERE a = 1 AND b = 2          -- ✅ 用上 a,b
WHERE b = 2                    -- ❌ 用不上（跳过了 a）
WHERE a = 1 AND c = 3          -- ✅ 只用上 a
WHERE a = 1 AND b > 5 AND c = 3 -- ✅ a,b 用上; b 是范围后 c 失效

索引失效的经典写法：

WHERE YEAR(created_at) = 2025        -- ❌ 函数包裹列 → 全表扫
WHERE created_at >= '2025-01-01'     -- ✅ 改成范围
WHERE name LIKE '%abc'               -- ❌ 前导通配
WHERE name LIKE 'abc%'               -- ✅
WHERE phone = 13800138000            -- ❌ 隐式类型转换（phone 是 varchar）
WHERE status <> 1                    -- ❌ 不等于, 大概率放弃索引

三、explain：执行计划怎么读

EXPLAIN SELECT * FROM orders WHERE status = 'paid' AND created_at > '2025-01-01';

重点看四列：

type（访问方式, 好→差）:
  system > const > eq_ref > ref > range > index > ALL
                        │        │       │      │      └ 全表扫描（优化目标!）
                        │        │       │      └ 扫全索引
                        │        │       └ 范围扫描
                        │        └ 非唯一索引等值
                        └ 唯一索引等值/连接

key: 实际使用的索引（NULL = 没用上）
rows: 预估扫描行数（越少越好, 数量级敏感）
Extra:
  Using index        覆盖索引, 极佳 ✅
  Using where        服务层再过滤（正常）
  Using filesort     额外排序 ⚠️（考虑给 ORDER BY 加索引）
  Using temporary    临时表 ⚠️（GROUP BY 常见）

-- 对比优化前后
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at)=2025;
-- type=ALL, rows=1000000 ❌

EXPLAIN SELECT * FROM orders WHERE created_at >= '2025-01-01';
-- type=range, key=idx_created, rows=52000 ✅

四、慢查询治理流程

1. 开启慢日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 超过1秒算慢
SET GLOBAL log_queries_not_using_indexes = ON;

tail -f /var/lib/mysql/localhost-slow.log
# Query_time: 3.2 Lock_time: 0.001 Rows_sent: 1 Rows_examined: 2000000
#                                          ▲ 扫了200万行只回1行 → 典型索引缺失

2. 定位与分析

-- 当前正在跑的语句（抓现行）
SHOW PROCESSLIST;
KILL <id>;      -- 确认是失控查询再杀!

-- 表统计信息
SHOW TABLE STATUS LIKE 'orders';

3. 优化三板斧

① 加对索引:   按查询条件建联合索引, 等值列在前、范围列在后
② 改写语句:   避免 SELECT * / 函数包列 / 隐式转换
③ 结构优化:   大分页改"游标翻页", 历史数据归档分表

深度分页的坑：

SELECT * FROM orders LIMIT 1000000, 20;
-- 要先扫过前100万行! 优化: 记住上次的位置
SELECT * FROM orders WHERE id > 1000020 ORDER BY id LIMIT 20;

五、运维侧的数据库健康检查

-- 连接数与上限
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- 死锁
SHOW ENGINE INNODB STATUS\G    -- LATEST DETECTED DEADLOCK 段

-- 缓冲池命中率（< 99% 要警惕内存不足）
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - reads / read_requests

-- 最大的几张表（INFORMATION_SCHEMA）
SELECT table_name, ROUND(data_length/1024/1024) AS data_mb,
       ROUND(index_length/1024/1024) AS idx_mb
FROM information_schema.tables
WHERE table_schema = 'mydb' ORDER BY data_length DESC LIMIT 10;

# 系统层三查（呼应性能篇）
iostat -x 1 3                  # 数据盘 await/%util
ss -tan state established '( dport = :3306 )' | wc -l   # 连接数
df -h /var/lib/mysql

六、索引的代价（别无脑加）

  • 每个索引拖慢写入（INSERT/UPDATE 要维护 B+ 树）
  • 索引占磁盘（index_length 有时比数据还大）
  • 区分度低的列不值得索引（如 status 只有 0/1 两值）
  • 冗余索引清理：(a) 与 (a,b) 并存时前者通常可删

七、案例复盘

现象: 订单列表接口 P99 从 200ms 涨到 5s
排查:
  1. 慢日志 → SELECT ... WHERE user_id=? AND status=? ORDER BY created_at DESC LIMIT 20
     Query_time 4.8s, Rows_examined 1800000
  2. EXPLAIN → type=ALL, 无可用索引
  3. 优化: CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
     等值列(user_id,status)在前, 排序列(created_at)收尾
结果: type=ref, rows=23, 接口 P99 回到 180ms
复盘: 新查询模式上线前, 先看执行计划

小结

场景 动作
慢 开慢日志 → explain 看 type/rows
全表扫 按等值+排序列建联合索引
索引失效 查函数包列/前导%/隐式转换
大分页 游标式翻页
深度诊断 processlist + innodb status + iostat

本文是「数据库」系列第 1 篇。