索引优化实践
数据库性能问题 80% 以上源于索引使用不当。本文从实战角度出发，讲解索引优化的常见场景、判断方法与解决方案。
一、慢查询排查流程
1.1 开启慢查询日志
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志（临时）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 永久配置
-- my.cnf:
-- [mysqld]
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1
1.2 使用 EXPLAIN 分析
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
AND created_at > '2024-01-01'
ORDER BY id DESC
LIMIT 100;
关键字段解读：
| 字段 | 含义 | 优化目标 |
|---|---|---|
type |
访问类型 | 最好是 ref/range，避免 ALL |
key |
实际使用索引 | 应有值，避免 NULL |
rows |
预计扫描行数 | 越小越好 |
Extra |
附加信息 | 避免 Using filesort、Using temporary |
type 从好到差：
const > eq_ref > ref > range > index > ALL
| type | 说明 | 场景 |
|---|---|---|
const |
主键/唯一索引，等值查询 | WHERE id = 1 |
eq_ref |
唯一索引关联 | JOIN |
ref |
非唯一索引等值查询 | WHERE name = 'zhang' |
range |
索引范围查询 | WHERE id > 100 |
index |
索引全扫描 | SELECT id FROM orders |
ALL |
全表扫描 | ❌ 需优化 |
1.3 PROFILING 查看耗时
SET profiling = 1;
-- 执行查询
SELECT * FROM orders WHERE status = 'pending';
-- 查看耗时
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
二、索引失效场景与解决
2.1 函数/运算导致索引失效
-- ❌ 函数：索引列参与计算
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM orders WHERE LEFT(customer_name, 3) = 'zhang';
-- ✅ 改写：使用函数索引或应用层处理
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- ❌ 运算：字段参与运算
SELECT * FROM orders WHERE price * 0.8 > 100;
-- ✅ 改写：反向计算或冗余字段
SELECT * FROM orders WHERE price > 100 / 0.8;
2.2 类型转换导致索引失效
-- ❌ 隐式类型转换：字符串列用数字查询
phone VARCHAR(20)
SELECT * FROM users WHERE phone = 13800138000; -- 数字转字符串
-- ✅ 使用字符串字面量
SELECT * FROM users WHERE phone = '13800138000';
-- ❌ 字符集不同导致索引失效
-- 表用 utf8mb4，查询条件用 utf8
2.3 模糊搜索导致索引失效
-- ❌ 左前缀不确定：%开头
SELECT * FROM users WHERE name LIKE '%zhang%';
-- ✅ 右前缀确定：可以用索引
SELECT * FROM users WHERE name LIKE 'zhang%';
-- ✅ 解决方案：全文索引 / ES
-- 全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
-- 使用全文索引
SELECT * FROM users WHERE MATCH(name) AGAINST('zhang san');
2.4 范围查询后索引失效
-- 假设有联合索引 idx_status_created(status, created_at)
-- ✅ 范围前：两个字段都能用索引
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';
-- ❌ 范围后：created_at 之后列失效
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01' AND customer_id = 100;
-- customer_id 无法使用索引
-- ✅ 解决：调整列顺序，将范围列放最后
-- 或者拆分为多个查询
2.5 OR 导致索引失效
-- ❌ OR 连接，类型不一致
SELECT * FROM orders WHERE status = 'pending' OR price > 100;
-- ✅ 改写：拆分为 UNION
SELECT * FROM orders WHERE status = 'pending'
UNION ALL
SELECT * FROM orders WHERE status != 'pending' AND price > 100;
-- ✅ 或者：为 OR 两边都建索引
ALTER TABLE orders ADD INDEX idx_status(status);
ALTER TABLE orders ADD INDEX idx_price(price);
三、联合索引设计
3.1 联合索引列顺序原则
原则：区分度高的列放前面，等值查询列放范围列前面
-- 场景：查询条件经常是 status = 'pending' AND created_at > '2024-01-01'
-- ❌ 错误顺序：范围列放前面
ALTER TABLE orders ADD INDEX idx_created_status(created_at, status);
-- ✅ 正确顺序：等值列放前面
ALTER TABLE orders ADD INDEX idx_status_created(status, created_at);
3.2 覆盖索引避免回表
-- 假设有联合索引 idx_name_age(name, age)
-- ✅ 覆盖索引：查询列都在索引中，无需回表
SELECT name, age FROM users WHERE name = 'zhang';
-- ❌ 非覆盖索引：需要回表
SELECT * FROM users WHERE name = 'zhang';
SELECT name, email FROM users WHERE name = 'zhang'; -- email 不在索引中
-- 实战优化：如果经常只查 name, age，建覆盖索引
ALTER TABLE users ADD INDEX idx_name_age(name, age);
3.3 索引下推（ICP）优化
MySQL 5.6+ 自动应用 ICP，减少回表：
-- 有联合索引 idx_name_age(name, age)
SELECT * FROM users WHERE name LIKE 'zhang%' AND age = 25;
-- 无 ICP：先按 name 查到所有行，再在 server 层过滤 age
-- 有 ICP：在索引层过滤 age，减少回表
四、慢查询优化实战案例
4.1 案例：分页深度翻页慢
问题：
-- 深度分页：查询第 10000 页，每页 10 条
SELECT * FROM orders
ORDER BY id DESC
LIMIT 100000, 10; -- 需要扫描 100010 行
-- 耗时：3.2 秒
分析：
LIMIT offset, size
- offset 越大，扫描行数越多
- 需要先扫描到第 100000 行，然后取 10 条
- 数据库不知道后面有多少行
解决方案 1：延迟关联
-- 先查 ID，再关联
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders
ORDER BY id DESC
LIMIT 100000, 10
) AS t2 ON o.id = t2.id;
-- 耗时：0.8 秒（先查主键只返回 10 条，再关联）
解决方案 2：游标分页（推荐）
-- 第一页
SELECT * FROM orders ORDER BY id DESC LIMIT 10;
-- 返回最后一条 id = 100010
-- 第二页：记住上一页最后一条的 id
SELECT * FROM orders
WHERE id < 100010
ORDER BY id DESC
LIMIT 10;
-- 无论翻到第几页，性能稳定
解决方案 3：记录当前位置
-- 用户翻页时携带上一页最后一行的 id
-- 前端：保存当前页最后一条 id
-- 后端：WHERE id < #{lastId}
4.2 案例：COUNT(*) 慢
问题：
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- 耗时：15 秒（几千万行）
分析：COUNT(*) 需要扫描所有行才能得到总数。
解决方案：
-- 方案1：使用覆盖索引加速
ALTER TABLE orders ADD INDEX idx_status(status);
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- 耗时：2 秒（只扫描索引）
-- 方案2：异步计数
-- 业务表 + 计数表（计数器）
UPDATE order_stats SET count = count + 1 WHERE date = '2024-01-01';
-- 方案3：缓存总数（允许一定误差）
-- Redis SET order:pending:count 123456 EX 3600
4.3 案例：JOIN 导致的性能问题
问题：
SELECT o.id, o.amount, u.name, u.email
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
LIMIT 100;
-- 耗时：5 秒
分析：EXPLAIN 发现 o 表是全表扫描，没有索引。
解决：
-- 1. 确保 JOIN 字段有索引
ALTER TABLE orders ADD INDEX idx_user_id(user_id);
-- 2. 确保 WHERE 条件有索引
ALTER TABLE orders ADD INDEX idx_status(status);
-- 3. 小表驱动大表（MySQL 自动选择，但可强制）
SELECT /*+ STRAIGHT_JOIN */ o.id, u.name
FROM orders o STRAIGHT_JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';
-- 4. 限制分页（先筛选再 JOIN）
SELECT o.id, o.amount, u.name
FROM (
SELECT id, user_id, amount
FROM orders
WHERE status = 'pending'
LIMIT 10000
) o
LEFT JOIN users u ON o.user_id = u.id;
五、索引优化 Checklist
5.1 新建表时
✅ 主键用自增 ID（InnoDB 表）
✅ 为 WHERE 条件字段建索引
✅ 为 ORDER BY / GROUP BY 字段建索引
✅ 区分度高的列放联合索引前面
✅ 字符串字段建索引时指定长度
✅ 避免建太多索引（写操作负担）
5.2 SQL 编写时
✅ 使用 EXPLAIN 分析查询
✅ 避免 SELECT *
✅ 等值查询放范围查询前面
✅ 使用 LIMIT 限制结果集
✅ 批量操作替代循环单条
✅ 避免在索引列使用函数
✅ 使用覆盖索引避免回表
5.3 监控指标
✅ 慢查询数量（slow_query_log）
✅ 平均响应时间
✅ 全表扫描比例（Handler_read_rnd_next）
✅ 索引使用率
✅ 锁等待时间
5.4 常见 SQL 性能对比
| SQL 类型 | 推荐写法 | 不推荐写法 |
|---|---|---|
| 分页 | WHERE id > #{lastId} LIMIT 10 |
LIMIT 100000, 10 |
| 统计 | 覆盖索引 COUNT(status) |
COUNT(*) 全表扫描 |
| 模糊搜索 | 全文索引 MATCH AGAINST |
LIKE '%keyword%' |
| IN 查询 | IN (1,2,3) 或拆分为 JOIN |
大量 IN 子查询 |
| 范围查询 | 拆分为多个等值查询 | 大范围 BETWEEN |
| JOIN | 小表驱动大表，有索引 | 大表 JOIN 大表 |
六、索引优化工具
6.1 pt-query-digest（Percona Toolkit）
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log
# 输出：
# - 查询排名（按执行时间）
# - 查询摘要
# - 索引建议
6.2 MySQL Workbench
图形化工具，可视化查看执行计划、索引使用情况。
6.3 information_schema
-- 查看表的索引
SELECT * FROM information_schema.statistics
WHERE table_schema = 'db_name'
AND table_name = 'orders'
ORDER BY index_name, seq_in_index;
-- 查看表大小和行数
SELECT table_name, engine, table_rows, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'db_name'
ORDER BY data_length DESC;
-- 查看未使用索引
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'db_name'
AND count_star = 0;
七、最佳实践总结
7.1 索引设计原则
- 查询驱动建索引：根据实际慢查询建立索引，不要盲目建
- 联合索引优先：多个字段经常一起查询时，建联合索引
- 覆盖索引优化：查询尽量使用覆盖索引
- 控制索引数量：每个索引增加写操作开销（5-10 个为宜）
- 区分度优先：区分度高的字段优先建索引
7.2 日常运维
1. 定期分析慢查询日志
2. 定期检查并删除无用索引
3. 大表加字段加索引使用 pt-online-schema-change
4. 监控索引使用率，删除低效索引
5. EXPLAIN 是优化前必做操作
一些查询优化
-- ❌ 避免 SELECT * SELECT * FROM user; -- ✅ 只查询必要字段 SELECT id, name, age FROM user;
-- ❌ 避免函数操作索引列 SELECT * FROM user WHERE YEAR(create_time) = 2024; -- ✅ 改为范围查询 SELECT * FROM user WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31';
-- ❌ 避免隐式类型转换 SELECT * FROM user WHERE phone = 13800138000; -- phone为VARCHAR -- ✅ 保持类型一致 SELECT * FROM user WHERE phone = '13800138000';
-- ❌ 避免负向查询 SELECT * FROM user WHERE status != 0; -- ✅ 改为正向查询 (如业务允许) SELECT * FROM user WHERE status IN (1, 2, 3);
-- ✅ 分页优化 (大数据量) -- 传统：LIMIT 1000000, 10 (扫描1000010行) SELECT * FROM user LIMIT 1000000, 10; -- 优化1：子查询 + 主键 SELECT * FROM user WHERE id >= (SELECT id FROM user LIMIT 1000000, 1) LIMIT 10; -- 优化2：记录上次查询最大id SELECT * FROM user WHERE id > 1000000 LIMIT 10;