MySQL教程FG182-MySQL INFORMATION_SCHEMA常用表

内容简介：INFORMATION_SCHEMA包含大量的元数据表，本文重点介绍其中最常用的表及其在生产环境中的应用。通过学习这些常用表的使用方法，DBA可以高效地查询数据库结构、监控性能、排查问题。本文风哥教程参考MySQL官方文档，提供丰富的实战案例和命令示例。学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

1.1 INFORMATION_SCHEMA概述

INFORMATION_SCHEMA是MySQL内置的系统数据库，用于存储关于MySQL服务器的元数据信息。它包含了大量的表，这些表实际上是系统视图，提供了对数据库元数据的访问接口。

1.2 常用表分类

INFORMATION_SCHEMA常用表可以分为以下几类：

数据库结构表：存储数据库、表、列、索引等结构信息
性能监控表：存储服务器性能、状态等信息
锁和事务表：存储锁和事务相关信息
系统配置表：存储系统变量、存储引擎等信息

Part02-生产环境规划与建议

2.1 表选择策略

在生产环境中使用INFORMATION_SCHEMA表时，需要根据不同的需求选择合适的表：

数据库结构查询：使用SCHEMATA、TABLES、COLUMNS、STATISTICS等表
性能监控：使用GLOBAL_STATUS、GLOBAL_VARIABLES等表
锁和事务监控：使用INNODB_LOCKS、INNODB_LOCK_WAITS、INNODB_TRX等表
进程管理：使用PROCESSLIST表

2.2 查询优化建议

查询INFORMATION_SCHEMA表时的优化建议：

使用具体的过滤条件：始终指定TABLE_SCHEMA和TABLE_NAME等条件
限制返回列：只查询需要的列，减少数据传输量
使用LIMIT：当只需要部分结果时，使用LIMIT限制返回行数
避免在高峰期查询：INFORMATION_SCHEMA查询可能影响服务器性能

风哥提示：INFORMATION_SCHEMA表是动态生成的视图，查询性能与数据库大小和复杂度相关。在大型数据库中，应避免执行复杂的跨多个INFORMATION_SCHEMA表的连接查询。

Part03-生产环境项目实施方案

3.1 数据库结构表

数据库结构表用于查询数据库、表、列、索引等结构信息。

3.1.1 SCHEMATA表

SCHEMATA表存储数据库的基本信息。

3.1.2 TABLES表

TABLES表存储表的基本信息。

# 查询指定数据库的所有表
mysql> SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘fgedudb’;
+——————–+————+——–+————+————-+————–+
| TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH |
+——————–+————+——–+————+————-+————–+
| fgedu_users | BASE TABLE | InnoDB | 10000 | 8388608 | 2097152 |
| fgedu_orders | BASE TABLE | InnoDB | 50000 | 41943040 | 10485760 |
| fgedu_products | BASE TABLE | InnoDB | 20000 | 16777216 | 4194304 |
| fgedu_categories | BASE TABLE | InnoDB | 100 | 16384 | 8192 |
| fgedu_order_items | BASE TABLE | InnoDB | 100000 | 83886080 | 20971520 |
+——————–+————+——–+————+————-+————–+
5 rows in set (0.01 sec)

3.1.3 COLUMNS表

COLUMNS表存储列的详细信息。

3.1.4 STATISTICS表

STATISTICS表存储索引的详细信息。

3.2 性能监控表

性能监控表用于查询服务器性能和状态信息。

3.2.1 GLOBAL_STATUS表

GLOBAL_STATUS表存储服务器的全局状态变量。

# 计算QPS（每秒查询数）
mysql> SELECT
VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Uptime’) AS QPS
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = ‘Queries’;
+———+——————-+
| QPS | VARIABLE_SOURCE |
+———+——————-+
| 0.9259 | global |
+———+——————-+
1 row in set (0.01 sec)

3.2.2 GLOBAL_VARIABLES表

GLOBAL_VARIABLES表存储服务器的全局系统变量。

3.2.3 PROCESSLIST表

PROCESSLIST表存储当前连接到服务器的进程信息。

# 终止长时间运行的进程
mysql> KILL 128;
Query OK, 0 rows affected (0.00 sec)

3.3 锁和事务表

锁和事务表用于查询InnoDB引擎的锁和事务信息。

3.3.1 INNODB_TRX表

INNODB_TRX表存储当前运行的InnoDB事务信息。

# 查询当前运行的事务
mysql> SELECT trx_id, trx_state, trx_started, trx_requested_lock_id,
trx_wait_started, trx_weight, trx_mysql_thread_id
FROM information_schema.INNODB_TRX;
+——–+———–+———————+———————+———————+————+——————+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id |
+——–+———–+———————+———————+———————+————+——————+
| 123456 | RUNNING | 2026-04-02 20:00:00 | NULL | NULL | 2 | 129 |
| 123457 | LOCK WAIT | 2026-04-02 20:01:00 | 123456:1:4:5 | 2026-04-02 20:01:05 | 3 | 130 |
+——–+———–+———————+———————+———————+————+——————+
2 rows in set (0.01 sec)

