sql - 为什么索引查找会在统计数据中显示高扫描计数？

Question

无需详细说明，我有一个查询使用所有聚集和非聚集索引查找生成执行计划(听起来很有希望)。不幸的是，查询表现不佳，我很难理解为什么。

我正在使用set stats io on，可以看到其中一个表正在产生大量扫描和逻辑/物理读取:

SET statistics io ON

go

    SELECT order_number,
           audit_id,
           orderadmission_net_paid_delta / 100.00,
           'Admission',
           orderadmission_net_paid_delta / 100.00,
           performance_gl_description1,
           section_data1,
           performance_gl_code,
           price_type_data1,
           year(performance_start_date),
           month(performance_start_date),
           paymentmethod_type,
           paymentmethod_name,
           ''
    FROM   JCRProdReplication.dbo.ts_audit WITH (NOLOCK)
           JOIN JCRProdReplication.dbo.ts_order_admission WITH (NOLOCK)
             ON orderadmission_audit_id = audit_id
           LEFT JOIN JCRProdReplication.dbo.ts_order WITH (NOLOCK)
             ON order_id = orderadmission_order_id
           LEFT JOIN JCRProdReplication.dbo.ts_performance WITH (NOLOCK)
             ON performance_id = orderadmission_performance_id
           LEFT JOIN JCRProdReplication.dbo.ts_seat WITH (NOLOCK)
             ON seat_id = orderadmission_seat_id
           LEFT JOIN JCRProdReplication.dbo.ts_section WITH (NOLOCK)
             ON section_id = seat_section_id
           LEFT JOIN JCRProdReplication.dbo.ts_price_type WITH (NOLOCK)
             ON price_type_id = orderadmission_price_type_id
           LEFT JOIN JCRProdReplication.dbo.ts_order_payment WITH (NOLOCK)
             ON orderpayment_audit_id = audit_id
           LEFT JOIN JCRProdReplication.dbo.ts_payment_method WITH (NOLOCK)
             ON paymentmethod_id = orderpayment_paymentmethod_id
    WHERE  audit_time >= '20140107'
           AND audit_time < '20140108' 

<小时/>

(72174 row(s) affected)
Table 'ts_payment_method'. Scan count 0, logical reads 4180, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_price_type'. Scan count 0, logical reads 4184, physical reads 26, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_section'. Scan count 0, logical reads 4184, physical reads 28, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_seat'. Scan count 0, logical reads 6276, physical reads 2240, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_performance'. Scan count 0, logical reads 4184, physical reads 50, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_order'. Scan count 0, logical reads 8368, physical reads 820, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_order_admission'. Scan count 71877, logical reads 288490, physical reads 44104, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ts_audit'. Scan count 1, logical reads 252, physical reads 5, read-ahead reads 246, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

为了理解为什么索引查找实际上会在统计数据中显示大量扫描/读取，我的下一步是什么？ (我一整天都在谷歌上搜索，但没有找到解释)。

Answer 1

STATISTICS IO 报告的扫描计数很可能不是您想象的那样。

示例

USE tempdb;
SET NOCOUNT ON;

CREATE TABLE Num1 (N INT PRIMARY KEY);

CREATE TABLE Num2 (N INT);
CREATE CLUSTERED INDEX IX ON Num2(N);

INSERT INTO Num1
OUTPUT      inserted.N
INTO Num2
SELECT number
FROM   master..spt_values
WHERE  type = 'P'
       AND number BETWEEN 1 AND 1000

SET STATISTICS IO ON;

SELECT *, sys.fn_PhysLocFormatter(N2.%%physloc%%)
FROM   Num1 N1
       INNER LOOP JOIN Num2 N2
         ON N1.N = N2.N

SET STATISTICS IO OFF;

DROP TABLE Num1, Num2

给出计划

并输出

Table 'Num2'. Scan count 1000, logical reads 2002
Table 'Num1'. Scan count 1, logical reads 3

尽管计划显示了搜索，但仍报告了 1000 次扫描。

因为索引未声明为唯一，所以每次查找都是部分扫描。索引会在数字中查找 1,000 次，然后每次查找时都需要扫描索引，直到找到与数字不匹配的第一行。

Num2.IX 索引有一个根页面和两个叶页面。

2,002 次逻辑读取分割如下。

大多数查找需要两次读取(单根页和单叶页)。其中两次查找(在我的例子中为编号 622 和 623)在读取两个叶页时进行了三次逻辑读取。

这些分别是第一页的最后一行和第二页的第一行。

对于数字622，它需要读取下一页的下一行以查看是否重复。对于号码623，解释是索引的结构如下。

根页面具有每个较低页面上可能包含的最低值。由于索引未声明为唯一，因此它还需要检查上一页，以防存在多个跨越页面边界的匹配行。

以下博客文章提供了有关解释 STATISTICS IO 输出的一些附加说明。

• Scan Count meaning in SET STATISTICS IO output: Part 1
• Scan Count meaning in SET STATISTICS IO output: Part 2