はじめに

MySQLのインストールした際に、SHOW DATABASESをしてみて、「これらの詳細は何だろう」と気になったので調べてみました。

環境

Windows11
MySQL 8.0.31

MySQLについては、パッケージ管理ツールであるchocolateyでインストールしています。

MySQLのインストール

PS C:\WINDOWS\system32> choco install mysql
Chocolatey v2.2.2
3 validations performed. 2 success(es), 1 warning(s), and 0 error(s).

Validation Warnings:
 - System Cache directory is not locked down to administrators.
   Remove the directory 'C:\ProgramData\ChocolateyHttpCache' to have
   Chocolatey CLI create it with the proper permissions.

Installing the following packages:
mysql
By installing, you accept licenses for the packages.
Progress: Downloading mysql 8.0.31... 100%

mysql v8.0.31 [Approved]
mysql package files install completed. Performing other installation steps.
The package mysql wants to run 'chocolateyInstall.ps1'.
Note: If you don't run this script, the installation will fail.
Note: To confirm automatically next time, use '-y' or consider:
choco feature enable -n allowGlobalConfirmation
Do you want to run the script?([Y]es/[A]ll - yes to all/[N]o/[P]rint): y

・・・途中省略・・・

Chocolatey installed 1/1 packages.
 See the log for details (C:\ProgramData\chocolatey\logs\chocolatey.log).

SHOW DATABASES

PS C:\WINDOWS\system32> mysql -u root -h localhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

information_schema

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges.

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains.

とある。データベースのテーブル、カラム、ストレージエンジン名や、アクセス権限が分かる。

Chapter 26 INFORMATION_SCHEMA Tables 26.2 INFORMATION_SCHEMA Table Reference

また、気になったのは以下の説明。

INFORMATION_SCHEMA as Alternative to SHOW Statements

SHOW文の代わりとしてinformation_schemaは機能するそう。試しに、SHOW COLUMNSに対応する情報をinformation_schemaのCOLUMNSテーブルから検索してみる。

mysql> SHOW COLUMNS FROM information_schema.TABLES;
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
| Field           | Type                                                               | Null | Key | Default | Extra |
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(64)                                                        | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)                                                        | YES  |     | NULL    |       |
| TABLE_NAME      | varchar(64)                                                        | YES  |     | NULL    |       |
| TABLE_TYPE      | enum('BASE TABLE','VIEW','SYSTEM VIEW')                            | NO   |     | NULL    |       |
| ENGINE          | varchar(64)                                                        | YES  |     | NULL    |       |
| VERSION         | int                                                                | YES  |     | NULL    |       |
| ROW_FORMAT      | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint unsigned                                                    | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint unsigned                                                    | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint unsigned                                                    | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint unsigned                                                    | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint unsigned                                                    | YES  |     | NULL    |       |
| DATA_FREE       | bigint unsigned                                                    | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint unsigned                                                    | YES  |     | NULL    |       |
| CREATE_TIME     | timestamp                                                          | NO   |     | NULL    |       |
| UPDATE_TIME     | datetime                                                           | YES  |     | NULL    |       |
| CHECK_TIME      | datetime                                                           | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(64)                                                        | YES  |     | NULL    |       |
| CHECKSUM        | bigint                                                             | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(256)                                                       | YES  |     | NULL    |       |
| TABLE_COMMENT   | text                                                               | YES  |     | NULL    |       |
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

mysql> SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY,  COLUMN_DEFAULT, EXTRA FROM information_schema.COLUMNS WHERE TABLE_NAME = "TABLES";
+-----------------+--------------------------------------------------------------------+-------------+------------+----------------+-------+
| COLUMN_NAME     | COLUMN_TYPE                                                        | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA |
+-----------------+--------------------------------------------------------------------+-------------+------------+----------------+-------+
| AUTO_INCREMENT  | bigint unsigned                                                    | YES         |            | NULL           |       |
| AVG_ROW_LENGTH  | bigint unsigned                                                    | YES         |            | NULL           |       |
| CHECK_TIME      | datetime                                                           | YES         |            | NULL           |       |
| CHECKSUM        | bigint                                                             | YES         |            | NULL           |       |
| CREATE_OPTIONS  | varchar(256)                                                       | YES         |            | NULL           |       |
| CREATE_TIME     | timestamp                                                          | NO          |            | NULL           |       |
| DATA_FREE       | bigint unsigned                                                    | YES         |            | NULL           |       |
| DATA_LENGTH     | bigint unsigned                                                    | YES         |            | NULL           |       |
| ENGINE          | varchar(64)                                                        | YES         |            | NULL           |       |
| INDEX_LENGTH    | bigint unsigned                                                    | YES         |            | NULL           |       |
| MAX_DATA_LENGTH | bigint unsigned                                                    | YES         |            | NULL           |       |
| ROW_FORMAT      | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') | YES         |            | NULL           |       |
| TABLE_CATALOG   | varchar(64)                                                        | YES         |            | NULL           |       |
| TABLE_COLLATION | varchar(64)                                                        | YES         |            | NULL           |       |
| TABLE_COMMENT   | text                                                               | YES         |            | NULL           |       |
| TABLE_NAME      | varchar(64)                                                        | YES         |            | NULL           |       |
| TABLE_ROWS      | bigint unsigned                                                    | YES         |            | NULL           |       |
| TABLE_SCHEMA    | varchar(64)                                                        | YES         |            | NULL           |       |
| TABLE_TYPE      | enum('BASE TABLE','VIEW','SYSTEM VIEW')                            | NO          |            | NULL           |       |
| UPDATE_TIME     | datetime                                                           | YES         |            | NULL           |       |
| VERSION         | int                                                                | YES         |            | NULL           |       |
+-----------------+--------------------------------------------------------------------+-------------+------------+----------------+-------+
21 rows in set (0.00 sec)

また、information_schema内のINNODB_CACHED_INDEXESやINNODB_INDEXESテーブルから、インデックスやバッファプールのページ数に関する情報も取得できる。

information_schemaのInnoDB関連テーブル［その1］

performance_schema

Chapter 27 MySQL Performance Schema 27.12.1 Performance Schema Table Reference

MySQL Server の実行を低レベルで監視するための機能で、どの情報を取得するかの管理や、クエリ実行の詳細等が確認できるそう。

普段はAWSのRDSでPerformance Insightsから見る程度ですが、詳細を見る際はこちらを活用すれば良いのだな、と参考になりました

sys

Chapter 28 MySQL sys Schema 26.4 sys Schema Object Reference

パフォーマンススキーマをより理解しやすい形式に要約したビューや、ストアドプロシージャ・ストアドファンクションが用意されている。

参考文献

information_schema

performance_schema

sys