📣

TiDB Cloud Serverless 现已更名为
Starter
!此页面由 AI 自动翻译,英文原文请见
此处。

EXPLAIN Statements Using Views

EXPLAIN 显示的是 view 所引用的表和索引,而不是视图本身的名称。这是因为视图只是虚拟表,并不存储任何数据。视图的定义和语句的其余部分在 SQL 优化过程中会合并在一起。

bikeshare example database 可以看到,以下两个查询的执行方式类似:

ALTER TABLE trips ADD INDEX (duration); CREATE OR REPLACE VIEW long_trips AS SELECT * FROM trips WHERE duration > 3600; EXPLAIN SELECT * FROM long_trips; EXPLAIN SELECT * FROM trips WHERE duration > 3600;
Query OK, 0 rows affected (2 min 10.11 sec) Query OK, 0 rows affected (0.13 sec) +--------------------------------+------------+-----------+---------------------------------------+-------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+------------+-----------+---------------------------------------+-------------------------------------+ | IndexLookUp_12 | 6372547.67 | root | | | | ├─IndexRangeScan_10(Build) | 6372547.67 | cop[tikv] | table:trips, index:duration(duration) | range:(3600,+inf], keep order:false | | └─TableRowIDScan_11(Probe) | 6372547.67 | cop[tikv] | table:trips | keep order:false | +--------------------------------+------------+-----------+---------------------------------------+-------------------------------------+ 3 rows in set (0.00 sec) +-------------------------------+-----------+-----------+---------------------------------------+-------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+-----------+-----------+---------------------------------------+-------------------------------------+ | IndexLookUp_10 | 833219.37 | root | | | | ├─IndexRangeScan_8(Build) | 833219.37 | cop[tikv] | table:trips, index:duration(duration) | range:(3600,+inf], keep order:false | | └─TableRowIDScan_9(Probe) | 833219.37 | cop[tikv] | table:trips | keep order:false | +-------------------------------+-----------+-----------+---------------------------------------+-------------------------------------+ 3 rows in set (0.00 sec)

同样,视图中的谓词会被下推到基础表中:

EXPLAIN SELECT * FROM long_trips WHERE bike_number = 'W00950'; EXPLAIN SELECT * FROM trips WHERE bike_number = 'W00950';
+--------------------------------+---------+-----------+---------------------------------------+---------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+---------------------------------------+---------------------------------------------------+ | IndexLookUp_14 | 3.33 | root | | | | ├─IndexRangeScan_11(Build) | 3333.33 | cop[tikv] | table:trips, index:duration(duration) | range:(3600,+inf], keep order:false, stats:pseudo | | └─Selection_13(Probe) | 3.33 | cop[tikv] | | eq(bikeshare.trips.bike_number, "W00950") | | └─TableRowIDScan_12 | 3333.33 | cop[tikv] | table:trips | keep order:false, stats:pseudo | +--------------------------------+---------+-----------+---------------------------------------+---------------------------------------------------+ 4 rows in set (0.00 sec) +-------------------------+-------------+-----------+---------------+-------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+-------------+-----------+---------------+-------------------------------------------+ | TableReader_7 | 43.00 | root | | data:Selection_6 | | └─Selection_6 | 43.00 | cop[tikv] | | eq(bikeshare.trips.bike_number, "W00950") | | └─TableFullScan_5 | 19117643.00 | cop[tikv] | table:trips | keep order:false | +-------------------------+-------------+-----------+---------------+-------------------------------------------+ 3 rows in set (0.00 sec)

在上面第一个语句中,你可以看到索引被用来满足视图定义,然后在 TiDB 读取表行时应用了 bike_number = 'W00950'。在第二个语句中,没有索引满足语句,因此使用了 TableFullScan

TiDB 会利用既满足视图定义又满足语句的索引。考虑以下复合索引:

ALTER TABLE trips ADD INDEX (bike_number, duration); EXPLAIN SELECT * FROM long_trips WHERE bike_number = 'W00950'; EXPLAIN SELECT * FROM trips WHERE bike_number = 'W00950';
Query OK, 0 rows affected (2 min 31.20 sec) +--------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------+ | IndexLookUp_13 | 63725.48 | root | | | | ├─IndexRangeScan_11(Build) | 63725.48 | cop[tikv] | table:trips, index:bike_number(bike_number, duration) | range:("W00950" 3600,"W00950" +inf], keep order:false | | └─TableRowIDScan_12(Probe) | 63725.48 | cop[tikv] | table:trips | keep order:false | +--------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------+ 3 rows in set (0.00 sec) +-------------------------------+----------+-----------+-------------------------------------------------------+---------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+----------+-----------+-------------------------------------------------------+---------------------------------------------+ | IndexLookUp_10 | 19117.64 | root | | | | ├─IndexRangeScan_8(Build) | 19117.64 | cop[tikv] | table:trips, index:bike_number(bike_number, duration) | range:["W00950","W00950"], keep order:false | | └─TableRowIDScan_9(Probe) | 19117.64 | cop[tikv] | table:trips | keep order:false | +-------------------------------+----------+-----------+-------------------------------------------------------+---------------------------------------------+ 3 rows in set (0.00 sec)

在第一个语句中,TiDB 能够同时利用 (bike_number, duration) 这两个部分的复合索引。而在第二个语句中,只使用了索引 (bike_number, duration) 的第一个部分 bike_number

文档内容是否有帮助?