【MySQL】日、週単位のデータを取得する処理が重い時に軽量化する方針

　結論から言うと日、週単位で絞り込める部分は必ず日、週単位のインデックスを用いた絞り込みを最初に行うようにし、その後に必要な処理をプログラムに行わせるべき、ということです。

　データの取得は書き込みに比べて頻繁に行われ、その速度の低下はユーザーに不便を強いてしまい不快感に繋がります。このためデータの取得機能は特に高速化すべきです。この記事では日、週単位でデータを扱う機能を高速化する方法を紹介します。

　データの取得の低速化の原因としてよくあるのがMySQL上で扱うテーブルの総レコード数の増加とデータ取得アルゴリズムが総数の増加に対応できていないという原因です。レコード数が3つの中から1つを探すのは総ざらいしても高速ですが、3億の中から1つを探すのは総ざらいでは遅くなりやすい、という感じです。この対策としてインデックスを用いる方法がよくとられます。MySQLのインデックスはＢ木を用いており計算量O(log n)です。

SQLデータベースにおけるバランス検索木(Bツリー)

　インデックスを用いることで高速化されます。ここで大事なのが何をインデックスするのかとインデックスが効果を発揮しているかです。次のSQLはID、名前、日時を持ったスケジュールの定義と取得クエリです。

CREATE TABLE schedules
(
    schedule_id   int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    schedule_name varchar(255) NOT NULL,
    schedule_at   timestamp    NOT NULL
);
CREATE INDEX schedule_name_index ON schedules (schedule_name);

SELECT *
FROM schedules
WHERE schedule_name LIKE 'Schedule-12%'
  AND '2023-08-29 00:00:00' <= schedule_at
  AND schedule_at < '2023-08-30 00:00:00'
ORDER BY schedule_at DESC
;

　3000件のランダムなデータを入れ、このクエリを EXPLAIN すると次の様になります。

　schedule_name についてのインデックスが効いておりある程度の高速化はできています。しかしこれは日、週単位でデータを取り扱う時は最善とはとても呼べないパターンです。というのも時間の経過によってデータの総数が増え続けると似た schedule_name のレコードも際限なく増え、インデックスで絞り込んだ結果の行数も増え続けてしまうのです。

　インデックスはO(log n)で高速にデータを絞り込んだり並び替えたりしてくれますがクエリ全体の処理がそこで終わらない時もあり、そういった時はインデックスによって絞り込んだ結果のデータのサイズに処理全体の速度が大きく影響されます。こういった場合EXPLAIN結果には Using index の他に Using where や Using filesort などが並びます。EXPLAINで表示されるメッセージについては次が詳しいです。

MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 8.8.2 EXPLAIN 出力フォーマット
MySQLとMariaDBの実行計画の処理

　ここから改善する方法は主に二つあります。

　一つはクエリ全てをカバーする複合インデックスを作ることです。これならば全てをインデックスでまかなえるため非常に高速です。しかしながら検索機能などでクエリが柔軟に構築される場合これは難しいです。これはインデックスを効かせるべきクエリが多岐にわたり用意するインデックスの総量も巨大になりやすいためです。こうなるとインデックスを保持するための容量も巨大になりデータをデータベースに書き込む際のインデックスの更新にかかる時間も無視できなくなります。最速な手法ではありますが適切な場面でのみ用いる必要があります。

　もう一つは効果的にインデックスが働く部分にのみインデックスをつける方法です。この記事の主題においてそれは日、週単位を出しているカラムです。こうすると複合インデックスほど速くありませんですが容量や書き込み時のパフォーマンスに悪影響を及ぼさず広範囲を大きく高速化できます。日、週単位で何かを取得するクエリの場合これは次の様にできます。

CREATE INDEX schedule_at_index ON schedules (schedule_at);

　日、週単位にまとめる日時カラムにのみインデックスをつけます。
　
　この状態で名前が Schedule-1からSchedule-100、日時が7年以内のランダムな値を持つレコードを300万件作りテストします。固定ユーザー100人が毎日何かして合計1200件ほどのデータを生む感じです。このデータに対してSELECT * FROM schedules WHERE schedule_name = 'Schedule-1' AND '2023-08-29 00:00:00' <= schedule_at AND schedule_at < '2023-08-30 00:00:00' ORDER BY schedule_at DESC;をEXPLAINすると次の結果になります。

　どちらも extra は同じような結果になっています。Backward index scanは名前の通り逆順でインデックスを使っているということです。schedule_name ではインデックスで絞り込んだ後の rows が 60904 とそれなりに残っている一方で schedule_at は 828 と大分少なくなっています。filtered はこの rows をWHERE等で filter した後残るレコードの割合です。インデックスから片方のカラムのWHEREを満たすレコード群を引っ張ってきて、それをもう片方のWHEREして90%を落として10%が残る感じです。rowsが多いと当然このフィルター処理に時間がかかります。この様に実行計画で歴然な差がある状態で、実際に実行すると明確に処理時間に差が出ます（自分の環境では大体2倍ほどのちがいでした）。データ量のさらなる増加やクエリの複雑化が起きると更に顕著な差が出ます。

　ここまで差が出るのは日、週単位で絞り込んだ後のデータの総数は時間経過で然程増えず、ある名前に紐づくデータは時間経過で増え続けるというシナリオが理由です。MySQLの検索機能はインデックス対象のデータをO(log n)で処理し、残ったデータをより計算量の多いアルゴリズムで処理するという流れのため先ほどの結果の様になります。このあたりはカーディナリティーで調べるとより詳しい話が出てきます。
　
　ちなみに複合インデックスを使うと次の様になり爆速です。実際のクエリの結果で返って来るのも4件だけであり filtered 100 に違わずインデックスだけで完璧に処理できています。

CREATE INDEX schedule_name_and_at_index ON schedules (schedule_name, schedule_at desc);

　この記事では日、週単位でのデータの取得のため日、週単位にするための列にインデックスをつけるのが良いと紹介しました。これは日、週単位で絞り込んだ後のデータの総数は時間経過で然程増えないという仮定に基づいています。また日、週単位以外のクエリであったとしてもデータの総数の増え方とクエリの内容を見て適切なインデックスを使うことで速度の低下を防げます。これはバランス良く格納される値の種類が増え続けるカラムを探して、そこにインデックスをつけるべきとも言い換えられます。