通过 `INFORMATION_SCHEMA` 和 `Performance_Schema` 进行数据库元数据查询与性能分析

好的，各位观众老爷，各位程序猿、程序媛们，欢迎来到今天的数据库元数据查询与性能分析特别节目！我是你们的老朋友，一位混迹江湖多年的“码农老司机”，今天咱们就来聊聊数据库世界的两大情报部门：INFORMATION_SCHEMA 和 Performance_Schema。

别看它们名字听起来高大上，其实啊，它们就是数据库的“八卦小报”和“体检报告”，专门负责收集和整理数据库内部的各种信息，帮助我们更好地了解和优化数据库。

第一幕：INFORMATION_SCHEMA——数据库的“户口本”

首先登场的是 INFORMATION_SCHEMA，这家伙就像数据库的“户口本”，详细记录了数据库里所有对象的身份信息，包括表、视图、列、索引、存储过程等等。如果你想知道数据库里有多少张表，每张表有哪些字段，字段的类型是什么，INFORMATION_SCHEMA 绝对是你的不二之选。

1.1 “户口本”长啥样？

INFORMATION_SCHEMA 本身也是一个数据库，里面包含了一堆视图（Views），每个视图负责记录特定类型的信息。比如，TABLES 视图记录了所有表的信息，COLUMNS 视图记录了所有列的信息，VIEWS 视图记录了所有视图的信息，以此类推。

1.2 查询“户口本”的正确姿势

查询 INFORMATION_SCHEMA 非常简单，就像查询普通的表一样，使用 SELECT 语句就可以了。但是，要注意一点，INFORMATION_SCHEMA 里面的数据都是只读的，你只能查询，不能修改。

下面是一些常用的查询示例：

查询所有数据库的名称：
```
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA;
```
这条语句就像在问：“老王，你家有几个娃？”
查询某个数据库（比如 mydatabase）的所有表名：
```
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydatabase';
```
这条语句就像在问：“老王，你家娃的名字都叫啥？”
查询某个表（比如 users）的所有列名和数据类型：
```
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydatabase' AND TABLE_NAME = 'users';
```
这条语句就像在问：“老王，你家大娃多高？什么血型？”

1.3 INFORMATION_SCHEMA 的妙用

INFORMATION_SCHEMA 的用途非常广泛，以下是一些常见的应用场景：

动态生成 SQL 语句： 你可以根据 INFORMATION_SCHEMA 里的信息，动态生成创建表、修改表结构的 SQL 语句，实现自动化部署。
数据字典： INFORMATION_SCHEMA 可以作为数据字典，帮助你了解数据库的结构和含义。
数据库迁移： 在进行数据库迁移时，可以使用 INFORMATION_SCHEMA 获取源数据库的结构信息，然后在新数据库中创建相应的表和索引。
代码生成器： 很多代码生成器都利用 INFORMATION_SCHEMA 来生成实体类、DAO 接口等代码，提高开发效率。

表格1：INFORMATION_SCHEMA 常用视图

视图名称	描述
`SCHEMATA`	数据库（Schema）的信息
`TABLES`	表的信息
`COLUMNS`	列的信息
`VIEWS`	视图的信息
`STATISTICS`	索引的信息
`ROUTINES`	存储过程和函数的信息
`PARAMETERS`	存储过程和函数的参数信息
`TABLE_CONSTRAINTS`	表约束（主键、外键、唯一约束等）的信息
`KEY_COLUMN_USAGE`	键（主键、外键）的列的使用信息
`TRIGGERS`	触发器的信息
`EVENTS`	事件的信息

总之，INFORMATION_SCHEMA 就像一本百科全书，你想了解数据库的任何信息，都可以从这里找到答案。

第二幕：Performance_Schema——数据库的“体检报告”

接下来，有请我们的重量级嘉宾 Performance_Schema 闪亮登场！这家伙可不是个简单的角色，它就像数据库的“体检报告”，详细记录了数据库在运行过程中的各种性能指标，包括 SQL 语句的执行时间、锁的等待时间、IO 的读写次数等等。通过分析 Performance_Schema 里的数据，你可以找到数据库的性能瓶颈，并进行相应的优化。

2.1 “体检报告”怎么来的？

与 INFORMATION_SCHEMA 不同，Performance_Schema 并不是默认启用的，你需要手动开启它。开启 Performance_Schema 会对数据库的性能产生一定的影响，因为它需要收集大量的性能数据。但是，为了更好地了解和优化数据库，这点牺牲是值得的。

开启 Performance_Schema 的方法很简单，只需要在 MySQL 的配置文件（my.cnf 或 my.ini）中添加以下配置：

performance_schema=on

然后重启 MySQL 服务即可。

2.2 “体检报告”都包含啥？

Performance_Schema 里面的数据非常丰富，它通过一系列的事件（Events）来记录数据库的运行情况。事件可以分为不同的类型，比如 SQL 语句的执行事件、锁的等待事件、IO 的读写事件等等。

Performance_Schema 使用不同的表来存储不同类型的事件。比如，events_statements_summary_by_digest 表记录了 SQL 语句的执行统计信息，events_waits_summary_global_by_event_name 表记录了全局的等待事件统计信息，以此类推。

2.3 解读“体检报告”的密码

解读 Performance_Schema 里的数据需要一定的技巧，因为里面的数据比较复杂，而且有很多关联关系。你需要了解不同表的含义，以及它们之间的关联关系，才能正确地分析数据。

下面是一些常用的查询示例：

