本文是"深入学习MySQL：从新手到高手"系列的第三篇，将深入讲解慢查询分析、EXPLAIN执行计划详解以及各类SQL优化技巧。

慢查询分析开启慢查询日志

慢查询日志是定位性能问题的第一步。

-- 查看慢查询日志状态SHOW VARIABLES LIKE 'slow_query%';SHOW VARIABLES LIKE 'long_query_time';-- 开启慢查询日志SET GLOBAL slow_query_log = 1;SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';SET GLOBAL long_query_time = 1; -- 超过1秒记录-- 记录未使用索引的查询SET GLOBAL log_queries_not_using_indexes = 1;-- 永久配置（my.cnf）[mysqld]slow_query_log = 1slow_query_log_file = /var/log/mysql/mysql-slow.loglong_query_time = 1log_queries_not_using_indexes = 1分析慢查询日志mysqldumpslow工具# 获取执行时间最长的10条SQLmysqldumpslow -t 10 /var/log/mysql/mysql-slow.log# 获取返回记录数最多的10条SQLmysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log# 获取访问频率最高的10条SQLmysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log# 按照时间排序，包含LEFT JOIN的SQLmysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/log/mysql/mysql-slow.logpt-query-digest工具# 安装percona-toolkityum install percona-toolkit# 分析慢查询日志pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt# 分析指定时间范围pt-query-digest --since '2024-01-01 00:00:00' --until '2024-01-02 00:00:00' /var/log/mysql/mysql-slow.log# 输出到数据库表pt-query-digest --review h=localhost,D=slow_query,t=query_review /var/log/mysql/mysql-slow.log

报告解读：

# Profile# Rank Query ID Response time Calls R/Call V/M Item# ==== ================== =============== ====== ======= ===== ============# 1 0x4E1B3D5C2F8A... 1500.0000 50.0% 500 3.0000 0.50 SELECT users# 2 0x7A2B3C4D5E6F... 800.0000 26.7% 200 4.0000 0.30 SELECT orders# 关键指标：# Response time: 总响应时间# Calls: 执行次数# R/Call: 平均每次执行时间# V/M: 方差/均值比（越大说明执行时间越不稳定）实时监控慢查询-- 查看当前正在执行的查询SHOW PROCESSLIST;SHOW FULL PROCESSLIST;-- 查看执行时间超过60秒的查询SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep' AND TIME > 60;-- 杀掉慢查询KILL [CONNECTION | QUERY] thread_id;EXPLAIN深度解析基本用法-- 基本用法EXPLAIN SELECT * FROM users WHERE id = 1;-- 显示额外信息EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;-- 实际执行并显示统计信息（MySQL 8.0.18+）EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;各字段详解id字段

id表示查询的执行顺序。

-- id相同：从上往下执行EXPLAIN SELECT * FROM users u, orders o WHERE u.id = o.user_id;-- id不同：id大的先执行EXPLAIN SELECT * FROM users WHERE id = (SELECT user_id FROM orders WHERE id = 1);-- id为NULL：表示结果集，不需要执行EXPLAIN SELECT * FROM users WHERE id = 1UNIONSELECT * FROM users WHERE id = 2;select_type字段-- SIMPLE: 简单查询（不包含子查询或UNION）EXPLAIN SELECT * FROM users WHERE id = 1;-- PRIMARY: 最外层查询-- SUBQUERY: 子查询EXPLAIN SELECT * FROM users WHERE id = (SELECT user_id FROM orders WHERE id = 1);-- DERIVED: 派生表（FROM子句中的子查询）EXPLAIN SELECT * FROM (SELECT * FROM users WHERE age > 20) t;-- UNION: UNION中第二个及之后的查询EXPLAIN SELECT * FROM users WHERE id = 1 UNION SELECT * FROM users WHERE id = 2;-- UNION RESULT: UNION的结果-- DEPENDENT SUBQUERY: 依赖外部查询的子查询EXPLAIN SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);type字段（重要）

type表示访问类型，从优到差排序：

