知识库

Appearance

索引与执行计划 ​

MySQL 查询变慢时，通常不是"数据库压力大"这么笼统。需要搞清楚的是：这条 SQL 扫描了多少行、走的是什么索引、有没有回表、有没有额外排序。EXPLAIN 和索引设计就是解决这些问题的工具。

一、B+Tree 索引到底在做什么 ​

InnoDB 默认的索引结构是 B+Tree。它不是给表加一个"加速开关"，而是提供了一种更少扫描数据的查找路径。

B+Tree 是一棵有序的多叉树。中间节点存的是键值范围和指向下一层的指针，叶子节点存的是完整键值和数据（聚簇索引）或主键值（二级索引）。叶子节点之间还有双向链表连接——所以等值查询可以沿着树快速定位，范围查询可以从一个叶子节点顺着链表往后扫。

InnoDB 有两种索引，差别在叶子节点存什么：

类型叶子节点里存什么影响
聚簇索引主键 + 整行数据按主键找到叶子节点就能拿到所有列
二级索引索引列 + 主键值找到主键后，可能还要回聚簇索引取其他列

所以"回表"就是：先在二级索引找到主键值，再拿主键值去聚簇索引取整行。如果查询需要的列全部在二级索引里（覆盖索引），就不需要回表。生产表的主键太长（比如 UUID 或长 VARCHAR），会让所有二级索引的叶子节点变大——因为每个二级索引都要存主键值。

二、准备一张示例表 ​

sql
CREATE TABLE access_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  app_name VARCHAR(64) NOT NULL,
  status_code INT NOT NULL,
  client_ip VARCHAR(45) NOT NULL,
  uri VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_user_time (user_id, created_at),
  KEY idx_app_status_time (app_name, status_code, created_at),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三个索引各自针对不同的查询模式：

  • idx_user_time — 查某个用户在某个时间段内的访问记录
  • idx_app_status_time — 查某个应用、某个状态码、某个时间范围内的记录
  • idx_created_at — 只按时间范围查，不涉及用户或应用

一条典型的查询：

sql
SELECT id, uri, created_at
FROM access_logs
WHERE user_id = 1001
  AND created_at >= '2026-05-01'
  AND created_at <  '2026-06-01'
ORDER BY created_at DESC
LIMIT 20;

这条查询适合 (user_id, created_at) 联合索引——等值条件 user_id 先定位，范围条件 created_at 沿叶子节点顺序扫，排序也能吃到索引的顺序。

三、联合索引的最左前缀原则 ​

联合索引遵循最左前缀：索引 (A, B, C) 相当于建了 (A)、(A,B)、(A,B,C) 的索引效果，但不能替代 (B) 或 (C) 单列索引。

(user_id, created_at) 索引能支持：

sql
WHERE user_id = 1001                                       -- 用到索引
WHERE user_id = 1001 AND created_at >= '2026-05-01'        -- 用到索引

但不能很好支持只按 created_at 查：

sql
WHERE created_at >= '2026-05-01'                           -- 用不上这个联合索引

如果业务里又经常按创建时间扫全表日志，就需要单独给 created_at 建一个索引。

联合索引列的顺序一般这样考虑：

因素放在哪
等值条件列放前面
范围条件列放在等值条件后面
排序用到的列尽量和索引顺序一致
区分度很低的列单独放前面收益有限

具体例子——这个索引：

sql
KEY idx_app_status_time (app_name, status_code, created_at)

适合的查询模式：

sql
WHERE app_name = 'api'
  AND status_code = 500
  AND created_at >= '2026-05-01'

app_name 和 status_code 是等值条件放前面，created_at 是范围条件放后面。如果反过来索引是 (created_at, app_name, status_code)，范围条件在前面，后面的等值条件就用不上了。

四、EXPLAIN——看执行计划 ​

在 SQL 前面加 EXPLAIN，查看 MySQL 如何打算执行它：

sql
EXPLAIN
SELECT id, uri, created_at
FROM access_logs
WHERE user_id = 1001
  AND created_at >= '2026-05-01'
  AND created_at <  '2026-06-01'
ORDER BY created_at DESC
LIMIT 20;

重点看这几个字段：

字段含义怎么判断好坏
type访问类型const > ref > range > index > ALL
possible_keys优化器认为可以用的索引为空说明没有索引可用
key实际选用的索引为 NULL 说明走了全表扫描
key_len用到的索引长度和联合索引的列匹配程度有关
rows预估扫描行数这个值很大而最终只取少量行时要重点关注
filtered扫描后过滤的比例比例很小说明索引过滤效果差
Extra额外信息看有没有 filesort、temporary、using index

访问类型从好到差：

type含义例子
const常量级，主键或唯一索引的等值查询WHERE id = 1
ref非唯一索引等值匹配WHERE user_id = 1001
range范围扫描WHERE created_at >= '...' AND created_at < '...'
index扫描整个索引全索引扫描，比 ALL 好但也不理想
ALL全表扫描逐行扫数据页，大表高频查询里要重点处理

小表走 ALL 可能很正常——几千行的表全扫比走索引再回表还快。大表、高频查询出现 ALL 才需要重点处理。

MySQL 8.0 支持 EXPLAIN ANALYZE——它会真的执行 SQL，给出每个步骤的实际耗时和行数：

