Blog
本記事の目的
DBのパフォーマンス向上に関する手法を学び、日常の開発でどのように活用するかを検討していきます。
1. 非正規化
非正規化は、結合回数を減らすことでクエリの速度を向上させる手法です。 詳細はこちらで説明しております。
ただし、個人的にはデータの整合性が崩れるリスクがあるため、特別な理由がない限り使用しない方針です。
2. インデックス
テーブルに「検索の目次」とも言えるインデックスを付与することで、検索速度を向上させる手法です。 詳細はこちらで説明しております。
この手法は、テーブル構成を大幅に変更する必要がなく、設計途中からでも適用可能なため、非常にコスパの良い手法だと考えています。
3. パーティション
1つのテーブルのデータを物理的に分割して格納することで、検索速度の向上を図る手法です。 この分割を「パーティション」と呼びます。
transactions テーブル（パーティション前）
| id | user_id | amount | created_at |
|---|---|---|---|
| 1 | 101 | 100.00 | 2024-01-15 |
| 2 | 102 | 250.00 | 2024-02-20 |
| 3 | 103 | 300.00 | 2024-03-05 |
| 4 | 101 | 150.00 | 2024-01-25 |
| 5 | 104 | 200.00 | 2024-02-10 |
| 6 | 105 | 350.00 | 2024-04-01 |
| 7 | 102 | 400.00 | 2024-03-22 |
| 8 | 105 | 120.00 | 2024-02-15 |
| 9 | 101 | 150.00 | 2024-03-18 |
| 10 | 103 | 500.00 | 2024-01-30 |
パーティション後（例：月ごとに分割）
transactions_2024_01
| id | user_id | amount | created_at |
|---|---|---|---|
| 1 | 101 | 100.00 | 2024-01-15 |
| 4 | 101 | 150.00 | 2024-01-25 |
| 10 | 103 | 500.00 | 2024-01-30 |
transactions_2024_02
| id | user_id | amount | created_at |
|---|---|---|---|
| 2 | 102 | 250.00 | 2024-02-20 |
| 5 | 104 | 200.00 | 2024-02-10 |
| 8 | 105 | 120.00 | 2024-02-15 |
transactions_2024_03
| id | user_id | amount | created_at |
|---|---|---|---|
| 3 | 103 | 300.00 | 2024-03-05 |
| 7 | 102 | 400.00 | 2024-03-22 |
| 9 | 101 | 150.00 | 2024-03-18 |
transactions_2024_04
| id | user_id | amount | created_at |
|---|---|---|---|
| 6 | 105 | 350.00 | 2024-04-01 |
パーティションを使用することで、WHERE句で特定の年月を絞り込む際に、その年月のデータのみをアクセスすればよくなり、検索速度が向上します。 パーティションはデータのスキャン範囲を絞り込むため、大量のデータを扱う場合に特に効果的です。 ただし、パーティションキーに偏りがある場合、特定のパーティションにアクセスが集中し、パフォーマンスが低下する可能性があるため、パーティションキーはできるだけデータ量が均等に分布するようなものを選定することが重要です。 ※実際に物理ファイルの配置をパーティションごとにわけるので、1テーブルにパーティションは1つのみ指定が可能になります。
Laravelのマイグレーションって対応するの？
Laravelを扱うことが多いので、マイグレーションでパーティションを設定するための関数が定義されているかどうかが気になりました。 そして調べてみたところ、Laravelではパーティションを設定するための専用の関数は定義されていないようです。そのため、以下のようにSQLを直接記載する必要があるようです。
DB::statement('
ALTER TABLE transactions
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
PARTITION p202401 VALUES LESS THAN (202402),
PARTITION p202402 VALUES LESS THAN (202403),
PARTITION p202403 VALUES LESS THAN (202404)
);
');
パーティションは手動なDBMSでサポートされているようなので、今度実装時に検討してみようと思います！
ヒント句
ヒント句（Hints）は、SQLクエリの実行計画をデータベースに指示するための追加情報であり、データベースのオプティマイザが最適な実行計画を選択する際に、特定の操作を強制的に行うように指示できます。例えば、インデックスの使用を強制したり、特定の結合方法を選ばせたりすることができます。DBMSもバージョンアップにより性能が向上していますが、それでもまだ実行計画を最適化できる余地はあります。
ヒント句は非常に強力なツールですが、使いこなすには少し修行が必要です。というのも、ヒント句を使うことで、オプティマイザに対して特定の実行計画を強制することができますが、その使い方を誤ると、かえってクエリのパフォーマンスを悪化させることがあります。
また、DBMSによってヒント句の構文が異なるため、移植性に関しても注意が必要です。
パラレルクエリ
SQLクエリを複数のスレッドやプロセスで並行して実行する方法です。 データベースシステムは、大きなデータセットを処理する際にクエリの一部を並列化し、複数のCPUコアを使って処理することで、クエリの実行速度を向上させることができます。 ※ パラレルクエリは、システムのリソースを多く消費するため、過度に使用するとパフォーマンスが低下する場合があります。
リソースに余裕がある時に扱うのがよさそうですね:)
オンメモリ
オンメモリとは、データがディスクではなくRAM（メモリ）に格納され、データベースの操作がメモリ上で行われるアーキテクチャのことを指します。 オンメモリデータベースやオンメモリテーブルを利用することで、ディスクI/Oの遅延を回避し、高速なデータアクセスが可能になります。 データベースのパフォーマンスのボトルネックはディスクI/Oであるため、オンメモリを活用することによって、データの読み書き速度が大幅に向上し、非常に効果的な手法となります。
ただし、オンメモリはメモリに依存するため、リソースに余裕がないと逆にパフォーマンスを低下させる原因となります。また、サーバーがシャットダウンした際には、メモリ内のデータが失われる可能性があるため、永続的なデータ保存が必要な場合には適さないこともあります。
終わり
以上が、データベースのパフォーマンス向上のための手法についての説明でした。後半で紹介した手法はいずれもサーバーリソースとの兼ね合いが非常に重要となります。そのため、各案件において「適用する」「適用しない」を慎重に選択する必要があります。特に、システム全体への影響を考慮しながら実施することが大切です。 また、これらの手法は難易度が高く、実施には一定の工数がかかるため、安易に実行するのではなく、慎重に検討した上で導入を決定したいと考えています。パフォーマンス向上が求められる場面でも、最適なタイミングや方法を見極めることが重要ですね。
それでは、ここまでお付き合いいただきありがとうございました:)