索引优化实战案例 | 清晨的一缕阳光

引言

理论知识的最终目的是指导实践。本章通过 5 个真实的索引优化案例，展示从问题发现、分析诊断到优化实施、效果验证的完整流程。

案例涵盖：

电商订单系统查询优化
用户系统登录性能优化
日志系统分页查询优化
报表系统聚合查询优化
社交系统关联查询优化

案例 1：电商订单列表查询优化

业务背景

某电商平台的订单列表查询接口，随着数据量增长，响应时间越来越慢。

表结构：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    merchant_id BIGINT NOT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    amount DECIMAL(10,2) NOT NULL,
    create_time DATETIME NOT NULL,
    pay_time DATETIME,
    finish_time DATETIME,
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB;

-- 数据量：500 万行

问题描述

慢查询 SQL：

-- 用户订单列表（按时间倒序，分页）
SELECT * FROM orders 
WHERE user_id = 10086 
ORDER BY create_time DESC 
LIMIT 0, 20;
-- 执行时间：2-3 秒

性能指标：

P99 延迟：3.5 秒
平均延迟：2.1 秒
目标：< 200ms

分析诊断

1. EXPLAIN 分析：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 
ORDER BY create_time DESC 
LIMIT 0, 20;

-- 输出：
-- +----+------+--------+------+---------------+------+---------+------+
-- | id | type | table  | type | possible_keys | key  | key_len| ref  | 
-- +----+------+--------+------+---------------+------+---------+------+
-- |  1 | SIMPLE | orders | ref  | idx_create_time | NULL | NULL   | NULL| 
-- +----+------+--------+------+---------------+------+---------+------+
-- rows: 50000
-- Extra: Using where; Using filesort

问题诊断：

type: ref（使用了 user_id 的索引？错！实际没有）
key: NULL（未使用索引）
rows: 50000（估算扫描 5 万行，该用户实际有 500+ 订单）
Extra: Using filesort（需要额外排序）

2. 查看索引：

SHOW INDEX FROM orders;
-- 只有 idx_create_time 索引
-- 没有 user_id 相关的索引！

3. 查看数据分布：

-- 查看用户订单分布
SELECT user_id, COUNT(*) as order_count 
FROM orders 
GROUP BY user_id 
ORDER BY order_count DESC 
LIMIT 10;

-- 结果：
-- 最大用户订单数：523
-- 平均用户订单数：15

优化方案

方案 1：创建联合索引

-- 创建 (user_id, create_time) 联合索引
CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);

-- 验证
EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 
ORDER BY create_time DESC 
LIMIT 0, 20;

-- 新输出：
-- type: ref
-- key: idx_user_time
-- rows: 523
-- Extra: NULL（无 filesort）

效果验证：

-- 执行时间对比
-- 优化前：2.1 秒
-- 优化后：0.005 秒
-- 提升：420 倍！

方案 2：覆盖索引优化（可选）

-- 如果只需要部分字段
SELECT id, order_no, amount, create_time 
FROM orders 
WHERE user_id = 10086 
ORDER BY create_time DESC 
LIMIT 0, 20;

-- 创建覆盖索引
CREATE INDEX idx_user_time_cover ON orders(user_id, create_time DESC, id, order_no, amount);

-- 无需回表，性能更优
-- 执行时间：0.003 秒

经验总结

问题根因：

缺少 user_id 列的索引
ORDER BY 导致 filesort

优化要点：

为 WHERE 条件列创建索引
将 ORDER BY 列加入联合索引
考虑覆盖索引避免回表

索引设计原则：

-- 联合索引列顺序
WHERE 条件列 + ORDER BY 列 + SELECT 列

-- 本例：
(user_id, create_time) -- 基础索引
(user_id, create_time, id, order_no, amount) -- 覆盖索引

案例 2：用户登录性能优化

业务背景

用户登录接口响应慢，高峰期出现超时。

