探究一下索引

1.现在有一张表，数据量约20w

create table custinfo(
id      int,
name    char(10),
gender  char(1),
age     int,
address char(10),
email   char(10),
constraint custinfo_pk primary key (id)
)
create index custinfo_index on custinfo (name)
declare @i int
select @i = 0
while @i<200000
begin
    if cast(ceiling(rand()*9) as varchar(1))%2 = 0
    begin
        insert into custinfo values(@i,char(65+ceiling(rand()*25))+char(97+ceiling(rand()*25))+char(97+ceiling(rand()*25))+char(97+ceiling(rand()*25)),
            'M',cast(ceiling(rand()*60) as varchar(2)),'','@q.email')
    end
    else
    begin
        insert into custinfo values(@i,char(65+ceiling(rand()*25))+char(97+ceiling(rand()*25))+char(97+ceiling(rand()*25))+char(97+ceiling(rand()*25)),
            'F',cast(ceiling(rand()*60) as varchar(2)),'','@email')
    end
    select @i = @i+1
end

2.使用DBCC IND ( dbname, [dbo.tablename], -1)打印出索引的具体情况，导入DBCCResult表。
几个重要字段：

• PageType ：1-数据页面 2-索引页面 3-Lob_mixed_page 4-Lob_tree_page 10-IAM页面
• IndexID ：0-代表堆 1-代表聚集索引 2-250 代表非聚集索引 大于250就是text或image字段
• IndexLevel ：0-叶节点 >=2 中间节点,最大的为根节点
• PageId ：索引页id
• FileId ：索引所在文件ID

统计结果：

    select * from DBCCResult where PageType = 2 and IndexID = 1;
    --1个根 5个中间 1539个叶节点 聚集索引
    select * from DBCCResult where PageType = 2 and IndexID = 2;
    --1个根 5个中间 1192个叶节点 非聚集索引

3.使用DBCC PAGE ('dbname',file number,page number,print option = [0|1|2|3])打印每一页的情况

• DBCC PAGE([master],1,3660,3)取到一个聚集索引的叶节点(即数据页面),得到里面有130行记录，估算一下，130*1539 共约20万条记录

• DBCC PAGE([master],1,3486,3)取到一个非聚集索引的叶节点,得到里面有131行记录，估算一下，131*1192 共约15万条记录

---

为什么非聚集索引叶节点的记录要多这么多？

• 聚集索引叶节点(即数据页面)存放的是一行的记录。估算一下一行记录8k/130 = 63 字节，约等于表一行的大小。
• 非聚集索引叶节点存放的是指针，指针内容如上图所示为非聚集索引的键 + 主键。
• 由于非聚集索引存在重复键select distinct(name) from custinfo，所以估计的15万条记录是基本符合。

---

如上图，可以看到非聚集索引指针的全貌，回表查询也就很好理解了。如果查询的结果不存在于当前非聚集索引的键中，也不存在于聚集索引中，就要回表查询了。因为非聚集索引大概率有重复，所以在定位在数据页之后还有一个遍历的过程。

---

估算一下非聚集索引叶节点一行记录(一个指针)8k/131 = 62字节。好像略大，如图所示最后一个字段ROW SIZE为32。
这是填充因子在起作用，修改索引填充因子为80
create index custinfo_index on custinfo (name) with fillfactor=80
重新调用DCCB IND可以看到非聚集索引页减少了很多。

重新调用DCCB PAGE可以看到每一页存储的指针多了很多。