SQLクエリのパフォーマンスを最適化するための6つのチューニングヒント (Query Optimization)

こんにちは！HEARTCOUNTチーム Analytics EngineerのJadenです。
今日はデータベース、SQLクエリを最適化するための6つのヒントを紹介します！

データベースでは、クエリのチューニングはシステムパフォーマンスに直接的な影響を及ぼします。特に大容量データを扱う場合、最適化されていないクエリは応答速度の低下、リソースの浪費、さらにはサービス障害さえも引き起こす可能性があります。

一方、よくチューニングされたクエリは、 同じタスクをより迅速かつより安定的に処理することができます。企業レベルでも、効率的なクエリ処理はコスト削減と生産性の向上に直結するため非常に重要と考えられています。

私が今日紹介するクエリ最適化の6つのヒントは、データエンジニア、開発者、アナリストなどデータベースを扱うすべての方に役立つと思います。

各ヒントは、実務で容易に直面する可能性のある状況を例に挙げて説明します。コード例にはいくつか問題があり、どのようにすればより良いクエリパフォーマンスを得ることができるのか、詳しく説明します。

ああ、その前にクエリ最適化のために必ず知っておくべき重要な概念が一つあります。「インデックス (Index)」です。これが何であり、クエリのパフォーマンスにどのような影響を与えるのか簡単に説明します。

それでは、これから私と一緒にクエリチューニングのための6つのヒントを一つずつ確認しながら、最適化されたクエリ作成ノウハウを学んでみましょう！

---

このような内容です！

1. 左辺で演算しない
2. ORの代わりにUNION使う
3. 必要な行、列のみ取得する
4. 分析関数を最大限に活用する
5. ワイルドカード(%)は最後に置く方が良い
6. 計算値を事前に保存しておき、後で使う

---

(事前知識) データベースのインデックスとは？

効率的なクエリを作成するために知っておくべき事前知識があります。

それはインデックスです。

データベース内のデータを検索したり分類する作業は、質問を投げるのと似ています。この時に使う「質問」がまさにクエリ(query)です。

クエリを通じてデータベースに必要な情報をすばやく正確に探してほしいと要求するわけです。この過程で重要な役割を果たすのが「インデックス作成(indexing)」です。

インデックス作成(indexing)を理解するためには、本の目次を思い浮かべてみてください。本を読む場合、私たちが特定の内容を探したい時に目次を見てそのページに直接行くことができますよね？

このように、目次が本の中で目的の情報をすばやく見つけられるのに役立つように、データベースでもインデックス(index)が同様の役割を果たします。

簡単に言えば、インデックス作成とはデータベースから目的の情報を探すための「ショートカット」を作成することだと考えてください。これにより、データ検索速度が大幅に向上し、データを効率的に管理・活用するのに大きな助けになります。

それでは、本題に入りましょう！

---

1.左辺で演算しない

左辺演算の問題点

データベースクエリを作成する際、様々な演算子を使用してデータを目的の形に変換したり、計算したりすることがよくあります。

例えば、特定の年のデータだけをフィルタリングしたい場合には、次のようなクエリを思い浮かべることができます。

SELECT * FROM sales WHERE YEAR(date) = 2021;

このクエリはsalesテーブルでdate列の年が2021年であるすべてのデータを検索してほしいというリクエストです。ここでYEAR(date)はdate列から年の部分のみを抽出する関数です。

ユーザーの立場ではこのような方法はとても直感的で理解しやすいですが、データベースの観点からは効率性に大きな問題が発生する可能性があります。

データソースを変更して自分が探したい範囲と比較する演算は、データベースがインデックスを適切に利用できないようにします。まるで本のページ番号を関数演算で計算して探しているようなものです。

インデックスは元データをそのまま使って作成されます。例えば、date列のインデックスは「2023-06-01」、「2023-06-02」のような日付の値自体で構成されます。

そのため、先ほど見たようにYEAR(date) = 2021のようにデータを変換する演算を実行すると、このインデックスをうまく利用することができなくなります。
一行ずつYEAR(date)演算を実行して、その結果が2021かどうかを確認するわけです。データが多ければ多いほど、これは膨大な作業量になるしかありません。

