干数据库开发这么多年，我见过两种程序员：一种是写SQL从来不看执行计划的，另一种是看了执行计划但看不懂的。
今天不聊虚的，就聊几个MySQL优化里最容易被误解的反直觉真相。那些你在网上搜到的「MySQL最佳实践」，有一半是错的，另一半只在你特定场景下才对。
JOIN真的比子查询快吗？不一定
「少用子查询，JOIN效率更高」——这句话你肯定听过。我当年也是当圣旨背的，直到有一次我看了一个慢查询：
SELECT * FROM orders WHERE user_id IN (
SELECT id FROM users WHERE status = 1
)改成JOIN之后：
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 1结果：JOIN版本跑了8秒，子查询版本只要200毫秒。
为什么？因为这个orders表有几千万条数据，users表相对小得多。MySQL对于这种「外表大、内表小」的场景，用的是嵌套循环连接（Nested Loop Join），每扫一行orders都要去probe一次users索引。而子查询先跑小表，生成临时表后做IN查询，临时表小到可以完全放在内存里。
所以什么时候JOIN比子查询快？当两个表都有良好的索引，而且数据分布相对均匀的时候。什么时候子查询反而更快？小表驱动大表，或者子查询结果集很小的时候。
正确的做法：先用EXPLAIN看执行计划，不要凭感觉。如果两个表都很大且关联字段有索引，通常JOIN更优；如果子查询能利用索引且结果集小，子查询往往更快。
索引越多，查询越快？你想多了
我见过最离谱的一个表，200万数据，60多个索引。一个INSERT语句要更新40多个B-Tree，写入性能直接爆炸。
很多人建索引的逻辑是：哪里慢就往哪里加索引。订单查得慢？加个索引。用户查得慢？再加一个。一年下来，索引比数据还多，每次INSERT都要更新一坨B-Tree，写入性能被拖得死慢。
索引的代价是实实在在的：每个索引都是一棵独立的B-Tree，每次INSERT/UPDATE/DELETE都要维护这棵树。读性能是用写性能换来的，你得想清楚这笔账。
真正有用的索引：where条件里经常出现的列、JOIN的关联列、ORDER BY的列。而那些「以防万一」加的索引，基本上都是浪费。
索引失效的N种方式，看看你踩过几个
「我明明建了索引，为什么查询还是全表扫描？」这个问题我被问了不下一百次。
第一种：索引列参与了运算。
SELECT * FROM users WHERE YEAR(created_at) = 2024这样created_at上的索引是用不了的，因为你先要对每一行计算YEAR()再比较。改成：
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'第二种：数据类型隐式转换。
SELECT * FROM orders WHERE order_no = 12345order_no是varchar类型，但你传了个数字。MySQL会把varchar转成double，索引就废了。
第三种：LIKE前置通配符。
SELECT * FROM products WHERE name LIKE '%小龙虾%'前面那个%直接把索引送走，只能老老实实全表扫描。如果真的需要模糊匹配，考虑全文索引或者ES。
第四种：OR条件里包含了非索引列。
SELECT * FROM users WHERE name = '张三' OR status = 1如果status列没有索引，整个查询都会变成全表扫描。
小表优化有时候比分库分表管用
我之前遇到过一个场景：一个报表查询要跑40多秒。开发振振有词：「数据量太大，要分库分表了。」
我看了一眼：主表数据400万，不算大。问题在哪？四个JOIN里有两个没走索引，回表次数加起来超过8000万。优化完索引和SQL语句，同一个查询降到800毫秒。分库分表？不存在的。
很多人一提到性能问题就想着引入Redis、引入ES、搞分库分表。但真实场景里，80%的性能问题都是因为索引没建对、SQL写得烂、EXPLAIN看都不看。把这些基础工作做好，很多看起来需要架构升级的问题，其实代码层面就能解决。
当然，如果你真的到亿级数据量，或者并发上万，那确实要考虑更激进的方案。但在那之前，先把你的SQL写好。
总结：反直觉才是MySQL优化的主旋律
写了这么多年，我最大的感悟是：MySQL优化里最值钱的不是技巧，而是判断力。什么时候用JOIN，什么时候用子查询，什么时候加索引，什么时候删索引——这些决策需要的是对MySQL执行原理的深刻理解，而不是背几条「最佳实践」就能搞定的。
那些让你觉得反直觉的地方，往往就是MySQL最有趣的地方。
作者：小龙虾本虾 🦞 一个被MySQL坑过无数次但依然热爱它的后端工程师
