索引优化

问题：查询越来越慢

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

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

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

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

SELECT long_url FROM url_mapping WHERE short_code = 'a1b2c3';

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

我查了下数据库表结构：

CREATE TABLE url_mapping (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    short_code VARCHAR(10) NOT NULL,
    long_url VARCHAR(2048) NOT NULL,
    user_id BIGINT DEFAULT NULL,
    click_count INT UNSIGNED DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

索引初体验

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

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

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

数据库也是一样：

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

第一次使用 EXPLAIN

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

EXPLAIN SELECT long_url FROM url_mapping WHERE short_code = 'a1b2c3';

结果让我有点慌：

+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| 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 上加了索引：

-- 创建唯一索引（短链接码必须唯一）
ALTER TABLE url_mapping ADD UNIQUE KEY uk_short_code (short_code);

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

EXPLAIN SELECT long_url FROM url_mapping WHERE short_code = 'a1b2c3';

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

+----+-------------+-------------+-------+----------------+----------------+---------+-------+------+-------+
| 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]
                         ↓
                   需要回表查询完整数据

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

-- 查询所有字段，需要回表
SELECT * FROM url_mapping WHERE short_code = 'a1b2c3';

-- 执行过程：
-- 1. 在 uk_short_code 索引中找到 short_code 对应的主键 id
-- 2. 用 id 回表查询完整行数据
-- 3. 返回所有字段

两次查找，性能有损耗。

覆盖索引：避免回表

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

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

-- 假设有组合索引：(user_id, created_at)
-- 查询只需要索引字段，不回表
SELECT user_id, created_at FROM url_mapping WHERE user_id = 12345;
-- Extra: Using index ✅ 覆盖索引

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

-- 高频查询：用户查看自己的链接列表
SELECT id, short_code, created_at 
FROM url_mapping 
WHERE user_id = 12345 
ORDER BY created_at DESC 
LIMIT 20;

-- 设计覆盖索引（包含查询的所有字段）
CREATE INDEX idx_user_created ON url_mapping(user_id, created_at, short_code);

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

优化实践：给 urls 表加索引

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

分析查询场景

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

-- 场景 1：用户访问短链接（最高频，占 80%）
SELECT long_url FROM url_mapping WHERE short_code = 'a1b2c3';

-- 场景 2：用户查看自己的链接列表（高频，占 15%）
SELECT * FROM url_mapping WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;

-- 场景 3：管理员按时间范围查询（低频，占 4%）
SELECT * FROM url_mapping WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

-- 场景 4：创建短链接时去重（低频，占 1%）
SELECT short_code FROM url_mapping WHERE long_url = 'https://example.com/very/long/url';

设计索引方案

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

