上个月帮一个项目组排查线上慢查询一条订单汇总SQL跑了12秒把监控面板刷得通红。我接手后第一件事不是改SQL而是先看执行计划结果一眼就发现type是ALL、rows扫了500万行临时表还挂着Using temporary。这类问题在MySQL里太常见了——索引没建对、Join顺序不合理、分页深度太大每个坑都能把原本该毫秒级返回的查询拖到秒级。这篇内容我把这些年做MySQL查询优化的实战经验整理成一套可复用的排查套路从执行计划解读、索引设计原则到分页改写、参数调优最后附上几个典型的故障案例。无论你是刚接触数据库的新人还是已经写过不少SQL但被性能问题折腾过的开发者这套方法论都能帮你把慢查询处理得更有条理而不是每次都在盲猜。1. 执行计划是优化的第一现场1.1 别只看EXPLAIN有没有走索引很多同学拿到一条慢SQL习惯性地跑一下EXPLAIN看到key列有值就放心了。实际上这个判断方法很容易误导人。我见过太多案例索引确实用上了但type是range甚至indexrows估算值高得离谱查询照样慢。EXPLAIN输出的每个字段都有它的意义但你至少要盯住这几个核心指标。type字段反映的是访问类型从好到差大概是system const eq_ref ref range index ALL。const和eq_ref是理想状态说明能通过主键或唯一索引精确定位到一行数据ref和range也还算不错至少在索引上做范围扫描或等值匹配一旦出现index和ALL就要高度警惕了。index代表全索引扫描虽然比全表扫描好一丢丢但如果索引列很多、数据量又大开销同样不小ALL就是全表扫描这是优化的红线。rows字段是优化器预估的扫描行数。注意它是估算值不是精确值。预估误差大的时候实际执行时间和EXPLAIN显示的结果会对不上。所以看rows要用“量级思维”它差几十行、几百行无伤大雅但量级差十倍以上就要怀疑统计信息是否过期或者SQL写法是不是误导了优化器。Extra字段更是宝藏。Using filesort表示排序没走索引需要额外的排序操作Using temporary说明有临时表参与计算大表场景下这是性能杀手Using index当然是最好的状态说明索引覆盖了所有查询列不用回表。这三个标记几乎是慢查询的“罪魁祸首指示牌”。1.2 从执行计划反推索引设计我分享一个实用的排查准则。拿到执行计划先看type和rows再看Extra这三个指标能帮你快速锁定问题方向。如果type是ALL且rows达到了几十万行那就是典型的全表扫描。这时候别急着加索引先确认这张表的WHERE条件和关联字段到底是什么。对于单表查询优先考虑在WHERE等值条件的字段上建索引如果存在排序字段再把排序字段追加到索引里构成联合索引。如果type是ref但rows依然很大说明索引选择度不够好命中了很多重复数据。比如status字段只有两个取值你在这个字段上建索引优化器大概率会放弃它。这种情况下不如考虑组合筛选条件或者在区分度更高的字段上建索引。有一个细节容易被忽略执行计划里的key_len。这个值能告诉你实际用到了联合索引的哪几列。比如你建了(a, b, c)的联合索引但执行计划里key_len只等于字段a的长度说明优化器只用了第一列。如果预期是三个条件都走索引那就要检查SQL里是不是在b或c上做了函数运算、隐式类型转换这类导致索引失效的操作。我习惯在分析执行计划时把所有关键指标记录到一个临时表格里对比优化前后的差异。比如下面这样指标优化前优化后是否达标typeALLref是rows523861128是ExtraUsing filesortUsing index是实际耗时12.8s35ms是这个方法很笨但很管用。优化不是拍脑袋完成的每个改动都要有数据支撑。你调整SQL之后把执行计划重新拉一遍对照表格就能判断改动是否真的有效。2. 索引优化从原理到落地2.1 联合索引的最左前缀原则联合索引在MySQL里的设计和使用核心就是最左前缀原则。很多人听过这个概念但实际运用的时候经常会犯错。举例说明。现在有一个订单表orders常见查询条件组合是WHERE user_id ? AND status ?并且按create_time排序。那么在(user_id, status, create_time)上建一个联合索引理论上是最优的。它的数据组织方式是先按user_id排user_id相同的按status排status也相同的再按create_time排。查询的时候从最左边的user_id开始定位一路往右推进。这带来一个约束如果你跳过了中间的列直接以create_time作为查询条件这个联合索引基本就废了。比如WHERE user_id ? AND create_time ?优化器只能用索引里的user_id部分create_time的排序信息没法利用因为它前面隔了一个status。这种情况下你可以考虑把索引重新设计为(user_id, create_time)或者调整查询条件。在设计联合索引时一个受用的原则是“把区分度高的字段放在最前面”。比如性别字段区分度太差用它做索引前缀很容易触碰到优化器选择index或者ALL的逻辑。反过来把订单号、用户ID这类区分度高的字段放在最前面每一次搜索都能快速缩小范围。2.2 回表与覆盖索引InnoDB的主键索引是聚簇索引叶子节点存的是整行数据。二级索引的叶子节点存的是主键值。走二级索引查询时如果SELECT的列在二级索引里找不到就需要根据主键回到聚簇索引去取完整行这个过程叫回表。回表本身是正常机制但如果命中行数特别多回表次数就会爆炸单条查询可能消耗几千次随机IO。解决回表问题最有效的手段就是覆盖索引。如果查询的所有列都已经包含在一个二级索引的叶子节点里优化器就不需要回表了Extra字段会显示Using index。举一个具体场景。某个订单列表页需要展示订单号order_no、用户IDuser_id和订单状态status查询条件是WHERE user_id ? ORDER BY create_time DESC LIMIT 20。如果你只建了(user_id)单列索引查询需要回表20次虽然不多但如果LIMIT 2000回表次数就上去了。这时候改成联合索引(user_id, create_time, order_no, status)排序信息直接在索引里拿到查询列又在索引里全覆盖执行计划里的Extra会变成Using index排序也不需要filesort。当然覆盖索引不是字段越多越好。索引本身也是数据维护成本不可忽略。写入频繁的表索引字段过多会明显拖慢INSERT和UPDATE。我的习惯是覆盖索引只在读多写少、且查询列稳定的场景下使用不要把整张大字段文本塞进索引。2.3 这些写法会让索引静默失效索引失效是查询优化里最让人抓狂的问题。SQL逻辑表面看没问题索引也建了但执行计划就是不走测试环境数据量小看不出来一上生产就原形毕露。我总结了几类最常见的索引失效写法对索引列使用函数。比如WHERE DATE(create_time) 2025-01-01这种写法会让create_time上的索引失效因为优化器无法通过索引树快速定位到某个函数结果。正确做法是改成范围条件WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00。隐式类型转换。索引字段是varchar类型查询条件写成WHERE phone 13800138000数字类型和字符串类型比较时MySQL会隐式地把字符串转换成数字索引同样失效。排查这类问题有个办法EXPLAIN看到type是ALL但SQL看起来找不出问题检查一下字段类型和条件值的类型是否一致。LIKE模糊查询以通配符开头。WHERE name LIKE %张%无法走索引是常识但很多人不知道WHERE name LIKE 张%其实是可以用到索引的。如果业务确实需要包含匹配建议换一个思路用全文索引或搜索引擎来解决。对索引列进行运算操作。WHERE age 1 30这种写法也会让索引失效应该把运算移到等号另一侧。OR条件连接。WHERE user_id 1 OR status 2这种写法如果OR两个条件里的字段不是同一个索引优化器很可能选择全表扫描。优先改成UNION或者拆成两条SQL。3. 典型慢查询场景拆解3.1 Join优化小表驱动大表不是唯一法则关于Join流传最广的说法是“小表驱动大表”。这个说法在绝大多数场景下是成立的但深究起来真正决定Join性能的是优化器选择的执行策略和索引是否匹配。先看一个常见场景。两张表关联查询A表2万行B表2000万行关联字段是各自的主键。小表驱动大表的意思就是先访问B表根据关联条件查找数据再用命中的主键去A表回查不对这里容易混淆。其实“驱动表”是外层循环先读取的表被驱动表的内层循环根据驱动表的关联值去查找。最理想的情况是被驱动表的关联字段有索引。比如A表驱动B表B表关联字段是主键或二级索引这样每一行数据都能通过索引快速定位。如果被驱动表的关联字段没有索引MySQL就要用Block Nested-Loop Join把驱动表的一批行数据放进Join Buffer里再和被驱动表做笛卡尔式扫描性能惨不忍睹。所以第一条准则是确保被驱动表的关联字段建了索引。第二条是应用范围条件尽量在驱动表上完成减少驱动表的扫描量。第三条是关联字段的字符集、排序规则必须一致否则索引可能失效还会触发隐式转换。-- 示例优化Join查询 SELECT u.id, u.name, o.order_no FROM users u INNER JOIN orders o ON u.id o.user_id WHERE u.status 1 AND o.create_time 2025-01-01 AND o.create_time 2025-02-01; -- 建议索引 -- users表已有主键id在status上建普通索引 -- orders表在(user_id, create_time)上建联合索引这样执行时先过滤users得到小结果集再通过orders的联合索引精准匹配能大幅减少Join需要处理的行数。3.2 ORDER BY排序查询的加速思路MySQL的排序有两种路线走索引排序和文件排序。文件排序Using filesort并不代表一定在磁盘上内存足够时是在sort buffer里完成的但不管哪种情况都不如索引排序高效。让ORDER BY走索引的套路是排序字段要符合联合索引的最左前缀并且与WHERE条件构成索引的连续覆盖。举例来说索引是(a, b, c)查询是WHERE a ? ORDER BY b这个顺序正好匹配索引排序就省了。如果WHERE a ? ORDER BY c因为跳过了b排序就无法利用索引如果WHERE a ? ORDER BY b DESC正序和倒序混用也会影响索引利用。还有一点容易踩坑ORDER BY字段的排序方向和索引定义方向不一致时MySQL 8.0之前无法利用索引排序8.0引入了降序索引后可以解决这个问题但实际维护时还要看业务场景是否频繁。分页加上排序才是真正的考验。LIMIT 1000000, 20这种深分页即使排序走了索引前面100万行数据也要全部扫描再丢掉这个浪费很大。业界通用的改善思路是把LIMIT条件改成基于主键的范围定位。3.3 深度分页的优化方案深分页优化的本质是不扫描前面无用数据直接定位到目标位置。一种常用方案是延迟关联。先用覆盖索引快速取到目标行的主键ID再把主键ID和原表做关联取完整数据。-- 优化前 SELECT id, order_no, create_time FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20; -- 优化后用延迟关联 SELECT o.id, o.order_no, o.create_time FROM ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20 ) t INNER JOIN orders o ON t.id o.id;子查询里只查主键id配合(create_time, id)联合索引能快速锁定目标行。因为二级索引比聚簇索引小得多同样扫描20万行消耗的IO要少非常多。拿到主键后再回表取完整行此时只有20行回表成本极低。另一种方案是“书签法”基于上次查询位置。前端记住上一次返回的最后一条create_time翻页时把条件带上SELECT id, order_no, create_time FROM orders WHERE status 1 AND (create_time, id) (2025-01-01 10:00:00, 12345) ORDER BY create_time DESC, id DESC LIMIT 20;这种方案跳过了OFFSET的物理扫描数据量大时性能非常稳定比延迟关联还要快。但它的场景限制是需要有排序字段的记录位置适合前后翻页的业务不适合随机跳页。3.4 子查询与关联子查询的改写MySQL处理子查询的能力在8.0版本进步很大引入了Hash Join和子查询优化手段但8.0之前很多子查询会退化成逐行执行相关子查询性能很差。一个典型场景是IN 子查询。有些人喜欢这么写SELECT * FROM product WHERE category_id IN ( SELECT category_id FROM category WHERE status 1 );8.0之前的MySQL会把它改写为EXISTS风格的关联执行如果category子查询结果集很大性能直线下降。8.0之后的优化器能在某些条件下自动做半连接优化。但为了稳妥在8.0之前建议改成显式的JOINSELECT p.* FROM product p INNER JOIN category c ON p.category_id c.category_id WHERE c.status 1;关联子查询correlated subquery更难优化。它是指子查询引用了外层查询的字段逻辑上没有错但每条外层记录都会触发一次子查询执行。如果外层查询扫描1000行子查询也要跑1000次。改成JOIN或者用窗口函数替代往往是更好的选择。还有个常见的坑是EXISTS。很多人以为EXISTS一定比IN快其实不一定。EXISTS擅长子查询结果集大、且外层结果集小的场景IN擅长子查询结果集小的时候。这两种写法在不同版本、不同数据分布下性能差异很大最佳实践是拿真实数据量做基准测试不要凭经验硬套。4. 别忽视参数和Schema层面的优化4.1 慢查询日志与pt-query-digest没有数据就没有优化方向。排查MySQL慢查询第一步棋永远是打开慢查询日志用工具把日志里的SQL做聚合分析找出真正值得优化的Top N。慢查询日志的配置比较简单-- 在MySQL配置文件中设置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time建议先设为1秒跑一段时间看整体情况再调低到0.5秒或0.2秒。设太小会让日志文件暴涨设太大又容易漏掉重要慢SQL。拿到慢查询日志后我推荐用Percona Toolkit里的pt-query-digest做聚合分析。它会自动把SQL归一化统计每条SQL在采样周期内的总耗时、平均耗时、扫描行数、出现的频率并按总耗时排序。你只需要看前10条慢SQL就能确定优化的优先级。-- 分析慢查询日志 pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt这个分析报告里有个指标很重要rows examine 和 rows sent 的比例。如果扫描了100万行才返回100行说明SQL的过滤性很差需要优先优化。4.2 innodb_buffer_pool_size怎么定InnoDB的Buffer Pool是MySQL数据在内存中的缓存区域。Buffer Pool越大热点数据被缓存的可能性就越高磁盘IO越少。innodb_buffer_pool_size的经典建议是物理内存的60%到70%但要考虑服务器上是否还跑着其他服务。如果这台机器专跑MySQL参数可以保守一点设为60%左右留出操作系统和其他进程的余地。如果内存紧张低于这个比例也正常关键是观察命中率。一个简单的判断方法-- 查看InnoDB Buffer Pool相关状态 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 (read_requests - reads) / read_requests。如果命中率低于95%Buffer Pool大概率偏小或者SQL扫描的数据量远超内存容量。但注意命中率只是一个参考值有些物理读不可避免比如全表扫描本来就不该频繁出现。还有一个容易忽略的参数是innodb_buffer_pool_instances。Buffer Pool在MySQL 5.7之后默认按实例拆分可以降低并发访问时的锁竞争。如果服务器核数多、连接数大、Buffer Pool总大小超过16G建议把实例数配置为8或16通常和CPU逻辑核数保持一定比例。4.3 连接池参数与事务开销的平衡数据库连接池的参数设置直接影响整个应用的数据库访问性能。很多人默认靠HikariCP或者Druid的推荐值但生产环境连接数开得过大或过小都会有问题。maximumPoolSize不是越大越好。每个连接在MySQL里都对应一个线程线程切换和上下文切换都要消耗CPU资源。连接数过多时CPU大部分时间浪费在线程调度上而不是执行SQL。经验值是最大连接数 ((核心线程数 * 2) 有效存储设备数)HikariCP作者也推荐类似的公式。minimumIdle建议跟maximumPoolSize保持一致避免频繁创建和销毁连接。但如果你服务在白天和晚上的负载差异极大可以考虑把空闲连接数调小降低空闲连接对内存和MySQL端TCP连接的占用。事务方面一个常见的性能隐患是长事务。很多应用把事务粒度拉得很大在处理业务逻辑的同时长时间持有连接和锁导致连接池耗尽、Undo日志膨胀。事务只应该包含真正需要原子性的最小操作集查询操作尽量放到事务外执行写操作完成之后尽快提交。5. 几个实战排查案例5.1 一条订单列表查询从12秒到30毫秒这个案例来自一个真实项目。当时的业务是订单列表页前端需要展示用户在某段时间内的订单数据SQL写法大体如下SELECT * FROM orders WHERE user_id 10086 AND create_time BETWEEN 2025-01-01 AND 2025-01-31 ORDER BY create_time DESC LIMIT 20;orders表当时的数据量在500万行左右。EXPLAIN结果里type为ALLrows预估超过400万Extra里有Using filesort查询平均耗时12秒。排查过程分三步。第一步确认索引情况发现orders表只在user_id上有一个单列索引。理论上这个SQL至少可以用上user_id的索引但执行计划显示全表扫描原因很可能是数据分布里user_id10086的数据量相对整表比例不高加上SELECT *需要回表优化器评估全表扫描更划算。第二步的调整策略是加联合索引。把索引改成(user_id, create_time)让等值条件和排序字段都在索引里。这个改动直接解决了两个问题查询范围大幅缩小排序也可以从索引中直接获取结果优化器的type变为rangerows降为8768Extra里不再出现Using filesort。第三步是覆盖索引的进一步优化。因为查询还占了order_no、status、amount等多个字段我把这几个字段全部加到索引里变成了(user_id, create_time, order_no, status, amount)。此时EXPLAIN的Extra已显示Using index说明查询完全走索引不用回表。最终这条SQL耗时降到了30毫秒左右相比原来的12秒提升接近400倍。这个案例想说明一点索引优化要做的不是“加一个索引交差”而是分析查询路径里每一次回表、每一次排序、每一次扫描把可避免的开销全部消除。5.2 一个错误的子查询改写性能反而更差有一次我在优化一条报表SQL原写法是用NOT IN子查询排除某些用户SELECT * FROM orders WHERE user_id NOT IN ( SELECT user_id FROM blacklist WHERE status 1 );我按经验改写成了LEFT JOINSELECT o.* FROM orders o LEFT JOIN blacklist b ON o.user_id b.user_id AND b.status 1 WHERE b.user_id IS NULL;结果测试之后这个改写不但没变快反而慢了3倍。原因是orders和blacklist的数据分布并不均衡优化器估算行数偏差很大。LEFT JOIN写法虽然规避了NOT IN的坑但错误的关联顺序让执行计划走了次优路径。我后来单独执行了两条SQL各自的EXPLAIN发现优化器在NOT IN版本里选择了反连接优化执行计划反而更合理而LEFT JOIN版本的执行计划里JOIN顺序错了先扫描了blacklist全表再和orders关联。这个案例给我的教训是任何优化改版必须用真实数据量验证不能靠经验空转。同一个业务数据分布变化最优SQL也可能跟着变化。优化不是“照着模板改”而是用数据说话。5.3 从线上问题看参数误配还有一个线上MySQL实例用户反馈业务高峰期接口偶发超时。检查之后发现慢查询日志几乎没有新的慢SQL那就说明问题不在SQL本身而在系统资源。进一步查看参数后发现问题出在innodb_buffer_pool_size上。这个实例分配在32G内存的机器上但配置只给了4GInnoDB Buffer Pool命中率长期在80%左右。业务高峰期时大量请求穿透到磁盘IO队列被拖到阈值附近接口时延自然上去了。调整过程并不复杂。先把innodb_buffer_pool_size从4G提到20G然后重启MySQL实例重新观察命中率与接口P95延迟。24小时后的效果Buffer Pool命中率从82%涨到99%以上接口P95延迟从3.2秒降到150毫秒左右慢查询日志里基本没有新的记录。这个案例说明一个容易被忽略的道理查询优化不只是SQL改写和索引设计底层参数和系统资源分配同样能成为瓶颈。有时候问题不在“查询有多慢”而在“数据能不能尽可能落在内存里”。6. 常见问题速查表与排查清单症状可能原因排查方向建议优化typeALLrows持续增大缺少有效索引查看WHERE列和ORDER BY列建联合索引优先覆盖WHERE等值条件ExtraUsing filesort排序未走索引检查ORDER BY字段和索引顺序调整联合索引让排序字段成为索引前缀一部分ExtraUsing temporary查询含有GROUP BY或去重临时表过大分析分组字段和查询列的索引覆盖情况让GROUP BY字段走索引或改成分批查询SQL耗时很长但EXPLAIN看着正常数据统计信息过期检查rows预估准确性和状态执行ANALYZE TABLE刷新统计信息order by字段加索引后仍Using filesort索引列顺序与查询条件不匹配检查SQL WHERE条件与ORDER BY字段组合重构联合索引字段顺序深分页LIMIT 100000, 20性能差扫描了前10万行再丢弃检查执行计划里扫描行数延迟关联或书签法重写联表查询慢被驱动表关联字段无索引查看EXPLAIN中哪个表先访问确认被驱动表关联字段有索引联表查询Extra显示BNL被驱动表不能走索引使用了Block Nested-Loop检查被驱动表的关联列类型和索引补索引或调整关联条件子查询慢子查询在大表上重复执行确认是否关联子查询子查询结果集大小改写成JOIN或临时表排查清单的另一块是“执行计划必看项”。我个人从实战中总结出的一套顺序是先看type是否有ALL再看rows预估量是否在可接受范围然后看Extra里的Using filesort和Using temporary是否出现最后用命令确认索引是不是真的存在。-- 确认一张表的索引情况 SHOW INDEX FROM orders; -- 分析一张表的统计信息是否过期 ANALYZE TABLE orders;这套流程看起来机械但能避免很多“拍脑袋调优”。每次优化结束后我会把优化前后的执行计划、耗时、扫描行数完整记录下来积累一段时间的案例库。之后再做同类优化直接对照历史记录就能快速定位问题。7. 最后分享一点实操体会做了这么多年的数据库性能优化我觉得有个核心认知要摆正MySQL查询优化不是靠某个“绝招”一劳永逸而是一套持续观察、量化、验证的循环。每一条慢SQL背后都对应了一次索引设计不合理、一次SQL写法考虑不周或者一次系统参数配置失误。很多人问我怎么快速成为优化高手我的答案很简单多做复盘。每次处理完一个线上问题把慢查询日志、执行计划、索引表结构和最终优化方案放在一起对比时间久了你对优化器行为的判断会越来越准。比如看到某条SQL的WHERE字段区分度不高你会在建索引之前就预判到它可能不会被用上看到深分页的写法你会在数据量变大之前提前重构成书签模式。工具只是辅助真正重要的是分析思路。MySQL的EXPLAIN输出、慢查询日志、性能状态变量这三大件足够你应付95%以上的性能问题。把这三个工具用熟配合今天分享的索引设计方法和SQL改写思路下一次再遇到慢查询你有底气直接定位到根因而不是盲目堆索引或者瞎猜参数。如果你在实际优化过程中遇到特别诡异的问题比如索引明明建了却不走、执行计划一直在变化、同一条SQL在测试环境和生产环境表现天差地别欢迎在留言区把EXPLAIN结果贴出来一起讨论。我自己也经常在社区里看别人的优化案例有时候一个陌生场景的排查过程能直接给你解决另一个问题提供灵感。
MySQL慢查询优化实战：从执行计划到索引设计的排查指南
上个月帮一个项目组排查线上慢查询一条订单汇总SQL跑了12秒把监控面板刷得通红。我接手后第一件事不是改SQL而是先看执行计划结果一眼就发现type是ALL、rows扫了500万行临时表还挂着Using temporary。这类问题在MySQL里太常见了——索引没建对、Join顺序不合理、分页深度太大每个坑都能把原本该毫秒级返回的查询拖到秒级。这篇内容我把这些年做MySQL查询优化的实战经验整理成一套可复用的排查套路从执行计划解读、索引设计原则到分页改写、参数调优最后附上几个典型的故障案例。无论你是刚接触数据库的新人还是已经写过不少SQL但被性能问题折腾过的开发者这套方法论都能帮你把慢查询处理得更有条理而不是每次都在盲猜。1. 执行计划是优化的第一现场1.1 别只看EXPLAIN有没有走索引很多同学拿到一条慢SQL习惯性地跑一下EXPLAIN看到key列有值就放心了。实际上这个判断方法很容易误导人。我见过太多案例索引确实用上了但type是range甚至indexrows估算值高得离谱查询照样慢。EXPLAIN输出的每个字段都有它的意义但你至少要盯住这几个核心指标。type字段反映的是访问类型从好到差大概是system const eq_ref ref range index ALL。const和eq_ref是理想状态说明能通过主键或唯一索引精确定位到一行数据ref和range也还算不错至少在索引上做范围扫描或等值匹配一旦出现index和ALL就要高度警惕了。index代表全索引扫描虽然比全表扫描好一丢丢但如果索引列很多、数据量又大开销同样不小ALL就是全表扫描这是优化的红线。rows字段是优化器预估的扫描行数。注意它是估算值不是精确值。预估误差大的时候实际执行时间和EXPLAIN显示的结果会对不上。所以看rows要用“量级思维”它差几十行、几百行无伤大雅但量级差十倍以上就要怀疑统计信息是否过期或者SQL写法是不是误导了优化器。Extra字段更是宝藏。Using filesort表示排序没走索引需要额外的排序操作Using temporary说明有临时表参与计算大表场景下这是性能杀手Using index当然是最好的状态说明索引覆盖了所有查询列不用回表。这三个标记几乎是慢查询的“罪魁祸首指示牌”。1.2 从执行计划反推索引设计我分享一个实用的排查准则。拿到执行计划先看type和rows再看Extra这三个指标能帮你快速锁定问题方向。如果type是ALL且rows达到了几十万行那就是典型的全表扫描。这时候别急着加索引先确认这张表的WHERE条件和关联字段到底是什么。对于单表查询优先考虑在WHERE等值条件的字段上建索引如果存在排序字段再把排序字段追加到索引里构成联合索引。如果type是ref但rows依然很大说明索引选择度不够好命中了很多重复数据。比如status字段只有两个取值你在这个字段上建索引优化器大概率会放弃它。这种情况下不如考虑组合筛选条件或者在区分度更高的字段上建索引。有一个细节容易被忽略执行计划里的key_len。这个值能告诉你实际用到了联合索引的哪几列。比如你建了(a, b, c)的联合索引但执行计划里key_len只等于字段a的长度说明优化器只用了第一列。如果预期是三个条件都走索引那就要检查SQL里是不是在b或c上做了函数运算、隐式类型转换这类导致索引失效的操作。我习惯在分析执行计划时把所有关键指标记录到一个临时表格里对比优化前后的差异。比如下面这样指标优化前优化后是否达标typeALLref是rows523861128是ExtraUsing filesortUsing index是实际耗时12.8s35ms是这个方法很笨但很管用。优化不是拍脑袋完成的每个改动都要有数据支撑。你调整SQL之后把执行计划重新拉一遍对照表格就能判断改动是否真的有效。2. 索引优化从原理到落地2.1 联合索引的最左前缀原则联合索引在MySQL里的设计和使用核心就是最左前缀原则。很多人听过这个概念但实际运用的时候经常会犯错。举例说明。现在有一个订单表orders常见查询条件组合是WHERE user_id ? AND status ?并且按create_time排序。那么在(user_id, status, create_time)上建一个联合索引理论上是最优的。它的数据组织方式是先按user_id排user_id相同的按status排status也相同的再按create_time排。查询的时候从最左边的user_id开始定位一路往右推进。这带来一个约束如果你跳过了中间的列直接以create_time作为查询条件这个联合索引基本就废了。比如WHERE user_id ? AND create_time ?优化器只能用索引里的user_id部分create_time的排序信息没法利用因为它前面隔了一个status。这种情况下你可以考虑把索引重新设计为(user_id, create_time)或者调整查询条件。在设计联合索引时一个受用的原则是“把区分度高的字段放在最前面”。比如性别字段区分度太差用它做索引前缀很容易触碰到优化器选择index或者ALL的逻辑。反过来把订单号、用户ID这类区分度高的字段放在最前面每一次搜索都能快速缩小范围。2.2 回表与覆盖索引InnoDB的主键索引是聚簇索引叶子节点存的是整行数据。二级索引的叶子节点存的是主键值。走二级索引查询时如果SELECT的列在二级索引里找不到就需要根据主键回到聚簇索引去取完整行这个过程叫回表。回表本身是正常机制但如果命中行数特别多回表次数就会爆炸单条查询可能消耗几千次随机IO。解决回表问题最有效的手段就是覆盖索引。如果查询的所有列都已经包含在一个二级索引的叶子节点里优化器就不需要回表了Extra字段会显示Using index。举一个具体场景。某个订单列表页需要展示订单号order_no、用户IDuser_id和订单状态status查询条件是WHERE user_id ? ORDER BY create_time DESC LIMIT 20。如果你只建了(user_id)单列索引查询需要回表20次虽然不多但如果LIMIT 2000回表次数就上去了。这时候改成联合索引(user_id, create_time, order_no, status)排序信息直接在索引里拿到查询列又在索引里全覆盖执行计划里的Extra会变成Using index排序也不需要filesort。当然覆盖索引不是字段越多越好。索引本身也是数据维护成本不可忽略。写入频繁的表索引字段过多会明显拖慢INSERT和UPDATE。我的习惯是覆盖索引只在读多写少、且查询列稳定的场景下使用不要把整张大字段文本塞进索引。2.3 这些写法会让索引静默失效索引失效是查询优化里最让人抓狂的问题。SQL逻辑表面看没问题索引也建了但执行计划就是不走测试环境数据量小看不出来一上生产就原形毕露。我总结了几类最常见的索引失效写法对索引列使用函数。比如WHERE DATE(create_time) 2025-01-01这种写法会让create_time上的索引失效因为优化器无法通过索引树快速定位到某个函数结果。正确做法是改成范围条件WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00。隐式类型转换。索引字段是varchar类型查询条件写成WHERE phone 13800138000数字类型和字符串类型比较时MySQL会隐式地把字符串转换成数字索引同样失效。排查这类问题有个办法EXPLAIN看到type是ALL但SQL看起来找不出问题检查一下字段类型和条件值的类型是否一致。LIKE模糊查询以通配符开头。WHERE name LIKE %张%无法走索引是常识但很多人不知道WHERE name LIKE 张%其实是可以用到索引的。如果业务确实需要包含匹配建议换一个思路用全文索引或搜索引擎来解决。对索引列进行运算操作。WHERE age 1 30这种写法也会让索引失效应该把运算移到等号另一侧。OR条件连接。WHERE user_id 1 OR status 2这种写法如果OR两个条件里的字段不是同一个索引优化器很可能选择全表扫描。优先改成UNION或者拆成两条SQL。3. 典型慢查询场景拆解3.1 Join优化小表驱动大表不是唯一法则关于Join流传最广的说法是“小表驱动大表”。这个说法在绝大多数场景下是成立的但深究起来真正决定Join性能的是优化器选择的执行策略和索引是否匹配。先看一个常见场景。两张表关联查询A表2万行B表2000万行关联字段是各自的主键。小表驱动大表的意思就是先访问B表根据关联条件查找数据再用命中的主键去A表回查不对这里容易混淆。其实“驱动表”是外层循环先读取的表被驱动表的内层循环根据驱动表的关联值去查找。最理想的情况是被驱动表的关联字段有索引。比如A表驱动B表B表关联字段是主键或二级索引这样每一行数据都能通过索引快速定位。如果被驱动表的关联字段没有索引MySQL就要用Block Nested-Loop Join把驱动表的一批行数据放进Join Buffer里再和被驱动表做笛卡尔式扫描性能惨不忍睹。所以第一条准则是确保被驱动表的关联字段建了索引。第二条是应用范围条件尽量在驱动表上完成减少驱动表的扫描量。第三条是关联字段的字符集、排序规则必须一致否则索引可能失效还会触发隐式转换。-- 示例优化Join查询 SELECT u.id, u.name, o.order_no FROM users u INNER JOIN orders o ON u.id o.user_id WHERE u.status 1 AND o.create_time 2025-01-01 AND o.create_time 2025-02-01; -- 建议索引 -- users表已有主键id在status上建普通索引 -- orders表在(user_id, create_time)上建联合索引这样执行时先过滤users得到小结果集再通过orders的联合索引精准匹配能大幅减少Join需要处理的行数。3.2 ORDER BY排序查询的加速思路MySQL的排序有两种路线走索引排序和文件排序。文件排序Using filesort并不代表一定在磁盘上内存足够时是在sort buffer里完成的但不管哪种情况都不如索引排序高效。让ORDER BY走索引的套路是排序字段要符合联合索引的最左前缀并且与WHERE条件构成索引的连续覆盖。举例来说索引是(a, b, c)查询是WHERE a ? ORDER BY b这个顺序正好匹配索引排序就省了。如果WHERE a ? ORDER BY c因为跳过了b排序就无法利用索引如果WHERE a ? ORDER BY b DESC正序和倒序混用也会影响索引利用。还有一点容易踩坑ORDER BY字段的排序方向和索引定义方向不一致时MySQL 8.0之前无法利用索引排序8.0引入了降序索引后可以解决这个问题但实际维护时还要看业务场景是否频繁。分页加上排序才是真正的考验。LIMIT 1000000, 20这种深分页即使排序走了索引前面100万行数据也要全部扫描再丢掉这个浪费很大。业界通用的改善思路是把LIMIT条件改成基于主键的范围定位。3.3 深度分页的优化方案深分页优化的本质是不扫描前面无用数据直接定位到目标位置。一种常用方案是延迟关联。先用覆盖索引快速取到目标行的主键ID再把主键ID和原表做关联取完整数据。-- 优化前 SELECT id, order_no, create_time FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20; -- 优化后用延迟关联 SELECT o.id, o.order_no, o.create_time FROM ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20 ) t INNER JOIN orders o ON t.id o.id;子查询里只查主键id配合(create_time, id)联合索引能快速锁定目标行。因为二级索引比聚簇索引小得多同样扫描20万行消耗的IO要少非常多。拿到主键后再回表取完整行此时只有20行回表成本极低。另一种方案是“书签法”基于上次查询位置。前端记住上一次返回的最后一条create_time翻页时把条件带上SELECT id, order_no, create_time FROM orders WHERE status 1 AND (create_time, id) (2025-01-01 10:00:00, 12345) ORDER BY create_time DESC, id DESC LIMIT 20;这种方案跳过了OFFSET的物理扫描数据量大时性能非常稳定比延迟关联还要快。但它的场景限制是需要有排序字段的记录位置适合前后翻页的业务不适合随机跳页。3.4 子查询与关联子查询的改写MySQL处理子查询的能力在8.0版本进步很大引入了Hash Join和子查询优化手段但8.0之前很多子查询会退化成逐行执行相关子查询性能很差。一个典型场景是IN 子查询。有些人喜欢这么写SELECT * FROM product WHERE category_id IN ( SELECT category_id FROM category WHERE status 1 );8.0之前的MySQL会把它改写为EXISTS风格的关联执行如果category子查询结果集很大性能直线下降。8.0之后的优化器能在某些条件下自动做半连接优化。但为了稳妥在8.0之前建议改成显式的JOINSELECT p.* FROM product p INNER JOIN category c ON p.category_id c.category_id WHERE c.status 1;关联子查询correlated subquery更难优化。它是指子查询引用了外层查询的字段逻辑上没有错但每条外层记录都会触发一次子查询执行。如果外层查询扫描1000行子查询也要跑1000次。改成JOIN或者用窗口函数替代往往是更好的选择。还有个常见的坑是EXISTS。很多人以为EXISTS一定比IN快其实不一定。EXISTS擅长子查询结果集大、且外层结果集小的场景IN擅长子查询结果集小的时候。这两种写法在不同版本、不同数据分布下性能差异很大最佳实践是拿真实数据量做基准测试不要凭经验硬套。4. 别忽视参数和Schema层面的优化4.1 慢查询日志与pt-query-digest没有数据就没有优化方向。排查MySQL慢查询第一步棋永远是打开慢查询日志用工具把日志里的SQL做聚合分析找出真正值得优化的Top N。慢查询日志的配置比较简单-- 在MySQL配置文件中设置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time建议先设为1秒跑一段时间看整体情况再调低到0.5秒或0.2秒。设太小会让日志文件暴涨设太大又容易漏掉重要慢SQL。拿到慢查询日志后我推荐用Percona Toolkit里的pt-query-digest做聚合分析。它会自动把SQL归一化统计每条SQL在采样周期内的总耗时、平均耗时、扫描行数、出现的频率并按总耗时排序。你只需要看前10条慢SQL就能确定优化的优先级。-- 分析慢查询日志 pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt这个分析报告里有个指标很重要rows examine 和 rows sent 的比例。如果扫描了100万行才返回100行说明SQL的过滤性很差需要优先优化。4.2 innodb_buffer_pool_size怎么定InnoDB的Buffer Pool是MySQL数据在内存中的缓存区域。Buffer Pool越大热点数据被缓存的可能性就越高磁盘IO越少。innodb_buffer_pool_size的经典建议是物理内存的60%到70%但要考虑服务器上是否还跑着其他服务。如果这台机器专跑MySQL参数可以保守一点设为60%左右留出操作系统和其他进程的余地。如果内存紧张低于这个比例也正常关键是观察命中率。一个简单的判断方法-- 查看InnoDB Buffer Pool相关状态 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 (read_requests - reads) / read_requests。如果命中率低于95%Buffer Pool大概率偏小或者SQL扫描的数据量远超内存容量。但注意命中率只是一个参考值有些物理读不可避免比如全表扫描本来就不该频繁出现。还有一个容易忽略的参数是innodb_buffer_pool_instances。Buffer Pool在MySQL 5.7之后默认按实例拆分可以降低并发访问时的锁竞争。如果服务器核数多、连接数大、Buffer Pool总大小超过16G建议把实例数配置为8或16通常和CPU逻辑核数保持一定比例。4.3 连接池参数与事务开销的平衡数据库连接池的参数设置直接影响整个应用的数据库访问性能。很多人默认靠HikariCP或者Druid的推荐值但生产环境连接数开得过大或过小都会有问题。maximumPoolSize不是越大越好。每个连接在MySQL里都对应一个线程线程切换和上下文切换都要消耗CPU资源。连接数过多时CPU大部分时间浪费在线程调度上而不是执行SQL。经验值是最大连接数 ((核心线程数 * 2) 有效存储设备数)HikariCP作者也推荐类似的公式。minimumIdle建议跟maximumPoolSize保持一致避免频繁创建和销毁连接。但如果你服务在白天和晚上的负载差异极大可以考虑把空闲连接数调小降低空闲连接对内存和MySQL端TCP连接的占用。事务方面一个常见的性能隐患是长事务。很多应用把事务粒度拉得很大在处理业务逻辑的同时长时间持有连接和锁导致连接池耗尽、Undo日志膨胀。事务只应该包含真正需要原子性的最小操作集查询操作尽量放到事务外执行写操作完成之后尽快提交。5. 几个实战排查案例5.1 一条订单列表查询从12秒到30毫秒这个案例来自一个真实项目。当时的业务是订单列表页前端需要展示用户在某段时间内的订单数据SQL写法大体如下SELECT * FROM orders WHERE user_id 10086 AND create_time BETWEEN 2025-01-01 AND 2025-01-31 ORDER BY create_time DESC LIMIT 20;orders表当时的数据量在500万行左右。EXPLAIN结果里type为ALLrows预估超过400万Extra里有Using filesort查询平均耗时12秒。排查过程分三步。第一步确认索引情况发现orders表只在user_id上有一个单列索引。理论上这个SQL至少可以用上user_id的索引但执行计划显示全表扫描原因很可能是数据分布里user_id10086的数据量相对整表比例不高加上SELECT *需要回表优化器评估全表扫描更划算。第二步的调整策略是加联合索引。把索引改成(user_id, create_time)让等值条件和排序字段都在索引里。这个改动直接解决了两个问题查询范围大幅缩小排序也可以从索引中直接获取结果优化器的type变为rangerows降为8768Extra里不再出现Using filesort。第三步是覆盖索引的进一步优化。因为查询还占了order_no、status、amount等多个字段我把这几个字段全部加到索引里变成了(user_id, create_time, order_no, status, amount)。此时EXPLAIN的Extra已显示Using index说明查询完全走索引不用回表。最终这条SQL耗时降到了30毫秒左右相比原来的12秒提升接近400倍。这个案例想说明一点索引优化要做的不是“加一个索引交差”而是分析查询路径里每一次回表、每一次排序、每一次扫描把可避免的开销全部消除。5.2 一个错误的子查询改写性能反而更差有一次我在优化一条报表SQL原写法是用NOT IN子查询排除某些用户SELECT * FROM orders WHERE user_id NOT IN ( SELECT user_id FROM blacklist WHERE status 1 );我按经验改写成了LEFT JOINSELECT o.* FROM orders o LEFT JOIN blacklist b ON o.user_id b.user_id AND b.status 1 WHERE b.user_id IS NULL;结果测试之后这个改写不但没变快反而慢了3倍。原因是orders和blacklist的数据分布并不均衡优化器估算行数偏差很大。LEFT JOIN写法虽然规避了NOT IN的坑但错误的关联顺序让执行计划走了次优路径。我后来单独执行了两条SQL各自的EXPLAIN发现优化器在NOT IN版本里选择了反连接优化执行计划反而更合理而LEFT JOIN版本的执行计划里JOIN顺序错了先扫描了blacklist全表再和orders关联。这个案例给我的教训是任何优化改版必须用真实数据量验证不能靠经验空转。同一个业务数据分布变化最优SQL也可能跟着变化。优化不是“照着模板改”而是用数据说话。5.3 从线上问题看参数误配还有一个线上MySQL实例用户反馈业务高峰期接口偶发超时。检查之后发现慢查询日志几乎没有新的慢SQL那就说明问题不在SQL本身而在系统资源。进一步查看参数后发现问题出在innodb_buffer_pool_size上。这个实例分配在32G内存的机器上但配置只给了4GInnoDB Buffer Pool命中率长期在80%左右。业务高峰期时大量请求穿透到磁盘IO队列被拖到阈值附近接口时延自然上去了。调整过程并不复杂。先把innodb_buffer_pool_size从4G提到20G然后重启MySQL实例重新观察命中率与接口P95延迟。24小时后的效果Buffer Pool命中率从82%涨到99%以上接口P95延迟从3.2秒降到150毫秒左右慢查询日志里基本没有新的记录。这个案例说明一个容易被忽略的道理查询优化不只是SQL改写和索引设计底层参数和系统资源分配同样能成为瓶颈。有时候问题不在“查询有多慢”而在“数据能不能尽可能落在内存里”。6. 常见问题速查表与排查清单症状可能原因排查方向建议优化typeALLrows持续增大缺少有效索引查看WHERE列和ORDER BY列建联合索引优先覆盖WHERE等值条件ExtraUsing filesort排序未走索引检查ORDER BY字段和索引顺序调整联合索引让排序字段成为索引前缀一部分ExtraUsing temporary查询含有GROUP BY或去重临时表过大分析分组字段和查询列的索引覆盖情况让GROUP BY字段走索引或改成分批查询SQL耗时很长但EXPLAIN看着正常数据统计信息过期检查rows预估准确性和状态执行ANALYZE TABLE刷新统计信息order by字段加索引后仍Using filesort索引列顺序与查询条件不匹配检查SQL WHERE条件与ORDER BY字段组合重构联合索引字段顺序深分页LIMIT 100000, 20性能差扫描了前10万行再丢弃检查执行计划里扫描行数延迟关联或书签法重写联表查询慢被驱动表关联字段无索引查看EXPLAIN中哪个表先访问确认被驱动表关联字段有索引联表查询Extra显示BNL被驱动表不能走索引使用了Block Nested-Loop检查被驱动表的关联列类型和索引补索引或调整关联条件子查询慢子查询在大表上重复执行确认是否关联子查询子查询结果集大小改写成JOIN或临时表排查清单的另一块是“执行计划必看项”。我个人从实战中总结出的一套顺序是先看type是否有ALL再看rows预估量是否在可接受范围然后看Extra里的Using filesort和Using temporary是否出现最后用命令确认索引是不是真的存在。-- 确认一张表的索引情况 SHOW INDEX FROM orders; -- 分析一张表的统计信息是否过期 ANALYZE TABLE orders;这套流程看起来机械但能避免很多“拍脑袋调优”。每次优化结束后我会把优化前后的执行计划、耗时、扫描行数完整记录下来积累一段时间的案例库。之后再做同类优化直接对照历史记录就能快速定位问题。7. 最后分享一点实操体会做了这么多年的数据库性能优化我觉得有个核心认知要摆正MySQL查询优化不是靠某个“绝招”一劳永逸而是一套持续观察、量化、验证的循环。每一条慢SQL背后都对应了一次索引设计不合理、一次SQL写法考虑不周或者一次系统参数配置失误。很多人问我怎么快速成为优化高手我的答案很简单多做复盘。每次处理完一个线上问题把慢查询日志、执行计划、索引表结构和最终优化方案放在一起对比时间久了你对优化器行为的判断会越来越准。比如看到某条SQL的WHERE字段区分度不高你会在建索引之前就预判到它可能不会被用上看到深分页的写法你会在数据量变大之前提前重构成书签模式。工具只是辅助真正重要的是分析思路。MySQL的EXPLAIN输出、慢查询日志、性能状态变量这三大件足够你应付95%以上的性能问题。把这三个工具用熟配合今天分享的索引设计方法和SQL改写思路下一次再遇到慢查询你有底气直接定位到根因而不是盲目堆索引或者瞎猜参数。如果你在实际优化过程中遇到特别诡异的问题比如索引明明建了却不走、执行计划一直在变化、同一条SQL在测试环境和生产环境表现天差地别欢迎在留言区把EXPLAIN结果贴出来一起讨论。我自己也经常在社区里看别人的优化案例有时候一个陌生场景的排查过程能直接给你解决另一个问题提供灵感。