MochiuWiki : SUSE, EC, PCB

MySQL - 情報参照

概要

MySQLは、データベース、テーブル、サーバ状態、権限、レプリケーション状態等の様々な情報を参照するための SHOW 文 と DESCRIBE 文を提供している。
これらの文は、データベース管理、パフォーマンスチューニング、トラブルシューティングにおいて不可欠なツールである。

SHOW 文は、データベースオブジェクト (データベース、テーブル、インデックス) の情報、サーバ変数や状態、実行中のプロセス、権限情報、エンジン情報、レプリケーション状態等を表示する。
DESCRIBE 文 (DESC) は、テーブル構造 (カラム名、データ型、NULL許容、キー情報等) を簡潔に表示する。

SHOW文の多くは、LIKE 句 または WHERE 句による絞り込みをサポートしており、必要な情報のみを効率的に取得できる。
また、多くの SHOW 文は、INFORMATION_SCHEMA データベース または performance_schema データベースのテーブルに対応しており、より柔軟なクエリが可能である。

対話的な管理作業には SHOW 文が便利であり、プログラムによる自動化やより複雑なフィルタリングには INFORMATION_SCHEMA を使用することを推奨する。


データベース情報の参照

SHOW DATABASES

SHOW DATABASES 文は、MySQLサーバ上の全てのデータベース名を表示する。
SHOW SCHEMAS は SHOW DATABASES の同義語である。

構文を以下に示す。

 SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]


ユーザが持つ権限に基づき、結果がフィルタリングされる。
SHOW DATABASES 権限がない場合、アクセス可能なデータベースのみが表示される。

実行例を以下に示す。

 # 全てのデータベースを表示
 SHOW DATABASES;
 
 # 出力例
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 | myapp_db           |
 | test_db            |
 +--------------------+
 
 # 'test'で始まるデータベースを表示
 SHOW DATABASES LIKE 'test%';
 
 # 'app'を含むデータベースを表示
 SHOW DATABASES WHERE `Database` LIKE '%app%';


LIKE 句を使用することで、パターンマッチングによる絞り込みが可能である。
WHERE 句を使用すると、より複雑な条件を指定できる。

SHOW CREATE DATABASE

SHOW CREATE DATABASE 文は、データベースを作成する CREATE DATABASE 文を表示する。
SHOW CREATE SCHEMA は SHOW CREATE DATABASE の同義語である。

構文を以下に示す。

 SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name


出力には、文字セット、照合順序、暗号化設定等が含まれる。

実行例を以下に示す。

 # myapp_dbの作成文を表示
 SHOW CREATE DATABASE myapp_db;
 
 # 出力例
 +----------+----------------------------------------------------------------------+
 | Database | Create Database                                                      |
 +----------+----------------------------------------------------------------------+
 | myapp_db | CREATE DATABASE `myapp_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 */|
 +----------+----------------------------------------------------------------------+
 
 # IF NOT EXISTSオプションを含めて表示
 SHOW CREATE DATABASE IF NOT EXISTS myapp_db;


sql_quote_show_create システム変数が有効 (デフォルト) の場合、識別子はバッククォートで囲まれる。


テーブル情報の参照

SHOW TABLES

SHOW TABLES 文は、指定したデータベース内の全てのテーブルとビューを表示する。

構文を以下に示す。

 SHOW [EXTENDED] [FULL] TABLES
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]


修飾子の意味を以下に示す。

  • EXTENDED
    失敗した ALTER TABLE 操作で作成された隠しテーブルも表示する。
  • FULL
    テーブルタイプ (BASE TABLE、VIEW、SYSTEM VIEW) を第2カラムで表示する。


実行例を以下に示す。

 # 現在のデータベースの全テーブルを表示
 SHOW TABLES;
 
 # 出力例
 +-------------------+
 | Tables_in_myapp   |
 +-------------------+
 | users             |
 | orders            |
 | products          |
 | order_items       |
 +-------------------+
 
 # myapp_dbデータベースの全テーブルを表示
 SHOW TABLES FROM myapp_db;
 
 # テーブルタイプも表示
 SHOW FULL TABLES;
 
 # 出力例
 +-------------------+------------+
 | Tables_in_myapp   | Table_type |
 +-------------------+------------+
 | users             | BASE TABLE |
 | orders            | BASE TABLE |
 | user_summary      | VIEW       |
 +-------------------+------------+
 
 # 'order'を含むテーブルを表示
 SHOW TABLES LIKE '%order%';
 
 # ビューのみを表示
 SHOW FULL TABLES WHERE Table_type = 'VIEW';


ビューも表示対象に含まれるため、FULL 修飾子でテーブルとビューを区別できる。

SHOW TABLE STATUS

SHOW TABLE STATUS 文は、テーブルの詳細な状態情報を表示する。

構文を以下に示す。

 SHOW TABLE STATUS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]


下表に、主要な出力カラムを示す。

SHOW TABLE STATUS 出力カラム
カラム名 説明
Name テーブル名
Engine ストレージエンジン (InnoDB、MyISAM等)
Version テーブルフォーマットのバージョン
Row_format 行フォーマット (Dynamic、Compact、Compressed等)
Rows 推定行数 (InnoDBの場合、40-50%の誤差がある近似値)
Avg_row_length 平均行長 (バイト)
Data_length データサイズ (バイト)
Max_data_length 最大データサイズ (バイト)
Index_length インデックスサイズ (バイト)
Data_free 未使用領域 (バイト)
Auto_increment 次のAUTO_INCREMENT値
Create_time テーブル作成日時
Update_time 最終更新日時
Check_time 最終チェック日時
Collation 照合順序
Checksum チェックサム値
Create_options テーブル作成時のオプション
Comment テーブルコメント


実行例を以下に示す。

 # 全テーブルの状態を表示
 SHOW TABLE STATUS;
 
 # usersテーブルの状態を表示
 SHOW TABLE STATUS LIKE 'users';
 
 # 出力例 (横長のため一部のみ表示)
 +-------+--------+---------+------------+------+----------------+-------------+
 | Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
 +-------+--------+---------+------------+------+----------------+-------------+
 | users | InnoDB |      10 | Dynamic    | 5432 |            150 |      815104 |
 +-------+--------+---------+------------+------+----------------+-------------+
 
 # InnoDBテーブルのみを表示
 SHOW TABLE STATUS WHERE Engine = 'InnoDB';
 
 # 1週間以内に更新されたテーブルを表示
 SHOW TABLE STATUS WHERE Update_time > DATE_SUB(NOW(), INTERVAL 7 DAY);


※注意

  • InnoDBの Rows 値は推定値であり、40〜50[%]のばらつきがある。
    正確な行数が必要な場合は、SELECT COUNT(*) FROM table_name を実行する。
  • Data_length と Index_length から、テーブルとインデックスのサイズを把握できる。
    ストレージ容量の管理やパフォーマンスチューニングに有用である。


SHOW CREATE TABLE

SHOW CREATE TABLE 文は、テーブルまたはビューを作成する CREATE TABLE 文 または CREATE VIEW 文を表示する。

構文を以下に示す。

 SHOW CREATE TABLE tbl_name


出力には、カラム定義、インデックス、外部キー制約、パーティション定義、テーブルオプション等が含まれる。
MySQL 8.0.16以降では、CHECK 制約も表示される。

実行例を以下に示す。

 # usersテーブルの作成文を表示
 SHOW CREATE TABLE users\G
 
 # 出力例
 *************************** 1. row ***************************
        Table: users
 Create Table: CREATE TABLE `users` (
   `id` int NOT NULL AUTO_INCREMENT,
   `username` varchar(50) NOT NULL,
   `email` varchar(100) NOT NULL,
   `status` varchar(20) DEFAULT 'active',
   `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   UNIQUE KEY `idx_username` (`username`),
   KEY `idx_status` (`status`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4


\G オプションにより、出力を縦方向に整形できる。(mysqlクライアントのみ)

テーブル構造のバックアップや、他のデータベースへの移行時に有用である。

DESCRIBE / DESC

DESCRIBE文 (短縮形: DESC) は、テーブル構造を簡潔に表示する。
SHOW COLUMNS のシンプルな代替である。

構文を以下に示す。

 {DESCRIBE | DESC} tbl_name [col_name | 'pattern']


出力カラムを以下に示す。

DESCRIBE 出力カラム
カラム名 説明
Field カラム名
Type データ型
Null NULL許容 (YES / NO)
Key キー情報 (PRI=主キー、UNI=UNIQUE、MUL=非ユニークインデックス)
Default デフォルト値
Extra 追加情報 (auto_increment、on update CURRENT_TIMESTAMP等)


実行例を以下に示す。

 # usersテーブルの構造を表示
 DESCRIBE users;
 
 # 短縮形
 DESC users;
 
 # 出力例
 +------------+--------------+------+-----+-------------------+-------------------+
 | Field      | Type         | Null | Key | Default           | Extra             |
 +------------+--------------+------+-----+-------------------+-------------------+
 | id         | int          | NO   | PRI | NULL              | auto_increment    |
 | username   | varchar(50)  | NO   | UNI | NULL              |                   |
 | email      | varchar(100) | NO   |     | NULL              |                   |
 | status     | varchar(20)  | YES  | MUL | active            |                   |
 | created_at | datetime     | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
 +------------+--------------+------+-----+-------------------+-------------------+
 
 # 特定のカラムのみ表示
 DESC users username;
 
 # 'name'を含むカラムのみ表示
 DESC users '%name%';


下表に、Key カラムの値の意味を示す。

Keyカラムの値
値 説明
PRI PRIMARY KEY (主キー)
UNI UNIQUE インデックス (一意制約)
MUL 非UNIQUE インデックス (複数の同じ値が許可される)
複合インデックスの最初のカラムでない場合も MUL と表示される。


SHOW COLUMNS

SHOW COLUMNS 文は、テーブルのカラム情報を表示する。
DESCRIBE よりも詳細な情報を取得できる。

構文を以下に示す。

 SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]


