字典表partitions_v查询性能问题

2026-07-09 其他常见问题

内容纲要

问题背景

客户需要根据数据库名、表名查询目标表所有分区名称与分区范围，原始 SQL 使用系统内置视图partitions_v、range_partitions_v实现逻辑，完整语句如下：

SELECT
partition_name AS name,
NULL AS createTimeSeconds,
partition_range AS expression
FROM (
    SELECT
    partition_name,
    NULL AS partition_range
    FROM metastore_inceptor1.partitions_v
    WHERE database_name = LOWER('mdwuser')
    AND table_name = LOWER('t_dws_s04_cust_atl_trd_aggr')
    UNION ALL
    SELECT
    partition_name,
    partition_range
    FROM metastore_inceptor1.range_partitions_v
    WHERE database_name = LOWER('mdwuser')
    AND table_name = LOWER('t_dws_s04_cust_atl_trd_aggr')
) t
ORDER BY partition_name ;

现象描述

执行该 SQL 单次耗时约 29~32 秒，接口响应超时，并发场景下极易拖慢整个元数据库。

原因分析

核心瓶颈：内置视图强制全量扫描千万行字典表
系统自带视图partitions_v、range_partitions_v创建时使用默认ALGORITHM=UNDEFINED算法，视图内部包含分区聚合逻辑（GROUP_CONCAT拼接分区值），数据库会执行先 全表扫描、再生成超大临时表 的低效逻辑：

无视外层database_name/table_name过滤条件，先完整扫描PARTITION_KEY_VALS分区值字典表581 万条全量数据；
对 581 万行数据分组、拼接字符串，生成 516 万行临时派生表存入内存 / 磁盘；
再用目标表仅 1383 条分区数据，循环关联千万行临时表做匹配；
99% 扫描、计算的海量数据最终全部丢弃，仅保留目标表少量分区，产生大量无效 IO 与 CPU 消耗。

执行计划佐证（关键信息解读）

主要耗时在 union all 上面的 partitions_v 表，执行计划解析

explain analyze
    SELECT
    partition_name,
    NULL AS partition_range
    FROM metastore_inceptor1.partitions_v
    WHERE database_name = 'mdwuser'
    AND table_name = 't_dws_s04_cust_atl_trd_aggr';

-> Nested loop inner join  (cost=8592881703.04 rows=78119330050) (actual time=29131.746..29193.443 rows=1383 loops=1)
    -> Nested loop inner join  (cost=780927953.03 rows=7809250566) (actual time=28965.008..29016.211 rows=1383 loops=1)
        -> Index lookup on part using PARTITIONS_N49 (TBL_ID='392739'), with index condition: (part.TBL_ID is not null)  (cost=821.93 rows=1383) (actual time=0.719..38.873 rows=1383 loops=1)
        -> Index lookup on partition_value using  (part_id=part.PART_ID)  (actual time=20.952..20.952 rows=1 loops=1383)
            -> Materialize  (cost=1698795.60..1698795.60 rows=5646602) (actual time=28964.239..28964.239 rows=5161874 loops=1)
                -> Group aggregate: group_concat(PARTITION_KEY_VALS.PART_KEY_VAL separator ',')  (cost=1134135.40 rows=5646602) (actual time=0.075..6504.620 rows=5161874 loops=1)
                    -> Index scan on PARTITION_KEY_VALS using PRIMARY  (cost=569475.20 rows=5646602) (actual time=0.068..3259.636 rows=5811950 loops=1)
    -> Filter: (partition_key.tab_id = '392739')  (cost=6312.68..1.50 rows=10) (actual time=0.127..0.128 rows=1 loops=1383)
        -> Index lookup on partition_key using  (tab_id=part.TBL_ID)  (actual time=0.127..0.127 rows=1 loops=1383)
            -> Materialize  (cost=7013.65..7013.65 rows=23298) (actual time=166.691..166.691 rows=23301 loops=1)
                -> Group aggregate: group_concat(PARTITION_KEYS.PKEY_NAME order by PARTITION_KEYS.INTEGER_IDX ASC separator ',')  (cost=4683.85 rows=23298) (actual time=0.054..48.071 rows=23301 loops=1)
                    -> Index scan on PARTITION_KEYS using PRIMARY  (cost=2354.05 rows=23298) (actual time=0.041..28.414 rows=23688 loops=1)

