SQLパフォーマンス向上のための勉強まとめ

## はじめに
SQLのパフォーマンスは応答性やリソース活用において重要な要素である。低パフォーマンスSQLはアプリ遅延、コスト増加、ユーザー不満の原因になる。

## 基本原則

### 1. 必要なデータだけを取得する
- `SELECT *` は避け、必要な列のみを指定する
- ディスク読み取り量、ネットワークトラフィック、メモリ使用量を削減できる

### 2. インデックスを効果的に活用する
- インデックスは検索速度を大幅に向上させる
- WHERE句、JOIN条件、ORDER BY句で使用される列にインデックスを作成する
- 主な種類:
  - クラスタインデックス: テーブルデータを物理的に並べ替え（主キーに最適）
  - 非クラスタインデックス: インデックスとデータへのポインタを別々に格納
  - フルテキストインデックス: 大量のテキストデータ検索用

```sql
CREATE INDEX index_name ON table_name (column_name);
```

**注意点**:
- INSERT/UPDATE/DELETEの速度は若干低下する可能性あり
- 使われていないインデックスは定期的に削除
- カーディナリティ（ユニークな値の数）が低い列へのインデックスは効果薄い

### 3. クエリ実行計画を理解する
- 実行計画はパフォーマンスのボトルネック特定に役立つ
- `EXPLAIN` コマンドで実行計画を確認可能
- 注意すべき操作:
  - フルテーブルスキャン
  - インデックススキャンとインデックスシーク（シークの方が効率的）
  - 一時テーブル作成やソート操作
  - 非効率的なJOIN操作

## JOIN操作の最適化

### JOINの種類と特性

| JOIN Type | 戻り値 | パフォーマンス上の注意点 | 使用例 |
|-----------|--------|--------------------------|--------|
| INNER JOIN | 両方のテーブルに一致する行のみ | インデックスがあれば効率的 | 注文をした顧客の検索 |
| LEFT JOIN | 左テーブルの全行＋右テーブルの一致行(なければNULL) | 右テーブルが大きいとインデックス重要 | 全顧客とその注文表示(注文なしも含む) |
| RIGHT JOIN | 右テーブルの全行＋左テーブルの一致行(なければNULL) | LEFT JOINと同様 | 全注文と対応顧客表示 |
| FULL OUTER JOIN | 左右いずれかに一致がある全行 | 大きなテーブルでは低パフォーマンス | 全顧客と全注文の関連表示 |
| CROSS JOIN | 両テーブルのデカルト積 | 大きなテーブルでは避ける | あらゆる組み合わせが必要な場合のみ |

### JOINのベストプラクティス
- 結合条件の列にインデックスを作成
- 結合条件はWHERE句ではなくON句で指定
- データ型の互換性を確認（型変換はインデックス使用を妨げる）
- 不必要なCROSS JOINは避ける

## WHERE句の最適化 

### 1. **HAVING 句より WHERE 句を優先（グループ化前にフィルタリング）**
`WHERE` 句は **GROUP BY の前** に適用されるため、不要なデータを事前に排除でき、パフォーマンスが向上します。一方、`HAVING` 句は **GROUP BY の後** に適用されるため、より多くのデータを処理する必要があり、非効率になる場合があります。  

✅ **効率的な例（WHERE を使用）**
```sql
SELECT department, AVG(salary) 
FROM employees 
WHERE department IN ('Sales', 'Engineering')  -- 事前に不要なデータを排除
GROUP BY department;
```

🚫 **非効率な例（HAVING を使用）**
```sql
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING department IN ('Sales', 'Engineering');  -- グループ化後にフィルタリング
```
**理由:** `HAVING` で絞り込むと、全てのデータをグループ化してから不要なデータを排除するため、処理コストが高くなります。

**例外:** 集約関数（`SUM()`, `AVG()`, `COUNT()` など）を使った条件は `HAVING` でしかフィルタリングできない。  
```sql
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 50000;  -- これは WHERE では指定できない
```
### 2. **関数適用は避け、列に直接比較を行う**
`WHERE` 句内で関数を適用すると、インデックスが効かずフルスキャンになる可能性があるため、できるだけ **生の列値に対して比較** を行うのがベストプラクティスです。

