為什么MySQL中很少使用視圖功能？
首先MySQL使用視圖有什么好處：
- 清晰簡單，可以讓簡單的語句邏輯更清晰
- 可復用，可以讓部分復雜的sql邏輯多次復用希痴，統(tǒng)一更新
- 安全，可以隱藏掉一些私密的表結構
那么MySQL中的視圖性能如何呢春感？結合以下的MySQL官方文檔看一下視圖的主要算法：
The optional ALGORITHM clause for CREATE VIEW or ALTER VIEW is a MySQL extension to standard SQL. It affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED.
For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.
For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.
For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used.
If no ALGORITHM clause is present, the default algorithm is determined by the value of the derived_merge flag of the optimizer_switch system variable. For additional discussion, see Section 8.2.2.3, “Optimizing Derived Tables, View References, and Common Table Expressions”.
那么可以看到砌创，主要有兩種方式：MERGE和TEMPTABLE
- MERGE
把視圖中的sql合并到查詢sql中，例如官方例子中：
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;
SELECT * FROM v_merge;
以上sql等價于
select * from (SELECT c1, c2 FROM t WHERE c3 > 100);
- TEMPTABLE
臨時表算法是先將視圖查出來的數據保存到一個臨時表中甥厦，查詢的時候查這個臨時表
執(zhí)行計劃分析
CREATE TABLE `user_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL DEFAULT '',
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
先試用MERGE方式：
create or replace ALGORITHM =MERGE view v as select * From user_info;
執(zhí)行以下sql：
mysql> explain SELECT * FROM user_info where id =1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT * FROM v where id =1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可以看到執(zhí)行計劃相同纺铭，并都使用了索引
再試用TEMPTABLE方式：
create or replace ALGORITHM =TEMPTABLE view v as select * From user_info;
mysql> explain SELECT * FROM user_info where id =1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> explain SELECT * FROM v where id =1;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
可以看到寇钉，查詢視圖的時候刀疙，首先使用的是查詢了全表放到臨時表中
總結
使用視圖時，如果使用TEMPTABLE會影響數據庫的優(yōu)化扫倡，比如索引等情況
使用視圖時谦秧，比較不容易看到視圖的邏輯，也不利于開發(fā)人員對sql的優(yōu)化
