利用INFORMATION_SCHEMA元数据表进行数据治理：审计查询与存储成本监控

Huang Jie 2026-08-04 18:26

在日常开发里，数据库就像一个大仓库，里面的表就是货架，数据就是货品。平时我们只关心业务逻辑，很少会去翻看“仓库管理台账”。这个台账，就是 INFORMATION_SCHEMA 元数据表。它默默记录着每张表、每个字段、每个索引的“身份信息”和“健康状态”。如果能把它用好，数据治理工作就能轻松不少。

听起来有点抽象，我举个例子。假设你接手了一个老系统，到处都是历史遗留问题。老板问你：那几张表占用空间最大？哪些表已经一年多没人动过了？哪个库的索引建得乱七八糟？这些问题看起来需要专门的监控平台，但实际上用几条 SQL 就能从 INFORMATION_SCHEMA 里得到答案。

一、为什么数据治理要盯上元数据表

1.1 元数据表是数据库的“户口本”

每个数据库实例里都有一组只读的虚拟表，它们不存业务数据，专门存放数据库自身的信息。比如：

有哪些数据库、哪些表、哪些字段；
每张表有多少行、占用多少磁盘；
索引、外键、约束是怎么定义的；
表是什么时候创建的，什么时候被更新过。

这些信息平时不起眼，但一旦出了问题，比如线上库突然变慢、磁盘快满了、某个表被删了字段，我们第一反应就是查这些元数据表。更重要的是，数据治理并不只是 DBA 的事，业务研发也需要关注自己负责的表是否规范、是否占用太多成本。

1.2 什么时候会用到它

数据治理不是一天做完的事，而是一个持续的过程。审计查询和成本监控是其中两个常见场景：

审计查询：想知道谁改了什么结构，哪些表没有主键，哪些字段类型不合适，哪些索引没有被使用。
成本监控：想知道哪些表占了大量空间，哪些表是“僵尸表”，清理之后能释放多少资源。

这时候，INFORMATION_SCHEMA 里的 TABLES、COLUMNS、STATISTICS 等表就能直接派上用场。简单说，它就是我们了解数据库现状的最短路。

二、利用INFORMATION_SCHEMA做审计查询

2.1 查表结构变更痕迹

MySQL 的 TABLES 表里有 CREATE_TIME 和 UPDATE_TIME。UPDATE_TIME 并不是每次 update 业务数据都会变，它主要反映的是表结构或者某些引擎下的数据变更。但我们可以用它来找最近被改动过的表，进一步分析是否存在未经评审的变更。

比如，下面这条查询可以列出最近 7 天里结构可能变动过的所有表。下面的案例全部使用 MySQL 8.0 的 SQL 语法。

-- 查出 information_schema 中所有表的最近更新信息
SELECT
    TABLE_SCHEMA        AS '数据库名',   -- 所属数据库
    TABLE_NAME          AS '表名',       -- 表名
    UPDATE_TIME         AS '最近更新',   -- 可能是结构变更或部分引擎的数据更新
    CREATE_TIME         AS '创建时间'    -- 建表时间
FROM
    information_schema.TABLES
WHERE
    UPDATE_TIME IS NOT NULL              -- 过滤掉从未更新过的表
    AND UPDATE_TIME >= NOW() - INTERVAL 7 DAY  -- 7天内
ORDER BY
    UPDATE_TIME DESC;                    -- 最近的排前面

这个结果给我们一个审计切入点。看到了没有？一条简单的查询，就能把最近“躁动”的表拉出来。如果发现有些表不是自己团队改的，那就该去查查操作记录了。

2.2 找出没有主键的表

主键是数据治理里的红线。没有主键的表容易出现重复数据，binlog 同步也容易出问题，delete 和 update 的性能也会受影响。用元数据表能很快找出来。

-- 思路：先从一个常数列表里生成所有表，再去掉有主键的表
SELECT
    t.TABLE_SCHEMA AS '数据库',
    t.TABLE_NAME   AS '表名',
    t.TABLE_ROWS   AS '估算行数'
FROM
    information_schema.TABLES t
WHERE
    t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')  -- 跳过系统库
    AND t.TABLE_TYPE = 'BASE TABLE'          -- 只要普通表，不要视图
    AND NOT EXISTS (
        SELECT 1
        FROM information_schema.TABLE_CONSTRAINTS tc
        WHERE tc.TABLE_SCHEMA = t.TABLE_SCHEMA
          AND tc.TABLE_NAME = t.TABLE_NAME
          AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'  -- 只看主键约束
    )
ORDER BY
    t.TABLE_SCHEMA, t.TABLE_NAME;

