MySQL 索引优化完全指南：让查询速度提升10倍-冉冉博客

数据库性能是网站编程的关键瓶颈。作为冉冉博客的技术教程系列，今天分享 MySQL 索引优化的实战经验，这些技巧在我的博客运营中都经过验证。

一、索引设计原则

索引是数据库性能优化的核心，但不是越多越好。设计索引需要遵循几个关键原则：

最左前缀原则

联合索引必须从最左列开始匹配：

-- 创建联合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- 能用上索引
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01';

-- 用不上索引
SELECT * FROM orders WHERE status = 'paid';  -- 缺少最左列
SELECT * FROM orders WHERE created_at > '2024-01-01';  -- 缺少前两列

选择性高的列优先

选择性 = 不重复值数量 / 总行数，越接近 1 越好：

-- 查看列的选择性
SELECT 
  COUNT(DISTINCT user_id) / COUNT(*) as user_selectivity,
  COUNT(DISTINCT status) / COUNT(*) as status_selectivity
FROM orders;

-- 用户ID选择性高（接近1），状态选择性低（可能只有几个值）
-- 所以 user_id 应该在联合索引前面

二、慢查询分析

找到慢查询是优化的第一步：

开启慢查询日志

-- my.cnf 配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  -- 超过2秒记录
log_queries_not_using_indexes = 1  -- 记录没用索引的查询

分析慢查询

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

关键指标解读：

type：ALL 最差（全表扫描），const 最佳（主键查询），ref、range 中等
key：实际使用的索引
rows：预估扫描行数，越少越好
Extra：Using filesort 或 Using temporary 需要优化

三、查询优化技巧

避免 SELECT *

-- 差：查询所有字段
SELECT * FROM posts WHERE status = 'publish';

-- 好：只查需要的字段
SELECT id, title, excerpt FROM posts WHERE status = 'publish';

优化深分页

-- 差：OFFSET 越大越慢
SELECT * FROM posts ORDER BY id LIMIT 100000, 20;

-- 好：用游标方式
SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 20;

避免索引列上用函数

-- 差：索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

-- 好：范围查询
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

OR 改成 UNION

-- 差：可能只用一个索引
SELECT * FROM orders WHERE user_id = 1 OR status = 'paid';

-- 好：利用多个索引
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE status = 'paid';

四、索引维护

索引不是建完就完事，需要定期维护：

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

-- 分析表更新索引统计信息
ANALYZE TABLE orders;

-- 重建损坏的索引
ALTER TABLE orders ENGINE=InnoDB;

五、实战案例

在我的博客中，文章列表查询曾是个瓶颈：

-- 问题查询：响应时间 800ms
SELECT * FROM posts 
WHERE status = 'publish' 
ORDER BY created_at DESC 
LIMIT 20;

-- 优化后：响应时间 50ms
-- 添加联合索引
CREATE INDEX idx_status_created ON posts(status, created_at DESC);

-- 查询结果一样，速度提升 16 倍

MySQL 索引优化是网站性能提升的关键一环。掌握这些技巧，能让你的数据库查询效率大幅提升。更多数据库和网站编程技术，欢迎持续关注冉冉博客。

文章版权归作者所有，未经允许请勿转载。

THE END