使用 AI 调优 SQL 查询

为了优化 AlloyDB for PostgreSQL 数据库查询性能，您可以使用 AI 驱动的分析洞见来调整 SQL 查询。ai.tune_sql 函数由大语言模型 (LLM) 提供支持，可通过分析查询执行计划来识别复杂的瓶颈，并提供可据以采取行动的优化建议。

调用 ai.tune_sql 函数时，它会分析您的查询，并在以下方面提供 Query Insights 和建议：

  • 查询摘要：分析查询性能瓶颈。
  • 查询重写：建议使用优化技术将原始查询重写为等效且更高效的查询。
  • 索引建议：建议索引类型，例如表达式索引、通用倒排索引 (GIN) 和可扩缩最近邻 (ScaNN) 索引。
  • 统计信息收集建议：根据查询执行计划，建议对多个列执行表 ANALYZE 和统计信息收集。
  • 全局统一配置 (GUC) 标志调优：通过分析执行计划和系统信息（例如内存和缓冲区用量及大小）来推荐新的 GUC 值。
  • 分析和清理：根据分析和清理历史记录以及活动和不活动元组计数，建议对表运行 ANALYZE 和 VACUUM。

限制

SQL 查询调优具有以下限制：

  • 预准备语句和参数化语句：对于预准备语句和参数化语句，ai.tune_sql 不会提供查询重写建议，但仍会提供其他调优建议。如需针对包含参数占位符（例如从 Query Insights 或 pg_stat_statements 复制的 $1 或 $2）的查询接收查询重写建议，请在运行 ai.tune_sql 之前将占位符替换为代表性的字面量值。
  • 仅当 explain_analyze 为 TRUE 时，才支持只读 SELECT 语句：当 explain_analyze 设置为 TRUE 时，仅支持单个只读 SELECT 语句。在其他语句（例如 INSERT、UPDATE、DELETE、SELECT INTO 或 SELECT FOR UPDATE）上运行 ai.tune_sql 并将 explain_analyze 设置为 TRUE 会返回出错提示。

准备工作

验证您是否具备以下条件：

启用 SQL 调优

