Mysql之视图，索引及数据的备份与恢复

最新推荐文章于 2024-04-25 12:02:52 发布

孤留光乩

最新推荐文章于 2024-04-25 12:02:52 发布

阅读量900

点赞数 3

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_74268571/article/details/131614051

版权

3.LOAD DATA INFILE导入导出

4.truncate和delete效率的区别

一、视图

1.视图是什么

        MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

        数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

        视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。

2.视图与数据表的区别

视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。

存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。

视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。

视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。

视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。

视图的建立和删除只影响视图本身，不影响对应的基本表。

3.视图的优缺点

优点：

1) 定制用户数据，聚焦特定的数据

在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。

例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

2) 简化数据操作

在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

3) 提高数据的安全性

视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4) 共享所需数据

通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

5) 更改数据格式

通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。

6) 重用 SQL 语句

视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

缺点：

1）性能影响：

视图在查询时会涉及到底层数据表的访问和计算，因此在处理大量数据或复杂计算的情况下，性能可能会受到影响。

2）数据一致性：

视图通常是基于底层数据表的查询结果创建的，当底层数据表发生更改时，需要保证视图与表的数据一致性，可能需要更新或重新创建视图。

3）更新限制：

默认情况下，视图是不可更新的，即不能通过视图来修改底层数据表中的数据。如果需要更新视图，需要确保视图的定义和底层表的约束之间的一致性。

4）可能导致查询复杂性增加：

虽然视图可以简化复杂查询的访问，但在某些情况下，使用视图可能会导致查询的复杂性增加，特别是涉及到多个视图的联合查询时。

4.视图的应用场景

数据隐藏和安全性：通过创建视图，可以隐藏底层数据库表的具体结构和敏感字段，只暴露给用户需要的数据。这样可以有效保护数据的安全性和隐私。

简化复杂查询：当有一些复杂的查询需求，需要多表联合查询、嵌套查询或涉及到计算字段时，可以通过在视图中定义查询逻辑，使得用户可以更简洁地进行查询操作。

数据角度的重组和转换：通过创建视图，可以将数据以逻辑上不同的方式重新组织和转换，使其更符合用户的需求，在一定程度上实现数据的重组和透明化处理。

数据统计和报表生成：通过视图，可以针对特定的业务需求进行数据汇总、统计和计算，从而更方便地生成报表和数据分析。

简化应用程序开发：通过创建视图，可以将复杂的查询逻辑封装在视图中，使得应用程序可以直接使用简单的 SELECT 语句进行数据访问，简化开发流程。

5.语法运用

可以使用 CREATE VIEW 语句来创建视图。语法格式如下：
CREATE VIEW <视图名> AS <SELECT语句>
语法说明如下:

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

<SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：

用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。

SELECT 语句不能引用系统或用户变量。

SELECT 语句不能包含 FROM 子句中的子查询。

SELECT 语句不能引用预处理语句参数。

注：

什么是预处理语句参数呢？

预处理语句参数是在使用预处理语句时，用于传递数据值或其他参数信息的占位符。预处理语句是一种特殊的SQL语句，通过将占位符与实际的数据值或参数进行绑定，可以动态执行相同或类似的SQL语句，提高查询的灵活性、安全性和性能。

在预处理语句中，通常使用问号"?"作为占位符来代表参数的位置。具体参数的值在执行预处理语句之前，由应用程序提供，并与占位符进行绑定。数据库系统在执行预处理语句时，会根据绑定的参数值自动替换占位符，从而执行具体的操作。

二、索引

1.什么是索引

        索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

        可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

        因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能

2.为什么要使用索引

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

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

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

1) 顺序访问

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

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

2) 索引访问

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

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

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

3.使用索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

优点

索引的优点如下：

通过创建唯一索引可以保证数据库表中每一行数据的唯一性。

可以给所有的 MySQL 列类型设置索引。

可以大大加快数据的查询速度，这是使用索引最主要的原因。

在实现数据的参考完整性方面可以加速表与表之间的连接。

在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点

增加索引也有许多不利的方面，主要如下：

创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

注：

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

4.何时不使用索引

表记录太少

经常增删改的表

数据重复且分布均匀的表字段，只应该为经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）

频繁更新的字段不适合创建索引(会增加IO负担)

where条件里用不到的字段不创建索引

5.索引何时失效

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

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

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

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

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

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

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

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

6.索引分类

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

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.唯一索引

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
CREATE UNIQUE INDEX indexName ON table(column(length))
3.主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。
CREATE TABLE mytable( 
    ID INT NOT NULL, 
    username VARCHAR(16) NOT NULL, 
    PRIMARY KEY(ID) 
);
4.组合索引

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

三、数据的备份与恢复

数据越多越能看出效果，博主就以19万条数据的数据表为例

1.数据库工具内导入导出

导入---13.56s

导出---2.67s

2.mysqldump导入导出

导出---3-5s
1. 导出表数据和表结构
mysqldump -u用户名 -p密码数据库名 > 数据库名.sql(这个名字随便叫)
D:\SoftwareInstallPath\mysql-8.0.13-winx64\bin>mysqldump -uroot -p123456 mybatis_ssm > 1234567.sql

2. 只导出表结构
-mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
# mysqldump -uroot -p -d abc > abc.sql
注：导出的数据在mysql的bin目录下

导入---1s
注意：首先建立空数据库
mysql>create database abc;
1. 方法一
mysql>use abc; #选择数据库
mysql>set names utf8; #设置数据库编码
mysql>source /D:/SoftwareInstallPath/mysql-8.0.13-winx64/bin/1234567.sql; #导入数据

2.方法二
mysql -u用户名 -p密码数据库名 < 数据库名.sql
#mysql -uabc_f -p abc < abc.sql

D:\software\MySql\mysql-8.0.18-winx64\mysql-8.0.18-winx64\bin>mysqldump -uroot -p123456 mybatis_ssm > 1234567.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

D:\software\MySql\mysql-8.0.18-winx64\mysql-8.0.18-winx64\bin>mysqldump -uroot -p123456 mybatis_ssm > 1234567.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

D:\software\MySql\mysql-8.0.18-winx64\mysql-8.0.18-winx64\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use abc;
Database changed
mysql> set names utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> source 1234567.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 11212 rows affected (0.08 sec)
Records: 11212  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 18 rows affected (0.00 sec)
Records: 18  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>