SQL Server索引结构的具体使用

索引是数据库的基础，只有先搞明白索引的结构，才能搞明白索引运行的逻辑

本文通过 索引表、数据页、执行计划、io统计、b+tree 来尽可能的介绍 sql 语句中 where 部分，和 select 部分 的运行逻辑

名词介绍

b+tree：一种数据结构

• 数据页：数据库保存数据的最小单位。(sql server一个数据页的大小是 8k，一个表中所有的数据都被保存到一个个的数据页中)
• 索引组织表：大白话一张表有聚集索引就是索引组织表（把表中的数据页以 b+tree 的方式组织起来）
• 索引表：一个索引对应一张索引表，索引表中每条数据都对应一张数据页。

通过dbcc ind(数据库, 表名, 索引id) 命令可以获取到表中指定索引的索引表信息

通过dbcc page(数据库, 1, 数据页id, 3) 命令可以获取到某个数据页中的数据

b+tree结构

准备数据

在 sys.dm_db_index_physical_stats 这张系统表中

index_depth 表示索引的深度 （对应上图b+tree就是树的高度）

page_cout 表示索引数据页的数量 （对应上图b+tree就是叶子节点的数量）

这里获取索引信息主要是为了 index_id

索引表

对 dbcc ind 中的数据进行一个总结

通过观察叶子节点的数据可以得到，每个节点都有一个前驱指针和后继指针，构成了一个双向链表

通过 indexlevel 这个字段区分 根节点、分支节点、叶子节点

通过 nextpagepid 和 prevpagepid 两个字段把相同深度的节点构成了一个双向链表

数据页

dbcc traceon(3604) — 打开跟踪标记，不打开的话 dbcc page 只能查看分支节点中的数据，不能查看叶子节点中的数据

根节点

分支节点

叶子节点

非聚集索引的叶子节点

对索引表和根节点对应的数据页，分支节点对应的数据页，叶子节点对应的数据页进行总结

聚集索引

　　叶子节点中保存的是 org_user 表中的数据

　　根节点和分支节点中保存的是指向下一级节点的条件

　　索引表中同级的节点都有一个前驱和后继指针，这两个指针把同级的节点构建成了一个双向链表

非聚集索引

　　根节点和分支节点与聚集索引一直，都是指向下一级节点的条件

　　叶子节点有区别包含 创建非聚集索引是指定的key、指向该行数据实际地址的key、保证索引唯一的key

　　　　username 就是创建索引时指定的，如果创建时指定多个，这里也会有多个

　　　　id 这个是指向这行数据真实地址的指针表结构不同这个key也不一样

　　　　　　索引组织表：这个key就是创建聚集索引时指定的 key

　　　　　　堆表：就值这个行数据所在堆表的地址

　　　　uniquifier 如果创建索引时指定该索引时唯一索引，那么这里就不会有这个字段，否则就会有这个字段用来区分重复的数据

通过索引表，找到 id = 66666 的这行数据所在的数据页　　　　

对上图进行解释

拿着 66666 从根节点指向的数据页开始找

66666 > 36017 所以就跳转到 491 这个数据页

66511 < 66666 ≤ 66669 所以就跳转到 2755 这个数据页

因为 2755 这个数据页已经是叶子节点了，直接在里面搜索 66666

就找到了这一行数据

回表

因为这条sql返回的字段是 select *

非聚集索引里面没有 age 这个字段

因此根据 username_66666 从非聚集索引中找到这条数据之后，根据 id 到聚集索引里面在查一次，找到 age 这个字段

覆盖索引

select id,username 非聚集索引里面这两个字段都有，所以就没有必要在查询聚集索引了

举一个例子

只有搞明白了索引运行的逻辑，结合执行计划等工具，才能搞明白什么情况下那些sql更好

谣言：

　　count(*) 和 count(列) 谁快，谁慢

　　首先这两种写法都不等价 count(*) 是所有的数据 count(列) null值不参与运算，所以如果count的某一列中包含了null值算出来的数据可能就有问题了

　　查询速度

　　　　count(*) 更块

　　　　count(列) 会受偏移量和字段中数据的大小影响

　　　　　　（通过 set statistics time on 可以非常简单的得出结论）

　　sql语句 大表写前面，小表写后面

　　　　当前数据库都会对sql进行优化，所以无所谓谁在前，谁在后

　　in 与 exists 谁好谁坏

　　　　当前数据库都会对sql进行优化，所以无所谓谁好，谁坏

　　这些坑人的谣言还有很多，有些在老版本的数据库是对的，在当前的数据库中已经过时了。

到此这篇关于sql server索引结构的具体使用的文章就介绍到这了,更多相关sql server 索引结构内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！