MySQL數(shù)據(jù)庫(kù)的視圖、存儲(chǔ)過程和存儲(chǔ)引擎

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

一、前言

MySQL 視圖（View）是一種虛擬存在的表，同真實(shí)表一樣，視圖也由列和行構(gòu)成，但視圖并不實(shí)際存在于數(shù)據(jù)庫(kù)中。行和列的數(shù)據(jù)來(lái)自于定義視圖的查詢中所使用的表，并且還是在使用視圖時(shí)動(dòng)態(tài)生成的。

二、MySQL視圖

2.1視圖作用

我們?cè)谑褂肧QL語(yǔ)句進(jìn)行多表查詢的時(shí)候的命令是非常冗長(zhǎng)而麻煩的，如果說這樣的操作還非常多的使用的情況下就會(huì)加大工作人員的工作量，畢竟不能保證如此長(zhǎng)的代碼不會(huì)寫錯(cuò)，并且多次進(jìn)行如此復(fù)雜的查詢也會(huì)造成服務(wù)器資源占用比變大的問題，那么我們有什么好的辦法解決這樣的問題呢？

其實(shí)，我們可以將這些需要經(jīng)常查詢的數(shù)據(jù)內(nèi)容（可能存在于多個(gè)表中）進(jìn)行匯總到一個(gè)虛擬的表中，既方便了工作人員查詢，也減輕了服務(wù)器的負(fù)擔(dān)，并且在節(jié)約磁盤空間的同時(shí)也可以支持?jǐn)?shù)據(jù)的動(dòng)態(tài)變化，而這樣的表就是“視圖”。

其實(shí)視圖是一種邏輯表，本身并不存放數(shù)據(jù)。而是作為一個(gè)select語(yǔ)句保存咋數(shù)據(jù)字典（可以理解為一個(gè)容器）中。通過視圖，可以展現(xiàn)基表的備份數(shù)據(jù)；視圖數(shù)據(jù)來(lái)自定義視圖的查詢中使用的表，使用視圖動(dòng)態(tài)生成。

基表：用來(lái)創(chuàng)建視圖的表base table

2.2視圖的特點(diǎn)及優(yōu)缺點(diǎn)

視圖所占資源較少，就好比是水中月，實(shí)際并不存在，但是會(huì)根據(jù)基表的變化而產(chǎn)生變化。

優(yōu)點(diǎn)：

1）簡(jiǎn)單：使用視圖的用戶完全不需要關(guān)心后面對(duì)應(yīng)的表的結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件，對(duì)用戶來(lái)說已經(jīng)是過濾好的復(fù)合條件的結(jié)果集。

2）安全：使用視圖的用戶只能訪問他們被允許查詢的結(jié)果集，對(duì)表的權(quán)限管理并不能限制到某個(gè)行某個(gè)列，但是通過視圖就可以簡(jiǎn)單的實(shí)現(xiàn)。

3）數(shù)據(jù)獨(dú)立：一旦視圖的結(jié)構(gòu)確定了，可以屏蔽表結(jié)構(gòu)變化對(duì)用戶的影響，源表增加列對(duì)視圖沒有影響；源表修改列名，則可以通過修改視圖來(lái)解決，不會(huì)造成對(duì)訪問者的影響。

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

缺點(diǎn)：

1）性能差：數(shù)據(jù)庫(kù)必須把視圖查詢轉(zhuǎn)化成對(duì)基本表的查詢，如果這個(gè)視圖是由一個(gè)復(fù)雜的多表查詢所定義，那么，即使是視圖的一個(gè)簡(jiǎn)單查詢，數(shù)據(jù)庫(kù)也要把它變成一個(gè)復(fù)雜的結(jié)合體，需要花費(fèi)一定的時(shí)間。

2）修改限制：當(dāng)用戶試圖修改視圖的某些信息時(shí)，數(shù)據(jù)庫(kù)必須把它轉(zhuǎn)化為對(duì)基本表的某些信息的修改，對(duì)于簡(jiǎn)單的視圖來(lái)說，這是很方便的，但是，對(duì)于比較復(fù)雜的試圖，可能是不可修改的。