这里我们关联了 TABLE_CONSTRAINTS，它记录着所有约束信息。查出来的表就需要重点审查：是故意不建主键，还是历史遗留问题。我之前见过一张日志表，没有主键，数据重复率高达 20%，后来加上自增主键并清理后，问题才消失。

2.3 检查索引是否冗余

索引太多会增加写入成本，太少了影响查询性能。利用 STATISTICS 视图，可以找出完全重复的索引。比如同一列上既有 idx_name 又有 idx_name_2，就属于冗余索引。

-- 按库、表、索引名分组，看看是否有多个索引使用相同的列
SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME   AS '表',
    INDEX_NAME   AS '索引名',
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ', ') AS '索引列',
    COUNT(*)     AS '列数'
FROM
    information_schema.STATISTICS
WHERE
    TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY
    TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
HAVING COUNT(*) > 0
ORDER BY
    TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;

但这只是最粗的一层。更精确的冗余判断需要比较索引列顺序。不过用元数据表先缩小范围，再结合慢查询日志，已经能帮我们避免大部分无谓索引。

要注意的是，INFORMATION_SCHEMA 是只读的，它只能告诉我们“是什么”，不能告诉我们“为什么”。所以审计的结果需要业务团队一起确认。

2.4 把审计结果存成快照

审计查询不能只做一次，最好定期记录。我们可以建一张快照表，把当前所有表的元数据存下来。这样以后想回看某一天的状态，或者对比前后变化，都非常方便。

-- 创建一张元数据快照表
CREATE TABLE meta_snapshot (
    id           INT AUTO_INCREMENT PRIMARY KEY,   -- 自增主键
    db_name      VARCHAR(64)  NOT NULL,            -- 数据库名
    table_name   VARCHAR(64)  NOT NULL,            -- 表名
    table_rows   BIGINT,                           -- 估算行数
    data_mb      DECIMAL(12,2),                    -- 数据大小 MB
    index_mb     DECIMAL(12,2),                    -- 索引大小 MB
    total_mb     DECIMAL(12,2),                    -- 总大小 MB
    create_time  DATETIME,                         -- 建表时间
    update_time  DATETIME,                         -- 最近更新时间
    snapshot_time DATETIME DEFAULT CURRENT_TIMESTAMP,  -- 快照生成时间
    UNIQUE KEY uk_snapshot_tbl (snapshot_time, db_name, table_name)  -- 防止重复采集
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 每天插入一次当前元数据（可以放到计划任务里执行）
INSERT INTO meta_snapshot
    (db_name, table_name, table_rows, data_mb, index_mb, total_mb, create_time, update_time)
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2),
    ROUND(INDEX_LENGTH / 1024 / 1024, 2),
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2),
    CREATE_TIME,
    UPDATE_TIME
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

有了快照表，审计就不只是“看当前”，而是能看到时间轴上的变化。比如排查某一天表结构是否被动过，查一下快照里的 update_time 就能发现线索。

三、存储成本监控实战

存储成本是云数据库账单里最扎眼的一项。很多时候我们都不知道空间到底被谁吃掉了。用元数据表可以把账算清楚。

3.1 按库统计空间占用

data_length 表示数据部分占用的字节数，index_length 表示索引部分占用的字节数。两者相加再除以 1024 的幂，就能得到约等于的 KB、MB、GB。

-- 按数据库汇总统计空间占用，并按总大小降序排列
SELECT
    TABLE_SCHEMA AS '数据库',
    ROUND(SUM(DATA_LENGTH) / 1024 / 1024, 2)         AS '数据大小(MB)',
    ROUND(SUM(INDEX_LENGTH) / 1024 / 1024, 2)        AS '索引大小(MB)',
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS '总大小(MB)',
    SUM(TABLE_ROWS)                                  AS '估算总行数'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
GROUP BY
    TABLE_SCHEMA
ORDER BY
    SUM(DATA_LENGTH + INDEX_LENGTH) DESC;

这样一跑，哪个库最“胖”一目了然。如果某个库占了几十个 GB，下一步就去查它下面的表。另外要注意，这个结果是整个实例的汇总，如果用的是共享数据库，还需要按租户字段拆分统计，不然可能把别人的成本也算到自己头上。

3.2 找出单表占用 TOP10

单个大表往往是成本大户，也可能是慢查询的根源。下面这个查询直接列出所有库中占用空间前 10 的表。

-- 展示存储空间最大的前10张表
SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME   AS '表名',
    TABLE_TYPE   AS '类型',
    ENGINE       AS '引擎',
    TABLE_ROWS   AS '估算行数',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS '总大小(MB)',
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS '索引大小(MB)'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY
    (DATA_LENGTH + INDEX_LENGTH) DESC   -- 按总字节数真实排序
