sql - subquery用法 - 加入與子查詢




subquery中文 (11)

我是一個老派的MySQL用戶,並且總是首選JOIN子查詢。 但是現在每個人都使用子查詢,我討厭它,我不知道為什麼。

我缺乏理論知識來判斷自己是否有任何區別。 子查詢和JOIN一樣好,因此沒有什麼可擔心的?


MySQL版本:5.5.28-0ubuntu0.12.04.2-log

我也覺得JOIN總是比MySQL中的子查詢更好,但是EXPLAIN是更好的判斷方式。 這裡有一個例子,子查詢比JOIN更好。

這是我的查詢與3個子查詢:

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;

EXPLAIN顯示:

+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+
| 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 |
+----+--------------------+----------+--------+-----------------------------------------------------+--------------+---------+-------------------------------------------------+------+--------------------------+

與JOIN相同的查詢是:

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 Using temporary; Using filesort

當然,當我運行這兩個查詢時,第一個在0.02秒內完成,第二個在1分鐘後不完成,因此EXPLAIN正確解釋了這些查詢。

如果我沒有list_tag表上的INNER JOIN,即如果我刪除

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

從第二個查詢開始,那麼EXPLAIN將為這兩個查詢返回相同的行數,並且這兩個查詢的運行速度也是相同的。


SQL Server的MSDN文檔說

許多包含子查詢的Transact-SQL語句也可以表示為連接。 其他問題只能通過子查詢提出。 在Transact-SQL中,包含子查詢的語句與不包含語義的語義版本之間通常沒有性能差異。 但是,在必須檢查存在的某些情況下,連接會產生更好的性能。 否則,必須為外部查詢的每個結果處理嵌套查詢,以確保消除重複。 在這種情況下,聯合方式會產生更好的結果。

所以如果你需要類似的東西

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

嘗試使用連接。 在其他情況下,它沒有區別。

我說:為子查詢創建函數消除了混亂的問題,並允許您為子查詢實現額外的邏輯。 所以我建議盡可能為子查詢創建函數。

代碼混亂是一個大問題,業界幾十年來一直在努力避免它。


只有當第二個連接表具有比主表多得多的數據時才會出現差異。 我有一個如下的經驗...

我們有一個十萬條用戶表和他們的會員資料(友誼)約三十萬條目。 這是一個加入聲明,以便收集朋友和他們的數據,但延遲很大。 但是,在成員資格表中只有少量數據的地方工作正常。 一旦我們改變它使用子查詢它工作得很好。

但與此同時,連接查詢正在處理其他具有比主表更少的條目的表。

所以我認為連接和子查詢語句工作正常,這取決於數據和情況。


在2010年,我會加入這個問題的作者,並會強烈投票JOIN 。 但有了更多的經驗(特別是在MySQL中),我可以說:是的子查詢可以更好。 我在這裡閱讀了多個答案。 有人說,子查詢速度更快,但缺乏一個很好的解釋。 我希望我能提供這個(很)遲的答案:

首先,讓我說最重要的是: 有不同形式的子查詢

第二個重要陳述: 規模很重要

如果您使用子查詢,您應該知道,DB-Server如何執行子查詢。 特別是如果子查詢被評估一次或每一行! 另一方面,現代DB-Server能夠優化很多。 在某些情況下,子查詢有助於優化查詢,但較新版本的DB-Server可能會使優化過時。

選擇字段中的子查詢

SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo

請注意,為foo每個結果行執行一個子查詢。 如果可能的話避免這種情況,它可能會大幅減慢對大數據集的查詢速度。 但是如果子查詢沒有引用foo ,它可以通過DB服務器作為靜態內容進行優化,並且只能評估一次。

Where-statement中的子查詢

SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)

如果幸運的話,數據庫將內部優化為JOIN 。 如果沒有,你的查詢在大數據集上會變得非常非常慢,因為它會為foo每一行執行子查詢,而不僅僅是像select-type那樣的結果。

Join語句中的子查詢

SELECT moo, bar 
  FROM foo 
    LEFT JOIN (
      SELECT MIN(bar), me FROM wilco GROUP BY me
    ) ON moo = me

這很有趣。 我們結合JOIN和子查詢。 在這裡,我們獲得了子查詢的真正實力。 想像一下wilco有數百萬行的數據集,但只有幾個不同的me 。 我們現在沒有加入一張巨大的桌子,而是現在有一張較小的臨時桌子加入。 這可能會導致更快的查詢,具體取決於數據庫大小。 您可以使用CREATE TEMPORARY TABLE ...INSERT INTO ... SELECT ...具有相同的效果,這可能會在非常複雜的查詢中提供更好的可讀性。

嵌套的子查詢

