SQLを最適化する30の方法

Posted at 2025-01-07

1. 適切なインデックスを選択する

ヒント

クエリで頻繁に使用される列に対して、適切なインデックス（単一列、複合インデックスなど）を作成します。

例

問題のある SQL:

SELECT name FROM employees WHERE department_id = 10;

最適化: department_idにインデックスを作成します:

CREATE INDEX idx_department_id ON employees(department_id);

2. SELECT * を避ける

ヒント

必要な列だけをクエリして、返されるデータ量を削減します。

例

問題のある SQL:

SELECT * FROM employees WHERE department_id = 10;

最適化: 必要な列だけをクエリします:

SELECT name FROM employees WHERE department_id = 10;

3. サブクエリよりも JOIN を優先する

ヒント

サブクエリは一般的に JOIN よりも効率が悪いです。

例

問題のある SQL:

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

最適化: サブクエリの代わりに JOIN を使用します:

SELECT e.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.location = 'New York';

4. EXPLAIN を使用してクエリを分析する

ヒント

EXPLAINまたはEXPLAIN ANALYZEを使用して SQL クエリの実行計画を確認し、パフォーマンスのボトルネックを特定します。

例

EXPLAIN SELECT name FROM employees WHERE department_id = 10;

5. 不要な ORDER BY 操作を避ける

ヒント

ORDER BYは特に大規模なデータセットで多くのリソースを消費します。本当に必要な場合にのみ使用してください。

例

問題のある SQL:

SELECT name FROM employees WHERE department_id = 10 ORDER BY hire_date;

最適化: ソートが不要な場合はORDER BYを削除します。

6. LIMIT を使用したページネーションクエリの最適化

ヒント

ページネーションにはLIMITを使用します。オフセットが大きいクエリの場合、インデックスやキャッシュを活用して最適化します。

例

問題のある SQL:

SELECT name FROM employees ORDER BY hire_date LIMIT 100000, 10;

最適化: 主キーまたはインデックスを使用してページネーションのパフォーマンスを向上させます:

SELECT name FROM employees WHERE id > 100000 ORDER BY hire_date LIMIT 10;

7. WHERE 句で関数を使用しない

ヒント

関数呼び出しはインデックスの使用を妨げるため、可能な限り避けます。

例

問題のある SQL:

SELECT name FROM employees WHERE YEAR(hire_date) = 2023;

最適化: 範囲クエリを使用します:

SELECT name FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

8. 複合インデックスの適切な順序を選択する

ヒント

複合インデックスでは、選択性が高い列を最初に配置します。

例

以下のクエリの場合:

SELECT * FROM employees WHERE department_id = 10 AND status = 'active';

選択性を高めるために、statusを最初にしたインデックスを作成します:

CREATE INDEX idx_status_department ON employees(status, department_id);

9. 単一挿入ではなくバッチ挿入を使用する

ヒント

バッチ挿入により、I/O とロックのオーバーヘッドを大幅に削減できます。

例

問題のある SQL: レコードを 1 つずつ挿入:

INSERT INTO employees (name, department_id) VALUES ('John', 10);

最適化: バッチ挿入を使用します:

INSERT INTO employees (name, department_id) VALUES ('John', 10), ('Alice', 20), ('Bob', 30);

10. NOT IN の使用を避ける

ヒント

NOT INはパフォーマンスが悪いため、NOT EXISTSまたはLEFT JOINに置き換えます。

例

問題のある SQL:

SELECT name FROM employees WHERE department_id NOT IN (SELECT id FROM departments);

最適化: LEFT JOINを使用します:

SELECT e.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id WHERE d.id IS NULL;

11. 冗長な DISTINCT を避ける

ヒント

重複データを削除する必要がある場合にのみDISTINCTを使用します。

例

問題のある SQL:

SELECT DISTINCT name FROM employees WHERE department_id = 10;

最適化: 重複削除が不要な場合はDISTINCTを削除します。

12. 適切な結合タイプを使用する

ヒント

すべてのデータが必要な場合を除き、INNER JOINを優先します。不要なLEFT JOINやRIGHT JOINは避けてください。

例

問題のある SQL:

SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

最適化: INNER JOINを使用します:

SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

13. テーブルのパーティション分割を使用する

ヒント

大規模なテーブルを分割することでクエリのパフォーマンスを向上させます。

