sql优化实战：从6秒+到2秒（使用索引）

今天客服反馈 客户在前两天查询一个移动端报表时报错了。

我看了一下报错信息，大致是timeout，这种错误基本可以确定是由于查询时间超过阀值（一般为3秒）。

sql代码如下：

 

SELECT  hh.EMP_ID ,
        hh.STORE_ID ,
        hh.pn1 ,
        SUM(hh.numb) numb ,
        SUM(hh.SJ) sj ,
        NULL zb
FROM      ( SELECT  po.EMP_ID ,
                    v.STORE_ID ,
                    v.UPLOAD_DATE ,
                    i.PN1 ,
                    CAST(iv.ITEM1 AS NUMERIC) numb ,
                    CAST(iv.ITEM1 AS NUMERIC)
                    * CAST(p.PRICE AS NUMERIC(10, 2)) SJ 
            FROM      TB_DR v WITH ( NOLOCK )
                    INNER HASH JOIN TB_DR_ITEM iv WITH ( NOLOCK ) ON iv.MAIN_ID = v.ID
                    INNER JOIN tb_org o WITH ( NOLOCK ) ON v.emp_id = o.emp_id 
                    INNER JOIN tb_org po WITH ( NOLOCK ) ON po.org_ID = o.PARENT_ORG_ID
                    INNER JOIN tb_PROD p WITH ( NOLOCK ) ON p.PROD_ID = iv.PROD_ID
                    INNER JOIN tb_DI i WITH ( NOLOCK ) ON i.DICT_ITEM_ID = p.BRAND_ID
            WHERE   LEFT(v.bdate, 7) = CONVERT(VARCHAR(7), GETDATE(), 120) 
                    AND v.CODE IN ( 'SB001', 'SB002','SB003')
                    AND po.emp_id = 12345
        ) hh
GROUP BY  hh.EMP_ID ,
        hh.STORE_ID ,
        hh.pn1

运行时间：大概是6秒。
表信息：tb_dr大概是500w条，tb_dr_item大概是2800w条数据，其他的表基本上都是几千条。

 

结果集：大致返回50条。

优化：由于tb_dr已有bdate字段的索引，没有看执行计划，但是由于 left 函数作用在bdate，导致这个索引用不上，同时考虑到tb_dr和tb_dr_item表主表-子表，子表通过main_id和主表的id关联，所以在join时不应该用hash，就算要用也应该是loop，所以改为：

 

SELECT  hh.EMP_ID ,
        hh.STORE_ID ,
        hh.pn1 ,
        SUM(hh.numb) numb ,
        SUM(hh.SJ) sj ,
        NULL zb
FROM      ( SELECT  po.EMP_ID ,
                    v.STORE_ID ,
                    v.UPLOAD_DATE ,
                    i.PN1 ,
                    CAST(iv.ITEM1 AS NUMERIC) numb ,
                    CAST(iv.ITEM1 AS NUMERIC)
                    * CAST(p.PROD_PRICE AS NUMERIC(10, 2)) SJ 
            FROM      TB_R v WITH ( NOLOCK )
                    INNER HASH JOIN TB_DR_ITEM iv WITH ( NOLOCK ) ON iv.MAIN_ID = v.ID
                    INNER JOIN tb_org o WITH ( NOLOCK ) ON v.emp_id = o.emp_id 
                    INNER JOIN tb_org po WITH ( NOLOCK ) ON po.org_ID = o.PARENT_ORG_ID
                    INNER JOIN tb_PROD p WITH ( NOLOCK ) ON p.PROD_ID = iv.PROD_ID
                    INNER JOIN tb_DI i WITH ( NOLOCK ) ON i.DICT_ITEM_ID = p.BRAND_ID
            WHERE   v.bdate like CONVERT(VARCHAR(7), GETDATE(), 120) +'%'
                    AND v.FUNC_CODE IN ( 'TB_006', 'TB_006_AT05',
                                            'TB_006_AT06',
                                            'TB_006_AT07',
                                            'TB_006_AT08',
                                            'TB_005_AT02',
                                            'TB_005_AT03',
                                            'TB_005_AT04',
                                            'TB_005_AT05',
                                            'TB_005_AT06',
                                            'TB_005_AT07',
                                            'TB_005_AT08',
                                            'TB_005_AT09' )
                    AND po.emp_id = 12345
        ) hh
GROUP BY  hh.EMP_ID ,
        hh.STORE_ID ,
        hh.pn1

 

其中最主要的是把LEFT(v.bdate, 7) = CONVERT(VARCHAR(7), GETDATE(), 120)  改为 v.biz_date like CONVERT(VARCHAR(7), GETDATE(), 120) +'%'，这样索引就能用上了。

查询速度从6秒降为2秒。