关于索引的误解

关于索引的误解有这些：

• Databases don’t need indexes.
• Primary keys are always clustered.

先创建聚集索引后，设置主键，那么主键是非聚集的。

• Online index operations don’t block.

在线操作索引可能会引起阻塞

With the default index creation, which does not use the ONLINE option, the S locks
are held for the entirety of the index build. Shown in Figure 8-3, the S lock is taken before
the SCH_S lock and isn’t released until after the index is build. The result is that the index
is unavailable during the index build.

• Any column can be filtered in multicolumn indexes.
• Clustered indexes store records in physical order.
• Indexes always output in the same order.
• Fill factor is applied to indexes during inserts.
• Deleting from heaps results in unrecoverable space.
• Every table should be a heap or have a clustered index.

最佳实践方案：

• Index to your current workload.
• Use clustered indexes on primary keys by default.
• Properly target database-level fill factors.
• Properly target index-level fill factors.
• Index unique and foreign key columns.
• Balance index count.