深入解析MySQL视图、索引、数据导入导出：优化查询和提高效率

ゝ一纸荒年。 2024-03-17 17:04 97阅读 0赞

**目录**

1. 视图（View）：

什么是视图？

为什么要使用视图？

视图的优缺点

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

2) 简化数据操作

3) 提高数据的安全性

4) 共享所需数据

5) 更改数据格式

6) 重用 SQL 语句

示例操作

没使用前

使用后

2. 索引（Index）：

什么是索引？

为什么要使用索引？

1) 顺序访问

2) 索引访问

索引的优缺点【重点】

优点

缺点

什么时候不使用索引

索引何时失效【重点】

索引分类

1) 普通索引

2) 唯一索引

3) 主键索引

4) 组合索引：

3. 数据导入导出：

什么是数据导入导出？

为什么要使用数据导入导出？

示例操作

导出

手动导出

dom命令出

导出表数据和表结构

只导出表结构

导入

LOAD DATA INFILE 导入导出

【注意】

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

## 1. 视图（View）： ##

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

1.  视图是基于一个或多个表的查询**结果集**，类似于虚拟表。它是一个虚拟的表，**没有实际的存储数据**，通过查询定义的，可以像表一样查询和使用。
2.  视图可以用于简化**复杂的查询操作**，隐藏底层表结构细节，提供更**简洁和易读的查询接口**。
3.  视图还可以用于限制用户**对数据的访问权限**，通过授权不同的视图给不同的用户，**实现数据安全性控制**。

**创建视图语法：**

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

**语法说明如下：**

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。  
\-<SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

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

*  用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
 *  SELECT 语句不能引用系统或用户变量。
 *  SELECT 语句不能包含 FROM 子句中的子查询。
 *  SELECT 语句不能引用预处理语句参数。

### 为什么要使用视图？ ###

> 视图提供了数据封装和抽象的能力，可以简化复杂查询、隐藏细节、实现安全性控制、提高数据访问的灵活性。

### 视图的优缺点 ###

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

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

#### **2) 简化数据操作** ####

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

#### **3) 提高数据的安全性** ####

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

#### **4) 共享所需数据** ####

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

#### **5) 更改数据格式** ####

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

#### **6) 重用 SQL 语句** ####

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

### 示例操作 ###

#### 没使用前 ####

sql语句长

SELECT * from 
    t_mysql_score sc,
    t_mysql_course c,
    t_mysql_teacher t,
    t_mysql_student s
    where sc.cid = c.cid
    and sc.sid = s.sid
    and c.tid = t.tid

结果：

![4111385d86e141b6b72adbb869bc4aad.png][]

#### 使用后 ####

> 我们利用
> 
>     CREATE VIEW v_student_score as
>     SELECT * from 
>     t_mysql_score sc,
>     t_mysql_course c,
>     t_mysql_teacher t,
>     t_mysql_student s
>     where sc.cid = c.cid
>     and sc.sid = s.sid
>     and c.tid = t.tid
> 
> 创建之后会出现一个错误
> 
> ![f459fe8ea5b447a9980f613077ceca6f.png][]
> 
> 代表了很多**重复**的意思 ，我们更改\*为s.\*,c.\*,t.tname,sc.score

CREATE VIEW v_student_score as
    SELECT s.*,c.*,t.tname,sc.score from 
    t_mysql_score sc,
    t_mysql_course c,
    t_mysql_teacher t,
    t_mysql_student s
    where sc.cid = c.cid
    and sc.sid = s.sid
    and c.tid = t.tid

结果：

![307871385a8e47c5a09d47397eba139e.png][]

**利用查询语句查询视图**

SELECT * from v_student_score

![c8185b847fbe4a6a8303f321388ac0ab.png][]

## 2. 索引（Index）： ##

### 什么是索引？ ###

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

1.  索引可以根据一个或多个列的值进行**排序和搜索**，提高查询时的效率。
2.  MySQL索引（Index）是一种特殊的数据结构，建立在表的列上，旨在加快数据库查询的速度
3.  通过在索引列上创建索引，数据库可以更快地定位和访问特定值，而无需扫描整个数据表。
4.  索引可以应用于**单个列或多个列的组合**，可以按升序或降序排序。
5.  常见的索引类型包括主键索引、唯一索引、普通索引等。

**创建索引：**

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

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

> 索引可以加快数据库查询的速度，提高查询效率，**特别是在大数据量的情况下，可以显著减少查询的时间**。

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

#### **1) 顺序访问** ####

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

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

#### **2) 索引访问** ####

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

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

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

### 索引的优缺点【重点】 ###

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

#### **优点** ####

索引的优点如下：