🚫 **非効率な例（関数適用あり）**
```sql
SELECT * FROM orders 
WHERE YEAR(order_date) = 2024;
```
**問題点:**  
- `YEAR(order_date)` により `order_date` のインデックスが効かなくなり、フルスキャンが発生。  

✅ **効率的な例（関数を使わない）**
```sql
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
```
**理由:**  
- `order_date` に対して直接比較を行うため、インデックスが利用されやすい。

### 3. **LIKE の先頭ワイルドカード（%）は避ける**
`LIKE '%文字列'` を使うと、インデックスが効かず **フルスキャン** になり、検索コストが高くなります。  

🚫 **非効率な例（先頭ワイルドカードあり）**
```sql
SELECT * FROM customers 
WHERE name LIKE '%Smith';
```
**問題点:**  
- 先頭の `%` によりインデックスが利用できず、すべてのレコードをスキャンする必要がある。

✅ **効率的な例（先頭ワイルドカードなし）**
```sql
SELECT * FROM customers 
WHERE name LIKE 'Smith%';
```
**理由:**  
- `Smith%` なら先頭部分が確定しており、B-Tree インデックスを利用可能。

**例外:** `LIKE '%Smith%'` を避けられない場合、`FULLTEXT INDEX` を活用するのが望ましい。

### 4. **範囲検索は複数の OR 条件より BETWEEN を使用**
`OR` で複数の範囲条件を指定すると、SQL のオプティマイザが適切にインデックスを利用できない可能性があります。そのため、`BETWEEN` を使うのがベストプラクティスです。

🚫 **非効率な例（OR を使用）**
```sql
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' OR order_date <= '2024-12-31';
```
✅ **効率的な例（BETWEEN を使用）**
```sql
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
```
**理由:**  
- `BETWEEN` は範囲検索に最適化されており、適切にインデックスが利用されやすい。

### 5. **存在チェックは IN より EXISTS を使用（パフォーマンス向上）**
`IN` 句は **小さいリスト** であれば問題ありませんが、サブクエリと組み合わせると非効率になりやすく、`EXISTS` の方が効率的な場合が多いです。

🚫 **非効率な例（IN を使用）**
```sql
SELECT * FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE active = 1);
```
**問題点:**  
- `IN` の内部サブクエリは、一度 **全ての`department_id` を取得し、それと比較** するため、非効率になりやすい。

✅ **効率的な例（EXISTS を使用）**
```sql
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.department_id = e.department_id 
    AND d.active = 1
);
```
**理由:**  
- `EXISTS` は **最初にマッチした時点で処理を終了** するため、不要な検索を省略できる。  
- `IN` よりも **最適化されやすい**（特に大規模データ）。

**例外:** `IN` もインデックスが適用されるケースでは問題ないため、実行計画を確認することが重要。

## **サブクエリで考慮すべきこと**  

サブクエリは、特にパフォーマンスに影響を与えやすいため、慎重に使用する必要がある。

### **1. 相関サブクエリ（外クエリの各行ごとに実行）はボトルネックになりやすい**
**相関サブクエリとは？**
- 外部クエリの各行ごとに **内部サブクエリが繰り返し実行** されるサブクエリのこと。
- これにより **行ごとにサブクエリが評価される** ため、パフォーマンスが著しく低下する可能性がある。

**❌ 非効率な例（相関サブクエリ）**
```sql
SELECT e.id, e.name, (SELECT d.name FROM departments d WHERE d.id = e.department_id) AS department_name
FROM employees e;
```
**問題点:**  
- `employees` の **各行ごとに** `departments` テーブルの `id` を検索するため、`employees` の行数が多いと処理が遅くなる。
- `employees` が 10,000 行あれば、10,000 回のサブクエリ実行が発生する可能性がある。

