索引存储

索引存储

对于索引存储，2008开始有两种优化方式，都是通过去除重复的数据减少存放的空间，使得原有的存放空间减少。更少的空间意味着更少的页，更少的页意味着查询过程中更少的I/O请求。分别为行压缩和页压缩

1.行压缩

第一种：降低行的体积。行压缩通过改变行的存储形式来达到目的，可以用在堆或者B_Tree上，启用行压缩，对应的一下功能会被启用。

行的原数据该表

定长数据会以变长格式存放

数值型数据类型也会存放成变长格式

--创建两个测试表作为对比

IF OBJECT_ID('dbo.NoCompression') IS NOT NULL DROP TABLE dbo.NoCompressionIF OBJECT_ID('dbo.RowCompression') IS NOT NULL DROP TABLE dbo.RowCompressionSELECT SalesOrderID , SalesOrderDetailID , CarrierTrackingNumber , OrderQty , ProductID , SpecialOfferID , UnitPrice , UnitPriceDiscount , LineTotal , rowguid , ModifiedDateINTO dbo.NoCompressionFROM Sales.SalesOrderDetailSELECT SalesOrderID , SalesOrderDetailID , CarrierTrackingNumber , OrderQty , ProductID , SpecialOfferID , UnitPrice , UnitPriceDiscount , LineTotal , rowguid , ModifiedDateINTO dbo.RowCompressionFROM Sales.SalesOrderDetail

压缩功能是在CREATE 或ALTER INDEX语句的DATA_COMPRESSION上实现，压缩可以用于聚集和非聚集索引上。

下面的语句是行压缩，大概压缩了33%的空间

--没有压缩CREATE CLUSTERED INDEX CLIX_NoCompression ON dbo.NoCompression(SalesOrderID, SalesOrderDetailID);--行压缩CREATE CLUSTERED INDEX CLIX_RowCompression ON dbo.RowCompression(SalesOrderID, SalesOrderDetailID)WITH (DATA_COMPRESSION = ROW);--检查使用的页SELECT OBJECT_NAME(object_id) AS table_name , in_row_reserved_page_countFROM sys.dm_db_partition_statsWHERE object_id IN ( OBJECT_ID('dbo.NoCompression'), OBJECT_ID('dbo.RowCompression') )

有无行压缩的页数：

压缩并不仅仅带来存储空间的减少，还能通过减少数据页的方式提高查询性能。

SET STATISTICS IO ON SELECT SalesOrderID,SalesOrderDetailID , CarrierTrackingNumber FROM dbo.NoCompression WHERE salesorderID BETWEEN 51500 AND 5200 SELECT SalesOrderID,SalesOrderDetailID , CarrierTrackingNumber FROM dbo.RowCompression WHERE salesorderID BETWEEN 51500 AND 5200 SET STATISTICS IO OFF

对进行压缩时，需要考虑以下情况

1.压缩的前提是应该是对大表进行操作，

2.如果最大的函数超过了8060bytes，则压缩不能进行

3.非聚集索引不会继承堆或者聚集索引上的压缩位置。每个都需要手动进行

4.压缩时高频CPU开销的操作，不能频繁的进行

二。页压缩

页压缩也可以在堆和B-Tree结构中进行。页压缩通常比行压缩更有效，因为他包含了行压缩，前缀压缩和字典压缩。

在进行页压缩前，会先进行行压缩，然后在对页中前缀相同的数据进行压缩。

--创建测试表：

IF OBJECT_ID('dbo.PageCompression') IS NOT NULL DROP TABLE dbo.PageCompressionSELECT SalesOrderID ,SalesOrderDetailID ,CarrierTrackingNumber ,OrderQty ,ProductID ,SpecialOfferID ,UnitPrice ,UnitPriceDiscount ,LineTotal ,rowguid ,ModifiedDateINTO dbo.PageCompressionFROM Sales.SalesOrderDetail

进行压缩处理：

CREATE CLUSTERED INDEX CLIX_PageCompression ON dbo.PageCompression(SalesOrderID, SalesOrderDetailID)WITH (DATA_COMPRESSION = PAGE);SELECT OBJECT_NAME(object_id) AS table_name , in_row_reserved_page_countFROM sys.dm_db_partition_statsWHERE object_id IN ( OBJECT_ID('dbo.NoCompression'), OBJECT_ID('dbo.PageCompression') )

执行查询：

SET STATISTICS IO ON SELECT SalesOrderID,SalesOrderDetailID ,CarrierTrackingNumber FROM dbo.PageCompression WHERE SalesOrderID BETWEEN 51500 AND 5200SET STATISTICS IO OFF

索引视图：

由于权限原因，查询可能不会返回很多数据，这时候视图可能作为一种候选方案，对于仅查询的静态数据，创建索引视图也是一个很好的方案。

不用索引视图的情况

SET STATISTICS IO ON SELECT psc.Name, SUM(sod.LineTotal) AS SumLIneTotal, SUM(sod.OrderQty) AS SumOrderQty, AVG(sod.UnitPrice) AS AvgUnitPrice FROM Sales.SalesOrderDetail sod INNER JOIN Production.Product p ON sod.ProductID=p.ProductID INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID=psc.ProductSubcategoryID GROUP BY psc.Name ORDER BY psc.Name

创建索引视图：

CREATE VIEW dbo.ProductSubcategorySummry --用于创建索引视图 WITH SCHEMABINDING AS SELECT psc.Name, SUM(sod.LineTotal) AS SumLIneTotal, SUM(sod.OrderQty) AS SumOrderQty, AVG(sod.UnitPrice) AS AvgUnitPrice FROM Sales.SalesOrderDetail sod INNER JOIN Production.Product p ON sod.ProductID =p.ProductID INNER JOIN production.ProductSubcategory psc ON p.ProductSubcategoryID=psc.ProductSubcategoryID GROUP BY psc.Name;

--创建聚集索引 CREATE UNIQUE CLUSTERED INDEX CLIX_ProductSubcategorySummay ON dbo.ProductSubcategorySummry(Name) SET STATISTICS IO ON SELECT name,SumLineTotal,SumOrderQty,TotalUnitPrice/Occurances AS AvgUnitPrice FROM dbo.ProductSubcategorySummry ORDER BY name

在使用索引视图后，逻辑读下降了很多

索引视图在需要将多个表关联到一个单元中时非常有效，可以降低关联时的IO请求

索引视图的限制条件：

1.视图中所有列必须是确定的

2.索引视图必须使用SCHEMA_BINDING选项

3.聚集索引必须使用unique选项

4.引用的表必须具有带有架构名

5.某些汇总函数如AVG(),不能用于索引视图

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。