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


1、等值连接：显性连接和隐性连接

在《MySQL必知必会》中对于等值连接有提到两种方式，第一种是直接在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之前的，所以这就导致两者的执行过程区别在于：
  • 隐性连接，在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 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|
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-----------+

下面对这些字段做个简单的说明（更多详情可以参考官方文档）：

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的结果时，其值可能是<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 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

2.2.4 type

该字段表示MySQL在表中找到所需行的方式，又称“访问类型”，常见的有：
该字段的值    含义
ALL    遍历全表
index与ALL的区别在于只遍历索引树
range表示只操作单表，且符合查询条件的记录不止1条
ref    表明本步执行计划操作的数据集中关联字段是索引字段，但不止1条记录符合上步执行计划操作的数据集的关联条件
eq_ref    表明本步执行计划操作的数据集中关联字段是索引字段，且只有1条记录符合上步执行计划操作的数据集的关联条件
const     表明上述"table"字段代表的数据集中，最多只有1行记录命中本步执行计划的查询条件
systemsystem只是const值的一个特例，它表示本步执行计划要操作的数据集中只有1行记录

2.2.5 possible_keys

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

2.2.6 key

该字段的值是MySQL真正用到的索引。

2.2.7 key_len

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

2.2.8 ref

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

2.2.9 rows

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

2.2.10 Extra

该字段的值包含了MySQL执行query时的其它额外信息。常见如下（查询效率由高到低）：
该字段的值        
含义
Using index表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。如果同时出现Using where，代表使用索引来查找读取记录， 也是可以用到索引的，但是需要查询到数据表。
Using where表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。如果type列是ALL或index，而没有出现该信息，则你有可能在执行错误的查询：返回所有数据。
Using filesort不是“使用文件索引”的含义！filesort是MySQL所实现的一种排序策略，通常在使用到排序语句ORDER BY的时候，会出现该信息。
Using temporary表示为了得到结果，使用了临时表，这通常是出现在多表联合查询，结果排序的场合。


3、STRAIGHT_JOIN


由上我们已经知道，EXPLAIN的提示可以帮助我们意识到哪些字段应该建索引，也可以帮我们确认SQL执行时，数据库优化器是否会以合理的顺序来JOIN多张表。


但有时候并不是说数据库优化器做的执行顺序就是最优的，而按照我们的FROM表的顺序执行才是最优的，那么问题来了：如果想让优化器以FROM语句列出的表顺序做JOIN，怎么办？

这里就要用到 STRAIGHT_JOIN 关键字了，将其加在SELECT关键字之后，用来告诉优化器按照FROM列出的表顺序进行JOIN。

举个例子，我有某个SQL如果按照优化器的执行顺序执行，则：
 
SQL查询时间为9s：


使用STRAIGHT_JOIN关键字，要求按照FROM表顺序进行执行，则：
 
SQL查询时间为0.2s：
 
但是仍然需要引起注意的是，这种方式因为执行顺序被固化了，那么随着时间的推移，数据库中的数据分布随着业务开展而发生变化，很可能导致原本运行顺畅的SQL逐渐变得糟糕。

另外，测试时为了保证结果的正确性，应避免查询缓存，需要每次执行前手动清除：

RESET QUERY CACHE;


4、参考链接



附件列表

     

    posted @ 2017-08-20 11:39  Dulk  阅读(4442)  评论(0)  编辑  收藏  举报