1、堆表



堆表通过IAM连接一起，查询时全表扫描。



1、1 非聚集索引



结构



叶子节点数据结构：行数据结构+Rid（8字节）



sqlserver给视图加索引 sql server增加索引_聚集索引

  • 中间节点数据结构： 

（非聚集非唯一索引）行数据结构+Page（4）+2+ Rid（8字节）



中间2字节有疑问？

sqlserver给视图加索引 sql server增加索引_数据库_02

  • （非聚集唯一索引）行数据结构+分割符?+ Page（4）

sqlserver给视图加索引 sql server增加索引_sqlserver给视图加索引_03

  • 堆表非聚集索引结构

sqlserver给视图加索引 sql server增加索引_sqlserver给视图加索引_04



1、2 聚集索引表



组织结构

sqlserver给视图加索引 sql server增加索引_聚集索引_05



1.2.1 聚集索引



聚集索引表由根节点（Root Node）、中间节点（Branch Nodes）、叶子节点组成。

如果叶子节点不够多时，根节点（Root Node）、中间节点（Branch Nodes）将不存在。



  • 根节点、中间节点行结构
  • 系统头部信息（2字节）+Key+&+PageId
  • 叶子节点
  • 参见行数据结构



sqlserver给视图加索引 sql server增加索引_sqlserver给视图加索引_06

  • 插入操作对BTree影响

sqlserver给视图加索引 sql server增加索引_sqlserver给视图加索引_07

  • 删除操作对索引树影响

sqlserver给视图加索引 sql server增加索引_数据结构与算法_08

  • 更新操作对索引树影响
注意事项



1. 聚集索引键值不能超过900字节，因为生成keyhashvalue时，如果大于900字节性能会有很大影响。Keyhashvalue用于查询页的数据行

2. 聚集索引键值尽量保持短，每页只有8096字节可用。减少中间节点的层数。

3. 聚集索引键值采用递增原则，有利于数据页连续性，减少BTree调整。



 



1.2.2 非聚集索引



  • 非聚集索引在索引表中数据结构
  • 根节点(root nodes)、中间节点(page nodes)结构：2字节系统信息+非聚集索引键值+ChildPage(4字节)+Key
  • 叶子节点leaf nodes数据结构：2字节系统信息+非聚集索引键值+ Key（keyhasvalue）

sqlserver给视图加索引 sql server增加索引_数据库_09

索引覆盖

sqlserver给视图加索引 sql server增加索引_聚集索引_10

避免聚集索引查找

最大键列数为 16，最大索引键大小为 900 字节

过滤索引

索引tree是否包含部分数据。一部分不需要建立索引，减少索引层数。



2、建立索引规则



  • 建立聚集索引规则
  • 唯一性：如果非唯一性，索引节点会增加一列唯一表示。
  • 静态的: 如果对聚集索引键值进行更新时，中间节点页会发生变化，叶子节点页也会发生变化。操作次数增加，页空间造成浪费。
  • 连续性：非连续性会造成页分拆，页空间浪费，碎片增多。
  • 键值大小：键值长度越长，中间节点的层数越多，读取层数越多，性能下降。
  • 索引覆盖 对常用查询指定列的索引可以适当增加列覆盖。
  • 非聚集索引
  • 数据密度原则：数据密度是指列值唯一的记录占总记录数的百分比，这个比率越高，则说明此列越适合建立索引。
  • 复合索引键列顺序：在索引中，索引的顺序主要由索引中的每一个键列确定，因此，对于复合索引，索引中的列顺序是很重要的，应该优先把数据密度大，选择性列，存储空间小的列放在索引键列的前面。
  • 选择性原则：选择性是满足条件的记录占总记录数的百分比，这个比率应该尽可能低，这样才能保证通过索引扫描后，只需要从基础表提取很少的数据。

3、相关工具



 



3.1 组织分析命令



DBCC IND

用于分析表组织和索引组织查询命令。

  • 命令行

DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )



  • 参数
  • Dbname:数据库名
  • Dbid：数据库Id
  • Objname：表名
  • Objid：表ID
  • nonclustered indid:非聚集索引ID，-2 根节点 -1 中间节点 Branch Nodes 0 叶子节点、1 所有节点



  • 下列查询语句等同于 DBCC IND

Select * from sys.dm_db_database_page_allocations(DB_ID(), object_id('TestData8000'),NULL,NULL,'DETAILED')

sys.dm_db_database_page_allocations(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)



  • @DatabaseId：数据库Id
  • @TableId:表名
  • @indexId：
  • @PartionId：分区Id



