MySQL慢查询排查：一个索引救了整个系统

2016年10月27日，一个普通的周四。

下午三点多，正是业务高峰期，运营的同事突然在群里说："网站好慢啊，页面加载要十几秒，用户都在投诉了。"

我心里咯噔一下，赶紧打开监控，发现数据库的CPU使用率已经跑到了90%以上，连接数也快满了。我知道，出事了。

一、问题现象

我先看了一下系统的状态：

1. 网站响应慢：页面加载时间从平时的几百毫秒，变成了十几秒，甚至超时。
2. 数据库CPU高：MySQL的CPU使用率，一直在90%以上，平时只有20-30%。
3. 连接数高：MySQL的连接数，接近最大值，很多请求在排队等待连接。
4. 慢查询多：慢查询日志里，出现了大量的慢查询，都是同一个SQL。

从这些现象来看，应该是数据库出了问题，某个SQL查询很慢，导致数据库CPU跑满，连接数被占满，整个系统都变慢了。

二、排查过程

确认了是数据库的问题，我开始排查。

第一步：查看慢查询日志

我先打开了MySQL的慢查询日志，看看哪些SQL查询比较慢。

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

慢查询日志已经开启了，longquerytime设置的是1秒，也就是超过1秒的查询，都会被记录到慢查询日志里。

我看了一下慢查询日志，发现最近一个小时，有大量的慢查询，而且都是同一个SQL：

SELECT * FROM orders 
WHERE user_id = 12345 
AND status = 'paid' 
AND created_at >= '2016-10-01' 
ORDER BY created_at DESC 
LIMIT 20;

这个SQL，是查询用户的订单列表，查询时间平均在5秒左右，最慢的一次，甚至达到了15秒。

而且，这个SQL的调用频率很高，每秒有几十次调用。每次调用都要5秒，数据库的连接，很快就被占满了，CPU也跑满了。

第二步：用EXPLAIN分析执行计划

找到了慢查询的SQL，我用EXPLAIN命令，分析一下这个SQL的执行计划，看看为什么这么慢。

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
AND status = 'paid' 
AND created_at >= '2016-10-01' 
ORDER BY created_at DESC 
LIMIT 20;

执行结果如下：

从执行计划可以看出：

• type = ALL：全表扫描，没有走任何索引。
• possible_keys = NULL：没有可用的索引。
• key = NULL：实际使用的索引是NULL，也就是没有用索引。
• rows = 3500000：扫描了350万行数据。
• Extra = Using where; Using filesort：用了where条件过滤，而且用了文件排序（filesort），也就是没有用索引排序，而是在内存或者磁盘中排序。

问题很清楚了：这个SQL，没有走索引，导致全表扫描，扫描了350万行数据，然后还要排序，所以非常慢。

第三步：查看表结构和索引

为什么这个SQL没有走索引呢？我看了一下orders表的结构和索引。

SHOW CREATE TABLE orders;

