索引优化

问题：查询越来越慢

链接数突破 5 万条后，有些查询要 200ms。用户开始抱怨短链接打开慢。

我打开监控面板，看到了不想看到的一幕：

查询延迟：50ms → 100ms → 200ms
数据库 CPU：30% → 60% → 85%
慢查询日志：每天都在增长

最频繁的查询是用户访问短链接：

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。

这条查询占了总请求的 80%，但平均耗时已经到了 150ms。对于一个重定向操作来说，这太慢了。

我查了下数据库表结构：

数据设计要点

• 核心是在 url_mapping 里保存业务事实，而不是把规则散落在应用逻辑里。
• 更新操作通常意味着链接状态变化，要同步考虑缓存刷新和审计记录。
• 关键字段包括 id、short_code、long_url、user_id、click_count、created_at、updated_at，它们决定了后续查询和管理能力。

表设计没问题，但查询越来越慢。问题出在哪里？

索引初体验

我之前从来没建过索引。对，这是真实的——我写过很多 SQL，但从未认真学过索引。

搜了一下教程，看到一句话：索引就像书的目录。

这个比喻我懂。没有目录，找内容要从第一页翻到最后一页；有了目录，直接翻到对应页码。

数据库也是一样：

• 没有索引：全表扫描，逐行查找
• 有索引：快速定位，直接读取

第一次使用 EXPLAIN

我在网上看到可以用 EXPLAIN 分析查询，试了一下：

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。
• 这里要读的是执行计划结论：有没有命中索引、扫描行数是否可控、是否出现全表扫描。

结果让我有点慌：

+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| 1  | SIMPLE      | url_mapping | ALL  | NULL          | NULL | NULL    | NULL | 50000 | Using where |
+----+-------------+-------------+------+---------------+------+-------+------+-------+-------------+

我看不懂这些字段，但有一行特别显眼：type: ALL，rows: 50000。

查了一下资料：

• type: ALL = 全表扫描，最差的访问类型
• rows: 50000 = 预估扫描 5 万行

难怪查询慢，每次都要扫描全表！

创建第一个索引

按照教程，我在 short_code 上加了索引：

数据设计要点

• 这是一次表结构演进：随着链接管理能力增加，把新状态、新时间点或新归属关系补进数据模型。
• 索引服务于高频查询，重点关注 uk_short_code。

创建索引只用了几秒钟。然后再执行查询：

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。
• 这里要读的是执行计划结论：有没有命中索引、扫描行数是否可控、是否出现全表扫描。

这次的结果让我松了口气：

+----+-------------+-------------+-------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table       | type  | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+-------------+-------+----------------+----------------+---------+-------+------+-------+
| 1  | SIMPLE      | url_mapping | const | uk_short_code  | uk_short_code  | 42      | const | 1    |       |
+----+-------------+-------------+-------+----------------+----------------+---------+-------+------+-------+

变化很明显：

• type: const = 常量查找，最优的访问类型
• rows: 1 = 只扫描 1 行

查询时间从 150ms 降到了 5ms，提升了 30 倍。

我第一次尝到了索引的甜头。

深入理解索引

索引不是加了就快，加错了反而更慢。这是我深入学习后的理解。

B+ 树：索引的数据结构

索引为什么快？因为它用的是 B+ 树。

我之前只知道二叉树，但数据库用的 B+ 树有个关键特点：所有数据都在叶子节点，且叶子节点通过指针连成链表。

这有什么好处？

查找 short_code = 'c3d4e5'：