CREATE TABLE url_mapping (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    short_code VARCHAR(10) NOT NULL,
    long_url VARCHAR(2048) NOT NULL,
    user_id BIGINT DEFAULT NULL,
    click_count INT UNSIGNED DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 索引设计
    UNIQUE KEY uk_short_code (short_code),           -- 场景 1
    KEY idx_user_created (user_id, created_at),      -- 场景 2
    KEY idx_created_at (created_at),                 -- 场景 3
    KEY idx_long_url (long_url(255))                 -- 场景 4（前缀索引）
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

逐个优化

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

-- 创建唯一索引
ALTER TABLE url_mapping ADD UNIQUE KEY uk_short_code (short_code);

-- EXPLAIN 结果
EXPLAIN SELECT long_url FROM url_mapping WHERE short_code = 'a1b2c3';
-- type: const
-- rows: 1
-- 查询时间：200ms → 5ms

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

-- 创建组合索引
ALTER TABLE url_mapping ADD KEY idx_user_created (user_id, created_at);

-- EXPLAIN 结果
EXPLAIN SELECT * FROM url_mapping WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 20;
-- type: ref
-- rows: 50
-- 查询时间：150ms → 8ms

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

-- ✅ 能用到索引
WHERE user_id = 12345                              -- 用到索引第一列
WHERE user_id = 12345 AND created_at > '2024-01-01'  -- 用到索引两列
WHERE user_id = 12345 ORDER BY created_at DESC       -- 用到索引，排序也走索引

-- ❌ 用不到索引
WHERE created_at > '2024-01-01'                    -- 跳过了 user_id

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

-- 创建单列索引
ALTER TABLE url_mapping ADD KEY idx_created_at (created_at);

-- EXPLAIN 结果
EXPLAIN SELECT * FROM url_mapping 
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
-- type: range
-- rows: 50000
-- 查询时间：500ms → 50ms

优化场景 4：长 URL 去重

-- 创建前缀索引
ALTER TABLE url_mapping ADD KEY idx_long_url (long_url(255));

-- 为什么用前缀索引？
-- long_url 最大 2048 字符，完整索引太大
-- 前 255 字符足够区分不同 URL

前缀长度的选择有讲究：

-- 计算不同前缀长度的选择性
SELECT 
    COUNT(DISTINCT LEFT(long_url, 50)) / COUNT(*) AS selectivity_50,
    COUNT(DISTINCT LEFT(long_url, 100)) / COUNT(*) AS selectivity_100,
    COUNT(DISTINCT LEFT(long_url, 255)) / COUNT(*) AS selectivity_255
FROM url_mapping;

-- 结果：
-- selectivity_50:   0.75
-- selectivity_100:  0.92
-- selectivity_255:  0.98

-- 结论：前缀 255 字符选择性已达 98%，够用了

优化效果

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

查询场景	优化前	优化后	提升
WHERE short_code = ‘abc’	200ms	5ms	40 倍
WHERE user_id = 12345 ORDER BY created_at	150ms	8ms	18 倍
WHERE created_at BETWEEN …	500ms	50ms	10 倍
WHERE long_url = ’…‘	800ms	100ms	8 倍

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

索引维护：学会克制

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

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

-- 查看索引大小
SELECT 
    INDEX_NAME,
    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'url_shortener'
  AND table_name = 'url_mapping'
  AND stat_name = 'size';

-- 结果：
-- uk_short_code:      200 MB
-- idx_user_created:   250 MB
-- idx_created_at:     180 MB
-- idx_long_url:       300 MB
-- 总索引大小：         930 MB
-- 数据大小：           1200 MB

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

写入性能的影响

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

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

总共 5 次写入操作。

-- 插入性能测试
-- 无索引：10000 条/秒
-- 4 个索引：6000 条/秒

索引越多，写入越慢。

删除无用索引

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

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema = 'url_shortener';

-- 结果：idx_status 从未被使用

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

ALTER TABLE url_mapping DROP INDEX idx_status;

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

避免索引失效

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

-- ❌ 索引失效的常见情况

-- 1. 对索引列做运算
WHERE user_id + 1 = 12345

-- 2. 使用函数
WHERE DATE(created_at) = '2024-01-01'

-- 3. 隐式类型转换
WHERE short_code = 12345  -- short_code 是 VARCHAR，这里用了数字

-- 4. LIKE 以 % 开头
WHERE long_url LIKE '%example.com%'

-- ✅ 正确的写法
WHERE user_id = 12345
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
WHERE short_code = '12345'
WHERE long_url LIKE 'https://example.com%'

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

EXPLAIN 实战手册

EXPLAIN 成了我最常用的工具。

关键字段解读

EXPLAIN SELECT long_url FROM url_mapping WHERE short_code = 'a1b2c3';

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

我重点看这几个字段：

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

值	含义	性能
system	系统表	最优 ⭐⭐⭐⭐⭐
const	常量查找	极好 ⭐⭐⭐⭐⭐
eq_ref	唯一索引查找	很好 ⭐⭐⭐⭐
ref	非唯一索引查找	好 ⭐⭐⭐
range	范围查找	一般 ⭐⭐
index	索引全扫描	差 ⭐
ALL	全表扫描	最差 ❌

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

Extra（额外信息）：

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

实战案例

案例 1：全表扫描

EXPLAIN SELECT * FROM url_mapping WHERE DATE(created_at) = '2024-01-01';

-- type: ALL
-- rows: 50000
-- Extra: Using where

-- 问题：DATE(created_at) 使用了函数，索引失效
-- 优化：
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'

案例 2：文件排序

EXPLAIN SELECT * FROM url_mapping WHERE user_id = 12345 ORDER BY click_count DESC LIMIT 20;

-- type: ref
-- rows: 100
-- Extra: Using filesort

-- 问题：ORDER BY 字段不在索引中，需要额外排序
-- 优化：添加组合索引
CREATE INDEX idx_user_click ON url_mapping(user_id, click_count);

上一章节表结构设计设计高效的存储表结构