1. 背景
* 视图是数据库中的虚拟表。包含一系列带有名称的行和列数据。视图是从一个或者多个表中导出的，视图的行为与表非常相似，用户可以使用SELECT语句查询数据，以及使用INSERT、UPDATE和DELETE修改记录，视图使用户操作更加方便而且保障数据库系统的安全。
* 视图一旦定义便存储在数据库中，与其对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化，同时若基本表的数据放生变化，则这种变化也会自动地反应到视图中。
2. 视图作用
* 使查询变得很清晰，视图中存放的数据就是我们所要的数据，并且可以简化用户操作。
* 让数据更安全，视图中的数据，不存在视图中，还是在基本表里面，通过视图这层关系，我们可以有效的保护我们的重要数据
* 提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响
3. 视图类型
* MERGE: 将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
* TEMPTABLE: 将视图的结果将被置于临时表中，然后使用它执行语句。
* UNDEFINED：默认所使用的算法。MySQL更倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效。
4. 视图类型例子
* 创建基表users
|
1
2
3
4
5
6
7
|
mysql>
CREATE
TABLE
users(
-> id
BIGINT
PRIMARY
KEY
NOT
NULL
AUTO_INCREMENT,
->
name
VARCHAR
(64)
NOT
NULL
,
-> sex ENUM(
'M'
,
'F'
)
NOT
NULL
,
-> age
INT
NOT
NULL
-> )ENGINE=INNODB CHARSET=utf8mb4;
Query OK, 0
rows
affected (0.15 sec)
|
* 对基表 users 插入数据
|
1
2
3
|
mysql>
INSERT
INTO
users
VALUES
(
NULL
,
'tom'
,
'M'
, 23), (
NULL
,
'jak'
,
'F'
, 32), (
NULL
,
'jus'
,
'M'
, 35);
Query OK, 3
rows
affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
|
* 查看基表数据
|
1
2
3
4
5
6
7
8
9
|
mysql>
SELECT
*
FROM
users;
+
----+------+-----+-----+
| id |
name
| sex | age |
+
----+------+-----+-----+
| 1 | tom | M | 23 |
| 2 | jak | F | 32 |
| 3 | jus | M | 35 |
+
----+------+-----+-----+
3
rows
in
set
(0.00 sec)
|
* 创建用户id和name vusers1视图，类型为UNDEFINED
|
1
2
|
mysql>
CREATE
ALGORITHM=UNDEFINED
VIEW
vusers1
AS
SELECT
id,
name
FROM
users;
Query OK, 0
rows
affected (0.01 sec)
|
* 分析查看 vusers1 视图执行计划
[ 未使用临时表 ]
|
1
2
3
4
5
6
7
|
mysql> EXPLAIN SELECT * FROM vusers1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|
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 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row
in
set
, 1 warning (0.00 sec)
|
* 创建用户id和name vusers2视图，类型为merge
|
1
2
|
mysql>
CREATE
ALGORITHM=MERGE
VIEW
vuser2
AS
SELECT
id,
name
FROM
users;
Query OK, 0
rows
affected (0.03 sec)
|
* 分析查看 vusers2 视力执行计划
[ 未使用临时表 ]
|
1
2
3
4
5
6
7
|
mysql> EXPLAIN SELECT * FROM vuser2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|
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 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row
in
set
, 1 warning (0.01 sec)
|
* 创建用户id和name vusers3视图，类型为TEMPTABLE
|
1
2
|
mysql>
CREATE
ALGORITHM=TEMPTABLE
VIEW
vuser3
AS
SELECT
id,
name
FROM
users;
Query OK, 0
rows
affected (0.19 sec)
|
* 分析查看 vusers3 视力执行计划
[ 使用了临时表 ]
|
1
2
3
4
5
6
7
8
|
mysql> EXPLAIN
SELECT
*
FROM
vuser3;
+
----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 |
PRIMARY
| <derived2> |
NULL
|
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| 3 | 100.00 |
NULL
|
| 2 | DERIVED | users |
NULL
|
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| 3 | 100.00 |
NULL
|
+
----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2
rows
in
set
, 1 warning (0.00 sec)
|
5. 查看视图信息
* desc 查看视图基本信息
|
1
2
3
4
5
6
7
8
|
mysql> desc vusers1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|
id
| bigint(20) | NO | | 0 | |
| name | varchar(64) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows
in
set
(0.00 sec)
|
* show table status 查看视图基本信息
|
1
2
3
4
5
6
7
|
mysql> show
table
status
like
'vusers1'
;
+
--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
|
Name
| Engine | Version | Row_format |
Rows
| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+
--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| vuser1 |
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
VIEW
|
+
--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row
in
set
(0.00 sec)
|
* show create view 查看视图信息
|
1
2
3
4
5
6
7
|
mysql> show
create
view
vuser1;
+
--------+----------------- ----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
View
|
Create
View
| character_set_client | collation_connection |
+
--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| vuser1 |
CREATE
ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW
`vuser1`
AS
select
`users`.`id`
AS
`id`,`users`.`
name
`
AS
`
name
`
from
`users` | utf8 | utf8_general_ci |
+
--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row
in
set
(0.00 sec)
|
6. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1944679，如需转载请自行联系原作者
