MySQL DDL操作--------視圖更優(yōu)實(shí)戰(zhàn)

作者：asd1123509133 2017-07-07 17:11:21

視圖是數(shù)據(jù)庫中的虛擬表。包含一系列帶有名稱的行和列數(shù)據(jù)。視圖是從一個(gè)或者多個(gè)表中導(dǎo)出的，視圖的行為與表非常相似，用戶可以使用SELECT語句查詢數(shù)據(jù)，以及使用INSERT、UPDATE和DELETE修改記錄，視圖使用戶操作更加方便而且保障數(shù)據(jù)庫系統(tǒng)的安全。

【引自asd1123509133的博客】 1. 背景

* 視圖是數(shù)據(jù)庫中的虛擬表。包含一系列帶有名稱的行和列數(shù)據(jù)。視圖是從一個(gè)或者多個(gè)表中導(dǎo)出的，視圖的行為與表非常相似，用戶可以使用SELECT語句查詢數(shù)據(jù)，以及使用INSERT、UPDATE和DELETE修改記錄，視圖使用戶操作更加方便而且保障數(shù)據(jù)庫系統(tǒng)的安全。

* 視圖一旦定義便存儲(chǔ)在數(shù)據(jù)庫中，與其對(duì)應(yīng)的數(shù)據(jù)并沒有像表那樣在數(shù)據(jù)庫中再存儲(chǔ)一份，通過視圖看到的數(shù)據(jù)只是存放在基本表中的數(shù)據(jù)。對(duì)視圖的操作與對(duì)表的操作一樣，可以對(duì)其進(jìn)行查詢、修改和刪除。當(dāng)對(duì)通過視圖看到的數(shù)據(jù)進(jìn)行修改時(shí)，相應(yīng)的基本表的數(shù)據(jù)也要發(fā)生變化，同時(shí)若基本表的數(shù)據(jù)放生變化，則這種變化也會(huì)自動(dòng)地反應(yīng)到視圖中。

2. 視圖作用

* 使查詢變得很清晰，視圖中存放的數(shù)據(jù)就是我們所要的數(shù)據(jù)，并且可以簡(jiǎn)化用戶操作。

* 讓數(shù)據(jù)更安全，視圖中的數(shù)據(jù)，不存在視圖中，還是在基本表里面，通過視圖這層關(guān)系，我們可以有效的保護(hù)我們的重要數(shù)據(jù)

* 提高表的邏輯獨(dú)立性，視圖可以屏蔽原有表結(jié)構(gòu)變化帶來的影響

3. 視圖類型

* MERGE: 將引用視圖的語句的文本與視圖定義合并起來，使得視圖定義的某一部分取代語句的對(duì)應(yīng)部分。

* TEMPTABLE: 將視圖的結(jié)果將被置于臨時(shí)表中，然后使用它執(zhí)行語句。

* UNDEFINED：默認(rèn)所使用的算法。MySQL更傾向于MERGE而不是TEMPTABLE，這是因?yàn)镸ERGE通常更有效。

4. 視圖類型例子

* 創(chuàng)建基表users

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)

* 對(duì)基表 users 插入數(shù)據(jù)

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

* 查看基表數(shù)據(jù)

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)

* 創(chuàng)建用戶id和name vusers1視圖，類型為UNDEFINED

mysql> CREATE ALGORITHM=UNDEFINED VIEW vusers1 AS SELECT id, name FROM users; 
Query OK, 0 rows affected (0.01 sec)

* 分析查看 vusers1 視圖執(zhí)行計(jì)劃

[ 未使用臨時(shí)表 ]

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)

* 創(chuàng)建用戶id和name vusers2視圖，類型為merge

mysql> CREATE ALGORITHM=MERGE VIEW vuser2 AS SELECT id, name FROM users; 
Query OK, 0 rows affected (0.03 sec)

* 分析查看 vusers2 視力執(zhí)行計(jì)劃

[ 未使用臨時(shí)表 ]

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)

* 創(chuàng)建用戶id和name vusers3視圖，類型為TEMPTABLE

mysql> CREATE ALGORITHM=TEMPTABLE VIEW vuser3 AS SELECT id, name FROM users; 
Query OK, 0 rows affected (0.19 sec)

* 分析查看 vusers3 視力執(zhí)行計(jì)劃

[ 使用了臨時(shí)表 ]

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 查看視圖基本信息

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 查看視圖基本信息

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 查看視圖信息

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)