2.3簡(jiǎn)述視圖算法

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

• undefined：未定義（默認(rèn)的），這不是一種實(shí)際使用的算法，而是一個(gè)“推卸責(zé)任”的算法。在未定義的情況下，告訴系統(tǒng)，視圖沒有定義算法，請(qǐng)自己選擇。
• temptable：臨時(shí)表算法，系統(tǒng)先執(zhí)行視圖的select語(yǔ)句，后執(zhí)行外部查詢語(yǔ)句。
• merge：合并算法，系統(tǒng)先將視圖對(duì)應(yīng)的select語(yǔ)句與外部查詢視圖的select語(yǔ)句進(jìn)行合并，然后再執(zhí)行。此算法比較高效，且在未定義算法的時(shí)候，經(jīng)常會(huì)默認(rèn)選擇此算法。

2.4創(chuàng)建視圖命令

創(chuàng)建一般視圖命令格式：

create view 視圖名稱 as select +內(nèi)容

三、MySQL存儲(chǔ)過程（偏向軟件開發(fā)方向）

3.1何為存儲(chǔ)過程？

簡(jiǎn)單的說，就是一組SQL語(yǔ)句集，功能強(qiáng)大，可以實(shí)現(xiàn)一些比較復(fù)雜的邏輯功能，類似于JAVA語(yǔ)言中的方法；

備注:存儲(chǔ)過程跟觸發(fā)器有點(diǎn)類似，都是一組SQL集，但是存儲(chǔ)過程是主動(dòng)調(diào)用的，且功能比觸發(fā)器更加強(qiáng)大，觸發(fā)器是某件事觸發(fā)后自動(dòng)調(diào)用；

3.2存儲(chǔ)過程特點(diǎn)

有輸入輸出參數(shù)，可以聲明變量，有if/else, case,while等控制語(yǔ)句，通過編寫存儲(chǔ)過程，可以實(shí)現(xiàn)復(fù)雜的邏輯功能；

函數(shù)的普遍特性：模塊化，封裝，代碼復(fù)用；
  速度快，只有首次執(zhí)行需經(jīng)過編譯和優(yōu)化步驟，后續(xù)被調(diào)用可以直接執(zhí)行，省去以上步驟；

四、MySQL存儲(chǔ)引擎

4.1存儲(chǔ)引擎介紹

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

這些不同的技術(shù)以及與之相應(yīng)的相關(guān)功能在MySQL中被稱作為“存儲(chǔ)引擎”。MySQL提供了多個(gè)不同的存儲(chǔ)引擎，可以預(yù)先設(shè)置或者在MySQL服務(wù)器中啟用。

4.2MySQL的體系架構(gòu)

以上主要由6部分組成，主要有各種組件以及存儲(chǔ)引擎和文件系統(tǒng)。我們做簡(jiǎn)單的介紹

Connectors:連接組件，需要有驅(qū)動(dòng)支持；主要用于使用不同的語(yǔ)言代碼程序和MySQL的交互

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

Management Service & Utilities：管理服務(wù)與工具組件；例如進(jìn)行備份恢復(fù)、MySQL復(fù)制、集群等；

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

Parser：查詢解析器；當(dāng)SQL命令語(yǔ)句傳遞到解析器的時(shí)候會(huì)被解析器驗(yàn)證和解析（權(quán)限、語(yǔ)法結(jié)構(gòu)）；

Optimizer:查詢優(yōu)化器；SQL語(yǔ)句在執(zhí)行之前使用查詢優(yōu)化器對(duì)查詢進(jìn)行優(yōu)化；舉個(gè)例子：

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

1）這個(gè)select語(yǔ)句查詢會(huì)先根據(jù)where語(yǔ)句進(jìn)行選?。?/p>

2）其次根據(jù)id和name進(jìn)行屬性投影；

3）結(jié)合兩個(gè)查詢條件輸出最終的查詢結(jié)果

Caches &  Buffers：緩存；當(dāng)查詢緩存中有命中的查詢結(jié)果，查詢語(yǔ)句就可以直接去查詢緩存中取數(shù)據(jù)；

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

4.3MyISAM存儲(chǔ)引擎