例

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    hire_date DATE
)
PARTITION BY RANGE (YEAR(hire_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

14. GROUP BY クエリの最適化

ヒント

インデックスを使用してGROUP BYクエリを最適化します。

例

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

15. IN の使用を最適化する

ヒント

大規模なIN操作では、データを一時テーブルに格納してJOINを使用します。

例

問題のある SQL:

SELECT name FROM employees WHERE department_id IN (1, 2, 3, 4, 5);

最適化: ID を一時テーブルに格納します:

CREATE TEMPORARY TABLE temp_ids (id INT);
INSERT INTO temp_ids (id) VALUES (1), (2), (3), (4), (5);
SELECT e.name FROM employees e JOIN temp_ids t ON e.department_id = t.id;

SELECT e.name, d.name
FROM employees e JOIN departments d
ON e.department_id = d.id
WHERE e.location = 'New York';

最適化: グローバル集約を行う前に、ローカルノードでロケーション関連データを処理します。

22. 複数列インデックスとインデックスマージの利用

ヒント

複数列をクエリする際、可能であれば複合インデックスを使用します。利用できない場合、データベースはインデックスマージを試みます。

例

問題のある SQL:

SELECT * FROM orders WHERE customer_id = 10 AND product_id = 5;

最適化: customer_idとproduct_idにインデックスを結合し、パフォーマンスを向上させます。EXPLAINを使用してインデックスマージが利用されているか確認してください。

23. CUBE および ROLLUP を使用した多次元分析の最適化

ヒント

CUBEやROLLUPを使用して多次元集約を行うことで、複数のGROUP BYクエリを削減できます。

例

問題のある SQL: 複数のGROUP BYクエリ。

SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;
SELECT region, SUM(sales) FROM sales_data GROUP BY region;

最適化: ROLLUPを使用して複数レベルで集約:

SELECT department_id, region, SUM(sales)
FROM sales_data
GROUP BY department_id, region WITH ROLLUP;

24. ウィンドウ関数を使用した複雑な分析クエリの最適化

ヒント

ウィンドウ関数（例: ROW_NUMBER()、RANK()、LAG()、LEAD()）を使用すると、自己結合やサブクエリの必要性が減り、複雑な分析が簡略化されます。

例

問題のある SQL: 前のレコードを取得するための自己結合。

SELECT a.*,
       (SELECT sales FROM sales_data b WHERE b.id = a.id - 1) AS previous_sales
FROM sales_data a;

最適化: ウィンドウ関数を使用:

SELECT id, sales, LAG(sales, 1) OVER (ORDER BY id) AS previous_sales
FROM sales_data;

25. 大規模テーブルのパーティションプリューニング

ヒント

非常に大規模なテーブルでは、パーティションプリューニングを使用してデータスキャン範囲を制限します。

例

問題のある SQL:

SELECT * FROM transactions WHERE transaction_date BETWEEN '2023-01-01' AND '2023-01-31';

最適化: テーブルを日付でパーティション分割し、プリューニングを活用:

CREATE TABLE transactions (
    id INT,
    amount DECIMAL(10, 2),
    transaction_date DATE
)
PARTITION BY RANGE (YEAR(transaction_date)) (
    PARTITION p2023 VALUES LESS THAN (2024)
);

26. 一時テーブルの使用を最小限にする

ヒント

複雑なクエリでの一時テーブル使用を減らします。一時テーブルはディスク I/O を増加させ、パフォーマンスに影響を与えます。

例

問題のある SQL: 中間結果を保存するための一時テーブル。

CREATE TEMPORARY TABLE temp_sales AS
SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;

最適化: サブクエリや共通テーブル式（CTE）を使用:

WITH temp_sales AS (
    SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id
)
SELECT * FROM temp_sales;

27. 並列クエリの最適化

ヒント

大規模なデータセットに対して並列クエリを活用して効率を向上させます。

例

問題のある SQL: 並列処理を行わない大規模なデータスキャン。

SELECT SUM(sales) FROM sales_data;

最適化: 並列クエリを有効化:

ALTER SESSION ENABLE PARALLEL QUERY;
SELECT /*+ PARALLEL(sales_data, 4) */ SUM(sales) FROM sales_data;

28. 複雑なクエリの高速化にマテリアライズドビューを使用

ヒント

複雑な集約クエリには、事前計算された結果を保存するマテリアライズドビューを使用します。

例

問題のある SQL: パフォーマンスにボトルネックがある複雑な集約クエリ。

SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;

最適化: マテリアライズドビューを作成:

CREATE MATERIALIZED VIEW mv_sales_data AS
SELECT department_id, SUM(sales) AS total_sales FROM sales_data GROUP BY department_id;

29. ロック競合を回避して並行クエリを最適化

ヒント

高並行環境では、適切なロック機構を使用してテーブルや行ロックを回避します。

例

問題のある SQL: 高並行性下でのテーブルロックによるパフォーマンス低下。

SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

最適化: 特定の行だけをロック:

SELECT * FROM orders WHERE order_id = 123 FOR UPDATE;

30. ロック時間を短縮してトランザクションを最適化

ヒント

長時間実行されるトランザクションでは、ロック時間を最小限に抑え、ロックの範囲を減らします。

例

問題のある SQL: トランザクション中にテーブルをロックする大規模データ操作。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

最適化: トランザクションを分割するか、ロック時間を短縮:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;