不要再问我in，exists走不走索引了...

引言在处理业务需求时，我们面临着将一条数据A存储到数据库B中，并找出与数据库B相比额外的部分。
为了简化问题，我用一个模型来描述这个过程。
在寻找解决方案时，我们发现了与“notin'”和“notexists”类似的逻辑。
然而，我们对“IN”、“NOTIN”、“EXISTS”和“NOTEXISTS”这四个运算符的用法和效率以及它们是否真正使用了索引存在疑问。
带着这些问题，本文将一一探讨。
使用说明为了方便理解，我们创建了两张表t1和t2，并填充了一些数据。
其中，ID为主键，name为简单索引。
对于当前的问题，我们可以使用“NOTIN”或“NOTEXISTS”来过滤t1表中的数据而不是t2表中的数据。
对name字段进行匹配操作。
执行结果是一样的，但请注意“NOTIN”和“NOTEXISTS”的用法有差异。
“NOTIN”要求子查询的匹配字段不能为空，否则可能返回空结果。
将具有空值的名称添加到T2表后，“NOTIN”将返回一个空集。
“NOTEXISTS”返回一个布尔值，不关心返回的具体数据。
在执行效率方面，根据MySQL版本的不同，“IN”和“EXISTS”索引的使用情况存在差异。
“IN”和“EXISTS”的效率取决于匹配字段的长度和数据量，并且存在一定的临界点。
In，执行过程存在，当执行“IN”查询时，MySQL会先执行子查询得到结果集，然后再执行条件。
完成后会与外部表进行笛卡尔积运算来过滤数据。
“EXISTS”查询首先遍历外部表并判断内表中每条记录的匹配情况，只有在内表中找到匹配情况才会返回true。
指数？网上有一种说法是“IN”和“EXISTS”不会被索引，但事实并非如此。
根据MySQL版本和字段长度的不同，是否使用索引存在差异。
通过实际测试验证，可以发现数据量和字段长度相关的规律。
效果如何？对于网上流传的“EXISTS”比“IN”效率更高的说法，通过数据量测试发现实际情况并不一致。
在不同数据量的情况下，“IN”和“EXISTS”的执行效率是不同的，优化器在不同情况下的处理也不同。
谁快谁慢，谁虚无或不存在？对于“NOTIN”和“NOTEXISTS”的对比，测试结果表明，在不同数据量和索引类型条件下，执行效率存在差异。
一般来说，当较小的表用作外部表时，“NOTEXISTS”效率更高，而当较大的表用作外部表时，“NOTIN”与“NOTEXISTS”或“NOTIN”一样高效。
快速地。
JOIN的嵌套循环要理解为什么JOIN中的“IN”变了，就需要了解JOIN的三个嵌套循环连接。
其中包括简单嵌套循环连接、索引嵌套循环连接和块索引嵌套连接。
这三个连接方法的复杂性和效率各不相同。
使用索引和缓存优化可以显着提高执行效率。

mysql不等于走索引吗

不一定，很多场景下，使用二级索引进行SWIM和不等操作的成本远远超过全表扫描的成本。
二级索引。
但不能完全判断NOTIN或<>操作不能被索引。

MySQLnotin不走索引？胡扯

今天的文章是之前策划的《程序员的十万个为什么》系列文章。

面试中经常会问MySQLnotin是否使用索引？偶尔同事会说千万不要用notin，索引性能会很差。
notin的性能与对应字段的区分有关。
是真的？

今天小江就带大家深入了解一下这个问题。
首先我们需要使用explain这个关键字，然后我们需要了解这个关键字。
Explain是执行计划，它可以返回给定MySQL语句的执行信息，以便您判断索引是否已达到以及是否需要优化。

文章结构

详细解释

索引原理

MySQL语句查询原理

Notin原理

结论

首先我们创建一个表，插入一些数据，方便后续测试。

