MySQL_10 存储引擎和视图

╰半夏微凉° 2023-10-12 08:37 116阅读 0赞

**目录**

一、存储引擎

1.介绍 :

2.分类 :

3.选择 :

二、视图

1.概述 :

2.原理 :

3.使用格式 :

4.代码演示 :

5.应用实例 :

--------------------

## 一、存储引擎 ##

### 1.介绍 :  ###

> **MySQL的表类型由表的存储引擎(Storage Engines)决定**，**主要包括InnoDB, MyISAM, Memory**等等。

### 2.分类 :  ###

> **1° MySQL数据表主要支持六种类型——**
> 
>  **①CSV;**
> 
>  **②Memory ： 数据存放在内存中，速度很快，适合临时表;**
> 
>  **③ARCHIVE : 存档类型的存储引擎，批量插入操作的速度非常高;**
> 
>  **④MRG\_MYISAM : 集合性质的存储引擎;**
> 
>  **⑤MyISAM : 进行批量插入的操作时速度很快;**
> 
>  **⑥InnoDB : MySQL默认的存储引擎，支持事务机制;**
> 
> 可以通过**SHOW ENGINES**指令来查看所有的存储引擎，如下图所示 ：
> 
> ![f5e5dedab7ee47fba618920d3e7866b0.png][]
> 
> **2° 以上六种类型根据是否支持事务又可分为两大类——**
> 
> **①“事务安全型” （TRANSACTION-SAFE）:** 支持事务。eg : **InnoDB**（其余都属于非事务安全型）
> 
> **②“非事务安全型”（NON-TRANSACTION-SAFE）** : 不支持事务。eg : Memory, MyISAM;

**PS :**  **主要的存储引擎及特点**，如下图所示 :

![a04a57e01d3f4c7d9654a219dc9910ca.png][]

**1°** **InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全**。但是比起MyISAM存储引擎，**InnoDB引擎写的处理效率差一些，并且会占用更多的磁盘空间**以保留数据和索引。

**2°** **MyISAM存储引擎既不支持事务，也不支持外键**，但其**访问速度快**，对事务完整性没有要求。

**3°** **Memory存储引擎**使用存在于内存中的内容来创建表。每个Memory表只实际对应一个磁盘文件。Memory类型表的**访问速度非常快（没有I/O读写）**，因为它的数据是存放在内存中的，并且默认使用了HASH索引。但是，一旦MySQL服务被关闭（eg : 重启MySQL80服务），表中的数据就会全部丢失掉（表的结构还在）。

### 3.选择 :  ###

> **1°** 若应用**不需要事务**，只是处理基本的CRUD操作，那么**选择MyISAM类型表**会很大地提高表的访问速度。
> 
> **2°** 若应用**需要事务**的支持，选择**InnoDB引擎**就完事儿了。
> 
> **3°** 若表中的**数据更新十分频繁**，可以**考虑使用Memory存储引擎**。Memory存储引擎会将数据存储在内存中，由于没有磁盘I/O的等待，速度极快。但每次MySQL服务重启后，所做的任何数据修改都会消失。**EG : 用户在线状态的显示**。

**PS :**   
可以通过 **ALTER TABLE table\_name ENGINE = engine\_name;** 来**修改表的存储引擎**。

--------------------

## 二、视图 ##

### 1.概述 :  ###

> **视图是一个虚拟表，其内容由查询定义**。同真实的表一样，视图也包含列(字段)，但其**数据是来源于对应的真实表**（也叫基表，基表可以有多张），称**视图与基表之间存在映射关系**。**通过视图可以修改基表中的数据，而基表的修改也会影响到视图的数据**（可以类比Java中多个引用指向堆空间中同一对象）。
> 
> 当表中的某些字段不想被直接访问时，就可以利用视图，规定用户可以访问的字段，用户通过视图与基表之间的映射关系来访问基表中对应的数据。

### 2.原理 :  ###

有隐私需求的一方可以限定表中的部分列为可访问，并**为这些可访问的列创建视图**；其他**用户会通过视图与基表的映射关系，来访问基表中相应字段的数据**。

我们先来建一张员工表，代码如下 :