注册 LLM 并启用所需的数据库标志。

  1. 通过在实例区域中注册全局模型端点或区域模型端点，为 ai.tune_sql 函数创建 Gemini 模型端点：

    • 如需在模型在实例区域中不可用时注册全球模型端点（例如 locations/global 上的 gemini-3.5-flash），请运行以下语句：

      CALL google_ml.create_model(
  model_id => 'gemini-3.5-flash-global',
  model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.5-flash:generateContent',
  model_qualified_name => 'gemini-3.5-flash',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      将 PROJECT_ID 替换为模型所在项目的 ID。

    • 如需在实例区域（例如 locations/REGION_ID 上的 gemini-2.5-pro）中注册区域级模型端点，请运行以下语句：

      CALL google_ml.create_model(
  model_id => 'gemini-2.5-pro',
  model_request_url => 'https://REGION_ID-aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/REGION_ID/publishers/google/models/gemini-2.5-pro:generateContent',
  model_qualified_name => 'gemini-2.5-pro',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      替换以下内容：

      • REGION_ID：AlloyDB 实例的区域，例如 us-central1。
      • PROJECT_ID：模型所在项目的 ID。

  2. 在当前数据库会话中设置 SQL 查询调优建议的模型 ID。指定您在上一步中注册的 model_id，例如 'gemini-3.5-flash-global' 或 'gemini-2.5-pro'：

    SET google_db_advisor.llm_model_id = 'gemini-3.5-flash-global';

  3. 通过设置 google_db_advisor.enable_ai_tune_sql 标志，为当前数据库会话启用 SQL 查询调优：

    SET google_db_advisor.enable_ai_tune_sql = on;

    由于 SET 仅适用于当前数据库会话，因此您可以通过为这两个标志运行 ALTER DATABASE DATABASE_NAME SET FLAG_NAME = VALUE; 或配置实例的数据库标志，在会话之间保留这些设置。

手动调优 SQL 查询

如需手动调优 SQL 查询，请使用 SELECT 语句运行 ai.tune_sql 函数。

  1. 将 psql 客户端连接到 AlloyDB 实例，或打开 AlloyDB Studio。

    如果您使用 SET 配置了 google_db_advisor.llm_model_id 和 google_db_advisor.enable_ai_tune_sql，请在调用 ai.tune_sql 之前在同一数据库会话中运行这些 SET 语句。

  2. 如需调优 SQL 查询，请运行 ai.tune_sql 函数。此函数会检索与查询相关的架构，并将其与查询一起发送到 LLM 进行性能分析：

    SELECT ai.tune_sql(query => 'SQL_QUERY', explain_analyze => TRUE);

    将 SQL_QUERY 替换为您要分析的 SQL 查询。

    默认情况下，执行参数 (explain_analyze) 设置为 TRUE。设置为 TRUE 时，该函数会运行 EXPLAIN ANALYZE 来执行查询并捕获执行计划。这种方法可提供更准确、更有用的查询调优建议。如果您将 explain_analyze 设置为 FALSE，则该函数仅运行 EXPLAIN 来生成计划，而不执行查询。

查看包含性能建议的查询计划

输出首先显示标准查询计划，然后显示 AI 生成的详细建议。这些建议包括查询摘要、建议的重写，以及有关统计信息收集、GUC 标志调整和清理的建议。ai.tune_sql 函数可能需要 2 到 4 分钟才能生成建议。

以下示例展示了反联接查询的 ai.tune_sql 输出：

SELECT ai.tune_sql(
  'SELECT *
   FROM store_sales
   WHERE ss_customer_sk NOT IN (
     SELECT sr_customer_sk
     FROM store_returns
     WHERE sr_item_sk = ss_item_sk)');

                     tune_sql
--------------------------------------------------
 NOTE
 --------------------------------------------------
 Advices generated with the assistance of an LLM. Independent verification of these advices is required due to the potential for inaccuracies.
 --------------------------------------------------
 ORIGINAL QUERY PLAN
 --------------------------------------------------
 Seq Scan on store_sales  (cost=0.00..111292742.52 rows=1440384 width=115) (actual time=0.092..103093.207 rows=1306312.00 loops=1)
   Filter: (NOT (ANY (ss_customer_sk = (SubPlan 1).col1)))
   Rows Removed by Filter: 1574198
   Buffers: shared hit=72987539
   SubPlan 1
     ->  Index Scan using idx_sr_item_sk on store_returns  (cost=0.42..76.74 rows=18 width=4) (actual time=0.005..0.034 rows=19.38 loops=2880510)
           Index Cond: (sr_item_sk = store_sales.ss_item_sk)
           Index Searches: 2880510
           Buffers: shared hit=72933670
 Planning Time: 0.257 ms
 --------------------------------------------------
 QUERY SUMMARY
 --------------------------------------------------
 The original query plan shows a Sequential Scan on store_sales with a correlated SubPlan for the NOT IN condition. For each of the 1.44 million rows in store_sales, it executes an Index Scan on store_returns, resulting in over 2.88 million index searches. This nested loop execution is the primary bottleneck, taking over 103 seconds. Converting the correlated NOT IN subquery to an anti-join (NOT EXISTS) will allow the optimizer to use a Hash Anti Join, processing both tables in bulk and significantly reducing execution time.
 --------------------------------------------------
 AI SUGGESTED QUERY 1
 --------------------------------------------------
 SELECT * FROM store_sales WHERE NOT EXISTS (SELECT 1 FROM store_returns WHERE store_returns.sr_item_sk = store_sales.ss_item_sk AND (store_returns.sr_customer_sk = store_sales.ss_customer_sk OR store_returns.sr_customer_sk IS NULL OR store_sales.ss_customer_sk IS NULL));
 Reason: The original query uses a NOT IN subquery which is executed as a correlated subquery (SubPlan), leading to millions of index scans. Rewriting to NOT EXISTS allows the optimizer to use a Hash Anti Join, which is much more efficient for large datasets. The OR IS NULL conditions ensure strict semantic equivalence with NOT IN when dealing with NULL values.
 --------------------------------------------------
 REWRITTEN QUERY PLAN
 --------------------------------------------------
 Gather  (cost=8541.96..139088.40 rows=154194 width=115)
   Workers Planned: 2
   ->  Parallel Hash Anti Join  (cost=7541.96..122669.00 rows=64248 width=115)
         Hash Cond: (store_sales.ss_item_sk = store_returns.sr_item_sk)
         Join Filter: ((store_returns.sr_customer_sk = store_sales.ss_customer_sk) OR (store_returns.sr_customer_sk IS NULL) OR (store_sales.ss_customer_sk IS NULL))
         ->  Parallel Seq Scan on store_sales  (cost=0.00..65872.20 rows=1200320 width=115)
         ->  Parallel Hash  (cost=6044.76..6044.76 rows=119776 width=8)
               ->  Parallel Seq Scan on store_returns  (cost=0.00..6044.76 rows=119776 width=8)
 --------------------------------------------------
 AI SUGGESTED INDEX 1
 --------------------------------------------------
 CREATE INDEX idx_sr_item_customer ON store_returns (sr_item_sk, sr_customer_sk);
 Reason: A composite index on (sr_item_sk, sr_customer_sk) allows index-only scans for the subquery or early aggregation, reducing table access and speeding up the anti-join condition evaluation.
 Note: Indexes are suggested solely based on the given query, but they don't consider the overall system workload.
           The suggested indexes improve the performance of the original query.
 --------------------------------------------------
 GUC FLAGS TUNING
 --------------------------------------------------
 SET work_mem = '64MB'; The current work_mem of 4MB is too small for a Hash Anti Join on tables with hundreds of thousands of rows. Increasing it to 64MB allows the hash table for store_returns to fit entirely in memory, preventing costly disk spills during the join.

 Execution Time: 103167.475 ms
(50 rows)

后续步骤