表结构：

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    phone VARCHAR(11),
    email VARCHAR(100),
    password_hash VARCHAR(255),
    status TINYINT DEFAULT 1,
    last_login_time DATETIME,
    INDEX idx_username (username)
) ENGINE=InnoDB;

-- 数据量：1000 万行

问题描述

慢查询 SQL：

-- 手机号登录
SELECT * FROM users 
WHERE phone = '13800138000' AND status = 1;
-- 执行时间：1.5 秒

性能指标：

P99 延迟：2.0 秒
平均延迟：1.2 秒
目标：< 50ms

分析诊断

1. EXPLAIN 分析：

EXPLAIN SELECT * FROM users 
WHERE phone = '13800138000' AND status = 1;

-- 输出：
-- type: ALL（全表扫描！）
-- key: NULL
-- rows: 10000000
-- Extra: Using where

2. 查看索引：

SHOW INDEX FROM users;
-- 只有 idx_username 索引
-- phone 列没有索引！

3. 查看数据分布：

-- 查看手机号分布
SELECT COUNT(DISTINCT phone) / COUNT(*) as uniqueness 
FROM users;
-- 结果：0.99（几乎唯一）

-- 查看状态分布
SELECT status, COUNT(*) 
FROM users 
GROUP BY status;
-- 结果：status=1 占 95%，status=0 占 5%

优化方案

方案 1：创建唯一索引

-- 手机号应该是唯一的
CREATE UNIQUE INDEX idx_phone ON users(phone);

-- 验证
EXPLAIN SELECT * FROM users 
WHERE phone = '13800138000' AND status = 1;

-- 新输出：
-- type: const（最优！）
-- key: idx_phone
-- rows: 1
-- Extra: Using where

效果验证：

-- 执行时间对比
-- 优化前：1.2 秒
-- 优化后：0.001 秒
-- 提升：1200 倍！

方案 2：联合索引优化

-- 如果 status 过滤也很重要
CREATE INDEX idx_phone_status ON users(phone, status);

-- 验证
EXPLAIN SELECT * FROM users 
WHERE phone = '13800138000' AND status = 1;

-- type: ref
-- key: idx_phone_status
-- rows: 1

经验总结

问题根因：

phone 列没有索引
全表扫描 1000 万行

优化要点：

登录凭证（手机号/邮箱）应创建唯一索引
高选择性列优先建索引
const 类型查询性能最优

注意事项：

-- 手机号登录时，确保使用字符串
SELECT * FROM users WHERE phone = '13800138000';  -- ✅
SELECT * FROM users WHERE phone = 13800138000;    -- ❌ 隐式转换

案例 3：日志系统分页优化

业务背景

系统日志表分页查询，深度分页时性能极差。

表结构：

CREATE TABLE system_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    action VARCHAR(50),
    ip_address VARCHAR(50),
    create_time DATETIME,
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB;

-- 数据量：5000 万行

问题描述

慢查询 SQL：

-- 深度分页
SELECT * FROM system_logs 
ORDER BY create_time DESC 
LIMIT 1000000, 20;
-- 执行时间：15 秒

性能指标：

第 1 页（LIMIT 0, 20）：0.05 秒
第 1000 页（LIMIT 10000, 20）：0.5 秒
第 50000 页（LIMIT 1000000, 20）：15 秒 ❌

分析诊断

1. EXPLAIN 分析：

EXPLAIN SELECT * FROM system_logs 
ORDER BY create_time DESC 
LIMIT 1000000, 20;

-- 输出：
-- type: index
-- key: idx_create_time
-- rows: 1000020
-- Extra: Using index

问题诊断：

使用了索引（好）
但需要扫描 100 万行（差）
MySQL 会扫描 1000020 行，丢弃前 1000000 行，返回 20 行

2. 分页原理：

深度分页 = 扫描 (offset + limit) 行 + 丢弃 offset 行 + 返回 limit 行

