MySQL视图，索引，数据备份与恢复

忘是亡心i 2023-10-13 13:51 39阅读 0赞

**目录**

一.视图

1.1什么是视图？

1.2视图的定义

1.4什么情况下使用视图？

1.5视图的语法

1.6视图与数据表的区别

1.7视图优缺点

优点：

缺点：

二.索引

2.1什么是索引？

2.2为什么要使用索引

2.2 索引原理

2.3索引的优缺点

优点：

缺点：

2.4何时不使用索引

2.5.索引何时失效

2.6.索引分类

2.6.1.普通索引

2.6.2.唯一索引

2.6.3.主键索引

2.6.4.组合索引

2.6.5.全文索引

全文索引的优点：

全文索引缺点：

三.数据库备份和恢复

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

## 一.视图 ##

#### 1.1什么是视图？ ####

视图是在计算机科学和数据库管理中使用的术语，用于表示对数据的特定呈现方式或逻辑结构。视图可以看作是从一个或多个表中选择和组织数据的虚拟表。它提供了一种方便的方式来访问和操作数据库中的数据，同时隐藏了底层的数据结构和复杂的查询逻辑。

视图可以根据应用程序的需求来定义，可以包含特定的列、行、过滤条件和排序规则。它可以用于简化复杂的查询操作，提供更直观和易于理解的数据呈现方式，同时可以保护敏感数据并限制用户访问的权限。

> 视图在数据库管理系统中有多种用途，例如：
> 
> 1. 简化复杂查询：通过定义视图，可以将复杂的查询逻辑封装到一个简单的接口中，使用户能够更轻松地执行查询操作。
> 
> 2. 数据安全性：视图可以隐藏底层表中的敏感数据，并限制用户对数据的访问权限。通过授予用户对视图的访问权限，可以确保只有经过授权的用户能够访问受限的数据。
> 
> 3. 数据完整性：视图可以用于实现数据的完整性约束。通过定义视图，并在其中添加必要的约束条件，可以确保数据满足特定的业务规则和约束，从而提高数据的质量和一致性。

总而言之，视图是一种对数据库中数据的抽象和逻辑组织方式，它提供了一种简化数据访问和操作的方式，同时增强了数据的安全性和完整性。

#### **1.2视图的定义** ####

视图就是从一个或多个表中，导出来的表，是一个虚拟存在的表。视图就像一个窗口（数据展示的窗口），通过这个窗口，可以看到系统专门提供的数据（也可以查看到数据表的全部数据），使用视图就可以不用看到数据表中的所有数据，而是只想得到所需的数据。

在数据库中，只存放了视图的定义，并没有存放视图的数据，数据还是存储在原来的表里，视图的数据是依赖原来表中的数据的，所以原来的表的数据发生了改变，那么显示的视图的数据也会跟着改变,例如向数据表中插入数据，那么在查看视图的时候，会发现视图中也被插入了同样的数据。

视图在外观上和表很相似，但是它不需要实际上的物理存储，视图实际上是由预定义的查询形式的表所组成的。

视图可以包含表的全部或者部分记录，也可以由一个表或者多个表来创建，当我们创建一个视图的时候，实际上是在数据库里执行了SELECT语句，SELECT语句包含了字段名称、函数、运算符，来给用户显示数据。

在数据库中，视图的使用方式与表的使用方式一致，我们可以像操作表一样去操作视图，或者去获取数据。

一般来说，我们只是利用视图来查询数据，不会通过视图来操作数据。

**1.3使用视图的理由是什么？**

>   
> 1.安全性。一般是这样做的:创建一个视图，定义好该视图所操作的数据。之后将用户权限与视图绑定。这样的方式是使用到了一个特性：grant语句可以针对视图进行授予权限。
> 
> 2.查询性能提高。  
>   
> 3.有灵活性的功能需求后，需要改动表的结构而导致工作量比较大。那么可以使用虚拟表的形式达到少修改的效果。
> 
> 这是在实际开发中比较有用的
> 
>   
> 例子：假如因为某种需要，a表与b表需要进行合并起来组成一个新的表c。最后a表与b表都不会存在了。而由于原来程序中编  
>   
> 写sql分别是基于a表与b表查询的，这就意味着需要重新编写大量的sql(改成向c表去操作数据)。而通过视图就可以做到不修  
>   
> 改。定义两个视图名字还是原来的表名a和b。a、b视图完成从c表中取出内容。  
>   
> 说明：使用这样的解决方式，基于对视图的细节了解越详细越好。因为使用视图还是与使用表的语法上没区别。比如视图名a  
> ，那么查询还是"select \* from a"。  
>   
> 4.复杂的查询需求。可以进行问题分解，然后将创建多个视图获取数据。将视图联合起来就能得到需要的结果了。  
>   
> 视图的工作机制:当调用视图的时候，才会执行视图中的sql，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。

