MySQL 聯表查詢和子查詢

兩個集合X和Y的笛卡兒積（Cartesian product），又稱直積，表示為X × Y，是其第一個對象是X的成員而第二個對象是Y的一個成員的所有可能的有序對。

假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。

摘自：http://www.keepmyway.com/index.php/161.html

MYSQL執行流程的簡單探讨.

周末糾結了一個關于mysql執行流程的問題,現在有點感悟,寫下這篇blog,以做記錄! 現在網絡上有很多文章,都極力推薦在mysql的多表查詢中使用聯表式,而反對子查詢式.但大多文章都是隻說其然,而不說其是以然.

說到mysql的執行,就不得不說它的執行流程.而它的執行流程又分為标準執行流程和優化後的執行流程.

标準流程 标準流程是SQL執行的标準流程,幾乎所有的SQL資料庫都是以這個流程作為基礎的.那麼在聯表的時候,他的流程是怎麼樣的呢? 這裡會帶入兩個專業的名詞,笛卡爾積,虛拟表(Virtual Table 簡稱VT); 笛卡爾積這個說明的篇幅太長,大家可以先google一下,這裡就不說明了,而且一般有學過集合的同學,都知道這麼一個東西 VT就是虛拟的表,在mysql處理某個問題的時候,它需要一個容器存放内容,那麼這個容器就是VT.

以下是标準流程的舉例說明 SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = ‘name’ LIMIT 5;

這是一個很常見的SQL語句.那它在标準流程中是怎麼執行的呢? 1.T1和T2進行笛卡爾積的計算,形成以個新的集合,放在一個VT内.我們稱這個VT為VT1; 2.對VT1進行ON條件的處理,找出VT1中符合T1.id = T2.t1_id條件的記錄,形成VT2; 3.對VT2進行WHERE字句處理,找出VT2中符合T1.name = ‘name’條件的記錄,形成VT3; 4.對VT3進行LIMIT字句處理,取出前5條資料,形成VT4; 5.傳回VT4;

這就是一個SQL的标準執行流程,由上面的流程可以看出,每兩表相聯的時候,都會先整理出一個笛卡爾集.這是非常消耗資源的.

這裡我們再看一個子查詢的處理過程. SELECT * FROM (SELECT * FROM T1 WHERE T1.name = ‘name’) as TMP INNER JOIN T2 ON TMP.id = T2.t1_id LIMIT 5;

如果按照标準的執行流程.這裡的處理流程是 1.對T1進行WHERE字句處理,得到一個臨時表TMP; 2.TMP和T2進行笛卡爾積的計算,形成以個新的集合,形成VT1; 3.對VT1進行ON條件的處理,找出VT1中符合T1.id = T2.t1_id條件的記錄,形成VT2; 4.對VT2進行WHERE字句處理,找出VT2中符合T1.name = ‘name’條件的記錄,形成VT3; 5.對VT3進行LIMIT字句處理,取出前5條資料,形成VT4; 6.傳回VT4;

對比之下,子查詢比INNER JOIN查詢多了一步的操作,就是先執行WHERE字句,過濾一遍T1,形成一個臨時表.這樣,使用TMP表和T2進行笛卡爾積計算的時候,因為TMP的資料比T1減少了很多,是以大大地提高了兩表連接配接的效率.雖然說因為子查詢而形成一個臨時表, 增加了開銷,但是卻能很大程度地減少笛卡爾積的體積,這個犧牲是可接受的.

如果是這樣的執行流程,子查詢肯定會比INNER JOIN快.那為什麼那麼多人推薦INNER JOIN呢?終究其原因就是,MYSQL優化器. 在MYSQL的語句執行之前,都會經過優化器,優化器對SQL進行一系列的處理,程式設計它自己認為效率最高的方式(但也有失誤的時候),然後再執行;

優化流程 以下是同一語句,經過MYSQL優化器處理之後的簡述.MYSQL優化器做的事很多,這裡隻是簡述. SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = ‘name’ LIMIT 5;

1.發現T1是主表,而且WHERE字句中使用的是T1中的name字段作為條件,是以優先排除T1.name != ‘name’的記錄.形成VT1 2.TMP和T2進行笛卡爾積的計算,形成以個新的集合,形成VT2; 3.對VT2進行ON條件的處理,找出VT1中符合T1.id = T2.t1_id條件的記錄,形成VT3; 4.對VT3進行WHERE字句處理,找出VT2中符合T1.name = ‘name’條件的記錄,形成VT4; 5.對VT4進行LIMIT字句處理,取出前5條資料,形成VT5; 6.傳回VT5;

優化器自行優先執行了WEHRE字句的内容,不用通過子查詢來排除記錄,這樣既可以減少笛卡爾積的體積,同時也不會因為子查詢而産生了一個臨時表. 故得出,如果可以盡量使用聯表查詢的結論

