SQLServer索引及统计信息

SQLServer索引及统计信息

索引除了提高性能，还能维护数据库。

索引是一种存储结构，主要以B-Tree形式存储信息。

B-Tree的定义：

1.每个节点最多只有m个节点(m>=2)

2.除了根节点和叶子节点外的每个节点上最少有m/2个子节点

3.如果根节点不是叶子节点，那么最少有两个子节点，整个B-Tree只能有一个根节点

4.带有k个子节点的非叶子节点包好k-1个键

5.每个叶子节点都出现在同一层

二：索引的主要类型

1.堆：堆的定义就是一堆数据，堆是有顺序的，在没有改动的情况下，他的顺序就是数据插入时的顺序，一旦数据改动，数据的顺序就会发生改变，而且不容易组织，所以没有聚集索引的数据表称为堆表，即使上面有非聚集索引，也还是堆表

2.聚集索引：每个表只能有一个聚集索引，能使用表按照创建时的首列顺序存放数据

3.非聚集索引：他和聚集索引一样，是B-Tree结构，不影响数据存储，只有堆和聚集索引才影响数据的存储，绝大部分索引的变种都是非聚集索引

4.列存储索引：用于应对大数据的查询操作

5.XML索引、空间索引、全文索引

三：索引元数据

sys.indexes:用于提供当前数据库中每个索引的信息，每个表、索引或者表变量都有对应一行

sys.index_columns:提供了索引内包含的列，也就是索引键

四：SQL Server 存储基础

按照数据页来存储数据和对象(不包括日志)，索引也是一种对象，所以也以页的形式存储在数据库中。

2.区：8个连续的页会组合成一个区，区不能小于8个页

3.页的组织：

堆、B-Tree、列式存储

(1).堆表：SQLServer默认的数据存储结构为堆表，任何没有聚集索引的表都成为堆表

--创建索引演示库 IF DB_ID('Index_Demo') IS NOT NULL DROP DATABASE Index_Demo GO --创建一个堆表 IF OBJECT_ID('Heap_Demo','U') IS NOT NULL DROP TABLE Heap_Demo CREATE TABLE Heap_Demo ( id INT IDENTITY(1,1), Name VARCHAR(10) ) GO SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Heap_Demo')

2.B-Tree意为平衡树，聚集索引和非聚集索引都使用B-Tree组织索引

以树形层次结构组织页面的，在这个结构中，页面有固定的顺序，页与页之间由双向链表用于组织

3.列存储结构：实际上是一个非聚集索引类型的存储结构，与传统的行存储索引不同，行存储需要存放表中的整行到索引中，而列存储只需要存储所需的列到索引中。可以进行页压缩

五：页碎片

SQL Server 可以在一个页中存放多条数据，但是当存放的数据超过8kb时，就会发生分页，根据表的页组织情况会有两种分页形式：Forwarded records和页拆分

Forwarded records这种分页形式仅发生在堆结构中。当一行数据被更新，并且大小已经无法放入该数据页时，会把这个数据页移到堆中的新数据页，并在新旧页中分别添加一个指针，标识这个数据在新旧页中的位置

从旧页指向新页的指针叫forwarded record pointer，存放在旧页中。

从新页指向旧页的指针叫back pointer存放在新页中

--创建测试环境

IF OBJECT_ID (N'HeapForwardedRecords','U') IS NOT NULL DROP TABLE HeapForwardedRecords CREATE TABLE HeapForwardedRecords ( RowID INT IDENTITY(1,1), DATA varchar(2500) ); INSERT INTO HeapForwardedRecords ( data ) SELECT TOP 24 REPLICATE('X',2000)--24行，每行2000bytes FROM sys.objects DECLARE @objectIN INT =OBJECT_ID(N'HeapForwardedRecords'); SELECT object_id,index_type_desc, page_count,record_count,forwarded_record_count FROM sys.dm_db_index_physical_stats(DB_ID(),@objectIN,NULL,NULL,'DETAILED'); GO DBCC IND(0,N'HeapForwardedRecords',-1)

一行数据占2000bytes，一个页有8060bytes，所以一个页最多可以存放4行数据，24条数据需要6页来存放

跟新每个页的其中两行数据，把长度变成2500，这个时候堆上的页会拆分出去。

UPDATE dbo.HeapForwardedRecords SET data=REPLICATE('X',2500) WHERE RowID%2=0; DECLARE @ObjectID INT = OBJECT_ID('dbo.HeapForwardedRecords'); SELECT object_id, index_type_desc, page_count,record_count,forwarded_record_count FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID,NULL,NULL,'DETAILED');