4.3.1MyISAM的介紹

MyISAM存儲(chǔ)引擎是MySQL數(shù)據(jù)庫(kù)系統(tǒng)5.5版本之前的默認(rèn)存儲(chǔ)引擎。考慮到的問題是：查詢的次數(shù)遠(yuǎn)遠(yuǎn)大于更新的次數(shù)，因此需要執(zhí)行讀取操作數(shù)據(jù)速度比較快，并且不需要占用大量的內(nèi)存和存儲(chǔ)資源。

MyISAM管理非事務(wù)表，提供索引和字段管理，且擁有表格鎖定機(jī)制從而優(yōu)化多個(gè)并發(fā)的讀寫操作。

4.3.2MyISAM的特點(diǎn)

1. 不支持事務(wù)；

2. 表格鎖定機(jī)制，數(shù)據(jù)在更新時(shí)鎖定整個(gè)表；

3. 數(shù)據(jù)庫(kù)在讀寫過程中互相阻塞；

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

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

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

7. 存儲(chǔ)文件為

   1）.frm 文件存儲(chǔ)表定義；

   2）.MYD 數(shù)據(jù)文件擴(kuò)展名

   3）.MYI 索引文件擴(kuò)展名

4.3.2MyISAM的適用場(chǎng)景

1. 業(yè)務(wù)無(wú)需支持事務(wù)；
2. 單方面讀取較多的業(yè)務(wù)，或單方面寫數(shù)據(jù)的業(yè)務(wù)；
3. 并發(fā)訪問少的業(yè)務(wù)；
4. 數(shù)據(jù)修改較少的業(yè)務(wù)；
5. 服務(wù)器硬件資源較低

4.4InnoDB存儲(chǔ)引擎

4.4.1InnoDB的介紹

InnoDB存儲(chǔ)引擎是MySQL數(shù)據(jù)庫(kù)系統(tǒng)535版本之后的默認(rèn)存儲(chǔ)引擎，是為了解決或者優(yōu)化MyISAM存儲(chǔ)引擎不足之處而產(chǎn)生的。

可以這么說，InnoDB存儲(chǔ)引擎是為處理巨大數(shù)據(jù)量時(shí)的最大性能而設(shè)計(jì)的。

4.4.2InnoDB的特點(diǎn)

1. 支持事務(wù)，支持4個(gè)事務(wù)隔離級(jí)別；
2. 行級(jí)鎖定，除了全表掃描是表級(jí)鎖定；
3. 讀寫阻塞與事務(wù)隔離級(jí)別相關(guān)；
4. 緩存高效，既能緩存索引也能緩存數(shù)據(jù)；
5. 表與主鍵以簇的方式存儲(chǔ)；
6. 支持分區(qū)、表空間；
7. 支持外鍵約束；

4.4.3InnoDB的適用場(chǎng)景

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

五、如何配置合適的存儲(chǔ)引擎？

5.1配置修改存儲(chǔ)引擎的步驟

根據(jù)需求選擇適合的存儲(chǔ)引擎，然后考慮如何修改；

（1）查看數(shù)據(jù)庫(kù)可以配置的存儲(chǔ)引擎類型；

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

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

5.2具體的操作步驟

5.2.1查看數(shù)據(jù)庫(kù)可以配置的存儲(chǔ)引擎類型；

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列表示當(dāng)前版本的MySQL所支持的引擎類型；

Support列表示對(duì)應(yīng)引擎是否可以使用，DEFAULT表示默認(rèn)使用的類型此版本（MySQL5.7.17）是InnoDB；

Transactions列表示對(duì)應(yīng)引擎是否支持事務(wù)；

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

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命令也可以查看表的存儲(chǔ)引擎

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配置存儲(chǔ)引擎為所選擇的類型

使用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)

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

另外可以在創(chuàng)建表的時(shí)候就指定存儲(chǔ)類型：create table test （id int） engine=MyISAM；

看完上述內(nèi)容，你們對(duì)MySQL數(shù)據(jù)庫(kù)的視圖、存儲(chǔ)過程和存儲(chǔ)引擎有進(jìn)一步的了解嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀。