从海量数据表中筛选符合不同条件组合的数据的SQL优化
速度很慢的SQL脚本
SET NOCOUNT ON;
DECLARE @sn VARCHAR(200);
DECLARE @n INT;
DECLARE @sn_tab TABLE(id BIGINT,sn VARCHAR(200));
IF OBJECT_ID('tempdb..#tab_f1') IS NOT NULL DROP TABLE #tab_f1
CREATE TABLE #tab_f1(id BIGINT)
CREATE INDEX idx_f1_id ON #tab_f1(id)
IF OBJECT_ID('tempdb..#tab_f2') IS NOT NULL DROP TABLE #tab_f2
CREATE TABLE #tab_f2(id BIGINT)
CREATE INDEX idx_f2_id ON #tab_f2(id)
WHILE EXISTS(SELECT 1 FROM HMFG.dbo.t_sn_wip_240426 WHERE opstatus=0)
BEGIN
DELETE FROM @sn_tab;
INSERT INTO @sn_tab(id,sn)
SELECT TOP 1000 snwp_id,snwp_serial_number FROM HMFG.dbo.t_sn_wip_240426 WHERE opstatus=0;
INSERT INTO #tab_f1(id)
SELECT stb.id FROM @sn_tab stb WHERE EXISTS(SELECT 1 FROM HTPV.dbo.t_program_values_temp0426 tpv
WHERE tpv.pgvl_serial_number=stb.sn
AND tpv.pgvl_step_name = 'Efficiency & Power Factor Test 230V 5V PIN' AND tpv.pgvl_item_name = 'Output 1 Pin' AND tpv.pgvl_item_para = 'Vin=230.000 Fin=50.000,A/O/V=0.000' AND tpv.pgvl_values = '0')
INSERT INTO #tab_f2(id)
SELECT stb.id FROM @sn_tab stb WHERE EXISTS(SELECT 1 FROM HTPV.dbo.t_program_values_temp0426 tpv
WHERE tpv.pgvl_serial_number=stb.sn
AND tpv.pgvl_step_name = '静态测试' AND tpv.pgvl_item_name = '输入功率(W)' AND tpv.pgvl_item_para = 'Vin:230.0000,Fin:50.0000;LD NoLoad' AND tpv.pgvl_values = '0')
IF EXISTS(SELECT 1 FROM #tab_f1)
BEGIN
UPDATE HMFG.dbo.t_sn_wip_240426
SET f1=1
FROM HMFG.dbo.t_sn_wip_240426 wip INNER JOIN #tab_f1 f1 ON wip.snwp_id=f1.id;
END
IF EXISTS(SELECT 1 FROM #tab_f2)
BEGIN
UPDATE HMFG.dbo.t_sn_wip_240426
SET f2=1
FROM HMFG.dbo.t_sn_wip_240426 wip INNER JOIN #tab_f2 f2 ON wip.snwp_id=f2.id;
END
UPDATE HMFG.dbo.t_sn_wip_240426 SET opstatus=1
FROM HMFG.dbo.t_sn_wip_240426 wip
INNER JOIN @sn_tab st ON wip.snwp_id=st.id
SET @n= @@ROWCOUNT;
PRINT @n;
END
脚本主要存在以下问题:
-
循环处理:脚本使用了一个WHILE循环,循环直到处理完HMFG.dbo.t_sn_wip_240426表中所有opstatus=0的行。这种迭代方式可能会非常消耗资源,尤其是当这类行数众多时。循环处理会导致数据库引擎反复执行相同的查询逻辑,增加了数据库服务器的负担,且不利于充分利用数据库的批量处理能力。
-
重复查询与子查询:在每次循环中,脚本都会从HMFG.dbo.t_sn_wip_240426表中选取前1000个待处理的snwp_id和snwp_serial_number,然后分别对这些记录进行两次EXISTS子查询,以确定是否满足设置f1和f2标志的条件。这种做法不仅重复查询了HTPV.dbo.t_program_values_temp0426表,而且在数据量较大时,子查询可能会成为性能瓶颈。
-
临时表与索引:虽然您为临时表#tab_f1和#tab_f2创建了索引,但每次循环都要清空并重新插入数据,导致临时表的使用效率不高。同时,临时表的插入操作本身也增加了额外的写入开销。
-
多次更新:脚本通过两次UPDATE语句分别设置f1和f2标志,然后再进行一次UPDATE操作来设置opstatus。多次更新操作不仅增加了数据库事务的复杂性,还可能导致更多的锁竞争和资源消耗。
优化后代码
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tab_conditions') IS NOT NULL DROP TABLE #tab_conditions;
CREATE TABLE #tab_conditions (
snwp_id BIGINT PRIMARY KEY,
f1 BIT NOT NULL DEFAULT (0),
f2 BIT NOT NULL DEFAULT (0)
);
-- 预计算条件组合对应的tme_sn_wip_240426.snwp_id集合
WITH cte_condition_combinations AS (
SELECT DISTINCT
tpv.pgvl_serial_number,
CASE WHEN tpv.pgvl_step_name = 'Efficiency & Power Factor Test 230V 5V PIN'
AND tpv.pgvl_item_name = 'Output 1 Pin'
AND tpv.pgvl_item_para = 'Vin=230.000 Fin=50.000,A/O/V=0.000'
AND tpv.pgvl_values = '0' THEN 1 ELSE 0 END AS f1_condition_met,
CASE WHEN tpv.pgvl_step_name = '静态测试'
AND tpv.pgvl_item_name = '输入功率(W)'
AND tpv.pgvl_item_para = 'Vin:230.0000,Fin:50.0000;LD NoLoad'
AND tpv.pgvl_values = '0' THEN 1 ELSE 0 END AS f2_condition_met
FROM HTPV.dbo.tme_program_values_temp0426 tpv
)
-- 更新#tab_conditions,记录满足条件的snwp_id及其对应的f1/f2标志
INSERT INTO #tab_conditions (snwp_id, f1, f2)
SELECT wip.snwp_id, MAX(f1_condition_met) AS f1, MAX(f2_condition_met) AS f2
FROM HMFG.dbo.tme_sn_wip_240426 wip
INNER JOIN cte_condition_combinations cc ON wip.snwp_serial_number = cc.pgvl_serial_number
WHERE wip.opstatus = 0
GROUP BY wip.snwp_id;
-- 一次性更新HMFG.dbo.tme_sn_wip_240426表
UPDATE HMFG.dbo.tme_sn_wip_240426
SET f1 = t.f1,
f2 = t.f2,
opstatus = 1
FROM HMFG.dbo.tme_sn_wip_240426 wip
LEFT JOIN #tab_conditions t ON wip.snwp_id = t.snwp_id
WHERE wip.opstatus = 0;
-- 输出更新的行数
DECLARE @n INT = @@ROWCOUNT;
PRINT @n;
主要优化措施如下:
-
避免循环:原脚本使用WHILE循环逐批处理数据,这里改为一次性处理所有符合条件的数据。
-
预计算:通过CTE(公用表表达式)预先计算出HTPV.dbo.t_program_values_temp0426表中每个条件组合对应的HMFG.dbo.t_sn_wip_240426.snwp_id集合,减少后续查询的复杂度。
-
合并更新:将原脚本中分别更新f1和f2的两步操作合并为一次UPDATE语句,同时更新f1、f2和opstatus字段。
-
简化临时表:修改临时表#tab_f1和#tab_f2的结构,直接存储满足条件的snwp_id及其对应的f1和f2标志,省去了多次EXISTS子查询。
-
使用LEFT JOIN:在最终的UPDATE语句中,使用LEFT JOIN确保即使某些snwp_id在临时表中不存在(即不满足条件),也能正确更新其opstatus字段为1。
提高SQL水平,AI提出的几点建议:
1.理解数据模型与关联关系:
- 明确表间关系:在编写SQL查询时,首先要清楚各个表之间的关联关系,了解哪些字段是用于连接不同表的关键字段。
- 熟悉数据模型:理解每个表的结构、字段含义以及数据分布情况,这对于编写高效查询至关重要。
2.避免过度使用循环与临时表:
- 尽可能利用SQL的集算特性:SQL语言擅长处理集合操作,尽量避免使用循环等过程化编程思路。许多原本需要循环处理的任务,如批量更新、条件判断等,往往可以通过一次或几次集算查询实现。
- 合理使用临时表:临时表在某些场景下可以帮助简化查询逻辑或提高查询性能,但过度使用或不当使用(如频繁插入、删除)可能导致额外开销。评估是否真正需要临时表,以及如何最高效地使用它。
3.掌握SQL查询优化技巧:
- 使用有效索引:确保涉及的表有合适的索引,尤其是在JOIN、WHERE、GROUP BY等子句中使用的字段。索引可以大幅降低数据访问成本,提高查询速度。
- 避免全表扫描与子查询嵌套:尽可能减少全表扫描,尤其是当表数据量较大时。合理运用JOIN、子查询、窗口函数等替代嵌套子查询,以提高查询效率。
- 利用预计算与物化视图:对于复杂查询或频繁使用的查询片段,可以考虑使用CTE(公用表表达式)、临时表或物化视图进行预计算,简化主查询逻辑。
4.学习与实践SQL性能调优:
- 学习SQL性能分析方法:理解执行计划、查询统计信息、等待事件等概念,学会使用数据库提供的性能分析工具(如SQL Server的Execution Plan、MySQL的EXPLAIN等)。
- 实践性能调优案例:通过解决实际的性能问题,逐步积累经验。可以从简单查询开始,逐步挑战复杂查询的优化。
5.持续学习与跟进新技术:
- 学习SQL高级特性:如窗口函数、递归查询、分区表、并行查询等,这些特性在特定场景下能显著提升查询性能。
- 关注数据库技术发展:数据库技术不断进步,新的数据库产品、特性、优化方法层出不穷。定期关注相关资讯、文档、博客、论坛等,保持知识更新。