本文档风哥主要介绍MySQL查询优化的实战技巧，包括EXPLAIN使用、查询重写、索引策略、连接优化等内容，风哥教程参考MySQL官方文档Optimization章节，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。更多视频教程www.fgedu.net.cn
Part01-基础概念与理论知识
1.1 查询执行过程
理解MySQL查询执行过程是优化的基础：
1. 查询执行流程
MySQL查询执行流程：
+——————-+
| 1. 客户端发送SQL |
+——————-+
|
v
+——————-+
| 2. 连接器验证 |
+——————-+
|
v
+——————-+
| 3. 查询缓存检查 | (MySQL 8.0已移除)
+——————-+
|
v
+——————-+
| 4. 分析器解析 |
+——————-+
|
v
+——————-+
| 5. 预处理器处理 |
+——————-+
|
v
+——————-+
| 6. 优化器优化 |
+——————-+
|
v
+——————-+
| 7. 执行器执行 |
+——————-+
|
v
+——————-+
| 8. 返回结果 |
+——————-+
2. 分析器工作
分析器进行词法分析和语法分析：
词法分析：
– 识别SQL关键字
– 识别表名、列名
– 识别常量、运算符
语法分析：
– 检查语法正确性
– 生成解析树
示例：
mysql> SELEC * FROM users;
ERROR 1064 (42000): You have an error in your SQL syntax
输出示例：
错误提示指出语法错误位置
3. 预处理器工作
预处理器进行检查：
– 检查表是否存在
– 检查列是否存在
– 检查权限
– 解析视图
示例：
mysql> SELECT * FROM non_existent_table;
ERROR 1146 (42S02): Table ‘test.non_existent_table’ doesn’t exist
输出示例：
表不存在的错误提示
4. 优化器工作
优化器选择最优执行计划：
– 选择索引
– 选择连接顺序
– 选择访问方法
– 估算成本
查看优化器决策：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’;
输出示例：
+—-+————-+——–+————+——+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+————+
| 1 | SIMPLE | orders | NULL | ref | idx_status | idx_status |
+—-+————-+——–+————+——+—————+————+
5. 执行器工作
执行器执行查询：
– 调用存储引擎接口
– 获取数据
– 返回结果
查看执行状态：
mysql> SHOW STATUS LIKE ‘Handler%’;
输出示例：
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| Handler_read_first | 1 |
| Handler_read_key | 100 |
| Handler_read_next | 1000 |
| Handler_read_rnd_next | 0 |
+—————————-+——-+
1.2 EXPLAIN使用方法
EXPLAIN是分析查询执行计划的核心工具：
1. 基本使用
基本语法：
EXPLAIN SELECT …
示例：
mysql> EXPLAIN SELECT * FROM orders WHERE id = 1\G
输出示例：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
2. EXPLAIN输出列说明
id：查询标识符
– 相同id表示从上到下顺序执行
– 不同id表示子查询，id越大越先执行
select_type：查询类型
+———————-+——————————————+
| 类型 | 描述 |
+———————-+——————————————+
| SIMPLE | 简单SELECT（无UNION/子查询） |
| PRIMARY | 最外层SELECT |
| SUBQUERY | 子查询 |
| DERIVED | 派生表 |
| UNION | UNION中的第二个或后面的SELECT |
| UNION RESULT | UNION的结果 |
+———————-+——————————————+
type：访问类型（从好到差）
+———————-+——————————————+
| 类型 | 描述 |
+———————-+——————————————+
| system | 单行表（系统表） |
| const | 单行匹配（主键/唯一索引） |
| eq_ref | 唯一索引扫描 |
| ref | 非唯一索引扫描 |
| fulltext | 全文索引 |
| ref_or_null | 包含NULL的ref |
| index_merge | 索引合并 |
| range | 范围扫描 |
| index | 全索引扫描 |
| ALL | 全表扫描 |
+———————-+——————————————+
示例：
mysql> EXPLAIN SELECT * FROM orders WHERE status IN (‘pending’, ‘processing’);
输出示例：
+—-+————-+——–+————+——-+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——-+—————+————+
| 1 | SIMPLE | orders | NULL | range | idx_status | idx_status |
+—-+————-+——–+————+——-+—————+————+
3. EXPLAIN FORMAT
JSON格式：
mysql> EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE id = 1\G
输出示例：
*************************** 1. row ***************************
EXPLAIN: {
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “1.00”
},
“table”: {
“table_name”: “orders”,
“access_type”: “const”,
“possible_keys”: [“PRIMARY”],
“key”: “PRIMARY”,
“used_key_parts”: [“id”],
“key_length”: “4”,
“ref”: [“const”],
“rows_examined_per_scan”: 1,
“rows_produced_per_join”: 1,
“filtered”: “100.00”,
“cost_info”: {
“read_cost”: “0.00”,
“eval_cost”: “0.10”
}
}
}
}
TREE格式（MySQL 8.0.16+）：
mysql> EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE id = 1\G
输出示例：
*************************** 1. row ***************************
EXPLAIN: -> Rows fetched before SELECT (cost=0.00..0.00 rows=1)
4. EXPLAIN ANALYZE
MySQL 8.0.18+支持EXPLAIN ANALYZE：
mysql> EXPLAIN ANALYZE SELECT * FROM orders WHERE status = ‘pending’;
输出示例：
+—————————————————————————+
| EXPLAIN |
+—————————————————————————+
| -> Filter: (orders.status = ‘pending’) (cost=100.50 rows=100)
(actual time=0.100..5.000 rows=100 loops=1)
-> Table scan on orders (cost=100.50 rows=1000)
(actual time=0.050..4.000 rows=1000 loops=1)
+—————————————————————————+
输出说明：
– cost：预估成本
– rows：预估行数
– actual time：实际执行时间
– rows：实际返回行数
– loops：循环次数
5. 关键指标解读
Extra列重要信息：
+—————————+——————————————+
| 信息 | 含义 |
+—————————+——————————————+
| Using index | 使用索引覆盖 |
| Using where | 使用WHERE过滤 |
| Using temporary | 使用临时表 |
| Using filesort | 使用文件排序 |
| Using join buffer | 使用连接缓冲区 |
| Impossible WHERE | WHERE条件不可能为真 |
+—————————+——————————————+
示例：
mysql> EXPLAIN SELECT * FROM orders ORDER BY created_at;
输出示例：
+—-+————-+——–+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+——+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL |
+—-+————-+——–+————+——+—————+——+
| Extra: Using filesort |
+———————————————————————+
1.3 优化器介绍
MySQL优化器负责选择最优的执行计划：
1. 优化器类型
MySQL使用基于成本的优化器（CBO）：
– 计算各种执行计划的成本
– 选择成本最低的执行计划
– 成本包括：I/O成本、CPU成本
查看优化器成本模型：
mysql> SELECT * FROM mysql.server_cost;
输出示例：
+——————————+————+———————+
| cost_name | cost_value | default_value |
+——————————+————+———————+
| disk_temptable_create_cost | NULL | 20.00 |
| disk_temptable_row_cost | NULL | 0.50 |
| key_compare_cost | NULL | 0.05 |
| memory_temptable_create_cost | NULL | 1.00 |
| memory_temptable_row_cost | NULL | 0.10 |
| row_evaluate_cost | NULL | 0.10 |
+——————————+————+———————+
2. 优化器开关
查看优化器开关：
mysql> SHOW VARIABLES LIKE ‘optimizer_switch’;
输出示例：
+——————+———————————————+
| Variable_name | Value |
+——————+———————————————+
| optimizer_switch | index_merge=on,…,loosescan=on,… |
+——————+———————————————+
常用优化器开关：
– index_merge：索引合并
– index_merge_union：索引合并UNION
– index_merge_sort_union：索引合并排序UNION
– index_merge_intersection：索引合并交集
– engine_condition_pushdown：引擎条件下推
– index_condition_pushdown：索引条件下推
– mrr：多范围读取
– mrr_cost_based：基于成本的MRR
– block_nested_loop：块嵌套循环连接
– batched_key_access：批量键访问
3. 优化器提示
使用优化器提示控制执行计划：
索引提示：
mysql> SELECT * FROM orders USE INDEX (idx_status)
WHERE status = ‘pending’;
输出示例：
+—-+————+——–+———————+
| id | status | amount | created_at |
+—-+————+——–+———————+
| 1 | pending | 100.00 | 2026-04-01 12:00:00 |
+—-+————+——–+———————+
强制使用索引：
mysql> SELECT * FROM orders FORCE INDEX (idx_status)
WHERE status = ‘pending’;
忽略索引：
mysql> SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = ‘pending’;
4. 优化器跟踪
启用优化器跟踪：
mysql> SET SESSION optimizer_trace=’enabled=on’;
执行查询：
mysql> SELECT * FROM orders WHERE status = ‘pending’;
查看跟踪信息：
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
输出示例：
*************************** 1. row ***************************
QUERY: SELECT * FROM orders WHERE status = ‘pending’
TRACE: {
“steps”: [
{
“join_preparation”: {
“select_id”: 1,
“steps”: […]
}
},
{
“join_optimization”: {
“select_id”: 1,
“steps”: […]
}
}
]
}
关闭跟踪：
mysql> SET SESSION optimizer_trace=’enabled=off’;
输出示例：
Query OK, 0 rows affected (0.00 sec)
5. 直方图统计
创建直方图：
mysql> ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;
输出示例：
+——————-+———–+———-+———————————————-+
| Table | Op | Msg_type | Msg_text |
+——————-+———–+———-+———————————————-+
| production_db.orders | histogram | status | Histogram statistics created for column ‘status’ |
+——————-+———–+———-+———————————————-+
查看直方图：
mysql> SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_NAME = ‘orders’;
输出示例：
+—————-+————-+————-+————+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | HISTOGRAM |
+—————-+————-+————-+————+
| production_db | orders | status | {…} |
+—————-+————-+————-+————+
Part02-生产环境规划与建议
2.1 查询重写技术
查询重写是优化查询的重要技术：
1. 避免SELECT *
问题：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’;
输出示例：
+—-+————-+——–+————+——+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+————+
| 1 | SIMPLE | orders | NULL | ref | idx_status | idx_status |
+—-+————-+——–+————+——+—————+————+
| Extra: NULL |
+——————————————————————-+
优化：
mysql> EXPLAIN SELECT id, status, amount FROM orders
WHERE status = ‘pending’;
输出示例：
+—-+————-+——–+————+——+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+————+
| 1 | SIMPLE | orders | NULL | ref | idx_status | idx_status |
+—-+————-+——–+————+——+—————+————+
| Extra: Using index |
+——————————————————————-+
说明：Using index表示使用了索引覆盖，无需回表
2. 使用LIMIT限制结果
问题：
mysql> SELECT * FROM orders;
优化：
mysql> SELECT * FROM orders LIMIT 100;
输出示例：
+—-+————+——–+———————+
| id | status | amount | created_at |
+—-+————+——–+———————+
| 1 | pending | 100.00 | 2026-04-01 12:00:00 |
| 2 | processing | 200.00 | 2026-04-01 12:01:00 |
+—-+————+——–+———————+
3. 避免在WHERE中使用函数
问题：
mysql> EXPLAIN SELECT * FROM orders
WHERE DATE(created_at) = ‘2026-04-01’;
输出示例：
+—-+————-+——–+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+——+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL |
+—-+————-+——–+————+——+—————+——+
| Extra: Using where |
+——————————————————————-+
优化：
mysql> EXPLAIN SELECT * FROM orders
WHERE created_at >= ‘2026-04-01’ AND created_at < '2026-04-02';
输出示例：
+----+-------------+--------+------------+-------+---------------+------------+
| id | select_type | table | partitions | type | possible_keys | key |
+----+-------------+--------+------------+-------+---------------+------------+
| 1 | SIMPLE | orders | NULL | range | idx_created | idx_created|
+----+-------------+--------+------------+-------+---------------+------------+
4. 使用UNION ALL替代OR
问题：
mysql> EXPLAIN SELECT * FROM orders
WHERE status = ‘pending’ OR amount > 1000;
输出示例：
+—-+————-+——–+————+——+—————–+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————–+——+
| 1 | SIMPLE | orders | NULL | ALL | idx_status | NULL |
+—-+————-+——–+————+——+—————–+——+
优化：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’
UNION ALL
SELECT * FROM orders WHERE amount > 1000;
输出示例：
+—-+————–+————-+————+——+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————–+————-+————+——+—————+————+
| 1 | PRIMARY | orders | NULL | ref | idx_status | idx_status |
| 2 | UNION | orders | NULL | ALL | NULL | NULL |
+—-+————–+————-+————+——+—————+————+
5. 使用EXISTS替代IN
问题：
mysql> EXPLAIN SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE status = ‘active’);
输出示例：
+—-+————-+———–+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+———–+————+——+—————+——+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL |
| 2 | SIMPLE | customers | NULL | ref | PRIMARY | PRIMARY|
+—-+————-+———–+————+——+—————+——+
优化：
mysql> EXPLAIN SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c
WHERE c.id = o.customer_id AND c.status = ‘active’);
输出示例：
+—-+————-+——-+————+——+—————+————-+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——-+————+——+—————+————-+
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL |
| 1 | SIMPLE | c | NULL | eq_ref| PRIMARY | PRIMARY |
+—-+————-+——-+————+——+—————+————-+
2.2 索引使用策略
合理使用索引是查询优化的核心：
1. 最左前缀原则
创建复合索引：
mysql> CREATE INDEX idx_status_created_amount ON orders(status, created_at, amount);
输出示例：
Query OK, 0 rows affected (5.12 sec)
有效使用：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’;
输出示例：
+—-+————-+——–+————+——+————————+————————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+————————+————————+
| 1 | SIMPLE | orders | NULL | ref | idx_status_created_amt | idx_status_created_amt |
+—-+————-+——–+————+——+————————+————————+
有效使用：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’ AND created_at > ‘2026-01-01’;
输出示例：
+—-+————-+——–+————+——-+————————+————————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——-+————————+————————+
| 1 | SIMPLE | orders | NULL | range | idx_status_created_amt | idx_status_created_amt |
+—-+————-+——–+————+——-+————————+————————+
无效使用：
mysql> EXPLAIN SELECT * FROM orders WHERE created_at > ‘2026-01-01’;
输出示例：
+—-+————-+——–+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+——+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL |
+—-+————-+——–+————+——+—————+——+
2. 索引覆盖
创建索引：
mysql> CREATE INDEX idx_covering ON orders(status, amount);
输出示例：
Query OK, 0 rows affected (3.45 sec)
使用索引覆盖：
mysql> EXPLAIN SELECT status, amount FROM orders WHERE status = ‘pending’;
输出示例：
+—-+————-+——–+————+——+—————+————–+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+————–+
| 1 | SIMPLE | orders | NULL | ref | idx_covering | idx_covering |
+—-+————-+——–+————+——+—————+————–+
| Extra: Using index |
+——————————————————————-+
3. 索引条件下推
启用ICP：
mysql> SET SESSION optimizer_switch=’index_condition_pushdown=on’;
输出示例：
Query OK, 0 rows affected (0.00 sec)
使用ICP：
mysql> EXPLAIN SELECT * FROM orders
WHERE status = ‘pending’ AND amount > 100;
输出示例：
+—-+————-+——–+————+——+—————+————–+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+————–+
| 1 | SIMPLE | orders | NULL | ref | idx_covering | idx_covering |
+—-+————-+——–+————+——+—————+————–+
| Extra: Using index condition |
+——————————————————————-+
4. 索引合并
查看索引合并：
mysql> EXPLAIN SELECT * FROM orders
WHERE status = ‘pending’ OR customer_id = 100;
输出示例：
+—-+————-+——–+————+————-+———————+———————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+————-+———————+———————+
| 1 | SIMPLE | orders | NULL | index_merge | idx_status,idx_cust | idx_status,idx_cust |
+—-+————-+——–+————+————-+———————+———————+
| Extra: Using union(idx_status,idx_cust) |
+——————————————————————-+
5. 索引选择
查看索引基数：
mysql> SHOW INDEX FROM orders;
输出示例：
+——–+————+————————+————–+————-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+——–+————+————————+————–+————-+
| orders | 0 | PRIMARY | 1 | id |
| orders | 1 | idx_status | 1 | status |
| orders | 1 | idx_customer | 1 | customer_id |
+——–+————+————————+————–+————-+
分析索引：
mysql> ANALYZE TABLE orders;
输出示例：
+——————-+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————-+———+———-+———-+
| production_db.orders | analyze | status | OK |
+——————-+———+———-+———-+
2.3 连接查询优化
连接查询优化是复杂查询的关键：
1. 连接类型
INNER JOIN：内连接
mysql> EXPLAIN SELECT o.*, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
输出示例：
+—-+————-+——-+————+——–+—————+————-+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——-+————+——–+—————+————-+
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY |
+—-+————-+——-+————+——–+—————+————-+
LEFT JOIN：左连接
mysql> EXPLAIN SELECT o.*, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;
输出示例：
+—-+————-+——-+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——-+————+——+—————+——+
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL |
| 1 | SIMPLE | c | NULL | ref | PRIMARY | PRIMARY|
+—-+————-+——-+————+——+—————+——+
2. 连接顺序优化
小表驱动大表：
mysql> EXPLAIN SELECT /*+ JOIN_ORDER(c, o) */ o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
输出示例：
+—-+————-+——-+————+——–+—————+————-+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——-+————+——–+—————+————-+
| 1 | SIMPLE | c | NULL | ALL | PRIMARY | NULL |
| 1 | SIMPLE | o | NULL | ref | idx_customer | idx_customer|
+—-+————-+——-+————+——–+—————+————-+
3. 块嵌套循环连接
配置BNL：
mysql> SET SESSION optimizer_switch=’block_nested_loop=on’;
输出示例：
Query OK, 0 rows affected (0.00 sec)
查看BNL使用：
mysql> EXPLAIN SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
输出示例：
+—-+————-+——-+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——-+————+——+—————+——+
| 1 | SIMPLE | c | NULL | ALL | PRIMARY | NULL |
| 1 | SIMPLE | o | NULL | ref | idx_customer | idx_customer|
+—-+————-+——-+————+——+—————+——+
| Extra: Using join buffer (Block Nested Loop) |
+——————————————————————-+
4. 批量键访问
配置BKA：
mysql> SET SESSION optimizer_switch=’batched_key_access=on,mrr=on,mrr_cost_based=off’;
输出示例：
Query OK, 0 rows affected (0.00 sec)
查看BKA使用：
mysql> EXPLAIN SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
输出示例：
+—-+————-+——-+————+——+—————+————-+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——-+————+——+—————+————-+
| 1 | SIMPLE | c | NULL | ALL | PRIMARY | NULL |
| 1 | SIMPLE | o | NULL | ref | idx_customer | idx_customer|
+—-+————-+——-+————+——+—————+————-+
| Extra: Using join buffer (Batched Key Access) |
+——————————————————————-+
5. 连接缓冲区配置
查看连接缓冲区大小：
mysql> SHOW VARIABLES LIKE ‘join_buffer_size’;
输出示例：
+——————+——–+
| Variable_name | Value |
+——————+——–+
| join_buffer_size | 262144 |
+——————+——–+
调整连接缓冲区：
mysql> SET SESSION join_buffer_size = 1048576;
输出示例：
Query OK, 0 rows affected (0.00 sec)
Part03-生产环境项目实施方案
3.1 SELECT优化
SELECT语句优化是查询优化的核心：
1. 只查询需要的列
问题：
mysql> SELECT * FROM orders WHERE status = ‘pending’;
优化：
mysql> SELECT id, status, amount FROM orders WHERE status = ‘pending’;
输出示例：
+—-+———+——–+
| id | status | amount |
+—-+———+——–+
| 1 | pending | 100.00 |
+—-+———+——–+
2. 使用LIMIT分页
基本分页：
mysql> SELECT * FROM orders LIMIT 0, 10;
输出示例：
+—-+————+——–+———————+
| id | status | amount | created_at |
+—-+————+——–+———————+
| 1 | pending | 100.00 | 2026-04-01 12:00:00 |
| 2 | processing | 200.00 | 2026-04-01 12:01:00 |
+—-+————+——–+———————+
优化深分页：
mysql> SELECT * FROM orders WHERE id > 10000 LIMIT 10;
输出示例：
+——-+————+——–+———————+
| id | status | amount | created_at |
+——-+————+——–+———————+
| 10001 | pending | 150.00 | 2026-04-01 13:00:00 |
+——-+————+——–+———————+
延迟关联优化：
mysql> SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders LIMIT 10000, 10) t
ON o.id = t.id;
输出示例：
+——-+————+——–+———————+
| id | status | amount | created_at |
+——-+————+——–+———————+
| 10001 | pending | 150.00 | 2026-04-01 13:00:00 |
+——-+————+——–+———————+
3. 使用COUNT优化
统计总数：
mysql> SELECT COUNT(*) FROM orders;
输出示例：
+———-+
| COUNT(*) |
+———-+
| 100000 |
+———-+
条件统计：
mysql> SELECT status, COUNT(*) FROM orders GROUP BY status;
输出示例：
+————+———-+
| status | COUNT(*) |
+————+———-+
| pending | 30000 |
| processing | 20000 |
| completed | 50000 |
+————+———-+
4. 使用DISTINCT优化
去重查询：
mysql> SELECT DISTINCT status FROM orders;
输出示例：
+————+
| status |
+————+
| pending |
| processing |
| completed |
+————+
使用GROUP BY替代：
mysql> SELECT status FROM orders GROUP BY status;
输出示例：
+————+
| status |
+————+
| pending |
| processing |
| completed |
+————+
5. 使用CASE WHEN优化
条件聚合：
mysql> SELECT
COUNT(CASE WHEN status = ‘pending’ THEN 1 END) AS pending_count,
COUNT(CASE WHEN status = ‘processing’ THEN 1 END) AS processing_count,
COUNT(CASE WHEN status = ‘completed’ THEN 1 END) AS completed_count
FROM orders;
输出示例：
+—————+——————+—————-+
| pending_count | processing_count | completed_count|
+—————+——————+—————-+
| 30000 | 20000 | 50000 |
+—————+——————+—————-+
3.2 子查询优化
子查询优化可以显著提升查询性能：
1. 子查询类型
标量子查询：
mysql> SELECT *, (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) AS item_count
FROM orders o;
输出示例：
+—-+———+——–+————+
| id | status | amount | item_count |
+—-+———+——–+————+
| 1 | pending | 100.00 | 3 |
+—-+———+——–+————+
相关子查询：
mysql> SELECT * FROM orders o
WHERE amount > (SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id);
输出示例：
+—-+————+——–+————-+
| id | status | amount | customer_id |
+—-+————+——–+————-+
| 1 | pending | 500.00 | 100 |
+—-+————+——–+————-+
2. 子查询转连接
问题：
mysql> EXPLAIN SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE status = ‘active’);
输出示例：
+—-+————-+———–+————+——+—————+————-+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+———–+————+——+—————+————-+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL |
| 1 | SIMPLE | customers | NULL | eq_ref| PRIMARY | PRIMARY |
+—-+————-+———–+————+——+—————+————-+
优化：
mysql> EXPLAIN SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.id AND c.status = ‘active’;
输出示例：
+—-+————-+——-+————+——–+—————+————-+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——-+————+——–+—————+————-+
| 1 | SIMPLE | c | NULL | ref | idx_status | idx_status |
| 1 | SIMPLE | o | NULL | ref | idx_customer | idx_customer|
+—-+————-+——-+————+——–+—————+————-+
3. 使用派生表
问题：
mysql> SELECT * FROM orders
WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 10);
优化：
mysql> EXPLAIN SELECT o.* FROM orders o
JOIN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 10) t
ON o.customer_id = t.customer_id;
输出示例：
+—-+————-+————+————+——+—————+————-+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+————+————+——+—————+————-+
| 1 | PRIMARY |
| 1 | PRIMARY | o | NULL | ref | idx_customer | idx_customer|
| 2 | DERIVED | orders | NULL | index| idx_customer | idx_customer|
+—-+————-+————+————+——+—————+————-+
4. 使用CTE（MySQL 8.0+）
公用表表达式：
mysql> WITH active_customers AS (
SELECT id FROM customers WHERE status = ‘active’
)
SELECT o.* FROM orders o
JOIN active_customers ac ON o.customer_id = ac.id;
输出示例：
+—-+———+——–+————-+
| id | status | amount | customer_id |
+—-+———+——–+————-+
| 1 | pending | 100.00 | 100 |
+—-+———+——–+————-+
递归CTE：
mysql> WITH RECURSIVE category_path AS (
SELECT id, parent_id, name, 1 AS level
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name, cp.level + 1
FROM categories c
JOIN category_path cp ON c.parent_id = cp.id
)
SELECT * FROM category_path;
输出示例：
+—-+———–+——–+——-+
| id | parent_id | name | level |
+—-+———–+——–+——-+
| 1 | NULL | Root | 1 |
| 2 | 1 | Child | 2 |
+—-+———–+——–+——-+
5. 窗口函数（MySQL 8.0+）
排名函数：
mysql> SELECT id, customer_id, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank
FROM orders;
输出示例：
+—-+————-+——–+——+
| id | customer_id | amount | rank |
+—-+————-+——–+——+
| 1 | 100 | 500.00 | 1 |
| 2 | 100 | 300.00 | 2 |
| 3 | 101 | 400.00 | 1 |
+—-+————-+——–+——+
聚合窗口：
mysql> SELECT id, customer_id, amount,
SUM(amount) OVER (PARTITION BY customer_id) AS total_amount,
AVG(amount) OVER (PARTITION BY customer_id) AS avg_amount
FROM orders;
输出示例：
+—-+————-+——–+————–+————+
| id | customer_id | amount | total_amount | avg_amount |
+—-+————-+——–+————–+————+
| 1 | 100 | 500.00 | 800.00 | 400.0000 |
| 2 | 100 | 300.00 | 800.00 | 400.0000 |
+—-+————-+——–+————–+————+
3.3 GROUP BY/ORDER BY优化
GROUP BY和ORDER BY优化对性能影响很大：
1. GROUP BY优化
使用索引优化GROUP BY：
mysql> CREATE INDEX idx_status ON orders(status);
输出示例：
Query OK, 0 rows affected (2.34 sec)
mysql> EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
输出示例：
+—-+————-+——–+————+——-+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——-+—————+————+
| 1 | SIMPLE | orders | NULL | index | idx_status | idx_status |
+—-+————-+——–+————+——-+—————+————+
| Extra: Using index |
+——————————————————————-+
松散索引扫描：
mysql> EXPLAIN SELECT status, MIN(created_at) FROM orders GROUP BY status;
输出示例：
+—-+————-+——–+————+——-+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——-+—————+————+
| 1 | SIMPLE | orders | NULL | range | idx_status | idx_status |
+—-+————-+——–+————+——-+—————+————+
| Extra: Using index for group-by |
+——————————————————————-+
2. ORDER BY优化
使用索引避免文件排序：
mysql> CREATE INDEX idx_created ON orders(created_at);
输出示例：
Query OK, 0 rows affected (3.45 sec)
mysql> EXPLAIN SELECT * FROM orders ORDER BY created_at LIMIT 10;
输出示例：
+—-+————-+——–+————+——-+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——-+—————+————+
| 1 | SIMPLE | orders | NULL | index | NULL | idx_created|
+—-+————-+——–+————+——-+—————+————+
文件排序场景：
mysql> EXPLAIN SELECT * FROM orders ORDER BY amount;
输出示例：
+—-+————-+——–+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+——+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL |
+—-+————-+——–+————+——+—————+——+
| Extra: Using filesort |
+——————————————————————-+
3. GROUP BY + ORDER BY优化
同时优化：
mysql> CREATE INDEX idx_status_created ON orders(status, created_at);
输出示例：
Query OK, 0 rows affected (4.56 sec)
mysql> EXPLAIN SELECT status, MAX(created_at) AS last_order
FROM orders
GROUP BY status
ORDER BY status;
输出示例：
+—-+————-+——–+————+——-+——————+——————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——-+——————+——————+
| 1 | SIMPLE | orders | NULL | index | idx_status_created| idx_status_created|
+—-+————-+——–+————+——-+——————+——————+
| Extra: Using index |
+——————————————————————-+
4. 临时表优化
避免临时表：
mysql> EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*) DESC;
输出示例：
+—-+————-+——–+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+——+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL |
+—-+————-+——–+————+——+—————+——+
| Extra: Using temporary; Using filesort |
+——————————————————————-+
增加临时表大小：
mysql> SHOW VARIABLES LIKE ‘tmp_table_size’;
输出示例：
+—————-+———-+
| Variable_name | Value |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+
mysql> SET SESSION tmp_table_size = 67108864;
输出示例：
Query OK, 0 rows affected (0.00 sec)
5. 大结果集优化
使用SQL_BIG_RESULT提示：
mysql> EXPLAIN SELECT SQL_BIG_RESULT status, COUNT(*)
FROM orders GROUP BY status;
输示例：
+—-+————-+——–+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+——+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL |
+—-+————-+——–+————+——+—————+——+
| Extra: Using temporary; Using filesort |
+——————————————————————-+
使用SQL_SMALL_RESULT提示：
mysql> EXPLAIN SELECT SQL_SMALL_RESULT status, COUNT(*)
FROM orders GROUP BY status;
输出示例：
+—-+————-+——–+————+——-+—————+————+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——-+—————+————+
| 1 | SIMPLE | orders | NULL | index | idx_status | idx_status |
+—-+————-+——–+————+——-+—————+————+
Part04-生产案例与实战讲解
4.1 慢查询优化案例
以下是慢查询优化的实战案例：
# 案例：订单统计查询慢
# 问题描述：
# 订单统计查询执行时间超过30秒
# 原始SQL：
mysql> SELECT
c.name,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.status = ‘active’
GROUP BY c.id, c.name
ORDER BY total_amount DESC;
# 执行时间：30秒
# 步骤1：分析执行计划
mysql> EXPLAIN SELECT … \G
# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: c
# type: ALL
# possible_keys: NULL
# key: NULL
# rows: 100000
# *************************** 2. row ***************************
# id: 1
# select_type: SIMPLE
# table: o
# type: ALL
# possible_keys: NULL
# key: NULL
# rows: 1000000
# 问题分析：
# – customers表全表扫描
# – orders表全表扫描
# – 没有使用索引
# 步骤2：创建索引
mysql> CREATE INDEX idx_customer_status ON customers(status);
mysql> CREATE INDEX idx_order_customer ON orders(customer_id);
# 输出示例：
# Query OK, 0 rows affected (5.67 sec)
# 步骤3：重写SQL
mysql> SELECT
c.name,
COALESCE(o.order_count, 0) AS order_count,
COALESCE(o.total_amount, 0) AS total_amount
FROM customers c
LEFT JOIN (
SELECT customer_id, COUNT(id) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
) o ON c.id = o.customer_id
WHERE c.status = ‘active’
ORDER BY total_amount DESC;
# 步骤4：验证优化效果
mysql> EXPLAIN SELECT … \G
# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: PRIMARY
# table: c
# type: ref
# possible_keys: idx_customer_status
# key: idx_customer_status
# rows: 50000
# 执行时间：2秒
# 性能提升：从30秒降到2秒，提升15倍
4.2 复杂查询优化案例
以下是复杂查询优化的实战案例：
# 案例：多表关联查询
# 问题描述：
# 多表关联查询执行时间超过60秒
# 原始SQL：
mysql> SELECT
o.id,
o.order_no,
c.name AS customer_name,
p.name AS product_name,
oi.quantity,
oi.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = ‘complete
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