LIMIT 1000000, 20
= 扫描 1000020 行 + 丢弃 1000000 行 + 返回 20 行

优化方案

方案 1：延迟关联（推荐）

-- 先通过覆盖索引获取 ID，再回表查询
SELECT l.* FROM system_logs l
INNER JOIN (
    SELECT id FROM system_logs 
    ORDER BY create_time DESC 
    LIMIT 1000000, 20
) tmp ON l.id = tmp.id;

-- 执行时间：0.5 秒（提升 30 倍）

原理：

子查询使用覆盖索引，只扫描索引树（更快）
获取 20 个 ID 后，再通过主键回表（精确查询）
避免了大量数据的回表操作

方案 2：游标分页（最佳）

-- 记录上一页最后一条的 create_time 和 id
-- 假设上一页最后一条：create_time='2024-08-01 12:00:00', id=5000000

SELECT * FROM system_logs 
WHERE create_time < '2024-08-01 12:00:00' 
   OR (create_time = '2024-08-01 12:00:00' AND id < 5000000)
ORDER BY create_time DESC, id DESC
LIMIT 20;

-- 执行时间：0.01 秒（提升 1500 倍）

优点：

性能稳定，不随页码深度下降
适合”加载更多”场景

缺点：

不能跳转到指定页码

方案 3：限制最大页码

-- 业务层面限制最大页码
-- 例如：最多允许访问前 10000 条

-- 应用层逻辑
$max_offset = 10000;
$offset = min($page * $page_size, $max_offset);

-- SQL
SELECT * FROM system_logs 
ORDER BY create_time DESC 
LIMIT $offset, 20;

经验总结

问题根因：

深度分页需要扫描大量数据
扫描行数 = offset + limit

优化方案对比：

方案	性能	适用场景	实现复杂度
延迟关联	⭐⭐⭐⭐	任意分页	中
游标分页	⭐⭐⭐⭐⭐	连续翻页	低
限制页码	⭐⭐⭐	浅分页	低

最佳实践：

-- 1. 避免深度分页（业务限制）
-- 2. 使用游标分页（性能最优）
-- 3. 延迟关联（兼容性好）

-- 游标分页示例代码（Java）
public List<Log> getNextPage(LocalDateTime lastTime, Long lastId, int limit) {
    return jdbcTemplate.query(
        "SELECT * FROM system_logs " +
        "WHERE create_time < ? OR (create_time = ? AND id < ?) " +
        "ORDER BY create_time DESC, id DESC " +
        "LIMIT ?",
        new Object[]{lastTime, lastTime, lastId, limit}
    );
}

案例 4：报表系统聚合查询优化

业务背景

日报表查询慢，影响数据导出功能。

表结构：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    category_id INT,
    amount DECIMAL(10,2),
    status TINYINT,
    create_time DATETIME,
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB;

-- 数据量：2000 万行

问题描述

慢查询 SQL：

-- 按类目统计每日销售额
SELECT 
    DATE(create_time) as date,
    category_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2024-02-01'
GROUP BY DATE(create_time), category_id;
-- 执行时间：8 秒

分析诊断

1. EXPLAIN 分析：

EXPLAIN SELECT 
    DATE(create_time) as date,
    category_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2024-02-01'
GROUP BY DATE(create_time), category_id;

-- 输出：
-- type: range
-- key: idx_create_time
-- rows: 1500000
-- Extra: Using where; Using temporary; Using filesort

问题诊断：

使用了索引（好）
但扫描了 150 万行（1 个月的数据）
Extra: Using temporary（使用了临时表）
Extra: Using filesort（需要排序）

2. 问题根因：

DATE() 函数导致无法使用索引分组
GROUP BY 两列，需要临时表和排序
数据量大，临时表和排序开销大

优化方案

方案 1：优化 GROUP BY

