DuckDB：PRAGMA语句动态配置数据库行为

PRAGMA语句是DuckDB从SQLite中采用的SQL扩展。PRAGMA命令可能会改变数据库引擎的内部状态，并可能影响引擎的后续执行或行为。本文介绍PRAGMA命令及其典型应用场景。

DuckDB PRAGMA介绍

在 DuckDB 中，PRAGMA 是一种编译指示（compiler directive），它是一种特殊的指令，用于配置数据库的各种内部设置、行为和特性。这些指令可以影响数据库的性能、资源使用、输出显示等诸多方面，就像是为数据库引擎提供了一系列的控制开关和微调旋钮。

在这里插入图片描述

PRAGMA特点

语法简单

PRAGMA 的语法相对简洁明了。通常使用PRAGMA关键字加上具体的指令名称和相应的参数（如果需要）来使用。例如，PRAGMA memory_limit='1GB';用于设置内存限制，这种语法形式易于理解和使用，即使对于新手用户也能比较快速地掌握如何配置一些基本的数据库设置。
动态配置性

许多 PRAGMA 可以在数据库会话过程中动态设置和调整。这意味着用户可以根据具体的查询任务、数据规模和系统资源状况实时改变数据库的行为。与一些数据库中需要修改配置文件并重新启动数据库才能生效的设置相比，DuckDB 的 PRAGMA 提供了更高的灵活性。例如，在处理一个特别复杂且内存密集型的查询时，可以先增加内存限制，然后在查询完成后再将其恢复。
针对性强

PRAGMA 主要是针对 DuckDB 自身的运行特性进行配置。它聚焦于数据库引擎内部的关键要素，如内存管理、查询执行机制、输出控制等，不像一些数据库的配置选项可能涉及到更广泛的领域（如数据库的网络连接、安全认证等方面）。这使得用户可以更精准地对 DuckDB 的性能和行为进行优化和控制。

PRAGMA作用

资源管理作用
- 内存管理
  
  通过PRAGMA memory_limit来控制数据库可使用的内存量。这对于防止内存过度占用至关重要，特别是在处理大型数据集或者复杂查询时。例如，在一个资源有限的服务器上运行 DuckDB，如果不限制内存使用，可能会导致系统内存耗尽，而通过合理设置PRAGMA memory_limit，可以确保数据库在给定的内存范围内高效运行。
- 线程控制
  
  PRAGMA threads指令允许用户设置数据库在执行查询时所使用的线程数量。在多核处理器环境下，合理配置线程数可以充分利用 CPU 资源，加速查询的执行。例如，对于一个可以并行处理的数据分析任务，增加线程数可以使 DuckDB 同时处理多个数据子集，从而缩短整体的查询时间。
性能优化作用
- 查询性能分析
  
  PRAGMA enable_profiling用于开启查询性能分析功能。当开启这个功能后，DuckDB 会收集查询执行过程中的详细性能数据，例如各个操作的执行时间、数据读取和写入的量等。这些数据存储在特定的表（如duckdb_profiles）中，通过查询这个表，用户可以深入了解查询的性能瓶颈，进而对查询进行优化。例如，如果发现某个连接操作（JOIN）在查询执行过程中占用了大量时间，就可以考虑优化表结构或者连接条件。
- 优化执行策略（部分情况）
  
  虽然目前公开的直接用于控制查询执行策略的 PRAGMA 相对有限，但在未来或者通过一些间接方式，PRAGMA 可能会用于引导查询执行计划的生成。例如，影响优化器对于索引的使用策略、子查询的展开方式等，从而使查询能够以更高效的方式执行。
用户体验和输出控制作用
- 进度条显示控制
  
  PRAGMA disable_progress_bar可以用于控制在执行长时间查询时是否显示进度条。在自动化脚本或者不需要可视化进度反馈的场景下，禁用进度条可以减少不必要的输出，使脚本的输出更加简洁。而在需要用户观察查询进度的情况下，又可以方便地重新启用进度条。
- 潜在的输出格式控制
  
  虽然目前 DuckDB 在这方面的功能有限，但从发展的角度看，PRAGMA 可用于控制查询结果的输出格式。例如，有可能通过特定的 PRAGMA 来决定输出结果是按照传统的表格形式、JSON 格式还是其他自定义的格式，以满足不同用户场景和与其他系统交互需求。

PRAGMA 示例

设置内存限制