**✅ 効率的な例（JOIN に置き換え）**
```sql
SELECT e.id, e.name, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
```
**改善点:**  
- `JOIN` を使うことで **一度の結合で済み**、パフォーマンスが大幅に向上。  
- 適切なインデックスが利用されるため、検索速度が向上。

### **2. 深くネストされたサブクエリは最適化が難しい**
**ネストされたサブクエリとは？**
- サブクエリの中にさらにサブクエリを含む構造。
- 最適化が難しく、データベースの **オプティマイザが適切にインデックスを利用できない可能性** がある。

**❌ 非効率な例（ネストされたサブクエリ）**
```sql
SELECT name 
FROM employees 
WHERE department_id IN (
    SELECT id FROM departments 
    WHERE company_id IN (
        SELECT id FROM companies WHERE industry = 'IT'
    )
);
```
**問題点:**  
- サブクエリが **3 層にネスト** されているため、最適化が難しく、パフォーマンスが低下。  
- `companies` → `departments` → `employees` の順にデータを取得するため、効率が悪い。  

**✅ 効率的な例（JOIN に置き換え）**
```sql
SELECT e.name 
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN companies c ON d.company_id = c.id
WHERE c.industry = 'IT';
```
**改善点:**  
- `JOIN` により **一度の結合処理でデータを取得できる**。  
- データベースの **インデックスが適切に使用される** ため、処理速度が向上。

### **3. 可能なら JOIN に書き換える**
- サブクエリの多くは `JOIN` に置き換え可能であり、その方が一般的に **パフォーマンスが良い**。
- `JOIN` は **データベースのオプティマイザが適切に処理しやすい** ため、インデックスの活用が容易。

**❌ 非効率な例（サブクエリを使用）**
```sql
SELECT name, salary 
FROM employees 
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');
```
**問題点:**  
- サブクエリを評価するために `departments` を **一度検索** してから `employees` を検索するため、効率が悪い。
- `departments` に該当する行が複数あるとエラーになる (`department_id` に一意性がない場合)。

**✅ 効率的な例（JOIN に書き換え）**
```sql
SELECT e.name, e.salary 
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales';
```
**改善点:**  
- `JOIN` にすることで、データの検索回数が減り、パフォーマンスが向上。

### **4. 存在チェックは IN より EXISTS を使用**
**`IN` の問題点**
- `IN` は **リストが小さい場合は問題ない** が、サブクエリと組み合わせると非効率になることが多い。
- 特に **サブクエリが大きな結果セットを返す場合、すべての値と比較する必要があり処理コストが増加する**。

**❌ 非効率な例（IN を使用）**
```sql
SELECT * 
FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE active = 1);
```
**問題点:**  
- `departments` の全ての `id` を取得してから `employees` に適用するため、**比較回数が増加**。
- `departments` のレコードが多い場合、パフォーマンスが低下。

**✅ 効率的な例（EXISTS を使用）**
```sql
SELECT * 
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.id = e.department_id 
    AND d.active = 1
);
```
**改善点:**  
- `EXISTS` は **最初に条件を満たした時点で処理を終了** するため、不要な比較を減らせる。
- 大量データの場合、`IN` より `EXISTS` の方が高速に処理できることが多い。

**例外:**  
- `IN` も **インデックスが適用されるケース** では問題ないため、実行計画（`EXPLAIN`）を確認することが重要。

## **ビューで考慮すべきこと**

ビューはデータベースの **仮想テーブル** であり、複雑なクエリを簡略化するために有効でである。ただし、ビューの設計次第では **パフォーマンスに悪影響を及ぼす** 可能性があるため、慎重に活用する必要がある。

### **1. 複雑なクエリの簡略化に有効**
**ビューを使用するメリット**
- **複雑なクエリをカプセル化** し、再利用しやすくする。
- **SQL の可読性と保守性を向上** させる。
- **アプリケーションやユーザーからデータの複雑さを隠蔽** できる。

