干MySQL优化这行也有年头了说实话每次看到线上数据库CPU飙到90%以上查下来就是一个简单的SQL没写好心里都很不是滋味。很多团队平时忙着加缓存、分库分表、堆机器配置却忽略了最基础也是最见效的一件事——SQL语句优化。MySQL查询效率上不去很多时候就是一条SQL写歪了。这篇文章我结合自己多年的实战踩坑经验专门聊聊SQL语句优化这件事从整体思路、索引设计、写法细节到执行计划解读把那些能直接落地的技巧和方法都摊开来给你看。不管你是刚入门的新手还是在项目里被慢查询折磨得头疼的开发者这篇文章应该都能让你找到点方向。1. 先想清楚再动手SQL优化到底在优化什么1.1 从一次慢查询事故说起前两年在某公司做过一个电商后台的优化线上有个报表页面每次打开要十几秒运维那边监控到这条SQL把数据库服务器CPU吃满了。DBA把慢查询日志拉出来一看就是对一张几百万行的订单表做统计WHERE条件里在一个非索引字段上用了函数再配合一个全表扫描。就这么一条SQL把整个业务库拖垮了其他正常的查询也跟着被堵住。这种案例我见过太多次了。大多数SQL性能问题本质就是一个逻辑读取了太多用不上的数据。数据库本身不聪明你让它怎么查它就怎么查它不会自动帮你把条件里的函数去掉也不会帮你选择合适的索引。SQL优化的本质就是通过各种手段减少数据库扫描的数据量减少它不必要的计算和排序让它用最少的IO和CPU去把数据拿出来。1.2 优化的两条主线结构优化和写法优化我做SQL优化习惯把工作分成两条线。一条是结构层面的优化核心就是索引的设计和表结构的设计。索引就像书的目录没有目录就只能一页一页翻有了目录才能直接翻到对应页码。很多SQL慢慢在连索引都没有或者索引建了但没被用上这就引出了第二条线。另一条是写法层面的优化也就是SQL语句本身的写法。同一个查询需求写法不同性能差距可能是几十倍甚至上百倍。举个最简单的例子两张表JOIN小表驱动大表和反过来写法是有讲究的再比如分页查询翻到第100万页的时候还硬要用LIMIT去翻那就是让数据库白干大量活。写法优化就是在告诉数据库你其实可以更聪明一点。这两条线不是独立的。写法再好没有索引支撑也是白搭索引建得再漂亮SQL写法把它绕过去了照样全表扫描。只有把两条线拧在一起才能真正把查询效率提上去。1.3 在动手之前要先确认的事情在动任何SQL之前我建议你先确认几件事不然容易白忙活。先确认慢查询到底慢在哪。是查询本身耗时高还是因为锁等待、磁盘IO抖动造成的假象用SHOW PROFILE或者performance_schema看一眼时间分布确认时间花在了CPU计算上还是花在了等待上方向不对后面全是白费功夫。再确认数据量和数据分布。对于一个几百行的配置表SQL优化根本没有任何意义怎么查都快。但一张上千万行的流水表连写法和索引都要抠细节。还有一个容易忽略的点字段的选择性。在一个性别字段上建索引基本没什么用因为它只有两个值区分度太低优化器算一下发现走索引比全表扫描还慢就直接把索引忽略了。最后要确认业务对查询时延的真实要求。有些报表查询跑个几秒钟是可以接受的有些接口查询超过500毫秒用户就走光了。目标不一样优化的策略和投入的精力也不一样。我见过不少团队为了把一个本来就不需要很快的统计查询优化到十几毫秒耗费了大量时间其实没有必要。2. 索引是SQL优化的命脉选型与避坑2.1 索引不是随便加的三类场景的取舍很多人一说到SQL优化第一反应就是加索引。这话没毛病但索引怎么加加在哪里背后是有章法的。我通常把索引的应用场景分成三类。第一类是高频查询条件字段。经常出现在WHERE、JOIN、ORDER BY里的字段是索引的首选。比如订单表的用户ID、商品表的状态字段这些字段是业务查询的入口没有索引就只能从头扫到尾。但是这里要留个心眼如果字段的值几乎都一样比如一个订单表里98%的数据都是已完成状态剩下2%是待支付你在状态字段上建索引就没多大意思。优化器算一下代价大概率还是全表扫描划算。第二类是组合索引。业务查询很少只用一个条件通常都是多条件组合。比如查某个用户在某段时间内的订单这个时候单个索引就不够用了需要建立组合索引。组合索引有一条铁律叫最左前缀原则查询条件必须从索引最左边的字段开始才能用上这个索引。我建组合索引时有一个习惯性思路把等值查询的字段放在前面把范围查询的字段放在后面。因为范围查询比如BETWEEN、大于小于一旦出现它后面的字段就再也无法走索引了这个细节后面会细说。第三类是覆盖索引。这个概念值得每个做SQL优化的人重视。所谓覆盖索引就是索引里面已经包含了查询需要的所有字段MySQL查完索引就直接把数据返回了不需要再回表查一遍原始数据行。这个性能提升是巨大的因为回表意味着一次随机IO而随机IO比顺序IO慢得多。2.2 索引失效的六种常见姿势索引建好了SQL也写了结果一看执行计划还是全表扫描。这种情况我遇到太多次了排查下来十有八九是踩了索引失效的坑。我把最常见的失效场景整理一下这些都是我实际项目中踩过的。对索引列使用函数或表达式。这是最隐蔽的坑。比如你在订单表的创建时间字段create_time上建了索引写SQL时用WHERE DATE(create_time) 2024-01-01这个索引就废了因为MySQL要先对每一行的create_time做DATE运算才能拿去和后面的值比较运算完的结果和索引里的原始值完全对不上。正确写法是改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02这样索引才能正常使用。隐式类型转换。这个坑特别常见。比如表的user_id字段是varchar类型你写SQL时传了一个数字进来WHERE user_id 123MySQL会把字段转换成数字再比较相当于对索引列做了函数操作索引直接失效。而且这种转换在数据量大时还可能造成结果不准确。我的判断习惯是写SQL之前先确认字段的字段类型尤其是从接口或者前端接收参数时一定要统一类型。前置模糊查询。WHERE name LIKE %张这种写法索引是没法用的因为MySQL不知道你要匹配的字符串从哪里开始只能把所有行的name字段都拿出来比一遍。后置模糊LIKE 张%是可以走索引的我的经验是如果确实需要前置模糊查询可以在搜索引擎或者专门的关键词表里做方案别在核心业务表上硬扛。组合索引不满足最左前缀。建了一个(user_id, status)的组合索引但查询里只有WHERE status 1没用上user_id这个索引就浪费了。写SQL之前心里要有数你的查询条件到底覆盖了哪几列顺序对不对。OR条件连接非索引字段。WHERE user_id 1 OR status 1只要其中一个字段没有索引整个查询就可能放弃索引走全表扫描。优化的办法是拆成两个查询用UNION ALL合并或者让OR两侧都走索引。我一直认为能写UNION ALL就别用OR代价更可控。范围查询后面的字段。组合索引(a, b, c)查询WHERE a 1 AND b 10 AND c 1这个索引只能用到a和bc就用不上了。因为b的范围查询一出现后面的字段顺序就乱了索引无法定位c的位置。这个在设计组合索引时就要根据业务想清楚哪些字段必须做范围哪些字段必须做等值。2.3 覆盖索引最容易吃到的性能红利覆盖索引是我最推荐的性能优化手段因为很多时候改动很小效果却很猛。所谓覆盖就是指查询的字段全都在索引里。比如有一个组合索引(user_id, create_time)当SQL写成SELECT user_id, create_time FROM orders WHERE user_id 123时MySQL在索引里就把数据找齐了根本不需要回表。我在某个项目里优化过一个高频接口业务场景是查用户最近一笔订单的时间。原本的SQL在订单表上查询要回表拿很多不需要的字段响应耗时在800毫秒左右。后来我把SQL改成只查询需要的两个字段并为此建立了一个覆盖索引查询耗时直接降到了40毫秒。这个操作没有改任何业务逻辑只改了SQL的字段列表和索引设计收益却大得惊人。所以我在写SQL时有一条基本原则你别一上来就SELECT *这个习惯不仅是浪费网络传输更重要的是它彻底扼杀了覆盖索引的可能性**。SELECT *会让数据库必须回表去取所有字段索引覆盖的效果就完全不存在了。3. 更聪明的写法让优化器少走弯路3.1 WHERE条件怎么写更高效很多SQL性能问题出在WHERE条件的写法上倒不是说写错了而是写法让优化器没了选择空间。先说一个基础但常被忽略的能用等值就不要用范围。等值条件对优化器来说定位最精准范围条件好歹还有区间要扫描。所以写SQL的时候要动脑子比如查某个用户某个时间段的数据可以把时间范围压缩到业务上真实需要的最小范围而不是习惯性写成create_time 2024-01-01这种大区间。再说NULL值的处理。WHERE name IS NOT NULL这种写法即使name上有索引MySQL也未必会走索引。因为索引在设计上对NULL值的处理比较特殊IS NULL在某些存储引擎和索引类型下没法走索引。更恶心的是有些人在业务字段上允许了NULL查出来的结果里一堆NULL根本没法建索引。我的建议是建表时尽量把字段设置成NOT NULL并给默认值别让NULL成为SQL优化的绊脚石。还有一点容易被忽略WHERE条件里的字段顺序。虽然优化器通常会自己调整条件顺序但复杂的查询里优化器的判断不一定最优。我的习惯是手动把选择性最高的条件放在最前面人为给优化器一个引导信号实测在很多复杂联表查询里是有帮助的。3.2 JOIN与子查询的选择联表查询慢大多数情况是没搞清楚驱动表和被驱动表的关系。MySQL在执行JOIN时会选一张表作为驱动表先查驱动表的数据然后拿着结果逐条去匹配被驱动表。我的原则是小表驱动大表用数据量小的表做驱动表大表做被驱动表并且确保被驱动表的JOIN字段上有索引。在这个原则下LEFT JOIN的写法和优化器选择也要留意。左连接左表是驱动表如果左表数据量远大于右表而且右表JOIN字段没有索引这个查询会非常慢。我在优化时有个习惯遇到LEFT JOIN慢先看右表的JOIN字段有没有索引没有就加上往往马上就见效果。子查询的问题就更多了。MySQL的优化器对子查询的处理方式不断完善但有些场景还是会出现性能坑。特别是IN子查询如果子查询返回的结果集特别大MySQL可能要把子查询结果物化成临时表再和外部表做关联。我一般建议优先把子查询改写成JOIN形式因为JOIN更容易让优化器找到合适的执行路径。但这里要补一句不是所有情况下JOIN都比子查询快关键还要看数据量和索引。我曾经优化过一个SQLEXISTS子查询比JOIN版本快了一倍原因是子查询可以提前终止匹配而JOIN需要把两边的数据全部关联完才知道结果。所以我的做法是写代码时多尝试几种写法用实际执行计划来做判断不要凭感觉。3.3 ORDER BY和LIMIT的优化空间排序是SQL中成本极高的操作之一。MySQL如果没法直接利用索引的顺序来返回数据就会把结果集扔到内存或磁盘上进行filesort。ORDER BY字段上有索引而且排序方向和索引方向一致就能省掉这个步骤。这一点在设计组合索引时就要考虑进去比如索引(user_id, create_time)查询WHERE user_id 1 ORDER BY create_time就可以直接利用索引顺序输出不需要额外排序。LIMIT深分页是个经典难题。比如分页查询翻到10000页SQL是LIMIT 990000, 20MySQL要把前面99万行全部扫过然后丢弃这个浪费非常恐怖。我优化过几个项目深分页问题拉低了整个列表接口的性能。解决方案有两个思路。第一个思路叫延迟关联。先用JOIN或者子查询在覆盖索引上定位出需要的ID然后再回表取完整数据。举个例子SELECT a.* FROM orders a JOIN (SELECT id FROM orders WHERE user_id 123 ORDER BY create_time DESC LIMIT 100000, 20) b ON a.id b.id;子查询只查ID而且是走索引的回表取数据的行数已经被限制在一个很小的范围内性能提升非常明显。第二个思路是游标分页也就是不传页码而是传上次查询返回的最后一个ID用条件WHERE id 上次的最大id去取下一页。这种方式在数据量达到百万级别时优势极其明显缺点是前端逻辑要做调整不能随便跳页。如果业务上可以接受这往往是最优解。还有一个细节想提一下LIMIT最大偏移量不能无限大很多框架和过期代码直接把前端传的page和size拼到SQL里如果有人把page传成99999你整个库都可能被拖垮。在业务层做参数校验限制最大翻页深度是对数据库的一种保护。3.4 避免隐式类型转换和一些常见浪费前面提到隐式类型转换会导致索引失效这里我再说一个容易忽视的场景字符集不同导致的隐式转换。两张表JOIN一张表字段字符集是utf8mb4另一张是utf8MySQL在比对时会强行把一侧的字段转换成另一个字符集这个转换操作又会把索引搞失效。我在搭建表结构时会把所有表的字符集统一成utf8mb4虽然存储空间会大一些但避免了大量跨表JOIN的性能坑。还有一个常见浪费是COUNT的写法。很多人查总数喜欢用COUNT(某个字段)但如果这个字段允许NULL结果可能不符合预期而且执行效率也不高。我的习惯是统计行数用COUNT(*)在InnoDB引擎下它和COUNT(主键)是一样的效果都是走索引统计不会去数具体的值。如果要查的是某个字段非空的个数再用COUNT(字段)这时要清楚它统计的是非NULL的行数。另外UPDATE和DELETE也要走索引。很多人的优化意识集中在SELECT上但大量的UPDATE和DELETE语句如果WHERE条件没有索引一样会全表扫描还可能导致锁范围变大把并发的读操作全部堵住。我处理过不少线上死锁问题归根结底就是一条UPDATE没走索引把几百行无辜的数据全锁住了。所以写UPDATE和DELETE之前先看一眼执行计划确认它走的是索引而不是全表扫描这个习惯能帮你避开很多雷。4. EXPLAIN执行计划把每一步看穿4.1 关键字段怎么看EXPLAIN是SQL优化的第一工具不看执行计划就改SQL基本等同于闭眼开车。我每拿到一条慢SQL第一步永远是EXPLAIN SELECT ...然后一行行看输出。这里我把最关键的几个字段挨个讲一下。type字段是执行方式的核心。从好到差通常是system const eq_ref ref range index ALL。system和const表示这表只有一行或通过主键等值查到一行非常快。eq_ref通常出现在主键或唯一索引的JOIN场景。ref是普通索引等值匹配性能也不错。range是索引范围扫描也可以接受。index表示扫了整个索引树也就是索引全扫描比全表扫描好点但也不理想。ALL就是全表扫描这是最要命的情况。我优化的目标通常是把type从ALL至少提升到range或ref。key字段告诉你实际用了哪个索引。有时候你建了索引但key是NULL那就说明索引没被用上需要回头看是不是索引失效场景。如果key有值还要对比一下Extra字段。Extra里有几个标记我要重点看。Using filesort说明要额外排序往往伴随ORDER BY没有利用索引这是优化点。Using temporary说明要用临时表常见于GROUP BY或者去重场景性能消耗很大要重点关注。Using index这是好信号表示覆盖索引生效了不需要回表。Using index condition表示索引条件下推ICP被使用也就是说部分WHERE条件在索引层就被过滤掉了这也是好事。rows字段是预估的需要扫描的行数。这个数字是我最关注的它基本能反映这条SQL的代价。rows越小越好一个百万级的表如果rows显示有几十万一定要警惕。Extra和rows要配合看比如Using index加小rows说明这条SQL已经被榨得很干了如果是ALL加Using filesort那基本可以判定这条SQL是全表扫描加额外排序双重性能损耗。4.2 实操一次慢查询的分析过程我拿一个真实的排查过程当例子带你走一遍完整分析链路。有个业务方反馈某个接口变慢了我把慢查询日志捞出来是一条订单查询SQLSELECT id, order_no, user_id, amount, status, create_time FROM orders WHERE status 1 AND create_time 2024-06-01 ORDER BY create_time DESC LIMIT 100;EXPLAIN的结果type是ALLkey是NULLrows是200多万Extra里还有Using filesort。这说明MySQL在扫全表把所有行捞出来过滤后还要自己做排序能不慢吗再看表结构create_time上有单独的索引status上有单独的索引但WHERE里同时出现了两个条件而且查询字段没有一个在索引里MySQL就没法选出一个足够好的索引来走。我当时的优化步骤是这样先建一个组合索引(status, create_time)。为什么把status放前面因为它是等值条件create_time是范围条件等值在前、范围在后。但这里有个细节Extra里还是可能出Using filesort因为ORDER BY create_time的排序方向和索引顺序关联性在范围匹配后不好保证。于是我把SQL改成延迟关联的写法SELECT a.id, a.order_no, a.user_id, a.amount, a.status, a.create_time FROM orders a JOIN ( SELECT id FROM orders WHERE status 1 AND create_time 2024-06-01 ORDER BY create_time DESC LIMIT 100 ) b ON a.id b.id;子查询里只查ID所以只需要回表100条外层再根据ID批量取出完整数据。并且为了给子查询支撑我把索引做成了(status, create_time)覆盖ID的查询。改完之后EXPLAIN显示子查询type是rangekey用上了新索引Extra里Using index conditionrows从200万降到了几万。整体查询耗时从2.3秒降到了30毫秒左右。这个案例就是一个标准流程捞慢日志、EXPLAIN定位、分析索引、改写法、对比执行计划。5. 常见问题速查表与避坑心得5.1 排查SQL性能问题的快速清单我在长期优化中攒了一张速查清单每次遇到慢查询都会按这个顺序过一遍效率很高整理出来分享给你。排查点判断方法常见解决办法是否全表扫描EXPLAIN看type是否ALL建合适的索引优化WHERE条件索引是否失效看key是否NULL结合WHERE写法去掉函数包裹、隐式转换、前置模糊是否额外排序Extra看Using filesort排序字段加入索引或调整查询字段是否临时表Extra看Using temporary优化GROUP BY/去重逻辑拆SQL是否深分页LIMIT偏移量是否巨大延迟关联或游标分页是否大字段拖累查询字段是否过多改成覆盖索引支持的字段组合JOIN字段是否有索引被驱动表的JOIN字段补索引调整驱动表顺序字符集是否统一JOIN字段的字符集比对统一表字符集UPDATE/DELETE是否扫全表同样EXPLAINWHERE条件加索引避免锁扩大5.2 一些实际工作中的体会SQL优化做到最后拼的不是对某个技巧的记忆而是对数据访问路径的判断力。我踩过太多坑之后最大的体会就是不到万不得已不要用SELECT *。哪怕你只需要一个字段你把所有字段都查出来意味着回表是必然的覆盖索引直接废掉。很多人觉得写全字段方便但后面每一次性能排查都要为这个习惯买单。第二个体会是SQL优化要围绕执行计划来验证而不是靠经验主义。同一个查询在这个数据分布下走索引更快换了一个数据分布可能全表扫描更快。我曾经遇到过一张表只有几万行的时候加索引反而让查询变慢因为优化器计算后发现全表扫描更省。所以每次改动之后跑一下EXPLAIN看一下实际数据量下的执行计划比什么都靠谱。最后再说个小技巧慢查询日志要开着。long_query_time设置成0.5秒甚至更低定期看一看你会发现很多潜在的SQL问题都被提前暴露了。我有一次就是在慢日志里发现一个平时跑得不慢、数据量涨上去就暴雷的SQL提前优化掉了后来数据量翻倍也没出问题。SQL优化是个细水长流的活一次优化解决了当前问题不代表以后数据量涨了、查询模式变了还会继续高效。保持良好的写作习惯、让每条SQL都走索引、定期回顾执行计划这三件事坚持做下来你的MySQL查询效率会稳定在一个让人省心的水位线上。