SELECT moo, bar
  FROM (
    SELECT moo, CONCAT(roger, wilco) AS bar
      FROM foo
      GROUP BY moo
      HAVING bar LIKE 'SpaceQ%'
  ) AS temp_foo
  GROUP BY bar
  ORDER BY bar

您可以在多個層次中嵌套子查詢。 如果您必須對結果進行分組或排序,這可以幫助處理巨大的數據集。 通常,DB-Server會為此創建一個臨時表,但有時您不需要對整個表進行排序,而只需對結果集進行排序。 這可能會提供更好的性能,具體取決於表的大小。

結論

子查詢不是替代JOIN ,你不應該像這樣使用它們(儘管可能)。 在我看來,正確使用子查詢是用作CREATE TEMPORARY TABLE ...的快速替換CREATE TEMPORARY TABLE ...一個好的子查詢以某種方式減少了數據集,你無法在一個ON語句中完成JOIN。如果一個子查詢有一個關鍵字GROUP BYDISTINCT並且最好不在select字段或where語句中,那麼它可能會提高性能。


子查詢是解決表格問題的邏輯正確方法,“從A得到事實,以B的事實為條件”。 在這種情況下,在子查詢中使用B比在連接中更合理。 從實際意義上講,這也更安全,因為您不必因為與B多重匹配而從A中獲得重複的事實而持謹慎態度。

但實際上,答案通常取決於表現。 有些優化器在給出一個連接與一個子查詢時吸引了檸檬,有些則以另一種方式吸引了檸檬,這是針對特定優化器的DBMS特定版本和特定於查詢的問題。

從歷史上看,顯式連接通常會贏,因此連接的確切智慧總是更好,但優化器一直在變得更好,所以我寧願先以邏輯上一致的方式編寫查詢,然後在性能約束條件允許時重新進行重構。


子查詢能夠快速計算聚合函數。 例如,尋找書的最低價格,並獲得與這個價格一起出售的所有書籍。 1)使用子查詢:

SELECT titles, price
FROM Books, Orders
WHERE price = 
(SELECT MIN(price)
 FROM Orders) AND (Books.ID=Orders.ID);

2)使用JOIN

SELECT MIN(price)
     FROM Orders;
-----------------
2.99

SELECT titles, price
FROM Books b
INNER JOIN  Orders o
ON b.ID = o.ID
WHERE o.price = 2.99;

從手冊中獲取( 13.2.10.11將子查詢重寫為Joins ):

LEFT [OUTER] JOIN可以比等效的子查詢更快,因為服務器可能能夠更好地進行優化 - 這個事實並非僅針對MySQL服務器。

所以子查詢可能比左[OUTER] JOINS慢,但在我看來,它們的強度稍高一些。


從舊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秒

EXPLAIN顯示他們檢查的行數完全相同,但一個需要3秒,一個接近即時。 故事的道德啟示? 如果性能很重要(何時不是?),請嘗試多種方式並查看哪一個最快。

和...

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

0秒

同樣的結果,檢查的行數相同。 我的猜測是DISTINCT mos_content.catid比DISTINCT mos_categories.id花費的時間要長得多。


根據我的觀察,就像兩種情況一樣,如果一張表少於100,000條記錄,那麼這個連接就會工作得很快。

但是如果一個表有超過100,000個表,那麼子查詢就是最好的結果。

我有一個表有500,000條記錄,我在下面創建的查詢中,其結果時間就像

SELECT * 
FROM crv.workorder_details wd 
inner join  crv.workorder wr on wr.workorder_id = wd.workorder_id;

結果:13.3秒

select * 
from crv.workorder_details 
where workorder_id in (select workorder_id from crv.workorder)

結果:1.65秒


現在,許多dbs可以優化子查詢和連接。 因此,您只需使用解釋來檢查您的查詢,並查看哪一個更快。 如果性能差異不大,我更喜歡使用子查詢,因為它們簡單易懂。


首先,為了比較兩者,你首先應該將查詢與子查詢區分開來:

  1. 一類子查詢總是具有用連接書寫的相應等價查詢
  2. 一類不能用連接重寫的子查詢

對於第一類查詢,一個好的RDBMS將會看到連接和子查詢是等價的,並且會產生相同的查詢計劃。

這些日子甚至mysql都這樣做。

儘管如此,有時卻並非如此,但這並不意味著連接總是會贏 - 我曾經在mysql中使用子查詢提高了性能。 (例如,如果有某些東西阻止mysql規劃師正確估計成本,並且規劃人員沒有看到連接變量和子查詢變量相同,那麼子查詢可以通過強制某個路徑勝過連接)。

結論是,如果你想確定哪一個會更好地執行,你應該測試你的查詢來加入和子查詢變體。

對於第二類 ,比較沒有意義,因為這些查詢不能用連接重寫,在這種情況下,子查詢是完成所需任務的自然方式,您不應該對它們進行區分。





join