主页 / dba / 问题 / 3335
Accepted
ivotron
Asked: 2011-06-16 15:17:08 +0800 CST

MySQL 中的 INFORMATION_SCHEMA 是如何实现的？

  • 772

理论上，INFORMATION_SCHEMA 是 SQL 标准中指定的一组视图，允许用户检查系统的元数据。这是如何在 MySQL 中实现的？

当我连接到全新安装时，我看到两个数据库：mysql和information_schema. 在数据库上使用SHOW CREATE TABLE语句后information_schema，看起来它不是作为一组视图实现的，而是用基表实现的。这个假设正确吗？或者还有其他对用户隐藏的系统表？

mysql information-schema

1 个回答

  1. Best Answer

    INFORMATION_SCHEMA数据库由使用 MEMORY 存储引擎的临时表组成。

    示例：这是 MySQL 5.5.12（Windows 版本）中的表 INFORMATION_SCHEMA.TABLES

    mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

    这些表没有物理文件夹，甚至没有 .frm 文件。你不能 mysqldump 它。你不能放弃它。您不能向其中添加表。您不能从中删除表。那么，桌子在哪里？？？

    INFORMATION_SCHEMA 数据库中的所有表都作为 MEMORY 存储引擎表直接存储在内存中。它们完全在 MySQL 内部，因此 .frm 机制在 mysqld 中处理。在我的回答中，我首先展示了 INFORMATION_SCHEMA.TABLES 的表格布局。它是内存中的临时表。它使用存储引擎协议进行操作。因此，当 mysqld 关闭时，所有 information_schema 表都将被删除。当 mysqld 启动时，所有 information_schema 表都被创建为 TEMPORARY 表，并为 mysql 实例中的每个表重新填充元数据。

    INFORMATION_SCHEMA数据库最初是在 MySQL 5.0 中引入的，让您可以访问有关其他存储引擎的表的元数据。例如，您可以执行SHOW DATABASES来获取数据库列表。您也可以像这样查询它们：

    SELECT schema_name database FROM information_schema.schemata;

    您可以通过两种方式检索数据库中的表名：

    use mydb
show tables;

    或者

    SELECT table_name from information_schema.tables WHERE table_schema = 'mydb';

    自成立以来，MySQL 已将 INFORMATION_SCHEMA 数据库扩展为具有进程列表（从 MySQL 5.1 开始）。您实际上可以查询进程列表以查找仍在运行至少 10 分钟的长时间运行的查询：

    SELECT * FROM information_schema.processlist WHERE time >= 600\G

    您可以使用 INFORMATION_SCHEMA 来做每件复杂的事情：例如：

    使用特定存储引擎获取所有表的计数：

    SELECT COUNT(1) TableCount,IFNULL(engine,'Total') StorageEngine
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
AND engine IS NOT NULL
GROUP BY engine WITH ROLLUP;

    获取推荐的 MyISAM 密钥缓冲区大小（以 MB 为单位）

    SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_key_buffer_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,
(SELECT 2 pw) B;

    以 GB 为单位获取推荐的 InnoDB 缓冲池大小

    SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 3 pw) B;

    按存储引擎获取所有数据库的磁盘使用情况（以 MB 为单位）

    SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize
FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 2 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

    相信我，INFORMATION_SCHEMA 还有更多奇妙的用途，时间不允许我进一步讨论。

    请记住，INFORMATION_SCHEMA 非常敏感，如果 mysql 正在运行并且您执行以下操作：

    cd /var/lib/mysql
mkdir junkfolder

    然后进入mysql运行

    mysql> SHOW DATABASES;

    您将看到 junkfolder 作为数据库之一。

    了解它对于 DBA 和开发人员来说非常重要。MySQL 5.0 Certification Study Guide一书的第 20 章（开发人员）和第 31 章（DBA）

    在此处输入图像描述

    在那里为开发人员和 DBA 认证考试做准备。拿到这本书，好好学习那些章节，你就可以用 MySQL 的 INFORMATION_SCHEMA 做一些很棒的事情。

    MySQL 5.5 的 INFORMATION_SCHEMA 数据库现在具有插件、全局变量（状态和静态）、会话变量（状态和静态）、存储引擎状态、性能指标检测、触发器映射、事件（可编程）等等。

    抱歉，这可能看起来像 WTMI，但我非常支持使用 INFORMATION_SCHEMA 数据库。

    • 32

相关问题