CREATE?TABLE?test?(?id?INT?NOT?NULL?AUTO_INCRMENT,second_key?INT,text?VARCHAR(100),PRIMARY?密钥？（id），?KEY?idx_second_key?(second_key))?Engine=InnoDB?CHARSET=utf8;INSERT?INTO?test?VALUES(1,?10,?'t1'),?(2,?20,?'t2'),(3,?30,?'t3'),(4,?40,?'t4'),(5,?50,?'t5'),(6,?60,?t6'),(7,?70,?'t7'),(8,?80,?'t8');

运行explain命令我们会得到以下内容

mysql>?解释?select?*?from?test?\G****************************?1.?行?******************************?id:?1select_type:?SIMPLE表:?测试?分区:?NULL?类型：？ALLpossible_keys：？NULLkey:?NULLkey_len:?NULLref:?NULLlines:?13?过滤：？100.00Extra:?NULL1?row?in?set,?1?warning?(0.00?sec)

这里内容很多，但这些是唯一值得我们关注的字段

type

rows

Extras

我们来一一解释一下MySQL运行时的type意味着什么当前语句的执行类型有以下几种：值：system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。

该系统比较少见。
当引擎是MyISAM或Memory并且只有一条记录时，它是系统，意味着可以在系统级别准确访问。
这种情况很少见，可以忽略不计。

const查询在相等值匹配时到达主键或唯一二级索引。
例如，whereid=1

eq_ref可用于使用主键或唯一索引连接表以进行等效匹配。

ref和ref_or_null，当非唯一索引和常量等效匹配时。
只有ref_or_null表示查询条件为wheresecond_keyisnull

fulltext，index_merge一般不被跳过。

unique_subquery和index_subquery表示union语句在使用in语句时实现了唯一索引或普通索引的等效查询。

10和second_key<90>

index我们到达了索引，但是我们需要扫描整个索引。

这太直观了，即不使用索引，而是使用全表扫描。

现在我们来谈谈台词。
当MySQL执行一条语句时，它会评估期望扫描的行数。

最后一点是关键内容？另外，不要认为这是一个扩展。
但这很重要，因为它可以更好地帮助您查明MySQL如何执行该语句。
让我们选择一些要点来谈谈。

使用索引，当我们的查询条件和返回的内容都存储在索引中时，我们可以使用覆盖索引而不需要返回表，例如selectsecond_keyfromtestwheresecond_key=10

10andsecond_keylike'%0';

Usingwhere，当我们使用的时候。
全表扫描以及当有条件触发全表扫描时会命中哪里。
例如select*fromtestwheretext='t'

使用filesort，查询不会到达任何索引，必须在内存或硬盘中排序，例如select*fromtestwheretext='t'orderbytextdesclimit10

也可以看出，无论是Type还是Extra，它们的性能从头到尾都是在下降的，所以我们在优化SQL的时候，一定要尽力去优化未来。
好了，关键词我们已经简单介绍到这里了，但是我们可以分析一下notin是否达到了索引，内容就更少了。
我们需要了解MySQL的索引原理。
下面是B+Tree索引的示意图，这也是MySQL索引的原理。

MySQL中的每个索引都会构建一棵树，我们也需要在脑海中记住一棵“树”。
所以我心里的两棵树是这样的。

为了快速描述本文的要点，图中方便地忽略了B+树的一些细节。

第一棵树是主键索引。
每个页面是B+树中最重要的概念：这里我们也称其为节点。
非叶节点不存储数据，仅存储指向子节点的指针，叶节点存储主键和所有其他列值。
每个节点通过双向指针连接左右节点，形成双向链表。
页面内的每个块都可以理解为一条记录。
页面内的多个记录通过单向指针链接，形成单个列表链接。
所有页面和页面内的记录基于主键从左到右递增。

第二棵树是二级索引。
非叶子节点不存储数据，只存储指向辅助节点的指针。
所有页面以及页面内的记录都符合二级索引，从左到右递增，这是与主键索引的主要区别，其余相同。

那么我们开始分析索引查询原理

select?*?from?test?where?second_key?=?40;

该语句的查询过程为：

由于second_key有索引，因此使用二级索引idx_second_key生成的树。

查看Page1，发现我们需要查询的记录位于Page12所属的叶子节点。

通过查询Page12，我们发现我们需要查询的记录位于Page27节点中。

在节点Page27中从左向右遍历，得到节点40

获取节点40存储的ID4主键

为什么没有二级索引数据，那么需要返回表时，通过ID4再次查找primary主键索引树。

按照刚才的顺序，终于找到了第27页内容的节点并返回。

同时，我们运行解释来验证类型是否为ref并使用等效的非唯一索引匹配。

解释?选择?*?from?test?dove?second_key?=?40?\G;id:?1select_type:?SIMPLE表:?测试?分区:?NULL类型:?refpossible_keys:?idx_second_key?key:?idx_second_keykey_len:?5ref:?constrows:?1?filtered:?100.00Extra:?NULL1?row?in?set,?1?warning?(0.00?sec)

上面是一个非常简单的查询，所以让我们给出“研究一个稍微复杂一点的问题”。

select?*?from?test?where?second_key?>?10?and?second_key?<?50;

该语句的查询过程为：

由于second_key有索引，因此使用二级索引idx_second_key生成的树。

10。
通过前面的解释，我们将识别出Page23的第二个节点。

因为叶子节点是双向链表，所以我们不需要再次从根节点查找更多的内容。
我们直接从左到右遍历比较，直到content>=50，则定位到第16页1个损坏的节点。

所以我们得到的结果是Page23和Page27的20、30和40个节点。

然后返回表，分别找到20、30、40对应的主键2、3、4的内容并返回数据。

我们继续解释一下。
类型为range，即范围查询使用索引，Extra有内容。
使用索引条件是指范围查询在返回表之前先使用索引进行比较。

解释？选择？*？来自？测试？哪里？第二个_key？>？10？和？第二个_key？<？50？\G;id:?1select_type:?SIMPLE表：?test?分区：?NULL类型：?rangepossible_keys:?idx_second_key？key:?idx_second_keykey_len:?5ref:?NULLrows:?3filtered:?100.00Extras:?Using?index?condition1?row?in?set,?1?warning?(0.00?sec)

好了，现在进入本文的高潮部分，你知道下面的语句如何执行吗？

select?*?from?test?where?second_key?not?in(10,30,50);

根据我们的经验，这次我们先解释一下它是如何坏掉的预料之中，类型是EVERYTHING扫描整个表你又在撒谎吗，小工匠？这不是没有索引吗？

INSERT?INTO?test?VALUES(1,?10,?'t1'),(2,?20,?'t2'),(3,?30,?t3'),(4,?40,?'t4'),(5,?50,?'t5'),(6,?60,?'t6'),(7,?70,?'t7'),?(8,?80,?'t8');0

好吧，这很尴尬。
那么，让我们尝试另一个句子。

INSERT?INTO?test?VALUES(1,?10,?'t1'),(2,?20,?'t2'),(3,?30,?'t3'),(4,?40,?'t4'),？?(5,?50,?'t5'),(6,?60,?t6'),(7,?70,?'t7'),(8,?80,?'t8');1

尝试再次运行它，看看是否可以恢复它。
很美丽。
这次我去了索引。

INSERT?INTO?test?VALUES(1,?10,?'t1'),(2,?20,?'t2'),(3,?30,?'t3'),(4,?40,?'t4'),(5,?50,?'t5'),(6,?60,?'t6'),(7,?70,?'t7'),(8,?80,?'t8');2

那么为什么索引第一次没有走呢？好吧，话不多说，我们来解读一下。
MySQL会优化索引的选择。
如果MySQL认为全表扫描比索引+返回表更高效，它就会选择全表扫描。
回到我们的例子，全表扫描行数是8行，不需要回表，但是如果使用索引，不仅需要扫描6次，还需要回表6次;MySQL认为重复表结果的性能消耗不如直接全表扫描，所以默认优化了MySQL导致直接全表扫描。

那么如果我只想select*并使用索引该怎么办呢？好的，安排

INSERT?INTO?test?VALUES(1,?10,?'t1'),(2,?20,?'t2'),?？(3,?30,?'t3'),(4,?40,?'t4'),(5,?50,?'t5'),(6,?60,?'t6'),(7,?70,?'t7'),(8,?80,?'t8');3插入T?INTO?测试?值(1,?10,?'t1'),(2,?20,?'t2'),(3,?30,?'t3'),(4,?40,?'t4'),(5,?50,?'t5'),(6,?60,?'t6'),(7,?70,?'t7'),(8,?80,?'t8');4

我放心了，这次不只是索引了？由于limit的增加，MySQL在优化时发现索引+表性能的性能更高。
因此，只要合理使用notin，它肯定会被索引，并且在真实环境中我们有很多记录，MySQL通常不会将所有性能评为较高。
。

那么最后我们来说一下notin索引的原理，这样我们就可以更加自信、大胆地使用notin了？让我们再次回到地图索引。

INSERT?INTO?test?VALUES(1,?10,?'t1'),(2,?20,?'t2'),(3,?30,?'t3'),(4,?40,?'t4'),(5,?50,?'t5'),(6,?60,?'t6'),(7,?70,?'t7'),(8,?80,?'t8');3

这条语句在实际执行时实际上是反汇编的

INSERT?INTO?测试?值(1,?10,?'t1'),(2,?20,?'t2'),(3,?30,?'t3'),?(4,?40,?'t4'),(5,?50,?'t5'),(6,?60,?'t6'),(7,?70,?'t7'),(8,?80,?'t8');6和<情况下使用索引。
那么你会亲自分析一下这个被揭穿的说法吗？一旦这条语句分解完成，就相当于4个开区间。
单独查找起始节点，然后按索引查找。
那么当有人告诉你不要使用索引时，你知道该怎么说吗？

本文是之前策划的《程序员的十万个为什么》系列文章。
如果还有其他问题可以给我留言或者查看我的《程序员笔记》订阅账号。
探索该系列文章。

参考文献

参考掘金小册子《从根源上理解MySQL》

原文：https://juejin.cn/post/7102968550540705799