mysqlderived2_浅谈SQL优化⼊门：2、等值连接和
EXPLAIN（MySQL）
1、等值连接：显性连接和隐性连接
在《MySQL必知必会》中对于等值连接有提到两种⽅式，第⼀种是直接在WHERE⼦句中规定如何关联即可，那么第⼆种则是使⽤INNER JOIN关键字。如下例两种⽅式是“等同”的。
//WHERE⽅式
SELECT
vend_name,
prod_name,
prod_price,
quantity
FROM
vendors,
products,
sql优化的几种方式orderitems
WHERE
vendors.vend_id = products.vend_id
AND
orderitems.prod_id = products.prod_id;
//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;
//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之前的，所以这就导致两者的执⾏过程区别在于：
隐性连接，在FROM过程中对所有表进⾏笛卡⼉积，最终通过WHERE条件过滤
显性连接，在每⼀次表连接时通过ON过滤，筛选后的结果集再和下⼀个表做笛卡⼉积，以此循环
这么久了，我们终于要说到SQL性能的主题上来了。那么以上，这两种执⾏⽅式会导致什么问题呢？假如有三张表做等值连接，每张表都有1000⾏数据，那么：
隐性连接，做所有表的笛卡⼉积，共1000*1000*1000=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 JOIN B ON ... 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 rmonCode_Delegation = dele.DELEGATION_CODE) tmp1
JOIN csic_event AS eve ON tmp1monCode_Event = eve.EVENT
EXPLAIN
SELECT *
FROM
(SELECT * from t_rank AS r JOIN csic_delegation_dict AS dele ON rmonCode_Delegation = del
e.DELEGATION_CODE) tmp1
JOIN csic_event AS eve ON tmp1monCode_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 | | 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|
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-----------+
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-----------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-----------+
| 1 | PRIMARY | eve | ALL | NULL | NULL | NULL | NULL | 441 | |
| 1 | PRIMARY | | 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|
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-----------+
下⾯对这些字段做个简单的说明(更多详情可以参考官⽅⽂档)：
2.2.1 id
SELECT语句的标识字段，若SQL中只有1个SELECT语句，则该值为1，否则依次递增；若SQL是UNION的结果，则该值为NULL。
值得⼀提的是，官⽅⽂档中并没有提到在多个SELECT语句时，即id有多个不同值时，哪个先执⾏，哪个后执⾏。那么如何去认识这个顺序呢？结合⽹友和⼀些简单测试的判断看来，⼤概是这样的：
id值较⼤的，执⾏优先级较⾼，且从上到下执⾏，且id值最⼤的组中，第⼀⾏为驱动表，如上图的table dele
id值相同时，认为是⼀组，执⾏顺序从上到下
当然，这可能多少有不严谨的地⽅，只能以后在使⽤过程中再根据实际场景去做进⼀步的判别了。先留个坑吧。
2.2.2 select_type
该字段⽤于说明SELECT语句的类型：
该字段的值含义
SIMPLE简单的SELECT，不适⽤UNION或⼦查询等
PRIMARY查询中包含任何复杂的⼦部分，最外层的SELECT标记为PRIMARY
UNIONUNION中的第⼆个或后⾯的SELECT语句
DEPENDENT UNIONUNION中的第⼆个或后⾯的SELECT语句，取决于外⾯的查询
UNION RESULTUNION的结果
SUBQUERY⼦查询中的第⼀个SELECT
DEPENDENT SUBQUERY⼦查询中的第⼀个SELECT，取决于外⾯的查询
DERIVED派⽣表的SELECT，FROM⼦句的⼦查询
UNCACHEABLE SUBQUERY⼀个⼦查询的结果不能被缓存，必须重新评估外链接的第⼀⾏
2.2.3 table
⽤于表⽰数据集来⾃哪张表，其值⼀般是表名，但：
当数据集市UNION的结果时，其值可能是，这⾥的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 | | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | | system | NULL | NULL | NULL | NULL | 1 | |
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论