我应该把索引放在哪里？

This is pseudo-code:

这是伪代码:

Select 
    count(score) 
from 
   myTable
join 
   HisTable on myTAble.id = HisTable.id 
where 
   name='aaa'
group by 
   id 
having 
   count(score) > 3 
order by 
   (someField)

My question is about index. Where should I put the non-clustered index and the clustered index? By which priority? (the aggregate? The order? The where? The join fields?)

我的问题是关于指数。我应该在哪里放置非聚集索引和聚簇索引?哪个优先? (汇总?订单?在哪里?连接字段?)

1 个解决方案

#1

In SQL Server, there are several basic considerations:

在SQL Server中,有几个基本注意事项:

the clustering key is vitally important, and it should be chosen with care - it should be narrow, unique, static and possibly ever-increasing (the NUSE principle) - see Kim Tripp's Ever-increasing clustering key - the Clustered Index Debate..........again! blog post for more detailed information on why those characteristics are important).

集群密钥至关重要,应谨慎选择 - 它应该是狭隘的,独特的,静态的,并且可能不断增加(NUSE原则) - 请参阅Kim Tripp不断增加的聚类密钥 - 聚集索引辩论...... .......再次!博客文章,了解有关这些特征为何重要的更详细信息)。

So your clustering key should be narrow - 4-byte INT is ideal, it should be UNIQUE and static, and this is perfectly handled by some kind of an ID - especially if it's an INT IDENTITY. GUID is significantly worse, variable-width VARCHAR fields are absolutely out of the question

所以你的聚类键应该是窄的 - 4字节INT是理想的,它应该是UNIQUE和静态的,这完全由某种ID处理 - 特别是如果它是INT IDENTITY。 GUID明显更糟,可变宽度VARCHAR字段绝对不可能
every foreign key column in a child table referencing a parent table should be part of a nonclustered index. This significantly speeds up JOINs and other operations

引用父表的子表中的每个外键列都应该是非聚簇索引的一部分。这大大加快了JOIN和其他操作
any column used in a WHERE or ORDER BY clause also is a good index candidate.

WHERE或ORDER BY子句中使用的任何列也是一个很好的索引候选者。

But mind you: indexing is an art and a balancing act. Each additional index adds more overhead - don't overdo your indices! Less is often more. Try to

但请注意:索引是一种艺术和平衡行为。每个额外的索引都会增加更多开销 - 不要过度使用索引!通常更少。尝试

pick a good clustering key

选择一个好的聚类键

put nonclustered indexes on each of your foreign key columns

在每个外键列上放置非聚簇索引

and then see if your app performs OK. If it does - fine, let it be. If not: try to isolate the offending queries, and try to tune those. Don't over-do your indexing by tuning each individual query - an index that might benefit one query can severely impact another one. It's a matter of finding the best set of indices for your entire application.

然后看看你的应用是否表现良好。如果它确实 - 那很好,就这样吧。如果不是:尝试隔离有问题的查询,并尝试调整这些查询。不要通过调整每个单独的查询来覆盖索引 - 一个可能使一个查询受益的索引会严重影响另一个查询。这是为您的整个应用程序找到最佳索引集的问题。

Read Kim Tripp's blog posts - all if you have the time. She's the Queen of Indexing and offers tons of very valuable insights into what to do (and what not to do).

阅读Kim Tripp的博客文章 - 如果你有时间的话。她是索引女王,并提供了大量有关如何做(以及不做)的宝贵见解。

#1