sql - SQL 中 WHERE 子句的索引性能

Question

我正在阅读我的数据库书籍中有关索引的内容，我想知道我的假设是否正确，其中包含非常量表达式的 WHERE 子句不会使用索引。所以如果我有

SELECT * FROM statuses WHERE app_user_id % 10 = 0;

这不会使用在 app_user_id 上创建的索引。但是

SELECT * FROM statuses WHERE app_user_id = 5;

将使用 app_user_id 上的索引。

Answer 1

通常(还有其他选项)数据库索引是 B 树，这意味着您可以对其进行范围扫描(包括相等性扫描)。

条件 app_user_id % 10 = 0 无法通过单范围扫描进行评估，这就是数据库可能不使用索引的原因。

它仍然可以决定以另一种方式使用索引，即进行全扫描:读取整个表比只读取整个索引花费更多的时间。另一方面，在阅读索引后，您可能仍然会回到 table 上，因此最终的整体成本可能会更高。

这由数据库查询优化器决定。

几个例子:

 select app_user_id from t where app_user_id % 10 = 0

在这里，你根本不需要表，所有需要的数据都在索引中。数据库很可能会进行完整的索引扫描。

 select count(*) from t where app_user_id % 10 = 0

相同。全索引扫描。

 select count(*) from t

只有当 app_user_id 为 NOT NULL 时，才能使用索引完成此操作(因为 NULL 数据不在索引中，至少在 Oracle 上，至少在单列索引上，您的数据库可能会以不同方式处理)。

有些数据库不需要为此访问表或索引，它们在元数据中维护行计数。

 select * from t where app_user_id = 5

这是索引的经典场景。数据库可以查看索引树的一小部分，检索一小部分(如果这是唯一索引或主索引，则只有一个)rowid，并从表中有选择地获取这些行。

 select * from t where app_user_id between 5 and 10

又一个经典的索引案例。树中的范围扫描返回少量 rowid 以从表中获取。

 select * from t where app_user_id between 5 and 10 order by app_user_id

由于索引扫描返回有序数据，您甚至可以免费进行排序。

 select * from t where app_user_id between 5 and 1000000000

也许在这里你不应该使用索引。好像匹配的记录太多了。在这种情况下，绑定(bind)变量对数据库隐藏范围实际上是有害的。

 select * from t where app_user_id between 5 and 1000000000 
    order by app_user_id

但在这里，由于排序会非常昂贵(甚至占用临时交换磁盘空间)，也许按索引顺序迭代是好的。也许吧。

 select * from t where app_user_id % 10 = 0

这很难决定。我们需要所有列，所以最终查询需要触及表。问题是是否先通过索引。查询返回整个表的大约 10%。对于高效的索引访问路径来说，这可能太多了。如果优化器有理由相信查询返回的数据远少于表的 10%，则在访问表后进行索引扫描可能会很好。如果表非常零散(大量已删除的行占用空间)，情况也是如此。