堆表

sqlserver给视图加索引 sql server增加索引_数据结构与算法_11

聚集索引表

sqlserver给视图加索引 sql server增加索引_数据结构与算法_12

查询结果集，字段说明

列

说明

PageFID

索引所在文件Id

PagePid

索引所在页Id

IAMFID

索引所在IAM文件Id

IAMPID

索引所在IAM的页Id

objectId

对象ID,表对象ID

IndexId

索引类型 0堆、1聚集索引、2-250非聚集索引

PartitionNumber

索引所在分区编号

PartitionId

索引所在的分区Id

Iam_Chain_Type

该页存放的数据类型、in-row data 数据页或索引页、Row-overflow-data 溢出数据行页 Blob data 大文件类型页

PageType

数据类型见页类型

IndexLevel

索引级别 null 根级，0 叶子级，其他索引级

NextPageFID

双链表前级文件Id

NextPagePID

双链表前级页Id

PrevPageFID

双链表后级文件Id

PrevPagePID

双链表后级页Id

DBCC Page

用于查看页数据信息。  



DBCC PAGE
(
['database name'|database id], -- can be the actual name or id of the database
file number, -- the file number where the page is found
page number, -- the page number within the file 
print option = [0|1|2|3] -- display option; each option provides differing levels of information
)



  • database name：数据库名
  • file Number:页所在文件Id
  • Page Number:页id
  • Print 0、1、2、3：不同的级别，3为最高级  

--DBCC IND('DataPageTestDb','TestData8000',-1) 先查看表在数据里页数据信息

--DBCC PAGE(DataPageTestDb,1,8,3) 以文本信息查看

--DBCC PAGE(DataPageTestDb,1,8,3) with tableresults，以表格信息查看

sqlserver给视图加索引 sql server增加索引_非聚集索引_13

sqlserver给视图加索引 sql server增加索引_聚集索引_14



3.2 查询计划



查看索引情况



--dbcc show_statistics ([tablename], [indexname])
--dbcc show_statistics (TestDataUnIndex, PK_TestDataUnIndex)



命令详细见

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms174384(v=sql.105)



-- 打开IO开销统计 set STATISTICS io ON

-- 打开执行时间统计 set STATISTICS TIME ON

-- Select * from Table



或

sqlserver给视图加索引 sql server增加索引_数据库_15

sqlserver给视图加索引 sql server增加索引_数据结构与算法_16



3.3 跟踪代码生成的SQL语句



Sql Profiler 用于跟踪程序生成的语句。

参考文章


下图用于跟踪Net sqlclient data provider 产生的语句,net体系应用。

sqlserver给视图加索引 sql server增加索引_sqlserver给视图加索引_17

以下语句用于跟踪，在线运行时，SQL操作用时比较长的语句 



SELECT TOP 50 

total_worker_time/execution_count AS [Avg CPU Time], 

(SELECT SUBSTRING(text,statement_start_offset/2, 

(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) 

FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, * 

FROM sys.dm_exec_query_stats 

ORDER BY [Avg CPU Time] DESC



以下语句用于查询数据库死锁 



select request_session_id,OBJECT_NAME(resource_associated_entity_id) tableName from 

sys.dm_tran_locks 

where resource_type='OBJECT' 

use master 

go 

--检索死锁进程 

select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name 

from sysprocesses 

where spid in 

( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0) 

select request_session_id,OBJECT_NAME(resource_associated_entity_id) tableName from 

sys.dm_tran_locks 

where resource_type='OBJECT'


 



4、其他



 



4.1 数据库字段类型及长度



 

类型

字节数

定长

变长

blob类型

uniqueidentifier

16

1

 

 

date

3

1

 

 

time

5

1

 

 

datetime2

8

1

 

 

datetimeoffset

10

1

 

 

tinyint

1

1

 

 

smallint

2

1

 

 

int

4

1

 

 

smalldatetime

4

1

 

 

real

4

1

 

 

money

8

1

 

 

datetime

8

1

 

 

float

8

1

 

 

sql_variant

8016

 

1

 

bit

1

1

 

 

decimal(18.2)

9

1

 

 

numeric(18.2)

9

1

 

 

varchar(max)

 

 

 

1

nvarchar(max)

 

 

 

1

varbinary(max)

 

 

 

1

XML

 

 

 

1

Image

 

 

 

1

text

 

 

 

 

ntext

 

 

 

 

varchar()

 

 

1

 

nvarchar()

 

 

1

 

varbinary()

 

 

1

 

char

 

1

 

 

nchar

 

1