你笑的时候全世界陪你一起笑,你哭的时候只有你一个人哭
Dio的性能测试经验总结 - 性能项目经验:SQL语句not in的优化
上一篇 /
下一篇 2015-12-01 10:34:10
/ 个人分类:性能测试
问题
新核心系统部署在IBM大型主机上,计算资源相当昂贵,所以性能测试时对耗时耗CPU的应用和SQL异常重视。今年夏天新核心性能测试时发现一条SQL非常消耗资源,SQL是一个自己和自己的单表not in查询(字段和表已经做了处理):
SELECT
MIN (CLUCOPCOD )
INTO
:H
FROM
TableA
WHERE
CLUPRO_COD = '01'
AND CLUBR = '9999'
AND CLUCOPCOD NOT IN (
SELECT
CLUCOPCOD
FROM
TableA
WHERE
CLUPRO_COD = :H
AND CLUBR = :H
) WITH UR
业务需求是从当前省市下查询未使用的最小公司代码,SQL实现是先查询系统中所有的公司代码(CLUBR=9999会记录所有公司代码),然后not in当前省市下已有的公司代码,最后MIN最小的。
这条SQL使用了(CLUPRO_COD,CLUBR)上的复合索引,但因为两个结果集非常大,所以存在大量的循环扫描,逻辑读和CPU消耗非常高。
优化
单表自身做循环查询,且性能又低下只能说明表设计的不好,或业务约束就有问题。但性能测试阶段已经很难推翻之前的设计,于是只能从SQL下手尽量优化了。
搜了下数据库SQL调优的很多建议,都不太推荐not in的使用,建议改用exists,于是分析了业务逻辑,尝试修改SQL如下:
SELECT MIN(T.CLUCOPCOD)
FROM
(SELECT A. CLUCOPCOD FROM TableA A
WHERE A. CLUPRO_COD = '01'
AND A. CLUBR = '9999') T LEFT JOIN TableA B
ON T.CLUCOPCOD = B. CLUCOPCOD
AND B. CLUPRO_COD =:H
AND B. CLUBR = :H
WHERE
B. CLUCOPCOD IS NULL
WITH UR;
抛弃使用not in,而用全量的CLUCOPCOD和本省下的CLUCOPCOD做左连接,然后通过CLUCOPCOD is null取反。
暂时解决
SQL修改后业务逻辑没有变化,性能效率上涨明显,但依然还不是最优的情况。限于时间我没有再继续深入优化(我也不是新核心性能测试组的人,只是临时支持),这个例子其实也说明了一个交易的性能从业务需求、架构和数据库设计阶段就已经大致确定了,后期的优化只能在有限的范围内调整。性能像是一条长长的河流,有时我们只兴奋于眼前的鹅卵石,却总是忽略了它的源头。
收藏
举报
TAG:
项目经验