MySQL  EXPLAIN 关键参数实战举例

-- 分析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;  -- 实际执行并显示耗时
-- 输出json格式（便于程序解析）
explain format=json select * from users where id = 1;
-- 查看连接中正在执行的sql的执行计划
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（子查询先执行）

-- 避免dependent subquery（子查询依赖外层）
explain select * from users u 
where (select count(*) from orders o where o.user_id = u.user_id) > 5;
-- 优化：改为join + group by
-- 注意derived临时表开销
explain select * from (select * from orders where status='pending') t;
-- 建议：直接查询，或创建视图

system > const > eq_ref > ref > range > index > all

-- const：最优
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：必须优化
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
-- 原因：对索引字段使用了函数，索引失效
-- 实际使用了联合索引
explain select * from orders where order_status = 'pending' and created_at > '2024-01-01';
-- key: idx_status_created（联合索引）

-- 联合索引：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 (关联到orders表的user_id)

-- 全表扫描：rows ≈ 表总行数
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
-- 实际返回行数 ≈ 2500（过滤掉了99%的数据）
-- 优化：创建(status, user_id)联合索引
-- 优化后 rows: 50, filtered: 100.00

-- 问题sql
explain select * from orders where status='pending' order by created_at desc;
-- extra: using where; using filesort
-- 优化：添加(status, created_at)联合索引
create index idx_status_created on orders(status, created_at);
-- 优化后extra不再出现filesort

-- 问题sql
explain select status, count(*) from orders group by status order by count(*);
-- extra: using temporary; using filesort
-- 优化：拆分为两个查询，或调整group by/order by顺序

-- 覆盖索引：查询字段都在索引中
explain select order_id, user_id from orders where order_id = 100;
-- extra: using index（主键索引覆盖）
-- 创建覆盖索引示例
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. 执行explain，获取执行计划
   ↓
2. 检查type：是否为all或index？
   ↓ 是 → 添加索引
   ↓ 否
3. 检查key：是否为null？
   ↓ 是 → 分析索引失效原因
   ↓ 否
4. 检查rows：是否过大？
   ↓ 是 → 优化索引选择性
   ↓ 否
5. 检查extra：是否有using filesort/using temporary？
   ↓ 是 → 优化排序/分组索引
   ↓ 否
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. 创建联合索引
create index idx_status_created on orders(order_status, created_at);
-- 2. 改写sql，避免date()函数
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)