在小厂实习了两个月，作为一个全都干的全栈工程师，笔者学习到了很多业务开发过程中的很实用的小技巧、优化的经验、方案设计的思路等。另外，这段实习的感想博客也已经排期了，所以这些内容就不在本文中呈现了。

本文就讲讲笔者在实习过程中经历的一些 SQL 优化方案。

为什么要优化 SQL 链接到标题

这个问题的答案其实是很显然的，接口有响应时间这一硬指标，响应时间是由网络延迟、服务器处理时间、数据库处理时间、中间价处理时间等组成。而绝大多数查询接口响应时间的瓶颈就在数据库处理时间上，因此优化 SQL 语句时期处理时间更短就十分重要了。

SQL 优化的基本思路链接到标题

这部分经典八股就引用一下 GPT 给的答案了。

优化查询逻辑：
- 简化查询：尽量避免复杂的子查询，可以考虑使用临时表或者物化视图来简化操作。
- 减少数据处理量：只查询需要的数据，使用SELECT具体字段而非SELECT *。
使用适当的索引：
- 创建索引可以显著提高查询速度，尤其是对于经常用于过滤、排序或分组的列。
- 使用复合索引对应多列查询的情况。
- 定期检查和优化索引，避免过多无用索引增加维护成本和影响写操作性能。
优化表结构：
- 数据归档：定期清理旧数据，减少表的大小。
- 使用合适的数据类型，避免数据类型不匹配导致的隐式转换。
- 考虑表分区，特别是对于大表，通过分区可以提高查询效率。
改进连接（Join）操作：
- 选择合适的连接类型（如INNER JOIN, LEFT JOIN等）。
- 在连接操作中使用索引，尤其是在外键和连接键上创建索引。
- 确保连接条件中的数据类型匹配，避免不必要的类型转换。
使用查询优化器的提示：
- 利用数据库提供的查询优化器（如MySQL的EXPLAIN命令），分析查询的执行计划。
- 根据执行计划调整查询语句和索引策略。
避免高成本操作：
- 尽量避免在WHERE子句中使用函数，这可能导致索引失效。
- 减少全表扫描，确保查询能够利用索引。
调整数据库配置：
- 调整缓存大小，优化内存使用。
- 调整并发设置，合理配置线程和连接数。
常规维护：
- 定期进行数据库维护，如更新统计信息、重建索引等。
- 监控数据库性能，及时发现并解决问题。

复杂查询的优化实例链接到标题

很多优化其实添加一下索引、减少数据处理量就可以完成，但也存在一些复杂情况，例如笔者遇到的这个。

接口需求链接到标题

模糊过滤黑名单关键词：在进行任何数据检索之前，首先要确保从产品表中排除所有含有黑名单关键词的数据单元。
查询条件：
- 按交易日查询：用户可以请求某一特定交易日的数据。查询时，系统需要返回该日所有未被黑名单关键词过滤的产品数据。
- 按列关键词查询最新数据：用户还可以根据a、b、c列中的某个关键词查询。这种查询要求返回每个符合条件的数据单元在其最新的交易日的数据。这需要对产品表进行日期比较和筛选，找出每个数据单元的最新记录。

优化前的 SQL 语句链接到标题

SELECT p1.*
FROM product p1
JOIN (
	SELECT a, b, c, MAX(date) AS max_date
	FROM product
	GROUP BY a, b, c
) p2 ON p1.a = p2.a 
	AND p1.b = p2.b 
	AND p1.c = p2.c 
	AND p1.date = p2.max_date
WHERE p1.a NOT LIKE '%硫酸%' 
	AND p1.a NOT LIKE '%铁%' 
	AND p1.a NOT LIKE '%贴水%'
	AND p1.a NOT LIKE '%国内矿%' 
	AND p1.a NOT LIKE '%钼深加工%' 
	AND p1.a NOT LIKE '%宏观经济%'
	AND p1.a NOT LIKE '%船用油%' 
	AND p1.a NOT LIKE '%电解铜%' 
	AND p1.b NOT LIKE '%升贴水%'
	AND p1.b NOT LIKE '%库存%' 
	AND p1.b NOT LIKE '%变化率%' 
	AND p1.c NOT LIKE '%加工费%'
	AND p1.c NOT LIKE '%大豆%' 
	AND p1.c NOT LIKE '%期货%' 
	AND p1.c NOT LIKE '%期货%'
	AND p1.c NOT LIKE '%库存%' 
	AND p1.c NOT LIKE '%价差%' 
	AND p1.c NOT LIKE '%溢价%'
	AND p1.c NOT LIKE '%升贴水%' 
	AND (p1.a LIKE '%氮气%' 
		OR p1.b LIKE '%氮气%' 
		OR p1.c LIKE '%氮气%')
	AND p1.`deleted_at` IS NULL 