LIMIT 10;                               -- 只要前十名

注意，TABLE_ROWS 是估算值，可能和实际行数有偏差。尤其是 InnoDB 引擎，它的统计信息是基于采样算出来的。想知道准确行数还是得跑 COUNT()，但 COUNT() 在大表上很慢，所以日常监控用估算值就够了。

3.3 判断哪些表可以清理

磁盘空间快满的时候，我们需要找出那些“占着茅坑不拉屎”的表。比如长期不更新、行数很少但体积很大的表，说明里面可能有大量碎片，或者存了历史数据。

-- 找出行数少但占用空间大的表，优先考虑清理或归档
SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME   AS '表名',
    TABLE_ROWS   AS '估算行数',
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS '索引大小(MB)',
    UPDATE_TIME  AS '最后更新时间'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND TABLE_ROWS < 10000          -- 行数小于1万，称为小表
    AND (DATA_LENGTH + INDEX_LENGTH) > 100 * 1024 * 1024  -- 但占用超过100MB
ORDER BY
    (DATA_LENGTH + INDEX_LENGTH) DESC;

这里有个小坑：TABLE_ROWS 和 UPDATE_TIME 对于某些引擎可能不准确，例如 MyISAM 的行数就是准确的，但 InnoDB 是估算值。所以在清理之前，一定要抽样核实一下。

3.4 模拟清理后的空间收益

清理表之前，先估算一下能释放多少空间。碎片整理和删除数据的效果不一样。如果只是为了回收碎片，可以使用 OPTIMIZE TABLE。但要注意，大表执行 OPTIMIZE 会锁表或者消耗大量 IO。我们可以先用元数据表查一遍，把候选表找出来，再分别估算。