-- 避免在 GROUP BY 中使用函数
SELECT 
    DATE(create_time) as date,
    category_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2024-02-01'
GROUP BY create_time, category_id;  -- 直接按 create_time 分组

-- 执行时间：5 秒（提升有限）

方案 2：创建联合索引

-- 创建 (create_time, category_id) 联合索引
CREATE INDEX idx_time_category ON orders(create_time, category_id);

-- 验证
EXPLAIN SELECT 
    DATE(create_time) as date,
    category_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2024-02-01'
GROUP BY DATE(create_time), category_id;

-- 新输出：
-- type: range
-- key: idx_time_category
-- rows: 1500000
-- Extra: Using where; Using index（覆盖索引！）

效果验证：

-- 执行时间对比
-- 优化前：8 秒
-- 优化后：2 秒
-- 提升：4 倍

方案 3：预聚合（最佳）

-- 创建日报表
CREATE TABLE daily_category_stats (
    stat_date DATE,
    category_id INT,
    order_count INT,
    total_amount DECIMAL(15,2),
    PRIMARY KEY (stat_date, category_id)
) ENGINE=InnoDB;

-- 每天凌晨统计前一天的数据
INSERT INTO daily_category_stats 
SELECT 
    DATE(create_time) as stat_date,
    category_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders 
WHERE create_time >= CURDATE() - INTERVAL 1 DAY
  AND create_time < CURDATE()
GROUP BY DATE(create_time), category_id;

-- 查询时直接查统计表
SELECT * FROM daily_category_stats 
WHERE stat_date >= '2024-01-01' 
  AND stat_date < '2024-02-01';

-- 执行时间：0.01 秒（提升 800 倍）

经验总结

问题根因：

GROUP BY 数据量大
临时表和排序开销大

优化层次：

索引优化（4 倍提升）
SQL 改写（避免函数）
预聚合（800 倍提升）⭐

最佳实践：

-- 报表系统通用方案
-- 1. 实时查询：索引优化
-- 2. T+1 报表：预聚合
-- 3. 复杂报表：数据仓库/OLAP

-- 预聚合表设计原则
-- - 按时间维度（天/周/月）
-- - 按业务维度（类目/地区/渠道）
-- - 定时任务更新
-- - 保留明细表用于钻取

案例 5：社交系统关联查询优化

业务背景

社交 Feed 流查询慢，影响用户体验。

表结构：

-- 用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    avatar_url VARCHAR(255)
);

-- 关注表
CREATE TABLE follows (
    follower_id BIGINT,  -- 关注者
    followee_id BIGINT,  -- 被关注者
    create_time DATETIME,
    PRIMARY KEY (follower_id, followee_id)
);

-- 动态表
CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    content TEXT,
    create_time DATETIME,
    INDEX idx_user_time (user_id, create_time)
);

-- 数据量：
-- users: 500 万
-- follows: 5000 万
-- posts: 1 亿

问题描述

慢查询 SQL：

-- 查询关注人的动态（Feed 流）
SELECT p.*, u.username, u.avatar_url
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.user_id IN (
    SELECT followee_id FROM follows 
    WHERE follower_id = 10086
)
ORDER BY p.create_time DESC
LIMIT 0, 20;
-- 执行时间：5 秒

分析诊断

1. EXPLAIN 分析：

EXPLAIN SELECT p.*, u.username, u.avatar_url
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.user_id IN (
    SELECT followee_id FROM follows 
    WHERE follower_id = 10086
)
ORDER BY p.create_time DESC
LIMIT 0, 20;

-- 输出：
-- 子查询：type: ALL（全表扫描 follows）
-- 主查询：type: ALL（全表扫描 posts）

2. 问题诊断：

子查询全表扫描 follows 表
主查询全表扫描 posts 表
IN 子查询效率低

优化方案

方案 1：改为 JOIN

