最近在开发环境发现在某些功能上面做测试时，速度很慢，经查是索引没有创建。


经过分析发现是因为在之前从正式环境导数据到开发环境时，因为开发环境数据空间不足以承担正式环境的数据量，于是在使用imp导入的时候，针对某些大表做了中断导入的操作。

但此操作直接导致该表的索引没有创建。（补数据库导入导出执行过程）


补救思路：通过查找两个环境的差异，得到缺失的索引，并导出重新执行即可。 
Select table_name,index_name,DBMS_METADATA.GET_DDL('INDEX',index_name) from 
	--获得差集
	(Select a.table_name,a.index_name from Dba_Indexes a Where a.owner='PROD'
	Minus
	Select b.table_name,b.index_name from Dba_Indexes b Where b.owner='DEV') u;


以后处理类似事情的时候要谨慎，完成后要做好检查工作才行，避免出现类似的情况发生。

一、实验简介

1.1 实验内容

本节实验中我们将学习并实践数据库的其它基本操作：索引、视图，导入和导出，备份和恢复等。

这些概念对于数据库管理员而言都非常重要，请仔细理解并完成所有实验操作。

作为基础篇，不会涉及到关于索引和视图的高级应用和核心概念，但是基本操作大家会了解，尤其是关于索引的内容，学会建立恰当的索引可以大大提高数据库的查询效率，更高级的应用我们会在进阶篇详细讲解。

1.2 实验知识点

索引
	
视图
	
导入和导出
	
备份和恢复
二、开发准备


注：如果你是从上一节直接进入本节进行学习的，请先删除上一节建立的数据库 mysql_shiyan ，删除语句为 DROP DATABASE mysql_shiyan; 。


在正式开始本实验内容之前，需要先下载相关代码。

下载的代码会搭建好一个名为 mysql_shiyan 的数据库（有三张表：department，employee，project），并向其中插入数据。

具体操作如下,首先输入命令进入 /home/shiyanlou/Desktop 目录：

cd /home/shiyanlou/Desktop


然后再输入命令，下载代码：

git clone https://github.com/shiyanlou/SQL6


下载完成后，输入命令开启 MySQL 服务并使用 root 用户登录：

# 打开 MySQL 服务
sudo service mysql start

# 使用 root 用户登录
mysql -u root


下载的 SQL6 目录下，有两个文件 MySQL-06.sql 和 in.txt，其中第一个文件用于创建数据库并向其中插入数据，第二个文件用于测试数据导入功能。

（SQL6 目录在桌面上，你可以尝试查看、编辑里面的文件。）

如果你是接着上一个实验操作的话，首先把 mysql_shiyan 数据库删掉：

mysql> drop database mysql_shiyan;


输入命令运行第一个文件，搭建数据库并插入数据：

mysql> source /home/fangsi/桌面/Python/MYSQL/SQL_4/SQL6/MySQL-06.sql

 

三、实验步骤

本节将通过实践操作，带领大家熟悉数据库的其它操作。

3.1 索引

索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行对比，然后返回满足条件的记录。这样做会执行大量磁盘 I/O 操作，并花费大量数据库系统时间。

而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。

对一张表中的某个列建立索引，有以下两种语句格式：

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);

CREATE INDEX 索引名 ON 表名字 (列名);


我们用这两种语句分别建立索引：

ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名为idx_id的索引

CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名为idx_name的索引


索引的效果是加快查询速度，当表中数据不够多的时候是感受不出它的效果的。这里我们使用命令 SHOW INDEX FROM 表名字; 查看刚才新建的索引：





在使用 SELECT 语句查询的时候，语句中 WHERE 里面的条件，会自动判断有没有可用的索引。

比如有一个用户表，它拥有用户名(username)和个人签名(note)两个字段。其中用户名具有唯一性，并且格式具有较强的限制，我们给用户名加上一个唯一索引；个性签名格式多变，而且允许不同用户使用重复的签名，不加任何索引。

