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

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

    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. +-------------------------------+-----------+-----------+---------------------------------------+-------------------------------------+

    同样，该视图中的谓词被下推至基表：

    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 使用的索引可以同时满足视图定义和语句本身，如以下组合索引所示：

    3. +--------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------+
    4. +--------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------+
    5. | IndexLookUp_13                 | 63725.48 | root      |                                                       |                                                       |
    6. | ├─IndexRangeScan_11(Build)     | 63725.48 | cop[tikv] | table:trips, index:bike_number(bike_number, duration) | range:("W00950" 3600,"W00950" +inf], keep order:false |
    7. | └─TableRowIDScan_12(Probe)     | 63725.48 | cop[tikv] | table:trips                                           | keep order:false                                      |
    8. +--------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------+
    9. 3 rows in set (0.00 sec)
    11. +-------------------------------+----------+-----------+-------------------------------------------------------+---------------------------------------------+
    12. | id                            | estRows  | task      | access object                                         | operator info                               |
    13. +-------------------------------+----------+-----------+-------------------------------------------------------+---------------------------------------------+
    14. | IndexLookUp_10                | 19117.64 | root      |                                                       |                                             |
    15. | ├─IndexRangeScan_8(Build)     | 19117.64 | cop[tikv] | table:trips, index:bike_number(bike_number, duration) | range:["W00950","W00950"], keep order:false |
    16. | └─TableRowIDScan_9(Probe)     | 19117.64 | cop[tikv] | table:trips                                           | keep order:false                            |
    17. +-------------------------------+----------+-----------+-------------------------------------------------------+---------------------------------------------+
    18. 3 rows in set (0.00 sec)