基础数据库查询优化

红太狼 2022-07-13 06:57 211阅读 0赞

昨天检查一位小兄弟的代码，发现执行起来很慢，就找找原因优化一下。

项目是C\# WebForm + SQLSERVER + EF 的。开了 SQLSERVER 的 PROFILER 监听，发现有连续几条 READS （硬盘读取次数）在3万左右的记录。查询的语句如下：

SELECT 
        [GroupBy1].[A1] AS [C1]
        FROM ( SELECT 
            COUNT(1) AS [A1]
            FROM [dbo].[Customers] AS [Extent1]
            WHERE ((DATEDIFF(d, [Extent1].[AddTime], GetDate())) <= 30) AND (5 = [Extent1].[SectionId])
        )  AS [GroupBy1]

语义大概是查询客户表，添加时间在30天内，部门ID是5的记录数量。这个表有几十万条记录，根据两个字段查数量，READS高肯定是没有经过优化。根据这个查询来找一下程序段，如下：

p => SqlFunctions.DateDiff("d", p.AddTime, DateTime.Now) <= 30 && p.SectionId == 5;

第一步，建立索引。由于使用这两个字段执行查询的地方很多，所以索引要建。我建立了一个索引，字段就是 AddTime 和 SectionId ，没有包含性字段。建立好后执行同样的语句，READS 降到了 1000 左右。虽然成功减少到30分之一，但和理想中的查询还是差很多。

第二步，优化语句。SqlFunctions 的调用肯定没有 < > = 这些比较符快，所以这里需要改一下。思考一下改成如下：

var d = DateTime.Now.AddDays(-30);
    p => p.AddTime >  d && p.SectionId == 5;

再执行一下，生成的SQL语句如下：

SELECT 
        [GroupBy1].[A1] AS [C1]
        FROM ( SELECT 
            COUNT(1) AS [A1]
            FROM [dbo].[Customers] AS [Extent1]
            WHERE ([Extent1].[AddTime] > convert(datetime, '2016-11-29 18:06:27.889', 121)) AND (5 = [Extent1].[SectionId])
        )  AS [GroupBy1]

READS 成功降到了个位数。 这样就没什么问题了。