这时候，如果你要查找某一用户，使用语句 select * from user where username=? 和 select * from user where note=? 性能是有很大差距的，对建立了索引的用户名进行条件查询会比没有索引的个性签名条件查询快几倍，在数据量大的时候，这个差距只会更大。

一些字段不适合创建索引，比如性别，这个字段存在大量的重复记录无法享受索引带来的速度加成，甚至会拖累数据库，导致数据冗余和额外的 CPU 开销。

3.2 视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。

注意理解视图是虚拟的表：

数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；
	
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；
	
视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；
	
在使用视图的时候，可以把它当作一张表。
创建视图的语句格式为：

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;


可见创建视图的语句，后半句是一个 SELECT 查询语句，所以视图也可以建立在多张表上，只需在 SELECT 语句中使用子查询或连接查询，这些在之前的实验已经进行过。

现在我们创建一个简单的视图，名为 v_emp，包含v_name，v_age，v_phone三个列：

CREATE VIEW v_emp (v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;



3.3 导入

此处讲解的是导入一个纯数据文件，该文件中将包含与数据表字段相对应的多条数据，这样可以快速导入大量数据，除此之外，还有用 SQL 语句的导入方式，语法为：source *.sql 这是实验中经常用到的。两者之间的不同是：数据文件导入方式只包含数据，导入规则由数据库系统完成；SQL 文件导入相当于执行该文件中包含的 SQL 语句，可以实现多种操作，包括删除，更新，新增，甚至对数据库的重建。

数据文件导入，可以把一个文件里的数据保存进一张表。导入语句格式为：

LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;


现在 SQL6 目录下有一个名为 in.txt 的文件，我们尝试把这个文件中的数据导入数据库 mysql_shiyan 的 employee 表中。

由于导入导出大量数据都属于敏感操作，根据 mysql 的安全策略，导入导出的文件都必须在指定的路径下进行，在 mysql 终端中查看路径变量：

mysql -uroot
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)


注意到 secure_file_priv 变量指定安全路径为 /var/lib/mysql-files/ ，要导入数据文件，需要将该文件移动到安全路径下。

打开 Xfce 终端，输入命令拷贝 SQL6 文件夹到 /var/lib/mysql-files/ 目录：

sudo cp -a /home/shiyanlou/Desktop/SQL6 /var/lib/mysql-files/


使用命令 sudo vim /var/lib/mysql-files/SQL6/in.txt 查看 in.txt 文件中的内容：

6	Alex	26	3000	123456	dpt1
7	Ken	27	3500	654321	dpt1
8	Rick	24	3500	987654	dpt3
9	Joe	31	3600	100129	dpt2
10	Mike	23	3400	110110	dpt1
11	Jim	35	3000	100861	dpt4
12	Mary	21	3000	100101	dpt2

 

可以看到其中仅仅包含了数据本身，没有任何的 SQL 语句

再使用以下命令以 root 用户登录数据库，再连接 mysql_shiyan 数据库：

# 在Xfce 终端输入命令
mysql -u root

# 在 MySQL 控制台中输入命令
use mysql_shiyan


查看一下没有导入数据之前，employee 表中的数据：

mysql> SELECT * FROM employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Jobs | NULL |   3600 |  19283 | dpt2   |
|  4 | Tony | NULL |   3400 | 102938 | dpt3   |
|  5 | Rose |   22 |   2800 | 114114 | dpt3   |
+----+------+------+--------+--------+--------+
5 rows in set (0.08 sec)

mysql> 


现在执行导入语句，文件中的数据成功导入 employee 表：

