MySQL教程FG089-MySQL执行计划查看与分析

本教程详细介绍MySQL执行计划的查看与分析方法，帮助数据库管理员和开发人员理解SQL语句的执行过程，从而优化查询性能。风哥教程参考MySQL官方文档SQL Statements、Optimization等相关内容。

Part01-基础概念与理论知识

1.1 MySQL执行计划概述

执行计划是MySQL查询优化器生成的一种表示方式，用于展示SQL语句的执行过程和访问路径。通过分析执行计划，我们可以了解MySQL如何处理查询，识别性能瓶颈，从而进行有针对性的优化。

— 查看执行计划的基本语法
EXPLAIN SELECT * FROM fgedu_students WHERE age > 18;

1.2 执行计划中的重要字段

执行计划包含多个字段，每个字段都有特定的含义，了解这些字段对于分析执行计划至关重要。

— 查看执行计划的详细信息
EXPLAIN EXTENDED SELECT * FROM fgedu_students WHERE age > 18;

1.3 执行计划的访问类型

执行计划中的访问类型（type）字段表示MySQL如何访问表中的数据，从最优到最差的顺序依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

— 查看不同访问类型的执行计划
EXPLAIN SELECT * FROM fgedu_students WHERE id = 1; — const类型
EXPLAIN SELECT * FROM fgedu_students WHERE name = ‘张三’; — ref类型
EXPLAIN SELECT * FROM fgedu_students WHERE age > 18; — range类型
EXPLAIN SELECT * FROM fgedu_students; — ALL类型

Part02-生产环境规划与建议

2.1 执行计划分析策略

在生产环境中，执行计划分析是性能优化的重要环节。建议定期对慢查询进行执行计划分析，识别性能瓶颈，并采取相应的优化措施。更多学习教程www.fgedu.net.cn

生产环境建议：建立执行计划分析的标准流程，包括定期分析慢查询日志、使用EXPLAIN查看执行计划、识别问题并实施优化等步骤。

2.2 索引使用建议

索引是影响执行计划的关键因素，合理的索引设计可以显著提高查询性能。建议根据查询模式创建合适的索引，避免过度索引和无效索引。

— 查看表的索引信息
SHOW INDEX FROM fgedu_students;

2.3 执行计划优化方向

根据执行计划的分析结果，优化方向主要包括：创建适当的索引、重写SQL语句、调整数据库参数、优化表结构等。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 执行计划查看方法

MySQL提供了多种查看执行计划的方法，包括EXPLAIN、EXPLAIN EXTENDED、EXPLAIN ANALYZE等。

— 使用EXPLAIN查看执行计划
EXPLAIN SELECT * FROM fgedu_students s JOIN fgedu_courses c ON s.id = c.student_id WHERE s.age > 18;

— 使用EXPLAIN EXTENDED查看更详细的执行计划
EXPLAIN EXTENDED SELECT * FROM fgedu_students WHERE age > 18;

— 使用EXPLAIN ANALYZE执行并分析查询
EXPLAIN ANALYZE SELECT * FROM fgedu_students WHERE age > 18;

3.2 执行计划分析实战

在实际项目中，执行计划分析需要结合具体的业务场景和查询模式进行。下面通过一个实际案例演示执行计划分析的过程。

— 创建测试表
CREATE TABLE fgedu_orders (
id INT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(20) NOT NULL,
customer_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
order_date DATETIME NOT NULL,
status VARCHAR(10) NOT NULL
);

— 插入测试数据
INSERT INTO fgedu_orders (order_no, customer_id, amount, order_date, status)
VALUES
(‘ORD001’, 1, 100.00, ‘2023-01-01 10:00:00’, ‘PAID’),
(‘ORD002’, 2, 200.00, ‘2023-01-02 11:00:00’, ‘PAID’),
(‘ORD003’, 1, 150.00, ‘2023-01-03 12:00:00’, ‘UNPAID’),
(‘ORD004’, 3, 300.00, ‘2023-01-04 13:00:00’, ‘PAID’),
(‘ORD005’, 2, 250.00, ‘2023-01-05 14:00:00’, ‘PAID’);

— 查看执行计划
EXPLAIN SELECT * FROM fgedu_orders WHERE customer_id = 1 AND status = ‘PAID’;

3.3 执行计划监控与调优

在生产环境中，需要建立执行计划监控机制，及时发现和解决性能问题。可以通过慢查询日志、Performance Schema等工具进行监控。学习交流加群风哥QQ113257174

