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