-- 将 IN 子查询改为 JOIN
SELECT p.*, u.username, u.avatar_url
FROM posts p
JOIN follows f ON p.user_id = f.followee_id
JOIN users u ON p.user_id = u.id
WHERE f.follower_id = 10086
ORDER BY p.create_time DESC
LIMIT 0, 20;

-- 执行时间：1 秒（提升 5 倍）

方案 2：创建索引

-- follows 表需要索引
CREATE INDEX idx_follower_time ON follows(follower_id, create_time);

-- posts 表已有索引，但需要优化
-- 现有：idx_user_time (user_id, create_time)

-- 验证
EXPLAIN SELECT p.*, u.username, u.avatar_url
FROM posts p
JOIN follows f ON p.user_id = f.followee_id
JOIN users u ON p.user_id = u.id
WHERE f.follower_id = 10086
ORDER BY p.create_time DESC
LIMIT 0, 20;

-- 新输出：
-- follows: type: ref, key: idx_follower_id
-- posts: type: ref, key: idx_user_time
-- users: type: eq_ref, key: PRIMARY

效果验证：

-- 执行时间对比
-- 优化前：5 秒
-- 优化后：0.1 秒
-- 提升：50 倍

方案 3：预计算 Feed 流（最佳）

-- 创建用户 Feed 流表
CREATE TABLE user_feed (
    user_id BIGINT,
    post_id BIGINT,
    post_time DATETIME,
    PRIMARY KEY (user_id, post_time, post_id)
) ENGINE=InnoDB;

-- 当用户发 post 时，插入到所有粉丝的 feed 表
INSERT INTO user_feed (user_id, post_id, post_time)
SELECT followee_id, ?, ? FROM follows 
WHERE follower_id = ?;

-- 查询 feed 流
SELECT p.*, u.username, u.avatar_url
FROM user_feed f
JOIN posts p ON f.post_id = p.id
JOIN users u ON p.user_id = u.id
WHERE f.user_id = 10086
ORDER BY f.post_time DESC
LIMIT 0, 20;

-- 执行时间：0.01 秒（提升 500 倍）

经验总结

问题根因：

IN 子查询效率低
缺少合适的索引
多表 JOIN 开销大

优化方案对比：

方案	性能	写放大	适用场景
IN 子查询	⭐	无	小数据量
JOIN	⭐⭐⭐	无	中等数据量
预计算 Feed	⭐⭐⭐⭐⭐	高	大数据量，读多写少

最佳实践：

-- Feed 流系统架构选择
-- 1. 推模式（写扩散）：适合大 V 少的场景
-- 2. 拉模式（读扩散）：适合大 V 多的场景
-- 3. 混合模式：普通用户推模式，大 V 拉模式

-- 索引设计原则
-- - JOIN 列必须有索引
-- - 优先使用主键关联
-- - 大表避免多表 JOIN

总结

优化流程总结

graph TD
    A[发现慢查询] --> B[EXPLAIN 分析]
    B --> C{问题类型？}
    C -->|全表扫描 | D[添加索引]
    C -->|索引失效 | E[改写 SQL]
    C -->|深度分页 | F[延迟关联/游标]
    C -->|JOIN 复杂 | G[优化关联]
    D --> H[验证效果]
    E --> H
    F --> H
    G --> H
    H --> I[上线监控]

核心要点

先分析后优化：EXPLAIN 是必备工具
索引是基础：80% 的问题可以通过索引解决
SQL 改写：避免函数、类型转换等
架构优化：预聚合、读写分离等

经验法则

80/20 法则：20% 的 SQL 占用 80% 的资源
渐进优化：索引 → SQL → 架构
持续监控：性能优化是持续过程

下一步

索引优化篇完结！后续将学习：

事务与锁机制
MVCC 原理
死锁分析与解决
主从复制与高可用

参考资料

《高性能 MySQL》第 5 章：创建高性能的索引
MySQL 官方文档 - 优化
Percona 性能优化博客