EXPLAINコマンドの活用法

EXPLAINはMySQLのSQL実行計画を分析するための強力なツールです。以下の構文で使用できます：

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]
    
{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}
    
{EXPLAIN | DESCRIBE | DESC} ANALYZE select_statement    

explain_type: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt: {
    SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

実行例：

EXPLAIN FORMAT=JSON 
SELECT o.order_id, o.customer_name, o.order_date,
       p.product_name, p.category, p.price,
       d.quantity, d.discount
FROM orders o
JOIN order_details d ON o.order_id = d.order_id
JOIN products p ON d.product_id = p.product_id
WHERE o.status = 'completed'
  AND o.order_date >= '2023-01-01'
  AND p.category = 'electronics';

出力結果の詳細解説

idの詳細

クエリを一意に識別します。複数のid値がある場合、数値が大きいほど先に実行されます。同じidの場合は上から順に実行されます。

select_typeの種類

type（結合方式）の詳細

性能順（良い→悪い）：

1. system：テーブルが1行のみ（システムテーブル相当）
2. const：主キーまたはユニークインデックスの等価検索
3. eq_ref：結合で各テーブルが1行のみ一致
4. ref：インデックスの等価検索（一致行が複数可能）
5. fulltext：全文インデックス検索
6. ref_or_null：ref + NULL値の検索
7. index_merge：複数のインデックスを統合使用
8. unique_subquery：ユニークインデックスのサブクエリ
9. index_subquery：非ユニークインデックスのサブクエリ
10. range：インデックスの範囲検索
11. index：インデックス全スキャン
12. ALL：テーブル全スキャン（最悪）

Extraフィールドの重要な値

• Using filesort：ORDER BYでインデックスが使用できず、ソートが必要
• Using temporary：GROUP BYやORDER BYで一時テーブルを作成
• Using index：カバリングインデックスを使用（テーブルアクセス不要）
• Using index condition：インデックス条件プッシュダウンを使用
• Using where：WHERE句でフィルタリングを実行
• Using join buffer：結合バッファを使用
• Distinct：DISTINCTを最適化
• Not exists：LEFT JOINを最適化
• Range checked for each record：各レコードで範囲検索を評価

実践的なEXPLAIN分析

以下に実用的な分析例を示します：

-- パフォーマンスが良い例
EXPLAIN SELECT employee_id, name 
FROM employees 
WHERE emp_no = 'E1001';

-- 結果:
-- type: const (主キー検索)
-- key: PRIMARY
-- rows: 1

-- 改善が必要な例
EXPLAIN SELECT * 
FROM sales s
JOIN customers c ON s.customer_id = c.id
WHERE s.sales_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND c.region = 'Kanto';

-- 結果例:
-- type: ALL (テーブル全スキャン)
-- Extra: Using where; Using join buffer

拡張EXPLAIN機能

MySQL 8.0以降では、EXPLAIN後にSHOW WARNINGSで詳細情報を確認できます：

EXPLAIN 
SELECT p.product_id, p.name,
       (SELECT AVG(price) FROM price_history 
        WHERE product_id = p.product_id) as avg_price
FROM products p
WHERE p.category = 'books';

SHOW WARNINGS;

クエリ性能の見積もり

ディスク検索回数で性能を見積もれます：

大規模テーブルの場合：

検索回数 = log(行数) / log(インデックスブロック長 / 3 * 2 / (インデックス長 + データポインタ長)) + 1

例：500,000行、キー3バイトの場合：

log(500000) / log(1024/3*2/(3+4)) + 1 = 約4回検索

最適化のベストプラクティス

• WHERE句のカラムにインデックスを作成
• 複合インデックスではカーディナリティの高いカラムを先頭に
• SELECT *を避け、必要なカラムのみ指定
• Joyin時は常に結合キーにインデックスを使用
• EXPLAINのtypeがconst、eq_ref、refになるよう最適化
• Extraに「Using filesort」「Using temporary」が表示されないように

インデックス設計のヒント

効果的なインデックス設計例：

-- 悪い例
SELECT * FROM orders 
WHERE customer_id = 12345 
  AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND status = 'completed';

-- 良いインデックス
CREATE INDEX idx_customer_date_status 
ON orders(customer_id, order_date, status);

インデックス使用確認：

EXPLAIN SELECT order_id, total_amount 
FROM orders 
WHERE customer_id = 12345 
  AND order_date >= '2023-01-01'
  AND status = 'completed';