假设你正在处理一个可能占用大量内存的数据加载任务，并且你的系统内存有限。你可以使用PRAGMA memory_limit来限制 DuckDB 使用的内存量。例如，要将内存限制设置为 2GB（2 * 1024 * 1024 * 1024 字节），可以在 DuckDB 客户端或脚本中执行以下命令：

# 设置内存限制
PRAGMA memory_limit='2147483648';# 查询内存限制
PRAGMA memory_limit;

没有 PRAGMA 语句时，数据库的许多配置参数可能是固定的，或者需要修改配置文件并重新启动数据库才能生效。例如在一些传统数据库中，要调整内存使用参数，可能需要编辑配置文件（如 PostgreSQL 的postgresql.conf文件），然后重启数据库服务。而 DuckDB 的 PRAGMA 语句可以在数据库运行过程中动态地改变数据库的行为。例如，通过PRAGMA memory_limit，可以根据当前的查询任务即时调整内存限制。如果正在执行一个小型查询，可将内存限制设置得较低；当遇到大型数据处理任务时，再动态增加内存限制，这为用户提供了很大的灵活性。

设置线程数量

当你在一个多核处理器的系统上运行 DuckDB，并且希望利用多核优势来加速查询执行时，可以使用PRAGMA threads来设置线程数。例如，若你的系统有 4 个核心，并且你想让 DuckDB 使用 4 个线程来执行查询，可以执行以下命令：

# 设置线程限制
PRAGMA threads=4;# 查看线程限制
PRAGMA threads;

开启性能分析

当你遇到一个执行速度较慢的查询，并且想要找出性能瓶颈时，可以开启查询性能分析。使用以下命令开启性能分析功能：

PRAGMA enable_profiling = true;

开启后，DuckDB 会在执行查询时收集性能数据。等你执行查询后，可以通过查询duckdb_profiles表来查看性能分析数据，如：

SELECT * FROM duckdb_profiles;

这个表中会包含诸如查询计划执行时间、各个操作符（如扫描操作、连接操作）的时间消耗等详细信息。通过分析这些数据，你可以确定哪个部分的查询执行花费了最多的时间，例如，如果发现连接操作花费的时间最长，你可以考虑优化表结构或者连接条件来提高性能。

查询元数据

列出Schema信息：

# 列出所有数据库
PRAGMA database_list;
# 列出所有数据表
PRAGMA show_tables;# 列出所有表，类似describe 
PRAGMA show_tables_expanded;

表信息

# 返回所有表的字段信息
PRAGMA table_info('table_name');
CALL pragma_table_info('table_name');

示例输出如下：

cid INTEGER,        -- cid of the column
name VARCHAR,       -- name of the column
type VARCHAR,       -- type of the column
notnull BOOLEAN,    -- if the column is marked as NOT NULL
dflt_value VARCHAR, -- default value of the column, or NULL if not specified
pk BOOLEAN          -- part of the primary key or not

数据库大小

# 获取每个数据库的文件和内存大小：
SET database_size;
CALL pragma_database_size();

返回信息示例如下：

database_name VARCHAR, -- database name
database_size VARCHAR, -- total block count times the block size
block_size BIGINT,     -- database block size
total_blocks BIGINT,   -- total blocks in the database
used_blocks BIGINT,    -- used blocks in the database
free_blocks BIGINT,    -- free blocks in the database
wal_size VARCHAR,      -- write ahead log size
memory_usage VARCHAR,  -- memory used by the database buffer manager
memory_limit VARCHAR   -- maximum memory allowed for the database

存储信息

# 获取表存储信息
PRAGMA storage_info('table_name');
CALL pragma_storage_info('table_name');

返回下面表格信息：

Name	Type	Description
`row_group_id`	`BIGINT`
`column_name`	`VARCHAR`
`column_id`	`BIGINT`
`column_path`	`VARCHAR`
`segment_id`	`BIGINT`
`segment_type`	`VARCHAR`
`start`	`BIGINT`	The start row id of this chunk
`count`	`BIGINT`	The amount of entries in this storage chunk
`compression`	`VARCHAR`	Compression type used for this column – see the “Lightweight Compression in DuckDB” blog post
`stats`	`VARCHAR`
`has_updates`	`BOOLEAN`
`persistent`	`BOOLEAN`	`false` if temporary table
`block_id`	`BIGINT`	empty unless persistent
`block_offset`	`BIGINT`	empty unless persistent