凌晨两点多手机跟闹钟似的震个不停。打开告警群一看不出所料某业务系统的慢查询又爆了一条统计报表SQL在千万级订单表上跑了十几秒。我翻出慢查询日志顺着日志里的SQL往下看越看越来气——这哪是什么疑难杂症分明是好几处教科书级别的“错误示范”叠在一起了。做数据库优化这些年我最深的一个体会是大部分线上SQL性能问题不是数据库不行也不是机器不够而是写法本身把路堵死了。这次我整理了8种最典型的、几乎所有人都在不同项目里踩过的SQL写法。它们单看语法全都没问题结果也正确但数据量一旦上去执行计划就会从“走索引”退化到“全表扫描”性能从毫秒级掉到秒级夸张的时候百倍差距轻轻松松。这篇文章就逐个拆解这8种写法讲原理、给对比、附优化方案顺便聊聊我平时是怎么用EXPLAIN快速锁定这类问题的。1. 先搞清楚一条SQL到底慢在哪里1.1 数据库究竟是怎么执行一条SQL的一条SQL从发出去到拿到结果大致要经过解析、优化、执行、返回四个阶段。解析负责检查语法和对象是否存在优化器负责制定执行计划执行器按照执行计划去访问数据最后把结果集返回给客户端。我们平时说的“慢SQL”绝大多数问题出现在“执行”这一步——优化器选了一条低效的访问路径。你可以把数据库查数据类比成在字典里找词。知道首字母直接翻到对应的字母段一眼就能扫到目标如果什么信息都没有就只能从第一页开始一页一页翻。索引就是字典的目录全表扫描就是从头翻到尾。优化的本质就是让数据库尽量走目录而不是从头翻。这里有一个容易被忽略的点优化器并不是万能的。它生成执行计划时依赖表的统计信息、索引信息、数据分布一旦统计信息过期或者SQL写法让优化器认为“全表扫更划算”它就会放弃索引。很多性能问题的根源其实就是我们写出来的SQL让优化器误判了。1.2 用EXPLAIN看清SQL的“体检报告”排查慢SQL第一步永远是看执行计划。MySQL里一条命令搞定EXPLAIN SELECT * FROM orders WHERE user_id 10086;执行计划返回的字段很多我建议你重点盯四个字段含义到底怎么看type访问类型从好到差大致是system const eq_ref ref range index ALLkey实际使用的索引如果值是NULL说明这条SQL根本没吃到索引红利rows预估扫描的行数这个数字越小越好它直接决定查询的“体力消耗”Extra额外信息出现Using filesort或Using temporary要警惕出现Using index则是好事举个例子。假设一张订单表有800万行执行计划的输出是type: ALLkey: NULLrows: 8000000光看这三个字段你已经知道这条SQL把整张表从头到尾扫了一遍。哪怕业务只需要1条记录数据库也把800万行全部过了一遍不慢才怪。我见过太多同学看一眼结果没错就直接上线从来不看执行计划结果就是把雷埋到生产环境里。2. 第一类坑让索引完全失效的写法2.1 在索引列上套函数或表达式这类写法在报表统计里最泛滥典型长这样SELECT * FROM orders WHERE DATE(create_time) 2025-01-01;看起来没什么毛病条件就是“查询2025年1月1日当天的订单”。但如果你在create_time上建了索引这个查询依然不会走索引。原因很简单索引里存的是create_time的原始值比如2025-01-01 14:30:00这种完整的时间戳而你的条件是“日期等于某一天”。数据库没办法把DATE(create_time)这个计算结果塞进索引树去二分查找只能把每一行的create_time都取出来先算一次DATE()再和条件比对。索引的组织结构完全帮不上忙优化器只能选择全表扫描。正确写法是把函数去掉改写为范围条件SELECT * FROM orders WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00;我实际测试过一张800万行的订单表第一种写法跑了4.8秒第二种写法跑了0.03秒差距超过两个数量级。标题说性能降低100倍真不是段子。注意不只是DATE()在索引列上做任何运算都有同样问题。常见的有WHERE price * 1.1 100、WHERE age 5 30。解决问题的原则很简单索引列裸着放左边参数放右边别给它裹一层函数。2.2 隐式类型转换无声无息的全表扫描再看这个SELECT * FROM users WHERE phone 13800138000;phone在表里是varchar(11)等号右边却是整数。你可能会觉得MySQL会自动把数字转成字符串来比较不影响结果。但真相是MySQL会把字段值转成数字再比较也就是说它相当于在phone列上执行了一次CAST(phone AS UNSIGNED)。这一转索引就废了因为索引里存的是字符串原始值没法直接用数字去匹配。正确写法只有一个就是让两边类型一致SELECT * FROM users WHERE phone 13800138000;这个坑最阴的地方在于数据量小的时候几乎察觉不到。几百条数据的表全表扫描也就几毫秒你根本感觉不出来。一旦到了百万级甚至千万级关联查询里的隐式类型转换会让执行时间成倍增长。我曾经排查过一个每天凌晨跑3分钟的统计任务最后发现是两张表的关联字段一个是int、一个是varchar导致连接时一边全表扫描。把字段类型统一之后执行时间直接降到20秒。2.3 前导通配符LIKE模糊查询里的隐形杀手SELECT * FROM articles WHERE title LIKE %数据库优化%;这种模糊搜索的需求很常见但这也是最典型的索引失效写法。MySQL的B树索引支持最左前缀匹配LIKE 关键字%是可以走索引的因为数据库知道左边界是什么。但LIKE %关键字%在字符串头部放了通配符数据库无法确定匹配的起点只能把索引整个扫一遍。放在大表上这就是全表扫描。如果业务实在需要中间匹配仅靠数据库索引很难根治。有两条路可以走第一数据量大、并发高的场景引入全文检索引擎比如Elasticsearch把搜索能力从数据库剥离出去。第二数据量可控的场景接受全表扫描但必须加严约束比如限定时间范围、加LIMIT控制单次的扫描成本。另外有一个小技巧如果需要查询的字段很少可以先只查索引字段。比如SELECT id FROM articles WHERE title LIKE %数据库优化%;如果id和title恰好在一个覆盖索引里数据库可以只扫索引而不用回表代价会低一些。但别指望靠这个根治它只是把灾难从“极慢”缓解到“慢”本质上的扫描动作并没有消失。3. 第二类坑看着省事实则数据量爆炸3.1 SELECT * 带来的连锁问题SELECT *大概是数据库世界里最常见的写法了说它是最坑的写法之一很多人不服我就是偷个懒把所有字段都查出来有什么错在数据量小的时候确实没什么错但到了线上大表问题就来了。第一不必要的字段传输。假设一条订单记录有商家备注、用户备注、扩展JSON等字段整行可能500字节而你的业务只需要订单号、商品ID、数量这三个字段总共不到100字节。查询1万条记录SELECT *要多传输5MB量级的数据网络带宽和内存都被白白消耗。第二无法使用覆盖索引。如果有一个辅助索引恰好包含了业务需要的字段查询可以只扫索引不回表效率极高。但SELECT *要求完整行数据辅助索引满足不了必须回表查聚簇索引IO次数成倍增加。第三多表关联时更严重。两张大表JOIN后使用SELECT *中间结果集体积大产生临时表的概率和临时表的尺寸都会上升。优化方案不需要多说了就是把SELECT *改成明确的字段列表。写代码的时候多打几个字段名比线上多花几秒查一次舒服多了。3.2 深分页LIMIT的隐藏成本翻页查询是业务标配但很多人的翻页写法是这样的SELECT * FROM comments WHERE video_id 9527 ORDER BY id LIMIT 100000, 20;用户翻到第5000页数据库并不是直接定位到第100000条然后往后取20条。MySQL的做法是先扫描前面全部100000行然后把它们全部丢掉只保留最后20行返回给你。这个“扫描前面全部”的动作既包含索引遍历也包含大量的回表操作。如果业务场景里每行数据1KB前10万条就是100MB的数据量。就算最后只返回20条该做的IO一点儿没省。优化方案有两个方向。方案一叫延迟关联核心是先从索引里快速捞出20条记录的主键ID再拿这20个ID去回表查完整数据SELECT c.* FROM comments c JOIN ( SELECT id FROM comments WHERE video_id 9527 ORDER BY id LIMIT 100000, 20 ) t ON c.id t.id;子查询里只返回主键不需要大量回表扫描成本大幅下降。方案二更彻底改成游标分页。前端记住上一页最后一条记录的ID下一页直接从这个ID往后取SELECT * FROM comments WHERE video_id 9527 AND id 100000 ORDER BY id LIMIT 20;这种写法只要id上有索引性能几乎恒定不管翻到第几页都一样快。缺点是它要求业务必须能按照主键顺序翻页不支持跳页。能用的场景尽量用这是深分页最省事的解法。3.3 多个OR条件让优化器无从下手OR条件也是索引失效的高频原因。比如SELECT * FROM order_list WHERE status 1 OR pay_type 2;status和pay_type上分别有索引但OR把两个条件连接在一起后优化器就很难决定先走哪个索引。除非它选择“索引合并”否则最稳妥的策略就是全表扫。你会发现一个很有意思的现象单独执行WHERE status 1很快再单独执行WHERE pay_type 2也很快放在一起就变得巨慢原因就在这里。改法之一是拆成两条查询再用UNION ALL合并SELECT * FROM order_list WHERE status 1 UNION ALL SELECT * FROM order_list WHERE pay_type 2 AND status 1;注意这里我特意写了status 1是为了防止两条查询结果重叠避免业务数据重复。同时要强调用UNION ALL而不是UNION因为UNION会多做一次去重排序这是在本来就不快的查询上雪上加霜。改法之二更简单。如果OR链接的都是同一个字段比如WHERE status 1 OR status 2直接写成WHERE status IN (1, 2)索引就能正常命中。很多时候把OR换成IN一条SQL就活过来了。4. 第三类坑连接查询与子查询里隐藏的灾难4.1 用WHERE IN (SELECT ...) 替代JOIN业务上经常有这种需求找出所有下过单的用户。很多同学习惯写成SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status 1);这行SQL从逻辑上完全正确但性能要看优化器的“心情”。在某些MySQL版本中优化器会把子查询的结果物化成一张临时表可能还会自动建索引但在另一些版本中它会把外层users表全表扫一遍然后每一行都去执行一次子查询。后者的代价有多大呢users表100万行orders表1000万行最终的执行成本差不多就是100万次与orders表相关的查询说是灾难并不过分。更稳妥的改写是改成JOINSELECT u.* FROM users u JOIN orders o ON u.id o.user_id WHERE o.status 1 GROUP BY u.id;如果担心JOIN产生重复用户就在外层加GROUP BY u.id或者用EXISTS来写SELECT u.* FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.status 1 );EXISTS对每个外层行执行一次子查询一旦找到匹配就返回true通常更容易命中索引。实际项目里我习惯先用JOIN因为JOIN的执行计划更稳定出问题也好排查。4.2 连接方向、字段类型与字符集不一致连接查询的坑不止在写法上还在“环境配置”上。最典型的场景SELECT o.id, u.name FROM orders o LEFT JOIN users u ON o.user_id u.id;orders表1000万行users表50万行。如果users.id有主键索引这个LEFT JOIN问题不大优化器会先扫描orders再通过主键去users里快速定位。但如果连接字段没有索引比如ON o.shop_code u.shop_code而两边都没有索引那就成了两张表互相嵌套循环扫描行数可能膨胀到1000万乘50万数据库直接被打爆。还有一个隐蔽问题字符集和字段类型不一致。两个表一个用utf8一个用utf8mb4连接的字段一个是int一个是varchar。这种情况下即便两边都有索引索引也可能在连接时失效因为MySQL必须对其中一个字段做转换才能比较。这种问题靠EXPLAIN都可能骗过你最后只能逐字段去核对元数据。连接查询的三条铁律连接字段必须有索引字段类型必须一致字符集必须一致。再加一条能用小表驱动大表就尽量小表驱动大表尤其是在LEFT JOIN中驱动表的选择会直接决定性能上限。4.3 ORDER BY RAND()经典的随机数陷阱业务里经常有“随机抽一个商品”“随机推荐一条内容”的需求于是有人写了这样的SQLSELECT * FROM products ORDER BY RAND() LIMIT 1;这个写法在教学示例里经常出现但它也是线上慢查询的重灾区。ORDER BY RAND()的执行逻辑是先把整张表扫描一遍给每一行生成一个随机数再对所有行做一次完整排序最后取第一条。100万行商品就是100万次随机数生成加上100万行排序。你说它性能降低100倍在某些配置下可能还不止。更合理的做法是避免对所有行排序。可以反过来先随机拿到一个ID再去查记录SELECT * FROM products WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM products))) ORDER BY id LIMIT 1;这个方案用到了主键扫描成本极低。但它有一个前提主键最好相对连续。如果表里经常删除数据id之间有很多空洞随机结果就会不均匀。更严谨一点的做法是先用程序或SQL把主键ID列表取出来在应用层随机选一个ID再回表查询。两种都比ORDER BY RAND()好得多。5. 实战复盘一次线上慢查询的完整排查过程5.1 第一步先定位慢SQL从哪来线上排查慢SQL第一步不是看代码而是开慢查询日志。我在正式环境一般这样配置slow_query_log 1 long_query_time 1 slow_query_log_file /var/log/mysql/mysql-slow.loglong_query_time 1表示超过1秒的SQL全部记录。然后再用工具汇总分析比如pt-query-digest或者直接按时间区间查日志。那次线上事故定位到的SQL大致长这样SELECT * FROM call_records WHERE DATE(create_time) 2025-03-10 AND staff_id IN (101,102,103,104) ORDER BY create_time DESC LIMIT 50;5.2 第二步用EXPLAIN还原执行计划放下代码先跑EXPLAIN。结果非常“标准”字段结果说明typeALL全表扫描keyNULL没有使用任何索引rows9000000预估扫描900万行ExtraUsing where; Using filesort还额外发生了排序看到这个执行计划问题基本就清晰了DATE(create_time)导致create_time上的索引完全失效虽然staff_id IN (...)本身可以走索引但优化器一看既然有一个条件把索引废了还不如直接全表扫。于是900万行全部过一遍还要做一次文件排序。十几秒的执行时间一点儿都不冤。5.3 第三步重写SQL第一步改动把DATE函数改成范围条件让索引恢复工作SELECT * FROM call_records WHERE create_time 2025-03-10 00:00:00 AND create_time 2025-03-11 00:00:00 AND staff_id IN (101,102,103,104) ORDER BY create_time DESC LIMIT 50;因为create_time和staff_id都在一个组合索引里这次执行计划变成了rangerows从900万降到了80万最终扫描的行数还会进一步缩小。第二步改动把SELECT *改成明确需要的字段。当时业务只需要通话号码、通话时长、话务员ID这几个字段改完之后连回表都可以避免直接走覆盖索引。5.4 第四步验证优化效果改完再跑EXPLAIN对比非常明显字段优化前优化后typeALLrangekeyNULLidx_create_staffrows9000000800000实际耗时14.2秒0.35秒40倍的差距全程花了不到十分钟。这个案例给我的启发并不是优化技巧有多神而是很多线上SQL性能问题根源都在基础写法上。SQL上线前花30秒看一眼执行计划能省掉后面无数个加班的夜晚。6. 常见问题速查表与避坑清单6.1 8种坑一张表记住我把这8种写法整理成一张速查表建议直接收藏写代码之前扫一眼编号坑点写法为什么慢优化方案1索引列套函数 DATE(create_time)xxx无法利用B树有序定位改成范围条件2隐式类型转换 phone 13800138000列被CAST索引失效字段和参数类型保持一致3LIKE %关键字%前导通配符无法最左匹配改成前缀匹配或上搜索引擎4SELECT *回表多余IO临时表膨胀明确列出需要的字段5LIMIT 100000, 20扫描前10万行再丢弃延迟关联或改成游标分页6WHERE a1 OR b2优化器难选索引倾向全表扫拆成UNION ALL或改IN7WHERE IN (SELECT ...)子查询临时表或逐行执行改成JOIN或EXISTS8ORDER BY RAND()全表生成随机数加排序随机取ID再回表6.2 排查慢SQL的个人经验最后分享几条我在无数次踩坑里攒下来的心得。第一复现慢SQL必须在接近生产的数据量下进行。很多SQL在开发库只有几千条数据跑出来10毫秒上线后到了千万级直接变10秒。开发环境“没问题”恰恰是最大的问题。第二看执行计划别只看type。有时候type显示range你以为没问题了但rows依然很大说明索引虽然用上了扫描范围本身还是很宽。这种情况要再想一步能不能把条件收得更紧或者加LIMIT限制返回量。第三优化前先确认业务上是否真的需要这条SQL。有的统计查询就是要扫全表怎么改写都救不了。这种场景应该考虑并行处理、离线计算或数仓方案而不是硬在业务数据库上死磕。第四也是最重要的把所有开发和DBA拉到一个群里约定写SQL前必须过EXPLAIN。执行计划截图贴在提交记录里比一百条团队规范都管用。最后再多说一句。那个凌晨报警的夜晚之后我在团队里立了一个规矩所有涉及线上数据表的查询提交前必须把执行计划截图一起贴出来。一开始大家嫌麻烦觉得多此一举直到有几个慢SQL在评审阶段就被执行计划拦下来大家才服气。SQL优化的本质不是炫技而是让数据库尽量少做无用功。少扫描一行速度自然快一截。这8种写法只要写代码的时候多想一步绝大多数都能绕开。