題外拓展 很多時候,你自己認為的主表,并不是真正的主表.例如 SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T2.name = ‘name’ LIMIT 5;

這條SQL中,用T2表中的name作為條件來查詢,當優化器察覺到這個問題的時候,它就會選擇T2作為主表,然後處理WHERE子句之後,再對T1進行聯接 雖然出來的結果是一樣的,但是他們的處理過程卻不一定是你所想象的 當然,這個還跟WEHRE子句中所用到到的索引有關系,總之優化器會選擇它認為最優的辦法來執行.但是,優化器認為是最優的,事實上并不一定是,是以我們要知道它的執行流程和規律,讓它在優化的時候,符合我們所想得.L

1、 mysql> SELECT t2.`RoomID`,t3.FloorID,t3.BuildingID FROM `bii_RoomUser` AS t1     -> LEFT JOIN `bii_Room` AS t2 ON(t2.RoomID = t1.RoomID)     -> LEFT JOIN `bii_Floor` AS t3 ON(t3.FloorID = t2.FloorID)     -> WHERE t1.UserID IN(166); +----------+---------+------------+ | RoomID   | FloorID | BuildingID | +----------+---------+------------+ | 10000026 | 1000012 |     100005 | | 10000027 | 1000012 |     100005 | | 10000028 | 1000012 |     100005 | +----------+---------+------------+ 3 rows in set (0.00 sec)

2、 mysql> select * from bii_RoomUser; +------------+--------+----------+ | RoomUserID | UserID | RoomID   | +------------+--------+----------+ |          1 |    111 | 10000015 | |          2 |    112 | 10000000 | |          3 |    114 | 10000000 | |          4 |    115 | 10000000 | |          5 |    136 | 10000000 | |          6 |    136 | 10000001 | |          7 |    136 | 10000002 | |          8 |    136 | 10000003 | |          9 |    136 | 10000008 | |         10 |    136 | 10000009 | |         11 |    136 | 10000015 | |         12 |    136 | 10000016 | |         13 |    137 | 10000000 | |         14 |    137 | 10000001 | |         15 |    137 | 10000002 | |         16 |    137 | 10000003 | |         17 |    137 | 10000015 | |         18 |    137 | 10000016 | |         19 |    137 | 10000008 | |         20 |    137 | 10000009 | |         21 |    137 | 10000004 | |         22 |    137 | 10000005 | |         23 |    137 | 10000024 | |         24 |    137 | 10000025 | |         25 |    138 | 10000004 | |         26 |    138 | 10000005 | |         27 |    138 | 10000024 | |         28 |    138 | 10000000 | |         29 |    138 | 10000015 | |         30 |    137 | 10000005 | |         31 |    155 | 10000000 | |         32 |    155 | 10000001 | |         33 |    155 | 10000002 | |         34 |    155 | 10000003 | |         35 |    155 | 10000008 | |         36 |    155 | 10000009 | |         37 |    155 | 10000015 | |         38 |    155 | 10000016 | |         39 |    156 | 10000000 | |         40 |    156 | 10000001 | |         41 |    156 | 10000002 | |         42 |    155 | 10000003 | |         43 |    156 | 10000008 | |         44 |    156 | 10000009 | |         45 |    156 | 10000015 | |         46 |    156 | 10000016 | |         47 |    157 | 10000000 | |         48 |    157 | 10000001 | |         49 |    157 | 10000002 | |         50 |    157 | 10000003 | |         51 |    157 | 10000008 | |         52 |    157 | 10000009 | |         53 |    157 | 10000015 | |         54 |    157 | 10000016 | |         55 |    158 | 10000000 | |         56 |    158 | 10000001 | |         57 |    158 | 10000002 | |         58 |    158 | 10000003 | |         59 |    158 | 10000008 | |         60 |    158 | 10000009 | |         61 |    158 | 10000015 | |         62 |    158 | 10000016 | |         63 |    166 | 10000026 | |         64 |    166 | 10000027 | |         65 |    166 | 10000028 | |         66 |    170 | 10000000 | |         67 |    170 | 10000001 | |         68 |    170 | 10000002 | |         69 |    170 | 10000003 | |         70 |    170 | 10000008 | |         71 |    170 | 10000009 | |         72 |    170 | 10000015 | |         73 |    170 | 10000016 | +------------+--------+----------+ 73 rows in set (0.00 sec)