mysql> LOAD DATA INFILE '/var/lib/mysql-files/SQL6/in.txt' INTO TABLE employee;
Query OK, 7 rows affected (0.67 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Jobs | NULL |   3600 |  19283 | dpt2   |
|  4 | Tony | NULL |   3400 | 102938 | dpt3   |
|  5 | Rose |   22 |   2800 | 114114 | dpt3   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
|  8 | Rick |   24 |   3500 | 987654 | dpt3   |
|  9 | Joe  |   31 |   3600 | 100129 | dpt2   |
| 10 | Mike |   23 |   3400 | 110110 | dpt1   |
| 11 | Jim  |   35 |   3000 | 100861 | dpt4   |
| 12 | Mary |   21 |   3000 | 100101 | dpt2   |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)

mysql> 


3.4 导出

导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。导出语句基本格式为：

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;


注意：语句中 “文件路径” 之下不能已经有同名文件。

现在我们把整个 employee 表的数据导出到 /var/lib/mysql-files/ 目录下，导出文件命名为 out.txt 具体语句为：

SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;


用 gedit 可以查看导出文件 /var/lib/mysql-files/out.txt 的内容：


也可以使用 sudo cat /var/lib/mysql-files/out.txt 命令查看。


mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;
Query OK, 12 rows affected (0.38 sec)

fangsi@fangsi-virtual-machine:~/桌面/Python/MYSQL/SQL_4/SQL6$ sudo gedit /var/lib/mysql-files/out.txt


1	Tom	26	2500	119119	dpt4
2	Jack	24	2500	120120	dpt2
3	Jobs	\N	3600	19283	dpt2
4	Tony	\N	3400	102938	dpt3
5	Rose	22	2800	114114	dpt3
6	Alex	26	3000	123456	dpt1
7	Ken	27	3500	654321	dpt1
8	Rick	24	3500	987654	dpt3
9	Joe	31	3600	100129	dpt2
10	Mike	23	3400	110110	dpt1
11	Jim	35	3000	100861	dpt4
12	Mary	21	3000	100101	dpt2

3.5 备份

数据库中的数据十分重要，出于安全性考虑，在数据库的使用中，应该注意使用备份功能。


备份与导出的区别：导出的文件只是保存数据库中的数据；而备份，则是把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件。


mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 备份的语句：

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表



mysqldump 是一个备份工具，因此该命令是在终端中执行的，而不是在 mysql 交互环境下


我们尝试备份整个数据库 mysql_shiyan，将备份文件命名为 bak.sql，先 Ctrl+D 退出 MySQL 控制台，再打开 Xfce 终端，在终端中输入命令：

cd /home/shiyanlou/
mysqldump -u root mysql_shiyan > bak.sql;


使用命令 “ls” 可见已经生成备份文件 bak.sql：

fangsi@fangsi-virtual-machine:~/桌面/Python/MYSQL/SQL_4/SQL6/Copy_MySQL$ mysqldump -u root -p mysql_shiyan > bak.sql;
Enter password: 
fangsi@fangsi-virtual-machine:~/桌面/Python/MYSQL/SQL_4/SQL6/Copy_MySQL$ ls
bak.sql
fangsi@fangsi-virtual-machine:~/桌面/Python/MYSQL/SQL_4/SQL6/Copy_MySQL$ 



你可以用 gedit 查看备份文件的内容，可以看见里面不仅保存了数据，还有所备份的数据库的其它信息。


 

3.6 恢复

用备份文件恢复数据库，其实我们早就使用过了。在本次实验的开始，我们使用过这样一条命令：

source /tmp/SQL6/MySQL-06.sql


这就是一条恢复语句，它把 MySQL-06.sql 文件中保存的mysql_shiyan 数据库恢复。

还有另一种方式恢复数据库，但是在这之前我们先使用命令新建一个空的数据库 test：

mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录

CREATE DATABASE test;  #新建一个名为test的数据库


再次 Ctrl+D 退出 MySQL，然后输入语句进行恢复，把刚才备份的 bak.sql 恢复到 test 数据库：

mysql -u root -p test < bak.sql