SHOW FIELDS は SHOW COLUMNS の同義語である。

修飾子の意味を以下に示す。

修飾子の意味
修飾子 説明
FULL Collation、Privileges、Commentカラムを追加表示する。
EXTENDED 隠しカラム (INVISIBLE属性のカラム) も表示する。


実行例を以下に示す。

 # usersテーブルのカラム情報を表示
 SHOW COLUMNS FROM users;
 
 # FULL修飾子で詳細情報を表示
 SHOW FULL COLUMNS FROM users;
 
 # 出力例 (FULL)
 +------------+--------------+-----------------+------+-----+-------------------+
 | Field      | Type         | Collation       | Null | Key | Default           |
 +------------+--------------+-----------------+------+-----+-------------------+
 | id         | int          | NULL            | NO   | PRI | NULL              |
 | username   | varchar(50)  | utf8mb4_0900... | NO   | UNI | NULL              |
 | email      | varchar(100) | utf8mb4_0900... | NO   |     | NULL              |
 +------------+--------------+-----------------+------+-----+-------------------+
 +-------------------+-----------------------------+
 | Extra             | Privileges                  |
 +-------------------+-----------------------------+
 | auto_increment    | select,insert,update,...    |
 |                   | select,insert,update,...    |
 |                   | select,insert,update,...    |
 +-------------------+-----------------------------+
 
 # 'name'を含むカラムのみ表示
 SHOW COLUMNS FROM users LIKE '%name%';
 
 # INT型のカラムのみ表示
 SHOW COLUMNS FROM users WHERE Type LIKE 'int%';


FULL修飾子を使用すると、以下の追加情報が表示される。

  • Collation
    カラムの照合順序 (文字列型のみ)
  • Privileges
    カラムに対する権限 (select、insert、update、references)
  • Comment
    カラムのコメント



インデックス情報の参照

SHOW INDEX

SHOW INDEX 文は、テーブルのインデックス情報を表示する。
SHOW INDEXES および SHOW KEYS は同義語である。

構文を以下に示す。

 SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]


下表に、主要な出力カラムを示す。

SHOW INDEX 出力カラム
カラム名 説明
Table テーブル名
Non_unique 0：ユニーク
1：非ユニーク
Key_name インデックス名 (PRIMARY、インデックス名)
Seq_in_index インデックス内のカラム順序 (1から始まる)
Column_name カラム名
Collation ソート順序 (A：昇順、D：降順、NULL：ソートなし)
Cardinality インデックスの一意値の推定数 (大きいほど選択性が高い)
Sub_part インデックスのプレフィックス長 (部分インデックスの場合)
Packed インデックスのパック方法 (通常はNULL)
Null NULL値が許可されるか (YES / 空文字列)
Index_type インデックスタイプ (BTREE、FULLTEXT、HASH、RTREE)
Comment インデックスのコメント
Index_comment インデックス作成時のコメント
Visible インデックスが可視か (YES / NO)
Expression 関数型キーの式 (MySQL 8.0.13以降)


