MySQL视图以及作用

什么是视图

视图其实就是一个虚拟表,实际不存储数据。可以理解为对某个查询的封装。内部实现视图的算法有两种:合并算法临时表算法

视图的作用

  1. 某些情况下,视图可以帮助提升性能(谨慎使用)
  2. 封装复杂查询,让SQL查询简单地重用

如何使用

# 例如有这个表users
mysql> create table users(
    ->     name char(64),
    ->     age char(32));

# 创建视图
mysql> create view users_view  AS select name from users where age = '1' with check option;

# 视图查询Explain
mysql> explain select * from users_view;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+


# 更新视图其实就是更新数据表上对应的数据
mysql> update  users_view set name = 3 where name = 1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from users_view;
+------+
| name |
+------+
| 3    |
| 2    |
+------+
2 rows in set (0.00 sec)

mysql> select * from users;
+------+------+
| name | age  |
+------+------+
| 3    | 1    |
| 2    | 1    |
| 2    | 2    |
+------+------+
3 rows in set (0.00 sec)

参考

《高性能MySQL》