物理索引存储信息：

这种页拆分比表扫描更糟糕的情况，本身堆表只有扫描(表、索引扫描)一种方式，当页拆分过多时，需要扫描的页就多了。最终使得性能下降

2.页拆分

这种方式存储在B-Tree结构中，聚集索引和非聚集索引都以这种方式分页

CREATE TABLE dbo.ClusteredPagaSplits ( RowID INT IDENTITY(1,1), Data VARCHAR(2500), CONSTRAINT PK_ClusteredPageSplits PRIMARY KEY CLUSTERED(RowID) --添加聚集索引 ); INSERT INTO dbo.ClusteredPagaSplits ( Data ) SELECT TOP 24 REPLICATE('X',2000) FROM sys.objects; DECLARE @ObjectID INT =OBJECT_ID('dbo.ClusteredPagaSplits'); SELECT object_id,index_type_desc,index_level,page_count,record_count FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID,NULL,NULL,'DETAILED');

--分页后索引物理情况 UPDATE dbo.ClusteredPagaSplits SET data=REPLICATE('X',2500) WHERE RowID %2=0; DECLARE @ObjectID INT =OBJECT_ID('dbo.ClusteredPagaSplits'); SELECT object_id,index_type_desc,index_level,page_count,record_count FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID,NULL,NULL,'DETAILED');

分页会导致页的增多，影响性能，同时，分页过程中会对新页添加排他锁，这是有一个会话需要访问一条数据，这条数据被更新正在分页，分页后数据的物理位置很有可能已经改变，导致无法进行连续读，所以减少了一次读操作所能处理的页数

三：索引层级的统计信息

1.DBCC SHOW_STATISTICS

返回当前对象的统计信息

DBCC SHOW_STATISTICS(N'Sales.SalesOrderDetail',PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID)

2.目录视图

（1）sys.stats目录视图返回数据库每个创建了统计信息的对象名及一些基础信息

（2）sys.stats_columns

(3)函数STATS_DATE()

知道统计信息的更新格式：

STATS_DATE(OBJECT_ID,stats_id)

SELECT object_id,stats_id FROM sys.stats WHERE object_id=OBJECT_ID(N'Sales.SalesOrderDetail') AND name=N'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID' --查询统计信息时间 SELECT STATS_DATE(1154103152,1)

3.索引使用的统计信息：sys.dm_db_index_usage_stats

可以查看索引的使用频率

SELECT database_id,object_id,index_id,user_lookups,user_scans,user_updates,user_seeks FROM sys.dm_db_index_usage_stats WHERE database_id=DB_ID() AND object_id=OBJECT_ID(N'Sales.SalesOrderDetail')

--产生seek SELECT * FROM sales.SalesOrderDetail WHERE SalesOrderID BETWEEN 43659 AND 44659 --产生scan SELECT * FROM sales.SalesOrderDetail --产生lookup SELECT ProductID,CarrierTrackingNumber FROM sales.SalesOrderDetail WHERE ProductID=778

UPDATE sales.SalesOrderDetail SET UnitPriceDiscount=0.01 WHERE UnitPriceDiscount=0.00

检查一下DMV：

SELECT database_id,object_id,index_id, system_lookups,system_scans,system_seeks,system_updates, last_system_lookup,last_system_scan,last_system_seek, last_system_update FROM sys.dm_db_index_usage_stats WHERE database_id=DB_ID() AND object_id=OBJECT_ID(N'Sales.SalesOrderDetail')

索引操作的统计信息：   sys.dm_db_index_operational

展示了索引底层的信息，前面的DMV主要展示对索引的逻辑操作，而这个DMO展示的是索引的底层信息

--演示DML活动 IF OBJECT_ID(N'Oper_Index','U') IS NOT NULL DROP TABLE Oper_Index CREATE TABLE Oper_Index ( ID INT, DATA BIT, CONSTRAINT PK_Oper_ID PRIMARY KEY CLUSTERED(ID) ) GO INSERT INTO dbo.Oper_Index SELECT ROW_NUMBER() OVER(ORDER BY t.object_id), t.object_id%2 FROM sys.tables t GO DELETE FROM dbo.Oper_Index WHERE Data=0 GO UPDATE dbo.Oper_Index SET data=0 WHERE data=1 --查看操作信息 SELECT OBJECT_SCHEMA_NAME(ios.object_id)+'.'+OBJECT_NAME(ios.object_id) AS table_name, i.name AS index_name, ios.leaf_insert_count,ios.leaf_update_count,ios.leaf_delete_count,ios.leaf_ghost_count FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios INNER JOIN sys.indexes i ON i.object_id=ios.object_id AND i.index_id=ios.index_id WHERE ios.object_id=OBJECT_ID(N'Oper_Index') ORDER BY ios.range_scan_count DESC