ORDER BY (rate+0) DESC 
LIMIT 1000;

我们现看看在测试环境（300k）下的运行时长：

十分不容乐观，需要狠狠的优化。

优化思路链接到标题

首先我们发现 LIKE 表达式是以通配符开始的，这样的查询显然是无法使用索引的，子查询干脆就直接全表搜索了，这些都是很严重拉低效率的。我们现用 explain 命令验证前面无法使用索引的猜测是否正确。

两个大大的 ALL 宣告索引的死亡。

我们先想想怎样把数据的范围限制住，最直接的思路就是和上面的语句一样用一堆 AND 连接的 NOT LIKE 表达式，还有一个思路是我们先用一堆 OR 连接的 LIKE 表达式找到需要被筛除的数据，然后用 LEFT JOIN 和 IS NULL 来去掉需要被筛除的数据，留下的也就是我们需要的。

这两个思路显然是后者执行效率更高，因为 AND 连接的语句需要考虑所有条件都被满足，而 OR 连接的语句只需要考虑某一个条件被满足，我们让测试说话。

方案一

SELECT a, b, c, date
FROM product p1
WHERE p1.a NOT LIKE '%硫酸%'
	AND p1.a NOT LIKE '%铁%'
	AND p1.a NOT LIKE '%贴水%'
	AND p1.a NOT LIKE '%国内矿%'
	AND p1.a NOT LIKE '%钼深加工%'
	AND p1.a NOT LIKE '%宏观经济%'
	AND p1.a NOT LIKE '%船用油%'
	AND p1.a NOT LIKE '%电解铜%'
	AND p1.b NOT LIKE '%升贴水%'
	AND p1.b NOT LIKE '%库存%'
	AND p1.b NOT LIKE '%变化率%'
	AND p1.c NOT LIKE '%加工费%'
	AND p1.c NOT LIKE '%大豆%'
	AND p1.c NOT LIKE '%期货%'
	AND p1.c NOT LIKE '%期货%'
	AND p1.c NOT LIKE '%库存%'
	AND p1.c NOT LIKE '%价差%'
	AND p1.c NOT LIKE '%溢价%'
	AND p1.c NOT LIKE '%升贴水%'
	AND (p1.a LIKE '%氮气%' 
		 OR p1.b LIKE '%氮气%' 
		 OR p1.c LIKE '%氮气%');

方案二

SELECT a, b, c, date
FROM product pr1
LEFT JOIN (
	SELECT id
	FROM product
	WHERE a LIKE '%硫酸%'
		OR a LIKE '%铁%'
		OR a LIKE '%贴水%'
		OR a LIKE '%国内矿%'
		OR a LIKE '%钼深加工%'
		OR a LIKE '%宏观经济%'
		OR a LIKE '%船用油%'
		OR a LIKE '%电解铜%'
		OR b LIKE '%升贴水%'
		OR b LIKE '%库存%'
		OR b LIKE '%变化率%'
		OR c LIKE '%加工费%'
		OR c LIKE '%大豆%'
		OR c LIKE '%期货%'
		OR c LIKE '%库存%'
		OR c LIKE '%价差%'
		OR c LIKE '%溢价%'
		OR c LIKE '%升贴水%'
) pr2 ON pr1.id = pr2.id
WHERE pr2.id IS NULL
AND (pr1.a LIKE '%氮气%' 
	 OR pr1.b LIKE '%氮气%' 
	 OR pr1.c LIKE '%氮气%')

显然方案二比方案一快了近一倍，我们实现了第一步优化。

我们再优化第二点——查询最新交易日时使用全表查询。这一点其实相对来说比较容易了，我们只需要对刚刚控制范围后的数据进行分组，使用 max 函数得到最新的交易日即可。