実行例を以下に示す。

 # usersテーブルの全インデックスを表示
 SHOW INDEX FROM users;
 
 # 出力例
 +-------+------------+--------------+--------------+-------------+-----------+-------------+
 | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality |
 +-------+------------+--------------+--------------+-------------+-----------+-------------+
 | users |          0 | PRIMARY      |            1 | id          | A         |        5432 |
 | users |          0 | idx_username |            1 | username    | A         |        5432 |
 | users |          1 | idx_status   |            1 | status      | A         |           3 |
 +-------+------------+--------------+--------------+-------------+-----------+-------------+
 +----------+--------+------+------------+-------------+---------+--------------+
 | Sub_part | Packed | Null | Index_type | Comment     | Visible | Expression   |
 +----------+--------+------+------------+-------------+---------+--------------+
 |     NULL |   NULL |      | BTREE      |             | YES     | NULL         |
 |     NULL |   NULL |      | BTREE      |             | YES     | NULL         |
 |     NULL |   NULL | YES  | BTREE      |             | YES     | NULL         |
 +----------+--------+------+------------+-------------+---------+--------------+
 
 # UNIQUEインデックスのみ表示
 SHOW INDEX FROM users WHERE Non_unique = 0;
 
 # 複合インデックスの例
 SHOW INDEX FROM orders WHERE Key_name = 'idx_user_date';
 
 # 出力例 (複合インデックス)
 +--------+------------+---------------+--------------+-------------+-----------+
 | Table  | Non_unique | Key_name      | Seq_in_index | Column_name | Collation |
 +--------+------------+---------------+--------------+-------------+-----------+
 | orders |          1 | idx_user_date |            1 | user_id     | A         |
 | orders |          1 | idx_user_date |            2 | order_date  | A         |
 +--------+------------+---------------+--------------+-------------+-----------+


重要な情報を以下に示す。

  • Non_unique
    0 : ユニークインデックス (PRIMARY KEY、UNIQUE)
    1 : 非ユニークインデックス
  • Cardinality
    インデックスの一意値の推定数。大きいほど選択性が高く、インデックスの効果が高い。
    ANALYZE TABLEを実行することで、この値の精度を向上できる。
  • Index_type
    BTREE (B-Tree、デフォルト)、FULLTEXT (全文検索)、HASH (メモリテーブル)、RTREE (空間インデックス)
  • Visible
    NO の場合、インデックスは存在するがオプティマイザに無視される。(MySQL 8.0以降)
  • Expression
    関数型インデックスの場合、式が表示される。(例: lower(email))



サーバ変数・状態の参照

SHOW VARIABLES

SHOW VARIABLES 文は、MySQLサーバのシステム変数とその値を表示する。

構文を以下に示す。

 SHOW [GLOBAL | SESSION] VARIABLES
    [LIKE 'pattern' | WHERE expr]


スコープの指定を以下に示す。

  • SESSION (デフォルト)
    セッション変数を表示する。
    現在の接続の設定
  • GLOBAL
    グローバル変数を表示する。
    サーバ全体の設定


実行例を以下に示す。

 # 全てのシステム変数を表示
 SHOW VARIABLES;
 
 # 出力例 (一部のみ)
 +--------------------------+---------------------------+
 | Variable_name            | Value                     |
 +--------------------------+---------------------------+
 | auto_increment_increment | 1                         |
 | autocommit               | ON                        |
 | character_set_client     | utf8mb4                   |
 | max_connections          | 151                       |
 | version                  | 8.0.35                    |
 +--------------------------+---------------------------+
 
 # 'max'を含む変数を表示
 SHOW VARIABLES LIKE '%max%';
 
 # 'size'を含む変数を表示
 SHOW VARIABLES LIKE '%size%';
 
 # グローバル変数を表示
 SHOW GLOBAL VARIABLES LIKE 'max_connections';
 
 # 出力例
 +-----------------+-------+
 | Variable_name   | Value |
 +-----------------+-------+
 | max_connections | 151   |
 +-----------------+-------+
 
 # バッファ関連の変数を表示
 SHOW VARIABLES WHERE Variable_name LIKE '%buffer%';


ワイルドカードの使用方法を以下に示す。

  • %
    任意の文字列 (0文字以上)
  • _
    任意の1文字


下表に、主要なシステム変数のカテゴリを示す。

主要なシステム変数のカテゴリ
カテゴリ システム変数
バッファとキャッシュ innodb_buffer_pool_size、query_cache_size、sort_buffer_size
接続とタイムアウト max_connections、wait_timeout、interactive_timeout
文字セットと照合順序 character_set_server、collation_server
ログ general_log、slow_query_log、log_bin
レプリケーション server_id、binlog_format、gtid_mode


SHOW STATUS

SHOW STATUS 文は、MySQLサーバの状態変数とその値を表示する。
状態変数は、サーバの動作統計 (接続数、クエリ数、バッファ使用状況等) を示す。

構文を以下に示す。

 SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]


スコープの指定を以下に示す。

  • SESSION (デフォルト)
    現在のセッションの統計を表示する。
  • GLOBAL
    サーバ起動からの累積統計を表示する。


実行例を以下に示す。

 # 全ての状態変数を表示
 SHOW STATUS;
 
 # 出力例 (一部のみ)
 +----------------------------+------------+
 | Variable_name              | Value      |
 +----------------------------+------------+
 | Aborted_clients            | 12         |
 | Aborted_connects           | 5          |
 | Bytes_received             | 123456789  |
 | Bytes_sent                 | 987654321  |
 | Connections                | 5432       |
 | Created_tmp_tables         | 234        |
 | Queries                    | 12345      |
 | Threads_connected          | 8          |
 | Uptime                     | 345600     |
 +----------------------------+------------+
 
 # 'Key'で始まる変数を表示 (キーバッファの統計)
 SHOW STATUS LIKE 'Key%';
 
 # 出力例
 +------------------------+-----------+
 | Variable_name          | Value     |
 +------------------------+-----------+
 | Key_blocks_not_flushed | 0         |
 | Key_blocks_unused      | 6698      |
 | Key_blocks_used        | 1302      |
 | Key_read_requests      | 123456789 |
 | Key_reads              | 12345     |
 | Key_write_requests     | 987654    |
 | Key_writes             | 98765     |
 +------------------------+-----------+
 
 # グローバル状態を表示
 SHOW GLOBAL STATUS LIKE 'Threads%';
 
 # 出力例
 +-------------------+-------+
 | Variable_name     | Value |
 +-------------------+-------+
 | Threads_cached    | 2     |
 | Threads_connected | 8     |
 | Threads_created   | 10    |
 | Threads_running   | 3     |
 +-------------------+-------+
 
 # 一時テーブル関連の統計を表示
 SHOW STATUS WHERE Variable_name LIKE 'Created_tmp%';


下表に、主要な状態変数のカテゴリを示す。

MySQL統計情報の分類
分類 統計項目
接続統計 Aborted_clients、Aborted_connects、Connections、Threads_connected
クエリ統計 Queries、Questions、Com_select、Com_insert、Com_update、Com_delete
バッファ・キャッシュ統計 Key_read_requests、Key_reads、Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads
一時テーブル統計 Created_tmp_tables、Created_tmp_disk_tables
スレッド統計 Threads_cached、Threads_created、Threads_running


※注意

  • 各 SHOW STATUS 呼び出しは、Created_tmp_tables値を1増加させる。
    これは、SHOW STATUS 自体が内部的に一時テーブルを使用するためである。



プロセス情報の参照

SHOW PROCESSLIST

SHOW PROCESSLIST 文は、MySQLサーバで実行中のスレッド (接続) の情報を表示する。

構文を以下に示す。

 SHOW [FULL] PROCESSLIST


修飾子の意味を以下に示す。

  • FULL
    Infoカラムの最大100文字制限を解除し、完全なクエリテキストを表示する。