-- 1. system: 表只有一行（系统表）EXPLAIN SELECT * FROM (SELECT 1) t;-- 2. const: 主键或唯一索引等值查询，最多返回一行EXPLAIN SELECT * FROM users WHERE id = 1;-- 3. eq_ref: 多表JOIN时，使用主键或唯一索引，每次只返回一行EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;-- 4. ref: 非唯一索引等值查询，可能返回多行EXPLAIN SELECT * FROM users WHERE username = 'Alice';-- 5. ref_or_null: 类似ref，但包含NULL值查询EXPLAIN SELECT * FROM users WHERE username = 'Alice' OR username IS NULL;-- 6. index_merge: 使用多个索引EXPLAIN SELECT * FROM users WHERE id = 1 OR username = 'Alice';-- 7. range: 索引范围查询EXPLAIN SELECT * FROM users WHERE id > 100;EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);EXPLAIN SELECT * FROM users WHERE id BETWEEN 1 AND 100;-- 8. index: 全索引扫描（比ALL好，因为索引文件通常比数据文件小）EXPLAIN SELECT id FROM users;-- 9. ALL: 全表扫描（需要优化）EXPLAIN SELECT * FROM users WHERE email LIKE '%@gmail.com';

优化目标：至少达到range级别，最好达到ref级别。

key_len计算规则

key_len用于判断联合索引使用了多少列。

-- 数据类型占用字节数-- TINYINT: 1字节-- SMALLINT: 2字节-- INT: 4字节-- BIGINT: 8字节-- DATE: 3字节-- DATETIME: 8字节（MySQL 5.6.4之前）/ 5字节（MySQL 5.6.4之后）-- TIMESTAMP: 4字节-- CHAR(n): n * 字符集字节数-- VARCHAR(n): n * 字符集字节数 + 2-- 字符集字节数-- latin1: 1字节-- utf8: 3字节-- utf8mb4: 4字节-- 允许NULL: +1字节-- 示例：idx_name_age(name VARCHAR(50), age INT)，使用utf8mb4-- name: 50 * 4 + 2 + 1 = 203字节（VARCHAR + 允许NULL）-- age: 4 + 1 = 5字节（INT + 允许NULL）-- 全部使用: 203 + 5 = 208字节-- 验证key_lenCREATE TABLE test_keylen ( id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50), INDEX idx_name_age_city(name, age, city)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;EXPLAIN SELECT * FROM test_keylen WHERE name = 'Alice';-- key_len = 203 (只用了name)EXPLAIN SELECT * FROM test_keylen WHERE name = 'Alice' AND age = 25;-- key_len = 208 (用了name + age)EXPLAIN SELECT * FROM test_keylen WHERE name = 'Alice' AND age = 25 AND city = '北京';-- key_len = 411 (用了全部)Extra字段（重要）-- Using index: 覆盖索引，无需回表（好）EXPLAIN SELECT id, username FROM users WHERE username = 'Alice';-- Using where: Server层过滤（中性）EXPLAIN SELECT * FROM users WHERE age > 25;-- Using index condition: 索引条件下推ICP（好）EXPLAIN SELECT * FROM users WHERE username > 'A' AND username LIKE '%e';-- Using temporary: 使用临时表（需优化）EXPLAIN SELECT DISTINCT city FROM users;-- Using filesort: 文件排序（需优化）EXPLAIN SELECT * FROM users ORDER BY email;-- Using join buffer: JOIN使用缓冲区（可能需优化）EXPLAIN SELECT * FROM users u, orders o WHERE u.city = o.city;-- Impossible WHERE: WHERE条件永远为falseEXPLAIN SELECT * FROM users WHERE 1 = 0;-- Select tables optimized away: 使用聚合函数访问索引EXPLAIN SELECT MIN(id) FROM users;-- No tables used: 不访问任何表EXPLAIN SELECT 1;EXPLAIN ANALYZE（MySQL 8.0.18+）-- 实际执行并显示真实统计信息EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;-- 输出示例：-> Filter: (users.age > 25) (cost=10142.85 rows=33333) (actual time=0.058..45.832 rows=33421 loops=1) -> Table scan on users (cost=10142.85 rows=100000) (actual time=0.052..30.123 rows=100000 loops=1)-- 解读：-- cost: 预估成本-- rows: 预估行数-- actual time: 实际时间（首行时间..总时间）-- rows: 实际行数-- loops: 循环次数SELECT优化避免SELECT *-- ❌ 不推荐SELECT * FROM users WHERE id = 1;-- ✅ 推荐：只查询需要的列SELECT id, username, email FROM users WHERE id = 1;-- 原因：-- 1. 减少网络传输-- 2. 可能使用覆盖索引-- 3. 减少内存使用分页优化-- ❌ 深分页性能差SELECT * FROM users ORDER BY id LIMIT 1000000, 10;-- 需要扫描1000010行，丢弃前1000000行-- ✅ 优化方案1：延迟关联SELECT u.* FROM users uINNER JOIN ( SELECT id FROM users ORDER BY id LIMIT 1000000, 10) t ON u.id = t.id;-- ✅ 优化方案2：游标分页（记住上次最大ID）SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;-- ✅ 优化方案3：业务限制（不允许查看太靠后的页）COUNT优化-- 不同COUNT的区别SELECT COUNT(*) FROM users; -- 统计总行数（包括NULL）SELECT COUNT(1) FROM users; -- 同COUNT(*)SELECT COUNT(id) FROM users; -- 统计id不为NULL的行数SELECT COUNT(email) FROM users; -- 统计email不为NULL的行数-- 性能：COUNT(*) ≈ COUNT(1) > COUNT(主键) > COUNT(普通列)-- InnoDB会选择最小的索引来统计COUNT(*)-- 大表COUNT优化-- 方案1：使用缓存（Redis）-- 方案2：使用统计表-- 方案3：使用EXPLAIN的rows估算EXPLAIN SELECT COUNT(*) FROM users;DISTINCT优化-- ❌ DISTINCT可能导致临时表和文件排序SELECT DISTINCT city FROM users;-- ✅ 使用索引优化CREATE INDEX idx_city ON users(city);SELECT DISTINCT city FROM users;-- ✅ 使用GROUP BY替代（某些场景更快）SELECT city FROM users GROUP BY city;JOIN优化JOIN算法