このような場合、データベースはインデックスによる迅速な検索ができず、結局、すべてのデータを最初から最後まで一通り調べなければなりません。

効果的な代替案: 右辺でのデータフィルタリング

左辺での演算がインデックスの利用を妨げることがわかったので、クエリのパフォーマンスを向上させるためのより良い方法を探してみましょう。

それは、データを「変換」せずに元の形を維持したまま必要なデータを見つけることです。
そのためには、「期間」を指定してデータをフィルタリングする方法を用いることができます。

例えば以下のような感じです。

SELECT * FROM sales 
WHERE date >= '2021-01-01' AND date <= '2021-12-31';

このクエリはsalesテーブルでdate列の値が2021年1月1日から2021年12月31日までの間にある全てのデータを検索します。

ここで注目すべき点はdate列をそのまま使っていることです。別の演算を実行せずに日付の値自体を直接比較しています。

これにより、データベースはdate列のインデックスを効果的に活用することができます。インデックスに保存された日付の値とクエリで指定した期間を比較しながら、必要なデータをすばやく見つけることができます。

このように元データを直接比較する条件を使用することが、インデックスを最大限に活用し、クエリのパフォーマンスを向上させる核心的な秘訣と言えますね。

---

2.ORの代わりにUNIONを使う

データを照会する場合、複数の条件を満たす結果を得るために、私たちはしばしばOR演算子を使います。

例えば、「Marketing」部門もしくは「IT」部門に属する社員を探したい場合、次のようなクエリを作成することができます。

SELECT * FROM employees 
WHERE department = 'Marketing' OR department = 'IT';

このクエリはシンプルで直感的ですが、パフォーマンスの面では最善の選択ではないかもしれません。

OR演算子を使用すると、データベースは1回のスキャンですべての条件を確認する必要があります。この過程で不要なデータまで大量に検索することになり、特にインデックスを適切に活用できない場合が多いです。

department列にインデックスがあるとします。OR演算子により、データベースはこのインデックスを効率的に使用することができなくなります。

インデックスは単一値に対する高速検索のために最適化されていますが、ORは複数の値を同時に検索しなければならないので、結局、インデックスのメリットを活かすことができず、データ全体を探さなければならない状況が発生するのです。

この問題を解決するためにUNIONを利用することができます。UNIONは各条件に対するクエリを別々に実行した後、その結果を合算する演算子です。

上のクエリをUNIONを使って次のように置き換えることができます。

SELECT * FROM employees WHERE department = 'Marketing'
UNION
SELECT * FROM employees WHERE department = 'IT';

このようにすると、データベースは各クエリを個別に最適化して実行することができます。

department = 'Marketing'とdepartment= 'IT'は、それぞれインデックスを通じてすばやく処理することができます。そして、UNIONが2つの結果を結合します。この過程で重複した結果は自動的に削除されます。

重複がないことが確実な場合は、UNION ALLを使用して重複削除のステップをスキップしてパフォーマンスを向上させることもできます。しかし、ほとんどの場合、正確な結果を得るためにUNIONの使用が推奨されます。

このように、ORの代わりにUNIONを使用することは、複雑なクエリのパフォーマンスを最適化する効果的な方法の一つです。

各条件ごとに個別のクエリを実行してインデックスを活用することで、データベースの負荷を軽減し、全体的なクエリパフォーマンスを大幅に向上させることができます。

---

3.必要な行と列のみを選択してパフォーマンスを最適化する

データベースからデータを照会する場合、不要な情報まで全てを取得することはパフォーマンス低下の主な原因の一つです。

代わりに必要なデータのみを取得することがパフォーマンス最適化の核心と言えます。 これを実際のクエリの例で詳しく説明します。

特定の条件を満たす行のみを選択する

例えば、「Marketing」部門に属し、売上高が100,000以上の社員の名前とメールアドレスを照会したいとします。

SELECT name, email
FROM employees
WHERE department = 'Marketing' AND sales >= 100000;

このクエリはemployeesテーブルで部門が「Marketing」で売上高が100,000以上の社員の名前(name)とメールアドレス(email)の情報のみを選択します。

