小. 快速. 可靠.
选择任意三个.
ANALYZE

1. Overview

analyze-stmt:

syntax diagram analyze-stmt

ANALYZE命令收集有关表和索引的统计信息,并将收集到的信息存储在数据库的内部表中,查询优化器可以在其中访问信息并使用它来帮助做出更好的查询计划选择. 如果未提供任何参数,则将分析所有连接的数据库. 如果给定模式名称作为参数,则将分析该数据库中的所有表和索引. 如果参数是表名,则仅分析该表以及与该表关联的索引. 如果参数是索引名称,则仅分析该索引.

具有使用复杂查询的长寿命数据库的应用程序应该在关闭每个数据库连接之前考虑运行以下命令:

PRAGMA analysis_limit=400;
PRAGMA optimize;

Optimized Pragma通常是禁止操作的,但是如果这样做对查询计划者很有用,它有时会运行ANALYZE. analysis_limit杂注限制了运行优化杂注的任何ANALYZE命令的范围,因此它不会占用过多的CPU周期. 常数" 400"可以根据需要进行调整. 对于大多数应用程序,介于100到1000之间的值效果很好.

2. Details

默认实现将所有统计信息存储在一个名为" sqlite_stat1 "的表中. 如果使用SQLITE_ENABLE_STAT4选项编译SQLite,则将收集其他直方图数据并将其存储在sqlite_stat4中 . 当使用SQLITE_ENABLE_STAT2SQLITE_ENABLE_STAT3编译时,较旧版本的SQLite将使用sqlite_stat2表或sqlite_stat3表,但所有最新版本的SQLite都会忽略sqlite_stat2和sqlite_stat3表. 将来的增强功能可能会创建其他具有相同名称模式的内部表 ,但最终数字大于" 4". 所有这些表统称为"统计表".

可以使用SELECT查询统计信息表的内容,也可以使用DELETEINSERTUPDATE命令对其进行更改. 从SQLite 3.7.9版本开始, DROP TABLE命令可用于统计表. (2011-11-01) ALTER TABLE命令不适用于统计信息表. 更改统计信息表的内容时应格外小心,因为无效的内容会导致SQLite选择效率低的查询计划. 一般而言,除了调用ANALYZE命令之外,不应通过任何机制来修改统计信息表的内容. 有关更多信息,请参见" 使用SQLITE_STAT表手动控制查询计划 ".

随着数据库内容的更改,由ANALYZE收集的统计信息不会自动更新. 如果数据库的内容发生重大变化,或者数据库架构发生变化,则应考虑重新运行ANALYZE命令以更新统计信息.

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

3. Automatically Running ANALYZE

PRAGMA优化命令将根据需要在各个表上自动运行ANALYZE. 建议的做法是,应用程序在关闭每个数据库连接之前立即调用PRAGMA优化语句.

每个SQLite 数据库连接都会记录查询计划者从准确的ANALYZE结果中受益时的情况. 这些记录保存在内存中,并在数据库连接的整个生命周期内累积. PRAGMAoptimize命令查看这些记录,并仅在那些可能对新的或更新的ANALYZE数据有用的表上运行ANALYZE. 在大多数情况下, PRAGMA优化将不会运行ANALYZE,但偶尔会对从未进行过分析的表或自上次分析以来已显着增长的表进行分析.

由于PRAGMA Optimize的操作在某种程度上是由先前在同一数据库连接上评估过的查询确定的,因此建议将PRAGMA Optimize推迟到数据库连接关闭之前,从而有机会积累尽可能多的使用情况信息.尽可能. 对于长时间保持打开状态的数据库连接,设置计时器每隔几小时或每隔几天运行一次PRAGMA Optimizer也很合理.

希望获得更多控制的应用程序可以运行PRAGMAoptimize(0x03)以获得SQLite认为适合运行的ANALYZE命令列表,但无需实际运行这些命令. 如果返回的集合为非空,则应用程序可以在提示用户进行指导之后,决定是否运行建议的ANALYZE命令.

The PRAGMA optimize command was first introduced with SQLite 3.18.0 (2017-03-28) and is a no-op for all prior releases of SQLite.

4. Approximate ANALYZE For Large Databases

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

建议的analysis_limit用法的用法模式如下所示:

PRAGMA analysis_limit=1000;

该编译指示告诉ANALYZE命令以通常的方式开始对索引的完整扫描. 但是,当访问的行数达到1000(或编译指示指定的其他限制)时,ANALYZE命令将开始采取措施停止扫描. 如果索引的最左列在之前的1000个步骤中至少更改了一次,则分析将立即停止. 但是,如果最左边的列始终是相同的,则ANALYZE会跳到最左边一列不同的第一个条目,并在终止之前再读取1000行.

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

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

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

一个好的经验法则是在运行" ANALYZE"或" PRAGMAoptimize "之前,始终将N的" PRAGMA analysis_limit = N"设置为100到1000. 结果不是很精确,但是足够精确,而且结果计算速度如此之快意味着开发人员更有可能计算它们. 近似ANALYZE比根本不运行ANALYZE更好.

4.1. Limitations of approximate ANALYZE

sqlite_stat4表中的内容不能用完全扫描之外的任何方法计算. 因此,如果指定了非零分析限制,则不会计算sqlite_stat4表.

by  ICOPY.SITE