mysql - 何时使用索引

Question

是否有关于何时使用索引有用的一般经验法则或公式？

例如，在下面的情况下，很明显在 id 列上添加一个 INDEX:

SELECT * FROM table WHERE id = '1iuhiasdf89384h'

但是，如果 is_qualified 有 2 个可能的值，则以下内容可能没有用:

SELECT * FROM table WHERE is_qualified=1

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

Answer 1

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

假设每页有 100 条记录。然后，随机选择百分之一的记录可能会选择(几乎)所有页面。在这种情况下，读取页面并在页面上进行过滤可能比使用索引更快，因为无论如何都会读取几乎所有数据页。

因此，对于大多数表，返回一条或几条记录的查询最好使用索引。返回大量记录的查询可能无法从索引中获益。一个推论是，对于小表，索引可能永远没有用。如果数据适合一页，使用 where 过滤器扫描页面可能与使用索引一样快。

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

索引的类型也有所不同。如果条件 is_qualified 并且只有 0.1% 的记录满足此条件，则索引可能有用。或者，如果 1% 是合格的，但记录非常大，所以一页上只有 10 条记录，那么索引可能有用。或者，如果 is_qualified 是聚集索引中的第一列，则所有带有 1 的值都在少数几页上。对于聚簇索引，即使 is_qualified = 1 的选择性为 30%，也意味着只读取 30% 的数据页——这应该将许多查询的时间缩短三分之二。

当然，这排除了使用索引进行连接和排序的情况——即使是 100% 的选择性仍然可以从索引中获益的情况。但是，您的问题似乎适合在 where 子句中进行过滤。