周末纠结了一个关于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子句中所用到到的索引有关系,总之优化器会选择它认为最优的办法来执行.但是,优化器认为是最优的,事实上并不一定是,所以我们要知道它的执行流程和规律,让它在优化的时候,符合我们所想得.
