在线等，这个非聚集索引建的合不合理？

Question

idx_tbl_test_01非聚集索引是建在col_1、col_2、col_3上的，那按col_1、col_2查询时不用这个索引吗？查询执行计划中显示where col_1 = ? and col_2 = ? and col_3 = ?时走idx_tbl_test_01，担当where col_1 = ? and col_2 = ?时却走idx_tbl_test_00。

Answer 1

索引优化向导是指Database Engine Tuning Advisor吗？
是的话，我跑过了。没有任何建议。

Answer 2

没脱离实际啊。
在最常用的功能中，信息是按col_4 ASC,col_5 ASC,col_1 ASC,col_2 ASC,col_3 ASC排的，而且经常要按col_4进行范围查询。所以建了一个idx_tbl_test_00的聚集索引。
在辅助功能中，要能按col_1、[col_2、[col_3]]进行查询，所以再想建一个idx_tbl_test_01的非聚集索引。
但是，在下面的查询中
SELECT * FROM (     
    SELECT *, ROW_NUMBER() OVER (ORDER BY col_4,col_5,col_1,col_2,col_3) AS row_no     
    FROM tbl_test  
    WHERE col_1 = ? AND col_2 = ?    
) AS w1 
WHERE w1.row_no >= ? AND w1.row_no <= ?
当查询结果不存在时，执行计划显示走的是idx_tbl_test_01；
当查询结果存在时，执行计划显示走的是idx_tbl_test_00；而当
SELECT * FROM (     
    SELECT *, ROW_NUMBER() OVER (ORDER BY col_4,col_5,col_1,col_2,col_3) AS row_no     
    FROM tbl_test  
    WHERE col_1 = ? AND col_2 = ? AND col_3 = ?     
) AS w1 
WHERE w1.row_no >= ? AND w1.row_no <= ?
不论查询结果存不存在，执行计划显示走的都是idx_tbl_test_01。查询优化器是不是不能很好的自动工作？要建STATISTICS来引导它吗？

Answer 3

谢谢楼上的建议。
当聚集的idx_tbl_test_00和非聚集的idx_tbl_test_01在列上有重复的话，有没有问题？

Answer 4

可以重复，至于有没有问题，要看你的实际应用
重复会导致你无法在该列上使用max或min

Answer 5

单就楼主提出的按col_1、[col_2、[col_3]]查询的话,这个索引就最好了.CREATE CLUSTERED INDEX idx_tbl_test_00
ON tbl_test (
    col_1 ASC,
    col_2 ASC,
    col_3 ASC
)
GO另外,楼主的"ROW_NUMBER() OVER (ORDER BY col_4,col_5,col_1,col_2,col_3)"好像不是SQL Server的语法.楼主的数据库类型是什么?

Answer 6

楼上的师兄，上面提到了
> 在最常用的功能中，信息是按col_4 ASC,col_5 ASC,col_1 ASC,col_2 ASC,col_3 ASC排的，
> 而且经常要按col_4进行范围查询。所以建了一个idx_tbl_test_00的聚集索引。
> 在辅助功能中，要能按col_1、[col_2、[col_3]]进行查询，
> 所以再想建一个idx_tbl_test_01的非聚集索引。
所以在col_1 ASC,col_2 ASC,col_3 ASC上建聚集索引不太合适吧。ROW_NUMBER()是SQLServer2005的新函数，我也是才知道。SQLServer我也不熟，记得上一次用的时候已经是4、5年前的事了。

Answer 7

明白了两个问题，1、微软就是微软，东西不错但不会轻易让你了解它产品内部的机制。
彻底跑了一下Database Engine Tuning Advisor，最终建议删掉idx_tbl_test_01，
建了一个新的非聚集索引
CREATE NONCLUSTERED INDEX idx_tbl_test_02
ON tbl_test (
    col_1 ASC,
    col_4 ASC,
    col_5 ASC,
    col_2 ASC,
    col_3 ASC
)
想象不到吧。（可能是我水平太差，其实大家都已经知道了）
可恨的是，MSDN的帮助里明文写着
“如果表有聚集索引，则该聚集索引中定义的列将自动追加到表上每个非聚集索引的末端。这可以生成覆盖查询，而不用在非聚集索引定义中指定聚集索引列。”
自己工具优化的时候还给加上了，不明白。2、鱼和熊掌不可兼得。
按col_1、[col_2、[col_3]]查询的话走idx_tbl_test_02，有没有结果都一样快了。
但无查询条件时走idx_tbl_test_00，却由以前的4秒多增到了8秒多。

Answer 8

TINYINT 类型的不用建立索引吧字段值范围小的一般不需要建立索引

Answer 9

子叶越多，虽然在比没建立索引的时候是加快了，但是并不是最优方案
建议在col_4上建立clustered index，在col_5,col_2上建立nonclustered index还有是先从sql上优化

Answer 10

谢谢喜欢看海的建议，回去试试。
还想请教看海两个问题：1、得出“在col_4上建立clustered index，在col_5,col_2上建立nonclustered index”的结论，是经过如何思考的？想学习学习。2、这段SQL怎样优化？
默认是当天或之后最近的数据最先显示，然后可以前页后页翻。所以我先
SELECT TOP 1 w1.row_no 
FROM (     
    SELECT col_4, ROW_NUMBER() OVER (ORDER BY col_4,col_5,col_1,col_2,col_3) AS row_no     
    FROM tbl_test  
) AS w1 
WHERE w1.col_4 >= CONVERT(VARCHAR, GETDATE(), 101)取到最先表示信息第一条的行号，然后根据这个行号执行下面的
SELECT * FROM (     
    SELECT *, ROW_NUMBER() OVER (ORDER BY col_4,col_5,col_1,col_2,col_3) AS row_no     
    FROM tbl_test  
    WHERE col_1 = ? AND col_2 = ?    
) AS w1 
WHERE w1.row_no >= ? AND w1.row_no <= ?
来取当页或前页、后页的数据。请教应该如何优化？

Answer 11

是否可以考虑下col_4     DATETIME       NULL,    
col_5     DATETIME       NULL    改成col_4     DATETIME       not NULL,    
col_5     DATETIME       not NULL    或者col_4     DATETIME       not NULL default('1900-1-1'),    
col_5     DATETIME       not NULL default('1900-1-1')因为null字段用于聚集索引是有影响的
   
考虑非聚集索引用
CREATE NONCLUSTERED INDEX idx_tbl_test_01 ON tbl_test
(
    col_1 ASC,
    col_2 ASC,
    col_3 ASC,
    col_4 ASC,
    col_5 ASC)
GO