MySQL：为什么使用子查询的Select..IN不能使用索引？

Question

MySQL：为什么使用子查询的Select..IN不能使用索引？

3

我开始学习MySQL，但在子查询或联接的索引方面遇到了一些问题。我创建了以下两个表：

create table User(id integer, poster integer, PRIMARY KEY (id,poster));
insert into User(id, poster) values(1, 123);
insert into User(id, poster) values(1, 345);
insert into User(id, poster) values(2, 123);


create table Feed(id integer, poster integer, c integer, time integer, PRIMARY KEY(id),  INDEX(poster),INDEX(time,c));
insert into Feed(id, poster, c,time) values(1, 123, 0, 2);
insert into Feed(id, poster, c,time) values(2, 123,1,1);
insert into Feed(id, poster, c,time) values(3, 345,2,3);

我最初尝试了一些简单的查询，比如

1. Select poster from User where id =1;  
2. Select c from Feed where poster = 1;
3. Select c from Feed where poster in (1,2,3)

第三个查询的解释看起来像这样：

SIMPLE  Feed    NULL    ALL poster  NULL    NULL    NULL    3   100.00  Using where; Using filesort

我不确定为什么需要文件排序。但是在Feed表中添加一个复合索引INDEX(time,poster,c)后，相同的查询将使用该索引。

以下是新的创建表查询：

   create table Feed(id integer, poster integer, c integer, time integer, PRIMARY KEY(id),INDEX(time,poster, c));

以下是使用新的复合索引进行解释输出

1 SIMPLE Feed NULL index NULL time 15 NULL 3 50.00 Using where; Using index

我猜测，由于 order by 具有更高的优先级且它是最左边的索引，所以我们首先使用它。然后通过将 poster 添加到复合索引中，我们仍然可以使用该复合索引进行过滤，并最终返回 c。

接下来，我尝试了一些子查询。

explain SELECT Feed.c from Feed where Feed.poster IN(select poster from User where id =1) order by Feed.time;

这里没有什么花哨的东西，我只是用子查询替换了硬编码的(1,2,3)。我希望看到相同的解释结果，但实际上我得到了

1   SIMPLE  User    NULL    ref PRIMARY,poster  PRIMARY 4   const   1   100.00  Using index; Using temporary; Using filesort
1   SIMPLE  Feed    NULL    index   NULL    time    15  NULL    3   33.33   Using where; Using index; Using join buffer (Block Nested Loop)

我很好奇为什么USER表会有Using temporary; Using filesort。我也尝试了左连接，但输出的解释结果相同。

explain SELECT Feed.c
FROM `Feed` 
LEFT JOIN `User` on User.poster = Feed.poster where User.id = 1 order by Feed.time;

根据我的阅读，我们应该避免使用文件排序和临时文件。

我该如何优化我的索引和查询？

谢谢

- user2612912

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MatBailie · Accepted Answer

并不是它不能，而是没有好处。

索引有点像另一个表格，可以与第一个表格连接，以帮助连接到真实的表格。

在您的情况下，扫描表格更快。另一种选择是使用索引来隔离底层表格中所需的行，然后转到底层表格获取这些行。

如果您的表格有100万行，则情况将会不同。那么使用索引减少扫描表格的工作量就是值得的。

因此，请编写一个创建更多随机数据的测试平台，然后您就可以看到它了。

或者，使用覆盖索引。它包含您需要搜索的所有列以及您将在SELECT和JOIN中包含的所有列。

在下面的示例中，我将（对于表Feed）INDEX(poster)更改为INDEX(poster, c)。现在，如果查询计划读取索引，则立即知道c的值，而无需“加入”基础表。

create table User(id integer, poster integer, PRIMARY KEY (id,poster), INDEX(poster));
insert into User(id, poster) values(1, 123);
insert into User(id, poster) values(1, 345);
insert into User(id, poster) values(2, 123);

create table Feed(id integer, poster integer, c integer, time integer, PRIMARY KEY(id),  INDEX(poster, c),INDEX(time,c));
insert into Feed(id, poster, c,time) values(1, 123, 0, 2);
insert into Feed(id, poster, c,time) values(2, 123,1,1);
insert into Feed(id, poster, c,time) values(3, 345,2,3);

现在，比较两个查询...

Select c from Feed where poster in (1,2,3)

SELECT c, time FROM feed WHERE poster IN (1,2,3)

第一个问题可以通过索引来回答。

第二个问题需要扫描整个表或在索引上查找并加入表格。由于表格非常小，优化器将决定只扫描整个表格，因为这样更便宜。