查询执行时间最长的 SQL 语句：

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT / 1000000000000 AS avg_latency,
    SUM_TIMER_WAIT / 1000000000000 AS total_latency
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

这条语句就像在问：“医生，我身体哪个部位最虚？”

查询锁等待时间最长的事件：

SELECT
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1000000000000 AS total_latency
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

这条语句就像在问：“医生，我哪里堵车最严重？”

查询某个 SQL 语句的执行计划：
```
EXPLAIN SELECT * FROM your_table WHERE your_condition;
```
这条语句就像在问：“医生，我的病是怎么来的？”

2.4 Performance_Schema 的神奇力量

Performance_Schema 的用途非常强大，以下是一些常见的应用场景：

性能监控： 你可以使用 Performance_Schema 实时监控数据库的性能指标，及时发现性能问题。
性能分析： 你可以使用 Performance_Schema 分析数据库的性能瓶颈，找到需要优化的 SQL 语句和索引。
性能调优： 你可以根据 Performance_Schema 的分析结果，进行相应的性能调优，比如优化 SQL 语句、添加索引、调整数据库参数等等。
故障诊断： 当数据库出现故障时，可以使用 Performance_Schema 诊断故障原因，快速定位问题。

表格2：Performance_Schema 常用表

表名	描述
`events_statements_current`	当前正在执行的 SQL 语句事件
`events_statements_history`	最近执行过的 SQL 语句事件
`events_statements_history_long`	较早执行过的 SQL 语句事件，保留时间更长
`events_statements_summary_by_digest`	按 SQL 语句摘要分组的统计信息，可以找到执行时间最长的 SQL 语句
`events_waits_current`	当前正在发生的等待事件
`events_waits_history`	最近发生的等待事件
`events_waits_history_long`	较早发生的等待事件，保留时间更长
`events_waits_summary_global_by_event_name`	按事件名称分组的全局等待事件统计信息，可以找到锁等待时间最长的事件
`file_summary_by_event_name`	按事件名称分组的文件 I/O 统计信息
`socket_summary_by_event_name`	按事件名称分组的 Socket I/O 统计信息
`memory_summary_global_by_event_name`	按事件名称分组的内存使用情况统计信息

总之，Performance_Schema 就像一位经验丰富的医生，能够帮你诊断数据库的各种疑难杂症，让你的数据库保持健康和活力。💪

第三幕：INFORMATION_SCHEMA + Performance_Schema = 无敌组合

如果把 INFORMATION_SCHEMA 和 Performance_Schema 结合起来使用，那简直就是如虎添翼，威力无穷！你可以使用 INFORMATION_SCHEMA 获取数据库的结构信息，然后使用 Performance_Schema 分析数据库的性能指标，从而更全面地了解和优化数据库。

比如，你可以使用 INFORMATION_SCHEMA 找到某个表的所有索引，然后使用 Performance_Schema 分析这些索引的使用情况，从而判断是否需要优化索引。

3.1 一个实战案例：找出未使用索引

SELECT
    s.index_name,
    t.table_name,
    t.table_schema
FROM
    information_schema.STATISTICS s
JOIN
    information_schema.TABLES t ON s.TABLE_NAME = t.TABLE_NAME AND s.TABLE_SCHEMA = t.TABLE_SCHEMA
LEFT JOIN
    performance_schema.table_io_waits_summary_by_index_usage p ON s.TABLE_NAME = p.OBJECT_NAME AND s.TABLE_SCHEMA = p.OBJECT_SCHEMA AND s.INDEX_NAME = p.INDEX_NAME
WHERE
    p.INDEX_NAME IS NULL
    AND s.INDEX_NAME != 'PRIMARY'
    AND t.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY
    s.index_name, t.table_name, t.table_schema
ORDER BY
    t.table_schema, t.table_name;

这条 SQL 语句就像一位侦探🕵️‍♀️，它会找出数据库中所有未使用过的索引，帮助你清理冗余索引，提高数据库的性能。

第四幕：注意事项与最佳实践

在使用 INFORMATION_SCHEMA 和 Performance_Schema 时，需要注意以下几点：

开启 Performance_Schema 会对数据库的性能产生一定的影响， 所以要根据实际情况选择是否开启。
Performance_Schema 里面的数据量非常大， 要定期清理过期数据，避免占用过多的存储空间。
查询 INFORMATION_SCHEMA 和 Performance_Schema 时， 要尽量使用索引，避免全表扫描。
要仔细阅读 MySQL 的官方文档， 了解 INFORMATION_SCHEMA 和 Performance_Schema 的详细信息。

以下是一些最佳实践：

定期监控数据库的性能指标， 及时发现性能问题。
使用 Performance_Schema 分析数据库的性能瓶颈， 找到需要优化的 SQL 语句和索引。
使用 INFORMATION_SCHEMA 获取数据库的结构信息， 方便进行数据库管理和维护。
多学习、多实践， 提高自己的数据库技能。

总结陈词

各位观众老爷，今天的数据库元数据查询与性能分析特别节目就到这里了。希望通过今天的讲解，大家对 INFORMATION_SCHEMA 和 Performance_Schema 有了更深入的了解。记住，它们是数据库的“八卦小报”和“体检报告”，是数据库管理员和开发人员的必备工具。掌握了它们，你就能更好地了解和优化数据库，让你的数据库跑得更快、更稳！

最后，祝大家编码愉快，Bug 远离！咱们下期再见！👋

发表回复 取消回复

发表回复取消回复