我的书签
添加书签
移除书签用 EXPLAIN 查看带视图的 SQL 执行计划
EXPLAIN 语句返回的结果会显示视图引用的表和索引，而不是视图本身的名称。这是因为视图是一张虚拟表，本身并不存储任何数据。视图的定义会和查询语句的其余部分在 SQL 优化过程中进行合并。
参考 bikeshare 数据库示例（英文），以下两个示例查询的执行方式类似：
1. ALTER TABLE trips ADD INDEX (duration);
2. CREATE OR REPLACE VIEW long_trips AS SELECT * FROM trips WHERE duration > 3600;
3. EXPLAIN SELECT * FROM long_trips;
4. EXPLAIN SELECT * FROM trips WHERE duration > 3600;
1. Query OK, 0 rows affected (2 min 10.11 sec)
3. Query OK, 0 rows affected (0.13 sec)
5. +--------------------------------+------------+-----------+---------------------------------------+-------------------------------------+
6. | id | estRows | task | access object | operator info |
7. +--------------------------------+------------+-----------+---------------------------------------+-------------------------------------+
8. | IndexLookUp_12 | 6372547.67 | root | | |
9. | ├─IndexRangeScan_10(Build) | 6372547.67 | cop[tikv] | table:trips, index:duration(duration) | range:(3600,+inf], keep order:false |
10. | └─TableRowIDScan_11(Probe) | 6372547.67 | cop[tikv] | table:trips | keep order:false |
11. +--------------------------------+------------+-----------+---------------------------------------+-------------------------------------+
12. 3 rows in set (0.00 sec)
14. +-------------------------------+-----------+-----------+---------------------------------------+-------------------------------------+
15. | id | estRows | task | access object | operator info |
16. +-------------------------------+-----------+-----------+---------------------------------------+-------------------------------------+
17. | IndexLookUp_10 | 833219.37 | root | | |
18. | ├─IndexRangeScan_8(Build) | 833219.37 | cop[tikv] | table:trips, index:duration(duration) | range:(3600,+inf], keep order:false |
19. | └─TableRowIDScan_9(Probe) | 833219.37 | cop[tikv] | table:trips | keep order:false |
20. +-------------------------------+-----------+-----------+---------------------------------------+-------------------------------------+
21. 3 rows in set (0.00 sec)
同样，该视图中的谓词被下推至基表：
1. EXPLAIN SELECT * FROM long_trips WHERE bike_number = 'W00950';
2. EXPLAIN SELECT * FROM trips WHERE bike_number = 'W00950';
1. +--------------------------------+---------+-----------+---------------------------------------+---------------------------------------------------+
2. | id | estRows | task | access object | operator info |
3. +--------------------------------+---------+-----------+---------------------------------------+---------------------------------------------------+
4. | IndexLookUp_14 | 3.33 | root | | |
5. | ├─IndexRangeScan_11(Build) | 3333.33 | cop[tikv] | table:trips, index:duration(duration) | range:(3600,+inf], keep order:false, stats:pseudo |
6. | └─Selection_13(Probe) | 3.33 | cop[tikv] | | eq(bikeshare.trips.bike_number, "W00950") |
7. | └─TableRowIDScan_12 | 3333.33 | cop[tikv] | table:trips | keep order:false, stats:pseudo |
8. +--------------------------------+---------+-----------+---------------------------------------+---------------------------------------------------+
9. 4 rows in set (0.00 sec)
11. +-------------------------+-------------+-----------+---------------+-------------------------------------------+
12. | id | estRows | task | access object | operator info |
13. +-------------------------+-------------+-----------+---------------+-------------------------------------------+
14. | TableReader_7 | 43.00 | root | | data:Selection_6 |
15. | └─Selection_6 | 43.00 | cop[tikv] | | eq(bikeshare.trips.bike_number, "W00950") |
16. | └─TableFullScan_5 | 19117643.00 | cop[tikv] | table:trips | keep order:false |
17. +-------------------------+-------------+-----------+---------------+-------------------------------------------+
18. 3 rows in set (0.00 sec)
执行以上第一条语句时使用了索引，满足视图定义，接着在 TiDB 读取行时应用了 bike_number = 'W00950' 条件。执行以上第二条语句时，不存在满足该语句的索引，因此使用了 TableFullScan。
TiDB 使用的索引可以同时满足视图定义和语句本身，如以下组合索引所示：
1. ALTER TABLE trips ADD INDEX (bike_number, duration);
2. EXPLAIN SELECT * FROM long_trips WHERE bike_number = 'W00950';
3. EXPLAIN SELECT * FROM trips WHERE bike_number = 'W00950';
1. Query OK, 0 rows affected (2 min 31.20 sec)
3. +--------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------+
4. | id | estRows | task | access object | operator info |
5. +--------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------+
6. | IndexLookUp_13 | 63725.48 | root | | |
7. | ├─IndexRangeScan_11(Build) | 63725.48 | cop[tikv] | table:trips, index:bike_number(bike_number, duration) | range:("W00950" 3600,"W00950" +inf], keep order:false |
8. | └─TableRowIDScan_12(Probe) | 63725.48 | cop[tikv] | table:trips | keep order:false |
9. +--------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------+
10. 3 rows in set (0.00 sec)
12. +-------------------------------+----------+-----------+-------------------------------------------------------+---------------------------------------------+
13. | id | estRows | task | access object | operator info |
14. +-------------------------------+----------+-----------+-------------------------------------------------------+---------------------------------------------+
15. | IndexLookUp_10 | 19117.64 | root | | |
16. | ├─IndexRangeScan_8(Build) | 19117.64 | cop[tikv] | table:trips, index:bike_number(bike_number, duration) | range:["W00950","W00950"], keep order:false |
17. | └─TableRowIDScan_9(Probe) | 19117.64 | cop[tikv] | table:trips | keep order:false |
18. +-------------------------------+----------+-----------+-------------------------------------------------------+---------------------------------------------+
19. 3 rows in set (0.00 sec)
在第一条语句中，TiDB 能够使用组合索引的两个部分 (bike_number, duration)。在第二条语句，TiDB 仅使用了索引 (bike_number, duration) 的第一部分 bike_number。
