记一次慢查询优化：从2秒到2毫秒的蜕变

前言

在数据库优化领域，慢查询是一个常见的问题，它不仅影响系统的响应速度，还可能导致用户体验下降。在平时系统开发中，产品也会要求系统查询必须在几秒响应之内，作为后端开发人员具备系统优化能力是十分重要的，

本文将以个人工作实际项目分析入手，详细介绍如何通过优化SQL查询和增加索引，将一个原本耗时2秒的查询优化到仅需2毫秒，欢迎大家在评论区一起讨论。

1. 问题背景

本文主要是在项目中，发现有其中一个分页查询十分慢，遇到网路阻塞的情况下，可能需要8秒，一般情况也是需要2、3s，这其实算是必要慢的了，如图所示，通过浏览器查看需要2s多。

2. 问题分析

所以本人就从程序代码分析入手，逐步分析到底是什么原因导致的。经查看，其实该列表查询比较简单，重点是在SQL语句中，改SQL语句如下：

SELECT
	device.*,
	user_recode.uid 
	user_recode.start_time
	user_recode.game_id 
	user_recode.coin 
FROM
	device device
	LEFT JOIN game_user_recode user_recode ON device.id = user_recode.device_id 
	AND user_recode.use_status = 1  
WHERE
	device.`status` NOT IN (- 100 ) 
		AND device.use_flag = 1
ORDER BY
	user_recode.start_time DESC,
	device.id ASC;

上述SQL主要是主要目的是获取设备信息以及相关的用户记录，并根据用户记录的开始时间和设备ID进行排序，主要是device与game_user_recode多表查询，分析查询其执行顺序

1. FROM子句：
   • 首先，数据库会处理FROM子句，确定需要查询的表。在这个查询中，有两个表：device（别名为device）和game_user_recode（别名为user_recode）。
2. JOIN条件：
   • 接下来，数据库会处理JOIN条件。在这个查询中，使用了LEFT JOIN，并且JOIN条件包括device.id = user_recode.device_id和user_recode.use_status = 1。这意味着数据库会尝试将device表中的每一行与game_user_recode表中满足JOIN条件的行相匹配。
3. WHERE子句：
   • 然后，数据库会处理WHERE子句中的条件。在这个查询中，WHERE子句包括多个条件，如device.status NOT IN (-100)、device.use_flag = 0。这些条件会进一步过滤JOIN操作后的结果集。
4. SELECT子句：
   • 在确定了最终的结果集之后，数据库会处理SELECT子句，选择需要显示的列。在这个查询中，选择了device.*以及user_recode表中的多个列，并使用了别名来区分它们。
5. ORDER BY子句：
   • 最后，数据库会处理ORDER BY子句，对结果集进行排序。在这个查询中，首先按user_recode.start_time降序排序，然后按device.id升序排序。

接下来通过explain执行计划，查看上述SQL为什么那么慢，如图所示：

可以看到，user_recode表数据很多，需要扫描全表，查询ref的级别是ALL，也没有走索引，很显然问题就是出在这里，无索引，全部扫描，肯定耗时。

device device LEFT JOIN game_user_recode这里如果没有索引，将会在game_user_recode全表寻找device.use_status = 1的数据

3. 优化策略

通过上述分析在JOIN的时候导致查询编码，即发现user_recode.use_status = 1这个条件在全表扫描中消耗了大量时间。因此，决定在这个字段上增加索引。

CREATE INDEX idx_use_status ON user_recode(use_status);

4. 索引优化效果

增加索引后，查询的执行顺序和逻辑保持不变，但数据库引擎可以利用这个索引快速定位到所有use_status = 1的记录，然后只对这些记录进行连接操作，而不是整个表的500万条记录。这极大地减少了需要处理的数据量，从而显著提高了查询效率。再次进行explain查询分析，不再是全部扫描，并且查询ref级别是const。

5. 验证优化效果

在增加索引后，重新在系统列表查询，在浏览器中可以看到，发现执行时间从2秒大幅下降到2毫秒，优化效果显著。

总结

本文通过一个具体的案例，详细介绍了慢查询优化的过程和方法，手把手讲解如何分析慢SQL，最终通过增加适当的索引，可以显著提高SQL查询的执行效率，实现从2秒到2毫秒的蜕变。

数据库优化是一个持续的过程，需要不断地监控、分析和调整。

通过合理的索引设计和查询优化，可以显著提升系统的性能和响应速度，从而提供更好的用户体验。希望能为读者在实际工作中提供参考和借鉴。