查询调优和索引调优- Server

Question

有人要求我改进以下查询的性能。我不能修改任何现有的索引，但我可以创建新的索引。

表结构可以在此SQL Fiddle中找到。

表的行大小(来自开发-查询计划和统计信息等将从开发中提供)

dbo.Logs        --6620130
dbo.LogMobiles  --6620082

供参考，生产中的表大小如下

dbo.Logs        --271856036
dbo.LogMobiles  --269611865

在运行查询之前，我更新了表上的统计信息，如下所示

UPDATE STATISTICS dbo.Logs ;
UPDATE STATISTICS dbo.LogMobiles ;
DBCC UPDATEUSAGE(db1, 'dbo.Logs');
DBCC UPDATEUSAGE(db1, 'dbo.LogMobiles');

查询如下

DECLARE
     @rpStartDateTime       DATETIME    = '20180101'
    ,@rpEndDateTime         DATETIME    = '20180131' ;
DECLARE
     @iStart INT = DATEDIFF(SECOND, '19700101', @rpStartDateTime)
    ,@iEnd  INT = DATEDIFF(SECOND, '19700101', @rpEndDateTime);

SELECT
      ROW_NUMBER()OVER(PARTITION BY L.NodeId ORDER BY L.NodeId, L.GPSTime ASC)
    , V.NodeId
    , V.UnitMaxSpeed
    , M.DriverNodeId1
    , M.StatusText
    , M.Speed
    , ISNULL(S.StreetMaxSpeed,200)
FROM
    #xMobiles               V
INNER JOIN 
    dbo.Logs        L   ON  L.NodeId = V.NodeId
INNER JOIN
    dbo.LogMobiles  M   ON  M.LogId = L.LogId
    --Must have Drivers identifiable
INNER JOIN
    #xDrivers       DN  ON  DN.DriverNodeId = M.DriverNodeId1
LEFT JOIN
    dbo.Streets     S   ON  S.StreetId = M.StreetId
WHERE
    L.GPSTime >= @iStart AND L.GPSTime <= @iEnd
ORDER BY L.NodeId, L.GPSTime
OPTION (MAXDOP 1) ;

LogId from dbo.LogMobiles是一个从dbo.Logs引用LogId (PK)的FK。

下面是没有附加索引的查询计划

逻辑读取如下

从该计划中，来自dbo.Logs的实际行数为230768，与最终输出相匹配。来自dbo.LogMobiles的实际行数(来自聚集索引扫描)为5676246，这是24倍。

执行时间~4秒。

在one2one和dbo.Logs之间应该存在一种dbo.LogMobiles关系(尽管其他功能可以将行记录到dbo.Logs)，正如您所看到的，逻辑读取的数量和从这些表检索的实际行数之间有很大的差别。

然后我在dbo.LogMobiles上添加了以下索引

CREATE NONCLUSTERED INDEX IX_LogId_SBR ON dbo.LogMobiles ( DriverNodeId1 ASC, LogId ASC ) INCLUDE ( Speed, StatusText, StreetId  )

这是带有新索引的计划

这减少了dbo.LogMobiles上的逻辑读取次数。

但将索引扫描返回的实际行数增加到6620082 (增加28倍)。

执行时间~4秒-相同。

接下来我该试试什么呢？

Answer 1

我是Oracle开发人员。根据我对Oracle性能调优的理解，这就是我将如何处理您的情况。

我看到了您SQL的三个入口点。

a. xMobiles
b. xDrivers
c. GPSTime endpoints.

每个入口点将产生不同的执行计划。基于执行计划树中行的选择性，需要动态地选择最佳计划。检查每一步为三个可能的执行计划生成的数据卷。这将给你一个很好的洞察的事实，“哪一个计划适合你的特定情况”。

关于您的第一个执行计划，您已经声明来自dbo.Logs的实际行数为230768，聚集索引(CI)扫描显示为5676246 (24倍以上)。

请注意“查找”和“扫描”之间的区别。IX_Log_NodeID1是通过索引查找访问的。这使用为该索引传递的谓词值，并且只查找那些与谓词值匹配的字符串。它不会扫描整个索引。然而，在执行计划的第二步中，整个CI PK_LogMobiles被扫描到内存中，然后执行嵌套循环。这不是最理想的。

这是我们的问题。在第二步中，必须通过CI查找访问PK_LogMobiles，在整个5676246个日志In中只查找230768个日志In。相反，整个CI被扫描。

在甲骨文中，当230768行分散在太多的页面(SQLServer)或块( Oracle )上时，就会出现这样的场景，从而导致主要部分/整个CI被扫描。这意味着这些记录没有足够的聚类以便查找。改进您所谓的CI的ClusteringFactor (Oracle术语)，以解决您的性能问题。