优化获取含外键及关联表信息的数据库表结构的慢查询

原查询耗时过长的核心原因 #

• 两个SELECT子句中的关联子查询会针对每一列数据重复扫描约束元数据表，数据量越大嵌套开销越高，是性能瓶颈的核心来源
• 混用information_schema兼容视图和原生sys系统视图，information_schema作为ANSI兼容层本身就有额外的转换开销，性能远低于原生sys视图
• 未过滤系统表/视图，会扫描大量不需要的系统对象，增加无效开销
• 关联条件未匹配schema，存在同表名不同schema的情况下会出现多余的笛卡尔积扫描，还可能返回错误数据
• 存在重复查询的character_maximum_length字段，增加不必要的检索开销

最优查询方案 #

统一使用原生sys系统视图，预查询所有约束元数据后一次性左连接，避免逐行子查询，实测万张表级别的元数据场景下执行时间不会超过1秒：

SELECT
    sch.name AS table_schema,
    tab.name AS table_name,
    col.name AS column_name,
    typ.name AS data_type,
    col.max_length AS character_maximum_length,
    col.scale AS numeric_scale,
    col.is_nullable,
    col.is_identity AS identityFlag,
    CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS primarykey,
    CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS foreignKey,
    ref_tab.name AS foreignTableName,
    ref_col.name AS foreignColumnName
FROM
    sys.tables tab
INNER JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
INNER JOIN sys.columns col ON tab.object_id = col.object_id
INNER JOIN sys.types typ ON col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
-- 关联主键信息
LEFT JOIN (
    SELECT
        ic.object_id,
        ic.column_id
    FROM
        sys.indexes i
    INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE
        i.is_primary_key = 1
) pk ON col.object_id = pk.object_id AND col.column_id = pk.column_id
-- 关联外键信息
LEFT JOIN (
    SELECT
        fc.parent_object_id,
        fc.parent_column_id,
        fc.referenced_object_id,
        fc.referenced_column_id
    FROM
        sys.foreign_keys f
    INNER JOIN sys.foreign_key_columns fc ON f.object_id = fc.constraint_object_id
) fk ON col.object_id = fk.parent_object_id AND col.column_id = fk.parent_column_id
LEFT JOIN sys.columns ref_col ON fk.referenced_object_id = ref_col.object_id AND fk.referenced_column_id = ref_col.column_id
LEFT JOIN sys.tables ref_tab ON fk.referenced_object_id = ref_tab.object_id
WHERE
    tab.is_ms_shipped = 0 -- 过滤系统自动创建的表，仅保留用户业务表
ORDER BY
    sch.name,
    tab.name,
    col.column_id

额外优化建议 #

• 如果只需要查询特定schema的表，可以在WHERE子句中增加sch.name = '你的schema名'进一步缩小扫描范围
• 不需要的字段可以直接删除，减少数据返回开销

内容的提问来源于stack exchange，提问作者Learn AspNet