副問い合わせや極限値のSQLパフォーマンスチューニング

問題のSQL文

問題のSQL文が以下です。

株式の価格テーブルから、NULL値(土日)を除き最新の日付を副問い合わせで取得し、証券コードと日付から終値を取得します。

SELECT close FROM stock_rate_history WHERE stock_code = "1301" AND date = ( SELECT MAX(date) FROM stock_rate_history WHERE close IS NOT NULL)

しかし、これが重いのなんの、1.2秒もかかります。

1.2秒です、1.2秒、いくらなんでも時間かかりすぎです。

対策として、一つ目のアイディアがユニークIDを作成する方法です。
問題のSQL文では、WHEREで証券コードと日付からデータを取得していますが、これを合わせるアイディアです。

例えば、証券コード 1301で、日付が2022/01/01だった場合、IDとして130120220101を作成し、これにUNIQUE制約を付ければSELECTを速くできます。

実際のSQL文は以下のようになります。

SELECT close FROM stock_rate_history WHERE id = CONCAT( "1301" ,( SELECT MAX(date) FROM stock_rate_history WHERE close IS NOT NULL))

これはかなりうまくいきました。

1.2秒から0.8秒まで速度が改善する効果が見られました。

2つ目のチューニングポイントは、MAX(date) の副問い合わせの部分です。
これ単体で0.7秒ほどかかっており、これが改善しなければ速度も改善できません。

アイディアとしては、dateにインデックスを作る方法です。

しかし、これはうまくいきませんでした。

BITREEのインデックスを作成したのですが、「WHERE close IS NOT NULL」があるため、インデックスが使用されなかったのです。WHERE句を削除すれば0.3秒まで速度が高速化できるのでインデックスは機能していますが、WHERE句が無いと条件に合いません。

また、インデックスを一度参照するのか逆に遅くなる現象まで発生しました。

/* 変更行数: 0  検索行数: 1  注意: 0  実行時間 1 クエリー: 2.406 sec. */

悩んだ末に思いついたのが、LIMITを使用して1行だけ取得する方法です。

date は先の件でインデックスが使用できる状態のため、ORDER BY による並び替えが高速になっています。
この高速な状態で LIMIT を使用して1行だけ取得すれば良いのではないか。そう考えてみました。

実際のSQL文がこちらです。

SELECT close FROM stock_rate_history WHERE stock_code = "1301" and close IS NOT NULL ORDER BY date DESC LIMIT 1

これはかなりうまくいきました。

なんと速度が0.3秒に短縮しました。

/* 変更行数: 0  検索行数: 1  注意: 0  実行時間 1 クエリー: 0.344 sec. */

副問い合わせ、複数のWHERE、極限値取得などの負荷要因に対し、

インデックスの使用、ORDER BY による並び替え、LIMITによる取得の方法で、1.2秒を0.3秒にまで短縮できました。

SQL文を見直すことはかなりの効果をもたらすことを確認できました。