sql表优化:主索引和辅助索引

Question

为了安全起见，人们通常会将表中的每一列都作为辅助索引，以防客户决定使用其中一个字段来搜索记录吗？

搜索是先遍历辅助索引，然后再搜索主键吗？从而缩小到所请求的数据范围？

如果您已经有一个作为主键的列，那么使用辅助索引有什么意义？

Answer 1

通常，您只对需要索引的列进行索引。添加额外的索引通常被认为是过早的优化。

大多数优化器都会找出最快的方法来找到最少的记录。这可能是使用and索引，但也可能是全表扫描。如果可以使用多个索引，则通常只使用一个索引，并将结果记录与其余条件进行比较。如果使用多个索引，则需要匹配结果集，并消除在两个索引中都找不到的记录。

对于自然键可能发生变化或很长(故意模糊)的表，通常会使用代理键。在这种情况下，自然关键字将被索引为辅助唯一关键字。在某些情况下，可能存在竞争的自然关键字，在这种情况下，所有自然关键字都将具有唯一的索引。

Answer 2

(以下响应适用于Sql Server。其他DBMS的某些部分可能会有所不同。)

最后一个问题首先是：“如果已经有一个列是主键，那么使用辅键的意义是什么？”我以一个表名为例进行说明，现在考虑查询"select * from people where lastname = 'flynn'".。如果在lastname列上没有索引，那么将按顺序扫描表以查找匹配项。每一行都必须被访问。主键索引在这里没有任何帮助。如果您为lastname列建立索引，则可以更快地找到结果。

通常情况下，您将只对那些对应用程序发出的查询有用的列进行索引。如果您的查询从来没有在名为"MiddleName“的列上有联接或where条件，那么对该列进行索引将不会带来任何好处。您不希望添加不必要的索引，因为它们会增加涉及该列的数据插入和更新的成本。

我们通常说，Sql Server在查询中对每个表实例只使用一个索引。因此，像"select * from people where firstname='Elroy‘and lastname = 'Flynn’“这样的查询最多只能使用一个索引，即使名字和姓氏都有索引。Sql Server将根据从数据值中收集的统计信息来选择其中一个索引。

为了完整起见，我必须在这里取得一些进展，并讨论聚集索引与非聚集索引。一个表只能有一个聚集索引:其余的都是非聚集索引。尽管有前面的段落，但当非聚集索引用于解析查询时，索引查找会产生一个中间结果，该结果是与聚集索引(通常是主键)相关联的键的全值。也就是说，每个非聚集索引的叶子都包含聚集键值，而不是行指针。找到此聚集键后，将使用该聚集索引将查找解析到特定的数据库行。因此，最终，所有索引查找最终都使用聚集索引。

不过，出于实际目的，通常来说，每个表实例只使用一个索引就足够了，而且更简单。请注意，如果一个表在查询中有别名，使其多次出现，则可以对不同的引用使用不同的索引。例如"select * from people p1 join people p2 on p1.firstname = p2.lastname"可以在p1实例上使用名字索引，在p2实例上使用姓氏索引。

请参阅http://msdn.microsoft.com/en-us/library/aa933131(v=SQL.80).aspx

Answer 3

还有一项还没有提到，每个额外的索引都必须维护。因此，如果索引以几种不同的组合覆盖所有列，它们不仅会占用大量空间，而且每次更新/插入/删除都有可能更改其中的一个或多个索引。这将导致这些操作在许多情况下变慢。

这始终是一种权衡。索引越多，服务器需要做的保持最新的工作就越多，但是您就越有可能拥有至少一个索引来覆盖您在该表中抛出的任何查询。