您的位置:首页 > 博客中心 > 数据库 >

MYSQL优化派生表(子查询)在From语句中的

时间:2022-03-14 09:20

      Mysql 在5.6.3中,优化器更有效率地处理派生表(在from语句中的子查询):

      优化器推迟物化子查询在from语句中的子查询,知道子查询的内容在查询正真执行需要时,才开始物化。这一举措提高了性能:

 

      1:之前版本(5.6.3),from语句中的子查询在explain select 查看执行计划语句执行时就会物化。它导致了部分查询执行,但explain语句的目的是获取执行计划信息,而不是执行查询

该版本物化不会在explain中发生,所以explain执行计划结果的得到更快;

      2:因为上面提及的,物化子查询的推迟有可能不会发生。考虑一个from语句中的子查询的结果和另一个表join(链接)查询,如果优化器先处理另一张表A,然后发现A中没有满足条件的行返回,此时join不会再继续执行,并且优化器会完全跳过物化子查询这步骤;

      考虑下面的explain语句,子查询出现在form语句中;

   

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

      优化器避免物化子查询直到子查询的结果在查询真正执行需要时。在上面情况下,查询并没有执行,所有并没有物化(子查询);

      即使查询执行时,也会要求优化器去避免物化。考虑下面的查询:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

     如果优化器先处理t1,并且where语句产生个空集,join结果必定是空集并且该子查询没必要去物化。

     最坏的情况(派生表被物化),查询执行会花费和之前版本之前的时间,在最好的情况下(派生表不会物化),查询执行更快。

     在派生表需要物化的情况下,优化器会通过为物化表添加索引来加速访问物化表的结果,如果添加的索引允许ref方式访问该物化表,会更好的减少在查询执行时读取的数据量。考虑下面的查询:

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
      ON t1.f1=derived_t2.f1;

     优化器在derived_t2派生表f1列构造一个索引,如果这样允许ref方式来最小化执行计划的话费,添加索引,优化器对待物化的派生表和平常的带index表一样,添加index的花费比起查询没有Index的执行无不足道,如果ref方式比其他访问方式花费更多,index不会添加,优化器不会做任何优化。

<======================分割线==================================>

From子查询形式:

SELECT ... FROM (subquery) [AS] name ...

 [AS] Name 语句是强制的(必须加上的), 因为From语句中每个表必须有一个名字. 子查询中的每个select列必须有一个唯一的名字。

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,‘1‘,1.0);
INSERT INTO t1 VALUES (2,‘2‘,2.0);
SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;

   Result: 2, ‘2‘, 4.0.

   另一个例子:目的是想得到一组集合和的平均值。 下面的语句并不起作用:

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

正确格式:

SELECT AVG(sum_column1)
  FROM (SELECT SUM(column1) AS sum_column1
        FROM t1 GROUP BY column1) AS t1;

      From语句中的子查询会返回一个标量一列,一行或者一个表。from语句中的子查询不能是相关子查询,除非有on 或者join关键字.

      在Mysql5.6.3之前,from语句中的子查询会在explain语句中执行(派生临时表会被物化)。因为上层查询需要得到所有表的信息在优化阶段。在某些情况下用explain select语句会修改表数据,如果外部查询访问所有表,并且内部查询调用一个修改表数据行的存储方法,见下:

mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.39 sec)

mysql> create table t2(c1 int);
Query OK, 0 rows affected (0.26 sec)

   现在创建一个修改t2表的存储方法;

mysql> delimiter //
mysql> create function f1(p1 int) returns int
    ->   begin
    ->       insert into t2 values(p1);
    ->       return p1;
    ->   end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

   直接引用该存储方法在一个explain select 语句中不会对t2有任何作用;

mysql> select * from t2;
Empty set (0.00 sec)

mysql> explain select f1(5);
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

 

    这是因为select语句没用引用任何表, 可以在table和extra列中输出看到:

mysql> explain select now() as a1,(select f1(5)) as a2;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1249 | Select 2 was reduced during optimization |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)

 

    然而, 当外部select引用任何表时,优化器会执行子查询:

mysql> explain select * from t1 as a1,(select f1(5)) as a2;
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 | NULL           |
|  1 | PRIMARY     | a1         | ALL    | NULL          | NULL | NULL    | NULL |    1 | NULL           |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
3 rows in set (0.02 sec)

mysql> select * from t2;
+------+
| c1   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

 

    这也意味着一个explain select语句(如上)也会需要一段时间来执行.

本类排行

今日推荐

热门手游