最近在学习数据库索引,所以在这里记录下最近的学习心得。
热身学习。
1.二叉平衡树和B树B+树的概念需要了解。
2.了解二叉平衡树的旋转。
3.思考为何数据库索引不使用二叉平衡树而选择B树或者B+树。
4.思考B+树作为索引相对于B树的优点在哪里。
具体可以参考:
知识总结
1.聚集索引
叶子节点包含聚集键值和全部数据。
表中的数据顺序通过聚集键的顺序来维护,聚集索引树本身就包含了一个表。
单独的外链双链表来进行页之间的维护。也就是说在每页中是有序的,每个页也是有序的。
思考,那如果页的最后一条数据添加或者删除会有哪些情况?
思考,聚集键值唯一性,宽度,易变性因素对整个索引产生的影响。
唯一性在下面的问题中探讨。
宽度的影响首先影响本身B树的每个节点的度,其次辅助索叶子节点引用键值的成本增加。
易变性使其记录需要重新定位,容易产生页面分离和碎片。其次每个辅助索引需要修改。
思考,通过聚集键找到叶子节点的时候,将叶子节点的页面加载进来的时候是通过二分查找吗?
2.非聚集索引
叶子节点存储的是索引键值和【聚集键或者sqlserver生成物理标示符RID】
思考RID是sqlserver自动生成的,还是真实物理地址。?
真实的物理行号。
思考,为何聚集键值必须唯一。
假设聚集键值不唯一,聚集键为姓名,非聚集键值为身份证号,如果一个非聚集索引是唯一的如身 份证号,定位到一个姓名A如果存在多个人姓名为A则更新的是跟新多个人的姓名是不合理的,因为 身份证号是唯一的。
如果聚集键不一定则sqlserver会在必要时添加一个隐藏的唯一标识列来保证内部的唯一性。
思考索引和约束有哪些区别?
索引会建立真实的物理结构需要维护,而索引则是逻辑上的意义。
3.索引结构
对于聚簇索引表的聚簇索引结构如下。
-- 创建聚簇索引表 create table employee( id int not null identity, lastname Nchar(30) not null, firstname nchar(29) not null, middleinit nchar(1) null, ssn char(11) not null, othercolumns char(258) not null default 'jack'); alter table employee add constraint employeePK primary key clustered (id) select * from employee -- 80000条 insert into employee(lastname,firstname,middleinit,ssn,othercolumns) values('','','','','') -- 查询索引结构 select index_depth as 'Depth' ,index_level as 'Level' ,record_count ,page_count ,avg_page_space_used_in_percent as 'pgPercentFull' ,min_record_size_in_bytes as 'minLen' ,max_record_size_in_bytes as 'maxLen' ,avg_record_size_in_bytes as 'avgLen' from sys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('employee'),1,null,'detailed')
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]) The filenum and pagenum parameters are taken from the page IDs that come from various system tables and appear in DBCC or other system error messages. A page ID of, say, (1:354) has filenum = 1 and pagenum = 354. The printopt parameter has the following meanings: 0 - print just the page header 1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps) 2 - page header plus whole page hex dump 3 - page header plus detailed per-row interpretation database consistenecy checker,简称dbcc Trace flag 3604 is to print the output in query window. Since you have not given -1 parameter (DBCC TRACEON(6304,-1)), it would be session specific. Once you close the window, it would be cleared.
-- 寻找12345的记录。 --创建临时表 create table temp_table( PageFID tinyint, pagePID int , IAMFID tinyint, IAMPID int, objectID int, indexID tinyint, partitionNumber tinyint, partitionID bigint, iam_chain_type varchar(30), pagetype tinyint, indexLevel tinyint, nextpageFID tinyint, nextpagePID int, prePageFID tinyint, prepagePID int, primary key(PageFID,PagepID) ) --寻找索引id select * from sys.sysindexes where name='employeePK' --查询dbcc ind结果集 --此处需要索引ID truncate table temp_table insert temp_table exec ('dbcc ind (test,employee,1)') -- 找到根页 select indexLevel, PageFID, pagePID, prePageFID, prepagePID, nextpageFID, nextpagePID from temp_table order by indexLevel desc,prepagePID --查询根页的记录开始B树搜索 --LEVEL 2 dbcc page('test',1,1695,3) --LEVEL 1 dbcc page('test',1,1966,3) --LEVEL 0 dbcc traceon(3604) dbcc page('test',1,1690,3)
根据索引名称获取索引ID
根据索引ID找到级别2的Root页号
查询级别2的页内容,定位级别1的页号。
根据级别1的页号,查询级别1的内容,定位叶子节点的页号。
根据叶子节点的页号,查询叶子节点的内容。
聚簇索引表的非聚簇索引结构。
-- 修改原来的聚簇索引表结构 update employee set ssn = CAST(id as char(8))+'ssn' --添加UK索引 alter table employee add constraint employeeSSNUK unique nonclustered (ssn) --还是查找12345 -- 查询索引结构 select index_depth as 'Depth' ,index_level as 'Level' ,record_count ,page_count ,avg_page_space_used_in_percent as 'pgPercentFull' ,min_record_size_in_bytes as 'minLen' ,max_record_size_in_bytes as 'maxLen' ,avg_record_size_in_bytes as 'avgLen' from sys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('employee'),2,null,'detailed') --寻找索引ID 2 select * from sys.sysindexes where name='employeeSSNUK' -- 查询结果集 truncate table temp_table insert temp_table exec ('dbcc ind (test,employee,2)') -- 找到根页fID,pageID select indexLevel, PageFID, pagePID, prePageFID, prepagePID, nextpageFID, nextpagePID from temp_table order by indexLevel desc,prepagePID --查询根页内容找到叶子节点的页号 dbcc page('test',1,13216,3) --查询叶子节点的内容 存储的是聚集键 dbcc traceon(3604) dbcc page('test',1,9037,3)
查询索引结构
查询索引ID
寻找根页号需要fID和pageID
查询根页内容,根据范围寻找叶子节点的页号。
查询叶子节点的内容。
关于堆表索引结构
--创建堆表 create table employeeHeap( id int not null identity, lastname Nchar(30) not null, firstname nchar(29) not null, middleinit nchar(1) null, ssn char(11) not null, othercolumns char(258) not null default 'jack'); alter table employeeHeap add constraint employeeHeapPK primary key nonclustered (id) --查询索引ID select * from sys.sysindexes where name='employeeHeapPK' -- 查询索引的结构 select index_depth as 'Depth' ,index_level as 'Level' ,record_count ,page_count ,avg_page_space_used_in_percent as 'pgPercentFull' ,min_record_size_in_bytes as 'minLen' ,max_record_size_in_bytes as 'maxLen' ,avg_record_size_in_bytes as 'avgLen' from sys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('employeeHeap'),3,null,'detailed') truncate table temp_table insert temp_table exec ('dbcc ind (test,employeeHeap,3)') -- 查找12345 步骤 -- 找到根页 select indexLevel, PageFID, pagePID, prePageFID, prepagePID, nextpageFID, nextpagePID from temp_table order by indexLevel desc,prepagePID --level 1 7888 dbcc page('test',1,7888,3) --查看叶节点的内容 dbcc page('test',1,7830,3) -- 创建解析函数 create function convert_rids (@rid binary(8)) returns varchar(30) as begin return ( convert (varchar(5), convert(int,substring(@rid,6,1) +substring(@rid,5,1))) +':'+ convert(varchar(10),convert(int,substring(@rid,4,1) +substring(@rid,3,1)+substring(@rid,2,1)+substring(@rid,1,1))) +':'+ convert(varchar(5),convert(int,substring(@rid,8,1)+substring(@rid,7,1))) ) end --fileID,pageID,slotNum 解析rid select test.dbo.convert_rids(0x5126000001000400) --查看行的内容 dbcc traceon(3604) dbcc page('test',1,9809,3)
查询索引ID
根据索引ID查看索引结构
根据索引ID获取根页号
查看根页的内容获取子节点的fID和pageID
根据根页提供的pageID查看叶子节点的内容
叶子节点存储的是物理行号,
解析行号。
查看行数据。
相关推荐
四、用SQL建立索引 五、用事务管理器建立索引 六、创建索引的方法和索引的特征 1.创建索引的方法 2.索引的特征 七、索引的类型 1.聚簇索引的体系结构 2.非聚簇索引的体系结构 八、系统如何访问表中的数据
书中详细阐述了SQL Server处理查询、管理数据的相关内容,包括SQL Server架构和配置、跟踪/扩展事件、日志和恢复、索引、表格、查询优化、事务/并发以及DBCC。 《深入解析SQL Server 2008》适合中高级数据库开发...
书中详细阐述了SQL Server处理查询、管理数据的相关内容,包括SQL Server架构和配置、跟踪/扩展事件、日志和恢复、索引、表格、查询优化、事务/并发以及DBCC。 《深入解析SQL Server 2008》适合中高级数据库开发...
本书全面探讨了sql server 2008的内部工作原理。全书共分为11章,首先在第1章中详细介绍了sql server 2008... 运行dbcc时,sqlserver在内部检查什么 处理多个并发用户时,如何在5个隔离级别和2个并发模型中做出选择
全书共分为11章,首先在第1章中详细介绍了SQL Server 2008的架构和配置,然后在接下来的10个章节中深入探讨了SQL Server 2008数据存储和查询处理等各个方面的内部机制,包括:数据库和数据库文件、表、索引、跟踪、...
全书共分为11章,首先在第1章中详细介绍了SQL Server 2008的架构和配置,然后在接下来的10个章节中深入探讨了SQL Server 2008数据存储和查询处理等各个方面的内部机制,包括:数据库和数据库文件、表、索引、跟踪、...
SQL Server接触不久的朋友可以只看以下蓝色字体字,简单有用节省时间;如果是数据库基础不错的朋友,可以全看,欢迎探讨。 索引的概念 索引的用途:我们对数据查询及处理速度已成为衡量应用系统成败的标准,而采用...
SQL Server全文索引相信大家都有一定的了解,下面就为您介绍SQL Server全文索引的用法及相关的语句,希望可以让您对SQL Server全文索引能有更深的认识
【赛迪网-IT技术报道】SQL Server数据库查询速度慢的原因有很多,常见的有以下几种: 1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2、I/O吞吐量小,形成了瓶颈效应。 3、没有...
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
1.4 SQL Server 2000的新特性 1.4.1 改进了对Web的支持 1.4.2 改进了伸缩能力和可靠性 1.4.3 改进了开发和管理环境 1.5 样本数据库 1.5.1 样本数据库安装 1.5.2 样本数据库的设计目的和设计思想 1.5.3 数据库图解 ...
一、SQL Profiler 事件类 Stored Procedures\RPC:Completed TSQL\SQL:BatchCompleted 事件关键字段 EventSequence、EventClass、SPID、DatabaseName、Error、StartTime、TextData、 HostName、ClientProcessID、...
1.4 SQL Server 2000的新特性 1.4.1 改进了对Web的支持 1.4.2 改进了伸缩能力和可靠性 1.4.3 改进了开发和管理环境 1.5 样本数据库 1.5.1 样本数据库安装 1.5.2 样本数据库的设计目的和设计思想 1.5.3 数据库图解 ...
前言 前面几节都是讲的基础内容,本节我们讲讲索引性能优化,当对大数据进行处理时首先想到的就是索引,一旦...标签查找和键查找是一个意思,在SQL 2005之前叫Key Lookup。怎么解释,如何定义呢?首先我们不看定义,
SQLSERVER的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表driving table)将被最先处理,在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表,当SQLSERVER...