MySQL數據庫的視圖、存儲過程和存儲引擎

本文主要介紹了MySQL數據庫的視圖和MySQL數據庫的存儲過程，還介紹了MySQL的兩個存儲引擎MyISAM和InnoDB，希望大家通過這篇文章有所收獲。

一、前言

MySQL 視圖（View）是一種虛擬存在的表，同真實表一樣，視圖也由列和行構成，但視圖并不實際存在于數據庫中。行和列的數據來自于定義視圖的查詢中所使用的表，并且還是在使用視圖時動態生成的。

二、MySQL視圖

2.1視圖作用

我們在使用SQL語句進行多表查詢的時候的命令是非常冗長而麻煩的，如果說這樣的操作還非常多的使用的情況下就會加大工作人員的工作量，畢竟不能保證如此長的代碼不會寫錯，并且多次進行如此復雜的查詢也會造成服務器資源占用比變大的問題，那么我們有什么好的辦法解決這樣的問題呢？

其實，我們可以將這些需要經常查詢的數據內容（可能存在于多個表中）進行匯總到一個虛擬的表中，既方便了工作人員查詢，也減輕了服務器的負擔，并且在節約磁盤空間的同時也可以支持數據的動態變化，而這樣的表就是“視圖”。

其實視圖是一種邏輯表，本身并不存放數據。而是作為一個select語句保存咋數據字典（可以理解為一個容器）中。通過視圖，可以展現基表的備份數據；視圖數據來自定義視圖的查詢中使用的表，使用視圖動態生成。

基表：用來創建視圖的表base table

2.2視圖的特點及優缺點

視圖所占資源較少，就好比是水中月，實際并不存在，但是會根據基表的變化而產生變化。

優點：

1）簡單：使用視圖的用戶完全不需要關心后面對應的表的結構、關聯條件和篩選條件，對用戶來說已經是過濾好的復合條件的結果集。

2）安全：使用視圖的用戶只能訪問他們被允許查詢的結果集，對表的權限管理并不能限制到某個行某個列，但是通過視圖就可以簡單的實現。

3）數據獨立：一旦視圖的結構確定了，可以屏蔽表結構變化對用戶的影響，源表增加列對視圖沒有影響；源表修改列名，則可以通過修改視圖來解決，不會造成對訪問者的影響。

總而言之，使用視圖的大部分情況是為了保障數據安全性，提高查詢效率。

缺點：

1）性能差：數據庫必須把視圖查詢轉化成對基本表的查詢，如果這個視圖是由一個復雜的多表查詢所定義，那么，即使是視圖的一個簡單查詢，數據庫也要把它變成一個復雜的結合體，需要花費一定的時間。

2）修改限制：當用戶試圖修改視圖的某些信息時，數據庫必須把它轉化為對基本表的某些信息的修改，對于簡單的視圖來說，這是很方便的，但是，對于比較復雜的試圖，可能是不可修改的。

2.3簡述視圖算法

對于視圖的算法，簡單介紹一下，我們需要在創建視圖的時候指定，基本語法：
create + [algorithm = temptable/merge/undefined] + view + 視圖名 + as + select語句 with check option;
視圖算法，即系統對視圖以及外部查詢視圖的select語句的一種解析方式。視圖算法有三種，分別為：

• undefined：未定義（默認的），這不是一種實際使用的算法，而是一個“推卸責任”的算法。在未定義的情況下，告訴系統，視圖沒有定義算法，請自己選擇。
• temptable：臨時表算法，系統先執行視圖的select語句，后執行外部查詢語句。
• merge：合并算法，系統先將視圖對應的select語句與外部查詢視圖的select語句進行合并，然后再執行。此算法比較高效，且在未定義算法的時候，經常會默認選擇此算法。

2.4創建視圖命令

創建一般視圖命令格式：

create view 視圖名稱 as select +內容

三、MySQL存儲過程（偏向軟件開發方向）

3.1何為存儲過程？

簡單的說，就是一組SQL語句集，功能強大，可以實現一些比較復雜的邏輯功能，類似于JAVA語言中的方法；

備注:存儲過程跟觸發器有點類似，都是一組SQL集，但是存儲過程是主動調用的，且功能比觸發器更加強大，觸發器是某件事觸發后自動調用；

3.2存儲過程特點

有輸入輸出參數，可以聲明變量，有if/else, case,while等控制語句，通過編寫存儲過程，可以實現復雜的邏輯功能；

函數的普遍特性：模塊化，封裝，代碼復用；
  速度快，只有首次執行需經過編譯和優化步驟，后續被調用可以直接執行，省去以上步驟；

四、MySQL存儲引擎

4.1存儲引擎介紹

MySQL中的數據用各種不同的技術存儲在文件或者內存中。而這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并最終提供廣泛的不同的功能和能力。

這些不同的技術以及與之相應的相關功能在MySQL中被稱作為“存儲引擎”。MySQL提供了多個不同的存儲引擎，可以預先設置或者在MySQL服務器中啟用。

4.2MySQL的體系架構

以上主要由6部分組成，主要有各種組件以及存儲引擎和文件系統。我們做簡單的介紹

