用 EXPLAIN 查看带视图的 SQL 执行计划

EXPLAIN 语句返回的结果会显示视图引用的表，而不是视图本身的名称。这是因为视图是一张虚拟表，本身并不存储任何数据。视图的定义会和查询语句的其余部分在 SQL 优化过程中进行合并。

示例

我们这里准备一个简单的示例，帮助你理解使用 EXPLAIN 解读 VIEW 的执行计划。

  1. > drop table if exists t1;
  2. > create table t1 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
  3. > insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
  4. > insert into t1 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
  5. > insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
  6. > insert into t1 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
  7. > insert into t1 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
  8. > insert into t1 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
  9. > insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
  10. > insert into t1 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
  11. > insert into t1 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');
  13. > drop table if exists t2;
  14. > create table t2 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
  15. > insert into t2 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
  16. > insert into t2 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
  17. > insert into t2 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
  18. > insert into t2 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
  19. > insert into t2 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
  20. > insert into t2 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
  21. > insert into t2 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
  22. > insert into t2 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
  23. > insert into t2 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');
  25. > create view v1 as select * from (select * from t1) sub where id > 4;
  26. > select * from v1;
  27. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  28. | id   | ti   | si   | bi   | fl       | dl      | de   | ch           | vch            | dd         | dt                  |
  29. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  30. |    5 |    1 |    2 |    6 |    51.26 |    5126 |   51 | byebye       |  is subquery?  | 2022-04-28 | 2022-04-28 22:40:11 |
  31. |    6 |    3 |    2 |    1 |    632.1 |    6321 |  632 | good night   | maybe subquery | 2022-04-28 | 2022-04-28 22:40:11 |
  32. |    7 |    4 |    4 |    3 |  7443.11 |  744311 | 7443 | yes          | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
  33. |    8 |    7 |    5 |    8 |     8758 |  875800 | 8758 | nice to meet | just subquery  | 2022-04-28 | 2022-04-28 22:40:11 |
  34. |    9 |    8 |    4 |    9 | 9849.312 | 9849312 | 9849 | see you      | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
  35. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  36. 5 rows in set (0.01 sec)

如上述示例所示，新建了一个命名为 v1 的 VIEW，并查询 v1 的结果。那么我们看一下这个视图的执行过程：

  1. > explain select * from v1;
  2. +--------------------------------------------------------------+
  3. | QUERY PLAN                                                   |
  4. +--------------------------------------------------------------+
  5. | Project                                                      |
  6. |   ->  Project                                                |
  7. |         ->  Project                                          |
  8. |               ->  Table Scan on db1.t1                       |
  9. |                     Filter Cond: (CAST(t1.id AS BIGINT) > 4) |
  10. +--------------------------------------------------------------+
  11. 5 rows in set (0.00 sec)

可以看到 Project 为这次查询过程中的执行顺序的父节点，首先是从缩进最多的子节点开始计算，完成后 “流入” 它的上层父节点，最终 “流入” Project 父节点。

先执行：

  • Filter Cond：过滤条件

再执行：

  • Table Scan：对某个全表进行扫描

下面的查询的执行方式与上述执行方式类似：

  1. > explain select * from (select * from t1) sub where id > 4;
  2. +--------------------------------------------------------+
  3. | QUERY PLAN                                             |
  4. +--------------------------------------------------------+
  5. | Project                                                |
  6. |   ->  Project                                          |
  7. |         ->  Table Scan on db1.t1                       |
  8. |               Filter Cond: (CAST(t1.id AS BIGINT) > 4) |
  9. +--------------------------------------------------------+
  10. 4 rows in set (0.03 sec)