MySQL支持三种JOIN算法：

Nested Loop Join（嵌套循环连接）-- 驱动表的每一行，都要扫描被驱动表-- 适合小表驱动大表SELECT * FROM small_table sJOIN big_table b ON s.id = b.small_id;-- 优化：确保被驱动表的连接字段有索引CREATE INDEX idx_small_id ON big_table(small_id);Block Nested Loop Join（块嵌套循环连接）-- 当被驱动表没有索引时使用-- 将驱动表数据放入join_buffer，减少被驱动表扫描次数-- 查看join_buffer大小SHOW VARIABLES LIKE 'join_buffer_size';-- 增大join_bufferSET SESSION join_buffer_size = 8388608; -- 8MBHash Join（MySQL 8.0.18+）-- 对于没有索引的等值JOIN，使用Hash Join-- 比Block Nested Loop更高效-- 查看是否使用Hash JoinEXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1;-- 输出包含 "Hash" 字样JOIN优化原则小表驱动大表-- MySQL优化器会自动选择驱动表，但有时需要手动优化-- 使用STRAIGHT_JOIN强制指定驱动表顺序SELECT STRAIGHT_JOIN u.*, o.* FROM users u -- 驱动表JOIN orders o ON u.id = o.user_id;确保被驱动表有索引-- ❌ 被驱动表没有索引SELECT * FROM users uJOIN orders o ON u.city = o.city; -- orders.city没有索引-- ✅ 添加索引CREATE INDEX idx_city ON orders(city);减少JOIN的表数量-- ❌ 过多的表JOINSELECT * FROM t1JOIN t2 ON t1.id = t2.t1_idJOIN t3 ON t2.id = t3.t2_idJOIN t4 ON t3.id = t4.t3_idJOIN t5 ON t4.id = t5.t4_id;-- ✅ 拆分查询或使用冗余字段-- 建议单次查询JOIN不超过3-4个表优先使用INNER JOIN-- INNER JOIN可以让优化器自由选择驱动表SELECT * FROM users uINNER JOIN orders o ON u.id = o.user_id;-- LEFT JOIN强制左表为驱动表SELECT * FROM users uLEFT JOIN orders o ON u.id = o.user_id;子查询优化子查询的问题-- ❌ 相关子查询：对外层每一行都执行一次子查询SELECT * FROM users uWHERE u.id IN ( SELECT user_id FROM orders WHERE amount > 1000);-- 查看执行计划EXPLAIN SELECT * FROM users uWHERE u.id IN (SELECT user_id FROM orders WHERE amount > 1000);-- 可能显示 DEPENDENT SUBQUERY用JOIN替代子查询-- ✅ 使用JOIN替代IN子查询SELECT DISTINCT u.* FROM users uINNER JOIN orders o ON u.id = o.user_idWHERE o.amount > 1000;-- ✅ 使用EXISTS替代IN（大数据量时更优）SELECT * FROM users uWHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);派生表优化-- MySQL 8.0会自动优化派生表（Derived Table Merge）-- 原始查询SELECT * FROM ( SELECT user_id, SUM(amount) as total FROM orders GROUP BY user_id) tWHERE t.total > 10000;-- 优化器可能合并为SELECT user_id, SUM(amount) as totalFROM ordersGROUP BY user_idHAVING total > 10000;ORDER BY优化排序算法

MySQL有两种排序算法：

