Logo

索引优化

那次数据库崩溃

还记得我第一次实现的订单超时检查吗？

# 😱 问题代码
def check_order_timeout():
    timeout_threshold = datetime.now() - timedelta(minutes=30)
    
    pending_orders = Order.objects.filter(
        status='pending_payment',
        created_at__lt=timeout_threshold
    )
    
    for order in pending_orders:
        cancel_order(order)

2024 年 3 月的一天，订单量达到 80 万单。早上 10 点，运维群里突然炸锅：

“数据库 CPU 100%！” “所有查询都卡住了！” “用户无法下单！”

我慌了，登录数据库一看，那个定时任务的查询正在扫描 80 万条订单记录。

慢查询日志

-- 😱 慢查询：扫描了 80 万行
SELECT * FROM orders 
WHERE status = 'pending_payment' 
  AND created_at < '2024-03-15 09:30:00';

-- 执行计划：
-- type: ALL（全表扫描）
-- rows: 800000
-- Extra: Using where
-- 执行时间：15.3 秒

这个查询每分钟都在执行，数据库当然扛不住！

索引基础

什么是索引？

索引就像书的目录，可以快速找到需要的内容，而不必翻遍整本书。

-- 没有索引：全表扫描 😱
SELECT * FROM orders WHERE status = 'pending';
-- 扫描 100 万行，耗时 2 秒

-- 有索引：索引查找 ✅
SELECT * FROM orders WHERE status = 'pending';
-- 扫描 100 行，耗时 0.01 秒

索引的类型

类型说明适用场景
主键索引唯一标识记录主键字段
唯一索引值必须唯一手机号、身份证
普通索引最常用的索引查询条件字段
联合索引多个字段的组合多条件查询
全文索引文本搜索搜索功能

联合索引设计

最关键的问题

-- 延时任务表
CREATE TABLE delay_tasks (
    id BIGINT PRIMARY KEY,
    task_id VARCHAR(100),
    execute_at TIMESTAMP,
    status VARCHAR(20),
    ...
);

-- 问题：如何创建索引？

索引设计原则

原则 1：最左前缀原则

-- ✅ 好的联合索引
CREATE INDEX idx_execute_status ON delay_tasks (execute_at, status);

-- 为什么？
-- 因为我们的查询是：
SELECT * FROM delay_tasks 
WHERE execute_at <= NOW() 
  AND status = 'pending';

-- 这个索引可以完美支持查询

-- ❌ 不好的联合索引
CREATE INDEX idx_status_execute ON delay_tasks (status, execute_at);

-- 为什么？
-- 虽然也能用，但不如上面的好

原则 2：索引覆盖查询

-- ✅ 覆盖索引：不需要回表
CREATE INDEX idx_covering ON delay_tasks (execute_at, status, task_id);

-- 查询时：
SELECT task_id FROM delay_tasks 
WHERE execute_at <= NOW() 
  AND status = 'pending';

-- 所有需要的字段都在索引中，不需要回表查询主键

-- ❌ 需要回表
SELECT * FROM delay_tasks 
WHERE execute_at <= NOW() 
  AND status = 'pending';

-- 需要回表查询其他字段，性能较差

原则 3：选择性和基数

-- 高选择性：status 有很多不同的值（pending, processing, completed, failed）
-- 适合建立索引

-- 低选择性：is_deleted 只有两个值（0, 1）
-- 单独索引效果不好，需要联合索引

实战索引优化

场景 1：订单超时查询

-- 原始表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    product_id BIGINT,
    status VARCHAR(50),
    created_at TIMESTAMP,
    ...
);

-- 原始查询（慢）
SELECT * FROM orders 
WHERE status = 'pending_payment' 
  AND created_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- 😱 执行计划：全表扫描
-- rows: 800000
-- time: 15.3s

-- ✅ 优化 1：添加联合索引
CREATE INDEX idx_status_created ON orders (status, created_at);

-- ✅ 执行计划：索引查找
-- rows: 1200
-- time: 0.05s

-- ✅ 优化 2：添加覆盖索引（如果只需要 ID）
CREATE INDEX idx_covering ON orders (status, created_at, id);

SELECT id FROM orders 
WHERE status = 'pending_payment' 
  AND created_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- ✅ 执行计划：索引覆盖
