sqlite - 在查询中使用时索引为 null 不起作用

Question

我有以下索引:

CREATE INDEX IF NOT EXISTS dateIdx ON table1 (DateFirstAdded COLLATE NOCASE)

查询的以下 ORDER BY 部分成功使用了索引:

ORDER BY DateFirstAdded COLLATE NOCASE ASC LIMIT 100

但以下情况则不然:

ORDER BY DateFirstAdded is null COLLATE NOCASE, DateFirstAdded COLLATE NOCASE ASC LIMIT 100

我需要创建一个新索引，还是需要调整查询？

Answer 1

从表中读取记录后，索引不能用于加速排序；使用它的唯一方法是在查询从表中读取数据时单步执行索引条目。

这意味着索引中条目的顺序必须与 ORDER BY 子句中指定的顺序完全相同。

在这种情况下，不存在将 DateFirstAdded IS NULL 作为其第一列的索引(并且 SQLite 不支持表达式索引)；您的索引末尾有 NULL 记录。(请注意，IS NULL 返回 0 或 1，不需要排序规则。)

要最后返回 NULL 记录并仍然使用索引进行排序，您必须将查询拆分为分别返回非 NULL 和 NULL 记录的复合查询；为了能够使用 ORDER BY，您必须使用子查询:

SELECT *
FROM (SELECT *
      FROM table1
      WHERE DateFirstAdded IS NOT NULL         -- uses index
      ORDER BY DateFirstAdded COLLATE NOCASE)  -- implied by index scan
UNION ALL
SELECT *
FROM table1
WHERE DateFirstAdded IS NULL                   -- uses index
LIMIT 100