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