【MySQL,Rails】アプリケーションの動作を改善したい（Explain）

どうもこんにちは。

今回はRailsアプリケーションの動きが悪くなっている問題をSQLの観点から調査してみました。

経緯

自社開発しているrailsアプリケーションのとある画面の一覧画面を開いた時にめちゃめちゃ時間がかかるという状況にありました。

理由としては10,000件のデータの読み込みのためのSQL実行に無駄な時間がかかっていたことでした。（ざらに30,40秒かかってました。。。）

SQL調査手順

実際のデータは載せることができないのでダミーデータで手順をまとめます。

1. 一覧画面でページを更新したときに実行されるSQLをすべて取得

アプリケーションが動作している環境のログを取得してそこから実行されたSQLを取得します。

今回のアプリケーションは、AWSのElastic Beanstalkで動作しているため、こちらからログを取得しました。
（ぶっちゃけローカル環境でもOKです。）

2. 取得したSQLの一つ一つがどのくらい時間がかかるのかDBコンソールで実行して計測する

自分はDBeaverを使用しているので、そこのコンソール上で実行をしてみました。

3. 時間がかかるSQLに対してExplainコマンドを実行する

例ですが、以下のようなSQLで最も時間がかかっていたとします。

SELECT `childs`.`id` AS t0_r0, `childs`.`created_at` AS t0_r1, `childs`.`updated_at` AS t0_r2, `childs`.`parent_id` AS t0_r3, `childs`.`name` AS t0_r4, `childs`.`age` AS t0_r5,
`childs`.`birthday` AS t0_r6, `parents`.`id` AS t1_r0, `parents`.`created_at` AS t1_r1, `parents`.`updated_at` AS t1_r2, `parents`.`name` AS t1_r3, `parents`.`age` AS t1_r4, `parents`.`birthday` AS t1_r5
FROM `childs`
LEFT OUTER JOIN `parents` ON `parents`.`id` = `childs`.`parent_id`;

このSQLの一番上にEXPLAINコマンドをくっつけます。

EXPLAIN
SELECT `childs`.`id` AS t0_r0, `childs`.`created_at` AS t0_r1, `childs`.`updated_at` AS t0_r2, `childs`.`parent_id` AS t0_r3, `childs`.`name` AS t0_r4, `childs`.`age` AS t0_r5,
`childs`.`birthday` AS t0_r6, `parents`.`id` AS t1_r0, `parents`.`created_at` AS t1_r1, `parents`.`updated_at` AS t1_r2, `parents`.`name` AS t1_r3, `parents`.`age` AS t1_r4, `parents`.`birthday` AS t1_r5
FROM `childs`
LEFT OUTER JOIN `parents` ON `parents`.`id` = `childs`.`parent_id`;

そうすると以下のように結果が出てきます。（あくまでイメージです。）

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	parents		ALL					51	100.0	Using where; Using join buffer (Block Nested Loop)

Explainコマンドを実行した時に、Using where; Using join buffer (Block Nested Loop)が表示されたら問題があると思ってください。

この表示の意味することは、

MySQLがジョインを実行する際に「ブロックネストループ」と呼ばれるアルゴリズムを使用している。このアルゴリズムは非効率なので、インデックスを貼った方がいい。

ということです。

表示されたのがUsing indexやUsing whereのみだった場合には特に問題はないと考えてOKです。

Explainコマンドについてもっと細かく

1. select_type: クエリの種類。これには、以下のような種類が存在する。
   1. SIMPLE（単一のSELECTクエリ）
   2. PRIMARY（最も外側のSELECTクエリ）
   3. SUBQUERY（サブクエリ内の最初のSELECTクエリ）
   4. DEPENDENT SUBQUERY（外部のクエリに依存するサブクエリ）
   5. UNION（UNIONの最初のSELECT）
   6. DEPENDENT UNION（外部のクエリに依存するUNION）
2. partitions: クエリが参照するパーティションのセット。p0,p1など。パーティションされていないテーブルでは、このフィールドはNULLです。
3. type: テーブルへのアクセスタイプ。表示の意味は以下のようになる。
   3. ALLはフルテーブルスキャン
   3. indexはフルインデックススキャン
   3. rangeは範囲スキャン
4. possible_keys: クエリが使用できる可能性のあるインデックスの一覧。
5. key: 実際にクエリが使用するインデックス。possible_keysの中から選ばれます。
6. key_len: 使用されるインデックスの長さ。これはバイト単位で、短いほど一般的に良い。
7. ref: どの列または定数がインデックスキーと一致するかを示す。
8. rows: MySQLがクエリを実行するために読む必要があると見積もっている行数。これはあくまで推定であり、実際の行数とは異なることがある。
9. filtered: WHERE条件によってフィルタリングされた後に残る行の推定されるパーセンテージ。
10. Extra: MySQLのクエリの実行に関する追加情報。例えば、「Using where」はWHERE句を使用して行をフィルタリングしていることを示し、「Using filesort」は結果をソートするために一時テーブルを使用していることを示します。

まとめ

今回はMySQLのExplainコマンドについて説明してみました。

DB関連は難しいですね。。。

以上