浅谈SQL优化入门：2、等值连接和EXPLAIN（MySQL）

浅谈SQL优化入门：2、等值连接和EXPLAIN（MySQL）

1.等值连接:显性连接和隐形连接

等值连接有提到两种方式

• 第一种是直接在WHERE子句中规定如何关联即可
• 第二种则是使用INNER JOIN关键字

如下例两种方式是“等同”的。

//where方式
select
	vend_name,
	prod_name,
	prod_price,
	quantity
from 
	vendors,
	products,
	orderitems
where 
	vendors.vend_id = products.vend_id
	and
	orderitems.prod_id = products.prod_id;

//INNER JOIN方式
select 
	vend_name,
	prod_name,
	prod_price,
	quantity
from 
	(vendors inner join products on vendors.vend_id = prodcuts.vend_id)
	inner join orderitems on orderitems.prod_id = products.prod_id;

其中，WHERE方式我们称之为隐性连接，而INNER JOIN方式我们称之为显性连接
这两者是有区别的，而上面我们说的“等同”，是指两者在结果集上是等同的，实际上在执行过程上却是不同的。

之前我们提到过sql语句的执行过程,实际上都会产生笛卡尔积,都会有一个虚拟表,用来暂时保存执行结果,以作为下一步的输入,另外,on过滤的执行顺序是在where之前的,所以这就导致了两者的执行过程中有区别

• 隐形连接(where),在from过程中对所有的表进行笛卡尔积,最终通过where条件过滤
• 显性连接(inner/left/right join),在每一次表连接时通过ON过滤，筛选后的结果集再和下一个表做笛卡儿积，以此循环

这么久了，我们终于要说到SQL性能的主题上来了。那么以上，这两种执行方式会导致什么问题呢？假如有三张表做等值连接，每张表都有1000行数据，那么：

• 隐性连接，做所有表的笛卡儿积，共100010001000=1亿 行数据，再通过WHERE过滤，也就是说，三张表连接最终扫描的数据量高达1亿
• 显性连接，先做头两张表的笛卡儿积1000*1000=100万 行数据，通过ON条件筛选后的结果集（可能不到1000行）再和第三张表1000行数据做笛卡儿积

结论:

显性连接最终做笛卡儿积的数量，根据之前表间ON后的结果，可能会远远小于隐性连接所要扫描的数量，所以同样是等值连接，显性连接的效率更高。

2.EXPLAIN

2.1 驱动表

有的人可能会疑惑，不对啊，你这么说来，显性连接和隐性连接的差距不是一点半点，为什么我测试出来，两者的执行效率却几乎是等同的呢？

这是因为数据库引擎捣的鬼，这里以MySQL举例，在MySQL中，表间关联的算法是Nest Loop Join，即JOIN是通过嵌套循环来实现的。而你所写SQL的连表顺序（非OUTER类型）并不是实际执行的连表顺序，因为数据库会针对表情况进行自动优化，以小的结果集来驱动大的结果集，我们也常说以小表驱动大表。

也就是说，假如你有三张表，你写下SQL的JOIN顺序是A inner join B ON … inner join C ON …，其中表A有1000条数据，表B有100条数据，表C只有10条数据，实际上在执行的时候，很可能是先扫描数量最少的表C，然后是表B，最后是表A，中途遇到符合ON条件过滤的则执行筛选。为什么？

数据库不傻，我们说过表连接时通过嵌套循环来实现的，从第一个表中取出第一条，和第二个表中所有记录进行匹配，再取出第二条，和第二个表中所有记录进行匹配，以此循环。这里的第一个表，我们就称之为驱动表。

如果驱动表越大，意味着外层循环次数就越多，那么被驱动表的访问次数自然也就越多（如驱动表和被驱动表数据分别为10条和100条，那么被驱动表访问次数为10次；如果分别是100条和10条，被驱动表访问次数则为100次）, 而每次访问被驱动表，即使需要的逻辑 IO 很少，循环次数多了，总量也不可能小，而且每次循环都不能避免消耗CPU，所以 CPU 运算量也会跟着增加。最终，这就意味着SQL性能的消耗，表现在查询时间变长。

就像你去超市买东西，总共都是买1000件东西，我让你买100件就付款一次，共付款10次；或者买10件就付款一次，共付款100次，哪个更累人？

所以，现在我们已经明白了，原来数据库在执行我们的SQL的时候，是会对执行顺序进行优化调整的。另外，要注意的是，这里的驱动表，并不是说数据量小的就是驱动表，我们刚才也提过，如果仅仅以表的大小来作为驱动表的判断依据，假若小表过滤后所剩下的结果集比大表多很多，结果就会在嵌套循环中带来更多的循环次数，这种情况小表驱动大表反而是低效率了。

所以，驱动表是由结果集的数据量来决定的：

• 指定了连接条件时，满足查询条件的记录行数少的表为驱动表
• 未指定连接条件时，行数少的表为驱动表

