Mysql之视图，索引，备份与恢复-阿里云开发者社区

前言：我们使用了视图，索引，备份与恢复会在我们的学习工作中极大的提高效率，我们在学习中可能感受不到，但是在工作中，我们使用的表数据中有许多的数据（一百万以上的数据），这时效率就变得尤为重要了。那么我就从以下三个方面来进行讲解。

一，视图

1.视图是什么？

视图是一种虚拟的表，是从数据库中一个或多个表中导出来的表。

数据库中存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。

使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

2.视图的重要性？

定制用户数据，聚焦特定的数据
在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。
例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。

使操作简便化。在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。（通过筛选，组合从而以不同的角度呈现数据，方便用户进行数据管理和分析。比如：多表查询时，我们只需要看几个字段）

增加数据的安全性。视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。（我们使用视图去修改，删除数据并不会改变原数据库中的数据）

共享所需数据
通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。（比如：政府部门之间的官员任免，这时政府中会共用一张用户表。

更改数据格式
通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。（比如我们在数据库中存储的时间日期是字符串，要想比较就得转换成data类型的）

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

3.那些地方使用视图？

数据加密和访问控制：通过视图，可以限制用户仅访问他们所需的数据列，并且可以隐藏敏感数据。通过授权用户仅对视图进行查询操作，可以实现更精细的数据访问控制和数据隐私保护。

安全性和版本控制：在一些情况下，为了保证系统的稳定性和数据的一致性，不允许直接对原始表进行修改。创建一个视图，作为用户操作的接口，可以保护原始表的安全并提供版本控制和数据规范化。

数据格式转换和适配：使用视图可以将数据从一个格式转换为另一个格式，以适应不同系统之间的数据交互。例如，可以使用视图将日期时间格式转换为字符串形式，或将多个字段合并为一个字段。

.数据库管理系统，在我们Java后台管理数据中，我们的数据如果是通过图形就可以直观地展示数据的趋势、关系和分布，帮助用户更好地理解和解释数据。

4.基本语法

CREATE VIEW 视图名 AS SELECT 语句;

代码：

CREATE VIEW v_four_table AS SELECT
t.tid,
t.tname,
s.* 
FROM
  t_mysql_score sc,
  t_mysql_teacher t,
  t_mysql_student s,
  t_mysql_course c 
WHERE
  sc.sid = s.sid 
  AND t.tid = c.tid 
  AND c.cid = sc.cid;
DROP VIEW v_four_table
SELECT * FROM v_four_table

二，索引

1.索引是什么？

索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。类似于图书的目录，方便快速定位，寻找指定的内容。

2.索引的重要性？

优点：

提高查询数据的速度。（当我们使用数据库查询某个关键字时，我们的查询效率会比没有使用索引的要快！！！）这是使用索引最主要的原因。

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

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

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

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

缺点：

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

创建和维护索引的时间增加了，同时占用硬盘空间。（公司在提示性能和升级磁盘之间会选择前者）

3.索引的种类：

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

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

主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；使用id查询：

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

注意：当有两个索引是会优先走最左边的索引：

4.那些地方使用索引？

经常使用的查询列：对频繁使用的列创建索引可以提高查询的性能。当某一列经常用于查询条件或排序操作时，为该列创建索引可以加速查询的执行。（比如：订单的收货，发货，）

5.索引何时失效

like以通配符%开头索引失效
当全表扫描比走索引查询的快的时候，会使用全表扫描，而不走索引
字符串不加单引号索引会失效（比如：select * from order ）
where中索引列使用了函数（例如substring字符串截取函数）
where中索引列有运算（用了< or > 右边的索引会失效，用<= or >= 索引不会失效）
is null可以走索引，is not null无法使用索引（取决于某一列的具体情况）
复合索引没有用到左列字段（最左前缀法则，如果没用用到最左列索引，或中间跳过了某列有索引的列，索引会部分失效）
条件中有or，前面的列有索引，后面的列没有，索引会失效。想让索引生效，只能将or条件中的每个列都加上索引

6.索引的语法：

1.创建索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名(长度))

2.修改索引

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名(长度))

3.删除索引

DROP INDEX 索引名 ON 表名

4.未使用索引对比使用索引花的时间：