优化整体思路

针对业务仅根据库名、表名取少量字段的轻量化查询场景，新建两套专用精简视图，从底层彻底解决慢查询问题：

修改视图算法：使用ALGORITHM=MERGE合并算法，外层WHERE过滤条件可直接下推到底层字典表，避免全表扫描；
精简关联表：移除业务不需要的字典表、存储表，减少 JOIN 次数；
按需保留字段：视图仅保留筛选、返回必需字段，减少磁盘数据读取；
提前过滤数据：视图驱动顺序改为DBS(库)→TBLS(表)→PARTITIONS(分区)，先通过库名表名过滤缩小数据集，再关联分区相关表。

优化落地执行脚本

步骤 1：创建精简视图 1 partitions_v_new_slim

适配 UNION 上半段查询，仅返回分区名称、库名、表名，移除分区键大表关联：

CREATE OR REPLACE ALGORITHM=MERGE DEFINER=inceptoruser@% SQL SECURITY DEFINER VIEW partitions_v_new_slim AS
SELECT
    p.PART_NAME AS partition_name,
    t.TBL_NAME AS table_name,
    d.NAME AS database_name
FROM metastore_inceptor1.DBS d
INNER JOIN metastore_inceptor1.TBLS t ON d.DB_ID = t.DB_ID
INNER JOIN metastore_inceptor1.PARTITIONS p ON t.TBL_ID = p.TBL_ID;

步骤 2：创建精简视图 2 range_partitions_v_slim

适配 UNION 下半段查询，仅返回分区名称、分区范围、库名、表名，移除 SDS、分区键冗余关联：

CREATE OR REPLACE ALGORITHM=MERGE DEFINER=inceptoruser@% SQL SECURITY DEFINER VIEW range_partitions_v_slim AS
SELECT
    part.PART_NAME AS partition_name,
    pr.range AS partition_range,
    tbls.TBL_NAME AS table_name,
    dbs.NAME AS database_name
FROM metastore_inceptor1.DBS dbs
INNER JOIN metastore_inceptor1.TBLS tbls
    ON dbs.DB_ID = tbls.DB_ID
INNER JOIN metastore_inceptor1.PARTITIONS part
    ON tbls.TBL_ID = part.TBL_ID
-- 仅关联分区范围表，用于读取partition_range字段
LEFT JOIN (
    SELECT
        part_ranges.PART_ID,
        GROUP_CONCAT(CONCAT(part_ranges.LOW,'-',part_ranges.HIGH) SEPARATOR ',') AS range
    FROM metastore_inceptor1.part_ranges
    GROUP BY part_ranges.PART_ID
) pr ON part.PART_ID = pr.part_id;

步骤 3：替换视图后的最终业务 SQL

SELECT
partition_name AS name,
NULL AS createTimeSeconds,
partition_range AS expression
FROM (
    SELECT
    partition_name,
    NULL AS partition_range
    FROM metastore_inceptor1.partitions_v_new_slim
    WHERE database_name = LOWER('mdwuser')
    AND table_name = LOWER('t_dws_s04_cust_atl_trd_aggr')
    UNION ALL
    SELECT
    partition_name,
    partition_range
    FROM metastore_inceptor1.range_partitions_v_slim
    WHERE database_name = LOWER('mdwuser')
    AND table_name = LOWER('t_dws_s04_cust_atl_trd_aggr')
) t
ORDER BY partition_name ;

查询效率从 29~32 秒降低到 1秒以内。