视图这样设计有什么好处？节省空间，内容是总是一致的话，那么我们不需要维护视图的内容，维护好真实表的内容，就可以保证视图的完整性了。

#### 1.4什么情况下使用视图？ ####

视图(view)是在数据库中用于呈现数据的一种方式。以下是一些情况下使用视图的例子：

> 1.  数据隐藏：视图可以用于隐藏敏感或保密的数据。通过创建只包含特定列或特定行的视图，可以限制用户访问敏感信息。
> 2.  数据转换：视图可以用于将数据进行重新组织或转换。例如，可以创建一个视图，将多个关联表中的数据合并为一个更容易理解的视图。
> 3.  数据过滤：视图可以用于过滤数据，以便只显示满足特定条件的行。这对于在大型数据库中查找特定数据非常有用。
> 4.  简化复杂的查询：通过创建一个包含复杂逻辑的视图，可以简化复杂查询的编写。这可以提高代码的可读性和维护性。
> 5.  数据聚合：视图可以用于从多个表中聚合和计算数据。这对于生成报表和统计数据非常有用。

注意：**视图只是数据库中的一个逻辑概念，并不实际存储数据。它只是在查询时按照定义的方式呈现已有的数据。**

#### **1.5视图的语法** ####

**可以使用 CREATE VIEW 语句来创建视图。语法格式如下：**

CREATE VIEW <视图名> AS <SELECT语句>

**语法说明如下：**

