执行计划

一、EXPLAIN基础 1.1 什么是EXPLAIN EXPLAIN SELECT * FROM users WHERE id = 10; 作用： 查看SQL执行计划 不实际执行查询 分析索引使用情况 优化查询性能 1.2 基本用法 -- 方式1：EXPLAIN EXPLAIN SELECT ...; -- 方式2：DESCRIBE（同义词） DESCRIBE SELECT ...; -- 方式3：查看实际执行（MySQL 8.0.18+） EXPLAIN ANALYZE SELECT ...; 二、id - 查询标识 2.1 含义 查询的执行顺序标识。 EXPLAIN SELECT * FROM users WHERE id = 10; 结果： id: 1 ← 简单查询 2.2 子查询 EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); 结果： id | table ----+-------- 1 | users 2 | orders ← 先执行id=2（子查询） 规则： id大的先执行 id相同，从上往下执行 三、select_type - 查询类型 3.1 SIMPLE 简单查询，无子查询或UNION。 ...

查询优化器概述 查询优化器（Optimizer） 的作用是为SQL生成最优执行计划。 -- 原始SQL SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.name = 'Alice' AND o.status = 'PAID'; -- 优化器决策 1. 表连接顺序：先users还是orders？ 2. 索引选择：使用哪个索引？ 3. 连接算法：Nested Loop、Hash Join、Sort-Merge Join？ 4. 条件下推：WHERE条件何时过滤？ 优化器类型 1. 基于规则的优化器（RBO） Rule-Based Optimizer：根据预定义规则选择执行计划。 -- 规则示例 1. 有索引优于无索引 2. 主键索引优于二级索引 3. 小表驱动大表 -- 缺点： 不考虑数据分布，可能选择非最优计划 2. 基于成本的优化器（CBO） Cost-Based Optimizer（MySQL使用）：根据成本评估选择最优计划。 -- 成本因素 1. IO成本：磁盘读取次数 2. CPU成本：记录比较次数 3. 内存成本：临时表、排序开销 -- 优点： 考虑数据分布，选择更优计划 成本评估 1. 成本模型 总成本 = IO成本 + CPU成本 IO成本 = 页数 × IO_BLOCK_READ_COST CPU成本 = 行数 × ROW_EVALUATE_COST -- 默认值（可调整） IO_BLOCK_READ_COST = 1.0 ROW_EVALUATE_COST = 0.1 2. 索引扫描成本 -- 全表扫描（Table Scan） 成本 = 表页数 × 1.0 + 表行数 × 0.1 -- 索引扫描（Index Scan） 成本 = 索引页数 × 1.0 + 回表行数 × 0.1 + 回表IO × 1.0 -- 示例 表：100万行，10000页 索引：1000页，回表10万行 全表扫描成本 = 10000 × 1.0 + 1000000 × 0.1 = 110000 索引扫描成本 = 1000 × 1.0 + 100000 × 0.1 + 100000 × 1.0 = 111000 -- 优化器选择：全表扫描（成本更低） 优化策略 1. 条件化简 -- 原始条件 WHERE id > 0 AND id < 100 AND id = 50 -- 化简后 WHERE id = 50 -- 原始条件 WHERE (status = 'PAID' OR status = 'PAID') AND user_id = 1 -- 化简后 WHERE status = 'PAID' AND user_id = 1 2. 常量折叠 -- 原始 WHERE price > 100 * 2 -- 优化后 WHERE price > 200 -- 原始 WHERE YEAR(created_at) = 2025 -- 无法优化（函数作用于列，索引失效） 3. 谓词下推（Predicate Pushdown） -- 原始SQL SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.name = 'Alice'; -- 优化后（谓词下推） SELECT * FROM orders o JOIN (SELECT * FROM users WHERE name = 'Alice') u ON o.user_id = u.id; -- WHERE条件先过滤users表，减少JOIN的数据量 4. 外连接消除 -- 原始SQL SELECT * FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE u.id IS NOT NULL; -- 优化后（LEFT JOIN → INNER JOIN） SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- WHERE条件保证u.id不为NULL，可以消除LEFT JOIN 表连接顺序 1. 小表驱动大表 -- 示例 users表：100行 orders表：100万行 -- ✅ 好：users驱动orders FOR EACH user IN users (100次) FOR EACH order IN orders WHERE order.user_id = user.id END FOR -- 总循环次数：100次（users） × 平均100次（orders） = 10000次 -- ❌ 不好：orders驱动users FOR EACH order IN orders (100万次) FOR EACH user IN users WHERE user.id = order.user_id END FOR -- 总循环次数：100万次（效率低） 2. 连接算法 Nested Loop Join（嵌套循环）： ...