MySQL 联表查询和子查询

最新推荐文章于 2024-08-29 16:38:10 发布

niao_ye

最新推荐文章于 2024-08-29 16:38:10 发布

阅读量1.2k

点赞数

分类专栏： MySQL 文章标签： mysql 联表查询子查询

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

两个集合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;

+----------+---------------+-------------------------------------------------------------------------+---------+----------+--------------------------------------------+---------+

+----------+---------------+-------------------------------------------------------------------------+---------+----------+--------------------------------------------+---------+

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

+---------+-------+-----------+----------+------------+-------------------------------------+

+---------+-------+-----------+----------+------------+-------------------------------------+

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