> <视图名>\`：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。  
> <SELECT语句>\`：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
> 
> 对于创建视图中的 SELECT 语句的指定存在以下限制：
> 
> 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。  
> SELECT 语句不能引用系统或用户变量。  
> SELECT 语句不能包含 FROM 子句中的子查询。  
> SELECT 语句不能引用预处理语句参数。  
>

#### 1.6视图与数据表的区别 ####

视图（View）和数据表（Table）是数据库中两个不同的概念，它们有一些明显的区别：

> 1. 存储方式：数据表是物理存储在数据库中的实体，而视图只是一个逻辑查询结果的虚拟表，并不真正存储数据。
> 
> 2. 数据内容：数据表存储实际的数据记录，而视图是基于一个或多个数据表的查询结果。视图的数据是根据特定的查询语句即时生成的。
> 
> 3. 结构定义：数据表有自己的结构定义，包括表名、列名、数据类型、约束等。而视图的结构定义是基于源表或其他视图，并且可以选择性地包含特定的列。
> 
> 4. 数据修改：可以向数据表插入、更新和删除数据，而视图是只读的。如果尝试对视图进行修改操作，实际上是对基础表的数据进行修改。
> 
> 5. 安全性和访问控制：视图可以用于限制对敏感数据的访问，通过只暴露特定的列或行。数据表通常不具备这种级别的细粒度安全性控制。
> 
> 6. 性能：由于视图是根据查询动态生成的，查询视图可能会引入一定的性能开销。数据表的查询则直接从实际的物理存储中获取数据，更加高效。

需要根据具体的业务需求和使用场景来决定是使用数据表还是视图。数据表适合存储和操作实际数据，而视图则更适用于提供灵活、可定制的数据呈现和访问方式。

#### 1.7视图优缺点 ####

#### 优点： ####

> **1 定制用户数据，聚焦特定的数据**
> 
> 在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。
> 
> 例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。
> 
> **2 简化数据操作**
> 
> 在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。
> 
> **3 提高数据的安全性**
> 
> 视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。
> 
> **4 共享所需数据**
> 
> 通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。
> 
> **5 更改数据格式**
> 
> 通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。
> 
> **6 重用 SQL 语句**
> 
> 视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

#### **缺点：** ####

> 1.  性能开销：使用视图进行查询时，性能可能下降。这是因为视图本身是基于查询语句生成的虚拟表，并且在每次查询时都需要重新计算。如果视图的定义比较复杂或查询涉及多个视图，可能会导致查询的执行时间增加。
> 2.  数据更新限制：视图是只读的，无法直接对视图进行插入、更新和删除操作。任何对视图的更改实际上是对基础表进行的修改。这意味着当视图的定义涉及多个表时，某些操作可能会受到限制。
> 3.  存储空间消耗：尽管视图本身并不存储任何数据，但每个视图都需要占用一定的存储空间来保存其定义和使用所需的元数据。如果定义了大量的视图，这可能会占据不小的存储空间，并且可能需要进行定期的维护和清理。
> 4.  数据一致性：由于视图是基于一个或多个源表生成的，所以在源表发生更改后，视图的数据可能会变得不一致。为确保视图的数据始终是最新和准确的，可能需要定期或实时地刷新视图或重新创建视图。
> 5.  逻辑复杂性：如果视图的定义变得非常复杂，包含多个嵌套查询、连接和计算等操作，就会增加对视图的理解和维护的难度，可能导致代码的可读性降低。

## 二.索引 ##

#### **2.1什么是索引？** ####

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

强调：一旦为表创建了索引，以后的查询最好先查索引，再根据索引定位的结果去找数据

建立索引会占用磁盘空间的索引文件。

#### 2.2为什么要使用索引 ####

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。

索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

**1 顺序访问**

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。

顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

**2 索引访问**

索引访问是通过遍历索引来直接访问表中记录行的方式。

使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

#### 2.2 索引原理 ####

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

> 本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

#### 2.3索引的优缺点 ####

索引在数据库中具有许多优点和一些缺点。下面是索引的主要优点和缺点：

##### 优点： #####

> **1. 快速查询：**
> 
> 索引能够加快数据库中数据的检索速度。通过在索引字段上建立索引，数据库可以直接跳到满足查询条件的数据位置，避免了全表扫描，提高了查询效率。
> 
> **2. 减少磁盘IO：**
> 
> 索引可以减少数据库从磁盘读取数据的次数。相比于全表扫描，通过索引只需要读取索引所在的数据页或块，减少了IO操作，提高了数据访问的效率。
> 
> **3. 支持唯一性约束：**
> 
> 通过在列上创建唯一索引，可以强制保持列的唯一性。这对于避免数据重复和维护数据的完整性非常有用。
> 
> **4. 支持排序和聚合：**
> 
> 索引可以优化排序和聚合操作的性能。通过使用合适的索引，数据库可以直接从索引中获取排序或聚合所需的数据，而不需要进行全表扫描或中间结果集的排序和处理。

##### 缺点： #####

> **1. 占用存储空间：**
> 
> 索引需要占用额外的存储空间来保存索引数据结构和索引列的值。如果数据库的大小很大，建立索引可能会占用大量的存储空间。
> 
> **2. 数据修改开销：**
> 
> 当对表进行插入、更新和删除操作时，索引也需要进行相应的维护。这会增加数据修改的开销，并且在频繁进行大量的数据修改时可能会影响数据库性能。
> 
> **3. 更新索引的开销：**
> 
> 当表的数据发生变化时，索引也需要进行更新。这可能导致索引的频繁重建或重新平衡，从而消耗一定的计算资源和时间。
> 
> **4. 增加查询优化器的复杂性：**
> 
> 对于复杂的查询的优化，使用索引可能导致查询优化器的复杂度增加，其选择合适的索引进行查询优化可能需要更多的计算和分析。

综上所述，索引的优点是提高查询性能和加速数据访问，而缺点是占用存储空间、增加数据修改开销和更新索引的开销。在使用索引时，需要根据具体的业务需求和数据库特征进行权衡和考虑，以达到优化性能和减少开销的平衡。

#### 2.4何时不使用索引 ####

*  表记录太少
 *  经常增删改的表
 *  数据重复且分布均匀的表字段，只应该为经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
 *  频繁更新的字段不适合创建索引(会增加IO负担)
 *  where条件里用不到的字段不创建索引

#### 2.5.索引何时失效 ####

like以通配符%开头索引失效

当全表扫描比走索引查询的快的时候，会使用全表扫描，而不走索引

字符串不加单引号索引会失效

where中索引列使用了函数（例如substring字符串截取函数）

where中索引列有运算（用了< or > 右边的索引会失效，用<= or >= 索引不会失效）

is null可以走索引，is not null无法使用索引（取决于某一列的具体情况）

复合索引没有用到左列字段（最左前缀法则，如果没用用到最左列索引，或中间跳过了某列有索引的列，索引会部分失效）

条件中有or，前面的列有索引，后面的列没有，索引会失效。想让索引生效，只能将or条件中的每个列都加上索引

#### 2.6.索引分类 ####

MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列，索引项可以为null（唯一索引、组合索引等），但是只要列中有null值就不会被包含在索引中。

#### 2.6.1.普通索引 ####

普通索引是最基本的索引，它没有任何限制；

**创建索引语法：**

create index index_name on table(column)；

**修改表结构方式添加索引：**

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

**删除索引**

DROP INDEX index_name ON table

#### 2.6.2.唯一索引 ####

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX indexName ON table(column(length))

#### 2.6.3.主键索引 ####

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。

CREATE TABLE mytable( 
        ID INT NOT NULL, 
        username VARCHAR(16) NOT NULL, 
        PRIMARY KEY(ID) 
    );

#### 2.6.4.组合索引 ####

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

#### 2.6.5.全文索引 ####

概念

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

全文索引的工作原理如下：

>   
> **1. 分词：**
> 
> 文本文档会被分成单词或词条，通常称为标记化。这些词条将成为索引的关键字。
> 
> **2. 建立索引：**
> 
> 每个词条都被记录在索引中，同时关联到它所在的文档或记录。索引通常以树状结构存储，以便快速定位到相关的文档。
> 
> **3. 查询：**
> 
> 用户可以通过指定关键字或短语来进行查询。搜索引擎将在索引中查找包含指定词条的文档，并按相关性排序返回结果。

#### 全文索引的优点： ####

> **1. 快速检索：**
> 
> 全文索引可以在大量文本数据中快速定位到所需的内容，比传统的线性搜索方法更高效。
> 
> **2. 高准确性：**
> 
> 通过使用分词技术和相关性排序算法，全文索引可以提供更准确和相关的搜索结果。
> 
> **3. 灵活性：**
> 
> 全文索引可以处理不同类型的文档，无论是短文本还是长文本、结构化或非结构化的数据。

#### 全文索引缺点： ####

> **1. 索引大小：**
> 
> 全文索引需要消耗大量的存储空间，特别是对于大规模的文本数据集，需要考虑到索引的存储和管理。
> 
> **2. 复杂性：**
> 
> 构建和维护全文索引需要一定的技术和资源，在更新和同步索引与原始文档之间需要一定的实时性。
> 
> **3. 多语言支持：**
> 
> 全文索引在处理多语言和不同语种的文本时可能会面临一些特殊的挑战，如分词算法和处理特殊字符的处理等。

总的来说，全文索引是一种强大的工具，可以大大提高文本数据的搜索和检索效率，广泛应用于搜索引擎、数据库管理系统、文件管理等领域。

版本支持

> Mysql 5.6之前版本，只有MyISAM支持全文索引，5.6之后，Innodb和MyISAM均支持全文索引。另外，只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。  
>

## 三.数据库备份和恢复 ##

**1.使用工具：navicat**

> \-- 使用Navicat工具导入 t\_log 共耗时 45s；  
> \-- 使用mysqldump导入 包含t\_log表的整个数据库 共耗时 20s；  
> \-- 使用单表数据导入load data infile 的方式，共耗时 7.502s  
> \-- DELETE/TRUNCATE  
> \-- 花费的时间：10.597s  
> DELETE FROM t\_log;  
> \-- 花费的时间：0.121s  
> TRUNCATE TABLE t\_log;

**2.导入导出：**

导出  
mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql

c:\mysql-8.0.13-winx64\bin>mysqldump -uroot -p123456 mybatis_ssm > 1234567.sql

**3. 导入**  
注意：首先建立空数据库

mysql>create database abc;

方法一

mysql>use abc;                   #选择数据库
      mysql>set names utf8;            #设置数据库编码
    导入数据
      mysql>source /c:/mysql-8.0.13-winx64/bin/1234567.sql;

方法二

mysql -u用户名 -p密码 数据库名 < 数据库名.sql
     
          #mysql -uabc_f -p abc < abc.sql
     
      可先通过SELECT INTO OUTFILE方式，将数据导出到Mysql的C:\ProgramData\MySQL\MySQL Server 5.5\data目录下，再通过LOAD DATA INFILE方式导入。
     
     
     
      1) select * from 表名 into outfile '/文件名.sql';
     
      2) load data infile '/文件名.sql' into table 表名(列名1,...);
     
     这时候就可以在 mysql.ini 文件的 [mysqld] 代码下增加 secure_file_priv=E:/TEST 再重启 mysql 就可以了。然后在导出的地址下面写上刚才配置的这个地址 eg: select * from tb_test into outfile "E:/TEST/test.txt"；就可以了。
     
    select * from t_log into outfile 'E:/12345678.sql';
     
    load data infile 'E:/12345678.sql' into table t_log(id,ip,userid,moduleid,content,createdate,url);
     
    show variables like 'secure%'
     
    desc t_log
     
    select * FROM t_log;