不要な行を除外して必要な列のみを選択することで、データベースが処理しなければならないデータの量を最小化するのです。これはクエリの応答速度を高めてシステム負荷を軽減するのに大きな助けになります。

サブクエリを活用して必要なデータのみを抽出する

今回は各部門別に最大の売上高を達成した社員の情報を照会する状況を想定してみます。

SELECT e.name, e.department, e.sales
FROM employees e
JOIN (
  SELECT department, MAX(sales) AS max_sales
  FROM employees
  GROUP BY department
) d ON e.department = d.department AND e.sales = d.max_sales;

このクエリではサブクエリを利用して部門別の最大売上高を計算した後、この結果とemployeesテーブルを結合して必要な情報だけを抽出しています。

サブクエリで不要な列を除いてdepartmentとmax_salesのみを選択することで、中間結果のサイズを最小化しています。

そして、これを基に最終的に必要な社員の名前、部門、売上高情報のみを照会するのです。このようにするとクエリの効率を大幅に向上することができます。

このように、データベースから情報を照会する場合は、できるだけ必要な行と列のみを選択することが重要です。

不要なデータ処理を減らすことで応答時間を短縮し、システムリソースを効率的に活用することができます。これはデータベースパフォーマンス最適化の基本の一つと言えますね。

---

4.分析関数でクエリのパフォーマンスを向上させる

分析関数(Analytic Functions)は、SQLクエリのパフォーマンスを一段階高める強力なツールです。単にデータを処理することを超えて、データ分析とクエリ最適化において重要な役割を果たします。

これらの関数は、複雑なデータセット内で各行ごとに詳細な計算を可能にします。また、データ全体にわたって様々な統計と計算を柔軟に実行することができます。代表的な関数として、ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG()などの関数があります。

クエリ効率を向上させる分析関数

分析関数を使うと、SQLクエリの効率が多くの点で向上します。

まず、従来の集計関数とは異なり、事前にデータをグループ化する必要がないため、不要なリソース消費を減らし、クエリのパフォーマンスを向上させるのに非常に役立ちます。

また、複雑なデータ分析の過程で発生する可能性のある中間結果の保存と再処理を最小限に抑えることができ、クエリの実行時間を大幅に短縮することができます。

ランキング関数によるデータの順序付け

ROW_NUMBER()、 RANK()、 DENSE_RANK()などの関数は、データ内の各項目の順位付けをするのに便利です。

例えば、部門別に給与が高い社員順に順位を付けたい場合はROW_NUMBER()関数を使うことができます。

SELECT 
  name, 
  department, 
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

このクエリは、各部門内で給与の高い順に社員に一意の順位を付与します。

RANK()とDENSE_RANK()関数も似たような方法で動作しますが、同じ値に対して同じランク(順位)を付与するという点が異なります。DENSE_RANK()の場合、ランクの間隔を常に1に保つという特徴があります。

データの変化を追跡する分析関数

LEAD()やLAG()関数を使用すると、現在の行を基準として前の行または次の行のデータを参照することができます。

これを利用すれば、各社員の年収の変化率を簡単に計算することができます。

SELECT
  name,
  salary,
  LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS prev_salary,
  salary - LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS salary_increase
FROM employees;

このクエリは、各部門内で入社日順に社員をソートした後、以前の社員の年収と現在の社員の年収の差を計算して、年収の上昇額を求めています。

このような関数は、特に時系列データや連続したデータセットを扱う場合に、以前のデータポイントとの比較が必要となる分析に非常に役立ちます。

分析関数によるデータフィルタリングの最適化

もし、各部門別に給与が高い上位3人の社員情報のみを抽出したい場合は、ROW_NUMBER()関数を利用して次のようなクエリを作成することができます。

WITH ranked_employees AS (
  SELECT 
    name, 
    department, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
  FROM employees
)
SELECT *
FROM ranked_employees
WHERE rank <= 3;

このクエリはまず、各部門内で給与の順位を付けた後、順位が3以下の社員のみを選択するように動作します。

これにより、最初にデータ全体をスキャンすることなく、必要な結果だけをすばやくフィルタリングすることができます。

