1. 什么是数据库统计信息?
2. 管理 SQLite 中的统计信息
3. 最佳实践: 定期更新统计信息

SQL 是一种声明式语言, 优雅地表达了想要什么数据, 而不是如何获取它. 这一过程由由数据库引擎实现.

人们很容易将查询规划器(execution planer)视为一个不可知的黑匣子, 从使用角度缺失可以在很大程度上忽略它. 但这种"黑匣子"的描述具有误导性.

因为输入到查询规划器的信息都在我们的控制之中! 如果提供糟糕的输入, 查询规划器的输出--查询执行计划--也可能不佳.

查询规划器的关键输入是什么? 它们包括:

• 查询: SQL 语句本身, 定义了需要什么数据.
• 表: 数据库表中的结构和数据.
• 索引: 旨在加速数据检索的数据结构.
• 统计信息: 关于数据分布和特征的动态元数据.

在这篇博客文章中, 将重点关注这些关键输入之一: 数据库统计信息. 深入探讨数据库统计信息是什么, 以及它们如何使查询规划器能够做出明智的决策.

什么是数据库统计信息?

简单来说, 数据库统计信息是动态元数据, 可帮助查询规划器做出更好的决策. 将它们视为数据的信息快照, 查询规划器使用这些快照来估计不同查询执行策略的成本和效率.

这些统计信息究竟是如何指导查询规划器的呢?

考虑 SQLite 中的 contacts 表, 结构如下所示:

CREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT,
    private BOOLEAN
);

CREATE INDEX idx_name ON contacts (name);
CREATE INDEX idx_private ON contacts (private);

研究查询规划器可能如何选择执行针对此 contacts 表的几个查询.

查询 1: 按姓名选择联系人

SELECT * FROM contacts WHERE name = 'James';

在 name 列上有一个索引 idx_name. 数据库引擎是否应该始终使用此索引来满足此查询?

不一定. 如果 contacts 表仅包含少量行, 那么忽略索引并执行全表扫描实际上可能更有效率.

为什么在这种情况下表扫描可能更可取? 索引查找虽然通常很快, 但确实会产生一些开销. 使用索引涉及在索引页和表页之间跳转以检索完整的行数据. 当表很小时, 索引使用的开销可能会超过与简单地扫描表中的所有行相比的优势.

但是, 如果 contacts 表包含大量行, 则使用索引会变得快得多--通常快几个数量级.

查询 2: 选择私人联系人

SELECT * FROM contacts WHERE private = TRUE;

在这种情况下, 查询的是 private 列, 该列也被索引 (idx_private) 并且包含布尔值(TRUE 或 FALSE). 查询规划器应该在这里使用 idx_private 索引吗?

同样, 视情况而定! 具体来说, 这取决于 private 列中值的分布.

如果 contacts 表中 99% 的行将 private 设置为 TRUE, 那么查询 private = TRUE 可能会检索几乎整个表. 在这种情况下, 表扫描可能比使用索引更有效率. 因为使用索引来检索几乎所有行将涉及大量的索引查找, 可能变得不如顺序表扫描有效.

相反, 如果搜索 private 设置为 FALSE 的 1% 的行(数据的一个小得多的子集), 那么使用 idx_private 索引将是更优越的选择. 索引将快速查明与条件匹配的少量行, 从而避免代价高昂的全表扫描.

这些示例突出表明, 查询规划器理想情况下需要关于表, 索引和数据分布的统计信息, 以便就查询执行策略做出明智的决策. 这不是盲目地一直使用索引; 而是根据数据特征智能地选择最有效的路径.

关键的要点不是记住查询规划器使用的特定优化算法. 而是要认识到查询规划器是一个复杂的组件, 如果它具有适当的输入(包括适当的索引和最新的统计信息), 则能够进行高效的查询执行.

管理 SQLite 中的统计信息

这些至关重要的统计信息从何而来, 以及如何生成和更新它们? 在 SQLite 中, 需要手动更新统计信息. 方法如下:

1. ANALYZE 命令:

ANALYZE 命令是生成统计信息的主要方法.