-- 找出候选清理的表，并展示当前占用，便于决定是否做 OPTIMIZE TABLE
SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME   AS '表名',
    ENGINE       AS '引擎',
    TABLE_COLLATION AS '排序规则',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS '总大小(MB)',
    ROUND((DATA_FREE) / 1024 / 1024, 2) AS '空闲碎片(MB)',
    ROUND((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024, 2) AS '实际使用(MB)'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = 'your_db'        -- 替换成你的数据库名
    AND DATA_FREE > 1024 * 1024 * 50  -- 碎片超过50MB才考虑清理
ORDER BY DATA_FREE DESC;

DATA_FREE 表示该表中有多少“空闲空间”。对 InnoDB 来说，这个值和碎片、预留空间有关。执行 OPTIMIZE TABLE 之后，DATA_FREE 通常会明显下降。

在真正动手清理前，别忘了先备份。

3.5 用快照跟踪表增长趋势

如果你按照前面 2.4 节的思路建了快照表，那么就可以很方便地算出每天表增长了多大。

-- 对比今天的快照和昨天的快照，找出增长最快的表
SELECT
    t1.db_name     AS '数据库',
    t1.table_name  AS '表名',
    t0.total_mb    AS '昨天大小(MB)',
    t1.total_mb    AS '今天大小(MB)',
    ROUND(t1.total_mb - t0.total_mb, 2) AS '增长量(MB)'
FROM
    meta_snapshot t1
JOIN
    meta_snapshot t0
    ON t0.db_name = t1.db_name
   AND t0.table_name = t1.table_name
   AND DATE(t0.snapshot_time) = DATE(t1.snapshot_time) - INTERVAL 1 DAY
WHERE
    DATE(t1.snapshot_time) = CURRENT_DATE
ORDER BY
    (t1.total_mb - t0.total_mb) DESC
LIMIT 20;

这个查询假设快照表每天都在固定时间有且仅有一条记录。实际使用中，可能因为任务中断或者延迟，导致日期对不上。最简单的办法是每天只插入一次，并且在查询时使用 DISTINCT 或者窗口函数，保证取到每天最新一条。这里为了演示，我们先忽略这些细节。

四、相关机制的补充说明

4.1 统计信息的更新策略

INFORMATION_SCHEMA 中的数据其实来自 MySQL 内部的统计信息。InnoDB 的统计信息会通过采样或者手动 ANALYZE TABLE 来更新。如果长期不更新，TABLE_ROWS 可能和实际相差很大。所以审计和监控不能只看一次，最好定期采集快照。

相关机制：MySQL 8.0 引入了数据字典，很多信息改为从数据字典读取，但 INFORMATION_SCHEMA 仍然保持兼容。我们不需要理解太深，只要知道它是一面“镜子”就行。

4.2 权限与安全边界

查询 INFORMATION_SCHEMA 一般不需要特殊权限，任何能连接数据库的用户都可以看。但要注意，它可能暴露库名、表名、字段信息。有些团队会屏蔽普通账号访问 information_schema，但对管理员来说，这是日常工具。

另外，不要在生产环境频繁对 INFORMATION_SCHEMA 做全表扫描，尤其是实例里表数量特别多的情况。虽然它看起来像内存表，实际上 TABLES 表是临时生成的，大量查询也可能带来一定开销。建议错峰执行，或者把结果写入监控表。

五、应用场景、优缺点与注意事项

5.1 典型应用场景

上线前的数据治理巡检：检查所有表是否有主键、字符集是否统一、字段类型是否合理。
存储成本分摊：统计每个业务线数据库的容量，用于内部成本核算。
数据库性能调优前置分析：找出大表、大索引，再结合慢查询日志做优化。
数据合规审计：审计表结构变更记录，追踪敏感字段的分布。
容量规划：通过快照数据预测未来一个月磁盘是否够用。

5.2 技术优点

无侵入性，不改变业务逻辑，只读查询。
覆盖面广，一次可以查完整个实例。
实时性好，不需要额外部署 agent。
可以和定时任务结合，形成持续监控。
语言简单，DBA 和研发都能上手。

5.3 技术缺点

统计信息不准，特别是 InnoDB 的行数是估算值。
大实例下查询可能较慢，因为要扫描所有表的元数据。
无法追溯历史，只有当前状态，结构变更记录不会被完整保留。
只能看到 MySQL 层面的信息，看不到文件系统级别的真实占用。
有些云数据库会屏蔽部分视图，导致查询结果不完整。

5.4 注意事项

不要完全相信 TABLE_ROWS，做容量规划时最好结合 COUNT(*) 抽样。
删除数据后如果空间没有释放，要检查表是否有分区、是否有大事务、是否启用了独立表空间。
使用 ALTER TABLE 或 OPTIMIZE TABLE 时要注意锁和主从延迟，最好在低峰期操作。
定期把元数据快照保存下来，这样就能看到趋势，比如表增长是线性还是爆炸式。
如果用的是云数据库，有些厂商会过滤掉部分系统信息，要以实际返回结果为准。
快照表本身也会占用空间，记得定期清理，只保留近 90 天即可。

六、总结

INFORMATION_SCHEMA 不是那种天天用的“热知识”，但它是数据治理的一把钥匙。通过几个简单的 SQL 查询，我们就能定位到哪些库表在浪费空间，哪些表没有主键隐患，哪些索引可能多余。审计和成本监控不必依赖昂贵的外部工具，先用好数据库自带的元数据表，就能解决大部分问题。

数据治理的很多工作本质上是“看见”。看见了异常，才能治理；看不见，就只能靠运气。希望这里的方法能让你少刷几张监控面板，多干一些真正有价值的事。

围绕“利用INFORMATION_SCHEMA元数据表进行数据治理：审计查询与存储成本监控”参与讨论

0 条

利用INFORMATION_SCHEMA元数据表进行数据治理：审计查询与存储成本监控

一、为什么数据治理要盯上元数据表

1.1 元数据表是数据库的“户口本”

1.2 什么时候会用到它

二、利用INFORMATION_SCHEMA做审计查询

2.1 查表结构变更痕迹

2.2 找出没有主键的表

2.3 检查索引是否冗余

2.4 把审计结果存成快照

三、存储成本监控实战

3.1 按库统计空间占用

3.2 找出单表占用 TOP10

3.3 判断哪些表可以清理

3.4 模拟清理后的空间收益

3.5 用快照跟踪表增长趋势

四、相关机制的补充说明

4.1 统计信息的更新策略

4.2 权限与安全边界

五、应用场景、优缺点与注意事项

5.1 典型应用场景

5.2 技术优点

5.3 技术缺点

5.4 注意事项

六、总结

评论

关联文章

AI助手

一、为什么数据治理要盯上元数据表

1.1 元数据表是数据库的“户口本”

1.2 什么时候会用到它

二、利用INFORMATION_SCHEMA做审计查询

2.1 查表结构变更痕迹

2.2 找出没有主键的表

2.3 检查索引是否冗余

2.4 把审计结果存成快照

三、存储成本监控实战

3.1 按库统计空间占用

3.2 找出单表占用 TOP10

3.3 判断哪些表可以清理

3.4 模拟清理后的空间收益

3.5 用快照跟踪表增长趋势

四、相关机制的补充说明

4.1 统计信息的更新策略

4.2 权限与安全边界

五、应用场景、优缺点与注意事项

5.1 典型应用场景

5.2 技术优点

5.3 技术缺点

5.4 注意事项

六、总结

同分类面试题

关联文章

AI助手