分析

1. 概述

ANALYZE 命令收集有关表和索引的统计信息，并将收集的信息存储在数据库的内部表中，查询优化器可以访问这些信息并使用它们来帮助做出更好的查询规划选择。如果没有给出任何参数，则分析主数据库和所有附加的数据库。如果给出模式名称作为参数，则分析该数据库中的所有表和索引。如果参数是表名，则只分析该表以及与该表关联的索引。如果参数是索引名，则只分析该索引。

2. 推荐使用模式

使用 ANALYZE 永远不是必需的。但是，如果应用程序执行复杂的查询，这些查询具有许多可能的查询计划，则如果运行了 ANALYZE，查询规划器将能够更好地选择最佳计划。这可能导致某些查询的性能显着提高。

在接下来的部分中按优先顺序描述了何时以及如何运行 ANALYZE 的两种推荐方法。

2.1. 定期运行“PRAGMA optimize”

PRAGMA optimize 命令将在需要时自动运行 ANALYZE。建议使用

具有短暂数据库连接的应用程序应在关闭每个数据库连接之前运行一次“PRAGMA optimize;”。
使用长连接的应用程序应在连接打开时运行“PRAGMA optimize=0x10002;”，然后定期运行“PRAGMA optimize;”，可能每天一次，或者如果数据库正在快速发展，则更频繁地运行。
所有应用程序都应该在模式更改后运行“PRAGMA optimize;”，尤其是在一个或多个 CREATE INDEX 语句之后。

PRAGMA optimize 命令通常是无操作的，但它偶尔会在数据库的单个表上运行一个或多个 ANALYZE 子命令，如果这样做对查询规划器有用的话。从 SQLite 版本 3.46.0 (2024-05-23) 开始，“PRAGMA optimize”命令会自动限制 ANALYZE 子命令的范围，以便即使在庞大的数据库上，“PRAGMA optimize”命令也能快速完成。无需使用 PRAGMA analysis_limit。这是今后运行 ANALYZE 的推荐方式。

PRAGMA optimize 命令通常只会考虑对之前由相同数据库连接查询过的或在 sqlite_stat1 表中没有条目的表运行 ANALYZE。但是，如果将 0x10000 位添加到参数中，PRAGMA optimize 将检查所有表以查看它们是否可以从 ANALYZE 中获益，而不仅仅是最近查询过的那些表。数据库连接首次打开时没有查询历史记录，这就是为什么在新的数据库连接上运行 PRAGMA optimize 时建议添加 0x10000 位的原因。

有关更多信息，请参阅下面的自动运行 ANALYZE 和大型数据库的近似 ANALYZE 部分。

2.2. 分析的固定结果

运行 ANALYZE 会导致 SQLite 为后续查询选择不同的查询计划。这几乎总是件好事，因为在 ANALYZE 之后选择的查询计划几乎在所有情况下都比在 ANALYZE 之前选择的查询计划更好。这就是 ANALYZE 的全部意义。但不能证明运行 ANALYZE 总是会带来益处。可以构建一些病态情况，在这种情况下，运行 ANALYZE 会使某些后续查询运行得更慢。

一些开发人员希望，一旦应用程序的设计固定下来，SQLite 将始终选择与开发和测试期间相同的查询计划。然后，如果将数百万个应用程序副本交付给客户，开发人员可以确保所有这些数百万个副本都运行相同的查询计划，而不管各个客户将其插入其特定数据库中的数据是什么。这有助于在现场重现性能问题投诉。

要实现这个目标，不要在应用程序中运行完整的 ANALYZE 或“PRAGMA optimize”命令。相反，只在开发期间使用命令行界面或类似工具手动运行 ANALYZE，在大小和内容与实际数据库类似的测试数据库上运行。然后使用如下脚本捕获此一次性 ANALYZE 的结果

.mode list
SELECT 
  'ANALYZE sqlite_schema;' ||
  'DELETE FROM sqlite_stat1;' ||
  'INSERT INTO sqlite_stat1(tbl,idx,stat)VALUES' ||
  (SELECT group_concat(format('(%Q,%Q,%Q)',tbl,idx,stat),',')
    FROM sqlite_stat1) ||
  ';ANALYZE sqlite_schema;';

在应用程序的已部署实例中创建新数据库实例时，或者在长运行应用程序的情况下可能是在应用程序启动时，运行上述脚本生成的命令。这将填充 sqlite_stat1 表，使其与开发和测试期间完全相同，并确保在现场选择的查询计划与在实验室测试期间选择的查询计划相同。也许将上述脚本生成的字符串复制/粘贴到名为“zStat1Init”的静态字符串常量中，然后调用

sqlite3_exec(db, zStat1Init, 0, 0, 0);

也许也可以根据脚本运行的上下文，在字符串常量的开头添加“BEGIN;”，在结尾添加“COMMIT;”。

有关更多信息，请参阅查询规划器稳定性保证。

3. 详细信息

默认实现将所有统计信息存储在名为 "sqlite_stat1" 的单个表中。如果 SQLite 是使用 SQLITE_ENABLE_STAT4 选项编译的，则会收集额外的直方图数据并将其存储在 sqlite_stat4 中。SQLite 的旧版本会在使用 SQLITE_ENABLE_STAT2 或 SQLITE_ENABLE_STAT3 编译时使用 sqlite_stat2 表或 sqlite_stat3 表，但 SQLite 的所有最新版本都忽略 sqlite_stat2 和 sqlite_stat3 表。未来的增强可能会创建具有相同名称模式但最后一位数字大于“4”的更多内部表。所有这些表统称为“统计信息表”。

