MySQL Information Schema完全指南（实战）

Information Schema 做什么用的?

Information Schema 是 MySQL 提供的一个系统数据库,包含了数据库服务器的所有元数据信息(metadata)。它就像是 MySQL 的”数据字典”或”目录”,记录了所有数据库、表、列、索引、权限等对象的详细信息。

核心特点

1. 只读数据库 : 所有表都是视图,不能直接修改
2. 符合 SQL 标准 : 基于 ANSI SQL 标准的 INFORMATION_SCHEMA
3. 跨平台统一 : 不同数据库的 INFORMATION_SCHEMA 结构类似
4. 实时数据 : 查询时实时从数据字典读取,不存储历史数据
5. 低性能开销 : 查询元数据的开销很小

Information Schema vs Performance Schema

特性	Information Schema	Performance Schema
用途	元数据查询(表结构、索引、权限等)	性能监控(SQL执行、锁等待、I/O等)
数据类型	静态元数据	动态运行时数据
数据来源	数据字典	运行时收集
典型查询	“这个库有哪些表?”	“哪些SQL最慢?”
使用频率	高(日常查询结构)	中(性能调优时)
性能开销	极小	可配置(默认小)

---

Information Schema 的核心表

Information Schema 包含约 60+ 张视图,按功能分为以下几类:

1. 数据库与表信息
   - SCHEMATA         : 数据库列表
   - TABLES           : 表列表
   - COLUMNS          : 列信息
   - STATISTICS       : 索引信息
   - TABLE_CONSTRAINTS: 约束信息

2. 权限与用户信息
   - USER_PRIVILEGES  : 用户权限
   - SCHEMA_PRIVILEGES: 数据库权限
   - TABLE_PRIVILEGES : 表权限
   - COLUMN_PRIVILEGES: 列权限

3. 存储程序信息
   - ROUTINES         : 存储过程和函数
   - TRIGGERS         : 触发器
   - EVENTS           : 事件调度器
   - PARAMETERS       : 存储过程参数

4. 字符集与排序规则
   - CHARACTER_SETS   : 字符集
   - COLLATIONS       : 排序规则
   - COLLATION_CHARACTER_SET_APPLICABILITY: 字符集与排序规则对应关系

5. InnoDB 专用信息
   - INNODB_*         : InnoDB 引擎的详细信息(30+张表)

6. 其他
   - FILES            : 表空间文件
   - PLUGINS          : 插件信息
   - ENGINES          : 存储引擎
   - PROCESSLIST      : 当前连接

---

一、数据库与表信息

1. SCHEMATA - 数据库信息

作用: 查询所有数据库的基本信息。

-- 查看所有数据库
SELECT
    schema_name AS database_name,
    default_character_set_name AS charset,
    default_collation_name AS collation
FROM information_schema.SCHEMATA
ORDER BY schema_name;

-- 查看非系统数据库
SELECT schema_name
FROM information_schema.SCHEMATA
WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY schema_name;

-- 统计每个数据库的表数量
SELECT
    s.schema_name,
    COUNT(t.table_name) AS table_count
FROM information_schema.SCHEMATA s
LEFT JOIN information_schema.TABLES t
    ON s.schema_name = t.table_schema
WHERE s.schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
GROUP BY s.schema_name
ORDER BY table_count DESC;

重要字段:

字段	说明
SCHEMA_NAME	数据库名称
DEFAULT_CHARACTER_SET_NAME	默认字符集
DEFAULT_COLLATION_NAME	默认排序规则
SQL_PATH	SQL 路径(通常为 NULL)

---

2. TABLES - 表信息

作用: 查询所有表的详细信息,包括表类型、存储引擎、行数、数据大小等。

-- 查看某个数据库的所有表，表的记录数等信息
SELECT
    table_name,
    engine,
    table_rows,
    ROUND(data_length/1024/1024, 2) AS data_mb,
    ROUND(index_length/1024/1024, 2) AS index_mb,
    ROUND((data_length + index_length)/1024/1024, 2) AS total_mb,
    table_collation,
    create_time,
    update_time,
    table_comment
FROM information_schema.TABLES
WHERE table_schema = 'your_database' -- 修改这里
ORDER BY (data_length + index_length) DESC;

-- 查找最大的表(TOP 10)
SELECT
    table_schema,
    table_name,
    ROUND((data_length + index_length)/1024/1024, 2) AS total_mb,
    table_rows
FROM information_schema.TABLES
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY (data_length + index_length) DESC
LIMIT 10;

-- 查找表行数最多的表
SELECT
    table_schema,
    table_name,
    table_rows,
    ROUND(data_length/1024/1024, 2) AS data_mb
FROM information_schema.TABLES
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY table_rows DESC
LIMIT 10;

-- 统计每个数据库的总大小
SELECT
    table_schema,
    COUNT(table_name) AS table_count,
    ROUND(SUM(data_length)/1024/1024, 2) AS data_mb,
    ROUND(SUM(index_length)/1024/1024, 2) AS index_mb,
    ROUND(SUM(data_length + index_length)/1024/1024, 2) AS total_mb
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;

