logo Navicat使用教程-2019.1 我也要发布文档

探讨MySQL信息模式中的INFORMATION_SCHEMA


Navicat Premium 是一套数据库管理工具,让你使用一个程序即可同時连接到 MySQL、MariaDB、SQL Server、SQLite、Oracle 和 PostgreSQL 数据库。快速管理不同类型的数据库。

点击下载Navicat Premium试用版

在关系数据库中,数据库元数据(例如有关MySQL服务器的信息、数据库或表的名称、列的数据类型或访问特权)存储在数据字典和/或系统目录中。MySQL在名为INFORMATION_SCHEMA的特殊模式中提供数据库元数据。每个MySQL实例中都有一个INFORMATION_SCHEMA。它包含几个只读表,您可以查询这些表以获得所需的信息。在今天的文章中,我们将探讨INFORMATION_SCHEMA的一些实际用法,例如使用Navicat Premium。

获取表信息

information_schema.tables表包含有关表的元数据,您猜对了!除了表名之外,您还可以检索其类型(基本表或视图)和引擎:

SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'sakila'
ORDER BY table_name;

这是上面的查询和Navicat的结果:

Navicat使用教程:探讨MySQL信息模式中的INFORMATION_SCHEMA

您还可以查询information_schema.tables以获取表的大小:

SELECT
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "sakila"
AND table_name = "film";

以下是Navicat Premium的结果:

Navicat使用教程:探讨MySQL信息模式中的INFORMATION_SCHEMA

通过一些调整,您可以列出每个数据库中每个表的大小:

SELECT
     table_schema as `Database`,
     table_name AS `Table`,
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

Navicat使用教程:探讨MySQL信息模式中的INFORMATION_SCHEMA

您甚至可以使用information_schema.tables列出MySQL实例中每个数据库的大小!

SELECT
     table_schema as `Database`,
     table_name AS `Table`,
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

Navicat使用教程:探讨MySQL信息模式中的INFORMATION_SCHEMA

查看表统计信息

INFORMATION_SCHEMA.STATISTICS表包含缓存的值。因此,这些默认情况下会在24小时后过期。如果没有缓存的统计信息或统计信息已过期,则在查询表统计信息列时将从存储引擎检索统计信息。

INFORMATION_SCHEMA.STATISTICS表的一种用途是查看特定模式中所有表的索引:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

以下是Navicat中sakila数据库提供的结果:

Navicat使用教程:探讨MySQL信息模式中的INFORMATION_SCHEMA

您只需删除where子句即可查看所有架构中的所有索引。在这种情况下,您可能还需要添加数据库名称:

SELECT DISTINCT
    stat.TABLE_SCHEMA as 'DATABASE',
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS stat;

Navicat使用教程:探讨MySQL信息模式中的INFORMATION_SCHEMA

结论

在今天的文章中,我们学习了许多使用MySQL INFORMATION_SCHEMA来获取有关MySQL实例中各种对象的元数据信息,从数据库到表、列、索引等数据库的方法。尽管查询是在Navicat Premium中运行的,但适用于Navicat for MySQL也可以正常使用!

相关内容推荐:

Navicat系列教程>>>


想要购买该产品正版授权,或了解更多产品信息请点击“咨询在线客服”