小巧、快速、可靠。
三者选其二。
三者选其二。
SQLite 内置了对 SQL 查询进行分析的支持，但默认情况下未启用。为了启用对查询分析的支持，SQLite 必须使用 以下选项 编译。
-DSQLITE_ENABLE_STMT_SCANSTATUS
使用此选项构建 SQLite 将启用 sqlite3_stmt_scanstatus_v2() API，该 API 提供对各种分析指标的访问。本页的其余部分讨论了 SQLite 命令行 shell 使用这些指标生成的分析报告，而不是直接使用 API。
shell 生成的分析报告与 EXPLAIN QUERY PLAN 命令生成的查询计划报告非常相似。本页假设读者熟悉此格式。
在使用上述选项编译的命令行 shell 中，使用 ".scanstats on" 命令启用查询分析。
sqlite> .scanstats on
启用后，shell 会在执行每个 SQL 查询后自动输出一个查询分析。可以使用 ".scanstats off" 禁用查询分析。例如
sqlite> .scanstats on sqlite> SELECT a FROM t1, t2 WHERE a IN (1,2,3) AND a=d+e; QUERY PLAN (cycles=255831538 [100%]) |--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) (cycles=60048488 [23%] loops=1 rows=3) `--SCAN t2 (cycles=133558052 [52%] loops=3 rows=150000)
考虑一个具有以下架构的数据库
CREATE VIRTUAL TABLE ft USING fts5(text); CREATE TABLE t1(a, b); CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
然后，在首先执行 ".scanstats on" 后
sqlite3> SELECT * FROM t1, t2 WHERE t2.c=t1.a; <...query results...> QUERY PLAN (cycles=1140768 [100%]) |--SCAN t1 (cycles=455974 [40%] loops=1 rows=500) `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) (cycles=619820 [54%] loops=500 rows=250)
上面示例中 ".scanstats on" 后面的文本是刚刚执行的联接查询的分析报告。分析报告中与 EXPLAIN QUERY PLAN 输出类似的部分表明查询是通过对表 "t1" 进行全表扫描来实现的，并对表 "t2" 上的每个访问行进行 INTEGER PRIMARY KEY 查询。
"SCAN t1" 行上的 "loops=1" 表示此循环（对表 "t1" 进行全表扫描）恰好运行了一次。"rows=500" 表示该次扫描访问了 500 行。
"SEARCH t2 USING ..." 行包含注释 "loops=500"，表示此 "循环"（实际上是通过 INTEGER PRIMARY KEY 查询）运行了 500 次。这很有道理 - 它针对 "t1" 的全表扫描访问的每一行运行一次。"rows=250" 表示总的来说，这 500 个循环访问了 250 行。换句话说，只有表 t2 上的一半 INTEGER PRIMARY KEY 查询成功，另一半查询没有找到任何行。
SEARCH 或 SCAN 条目的循环计数不一定是外循环输出的行数。例如，如果上面查询修改如下
sqlite3> SELECT * FROM t1, t2 WHERE t1.b<=100 AND t2.c=t1.a; <...query results...> QUERY PLAN (cycles=561002 [100%]) |--SCAN t1 (cycles=345950 [62%] loops=1 rows=500) `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) (cycles=128690 [23%] loops=100 rows=50)
这次，即使 "SCAN t1" 循环仍然访问 500 行，但 "SEARCH t2" 查询只执行了 100 次。这是因为 SQLite 能够丢弃不匹配 "t1.b<=100" 约束的 "t1" 行。
"cycles" 测量值基于 CPU 时间戳计数器，因此是实际时间的良好指标。对于上面的查询，总循环数为 561002。对于两个循环（"SCAN t1..." 和 "SEARCH t2..."），循环计数表示在可以直接归因于该循环的运算中花费的时间。具体来说，这是在该循环的数据库 B 树中进行导航和提取数据所花费的时间。这些值永远无法完全加起来成为查询的总循环数，因为 SQLite 执行的其他内部运算不直接归因于任何循环。
"SCAN t1" 循环的循环计数为 345950，约占查询总时间的 62%。"SEARCH t1" 循环执行的 100 次查询使用了 128690 个循环，约占总时间的 23%。
当使用虚拟表时，"rows" 和 "loops" 指标与常规 SQLite 表的循环具有相同的含义。"cycles" 测量值是在与循环相关的虚拟表方法中消耗的总循环数。例如
sqlite3> SELECT * FROM ft('sqlite'), t2 WHERE t2.c=ft.rowid;
<...query results...>
QUERY PLAN (cycles=836434 [100%]
|--SCAN ft VIRTUAL TABLE INDEX 0:M1 (cycles=739602 [88%] loops=1 rows=48)
`--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) (cycles=62866 [8%] loops=48 rows=25)
在这种情况下，对 fts5 表 "ft" 的单个查询（loops=1）返回了 48 行（rows=48），并消耗了 739602 个循环（cycles=739602），大约占查询总时间的 88%。
使用上一节中相同的架构，考虑以下更复杂的示例
sqlite3> WITH cnt(i) AS (
SELECT 1 UNION SELECT i+1 FROM cnt WHERE i<100
)
SELECT
*, (SELECT d FROM t2 WHERE c=ft.rowid)
FROM
(SELECT count(*), a FROM t1 GROUP BY a) AS v1 CROSS JOIN
ft('sqlite'),
cnt
WHERE cnt.i=ft.rowid AND v1.a=ft.rowid;
<...query results...>
QUERY PLAN (cycles=177665334 [100%])
|--CO-ROUTINE v1 (cycles=4500444 [3%])
| |--SCAN t1 (cycles=397052 [0%] loops=1 rows=500)
| `--USE TEMP B-TREE FOR GROUP BY
|--MATERIALIZE cnt (cycles=1275068 [1%])
| |--SETUP
| | `--SCAN CONSTANT ROW
| `--RECURSIVE STEP
| `--SCAN cnt (cycles=129166 [0%] loops=100 rows=100)
|--SCAN v1 (loops=1 rows=500)
|--SCAN ft VIRTUAL TABLE INDEX 0:M1= (cycles=161874340 [91%] loops=500 rows=271)
|--SCAN cnt (cycles=7336350 [4%] loops=95 rows=9500)
`--CORRELATED SCALAR SUBQUERY 3 (cycles=168538 [0%] loops=37)
`--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) (cycles=94724 [0%] loops=37 rows=21)
上面示例中最复杂的部分是理解查询计划 - 报告中也会由 EXPLAIN QUERY PLAN 命令生成的部分。其他要点是
子查询 "v1" 是作为 协程 实现的。在这种情况下，子查询单独报告，并且可以获得整个子查询的 "cycles" 计数。还有一个 "SCAN v1" 行 - 这表示从主查询调用子查询协程。此条目没有与其相关的循环，因为整个子查询的成本都归因于协程。它确实有 "loops" 和 "rows" 值 - 子查询扫描一次，并返回 500 行。
递归子查询 "cnt" 在主查询运行之前进行物化（缓存到临时表中）。整个物化成本都归因于 "MATERIALIZE cnt" 元素。还有一个 "SCAN cnt" 项目，表示对物化子查询的扫描。与该项目相关的循环值表示扫描包含物化子查询的临时表所花费的时间，这与填充该表所使用的循环是分开的。
标量子查询也有循环和循环测量值。这些分别表示执行子查询所消耗的总循环数以及执行子查询的次数。
当一个项目是另一个项目的父级时，例如 "CORRELATED SCALAR SUBQUERY 3" 和 "SEARCH t2 USING..."，则与父级相关的循环值包含与所有子元素相关的循环。在所有情况下，百分比值都与查询使用的总循环数相关，而不是与父级使用的循环数相关。
以下查询使用 自动索引 和外部排序
sqlite> SELECT * FROM t2, (SELECT count(*) AS cnt, d FROM t2 GROUP BY d) AS v2 WHERE v2.d=t2.d AND t2.d>100 ORDER BY v2.cnt; <...query results...> QUERY PLAN (cycles=6234376 [100%]) |--MATERIALIZE v2 (cycles=2351916 [38%]) | |--SCAN t2 (cycles=188428 [3%] loops=1 rows=250) | `--USE TEMP B-TREE FOR GROUP BY |--SCAN t2 (cycles=455736 [7%] loops=1 rows=250) |--CREATE AUTOMATIC INDEX ON v2(d, cnt) (cycles=1668380 [27%] loops=1 rows=250) |--SEARCH v2 USING AUTOMATIC COVERING INDEX (d=?) (cycles=932824 [15%] loops=200 rows=200) `--USE TEMP B-TREE FOR ORDER BY (cycles=662456 [11%] loops=1 rows=200)
要点是
此查询将子查询物化到临时表中，然后在其上创建自动（即瞬态）索引，然后使用该索引优化联接。所有这三个步骤 - "MATERIALIZE v2"、"CREATE AUTOMATIC INDEX" 和 "SEARCH ... USING AUTOMATIC INDEX" 都具有单独的循环计数。与 "CREATE AUTOMATIC INDEX" 行相关的 "rows" 表示索引中包含的总行数。与 "SEARCH ... USING AUTOMATIC INDEX" 行相关的 "loops" 和 "rows" 表示使用索引进行的查询次数以及这些查询找到的总行数。
外部排序 "USE TEMP B-TREE FOR ORDER BY" 也是单独计算的。循环计数表示由排序返回的行所消耗的额外循环数，超过了以任意顺序返回这些行所使用的循环数。行计数表示排序的行数。
除了启用分析的 ".scanstats on" 和禁用分析的 ".scanstats off" 之外，shell 还接受 ".scanstats est"
sqlite> .scanstats est
这将启用一种特殊的分析报告，该报告在查询分析的每个 "SCAN..." 和 "SEARCH..." 元素中包含两个额外的值
sqlite> SELECT a FROM t1, t2 WHERE a IN (1,2,3) AND a=d+e ORDER BY a; <query results...> QUERY PLAN (cycles=264725190 [100%] |--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) (cycles=60511568 [23%] loops=1 rows=3 rpl=3.0 est=3.0) `--SCAN t2 (cycles=139461608 [53%] loops=3 rows=150000 rpl=50000.0 est=1048576.0)
还支持 ".scanstats vm" 命令。这将启用更低级别的分析报告，显示每个 VM 指令执行的次数以及它在执行时所占的时钟周期百分比
sqlite> .scanstats vm
然后
sqlite> SELECT count(*) FROM t2 WHERE (d % 5) = 0; <query results...> addr cycles nexec opcode p1 p2 p3 p4 p5 comment ---- ------ ------ ------------- ---- ---- ---- ------------- -- ------------- 0 0.0% 1 Init 1 18 0 0 Start at 18 1 0.0% 1 Null 0 1 1 0 r[1..1]=NULL 2 0.0% 1 OpenRead 0 2 0 2 0 root=2 iDb=0; t2 3 0.0% 1 ColumnsUsed 0 0 0 2 0 4 0.0% 1 Explain 4 0 0 SCAN t2 0 5 0.0% 1 CursorHint 0 0 0 EQ(REM(c1,5),0) 0 6 0.0% 1 Rewind 0 14 0 0 7 46.86% 150000 Column 0 1 3 0 r[3]= cursor 0 column 1 8 18.94% 150000 Remainder 4 3 2 0 r[2]=r[3]%r[4] 9 5.41% 150000 ReleaseReg 3 1 0 0 release r[3] mask 0 10 12.09% 150000 Ne 5 13 2 80 if r[2]!=r[5] goto 13 11 1.02% 30000 ReleaseReg 2 1 0 0 release r[2] mask 0 12 2.95% 30000 AggStep1 0 0 1 count(0) 0 accum=r[1] step(r[0]) 13 12.72% 150000 Next 0 7 0 1 14 0.0% 1 AggFinal 1 0 0 count(0) 0 accum=r[1] N=0 15 0.0% 1 Copy 1 6 0 0 r[6]=r[1] 16 0.0% 1 ResultRow 6 1 0 0 output=r[6] 17 0.01% 1 Halt 0 0 0 0 18 0.0% 1 Transaction 0 0 1 0 1 usesStmtJournal=0 19 0.0% 1 Integer 5 4 0 0 r[4]=5 20 0.0% 1 Integer 0 5 0 0 r[5]=0 21 0.0% 1 Goto 0 1 0 0
本页最后修改于 2023-07-27 20:27:55 UTC