Rowstore索引和Columnstore索引方式对比分析


摘要:
列存储索引在sqlserver2012版本中开始引用的,
何时应该使用列存储、何时应使用传统的行存储索引,存在很多问题。微软对列存储的最新增强功能尤其如此。微软利用列存储索引推出一些令人印象深刻的性能提升。
例如,微软在他们的网站上指出,与传统的面向行的存储相比,列存储索引可以实现高达10倍的查询性能提升,并且在未压缩的数据大小上可以实现10倍的数据压缩率。
列存储的原始实施有一些重大限制。例如,您需要删除索引以修改基表中的数据。
另外,最初的实现并没有使用柱状格式来存储物理数据。



一、行存储和列存储区别

行存储:行存储索引是自SQL Server初始版本以来一直存在的传统风格。
Rowstore索引旨在通过使查询能够通过索引快速定位数据而不是扫描整个表来加快数据检索速度。
Rowstore数据按行和列进行逻辑组织,并物理存储在面向行的数据页中。
SQL Server通过数据页面在内部组织使用B-Tree结构的rowstore索引。
SQL Server支持聚簇和非聚簇索引。使用聚簇索引时,基表中的数据根据​​聚簇索引进行组织。
通过搜索特定值或检索小范围的值,Rowstore索引在寻求数据的查询上表现最佳。
————————————————–
列存储: 列存储索引也在逻辑上组织为包含行和列的表格,但数据物理存储为列式数据格式。
列存储索引适用于大数据集的大多数只读查询,如数据仓库工作负载。
列存储索引不太适合寻求特定单个值的查询。列通常包含相似的数据,这些数据可以高度压缩数据,从而提高内存利用率并显着减少磁盘使用量。
列存储索引可以是群集或非群集。群集和非群集列存储索引的功能相同。不同之处在于,集群式列存储索引为整个表提供主存储空间,而非聚簇索引是包含基础表中某些列副本的二级索引。
——————————————————————–
对于使用更多更新和查找操作的联机事务处理(OLTP)工作负载,行存储索引往往更好,
而对于使用更多读取操作的联机分析处理(OLAP)工作负载,列存储索引往往更好。
Rowstore索引在执行随机读取和写入时往往更好。列存储索引对于执行顺序读取和写入往往更好。



二、列存储的应用

列存储进行实时分析和OLTP应用
SQL Server列存储索引最具吸引力的地方是它们能够启用实时分析。
传统的BI和数据仓库应用程序通常不是使用实时数据构建的。
相反,它们通常是通过ETL过程构建的,ETL过程定期采用OLTP数据并将其加载到后端数据仓库中。这意味着决策数据不是最新的,
而是几个小时甚至几天。数据只与最后一次ETL数据加载时的数据相同。
列存储索引可以通过加速实时OLTP数据的查询性能来解决此问题,从而可以在没有ETL引起的延迟的情况下对实时数据执行BI和分析分析。

从SQL Server 2016开始,您还可以通过内存中OLTP表创建可更新的非群集列存储,从而可能提供查看实时分析所需的性能。非聚集列存储索引存储所选列的副本,
因此您确实需要额外的空间,但数据将被高度压缩。通过组合列存储和内存中OLTP,您可以在列存储索引上运行BI查询,并同时支持高性能的OLTP事务。
SQL Server还使您能够灵活地在列存储索引上拥有一个或多个非集群行存储索引,从而使您能够在基础列存储上执行高效的表查找。
————————————–

二、列存储的应用举例

列存储索引和行的性能比较。
下面的Microsoft ®上找到样本查询的GitHub ®使用AdventureworksDW2016_EXT数据库来说明在性能上的差异,
你可以得到使用列存储索引。


-----查询1:使用名为FactResellerSalesXL_PageCompressed的rowstore索引。
  SET STATISTICS IO和TIME操作用于记录查询所需的存储使用情况和已用时间。
USE [AdventureworksDW2016_EXT]
GO

DBCC DROPCLEANBUFFERS   ---清楚缓存
  
SET STATISTICS IO ON    ---打开属性开关
SET STATISTICS TIME ON

