元数据概述

元数据是描述数据的数据，在 MySQL 中，元数据包含了数据库、表、列、索引、约束等结构信息。MySQL 提供了多种方式来查询和获取这些元数据信息。

📊 INFORMATION_SCHEMA

标准的元数据视图，提供数据库结构信息

🔧 SHOW 语句

MySQL 特有的元数据查询命令

📈 PERFORMANCE_SCHEMA

性能监控和诊断信息

⚙️ SYS 模式

简化的性能监控视图

元数据的用途：数据库结构分析和文档生成自动化脚本和工具开发数据库迁移和同步性能监控和优化安全审计和权限管理数据字典维护

INFORMATION_SCHEMA 数据库

查看所有元数据表

-- 查看 INFORMATION_SCHEMA 中的所有表
SELECT TABLE_NAME, TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
ORDER BY TABLE_NAME;

-- 查看常用的元数据表
SELECT TABLE_NAME, TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
  AND TABLE_NAME IN (
    'SCHEMATA', 'TABLES', 'COLUMNS', 'STATISTICS', 
    'KEY_COLUMN_USAGE', 'TABLE_CONSTRAINTS', 'VIEWS'
  )
ORDER BY TABLE_NAME;

数据库信息查询

-- 查看所有数据库
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 ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY SCHEMA_NAME;

-- 查看当前数据库信息
SELECT 
    SCHEMA_NAME as current_database,
    DEFAULT_CHARACTER_SET_NAME as charset,
    DEFAULT_COLLATION_NAME as collation
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

-- 统计每个数据库的表数量
SELECT 
    TABLE_SCHEMA as database_name,
    COUNT(*) as table_count,
    SUM(CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 1 ELSE 0 END) as base_tables,
    SUM(CASE WHEN TABLE_TYPE = 'VIEW' THEN 1 ELSE 0 END) as views
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
GROUP BY TABLE_SCHEMA
ORDER BY table_count DESC;

表信息查询

-- 查看当前数据库的所有表
SELECT 
    TABLE_NAME as table_name,
    TABLE_TYPE as type,
    ENGINE as storage_engine,
    TABLE_ROWS as estimated_rows,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as size_mb,
    TABLE_COMMENT as comment,
    CREATE_TIME as created_at,
    UPDATE_TIME as updated_at
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY size_mb DESC;

-- 查看表的详细存储信息
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) as data_size_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) as index_size_mb,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as total_size_mb,
    ROUND(INDEX_LENGTH / DATA_LENGTH * 100, 2) as index_ratio_percent
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
  AND DATA_LENGTH > 0
ORDER BY total_size_mb DESC;

-- 查找大表（超过指定大小）
SELECT 
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as size_mb,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND (DATA_LENGTH + INDEX_LENGTH) > 10 * 1024 * 1024  -- 大于10MB
ORDER BY size_mb DESC;

列信息查询

-- 查看表的所有列信息
SELECT 
    COLUMN_NAME as column_name,
    DATA_TYPE as data_type,
    COLUMN_TYPE as full_type,
    IS_NULLABLE as nullable,
    COLUMN_DEFAULT as default_value,
    EXTRA as extra_info,
    COLUMN_COMMENT as comment
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'employees'  -- 替换为实际表名
ORDER BY ORDINAL_POSITION;

-- 查找所有包含特定列名的表
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLUMN_NAME LIKE '%email%'
ORDER BY TABLE_NAME, COLUMN_NAME;

-- 查找所有自增列
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND EXTRA LIKE '%auto_increment%'
ORDER BY TABLE_NAME;

-- 统计数据类型使用情况
SELECT 
    DATA_TYPE,
    COUNT(*) as usage_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()), 2) as percentage
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY DATA_TYPE
ORDER BY usage_count DESC;

索引和约束信息

索引信息查询

-- 查看表的所有索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX as position,
    NON_UNIQUE,
    INDEX_TYPE,
    COMMENT
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'employees'  -- 替换为实际表名
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- 查找复合索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COUNT(*) as column_count,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as columns
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY TABLE_NAME, INDEX_NAME
HAVING COUNT(*) > 1
ORDER BY TABLE_NAME, column_count DESC;

-- 查找没有索引的表
SELECT 
    t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.STATISTICS s 
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA 
    AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND s.TABLE_NAME IS NULL;

-- 统计索引使用情况
SELECT 
    TABLE_NAME,
    COUNT(DISTINCT INDEX_NAME) as index_count,
    SUM(CASE WHEN NON_UNIQUE = 0 THEN 1 ELSE 0 END) as unique_indexes,
    SUM(CASE WHEN NON_UNIQUE = 1 THEN 1 ELSE 0 END) as non_unique_indexes
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
GROUP BY TABLE_NAME
ORDER BY index_count DESC;