-- 查找没有主键的表
SELECT
    t.table_schema,
    t.table_name,
    t.table_rows
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 NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND t.table_type = 'BASE TABLE'
  AND tc.constraint_name IS NULL
ORDER BY t.table_schema, t.table_name;

-- 查找使用 MyISAM 引擎的表(建议迁移到 InnoDB)
SELECT
    table_schema,
    table_name,
    engine,
    ROUND((data_length + index_length)/1024/1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY (data_length + index_length) DESC;

重要字段:

字段	说明
TABLE_SCHEMA	数据库名
TABLE_NAME	表名
TABLE_TYPE	表类型: BASE TABLE(普通表), VIEW(视图), SYSTEM VIEW
ENGINE	存储引擎: InnoDB, MyISAM, MEMORY 等
VERSION	表的版本号
ROW_FORMAT	行格式: Dynamic, Compact, Redundant, Compressed
TABLE_ROWS	表行数(估算值,InnoDB 不准确)
AVG_ROW_LENGTH	平均行长度(字节)
DATA_LENGTH	数据大小(字节)
MAX_DATA_LENGTH	最大数据大小
INDEX_LENGTH	索引大小(字节)
DATA_FREE	碎片空间(字节)
AUTO_INCREMENT	下一个自增值
CREATE_TIME	创建时间
UPDATE_TIME	最后更新时间
CHECK_TIME	最后检查时间(MyISAM)
TABLE_COLLATION	表的排序规则
CHECKSUM	校验和
CREATE_OPTIONS	创建选项
TABLE_COMMENT	表注释

注意事项:

1. TABLE_ROWS 不准确: InnoDB 的 TABLE_ROWS 是估算值,误差可能达到 40-50%

   -- 要获取准确行数,需要 COUNT(*)
   SELECT COUNT(*) FROM your_table;

2. DATA_FREE 表示碎片: 如果 DATA_FREE 很大,说明表有很多碎片,可以优化:

   OPTIMIZE TABLE your_table;

---

3. COLUMNS - 列信息

作用: 查询表的所有列的详细信息。

-- 查看某个表的所有列
SELECT
    column_name,
    ordinal_position AS position,
    column_default,
    is_nullable,
    data_type,
    column_type,
    character_maximum_length,
    numeric_precision,
    numeric_scale,
    column_key,
    extra,
    column_comment
FROM information_schema.COLUMNS
WHERE table_schema = 'your_database'
  AND table_name = 'your_table'
ORDER BY ordinal_position;

-- 查找所有包含特定列名的表
SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    column_comment
FROM information_schema.COLUMNS
WHERE column_name = 'user_id'
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY table_schema, table_name;

-- 查找所有 TEXT/BLOB 类型的列
SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    ROUND(CHARACTER_MAXIMUM_LENGTH/1024/1024, 2) AS max_mb
FROM information_schema.COLUMNS
WHERE data_type IN ('TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'BLOB', 'MEDIUMBLOB', 'LONGBLOB')
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY table_schema, table_name, ordinal_position;

-- 查找所有允许 NULL 的列
SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    is_nullable
FROM information_schema.COLUMNS
WHERE is_nullable = 'YES'
  AND table_schema = 'your_database'
  AND table_name = 'your_table'
ORDER BY ordinal_position;

-- 查找所有没有默认值的列
SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    is_nullable
FROM information_schema.COLUMNS
WHERE column_default IS NULL
  AND is_nullable = 'NO'
  AND extra NOT LIKE '%auto_increment%'
  AND table_schema = 'your_database'
ORDER BY table_name, ordinal_position;

-- 生成表的 CREATE TABLE 语句(简化版)
SELECT
    CONCAT('CREATE TABLE ', table_name, ' (') AS create_statement
UNION ALL
SELECT
    CONCAT('  ', column_name, ' ', column_type,
           IF(is_nullable = 'NO', ' NOT NULL', ''),
           IF(column_default IS NOT NULL, CONCAT(' DEFAULT ', QUOTE(column_default)), ''),
           IF(extra != '', CONCAT(' ', extra), ''),
           ',')
FROM information_schema.COLUMNS
WHERE table_schema = 'your_database'
  AND table_name = 'your_table'
ORDER BY ordinal_position;

重要字段:

字段	说明
TABLE_SCHEMA	数据库名
TABLE_NAME	表名
COLUMN_NAME	列名
ORDINAL_POSITION	列的位置(从1开始)
COLUMN_DEFAULT	默认值
IS_NULLABLE	是否允许 NULL: YES, NO
DATA_TYPE	数据类型: int, varchar, datetime 等
COLUMN_TYPE	完整类型: int(11), varchar(255) 等
CHARACTER_MAXIMUM_LENGTH	字符最大长度
CHARACTER_OCTET_LENGTH	字符字节长度
NUMERIC_PRECISION	数值精度
NUMERIC_SCALE	数值小数位数
DATETIME_PRECISION	日期时间精度
CHARACTER_SET_NAME	字符集
COLLATION_NAME	排序规则
COLUMN_KEY	键类型: PRI(主键), UNI(唯一), MUL(索引)
EXTRA	额外信息: auto_increment, on update CURRENT_TIMESTAMP
PRIVILEGES	权限
COLUMN_COMMENT	列注释
GENERATION_EXPRESSION	生成列表达式(MySQL 5.7+)

---

4. STATISTICS - 索引信息

作用: 查询表的所有索引和统计信息。

-- 查看某个表的所有索引
SELECT
    index_name,
    non_unique,
    seq_in_index AS position,
    column_name,
    collation,
    cardinality,
    sub_part,
    nullable,
    index_type,
    comment
FROM information_schema.STATISTICS
WHERE table_schema = 'your_database'
  AND table_name = 'your_table'
ORDER BY index_name, seq_in_index;

-- 查找复合索引(多列索引)
SELECT
    table_schema,
    table_name,
    index_name,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) AS index_columns,
    COUNT(*) AS column_count
FROM information_schema.STATISTICS
WHERE table_schema = 'your_database'
  AND non_unique = 1
GROUP BY table_schema, table_name, index_name
HAVING COUNT(*) > 1
ORDER BY table_name, index_name;

-- 查找唯一索引
SELECT
    table_schema,
    table_name,
    index_name,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.STATISTICS
WHERE non_unique = 0
  AND index_name != 'PRIMARY'
  AND table_schema = 'your_database'
GROUP BY table_schema, table_name, index_name
ORDER BY table_name, index_name;

-- 统计每个表的索引数量
SELECT
    table_schema,
    table_name,
    COUNT(DISTINCT index_name) AS index_count
FROM information_schema.STATISTICS
WHERE table_schema = 'your_database'
GROUP BY table_schema, table_name
ORDER BY index_count DESC;

-- 查找前缀索引(使用了 sub_part)
SELECT
    table_schema,
    table_name,
    index_name,
    column_name,
    sub_part
FROM information_schema.STATISTICS
WHERE sub_part IS NOT NULL
  AND table_schema = 'your_database'
ORDER BY table_name, index_name;

-- 查找可能的冗余索引
-- 例如: idx_a(a) 和 idx_ab(a,b),其中 idx_a 可能是冗余的
SELECT
    s1.table_schema,
    s1.table_name,
    s1.index_name AS index1,
    GROUP_CONCAT(s1.column_name ORDER BY s1.seq_in_index) AS index1_columns,
    s2.index_name AS index2,
    GROUP_CONCAT(s2.column_name ORDER BY s2.seq_in_index) AS index2_columns
FROM information_schema.STATISTICS s1
JOIN information_schema.STATISTICS s2
    ON s1.table_schema = s2.table_schema
    AND s1.table_name = s2.table_name
    AND s1.index_name < s2.index_name
    AND s1.seq_in_index = 1
    AND s2.seq_in_index = 1
    AND s1.column_name = s2.column_name
WHERE s1.table_schema = 'your_database'
  AND s1.index_name != 'PRIMARY'
  AND s2.index_name != 'PRIMARY'
GROUP BY s1.table_schema, s1.table_name, s1.index_name, s2.index_name;

重要字段:

字段	说明
TABLE_SCHEMA	数据库名
TABLE_NAME	表名
NON_UNIQUE	是否非唯一: 0(唯一), 1(非唯一)
INDEX_SCHEMA	索引所在数据库
INDEX_NAME	索引名称
SEQ_IN_INDEX	列在索引中的位置(从1开始)
COLUMN_NAME	列名
COLLATION	排序方式: A(升序), D(降序), NULL
CARDINALITY	基数(不重复值的估计数量)
SUB_PART	前缀索引的长度
PACKED	是否压缩
NULLABLE	列是否允许 NULL
INDEX_TYPE	索引类型: BTREE, HASH, FULLTEXT, SPATIAL
COMMENT	索引注释
INDEX_COMMENT	索引注释

实战案例: 索引优化

-- 1. 找出基数很低的索引(可能不高效)
SELECT
    table_schema,
    table_name,
    index_name,
    column_name,
    cardinality,
    (SELECT table_rows FROM information_schema.TABLES t
     WHERE t.table_schema = s.table_schema AND t.table_name = s.table_name) AS table_rows,
    ROUND(cardinality / NULLIF((SELECT table_rows FROM information_schema.TABLES t
                                 WHERE t.table_schema = s.table_schema AND t.table_name = s.table_name), 0) * 100, 2) AS selectivity
FROM information_schema.STATISTICS s
WHERE table_schema = 'your_database'
  AND seq_in_index = 1
  AND index_name != 'PRIMARY'
HAVING selectivity < 10  -- 选择性低于 10%
ORDER BY selectivity;

-- 2. 生成删除索引的 SQL
SELECT
    CONCAT('ALTER TABLE ', table_schema, '.', table_name,
           ' DROP INDEX ', index_name, ';') AS drop_index_sql
FROM information_schema.STATISTICS
WHERE table_schema = 'your_database'
  AND index_name = 'idx_to_drop';

---

5. TABLE_CONSTRAINTS - 约束信息

作用: 查询表的所有约束(主键、外键、唯一约束等)。

-- 查看某个表的所有约束
SELECT
    constraint_name,
    constraint_type,
    table_schema,
    table_name
FROM information_schema.TABLE_CONSTRAINTS
WHERE table_schema = 'your_database'
  AND table_name = 'your_table'
ORDER BY constraint_type;

-- 查找所有外键约束
SELECT
    constraint_name,
    table_schema,
    table_name,
    constraint_type
FROM information_schema.TABLE_CONSTRAINTS
WHERE constraint_type = 'FOREIGN KEY'
  AND table_schema = 'your_database'
ORDER BY table_name;

-- 查找所有唯一约束
SELECT
    constraint_name,
    table_schema,
    table_name
FROM information_schema.TABLE_CONSTRAINTS
WHERE constraint_type = 'UNIQUE'
  AND table_schema = 'your_database'
ORDER BY table_name;

约束类型:

CONSTRAINT_TYPE	说明
PRIMARY KEY	主键约束
FOREIGN KEY	外键约束
UNIQUE	唯一约束
CHECK	检查约束(MySQL 8.0.16+)

---

6. KEY_COLUMN_USAGE - 约束列信息

作用: 查询约束涉及的具体列,特别是外键关系。

-- 查看外键关系
SELECT
    kcu.constraint_name,
    kcu.table_schema,
    kcu.table_name,
    kcu.column_name,
    kcu.referenced_table_schema,
    kcu.referenced_table_name,
    kcu.referenced_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
    AND kcu.table_name = tc.table_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND kcu.table_schema = 'your_database'
ORDER BY kcu.table_name, kcu.constraint_name;

-- 查看主键列
SELECT
    table_schema,
    table_name,
    column_name,
    ordinal_position
FROM information_schema.KEY_COLUMN_USAGE
WHERE constraint_name = 'PRIMARY'
  AND table_schema = 'your_database'
ORDER BY table_name, ordinal_position;

-- 生成外键关系图
SELECT
    CONCAT(kcu.table_name, '.', kcu.column_name,
           ' -> ',
           kcu.referenced_table_name, '.', kcu.referenced_column_name) AS fk_relation
FROM information_schema.KEY_COLUMN_USAGE kcu
WHERE kcu.referenced_table_name IS NOT NULL
  AND kcu.table_schema = 'your_database'
ORDER BY kcu.table_name;

---

7. REFERENTIAL_CONSTRAINTS - 外键详细信息

作用: 查询外键的更新和删除规则。

-- 查看外键的级联规则
SELECT
    rc.constraint_name,
    rc.constraint_schema,
    kcu.table_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.REFERENTIAL_CONSTRAINTS rc
JOIN information_schema.KEY_COLUMN_USAGE kcu
    ON rc.constraint_name = kcu.constraint_name
    AND rc.constraint_schema = kcu.constraint_schema
WHERE rc.constraint_schema = 'your_database'
ORDER BY kcu.table_name;

级联规则:

规则	说明
CASCADE	级联操作(删除父记录时删除子记录)
SET NULL	设置为 NULL
RESTRICT	拒绝操作(默认)
NO ACTION	无操作(同 RESTRICT)
SET DEFAULT	设置为默认值(MySQL 不支持)

---

二、权限与用户信息

1. USER_PRIVILEGES - 用户全局权限

作用: 查询用户的全局权限。

-- 查看所有用户的全局权限
SELECT
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.USER_PRIVILEGES
ORDER BY grantee, privilege_type;

-- 查看某个用户的权限
SELECT
    privilege_type,
    is_grantable
FROM information_schema.USER_PRIVILEGES
WHERE grantee = "'username'@'hostname'"
ORDER BY privilege_type;

-- 查找拥有 SUPER 权限的用户
SELECT DISTINCT grantee
FROM information_schema.USER_PRIVILEGES
WHERE privilege_type = 'SUPER'
ORDER BY grantee;

2. SCHEMA_PRIVILEGES - 数据库权限

作用: 查询用户对特定数据库的权限。

-- 查看某个数据库的权限分配
SELECT
    grantee,
    table_schema,
    privilege_type,
    is_grantable
FROM information_schema.SCHEMA_PRIVILEGES
WHERE table_schema = 'your_database'
ORDER BY grantee, privilege_type;

3. TABLE_PRIVILEGES - 表权限

作用: 查询用户对特定表的权限。

-- 查看某个表的权限分配
SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type,
    is_grantable
FROM information_schema.TABLE_PRIVILEGES
WHERE table_schema = 'your_database'
  AND table_name = 'your_table'
ORDER BY grantee, privilege_type;

4. COLUMN_PRIVILEGES - 列权限

作用: 查询用户对特定列的权限。

-- 查看某个表的列权限
SELECT
    grantee,
    table_schema,
    table_name,
    column_name,
    privilege_type
FROM information_schema.COLUMN_PRIVILEGES
WHERE table_schema = 'your_database'
  AND table_name = 'your_table'
ORDER BY column_name, grantee;

---

三、存储程序信息

1. ROUTINES - 存储过程和函数

作用: 查询所有存储过程和函数的定义。

-- 查看所有存储过程和函数
SELECT
    routine_schema,
    routine_name,
    routine_type,
    data_type AS return_type,
    routine_definition,
    created,
    last_altered,
    routine_comment
FROM information_schema.ROUTINES
WHERE routine_schema = 'your_database'
ORDER BY routine_type, routine_name;

-- 只查看存储过程
SELECT
    routine_name,
    created,
    last_altered,
    routine_comment
FROM information_schema.ROUTINES
WHERE routine_schema = 'your_database'
  AND routine_type = 'PROCEDURE'
ORDER BY routine_name;

-- 只查看函数
SELECT
    routine_name,
    data_type AS return_type,
    routine_comment
FROM information_schema.ROUTINES
WHERE routine_schema = 'your_database'
  AND routine_type = 'FUNCTION'
ORDER BY routine_name;

-- 查找包含特定关键字的存储过程
SELECT
    routine_schema,
    routine_name,
    routine_type
FROM information_schema.ROUTINES
WHERE routine_definition LIKE '%UPDATE%'
  AND routine_schema = 'your_database'
ORDER BY routine_name;

-- 生成删除存储过程的 SQL
SELECT
    CONCAT('DROP ', routine_type, ' IF EXISTS ',
           routine_schema, '.', routine_name, ';') AS drop_sql
FROM information_schema.ROUTINES
WHERE routine_schema = 'your_database'
ORDER BY routine_type, routine_name;

2. PARAMETERS - 存储过程参数

作用: 查询存储过程和函数的参数信息。

-- 查看存储过程的参数
SELECT
    specific_schema,
    specific_name,
    ordinal_position,
    parameter_mode,
    parameter_name,
    data_type,
    character_maximum_length,
    numeric_precision,
    numeric_scale
FROM information_schema.PARAMETERS
WHERE specific_schema = 'your_database'
  AND specific_name = 'your_procedure'
ORDER BY ordinal_position;

-- 查看所有存储过程的参数列表
SELECT
    specific_name,
    GROUP_CONCAT(
        CONCAT(parameter_mode, ' ',
               parameter_name, ' ',
               data_type)
        ORDER BY ordinal_position
    ) AS parameters
FROM information_schema.PARAMETERS
WHERE specific_schema = 'your_database'
GROUP BY specific_name
ORDER BY specific_name;

参数模式:

PARAMETER_MODE	说明
IN	输入参数
OUT	输出参数
INOUT	输入输出参数

3. TRIGGERS - 触发器

作用: 查询所有触发器的定义。

-- 查看所有触发器
SELECT
    trigger_schema,
    trigger_name,
    event_manipulation,
    event_object_table,
    action_timing,
    action_statement,
    created
FROM information_schema.TRIGGERS
WHERE trigger_schema = 'your_database'
ORDER BY event_object_table, action_timing, event_manipulation;

-- 查看某个表的触发器
SELECT
    trigger_name,
    event_manipulation,
    action_timing,
    action_statement
FROM information_schema.TRIGGERS
WHERE trigger_schema = 'your_database'
  AND event_object_table = 'your_table'
ORDER BY action_timing, event_manipulation;

-- 生成删除触发器的 SQL
SELECT
    CONCAT('DROP TRIGGER IF EXISTS ',
           trigger_schema, '.', trigger_name, ';') AS drop_sql
FROM information_schema.TRIGGERS
WHERE trigger_schema = 'your_database';

触发器时机和事件:

ACTION_TIMING	说明
BEFORE	操作前触发
AFTER	操作后触发

EVENT_MANIPULATION	说明
INSERT	插入时触发
UPDATE	更新时触发
DELETE	删除时触发

4. EVENTS - 事件调度器

作用: 查询所有定时事件的信息。

-- 查看所有事件
SELECT
    event_schema,
    event_name,
    event_type,
    execute_at,
    interval_value,
    interval_field,
    status,
    on_completion,
    event_definition,
    created,
    last_altered,
    last_executed,
    event_comment
FROM information_schema.EVENTS
WHERE event_schema = 'your_database'
ORDER BY event_name;

-- 查看正在运行的事件
SELECT
    event_name,
    event_type,
    status,
    last_executed
FROM information_schema.EVENTS
WHERE event_schema = 'your_database'
  AND status = 'ENABLED'
ORDER BY last_executed DESC;

事件类型:

EVENT_TYPE	说明
ONE TIME	一次性事件
RECURRING	周期性事件

---

四、字符集与排序规则

1. CHARACTER_SETS - 字符集

作用: 查询所有支持的字符集。

-- 查看所有字符集
SELECT
    character_set_name,
    default_collate_name,
    description,
    maxlen
FROM information_schema.CHARACTER_SETS
ORDER BY character_set_name;

-- 查看常用字符集
SELECT
    character_set_name,
    default_collate_name,
    maxlen
FROM information_schema.CHARACTER_SETS
WHERE character_set_name IN ('utf8', 'utf8mb4', 'latin1', 'gbk')
ORDER BY character_set_name;

常用字符集:

字符集	说明	maxlen
utf8mb4	UTF-8 完整支持(推荐)	4
utf8	UTF-8(不支持表情符)	3
latin1	西欧字符集	1
gbk	简体中文	2

2. COLLATIONS - 排序规则

作用: 查询所有支持的排序规则。

-- 查看所有排序规则
SELECT
    collation_name,
    character_set_name,
    is_default,
    is_compiled,
    sortlen
FROM information_schema.COLLATIONS
ORDER BY character_set_name, collation_name;

-- 查看 utf8mb4 的排序规则
SELECT
    collation_name,
    is_default
FROM information_schema.COLLATIONS
WHERE character_set_name = 'utf8mb4'
ORDER BY collation_name;

常用排序规则:

排序规则	说明
utf8mb4_general_ci	通用排序,不区分大小写(性能好)
utf8mb4_unicode_ci	Unicode 标准排序,不区分大小写(准确)
utf8mb4_bin	二进制排序,区分大小写
utf8mb4_0900_ai_ci	MySQL 8.0 默认,支持 Unicode 9.0

---

五、存储引擎信息

1. ENGINES - 存储引擎

作用: 查询所有支持的存储引擎。

-- 查看所有存储引擎
SELECT
    engine,
    support,
    comment,
    transactions,
    xa,
    savepoints
FROM information_schema.ENGINES
ORDER BY engine;

-- 查看支持的存储引擎
SELECT engine, comment
FROM information_schema.ENGINES
WHERE support IN ('YES', 'DEFAULT')
ORDER BY engine;

支持级别:

SUPPORT	说明
YES	支持
DEFAULT	默认引擎
NO	不支持
DISABLED	已禁用

2. PLUGINS - 插件信息

作用: 查询所有已安装的插件。

-- 查看所有插件
SELECT
    plugin_name,
    plugin_version,
    plugin_status,
    plugin_type,
    plugin_library,
    plugin_description
FROM information_schema.PLUGINS
ORDER BY plugin_type, plugin_name;

-- 查看存储引擎插件
SELECT
    plugin_name,
    plugin_status
FROM information_schema.PLUGINS
WHERE plugin_type = 'STORAGE ENGINE'
ORDER BY plugin_name;

---

六、连接与进程信息

1. PROCESSLIST - 当前连接

作用: 查看当前所有连接和正在执行的 SQL。

-- 查看所有连接
SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM information_schema.PROCESSLIST
ORDER BY time DESC;

-- 查看正在执行的查询
SELECT
    id,
    user,
    db,
    time,
    state,
    LEFT(info, 100) AS query
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
  AND info IS NOT NULL
ORDER BY time DESC;

-- 查找长时间运行的查询(超过 60 秒)
SELECT
    id,
    user,
    host,
    db,
    time,
    state,
    info
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
  AND time > 60
ORDER BY time DESC;

-- 统计每个用户的连接数
SELECT
    user,
    COUNT(*) AS connection_count,
    SUM(time) AS total_time
FROM information_schema.PROCESSLIST
GROUP BY user
ORDER BY connection_count DESC;

-- 生成 KILL 语句
SELECT
    CONCAT('KILL ', id, ';') AS kill_sql
FROM information_schema.PROCESSLIST
WHERE time > 300
  AND command != 'Sleep'
  AND user != 'root';

重要字段:

字段	说明
ID	进程 ID(可用于 KILL)
USER	用户名
HOST	客户端主机
DB	当前数据库
COMMAND	命令类型: Query, Sleep, Connect 等
TIME	命令执行时间(秒)
STATE	执行状态
INFO	正在执行的 SQL 语句

注意: MySQL 8.0 推荐使用 performance_schema.threads 代替 information_schema.PROCESSLIST。

---

七、InnoDB 专用信息

1. INNODB_TABLES - InnoDB 表信息

-- 查看 InnoDB 表的详细信息
SELECT
    name,
    space,
    row_format,
    zip_page_size,
    space_type,
    n_cols,
    file_per_table
FROM information_schema.INNODB_TABLES
WHERE name LIKE 'your_database/%'
ORDER BY name;

2. INNODB_TABLESPACES - 表空间信息

-- 查看所有表空间
SELECT
    space,
    name,
    flag,
    row_format,
    page_size,
    zip_page_size,
    space_type,
    file_size,
    allocated_size
FROM information_schema.INNODB_TABLESPACES
WHERE name LIKE 'your_database/%'
ORDER BY file_size DESC;

3. INNODB_BUFFER_PAGE - Buffer Pool 页信息

-- 查看 Buffer Pool 中缓存最多的表
SELECT
    table_name,
    COUNT(*) AS page_count,
    ROUND(COUNT(*) * 16 / 1024, 2) AS cached_mb
FROM information_schema.INNODB_BUFFER_PAGE
WHERE table_name IS NOT NULL
GROUP BY table_name
ORDER BY page_count DESC
LIMIT 10;

4. INNODB_TRX - 当前事务

-- 查看当前活跃的事务
SELECT
    trx_id,
    trx_state,
    trx_started,
    trx_requested_lock_id,
    trx_wait_started,
    trx_weight,
    trx_mysql_thread_id,
    trx_query,
    trx_tables_locked,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

-- 查找长时间未提交的事务
SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_s,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started;

5. INNODB_LOCKS - 锁信息 (MySQL 5.7)

注意: MySQL 8.0 已弃用,使用 performance_schema.data_locks 代替。

-- MySQL 5.7: 查看当前锁
SELECT
    lock_id,
    lock_trx_id,
    lock_mode,
    lock_type,
    lock_table,
    lock_index,
    lock_data
FROM information_schema.INNODB_LOCKS;

6. INNODB_LOCK_WAITS - 锁等待 (MySQL 5.7)

注意: MySQL 8.0 已弃用,使用 performance_schema.data_lock_waits 代替。

-- MySQL 5.7: 查看锁等待关系
SELECT
    requesting_trx_id,
    requested_lock_id,
    blocking_trx_id,
    blocking_lock_id
FROM information_schema.INNODB_LOCK_WAITS;

---

八、文件与分区信息

1. FILES - 表空间文件

作用: 查询 InnoDB 表空间文件的物理存储信息。

-- 查看所有表空间文件
SELECT
    file_name,
    file_type,
    tablespace_name,
    engine,
    ROUND(total_extents * extent_size / 1024 / 1024, 2) AS total_mb,
    ROUND(data_free / 1024 / 1024, 2) AS free_mb,
    initial_size,
    maximum_size,
    autoextend_size
FROM information_schema.FILES
WHERE tablespace_name IS NOT NULL
ORDER BY total_extents * extent_size DESC;

-- 查看 InnoDB 系统表空间
SELECT
    file_name,
    ROUND(total_extents * extent_size / 1024 / 1024, 2) AS size_mb
FROM information_schema.FILES
WHERE tablespace_name = 'innodb_system';

2. PARTITIONS - 分区信息

作用: 查询表的分区详细信息。

-- 查看分区表的信息
SELECT
    table_schema,
    table_name,
    partition_name,
    partition_ordinal_position,
    partition_method,
    partition_expression,
    partition_description,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    create_time,
    update_time
FROM information_schema.PARTITIONS
WHERE table_schema = 'your_database'
  AND partition_name IS NOT NULL
ORDER BY table_name, partition_ordinal_position;

-- 查看每个分区的行数
SELECT
    table_name,
    partition_name,
    table_rows,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.PARTITIONS
WHERE table_schema = 'your_database'
  AND partition_name IS NOT NULL
ORDER BY table_name, partition_ordinal_position;

---

实战场景: 数据库诊断与管理

场景 1: 数据库健康检查

-- 1. 检查没有主键的表(必须修复)
SELECT t.table_schema, t.table_name, t.table_rows
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 NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND t.table_type = 'BASE TABLE'
  AND tc.constraint_name IS NULL;

-- 2. 检查使用 MyISAM 引擎的表(建议迁移到 InnoDB)
SELECT table_schema, table_name, engine
FROM information_schema.TABLES
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql', 'sys');

-- 3. 检查字符集不一致的列
SELECT
    t.table_schema,
    t.table_name,
    c.column_name,
    c.character_set_name,
    c.collation_name
FROM information_schema.COLUMNS c
JOIN information_schema.TABLES t
    ON c.table_schema = t.table_schema
    AND c.table_name = t.table_name
WHERE c.character_set_name IS NOT NULL
  AND (c.character_set_name != t.table_collation
       OR c.character_set_name NOT IN ('utf8mb4'))
  AND t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

-- 4. 检查有大量碎片的表
SELECT
    table_schema,
    table_name,
    ROUND(data_free / 1024 / 1024, 2) AS data_free_mb,
    ROUND(data_free / (data_length + index_length) * 100, 2) AS fragmentation_ratio
FROM information_schema.TABLES
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND data_free > 0
  AND (data_free / (data_length + index_length)) > 0.2
ORDER BY data_free DESC;

场景 2: 生成表结构文档

-- 生成 Markdown 格式的表结构文档
SELECT CONCAT('# 数据库: ', table_schema) AS doc
FROM information_schema.SCHEMATA
WHERE schema_name = 'your_database'

UNION ALL

SELECT CONCAT('## 表: ', table_name, '\n',
              '**说明**: ', IFNULL(table_comment, '无'), '\n',
              '**引擎**: ', engine, '\n',
              '**字符集**: ', table_collation, '\n',
              '**行数**: ', table_rows, '\n',
              '**大小**: ', ROUND((data_length + index_length)/1024/1024, 2), ' MB\n')
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
  AND table_type = 'BASE TABLE'

UNION ALL

SELECT CONCAT('### 字段列表\n',
              '| 字段名 | 类型 | 允许NULL | 默认值 | 说明 |\n',
              '|--------|------|----------|--------|------|\n')

UNION ALL

SELECT CONCAT('| ', column_name,
              ' | ', column_type,
              ' | ', is_nullable,
              ' | ', IFNULL(column_default, 'NULL'),
              ' | ', IFNULL(column_comment, ''),
              ' |')
FROM information_schema.COLUMNS
WHERE table_schema = 'your_database'
  AND table_name = 'your_table'
ORDER BY ordinal_position;

场景 3: 数据库迁移检查

-- 迁移前检查清单

-- 1. 检查表的总数和大小
SELECT
    COUNT(*) AS table_count,
    ROUND(SUM(data_length + index_length)/1024/1024/1024, 2) AS total_gb
FROM information_schema.TABLES
WHERE table_schema = 'your_database';

-- 2. 检查是否有外键约束
SELECT COUNT(*) AS fk_count
FROM information_schema.TABLE_CONSTRAINTS
WHERE constraint_type = 'FOREIGN KEY'
  AND table_schema = 'your_database';

-- 3. 检查是否有触发器
SELECT COUNT(*) AS trigger_count
FROM information_schema.TRIGGERS
WHERE trigger_schema = 'your_database';

-- 4. 检查是否有存储过程和函数
SELECT COUNT(*) AS routine_count
FROM information_schema.ROUTINES
WHERE routine_schema = 'your_database';

-- 5. 检查是否有事件
SELECT COUNT(*) AS event_count
FROM information_schema.EVENTS
WHERE event_schema = 'your_database';

-- 6. 检查是否有视图
SELECT COUNT(*) AS view_count
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
  AND table_type = 'VIEW';

场景 4: 生成数据库克隆脚本

-- 生成创建数据库的 SQL
SELECT CONCAT('CREATE DATABASE IF NOT EXISTS ', schema_name,
              ' CHARACTER SET ', default_character_set_name,
              ' COLLATE ', default_collation_name, ';')
FROM information_schema.SCHEMATA
WHERE schema_name = 'your_database';

-- 生成创建表的 SQL
SELECT CONCAT('-- Table: ', table_name, '\n',
              'SHOW CREATE TABLE ', table_schema, '.', table_name, ';')
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

场景 5: 找出相似的表结构

-- 查找列数相同的表(可能是分表)
SELECT
    c1.table_schema,
    c1.table_name AS table1,
    c2.table_name AS table2,
    c1.column_count
FROM (
    SELECT table_schema, table_name, COUNT(*) AS column_count
    FROM information_schema.COLUMNS
    WHERE table_schema = 'your_database'
    GROUP BY table_schema, table_name
) c1
JOIN (
    SELECT table_schema, table_name, COUNT(*) AS column_count
    FROM information_schema.COLUMNS
    WHERE table_schema = 'your_database'
    GROUP BY table_schema, table_name
) c2
ON c1.table_schema = c2.table_schema
AND c1.table_name < c2.table_name
AND c1.column_count = c2.column_count
ORDER BY c1.table_name, c2.table_name;

---

常见问题与注意事项

1. Information Schema 的查询性能

问题: 查询 Information Schema 有时很慢。

原因:

• Information Schema 的表是视图,查询时实时从数据字典读取
• 某些查询需要扫描大量元数据
• 某些查询会触发表的打开操作

优化建议:

-- ❌ 慢: 查询所有数据库的表
SELECT * FROM information_schema.TABLES;

-- ✅ 快: 指定数据库名
SELECT * FROM information_schema.TABLES WHERE table_schema = 'your_database';

-- ❌ 慢: 使用 LIKE
SELECT * FROM information_schema.TABLES WHERE table_name LIKE '%order%';

-- ✅ 快: 使用 = 或 IN
SELECT * FROM information_schema.TABLES WHERE table_name IN ('orders', 'order_items');

2. TABLE_ROWS 不准确

问题: TABLES.TABLE_ROWS 的值不准确。

原因:

• InnoDB 的 TABLE_ROWS 是估算值(基于统计信息)
• 估算误差可达 40-50%

解决方案:

-- 方法 1: 使用 COUNT(*) (准确但慢)
SELECT COUNT(*) FROM your_table;

-- 方法 2: 更新统计信息后再查询 (折中方案)
ANALYZE TABLE your_table;
SELECT table_rows FROM information_schema.TABLES
WHERE table_schema = 'your_database' AND table_name = 'your_table';

3. 权限问题

问题: 普通用户查询 Information Schema 时看不到所有表。

原因: 用户只能看到自己有权限的对象。

解决方案:

-- 授予查看所有表的权限(谨慎使用)
GRANT SELECT ON information_schema.* TO 'username'@'hostname';

-- 或者使用 root 用户查询

4. MySQL 8.0 的变化

MySQL 8.0 对 Information Schema 进行了重构:

改进:

• 查询性能显著提升(基于数据字典)
• 支持更多新特性(CTE、窗口函数等)

废弃的表:

• INNODB_LOCKS → 使用 performance_schema.data_locks
• INNODB_LOCK_WAITS → 使用 performance_schema.data_lock_waits

5. 查询 ROUTINES 返回乱码

问题: 查询 ROUTINES.ROUTINE_DEFINITION 时返回乱码。

原因: 字符集不匹配。

解决方案:

SET NAMES utf8mb4;
SELECT routine_name, routine_definition
FROM information_schema.ROUTINES
WHERE routine_schema = 'your_database';

---

Information Schema vs MySQL 8.0 数据字典

MySQL 8.0 的数据字典改进

MySQL 8.0 引入了事务性数据字典,替代了旧的文件系统元数据:

旧版本(MySQL 5.7):

元数据存储:
- .frm 文件: 表结构
- .ibd 文件: 表数据
- .MYD/.MYI 文件: MyISAM 数据和索引

新版本(MySQL 8.0):

统一数据字典:
- 所有元数据存储在 InnoDB 表中
- 原子性 DDL 操作
- Information Schema 性能显著提升

性能对比:

操作	MySQL 5.7	MySQL 8.0
查询 TABLES	慢(需要打开表)	快(直接读数据字典)
查询 COLUMNS	慢	快
查询 STATISTICS	慢	快

---

Information Schema 是 MySQL 管理和监控的重要工具,熟练掌握它可以帮助我们更好地理解和管理数据库结构。

---