关于指数的问题 [英] Question regarding index

问题描述

我有以下查询：

            SELECT 
                  M.Col7,   
                  M.Col8,   
                  M.Col9,   
                      M.Col10   

                FROM    [MyTable] M                    

        WHERE   M.Col1 = COALESCE(@Col1, M.Col1)              
                AND M.Col2 = COALESCE(@Col2, M.Col2)              
                AND M.Col3 = COALESCE(@Col3,              
                                                M.Col3)              
                AND M.Col4 = COALESCE(@Col4,              
                                                 M.Col4)              
                AND M.Col5 = COALESCE(@Col5,              
                                                    M.Col5)              
                AND M.Col6 LIKE COALESCE(@Col6, M.Col6) +'%'

我在co上有一个非聚集索引l7,8,9,10列。如果我删除where子句，查询运行正常。但是一旦我把where子句放入查询就需要很长时间才能执行。我的桌子有200 K行。现在我想用一个列为Col1,2,3,4,5,6的列的非聚簇索引。我是做对的还是快速或最适合的选择？

I have a combined non clustered index on col7,8,9,10 columns. The query is running fine if I remove the where clause. But as soon as I put the where clause the query is taking long time to execute. My table has 200 K rows. Now I am thinking to put a single non clustered index with columns in this order Col1,2,3,4,5,6. Am I doing right to make it fast or what should be the best option for this?

推荐答案

试试这个有覆盖指数

CREATE INDEX IX_foo ON MyTable
   (Col1,Col2,Col3,Col4,Col5,Col6)
   INCLUDE (Col7,Col8,Col9,Col10)

其他想法：

• 由于数据类型的处理方式，ISNULL优于COALESCE
  
  参见为什么IsNull是合并的两倍慢（相同的查询）？


• （ISNULL OR ..）模式已经过优化（至某点，YMMV）
  
  请参阅为什么IsNull合并速度慢两倍（相同查询） ？再次


• 列顺序应该是最具选择性的（WHERE顺序无关紧要）

• ISNULL is better then COALESCE because of how datatypes are handled
  See Why IsNull is twice slow as coalesce (same query)?
• The (ISNULL OR ..) pattern has been optimised (to a point, YMMV)
  See Why IsNull is twice slow as coalesce (same query)? again
• The column order should be most selective first (WHERE order doesn't matter)

其他：

• 什么是聚集索引和主键？

编辑，解释WHERE评论

Edit, to explain the WHERE comments

WHERE
    M.Col1 = ISNULL(@Col1, M.Col1)
    AND
    ...

OR

WHERE
    (@Col1 IS NULL OR M.Col1 = @Col1
    AND
    ...

这篇关于关于指数的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！