SELECT 操作包含内容的解释

（1）range scan 堆或索引上的表扫描和范围扫描的积累值   Bingint

表中的所有行或者部分行通过扫描形式访问时，就会产生累积。不管扫描过程中访问了多少行数据，这个值只记录发生的次数

-- 查看 Sales.SalesOrderDetail 上的 range scan SELECT OBJECT_NAME(ios.object_id) AS table_name, i.name AS index_name, ios.range_scan_count FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'),NULL,NULL) ios INNER JOIN sys.indexes i ON i.object_id=ios.object_id AND i.index_id=ios.index_id ORDER BY ios.range_scan_count DESC --执行一次，产生rang scan SELECT * FROM sales.SalesOrderDetail --再次检查 SELECT OBJECT_NAME(ios.object_id) AS table_name, i.name AS index_name, ios.range_scan_count FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'),NULL,NULL) ios INNER JOIN sys.indexes i ON i.object_id=ios.object_id AND i.index_id=ios.index_id ORDER BY ios.range_scan_count DESC

可以看到range scan已经增加了1

（2）singleton lookup 从索引或堆中单行检索的累积值

当产生key lookup时，值就会增加，这和sys.dm_db_index_usage_stats中的user_lookups的收集机制基本相同。user_lookups每发生一次其值只增加1，sys.dm_db_index_usage_stats是针对影响的所有行，而不是次数

--查看Sales.SalesOrderDetail 上的 range scan SELECT OBJECT_NAME(ios.object_id) AS table_name, i.name AS index_name, ios.singleton_lookup_count FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'),NULL,NULL) ios INNER JOIN sys.indexes i ON i.object_id=ios.object_id AND i.index_id=ios.index_id ORDER BY ios.singleton_lookup_count DESC

--执行一次产生lookup SELECT ProductID,CarrierTrackingNumber FROM sales.SalesOrderDetail WHERE productid=778

--再次执行 SELECT OBJECT_NAME(ios.object_id) AS table_name, i.name AS index_name, ios.singleton_lookup_count FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'),NULL,NULL) ios INNER JOIN sys.indexes i ON i.object_id=ios.object_id AND i.index_id=ios.index_id ORDER BY ios.singleton_lookup_count DESC

(3)forwarded fetch

每发生一次forwarded record操作其值就会累计。

--forwarded record CREATE TABLE dbo.ForwardedRecords( ID INT IDENTITY(1,1), Value VARCHAR(8000) ); INSERT INTO dbo.ForwardedRecords ( Value ) SELECT replicate(type,500) FROM sys.objects; UPDATE dbo.ForwardedRecords SET Value=REPLICATE(Value,16) WHERE ID%3=1; --检查forwarded_fetch_count SELECT OBJECT_NAME(ios.object_id) AS table_name, i.name AS index_name, ios.forwarded_fetch_count FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID('dbo.ForwardedRecords'),NULL,NULL) ios INNER JOIN sys.indexes i ON i.object_id=ios.object_id AND i.index_id=ios.index_id ORDER BY ios.forwarded_fetch_count DESC

这个DMF可以查看在索引上的资源争用，以便研究索引是否合理创建

--行锁 的信息 SELECT SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty FROM Sales.SalesOrderDetail WHERE ProductID=710 --查询 SELECT OBJECT_NAME(ios.object_id) AS table_name,i.name AS index_name, ios.row_lock_count, ios.row_lock_wait_count, ios.row_lock_wait_in_ms FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID('Sales.SalesOrderDetail'),NULL,NULL) ios INNER JOIN sys.indexes i ON i.object_id=ios.object_id AND i.index_id=ios.index_id ORDER BY ios.range_scan_count DESC

查看page_lock的相关信息

SELECT OBJECT_NAME(ios.object_id) AS table_name, i.name AS index_name, ios.page_lock_count, ios.page_lock_wait_count, ios.page_lock_wait_in_ms FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID('Sales.SalesOrderDetail'),NULL,NULL) ios INNER JOIN sys.indexes i ON i.object_id=ios.object_id AND i.index_id=ios.index_id ORDER BY ios.range_scan_count DESC

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