最近排查了一个让业务方半夜打来电话的慢查询语句本身不复杂两张表做个关联查询单次执行要3秒多。这种量级的慢查询在OLTP系统里已经算事故了压测的时候直接拖垮了连接池。折腾了一圈最后只改了一行代码执行时间降到50毫秒。整个过程挺典型的从现象、定位、分析到解决每一步都有值得复盘的地方。这篇文章就把完整思路和操作细节写出来遇到同类问题可以直接照着排查。我也把执行计划、优化前后的对比、以及一些容易踩的坑都整理在下面了包括为什么索引建了却用不上、为什么关联查询会全表扫描、字符集和排序规则到底怎么影响性能。如果你手头正好有类似的慢SQL建议先把EXPLAIN跑一遍再对照文章里的思路一步步来大概率能找出问题所在。1. 问题现象慢查询是怎么被发现的先说下当时的业务背景。这是一个电商类的订单查询接口前端页面要展示用户的历史订单列表后端SQL大致逻辑是查订单主表再关联用户的优惠券领取记录表目的是拿到每笔订单用了哪张优惠券、优惠了多少金额。表结构并不复杂订单表数据量在百万级优惠券领取记录表在千万级两个表都建了索引。收到监控告警的时候接口平均响应时间已经超过2秒数据库的CPU使用率也居高不下。查看了慢查询日志发现有一条SQL的执行时间稳定在2.8秒到3.2秒之间而且调用频率还不低高峰期每分钟要被调用几十次。这意味着数据库每秒钟都要分出好几个线程去处理这个查询连接池很快就被占满了。这类问题最直接的排查入口就是慢查询日志。但慢查询日志默认是关闭的需要手动开启并且要设置合理的阈值。生产环境我一般把long_query_time设为1秒也就是超过1秒的SQL都会被记录下来太小了日志量会爆炸太大了又容易漏掉真正有问题的语句。开启方式如下-- 临时开启重启MySQL后失效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 永久生效需要写入my.cnf配置文件 -- [mysqld] -- slow_query_log 1 -- slow_query_log_file /var/log/mysql/mysql-slow.log -- long_query_time 1日志里被抓到的那条慢SQL简化后大概是这样的SELECT o.order_id, o.order_amount, c.coupon_amount FROM order_info o LEFT JOIN coupon_usage c ON o.order_id c.order_id WHERE o.user_id 10086 AND o.order_status 1 ORDER BY o.pay_time DESC LIMIT 20;一眼看上去这个SQL似乎没什么大问题order_info表的user_id有索引coupon_usage表的order_id也有索引关联字段都建了索引为什么还会慢成这样带着这个疑问我开始一步步排查。提示慢查询日志记录的是SQL文本、执行时间、锁等待时间、返回行数等信息但不会直接告诉你问题出在哪需要配合EXPLAIN分析执行计划才能定位根因。2. 定位与分析EXPLAIN执行计划里的门道拿到慢SQL之后第一件事不是去改代码而是用EXPLAIN看执行计划。这就像看病先拍片子哪里出了问题一目了然。我在生产库的只读副本上执行了EXPLAIN结果如下EXPLAIN SELECT o.order_id, o.order_amount, c.coupon_amount FROM order_info o LEFT JOIN coupon_usage c ON o.order_id c.order_id WHERE o.user_id 10086 AND o.order_status 1 ORDER BY o.pay_time DESC LIMIT 20;执行计划的关键信息如下表typepossible_keyskeyrowsExtraorder_info orefidx_user_id, idx_pay_timeidx_user_id3560Using index condition; Using filesortcoupon_usage cALLidx_order_idNULL13204488Using where; Using join buffer (hash join)看到ALL这个关键字问题基本就锁定了coupon_usage表在被驱动的时候居然是全表扫描扫描行数高达1300多万。而key列显示NULL说明idx_order_id索引完全没被用上。这就像两个人联合办公左边的人效率很高右边的人却把一整栋楼的档案都翻了一遍才找到对应记录整体速度自然被拖垮。为什么索引存在却没用上这里要解释一下MySQL优化器的工作原理。EXPLAIN输出的rows字段是优化器估算的需要扫描的行数当它认为走索引的成本比全表扫描还高时就会放弃索引。但在我们这个场景里1300多万行的全表扫描成本显然更高优化器不太可能因为成本原因放弃索引。那剩下的可能性就只有索引失效了。顺着这个思路我看了下两张表的建表语句问题很快就暴露了-- order_info 表 CREATE TABLE order_info ( order_id bigint NOT NULL COMMENT 订单ID, user_id bigint NOT NULL COMMENT 用户ID, order_status tinyint NOT NULL DEFAULT 0 COMMENT 订单状态, pay_time datetime DEFAULT NULL COMMENT 支付时间, order_amount decimal(10,2) DEFAULT NULL COMMENT 订单金额, PRIMARY KEY (order_id), KEY idx_user_id (user_id), KEY idx_pay_time (pay_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci; -- coupon_usage 表 CREATE TABLE coupon_usage ( id bigint NOT NULL AUTO_INCREMENT COMMENT 自增主键, order_id varchar(32) NOT NULL COMMENT 订单ID历史原因导致字段类型不一致, coupon_id bigint NOT NULL COMMENT 优惠券ID, coupon_amount decimal(10,2) DEFAULT NULL COMMENT 优惠金额, PRIMARY KEY (id), KEY idx_order_id (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;问题就出在order_info.order_id是bigint类型而coupon_usage.order_id是varchar(32)类型。两个不同类型的字段做等值匹配MySQL会自动进行隐式类型转换把varchar转换成数值型再比较。一旦对索引列做了函数或者类型转换索引就失效了。这个原理其实不难理解。varchar类型的order_id存储的是字符串比如10086而bigint类型的order_id存储的是数字10086。MySQL做比较的时候要把字符串10086隐式转换成数字10086。转换过程是逐行进行的等于对coupon_usage.order_id列上的每一行都执行了一次CAST(order_id AS SIGNED)这和在索引列上直接写函数是一个效果——索引树的结构是基于原始字符串值建立的一旦被转换原来的索引顺序就派不上用场了。这里还有一个更隐蔽的坑即使你给varchar列建了索引只要查询条件里用了数值型和它比较索引一样会失效。因为MySQL的规则是当比较的双方一个是字符串、一个是数字时字符串会被转换成数字而不是反过来。所以哪怕你把查询条件写成ON o.order_id CAST(c.order_id AS UNSIGNED)也只会在c表上触发全表扫描因为转换操作作用在了索引列上。注意看到Using join buffer (hash join)并不一定代表MySQL走了hash join算法。这里其实是MySQL 8.0在无法使用索引做关联时退化成的一种BNLBlock Nested Loop优化本质还是扫描驱动表的大量数据。真正的问题在于被驱动表上没有可用索引。3. 核心优化动作那一行代码到底改了啥既然根因是字段类型不一致导致的隐式类型转换那最优的解法自然是统一两边的类型。理论上最彻底的做法是改表结构把coupon_usage.order_id从varchar(32)改成bigint。但生产环境千万级数据的表ALTER TABLE动辄锁表几分钟而且这个字段可能被其他系统引用改动风险太大业务方根本不会同意在高峰期做这种操作。所以我的选择是不改表结构只改SQL语句中的关联条件让转换作用在没有索引的那一侧也就是驱动表的字段上。修正后的SQL如下SELECT o.order_id, o.order_amount, c.coupon_amount FROM order_info o LEFT JOIN coupon_usage c ON o.order_id CAST(c.order_id AS UNSIGNED) WHERE o.user_id 10086 AND o.order_status 1 ORDER BY o.pay_time DESC LIMIT 20;注意这行代码的微妙之处CAST(c.order_id AS UNSIGNED)是显式地对coupon_usage.order_id做类型转换。从逻辑上看和MySQL自动执行的隐式转换是一样的但执行计划却完全不同。因为在优化器看来c.order_id被函数包裹之后索引确实无法使用了但此时它可以选择把转换后的结果应用到驱动表order_info的order_id上进行匹配而不是对1300万行的coupon_usage逐行转换。换句话说原来MySQL是把整个被驱动表拖出来硬扛现在变成了只取驱动表查询出来的那几十行结果去被驱动表里精准匹配。驱动表order_info经过user_id索引过滤后实际只剩3560行再经过order_status条件过滤可能就剩几百行。用这几百行去匹配1300万行的表每一行都走idx_order_id索引扫描行数急剧下降。改完之后再看执行计划表typekeyrowsExtraorder_info orefidx_user_id3560Using index condition; Using filesortcoupon_usage crefidx_order_id1Using wherecoupon_usage的type从ALL变成了refkey使用上了idx_order_idrows从1320万降到了1。执行时间直接从3.1秒降到了50毫秒左右效果立竿见影。不过这里要诚实地说一句这个写法虽然快但并不是最优雅的长期方案。因为CAST(c.order_id AS UNSIGNED)仍然意味着每次执行查询时都要对coupon_usage.order_id做一次转换运算虽然数据量小了之后影响可以忽略但在海量数据的场景下这仍然是一个性能隐患。最规范的做法还是找一次低峰期窗口把表结构统一了或者新建一个order_id_bigint冗余字段建上索引然后逐步迁移。实操心得如果你和我一样不能立刻改表结构但又不想在SQL里写CAST还有一个临时方案——把驱动表和被驱动表对调先用子查询取出小结果集再跟大表关联。比如写成子查询先过滤出订单再JOIN优惠券表。但在MySQL 8.0里子查询的优化已经做得不错了具体效果还是要以EXPLAIN为准。我自己更推荐显式CAST因为这个写法对优化器的提示最明确。4. 优化前后对比与相关参数验证改完SQL之后我做了三轮验证单次执行耗时、并发压测、以及慢查询日志确认。先说单次执行耗时。用SHOW PROFILE可以看SQL执行各阶段的耗时分布SET profiling 1; -- 执行慢SQL原始版本 SELECT ...; -- 执行优化后SQL SELECT ...; SHOW PROFILES;结果对比很直观版本执行耗时扫描行数额外开销原始SQL隐式转换3.126s约1320万全表扫描 hash join缓冲优化后SQL显式CAST0.048s约3560 关联命中行索引查找这里要提一个容易忽略的点Using filesort还在执行计划里不过它排序的是驱动表过滤后的几百行数据消耗几乎可以忽略。如果要彻底消除这个文件排序可以给order_info建一个(user_id, order_status, pay_time)的复合索引让索引天然有序避免排序。但在当前量级下加这个索引的收益不大反而会增加写入负担所以我没有做这一步。接着做并发压测。用压测工具模拟100个并发线程持续压测5分钟对比优化前后的吞吐量和延迟指标优化前优化后QPS每秒查询数421800平均响应时间2200ms62msP99响应时间3800ms110ms数据库CPU使用率85%22%数据差异非常明显。优化前数据库CPU几乎被打满连接池大量积压接口超时率接近8%优化后CPU占用大幅下降接口全部正常返回。这个改动对整体系统稳定性的提升比单纯解决一个慢查询意义更大。最后确认慢查询日志。优化上线后观察了48小时这条SQL再也没出现在慢日志里。监控告警也都恢复了平静。提示判断优化是否有效不能只看一句SQL的执行时间。要结合EXPLAIN中的type、rows、Extra三列综合评估。type从ALL变成refrows从千万级降到个位数Extra里不再出现Using join buffer这三个信号同时出现基本可以断定优化到位了。5. 常见问题与避坑指南遇到类似慢查询该怎么办这个案例讲完了但实际工作中慢查询的坑远不止隐式类型转换一种。我顺手整理了几个高频出现的问题和排查经验每条都是实打实踩过坑才总结出来的。5.1 为什么索引建了却不走可能有哪些原因索引失效的原因很多我捡最常见的几个说。第一就是本节案例里的隐式类型转换。但在写CAST时要注意方向一定要把函数加在驱动表字段上而不是被驱动表字段上。比如ON CAST(o.order_id AS CHAR) c.order_id和ON o.order_id CAST(c.order_id AS UNSIGNED)的优化效果差异很大。前者是对驱动表3560行做转换然后去匹配索引列后者是对被驱动表1300万行做转换索引直接失效。两者的执行计划完全不同千万不要搞反。第二是函数包裹索引列。比如WHERE DATE(pay_time) 2024-01-01优化器无法直接用pay_time索引因为需要先把每一行的pay_time都计算一遍才能比较。正确写法是范围查询WHERE pay_time 2024-01-01 00:00:00 AND pay_time 2024-01-02 00:00:00这种改写对优化器非常友好能充分利用索引的范围扫描能力。第三是前置通配符。LIKE %abc%这种写法索引也是用不上的。如果业务真的需要包含匹配建议用全文索引或者外部的搜索引擎不要指望数据库硬扛。第四是联合索引不满足最左前缀原则。比如索引是(user_id, order_status, pay_time)但查询条件是WHERE order_status 1 AND pay_time 2024-01-01跳过了user_id索引直接失效。5.2 字符集不同会导致索引失效吗怎么排查和字段类型不一致类似的还有字符集不一致的问题。两个表的关联字段一个是utf8mb4一个是latin1关联的时候MySQL也会做隐式字符集转换导致索引失效。排查方法很简单看执行计划的rows列是否异常翻倍再查两张表的CHARACTER_SET_NAMESELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME IN (order_info, coupon_usage) AND COLUMN_NAME order_id;字符集不一致的解决方案有两种一种是统一两边表的字符集把latin1表改成utf8mb4另一种是在关联条件里显式转换写法是CONVERT(c.order_id USING utf8mb4)不过还是要留意函数加在哪一侧。核心原则所有函数、类型转换、字符集转换都尽量加在驱动表一侧保住被驱动表的索引。5.3 为什么会看到Using join buffer这说明什么Using join buffer是一个非常明确的信号说明MySQL在关联查询时无法直接通过索引获取被驱动表的匹配行只能把驱动表的数据先放进内存缓冲区再去被驱动表里逐行匹配。虽然MySQL 8.0对hash join做了一些优化但本质上还是笨办法数据量一大就原形毕露。看到这个标记优先排查关联字段的索引情况。如果两个关联字段的类型、字符集完全一致还是出现Using join buffer那要考虑是不是统计信息过期了执行一下ANALYZE TABLE更新统计信息再重新看执行计划。5.4 小表驱动大表是不是所有场景都适用这是MySQL优化的经典原则驱动表的结果集越小被驱动表扫描的次数就越少。但很多人把这个原则理解错了以为只要把数据量小的表放在JOIN前面就行。实际上MySQL优化器会自动评估两张表谁的过滤性更好选择成本更低的作为驱动表。如果两张表的关联字段都有索引优化器选择的顺序通常是合理的不需要你手动干预。需要手动控制驱动表的场景往往是某张表的关联字段索引失效了。比如一个查询里A表关联B表B表的关联字段因为隐式转换无法使用索引这时候就算A表数据再多B表数据再少优化器可能也会选择全表扫描B表。此时可以考虑用STRAIGHT_JOIN强制指定驱动顺序但前提是你对数据分布和执行计划有足够的把握。5.5 改了一行代码后怎么验证没有副作用这类优化上线前一定要做回归验证。我把这套验证流程整理成了清单照着做一遍基本不会出问题执行计划对比优化前后分别跑EXPLAIN确认type、rows、Extra三列的关键指标都在变好。功能回归用线上真实参数跑几次确认结果集和原来一致。特别是关联查询要注意有没有出现重复数据或者丢数据。并发压测用和线上相近的并发规模压测观察延迟和吞吐量的变化。慢日志复查上线后持续观察几天的慢查询日志确认同类SQL不再出现。监控告警确认数据库CPU、连接池使用率、接口响应时间这些核心指标观察是否有明显改善。5.6 还有一个容易被忽略的坑排序字段和LIKE查询这次优化中Using filesort还在虽然影响不大但如果遇到大数据量的排序必须重视。一个典型的例子SELECT * FROM order_info WHERE user_id 10086 ORDER BY pay_time DESC LIMIT 20;如果只有user_id单列索引MySQL会先查出所有满足条件的记录可能几千条再在内存里排序最后取20条。这种排序在小数据量下还好但一旦用户下单量巨大内存排序的消耗就会很夸张。解决方案是建联合索引(user_id, pay_time)索引天然按用户和支付时间排序查询直接顺序扫描索引即可不需要额外排序。LIKE查询也是重灾区。LIKE abc%是可以走索引的但LIKE %abc%走不了。如果业务必须用后置通配符可以看看是否能用覆盖索引来缓解压力或者干脆引入搜索引擎。6. 复盘总结这一行代码背后的优化思路回到标题本身表面上只改了一行代码但这行代码背后涉及的是一整套排查逻辑。说白了慢查询优化不是靠猜而是靠证据链慢日志发现异常EXPLAIN定位瓶颈字段对比找到根因改写SQL验证效果压测确认收益。每一步都有了明确的数据支撑最终才能有把握地说“就改这一行”。我个人做MySQL性能调优这几年最大的体会是很多慢查询不是SQL写得多烂而是表结构设计和数据类型的先天缺陷在特定查询条件下暴露了出来。这个案例里如果两张表的order_id从一开始就统一成bigint这个慢查询根本不会存在。但现实是系统经过多年迭代字段类型不统一的情况非常普遍你不可能每次都去改表结构用显式类型转换把索引救回来是最经济的做法。最后再分享一个自己养成的小习惯每次写完SQL都会顺手跑一下EXPLAIN重点看type和rows。只要发现ALL或者超大rows不管当前查询快不快我都会追一下原因。因为现在数据量小可能没问题等数据涨上去了慢查询就会像这次一样突然爆发。提前排查成本最低。 最近排查了一个让业务方半夜打来电话的慢查询语句本身不复杂两张表做个关联查询单次执行要3秒多。这种量级的慢查询在OLTP系统里已经算事故了压测的时候直接拖垮了连接池。折腾了一圈最后只改了一行代码执行时间降到50毫秒。整个过程挺典型的从现象、定位、分析到解决每一步都有值得复盘的地方。这篇文章就把完整思路和操作细节写出来遇到同类问题可以直接照着排查。我也把执行计划、优化前后的对比、以及一些容易踩的坑都整理在下面了包括为什么索引建了却用不上、为什么关联查询会全表扫描、字符集和排序规则到底怎么影响性能。如果你手头正好有类似的慢SQL建议先把EXPLAIN跑一遍再对照文章里的思路一步步来大概率能找出问题所在。1. 问题现象慢查询是怎么被发现的先说下当时的业务背景。这是一个电商类的订单查询接口前端页面要展示用户的历史订单列表后端SQL大致逻辑是查订单主表再关联用户的优惠券领取记录表目的是拿到每笔订单用了哪张优惠券、优惠了多少金额。表结构并不复杂订单表数据量在百万级优惠券领取记录表在千万级两个表都建了索引。收到监控告警的时候接口平均响应时间已经超过2秒数据库的CPU使用率也居高不下。查看了慢查询日志发现有一条SQL的执行时间稳定在2.8秒到3.2秒之间而且调用频率还不低高峰期每分钟要被调用几十次。这意味着数据库每秒钟都要分出好几个线程去处理这个查询连接池很快就被占满了。这类问题最直接的排查入口就是慢查询日志。但慢查询日志默认是关闭的需要手动开启并且要设置合理的阈值。生产环境我一般把long_query_time设为1秒也就是超过1秒的SQL都会被记录下来太小了日志量会爆炸太大了又容易漏掉真正有问题的语句。开启方式如下-- 临时开启重启MySQL后失效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 永久生效需要写入my.cnf配置文件 -- [mysqld] -- slow_query_log 1 -- slow_query_log_file /var/log/mysql/mysql-slow.log -- long_query_time 1日志里被抓到的那条慢SQL简化后大概是这样的SELECT o.order_id, o.order_amount, c.coupon_amount FROM order_info o LEFT JOIN coupon_usage c ON o.order_id c.order_id WHERE o.user_id 10086 AND o.order_status 1 ORDER BY o.pay_time DESC LIMIT 20;一眼看上去这个SQL似乎没什么大问题order_info表的user_id有索引coupon_usage表的order_id也有索引关联字段都建了索引为什么还会慢成这样带着这个疑问我开始一步步排查。提示慢查询日志记录的是SQL文本、执行时间、锁等待时间、返回行数等信息但不会直接告诉你问题出在哪需要配合EXPLAIN分析执行计划才能定位根因。2. 定位与分析EXPLAIN执行计划里的门道拿到慢SQL之后第一件事不是去改代码而是用EXPLAIN看执行计划。这就像看病先拍片子哪里出了问题一目了然。我在生产库的只读副本上执行了EXPLAIN结果如下EXPLAIN SELECT o.order_id, o.order_amount, c.coupon_amount FROM order_info o LEFT JOIN coupon_usage c ON o.order_id c.order_id WHERE o.user_id 10086 AND o.order_status 1 ORDER BY o.pay_time DESC LIMIT 20;执行计划的关键信息如下表typepossible_keyskeyrowsExtraorder_info orefidx_user_id, idx_pay_timeidx_user_id3560Using index condition; Using filesortcoupon_usage cALLidx_order_idNULL13204488Using where; Using join buffer (hash join)看到ALL这个关键字问题基本就锁定了coupon_usage表在被驱动的时候居然是全表扫描扫描行数高达1300多万。而key列显示NULL说明idx_order_id索引完全没被用上。这就像两个人联合办公左边的人效率很高右边的人却把一整栋楼的档案都翻了一遍才找到对应记录整体速度自然被拖垮。为什么索引存在却没用上这里要解释一下MySQL优化器的工作原理。EXPLAIN输出的rows字段是优化器估算的需要扫描的行数当它认为走索引的成本比全表扫描还高时就会放弃索引。但在我们这个场景里1300多万行的全表扫描成本显然更高优化器不太可能因为成本原因放弃索引。那剩下的可能性就只有索引失效了。顺着这个思路我看了下两张表的建表语句问题很快就暴露了-- order_info 表 CREATE TABLE order_info ( order_id bigint NOT NULL COMMENT 订单ID, user_id bigint NOT NULL COMMENT 用户ID, order_status tinyint NOT NULL DEFAULT 0 COMMENT 订单状态, pay_time datetime DEFAULT NULL COMMENT 支付时间, order_amount decimal(10,2) DEFAULT NULL COMMENT 订单金额, PRIMARY KEY (order_id), KEY idx_user_id (user_id), KEY idx_pay_time (pay_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci; -- coupon_usage 表 CREATE TABLE coupon_usage ( id bigint NOT NULL AUTO_INCREMENT COMMENT 自增主键, order_id varchar(32) NOT NULL COMMENT 订单ID历史原因导致字段类型不一致, coupon_id bigint NOT NULL COMMENT 优惠券ID, coupon_amount decimal(10,2) DEFAULT NULL COMMENT 优惠金额, PRIMARY KEY (id), KEY idx_order_id (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;问题就出在order_info.order_id是bigint类型而coupon_usage.order_id是varchar(32)类型。两个不同类型的字段做等值匹配MySQL会自动进行隐式类型转换把varchar转换成数值型再比较。一旦对索引列做了函数或者类型转换索引就失效了。这个原理其实不难理解。varchar类型的order_id存储的是字符串比如10086而bigint类型的order_id存储的是数字10086。MySQL做比较的时候要把字符串10086隐式转换成数字10086。转换过程是逐行进行的等于对coupon_usage.order_id列上的每一行都执行了一次CAST(order_id AS SIGNED)这和在索引列上直接写函数是一个效果——索引树的结构是基于原始字符串值建立的一旦被转换原来的索引顺序就派不上用场了。这里还有一个更隐蔽的坑即使你给varchar列建了索引只要查询条件里用了数值型和它比较索引一样会失效。因为MySQL的规则是当比较的双方一个是字符串、一个是数字时字符串会被转换成数字而不是反过来。所以哪怕你把查询条件写成ON o.order_id CAST(c.order_id AS UNSIGNED)也只会在c表上触发全表扫描因为转换操作作用在了索引列上。注意看到Using join buffer (hash join)并不一定代表MySQL走了hash join算法。这里其实是MySQL 8.0在无法使用索引做关联时退化成的一种BNLBlock Nested Loop优化本质还是扫描驱动表的大量数据。真正的问题在于被驱动表上没有可用索引。3. 核心优化动作那一行代码到底改了啥既然根因是字段类型不一致导致的隐式类型转换那最优的解法自然是统一两边的类型。理论上最彻底的做法是改表结构把coupon_usage.order_id从varchar(32)改成bigint。但生产环境千万级数据的表ALTER TABLE动辄锁表几分钟而且这个字段可能被其他系统引用改动风险太大业务方根本不会同意在高峰期做这种操作。所以我的选择是不改表结构只改SQL语句中的关联条件让转换作用在没有索引的那一侧也就是驱动表的字段上。修正后的SQL如下SELECT o.order_id, o.order_amount, c.coupon_amount FROM order_info o LEFT JOIN coupon_usage c ON o.order_id CAST(c.order_id AS UNSIGNED) WHERE o.user_id 10086 AND o.order_status 1 ORDER BY o.pay_time DESC LIMIT 20;注意这行代码的微妙之处CAST(c.order_id AS UNSIGNED)是显式地对coupon_usage.order_id做类型转换。从逻辑上看和MySQL自动执行的隐式转换是一样的但执行计划却完全不同。因为在优化器看来c.order_id被函数包裹之后索引确实无法使用了但此时它可以选择把转换后的结果应用到驱动表order_info的order_id上进行匹配而不是对1300万行的coupon_usage逐行转换。换句话说原来MySQL是把整个被驱动表拖出来硬扛现在变成了只取驱动表查询出来的那几十行结果去被驱动表里精准匹配。驱动表order_info经过user_id索引过滤后实际只剩3560行再经过order_status条件过滤可能就剩几百行。用这几百行去匹配1300万行的表每一行都走idx_order_id索引扫描行数急剧下降。改完之后再看执行计划表typekeyrowsExtraorder_info orefidx_user_id3560Using index condition; Using filesortcoupon_usage crefidx_order_id1Using wherecoupon_usage的type从ALL变成了refkey使用上了idx_order_idrows从1320万降到了1。执行时间直接从3.1秒降到了50毫秒左右效果立竿见影。不过这里要诚实地说一句这个写法虽然快但并不是最优雅的长期方案。因为CAST(c.order_id AS UNSIGNED)仍然意味着每次执行查询时都要对coupon_usage.order_id做一次转换运算虽然数据量小了之后影响可以忽略但在海量数据的场景下这仍然是一个性能隐患。最规范的做法还是找一次低峰期窗口把表结构统一了或者新建一个order_id_bigint冗余字段建上索引然后逐步迁移。实操心得如果你和我一样不能立刻改表结构但又不想在SQL里写CAST还有一个临时方案——把驱动表和被驱动表对调先用子查询取出小结果集再跟大表关联。比如写成子查询先过滤出订单再JOIN优惠券表。但在MySQL 8.0里子查询的优化已经做得不错了具体效果还是要以EXPLAIN为准。我自己更推荐显式CAST因为这个写法对优化器的提示最明确。4. 优化前后对比与相关参数验证改完SQL之后我做了三轮验证单次执行耗时、并发压测、以及慢查询日志确认。先说单次执行耗时。用SHOW PROFILE可以看SQL执行各阶段的耗时分布SET profiling 1; -- 执行慢SQL原始版本 SELECT ...; -- 执行优化后SQL SELECT ...; SHOW PROFILES;结果对比很直观版本执行耗时扫描行数额外开销原始SQL隐式转换3.126s约1320万全表扫描 hash join缓冲优化后SQL显式CAST0.048s约3560 关联命中行索引查找这里要提一个容易忽略的点Using filesort还在执行计划里不过它排序的是驱动表过滤后的几百行数据消耗几乎可以忽略。如果要彻底消除这个文件排序可以给order_info建一个(user_id, order_status, pay_time)的复合索引让索引天然有序避免排序。但在当前量级下加这个索引的收益不大反而会增加写入负担所以我没有做这一步。接着做并发压测。用压测工具模拟100个并发线程持续压测5分钟对比优化前后的吞吐量和延迟指标优化前优化后QPS每秒查询数421800平均响应时间2200ms62msP99响应时间3800ms110ms数据库CPU使用率85%22%数据差异非常明显。优化前数据库CPU几乎被打满连接池大量积压接口超时率接近8%优化后CPU占用大幅下降接口全部正常返回。这个改动对整体系统稳定性的提升比单纯解决一个慢查询意义更大。最后确认慢查询日志。优化上线后观察了48小时这条SQL再也没出现在慢日志里。监控告警也都恢复了平静。提示判断优化是否有效不能只看一句SQL的执行时间。要结合EXPLAIN中的type、rows、Extra三列综合评估。type从ALL变成refrows从千万级降到个位数Extra里不再出现Using join buffer这三个信号同时出现基本可以断定优化到位了。5. 常见问题与避坑指南遇到类似慢查询该怎么办这个案例讲完了但实际工作中慢查询的坑远不止隐式类型转换一种。我顺手整理了几个高频出现的问题和排查经验每条都是实打实踩过坑才总结出来的。5.1 为什么索引建了却不走可能有哪些原因索引失效的原因很多我捡最常见的几个说。第一就是本节案例里的隐式类型转换。但在写CAST时要注意方向一定要把函数加在驱动表字段上而不是被驱动表字段上。比如ON CAST(o.order_id AS CHAR) c.order_id和ON o.order_id CAST(c.order_id AS UNSIGNED)的优化效果差异很大。前者是对驱动表3560行做转换然后去匹配索引列后者是对被驱动表1300万行做转换索引直接失效。两者的执行计划完全不同千万不要搞反。第二是函数包裹索引列。比如WHERE DATE(pay_time) 2024-01-01优化器无法直接用pay_time索引因为需要先把每一行的pay_time都计算一遍才能比较。正确写法是范围查询WHERE pay_time 2024-01-01 00:00:00 AND pay_time 2024-01-02 00:00:00这种改写对优化器非常友好能充分利用索引的范围扫描能力。第三是前置通配符。LIKE %abc%这种写法索引也是用不上的。如果业务真的需要包含匹配建议用全文索引或者外部的搜索引擎不要指望数据库硬扛。第四是联合索引不满足最左前缀原则。比如索引是(user_id, order_status, pay_time)但查询条件是WHERE order_status 1 AND pay_time 2024-01-01跳过了user_id索引直接失效。5.2 字符集不同会导致索引失效吗怎么排查和字段类型不一致类似的还有字符集不一致的问题。两个表的关联字段一个是utf8mb4一个是latin1关联的时候MySQL也会做隐式字符集转换导致索引失效。排查方法很简单看执行计划的rows列是否异常翻倍再查两张表的CHARACTER_SET_NAMESELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME IN (order_info, coupon_usage) AND COLUMN_NAME order_id;字符集不一致的解决方案有两种一种是统一两边表的字符集把latin1表改成utf8mb4另一种是在关联条件里显式转换写法是CONVERT(c.order_id USING utf8mb4)不过还是要留意函数加在哪一侧。核心原则所有函数、类型转换、字符集转换都尽量加在驱动表一侧保住被驱动表的索引。5.3 为什么会看到Using join buffer这说明什么Using join buffer是一个非常明确的信号说明MySQL在关联查询时无法直接通过索引获取被驱动表的匹配行只能把驱动表的数据先放进内存缓冲区再去被驱动表里逐行匹配。虽然MySQL 8.0对hash join做了一些优化但本质上还是笨办法数据量一大就原形毕露。看到这个标记优先排查关联字段的索引情况。如果两个关联字段的类型、字符集完全一致还是出现Using join buffer那要考虑是不是统计信息过期了执行一下ANALYZE TABLE更新统计信息再重新看执行计划。5.4 小表驱动大表是不是所有场景都适用这是MySQL优化的经典原则驱动表的结果集越小被驱动表扫描的次数就越少。但很多人把这个原则理解错了以为只要把数据量小的表放在JOIN前面就行。实际上MySQL优化器会自动评估两张表谁的过滤性更好选择成本更低的作为驱动表。如果两张表的关联字段都有索引优化器选择的顺序通常是合理的不需要你手动干预。需要手动控制驱动表的场景往往是某张表的关联字段索引失效了。比如一个查询里A表关联B表B表的关联字段因为隐式转换无法使用索引这时候就算A表数据再多B表数据再少优化器可能也会选择全表扫描B表。此时可以考虑用STRAIGHT_JOIN强制指定驱动顺序但前提是你对数据分布和执行计划有足够的把握。5.5 改了一行代码后怎么验证没有副作用这类优化上线前一定要做回归验证。我把这套验证流程整理成了清单照着做一遍基本不会出问题执行计划对比优化前后分别跑EXPLAIN确认type、rows、Extra三列的关键指标都在变好。功能回归用线上真实参数跑几次确认结果集和原来一致。特别是关联查询要注意有没有出现重复数据或者丢数据。并发压测用和线上相近的并发规模压测观察延迟和吞吐量的变化。慢日志复查上线后持续观察几天的慢查询日志确认同类SQL不再出现。监控告警确认数据库CPU、连接池使用率、接口响应时间这些核心指标观察是否有明显改善。5.6 还有一个容易被忽略的坑排序字段和LIKE查询这次优化中Using filesort还在虽然影响不大但如果遇到大数据量的排序必须重视。一个典型的例子SELECT * FROM order_info WHERE user_id 10086 ORDER BY pay_time DESC LIMIT 20;如果只有user_id单列索引MySQL会先查出所有满足条件的记录可能几千条再在内存里排序最后取20条。这种排序在小数据量下还好但一旦用户下单量巨大内存排序的消耗就会很夸张。解决方案是建联合索引(user_id, pay_time)索引天然按用户和支付时间排序查询直接顺序扫描索引即可不需要额外排序。LIKE查询也是重灾区。LIKE abc%是可以走索引的但LIKE %abc%走不了。如果业务必须用后置通配符可以看看是否能用覆盖索引来缓解压力或者干脆引入搜索引擎。6. 复盘总结这一行代码背后的优化思路回到标题本身表面上只改了一行代码但这行代码背后涉及的是一整套排查逻辑。说白了慢查询优化不是靠猜而是靠证据链慢日志发现异常EXPLAIN定位瓶颈字段对比找到根因改写SQL验证效果压测确认收益。每一步都有了明确的数据支撑最终才能有把握地说“就改这一行”。我个人做MySQL性能调优这几年最大的体会是很多慢查询不是SQL写得多烂而是表结构设计和数据类型的先天缺陷在特定查询条件下暴露了出来。这个案例里如果两张表的order_id从一开始就统一成bigint这个慢查询根本不会存在。但现实是系统经过多年迭代字段类型不统一的情况非常普遍你不可能每次都去改表结构用显式类型转换把索引救回来是最经济的做法。最后再分享一个自己养成的小习惯每次写完SQL都会顺手跑一下EXPLAIN重点看type和rows。只要发现ALL或者超大rows不管当前查询快不快我都会追一下原因。因为现在数据量小可能没问题等数据涨上去了慢查询就会像这次一样突然爆发。提前排查成本最低。