最近在帮一个团队处理线上MySQL性能问题发现十次告警里有七次是SELECT语句拖垮了整个库。上周那个案例尤其典型一个报表接口从平均200ms直接飙到6秒数据库连接池被打满前端跟着一片超时报错。翻出慢查询日志一看罪魁祸首就是一条200万行级别的全表扫描SELECT数据量一涨执行计划就彻底崩了。这种问题在MySQL运维里太常见了所以这篇就专门聊聊SELECT语句优化的完整思路——从怎么定位慢SQL到执行计划怎么看再到索引怎么建、写法怎么改最后给一个真实线上案例的逐步优化过程。不管你是刚接手数据库的新手还是已经写了几年SQL的老开发只要你的业务跑在MySQL上这套思路都能直接用。1. 拿到慢SQL先别急着加索引先搞清楚它为什么慢处理慢SQL时我第一件事不是看SQL本身而是搞清楚这条SQL到底慢在哪一步。很多人一上来就加个索引试试这是最浪费时间的做法——SQL慢的原因可能根本不在索引而在锁等待、临时表、大事务、甚至网络往返。方向错了后面所有工作都是白费。1.1 慢查询日志和全局状态的正确用法生产环境建议长期开启慢查询日志。MySQL的慢查询日志默认是关闭的需要在配置里打开slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time的官方单位是秒我习惯在测试环境把它调到0.1秒这样能捞出更多潜在问题生产上建议1~3秒否则日志量太大一个高峰期下来能写几十GB。除了慢日志本身我还会顺手看几个全局状态值SHOW GLOBAL STATUS LIKE Select_full_join; SHOW GLOBAL STATUS LIKE Select_scan; SHOW GLOBAL STATUS LIKE Sort_merge_passes;Select_scan表示全表扫描的次数Select_full_join表示没走索引的关联查询次数。这两个值如果持续上涨说明库里存在批量性的坏SQL光修一条没用得按应用维度去排查。1.2 先区分是执行慢还是等待慢这是排查中最容易翻车的一步。一条SELECT的执行时间由两部分组成真正干活的时间 排队等待的时间。等锁、等元数据锁、等buffer pool空间都算在等待时间里。有时候SQL本身只要几十毫秒但被一个长事务锁在前面表现就是执行了5秒。我在MySQL 8.0上一般这么看SET profiling 1; SELECT * FROM orders WHERE status 0 ORDER BY created_at DESC LIMIT 20; SHOW PROFILE FOR QUERY 1;SHOW PROFILE会列出这条语句在Sending data、Sorting result、Copying to tmp table、statistics等阶段的耗时分布。如果看到大量的Waiting for table metadata lock那就是有别的长事务没提交跟SQL本身无关直接去查information_schema.innodb_trx把阻塞源处理掉问题就消失了。1.3 SELECT在事务里的位置比你想的重要这里要提一下事务视角。InnoDB的普通SELECT是快照读走MVCC不加锁。但如果你在同一个事务里先SELECT后面又执行UPDATE或SELECT FOR UPDATE事务就会拉到当前读路径事务开启时间越长undo log越难清理后面的快照读要回滚的版本链就越长实际表现就是SQL越跑越慢而且你改索引根本没有用。我踩过一个很经典的坑业务代码里一个方法被Transactional包着里面先查了20条数据做校验然后调远程接口超时等了30秒事务一直没提交之后所有查同一张表的连接都在等锁。这种问题要是不先看事务和锁光优化SELECT写法是治不好的。所以拿到慢SQL第一个要问的是这条SELECT所在的连接当前事务开了多久、有没有未提交的写操作。2. 读懂EXPLAIN执行计划纸上推演比盲目改写靠谱定位完确实是语句本身慢之后下一步就是把这条SQL的执行计划挖出来。MySQL里没有任何一个工具能像EXPLAIN这样直观地告诉你这条SQL会不会快、快在哪里、差在哪里。2.1 type、key、rows、Extra这四列最重要EXPLAIN的输出列很多select_type、table、partitions这些我基本只看一眼真正决定性能的是下面这四样列名看什么重点关注type访问类型最好到最差const/system eq_ref ref range index ALLkey优化器选中的索引如果为NULL说明没走索引rows预计扫描行数这个数字越大扫描成本越高Extra额外信息看到Using filesort、Using temporary就要警惕其中访问类型是衡量扫描范围的直接指标。const表示通过主键或唯一索引查到最多一行eq_ref表示关联查询中被驱动表每次最多读一行ref表示用普通二级索引等值匹配range表示走了范围扫描index表示扫描了整棵索引树ALL就是全表扫描。一条统计查询如果typeALL且rows1200万那无论怎么写物理上就是要扫全表除非加索引。我在EXPLAIN里最怕看到两个组合rows很大 Extra里有Using filesort或者Using temporary。这说明扫描大结果集的同时还要排序或建临时表简直是把两种最贵的操作叠在一起。2.2 Using filesort和Using temporary的背后逻辑结合mysql排序这个话题来说filesort不是磁盘排序准确说它是在内存或磁盘上为结果集额外排序。一旦出现MySQL就得把符合条件的行先捞出来再按ORDER BY字段排序。没有索引可以利用时这条成本几乎随行数线性上升。我见过一个真实案例一张500万行的表按创建时间排序查最新20条因为没有走索引每次都要把符合条件的50万行全捞出来排序慢是真的慢。Using temporary一般出现在GROUP BY、DISTINCT、UNION这类需要去重或聚合的语句里。MySQL如果判断无法通过索引直接完成分组就会先把中间结果写进内存临时表数据量超过tmp_table_size后还会落到磁盘临时表。看到Using temporary的时候我会先检查GROUP BY字段和WHERE条件字段是否在同一联合索引里这比盲目改SQL写法通常更靠谱。2.3 别对rows和filtered太当真EXPLAIN里rows是基于采样统计的估算值不是精确值。MySQL统计信息默认是从存储引擎采样得到的当数据分布变化剧烈但统计信息没更新时rows可能和真实值差出十倍以上。filtered表示经过过滤后剩余行的百分比注意它在MySQL 8.0.17以后的EXPLAIN ANALYZE里才是实际值普通EXPLAIN里依旧是估算。我一般把rows × filtered当作这条语句实际可能触碰的行数。如果500万行的表rows500万、filtered1%说明优化器认为最终只剩5万行但前提是它能找到快速过滤的路径如果它选择了全表扫描那这5万行是扫完500万之后才剩下来的代价一点没省。2.4 另一个容易被忽视的列possible_keyspossible_keys列出了优化器理论上可选的索引key是它最终实际选中的索引。当两个索引都可用时优化器会按成本模型选一个。了解这列的意义在于如果possible_keys为空说明你这张表压根没有能匹配的索引这时再怎么改SQL写法都白搭如果possible_keys有值但key是NULL说明优化器评估后认为走索引还不如全表扫描——这种情况通常是因为你建的索引区分度太低比如一个字段只有0/1两个值优化器觉得扫全表比走索引再回表更划算。3. 索引设计SELECT优化的第一生产力如果说执行计划是看病那索引设计就是开药。绝大多数SELECT性能问题最终的解法都落在索引没建对这四个字上。但索引不是越多越好设计的关键是让优化器有合适的路可走。3.1 联合索引字段顺序的决策逻辑决策逻辑核心就三句话等值条件放前面区分度高的放前面排序字段放在匹配条件后面。拿一个反例说明。一张用户订单表常见查询是SELECT * FROM orders WHERE status 1 AND user_id 123 ORDER BY created_at DESC;有人建了(status, user_id, created_at)联合索引表面看三个字段都覆盖了。但WHERE里user_id传入的更多是随机值而status的取值只有0/1/2区分度极低。把区分度低的status放最前面优化器一看这索引第一列只能过滤出三分之一的数据走索引还需要回表干脆不如全表扫。更合理的首字段是user_id因为它在查询里是等值条件而且区分度高。联合索引的顺序错了后面几个字段写得再好等于前面被WHERE条件拦截下来的范围没有缩小。3.2 最左前缀法则和它的例外联合索引的匹配规则是最左前缀如果查询条件里的列不是从联合索引最左列开始索引就用不上。所以建索引前要捋清楚常见查询里的字段组合看哪几个字段能覆盖大部分条件而不是每个查询都单独建索引。例外情况是MySQL 8.0.13开始支持函数索引以及SKIP SCAN优化——它允许优化器在某些跳过最左列的情况下使用索引但性能不如正常前缀匹配。这个功能生效条件比较苛刻我不建议把业务查询设计成依赖SKIP SCAN老老实实把最左列放进WHERE更稳定。3.3 覆盖索引让回表彻底消失回表这个词指的是二级索引找到主键后再到聚簇索引里面去取整行数据。如果查询需要的所有列都已经在索引树里MySQL就能直接返回不需要回表EXPLAIN里会显示Using index。这就是覆盖索引的效果。举个例子SELECT order_no, status FROM orders WHERE status 1;如果只有(status)单列索引MySQL用索引找到status1的叶子节点后每个节点里只有status和主键想要order_no就得再回表查一次。但如果你建了(status, order_no)联合索引叶子节点里已经带上了order_no查询可以直接从索引返回省掉大量随机IO。对于高频的小查询覆盖索引往往是投入产出比最高的一招。3.4 索引下推到底干了什么索引下推Index Condition Pushdown简称ICP是MySQL 5.6引入的优化默认开启。它的意思是把一部分WHERE条件判断提前到索引扫描阶段。以前没有ICP时索引定位到主键后要回表取行再在服务层判断条件有了ICP有些条件在索引树内部就能判断掉减少回表次数。举例说明。索引(a, b)查询WHERE a 1 AND b 2。没有ICP时先按a的范围把一堆主键捞出来回表再逐行过滤b2有ICP时MySQL在索引遍历过程中直接就判断了b2回表行数大大减少。这也是为什么建联合索引的收益经常比想象中大——不仅仅是因为排序是因为ICP放大了过滤能力。3.5 索引不是堆得多而是堆得准结合mysql创建索引多说一句。我见过最夸张的表单表挂了9个索引其中8个都是单列索引查询时优化器还经常选错。索引多了带来三个问题一是B树维护成本直线上升INSERT/UPDATE/DELETE的写放大明显二是索引占据大量存储空间buffer pool里放得下数据就放不下索引三是统计信息和优化器的决策空间变大执行计划不稳定。我在设计索引时有条粗线单表单列索引不超过4个联合索引不超过2个。而且每个索引都要能对应到具体SQL。如果一个索引在最近一个季度慢查询日志里从没被用到那就是可以砍掉的候选。先砍再跑压测看执行计划有没有变化这种瘦身对写多的业务来说是实打实的收益。4. 从SQL写法层面消除性能陷阱索引建对了很多慢SQL已经能解决。但还有一类问题是SQL写法本身让索引发挥不出来。这层问题不解决索引建得再好也是白搭。4.1 SELECT *、隐式转换和函数包裹排在第一的是SELECT *。这个坏习惯的代价有三层网络传输的数据量大连接层和buffer pool都被浪费需要回表取所有列索引覆盖失效做排序、临时表、GROUP BY时处理的字段越多内存和磁盘的消耗越大。我一直建议业务查询列只写真正用到的字段。第二是隐式类型转换。假设一个列是VARCHAR你把它跟数字比较WHERE user_id 123 -- 如果 user_id 是 varcharMySQL会尝试把列转成数字再比较这一转索引就失效了。字符集不一致也会出现隐式转换最典型的坑是两表关联时一个utf8一个utf8mb4关联字段没法直接用索引。所以不要在关联字段上混用字符集。第三是函数包裹。最常见的写法是WHERE DATE(created_at) 2024-11-20DATE函数把created_at的索引列包住了B树无法按范围检索只能全扫。改成WHERE created_at 2024-11-20 00:00:00 AND created_at 2024-11-21 00:00:00索引就能正常走起来。如果你确实天天要用日期函数查询MySQL 8.0.13以后可以建函数索引但这属于偏招能用普通列范围解决的问题尽量不要用函数索引。4.2 OR、IN、NOT IN、LIKE这些关系词的取舍OR条件有个隐藏规则MySQL只有确认OR两边都能走索引时才会用索引合并取并集否则就是全表扫描。比如WHERE status 1 OR status 2如果两个值都能用索引范围定位还行但如果一边能走索引一边不能整个条件就会被优化器降级成全扫。我一般会让业务写IN而不是多个OR改写成WHERE status IN (1, 2)执行路径更稳定。LIKE的坑大家都熟LIKE %abc和LIKE %abc%必然全扫但LIKE abc%是可以走索引的前缀匹配。业务里如果真需要中间或后缀模糊搜索更靠谱的方案是上全文索引或者外部检索系统而不是在MySQL里硬抗。NOT IN和NOT EXISTS这两个操作符也要慎用它们经常让优化器放弃索引选择全表扫描。需要排除某个集合时优先考虑LEFT JOIN IS NULL写法很多场景下执行计划明显更好。4.3 深分页优化延迟关联才是正解分页是SELECT优化里绕不开的场景。很多业务一上来就写LIMIT 100000, 20MySQL的LIMIT实现是先扫够100020行再把前100000行扔掉。翻页越深扔掉的越多消耗就越大而且这个过程还伴随着可能的filesort。延迟关联是我处理深分页最常用的招先在子查询里把符合条件的主键取出来再回原表取完整行。例如SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20 ) t ON o.id t.id子查询里因为只需要主键和排序字段走的索引树很轻扫描大偏移量的成本远低于回表拿全行。4.4 子查询改JOIN但别踩去重的坑子查询能不能改成JOIN不能一刀切。MySQL 5.7以后对IN子查询有半连接优化很多情况下子查询效率并不差。但相关子查询和派生表场景确实容易出问题外层每查一行内层子查询就执行一次变成典型的嵌套循环数据量一大就崩。改写时要留意的一个坑是去重问题。WHERE id IN (SELECT xxx FROM t2)如果改成JOINt2里出现重复记录时结果集会变大必须先GROUP BY或者用DISTINCT去重否则业务数据就错了。我见过不止一个人把子查询改成JOIN后性能变好了但对账发现数据多出来几千行。4.5 COUNT和排序优化里容易被忽略的点COUNT(*)和COUNT(字段)是有区别的。InnoDB里COUNT(*)会找最小的索引来数COUNT(字段)还要额外判断字段是否为NULL反而更慢。大表计数想要秒级要么走缓存要么用汇总表定时累加硬在线上跑COUNT对数据库压力很大。排序优化在前面索引章节提过这里再补充一句ORDER BY要利用索引排序字段必须跟WHERE条件的等值字段在同一联合索引里并且排序方向要一致。比如WHERE user_id 1 ORDER BY created_at DESC可以建(user_id, created_at)索引但如果你要ASC又要DESC8.0开始支持降序索引可以直接建(user_id, created_at DESC)。5. 事务和锁视角下的SELECT优化SELECT优化不只是索引和SQL写法的事事务和锁的影响比很多人想象中大得多。这一节我会把mysql事务处理和mysql锁的分类两个重点串起来讲。5.1 快照读与当前读普通SELECT为何不锁表InnoDB默认隔离级别是REPEATABLE READ这里普通SELECT走的是快照读。所谓快照读就是基于MVCC的版本链读取的是该事务开始时的一致性快照不加任何锁。这也是为什么很多人说MySQL的SELECT不会挡别人的SELECT普通SELECT之间完全不互相阻塞。但要注意两个例外SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE。这两个语句走的是当前读会对命中的行加上排他锁或共享锁并且会在记录之间的间隙加间隙锁这正是并发场景下死锁和锁等待的主要来源之一。如果你在代码里习惯了用FOR UPDATE去解决并发问题要非常小心它和批量更新之间的锁互斥线上经常出现一条SELECT卡死一片服务的情况。5.2 长事务为什么会拖垮SELECT普通SELECT虽然不加锁但它所在的读事务会一直持有自己的快照。REPEATABLE READ隔离级别下只要事务不结束这个快照就要保留意味着事务开始之后的旧版本undo不能被清理同时所有需要读取该行的其他事务都要沿着版本链回滚到对应时间点。事务拖得越长版本链越长每次查询的额外成本越高。真实案例一个服务方法用Transactional包住里面先SELECT接着调外部接口等30秒再UPDATE。这个事务开始到提交间隔了30秒以上期间所有针对同一行数据的SELECT都要遍历几个版本的undoSQL本身没变但是越来越慢。我的排查习惯是直接看information_schema.innodb_trx表按trx_started排序把那些只读却长时间不提交的事务找出来这个问题比索引失效隐蔽得多。5.3 锁等待的快速定位方法再回一下mysql锁的分类。InnoDB锁大致分三类Record Lock记录锁锁单行Gap Lock间隙锁锁索引记录的间隙Next-Key Lock是前两者组合锁住索引记录前面的间隙。普通SELECT不涉及这些但FOR UPDATE和写操作都会涉及。当应用超时、数据库线程堆积时我一般这样查SELECT * FROM performance_schema.data_lock_waits; SELECT * FROM sys.innodb_lock_waits;sys.innodb_lock_waits会直接给出被阻塞的事务、阻塞源事务、等待锁的SQL、阻塞事务的SQL。看到结果后关键动作是查阻塞事务的trx_started和trx_rows_modified判断它是一个写了很多行但迟迟不提交的大胃王还是一个查完忘提交的慢吞吞。大多数线上锁等待都是后者事务开着不提交前端的SELECT和更新全被卡住。这种问题靠优化SQL本身解决不了必须从应用层的事务边界下手。6. 一个完整案例从2.3秒优化到12毫秒理论讲了这么多最后分享一个我实际处理的线上案例把整套思路串一遍。这个案例比较典型包含执行计划分析、索引调整、SQL改写三层优化。6.1 业务背景与问题SQL业务是一个订单列表接口单表orders约1200万行每天新增几万条按状态和日期过滤。线上告警是接口响应变慢从平均200ms飙到2秒以上。我拿到的慢SQL简化后长这样SELECT o.id, o.order_no, u.nickname, o.status, o.amount, o.created_at FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status 0 AND DATE(o.created_at) 2024-11-20 ORDER BY o.created_at DESC LIMIT 20;一眼就能看出两个问题DATE(o.created_at)让created_at索引失效status0的过滤条件加上ORDER BY created_at顺序上很难利用索引。6.2 执行计划暴露出的根因EXPLAIN结果很直观orders表的type是ALLrows约1200万Extra里有Using where和Using filesort。两条危险信号全中。users表虽然走的是主键但orders这边就已经把全表扫了一遍关联根本救不回来。当时线上表有不少零散索引但没有一个能同时服务status过滤和created_at排序。再加上DATE函数包裹优化器连range扫描的机会都没有只能扫全表。6.3 三层优化与效果验证第一层优化是去掉函数包裹把日期条件改成范围WHERE o.status 0 AND o.created_at 2024-11-20 00:00:00 AND o.created_at 2024-11-21 00:00:00只做这一步执行时间从2.3秒降到800ms左右。因为created_at单列索引虽然能用但要先按日期过滤再回表过滤status成本还是不小。第二层是建联合索引(status, created_at)让WHERE和ORDER BY同时落在同一棵索引树上。执行时间降到280ms。这里要注意status区分度低但它是等值条件放在联合索引最前面仍然能帮优化器快速锁定范围created_at放在后面则刚好满足排序避免filesort。第三层是做覆盖索引。查询最终要返回的字段是id、order_no、amount、created_at、status而WHERE定位和排序用的是status和created_at。我建了(status, created_at, order_no, amount)把回表也省掉。执行时间最终稳定在12ms左右接口整体恢复到100ms内。优化步骤改动内容执行时间原SQL无2.3s第一步日期函数改范围条件约800ms第二步加联合索引(status, created_at)约280ms第三步覆盖索引去掉回表约12ms6.4 经验沉淀别在同一坑里摔两次这个案例最值得记住的点是三层优化的每一层都在解决不同问题。第一步是让优化器能走索引第二步是让过滤和排序共用索引第三步是消灭回表。它们不是互斥选择而是层层叠加的关系。之后你再看到一条慢SELECT就按这个顺序过一遍能不能走索引、能不能索引覆盖、能不能少回表、能不能用范围代替函数计算。每过一层用EXPLAIN和真实执行时间验证一次慢SQL的优化就没那么玄学。最后再分享一个实际操作中的小技巧线上改动索引之前我习惯先把表的统计信息刷一遍ANALYZE TABLE orders不然优化器手里的统计是过期的你建好了索引它可能还是选原来的烂计划。另外所有索引调整尽量在低峰期执行用pt-online-schema-change这类工具做在线变更避免直接ALTER长时间锁表。这套流程我跑过很多次从定位到落地再到验证基本能覆盖日常遇到的九成SELECT性能问题。