sql - Join 和子查询比较

  显示原文与译文双语对照的内容

我是一个老式的MySQL用户,并且总是偏爱 JOIN 胜过 sub-query 。 但现在大家都使用 sub-query,我讨厌它,我不知道为什么。

我缺乏对自己的理论知识,如果有任何区别的话。 是sub-query和 JOIN 一样好,所以没有什么可以担心的?

时间:

在大多数情况下,JOIN的速度比sub-queries快,而且sub-query的速度是非常快的。

JOIN 年代rdbms可以创建一个执行计划,更好的为你的查询和处理可以预测哪些数据应该被加载和节省时间,不像sub-query它将运行所有查询和负载数据进行处理。

sub-queries的好事是,它们比 JOIN 更具可读性 s: 这就是为什么大多数新sql人喜欢它们,这是简单的方法,但是当谈到性能,加入更在大多数情况下,即使他们并不难读。

Sub-queries是解决表单问题的逻辑正确方法,"从A 获取事实,条件来自来自的事实"。 在这种情况下,在sub-query中粘贴比做一个加入更合理。 也更安全,在实际意义上,既然你不需要谨慎获得由于多个重复的事实与b。

然而,实际上,答案通常取决于性能。 有些optimisers在给定加入 vs的时候,还有一个 sub-query,和一些口香糖,这是 optimiser-specific,DBMS-version-specific和 query-specific 。

历史上,显式连接通常赢,因此加入更好的建立智慧,但optimisers变得更好,所以我更喜欢先编写查询逻辑一致的方式,然后重组如果性能约束保证这一点。

使用解释查看数据库如何执行数据查询。 这个答案有一个巨大的"这取决于"。。

当一个子查询认为一个子查询比另一个子查询快的时候,它可以将子查询重写为一个子查询或者一个子查询。 这取决于数据,索引,相关性,数据量,查询等。

首先,要比较这两者,应该将查询与子查询区分为:

  1. 一个子查询,它总是具有与join一起编写的对应查询
  2. 不能使用联接重写的子查询

查询一个rdbms的头等舱会看到连接和子查询等价和将产生相同的查询计划。

这些天甚至MySQL也会这样做。

不过,有时候没有,但这并不意味着加入总是赢——我在mysql情况下使用子查询时提高性能。 (例如,如果有一些防止mysql正确估计成本计划,如果计划没有看到join-variant和subquery-variant相同的子查询可以比连接通过迫使某个路径)。

结论是你应该测试查询和子查询变体的查询,如果你想确定哪一个会执行更好的。

第二阶级比较毫无意义的查询使用连接不能重写,并在这些情况下,子查询是自然的方法所需的任务和你不应该歧视他们。

MSDN文档 SQL Server 表示

包含子查询的许多Transact-SQL语句可以交替地被定义为联接。 其他问题只能用子查询引起。 在Transact-SQL中,包含子查询的语句和语义等效的版本之间的性能差异通常不存在。 但是,在必须检查存在的情况下,联接会产生更好的性能。 否则,为确保消除重复值,必须为外部查询的每个结果都处理嵌套查询。 在这种情况下,连接方法会产生更好的结果。

所以如果你需要一些东西


select * from t1 where exists select * from t2 where t2.parent=t1.id

尝试使用连接。 在其他情况下,它没有什么区别。

我说:为子查询创建函数消除cluttter的问题,让你实现额外的逻辑子查询。 所以我建议尽可能为子查询创建函数。

代码中的混乱是一个大问题,业界一直在努力避免它数十年。

从一个旧的Mambo CMS运行一个非常大的数据库:


SELECT id, alias
FROM
 mos_categories
WHERE
 id IN (
 SELECT
 DISTINCT catid
 FROM mos_content
 );

0 秒


SELECT
 DISTINCT mos_content.catid,
 mos_categories.alias
FROM
 mos_content, mos_categories
WHERE
 mos_content.catid = mos_categories.id;

~3 秒

解释表明,它们检查的行数是完全相同的,但一个是 3秒,一个是接近即时。 故事的寓意如果性能是重要的( 什么时候不是),请尝试多种方式,看看哪个是最快的。

还有。。


SELECT
 DISTINCT mos_categories.id,
 mos_categories.alias
FROM
 mos_content, mos_categories
WHERE
 mos_content.catid = mos_categories.id;

0 秒

同样的结果,检查的行数相同。 我的猜测是不同的mos_content.catid 比不同的mos_categories.id 需要更长的时间。

MySQL版本:5.5.28 -0 ubuntu 0.12.04.2 -log

我还认为加入总是比MySQL中的sub-query更好,但解释是一个更好的判断方法。 下面是一个子查询比联接更好的例子。

以下是我的3 sub-queries查询:


EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION' 
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score <0.5 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id <1000000000 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=43) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=55) IS NULL 
 AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL 
ORDER BY vrl.moved_date DESC LIMIT 200;

解释说明:


