MySQLパフォーマンスチューニングとは〜パフォーマンス測定（実行計画の確認EXPLAINコマンド）〜

こんにちは。この記事では、MySQLのパフォーマンスチューニングの前に実施するパフォーマンス測定のうち、実行計画の確認で利用するEXPLAINコマンドについて紹介します。

MySQLの実行計画は、SQLクエリの実行方法を詳細に示す重要なツールです。
以下に実行計画の概要と主要なポイントをまとめます。

パフォーマンスチューニングの概要、スロークエリの特定方法は以下の記事で紹介しておりますので、ご参考ください。

実行計画とは
EXPLAINコマンドの利用
1. 基本的なコマンド文法
2. コマンドの出力結果例と見方
まとめ

実行計画とは

実行計画は、MySQLがSQLクエリをどのように処理するかを示す「実行手順書」のようなものです。
これにより、以下のような情報を得ることができます。

インデックスの使用状況
テーブルのスキャン方法
結合の順序と方法
データアクセスの効率性

EXPLAINコマンドの利用

実行計画の取得はEXPLAINコマンドを利用します。
EXPLAINコマンドは、MySQLのクエリ実行計画を分析するための強力なツールです。

スロークエリを確認し、疑わしいクエリに対してEXPLAINコマンドを実行することでインデックスが利用されていないなどの問題の特定に役立ちます。

以下にEXPLAINコマンドの主な特徴と使用方法を説明します。

基本的なコマンド文法

クエリの前に「EXPLAIN」をつけて実行します。

EXPLAIN クエリ文;

コマンドの出力結果例と見方

EXPLAINコマンドの実行結果の例とその見方を紹介します。

実行環境は以下の通りです。

OS	CentOS Stream release 9
MySQL	8.4.2 MySQL Community Server
データベース名	my_database
テーブル名	users

テーブルusersのカラム情報は以下の通りで、２０件のデータを格納しています。

mysql> desc users;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| user_id    | int         | NO   | PRI | NULL    |       |
| name       | varchar(50) | YES  |     | NULL    |       |
| age        | int         | YES  | MUL | NULL    |       |
| birthplace | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

mysql>

テーブルusersに対して、全データのうち、出身地（birthplace）がtokyoのユーザをSELECTする文のEXPLAINコマンドを実行してみます。

実行結果は以下の通りです。

mysql> EXPLAIN select * from users where birthplace='tokyo';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

各列の情報は以下の通りです。

列名	説明
id	SELECT識別子。クエリ内の各SELECT文に割り当てられる一意の番号です。
select_type	SELECT文の種類を示します。 SIMPLE、PRIMARY、UNION、SUBQUERY等があります。・SIMPLE：サブクエリやUNIONを含まない単純なSELECT文を示します。・SUBQUERY：FROM句以外で使用されるサブクエリを示します。・DERIVED：FROM句内のサブクエリ(導出テーブル)を示します。・UNION：UNIONの2番目以降のSELECT文を示します。・UNION RESULT：UNIONの結果を示します。・DEPENDENT SUBQUERY：外部クエリに依存するサブクエリを示します。・DEPENDENT UNION：UNIONの2番目以降のSELECT文で、外部クエリに依存するものを示します。・MATERIALIZED：実体化されたサブクエリを示します。・UNCACHEABLE SUBQUERY：結果をキャッシュできないサブクエリを示します。・UNCACHEABLE UNION：UNIONの結果をキャッシュできない場合に表示されます。
table	出力行が参照するテーブルの名前です。
partitions	アクセスされるパーティションです。パーティショニングされたテーブルの場合に表示されます。 ※パーティショニング：1つの大きなテーブルを複数の小さな物理的な部分(パーティション)に分割すること
type	アクセス方法を示します。主な値としては以下の通りです。 const, eq_ref, ref, range, indexなどが望ましく、ALLは避けるべきです。・const: インデックスを使用した一意な行の取得・eq_ref: 一意インデックスを使用した結合・ref: 非一意インデックスを使用した結合・range: インデックスを使用した範囲検索・index: インデックス全体のスキャン・ALL: フルテーブルスキャン (最も非効率)
possible_keys	使用可能なインデックスのリストを示します。
key	使用されているインデックスを示します。 NULLの場合はインデックスが使用されていません。
key_len	使用されているインデックスの長さを示します。短いほど効率的です。
ref	インデックスと比較されるカラムまたは定数を示します。
rows	検査される行数の見積もりを示します。少ないほど効率的です。
filtered	テーブル条件によってフィルタリングされる行の推定割合です。 0から100までの値を取ります。 100が最大値で、これは行のフィルタリングが行われなかったことを意味します。 100に近いほど、データの取得が効率的であることを示します。
Extra	追加情報を提供します。 “Using index”: カバリングインデックスが使用されている (良い兆候) “Using filesort”: ソートにファイルが使用されている (要注意) “Using temporary”: 一時テーブルが使用されている (要注意)

まとめ

今回はMySQLのパフォーマンスチューニングを紹介しました。

実行計画の分析は、SQLクエリのパフォーマンス改善に不可欠なスキルです。

定期的に実行計画を確認し、必要に応じてクエリやインデックスを最適化することで、データベースの効率を大幅に向上させることができます。

ご参考になれば、幸いです。

以上になります。ありがとうございました。

参考

https://zenn.dev/yuulab/articles/f415920220acf9
https://qiita.com/fumiya_sasaki_9933/items/f13789a3849f890eda04

実行計画とは

EXPLAINコマンドの利用

基本的なコマンド文法

コマンドの出力結果例と見方

まとめ

参考

コメント