我有一个表,其中包含大约100M个条目,有3个不同的租户,将行平均分割
我已经基于此表创建了一个索引视图和一个非聚集列存储。这两个索引都是根据Tenant编号进行分区的。每个分区大约有3000万行。
用这个分区的列存储查询视图需要2.6秒同一个没有分区的查询耗时2.4s(两种情况下的执行计划相同。在分区索引上的查询中,只查询一个分区(。
为什么分区会使查询速度变慢,而不是更快?
以下是详细信息:
--I create a view
CREATE VIEW dbo.myView
WITH SCHEMABINDING
AS
SELECT TenantNumber, Date, ProductId,
COUNT_BIG(*) rowCount,
SUM(TotalSales) TotalSales
FROM [dbo].[myTable]
Group by TenantNumber, Date, ProductId;
此视图的每个租户大约有3000万行。
--I create my partition scheme
CREATE PARTITION FUNCTION myRangeTenantNumber (int)
AS RANGE FOR VALUES (1,2,3);
CREATE PARTITION SCHEME mySchemeTenantNumber
AS PARTITION myRangeTenantNumber
ALL TO ('PRIMARY');
--I index my view
CREATE UNIQUE CLUSTERED INDEX idx_TenantDateProductId on
myView(TenantNumber, Date, ProductId
ON mySchemeTenantNumber(TenantNumber);
查询确认每个分区大约有3000万行。
--I add a columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX idx_columnstore
ON dbo.myView
(TenantNumber, SkuNumber, Date, TotalSales)
ON mySchemeTenantNumber(TenantNumber);
对于这个索引,我可以看到每个分区有大约30M行
--I query my view
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SET STATISTICS time ON;
SELECT Date, SUM(TotalSales) TotalSales
FROM myView WITH(NOEXPAND)
WHERE TenantNumber = 1 AND Date >= '2018-01-01' AND Date <'2021-01-01'
GROUP BY Date
OPTION(RECOMPILE);
SET STATISTICS time OFF;
SET STATISTICS IO OFF;
执行计划确认只查询了一个分区(在创建的3个分区中(,但执行速度仍然比查询没有分区的同一视图慢约10%
此外,这比我为每个租户手动创建索引视图慢了大约30%。
编辑:粘贴计划无法解析导出的计划,我将它们存储在以下pastebin中:这是我从非分区索引链接中得到的,也是我从分区索引链接获得的。
图表看起来一样:
sql计划
但在xml中,当依赖分区索引时,我可以看到3个区别:
在WaitStats部分,添加了一个WaitType
<Wait WaitType="IO_QUEUE_LIMIT" WaitTimeMs="47" WaitCount="4"
在RelOp中,RunTimePartitionSummary指定要查询的分区,正如预期的那样:
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="1">
<PartitionRange Start="1" End="1" />
</PartitionsAccessed>
</RunTimePartitionSummary>
IndexScan是有序的(当我查询具有非分区索引的视图时,它不是有序的(
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="true" Storage="ColumnStore">
并且它有一个SeekPredicate部分,该部分在具有非分区索引的视图的查询中也不存在:
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Column="PtnId1000" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
为什么对索引进行分区会导致额外的SeekPredicate和有序索引扫描?这可能是执行速度较慢的原因吗?
两个计划的CPU时间非常相似;正如预期的那样,对于分区的情况稍微低一些。分区计划中有更多的IO等待,这很可能是页面缓存状态的意外。在完全冷的缓存或完全热的缓存中运行两者都可以消除这种影响。
非分区
<WaitStats>
<Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="554" WaitCount="11" />
<Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="6" WaitCount="8197" />
</WaitStats>
<QueryTimeStats CpuTime="350" ElapsedTime="856" />
分区
<WaitStats>
<Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="755" WaitCount="34" />
<Wait WaitType="IO_QUEUE_LIMIT" WaitTimeMs="47" WaitCount="4" />
<Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="4" WaitCount="7909" />
</WaitStats>
<QueryTimeStats CpuTime="327" ElapsedTime="1099" />