表结构如下：

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order_no` varchar(32) NOT NULL,
  `status` varchar(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

从表结构可以看出，orders表，只有两个索引：

• 主键索引：id
• 唯一索引：order_no

而我们的查询条件，是userid、status、createdat，这三个字段，都没有索引！

所以，这个SQL查询的时候，只能全表扫描，扫描350万行数据，然后过滤出符合条件的行，再排序，所以非常慢。

第四步：确认数据量和分布

在加索引之前，我先确认了一下数据量和数据分布，确保加索引之后，效果会好。

-- 查看表的总行数
SELECT COUNT(*) FROM orders;
-- 结果：3500000

-- 查看某个用户的订单数
SELECT COUNT(*) FROM orders WHERE user_id = 12345;
-- 结果：156

-- 查看status的分布
SELECT status, COUNT(*) FROM orders GROUP BY status;
-- 结果：paid: 2800000, pending: 500000, cancelled: 200000

从数据分布可以看出：

• 表总共有350万行数据，数据量不小。
• 单个用户的订单数，平均只有100多，很少。
• status字段，大部分都是'paid'，区分度不高。

所以，如果在userid字段上加索引，查询的时候，就能通过索引，快速定位到某个用户的订单（只有100多行），然后再过滤status和createdat，再排序，就会非常快。

三、解决方案

找到了问题的根因，解决方案就很简单了：加索引。

1. 加索引

我在user_id字段上，加了一个索引：

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

加索引的过程，花了大概2分钟，因为表有350万行数据，加索引需要扫描全表，构建索引。加索引的时候，会锁表，所以我是在业务低峰期（凌晨）加的，避免影响线上业务。

2. 验证效果

加完索引之后，我再次用EXPLAIN分析执行计划：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
AND status = 'paid' 
AND created_at >= '2016-10-01' 
ORDER BY created_at DESC 
LIMIT 20;

执行结果如下：

从执行计划可以看出：

• type = ref：走了非唯一索引，通过索引查找。
• possiblekeys = idxuserid：可用的索引是idxuser_id。
• key = idxuserid：实际使用的索引是idxuserid。
• rows = 156：只扫描了156行数据（就是这个用户的所有订单）。
• Extra = Using where：用了where条件过滤，没有filesort了（因为数据量小，在内存中排序很快）。

加了索引之后，扫描的行数，从350万行，降到了156行，查询时间，从5秒，降到了几毫秒！

我又实际执行了一下这个SQL，查询时间是0.003秒，也就是3毫秒，比之前的5秒，快了1000多倍！

3. 观察系统状态

加完索引之后，我观察了一下系统的状态：

• 数据库CPU使用率，从90%以上，降到了20%左右。
• 连接数，从接近最大值，降到了正常水平。
• 网站响应时间，从十几秒，降到了几百毫秒。
• 慢查询日志里，这个SQL再也没有出现过。

一个索引，就救了整个系统。

四、进一步优化

加了user_id索引之后，问题解决了。但是，我还想进一步优化，让这个查询更快。

1. 联合索引

虽然加了userid索引，查询已经很快了，但是，查询的时候，还是需要回表，然后用where条件过滤status和createdat，再排序。

如果我建一个联合索引，把userid、status、createdat都包含进去，那么查询的时候，就能直接通过索引，找到符合条件的数据，不需要回表，也不需要额外的排序，会更快。

-- 先删除之前的索引
ALTER TABLE orders DROP INDEX idx_user_id;

-- 建联合索引
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

建了联合索引之后，再次用EXPLAIN分析：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
AND status = 'paid' 
AND created_at >= '2016-10-01' 
ORDER BY created_at DESC 
LIMIT 20;

执行结果显示，type = ref，key = idxuserstatus_created，rows = 89，Extra = Using where。

扫描的行数，从156行，降到了89行，因为索引已经包含了status和created_at，能更精确地过滤数据。查询时间，也从3毫秒，降到了1毫秒左右。

而且，因为联合索引的最后一个字段是createdat，索引本身就是按createdat排序的，所以ORDER BY created_at DESC的时候，不需要额外的排序，直接按索引逆序读取就行，效率更高。

2. 覆盖索引

如果查询的字段，都包含在索引里，那么就不需要回表了，这就是覆盖索引。

比如，如果我们只需要查询id、orderno、amount、createdat，而不是SELECT *，那么可以建一个包含这些字段的覆盖索引：

ALTER TABLE orders ADD INDEX idx_cover (user_id, status, created_at, order_no, amount);

然后查询的时候，只查询索引里包含的字段：

SELECT id, order_no, amount, created_at FROM orders 
WHERE user_id = 12345 
AND status = 'paid' 
AND created_at >= '2016-10-01' 
ORDER BY created_at DESC 
LIMIT 20;

这样，查询的时候，直接从索引里就能拿到所有需要的数据，不需要回表，效率更高。

但是，覆盖索引也有缺点：索引会占用更多的存储空间，而且插入和更新的时候，维护索引的成本也更高。所以，要根据实际情况，权衡是否使用覆盖索引。

在我们的场景下，因为需要查询订单的所有字段（SELECT *），所以不适合用覆盖索引，用联合索引就够了。

五、经验总结

这次MySQL慢查询排查，给了我很多经验和教训。

1. 索引很重要，但是不要滥用

索引，是数据库性能优化的最重要的手段之一。一个合适的索引，能让查询速度提升几百倍、几千倍。但是，索引也不是越多越好，因为：

• 索引会占用存储空间。
• 插入、更新、删除的时候，需要维护索引，会降低写操作的性能。
• 太多的索引，会让优化器选择索引的时候，产生困惑，可能选错索引。

所以，建索引的时候，要根据实际的查询场景，建合适的索引，不要盲目地给每个字段都建索引。

2. 建索引的原则

建索引的时候，有几个原则：

• 频繁作为查询条件的字段，应该建索引。比如，我们的user_id，经常作为查询条件，就应该建索引。
• 区分度高的字段，适合建索引。区分度，就是不同值的数量占总行数的比例。区分度越高，索引的效果越好。比如，user_id的区分度就很高，而status的区分度就很低（大部分都是'paid'）。
• 联合索引，要遵循最左前缀原则。联合索引的字段顺序，很重要，要把区分度高的、频繁作为查询条件的字段，放在前面。
• 排序、分组的字段，也可以考虑建索引。如果索引的顺序和排序、分组的顺序一致，就可以避免额外的排序，提高效率。
• 不要在小表上建索引。如果表的数据量很小（比如只有几百行），全表扫描也很快，建索引的意义不大，反而会增加维护成本。

3. 慢查询排查的步骤

遇到慢查询的时候，排查的步骤一般是：

1. 查看慢查询日志：找到慢查询的SQL。
2. 用EXPLAIN分析执行计划：看看SQL有没有走索引，扫描了多少行，有没有filesort、temporary等。
3. 查看表结构和索引：看看表有哪些索引，查询条件的字段有没有索引。
4. 确认数据量和数据分布：看看表的数据量，字段的数据分布，判断索引的效果。
5. 加索引或者优化SQL：根据分析结果，加合适的索引，或者优化SQL的写法。
6. 验证效果：优化之后，再次用EXPLAIN分析，实际执行SQL，看看效果。
7. 观察系统状态：优化之后，观察数据库的CPU、连接数、响应时间等，确认问题解决。

4. 预防慢查询的措施

除了排查和解决慢查询，更重要的是预防慢查询的发生：

• 设计表结构的时候，就要考虑索引。根据业务的查询场景，设计合适的索引。
• 开启慢查询日志。定期分析慢查询日志，发现潜在的慢查询，及时优化。
• 代码review的时候，关注SQL。看看SQL有没有走索引，有没有可能产生慢查询。
• 上线前，用EXPLAIN分析SQL。确保SQL的执行计划是合理的。
• 定期监控数据库的性能。关注CPU、连接数、慢查询数量等指标，发现异常及时处理。
• 数据库读写分离。把读操作分散到从库，减轻主库的压力。
• 加缓存。对于频繁查询、不经常变化的数据，用Redis等缓存，减轻数据库的压力。

六、写在最后

MySQL慢查询排查：一个索引救了整个系统。

这次事件，给我留下了深刻的印象。一个简单的索引，就能让查询速度提升1000多倍，就能让整个系统，从濒临崩溃，恢复正常。数据库的性能优化，真的很重要。

作为一个程序员，我们不仅要会写代码，还要懂数据库，懂SQL优化，懂索引设计。因为，很多时候，系统的瓶颈，不在代码，而在数据库。一个写得不好的SQL，一个缺失的索引，就能拖垮整个系统。

希望我的这次经历，能给大家一些参考。在遇到慢查询的时候，不要慌张，按照步骤，一步步排查，找到问题的根因，然后解决它。

最后，用一句话总结：

"索引不是万能的，但是没有索引，是万万不能的。"

愿我们都能写出高效的SQL，设计出合理的索引，让我们的系统，跑得又快又稳。