第一部分SQL优化五大核心步骤在动手改写SQL之前我们需要一套标准化的诊断流程。就像医生看病要遵循“望闻问切”SQL优化也有固定的五个步骤。第一步慢查询日志定位问题SQL优化工作首先要找到目标——哪些SQL是罪魁祸首MySQL的慢查询日志就是我们的雷达。sql-- 查看慢查询日志是否开启 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 临时开启慢查询日志生产环境需谨慎 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 单位秒记录执行超过1秒的SQL SET GLOBAL log_queries_not_using_indexes ON; -- 记录未使用索引的查询long_query_time建议设置为1-5秒根据业务压力调整。设置太低会导致日志量过大太高会漏掉问题SQL。通过慢查询日志我们拿到了需要优化的SQL清单。第二步EXPLAIN分析执行计划拿到问题SQL后用EXPLAIN命令查看它的执行计划这是最关键的一步。sqlEXPLAIN SELECT * FROM order WHERE shop_id 1 ORDER BY created_at DESC LIMIT 10;EXPLAIN输出结果中需要重点关注的字段有字段重点关注什么危险信号type访问类型效率从好到差const eq_ref ref range index ALLALL全表扫描、index全索引扫描rows预估扫描的行数行数过大与实际不符Extra额外的执行信息Using filesort文件排序、Using temporary临时表type字段详解const/system最多匹配一行主键或唯一索引查询速度最快eq_ref使用唯一索引做表连接每次读取一行ref使用非唯一索引或唯一索引前缀返回多行range索引范围扫描如between、、、in操作index扫描整个索引树比ALL好一点但依然糟糕ALL全表扫描SQL优化的头号敌人Extra危险信号Using filesort无法利用索引排序需要额外排序操作数据量大时性能急剧下降Using temporary使用了临时表保存中间结果常见于GROUP BY、DISTINCT、UNION等操作Using where表示存储引擎返回数据后MySQL服务层再进行过滤可能意味着索引使用不充分第三步SHOW PROFILE分析耗时细节如果EXPLAIN看不出明显问题可以使用SHOW PROFILE查看SQL执行各阶段的耗时。sql-- 开启profiling SET profiling 1; -- 执行你的SQL SELECT * FROM order WHERE shop_id 1 ORDER BY created_at DESC LIMIT 10; -- 查看所有查询的耗时 SHOW PROFILES; -- 查看指定查询的详细耗时 SHOW PROFILE FOR QUERY 1;输出结果会显示CPU、I/O、上下文切换等细粒度指标帮我们定位瓶颈是在数据读取、排序还是网络传输。第四步OPTIMIZER_TRACE分析优化器行为当优化器选择的执行计划不符合预期时可以使用OPTIMIZER_TRACE查看优化器的决策过程。sql-- 开启trace SET optimizer_trace enabledon; SET optimizer_trace_max_mem_size 1000000; -- 执行你的SQL SELECT * FROM order WHERE shop_id 1 ORDER BY created_at DESC LIMIT 10; -- 查看trace结果 SELECT * FROM information_schema.optimizer_trace\Gtrace信息详细记录了优化器考虑哪些索引、如何计算成本、最终为什么选择某个执行计划对理解优化器行为非常有帮助。第五步对症下药采取优化措施通过以上分析我们找到了问题根源接下来就是采取措施。常见的优化手段包括索引优化创建合适索引、调整索引顺序、使用覆盖索引SQL重写改写查询逻辑、分解复杂SQL、避免常见陷阱表结构调整分区表、分库分表、字段类型优化参数调优调整MySQL配置参数如sort_buffer_size、join_buffer_size架构升级引入缓存、搜索引擎、数据仓库接下来我们通过10个真实场景案例看看如何应用这套方法论。第二部分10个实战案例精讲案例1最左匹配原则失效【问题现象】表_t有复合索引idx_shopid_orderno(shop_id,order_no)但以下查询很慢sqlSELECT * FROM _t WHERE order_no 20240001;【原因分析】EXPLAIN显示typeALL全表扫描。复合索引遵循“最左匹配原则”查询条件必须包含索引最左边的列。这里跳过了shop_id直接查order_no索引无法使用。【解决方案】方案一调整索引顺序创建(order_no,shop_id)的新索引方案二改写查询带上shop_id条件如果业务允许方案三为order_no单独创建索引案例2隐式类型转换【问题现象】表_user的mobile字段是varchar类型有索引。以下查询执行缓慢sqlSELECT * FROM _user WHERE mobile 12345678901; -- mobile是varchar类型【原因分析】条件中的12345678901是数字类型MySQL会将字段值转换为数字再比较相当于在索引列上做了运算导致索引失效。EXPLAIN显示typeALL。【解决方案】确保查询条件的类型与字段类型一致sql-- 正确写法 SELECT * FROM _user WHERE mobile 12345678901;案例3大分页查询【问题现象】分页查询随着页码增大越来越慢sqlSELECT * FROM _t WHERE a 1 AND b 2 ORDER BY c DESC LIMIT 10000, 10;【原因分析】LIMIT 10000, 10意味着需要扫描10010行然后丢弃前10000行只返回最后10行。扫描的行数随分页深度线性增长。【解决方案】方案一延迟关联sqlSELECT t1.* FROM _t t1, (SELECT id FROM _t WHERE a 1 AND b 2 ORDER BY c DESC LIMIT 10000, 10) t2 WHERE t1.id t2.id;子查询只查主键id可以利用覆盖索引避免回表。然后通过主键关联获取完整数据。方案二记录上次位置sql-- 第一次查询记录最后一条的c值 SELECT * FROM _t WHERE a 1 AND b 2 ORDER BY c DESC LIMIT 10; -- 下一页查询传入上次的c值 SELECT * FROM _t WHERE a 1 AND b 2 AND c last_c ORDER BY c DESC LIMIT 10;这种方式利用了索引的有序性扫描行数固定为10但需要修改接口协议。案例4IN ORDER BY组合【问题现象】sqlSELECT * FROM _order WHERE shop_id 1 AND order_status IN (1, 2, 3) ORDER BY created_at DESC LIMIT 10;【原因分析】复合索引是idx_shopid_status_created(shop_id,order_status,created_at)。IN条件会导致索引使用不理想MySQL在计算成本时当IN内的值超过eq_range_index_dive_limit默认200时cost估算不准确可能选错索引。【解决方案】调整索引顺序为(shop_id,created_at,order_status)结合延迟关联sqlSELECT t1.* FROM _order t1, (SELECT id FROM _order WHERE shop_id 1 AND order_status IN (1, 2, 3) ORDER BY created_at DESC LIMIT 10) t2 WHERE t1.id t2.id;案例5范围查询阻断后续索引【问题现象】sqlSELECT * FROM _order WHERE shop_id 1 AND created_at 2021-01-01 AND order_status 10;【原因分析】索引idx_shopid_created_status(shop_id,created_at,order_status)。当created_at使用范围查询后后续的order_status条件无法通过索引过滤只能在扫描出的数据中逐一判断。【解决方案】将范围条件放在索引最后sql-- 调整索引顺序为 (shop_id, order_status, created_at) -- 查询保持不变这样shop_id和order_status先过滤出精确范围最后对created_at做范围扫描。案例6不等于条件无法使用索引【问题现象】sqlSELECT * FROM _order WHERE shop_id 1 AND order_status ! 1;【原因分析】!、、NOT IN等不等于操作通常无法使用索引快速定位但MySQL 5.6的索引条件下推ICP可以在一定程度上优化减少回表次数。【解决方案】如果业务允许可以改写为IN或BETWEENsql-- 假设order_status只有1、2、3三种状态 SELECT * FROM _order WHERE shop_id 1 AND order_status IN (2, 3);或者使用UNION ALLsqlSELECT * FROM _order WHERE shop_id 1 AND order_status 2 UNION ALL SELECT * FROM _order WHERE shop_id 1 AND order_status 3;案例7优化器放弃索引【问题现象】sqlSELECT * FROM _order WHERE order_status 1; -- 查询未支付订单表中有大量数据但未支付订单只占1%仍然走了全表扫描。【原因分析】当需要访问的数据占表的比例较大时通常是20%左右优化器认为全表扫描比索引扫描更高效。因为使用二级索引需要先查索引再回表产生大量随机I/O。【解决方案】方案一使用覆盖索引避免回表sql-- 创建覆盖索引 ALTER TABLE _order ADD INDEX idx_status_covered (order_status, id, other_columns);方案二强制使用索引sqlSELECT * FROM _order FORCE INDEX(idx_order_status) WHERE order_status 1;方案三如果比例确实很低需要更新统计信息帮助优化器做出正确判断案例8复杂统计查询【问题现象】sqlSELECT SUM(amt) FROM _t WHERE a 1 AND b IN (1, 2, 3) AND c 2020-01-01;表数据量巨大查询耗时几十秒。【原因分析】虽然走索引但数据量大、条件复杂MySQL需要扫描大量行进行累加计算。【解决方案】方案一汇总表定时预计算sql-- 创建每日汇总表 INSERT INTO daily_summary (date, a, b, total_amt) SELECT DATE(c), a, b, SUM(amt) FROM _t WHERE c CURDATE() - INTERVAL 1 DAY AND c CURDATE() GROUP BY DATE(c), a, b;方案二引入Elasticsearch等搜索引擎适合复杂聚合分析方案三使用数据仓库如ClickHouse、Doris处理海量数据分析案例9排序方向混用【问题现象】sqlSELECT * FROM _t WHERE a 1 ORDER BY b DESC, c ASC;【原因分析】索引idx_a_b_c(a, b, c)默认都是升序。当ORDER BY b DESC, c ASC时b的排序方向与索引相反导致无法利用索引排序出现Using filesort。【解决方案】方案一创建与排序一致的索引sql-- 索引指定排序方向 ALTER TABLE _t ADD INDEX idx_a_b_desc_c_asc (a, b DESC, c ASC);方案二如果无法修改索引尝试调整排序方向业务允许的话案例10数据碎片整理【问题现象】业务表定期清理过期数据如7天前的数据但随着清理操作执行查询性能逐渐下降。【原因分析】频繁的删除和更新操作会产生数据碎片。表数据在物理文件上不连续导致扫描效率降低。虽然统计信息显示行数不多但实际物理I/O很大。【解决方案】定期进行碎片整理sql-- InnoDB表重建 ALTER TABLE table_name ENGINE InnoDB; -- 或者使用OPTIMIZE OPTIMIZE TABLE table_name;建议在业务低峰期执行并评估所需时间。对于大表可以考虑使用pt-online-schema-change工具在线操作。第三部分进阶优化策略掌握了基础的5大步骤和10个案例你已经能解决80%的SQL性能问题。剩下的20%需要更深入的理解和技巧。统计信息的重要性优化器依赖统计信息估算成本。过时的统计信息可能导致选错索引。sql-- 查看统计信息更新时间 SHOW TABLE STATUS LIKE table_name; -- 更新统计信息 ANALYZE TABLE table_name;在以下场景应及时更新统计信息大量数据导入/删除后索引创建/删除后数据分布发生重大变化索引设计的艺术高选择性列优先选择性 不重复值数量 / 总行数越接近1越好尽量使用覆盖索引索引包含查询需要的所有列消除回表冗余索引清理定期检查重复或未使用的索引sql-- 查找未使用的索引需要开启performance_schema SELECT * FROM sys.schema_unused_indexes;分库分表策略当单表数据量过大超过千万级别索引也无法解决问题时需要考虑分库分表垂直拆分按业务模块拆分到不同数据库水平拆分按某个字段如用户IDhash到不同表/库分区表MySQL内置分区功能对应用透明利用HINT影响优化器在少数情况下优化器的选择不符合预期可以使用HINT强制指定。sql-- 强制使用某个索引 SELECT * FROM table_name FORCE INDEX(idx_name) WHERE ...; -- 指定JOIN顺序 SELECT /* STRAIGHT_JOIN */ ...; -- MySQL 8.0的优化器HINT更丰富 SELECT /* INDEX(table_name idx_name) */ ...;避免过度优化不是所有SQL都需要优化到极致。遵循80/20法则集中精力优化高频执行、消耗最大的SQL。sql-- 例如枚举值很少的字段GROUP BY可能比DISTINCT更慢 SELECT COUNT(DISTINCT status) FROM large_table; -- 可能更快 -- 不要盲目改为 SELECT COUNT(*) FROM (SELECT status FROM large_table GROUP BY status) t; -- 可能更慢结语SQL优化是一项实践性很强的工作需要理论结合实战。本文的“5大步骤10个案例”是一套经过验证的方法论希望能帮你建立起自己的优化体系。回顾一下核心要点诊断先行用慢日志、EXPLAIN、profile找出真凶索引为王理解索引工作原理合理设计索引案例积累常见场景心中有数遇到问题快速定位适度优化权衡成本与收益避免过度设计