未使用索引：0.079s

创建索引：0.586s

使用索引：0.079s

5.查看使用的索引：关键字explan

具体代码：

-- 未使用索引查询字段：0.223s
SELECT * FROM t_log where moduleid='110401'
-- 1.创建索引
 CREATE INDEX idx_moduleid ON t_log(moduleid)
-- 2使用索引查询字段：0.045s
SELECT * FROM t_log where moduleid='110401'
-- 3使用主键索引查询字段：0s
SELECT * FROM t_log where id='03ea476a0e8a413196b28b5cba2bb526'
-- 4使用组合索引查询字段：0s
-- 创建组合索引
 CREATE INDEX idx_moduleid ON t_log(id,moduleid,url);
--  最左原则
EXPLAIN SELECT * FROM t_log where id=''and moduleid=''and url=''
-- 不走组合索引
EXPLAIN SELECT * FROM t_log where id=''
EXPLAIN SELECT * FROM t_log where moduleid=''
EXPLAIN SELECT * FROM t_log where moduleid=''and url=''
SELECT * FROM t_log where id='03ea476a0e8a413196b28b5cba2bb526'
-- 5查看sql的执行计划：
EXPLAIN  SELECT * FROM t_log where moduleid='110401'
-- 导出
select * from t_log into outfile 'D:/123123.sql';
-- 6创建唯一索引
 CREATE UNIQUE INDEX idx_moduleid ON t_log(moduleid)
-- 7.修改索引
   ALTER TABLE t_log ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名[(长度)][ASC|DESC])
-- 8.删除索引
   DROP INDEX idx_moduleid ON t_log

三，备份与恢复

1.备份与恢复是什么？

备份就是将数据库中的数据备份到我们指定的位置，恢复就是将我们电脑中有的数据能够重新插入到数据库中去。

2.备份与恢复的重要性？

数据丢失保护：备份是防止数据丢失的关键措施。无论是因为意外删除、硬件故障、恶意软件还是灾难性事件，备份可以帮助恢复和恢复丢失的数据，确保数据的完整性和可用性。
灾难恢复：备份是关键的灾难恢复计划的组成部分。当面临自然灾害、黑客攻击、设备故障等灾难性事件时，备份可以帮助恢复业务关键数据和系统，减少停机时间和业务损失。
数据完整性保护：备份可以保护数据的完整性。如果数据在存储或传输过程中出现问题，备份可以用作验证数据的正确性和完整性的手段。

3.那些地方使用备份与恢复？

牵涉到重要的数据时，当我们使用公司的数据要进行删除时。

4.怎么使用备份与恢复？

方法一：

导入

之间使用工具navicat导入数据（十分耗时间，数据多时不建议使用）

导入耗时40s，效率非常的底下。

导出：

方法二：

1.找到安装MySQL的bin目录

2.cmd打开运行窗口

3.输入导出命令

mysqldump -uroot -p -d abc > abc.sql

导入：

注意：首先建立空数据库

命令： mysql>create database abc;

mysql>use abc; #选择数据库

mysql>set names utf8; #设置数据库编码

mysql>source /D:/SoftwareInstallPath/mysql-8.0.13-winx64/bin/abc.sql; #导入数据到MySQL中

方式三：

导出：

select * from t_log into outfile "D:/test.txt";

此时我们就不会成功，因为我们未给D盘赋权限

将这行代码secure_file_priv=D: 放到ini文件中

之后重启MySQL的服务，才会成功！！！

导入：

select * from t_log into outfile 'D:/test.txt';

load data infile 'D:/test.txt' into table t_log(id,ip,userid,moduleid,content,createdate,url);

show variables like 'secure%'

desc t_log;

效果就0.几秒，十分的快！！！

具体的代码：

-- 导出
 select * from t_log into outfile "D:/test.txt";
--  导入
select * from t_log into outfile 'D:/test.txt';
load data infile 'D:/test.txt' into table t_log(id,ip,userid,moduleid,content,createdate,url); 
show variables like 'secure%'
desc t_log;
select * FROM t_log;
-- 删数据
TRUNCATE t_log

希望这篇博客能够帮助到正在学习工作的你！！！

Mysql之视图，索引，备份与恢复