概要

定期的に行っている運用作業で、運営側が利用する売上情報をtoCサービスのテーブル群から無理やり抽出して集計するSQLの処理時間が遅くなってきたが、
急にテーブル構成を変えたり仕組み化とかだったりはコストがかかるので、SQLのみでさっさと要因を調査して速度改善したので手順をメモする。

前提

MySQL5.7系であること

手順

1.処理時間を測定する

SQLで処理時間を測定する。
※Sequal Ace等でSQLを実行したらクエリの所要時間が表示されますが、
プロファイリングを使用すると、より詳細な情報を得ることができます

処理時間を表示するSQL

-- プロファイリングを有効化
SET profiling = 1;

-- 対象SQL
SELECT SQL_NO_CACHE '対象SQL';

-- プロファイリング結果を表示
SHOW PROFILES;

測定対象SQLのクエリーキャッシュを無効にするためSQL_NO_CACHEを利用したが、
シチュエーションによっては効かないことがある。

実行結果例

Query_ID	Duration	Query
1	        0.00032425	-- 対象SQL↵SELECT SQL_NO_CACHE * FROM test_table

Durationは秒単位ですが小数で分かりづらければ、別途SQLなどでミリ秒に変換すると分かりやすくなります。

変換例

SELECT 0.00032425 * 1000 AS 'Milliseconds';
-- 0.32425000

2.実行計画を確認する

EXPLAINで実行計画を確認する。
実行計画を確認することで非効率なクエリを見つけたり、修正結果を確認できる。
出力フォーマットの詳細はこちらに記載されているが、特に注目したいのは「type, key, Extra」の3カラム。

実行計画を表示するSQL

EXPLAIN SELECT '対象SQL'

+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+
| 1  | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 3012984 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+
~~~省略

type

対象のテーブルに対してのアクセス方法
- 「ALL」の場合はフルテーブルスキャンなので改善の余地があるが、意図的に全行を読み出している場合は問題ない
- 「index」の場合はフルインデックススキャンなので改善の余地があるが、ORDER BY + LIMIT で行数を絞り込んでいる場合は問題ない

key

テーブルにアクセスするために使ったINDEX
- 「NULL」の場合はINDEXが効いていない
- INDEX効いていても検索対象のデータ量が多かったらあまり意味ないかも。逆にINDEX検索・更新によるオーバーヘッドがかかりそう
- 「検索対象のデータ量が多い」の閾値は、どこかのベンダーは検索結果が全データの10%以上くらいだと言っていた気がするが（以前別会社に居た頃）、システム環境にもよるかと思うのであらかじめ実測を行って感覚を掴んだ方がいいかも・・

Extra

他に使用している条件など。よく見るものだけ掻い摘むと、
- 「Using temporary; Using filesort」の場合はJOINしてからソート（最遅）
- 「Using filesort」だけの場合はソートしてからJOINが実行
- 「Using filesort」が無い場合はインデックスを用いてソート（最速）

3.統計情報を確認する

実行計画はデータ量や統計情報などを元に作られるため、念のため統計情報を確認する。
統計情報とは、テーブルやインデックスにどういったデータが入っているかの統計。
統計情報が実データの分布と著しく乖離している場合、実行計画が劣化している可能性がある。

統計情報を表示するSQL

SELECT * FROM mysql.innodb_table_stats WHERE table_name = '対象テーブル名';

+---------------+-------------+---------------------+----------+----------------------+--------------------------+
| database_name | table_name  | last_update         | n_rows   | clustered_index_size | sum_of_other_index_sizes |
+---------------+-------------+---------------------+----------+----------------------+--------------------------+
| test          | test_table  | 2023-04-03 23:41:56 | 1111     | 222222               | 3333333                  |
+---------------+-------------+---------------------+----------+----------------------+--------------------------+

4.非効率なSQLを修正する

修正方法は様々あってケースバイケースだが、今回はSQLのみでさっさと改善したいので以下のアプローチで修正する。

検索対象となるレコードを早い段階で絞る
既に存在するINDEXを利用する
無駄な中間テーブルを減らす

5.再度「1.処理時間を測定する」へ

抽出データが正しく、処理時間が許容範囲だったらOK。
NGなら別の修正を行う。

おわり

今回は運用で利用しているSQLの処理速度をさっさと改善したかったのでSQLのみで対応したが、既存テーブルの構成やデータ量によってはSQLだけでの根本的な速度改善には限界がある。もしくはBigQueryなどにデータを突っ込んで集計するか・・。

SQLのみで遅いクエリを改善メモ

概要

前提

手順