MySQL DDL操作--------視圖最佳實戰

標籤：mysql dba   view   

1. 背景

   * 視圖是資料庫中的虛擬表。包含一系列帶有名稱的行和列資料。視圖是從一個或者多個表中匯出的，視圖的行為與表非常相似，使用者可以使用SELECT語句查詢資料，以及使用INSERT、UPDATE和DELETE修改記錄，視圖使使用者操作更加方便而且保障資料庫系統的安全。

   * 視圖一旦定義便儲存在資料庫中，與其對應的資料並沒有像表那樣在資料庫中再儲存一份，通過視圖看到的資料只是存放在基本表中的資料。對視圖的操作與對錶的操作一樣，可以對其進行查詢、修改和刪除。當對通過視圖看到的資料進行修改時，相應的基本表的資料也要發生變化，同時若基本表的資料放生變化，則這種變化也會自動地反應到視圖中。

2. 視圖作用

   * 使查詢變得很清晰，視圖中存放的資料就是我們所要的資料，並且可以簡化使用者操作。

   * 讓資料更安全，視圖中的資料，不存在視圖中，還是在基本表裡面，通過視圖這層關係，我們可以有效保護我們的重要資料

     * 提高表的邏輯獨立性，視圖可以屏蔽原有表結構變化帶來的影響

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M00/9A/ED/wKiom1lcTivizFMPAAAkdBTBCTk724.jpg" title="view.jpg" alt="wKiom1lcTivizFMPAAAkdBTBCTk724.jpg" />

3. 檢視類型

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

   * TEMPTABLE: 將視圖的結果將被置於暫存資料表中，然後使用它執行語句。

   * UNDEFINED：預設所使用的演算法。MySQL更傾向於MERGE而不是TEMPTABLE，這是因為MERGE通常更有效。

4. 檢視類型例子

   * 建立基表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)

   * 對基表 users 插入資料

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

   * 查看基表資料

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

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

   * 分析查看 vusers1 視圖執行計畫 

        [ 未使用暫存資料表 ]

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

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

   * 分析查看 vusers2 視力執行計畫

     [ 未使用暫存資料表 ]

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

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

   * 分析查看 vusers3 視力執行計畫

     [ 使用了暫存資料表 ]

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)

6. 總結

以需求驅動技術，技術本身沒有優略之分，只有業務之分。

本文出自 “sea” 部落格，請務必保留此出處http://lisea.blog.51cto.com/5491873/1944679

MySQL DDL操作--------視圖最佳實戰