+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| 1 | PRIMARY | vrl | index | PRIMARY | moved_date | 8 | NULL | 200 | Using where |
| 1 | PRIMARY | l | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where |
| 1 | PRIMARY | vrlih | eq_ref | PRIMARY | PRIMARY | 9 | ranker.vrl.list_id,ranker.vrl.ontology_id,const | 1 | Using where |
| 1 | PRIMARY | lbs | eq_ref | PRIMARY,idx_list_burial_state,burial_score | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | list_tag | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.l.list_id,const | 1 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | list_tag | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.l.list_id,const | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | list_tag | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.l.list_id,const | 1 | Using where; Using index |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+

连接相同的查询是:


EXPLAIN SELECT vrl.list_id,vrl.ontology_id,vrl.position,l.name AS list_name, vrlih.position AS previous_position, vrl.moved_date 
FROM `vote-ranked-listory` vrl 
INNER JOIN lists l ON l.list_id = vrl.list_id 
INNER JOIN `vote-ranked-list-item-history` vrlih ON vrl.list_id = vrlih.list_id AND vrl.ontology_id=vrlih.ontology_id AND vrlih.type='PREVIOUS_POSITION' 
INNER JOIN list_burial_state lbs ON lbs.list_id = vrl.list_id AND lbs.burial_score <0.5 
LEFT JOIN list_tag lt1 ON lt1.list_id = vrl.list_id AND lt1.tag_id = 43 
LEFT JOIN list_tag lt2 ON lt2.list_id = vrl.list_id AND lt2.tag_id = 55 
INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403 
WHERE vrl.position <= 15 AND l.status='ACTIVE' AND l.is_public=1 AND vrl.ontology_id <1000000000 
AND lt1.list_id IS NULL AND lt2.tag_id IS NULL 
ORDER BY vrl.moved_date DESC LIMIT 200;

输出是:


+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | lt3 | ref | list_tag_key,list_id,tag_id | tag_id | 5 | const | 2386 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | l | eq_ref | PRIMARY,status,ispublic,idx_lookup,is_public_status | PRIMARY | 4 | ranker.lt3.list_id | 1 | Using where |
| 1 | SIMPLE | vrlih | ref | PRIMARY | PRIMARY | 4 | ranker.lt3.list_id | 103 | Using where |
| 1 | SIMPLE | vrl | ref | PRIMARY | PRIMARY | 8 | ranker.lt3.list_id,ranker.vrlih.ontology_id | 65 | Using where |
| 1 | SIMPLE | lt1 | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.lt3.list_id,const | 1 | Using where; Using index; Not exists |
| 1 | SIMPLE | lbs | eq_ref | PRIMARY,idx_list_burial_state,burial_score | PRIMARY | 4 | ranker.vrl.list_id | 1 | Using where |
| 1 | SIMPLE | lt2 | ref | list_tag_key,list_id,tag_id | list_tag_key | 9 | ranker.lt3.list_id,const | 1 | Using where; Using index |
+----+-------------+-------+--------+-----------------------------------------------------+--------------+---------+---------------------------------------------+------+----------------------------------------------+

rows 列的比较说明了差异,并且使用join的查询正在使用 Using temporary; Using filesort

当然,当我同时运行两个查询时,第一个在 0.02秒内完成,第二个在 1分钟后完成,所以解释了这些查询。

如果在 list_tag 表 换句话说,中没有内部联接,则删除


AND (SELECT list_id FROM list_tag WHERE list_id=l.list_id AND tag_id=246403) IS NOT NULL 

从第一个查询并相应地:


INNER JOIN list_tag lt3 ON lt3.list_id = vrl.list_id AND lt3.tag_id = 246403

从第二个查询中,解释返回两个查询的相同行数,同时两个查询都运行得同样快。

子查询通常用于将一行作为原子值返回,但它们可以用于将值与多行关键字进行比较。 在SQL语句中几乎任何有意义的点都允许它们,包括目标列表,where子句等等。 一个简单的sub-query可以用作搜索条件。 例如在一对表格之间:


 SELECT title FROM books WHERE author_id = (SELECT id FROM authors WHERE last_name = 'Bar' AND first_name = 'Foo');

请注意,在sub-query的结果上使用普通值运算符需要返回一个字段。 如果你有兴趣检查一组其他值中的单个值是否存在,请使用:


 SELECT title FROM books (WHERE author_id IN (SELECT id FROM authors WHERE last_name ~ '^[A-E]');

这显然不同于 LEFT-JOIN,你只想在表A 和join-condition中加入内容,即使在表B 中找不到匹配的记录,等等。

如果你只是担心速度,你必须检查数据库并写一个好的查询,看看性能有什么显著的差异。

Sub-queries在你确实需要使用 2查询查找数据时非常有用。 一个例子是,选择所有销售额高于平均值的人。 好吧,首先你必须找出( 1查询那里) 然后比较平均每个人都反对,平均( 第二个选择)的销售。

至于加入 vs 子查询,请记住,无论你选择哪一个,子查询的语句将执行select语句而加入只会选择 select语句,所以通过这个属性连接语句总是会更快。 然而我没有任何经验在实际真实的差异所以不要相信我的话--写两个查询从大型数据库,看看哪一个快回来。

现在,许多dbs可以优化子查询和连接。 因此,你只需使用解释检查查询,并查看哪个查询更快。 如果性能差异不大,我宁愿使用子查询,因为它们简单易懂。

...