📚 课程目录第3章关系型数据库元数据采集（上）

3、关系型数据库元数据采集（上）：MySQL 元数据采集实战（information_schema 详解）

做数据工程这些年，我打交道最多的数据库就是 MySQL。说实话，刚开始接触元数据采集时，我也是一头雾水——明明数据就在表里躺着，为什么还要多此一举去读「关于数据的数据」？直到有一次，我需要给一个几百张表的数仓做血缘分析，手动翻建表语句翻到怀疑人生。嗯，从那以后，我就彻底明白了 information_schema 的价值。

这一节，咱们就把它彻底吃透。

3.1 information_schema 是什么？

简单说，information_schema 是 MySQL 自带的「数据库的数据库」。它里面装着的，是所有其他数据库的元数据——表结构、列信息、索引、权限、字符集……你能想到的，它都有。

我个人习惯把它当成 MySQL 的「系统目录」来用。你想想看，平时我们用 SHOW DATABASES、SHOW TABLES 看到的那些信息，其实底层都是从 information_schema 里查出来的。

核心要点：information_schema 是只读的，你不能往里面 INSERT、UPDATE、DELETE。它是 MySQL 自动维护的视图集合，不是真正的物理表。

3.2 核心表与视图一览

information_schema 里有一百多个视图，但日常元数据采集用到的，其实就那么十几个。我按使用频率排了个序，你重点关注前 8 个就行。

视图名称	核心字段	采集用途
`SCHEMATA`	SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME	获取所有数据库列表
`TABLES`	TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS	获取表清单、存储引擎、行数估算
`COLUMNS`	TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE	获取字段定义、数据类型、是否可空
`STATISTICS`	TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX	获取索引信息、主键、唯一键
`KEY_COLUMN_USAGE`	CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME	获取外键关系、约束信息
`TABLE_CONSTRAINTS`	CONSTRAINT_TYPE, TABLE_SCHEMA, TABLE_NAME	区分 PRIMARY KEY、UNIQUE、FOREIGN KEY
`PARTITIONS`	TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION	获取分区表信息
`PROCESSLIST`	ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO	监控当前连接与正在执行的 SQL

我的小技巧：别死记硬背字段名。你只需要记住每个视图的「用途关键词」，比如查表用 TABLES，查字段用 COLUMNS。具体字段，写 SQL 时 DESC information_schema.TABLES 一下，全出来了。

3.3 实战：用 SQL 采集元数据

光说不练假把式。咱们直接上手写几个采集脚本，这些我在项目中反复用过，你拿去稍微改改就能用。

3.3.1 采集所有数据库列表

-- 排除系统库，只取业务库
SELECT SCHEMA_NAME AS database_name,
       DEFAULT_CHARACTER_SET_NAME AS charset,
       DEFAULT_COLLATION_NAME AS collation
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY SCHEMA_NAME;

为什么排除系统库？我曾经在一个生产环境上没加这个过滤，结果把 mysql 库里的权限表也当业务表采集了，下游数据同步直接炸了。嗯，血的教训。

3.3.2 采集表级元数据

SELECT TABLE_SCHEMA AS db_name,
       TABLE_NAME AS table_name,
       TABLE_TYPE AS table_type,  -- 'BASE TABLE' 或 'VIEW'
       ENGINE,
       TABLE_ROWS AS estimated_rows,
       AVG_ROW_LENGTH,
       DATA_LENGTH + INDEX_LENGTH AS total_size_bytes,
       CREATE_TIME,
       UPDATE_TIME,
       TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

注意：TABLE_ROWS 是估算值，不是精确行数。对于 InnoDB 表，这个值误差可能在 30%-50%。如果你需要精确行数，得用 COUNT(*)，但大表慎用。

3.3.3 采集字段级元数据

字段级采集是元数据血缘分析的基础。我个人习惯把 COLUMNS 和 KEY_COLUMN_USAGE 做一次 JOIN，一次性拿到字段定义和约束信息。

SELECT c.TABLE_SCHEMA,
       c.TABLE_NAME,
       c.COLUMN_NAME,
       c.ORDINAL_POSITION,
       c.COLUMN_DEFAULT,
       c.IS_NULLABLE,
       c.DATA_TYPE,
       c.CHARACTER_MAXIMUM_LENGTH,
       c.NUMERIC_PRECISION,
       c.NUMERIC_SCALE,
       c.COLUMN_TYPE,
       c.COLUMN_KEY,   -- PRI, UNI, MUL
       c.EXTRA,        -- auto_increment, on update CURRENT_TIMESTAMP
       k.REFERENCED_TABLE_NAME,
       k.REFERENCED_COLUMN_NAME
FROM information_schema.COLUMNS c
LEFT JOIN information_schema.KEY_COLUMN_USAGE k
  ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
 AND c.TABLE_NAME   = k.TABLE_NAME
 AND c.COLUMN_NAME  = k.COLUMN_NAME
 AND k.REFERENCED_TABLE_NAME IS NOT NULL
WHERE c.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION;

3.4 知识体系总览

下面这张图，是我自己梳理的 information_schema 元数据采集全景。你把它存下来，以后写采集脚本时对照着看，思路会清晰很多。

3.5 避坑指南与性能优化

information_schema 虽然好用，但用不好也会踩坑。我把自己踩过的几个坑列出来，你遇到了直接绕开。

别在大库上直接 SELECT *：information_schema 的底层实现是临时表，全表扫描会触发大量内部 I/O。我曾经在一个 2000+ 表的实例上跑 SELECT * FROM TABLES，直接让监控告警了。解决办法：只查你需要的字段，加上 WHERE TABLE_SCHEMA 过滤。
INFORMATION_SCHEMA 大小写敏感：MySQL 在 Linux 上默认区分大小写，Windows 和 macOS 不区分。为了保险，我统一用大写写视图名。
权限问题：普通用户只能看到自己有权限的库和表。如果你发现查出来的元数据不全，先检查用户权限，别急着怀疑 SQL 写错了。
采集频率别太高：元数据采集建议每天一次，或者每次表结构变更后触发。实时采集对 information_schema 的压力很大，尤其是 COLUMNS 视图。

我的经验：如果你需要频繁采集元数据，可以考虑把 information_schema 的数据先拉到一张中间表里，然后从中间表查询。这样既减轻了系统库的压力，又能做历史版本对比。

3.6 小结

information_schema 是 MySQL 元数据采集的基石。你只要掌握了 SCHEMATA、TABLES、COLUMNS 这三个核心视图，就能覆盖 80% 以上的采集场景。剩下的 STATISTICS、KEY_COLUMN_USAGE、PARTITIONS 这些，按需补充就行。