1. 从根节点开始
   根节点: [a1b2c3, e6f7g8, ...]
           ↓
   中间节点: [a1b2c3, b2c3d4, c3d4e5, d4e5f6]
           ↓
   叶子节点: [c3d4e5 → https://example.com/page3]

2. 只需要 3 次磁盘 I/O（树高度）
3. 找到数据直接返回

对比全表扫描：

• 全表扫描：50000 行 × 10ms = 500 秒（夸张了，但原理如此）
• B+ 树查找：3 次 I/O × 10ms = 30ms

而且，B+ 树的高度很低。对于 1000 万数据，B+ 树通常只有 3-4 层。这意味着最多 3-4 次磁盘 I/O 就能找到任何数据。

聚簇索引 vs 非聚簇索引

我遇到了一个概念：聚簇索引。什么意思？

聚簇索引：数据本身就存储在索引的叶子节点。InnoDB 的主键就是聚簇索引。

主键索引（聚簇）：
叶子节点存储完整行数据
[id=1 → 完整行数据：short_code, long_url, user_id, ...]

非聚簇索引：叶子节点存储的是主键值，不是完整数据。

普通索引（非聚簇）：
叶子节点存储主键值
[short_code='a1b2c3' → id=1]
                         ↓
                   需要回表查询完整数据

这解释了一个现象：用普通索引查询时，需要回表。

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。

两次查找，性能有损耗。

覆盖索引：避免回表

有什么办法可以不回表？覆盖索引。

如果查询的字段都在索引中，就不需要回表。

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。

这个技巧很实用。对于高频查询，设计覆盖索引可以提升性能。

数据设计要点

• 索引服务于高频查询，重点关注 idx_user_created。
• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。

这样查询就完全在索引中完成，不需要回表读取完整数据行。

优化实践：给 urls 表加索引

理解了原理，我开始给 urls 表设计完整的索引方案。

分析查询场景

我统计了系统中的主要查询：

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。

设计索引方案

根据查询场景，我设计了以下索引：

数据设计要点

• 核心是在 url_mapping 里保存业务事实，而不是把规则散落在应用逻辑里。
• 更新操作通常意味着链接状态变化，要同步考虑缓存刷新和审计记录。
• 关键字段包括 id、short_code、long_url、user_id、click_count、created_at、updated_at，它们决定了后续查询和管理能力。

逐个优化

优化场景 1：用户访问短链接

数据设计要点

• 这是一次表结构演进：随着链接管理能力增加，把新状态、新时间点或新归属关系补进数据模型。
• 索引服务于高频查询，重点关注 uk_short_code。
• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。
• 这里要读的是执行计划结论：有没有命中索引、扫描行数是否可控、是否出现全表扫描。

优化场景 2：用户查看链接列表

数据设计要点

• 这是一次表结构演进：随着链接管理能力增加，把新状态、新时间点或新归属关系补进数据模型。
• 索引服务于高频查询，重点关注 idx_user_created。
• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。
• 这里要读的是执行计划结论：有没有命中索引、扫描行数是否可控、是否出现全表扫描。

这里有个关键：组合索引要遵循最左前缀原则。

数据设计要点

• 这里关注数据模型和约束关系，不需要记住具体语法。

优化场景 3：按时间范围查询

数据设计要点

• 这是一次表结构演进：随着链接管理能力增加，把新状态、新时间点或新归属关系补进数据模型。
• 索引服务于高频查询，重点关注 idx_created_at。
• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。
• 这里要读的是执行计划结论：有没有命中索引、扫描行数是否可控、是否出现全表扫描。

优化场景 4：长 URL 去重

数据设计要点

• 这是一次表结构演进：随着链接管理能力增加，把新状态、新时间点或新归属关系补进数据模型。
• 索引服务于高频查询，重点关注 idx_long_url。

前缀长度的选择有讲究：

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。

优化效果

索引加完后，查询性能全面提升：

看到 EXPLAIN 输出从 ALL 变成 ref 的那一刻，我理解了为什么 DBA 是一个专门的职业。

索引维护：学会克制

索引不是免费的。每个索引都会拖慢写入速度。

我查了一下索引的空间占用：

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。

索引占了数据大小的 77%，这个代价可不小。

写入性能的影响

每插入一条记录，数据库需要：

1. 写入数据（1 次）
2. 更新所有索引（4 次）

总共 5 次写入操作。

数据设计要点

• 这里关注数据模型和约束关系，不需要记住具体语法。

索引越多，写入越慢。

删除无用索引

我还发现了一个问题：有些索引从来没被用过。

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。

idx_status 是我之前测试时加的，但实际查询很少用 status 字段过滤。果断删除：

数据设计要点

• 这是一次表结构演进：随着链接管理能力增加，把新状态、新时间点或新归属关系补进数据模型。

我学会了克制，只加必要的索引。

避免索引失效

还有一个坑：索引建了，但查询没用上。

数据设计要点

• 关键字段包括 WHERE，它们决定了后续查询和管理能力。

每次写查询，我都会用 EXPLAIN 验证一下索引是否生效。

EXPLAIN 实战手册

EXPLAIN 成了我最常用的工具。

关键字段解读

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。
• 这里要读的是执行计划结论：有没有命中索引、扫描行数是否可控、是否出现全表扫描。

+----+-------------+-------------+-------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table       | type  | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+-------------+-------+----------------+----------------+---------+-------+------+-------+

我重点看这几个字段：

type（访问类型）：最重要的指标

rows（预估扫描行数）：越小越好

Extra（额外信息）：

• Using index：覆盖索引，好 ✅
• Using where：用 WHERE 过滤，正常
• Using filesort：额外排序，要优化 ⚠️
• Using temporary：使用临时表，要优化 ⚠️

实战案例

案例 1：全表扫描

数据设计要点

• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。
• 这里要读的是执行计划结论：有没有命中索引、扫描行数是否可控、是否出现全表扫描。

案例 2：文件排序

数据设计要点

• 索引服务于高频查询，重点关注 idx_user_click。
• 查询目标是用短码快速找到目标链接、状态或统计结果，避免在跳转路径上做大范围扫描。
• 这里要读的是执行计划结论：有没有命中索引、扫描行数是否可控、是否出现全表扫描。