出力カラムを以下に示す。

SHOW PROCESSLIST 出力カラム
カラム名 説明
Id スレッドID (接続ID)
User ユーザ名
Host ホスト名またはIPアドレス
db 現在のデータベース名
Command コマンドタイプ (Query、Sleep、Connect等)
Time コマンド実行時間 (秒)
State スレッドの状態 (Sending data、Sorting result等)
Info 実行中のクエリテキスト (最大100文字、FULLオプションで制限解除)


実行例を以下に示す。

 # 全てのプロセスを表示
 SHOW PROCESSLIST;
 
 # 出力例
 +----+------+-----------+--------+---------+------+-------+-----------------------+
 | Id | User | Host      | db     | Command | Time | State | Info                  |
 +----+------+-----------+--------+---------+------+-------+-----------------------+
 |  5 | root | localhost | myapp  | Query   |    0 | init  | SHOW PROCESSLIST      |
 | 12 | app  | 10.0.1.5  | myapp  | Query   |   45 | Sending data | SELECT * FROM ... |
 | 15 | app  | 10.0.1.7  | myapp  | Sleep   |  120 |       | NULL                  |
 | 18 | app  | 10.0.1.9  | myapp  | Query   |   10 | Sorting result | SELECT ... ORDER BY |
 +----+------+-----------+--------+---------+------+-------+-----------------------+
 
 # 完全なクエリテキストを表示
 SHOW FULL PROCESSLIST;
 
 # performance_schema.processlistテーブルで同等の情報を取得
 SELECT * FROM performance_schema.processlist;
 
 # 実行時間が長いクエリを特定
 SELECT * FROM performance_schema.processlist
 WHERE COMMAND = 'Query' AND TIME > 30
 ORDER BY TIME DESC;


権限の要件を以下に示す。

  • PROCESS 権限がある場合
    全てのスレッドを表示できる。
  • PROCESS 権限がない場合
    自分のスレッドのみ表示できる。


MySQL 8.0.22以降では、performance_schema.processlist テーブルの使用を推奨する。
このテーブルは、SHOW PROCESSLIST よりも詳細な情報を提供し、WHERE 句による柔軟なフィルタリングが可能である。

下表に、Commandカラムの主要な値を示す。

Commandカラムの主要な値
値 説明
Query クエリを実行中
Sleep アイドル状態 (次のクエリを待機中)
Connect 接続処理中
Quit 切断処理中


下表に、Stateカラムの主要な値を示す。

Stateカラムの主要な値
値 説明
Sending data データを読み取って送信中
Sorting result 結果をソート中
Creating tmp table 一時テーブルを作成中
Locked テーブルロック待ち
Updating 行を更新中



権限情報の参照

SHOW GRANTS

SHOW GRANTS 文は、ユーザまたはロールに付与された権限を表示する。

構文を以下に示す。

 SHOW GRANTS
    [FOR user_or_role
       [USING role [, role] ...]]


実行例を以下に示す。

 # 現在のユーザの権限を表示
 SHOW GRANTS;
 
 # 出力例
 +---------------------------------------------------------------------+
 | Grants for root@localhost                                           |
 +---------------------------------------------------------------------+
 | GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION |
 | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
 +---------------------------------------------------------------------+
 
 # 特定のユーザの権限を表示
 SHOW GRANTS FOR 'jeffrey'@'localhost';
 
 # 出力例
 +-------------------------------------------------------------------+
 | Grants for jeffrey@localhost                                      |
 +-------------------------------------------------------------------+
 | GRANT USAGE ON *.* TO `jeffrey`@`localhost`                       |
 | GRANT SELECT, INSERT, UPDATE ON `myapp`.* TO `jeffrey`@`localhost`|
 +-------------------------------------------------------------------+
 
 # ホスト名を省略すると、'%'がデフォルトとなる
 SHOW GRANTS FOR 'jeffrey';
 # これは SHOW GRANTS FOR 'jeffrey'@'%' と同等
 
 # ロールの権限を表示
 SHOW GRANTS FOR 'app_role';
 
 # ユーザが特定のロールを使用した場合の権限を表示
 SHOW GRANTS FOR 'jeffrey'@'localhost' USING 'app_role';


MySQL 8.0以降では、ALL PRIVILEGES ではなく、明示的な権限リストが表示される。
例えば、GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO '<ユーザ名>'@'<IPアドレス または ホスト名>' のように表示される。

部分的リボーク (Partial Revokes) が有効な場合、REVOKE 文も出力に含まれる。

 # 部分的リボークの例
 GRANT SELECT ON *.* TO 'user1'@'localhost';
 REVOKE SELECT ON sensitive_db.* FROM 'user1'@'localhost';
 
 SHOW GRANTS FOR 'user1'@'localhost';
 
 # 出力例
 +--------------------------------------------------------------+
 | Grants for user1@localhost                                   |
 +--------------------------------------------------------------+
 | GRANT SELECT ON *.* TO `user1`@`localhost`                   |
 | REVOKE SELECT ON `sensitive_db`.* FROM `user1`@`localhost`   |
 +--------------------------------------------------------------+


下表に、権限の種類を示す。

MySQLの権限レベル
権限レベル 構文 説明
グローバル権限 GRANT ... ON *.* サーバ全体の権限
データベース権限 GRANT ... ON db_name.* 特定のデータベースの権限
テーブル権限 GRANT ... ON db_name.tbl_name 特定のテーブルの権限
カラム権限 GRANT SELECT (col1, col2) ON db_name.tbl_name 特定のカラムの権限



警告・エラー情報の参照

SHOW WARNINGS

SHOW WARNINGS 文は、直前に実行された文で生成された警告、エラー、ノートを表示する。

構文を以下に示す。

 SHOW WARNINGS [LIMIT [offset,] row_count]
 SHOW COUNT(*) WARNINGS


下表に、出力カラムを示す。

SHOW WARNINGS 出力カラム
カラム名 説明
Level メッセージのレベル (Warning、Error、Note)
Code エラーコード (数値)
Message メッセージテキスト


実行例を以下に示す。

 # 警告を生成するクエリを実行
 SELECT 'abc' + 10;
 
 # 警告を表示
 SHOW WARNINGS;
 
 # 出力例
 +---------+------+---------------------------------------+
 | Level   | Code | Message                               |
 +---------+------+---------------------------------------+
 | Warning | 1292 | Truncated incorrect DOUBLE value: 'abc'|
 +---------+------+---------------------------------------+
 
 # 警告の総数を表示
 SHOW COUNT(*) WARNINGS;
 
 # 出力例
 +---------+
 | @@session.warning_count |
 +---------+
 |       1 |
 +---------+
 
 # または、変数を直接参照
 SELECT @@warning_count;
 
 # LIMITで表示数を制限
 SHOW WARNINGS LIMIT 5;
 
 # データ型の暗黙的変換による警告
 CREATE TABLE test (a INT);
 INSERT INTO test VALUES ('100abc');
 SHOW WARNINGS;
 
 # 出力例
 +---------+------+---------------------------------------+
 | Level   | Code | Message                               |
 +---------+------+---------------------------------------+
 | Warning | 1265 | Data truncated for column 'a' at row 1|
 +---------+------+---------------------------------------+


