关于MSSQL中的索引
2018-02-24 23:56 梦游0和1的世界 阅读(512) 评论(0) 编辑 收藏 举报MSSQL 中索引用的是B+树 这种数据结构
以内存为存储介质的数据结构中 红黑树是一种 对 查找插入删除 都有一定要求的 比较理想的数据结构
在以磁盘作为存储介质时 由于磁盘读取的特性,机械头需定位磁道,物理读取比内存读取慢了几百倍,所以为了减少物理读取 某些大牛改进出了B+树这样的数据结构
MSSQL 中 B+ 树 的深度 大多在 3-4 层 之间 因此 查找某个元素的时候 只需要物理扫描几次就可以定位到想要的数据所在的数据页
B+ 树 和 B 树 ,2-3树 等有同一个特性,就是用节点分裂来保持树的平衡, 当根节点也满需要分裂时出现新的根节点,树的整体高度+1,下面来看些实例
以下是脚本
drop table mytest create table mytest( id int , name varchar(10), remark varchar(1000) default('dwaojscalksncjkaskjlfbqaslnmdnkllasn') ) create nonclustered index nidxname on mytest (name) create clustered index cidxid on mytest(id) declare @i int =0 while @i<32000 begin declare @s varchar(10)='abcdefgxyz' declare @id int =0 set @id =RAND()*10000 --if not exists (select 1 from mytest where id=@id) --begin insert mytest values (@id,SUBSTRING(@s,CAST(rand()*10 as int),1)+ SUBSTRING(@s,CAST(rand()*10 as int),1)+ SUBSTRING(@s,CAST(rand()*10 as int),1)+ SUBSTRING(@s,CAST(rand()*10 as int),1)+ SUBSTRING(@s,CAST(rand()*10 as int),1) ,default) --end set @i=@i+1 end --又插入了1000条数据 truncate table DBCCINDALLPAGE INSERT INTO DBCCINDALLPAGE EXEC ('dbcc ind (test,mytest,-1) WITH NO_INFOMSGS') SELECT * FROM DBCCINDALLPAGE order by Index_Level desc dbcc traceon (2588,3604) dbcc ind (test,mytest,-1) dbcc page (test,1, 173,1) --根页 dbcc page (test,1, 173,3) dbcc page (test,1, 22238,3) dbcc page (test,1, 22239,3)
分裂前的索引的概况,箭头所指173 为根页
再来看下根页的信息 已有360个槽位 也就是B+树中的子节点 剩余空间995 (满页在8K 左右,目前已快接近分裂条件)
又插入2000条数据后,根分裂了,产生了新的根页,具体看图
如图可以看到22238 为新的根页 INDEX_LEVLE 为2 ,此时树的高度+1了
再来看看原来173页的情况,现在子节点数变成了183个 剩余空间也变成了接近半页
由于原根页满载 MSSQL 将173 的一半在数据移动到了22239 页 ,并用新的根页22238 指向了 他们
这就是B树类 数据结构保持自平衡的分裂特性 ,最后是两张 子节点分配情况的对比图
分裂前 173 页 指向 360 个子页 最大值9961
分裂后 173页 指向183 个子页 最大值4414 ,原先4414 -9961 范围的数据分配情况 搬到了新创建的22239 兄弟页上