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都做完了，接下来要做的事情就是查询下： isMan=1的记录，如下图：

 

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

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

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

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

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

 

二：分析原因

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

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

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

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

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

 

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

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

DBCC SHOW_STATISTICS 去查看，如图：

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

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

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

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

 

三：启示

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

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

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

 

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

分类:  sql server

好文要顶  关注我  收藏该文   

一线码农
关注 - 56
粉丝 - 6493

荣誉： 推荐博客

+加关注

4

0

关注我

« 上一篇： Sql Server之旅——第六站 使用winHex利器加深理解数据页
» 下一篇： Sql Server之旅——第八站 复合索引和include索引到底有多大区别？

posted @  2015-01-29 00:39  一线码农 阅读( 3057) 评论( 17)  编辑  收藏

评论列表

  

#1楼 2015-01-29 01:04  会长   

更新速度太快了，我要把你的文章放到月刊中，你不介意吧，你发的比我整理的都快

支持(0) 反对(0)

  

#2楼 2015-01-29 07:00  罗里罗嗦夫斯基   

举例之所以scan是因为返回数据太大

结论也有待商确：唯一性较高的字段建立cluster index

类似isman之类的字段是可以建立non cluster index的，不过不要作为索引的前沿

支持(0) 反对(0)

  

#3楼 2015-01-29 09:12  fishstone   

嘛，我一般很少建单字段索引，影响应该不太大吧

支持(0) 反对(0)

  

#4楼 2015-01-29 09:17  摆脱菜鸟   

@ 罗里罗嗦夫斯基

引用

举例之所以scan是因为返回数据太大

结论也有待商确：唯一性较高的字段建立cluster index

类似isman之类的字段是可以建立no cluster index的，不过不要作为索引的前沿

楼主说的是必须在唯一性较高的字段上建立 非聚集索引。

另外想问个问题，，非聚集索引记录会存放两个值，一个是索引值isMan和一个聚集索引值ID，这个聚集索引值ID是什么，是聚集索引的列值还是表的主键？那如果表没有聚集索引以及主键的情况下呢？只有非聚集索引。
另外有个概念可能不是很清楚，数据页包含索引数据页和实际记录的数据页吧，索引数据页只能在有索引的表中存在？
索引页有多少是怎么计算出来的，依据页大小，以及索引列的长度？

最后一个问题，楼主那个交流群是多少，满了没有？

支持(0) 反对(0)

  

#5楼 2015-01-29 10:16  dotNetDR_   

那么问题还是来了，对于只存在0，1或者类似的字段。怎么设计搜索性能才会提升上去？博主可否在文中加一加内容？

支持(0) 反对(0)

  

#6楼 2015-01-29 11:19  john_masen   

@ dotNetDR_
其实本文讲了一个很常见的索引问题：即非聚集索引没有包含select语句中所有需要输出的字段时，数据库引擎不得不读取聚集索引来凑出所有的输出字段。 
虽然创建非聚集索引的时候可以include额外的字段来避免上述情况，但是同样需要付出索引膨胀以及检索效率下降，表插入性能下降。数据库优化就是拆东墙补西墙，关键是如何把握平衡。

支持(0) 反对(0)

  

#7楼 2015-01-29 17:29  堂堂88   

楼主键的是聚集索引,而不是非聚集索引啊。
我试了一下楼主脚本:

--if(object_id('Person') is not null)
-- 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  NONCLUSTERED INDEX idx_isMan ON dbo.Person(isMan)

--DECLARE @ch INT;
--set @ch = 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


select * from dbo.Person where isman=1

支持(0) 反对(0)

  

#10楼 [ 楼主]  2015-01-29 22:27  一线码农   

@ fishstone
影响大不大，先自己测试一下就明白啦~

支持(0) 反对(0)

  

#11楼 [ 楼主]  2015-01-29 22:32  一线码农   

@ 摆脱菜鸟
1.一个聚集索引值ID : 这个就是聚集索引的列值。

2. 如果没有聚集索引，那非聚集索引存放的是堆表的RowID。

3. 当你建立聚集索引之后，会将堆表数据页的数据全部灌到聚集索引数据页
中去了，并且按照索引列值排序，这也是为什么一个表只能有一个聚集索 引，数据页有很多种，堆表数据页，索引数据页，IAM数据页等等。

4. 索引页有多少，可以用DBCC ind来查看。

5. 群没有满员，可以随时加的~~

支持(0) 反对(0)

  

#12楼 [ 楼主]  2015-01-29 22:38  一线码农   

@ dotNetDR_
走表扫描了，要么少select出几个字段，争取做到索引覆盖或者索引连接。

支持(0) 反对(0)

  

#13楼 2015-01-29 23:10  会长   

@ 一线码农
博主放心，是非商业目的、非博客园官方的月刊，只为了在手机上方便阅读。文中给出了作者和原文链接。

支持(0) 反对(0)

  

#14楼 2015-01-30 12:18  dotNetDR_   

@ 一线码农

引用

@dotNetDR_
走表扫描了，要么少select出几个字段，争取做到索引覆盖或者索引连接。

明白了，尽量先用索引晒出一部分数据。

支持(0) 反对(0)

  

#15楼 2015-02-15 14:38  我是大菠萝   

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

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

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


以上的言论，不敢苟同；
博主所谓的过多的数据是指什么？记录数多还是column多？如果是记录数，那就不是键查找的问题，但如果是指column多，那和“必须在高唯一性的字段上建立索引，这样的话在非聚集索引B树中符合的数据相对较少，也就减少了我蹦跶到”主键索引“的B树次数”有什么关系？

在筛选性差的列上建索引不是完全的不可以，有些场景还是需要的；所谓的维护成本一定大于查询成本，更是没有依据；
从另一个角度看，真的在“高唯一性的字段”建索引就一定好么？拿用户表举例，在username上创建索引固然可以提高按用户名查找的效率，但由此带来的页拆分而导致的写入、查询性能下降更是不能忽视；

支持(0) 反对(0)

  

#16楼 2015-03-29 15:02  pursuer.chen   

这就是非聚集索引和聚集索引的区别，聚集索引不会像非聚集索引那样需要前后对比所有与该键值不一样的值。