警告の保存と設定を以下に示す。

警告の保存と設定
設定項目 説明
max_error_count システム変数 保存されるメッセージの最大数 (デフォルト: 1024)
この値を超えるメッセージは破棄される。
sql_notes システム変数 ON (デフォルト): ノートがwarning_countをインクリメントする。
OFF: ノートが無視される。


警告をクリアする方法を以下に示す。
警告は、次の文が実行されると自動的にクリアされる。

SHOW ERRORS

SHOW ERRORS 文は、直前に実行された文で生成されたエラーのみを表示する。
警告やノートは表示されない。

構文を以下に示す。

 SHOW ERRORS [LIMIT [offset,] row_count]
 SHOW COUNT(*) ERRORS


実行例を以下に示す。

 # エラーを生成するクエリを実行
 SELECT * FROM nonexistent_table;
 
 # エラーを表示
 SHOW ERRORS;
 
 # 出力例
 +-------+------+----------------------------------------+
 | Level | Code | Message                                |
 +-------+------+----------------------------------------+
 | Error | 1146 | Table 'myapp.nonexistent_table' doesn't exist |
 +-------+------+----------------------------------------+
 
 # エラーの総数を表示
 SHOW COUNT(*) ERRORS;
 
 # 出力例
 +---------+
 | @@session.error_count |
 +---------+
 |       1 |
 +---------+
 
 # または、変数を直接参照
 SELECT @@error_count;


SHOW WARNINGS との違いを以下に示す。

  • SHOW WARNINGS
    警告、エラー、ノートの全てを表示する。
  • SHOW ERRORS
    エラーのみを表示する。(警告やノートは除外)



エンジン情報の参照

SHOW ENGINES

SHOW ENGINES 文は、サーバが認識している全てのストレージエンジンを表示する。

構文を以下に示す。

 SHOW [STORAGE] ENGINES


下表に、出力カラムを示す。

SHOW ENGINES 出力カラム
カラム名 説明
Engine ストレージエンジン名
Support サポート状況 (YES、DEFAULT、NO、DISABLED)
Comment エンジンの説明
Transactions トランザクションサポート (YES / NO)
XA XAトランザクションサポート (YES / NO)
Savepoints セーブポイントサポート (YES / NO)


実行例を以下に示す。

 # 全てのストレージエンジンを表示
 SHOW ENGINES;
 
 # 出力例
 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
 | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
 | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
 | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
 | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
 | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
 | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
 | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
 | BLACKHOLE          | YES     | /dev/null storage engine (anything you write disappears)       | NO           | NO   | NO         |
 | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
 | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+


Support カラムの値の意味を以下に示す。

Supportカラムの値の意味
値 説明
YES エンジンはサポートされており、使用可能である。
DEFAULT デフォルトのストレージエンジンである。
NO エンジンはコンパイルされていないか、サポートされていない。
DISABLED エンジンはコンパイルされているが、無効化されている。


デフォルトのストレージエンジンを確認する方法を以下に示す。

 # デフォルトエンジンを確認
 SHOW VARIABLES LIKE 'default_storage_engine';
 
 # または
 SELECT @@default_storage_engine;


SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS 文は、InnoDBストレージエンジンの詳細な内部状態を表示する。
デバッグやパフォーマンスチューニングに使用される。

構文を以下に示す。

 SHOW ENGINE INNODB STATUS


PROCESS 権限が必要である。

実行例を以下に示す。

 # InnoDBの内部状態を表示
 SHOW ENGINE INNODB STATUS\G
 
 # 出力例 (非常に長いため一部のみ表示)
 *************************** 1. row ***************************
   Type: InnoDB
   Name:
 Status:
 =====================================
 2024-01-15 10:30:45 0x7f8a8c000700 INNODB MONITOR OUTPUT
 =====================================
 Per second averages calculated from the last 47 seconds
 -----------------
 BACKGROUND THREAD
 -----------------
 srv_master_thread loops: 12345 srv_active, 0 srv_shutdown, 123456 srv_idle
 srv_master_thread log flush and writes: 123456
 ----------
 SEMAPHORES
 ----------
 OS WAIT ARRAY INFO: reservation count 12345
 OS WAIT ARRAY INFO: signal count 12345
 RW-shared spins 123456, rounds 234567, OS waits 3456
 RW-excl spins 12345, rounds 23456, OS waits 345
 Spin rounds per wait: 1.90 RW-shared, 1.90 RW-excl
 ------------------------
 LATEST DETECTED DEADLOCK
 ------------------------
 (デッドロック情報が表示される)
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 123456
 Purge done for trx's n:o < 123455 undo n:o < 0 state: running
 History list length 12
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 421234567890123, ACTIVE 0 sec
 (トランザクション情報が表示される)
 --------
 FILE I/O
 --------
 I/O thread 0 state: waiting for completed aio requests
 (I/O統計が表示される)
 -----------------------------
 INSERT BUFFER AND ADAPTIVE HASH INDEX
 -----------------------------
 (挿入バッファとアダプティブハッシュインデックスの統計が表示される)
 ---
 LOG
 ---
 Log sequence number 123456789
 Log flushed up to   123456789
 (ログ情報が表示される)
 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total large memory allocated 1073741824
 Dictionary memory allocated 12345
 Buffer pool size   65536
 Free buffers       12345
 Database pages     53191
 (バッファプール統計が表示される)


主要な出力セクションを以下に示す。

主要な出力セクション
セクション名 説明
SEMAPHORES セマフォとロックの統計
LATEST DETECTED DEADLOCK 最後に検出されたデッドロックの詳細
TRANSACTIONS 実行中のトランザクション情報
FILE I/O ファイルI/O統計
INSERT BUFFER AND ADAPTIVE HASH INDEX 挿入バッファとアダプティブハッシュインデックスの統計
LOG REDOログ情報
BUFFER POOL AND MEMORY バッファプールとメモリ使用状況


デッドロックのデバッグやパフォーマンス問題の診断に便利である。
特に、LATEST DETECTED DEADLOCK セクションは、デッドロックの原因を特定するために重要である。


レプリケーション情報の参照

SHOW BINARY LOGS

SHOW BINARY LOGS 文は、サーバ上のバイナリログファイルの一覧を表示する。
SHOW MASTER LOGS は同義語である。

構文を以下に示す。

 SHOW BINARY LOGS
 SHOW MASTER LOGS


REPLICATION CLIENT 権限が必要である。

下表に、出力カラムを示す。

SHOW BINARY LOGS 出力カラム
カラム名 説明
Log_name バイナリログファイル名
File_size ファイルサイズ (バイト)
Encrypted 暗号化されているか (YES / NO、MySQL 8.0.14以降)


