はじめに
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
- Chapter 26 INFORMATION_SCHEMA Tables
- 26.2 INFORMATION_SCHEMA Table Reference
- information_schemaのInnoDB関連テーブル［その1］
performance_schema
- Chapter 27 MySQL Performance Schema
- 27.12.1 Performance Schema Table Reference
- Performance Schemaの仕組みと活用法の紹介
sys
