[SkylerAI]零碎知识点05-SQL语句的优化

PS：
1.SQL语句涉及到的表名都使用“某表名”代替
2.仅作为自己总结学习，不喜走开

第一稿SQL（查询达到3分钟）：

select * from(
            SELECT ROW_NUMBER() OVER (order by t1.formState) as num,t1.*,t3.ORG_NAME,t4.LastPhotoURL FROM dbo.T_SDC_probation_interview_main t1
            INNER JOIN 某表名 t2
            ON t1.projectCode = t2.projectCode
            INNER JOIN
            (SELECT * FROM 某表名 WHERE leaving_date IS NULL)t3
            ON t1.empNumber=t3.EMPLOYEE_NUMBER
            LEFT JOIN
            (SELECT * FROM 某表名) t4
            ON t3.EMPLOYEE_NUMBER=t4.Employee_Number
            WHERE
            t2.employeeNumber=#{assignerNumber}
            AND t1.formState In (${searchState})
            <if test="searchContent!=null and searchContent!=''">
                AND (t1.empNumber LIKE '%${searchContent}%' OR t1.empName LIKE '%${searchContent}%' OR t1.evaluateEmpNumber LIKE '%${searchContent}%' OR t1.evaluateEmpName LIKE '%${searchContent}%')
            </if>
        ) x where x.num between 1 + #{strip} * (#{pages} - 1) and #{strip} * #{pages}

优化后SQL（查询大约3秒）：

SELECT ORG_NAME,SEX,EMPLOYEE_NUMBER INTO #temp1 FROM 某表名 WHERE leaving_date IS NULL
SELECT LastPhotoURL,Employee_Number INTO #temp2 FROM 某表名

SELECT * FROM(
            SELECT
            ROW_NUMBER() OVER (order by t1.formState) as num,
            t1.allotEvaluateDate,
            t1.declareDate,
            t1.empName,
            t1.empNumber,
            t1.evaluateEmpName,
            t1.evaluateEmpNumber,
            t1.formState,
            t1.interviewDate,
            t1.projectCode,
            #temp1.ORG_NAME as empOrgName,
            #temp1.SEX as empSex,
            #temp2.LastPhotoURL as empPhotoUrl
            FROM 某表名 t1
            INNER JOIN 某视图 t2
            ON t1.projectCode = t2.projectCode
            INNER JOIN #temp1
            ON t1.empNumber=#temp1.EMPLOYEE_NUMBER
            LEFT JOIN #temp2
            ON #temp1.EMPLOYEE_NUMBER=#temp2.Employee_Number
            WHERE
            t2.employeeNumber=#{assignerNumber}
            AND t1.formState In (${searchState})
            <if test="searchContent!=null and searchContent!=''">
                AND (t1.empNumber LIKE '%${searchContent}%' OR t1.empName LIKE '%${searchContent}%' OR t1.evaluateEmpNumber LIKE '%${searchContent}%' OR t1.evaluateEmpName LIKE '%${searchContent}%')
            </if>
        ) x WHERE x.num BETWEEN 1 + #{strip} * (#{pages} - 1) AND #{strip} * #{pages}

        DROP TABLE #temp1
        DROP TABLE #temp2

参考优化的标准：

1.减少*号的使用，尽量具体到字段名。
2.单表进行查询观察时间，两表进行关联查询观察时间，目的是找出查询较慢的语句，将其提出作为临时表。

感觉优化后的查询速度也不算快，如您有好的建议，欢迎您指点！

2019年8月13日 更新
解释-----“找出查询较慢的语句，将其提出作为临时表”

提出作为临时表后至少要根据where条件筛掉将近50%的数据，类似以下语句则没必要提为临时表，反而会增加查询负担。

SELECT LastPhotoURL,Employee_Number INTO #temp2 FROM 某表名