Zenn
株式会社ログラス テックブログPublicationへの投稿
🚀

遅いSQLを改善して高速化を実現するためのアプローチ

Kazuma Ueda
2024/12/16に公開
SQL
tech

こんにちは、株式会社ログラスでエンジニアをしている上田（@ueda1013）です。

この記事では、大規模データを扱う中でよく直面する SQL パフォーマンスの課題について、例題を用いて改善の一例をご紹介します。

大規模なデータを扱うシステムでは、データの明細数が数百万〜数千万件に及ぶことも珍しくありません。そのため、SQL がアプリケーション全体のパフォーマンスに大きく影響することが多いですが、適切な対策を取ることで大幅な改善が可能です。

この記事では、比較的シンプルな改善手法を取り上げているので、ぜひ参考にしてみてください。

改善の内容

今回は主に1つの改善アプローチを紹介します。

JOIN の削減

複数テーブルの JOIN を利用した SQL は、大量のデータを処理する際にボトルネックになる場合があります。以下は、典型的な課題とその解決策について解説します。

起きがちな問題

この記事では架空の大規模データを扱う大企業の請求書管理サービスを題材にサンプルクエリで解説したいと思います。なお、あくまで架空のクエリで実際のユースケースを考慮したものではないことにご注意ください。

このクエリでは、複数のマスタテーブルと階層構造を利用してデータを抽出しています。

select
    invoices.invoice_id,
    invoices.amount,
    accounts.account_name,
    departments.department_name,
    products.product_name
from
    // 請求書情報テーブル
    // このテーブルに膨大なレコードが入っている想定をしています
    invoices
    // 勘定科目テーブルをJOIN
    JOIN accounts ON invoices.account_id = accounts.account_id
    // 勘定科目の階層関係を解決するテーブルをJOIN
    JOIN accounts_nodes ON accounts.account_id = accounts_nodes.child_account_id
    // その他のマスタ情報のJOIN
    JOIN some_masters ON invoices.some_master_id = some_masters.some_master_id
    // その他のマスタ情報の階層関係を解決するテーブルをJOIN
    JOIN some_master_nodes ON some_masters.some_master_id = some_masters_nodes.child_some_master_id
    // 以降もJOINが続く...
    // 対象のIDのみフィルタリング
    accounts_nodes.parent_account_id = 'account_001'
    and some_masters_nodes.parent_some_master_id = 'some_master_001'
    // 以降もフィルタリングが続く...
    and invoices.amount > 10000
    and invoices.invoice_date between '2024-01-01' and '2024-12-31'

このクエリには、主に次のような問題が発生する可能性があります。

  • 途中の計算結果のレコードの爆発的増加
    • JOIN の Nested Loop （多重ループ）が多発する
    • 実行計画を確認した結果、WHERE 条件によるフィルタリングのタイミングが遅く、結合後の実行になる
    • その結果、結合の度に途中の計算結果として膨大なレコードを生成する構造になる
  • I/O コストの増大
    • 膨大なレコードを JOIN で結合するため、ディスク I/O の負荷が高くなる
  • インデックスが有効に活用されない
    • 結合後のフィルタリングのためか、インデックス検索が効かず、Bitmap Heap Scan などが発生する

これらの要因が重なると、クエリの実行時間が著しく長くなる可能性があります。

解決方法

このような課題に対処するためのアプローチとして、以下のような方法を採用することが考えられます。

具体的には、クエリを次のように変更します。

select
    invoices.invoice_id,
    invoices.amount
from
    invoices
    // 階層関係を解決するテーブルのJOINは辞める
    JOIN accounts ON invoices.account_id = accounts.account_id
    JOIN some_masters ON invoices.some_master_id = some_masters.some_master_id
where
    // 事前に取得した勘定科目、その他のマスタ情報のIDリストを指定する
    invoices.account_id in ('account_001', 'account_002')
    and invoices.some_masters_some_master_id in ('some_master_001', 'some_master_002')
    and invoices.amount > 10000
    and invoices.invoice_date between '2024-01-01' and '2024-12-31'

階層関係を解決するテーブルの JOIN を辞め、代わりにデータ抽出の対象となる ID のリストを事前に取得しておき、クエリの WHERE 条件に ID を指定する構造に変更しています。

全体のクエリの実行回数は増えますが、ID のリストを取得するクエリの対象テーブルは invoices のように膨大なレコードを持たないのでそこまで問題ではありません。

これによって、JOIN によるレコードの爆発的増加を回避できるようになり、高速化がされる可能性があります。

また、シンプルな SQL 構造によりデバッグや保守がしやすくなる効果が得られます。

パフォーマンス検証のテクニック

SQL のパフォーマンス改善では、クエリを修正するだけでなく、意図した効果が得られているかを検証することが重要です。

ここでは、パフォーマンス検証に役立つテクニックや注意点をいくつかご紹介します。

シンプルな変更が大きな効果をもたらすこともある

シンプルな変更でも SQL の実行時間が大幅に短縮されることがあります。実行計画を分析することでボトルネックを特定しやすくなり、改善の方向性を絞り込むのに役立ちます。実行計画の取得コストはそれほど高くないため、遅い SQL を見つけた際には、まず確認する習慣をつけるのがおすすめです。

パフォーマンス検証は開発環境と本番環境の差異に気をつける

開発環境と本番環境のインデックスやデータ量に差があると、開発環境で実施したパフォーマンス検証が本番環境では通用しないことがあります。開発環境と本番環境の実行計画が異なり、最悪の場合はリリース後に重大なパフォーマンス問題を引き起こすリスクもあるので、開発環境を整えたり、本番相当の環境でのパフォーマンス検証を実施する必要があります。

SQL が遅いのは他の重い処理に巻き込まれているのが原因かもしれない

実行時間が遅い SQL を見つけて遅い原因を調査する際は、SQL の遅さだけでなく、CPU使用率やI/O待ち時間、同時に実行されている他の重いクエリにも目を向ける必要があります。実際、重いクエリが影響して全体の負荷が高まっているだけで、SQL 自体が原因ではない場合をよく見かけます。

まとめ

SQL のパフォーマンス改善は、原因を調査し、実行計画を確認しながら進めることで、低コストで大きな効果を得られる可能性があります。こうした改善を通じて、ログラスのプロダクトをより快適に使っていただけるよう取り組んでいきたいと思います。

この記事が SQL のパフォーマンス改善に取り組む方々の参考になれば幸いです。最後まで読んでいただき、ありがとうございました！🚀✨

株式会社ログラス テックブログ
株式会社ログラス テックブログPublication

株式会社ログラスは「良い景気を作ろう。」をミッションに、企業経営を推進する次世代型経営管理クラウド「Loglass 経営管理」を開発し提供しています。

Discussion