我们看看优化前后的差异：

优化前：

SELECT a, b, c, MAX(date) AS max_date
FROM product
GROUP BY a, b, c;

优化后：

SELECT a, b, c, max(date) AS m_date
FROM (
	SELECT a, b, c, date
	FROM product pr1
	LEFT JOIN (
		SELECT id
		FROM product
		WHERE a LIKE '%硫酸%'
			OR a LIKE '%铁%'
			OR a LIKE '%贴水%'
			OR a LIKE '%国内矿%'
			OR a LIKE '%钼深加工%'
			OR a LIKE '%宏观经济%'
			OR a LIKE '%船用油%'
			OR a LIKE '%电解铜%'
			OR b LIKE '%升贴水%'
			OR b LIKE '%库存%'
			OR b LIKE '%变化率%'
			OR c LIKE '%加工费%'
			OR c LIKE '%大豆%'
			OR c LIKE '%期货%'
			OR c LIKE '%库存%'
			OR c LIKE '%价差%'
			OR c LIKE '%溢价%'
			OR c LIKE '%升贴水%'
	) pr2 ON pr1.id = pr2.id
	WHERE pr2.id IS NULL
		AND (pr1.a LIKE '%氮气%' 
			 OR pr1.b LIKE '%氮气%' 
			 OR pr1.c LIKE '%氮气%')
) AS ta
GROUP BY a, b, c;

效果也是十分显然的。

最后我们只需要把查询到的最新交易日数据用 INNER JOIN 和整张表进行连接即可得到需要的数据。

SELECT *
FROM product p 
INNER JOIN (
	SELECT a, b, c, max(date) AS m_date
	FROM (
		SELECT a, b, c, date
		FROM product pr1
		LEFT JOIN (
			SELECT id
			FROM product
			WHERE a LIKE '%硫酸%'
				OR a LIKE '%铁%'
				OR a LIKE '%贴水%'
				OR a LIKE '%国内矿%'
				OR a LIKE '%钼深加工%'
				OR a LIKE '%宏观经济%'
				OR a LIKE '%船用油%'
				OR a LIKE '%电解铜%'
				OR b LIKE '%升贴水%'
				OR b LIKE '%库存%'
				OR b LIKE '%变化率%'
				OR c LIKE '%加工费%'
				OR c LIKE '%大豆%'
				OR c LIKE '%期货%'
				OR c LIKE '%库存%'
				OR c LIKE '%价差%'
				OR c LIKE '%溢价%'
				OR c LIKE '%升贴水%'
		) pr2 ON pr1.id = pr2.id
		WHERE pr2.id IS NULL
		AND (pr1.a LIKE '%氮气%' 
			 OR pr1.b LIKE '%氮气%' 
			 OR pr1.c LIKE '%氮气%')
	) AS ta
GROUP BY a, b, c
) tb 
ON p.date = tb.m_date
AND p.a = tb.a
AND p.c = tb.c
AND p.b = tb.b 
ORDER BY p.a ,p.b,p.c,p.date;

我们看看最终的效率。

效果显著，我们看看生产环境（500k）环境下的情况：

优化前：

优化后：

总结链接到标题

这个复杂场景的优化总结下来其实就两步：

AND 连接查询满足所有条件的数据改为 OR 连接查询所有不满足条件的数据并用 LEFT JOIN 进行筛除。
先把数据的范围控制住，再在小范围的数据内进行处理。

记录一次优化 SQL

为什么要优化 SQL 链接到标题

SQL 优化的基本思路链接到标题

复杂查询的优化实例链接到标题

相关数据表链接到标题

接口需求链接到标题

优化前的 SQL 语句链接到标题

优化思路链接到标题

总结链接到标题

为什么要优化 SQL 链接到标题

SQL 优化的基本思路 链接到标题

复杂查询的优化实例 链接到标题

相关数据表 链接到标题

接口需求 链接到标题

优化前的 SQL 语句 链接到标题

优化思路 链接到标题

总结 链接到标题

SQL 优化的基本思路链接到标题

复杂查询的优化实例链接到标题

相关数据表链接到标题

接口需求链接到标题

优化前的 SQL 语句链接到标题

优化思路链接到标题

总结链接到标题