SQL元数据就是描述数据库中数据的数据，就像数据的“户口本”，记录着数据的结构、属性和关系等信息，帮助我们理解和管理数据库，分类如下：

• 数据结构元数据：描述数据库对象的结构，如表、列、索引的名称、数据类型、长度等。例如表中某列名为“姓名”，数据类型为文本，长度20，这些都是结构元数据。
• 数据关系元数据：反映数据对象之间的关联，如表与表之间的外键约束、视图的定义等。比如“订单表”通过外键关联“用户表”的用户ID，这种关联关系就是关系元数据。
• 数据约束元数据：定义数据的规则和限制，如主键、唯一约束、非空约束等。像“用户ID”设为主键，确保数据不重复，这属于约束元数据。
• 数据统计元数据：记录数据的统计信息，如表的行数、列的最大值/最小值、索引的使用频率等，用于优化查询性能。

SQL元数据操作常见方法有：用DESCRIBE或SHOW COLUMNS查看表结构；SHOW TABLES或SHOW TABLE STATUS获取表信息；INFORMATION_SCHEMA系统视图查询元数据；EXPLAIN分析查询执行计划；存储过程和函数操作元数据；SHOW CREATE TABLE查看建表语句。这些方法助于我们了解数据库结构。

下面简单介绍几种常见的SQL元数据操作方法，仅供参考：

一、通过information_schema获取元数据

1. 查看所有数据库信息

语法结构：

SELECT * FROM information_schema.SCHEMATA;

语法元素：

• SCHEMATA：系统视图，存储数据库元数据
• 参数：可通过WHERE条件过滤（如SCHEMA_NAME = 'db_name'）

返回值：

字段名	说明
CATALOG_NAME	目录名（总是def）
SCHEMA_NAME	数据库名称
DEFAULT_CHARACTER_SET_NAME	默认字符集
DEFAULT_COLLATION_NAME	默认排序规则
SQL_PATH	路径（通常为NULL）

应用示例：

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = 'test_db';

模拟输出：

SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME
test_db	utf8mb4

2. 查看指定数据库的表信息

语法结构：

SELECT * FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database_name';

语法元素：

• TABLES：系统视图，存储表元数据
• 关键参数：TABLE_SCHEMA（指定数据库名）

返回值：

字段名	说明
TABLE_NAME	表名称
ENGINE	存储引擎（InnoDB/MyISAM等）
TABLE_ROWS	行数（InnoDB为估算值）
AVG_ROW_LENGTH	平均行长度（字节）
DATA_LENGTH	数据存储空间（字节）
INDEX_LENGTH	索引存储空间（字节）
TABLE_COLLATION	表的排序规则

应用示例：

SELECT TABLE_NAME, ENGINE, TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'test_db';

模拟输出：

TABLE_NAME	ENGINE	TABLE_ROWS
user	InnoDB	1000
product	InnoDB	5000

3. 查看表中的列信息

语法结构：

SELECT * FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'db_name' 
  AND TABLE_NAME = 'table_name';

语法元素：

• COLUMNS：系统视图，存储列元数据
• 必选参数：TABLE_SCHEMA和TABLE_NAME

返回值：

字段名	说明
COLUMN_NAME	列名
DATA_TYPE	数据类型（int/varchar等）
CHARACTER_MAXIMUM_LENGTH	字符最大长度
NUMERIC_PRECISION	数字精度
IS_NULLABLE	是否允许NULL值
COLUMN_KEY	索引类型（PRI/UNI/MUL）
COLUMN_DEFAULT	默认值
EXTRA	额外信息（如auto_increment）

应用示例：

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE 
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'test_db' 
  AND TABLE_NAME = 'user';

模拟输出：

COLUMN_NAME	DATA_TYPE	IS_NULLABLE
id	int	NO
name	varchar	YES

4. 查看表的索引信息

语法结构：

SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'db_name' 
  AND TABLE_NAME = 'table_name';

语法元素：

• STATISTICS：系统视图，存储索引元数据
• 必选参数：TABLE_SCHEMA和TABLE_NAME

返回值：

字段名	说明
INDEX_NAME	索引名称
NON_UNIQUE	是否唯一（0=唯一，1=非唯一）
SEQ_IN_INDEX	索引中列的顺序（从1开始）
COLUMN_NAME	索引列名
CARDINALITY	唯一值数量估算（索引选择性）
INDEX_TYPE	索引类型（BTREE/FULLTEXT等）

应用示例：

SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE 
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'test_db' 
  AND TABLE_NAME = 'user';

模拟输出：

INDEX_NAME	COLUMN_NAME	NON_UNIQUE
PRIMARY	id	0
idx_name	name	1

5. 查询表大小

语法结构：

SELECT
  table_name AS 'Table',
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'database_name';

语法元素：

• DATA_LENGTH：数据存储空间
• INDEX_LENGTH：索引存储空间
• 单位转换：/1024/1024（字节→MB）

应用示例：

SELECT table_name, 
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = 'test_db';

模拟输出：

table_name	size_mb
user	0.02
product	0.05

二、使用SHOW语句获取元数据

1. 查看所有数据库

语法结构：