**✅ 効率的な利用例**
**長い結合クエリの簡略化**
```sql
CREATE VIEW employee_salary_view AS
SELECT e.id, e.name, d.name AS department_name, s.amount AS salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN salaries s ON e.id = s.employee_id;
```
**メリット:**  
- `employee_salary_view` を使用することで、アプリケーションやレポート作成時に **複雑な JOIN クエリを毎回書く必要がなくなる**。  
- クエリの **再利用性** が高まり、保守が容易になる。

**✅ ビューを使った簡潔なクエリ**
```sql
SELECT * FROM employee_salary_view WHERE salary > 50000;
```
**メリット:**  
- `employee_salary_view` を使えば、JOIN の詳細を気にせずにクエリを実行できる。

### **2. 単純なビューは直接クエリと同程度のパフォーマンス**
- ビューは **仮想テーブル** であり、データ自体を保持しない（通常のビューの場合）。
- 基本的に **ビューはその都度展開され、元のテーブルのデータを取得する** ため、ビューを使っても **直接同じクエリを実行するのと同じパフォーマンス** になる。

**✅ 単純なビュー（パフォーマンスの影響が少ない）**
```sql
CREATE VIEW active_employees AS
SELECT id, name, department_id 
FROM employees 
WHERE status = 'active';
```
この場合、以下の 2 つのクエリの **実行速度はほぼ同じ** です。
```sql
SELECT * FROM active_employees;
```
```sql
SELECT id, name, department_id FROM employees WHERE status = 'active';
```
**理由:**  
- `active_employees` は **単純なフィルタリング** しか行っていないため、ビューを介しても元の `employees` テーブルに直接アクセスするのと変わらない。

**例外:**  
- 一部のデータベース（例えば MySQL）では、ビューに **インデックスが適用されにくい** 場合があるため、大量データを扱う場合は注意が必要。

### **3. 複雑なビューは基となる操作のパフォーマンス特性を継承**
**ビューの中に複雑な処理（JOIN, 集約, サブクエリなど）が含まれると、元のテーブルのパフォーマンス問題をそのまま引き継ぐ**。
- ビューのクエリが **非効率であれば、ビューを使用するすべてのクエリも遅くなる**。
- 特に、**ネストされたビュー**（ビューを基に別のビューを作成する）を避けることが重要。

**❌ 非効率な複雑なビュー**
```sql
CREATE VIEW department_salary_summary AS
SELECT d.name AS department_name, AVG(s.amount) AS avg_salary
FROM employees e
JOIN salaries s ON e.id = s.employee_id
JOIN departments d ON e.department_id = d.id
GROUP BY d.name;
```
このビューを使ってさらに集計すると、パフォーマンスが悪化しやすい。
```sql
SELECT * FROM department_salary_summary WHERE avg_salary > 60000;
```
**問題点:**  
- `department_salary_summary` では **すでに `GROUP BY` と `AVG()` を実行している**。  
- これをさらに外側でフィルタリングすると、**元のクエリが何度も実行される可能性** がある。

**✅ 効率的な解決策**
1. **マテリアライズドビュー（Materialized View）を利用する**
   - マテリアライズドビューは **データを物理的に保存** するため、頻繁に計算が必要なビューに向いている。
   - 例えば PostgreSQL の `REFRESH MATERIALIZED VIEW` を活用することで、計算結果をキャッシュできる。

2. **インデックスを適用する**
   - ビューを介してアクセスするカラムに適切な **インデックスを作成** することで、パフォーマンスを向上できる。

3. **ビューを分割し、必要最小限のデータのみ取得**
   - 例えば、給与が 60,000 以上の従業員を対象とするビューを個別に作成することで、不要なフィルタリングを減らせる。

```sql
CREATE VIEW high_salary_employees AS
SELECT e.id, e.name, d.name AS department_name, s.amount AS salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN salaries s ON e.id = s.employee_id
WHERE s.amount > 60000;
```
このビューを使えば、以下のようなクエリの効率が上がる：
```sql
SELECT * FROM high_salary_employees;
```
**メリット:**  
- `WHERE s.amount > 60000` をあらかじめビュー内で適用しているため、余分なデータを処理する必要がなくなる。