はじめに

こんにちは、エンジニアのhyodoです。

弊社では広告クリエイティブ収集サービス「アドクロールクラウド」のデータとして膨大な広告データを蓄積しています。 （*2023年9月時点での広告データの総取得数は6.2億件）

このようなビッグデータを扱う場合、 データベース操作のパーフォーマンスを上げることは非常に重要です。

この記事では、MySQLのヒント句を利用して処理速度の改善のチューニングを行なった事例を紹介します。

まずはEXPLAINで実行計画を確認

SQL文の頭に「EXPLAIN」をつけると、SQLの実行計画（実行する方法に関する情報）がわかります。

あくまで実行計画を示すだけなので、UPDATE文やDELETE文で行なっても実際にデータが更新されることはありません。

例

EXPLAIN 
SELECT *
FROM tasks
WHERE status IN (0, 10) AND name = 'hyodo';

実行結果

+------+-------------+-------+------+-------------------------+----------------+---------+-------+---------+-------------+
| id   | select_type | table | type | possible_keys           | key            | key_len | ref   | rows    | Extra       |
+------+-------------+-------+------+-------------------------+----------------+---------+-------+---------+-------------+
|    1 | SIMPLE      | tasks | ref  | status,name,status-name | name           | 5       | const | 2696720 | Using where |
+------+-------------+-------+------+-------------------------+----------------+---------+-------+---------+-------------+

"possible_keys"は利用可能なインデックスを、"key"は利用されたインデックスを示します。 実行結果を見ると、利用されたインデックス(key)が「name」となっていますが、インデックス「status」を利用したいです。

MySQLのヒント句を用いるとクエリで使用するインデックスを指定できるため、SQL文に追加します。

ヒント句を用いて使用するインデックスを変更

EXPLAIN実行結果の"rows"カラムの数値は、クエリーを実行するために調査する必要があると考える行数を表しています。 この値の増減で、実際にクエリを流した際の処理時間の変化を推測することができます。

それでは、SQL文に「USE INDEX(インデックス名)」を追加することで、使用するインデックスを指定します。

ヒント句を追加したSQL文

EXPLAIN 
SELECT *
FROM tasks
USE INDEX(status)
WHERE status IN (0, 10) AND name = 'hyodo';

実行結果

+------+-------------+-------+-------+---------------+--------+---------+------+---------+------------------------------------+
| id   | select_type | table | type  | possible_keys | key    | key_len | ref  | rows    | Extra                              |
+------+-------------+-------+-------+---------------+--------+---------+------+---------+------------------------------------+
|    1 | SIMPLE      | tasks | range | status        | status | 5       | NULL | 1378216 | Using index condition; Using where |
+------+-------------+-------+-------+---------------+--------+---------+------+---------+------------------------------------+

USE INDEXで「status」を指定しているため、 実行結果の利用可能なインデックス(possible_keys)と利用されたインデックス(key)に「status」が表示されました。

また、インデックス指定前後のrowsカラムの値を比較すると、2696720から1378216へ半分ほどに減少しています。 このようにインデックスの指定によってクエリの速度が改善できる場合があります。

最後に

MySQLのクエリの速度改善の手段として、インデックスの設定を行うことがありますが、 必ずしも意図したインデックスを利用したクエリが流れているとは限りません。

EXPLAINでクエリの実行計画を確認して、ヒント句で適切なクエリチューニングができるようにしましょう。