DBアクセスが遅かったときに試したこと

本記事は【Advent Calendar 2024】 6日目の記事です。
🌟🎄 5日目 ▶▶ 本記事 ▶▶ 7日目 🎅🎁

こんにちは、システムエンジニアの上村です。

業務でインフラ再構築に携わることがあったのですが、その中でアプリの一部機能の処理が非常に遅いということが分かりました。どこで遅くなっているのかを切り分けるため、DEBUGログを出力させて調査した結果、特定のSQL実行時に特に時間がかかっていることが分かりました。そのSQL実行時間を改善するのに苦労したので、どうやって解決したかを書いていこうと思います。

前提
試したこと
今回原因だったこと
今回行った対処
他に試すことができそうなこと
まとめ

前提

RDBMSはMySQLを使っています。

試したこと

統計情報の更新

DBは統計情報というデータを持ちます。統計情報とは、表やインデックス、使用している領域、データの種類、データの分布等のデータ特性を表す情報のことです。

統計情報が不適切だと、SQL実行に時間がかかることがあります。

以下SQLで統計情報が更新できます。

ANALYZE TABLE [テーブル名];

今回は、アクセスが遅かったテーブルを対象に統計情報の更新を行いました。

実行計画の調査

実行計画とは、あるSQLが実行された際にクエリがどのような手順で実行されるかを示します。

以下SQLにより実行計画が調査できます。

EXPLAIN [実行されるSQL]

例えばEXPLAIN SELECT * FROM test_table where id2 = 1;というSQLの実行計画は以下の通りとなります。 ※id2は主キーではない。

特に、typeを見ることでどのようにテーブルにアクセスするかが分かります。本記事で出てくるtypeは以下2つです。

ALL：フルテーブルスキャン
- 全件検索なので一番遅い
INDEX：フルインデックススキャン
- インデックスをもとに検索するのでフルテーブルスキャンより速い
- なお、インデックスとは目的のレコードを効率よく取得するための索引のこと

今回の場合は、アクセスが遅かったSQLにEXPLAINをつけて実行計画を調査しました。

DBスペックの向上

元々DBスペックが過剰だったので、インフラ再構築時はより低いDBスペックとしていました。ただ、DBスペックの差異が原因の可能性もあったため、インフラ再構築前のスペックへと戻して実行時間を計測しました。なお、DBはAmazon RDSを使っていたため、今回はこのインスタンスタイプを元に戻しました。

今回原因だったこと

今回は、以下要因が組み合わさったことが原因でした。

インフラ再構築前から実行SQLがフルテーブルスキャンとなっていたこと。
DBスペックを低くしたこと。

1点目については、WHERE句のカラムへインデックスを作成してフルインデックススキャンとすることで、アクセス時間を大幅に改善することができました。以下SQLでインデックスが作成できます。

CREATE INDEX [インデックス名] ON [テーブル名] ([カラム名1],[カラム名2], ...);

特にインデックス名は、分かりやすい名称であれば何でも良いです。

2点目については、フルテーブルスキャンのままでも、DBスペックをもとに戻すことで実行時間が大幅に改善しました。

なお、今回実行にかかっていたSQLの対象テーブルは20万件程度のレコードがありました。どちらの対処でも、約100秒→約0.5秒未満へと短縮することができました。

今回行った対処

上記どちらかの対処を行うことで事象は改善するのですが、今回はフルインデックススキャンへとする方針を採用しました。というのも、一般的にDBスペック向上はお金がかかるので、他にお金がかからない対処があればそちらを採用したかったためです。

他に試すことができそうなこと

今回はインデックス作成により事象解消しましたが、他にも、たとえフルインデックススキャンを行っていても、インデックスが断片化してアクセスに時間がかかるようになるケースもあるようです。その場合、インデックス再構築により改善する可能性があるようです。下記SQLでインデックス再構築後にアクセス時間が改善しているかも試すことができます。