CREATE TABLE IF NOT EXISTS `employee`(
    		`eno` MEDIUMINT UNSIGNED UNIQUE NOT NULL DEFAULT 0,
    		`ename` VARCHAR(20) NOT NULL DEFAULT '',
    		`ecareer` VARCHAR(20) NOT NULL DEFAULT '',
    		`mgr` MEDIUMINT UNSIGNED,
    		`hiredate` DATE NOT NULL,
    		`esalary` DECIMAL(8,2) NOT NULL DEFAULT 0.0,
    		`ebonus` DECIMAL(8,2) DEFAULT 0.0,
    		`deptno` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
    ) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;
    
    INSERT INTO `employee`
    		VALUES
    		(5000, 'Cyan', 'Full_Stack', 2000, '2002-1-1', 9000, NULL, '20'),
    		(5001, 'Five', 'Back_End', 2000, '2002-1-1', 9000, 10000, '20'),
    		(5534, 'Raina', 'Sale', 2766, '2003-3-1', 5000, 5000, '30'),
    		(5537, 'Ice', 'Back_End', 2000, '2003-6-12', 9000, 10000, '20'),
    		(5600, 'Kyrie', 'Fore_End', 2000, '2004-9-1', 9500, 20000, '20'),
    		(5611, 'James', 'Sale', 2766, '2004-9-13', 5500, 10000, '30'),
    		(5612, 'Bob', 'Sale', 2766, '2004-9-15', 5500, 10000, '30'),
    		(5700, 'Alice', 'Manage', 1088, '2004-10-1', 20000, 30000, '10'),
    		(6111, 'Fiven', 'Manage', 1088, '2006-1-11', 20000, 30000, '10'),
    		(6113, 'Cyana', 'Fore_End', 2000, '2006-1-11', 11000, 20000, '20'),
    		(6150, 'Peter', 'Accounting', 1760, '2006-3-1', 6000, 20000, '40'),
    		(6300, 'White', 'Accounting', 1760, '2009-6-6', 7000, 20000, '40'),
    		(6381, 'Frank', 'Manage', 1900, '2010-1-3', 25000, 50000, '10');
    
    SELECT * FROM `employee`;

**员工表如下 :**

![b27d98cbc1b84efea287750861e4333f.png][]

若我们不想让“上级信息”，“入职信息”，“工资”和“奖金”这些比较敏感的字段被任意访问的话，就可以建立对应的视图，在视图中排除这些不想被任意访问的列，如下图所示 :

![2fe41590b49f4057b4718fdca8a77d48.png][]

当用户想要通过视图查询数据时，就会通过映射关系找到基表中相应的字段，并将字段的数据返回给用户。  
当用户想要通过视图修改数据时，假设WHERE子句只有一个条件，那么需要进行两次映射才能完成该操作，第一次映射是去真实表中找到查询条件的字段，第二次映射是去真实表中找到要修改的字段。

### 3.使用格式 :  ###

> **1° CREATE VIEW view\_name AS SELECT...;**
> 
> **2° ALTER VIEW view\_name AS SELECT...;**
> 
> **3° SHOW CREATE VIEW view\_name; //显示创建视图的指令**
> 
> **4° DROP VIEW view\_name1, view\_name2...;**
> 
>  **PS :** 
> 
>  **①创建视图后，数据库中仅存在一个对应的视图结构文件（.frm文件），而不存在数据文件。PS : MySQL8.0中不再单独提供.frm文件，而是合并在.ibd文件中**。
> 
> **②**视图数据的变化会影响到对应的真实表，真实表数据的变化也会影响到视图。
> 
>  **③视图中可以再使用视图；但本质上数据都是来自于基表\[映射关系\]。**

### 4.代码演示 :  ###

操作员工表employee，要求员工的上级信息，入职时间，工资和奖金不能直接访问，要求通过建立视图来实现，如下 :

CREATE VIEW emp_view_1 
    		AS 
    		SELECT eno, ename, ecareer, deptno
    		FROM employee;
    		
    SELECT * FROM emp_view_1;

![f16a19d5f291420a8702f8cbefc03a13.png][]

