执行查询优化大约需要一分钟时间

Question

有一个简单的表存储IIS日志。

表模式如下：

CREATE TABLE [dbo].[TBL_iisLog](
    [cdate] [varchar](50) NULL,
    [ctime] [varchar](50) NULL,
    [serverip] [varchar](50) NULL,
    [uri] [varchar](255) NULL,
    [port] [varchar](50) NULL,
    [username] [varchar](50) NULL,
    [clientip] [varchar](50) NULL,
    [useragent] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

请注意，我在以下字段中添加了这三个索引：

• username
• clientip
• （cdate和ctime）的组合

对于表，执行以下查询大约需要50-60秒：

SELECT TOP 10
    username Usename,
    (SELECT COUNT(DISTINCT SUBSTRING(useragent, PATINDEX('%SIG:+%',useragent) + 5, 36))
        FROM tbl_iislog AS SoftwareSignatureCountTempTable
        WHERE (PATINDEX('%SIG:+%',useragent) > 0)
            AND SoftwareSignatureCountTempTable.username = MainTBL.username
    ) AS SoftwareSignatureCount
    ,
    (SELECT COUNT(DISTINCT
        LEFT(useragent, IIF((PATINDEX('%VDB%',useragent) -1) > 0, PATINDEX('%VDB%',useragent) -1, 0))
        +
        RIGHT(useragent, IIF((PATINDEX('%BPC%',useragent) -1) > 0, PATINDEX('%BPC%',useragent) -1, 0))
        )
        FROM TBL_iislog UseragentTempTable
        WHERE UseragentTempTable.username = MainTBL.username
    ) AS UserAgentCount
    ,
    (SELECT COUNT(DISTINCT clientip)
        FROM tbl_iislog AS IPTempTable
        WHERE IPTempTable.username = MainTBL.username
    ) AS IPCount
    ,
    (SELECT COUNT(clientip)
        FROM tbl_iislog ConnectionsTempTable
        WHERE ConnectionsTempTable.uri = '/version_checker.ver'
        AND ConnectionsTempTable.username = MainTBL.username
    ) AS Connections
    FROM TBL_iisLog AS MainTBL
    WHERE (username LIKE 'softgsg-%') OR (username LIKE 'sg-%')
    GROUP BY username HAVING COUNT(clientip) > 0
    ORDER BY SoftwareSignatureCount DESC, Connections DESC

我将感谢任何帮助我优化查询的建议。

Answer 1

首先，其他人发表了评论，并且是真的。。。查询一团糟，有这么多字段级的select语句可能会降低性能，因为每个查询都是针对您正在运行的每个RECORD、每个COLUMN执行的。它们都是基于当前用户，只是碰巧是特定标准的IIF（）。我已经简化了，它可能需要一点调整，但应该会对你有很大帮助。

首先，作为HAVING子句基础的特定clientIP的计数实际上与该列无关，但最好只*指示记录存在，而不考虑特定列。此外，在同一查询级别中，通过执行SUM（IIF（））可以处理版本检查器的"连接"计数。

对于您的签名条目，我在同一次跑步中做了两次。首先，我通过MAX（IIF（））获取一个标志，以查看是否有任何记录没有"%SIG:%%"引用，然后还获取一个COUNT（与该签名查找不同）。这样，COUNT减去任何非签名（这些都将被视为空格"，因此无论有多少非"SIG"条目将给出实际最终计数，都最多扣除1个条目。

我也不知道为什么你的IP计数大于0…从IIS日志文件中，所有东西都应该有一个客户端IP，所以我认为这是无关紧要的，因此被删除了。

我还认为我严重简化了您的用户代理字段。如果找不到"%VDB%"或"%BPC%"条目，只需使用空字符串，否则获取每个条目的子字符串上下文。

因此，现在只需对表进行一次遍历，所有聚合都一次性完成，应该会有所帮助。现在，索引。由于你在where子句中使用了LIKE，但好的是你不喜欢前导的"%"，否则这将表明你正在寻找字符串"softgsg-"或"sg-"ANYwhere，你正在字符串的前导部分寻找它，该部分可以通过索引进行优化。

话虽如此，我会在的表格上有一个复合指数

（用户名，客户端IP）

由于您的列处理的是255甚至最大列宽的文本，所以我不想为了使索引成为"覆盖"索引而杀死它。

select
      PQ.*
   from 
      ( SELECT
              L.username,
              COUNT(*) TotalRecs,
              COUNT(DISTINCT clientip) DistinctIPs,
              SUM( IIF( L.uri = '/version_checker.ver', 1, 0 )) as Connections,
              MAX( IIF( PATINDEX('%SIG:+%', L.useragent) = 0, 1, 0 )) as NonSigEntries,
              COUNT( DISTINCT IIF( PATINDEX('%SIG:+%', L.useragent) = 0, ' ',
                                   SUBSTRING( L.useragent, PATINDEX('%SIG:+%', L.useragent) + 5, 36))) SoftwareSignatureCount,
              COUNT( DISTINCT 
                        IIF(PATINDEX('%VDB%', L.useragent) = 0, '', LEFT( L.useragent, PATINDEX('%VDB%', L.useragent) -1))
                      + IIF(PATINDEX('%BPC%', L.useragent) = 0, '', RIGHT( L.useragent, PATINDEX('%BPC%', L.useragent) -1))
                    ) AS UserAgentCount
           FROM 
              tbl_iislog L
           WHERE 
                 L.username LIKE 'softgsg-%'
              OR L.username LIKE 'sg-%'
           GROUP BY
              L.username ) PQ
   ORDER BY
      PQ.SoftwareSignatureCount - PQ.NonSigEntries DESC,
      PQ.Connections DESC

当我对查询提供的空表结构进行测试时，该查询运行。由于排序依据，我预包装了查询（PreQuery的别名PQ），这样我就有了软件签名计数和非签名条目标志的最终值，然后连接，这样我不必将相同的count（复杂语句）重新复制到两个字段列表中，并按…的顺序排序。有些引擎允许您使用列结果名称别名与整个表达式。