约束信息查询

-- 查看所有约束
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    CONSTRAINT_TYPE,
    ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, CONSTRAINT_TYPE;

-- 查看主键信息
SELECT 
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    kcu.COLUMN_NAME,
    kcu.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
WHERE tc.TABLE_SCHEMA = DATABASE()
  AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY tc.TABLE_NAME, kcu.ORDINAL_POSITION;

-- 查看外键关系
SELECT 
    kcu.TABLE_NAME as child_table,
    kcu.COLUMN_NAME as child_column,
    kcu.REFERENCED_TABLE_NAME as parent_table,
    kcu.REFERENCED_COLUMN_NAME as parent_column,
    rc.CONSTRAINT_NAME,
    rc.UPDATE_RULE,
    rc.DELETE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    AND kcu.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
WHERE kcu.TABLE_SCHEMA = DATABASE()
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.TABLE_NAME;

-- 查看唯一约束
SELECT 
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    GROUP_CONCAT(kcu.COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION) as columns
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
WHERE tc.TABLE_SCHEMA = DATABASE()
  AND tc.CONSTRAINT_TYPE = 'UNIQUE'
GROUP BY tc.TABLE_NAME, tc.CONSTRAINT_NAME
ORDER BY tc.TABLE_NAME;

SHOW 语句

基本 SHOW 语句

-- 显示所有数据库
SHOW DATABASES;

-- 显示当前数据库的所有表
SHOW TABLES;

-- 显示表结构
SHOW COLUMNS FROM employees;
-- 或者使用简写
DESC employees;
DESCRIBE employees;

-- 显示表的创建语句
SHOW CREATE TABLE employees;

-- 显示表的索引
SHOW INDEX FROM employees;

-- 显示表状态
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 'employees';

-- 显示所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';

高级 SHOW 语句

-- 显示服务器状态
SHOW STATUS;
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Slow_queries';

-- 显示系统变量
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE 'max_connections';

-- 显示进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 显示存储引擎
SHOW ENGINES;

-- 显示字符集
SHOW CHARACTER SET;
SHOW COLLATION;

-- 显示权限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

-- 显示错误和警告
SHOW ERRORS;
SHOW WARNINGS;

-- 显示二进制日志
SHOW BINARY LOGS;
SHOW MASTER STATUS;

条件过滤的 SHOW 语句

-- 使用 LIKE 过滤
SHOW TABLES LIKE 'emp%';
SHOW VARIABLES LIKE 'innodb_buffer%';
SHOW STATUS LIKE 'Com_select';

-- 使用 WHERE 子句（MySQL 5.7+）
SHOW COLUMNS FROM employees WHERE Field LIKE '%name%';
SHOW TABLE STATUS WHERE Engine = 'InnoDB';

-- 显示特定数据库的表
SHOW TABLES FROM mysql;
SHOW TABLES IN information_schema LIKE 'TABLE%';

视图和存储过程元数据

视图信息查询

-- 查看所有视图
SELECT 
    TABLE_NAME as view_name,
    VIEW_DEFINITION as definition,
    CHECK_OPTION,
    IS_UPDATABLE,
    DEFINER,
    SECURITY_TYPE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

-- 查看视图的列信息
SELECT 
    TABLE_NAME as view_name,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN (
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = DATABASE()
  )
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- 创建示例视图用于演示
CREATE VIEW employee_summary AS
SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary
FROM employees
WHERE status = 'active'
GROUP BY department;

-- 查看视图定义
SHOW CREATE VIEW employee_summary;

存储过程和函数信息

-- 查看所有存储过程和函数
SELECT 
    ROUTINE_NAME as name,
    ROUTINE_TYPE as type,
    DATA_TYPE as return_type,
    ROUTINE_DEFINITION as definition,
    DEFINER,
    CREATED,
    LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE();

-- 查看存储过程参数
SELECT 
    SPECIFIC_NAME as routine_name,
    PARAMETER_NAME,
    PARAMETER_MODE,
    DATA_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = DATABASE()
ORDER BY SPECIFIC_NAME, ORDINAL_POSITION;

-- 显示存储过程
SHOW PROCEDURE STATUS WHERE Db = DATABASE();
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- 创建示例存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department = dept_name AND status = 'active'
    ORDER BY salary DESC;
END //
DELIMITER ;

-- 查看存储过程定义
SHOW CREATE PROCEDURE GetEmployeesByDepartment;

性能相关元数据

PERFORMANCE_SCHEMA 查询