![864c1838c0f248aeb5fe8e798c49b1b5.png][]

**通过视图修改基本表的数据，如下 :**

UPDATE emp_view_1
            SET ecareer = 'Big_Data'
    		WHERE eno = 5000;
    		
    SELECT * FROM emp_view_1;
    SELECT * FROM employee;

![c0b00d0231f84ce5a02076f36dac866c.png][]

![99402f50e7fd4cc995f1d891c06e5ff4.png][]

**基本表的数据随着视图的更改而发生了变化**。  
继续，**直接通过基本表来修改数据，如下 :**

UPDATE employee
    		SET ecareer = 'Full_Stack'
    		WHERE ename = 'Five';
    
    SELECT * FROM emp_view_1;
    SELECT * FROM employee;

![8210b5e56dcc46eea765c941c43bc497.png][]

![9ca23f7a1e2e4db48d2d2d2d0be19392.png][]

**视图查询的数据随着基本表的更改也发生了变化。**  
继续，**尝试在视图上再次创建新的视图**，如下 :

CREATE VIEW emp_view_2
    		AS
    		SELECT eno, ename 
    		FROM emp_view_1;
    		
    SELECT * FROM emp_view_2;

![5b250b96bb634269b9b34926535999b7.png][]

### 5.应用实例 :  ###

> **1° 安全 :** 对于包含重要信息的数据表，有些字段是保密的，不能让用户直接看到。这时就可以创建一张视图，**在视图中仅保留可见字段**。这样**既可以满足用户的查询需求，也能做到让用户无法直接查看保密的字段**。
> 
> **2° 性能 :** 关系数据库中的数据常常会分表存储，往往通过外键约束来建立表与表之间的关系。这时，在查询时通常会用到**连接（JOIN），不但麻烦，效率相对也比较低**。如果**建立一个视图，将相关的表和字段组合在一起**，就**可以避免使用JOIN来查询数据**。
> 
> **3° 灵活 :** 如果系统中有一张旧表，该表由于设计问题即将被废弃，但是很多应用的数据都是基于这张表的，不易修改。这时就可以建立一张视图，**视图中的数据直接映射到新建的表**，这样既可以少做很多改动，也达到了升级数据表的目的。

**System.out.println("END------------------------------------------------------------------------------");**

[f5e5dedab7ee47fba618920d3e7866b0.png]: https://img-blog.csdnimg.cn/f5e5dedab7ee47fba618920d3e7866b0.png
[a04a57e01d3f4c7d9654a219dc9910ca.png]: https://img-blog.csdnimg.cn/a04a57e01d3f4c7d9654a219dc9910ca.png
[b27d98cbc1b84efea287750861e4333f.png]: https://img-blog.csdnimg.cn/b27d98cbc1b84efea287750861e4333f.png
[2fe41590b49f4057b4718fdca8a77d48.png]: https://img-blog.csdnimg.cn/2fe41590b49f4057b4718fdca8a77d48.png
[f16a19d5f291420a8702f8cbefc03a13.png]: https://img-blog.csdnimg.cn/f16a19d5f291420a8702f8cbefc03a13.png
[864c1838c0f248aeb5fe8e798c49b1b5.png]: https://img-blog.csdnimg.cn/864c1838c0f248aeb5fe8e798c49b1b5.png
[c0b00d0231f84ce5a02076f36dac866c.png]: https://img-blog.csdnimg.cn/c0b00d0231f84ce5a02076f36dac866c.png
[99402f50e7fd4cc995f1d891c06e5ff4.png]: https://img-blog.csdnimg.cn/99402f50e7fd4cc995f1d891c06e5ff4.png
[8210b5e56dcc46eea765c941c43bc497.png]: https://img-blog.csdnimg.cn/8210b5e56dcc46eea765c941c43bc497.png
[9ca23f7a1e2e4db48d2d2d2d0be19392.png]: https://img-blog.csdnimg.cn/9ca23f7a1e2e4db48d2d2d2d0be19392.png
[5b250b96bb634269b9b34926535999b7.png]: https://img-blog.csdnimg.cn/5b250b96bb634269b9b34926535999b7.png