🐙

MySQL SQLパフォーマンス改善 -実行計画の確認ポイント-

2022/10/05に公開

MySQL

SQL

RDB

tech

本記事の目的

本記事では MySQL(InnoDB) の SQL 性能調査・改善のために、実行計画の主要な確認ポイントを整理します。また、SQL の重要な要素であるインデックスについても基本を簡単におさらいします。

なお、MySQL 実行計画の各種項目の意味は公式サイトに記載があるが、ぱっと見た時にどれが SQL の性能において重要度が高いのか少しわかりずらかったりしたので、今後のためにも記事に残すことにしました。

SQL パフォーマンスまわりで参考になった書籍や記事も最後に紹介していますので、ぜひそちらも合わせて参考にしてみてください。

インデックスとは

インデックスはデータベースの中で特有の構造を持ち、CREATE INDEX 文で作成可能です。
SQL の性能に関わる重要な要素です。

データベースのインデックスは、分厚い紙の辞書から特定の用語を検索することに似ています。あらかじめ順序づけられているため（辞書であれば五十音順など）、特定の用語を短時間で見つけることを可能とします。
もし辞書の中身の並びがランダムだった場合、特定の用語を検索するためには 1 ページ 1 ページすべて確認する必要があり、それが大変なことは言うまでもないですね。。（これがいわゆる全探索）

つまりインデックスの主目的は、データに対して順序づけを行い、アクセスを高速化することにあります。大量データに対してアクセスする際は、インデックスを利用できるように SQL を設計しなければ、データ量の増加ともに重大な性能劣化を引き起こすことになります。

設計の際は、検索条件（WHERE 句条件）に使用されるカラムにインデックスを作成し、また、インデックスを利用できない SQL は極力避けるようにすることが大切です。

では、インデックスが SQL で使用されているかどうかをどのように確認できるのでしょうか？
そのためには以降で説明する実行計画を確認する必要があります。実行計画の見方を学び、SQL の性能を評価できるようになりましょう。

なお、MySQL のインデックス基礎知識は、cookpad 様の以下ブログが分かりやすいです。

実行計画とは

「実行計画」は、SQL を実行するためにどのような手順を RDB が選択したを確認するものです。
これを確認することで、インデックスを使用したのか、テーブルをフルキャンしたのか、結合時にはどのような条件を使用したのかなどを知ることができます。

もし、SQL 絡みの性能改善の要望が上がってきたら、SQL を確認しつつまずは実行計画を確認することになると思います。
※アプリ側で多重ループしてたりなど、SQL に問題ない可能性もありますが、それはここでは触れないこととします。

MySQL 実行計画の取得

前段が長くなりましたが、MySQL の実行計画を取得していきましょう。
ここではテーブル「demo」が存在していると仮定し、適当なデータを少し登録しておきます。

MySQL では実行計画を取得したい SQL の先頭に、EXPLAINを付与して実行計画を取得します。
以下前提テーブルを作成し、主キーを条件にデータを取得する SQL の実行計画を確認します。

★ 事前準備

create database sample character set UTF8 collate utf8_bin;

create table sample.demo (
  id int not null primary key,
  name varchar(10),
  age int,
  gender varchar(10),
  hobby varchar(32)
);

create index idx_demo_01 on sample.demo (age);

insert into sample.demo values (1, 'taro', 24, 'male', 'cooking');
insert into sample.demo values (2, 'jiro', 18, 'male', 'baseball');
insert into sample.demo values (3, 'hanako', 15, 'female', 'piano');

★ 実行計画の取得

explain select * from sample.demo where id = 1;

★ 実行計画取得結果

mysql> explain select * from sample.demo where id = 1;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | demo  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

無事実行計画を取得できました！！

💡TIPS
ちなみに統計情報の更新は以下の通り。
統計情報が古いとオプティマイザが古い情報から実行計画を作成するので、場合によっては想定外に性能が落ちる可能性があります。
特にテーブルに大きな変更（登録や更新など）が入った場合は、統計情報を更新する必要があります。

ANALYZE TABLE sample.demo;

MySQL 実行計画内容の確認

実行計画の取得はできましたが、中身の理解が難しいですね。重要な部分を中心に確認していきましょう。
なお、MySQL のリファレンスマニュアルに各カラムの意味が記載あるので、公式わかる人はこっちを熟読しましょう。何事もまずは公式サイトです。

さて、実行計画出力結果の各項目は公式サイトにあるように次の通りです。

列名	JSON 名	意味
`id`	select_id	SELECT 識別子
`select_type`	なし	SELECT 型
`table`	table_name	出力行のテーブル
`partitions`	partitions	一致するパーティション
`type`	access_type	結合型
`possible_keys`	possible_keys	選択可能なインデックス
`key`	key	実際に選択されたインデックス
`key_len`	key_length	選択されたキーの長さ
`ref`	ref	インデックスと比較されるカラム
`row`	rows	調査される行の見積もり
`filtered`	filtered	テーブル条件によってフィルタ処理される行の割合
`Extra`	なし	追加情報