-- 1. 全字段排序（单次扫描）-- 将所有需要的字段放入sort_buffer，排序后直接返回-- 2. rowid排序（两次扫描）-- 只将排序字段和rowid放入sort_buffer，排序后回表获取数据-- 当行数据很大时使用-- 相关参数SHOW VARIABLES LIKE 'max_length_for_sort_data'; -- 默认4096SHOW VARIABLES LIKE 'sort_buffer_size'; -- 排序缓冲区大小利用索引排序-- ❌ Using filesortEXPLAIN SELECT * FROM users ORDER BY email;-- ✅ Using index（利用索引排序）CREATE INDEX idx_email ON users(email);EXPLAIN SELECT id, email FROM users ORDER BY email;-- 联合索引排序CREATE INDEX idx_city_age ON users(city, age);-- ✅ 可以使用索引排序SELECT * FROM users WHERE city = '北京' ORDER BY age;-- ❌ 无法使用索引排序（排序方向不一致）SELECT * FROM users WHERE city = '北京' ORDER BY age DESC, id ASC;-- ✅ MySQL 8.0支持降序索引CREATE INDEX idx_city_age_desc ON users(city, age DESC);ORDER BY优化原则-- 1. 尽量使用索引排序-- 2. 减少SELECT的字段数量-- 3. 增大sort_buffer_sizeSET SESSION sort_buffer_size = 4194304; -- 4MB-- 4. 如果排序数据量大，考虑增大tmp_table_sizeSHOW VARIABLES LIKE 'tmp_table_size';GROUP BY优化利用索引分组-- ❌ Using temporary; Using filesortEXPLAIN SELECT city, COUNT(*) FROM users GROUP BY city;-- ✅ 添加索引后：Using indexCREATE INDEX idx_city ON users(city);EXPLAIN SELECT city, COUNT(*) FROM users GROUP BY city;优化技巧-- 1. 使用索引列分组-- 2. 减少分组后的数据量SELECT city, COUNT(*) FROM users WHERE created_at > '2024-01-01' -- 先过滤GROUP BY city;-- 3. 使用WITH ROLLUP时注意性能SELECT city, COUNT(*) FROM users GROUP BY city WITH ROLLUP;UNION优化-- UNION: 去重（需要排序）-- UNION ALL: 不去重（更快）-- ❌ 不需要去重时使用UNIONSELECT id FROM users WHERE age > 30UNIONSELECT id FROM users WHERE city = '北京';-- ✅ 使用UNION ALLSELECT id FROM users WHERE age > 30UNION ALLSELECT id FROM users WHERE city = '北京';-- 如果需要去重，在外层处理SELECT DISTINCT id FROM ( SELECT id FROM users WHERE age > 30 UNION ALL SELECT id FROM users WHERE city = '北京') t;批量操作优化批量INSERT-- ❌ 单条插入INSERT INTO users (name, age) VALUES ('Alice', 25);INSERT INTO users (name, age) VALUES ('Bob', 30);INSERT INTO users (name, age) VALUES ('Charlie', 35);-- ✅ 批量插入INSERT INTO users (name, age) VALUES ('Alice', 25),('Bob', 30),('Charlie', 35);-- ✅ 使用LOAD DATA（最快）LOAD DATA INFILE '/tmp/users.csv'INTO TABLE usersFIELDS TERMINATED BY ','LINES TERMINATED BY '\n'(name, age);批量UPDATE-- ❌ 单条更新UPDATE users SET status = 1 WHERE id = 1;UPDATE users SET status = 1 WHERE id = 2;UPDATE users SET status = 1 WHERE id = 3;-- ✅ 批量更新UPDATE users SET status = 1 WHERE id IN (1, 2, 3);-- ✅ CASE WHEN批量更新不同值UPDATE users SET status = CASE id WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3ENDWHERE id IN (1, 2, 3);批量DELETE-- ❌ 一次删除大量数据（锁表时间长）DELETE FROM logs WHERE created_at < '2023-01-01';-- ✅ 分批删除DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 10000;-- 循环执行直到删除完毕-- ✅ 使用存储过程分批删除DELIMITER //CREATE PROCEDURE batch_delete()BEGIN DECLARE rows_affected INT DEFAULT 1; WHILE rows_affected > 0 DO DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 10000; SET rows_affected = ROW_COUNT(); SELECT SLEEP(0.1); -- 避免主从延迟 END WHILE;END //DELIMITER ;查询优化器提示索引提示-- 强制使用索引SELECT * FROM users FORCE INDEX (idx_username) WHERE username = 'Alice';-- 建议使用索引SELECT * FROM users USE INDEX (idx_username) WHERE username = 'Alice';-- 忽略索引SELECT * FROM users IGNORE INDEX (idx_username) WHERE username = 'Alice';优化器提示（MySQL 8.0+）-- 设置JOIN顺序SELECT /*+ JOIN_ORDER(t1, t2) */ * FROM t1 JOIN t2 ON t1.id = t2.t1_id;-- 设置JOIN算法SELECT /*+ BNL(t2) */ * FROM t1 JOIN t2 ON t1.c1 = t2.c1; -- Block Nested LoopSELECT /*+ HASH_JOIN(t2) */ * FROM t1 JOIN t2 ON t1.c1 = t2.c1; -- Hash JoinSELECT /*+ NO_BNL(t2) */ * FROM t1 JOIN t2 ON t1.c1 = t2.c1; -- 禁用BNL-- 设置索引SELECT /*+ INDEX(users idx_username) */ * FROM users WHERE username = 'Alice';SELECT /*+ NO_INDEX(users idx_username) */ * FROM users WHERE username = 'Alice';-- 设置并行度（MySQL 8.0.14+）SELECT /*+ SET_VAR(innodb_parallel_read_threads=4) */ COUNT(*) FROM big_table;SQL优化案例实战案例1：电商订单查询优化-- 原始SQL（执行时间：5.2秒）SELECT o.*, u.username, p.product_nameFROM orders oLEFT JOIN users u ON o.user_id = u.idLEFT JOIN products p ON o.product_id = p.idWHERE o.status = 1 AND o.created_at > '2024-01-01'ORDER BY o.created_at DESCLIMIT 20;-- 分析EXPLAIN SELECT ...;-- type: ALL, 全表扫描-- 优化步骤：-- 1. 添加联合索引CREATE INDEX idx_status_created ON orders(status, created_at);-- 2. 优化后（执行时间：0.02秒）EXPLAIN SELECT ...;-- type: range, 使用索引案例2：统计报表优化-- 原始SQL（执行时间：30秒）SELECT DATE(created_at) as date, COUNT(*) as order_count, SUM(amount) as total_amountFROM ordersWHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'GROUP BY DATE(created_at)ORDER BY date;-- 问题：对created_at使用函数，无法使用索引-- 优化方案1：添加日期列ALTER TABLE orders ADD COLUMN order_date DATE;UPDATE orders SET order_date = DATE(created_at);CREATE INDEX idx_order_date ON orders(order_date);SELECT order_date, COUNT(*) as order_count, SUM(amount) as total_amountFROM ordersWHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'GROUP BY order_dateORDER BY order_date;-- 优化方案2：使用汇总表（预计算）CREATE TABLE order_daily_stats ( stat_date DATE PRIMARY KEY, order_count INT, total_amount DECIMAL(15,2));-- 每日定时任务更新INSERT INTO order_daily_statsSELECT DATE(created_at), COUNT(*), SUM(amount)FROM ordersWHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAYGROUP BY DATE(created_at)ON DUPLICATE KEY UPDATE order_count = VALUES(order_count), total_amount = VALUES(total_amount);案例3：多条件搜索优化-- 原始SQL：动态条件搜索SELECT * FROM productsWHERE 1=1AND (category_id = 10 OR category_id IS NULL)AND (price BETWEEN 100 AND 500 OR price IS NULL)AND (name LIKE '%手机%' OR name IS NULL)ORDER BY created_at DESCLIMIT 20;-- 问题：OR条件导致索引失效-- 优化方案：使用全文索引 + 条件索引CREATE FULLTEXT INDEX ft_name ON products(name);CREATE INDEX idx_category_price ON products(category_id, price);-- 拆分查询SELECT * FROM productsWHERE category_id = 10AND price BETWEEN 100 AND 500AND MATCH(name) AGAINST('手机' IN NATURAL LANGUAGE MODE)ORDER BY created_at DESCLIMIT 20;本章小结

本文详细讲解了SQL优化的核心知识：

慢查询分析：开启慢查询日志，使用mysqldumpslow和pt-query-digest分析EXPLAIN详解：重点关注type和Extra字段，key_len计算方法SELECT优化：避免SELECT *，分页优化，COUNT优化JOIN优化：小表驱动大表，确保被驱动表有索引子查询优化：用JOIN替代子查询ORDER BY/GROUP BY优化：利用索引排序和分组批量操作优化：批量INSERT/UPDATE/DELETE优化器提示：FORCE INDEX、MySQL 8.0 Hints

下一篇预告：《深入学习MySQL（四）：事务与锁机制深入剖析》，将详细讲解ACID特性、隔离级别、MVCC原理以及各种锁类型。