SELECT s.SalesTerritoryRegion,d.[CalendarYear],FirstName + ' ' + lastName as 'Employee',FORMAT(SUM(f.SalesAmount),'C') AS 'Total Sales',
SUM(f.OrderQuantity) as 'Order Quantity', COUNT(distinct f.SalesOrdernumber) as 'Number of Orders',
count(distinct f.Resellerkey) as 'Num of Resellers'
FROM FactResellerSalesXL_PageCompressed f
INNER JOIN [dbo].[DimDate] d ON f.OrderDateKey= d.Datekey
INNER JOIN [dbo].[DimSalesTerritory] s on s.SalesTerritoryKey=f.SalesTerritoryKey
INNER JOIN [dbo].[DimEmployee] e on e.EmployeeKey=f.EmployeeKey
WHERE FullDateAlternateKey between '1/1/2005' and '1/1/2007'
GROUP BY d.[CalendarYear],s.SalesTerritoryRegion,FirstName + ' ' + lastName
ORDER BY SalesTerritoryRegion,CalendarYear,[Total Sales] desc

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

 
------查询2下一个查询方法相同,它使用FactResellerSalesXL_CCI列存储索引而不是行存储。两个表中的数据都是相同的。同样,统计信息用于记录查询性能。

USE [AdventureworksDW2016_EXT]
GO

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT s.SalesTerritoryRegion,d.[CalendarYear],FirstName + ' ' + lastName as 'Employee',FORMAT(SUM(f.SalesAmount),'C') AS 'Total Sales',
SUM(f.OrderQuantity) as 'Order Quantity', COUNT(distinct f.SalesOrdernumber) as 'Number of Orders',
count(distinct f.Resellerkey) as 'Num of Resellers'
FROM FactResellerSalesXL_CCI f
INNER JOIN [dbo].[DimDate] d ON f.OrderDateKey= d.Datekey
INNER JOIN [dbo].[DimSalesTerritory] s on s.SalesTerritoryKey=f.SalesTerritoryKey
INNER JOIN [dbo].[DimEmployee] e on e.EmployeeKey=f.EmployeeKey
WHERE FullDateAlternateKey between '1/1/2005' and '1/1/2007'
GROUP BY d.[CalendarYear],s.SalesTerritoryRegion,FirstName + ' ' + lastName
ORDER BY SalesTerritoryRegion,CalendarYear,[Total Sales] desc

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

 


—-行存储查询1结果:
(42 rows affected)
Table ‘Worktable’. Scan count 3, logical reads 7099656, physical reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0.
Table ‘FactResellerSalesXL_PageCompressed’. Scan count 1, logical reads 87246, physical reads 1.
Table ‘DimDate’. Scan count 1, logical reads 59, physical reads 0.
Table ‘DimEmployee’. Scan count 1, logical reads 31, physical reads 1.
Table ‘DimSalesTerritory’. Scan count 1, logical reads 3, physical reads 1.
—————————————————————————
SQL Server Execution Times:
CPU time = 19515 ms, elapsed time = 20831 ms.
————————————————————————–
—-列存储查询2结果:
(42 rows affected)
Table ‘FactResellerSalesXL_CCI’. Scan count 1, logical reads 0, physical reads 0.
Table ‘FactResellerSalesXL_CCI’. Segment reads 7, segment skipped 5.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0.
Table ‘DimSalesTerritory’. Scan count 1, logical reads 3, physical reads 1.
Table ‘DimEmployee’. Scan count 1, logical reads 31, physical reads 1.
Table ‘DimDate’. Scan count 1, logical reads 59, physical reads 0.
————————————————————————-
SQL Server Execution Times:
CPU time = 2297 ms, elapsed time = 2803 ms.

查询1和查询显示的行存储和列存储查询的性能和差异

对于上面的例子,我们可以看到columnstore需要更少的I / O,并且提供了比rowstore更好的性能。本文附带的视频中提供了更详细的信息。
如果您有一些缓慢的查询,您认为可能会从使用列存储索引中受益,该怎么办?试用列索引索引并查看它是否适用于您很容易。
如果您创建列存储索引并且不提供您期望的性能优势,则可以通过简单地删除列存储索引来回滚。您的里程可能因列存储索引而异,
您看到的性能将取决于您的情况,但对于某些类型的查询,它可能比行存储有优势。