database - H2 数据库 : slow query although index is used

Question

使用 H2 1.3.176。

1)表定义:

CREATE TABLE TEST(ID BIGINT PRIMARY KEY, ACCOUNT BIGINT, TXID BIGINT); 

2) 向表中插入值:

INSERT INTO TEST SELECT X, RAND()*100, X FROM SYSTEM_RANGE(1, 1000000)

3) 创建用于我的查询的索引:

CREATE Unique INDEX IDX_TEST_ACCOUNT_TXID ON `test` (account, txId DESC);

4) 执行以下查询:

explain analyze
select txid from test where account=22 AND txid<9999999 order by txid desc limit 25

我得到以下执行计划:

SELECT
    TXID
FROM PUBLIC.TEST
    /* PUBLIC.IDX_TEST_ACCOUNT_TXID: ACCOUNT = 22
        AND TXID < 9999999
     */
    /* scanCount: 9867 */
WHERE (ACCOUNT = 22)
    AND (TXID < 9999999)
ORDER BY 1 DESC
LIMIT 25
/*
TEST.IDX_TEST_ACCOUNT_TXID read: 103
*/

问题:为什么H2需要扫描整个索引？我期望扫描计数为 25，因为索引中的 txid 应该已经按降序排列，所以一旦 H2 位于索引的 account=22 分支中，它应该能够读取接下来的 25 个条目。如果表中有数百万个条目，这将导致查询缓慢。即使 H2 必须在索引中搜索第一个匹配条目，我也希望这是一个 O(log(N)) 算法而不是扫描。如果我在没有列帐户的情况下做同样的事情(意味着该表只包含 id 和 txid)，那么 txid 上的降序索引确实会导致扫描计数为 25(使用查询“select txid from test where txid<9999999 order通过 txid desc”)。为什么附加列会破坏执行计划？也许我不明白索引是如何工作的。有没有更好的方法来为我的查询定义索引？

Answer 1

我单步检查了 h2 源代码，发现出了什么问题:

在准备执行查询期间，h2 尝试确定它是否可以使用索引对结果集进行排序和限制。由于第一个索引列(account)不在order by子句中，h2认为不能使用索引。这导致 h2 扫描整个索引以获取所有行，然后对结果集进行排序和限制。这是令人惊讶的，因为 account 条件是一个“相等”条件，所以 h2 应该意识到它确实可以使用索引对结果集进行排序和限制。解决方案是在 order by 子句中提供帐户列。因此查询应该是:

select txid from test where account=22 AND txid<9999999 order by account, txid desc limit 25

我得到了预期的执行计划

SELECT
    TXID
FROM PUBLIC.TEST
    /* PUBLIC.IDX_TEST_ACCOUNT_TXID: ACCOUNT = 22
        AND TXID < 9999999
     */
    /* scanCount: 25 */
WHERE (ACCOUNT = 22)
    AND (TXID < 9999999)
ORDER BY =ACCOUNT, 1 DESC
LIMIT 25
/* index sorted */

它的扫描次数只有 25 :)