-- 分析SELECT語句
EXPLAIN SELECT * FROM users WHERE id = 1;
-- 分析DELETE/UPDATE/INSERT（MySQL 5.6+）
EXPLAIN DELETE FROM users WHERE status = 'inactive';
-- 顯示更詳細信息（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;  -- 實際執(zhí)行并顯示耗時
-- 輸出JSON格式（便于程序解析）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;
-- 查看連接中正在執(zhí)行的SQL的執(zhí)行計劃
EXPLAIN FOR CONNECTION 123;  -- 123為connection_id

EXPLAIN SELECT o.order_id, u.user_name 
FROM orders o 
LEFT JOIN users u ON o.user_id = u.user_id 
WHERE o.order_status = 'pending' 
  AND o.created_at >= '2024-01-01' 
LIMIT 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
   partitions: NULL
         type: range
possible_keys: idx_status_created,idx_created_at
          key: idx_status_created
      key_len: 102
          ref: NULL
         rows: 185000
     filtered: 100.00
        Extra: Using index condition
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.o.user_id
         rows: 1
     filtered: 100.00
        Extra: NULL

-- id相同：多表JOIN
EXPLAIN SELECT * FROM orders o, users u WHERE o.user_id = u.user_id;
-- 結果：兩個id均為1
-- id不同：子查詢
EXPLAIN SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders);
-- 結果：子查詢id=2，外層查詢id=1（子查詢先執(zhí)行）

-- 避免DEPENDENT SUBQUERY（子查詢依賴外層）
EXPLAIN SELECT * FROM users u 
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) > 5;
-- 優(yōu)化：改為JOIN + GROUP BY
-- 注意DERIVED臨時表開銷
EXPLAIN SELECT * FROM (SELECT * FROM orders WHERE status='pending') t;
-- 建議：直接查詢，或創(chuàng)建視圖

system > const > eq_ref > ref > range > index > ALL

-- const：最優(yōu)
EXPLAIN SELECT * FROM users WHERE user_id = 1;
-- type=const
-- ref：良好
EXPLAIN SELECT * FROM orders WHERE order_status = 'pending';
-- type=ref（假設status有索引）
-- range：可接受
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- type=range（假設created_at有索引）
-- ALL：必須優(yōu)化
EXPLAIN SELECT * FROM orders WHERE order_status = 'pending';
-- type=ALL（status無索引）

-- possible_keys有值但key為NULL：索引未被使用
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';
-- possible_keys: idx_created_at, key: NULL
-- 原因：對索引字段使用了函數(shù)，索引失效
-- 實際使用了聯(lián)合索引
EXPLAIN SELECT * FROM orders WHERE order_status = 'pending' AND created_at > '2024-01-01';
-- key: idx_status_created（聯(lián)合索引）

-- 聯(lián)合索引：idx_status_created (order_status VARCHAR(20), created_at DATETIME)
SHOW INDEX FROM orders;  -- 查看索引定義
EXPLAIN SELECT * FROM orders 
WHERE order_status = 'pending' 
  AND created_at >= '2024-01-01';
-- key_len = 83
-- 計算：order_status(20*3+2=62) + created_at(8) + NULL標志(1) = 71? 實際83包含額外開銷
-- 如果只使用第一個字段
EXPLAIN SELECT * FROM orders WHERE order_status = 'pending';
-- key_len = 62（僅使用了order_status字段）

EXPLAIN SELECT * FROM orders o, users u 
WHERE o.user_id = u.user_id 
  AND o.order_status = 'pending';
-- table=o 的 ref: const (order_status='pending')
-- table=u 的 ref: test.o.user_id (關聯(lián)到orders表的user_id)

-- 全表掃描：rows ≈ 表總行數(shù)
EXPLAIN SELECT * FROM orders WHERE order_status = 'pending';
-- rows: 5,000,000（全表）
-- 添加索引后：rows大幅下降
EXPLAIN SELECT * FROM orders WHERE order_status = 'pending';
-- rows: 250,000（索引過濾后）

EXPLAIN SELECT * FROM orders 
WHERE order_status = 'pending' 
  AND user_id = 100;
-- rows: 250000, filtered: 1.00
-- 實際返回行數(shù) ≈ 2500（過濾掉了99%的數(shù)據(jù)）
-- 優(yōu)化：創(chuàng)建(status, user_id)聯(lián)合索引
-- 優(yōu)化后 rows: 50, filtered: 100.00

-- 問題SQL
EXPLAIN SELECT * FROM orders WHERE status='pending' ORDER BY created_at DESC;
-- Extra: Using where; Using filesort
-- 優(yōu)化：添加(status, created_at)聯(lián)合索引
CREATE INDEX idx_status_created ON orders(status, created_at);
-- 優(yōu)化后Extra不再出現(xiàn)filesort

-- 問題SQL
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*);
-- Extra: Using temporary; Using filesort
-- 優(yōu)化：拆分為兩個查詢，或調整GROUP BY/ORDER BY順序

-- 覆蓋索引：查詢字段都在索引中
EXPLAIN SELECT order_id, user_id FROM orders WHERE order_id = 100;
-- Extra: Using index（主鍵索引覆蓋）
-- 創(chuàng)建覆蓋索引示例
CREATE INDEX idx_cover ON orders(status, created_at, order_id);
EXPLAIN SELECT status, created_at, order_id FROM orders WHERE status='pending';
-- Extra: Using index（無需回表）

1. 執(zhí)行EXPLAIN，獲取執(zhí)行計劃
   ↓
2. 檢查type：是否為ALL或index？
   ↓ 是 → 添加索引
   ↓ 否
3. 檢查key：是否為NULL？
   ↓ 是 → 分析索引失效原因
   ↓ 否
4. 檢查rows：是否過大？
   ↓ 是 → 優(yōu)化索引選擇性
   ↓ 否
5. 檢查Extra：是否有Using filesort/Using temporary？
   ↓ 是 → 優(yōu)化排序/分組索引
   ↓ 否
6. 性能良好

EXPLAIN SELECT 
    DATE(o.created_at) as order_date,
    u.user_level,
    COUNT(*) as order_count,
    SUM(o.order_amount) as total_amount
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.created_at >= '2024-01-01'
  AND o.created_at < '2024-02-01'
  AND o.order_status IN ('paid', 'shipped')
GROUP BY DATE(o.created_at), u.user_level
ORDER BY order_date DESC, u.user_level;

-- 1. 創(chuàng)建聯(lián)合索引
CREATE INDEX idx_status_created ON orders(order_status, created_at);
-- 2. 改寫SQL，避免DATE()函數(shù)
SELECT 
    DATE(o.created_at) as order_date,
    u.user_level,
    COUNT(*) as order_count,
    SUM(o.order_amount) as total_amount
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.created_at >= '2024-01-01'
  AND o.created_at < '2024-02-01'
  AND o.order_status IN ('paid', 'shipped')
GROUP BY DATE(o.created_at), u.user_level
ORDER BY order_date DESC, u.user_level;
-- 3. 考慮使用匯總表（物化視圖）預處理

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_status = 'pending'\G

-> Filter: (orders.order_status = 'pending')  (cost=101.23 rows=1850) (actual time=0.123..0.456 rows=1234 loops=1)
    -> Index lookup on orders using idx_status (order_status='pending')  (cost=101.23 rows=1850) (actual time=0.098..0.234 rows=1234 loops=1)