比较查询性能:连接Vs选择与表不同

Question

我有两张桌子person和city。 person 表和城市表使用city_id亲自连接。person表包含大约一个百万行，城市表大约有10000行。

indexes on person: index1: id, index2: city_id
indexes on city:   index1: id

我需要选择所有那些没有人行与之相关的城市。城市和个人表如下(演示数据)。

CITY                PERSON

id  city            id  name   city_id
-------------       ------------------
1    city-1         1   name-1   1
2    city-2         2   name-2   2
3    city-3         3   name-3   2
4    city-4         4   name-4   3
5    city-5         5   name-5   1
6    city-6         6   name-6   3
7    city-7         7   name-7   4
8    city-8         8   name-8   8

为了得到结果，我写了两个查询：

query1：

     select c.id, c.city 
     from city c 
     left join person p on c.id = p.city_id  
     where p.id is null

query2：

     select * 
     from city 
     where id not in ( select distinct city_id from person)

这两个查询的执行计划看起来都很相似：

对于查询1：

对于查询2：

​

然后，我使用了概要分析，并两次运行这两个查询来查看它们花费了多少时间：

query1: 0.000729 0.000737 0.000763
query2: 0.000857 0.000840 0.000852

显然，从以上数据来看，query1的性能优于query2。

我很困惑，因为我对query2的理解应该优于query1。由于query2的嵌套查询使用的是被索引的city_id，而mysql可以利用city_id索引来获取所有id的，而query1使用的是join，这将获得两个表的笛卡尔乘积。是因为我使用了较少的数据f. person(1000)和city(200)记录。

我错过了什么，因为query1比query2表现得更好。

编辑

来自mysql文档：

covering index: An index that includes all the columns retrieved by a query. Instead of using 
the index values as pointers to find the full table rows, the query returns values 
from the index structure, saving disk I/O

这是我在提出query2时所作的假设。

Answer 1

你们的表现差别很小。您确实需要多次运行查询，以确定这些差异是否相关。行的数目也很小。很可能，所有的数据都在一个或两个数据页上。因此，您不能从示例中概括(即使结果是正确的)。

我建议把这写成：

select c.* 
from city c
where not exists (select 1 from person p where p.city_id = c.id);

为了提高性能，您需要在person(city_id)上建立索引。

这可能与left join具有相同的执行计划。我只是觉得这是一个更清晰的意图声明--而且它通常在任何数据库上都有很好的性能。

not in并不完全等价。以下是一些原因：

1. select distinct可以抛出优化器。这是不需要的，但有些数据库实际上可能运行distinct.
2. NULLs，但处理方式不同。如果子查询中的任何一行返回一个NULL值，则外部查询将根本不返回任何行。

。

Answer 2

您可以在NOT中删除distinct，因为IN()本身考虑不同的记录。在上面的查询中，由于没有额外的选择来检索join中的数据，所以join在这里进行了更多的优化。但这还是要看情况。

我想说的是，加入通常是代价高昂的。