-- 查看表的 I/O 统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = DATABASE()
ORDER BY (COUNT_READ + COUNT_WRITE) DESC;

-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = DATABASE()
  AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_FETCH DESC;

-- 查看未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = DATABASE()
  AND INDEX_NAME IS NOT NULL
  AND COUNT_STAR = 0
ORDER BY OBJECT_NAME;

-- 查看语句统计
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as execution_count,
    AVG_TIMER_WAIT / 1000000000 as avg_time_seconds,
    SUM_TIMER_WAIT / 1000000000 as total_time_seconds
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
ORDER BY COUNT_STAR DESC
LIMIT 10;

SYS 模式查询

-- 查看表和索引大小（需要 SYS 模式）
SELECT 
    table_schema,
    table_name,
    total_size,
    data_size,
    index_size
FROM sys.schema_table_statistics
WHERE table_schema = DATABASE()
ORDER BY total_size DESC;

-- 查看未使用的索引
SELECT 
    object_schema,
    object_name,
    index_name
FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE();

-- 查看冗余索引
SELECT 
    table_schema,
    table_name,
    redundant_index_name,
    redundant_index_columns,
    dominant_index_name,
    dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE();

-- 查看表访问统计
SELECT 
    table_schema,
    table_name,
    rows_fetched,
    rows_inserted,
    rows_updated,
    rows_deleted,
    io_read_requests,
    io_write_requests
FROM sys.schema_table_statistics_with_buffer
WHERE table_schema = DATABASE()
ORDER BY (rows_fetched + rows_inserted + rows_updated + rows_deleted) DESC;

实用的元数据查询

数据库健康检查

-- 数据库大小统计
SELECT 
    table_schema as database_name,
    COUNT(*) as table_count,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as total_size_mb,
    ROUND(SUM(data_length) / 1024 / 1024, 2) as data_size_mb,
    ROUND(SUM(index_length) / 1024 / 1024, 2) as index_size_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
GROUP BY table_schema
ORDER BY total_size_mb DESC;

-- 查找没有主键的表
SELECT 
    t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON t.TABLE_SCHEMA = tc.TABLE_SCHEMA
    AND t.TABLE_NAME = tc.TABLE_NAME
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND tc.CONSTRAINT_NAME IS NULL;

-- 查找可能需要优化的表（数据大但索引少）
SELECT 
    t.TABLE_NAME,
    t.TABLE_ROWS,
    ROUND((t.DATA_LENGTH + t.INDEX_LENGTH) / 1024 / 1024, 2) as size_mb,
    COUNT(DISTINCT s.INDEX_NAME) as index_count,
    ROUND(t.INDEX_LENGTH / t.DATA_LENGTH * 100, 2) as index_ratio_percent
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.STATISTICS s
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
    AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_ROWS > 1000
GROUP BY t.TABLE_NAME, t.TABLE_ROWS, t.DATA_LENGTH, t.INDEX_LENGTH
HAVING index_count < 3 OR index_ratio_percent < 10
ORDER BY size_mb DESC;

数据字典生成

