博客上线一个月，访问量慢慢上来了。但我发现文章列表页越来越慢，有时候要等一两秒才能打开。用Chrome开发者工具看了一下，TTFB（首字节时间）居然有1.2秒，这对于一个只有几百篇文章的博客来说，显然不正常。

性能问题，十有八九出在数据库。我决定好好优化一下MySQL查询。今天就来分享一下从慢查询定位到索引优化的完整过程。

第一步：开启慢查询日志

优化的第一步是找到慢查询。MySQL有个慢查询日志功能，可以把执行时间超过指定阈值的SQL记录下来，方便分析。

在my.cnf里加了几行配置：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.1
log_queries_not_using_indexes = 1

这样，执行时间超过0.1秒的SQL，以及没有使用索引的SQL，都会被记录到慢查询日志里。跑了一天，果然抓到了几条慢SQL，其中最慢的是文章列表页的查询：

SELECT p.id, p.title, p.excerpt, p.created_at, p.views,
       c.name as category_name, u.nickname as author
FROM blog_posts p
LEFT JOIN blog_categories c ON p.category_id = c.id
LEFT JOIN blog_users u ON p.user_id = u.id
WHERE p.status = 'published' AND p.deleted_at IS NULL
ORDER BY p.created_at DESC
LIMIT 0, 10;

这条SQL看起来很正常，但执行时间居然有1.2秒。

第二步：EXPLAIN分析

找到慢SQL后，用EXPLAIN命令分析执行计划：

EXPLAIN SELECT p.id, p.title, ... FROM blog_posts p ...;

EXPLAIN的结果显示，blog_posts表的type列是ALL，也就是全表扫描；rows列显示扫描了856行，而实际只需要10行。Extra列显示"Using where; Using filesort"，说明用了WHERE条件过滤，并且做了文件排序（filesort）。

问题找到了：blogposts表的status和createdat字段没有合适的索引，导致MySQL需要全表扫描，然后用filesort排序。数据量小的时候感觉不到，数据量大了就会很慢。

第三步：添加联合索引

知道了问题，解决方案就是加索引。但加索引也有讲究，不是给每个字段都加索引就完事了。

首先，WHERE条件里用到了status和deletedat两个字段，ORDER BY用到了createdat字段。如果分别给这三个字段加单列索引，MySQL只能用到其中一个（通常是选择性最高的那个），其他条件还是需要回表过滤。

更好的方案是建一个联合索引，把WHERE条件和ORDER BY的字段都包含进去。联合索引的顺序很重要，要遵循"最左前缀原则"——查询条件从索引的最左边开始匹配，才能用到索引。

我建了这样一个联合索引：

ALTER TABLE blog_posts 
ADD INDEX idx_status_created (status, deleted_at, created_at);

索引顺序是status → deletedat → createdat。这样，WHERE条件status='published'可以用到索引的第一列，deletedat IS NULL可以用到第二列，ORDER BY createdat可以用到第三列，不需要filesort了。

加完索引再EXPLAIN，type从ALL变成了range，rows从856变成了10，Extra列的"Using filesort"消失了。执行时间从1.2秒降到了50毫秒，提升了二十多倍。

第四步：覆盖索引进一步优化

50毫秒已经不错了，但我还想再优化一下。仔细看EXPLAIN的结果，Extra列显示"Using index condition"，说明用了索引条件下推（ICP），但还是需要回表查询数据——因为索引里只包含了status、deletedat、createdat三个字段，而查询需要的title、excerpt、views等字段不在索引里，MySQL需要根据主键回表查询。

如果能把查询需要的所有字段都放到索引里，就不需要回表了，这就是"覆盖索引"。覆盖索引的好处是，所有数据都能从索引中获取，不需要回表，性能更好。

但覆盖索引也有缺点：索引会变得很大，占用更多磁盘空间和内存。而且如果查询字段很多，建覆盖索引就不现实了。

对于文章列表页，查询的字段比较多（id、title、excerpt、createdat、views、categoryid、user_id），建覆盖索引不太合适。但对于一些简单的查询，比如只查id和title的，可以考虑覆盖索引。

比如热门文章的查询：

SELECT id, title, views FROM blog_posts 
WHERE status = 'published' 
ORDER BY views DESC LIMIT 5;

可以建这样的覆盖索引：

ALTER TABLE blog_posts 
ADD INDEX idx_status_views_title (status, views, title);

这样，查询需要的status、views、title都在索引里，id是主键（InnoDB的二级索引默认包含主键），所以不需要回表，直接从索引就能获取所有数据。EXPLAIN的Extra列会显示"Using index"，说明用了覆盖索引。

索引优化的注意事项

优化完这几条查询，博客的页面加载速度明显提升了。总结一下索引优化的几个注意事项：

第一，索引不是越多越好。每个索引都会占用磁盘空间，而且INSERT、UPDATE、DELETE操作需要维护所有索引，会降低写性能。一般来说，一张表的索引数量不要超过五个。

第二，联合索引的顺序很重要。要遵循最左前缀原则，把等值查询的字段放前面，范围查询的字段放后面，ORDER BY的字段放最后。

第三，避免在索引字段上做函数运算或类型转换。比如WHERE YEAR(createdat)=2015，这样用不到createdat的索引，应该改成WHERE created_at BETWEEN '2015-01-01' AND '2015-12-31'。

第四，定期分析慢查询，持续优化。数据量在增长，查询在变化，今天快的SQL明天可能就慢了。养成定期看慢查询日志的习惯，持续优化。

总结

MySQL索引优化是后端开发者的必备技能。核心思路是：用慢查询日志定位问题，用EXPLAIN分析执行计划，用联合索引和覆盖索引优化查询。优化不是一劳永逸的，需要持续关注、持续改进。希望这篇文章能对你有所帮助。