分析関数を利用すれば、このようにSQLクエリの効率とパフォーマンスを大幅に向上させることができます。今後、データ処理と分析の過程で、このような分析関数を積極的に活用することをお勧めします！

---

5.ワイルドカード(%)は最後に置く方が良い

SQLでLIKE演算子と一緒にワイルドカード(%)を使うと、テキストデータを柔軟に検索することができ、本当に便利ですよね。

ところで、このワイルドカードの位置によってクエリのパフォーマンスが大きく変わることがあるという事実、ご存じでしたか？

これからワイルドカードを文字列の末尾に置くことがなぜパフォーマンスの最適化に役立つのかを調べて、実際のコード例でその違いを確認してみましょう！

ワイルドカードの位置が重要な理由

まず、文字列の前にワイルドカードを記述する場合を見てみましょう。

SELECT * FROM users WHERE name LIKE '%John';

このクエリは「John」で終わるすべての名前を見つけようとします。しかし、問題はワイルドカードが前にある場合は、データベースが「John」で終わるすべての可能な文字列の組み合わせをいちいち検索しなければならないということです。

結果として、データベースは膨大なリソースを消費し、クエリの速度が遅くなり、インデックスがあってもそれをうまく活用することができません。

では、次に文字列の後ろにワイルドカードを記述する場合を見てみましょう。

SELECT * FROM users WHERE name LIKE 'John%';

このクエリは「John」で始まるすべての名前を探しています。このようにすると、データベースがインデックスを活用して検索範囲を効果的に絞り込むことができます。

データベースは最初にインデックスから「John」で始まる最初のエントリをすばやく見つけます。そして次に「John」で始まらない最初のエントリが見つかるまで検索すればいいのです。

このようにLIKE演算子とワイルドカード(%)を使用する場合は、なるべく文字列の末尾にワイルドカードを記述すると、データベースがインデックスをよりよく活用できるようになります。

---

6.計算値を事前に保存しておいて、後で照会する

データベースで複雑な計算をリアルタイムで処理することは、クエリのパフォーマンスに大きな負担となることがあります。大量のデータを扱う場合は特にそうです。

このような状況では、頻繁に使われる計算値をあらかじめ保存しておき、必要に応じてすぐに取り出すことが効果的な最適化方法となることがあります。

リアルタイム計算の非効率性

例えば、eコマースサイトで各商品の平均購入金額、総売上高、購入者数、再購入率など様々な統計値をリアルタイムで計算するとします。

SELECT 
    p.product_id,
    AVG(od.quantity * od.unit_price) AS avg_order_amount,
    SUM(od.quantity * od.unit_price) AS total_sales,
    COUNT(DISTINCT o.customer_id) AS num_purchasers,
    COUNT(DISTINCT CASE WHEN o.customer_id IN (
        SELECT customer_id 
        FROM orders
        WHERE product_id = p.product_id
        GROUP BY customer_id
        HAVING COUNT(*) > 1
    ) THEN o.customer_id END) * 1.0 / COUNT(DISTINCT o.customer_id) AS repurchase_rate
FROM 
    products p
    JOIN order_details od ON p.product_id = od.product_id
    JOIN orders o ON od.order_id = o.order_id
GROUP BY 
    p.product_id;

このクエリはproductsテーブル、order_detailsテーブル、ordersテーブルを結合して商品(product_id)別に平均購入金額(avg_order_amount)、総売上高(total_sales)、購入者数(num_purchasers)、再購入率(repurchase_rate)を計算しています。

問題は、このクエリが実行されるたびに膨大な量の注文データと顧客データの両方を読み込んで複雑な計算を実行しなければならないことです。特に、再購入率の計算のためにサブクエリまで使用されているので、クエリの速度はさらに遅くなるしかないでしょう。

さらに、このような統計値が頻繁に使用される場合、同じ複雑な計算を繰り返し行うことになり、膨大なリソースの浪費が発生します。

計算値を保存して利用する

このような問題を解決するために、計算結果を別のテーブルに保存しておくことができます。