• ANALYZE;: 不带参数运行 ANALYZE 会为整个数据库中的每个索引生成统计信息.
• ANALYZE schema-name.table-name; 或 ANALYZE index-name;: 可以将分析范围缩小到特定的模式, 表, 甚至单个索引.

-- 为数据库中的所有索引生成统计信息
ANALYZE;

-- 为"main" 模式中的" contacts" 表生成统计信息
ANALYZE main.contacts;

-- 为" idx_name" 索引生成统计信息
ANALYZE idx_name;

重要的是要注意 ANALYZE 可能是一个 开销很大的操作, 尤其是在大型数据库上. 它可能需要一些时间才能完成, 并且至关重要的是, 它在运行时会对数据库施加 写锁.
对于长期工作, 并发访问的数据库, 此写锁可能会成为问题. 因此, 通常 不建议将直接运行 ANALYZE 作为生产数据库的例行做法.

2. 使用 PRAGMA analysis_limit 优化的 ANALYZE:

SQLite 提供了一种限制 ANALYZE 扫描的数据量的方法, 从而显着加快其速度. 可以使用 PRAGMA analysis_limit 命令设置"分析限制":

PRAGMA analysis_limit = 500; -- 将分析限制为 500 个数据页的样本
ANALYZE; -- 在分析限制生效的情况下进行分析

通过设置 PRAGMA analysis_limit, 指示 ANALYZE 进程在生成统计信息时仅考虑数据页的样本, 而不是扫描整个数据库.

这在速度和统计准确性之间提供了良好的平衡. 虽然不是绝对精确, 但采样的统计信息通常"足够好" , 足以让查询规划器做出合理的决策.

在设置分析限制的情况下, ANALYZE 完成速度会快得多, 因为它处理的是较小的数据子集.

3. 推荐方法: PRAGMA optimize;

即使使用 analysis_limit, 直接调用 ANALYZE 也不是进行持续数据库维护的最佳实践. SQLite 建议使用 PRAGMA optimize;.

PRAGMA optimize;

运行 PRAGMA optimize; 通常是空操作(无操作). 但是, SQLite 在内部检查更新统计信息是否可能提高查询性能.

如果它确定统计信息已过时或丢失, 并且更新统计信息将是有益的, 则 PRAGMA optimize; 将触发统计信息的自动更新. 这种自动更新可能会使用类似于 analysis_limit 的抽样技术以提高效率.

与定期手动运行 ANALYZE 相比, 这种 PRAGMA optimize; 方法效率更高且侵入性更小. 它使 SQLite 能够在后台智能地管理统计信息更新, 仅在需要时才进行更新.

检查统计信息(可选): sqlite_stat1 表

虽然对于大多数开发人员来说不是必需的, 但如果对收集的统计信息感到好奇, 可以查看 sqlite_stat1 表(以及可能存在的其他 sqlite_stat 表, 具体取决于 SQLite 版本).

SELECT * FROM sqlite_stat1;

sqlite_stat1 表包含 tbl, idx 和 stat 等列. stat 列包含逗号分隔的值. 第一个值是表或索引中的近似行数. 后续整数提供有关索引列中值分布的信息(例如, 索引的第一列中具有相同值的行数).

但是, 对于绝大多数开发人员来说, 直接检查 sqlite_stat 表没有必要. 关键的要点是了解统计信息是存在的, 查询规划器使用它们, 并且应该维护它们.

最佳实践: 定期更新统计信息

核心信息是, 对于长期存在的数据库, 应该定期更新统计信息, 以确保查询规划器拥有做出最佳决策所需的信息.

多久更新一次? 建议的做法是在每次关闭数据库连接之前执行 PRAGMA optimize;.

import sqlite3

def main():
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()

    # ... 执行数据库操作 ...

    cursor.execute("PRAGMA optimize;") # 关闭前更新统计信息
    conn.close()

if __name__ == "__main__":
    main()

如果数据库连接保持打开很长时间, 请考虑设置计时器或计划任务以在适当的时间间隔(例如, 每晚或在非高峰时段)定期运行 PRAGMA optimize;. 频率取决于数据更改的活跃程度; 更频繁的数据修改可能需要更频繁的统计信息更新.

通过主动管理数据库统计信息, 可以使 SQLite 查询规划器发挥其魔力, 确保查询尽可能高效地运行.