*  通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
 *  可以给所有的 MySQL 列类型设置索引。
 *  可以大大加快数据的查询速度，这是使用索引最主要的原因。
 *  在实现数据的参考完整性方面可以加速表与表之间的连接。
 *  在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

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

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

*  创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
 *  索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
 *  当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

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

#### 什么时候不使用索引 ####

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

#### 索引何时失效【重点】 ####

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

### 索引分类 ###

> 创建日志文件
> 
>     CREATE TABLE `t_log` (
>       `id` varchar(32) NOT NULL COMMENT '唯一标识',
>       `ip` varchar(15) NOT NULL COMMENT 'IP地址',
>       `userid` varchar(32) NOT NULL COMMENT '用户ID',
>       `moduleid` varchar(32) NOT NULL COMMENT '模块ID',
>       `content` varchar(500) NOT NULL COMMENT '日志内容',
>       `createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
>       `url` varchar(100) DEFAULT NULL COMMENT '请求URL地址',
>       PRIMARY KEY (`id`)
>     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> 然后导入我们的文件t\_log.sql
> 
> **【ps】**文件里的数据均为虚拟数据

#### 1) 普通索引 ####

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

select COUNT(1) from t_log    -- 用时：0.026s

**建索引前 0.11s**

select * from t_log where moduleid = '100301';

**创建索引所花费的时间： 1.132s**

Create index idx_moduleid on t_log(moduleid);

**建索引后 0.001s**

select * from t_log where moduleid = '100301';

![c6fc22c1770143bb86dad9f94533ffce.png][]

**删除索引 0.014s**

drop index idx_moduleid on t_log

**可以查看走过的索引**

EXPLAIN select * from t_log where moduleid = '100301';

有索引

![013d1fe70d494f0f83e09a657a963ae3.png][]

无索引

![d923e4525a564baf8db6d6e9080afc4d.png][]

#### 2) 唯一索引 ####

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

>     -- Duplicate entry '/quartz/queryJobLst' for key 'idx_url'    有重复列段
>     -- 创建
>     create UNIQUE index idx_url on t_log(url);
>     -- 删除
>     drop index idx_url on t_log;
> 
> 我们的 t\_log里面的 url有很多的 /quartz/queryJobLst数据  
>   
> ![c29ef2525c18447492ee92e78007c62f.png][]

#### 3) 主键索引 ####

> 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；

> 我们的id是主键所以查询的时间还是很快的  
> 
> 
>      -- 主键索引所花费的时间：0s
>     select * from t_log where id = '07489cdafd6d4a3489884cd3c00c7b27';
>     EXPLAIN select * from t_log where id = '07489cdafd6d4a3489884cd3c00c7b27';
> 
>   
> ![a49e413c3b29417c9decf342ec6a5957.png][]

#### 4) 组合索引： ####

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

>   
> 用的**最左原则，**看似有多个索引，其实走了一个索引，走的是左边第一个索引
> 
>     -- 花费的时间：2.965s
>     create index idx_userid_moduleid_url on t_log(userid,moduleid,url);
> 
>   
> ![b7cf9a868e2d47629264b05af480a033.png][]
> 
>     -- 走组合索引
>     EXPLAIN select * from t_log where userid = '' and moduleid = '' and url = '';
>     EXPLAIN select * from t_log where userid = '' and moduleid = '';
>     EXPLAIN select * from t_log where userid = '' ;
>     EXPLAIN select * from t_log where userid = '' and url = '';
>     -- 不走组合索引
>     EXPLAIN select * from t_log where moduleid = '';
>     EXPLAIN select * from t_log where url = '';
>     EXPLAIN select * from t_log where moduleid = '' and url = '';
> 
>   
>

## 3. 数据导入导出： ##

### 什么是数据导入导出？ ###

1.  数据导入是将**外部数据文件的数据**导入到MySQL数据库中。
2.  数据导出是将MySQL数据库中的数据导出为外部数据文件，以便在其他系统或数据库中使用。
3.  数据导入导出常用的工具有mysqldump、mysqlimport等。
4.  数据导入导出通常用于**数据迁移、数据备份和恢复**、与其他系统进行数据交换等场景。
5.  数据导入导出的文件格式可以是纯文本格式，也可以是其他数据库可识别的格式如**SQL**文件。

**数据导入：**  
使用LOAD DATA INFILE语句导入纯文本数据文件：

LOAD DATA INFILE 'path_to_file' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

**数据导出：**  
使用SELECT ... INTO OUTFILE语句将查询结果导出为纯文本数据文件：

SELECT column1, column2, ... INTO OUTFILE 'path_to_file' FROM table_name;

### 为什么要使用数据导入导出？ ###

> 数据导入导出允许将数据从一个系统迁移到另一个系统，进行备份和恢复，与其他系统进行数据交换和共享。

### 示例操作 ###

#### 导出 ####

##### 手动导出 #####

> **选择需要导的表，然后右键，转储SQL文件，结构和数据，保存即可**

> ![636a61580a1c46f6bf524c726c12414f.png][]
> 
> **在数据多的情况下，我们导出的时间就需要很多**  
>   
> ![d96b5aa0ac884eb29195b7cbd8c3808c.png][]  
>   
>

##### dom命令出 #####

> 找到我们的mysql的安装位置，找到bin文件夹，cmd进去。
> 
> 导出的数据在**mysql**的**bin**目录下

###### 导出表数据和表结构 ######

> **mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql(这个名字随便叫)**  
> 
> 
>      mysqldump -uroot -p123456 mybatis_ssm > 1234567.sql
> 
> ![3006fe5fdc9f4d928111a8270a4e21ec.png][]  
> 我们的bin文件夹下面就有123456.sql文件
> 
> ![24fab27e9d3b4821a4319990ccdd1982.png][]

###### 只导出表结构 ######

> **mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql**  
> 
> 
>     mysqldump -uroot -p -d abc > abc.sql
> 
>   
>

#### 导入 ####

> 为了防止多种因素，很多企业都是在dom命令建立数据库，所以我在这里也演示一下；
> 
> 还是在bin文件夹里面cmd命令里面进行
> 
>     mysql -u root -p
> 
> 输入设置用户的密码  
> ![f22073b8a3a04fc08ad29d0f67e04ba3.png][]

**进去之后就可以正常使用sql语句了**

> 注意：首先建立空数据库
> 
>     mysql>create database abc;
> 
>   
> **方法一**
> 
>     mysql>use abc;                   选择数据库
>     mysql>set names utf8;            设置数据库编码
>     mysql>source /D:/SoftwareInstallPath/mysql-8.0.13-winx64/bin/1234567.sql;  导入数据 
> 
> **方法二**  
> 
> 
>     mysql -u用户名 -p密码 数据库名 < 数据库名.sql
>                 #mysql -uabc_f -p abc < abc.sql

#### **LOAD DATA INFILE 导入导出** ####

> 可先通过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 "D:/TEST/test.txt"**；就可以了。

-- 导出
    select * from t_log into outfile 'D:/12345678.sql';
    -- 导出
    load data infile 'D:/12345678.sql' into table t_log(id,ip,userid,moduleid,content,createdate,url); 
    show variables like 'secure%'
    desc t_log;
    
    select * FROM t_log;

## 【注意】 ##

> 在使用视图、索引和数据导入导出时，需要根据具体的数据结构和业务需求进行慎重的选择和操作。此外，视图和索引的创建需要考虑数据库的性能和资源消耗，并根据实际情况进行适当的优化和管理。

[4111385d86e141b6b72adbb869bc4aad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/831356870e1243648b8737e530fd54b4.png
[f459fe8ea5b447a9980f613077ceca6f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/bd626d3ccc304f06b17b50d199e46b07.png
[307871385a8e47c5a09d47397eba139e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/8a90c9cf266d48ceb01f3407eb72cee2.png
[c8185b847fbe4a6a8303f321388ac0ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f22cc8382b1443708e31e069e6c7bf77.png
[c6fc22c1770143bb86dad9f94533ffce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/1e660e27fa70429895a9626960166480.png
[013d1fe70d494f0f83e09a657a963ae3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/34c80485d72643ed9d2483b81743812d.png
[d923e4525a564baf8db6d6e9080afc4d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/e911cc9bf8b748928d6fe1091b0d26d0.png
[c29ef2525c18447492ee92e78007c62f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/444029d2fd1f407d9223aef17873b809.png
[a49e413c3b29417c9decf342ec6a5957.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/19e5fc7ab3ec446688ee73984cb3cf3f.png
[b7cf9a868e2d47629264b05af480a033.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/8271731f74f144f286d1b3990db5eb8d.png
[636a61580a1c46f6bf524c726c12414f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/c50866710bca41e5a22f2d81f26886b3.png
[d96b5aa0ac884eb29195b7cbd8c3808c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/82f25d95d7ed47e0a0b8d6a989bdb358.png
[3006fe5fdc9f4d928111a8270a4e21ec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/e412826d750c4144a2f46e1389c36ae0.png
[24fab27e9d3b4821a4319990ccdd1982.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/253ff81fee6c45e19952c23dbdb0b296.png
[f22073b8a3a04fc08ad29d0f67e04ba3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/7ae9fc3dff074302a72246fdbdba0137.png