主页 / dba / 问题 / 339327
Accepted
user570286
Asked: 2024-05-08 05:07:23 +0800 CST

如何找到最上面的行并仅通过一次扫描来计算行数？

  • 772

假设我有一个如下所示的架构：

-- Many rows
CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER);

-- t1's rows with c1 even
CREATE VIEW t1_filtered(i, c1, c2) AS
  SELECT i, c1, c2 FROM t1 WHERE c1 % 2 == 0;
-- The real WHERE clause is slightly more complex.

假设该表t1包含数百万行：

INSERT INTO t1(i, c1, c2)
  SELECT value, random(), random() FROM generate_series(1, 5000000);

t1假设我想获取具有最高偶数的行的索引以及具有偶数的c1行的计数：c1c2

SELECT
  (SELECT i FROM t1_filtered ORDER BY c1 DESC LIMIT 1),
  (SELECT count(*) FROM t1_filtered WHERE c2 % 2 == 0);

真正的ORDER BY子句要复杂得多，但这足以说明我的问题。

在我看来，这应该只需要一次扫描就可以实现t1，但是EXPLAIN QUERY PLAN说这个查询扫描了t1两次：

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
`--SCALAR SUBQUERY 2
   `--SCAN t1

如果我加入两个子查询而不是将它们写为结果列，那么查询计划会有所不同，但仍然有两次扫描t1：

QUERY PLAN
|--CO-ROUTINE (subquery-1)
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
|--MATERIALIZE (subquery-2)
|  `--SCAN t1
|--SCAN (subquery-1)
`--SCAN (subquery-2)

无论如何，我希望这个查询是这样的伪代码：

var top_row = {i: NULL, c1: 0};
var count = 0;
for each {i, c1, c2} in t1:
  if c1 % 2 == 0:
    if c1 > top_row.c1:
      top_row = {i, c1};
    if c2 % 2 == 0:
      count = count + 1;
return {top_row.i, count};

我怎样才能让查询规划器知道这只需要一次扫描？

更新，2024-05-09：我尝试了Charlieface 提出的查询。根据EXPLAIN QUERY PLAN，它确实使查询规划器仅使用一次扫描t1......

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  |--SCAN t1
|  |  `--USE TEMP B-TREE FOR ORDER BY
|  `--SCAN (subquery-4)
`--SCAN t

...但它的运行速度明显比我的原始查询慢：使用测试SQLite REPLSELECT (...), (...)中的两个查询，我发现（对于包含 500 万行随机数据的示例表）我的原始查询的平均运行时间为 2.22 秒样本标准差为 0.03 秒，该查询的平均值为 5.57 秒。开发人员。0.23秒。.timer ont1

我的直觉直觉并没有向我建议，但研究查询计划的“USE TEMP B-TREE FOR ORDER BY”确实建议我在t1(c1). 这确实加快了我原来的查询速度，使得 st 平均需要 1.09 秒。开发人员。0.02秒。然而，令我惊讶的是，索引显然使 Charlieface 的查询花费了更长的时间，甚至可能使其无法终止——我在等待 108 秒后中断了它，然后在重试 32 秒后中断了它，然后我没有'不要再试一次。

Charlieface 的查询确实通过将扫描次数减少t1到 1 来回答了我的问题，但相对于我原来的查询，它的实际性能较差，使我不愿意接受它作为答案。我希望这不会“移动球门柱”太多。我确实将其标记为query-performance，因此性能从一开始就是我问题的一部分。

更新，2024-05-09 #2：使用CREATE INDEX t1_c1 ON t1(c1)，我的原始查询的查询计划变为

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX t1_c1
`--SCALAR SUBQUERY 2
   `--SCAN t1

Charlieface 查询的查询计划变为

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX t1_c1
|  `--SCAN (subquery-4)
`--SCAN t

根据Charlieface 的评论CREATE INDEX index_per_comment660077_339327 ON t1 (c1 DESC) WHERE (c1 % 2 = 0)（SQLite 不支持），查询计划分别变为：INCLUDE

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX index_per_comment660077_339327
`--SCALAR SUBQUERY 2
   `--SCAN t1 USING INDEX index_per_comment660077_339327

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX index_per_comment660077_339327
|  `--SCAN (subquery-4)
`--SCAN t
query-performance

2 个回答

  1. Best Answer

    对于 SQLite，如果聚合查询包含单个 min() 或 max() 函数，则输出中使用的列的值取自获得 min() 或 max() 值的行。这是一个特点。

    SELECT i, MAX(c1), COUNT(*) FILTER (WHERE c2 % 2 = 0) 
FROM t1_filtered;

    该查询不需要索引，并且具有以下简单的查询计划：

    EXPLAIN QUERY PLAN
SELECT i, MAX(c1), COUNT(*) FILTER (WHERE c2 % 2 = 0) 
FROM t1_filtered;

QUERY PLAN
`--SCAN t1

    该查询将在一次扫描中计算MAX(c1)和，并将返回在实现的同一行中找到的值。COUNT(*)iMAX(c1)

    EXPLAIN 将显示 SQLite OPcodes 中已编译的查询，您可以看到生成的代码与您想要的伪代码完全相同。

    EXPLAIN
SELECT i, MAX(c1), COUNT(*) FILTER (WHERE c2 % 2 = 0) 
FROM t1_filtered;

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     23    0                    0   Start at 23
1     Null           0     1     5                    0   r[1..5]=NULL
2     OpenRead       1     2     0     3              0   root=2 iDb=0; t1
3     Rewind         1     17    0                    0
4       Column         1     1     7                    0   r[7]= cursor 1 column 1
5       Remainder      8     7     6                    0   r[6]=r[7]%r[8]
6       Ne             9     16    6                    80  if r[6]!=r[9] goto 16
7       Column         1     1     6                    0   r[6]= cursor 1 column 1
8       CollSeq        10    0     0     BINARY-8       0
9       AggStep        0     6     4     max(1)         1   accum=r[4] step(r[6])
10      Column         1     2     7                    0   r[7]= cursor 1 column 2
11      Remainder      8     7     6                    0   r[6]=r[7]%r[8]
12      Ne             9     14    6                    80  if r[6]!=r[9] goto 14
13      AggStep        0     0     5     count(0)       0   accum=r[5] step(r[0])
14      If             10    16    0                    0
15      Rowid          1     1     0                    0   r[1]=t1.rowid
16    Next           1     4     0                    1
17    AggFinal       4     1     0     max(1)         0   accum=r[4] N=1
18    AggFinal       5     0     0     count(0)       0   accum=r[5] N=0
19    Copy           1     11    0                    0   r[11]=r[1]
20    Copy           4     12    1                    0   r[12..13]=r[4..5]
21    ResultRow      11    3     0                    0   output=r[11..13]
22    Halt           0     0     0                    0
23    Transaction    0     0     2     0              1   usesStmtJournal=0
24    Integer        2     8     0                    0   r[8]=2
25    Integer        0     9     0                    0   r[9]=0
26    Goto           0     1     0                    0
    • 1

  2. 您可以使用行编号和条件聚合

    SELECT
  MIN(t.i) FILTER (WHERE t.rn = 1),
  COUNT(*) FILTER (WHERE t.c2 % 2 = 0)
FROM (
    SELECT *,
      ROW_NUMBER() OVER (ORDER BY t.c1 DESC) AS rn
    FROM t1_filtered t
) t;
    • 0

相关问题