-- 生成完整的数据字典
SELECT 
    t.TABLE_NAME as '表名',
    t.TABLE_COMMENT as '表说明',
    c.COLUMN_NAME as '字段名',
    c.COLUMN_TYPE as '数据类型',
    CASE WHEN c.IS_NULLABLE = 'YES' THEN '是' ELSE '否' END as '允许空值',
    IFNULL(c.COLUMN_DEFAULT, '') as '默认值',
    c.EXTRA as '额外信息',
    c.COLUMN_COMMENT as '字段说明',
    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN '是' ELSE '' END as '主键',
    CASE WHEN uk.COLUMN_NAME IS NOT NULL THEN '是' ELSE '' END as '唯一键'
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN (
    SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
        AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA
    AND c.TABLE_NAME = pk.TABLE_NAME
    AND c.COLUMN_NAME = pk.COLUMN_NAME
LEFT JOIN (
    SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
        AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
    WHERE tc.CONSTRAINT_TYPE = 'UNIQUE'
) uk ON c.TABLE_SCHEMA = uk.TABLE_SCHEMA
    AND c.TABLE_NAME = uk.TABLE_NAME
    AND c.COLUMN_NAME = uk.COLUMN_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;

依赖关系分析

-- 查看表之间的外键依赖关系
WITH RECURSIVE table_dependencies AS (
    -- 基础查询：没有依赖的表
    SELECT 
        TABLE_NAME,
        0 as level,
        CAST(TABLE_NAME AS CHAR(1000)) as path
    FROM INFORMATION_SCHEMA.TABLES t
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_TYPE = 'BASE TABLE'
      AND NOT EXISTS (
          SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
          WHERE kcu.TABLE_SCHEMA = DATABASE()
            AND kcu.TABLE_NAME = t.TABLE_NAME
            AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
      )
    
    UNION ALL
    
    -- 递归查询：有依赖的表
    SELECT 
        kcu.TABLE_NAME,
        td.level + 1,
        CONCAT(td.path, ' -> ', kcu.TABLE_NAME)
    FROM table_dependencies td
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        ON td.TABLE_NAME = kcu.REFERENCED_TABLE_NAME
        AND kcu.TABLE_SCHEMA = DATABASE()
    WHERE td.level < 10  -- 防止无限递归
      AND FIND_IN_SET(kcu.TABLE_NAME, REPLACE(td.path, ' -> ', ',')) = 0
)
SELECT 
    level as dependency_level,
    TABLE_NAME,
    path as dependency_path
FROM table_dependencies
ORDER BY level, TABLE_NAME;

-- 简化版本：直接查看外键关系
SELECT 
    kcu.TABLE_NAME as child_table,
    kcu.COLUMN_NAME as child_column,
    kcu.REFERENCED_TABLE_NAME as parent_table,
    kcu.REFERENCED_COLUMN_NAME as parent_column,
    'depends on' as relationship
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE kcu.TABLE_SCHEMA = DATABASE()
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.REFERENCED_TABLE_NAME, kcu.TABLE_NAME;

元数据的实际应用

自动化脚本示例

-- 生成备份脚本的元数据查询
SELECT 
    CONCAT(
        'mysqldump -u username -p ',
        TABLE_SCHEMA, ' ',
        TABLE_NAME, ' > ',
        TABLE_NAME, '_backup.sql;'
    ) as backup_command
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

-- 生成表结构比较脚本
SELECT 
    TABLE_NAME,
    CONCAT(
        'SELECT "', TABLE_NAME, '" as table_name, ',
        'COUNT(*) as column_count, ',
        'GROUP_CONCAT(CONCAT(COLUMN_NAME, ":", DATA_TYPE) ORDER BY ORDINAL_POSITION) as structure ',
        'FROM INFORMATION_SCHEMA.COLUMNS ',
        'WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = "', TABLE_NAME, '";'
    ) as structure_query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

-- 生成索引分析报告
SELECT 
    TABLE_NAME,
    CONCAT(
        'ANALYZE TABLE ', TABLE_NAME, '; ',
        'SHOW INDEX FROM ', TABLE_NAME, ';'
    ) as analysis_command
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

监控和报警查询

-- 监控表大小增长
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as current_size_mb,
    UPDATE_TIME as last_updated,
    CASE 
        WHEN (DATA_LENGTH + INDEX_LENGTH) > 100 * 1024 * 1024 THEN 'LARGE'
        WHEN (DATA_LENGTH + INDEX_LENGTH) > 50 * 1024 * 1024 THEN 'MEDIUM'
        ELSE 'SMALL'
    END as size_category
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 检查表的健康状况
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as size_mb,
    CASE 
        WHEN UPDATE_TIME IS NULL THEN 'NO_UPDATES'
        WHEN UPDATE_TIME < DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 'STALE'
        WHEN UPDATE_TIME < DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 'OLD'
        ELSE 'RECENT'
    END as update_status,
    CASE 
        WHEN TABLE_ROWS = 0 THEN 'EMPTY'
        WHEN TABLE_ROWS < 1000 THEN 'SMALL'
        WHEN TABLE_ROWS < 100000 THEN 'MEDIUM'
        ELSE 'LARGE'
    END as row_count_category
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY size_mb DESC;

最佳实践和注意事项

最佳实践：定期查询元数据以监控数据库健康状况使用元数据自动生成文档和报告通过元数据分析优化数据库性能建立基于元数据的监控和报警机制使用元数据验证数据库结构的一致性在数据迁移时利用元数据确保完整性

注意事项：INFORMATION_SCHEMA 查询可能对性能有影响某些元数据信息可能不是实时更新的不同 MySQL 版本的元数据结构可能有差异PERFORMANCE_SCHEMA 需要启用才能使用元数据查询权限可能受到限制大型数据库的元数据查询可能较慢

应用场景	推荐方法	注意事项
结构分析	INFORMATION_SCHEMA	查询可能较慢
快速查看	SHOW 语句	输出格式固定
性能监控	PERFORMANCE_SCHEMA	需要启用相关功能
自动化脚本	组合查询	考虑权限和性能
文档生成	格式化查询	定期更新

-- 清理示例对象
DROP VIEW IF EXISTS employee_summary;
DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;

SELECT 'Metadata tutorial completed. Example objects cleaned up.' AS message;