所以，准确地说，要想效率高，是要以小结果集驱动大的结果集。

2.2 EXPLAIN

那么,如何知道SQL优化后是如何执行SQL查询顺序的呢？这就要使用到MySQL中的关键字EXPLAIN了。
命令的主要作用是输出mysql的优化器对sql的执行计划,即mysql会解释如何处理输入的sql(是否使用索引,使用哪个索引,多表以什么顺序以及什么关联字做join)

我们说想要sql执行效率高,就是以小结果驱动大结果集,而EXPLAIN的提示就可以帮助我们确认SQL执行时优化器是否会以合理的顺序来JOIN多张表。

EXPLAIN的使用很简单，直接加在SELECT之前即可，它不会真正去执行SQL，只是做分析处理。如下：

EXPLAIN
SELECT *
FROM
    (SELECT * from t_rank AS r JOIN csic_delegation_dict AS dele ON r.commonCode_Delegation = dele.DELEGATION_CODE) tmp1 
        JOIN csic_event AS eve ON tmp1.commonCode_Event = eve.EVENT

EXPLAIN命令会为SQL中出现的每张表返回一行信息来说明数据库优化器将会如何操作这张表，返回的信息以表呈现，共有10个字段，如下示例：

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-----------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra     |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-----------+
|  1 | PRIMARY     | eve        | ALL    | NULL              | NULL    | NULL    | NULL |  441 |           |
|  1 | PRIMARY     | <derived2> | ALL    | NULL              | NULL    | NULL    | NULL |  504 |Using where|
|  2 | DERIVED     | dele       | ALL    | NULL              | NULL    | NULL    | NULL |   41 |           |
|  2 | DERIVED     | r          | ALL    | NULL              | NULL    | NULL    | NULL |  539 |Using where|
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-----------+

下面对这些字段做个简单的说明

(1)id
select语句的标识字段,若sql中只有一个select语句,该值为1,否则依次递增,若sql是union的结果,该值为null

• id值较大,执行优先级较高,且从上到下执行,在id最大的组中,第一行为驱动表,如上图的的dele表
• id值相同时,认为是一组,执行顺序从上到下

当然，这可能多少有不严谨的地方，只能以后在使用过程中再根据实际场景去做进一步的判别了。

(2)select_type
该字段用于说明SELECT语句的类型：

该字段的值	含义
SIMPLE	简单的SELECT，不适用UNION或子查询等
PRIMARY	查询中包含任何复杂的子部分，最外层的SELECT标记为PRIMARY
UNION	UNION中的第二个或后面的SELECT语句
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT，取决于外面的查询
DERIVED	派生表的SELECT，FROM子句的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外链接的第一行

(3)table
用于表示数据集来自哪张表，其值一般是表名，但：

• 当数据集市UNION的结果时，其值可能是<UNION M,N>，这里的M或N是id字段的值
• 当数据集来自派生表的SELECT，则显示的是derived*，这里的*是id字段的值，如：

mysql> EXPLAIN SELECT * FROM (SELECT * FROM ( SELECT * FROM t1 WHERE id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t1         | const  | PRIMARY,idx_t1_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(4)type
该字段表示MySQL在表中找到所需行的方式，又称“访问类型”，常见的有：

该字段的值	含义
ALL	遍历全表
index	与ALL的区别在于只遍历索引树
range	表示只操作单表，且符合查询条件的记录不止1条
ref	表明本步执行计划操作的数据集中关联字段是索引字段，但不止1条记录符合上步执行计划操作的数据集的关联条件
eq_ref	表明本步执行计划操作的数据集中关联字段是索引字段，且只有1条记录符合上步执行计划操作的数据集的关联条件
const	表明上述"table"字段代表的数据集中，最多只有1行记录命中本步执行计划的查询条件
system	system只是const值的一个特例，它表示本步执行计划要操作的数据集中只有1行记录

(5)possible_keys
该字段的值是可能被MySQL用作索引的字段，若值为NULL，则没有字段会被用作索引，因此查询效率不会高，这种情况下，需要优化数据表的索引结构。

(6)key
该字段的值是MySQL真正用到的索引。

(7)key_len
该字段的值表明上述key字段的length，当MySQL将某联合索引字段作为SQL执行时用到的索引时，key_len字段可以暗示MySQL真正在什么程度上（多长的最左前缀匹配字段）使用了该联合索引。若key字段的值为NULL，则key_len字段值也为NULL。

(8)ref
该字段的值表明数据表中的哪列或哪个constants会被用于与key字段指定的索引做比较。

(9)rows
该字段的值表明MySQL执行该步计划对应的查询时扫描的行数，该值是估算值，不完全准确。这个值对于SQL优化非常具有参考意义，通常情况下，该值越小查询效率越高。

(10)Extra
该字段的值包含了MySQL执行query时的其它额外信息。常见如下（查询效率由高到低）：