我开始学习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;
根据我的阅读,我们应该避免使用文件排序和临时文件。
我该如何优化我的索引和查询?
谢谢