sql
EXPLAIN ANALYZE
SELECT id, uri, created_at
FROM access_logs
WHERE user_id = 1001
  AND created_at >= '2026-05-01'
  AND created_at <  '2026-06-01'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN 是预估的，EXPLAIN ANALYZE 是实际跑出来的。因为会真正执行，重查询适合在从库或测试环境上跑。

五、Extra 里的关键信息 ​

Extra 字段里几个常见内容：

Extra是什么意思要不要处理
Using index覆盖索引，不需要回表好的状态
Using where存储引擎返回后 Server 层还要过滤看过滤量有多少
Using filesort需要额外排序，排序吃不到索引如果数据量大要关注
Using temporary需要临时表来存中间结果通常是 GROUP BY/DISTINCT 导致
Using index conditionICP 索引条件下推，在引擎层就做一部分过滤8.0 常见，减少回表次数

覆盖索引的例子——查询的列全在索引里：

sql
SELECT user_id, created_at
FROM access_logs
WHERE user_id = 1001
  AND created_at >= '2026-05-01';
-- 这两个列都在 idx_user_time 里，Extra 可能显示 Using index

回表的例子——查了索引里没有的列：

sql
SELECT user_id, uri, created_at
FROM access_logs
WHERE user_id = 1001
  AND created_at >= '2026-05-01';
-- uri 不在 idx_user_time 里，需要拿主键回到聚簇索引取 uri

六、索引失效的常见原因 ​

不是建了索引就一定会用到。以下几种方式会让索引失效：

对索引列做函数或运算 ​

sql
WHERE DATE(created_at) = '2026-05-21'

DATE() 包裹了索引列，优化器没法用索引。改成范围条件就正常了：

sql
WHERE created_at >= '2026-05-21'
  AND created_at <  '2026-05-22'

隐式类型转换 ​

字段是字符串类型，但传了数字：

sql
WHERE phone = 13800138000     -- phone 是 VARCHAR

MySQL 会做类型转换，可能不走索引。改成跟字段类型一致的写法：

sql
WHERE phone = '13800138000'

LIKE 前置通配符 ​

sql
WHERE uri LIKE '%/api/user%'

BTree 索引没法用前置模糊匹配。LIKE '/api/user%' 前缀固定时可以走索引。

联合索引中范围条件后面的列 ​

索引是 (app_name, created_at, status_code)，查询是：

sql
WHERE app_name = 'api'
  AND created_at >= '2026-05-01'
  AND status_code = 500

created_at 是范围条件，后面的 status_code 就用不上了。更适合的索引顺序是 (app_name, status_code, created_at)——等值条件放前面。

七、慢查询日志 ​

配置开启慢日志：

ini
[mysqld]
slow_query_log=ON
slow_query_log_file=/data/mysql/logs/slow.log
long_query_time=1

long_query_time 要看业务来定。接口库设 0.5 秒可能都嫌慢；离线报表库设 5 秒可能很正常。

看慢日志内容：

bash
tail -f /data/mysql/logs/slow.log

用 mysqldumpslow 做简单聚合，看最耗时的 SQL 模式：

bash
mysqldumpslow -s t -t 10 /data/mysql/logs/slow.log

-s t 按总时间排序，-t 10 只看前 10 条。

生产环境更常用 pt-query-digest 这类工具分析慢日志，但最基础的判断还是不离开 SQL 本身、索引和 EXPLAIN。

判断一条慢 SQL 时，Rows_examined（扫描了多少行）和 Rows_sent（返回了多少行）的对比很关键：

  • 扫描几万行、返回几十行：访问路径可能不对，优先看索引
  • 扫描几十万行、返回几万行：业务本身取的数据就多，不是单纯加索引能解决的

八、常见查询场景的索引选择 ​

查询场景索引参考为什么
按主键查单行主键索引已有聚簇索引直接定位
按用户查最近记录(user_id, created_at)等值定位 + 范围顺序扫
按状态查待处理任务(status, created_at)要看状态区分度，高区分度放前面
按业务键防重UNIQUE KEY唯一索引自动阻止重复插入
分页翻到很后面避免 LIMIT 100000, 20深分页要扫过前面所有行才能取到后面的

深分页的问题是：LIMIT 100000, 20 意味着 MySQL 先要扫过前面的 100000 行，才能取到想要的 20 行。改成基于上一次最大 ID 的游标方式，每次只扫需要的那一段：

sql
-- 避免这种
SELECT id, uri FROM access_logs ORDER BY id LIMIT 100000, 20;

-- 改这种（前提是 id 单调递增）
SELECT id, uri FROM access_logs
WHERE id > 100000
ORDER BY id LIMIT 20;

索引不是免费的——每个二级索引都会让 INSERT、UPDATE、DELETE 多维护一个结构，还占用磁盘空间。写多读少的表，索引要更克制。

九、慢查询排查——不只是 SQL ​

慢 SQL 只是一个入口。排查的完整链路应该包括：

步骤看什么
慢日志Query_time、Lock_time、Rows_examined、Rows_sent
当前线程状态是不是在锁等待、排序、创建临时表
EXPLAINtype、key、rows、Extra
表结构和索引字段类型是否合理、索引列顺序、统计信息是否更新
业务语义能不能缩小时间范围、减少返回列、改分页方式

补索引能解决一部分问题，但不是全部。WHERE DATE(created_at)、深分页、一次拉几十万行、前置模糊匹配——这些用索引解决不了，要改 SQL 或业务查询方式。故障现场常见的临时处理是加索引、更新统计信息或限制入口，但后续还是要把 SQL 改到索引能正常工作的形态。