Sql Server之旅——第七站 为什么都说状态少的字段不能建索引

原文:Sql Server之旅——第七站 为什么都说状态少的字段不能建索引

 

  我们在学sqlserver的时候,大多教科书和前辈们都说状态少的字段不要建索引,由此带来的开销还不如不建索引,但是这句话有多少人真的知道,

或者说有多少人真的对此有比较深刻的理解,而不是听别人道听途说。。。这样记得快,忘记的也不慢。。。这篇我来分析一下这句话到底有几个意思。

 

一:现象

  首先我们还是用测试数据来发现问题,我先建立一个Person,有5个字段,建表sql如下:

DROP TABLE dbo.Person

CREATE TABLE Person(ID INT PRIMARY KEY IDENTITY,NAME VARCHAR(900),Age INT,Email VARCHAR(20),isMan INT )

-- 在isMan字段创建非聚集索引(0:女 1:男)
CREATE INDEX idx_isMan ON dbo.Person(isMan)

DECLARE @ch AS INT=0

WHILE @ch<=100000
BEGIN
    INSERT INTO dbo.Person(NAME,Age,Email,isMan) 
    VALUES
    (
      REPLICATE(CHAR(@ch),50),
      @ch,
      CAST(CAST(RAND()*1000000000 AS INT) AS VARCHAR(10))+'qq.com',
      @ch%2
    )
    SET @ch=@ch+1
END

通过上面的sql可以发现表中有5个字段,ID为聚集索引,isMan为非聚集索引,isMan也就是两种状态(0,1),并且插入10w条记录,截图如下:

Sql Server之旅——第七站 为什么都说状态少的字段不能建索引

 

 

sql都做完了,接下来要做的事情就是查询下: isMan=1的记录,如下图:

Sql Server之旅——第七站 为什么都说状态少的字段不能建索引Sql Server之旅——第七站 为什么都说状态少的字段不能建索引

 

    麻蛋。。。。哥哥明明是在isMan上做数据检索的,怎么就变成 “聚集索引扫描”了???这他么的什么意思嘛,居然不走我的“idx_isMan”索引,

却走他么的“聚集索引(PK__Person__3214EC276EF57B66)”。。。。同时也看到上面的”逻辑读取”为521。。。说明在内存中走了521个数据页。

但是我不服呀。。。我一定要让执行计划走我的索引。。。办法就是强制指定。。。如下图。

Sql Server之旅——第七站 为什么都说状态少的字段不能建索引Sql Server之旅——第七站 为什么都说状态少的字段不能建索引

Sql Server之旅——第七站 为什么都说状态少的字段不能建索引

看到上面的图,你是不是已经疯了。。。老子才捞5w的数据,你给我走了10w多次数据页。。。这么说1条记录要走两个数据页。。。而扫描聚集

索引才走521个数据页,相差200倍。。。难怪执行计划打死也不走“idx_isMan”这条索引。。。要是这样走了人家还不拿刀捅了sqlserver么???

 

二:分析原因

  现在很生气,整个人都不好了,为什么会这样???为了找出问题,我们还得看数据页。

1 DBCC TRACEON(3604,2588)
2 DBCC IND(Ctrip,Person,-1)

Sql Server之旅——第七站 为什么都说状态少的字段不能建索引Sql Server之旅——第七站 为什么都说状态少的字段不能建索引Sql Server之旅——第七站 为什么都说状态少的字段不能建索引

通过上面的三个图,大概可以看到,10w条数据用了697数据页,其中聚集索引有521个,非聚集索引为176个,这也说明了上面的”聚集索引扫描“

它自己所有的数据页来才捞出数据,同时还发现这两个索引都有一个共同特征就是,只有一个根节点(indexLevel=1)和无数个(indexLevel=0)

叶子节点,然后我脑子里面就有一幅图出来了。。。

 

Sql Server之旅——第七站 为什么都说状态少的字段不能建索引

上面就是我构思出来的图,这个专业一点的名字叫做书签查找。。。我们通过建立”idx_isMan“索引后,就会构建右半图的B树结构,其中索引记录

会存放两个值,一个是索引值isMan和一个聚集索引值ID,如果你不相信的话,可以通过DBCC Page去探索"idx_isMan"的索引页,你也可以通过

DBCC SHOW_STATISTICS 去查看,如图:

Sql Server之旅——第七站 为什么都说状态少的字段不能建索引

然后引擎通过“idx_isMan“扫描后,拿到了key值,但是非常可惜,我是select * 的,所以必须还要喷出记录中的Name,Emai等l字段,但是

”index_isMan"中并没有保存这几个字段,所以必须通过key去”聚集索引“的B树中去找。。。最后通过”聚集索引“的B树找到了目标记录,这也

就是所谓的执行计划中的”键查找“,然后喷出”Name,Email“等字段。。。。问题就在这里。。。因为我这样来回的蹦跶蹦跶。。。造成了找出

完整的一个记录,需要蹦跶2-3次数据页。。。具体的寻找记录,可参考图中的”紫色线条“,最后也就造成了10w多次蹦跶。。。

 

三:启示

     那这个例子给我们什么启示呢???仔细想想你就知道。。。使用非聚集索引,千万不要捞取过多的数据。。。因为过多的数据会造成在多个

B树中来回的蹦跶。。。想要做到捞取数据较少,就必须在高唯一性的字段上建立索引,这样的话在非聚集索引B树中符合的数据相对较少,也就

减少了我蹦跶到”主键索引“的B树次数。。。这样的话来回蹦跶的次数远远比”聚集索引“扫描来的实惠,对不对。。。

 

所以结论出来了:必须在唯一性较高的字段上建立非聚集索引。

上一篇:ORA-01552: 非系统表空间 'USERS' 不能使用系统回退段的处理


下一篇:终于可以通过GDB和BOCHS调试LINUX0.11内核了