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树次数。。。这样的话来回蹦跶的次数远远比”聚集索引“扫描来的实惠，对不对。。。

 

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

-----------------------------------------------------------------------------------------------

　http://www.cnblogs.com/huangxincheng/p/4269891.html

这一篇再说下索引的最后一个主题，索引覆盖，当然学习比较好的捷径是看看那些大师们设计的索引，看从中能提取些什么营养的东西，下面我们看

看数据库中一个核心的Orders表。

　　

一：查看表的架构

<1> 先查看这个表的大概架构信息

1 --查看表的架构信息
2 SELECT c.column_id,c.name,t.name FROM sys.columns AS c 
3 JOIN sys.types t
4 ON c.system_type_id=t.system_type_id
5 WHERE c.object_id=object_id('O_Orders') 
6 ORDER BY c.column_id

 

从这个订单表来看大概有89个字段。。。还是蛮多的，可能有太多的历史原因吧，下面就有一个疑问来了，针对这么多的字段加上五花八门的类型，如何规划

好单列索引和复合索引。。。下面我们来看看这些专家们怎么设计的。

 

<2> 复合索引

　　首先声明一下，由于我的权限有限，不能进行DBCC IND，PAGE等命令，所以我没有能力判断下面的索引是include索引还是复合索引，所以这里统一叫成

复合索引吧。

1 SELECT name,type_desc FROM sys.indexes WHERE object_id=object_id('O_Orders')

从上面可以看到，有9个非聚集索引，1个聚集索引，然后可以通过 SHOW_STATISTICS 抽查几个索引看看到底关联了哪些字段，找到其中的二个索引，

覆盖多达6列，如索引"idx_order_status_2","IX_O_OrdersUID"。

DBCC SHOW_STATISTICS(O_Orders,idx_order_status_2)
DBCC SHOW_STATISTICS(O_Orders,IX_O_OrdersUID)

 

从这两个索引中关联的字段大概可以看出两点信息：

①：这些字段都比较小，为char(1)，smallint，bit这样的，自然表示的状态会比较少。

②：将表中多个状态少的字段挑选几个按照访问频率组合在一起做一个索引。

 

但是仔细想想，虽然原则上说状态少的字段不合适建索引，但是类似“订单状态(OrderStatus”这种字段，肯定是一个被频繁查询的列。。。既然是频繁的列，

肯定就要想办法优化，方法就是建复合索引，这样在复杂的sql中更加容易被撞上索引覆盖。

比如下面这样：

1 SET STATISTICS IO ON 
2 SELECT OrderStatus, ProcessStatus, SendTicketCity, FlightAgency, Eticket, OrderID
3 FROM dbo.Orders WHERE OrderStatus='P' AND ProcessStatus='1' AND SendTicketCity=1

然后继续挑选几个索引瞄一瞄。。。一般来说，覆盖1到2个列的索引都叫小索引。

1 DBCC SHOW_STATISTICS(O_Orders,idx_eid_orderdate)
2 DBCC SHOW_STATISTICS(O_Orders,IX_O_Order_FinishDate)

通过上面的索引大概可以看到，Eid和FinishDate这两列，一眼扫过就知道应该是一个唯一性比较高的列了，至于为什么要覆盖2列，那这个就是根据业务

和生产的滚动数据来决定了，那这样的索引有什么好处呢？同样更容易会撞到索引链接，也就是多条件中会走到多个索引，每个索引中贡献一些列刚好可以

满足select中的所有列。。。比如下面这样。

1 -- 可以看到，select中的所有列都是有idx_eid_orderdate 和 IX_O_Order_FinishDate 贡献
2 SELECT OrderID,FinishDate,PrepayType,Eid,OrderDate
3 FROM  dbo.O_Orders WHERE Eid='cctv1' AND FinishDate>2015-1-1

 

好了，就像园友说的，索引就是拆东墙补西墙，每建一个索引都需要评估它的利弊。