3.3.2 INNODB_LOCKS表

INNODB_LOCKS表存储当前持有的InnoDB锁信息。

# 查询当前持有的锁
mysql> SELECT lock_id, lock_trx_id, lock_mode, lock_type, lock_table,
lock_index, lock_space, lock_page, lock_rec, lock_data
FROM information_schema.INNODB_LOCKS;
+—————-+————-+———–+———–+—————————+————+————+———–+———+—————-+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+—————-+————-+———–+———–+—————————+————+————+———–+———+—————-+
| 123456:1:4:5 | 123456 | X | RECORD | `fgedudb`.`fgedu_orders` | PRIMARY | 1 | 4 | 5 | 10000 |
| 123457:1:4:5 | 123457 | X | RECORD | `fgedudb`.`fgedu_orders` | PRIMARY | 1 | 4 | 5 | 10000 |
+—————-+————-+———–+———–+—————————+————+————+———–+———+—————-+
2 rows in set (0.01 sec)

3.3.3 INNODB_LOCK_WAITS表

INNODB_LOCK_WAITS表存储当前等待锁的信息。

# 查询锁等待关系
mysql> SELECT requesting_trx_id, requested_lock_id, blocking_trx_id, blocking_lock_id
FROM information_schema.INNODB_LOCK_WAITS;
+—————–+——————+—————–+——————+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+—————–+——————+—————–+——————+
| 123457 | 123457:1:4:5 | 123456 | 123456:1:4:5 |
+—————–+——————+—————–+——————+
1 row in set (0.01 sec)

# 结合INNODB_TRX和INNODB_LOCK_WAITS查询锁等待详情
mysql> SELECT
r.trx_id AS requesting_trx_id,
r.trx_mysql_thread_id AS requesting_thread_id,
r.trx_query AS requesting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread_id,
b.trx_query AS blocking_query
FROM
information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id
INNER JOIN information_schema.INNODB_TRX b ON w.blocking_trx_id = b.trx_id;
+—————–+——————–+——————————————+—————–+——————–+——————————————+
| requesting_trx_id | requesting_thread_id | requesting_query | blocking_trx_id | blocking_thread_id | blocking_query |
+—————–+——————–+——————————————+—————–+——————–+——————————————+
| 123457 | 130 | UPDATE fgedu_orders SET status = ‘paid’ WHERE id = 10000 | 123456 | 129 | UPDATE fgedu_orders SET total_amount = 500 WHERE id = 10000 |
+—————–+——————–+——————————————+—————–+——————–+——————————————+
1 row in set (0.01 sec)

3.4 系统配置表

系统配置表用于查询服务器的系统配置信息。

3.4.1 ENGINES表

ENGINES表存储存储引擎的信息。

# 查询所有存储引擎
mysql> SELECT ENGINE, SUPPORT, COMMENT, TRANSACTIONS, XA, SAVEPOINTS
FROM information_schema.ENGINES;
+——————–+———+—————————————————————-+————–+——+————+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+——————–+———+—————————————————————-+————–+——+————+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.01 sec)

# 查询支持事务的存储引擎
mysql> SELECT ENGINE, COMMENT FROM information_schema.ENGINES WHERE TRANSACTIONS = ‘YES’;
+——–+—————————————————————-+
| ENGINE | COMMENT |
+——–+—————————————————————-+
| InnoDB | Supports transactions, row-level locking, and foreign keys |
+——–+—————————————————————-+
1 row in set (0.01 sec)

3.4.2 PLUGINS表

PLUGINS表存储插件的信息。

Part04-生产案例与实战讲解

4.1 数据库结构审计

使用INFORMATION_SCHEMA表进行数据库结构审计的实战案例。

案例：审计数据库中没有主键的表
问题描述：需要找出数据库中没有主键的表，以提高数据完整性和查询性能
解决方法：使用INFORMATION_SCHEMA.TABLES和INFORMATION_SCHEMA.STATISTICS表查询没有主键的表

# 查询没有主键的表
SELECT
t.TABLE_SCHEMA,
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 AND
s.INDEX_NAME = ‘PRIMARY’
WHERE
t.TABLE_SCHEMA NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’, ‘sys’) AND
s.INDEX_NAME IS NULL;

# 为没有主键的表添加主键
mysql> ALTER TABLE fgedudb.fgedu_logs ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 100000 rows affected (0.10 sec)
Records: 100000 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE fgedudb.fgedu_temp ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

4.2 性能分析

使用INFORMATION_SCHEMA表进行性能分析的实战案例。

案例：分析数据库的查询性能
问题描述：需要分析数据库的查询性能，找出慢查询的原因
解决方法：使用GLOBAL_STATUS表查询查询类型的分布情况

# 查询各种查询类型的分布
SELECT
VARIABLE_NAME,
VARIABLE_VALUE,
ROUND((VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Com_select’ + Com_insert + Com_update + Com_delete)) * 100, 2) AS PERCENTAGE
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME IN (‘Com_select’, ‘Com_insert’, ‘Com_update’, ‘Com_delete’);

