文章目录1. 优化器不是“法律条文”而是“精算师”2. 临界点到底选哪种3. 拆解图中的例子情况 AWHERE num NOT IN (1, 2)情况 BWHERE num NOT IN (3)4. 总结与口诀 进阶思考这张图解释了 MySQL 优化器的一个核心灵魂成本准则Cost-based Optimization。很多人有个误区觉得“NOT IN、!、LIKE %xxx肯定不走索引”。但实际上MySQL 并不看心情它只看数学。为了让你彻底理解我们要把这个逻辑拆成三层1. 优化器不是“法律条文”而是“精算师”MySQL 优化器在执行每一条 SQL 前都会在后台算两笔账账本 A走索引先去二级索引找 ID拿到 ID 后再去主键索引找整行数据这个动作叫“回表”。账本 B全表扫描直接从头到尾把整张表的数据扫一遍。重点来了二级索引是逻辑有序的但在磁盘上回表去拿具体数据时往往是随机 I/O而全表扫描是顺序 I/O。对于磁盘来说顺序读比随机读快得多。2. 临界点到底选哪种优化器会估算一个“比例”。如果你的NOT IN过滤掉绝大部分数据只剩下1%的数据需要查。优化器想“回表只要回 1% 的次数不麻烦走索引吧”如果你的NOT IN只过滤掉一点点剩下90%的数据都要查。优化器想“我要回表 90% 的行这得在磁盘上跳来跳去 90 万次我不如直接花点力气把整张表顺序读一遍呢”3. 拆解图中的例子假设表里有 200 万零几行数据num 1有 100 万行num 2有 100 万行num 3只有 5 行情况 AWHERE num NOT IN (1, 2)含义实际上就是找num 3的那 5 行。成本只需要通过索引找到这 5 行然后回表 5 次。结果走索引。因为数据量极小回表成本几乎为零。情况 BWHERE num NOT IN (3)含义实际上是找num为 1 和 2 的那 200 万行。成本要回表 200 万次磁头会在磁盘上跳疯掉。结果不走索引全表扫描。优化器认为顺序读这 200 万行比跳着读更快。4. 总结与口诀这个知识点其实在讲“索引的选择性Selectivity”。什么时候走索引当过滤完剩下的数据**“少而精”**的时候。什么时候全表扫描当剩下的数据**“多而杂”**通常超过全表的 20%~30%的时候。所以不要记“NOT IN 不走索引”要记“剩下的太多就不走索引”。 进阶思考如果你查询的字段就在索引里覆盖索引不需要回表那么哪怕NOT IN剩下 99% 的数据它也依然会走索引。因为不需要回表随机 I/O 的痛点消失了。