堆表通过 IAM 连接一起, 查询时全表扫描.
1,1 非聚集索引
结构
叶子节点数据结构: 行数据结构 + Rid(8 字节)
中间节点数据结构:
(非聚集非唯一索引)行数据结构 + Page(4)+2+ Rid(8 字节)
中间 2 字节有疑问?
(非聚集唯一索引)行数据结构 + 分割符?+ Page(4)
堆表非聚集索引结构
1,2 聚集索引表
组织结构
1.2.1 聚集索引
聚集索引表由根节点(Root Node), 中间节点(Branch Nodes), 叶子节点组成.
如果叶子节点不够多时, 根节点 (Root Node), 中间节点(Branch Nodes) 将不存在.
根节点, 中间节点行结构
系统头部信息(2 字节)+Key+&+PageId
叶子节点
参见行数据结构
插入操作对 BTree 影响
删除操作对索引树影响
更新操作对索引树影响
注意事项
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)
索引覆盖
避免聚集索引查找
最大键列数为 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
堆表
聚集索引表
查询结果集, 字段说明
列 | 说明 |
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 | 数据类型见 < a href="file:///E:/Thinking/#_页类型" ztid="234" ow="42" oh="17"> file:///E:/Thinking/#_页类型 |
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, 以表格信息查看
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
或
3.3 跟踪代码生成的 SQL 语句
Sql Profiler 用于跟踪程序生成的语句.
参考文章
https://www.cnblogs.com/yx007/p/7268310.html
下图用于跟踪 Net sqlclient data provider 产生的语句, net 体系应用.
以下语句用于跟踪, 在线运行时, 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 |
以上为本篇文章的主要内容, 希望大家多提提意见, 如果喜欢记得点个赞哦
来源: https://www.cnblogs.com/edison0621/p/10436353.html