可以使用 SELECT 查询统计信息表的内容，可以使用 DELETE、INSERT 和 UPDATE 命令更改这些内容。从 SQLite 版本 3.7.9 开始 (2011-11-01)，DROP TABLE 命令适用于统计信息表。ALTER TABLE 命令不适用于统计信息表。更改统计信息表的内容时应谨慎，因为无效的内容会导致 SQLite 选择效率低下的查询计划。一般来说，除了调用 ANALYZE 命令之外，不应该通过任何其他机制修改统计信息表的内容。有关更多信息，请参阅“使用 SQLITE_STAT 表手动控制查询计划”。

通过 ANALYZE 收集的统计信息不会随着数据库内容的更改而更新。如果数据库内容发生重大更改，或者数据库模式发生更改，则应考虑重新运行 ANALYZE 命令以更新统计信息。

查询规划器在读取模式时将统计信息表的内容加载到内存中。因此，当应用程序直接更改统计信息表时，SQLite 不会立即注意到这些更改。应用程序可以通过运行 ANALYZE sqlite_schema 强制查询规划器重新读取统计信息表。

4. 自动运行分析

PRAGMA optimize 命令将根据需要自动对各个表运行 ANALYZE。推荐的做法是，应用程序在关闭每个数据库连接之前调用 PRAGMA optimize 语句。或者，如果应用程序长时间保持单个数据库连接打开，则应在连接首次打开时运行“PRAGMA optimize=0x10002”，然后定期运行“PRAGMA optimize;”，可能每隔几小时或每天运行一次。

每个 SQLite 数据库连接会记录查询规划器何时会从具有准确的 ANALYZE 结果中获益的情况。这些记录保存在内存中，并在数据库连接的生命周期内累积。PRAGMA optimize 命令会查看这些记录，并且只对那些新的或更新的 ANALYZE 数据可能会有用的表运行 ANALYZE。在大多数情况下，PRAGMA optimize 不会运行 ANALYZE，但它偶尔会运行，要么是对以前从未分析过的表，要么是对自上次分析以来已大幅增长的表。

由于 PRAGMA optimize 的操作在一定程度上取决于之前在相同数据库连接上评估过的查询，因此建议在关闭数据库连接之前延迟 PRAGMA optimize，以便它有时间积累尽可能多的使用信息。为长时间保持打开状态的数据库连接设置一个计时器，每隔几小时或几天运行一次 PRAGMA optimize 也是合理的。在数据库连接打开后立即运行 PRAGMA optimize 时，可以将 0x10000 位添加到位掩码参数中（从而使命令读取“PRAGMA optimize=0x10002”），这会导致检查所有表，即使是在当前连接期间尚未查询过的表。

PRAGMA optimize 命令首次引入于 SQLite 3.18.0 (2017-03-28)，对于所有之前的 SQLite 版本来说，它都是无操作的。PRAGMA optimize 命令在 SQLite 3.46.0 (2024-05-23) 中得到了显著增强，本文档中给出的建议基于这些增强。使用早期版本的 SQLite 的应用程序应参考相应的文档，以获得有关最佳使用 PRAGMA optimize 的方式的更佳建议。

5. 大型数据库的近似分析

默认情况下，ANALYZE 会对每个索引进行完整扫描。对于大型数据库，这可能很慢。因此，从 SQLite 版本 3.32.0 (2020-05-22) 开始，可以使用 PRAGMA analysis_limit 命令来限制 ANALYZE 执行的扫描量，从而帮助 ANALYZE 运行得更快，即使在非常大的数据库文件上也是如此。我们称之为运行“近似 ANALYZE”。

建议的 analysis_limit pragma 的使用模式如下

PRAGMA analysis_limit=1000;

此 pragma 告诉 ANALYZE 命令像往常一样开始对索引进行完整扫描。但是，当访问的行数达到 1000（或 pragma 指定的其他限制）时，ANALYZE 命令将开始采取措施停止扫描。如果索引的最左列在之前的 1000 步中至少更改过一次，则分析会立即停止。但如果最左列始终相同，则 ANALYZE 会跳到第一个最左列不同的条目，并读取另外 1000 行，然后终止。

前一段中描述的分析限制效果的详细信息可能会在 SQLite 的未来版本中发生变化。但核心思想将保持不变。分析限制为 N 将努力将每个索引中访问的行数限制在约 N 行。

建议 N 的值为 100 到 1000 之间。或者，要禁用分析限制，使 ANALYZE 对每个索引进行完整扫描，请将分析限制设置为 0。为了向后兼容，分析限制的默认值为 0。

近似 ANALYZE 放置在 sqlite_stat1 表中的值与不受限制的分析计算出的值并不完全相同。但它们通常足够接近。sqlite_stat1 表中的索引统计信息在任何情况下都是近似的，因此近似 ANALYZE 的结果与传统完整扫描 ANALYZE 略有不同，几乎没有实际影响。可以构建一个病态案例，其中近似 ANALYZE 明显不如完整扫描 ANALYZE，但这种情况在现实世界的问题中很少见。

一个好的经验法则是，在运行 "ANALYZE" 之前始终设置 "PRAGMA analysis_limit=N"，其中 N 为 100 到 1000 之间的某个值。过去，在运行 "PRAGMA optimize" 之前也建议这样做，但从 3.46.0 版 (2024-05-23) 开始，这种情况会自动发生。使用 PRAGMA analysis_limit 时，结果并不完全精确，但足够精确，而且计算结果的速度快得多，这意味着开发人员更有可能计算结果。近似 ANALYZE 比根本不运行 ANALYZE 更好。

5.1. 近似 ANALYZE 的局限性

sqlite_stat4 表中的内容无法通过低于完整扫描的任何方式计算。因此，如果指定了非零分析限制，则不会计算 sqlite_stat4 表。

此页面最后修改时间为 2024-05-05 15:23:53 UTC