再次梳理一下索引的使用

聚簇索引和非聚簇索引的区别

 

 

 

 

在聚簇索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。

聚簇索引插入数据时速度相对非聚簇索引慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入
在非聚簇索引中，叶级页指向表中的记录：记录的物理顺序与逻辑顺序没有必然的联系。

聚簇索引非常象目录表，目录表的顺序与实际的页码顺序是一致的。

非聚簇索引则更象新华字典的部首索引表，索引表中的顺序通常与实际的页码顺序是不一致的。

一本书也许有多个索引。例如，它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引。

 

非聚集索引中的行定位器有两种形式：
有聚集索引:行定位器就是指向聚集索引键。
没有聚集索引:行定位器就是指向行的指针。
由于非聚集索引将聚集索引键作为其行指针存储，因此使聚集索引键尽可能小很重要。如果表还有非聚集索引，请不要选择大的列作为聚集索引的键。
索引之所以能加快查询速度，根本原因是是通过索引扫描尽可能少的数据找到需要的数据。

 

 

哪些场景聚集索引不适用：

1. 频繁更改的列，这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。
2. 该列的新插入的值不是顺序增长的
3. 宽列，来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。

 

 

联合索引中应用有多少个字段呢？

1. 如果不确定确切的索引数，最佳的方法可能就是每个索引用一个字段。
2. 如果索引中的字段匹配多个检索或频繁进行的关键检索中的字段顺序，那么每个索引包含多个字段是有用的。唯一的说明是索引的统计信息只基于第一个字段，而不是整组索引

 

 

索引还有其它什么配置？

1. 索引顺序——我们可以创建递增或递减顺序的索引。                                                                            
2. 填充指数——为每一个索引指定填充因数，以确定索引在创建或重建时在每个索引页预留多少空余空间。
3. 统计信息——保证手动创建索引统计信息，或者允许 SQL Server 根据数据库大小自动创建和更新它们。

 

 

什么情况需要索引?

1. Primary Key's 主键 
2. Foreign Key's 外键 
3. 支持 SELECT、UPDATE 和 DELETE 命令的字段
4. INNER JOIN 
5. RIGHT | LEFT OUTER JOIN 
6. WHERE 
7. ORDER BY 
8. GROUP BY 
9. HAVING

 

索引的其它考虑因素是：                                                                              

1. 数据量 ——与遍历索引相比，记录行很小的表格可以一样快速地访问数据并更节省资源。 
2. 数据选择比——当数据的选择比低时，如在字段中存储相同的数据，索引值可以是最小的。

 

 

索引设计与优化好的实践

1. 没有系统是一成不变的，因此我们必须不断地修改索引以支持使用变化。
2. 高选择性的（不同的）数据的索引字段。
3. 如果是非常大型的数据库（VLDB），那么可以将数据和聚簇索引与非聚簇索引分开，并存储在其它物理硬盘上的独立文件组中。
4. 从整体角度上处理索引以便保证索引对应用一部分有益时不会对另一部分有害。
5. 平衡每个表的索引数目以便减少 SQL Server 在执行事务处理时的工作量。这样将减少整体存储空间支持，同时在时间和 I/O 方面仍然实现高效的处理。
6. 平衡还有助于保证新索引不会在对应用的一部分有益的同时损害另一部分的性能。要对索引进行全面的测试，并重新检查一个位置的修改不会损害应用另一部分。
7. 在测试阶段中检查查询计划，以保证索引可以改进查询时间以及预期的资源使用率。
8. 为每个索引选择恰当的填充因数。如果表内数据只会有很少的修改，那么索引就要配置一个高填充因数，如，接近于 100%，这样将节省存储空间。如果表内数据会有许多的修改，那么我们可以选择一个较低的填充因数，如 65%到 85%，这样当数据添加到页时，索引重新建立的页划分将是最小的。                                                                                                                                
9. 定期使用 DBCC SHOWCONTIG 命令来检查表、索引或数据的碎片。根据碎片程度，定期重建索引。
10. 如果在短时期内，索引出现大量的碎片，那么我们需要检查数据是如何插入、更新以及删除的，以便确认我们是否需要一个较低的填充因数。同时，确定是否可以修改代码来减少碎片的数量。
11. 在索引创建、删除和/或重建时捕捉性能基线，以确定每个操作对性能的影响。
12. 在变更管理过程之后引进新索引测试和部署。保证用文档记录所有修改。
13. 按周、月或季度执行数据库维护。只有不断地维护，SQL Server 才可以完美地运行。

 

 

索引设计与优化不好的实践