この中で特に重要度の高いものはTYPE列となります。MySQL のドキュメントでは「結合のタイプ」と記載があるが、データがどのようにアクセスされるかを示すものになります。
TYPE の値をまずは確認し、インデックスを上手く使用できているかを確認します。

TYPE 列値	説明
`system`	テーブルに行が 1 つのみ（=system テーブル）。const の特殊ケース
`const`	一致するレコードが最大 1 行。行が 1 つしかないため、オプティマイザの残りによって定数と見なされることがある。インデックスが`PRIMARY KEY`または`UNIQUE NOT NULL`インデックスである場合に使用。 1 回しか読み取られないため非常に高速。テーブルアクセスが発生するかどうかは `Extra` 列の `Using Index` を参照
`eq_ref`	結合で使用されるインデックスが`PRIMARY KEY`または`UNIQUE NOT NULL`インデックスである場合に使用。const と似ているが、結合時に用いられる。高速。テーブルアクセスが発生するかどうかは `Extra` 列の `Using Index` を参照
`ref`	結合でキーの左端のプリフィクスのみが使用される場合、またはキーが `PRIMARY KEY` や`UNIQUE NOT NULL`ではない場合に使用される。テーブルアクセスが発生するかどうかは `Extra` 列の `Using Index` を参照
`range`	行を選択するためのインデックスを使用して、特定の範囲にある行のみが取得される。出力行の`key`カラムは、使用されるインデックスを示す。 B ツリーの走査を行い、一致するすべてのインデックスのエントリを探すのにリーフノードをたどる。テーブルアクセスが発生するかどうかは `Extra` 列の `Using Index` を参照
`index`	フルインデックススキャン。インデックスの全体をインデックスの順番に沿って読む。 ※インデックスツリーがスキャンされることを除いて、`ALL` と同じ。改善の余地あり
`ALL`	フルテーブルスキャン。全行全列を読み込む。改善が必要

また、TYPE 列以外の見ておきたい主要な列の値を挙げます。

列名	列値	説明
`Extra`	`Using Index`	`Extra`列に`Using Index`がある場合、テーブルアクセスは発生しない。
`key`	`PRIMARY`など	MySQL が実際に使用することを決定したインデックスを示す。 ※`PRIMARY`は主キーに自動的に作成されるインデックスの名前。`PRIMARY`とあった場合は主キーのインデックスを利用できている。
`possible_keys`	`PRIMARY`など	MySQL が検索するために選択できるインデックスを示す。テーブルにあるインデックスを確認するには、`SHOW INDEX FROM tbl_name`を使用する。

上記で紹介したのは実行計画の出力内容の一部です。上記内容を抑えておけば最低限実行計画の中身はなんとなく評価できるというラインだと思います。
実際のアプリケーションで使用している SQL から実行計画を出力し、本記事で基本をさらい、公式ドキュメントでさらに知見を深めていただければ幸いです。

参考書籍・参考サイト紹介

世の中にはたくさんの素晴らしい本や記事がたくさんありますよね！
SQL のパフォーマンスまわりに関わる部分で、私が読んでみて良かったものを大変恐縮ながら紹介させていただきます。

SQL パフォーマンス詳解

SQL のパフォーマンスに関する内容を簡潔に分かりやすく、かつ重要な情報をまとめてくださっており、大変参考になりました。
本記事の実行計画パートの内容もこちらを参考にさせていただいています。PDF ですぐに購入可能ですし、とてもおすすめです。

SQL パフォーマンス詳解著者技術記事

MySQL 公式ドキュメント

一度記事内で登場しましたが公式サイト。MySQL に関する正しい情報が載っています。

cookpad 開発者ブログ

こちらも一度記事内で登場しましたが、cookpad 様が InnoDB のインデックスの基礎知識をまとめてくださってます。

Yahoo MySQL チューニング slideshare

Yahoo 様スライド。インデックスを作成するポイントや SQL チューニング手順など、開発者が抑えるべきポイントが大変分かりやすくまとめられていました。

Oracle MySQL パフォーマンスチューニング概要

Oracle 様の MySQL のパフォーマンスチューニングに関する内容を資料化してくれています。一読しておくと参考になります。

おわりに

ここまで読んでくださりありがとうございます。
想定外のパフォーマンス劣化を避けるためには、最適なインデックス設計、SQL 設計、開発や保守時に実行計画をおろそかにしないことが重要です。

本記事が、皆様の開発にお役に少しでも立てますと光栄です。

それではまた！

GitHubで編集を提案