CREATE TABLE product_stats AS
SELECT
    p.product_id,
    AVG(od.quantity * od.unit_price) AS avg_order_amount,
    SUM(od.quantity * od.unit_price) AS total_sales,
    COUNT(DISTINCT o.customer_id) AS num_purchasers,
    COUNT(DISTINCT CASE WHEN o.customer_id IN (
        SELECT customer_id
        FROM orders
        WHERE product_id = p.product_id
        GROUP BY customer_id
        HAVING COUNT(*) > 1
    ) THEN o.customer_id END) * 1.0 / COUNT(DISTINCT o.customer_id) AS repurchase_rate
FROM
    products p
    JOIN order_details od ON p.product_id = od.product_id
    JOIN orders o ON od.order_id = o.order_id
GROUP BY
    p.product_id;

このクエリは最初にproduct_statsという新しいテーブルを作成し、先ほどの複雑な計算を実行して各商品の統計値をあらかじめ保存しています。

このようにしておけば、後でこのような統計値が必要な場合に、このテーブルから直接に値を取得することができます。複雑なリアルタイム計算の代わりに、あらかじめ保存された値を使うので、クエリの速度が格段に速くなりますね。

定期的な計算結果の更新

もちろん、注文データが新たに追加されるたびに統計値を更新する必要があります。しかし、これはリアルタイムで行う必要はありません。

代わりに一定のサイクル(例えば、1日1回)ごとに統計値を更新するバッチ処理を実行すればいいのです。

UPDATE product_stats ps
SET
    avg_order_amount = (
        SELECT AVG(od.quantity * od.unit_price)
        FROM order_details od
        WHERE od.product_id = ps.product_id
    ),
    total_sales = (
        SELECT SUM(od.quantity * od.unit_price)
        FROM order_details od
        WHERE od.product_id = ps.product_id  
    ),
    num_purchasers = (
        SELECT COUNT(DISTINCT o.customer_id)
        FROM order_details od
        JOIN orders o ON od.order_id = o.order_id
        WHERE od.product_id = ps.product_id
    ),
    repurchase_rate = (
        SELECT 
            COUNT(DISTINCT CASE WHEN o.customer_id IN (
                SELECT customer_id
                FROM orders
                WHERE product_id = ps.product_id
                GROUP BY customer_id
                HAVING COUNT(*) > 1
            ) THEN o.customer_id END) * 1.0 / COUNT(DISTINCT o.customer_id)
        FROM order_details od 
        JOIN orders o ON od.order_id = o.order_id
        WHERE od.product_id = ps.product_id
    );

このクエリはproduct_statsテーブルの統計値を最新の注文履歴に基づいて更新します。

各統計値ごとにサブクエリを使用して最新の値を計算し、その結果、product_statsテーブルの値を更新しています。

このように計算結果を保存して定期的に更新することで、複雑なリアルタイムクエリの負担を大幅に軽減することができます。

よく使われる統計値、集計値などはあらかじめ計算して保存しておくと、パフォーマンスの最適化に大きな助けになるという事実、ぜひ覚えておいてください！

---

まとめ

1. データを変換する演算はできるだけ避けて、元のデータを直接比較する条件を使用してください。これにより、インデックスの活用度を高め、クエリ速度を向上させることができます。
2. OR演算子の代わりにUNIONを利用すると、各条件を独立して最適化し、インデックスを効果的に使用することができます。
3. 不要な行と列を除外して、必要なデータのみを照会してください。これにより、データ処理量(スループット)が最小限に抑えられ、クエリのパフォーマンスが向上します。
4. ROW_NUMBER()、RANK()、LEAD()、LAG()などの分析関数を積極的に活用すれば、複雑なデータ分析を柔軟かつ効率的に行うことができます。
5. LIKE演算子とワイルドカード(%)を使用する場合は、文字列の末尾にワイルドカードを記述する方がインデックスの活用に有利です。
6. 複雑な計算はリアルタイムで処理するよりも、あらかじめ計算・保存しておいて定期的に更新する方が効率的です。

---

今日も皆様のお役に立てれば幸いです。
"Everyone is an Analyst"、HEARTCOUNTチームのJadenでした！