MySQL DDL Operation--------View Best Practice

1. Background

* view is a virtual table in a database. Contains a series of row and column data with names. Views are exported from one or more tables, and the behavior of the view is very similar to a table where the user can query data using the SELECT statement and modify records using INSERT, UPDATE, and delete, which makes the user's operation more convenient and ensures the security of the database system.

* Once the view is defined, it is stored in the database, and its corresponding data is not stored in the database as a table, and the data seen through the view is only the data stored in the base table. You can query, modify, and delete operations on a view as you would for a table. When you make changes to the data that you see through the view, the data for the underlying table changes, and the changes are automatically reflected in the view if the underlying table's data is released.

2. View function

* make the query very clear, the data stored in the view is the data we want, and can simplify the user operation.

* make the data more secure, the data in the view, not in the view, or in the basic table, through the view of this layer of relationship, we can effectively protect our important data

* Improve the logical independence of the table, the view can mask the impact of changes in the original table structure

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

3. View Type

* Merge: Merges the text of the statement referencing the view with the view definition so that some part of the view definition supersedes the corresponding part of the statement.

* temptable: The result of the view will be placed in the staging table and then used to execute the statement.

* UNDEFINED: The algorithm used by default. MySQL prefers merge to temptable, because the merge is usually more efficient.

4. View type examples

* Create base table users

Mysql> CREATE TABLE users, id BIGINT PRIMARY KEY not NULL auto_increment, name VARCHAR (+) NOT NULL, -A sex ENUM (' M ', ' F ') not NULL,--------------------"Engine=innodb charset=utf8mb4 Query OK, 0 rows affected (0.15 sec)

* Insert data to the base table users

Mysql> INSERT into users VALUES (null, ' Tom ', ' m ', ' at '), (null, ' Jak ', ' F ', +), (null, ' jus ', ' m ', 35); Query OK, 3 rows affected (0.04 sec) Records:3 duplicates:0 warnings:0

* View base table Data

Mysql> SELECT * FROM users;+----+------+-----+-----+| ID | name | sex |  Age |+----+------+-----+-----+| 1 | Tom |  M |  23 | | 2 | Jak |  F |  32 | | 3 | Jus |  M | |+----+------+-----+-----+3 rows in Set (0.00 sec)

* create User ID and name vusers1 view, type undefined

mysql> CREATE algorithm=undefined VIEW vusers1 as SELECT ID, name from users; Query OK, 0 rows affected (0.01 sec)

* analysis View vusers1 view execution Plan

[temporary table not used]

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) 

* create User ID and name vusers2 view, type merge

mysql> CREATE algorithm=merge VIEW vuser2 as SELECT ID, name from users; Query OK, 0 rows affected (0.03 sec)

* analysis View vusers2 Vision Execution Plan

[ temporary table not used ]

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) 

* create User ID and name VUSERS3 view, type temptable

mysql> CREATE algorithm=temptable VIEW vuser3 as SELECT ID, name from users; Query OK, 0 rows affected (0.19 sec)

* analysis View VUSERS3 Vision Execution Plan

[ temporary table used ]

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&NBSP;SEC)

5. View the View information

* desc View basic Information

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 View basic information

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 view information

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. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1944679

MySQL DDL Operation--------View Best Practice