我负责一个较小的DB 300百兆100个表,大约45个用户在整个工作日点击它.主要是读取,但有相当多的更新和插入.我一直在减慢学习数据库结构的速度,以便从中获得一些性能.我听说看看索引是一个很好的起点.所有数据库表的所有索引都是聚集的,其中一些是非聚集的.
与群集和非群集相比,是否有任何速度优势？我有一个维护计划(是的,我知道),在差异备份之前每晚重新编制和重建索引,这对于现在来说是否足够好,直到我更好地掌握索引的形成和利用率？
是否有(a)脚本可以帮助我查看各种索引的“性能”？
我的自我变成了多大的蠕虫？
解决方法
聚簇索引确定表中数据的物理顺序,对于经常搜索值范围的列特别有效.当索引值是唯一的时,它们对于查找特定行也很有效.
通常(有例外),聚集索引应该在单调增加的列上 – 例如标识列或其他值增加的列 – 并且是唯一的.在许多情况下,主键是聚簇索引的理想列(但不要在uniqueidentifier / GUID列上放置聚簇索引.)
从这MSDN article：
Before creating clustered indexes,
understand how your data will be
accessed. Consider using a clustered
index for:
- Columns that contain a large number of distinct values.
- Queries that return a range of values using operators such as
BETWEEN,>,>=,<,and <=.- Columns that are accessed sequentially.
- Queries that return large result sets.
- Columns that are frequently accessed by queries involving join or GROUP BY
clauses; typically these are foreign
key columns. An index on the column(s)
specified in the ORDER BY or GROUP BY
clause eliminates the need for sql
Server to sort the data because the
rows are already sorted. This improves
query performance.- OLTP-type applications where very fast single row lookup is required,
typically by means of the primary key.
Create a clustered index on the
primary key.Clustered indexes are not a good
choice for:
- Columns that undergo frequent changes: This results in the entire
row moving (because sql Server must
keep the data values of a row in
physical order). This is an important
consideration in high-volume
transaction processing systems where
data tends to be volatile.- Wide keys: The key values from the clustered index are used by all
nonclustered indexes as lookup keys
and therefore are stored in each
nonclustered index leaf entry.
sqlServerpedia.com有一些很好的文章/教程用于索引调优：Index Related DMV Queries和Using the Right Indexes for Optimal Performance.
