你笑的时候全世界陪你一起笑,你哭的时候只有你一个人哭

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: 项目经验

MoeImouto的个人空间 引用 删除 MoeImouto   /   2016-06-07 17:20:20
5
 

评分:0

我来说两句

Open Toolbar