1. MySQL查询优化为什么你的SQL跑得慢刚入行那会儿我最怕的就是DBA走过来问这个查询是你写的——通常意味着某个SQL语句正在拖垮整个数据库。经过多年踩坑我发现90%的性能问题都源于糟糕的查询设计。以最近优化的一个订单统计查询为例原始执行时间4.7秒优化后仅需0.03秒提升156倍这背后不是魔法而是对MySQL工作原理的理解和正确的优化姿势。查询效率低下的典型症状包括页面加载转圈、批量作业超时、数据库CPU飙升。这些问题往往源于全表扫描、临时表滥用、错误索引使用等常见陷阱。比如用OR连接不同字段的条件时MySQL通常无法有效使用索引而改用UNION ALL往往能立竿见影。关键认知优化不是简单的加索引而是让查询方式匹配MySQL的思考方式2. 核心优化原则与执行计划分析2.1 EXPLAIN你的SQL体检报告拿到问题SQL后我第一反应总是先看执行计划。这个5.7版本的表结构很能说明问题CREATE TABLE order_details ( id bigint(20) NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id int(11) NOT NULL, product_id int(11) NOT NULL, status tinyint(4) NOT NULL DEFAULT 0, create_time datetime NOT NULL, price decimal(10,2) NOT NULL, PRIMARY KEY (id), KEY idx_user (user_id), KEY idx_product (product_id), KEY idx_time_status (create_time,status) ) ENGINEInnoDB;对于这个看似简单的查询EXPLAIN SELECT * FROM order_details WHERE user_id 1001 AND status 2;执行计划显示idselect_typetabletypepossible_keyskeyrowsExtra1SIMPLEorder_detailsrefidx_user,idx_time_statusidx_user253Using where这里暴露了三个问题使用了idx_user但没用到status条件typeref还算可以但不如eq_refUsing where表示存储引擎检索行后还要过滤2.2 索引优化实战策略组合索引的黄金法则针对上述案例最佳解决方案是创建覆盖索引ALTER TABLE order_details ADD INDEX idx_user_status (user_id, status);优化后的执行计划idselect_typetabletypepossible_keyskeyrowsExtra1SIMPLEorder_detailsrefidx_user,idx_time_status,idx_user_statusidx_user_status17Using index关键改进rows从253降到17Extra显示Using index索引覆盖扫描行数减少92%索引选择性的计算公式创建索引前先用这个公式评估价值SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;经验值0.2适合单列索引0.1可考虑组合索引0.01通常不值得建索引3. 高级优化技巧与反模式规避3.1 查询重写艺术分页查询优化典型反模式SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;优化方案假设主键是idSELECT * FROM orders WHERE id (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 1) ORDER BY create_time DESC LIMIT 20;实测效果100万数据原查询1.2s优化后0.05sJOIN优化三原则小表驱动大表小表放在JOIN左侧确保关联字段有索引避免SELECT *只取必要字段错误示范SELECT * FROM users u JOIN orders o ON u.id o.user_id JOIN products p ON o.product_id p.id;优化版本SELECT u.name, o.order_no, p.product_name FROM orders o JOIN users u ON o.user_id u.id JOIN products p ON o.product_id p.id;3.2 隐式转换的陷阱这个查询看起来没问题SELECT * FROM users WHERE phone 13800138000;但若phone是varchar类型会导致全表扫描每行都要做类型转换正确写法SELECT * FROM users WHERE phone 13800138000;常见隐式转换场景字符串字段与数字比较字符集不匹配的JOIN日期与字符串比较4. 实战电商系统SQL优化全记录4.1 案例背景某电商平台促销期间出现数据库CPU持续100%主要慢查询是一个订单统计SQLSELECT COUNT(DISTINCT o.id) AS order_count, SUM(oi.price * oi.quantity) AS gmv FROM orders o JOIN order_items oi ON o.id oi.order_id WHERE o.create_time BETWEEN 2023-11-01 AND 2023-11-11 AND o.status IN (2,3,5) AND oi.product_id IN ( SELECT id FROM products WHERE category_id 12 AND is_deleted 0 );执行时间8.7秒4.2 优化步骤分解第一步分析执行计划发现orders表全表扫描order_items使用低效的index_merge子查询产生临时表第二步索引优化ALTER TABLE orders ADD INDEX idx_status_time (status, create_time); ALTER TABLE order_items ADD INDEX idx_order_product (order_id, product_id);第三步查询重写改为JOIN替代IN子查询SELECT COUNT(DISTINCT o.id) AS order_count, SUM(oi.price * oi.quantity) AS gmv FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN products p ON oi.product_id p.id WHERE o.create_time BETWEEN 2023-11-01 AND 2023-11-11 AND o.status IN (2,3,5) AND p.category_id 12 AND p.is_deleted 0;第四步进一步优化使用派生表减少DISTINCT计算量SELECT COUNT(*) AS order_count, SUM(oi_sum) AS gmv FROM ( SELECT o.id, SUM(oi.price * oi.quantity) AS oi_sum FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN products p ON oi.product_id p.id WHERE o.create_time BETWEEN 2023-11-01 AND 2023-11-11 AND o.status IN (2,3,5) AND p.category_id 12 AND p.is_deleted 0 GROUP BY o.id ) t;最终执行时间0.15秒提升58倍5. 慢查询日志分析与优化工具链5.1 开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的记录 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;5.2 使用pt-query-digest分析pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt分析报告关键指标Query time distributionTables involvedIndex usageQuery fingerprint5.3 优化器提示Optimizer Hints当优化器选错索引时SELECT /* INDEX(orders idx_status_time) */ * FROM orders WHERE status 2 AND create_time 2023-01-01;常用提示/* INDEX(table index) */强制使用索引/* NO_INDEX(table index) */禁止使用索引/* JOIN_ORDER(table1, table2) */指定JOIN顺序6. 性能监控与持续优化6.1 关键性能指标-- 查看当前连接状态 SHOW STATUS LIKE Threads_%; -- 查看索引使用情况 SELECT * FROM sys.schema_index_statistics WHERE table_schema NOT IN (mysql,sys); -- 查看全表扫描查询 SELECT * FROM sys.statements_with_full_table_scans ORDER BY exec_count DESC LIMIT 10;6.2 定期维护建议每周分析慢查询日志每月检查冗余索引大促前进行压力测试使用pt-index-usage跟踪索引使用率6.3 配置参数调优关键参数根据服务器配置调整[mysqld] innodb_buffer_pool_size 12G # 总内存的50-70% innodb_log_file_size 2G innodb_flush_log_at_trx_commit 2 # 非金融业务可设为2 innodb_read_io_threads 16 innodb_write_io_threads 167. 避坑指南我踩过的那些坑OR条件优化错误WHERE a1 OR b2正确WHERE a1 UNION ALL SELECT ... WHERE b2 AND a!1LIKE模糊查询LIKE %关键字%绝对不用索引LIKE 关键字%可能用索引COUNT(*) vs COUNT(1)在MySQL中性能无差异但COUNT(列名)会排除NULL值事务隔离级别读多写少用READ-COMMITTED写多用REPEATABLE-READ批量插入优化错误循环执行单条INSERT正确使用多值INSERT或LOAD DATA-- 低效 INSERT INTO t VALUES(1); INSERT INTO t VALUES(2); -- 高效 INSERT INTO t VALUES(1),(2); -- 最高效 LOAD DATA INFILE data.txt INTO TABLE t;8. 新版MySQL的优化新特性8.1 MySQL 8.0优化器增强不可见索引测试删除索引的影响而不真正删除ALTER TABLE t ALTER INDEX idx_name INVISIBLE;降序索引更好地支持ORDER BY DESCCREATE INDEX idx_name ON t(create_time DESC);函数索引直接索引计算列CREATE INDEX idx_name ON t((DATE(create_time)));8.2 窗口函数优化旧版需要自连接或变量的复杂分页-- 8.0 高效实现 SELECT *, RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS ranking FROM employees;9. 优化检查清单在提交SQL前问自己这7个问题是否使用了EXPLAIN分析是否用到了合适的索引是否有更好的JOIN顺序是否可以减少返回的数据量是否可以避免临时表是否可以重写子查询是否可以批量操作替代循环记住最好的优化往往发生在设计阶段。合理的表结构、恰当的数据类型、超前的索引规划比事后调优重要十倍。上周我review的一个新系统由于初期设计了冗余字段使核心查询减少了3个JOIN操作QPS直接从200提升到1500。这种架构级的优化才是真正的高手之道。 
长河君 · 2026/9/10 16:10:47 · 阅读 1.2k MySQL查询优化实战：从慢查询到高性能SQL
1. MySQL查询优化为什么你的SQL跑得慢刚入行那会儿我最怕的就是DBA走过来问这个查询是你写的——通常意味着某个SQL语句正在拖垮整个数据库。经过多年踩坑我发现90%的性能问题都源于糟糕的查询设计。以最近优化的一个订单统计查询为例原始执行时间4.7秒优化后仅需0.03秒提升156倍这背后不是魔法而是对MySQL工作原理的理解和正确的优化姿势。查询效率低下的典型症状包括页面加载转圈、批量作业超时、数据库CPU飙升。这些问题往往源于全表扫描、临时表滥用、错误索引使用等常见陷阱。比如用OR连接不同字段的条件时MySQL通常无法有效使用索引而改用UNION ALL往往能立竿见影。关键认知优化不是简单的加索引而是让查询方式匹配MySQL的思考方式2. 核心优化原则与执行计划分析2.1 EXPLAIN你的SQL体检报告拿到问题SQL后我第一反应总是先看执行计划。这个5.7版本的表结构很能说明问题CREATE TABLE order_details ( id bigint(20) NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id int(11) NOT NULL, product_id int(11) NOT NULL, status tinyint(4) NOT NULL DEFAULT 0, create_time datetime NOT NULL, price decimal(10,2) NOT NULL, PRIMARY KEY (id), KEY idx_user (user_id), KEY idx_product (product_id), KEY idx_time_status (create_time,status) ) ENGINEInnoDB;对于这个看似简单的查询EXPLAIN SELECT * FROM order_details WHERE user_id 1001 AND status 2;执行计划显示idselect_typetabletypepossible_keyskeyrowsExtra1SIMPLEorder_detailsrefidx_user,idx_time_statusidx_user253Using where这里暴露了三个问题使用了idx_user但没用到status条件typeref还算可以但不如eq_refUsing where表示存储引擎检索行后还要过滤2.2 索引优化实战策略组合索引的黄金法则针对上述案例最佳解决方案是创建覆盖索引ALTER TABLE order_details ADD INDEX idx_user_status (user_id, status);优化后的执行计划idselect_typetabletypepossible_keyskeyrowsExtra1SIMPLEorder_detailsrefidx_user,idx_time_status,idx_user_statusidx_user_status17Using index关键改进rows从253降到17Extra显示Using index索引覆盖扫描行数减少92%索引选择性的计算公式创建索引前先用这个公式评估价值SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;经验值0.2适合单列索引0.1可考虑组合索引0.01通常不值得建索引3. 高级优化技巧与反模式规避3.1 查询重写艺术分页查询优化典型反模式SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;优化方案假设主键是idSELECT * FROM orders WHERE id (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 1) ORDER BY create_time DESC LIMIT 20;实测效果100万数据原查询1.2s优化后0.05sJOIN优化三原则小表驱动大表小表放在JOIN左侧确保关联字段有索引避免SELECT *只取必要字段错误示范SELECT * FROM users u JOIN orders o ON u.id o.user_id JOIN products p ON o.product_id p.id;优化版本SELECT u.name, o.order_no, p.product_name FROM orders o JOIN users u ON o.user_id u.id JOIN products p ON o.product_id p.id;3.2 隐式转换的陷阱这个查询看起来没问题SELECT * FROM users WHERE phone 13800138000;但若phone是varchar类型会导致全表扫描每行都要做类型转换正确写法SELECT * FROM users WHERE phone 13800138000;常见隐式转换场景字符串字段与数字比较字符集不匹配的JOIN日期与字符串比较4. 实战电商系统SQL优化全记录4.1 案例背景某电商平台促销期间出现数据库CPU持续100%主要慢查询是一个订单统计SQLSELECT COUNT(DISTINCT o.id) AS order_count, SUM(oi.price * oi.quantity) AS gmv FROM orders o JOIN order_items oi ON o.id oi.order_id WHERE o.create_time BETWEEN 2023-11-01 AND 2023-11-11 AND o.status IN (2,3,5) AND oi.product_id IN ( SELECT id FROM products WHERE category_id 12 AND is_deleted 0 );执行时间8.7秒4.2 优化步骤分解第一步分析执行计划发现orders表全表扫描order_items使用低效的index_merge子查询产生临时表第二步索引优化ALTER TABLE orders ADD INDEX idx_status_time (status, create_time); ALTER TABLE order_items ADD INDEX idx_order_product (order_id, product_id);第三步查询重写改为JOIN替代IN子查询SELECT COUNT(DISTINCT o.id) AS order_count, SUM(oi.price * oi.quantity) AS gmv FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN products p ON oi.product_id p.id WHERE o.create_time BETWEEN 2023-11-01 AND 2023-11-11 AND o.status IN (2,3,5) AND p.category_id 12 AND p.is_deleted 0;第四步进一步优化使用派生表减少DISTINCT计算量SELECT COUNT(*) AS order_count, SUM(oi_sum) AS gmv FROM ( SELECT o.id, SUM(oi.price * oi.quantity) AS oi_sum FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN products p ON oi.product_id p.id WHERE o.create_time BETWEEN 2023-11-01 AND 2023-11-11 AND o.status IN (2,3,5) AND p.category_id 12 AND p.is_deleted 0 GROUP BY o.id ) t;最终执行时间0.15秒提升58倍5. 慢查询日志分析与优化工具链5.1 开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的记录 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;5.2 使用pt-query-digest分析pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt分析报告关键指标Query time distributionTables involvedIndex usageQuery fingerprint5.3 优化器提示Optimizer Hints当优化器选错索引时SELECT /* INDEX(orders idx_status_time) */ * FROM orders WHERE status 2 AND create_time 2023-01-01;常用提示/* INDEX(table index) */强制使用索引/* NO_INDEX(table index) */禁止使用索引/* JOIN_ORDER(table1, table2) */指定JOIN顺序6. 性能监控与持续优化6.1 关键性能指标-- 查看当前连接状态 SHOW STATUS LIKE Threads_%; -- 查看索引使用情况 SELECT * FROM sys.schema_index_statistics WHERE table_schema NOT IN (mysql,sys); -- 查看全表扫描查询 SELECT * FROM sys.statements_with_full_table_scans ORDER BY exec_count DESC LIMIT 10;6.2 定期维护建议每周分析慢查询日志每月检查冗余索引大促前进行压力测试使用pt-index-usage跟踪索引使用率6.3 配置参数调优关键参数根据服务器配置调整[mysqld] innodb_buffer_pool_size 12G # 总内存的50-70% innodb_log_file_size 2G innodb_flush_log_at_trx_commit 2 # 非金融业务可设为2 innodb_read_io_threads 16 innodb_write_io_threads 167. 避坑指南我踩过的那些坑OR条件优化错误WHERE a1 OR b2正确WHERE a1 UNION ALL SELECT ... WHERE b2 AND a!1LIKE模糊查询LIKE %关键字%绝对不用索引LIKE 关键字%可能用索引COUNT(*) vs COUNT(1)在MySQL中性能无差异但COUNT(列名)会排除NULL值事务隔离级别读多写少用READ-COMMITTED写多用REPEATABLE-READ批量插入优化错误循环执行单条INSERT正确使用多值INSERT或LOAD DATA-- 低效 INSERT INTO t VALUES(1); INSERT INTO t VALUES(2); -- 高效 INSERT INTO t VALUES(1),(2); -- 最高效 LOAD DATA INFILE data.txt INTO TABLE t;8. 新版MySQL的优化新特性8.1 MySQL 8.0优化器增强不可见索引测试删除索引的影响而不真正删除ALTER TABLE t ALTER INDEX idx_name INVISIBLE;降序索引更好地支持ORDER BY DESCCREATE INDEX idx_name ON t(create_time DESC);函数索引直接索引计算列CREATE INDEX idx_name ON t((DATE(create_time)));8.2 窗口函数优化旧版需要自连接或变量的复杂分页-- 8.0 高效实现 SELECT *, RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS ranking FROM employees;9. 优化检查清单在提交SQL前问自己这7个问题是否使用了EXPLAIN分析是否用到了合适的索引是否有更好的JOIN顺序是否可以减少返回的数据量是否可以避免临时表是否可以重写子查询是否可以批量操作替代循环记住最好的优化往往发生在设计阶段。合理的表结构、恰当的数据类型、超前的索引规划比事后调优重要十倍。上周我review的一个新系统由于初期设计了冗余字段使核心查询减少了3个JOIN操作QPS直接从200提升到1500。这种架构级的优化才是真正的高手之道。