# 输出示例
+—————+—————-+————+
| VARIABLE_NAME | VARIABLE_VALUE | PERCENTAGE |
+—————+—————-+————+
| Com_delete | 1000 | 1.25 |
| Com_insert | 10000 | 12.50 |
| Com_select | 50000 | 62.50 |
| Com_update | 19000 | 23.75 |
+—————+—————-+————+

# 查询InnoDB缓冲池使用情况
SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM
information_schema.GLOBAL_STATUS
WHERE
VARIABLE_NAME LIKE ‘Innodb_buffer_pool%’;

# 计算缓冲池命中率
mysql> SELECT
ROUND((1 – (VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’))) * 100, 2) AS BUFFER_POOL_HIT_RATE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’;
+———————+——————-+
| BUFFER_POOL_HIT_RATE | VARIABLE_SOURCE |
+———————+——————-+
| 99.90 | global |
+———————+——————-+
1 row in set (0.01 sec)

4.3 锁问题排查

使用INFORMATION_SCHEMA表进行锁问题排查的实战案例。

案例：排查数据库中的死锁问题
问题描述：应用程序报告死锁错误，需要找出死锁的原因
解决方法：使用INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS表查询死锁信息

# 查询当前的锁等待情况
SELECT
r.trx_id AS requesting_trx_id,
r.trx_mysql_thread_id AS requesting_thread,
r.trx_query AS requesting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query,
b.trx_started AS blocking_started,
TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_duration_seconds
FROM
information_schema.INNODB_LOCK_WAITS w
JOIN
information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id
JOIN
information_schema.INNODB_TRX b ON w.blocking_trx_id = b.trx_id;

# 输出示例
+—————–+—————–+——————————————+—————–+—————–+——————————————+———————+————————+
| requesting_trx_id | requesting_thread | requesting_query | blocking_trx_id | blocking_thread | blocking_query | blocking_started | blocking_duration_seconds |
+—————–+—————–+——————————————+—————–+—————–+——————————————+———————+————————+
| 123457 | 130 | UPDATE fgedu_orders SET status = ‘paid’ WHERE id = 10000 | 123456 | 129 | UPDATE fgedu_orders SET total_amount = 500 WHERE id = 10000 | 2026-04-02 20:00:00 | 120 |
+—————–+—————–+——————————————+—————–+—————–+——————————————+———————+————————+

# 查看锁的详细信息
SELECT
l.lock_id,
l.lock_trx_id,
l.lock_mode,
l.lock_type,
l.lock_table,
l.lock_index,
l.lock_data,
t.trx_query
FROM
information_schema.INNODB_LOCKS l
JOIN
information_schema.INNODB_TRX t ON l.lock_trx_id = t.trx_id;

# 终止阻塞的事务
mysql> KILL 129;
Query OK, 0 rows affected (0.00 sec)

Part05-风哥经验总结与分享

5.1 最佳实践

合理选择表：根据不同的需求选择合适的INFORMATION_SCHEMA表，避免不必要的查询
优化查询条件：始终使用TABLE_SCHEMA和TABLE_NAME等条件过滤，减少查询范围
限制返回数据：只查询需要的列，使用LIMIT限制返回行数
定期更新统计信息：定期使用ANALYZE TABLE更新表统计信息，确保INFORMATION_SCHEMA中的统计数据准确
结合Performance Schema：将INFORMATION_SCHEMA与Performance Schema结合使用，可以获取更全面的性能信息
创建自定义视图：对于频繁使用的复杂查询，可以创建自定义视图简化查询
避免在高峰期查询：INFORMATION_SCHEMA查询可能影响服务器性能，应避免在业务高峰期执行

5.2 常见问题与解决方案

风哥提示：使用INFORMATION_SCHEMA表时，遇到性能问题是最常见的情况，需要通过优化查询条件和限制返回数据来解决。

问题现象	可能原因	解决方案
查询INFORMATION_SCHEMA表速度慢	查询的表数量过多，没有使用过滤条件	增加TABLE_SCHEMA和TABLE_NAME等过滤条件，减少查询范围
统计信息不准确	表统计信息过时，没有定期更新	使用ANALYZE TABLE定期更新表统计信息
查询结果与实际情况不符	缓存导致的结果不一致，或者统计信息不准确	刷新缓存，更新统计信息
在大型数据库中查询性能极差	数据库表数量过多，或者查询过于复杂	分批次查询，使用更具体的过滤条件，考虑使用缓存
无法查询到某些信息	用户权限不足，或者信息不存在	检查用户权限，确认信息是否存在

生产环境建议：建立INFORMATION_SCHEMA的定期查询和分析机制，用于数据库结构审计、性能监控和问题排查。同时，注意INFORMATION_SCHEMA查询的性能影响，避免在业务高峰期执行复杂的元数据查询。更多学习教程公众号风哥教程itpux_com

from MySQL:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html