実行例を以下に示す。

 # 全てのバイナリログを表示
 SHOW BINARY LOGS;
 
 # 出力例
 +-------------------+-----------+-----------+
 | Log_name          | File_size | Encrypted |
 +-------------------+-----------+-----------+
 | mysql-bin.000001  | 177       | No        |
 | mysql-bin.000002  | 156789    | No        |
 | mysql-bin.000003  | 234567    | No        |
 | mysql-bin.000004  | 345678    | No        |
 +-------------------+-----------+-----------+


バイナリログの管理に有用である。
古いログファイルを削除する際に、どのファイルが存在するかを確認できる。

SHOW MASTER STATUS / SHOW BINARY LOG STATUS

SHOW MASTER STATUS 文は、ソースサーバ (マスターサーバ) のバイナリログ情報を表示する。
MySQL 8.4.0以降では、SHOW BINARY LOG STATUS に名称が変更された。

構文を以下に示す。

 SHOW MASTER STATUS
 SHOW BINARY LOG STATUS  -- MySQL 8.4.0以降


下表に、出力カラムを示す。

SHOW MASTER STATUS 出力カラム
カラム名 説明
File 現在のバイナリログファイル名
Position 現在のバイナリログ位置 (バイト)
Binlog_Do_DB レプリケーション対象のデータベース (binlog-do-db設定)
Binlog_Ignore_DB レプリケーション除外のデータベース (binlog-ignore-db設定)
Executed_Gtid_Set 実行されたGTIDセット (GTIDモード有効時)


実行例を以下に示す。

 # ソースサーバのバイナリログ情報を表示
 SHOW MASTER STATUS;
 
 # 出力例
 +-------------------+----------+--------------+------------------+-------------------------------------------+
 | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
 +-------------------+----------+--------------+------------------+-------------------------------------------+
 | mysql-bin.000004  | 234567   |              |                  | 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5 |
 +-------------------+----------+--------------+------------------+-------------------------------------------+


レプリカサーバを設定する時に、ソースサーバのバイナリログ位置を確認するために使用される。

SHOW SLAVE STATUS / SHOW REPLICA STATUS

SHOW SLAVE STATUS 文は、レプリカサーバ (スレーブサーバ) のレプリケーション状態を表示する。
MySQL 8.0.22以降では、SHOW REPLICA STATUS に名称が変更された。

構文を以下に示す。

 SHOW {REPLICA | SLAVE} STATUS [FOR CHANNEL channel]


下表に、主要な出力カラムを示す。

SHOW REPLICA STATUS 主要出力カラム
カラム名 説明
Replica_IO_State I/Oスレッドの状態
Source_Host ソースサーバのホスト名
Source_Port ソースサーバのポート番号
Source_User レプリケーション用ユーザ名
Source_Log_File 現在読み取り中のソースバイナリログファイル
Read_Source_Log_Pos ソースバイナリログの読み取り位置
Relay_Log_File 現在のリレーログファイル
Relay_Log_Pos リレーログの位置
Exec_Source_Log_Pos 実行済みのソースバイナリログ位置
Replica_IO_Running I/Oスレッドが実行中か (Yes / No / Connecting)
Replica_SQL_Running SQLスレッドが実行中か (Yes / No)
Last_Error 最後のエラーメッセージ
Last_Errno 最後のエラー番号
Last_IO_Error I/Oスレッドの最後のエラー
Last_SQL_Error SQLスレッドの最後のエラー
Seconds_Behind_Source ソースサーバからの遅延時間 (秒)
Relay_Log_Space リレーログの合計サイズ (バイト)
SQL_Delay 意図的な遅延 (秒)
Retrieved_Gtid_Set 取得されたGTIDセット
Executed_Gtid_Set 実行されたGTIDセット
Auto_Position GTID自動位置決定が有効か (1 / 0)


実行例を以下に示す。

 # レプリカサーバのレプリケーション状態を表示
 SHOW REPLICA STATUS\G
 
 # 出力例 (一部のみ)
 *************************** 1. row ***************************
                Replica_IO_State: Waiting for source to send event
                   Source_Host: 192.168.1.100
                   Source_User: repl
                   Source_Port: 3306
                 Connect_Retry: 60
               Source_Log_File: mysql-bin.000004
           Read_Source_Log_Pos: 234567
                Relay_Log_File: relay-bin.000002
                 Relay_Log_Pos: 123456
         Relay_Source_Log_File: mysql-bin.000004
              Replica_IO_Running: Yes
             Replica_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Source_Log_Pos: 234500
               Relay_Log_Space: 123789
               Until_Condition: None
          Seconds_Behind_Source: 0
                   Last_IO_Errno: 0
                   Last_IO_Error:
                  Last_SQL_Errno: 0
                  Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Source_Server_Id: 1
                   Source_UUID: 3e11fa47-71ca-11e1-9e33-c80aa9429562
              Source_Info_File: mysql.slave_master_info
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
              Source_Retry_Count: 86400
                   Auto_Position: 1
            Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
             Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5

 # マルチソースレプリケーションの場合、チャネル名を指定
 SHOW REPLICA STATUS FOR CHANNEL 'channel_name';


レプリケーション状態の確認に不可欠である。
特に、以下のカラムを重点的に確認する。

  • Replica_IO_Running と Replica_SQL_Running
    両方がYesであることを確認する。Noの場合、レプリケーションが停止している。
  • Seconds_Behind_Source
    ソースサーバからの遅延時間。0に近いほど良好。
  • Last_Error、Last_IO_Error、Last_SQL_Error
    エラーが発生していないか確認する。
    エラーがある場合、その内容を確認して対処する。



LIKE句とWHERE句による絞り込み

多くの SHOW 文は、LIKE 句 または WHERE 句による結果の絞り込みをサポートしている。

LIKE句

LIKE 句は、シンプルなパターンマッチングによる絞り込みを提供する。

ワイルドカードを以下に示す。

  • %
    任意の文字列 (0文字以上)
  • _
    任意の1文字


エスケープ文字を以下に示す。

  • デフォルトのエスケープ文字は、\ である。
  • \% は文字としての %、\_ は文字としての _ を意味する。
  • ESCAPE 句でエスケープ文字を変更できる。


実行例を以下に示す。

LIKE 句は、デフォルトで大文字小文字を区別しない。(照合順序に依存)

 # 'user'で始まるテーブルを表示
 SHOW TABLES LIKE 'user%';
 
 # 'tmp'を含むテーブルを表示
 SHOW TABLES LIKE '%tmp%';
 
 # 'log'で終わるテーブルを表示
 SHOW TABLES LIKE '%log';
 
 # 'user'の後に任意の1文字が続くテーブルを表示
 SHOW TABLES LIKE 'user_';
 
 # 'max'を含むシステム変数を表示
 SHOW VARIABLES LIKE '%max%';
 
 # 'innodb'で始まる変数を表示
 SHOW VARIABLES LIKE 'innodb%';
 
 # 'Key'で始まるステータス変数を表示
 SHOW STATUS LIKE 'Key%';
 
 # エスケープ文字の使用例
 # テーブル名に '_' を含むテーブルを検索
 SHOW TABLES LIKE '%\_%';
 
 # エスケープ文字を変更
 SHOW TABLES LIKE '%!_%' ESCAPE '!';


