我有一个问题。如果我索引了一个特定的视图表用作引用的基表，在运行在云SQL上的MySQL上查询视图表的性能是否会提高？对于上下文，有两个表B和V，其中V是从基表B引用数据的视图表。如果我在B上的某个列x和y上添加索引，它是否有助于提高V上的查询性能，其中我使用x和y作为过滤器？
sr4lhrrt1#
一个快速的实验可以证实这一点：
mysql> create table B (id serial primary key, x int, y int);Query OK, 0 rows affected (0.03 sec)mysql> create view V as select * from B;Query OK, 0 rows affected (0.01 sec)mysql> explain select * from V where x = 123;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> alter table B add key (x);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from V where x = 123;+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | b | NULL | ref | x | x | 5 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
mysql> create table B (id serial primary key, x int, y int);
Query OK, 0 rows affected (0.03 sec)
mysql> create view V as select * from B;
Query OK, 0 rows affected (0.01 sec)
mysql> explain select * from V where x = 123;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
1 row in set, 1 warning (0.00 sec)
mysql> alter table B add key (x);
Records: 0 Duplicates: 0 Warnings: 0
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | b | NULL | ref | x | x | 5 | const | 1 | 100.00 | NULL |
https://dev.mysql.com/doc/refman/8.0/en/view-restrictions.html说：索引可用于使用合并算法处理的视图。但是，使用temptable算法处理的视图无法利用其基础表上的索引（尽管在生成临时表期间可以使用索引）。阅读https://dev.mysql.com/doc/refman/8.0/en/view-algorithms.html了解视图算法的更多细节。
1条答案
按热度按时间sr4lhrrt1#
一个快速的实验可以证实这一点：
https://dev.mysql.com/doc/refman/8.0/en/view-restrictions.html说：
索引可用于使用合并算法处理的视图。但是，使用temptable算法处理的视图无法利用其基础表上的索引（尽管在生成临时表期间可以使用索引）。
阅读https://dev.mysql.com/doc/refman/8.0/en/view-algorithms.html了解视图算法的更多细节。