SHOW DATABASES;

返回值：

• 单列表格：所有数据库名称列表
• 系统库：information_schema/mysql/performance_schema

应用示例：

SHOW DATABASES;

模拟输出：

Database
information_schema
mysql
test_db

2. 查看数据库创建语句

语法结构：

SHOW CREATE DATABASE db_name;

返回值：

• 包含字符集和排序规则的完整建库语句
• 关键信息：DEFAULT CHARACTER SET

应用示例：

SHOW CREATE DATABASE test_db;

模拟输出：

Database	Create Database
test_db	CREATE DATABASE test_db /*!40100 DEFAULT CHARACTER SET utf8mb4 */

3. 查看表结构信息

语法结构：

-- 查看当前库表
SHOW TABLES;

-- 查看指定库表
SHOW TABLES FROM db_name;

-- 查看表详情
SHOW TABLE STATUS FROM db_name;

返回值差异：

命令	返回内容
SHOW TABLES	单纯表名列表
SHOW TABLE STATUS	详细表信息（行数/大小等）

应用示例：

SHOW TABLE STATUS FROM test_db;

模拟输出：

Name	Engine	Rows	Avg_row_length	Data_length
user	InnoDB	1000	128	131072

4. 查看表定义

语法结构：

-- 表创建语句
SHOW CREATE TABLE tbl_name;

-- 列信息
SHOW COLUMNS FROM tbl_name;

-- 索引信息
SHOW INDEX FROM tbl_name;

核心返回值对比：

命令	关键信息
SHOW CREATE TABLE	完整建表语句（含索引/约束）
SHOW COLUMNS	列定义（类型/是否允许空等）
SHOW INDEX	索引详情（唯一性/列名等）

应用示例：

SHOW INDEX FROM user;

模拟输出：

Table	Key_name	Column_name	Non_unique
user	PRIMARY	id	0

三、通过DESCRIBE获取元数据

语法结构：

DESCRIBE tbl_name;  -- 完整写法
DESC tbl_name;      -- 简写

等价关系：

DESC user ≡ SHOW COLUMNS FROM user;

返回值：

字段名	说明
Field	列名
Type	数据类型
Null	是否允许NULL
Key	索引类型（PRI/UNI等）
Default	默认值
Extra	额外信息（auto_increment等）

应用示例：

DESC user;

模拟输出：

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment

四、性能优化及避坑指南

1. TABLE_ROWS不准确问题

• InnoDB的TABLE_ROWS基于统计采样估算
• 解决方案：

  -- 精确行数（消耗大）
  SELECT COUNT(*) FROM table;
  -- 定时更新统计
  ANALYZE TABLE table;
  

2. 元数据查询锁机制

• MySQL 5.5前：查询information_schema可能触发全局锁
• 规避方案：

  SET SESSION transaction_read_only = ON; -- 开启只读事务
  

3. 查询性能优化

   -- 低效（全量扫描）
   SHOW TABLE STATUS;
   
   -- 高效替代（精准过滤）
   SELECT * FROM information_schema.TABLES 
   WHERE TABLE_SCHEMA = 'db' 
     AND TABLE_NAME IN ('t1','t2');
   

4. 表大小精度问题

• InnoDB的DATA_LENGTH包含缓冲池未刷新数据
• 精确测量：

  du -sh /var/lib/mysql/db_name/*.ibd
  

五、SQL元数据操作面试题

1. Q：SHOW TABLE STATUS显示的行数为什么与SELECT COUNT(*)不同？
   A：InnoDB的MVCC机制导致行数具有多版本特性，TABLE_ROWS仅为估算值

2. Q：如何获取数据库中的所有外键约束？
   A：

   SELECT 
     TABLE_NAME, 
     CONSTRAINT_NAME,
     REFERENCED_TABLE_NAME
   FROM information_schema.KEY_COLUMN_USAGE
   WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
   

3. Q：如何检测未使用的索引？
   A：

   SELECT *
   FROM sys.schema_unused_indexes;  -- MySQL 5.6+
   

4. Q：为什么information_schema查询比SHOW慢？
   A：information_schema需解析视图结构，而SHOW直接读取缓存

5. Q：如何安全获取生产环境元数据？
   A：

   SET SESSION transaction_read_only = ON;
   START TRANSACTION;
   SHOW TABLES;
   COMMIT;
   

• 使用只读事务
• 避开业务高峰期
• 优先使用SHOW获取简单信息

6. Q：如何获取表的精确大小？
   A：

   -- InnoDB独立表空间
   SELECT file_name, allocated_size 
   FROM information_schema.INNODB_SYS_TABLESPACES 
   WHERE NAME LIKE 'db/table';
   

7. Q：如何查看活跃的事务锁？
   A：

   SELECT * FROM information_schema.INNODB_TRX;  -- 活动事务
   SELECT * FROM performance_schema.data_locks;  -- 持有锁
   

实践建议：

1. 交互操作 → SHOW/DESC
2. 程序调用 → information_schema
3. 性能敏感 → 避免全量扫描
4. 数据精度 → 区分估算值与精确值
5. 生产环境 → 使用只读事务

阅读原文：原文链接