-- rows: 1200
-- time: 0.02s（不需要回表）

场景 2：延时任务查询

-- 延时任务表
CREATE TABLE delay_tasks (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    task_id VARCHAR(100) UNIQUE,
    task_type VARCHAR(50),
    task_data TEXT,
    execute_at TIMESTAMP,
    status ENUM('pending', 'processing', 'completed', 'failed'),
    retry_count INT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    ...
);

-- 核心查询
SELECT * FROM delay_tasks 
WHERE execute_at <= NOW() 
  AND status = 'pending'
ORDER BY execute_at ASC
LIMIT 100;

-- ✅ 最佳索引设计
CREATE INDEX idx_execute_status ON delay_tasks (execute_at, status);

-- 额外的查询 1：按状态统计
SELECT status, COUNT(*) FROM delay_tasks GROUP BY status;

-- ✅ 添加状态索引
CREATE INDEX idx_status ON delay_tasks (status);

-- 额外的查询 2：监控任务积压
SELECT COUNT(*) FROM delay_tasks 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- ✅ 添加创建时间索引
CREATE INDEX idx_created_at ON delay_tasks (created_at);

索引维护

定期分析索引

-- 查看索引使用情况
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'delay_tasks'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- 查看索引大小
SELECT 
    INDEX_NAME,
    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE DATABASE_NAME = 'your_database'
  AND TABLE_NAME = 'delay_tasks'
  AND STAT_NAME = 'size'
  AND STAT_TYPE = 'Leaf-node-size'
ORDER BY size_mb DESC;

删除无用索引

-- 查看未使用的索引
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'your_database'
ORDER BY object_schema, object_name;

-- ⚠️ 谨慎删除：先确认索引确实不需要
-- DROP INDEX idx_unused ON delay_tasks;

重建碎片化索引

-- 查看表碎片
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    DATA_LENGTH / 1024 / 1024 AS data_mb,
    INDEX_LENGTH / 1024 / 1024 AS index_mb,
    DATA_FREE / 1024 / 1024 AS data_free_mb,
    ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS fragmentation_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
  AND DATA_FREE > 0
ORDER BY fragmentation_pct DESC;

-- 优化表（重建索引）
OPTIMIZE TABLE delay_tasks;

-- 或者：ALTER TABLE
ALTER TABLE delay_tasks ENGINE=InnoDB;

想一想

思考 1

如果查询条件中有 execute_at >= NOW() AND execute_at <= NOW() + INTERVAL 1 HOUR，如何设计索引？

参考答案

问题分析：

这是一个范围查询，查询未来 1 小时内将要执行的任务。

-- 查询语句
SELECT * FROM delay_tasks 
WHERE execute_at >= NOW() 
  AND execute_at <= NOW() + INTERVAL 1 HOUR
  AND status = 'pending'
ORDER BY execute_at ASC
LIMIT 100;

索引设计：

-- ✅ 方案 1：execute_at + status（推荐）
CREATE INDEX idx_execute_status ON delay_tasks (execute_at, status);

-- 执行计划：
-- Using index idx_execute_status
-- rows: 50
-- Extra: Using where; Using filesort

-- ❌ 方案 2：status + execute_at（不推荐）
CREATE INDEX idx_status_execute ON delay_tasks (status, execute_at);

-- 执行计划：
-- Using index idx_status_execute
-- rows: 1000
-- Extra: Using where; Using filesort

为什么方案 1 更好？

最左前缀原则：

方案 1：idx_execute_status (execute_at, status)
  ├─ 先按 execute_at 过滤（范围查询）
  └─ 再按 status 过滤
  ✅ execute_at 的范围查询可以直接利用索引

方案 2：idx_status_execute (status, execute_at)
  ├─ 先按 status 过滤（等值查询）
  └─ 再按 execute_at 过滤（范围查询）
  ⚠️ 虽然也能用，但不够高效

最佳实践：

-- 对于范围查询，把范围字段放在索引前面
CREATE INDEX idx_range_field_first ON table_name (range_field, other_field);

-- 对于等值查询，把等值字段放在索引前面
CREATE INDEX idx_eq_field_first ON table_name (eq_field, other_field);
上一章节 轮询设计 数据库轮询方案的基本实现
下一章节 批量处理 批量处理提升效率的关键技巧
系统设计 - 延时队列