WHERE句

WHERE 句は、より複雑な条件による絞り込みを提供する。

下表に、使用可能な演算子を示す。

使用可能な演算子
演算子の種類 演算子
比較演算子 =、<>、<、>、<=、>=、<=>
論理演算子 AND、OR、NOT
その他の演算子 BETWEEN、IN、IS NULL、IS NOT NULL、LIKE


実行例を以下に示す。

 # VIEWのみを表示
 SHOW FULL TABLES WHERE Table_type = 'VIEW';
 
 # InnoDBテーブルのみを表示
 SHOW TABLE STATUS WHERE Engine = 'InnoDB';
 
 # 1週間以内に更新されたテーブルを表示
 SHOW TABLE STATUS WHERE Update_time > DATE_SUB(NOW(), INTERVAL 7 DAY);
 
 # サイズが100MBを超えるテーブルを表示
 SHOW TABLE STATUS WHERE Data_length + Index_length > 100 * 1024 * 1024;
 
 # 'user'または'order'を含むテーブルを表示
 SHOW TABLES WHERE Tables_in_myapp LIKE '%user%' OR Tables_in_myapp LIKE '%order%';
 
 # UNIQUEインデックスのみを表示
 SHOW INDEX FROM users WHERE Non_unique = 0;
 
 # 実行時間が30秒を超えるプロセスを表示 (performance_schema使用)
 SELECT * FROM performance_schema.processlist
 WHERE COMMAND = 'Query' AND TIME > 30;
 
 # 'buffer'を含むシステム変数を表示
 SHOW VARIABLES WHERE Variable_name LIKE '%buffer%';
 
 # INを使用した複数値の条件
 SHOW VARIABLES WHERE Variable_name IN ('max_connections', 'wait_timeout', 'innodb_buffer_pool_size');


WHERE 句は、カラム名を正確に指定する必要がある。
カラム名にバッククォートが必要な場合もある。(例: WHERE `Database` LIKE '%app%')

使い分けの指針

LIKE 句と WHERE 句の使い分けを以下に示す。

LIKE 句を使用すべき場合を以下に示す。

  • シンプルなパターンマッチング (前方一致、部分一致、後方一致) のみが必要な場合
    例: SHOW TABLES LIKE 'user%'
  • 対話的な確認作業で、素早く絞り込みたい場合
  • 条件が1つのカラムに対する1つのパターンのみの場合


WHERE 句を使用すべき場合を以下に示す。

  • 複数の条件を組み合わせる必要がある場合
    例: WHERE Engine = 'InnoDB' AND Data_length > 1000000
  • 数値の比較、日付の比較、範囲検索が必要な場合
    例: WHERE Update_time > '2024-01-01'
  • 論理演算 (AND、OR) が必要な場合
    例: WHERE Table_type = 'VIEW' OR Engine = 'MEMORY'
  • IN、BETWEEN、IS NULL等の高度な条件が必要な場合
    例: WHERE Variable_name IN ('max_connections', 'wait_timeout')


パフォーマンスでは、LIKE 句も WHERE 句もほぼ同等である。
ただし、WHERE 句の方が柔軟性が高いため、複雑な条件では WHERE 句を推奨する。


INFORMATION_SCHEMAとの対応

INFORMATION_SCHEMAの概要

INFORMATION_SCHEMA は、MySQLサーバのメタデータにアクセスするための仮想データベースである。
データベース、テーブル、カラム、権限等の情報をテーブル形式で提供する。

下表に、INFORMATION_SCHEMAの特徴を示す。

INFORMATION_SCHEMAの特徴
特徴 説明
仮想データベース ディスク上に実際のファイルは存在せず、サーバのメタデータから動的に生成される。
標準SQL準拠 ANSI SQL標準のINFORMATION_SCHEMAに準拠している。(一部MySQLの拡張あり)
読み取り専用 SELECT文のみ可能。INSERT、UPDATE、DELETE文は使用できない。
柔軟なクエリ WHERE句、JOIN句、ORDER BY句、GROUP BY句等のSQL構文を自由に使用できる。


MySQL 5.7.6以降では、パフォーマンス向上のため、performance_schema データベースのテーブル使用を推奨する。
特に、SHOW VARIABLESやSHOW STATUSの代わりに、
performance_schema.global_variables や performance_schema.global_status を使用することを推奨する。

SHOW文とINFORMATION_SCHEMAの対応表

下表に、主要な SHOW 文と対応する INFORMATION_SCHEMA テーブルを示す。

SHOW文とINFORMATION_SCHEMA対応表
SHOW文 INFORMATION_SCHEMAテーブル performance_schemaテーブル
SHOW DATABASES INFORMATION_SCHEMA.SCHEMATA -
SHOW TABLES INFORMATION_SCHEMA.TABLES -
SHOW COLUMNS INFORMATION_SCHEMA.COLUMNS -
SHOW INDEX INFORMATION_SCHEMA.STATISTICS -
SHOW TABLE STATUS INFORMATION_SCHEMA.TABLES -
SHOW VARIABLES INFORMATION_SCHEMA.GLOBAL_VARIABLES
INFORMATION_SCHEMA.SESSION_VARIABLES		 performance_schema.global_variables
performance_schema.session_variables
SHOW STATUS INFORMATION_SCHEMA.GLOBAL_STATUS
INFORMATION_SCHEMA.SESSION_STATUS		 performance_schema.global_status
performance_schema.session_status
SHOW PROCESSLIST INFORMATION_SCHEMA.PROCESSLIST performance_schema.processlist
SHOW GRANTS INFORMATION_SCHEMA.USER_PRIVILEGES
INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
INFORMATION_SCHEMA.TABLE_PRIVILEGES
INFORMATION_SCHEMA.COLUMN_PRIVILEGES		 -
SHOW ENGINES INFORMATION_SCHEMA.ENGINES -
SHOW PLUGINS INFORMATION_SCHEMA.PLUGINS -


MySQL 8.0以降では、performance_schema テーブルの使用を強く推奨する。
INFORMATION_SCHEMA のシステム変数・状態変数テーブルは、内部的に performance_schema にリダイレクトされている。

INFORMATION_SCHEMAを使用したクエリ例

