SQL查询优化

自从N软离职之后就用SQL很少了。就是写ORM访问层和平时连到数据库取数据的仪器或者简单单表查询用用。今天解决个SQL查询优化的问题，想起来原来我还会SQL。

什么是SQL优化：
一个查询目标不是只一种写法，不同写法可能有的性能很低，有的性能高很多。如果表里数据不多的话，那么怎么写都无所谓，如果表数据是千万上亿的数据量那么写法还是有讲究的。这就涉及到SQL查询优化，当年我在N软时候坐在总DBA旁边，学了一丢丢SQL的知识。

SQL出发点从建表的索引为出发点。写查询要不快速缩小数据范围的条件写前面。多个条件能按索引顺序写最好。确保数据库SQL执行引擎把SQL执行运用上索引。如果没索引的话得考虑补索引。

比如下面表（表数据有上亿条）：

如果我要按MsgType和BllType查询数据。如果表不建这个索引那么查询一个数据在上亿的数据量时候就得40多秒。见了索引之后按条件查询就是秒开。这就是一种优化，如果必须按要求条件查询，表里又没相关索引，就得考虑建索引，否则SQL执行引擎只能全表扫描了。数据越大执行越慢。

然后就是用as操作代替join表链接写法。

比如查一段时间范围指定检验项目结果大于15且诊断包含糖尿病的项目结果数据。最常想到的就是多表链接JOIN。这么查询在小表时候无所谓，但是这个场景是报告结果表数据有上亿的，报告有上亿的，标本表有上亿的。这样链接查询就会导致查询慢。二维链接后数据量是不可估量的。

SELECT b.CName, d.Symptom, a.Result FROM dbo.RP_VisitNumberReportResult a
LEFT JOIN dbo.BT_TestCode b ON a.TestCodeDR = b.RowID
LEFT JOIN dbo.RP_VisitNumberReport c ON c.RowID = a.VisitNumberReportDR
LEFT JOIN dbo.RP_VisitNumber d ON c.VisitNumberDR = d.RowID
WHERE a.AuthDate BETWEEN '20220101'
AND '20220512'
AND b.Code = 'S0046'
AND a.Result >= 15 AND d.Symptom like '%糖尿病%'

这时候就要用的as结合临时表的写法。
先第一级查询把项目结果按审核时间条件和结果条件把数据范围筛到一个很小范围。用as把相关表要查的列查到as为临时表的新列名。这样就得到一个小的临时表。

select * , (select t1.CName from dbo.BT_TestCode t1 where t1
                    .RowID = t.TestCodeDR) as TestCodeName, (
                    select t1.Code from dbo.BT_TestCode t1 where t1
                    .RowID = t.TestCodeDR) as TestCode, (
                    select t2.VisitNumberDR from dbo.RP_VisitNumberReport t2 where t2
                    .RowID = t.VisitNumberReportDR) as VisitNumberDR from dbo
                .RP_VisitNumberReportResult t where AuthDate BETWEEN '20220101'
                AND '20220512'
                AND Result >= 15) as tmp

然后基于一级临时表按一级表得到的项目代码筛选得到二级临时表

select VisitNumberDR, TestCode, TestCodeName, Result from(
            (select * , (select t1.CName from dbo.BT_TestCode t1 where t1
                    .RowID = t.TestCodeDR) as TestCodeName, (
                    select t1.Code from dbo.BT_TestCode t1 where t1
                    .RowID = t.TestCodeDR) as TestCode, (
                    select t2.VisitNumberDR from dbo.RP_VisitNumberReport t2 where t2
                    .RowID = t.VisitNumberReportDR) as VisitNumberDR from dbo
                .RP_VisitNumberReportResult t where AuthDate BETWEEN '20220101'
                AND '20220512'
                AND Result >= 15) as tmp) WHERE TestCode =
        'S0046'

然后基于二级表和as在二级表筛选项目的基础上同时加入标本诊断的列形成三级临时表tmp1

select * , (select Symptom from dbo.RP_VisitNumber tt where tt
    .RowID = tmp1.VisitNumberDR) as PatSymptom from(
    (select VisitNumberDR, TestCode, TestCodeName, Result from(
            (select * , (select t1.CName from dbo.BT_TestCode t1 where t1
                    .RowID = t.TestCodeDR) as TestCodeName, (
                    select t1.Code from dbo.BT_TestCode t1 where t1
                    .RowID = t.TestCodeDR) as TestCode, (
                    select t2.VisitNumberDR from dbo.RP_VisitNumberReport t2 where t2
                    .RowID = t.VisitNumberReportDR) as VisitNumberDR from dbo
                .RP_VisitNumberReportResult t where AuthDate BETWEEN '20220101'
                AND '20220512'
                AND Result >= 15) as tmp) WHERE TestCode =
        'S0046') as tmp1)

然后就按最终得到满足条件的临时表用诊断筛选得到要的数据

select * from(select * , (select Symptom from dbo.RP_VisitNumber tt where tt
    .RowID = tmp1.VisitNumberDR) as PatSymptom from(
    (select VisitNumberDR, TestCode, TestCodeName, Result from(
            (select * , (select t1.CName from dbo.BT_TestCode t1 where t1
                    .RowID = t.TestCodeDR) as TestCodeName, (
                    select t1.Code from dbo.BT_TestCode t1 where t1
                    .RowID = t.TestCodeDR) as TestCode, (
                    select t2.VisitNumberDR from dbo.RP_VisitNumberReport t2 where t2
                    .RowID = t.VisitNumberReportDR) as VisitNumberDR from dbo
                .RP_VisitNumberReportResult t where AuthDate BETWEEN '20220101'
                AND '20220512'
                AND Result >= 15) as tmp) WHERE TestCode =
        'S0046') as tmp1))
where PatSymptom = '糖尿病'

以上就是SQL优化的路子，速度提升很多。