— 开启慢查询日志
SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL slow_query_log_file = ‘/mysql/data/fgedu-slow.log’;
SET GLOBAL long_query_time = 1;

— 查看慢查询日志配置
SHOW VARIABLES LIKE ‘%slow%’;

Part04-生产案例与实战讲解

4.1 全表扫描案例分析

全表扫描是性能最差的访问类型，需要尽量避免。下面通过一个案例分析全表扫描的原因和优化方法。

— 创建测试表
CREATE TABLE fgedu_users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT NOT NULL,
created_at DATETIME NOT NULL
);

— 插入测试数据
INSERT INTO fgedu_users (username, email, age, created_at)
VALUES
(‘user1’, ‘user1@example.com’, 20, ‘2023-01-01 00:00:00’),
(‘user2’, ‘user2@example.com’, 25, ‘2023-01-02 00:00:00’),
(‘user3’, ‘user3@example.com’, 30, ‘2023-01-03 00:00:00’),
(‘user4’, ‘user4@example.com’, 35, ‘2023-01-04 00:00:00’),
(‘user5’, ‘user5@example.com’, 40, ‘2023-01-05 00:00:00’);

— 查看执行计划（全表扫描）
EXPLAIN SELECT * FROM fgedu_users WHERE age = 25;

— 创建索引
CREATE INDEX idx_age ON fgedu_users(age);

— 再次查看执行计划（索引扫描）
EXPLAIN SELECT * FROM fgedu_users WHERE age = 25;

4.2 索引扫描案例分析

索引扫描是一种常用的访问类型，合理使用索引可以显著提高查询性能。下面通过一个案例分析索引扫描的使用情况。

— 创建复合索引
CREATE INDEX idx_customer_status ON fgedu_orders(customer_id, status);

— 查看执行计划（使用复合索引）
EXPLAIN SELECT * FROM fgedu_orders WHERE customer_id = 1 AND status = ‘PAID’;

— 查看执行计划（索引覆盖）
EXPLAIN SELECT customer_id, status FROM fgedu_orders WHERE customer_id = 1 AND status = ‘PAID’;

4.3 复杂查询执行计划分析

对于复杂的查询语句，执行计划分析尤为重要。下面通过一个案例分析复杂查询的执行计划。

— 创建测试表
CREATE TABLE fgedu_order_items (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES fgedu_orders(id)
);

— 插入测试数据
INSERT INTO fgedu_order_items (order_id, product_id, quantity, price)
VALUES
(1, 1, 2, 50.00),
(1, 2, 1, 100.00),
(2, 1, 1, 50.00),
(3, 3, 3, 200.00),
(4, 2, 2, 100.00);

— 复杂查询执行计划分析
EXPLAIN SELECT o.order_no, o.customer_id, SUM(oi.quantity * oi.price) AS total_amount
FROM fgedu_orders o
JOIN fgedu_order_items oi ON o.id = oi.order_id
WHERE o.order_date BETWEEN ‘2023-01-01’ AND ‘2023-01-31’
GROUP BY o.order_no, o.customer_id
ORDER BY total_amount DESC;

风哥提示：对于复杂查询，需要关注执行计划中的join_type、rows、filtered等字段，分析是否存在性能瓶颈。

Part05-风哥经验总结与分享

5.1 执行计划分析技巧

执行计划分析需要掌握一定的技巧，包括识别关键字段、理解访问类型、分析索引使用情况等。

执行计划分析技巧：

关注type字段，尽量避免ALL（全表扫描）
关注key字段，确认是否使用了合适的索引
关注rows字段，评估查询的行数
关注Extra字段，了解额外的执行信息

5.2 常见问题与解决方案

在执行计划分析中，常见的问题包括全表扫描、索引失效、临时表使用等，需要采取相应的解决方案。更多学习教程公众号风哥教程itpux_com

— 索引失效案例：使用函数导致索引失效
EXPLAIN SELECT * FROM fgedu_users WHERE YEAR(created_at) = 2023;

— 优化方案：重写SQL语句
EXPLAIN SELECT * FROM fgedu_users WHERE created_at BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;

5.3 最佳实践建议

执行计划分析的最佳实践包括定期分析、建立标准流程、持续优化等。

最佳实践建议：

定期分析慢查询日志，识别性能问题
使用EXPLAIN分析SQL语句的执行计划
根据执行计划优化SQL语句和索引
建立执行计划分析的标准流程
持续监控和优化数据库性能

通过本教程的学习，您应该掌握了MySQL执行计划的查看与分析方法，能够识别性能瓶颈并进行有针对性的优化。from MySQL:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html