MySQL SQL性能调优实战指南 # MySQL SQL性能调优实战指南一、核心思想：系统化调优四步法SQL调优不是盲目加索引，而是一个系统化工程，应遵循 测量 -> 分析 -> 优化 -> 验证 的闭环流程。测量：通过慢查询日志、监控系统定位需要优化的慢SQL。分析：使用EXPLAIN等工具分析SQL的执行计划，找到性能瓶颈。优化：根据分析结果，采取索引优化、SQL重写、架构调整等手段。验证：在测试环境验证优化效果，并上线监控。二、性能诊断与分析工具1. 慢查询日志作用：记录执行时间超过阈值的SQL语句。配置：-- 开启慢查询日志 SET GLOBAL slow_query_log = ON; -- 设置阈值（如0.1秒） SET GLOBAL long_query_time = 0.1; -- 查看日志文件路径 SHOW VARIABLES LIKE 'slow_query_log_file';2. EXPLAIN 执行计划分析作用：查看MySQL如何执行你的SQL，是调优的核心工具。关键字段解读：type：访问类型，性能从好到差：system > const > eq_ref > ref > range > index > ALL。ALL：全表扫描，性能最差，必须优化。range：范围扫描，使用了索引，常见于BETWEEN、>、LIKE 'abc%'。ref：非唯一性索引查找。possible_keys：可能用到的索引。key：实际使用的索引。rows：MySQL估计需要扫描的行数，这个值越小越好。Extra：额外信息，关键提示：Using index：覆盖索引，查询的列都在索引中，无需回表，性能高。Using where：需要回表过滤数据。Using filesort：文件排序，需要优化（通常需要为ORDER BY字段建立索引）。Using temporary：使用临时表，常见于GROUP BY或DISTINCT，性能差。使用方式：EXPLAIN SELECT * FROM users WHERE age > 25; EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25; -- 更详细的结构化信息 -- MySQL 8.0+ 可以查看实际执行计划 EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;三、索引优化策略（核心）索引是提升查询性能的关键，但也是一把双刃剑（占用空间、影响写入性能）。1. 索引设计原则高选择性字段优先：字段的唯一性越高，索引效果越好。-- 计算选择性：值越接近1，选择性越高 SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM orders;最左前缀原则：复合索引 (a, b, c) 会创建 (a), (a,b), (a,b,c) 三个索引。查询条件必须包含索引的最左列，否则索引失效。索引列顺序：等值查询放前面，范围查询放后面。避免冗余索引：尽量复用现有索引，删除无用索引。-- 查看表上的索引 SHOW INDEX FROM table_name;2. 索引优化实战覆盖索引：查询的列都在索引中，避免回表。-- 假设有复合索引 idx_status_covering(status, user_id, order_no) SELECT user_id, order_no FROM orders WHERE status = 'pending'; -- 走覆盖索引范围查询优化：-- 原始（慢）：索引可能只用到create_time CREATE INDEX idx_time ON orders(create_time); SELECT * FROM orders WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31' AND status = 'completed'; -- 优化后：将等值条件放在前面 CREATE INDEX idx_status_time ON orders(status, create_time);3. 索引失效的常见场景（避坑指南）函数操作：对索引列使用函数或计算。-- ❌ 失效 SELECT * FROM users WHERE DATE(create_time) = '2024-01-01'; -- ✅ 优化 SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';隐式类型转换：数据类型不匹配。-- ❌ 失效（phone是varchar类型） SELECT * FROM users WHERE phone = 13800138000; -- ✅ 优化 SELECT * FROM users WHERE phone = '13800138000';前导通配符：LIKE以%开头。-- ❌ 失效 SELECT * FROM users WHERE name LIKE '%john%'; -- ✅ 可能有效（后缀通配符） SELECT * FROM users WHERE name LIKE 'john%';OR条件：如果OR连接的条件中有一个没有索引，整个查询都可能不走索引。NOT IN 或 !=：可能导致索引失效（或效果变差）。四、SQL语句优化技巧1. 查询重写用JOIN代替子查询：子查询会创建临时表，JOIN通常性能更好。-- ❌ 低效的子查询 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level > 3); -- ✅ 高效JOIN SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.vip_level > 3;用EXISTS代替IN：当外表数据量大时，EXISTS可能更优。避免SELECT *：只查询需要的字段，特别是对于TEXT/BLOB等大字段。HAVING改为WHERE：HAVING用于分组后过滤，尽量将过滤条件放在WHERE中提前过滤。2. 分页查询优化问题：LIMIT 1000000, 20 会扫描100万条记录，性能极差。方案一：游标分页：基于有序且唯一的列（如自增ID）进行翻页。-- 每次记录上一页最后一条的ID SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;方案二：延迟关联：先通过覆盖索引查询出主键，再回表获取其他字段。SELECT * FROM orders o JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t ON o.id = t.id;3. 大数据量处理分批处理：避免一次性操作大量数据，应分批次循环处理。-- 每次处理1000条 UPDATE orders SET status = 'processed' WHERE status = 'pending' LIMIT 1000;分区表：对于历史数据，可以使用分区表进行物理隔离，按时间或键值分区。CREATE TABLE logs ( id BIGINT, log_date DATE ) PARTITION BY RANGE (YEAR(log_date)) ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025) );五、数据库架构与配置优化1. 架构优化读写分离：主库负责写入，从库负责读取，分散压力。分库分表：当单表数据量过大（如超过千万级）时，垂直分库（按业务拆分）或水平分表（按ID取模）。引入缓存：热点数据（如用户信息、商品详情）使用Redis等缓存，减少数据库压力。2. 配置参数调优（关键）在my.cnf或my.ini中调整：InnoDB缓冲池（最重要）：设置为物理内存的50%-80%。innodb_buffer_pool_size = 16G日志缓冲：innodb_log_buffer_size = 64M连接数：max_connections = 1000排序/临时表：sort_buffer_size = 2M tmp_table_size = 64M六、实战调优案例：电商订单查询场景查询用户近3个月内状态为pending或shipped的订单，按时间倒序取前20条。SELECT * FROM orders WHERE user_id = 100 AND status IN ('pending', 'shipped') AND create_time BETWEEN '2024-01-01' AND '2024-03-01' ORDER BY create_time DESC LIMIT 20;问题分析原表可能没有索引，导致全表扫描。ORDER BY导致Using filesort。优化步骤分析：EXPLAIN 显示 type=ALL，Extra=Using where; Using filesort。建立复合索引：遵循最左前缀原则，将等值查询的字段user_id和status放在前面，范围查询字段create_time放在后面，且确保排序的字段也在索引中。CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);改写查询：利用覆盖索引，只查需要的字段，减少回表。SELECT id, user_id, order_no, amount, create_time FROM orders WHERE user_id = 100 AND status IN ('pending', 'shipped') AND create_time BETWEEN '2024-01-01' AND '2024-03-01' ORDER BY create_time DESC LIMIT 20;验证：再次EXPLAIN，type变为range或ref，rows大幅减少，Extra中不再有Using filesort，且出现Using index（覆盖索引）。七、调优检查清单[ ] 慢查询日志：是否已开启并定期分析？[ ] 索引设计：高频查询条件是否有索引？复合索引顺序是否合理？[ ] 索引失效：是否对索引列使用了函数、计算、隐式转换？[ ] SQL写法：是否避免了SELECT *？分页是否使用了大偏移量？[ ] 表结构：字段类型是否选择合适（如能用int就不用varchar）？[ ] 架构层面：读写分离、缓存、分库分表是否按需引入？[ ] 配置参数：缓冲池大小是否合理？连接数是否充足？总结MySQL SQL调优的核心是通过EXPLAIN理解SQL的执行路径，并利用索引减少需要扫描的数据行数。它是一个持续优化的过程，需要结合业务场景、数据规模和系统架构来综合考量。记住口诀：测量先行，索引为王，SQL避坑，架构护航。