3、 mysql> select * from bii_Room; +----------+---------------+-------------------------------------------------------------------------+---------+----------+--------------------------------------------+---------+ | RoomID   | RoomName      | Location                                                                | FloorID | IsPublic | Pic                                        | Point   | +----------+---------------+-------------------------------------------------------------------------+---------+----------+--------------------------------------------+---------+ | 10000000 | 大會議室      | 90,19,120,13,144,9,172,5,204,6,219,8,219,104,90,104                     | 1000000 |        0 | userfile/image/room/yicenghuiyishi.png     | 144,52  | | 10000001 | 男衛生間      | 260,13,285,23,306,33,306,103,260,103                                    | 1000000 |        0 | userfile/image/room/yicengnanwei.png       | 274,53  | | 10000002 | 女衛生間      | 304,33,330,49,354,75,366,103,304,103                                    | 1000000 |        0 | userfile/image/room/yicengnvwei.png        | 321,63  | | 10000003 | 公共區域      | 4,103,366,103,366,149,4,149                                             | 1000000 |        1 | userfile/image/room/yicengdating.png       | 193,115 | | 10000004 | 接待室        | 87,147,186,147,186,239,150,235,120,231,87,220                           | 1000000 |        0 | userfile/image/room/yicengjiedaishi.png    | 129,171 | | 10000005 | 大會議室      | 216,149,362,149,354,169,336,187,312,207,282,221,252,229,235,235,216,237 | 1000001 |        0 | userfile/image/room/ercenghuiyishi.png     | 251,174 | | 10000006 | 男衛生間      | 259,15,281,24,202,32,202,104,259,104                                    | 1000001 |        0 | userfile/image/room/ercengnanwei.png       | 273,50  | | 10000007 | 女衛生間      | 302,32,327,48,344,66,356,84,365,102,302,102                             | 1000001 |        0 | userfile/image/room/ercengnvwei.png        | 317,68  | | 10000008 | 辦公區        | 72,32,95,20,131,9,161,5,185,3,215,6,233,8,233,61,72,61                  | 1000001 |        0 | userfile/image/room/ercengbanggongqu.png   | 149,24  | | 10000009 | 第二洽談室    | 72,61,173,61,173,126,72,126                                             | 1000001 |        0 | userfile/image/room/ercengdierqiatang.png  | 113,84  | | 10000010 | 男衛生間      | 259,15,283,23,304,34,304,103,259,103                                    | 1000002 |        0 | userfile/image/room/sancengnanwei.png      | 271,55  | | 10000011 | 女衛生間      | 304,34,324,45,340,61,355,79,366,103,304,103                             | 1000002 |        0 | userfile/image/room/sancengnvwei.png       | 317,67  | | 10000015 | A-101機房     | 365,81,422,81,422,113,365,113                                           | 1000008 |        0 | userfile/image/room/jifang.png             | 380,82  | | 10000016 | A座大廳       | 163,59,243,59,243,113,163,113                                           | 1000008 |        0 | userfile/image/room/dating.png             | 191,73  | | 10000024 | 第三洽談室    | 1,126,11,94,17,76,23,57,56,38,92,30,72,26                               | 1000001 |        0 | userfile/image/room/ercengdisanqiatang.png | 32,84   | | 10000025 | 休息室        | 87,165,186,165,186,237,161,237,137,234,113,228,87,219                   | 1000001 |        0 | userfile/image/room/ercengxiuxishi.png     | 125,184 | | 10000026 | NEC辦公區西片 | 0,0,142,0,142,400,0,400                                                 | 1000012 |        0 | userfile/image/room/NECxi.png              | 59,110  | | 10000027 | NEC辦公區中片 | 142,0,290,0,290,400,142,400                                             | 1000012 |        0 | userfile/image/room/NECzhong.png           | 196,110 | | 10000028 | NEC辦公區東片 | 290,0,425,0,425,400,290,400                                             | 1000012 |        0 | userfile/image/room/NECdong.png            | 345,110 | +----------+---------------+-------------------------------------------------------------------------+---------+----------+--------------------------------------------+---------+ 19 rows in set (0.00 sec)

4、 mysql> select * from bii_Floor; +---------+-------+-----------+----------+------------+-------------------------------------+ | FloorID | Floor | FloorName | Location | BuildingID | Pic                                 | +---------+-------+-----------+----------+------------+-------------------------------------+ | 1000000 |     1 | 1         |          |     100002 | userfile/image/floor/C1.png         | | 1000001 |     2 | 2         |          |     100002 | userfile/image/floor/C2.png         | | 1000002 |     3 | 3         |          |     100002 | userfile/image/floor/C3.png         | | 1000003 |     0 | B1        |          |     100002 |                                     | | 1000004 |     1 | 1         |          |     100001 |                                     | | 1000005 |     2 | 2         |          |     100001 |                                     | | 1000006 |     3 | 3         |          |     100001 |                                     | | 1000007 |    -1 | B1        |          |     100001 |                                     | | 1000008 |     1 | 1         |          |     100000 | userfile/image/floor/A1.png         | | 1000009 |     2 | 2         |          |     100000 |                                     | | 1000010 |     3 | 3         |          |     100000 |                                     | | 1000011 |    -1 | B1        |          |     100000 |                                     | | 1000012 |    11 | 11        |          |     100005 | userfile/image/floor/NEClouceng.png | +---------+-------+-----------+----------+------------+-------------------------------------+ 13 rows in set (0.00 sec)