この記事はtacoms Advent Calendarの14日目の記事です。
tacoms SREの@ikuwowです。
今日はtacomsで最近実施したSQLのパフォーマンス改善例の紹介をします。あまり素直にお勧めできる方法ではないですが、制限のある中でひとつ解決策を見出したという例になります。
課題
（テーブル構成等は全て例で実際のものとは異なります）
以下のようなこんなテーブルがありました。サポートチケットを管理するテーブルで、チケットが作成された時にinsertされ、状態や内容の更新があるたびにupdateされ、クローズされたあとも履歴として参照することがある、そんなテーブルです。
CREATE TABLE tickets (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NULL,
customer_email VARCHAR(255),
primary_assignee_id INT NOT NULL,
secondary_assignee_id INT,
subject VARCHAR(255) NOT NULL,
description TEXT,
remarks TEXT,
status_id INT NOT NULL,
priority ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL DEFAULT 'Low',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
（他多数のカラム）
);
（もっと正規化しろとかenumやめろなどの話は一旦置いておきます）
今回は以下のSQLの実行で使えるインデックスがなく、フルスキャンが走り非常に遅いSQLになっていました。
SELECT * FROM tickets
WHERE customer_email = '...' AND created_at between '2024-11-14 00:00:00' and '2024-12-14 00:00:00'
ORDER BY created_at desc limit 50;
以下のようなインデックスを貼るのが効果がありそうです。実際にcloneしたMySQLクラスタに実行したところexplainレベルでは大きな改善が見られましたし、Online DDLのため読み書きをブロックしないで実行できます。
CREATE INDEX customer_email_created_at ON tickets
(customer_email, created_at);
しかし今回は以下の理由でこのインデックスを追加できないという判断をしました。
- テーブルのサイズが大きく、インデックス追加のコストが高い
- レコード数は数十億行オーダー
- カラム数は例に示したよりずっと多い
- 実験したところインデックス実行時間が数十分程度
- 高負荷によるダウンタイムやdegradeを起こせない、影響度合いが読めない
- 書き込みが多めでインデックスによるパフォーマンス低下が無視できなそう
- ミッションクリティカルな処理に直結しているためリスクを取れない
この場合でのSQLのパフォーマンス改善を、大きな設計変更を伴わずに行う方法を考えました。
やったこと：tickets_search_indexテーブルの作成
仕様の深掘りをした結果、以下の前提を置くことができました。
- このSQLは過去1ヶ月分のみが検索対象である
- 検索条件はcustomer_email, created_atのみでよい
- 更新が行われないカラム
この上で、以下のtickets_search_indexテーブルを作りました。ticketsテーブルのレコード数カラム数を削ってインデックスを貼った形のテーブルです。
CREATE TABLE tickets_search_index (
id BIGINT PRIMARY KEY,
customer_email VARCHAR(255),
created_at DATETIME,
INDEX idx_customer_email_created_at (customer_email, created_at)
);
このtickets_search_indexに、直近3ヶ月分のticketsレコードに対応するレコードが作られるように、アプリケーションでticketsと同時に書き込むようにました。
具体的には、ticketsのinsertと同時にtickets_search_indexにinsertし、ticketsがclosedになったあとに定期バッチ処理でtickets_search_indexからdeleteするようにしました。
SQLは以下のようにticketsのクエリに対してtickets_search_indexテーブルをjoinするようにし、高速にticketsのレコードを絞り込んで取得することができるようになりました。
SELECT tickets.* FROM tickets
INNER JOIN tickets_search_index on tickets.id = tickets_search_index.id
WHERE tickets_search_index.customer_email = '...'
AND tickets_search_index.created_at between '2024-11-14 00:00:00' and '2024-12-14 00:00:00'
ORDER BY tickets_search_index.created_at desc limit 50;
またテーブルサイズ全体が小さいため、インデックス追加やカラム追加のコストも小さくなり、検索ディメンジョン（カラム）追加が現実的になりました。
explainの比較
before:
（なんとidの範囲を使ってざっくり絞った上でwhereでフルスキャンしながら絞り込んでいました）
+----+-------------+---------+------------+-------+----------------+-----+---------+-----+---------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+-----+---------+-----+---------+----------+------------------------------------+
| 1 | SIMPLE | tickets | | range | id | id | 4 | | 6039880 | 1 | Using index condition; Using where |
+----+-------------+---------+------------+-------+----------------+-----+---------+-----+---------+----------+------------------------------------+
after:
+----+-------------+----------------------+------------+--------+---------------------------------------+-------------------------------+---------+----------------------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------------+------------+--------+---------------------------------------+-------------------------------+---------+----------------------------------+------+----------+--------------------------+
| 1 | SIMPLE | tickets_search_index | | range | PRIMARY,idx_customer_email_created_at | idx_customer_email_created_at | 5 | | 1116 | 100 | Using index |
| 1 | SIMPLE | tickets | | eq_ref | PRIMARY | PRIMARY | 1022 | db.tickets_search_index.order_id | 1 | 5 | Using where |
+----+-------------+----------------------+------------+--------+---------------------------------------+-------------------------------+---------+----------------------------------+------+----------+--------------------------+
tickets_search_indexをインデックスで高速に絞り込み、idでticketsを特定することができています。
評価
ticketsに直接インデックスを追加する場合と比べて、以下の違い（デメリット）があります。
- tickets_search_indexへのinsert/deleteのスループットが増える
- ticketsテーブルと同時かつ同じ頻度
- 書き込みインスタンスへの書き込み量が増える
- 1トランザクション内のクエリ数が増える（insert/delete)
- tickets, tickets_search_indexという依存関係が増える
- 前提においた過去1ヶ月間など、検索対象とできるレコードが限られる
このため、今回のようにどうしてもインデックスを貼れない場合以外には、直接インデックスを貼ることが望ましいと思います。今回はインデックス追加時の短時間の負荷やその他の影響を避けるために、これらのデメリットを受け入れた形になります。
tickets_search_indexでの絞り込みが必要（ticketsではパフォーマンスが悪い場合）な頻度が増えたり、必要な検索ディメンジョン（カラム）が増えたり、insertだけでなくupdateが必要になってくると、これらのデメリットは大きくなります。
今回はアプリケーションの処理の中でtickets_search_indexへレコードをinsertするようにしましたが、triggerでticketsへのinsertをトリガーにtickets_search_indexへのinsertを行う方法もあります。またPostgreSQLであればマテリアライズドビューが使えるため、tickets_search_indexをinsert/update/deleteを意識せず宣言的に定義できます。
データ構造では、更新の多いアクティブなチケットとその履歴を一緒に管理していることが問題だと感じてます。今後1年以内を目安に、active_tickets、closed_ticketsのようにテーブルを分けたりすることを含め、改善を進めていきたいと思います。
まとめ
今回はインデックス追加の影響を許容できない場合にインデックス貼る用の小さいテーブルを作る方法を紹介しました。少しでも参考になれば幸いです。
tacomsでは現在エンジニアを積極採用しています！
Discussion