我们输入命令查看 test 数据库的表，便可验证是否恢复成功：

mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录

use test               #连接数据库test

SHOW TABLES;           #查看test数据库的表


可以看见原数据库的4张表和1个视图，现在已经恢复到 test 数据库中：

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| department     |
| employee       |
| project        |
| table_1        |
| v_emp          |
+----------------+
5 rows in set (0.04 sec)

mysql> 


再查看 employee 表的恢复情况：

mysql> SELECT * FROM employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Jobs | NULL |   3600 |  19283 | dpt2   |
|  4 | Tony | NULL |   3400 | 102938 | dpt3   |
|  5 | Rose |   22 |   2800 | 114114 | dpt3   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
|  8 | Rick |   24 |   3500 | 987654 | dpt3   |
|  9 | Joe  |   31 |   3600 | 100129 | dpt2   |
| 10 | Mike |   23 |   3400 | 110110 | dpt1   |
| 11 | Jim  |   35 |   3000 | 100861 | dpt4   |
| 12 | Mary |   21 |   3000 | 100101 | dpt2   |
+----+------+------+--------+--------+--------+
12 rows in set (0.29 sec)

mysql> 


四、实验总结

在本实验中，我们实践了下面几个基本概念：

索引：可以加快查询速度
	
视图：是一种虚拟存在的表
	
导入：从文件中导入数据到表
	
导出：从表中导出到文件中
	
备份：mysqldump 备份数据库到文件
	
恢复：从文件恢复数据库
下一节我们将学习 MySQL 服务安装及命令使用 。

五、课后习题

建立员工名字 employee.name 和对应部门人数 department.people_num 的视图并展示。

练习题参考答案

以下内容仅供参考，为了能有更好的学习效果，请尽量按自己的想法来完成练习题。

mysql> CREATE VIEW name_people_num (name, people_num)
    -> AS SELECT name, people_num FROM employee, department
    -> WHERE in_dpt = dpt_name;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM name_people_num;
+------+------------+
| name | people_num |
+------+------------+
| Alex |         11 |
| Ken  |         11 |
| Mike |         11 |
| Jack |         12 |
| Jobs |         12 |
| Joe  |         12 |
| Mary |         12 |
| Tony |         10 |
| Rose |         10 |
| Rick |         10 |
| Tom  |         15 |
| Jim  |         15 |
+------+------------+
12 rows in set (0.00 sec)

1.oracle数据库的导出：
（1）打开cmd窗口输入命令，将数据库表结构、存储过程、触发器、索引等各项内容全部导出，包括表中的数据也会导出：
exp 用户名/密码@实例名 file:=F:\db\dbtest.dmp
（2）同上导出，不过不导出表中数据：
exp 用户名/密码@实例名 file:=F:\db\dbtest.dmp owner=(用户名)rows=n
（3）导出oracle数据库某表结构及其数据：

 exp 用户名/密码@实例名
file=F:\db\tabledatabackup.dmp tables=A(导出多张表用逗号隔开,如：table=A,B)

最后数据库导出的文件会存在F:\db目录下，可将其拷贝至需要导入的服务器上。
（4）导出oracle数据库某表结构及其部分符合条件的数据：

exp 用户名/密码@实例名 tables=tableloginlog query=\"where logintime>='2017-02-22'\" file=F:\db\tableloginlogbackup.dmp

2.oracle数据库的导入：
将导出的dmp文件放至E:\db下准备导入，打开cmd窗口输入命令：
imp 用户名/密码@实例名  file=E:\db\dbtest.dmp full=y ignore=y

步骤

选中表->右击->导出向导->选择列、保存路径->开始

其中导出的csv文件不含列索引，故用pandas读取该csv文件时,命令：

#header=None代表csv文件无列索引，有列索引时忽略列索引用header=0
df=pd.read_csv(path,header=None,names=['CAPTURE_TIME','DEVICE_ID','LNG','LAT'])