Error message here!

Hide Error message here!

忘记密码?

Error message here!

请输入正确邮箱

Hide Error message here!

密码丢失?请输入您的电子邮件地址。您将收到一个重设密码链接。

Error message here!

返回登录

Close

SQL Server之索引解析(二)

艾心❤ 2019-02-26 11:53:00 阅读数:249 评论数:0 点赞数:0 收藏数:0

1、堆表

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

1、1 非聚集索引

结构

叶子节点数据结构:行数据结构+Rid(8字节)

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115253791-2106341859.png)

  • 中间节点数据结构: 
    (非聚集非唯一索引)行数据结构+Page(4)+2+ Rid(8字节)

中间2字节有疑问?

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115254938-1082360688.png)

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

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115255873-778454799.png)

  • 堆表非聚集索引结构

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115256643-87688554.png)1、2 聚集索引表

组织结构

![索引与数据结构](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115257406-1161979151.jpg)1.2.1 聚集索引

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

如果叶子节点不够多时,根节点(Root Node)、中间节点(Branch Nodes)将不存在。

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

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115258400-602875030.png)

  • 插入操作对BTree影响

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115259041-1312914011.png)

  • 删除操作对索引树影响

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115300167-704201719.png)

  • 更新操作对索引树影响

注意事项

  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)

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115300861-1829885820.png)

  • 索引覆盖

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115301712-24330749.png)

避免聚集索引查找

最大键列数为 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.dmdbdatabasepageallocations(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)

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

堆表

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115302720-185368033.png)

聚集索引表

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115303426-939434217.png)

查询结果集,字段说明列

说明 PageFID

索引所在文件Id PagePid

索引所在页Id IAMFID

索引所在IAM文件Id IAMPID

索引所在IAM的页Id objectId

对象ID,表对象ID IndexId

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

索引所在分区编号 PartitionId

索引所在的分区Id IamChainType

该页存放的数据类型、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 databasefile number, -- the file number where the page is foundpage number, -- the page number within the fileprint 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,以表格信息查看

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115304169-871842770.png)

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115304875-1432719864.png)3.2 查询计划

查看索引情况

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

--dbcc showstatistics (TestDataUnIndex, PKTestDataUnIndex)

命令详细见

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

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115306154-487850814.png)

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115307393-1977125725.png)3.3 跟踪代码生成的SQL语句

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

参考文章

https://www.cnblogs.com/yx007/p/7268310.html

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

![image](https://img2018.cnblogs.com/blog/533598/201902/533598-20190226115308160-1190111906.png)

以下语句用于跟踪,在线运行时,SQL操作用时比较长的语句SELECT TOP 50totalworkertime/executioncount AS [Avg CPU Time], (SELECT SUBSTRING(text,statementstartoffset/2, (CASE WHEN statementendoffset= -1 then LEN(CONVERT(nvarchar(max), text)) /* 2 ELSE statementendoffset end -statementstartoffset)/2) FROM sys.dmexecsqltext(sqlhandle)) AS querytext,/*FROM sys.dm_exec_query_stats ORDER BY [Avg CPU Time] DESC

以下语句用于查询数据库死锁select requestsessionid,OBJECTNAME(resourceassociatedentityid) tableName from sys.dmtranlocks where resourcetype='OBJECT' use master go --检索死锁进程 select spid, blocked, loginame, lastbatch, status, cmd, hostname, programname from sysprocesses where spid in ( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0) select requestsessionid,OBJECTNAME(resourceassociatedentityid) tableName from sys.dmtranlocks where resourcetype='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

编程之旅,人生之路,不止于编程,还有诗和远方。
阅代码原理,看框架知识,学企业实践;
赏诗词,读日记,踏人生之路,观世界之行;