何时使用索引

Question

是否有一个通用的经验法则或公式来确定何时使用索引是有用的？

例如，在下面的情况中，显然应该在id列上添加索引：

SELECT * FROM table WHERE id = '1iuhiasdf89384h'

然而，如果 is_qualified 只有 2 个可能的值，则以下内容可能没有用处：

SELECT * FROM table WHERE is_qualified=1

如果一个项目有10个[0-9]的可能值，100个值[0-99]呢？通常什么时候需要添加索引，这与基数有什么关系？

- David542

---

一个普遍的"规则"是将所有主键列和参与外键关系的任何列索引。之后，按照查询性能需要进行索引。 - Yuck

当查询执行表扫描而不是直接访问所需行时，您可以添加索引。通常，基数越高，索引就越好。在您的特定情况下，对于10个或更少的可能值，不需要索引；对于11个或更多的可能值，则需要索引。确切的数字取决于您的关系型数据库和数据库计算机的性能。添加索引并测量性能的增加或减少。 - Gilbert Le Blanc

Answer 1

作为一般的经验法则，优化器在where子句中选择使用索引的原因是为了减少读取的数据页数。因此，如果您的数据随机分布在页面上，则重要的问题是每个页面上有多少条记录以及过滤器选择了多少条记录。

假设每个页面有100条记录。那么对于随机选择的1%的记录，可能会选择（几乎）所有页面。在这种情况下，阅读页面并在页面上执行过滤通常比使用索引更快，因为几乎所有数据页面都将被读取。

因此，对于大多数表，返回一个或少量记录的查询将更适合于使用索引。返回大量记录的查询可能不会从索引中受益。推论是，对于小表，索引可能永远没有用处。如果数据适合一个页面，使用where过滤器扫描该页面可能与使用索引一样快。

也就是说，如果查询的选择性大于数据页上平均记录数的倒数，则索引可能无用。这尽可能接近“通用”规则，但请继续阅读。

索引的类型也很重要。如果条件为is_qualified，只有0.1%的记录符合此条件，则索引可能证明有用。或者，如果有1%符合条件，但记录非常大，因此每个页面只有10个记录，则索引可能是有用的。或者，如果is_qualified是聚集索引中的第一列，则所有值为1的记录都在少数页面上。对于聚集索引，即使is_qualified = 1的选择性为30％，也仅意味着读取30％的数据页面-这应该将许多查询的时间缩短三分之二。

当然，这忽略了索引用于连接和排序的情况-即使选择性达到100％，索引仍可能受益。但是，您的问题似乎针对where子句中的过滤器。

- Gordon Linoff

Answer 2

我认为您需要对使用索引进行一些研究和阅读。即使在您自己的示例中，您期望在"id"列上创建索引，因为您正在寻找特定的一个... 但是，在IS_QUALIFIED上没有索引并不重要，因为它只有两个可能的值... 但是具有字母数字的ID可能有数十亿个值。

索引用于帮助快速缩小范围并查找记录，而无需访问原始数据页面即可基于您希望获取的常见条件拉取符合条件的记录。根据您预计运行的常见查询类型，应考虑甚至具有多个列的索引。

让我们看一下您的数据情景列，并假设表是每个“id”的子表，具有“is_qualified”和“othertype”（您的值为0-9），以及其他一些内容，例如某事物的日期或“其他类型”的描述。

如果您仅在ID上拥有索引，则所有“ID”记录将被分组在一起，这很好，一旦您到达它们，快速浏览即可获得结果。

假设现在您正在寻找所有具有“Is_Qualified”=1且“othertype”列为3的ID。 您无法快速获取它们，没有从一个到另一个的相关性……但是如果您有一个(is_qualified、othertype、id)的多键索引，那么您可以快速跳转到仅这些记录中的Is_Qualified = 1和othertype = 3，然后您就可以准备好所有的ID了。

将其应用于查询数百万条记录的表，运行没有索引的查询，您将非常欣赏它们的目的，并学会设计有意义的索引。

- DRapp

Answer 3

重要的是表格中存在多少行，而不是它们具有2个还是10个可能值。对于少于200-300行的表格，无需索引。如果您有100万行，则即使列仅具有0和1，拥有索引也很好-您将避免完全扫描。例如，如果100万行中只有500行为1，则查询将跳过扫描999,500行。

- i486