INFORMATION_SCHEMA を使用した実践的なクエリ例を以下に示す。

 # 特定のデータベース内のテーブル一覧と行数を表示
 SELECT TABLE_NAME, ENGINE, TABLE_ROWS,
        ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'myapp_db'
 ORDER BY size_mb DESC;
 
 # 出力例
 +-------------+--------+------------+---------+
 | TABLE_NAME  | ENGINE | TABLE_ROWS | size_mb |
 +-------------+--------+------------+---------+
 | orders      | InnoDB | 1234567    | 245.67  |
 | order_items | InnoDB | 5678901    | 189.34  |
 | users       | InnoDB | 123456     | 12.34   |
 | products    | InnoDB | 45678      | 5.67    |
 +-------------+--------+------------+---------+
 
 # 特定のテーブルのカラム情報を表示
 SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'myapp_db' AND TABLE_NAME = 'users'
 ORDER BY ORDINAL_POSITION;
 
 # 出力例
 +-------------+--------------+-------------+------------+-------------------+-------------------+
 | COLUMN_NAME | DATA_TYPE    | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT    | EXTRA             |
 +-------------+--------------+-------------+------------+-------------------+-------------------+
 | id          | int          | NO          | PRI        | NULL              | auto_increment    |
 | username    | varchar      | NO          | UNI        | NULL              |                   |
 | email       | varchar      | NO          |            | NULL              |                   |
 | status      | varchar      | YES         | MUL        | active            |                   |
 | created_at  | datetime     | YES         |            | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
 +-------------+--------------+-------------+------------+-------------------+-------------------+
 
 # 特定のテーブルのインデックス構造を表示
 SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE, INDEX_TYPE
 FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA = 'myapp_db' AND TABLE_NAME = 'orders'
 ORDER BY INDEX_NAME, SEQ_IN_INDEX;
 
 # 出力例
 +---------------+-------------+--------------+------------+------------+
 | INDEX_NAME    | COLUMN_NAME | SEQ_IN_INDEX | NON_UNIQUE | INDEX_TYPE |
 +---------------+-------------+--------------+------------+------------+
 | PRIMARY       | id          |            1 |          0 | BTREE      |
 | idx_user_date | user_id     |            1 |          1 | BTREE      |
 | idx_user_date | order_date  |            2 |          1 | BTREE      |
 | idx_status    | status      |            1 |          1 | BTREE      |
 +---------------+-------------+--------------+------------+------------+
 
 # ストレージエンジンごとのテーブル数とサイズ統計を表示
 SELECT ENGINE,
        COUNT(*) AS table_count,
        ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_size_mb
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'myapp_db'
 GROUP BY ENGINE;
 
 # 出力例
 +--------+-------------+---------------+
 | ENGINE | table_count | total_size_mb |
 +--------+-------------+---------------+
 | InnoDB |          15 | 452.34        |
 | MEMORY |           2 | 0.12          |
 +--------+-------------+---------------+
 
 # AUTO_INCREMENTの現在値と最大値を表示
 SELECT TABLE_NAME, AUTO_INCREMENT,
        COLUMN_TYPE,
        CASE DATA_TYPE
           WHEN 'tinyint' THEN 255
           WHEN 'smallint' THEN 65535
           WHEN 'mediumint' THEN 16777215
           WHEN 'int' THEN 4294967295
           WHEN 'bigint' THEN 18446744073709551615
        END AS max_value,
        ROUND(AUTO_INCREMENT / (
           CASE DATA_TYPE
              WHEN 'tinyint' THEN 255
              WHEN 'smallint' THEN 65535
              WHEN 'mediumint' THEN 16777215
              WHEN 'int' THEN 4294967295
              WHEN 'bigint' THEN 18446744073709551615
           END
        ) * 100, 2) AS usage_percent
 FROM INFORMATION_SCHEMA.TABLES t
 JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
 WHERE t.TABLE_SCHEMA = 'myapp_db'
    AND t.AUTO_INCREMENT IS NOT NULL
    AND c.EXTRA = 'auto_increment';
 
 # グローバル変数を取得 (performance_schema使用)
 SELECT VARIABLE_NAME, VARIABLE_VALUE
 FROM performance_schema.global_variables
 WHERE VARIABLE_NAME IN ('max_connections', 'innodb_buffer_pool_size', 'query_cache_size');
 
 # 出力例
 +--------------------------+----------------+
 | VARIABLE_NAME            | VARIABLE_VALUE |
 +--------------------------+----------------+
 | innodb_buffer_pool_size  | 134217728      |
 | max_connections          | 151            |
 | query_cache_size         | 1048576        |
 +--------------------------+----------------+
 
 # 実行中のクエリを取得 (performance_schema使用)
 SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST,
        PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME,
        PROCESSLIST_STATE, PROCESSLIST_INFO
 FROM performance_schema.processlist
 WHERE PROCESSLIST_COMMAND = 'Query'
 ORDER BY PROCESSLIST_TIME DESC;

 # 外部キー制約の情報を表示
 SELECT TABLE_NAME, COLUMN_NAME,
        REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME,
        CONSTRAINT_NAME
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE TABLE_SCHEMA = 'myapp_db'
    AND REFERENCED_TABLE_NAME IS NOT NULL;
 
 # 出力例
 +-------------+-------------+-----------------------+------------------------+------------------+
 | TABLE_NAME  | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | CONSTRAINT_NAME  |
 +-------------+-------------+-----------------------+------------------------+------------------+
 | orders      | user_id     | users                 | id                     | fk_orders_user   |
 | order_items | order_id    | orders                | id                     | fk_items_order   |
 | order_items | product_id  | products              | id                     | fk_items_product |
 +-------------+-------------+-----------------------+------------------------+------------------+


INFORMATION_SCHEMA を使用するメリットを以下に示す。

INFORMATION_SCHEMAを使用するメリット
メリット 説明
複雑なフィルタリング WHERE句で複数の条件を組み合わせることができる。
集計とグループ化 COUNT、SUM、AVG等の集計関数とGROUP BY句を使用できる。
結合 複数のテーブルをJOINして、関連する情報を一度に取得できる。
ソートと制限 ORDER BY句とLIMIT句で、結果を自由にソート・制限できる。
プログラム連携 アプリケーションから標準的なSELECT文として実行できる。


統計キャッシングの注意事項を以下に示す。

  • INFORMATION_SCHEMA.TABLES の統計情報 (TABLE_ROWS、DATA_LENGTH、INDEX_LENGTH等) は、キャッシュされる。
  • information_schema_stats_expiry システム変数 (デフォルト: 86400秒 = 24時間) により、キャッシュの有効期間が制御される。


最新の統計情報を取得する方法を以下に示す。

  • ANALYZE TABLE 文を実行する。
    テーブルの統計情報を更新する。
  • information_schema_stats_expiry = 0 に設定する。
    統計キャッシュを無効化し、常に最新の情報を取得する。(パフォーマンス低下に注意)


 # 統計情報を更新
 ANALYZE TABLE users, orders, products;
 
 # 統計キャッシュを無効化 (セッションレベル)
 SET SESSION information_schema_stats_expiry = 0;
 
 # 再度テーブル情報を取得
 SELECT TABLE_NAME, TABLE_ROWS
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'myapp_db';



「https://mochiu.net/index.php?title=MySQL_-_情報参照&oldid=14312」から取得