1. 避免表出现热点。当所有事务都访问表的相同位置（如：表格末端）以及引发线路争夺问题时，就会出现。
2. 不要在查询中使用索引提示（强制索引）来替代 SQL Server 优化器索引选择，除非所有的选项都用完了。
3. 避免页切分。它是用在现有数据页空间无法再存储新记录行的时候。当出现这种情况时，SQL Server 将把一半的数据转移到新的页面。最终结果是，原先的页面存储了一半数据而新的页面存储了另一半数据。这是一个非常耗费资源的操作，因为 SQL Server 必须完成记录行的存储及后续处理。
4. 不要建立或维护不必要的重复索引。

 

 

如何选择最佳方案？

原则：测试，测试，反复地测试

方法：

1. Display Estimated Execution Plan
2. Actual Executio Plan
3. Database  Engine Tuning Advisor
4. SQL Profiler
5. sys.dm_db_missing _index_columns (Dynamic Management View)

 

 

什么样的查询会忽略非聚簇索引？

1、  高碎片率——当索引有超过 40%的碎片时，优化器可能会忽略该索引，因为查找一个碎裂索引比扫描一个表的开销要高。

2、  唯一性——如果优化器确定一个非聚簇索引不是唯一的，那么它会认为扫描表会比使用非聚簇索引要效率高些。比如：如果一个查询引用一个比特字段（这里的 bit=1）而且字段的统计显示 75%的记录行都是 1，那么优化器可能认为表扫描比非聚簇索引扫描更快获得结果。

3、  过时的统计——如果字段的统计过期了，那么 SQL Server 会对聚簇索引的好处作出错误的判断。自动更新统计不仅减缓数据修改脚本，而且随着时间的推移，它还会变得与实际的记录统计不同步。有时，最好运行一下 sp_updatestats 或 UPDATE STATISTICS。

4、  方法使用——当查询条件带有一个方法时，SQL Server 就无法使用索引。如果我们引用了一个非聚簇索引字段，但又使用一个方法，如 convert(varchar, Col1_Year) = 2004，那么 SQL Server 也无法使用 Col1_Year 上的索引。

5、  错误字段——如果一个非聚簇索引是定义在 (col1, col2, col3)，而我们的查询中有一个 WHERE 子句，如“where col2 = 'somevalue”，那么就不会使用索引。只有当索引中的第一个字段有在 WHERE 子句中引用时，才会使用该索引。对于这样的 WHERE 子句，如“where col3 = 'someval”，将不会使用索引中，但是，如“where col1 = 'someval”或“where col1='someval and col3 = 's omeval2”的 WHERE 子句则会使 用索引。索引不会在查询中使用 col3，因为这个字段在索引定义中不并在 col1 之后。如果想要在类似的这种情况下使用 col3 来查找，那么最佳的方法就是定义两个单独的非聚簇索引，一个在 col1，另一个在 col3。

 

 

如果查询没有使用我们的索引如何检查？

1、  运行 dbcc showcontig('tablename')来检查表是否有碎片。

2、  检查“where clause”来查看是否它引用了索引的第一个字段。

3、  保证“where clause”的查询条件中没有针对索引的第一个字段的方法。

4、  只当统计过期时才更新统计。如果表有碎片，那么在重新索引之后更新统计。

5、  确保所使用的查询条件是足够唯一的，这样 SQL Server 更好地查找数据。

 

 

重要概念1：页拆分

为了存储数据，SQL Server 使用有 8kb 数据块的页。而填充到页中的数据量则被称为

填充因数，并且填充因数越高，8kb 页面就越满。越高的填充因数就意味着需要越少的

页，从而 IO/CPU/RAM 使用也就越少。因此，我们可能会想将索引设置为 100%填充因数；

然而，这里有个问题：一旦页面填满了，而又有在已填充的索引范围内的新值到达时，

SQL Server 将通过“页拆分”来为索引提供空间。本质上，SQL Server 是将把填满的页

拆分成两个页，这样就会有更多的存储空间了。我们可以通过设置填充因数为 70%左右来

解决这个问题。这样就总是有 30%的可用空间预留给将输入的值。这个方法的问题是我们

必须不断的“重建”索引，才可以维持 30%的可用空间

 

 

重要概念2：SQL Server 统计

统计维护在字段和索引上，并且它们会用于帮助 SQL Server 确定某些值可能的“唯一”性——比如，如果统计显示某个值可以匹配接近 80%的记录行，那么 SQL Server 将通过扫描表来代替索引。如果统计显示某一个值可能匹配接近 10%的行，那么查询优化器将选择影响数据库最小的查询。 SQL Server 统计可以自动地维护或手动运行。由于重新索引会改变统计结果，因此我推荐，在重新索引之后，我们手动运行 sp_updatestats 或 T-SQL UPDATE STATISTICS 命令。统计只在任何组合合索引的第一字段上维护，因此无法确定索引的其它字段的“唯一性”。