Connectors:連接組件，需要有驅動支持；主要用于使用不同的語言代碼程序和MySQL的交互

Connection Pool：連接池組件；主要是管理、緩沖用戶的連接，線程處理等需要緩存的需求

Management Service & Utilities：管理服務與工具組件；例如進行備份恢復、MySQL復制、集群等；

SQL Interface：SQL接口；主要接受用戶的SQL命令語句，并且返回用戶需要查詢的結果；

Parser：查詢解析器；當SQL命令語句傳遞到解析器的時候會被解析器驗證和解析（權限、語法結構）；

Optimizer:查詢優化器；SQL語句在執行之前使用查詢優化器對查詢進行優化；舉個例子：

select id，name from where hobby = ‘read’；

1）這個select語句查詢會先根據where語句進行選取；

2）其次根據id和name進行屬性投影；

3）結合兩個查詢條件輸出最終的查詢結果

Caches &  Buffers：緩存；當查詢緩存中有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據；

Plugggable Storage Engines：插入式存儲引擎；是為了管理操作數據（存儲、更新、查詢數據的方法方式）

4.3MyISAM存儲引擎

4.3.1MyISAM的介紹

MyISAM存儲引擎是MySQL數據庫系統5.5版本之前的默認存儲引擎。考慮到的問題是：查詢的次數遠遠大于更新的次數，因此需要執行讀取操作數據速度比較快，并且不需要占用大量的內存和存儲資源。

MyISAM管理非事務表，提供索引和字段管理，且擁有表格鎖定機制從而優化多個并發的讀寫操作。

4.3.2MyISAM的特點

1. 不支持事務；

2. 表格鎖定機制，數據在更新時鎖定整個表；

3. 數據庫在讀寫過程中互相阻塞；

4. 可以通過key_buffer_size來設置緩存索引，提高訪問性能，減少磁盤IO讀寫壓力

5. 速度快且占用資源少；

6. 不支持外鍵約束，只支持全文索引；

7. 存儲文件為

   1）.frm 文件存儲表定義；

   2）.MYD 數據文件擴展名

   3）.MYI 索引文件擴展名

4.3.2MyISAM的適用場景

1. 業務無需支持事務；
2. 單方面讀取較多的業務，或單方面寫數據的業務；
3. 并發訪問少的業務；
4. 數據修改較少的業務；
5. 服務器硬件資源較低

4.4InnoDB存儲引擎

4.4.1InnoDB的介紹

InnoDB存儲引擎是MySQL數據庫系統535版本之后的默認存儲引擎，是為了解決或者優化MyISAM存儲引擎不足之處而產生的。

可以這么說，InnoDB存儲引擎是為處理巨大數據量時的最大性能而設計的。

4.4.2InnoDB的特點

1. 支持事務，支持4個事務隔離級別；
2. 行級鎖定，除了全表掃描是表級鎖定；
3. 讀寫阻塞與事務隔離級別相關；
4. 緩存高效，既能緩存索引也能緩存數據；
5. 表與主鍵以簇的方式存儲；
6. 支持分區、表空間；
7. 支持外鍵約束；

4.4.3InnoDB的適用場景

1. 業務需要事務的支持；
2. 并發量高的業務；
3. 業務數據更新頻繁；微博等
4. 業務數據一致性要求較高：銀行業務
5. 硬件資源較高

五、如何配置合適的存儲引擎？

5.1配置修改存儲引擎的步驟

根據需求選擇適合的存儲引擎，然后考慮如何修改；

（1）查看數據庫可以配置的存儲引擎類型；

（2）查看表正在使用的存儲引擎類型；

（3）配置存儲引擎為所選擇的類型

5.2具體的操作步驟

5.2.1查看數據庫可以配置的存儲引擎類型；

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Engine列表示當前版本的MySQL所支持的引擎類型；

Support列表示對應引擎是否可以使用，DEFAULT表示默認使用的類型此版本（MySQL5.7.17）是InnoDB；

Transactions列表示對應引擎是否支持事務；

5.2.2查看表正在使用的存儲引擎類型；

mysql> show table status from student where name = 'info';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| info | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |            0 |         0 |              5 | 2020-01-08 19:47:38 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)

或者使用create命令也可以查看表的存儲引擎

mysql> show create table info;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info  | CREATE TABLE "info" (
  "id" int(3) NOT NULL AUTO_INCREMENT,
  "name" varchar(6) DEFAULT NULL,
  "score" decimal(5,2) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

5.2.3配置存儲引擎為所選擇的類型

使用alter命令：alter table 表名 engine= 引擎名稱

mysql> alter table info engine=Myisam;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table info;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info  | CREATE TABLE "info" (
  "id" int(3) NOT NULL AUTO_INCREMENT,
  "name" varchar(6) DEFAULT NULL,
  "score" decimal(5,2) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

或者在手工編譯安裝時在MySQL主配置文件/etc/my.cnf的mysqld下添加：default-storage-engine=引擎名稱，之后重啟服務即可。

另外可以在創建表的時候就指定存儲類型：create table test （id int） engine=MyISAM；

看完上述內容，你們對MySQL數據庫的視圖、存儲過程和存儲引擎有進一步的了解嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀。