MySQL--视图&导入导出&索引&执行计划

一、索引

1.使用工具，类似Sqlyog、Navicat等导入导出数据。

直接在要导入的数据库建立右键运行sql文件

然后选择我们要导入的数据即可

耗时47.35秒

2.使用mysqldump导入导出

2.1导出

2.1.1 导出表数据和表结构

mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql(这个名字随便叫)

#/usr/local/mysql/bin mysqldump -uroot -pabc >abc.sql
找到我们MySQL的安装路径，找到bin命令

执行命令时无需登录服务端，但是执行命令需要携带数据库用户及密码
输入命令即可

mysqldump -uroot -p123 mybatis_ssm > 7654321.sql

然后我们bin目录下面就有我们导入的文件

2.1.2 只导出表结构

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

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

在bin目录下输入该命令即可

2.2 导入

注意：首先建立空数据库
mysql>create database abc;

2.2.1 方法一

mysql>use abc; #选择数据库
mysql>set names utf8; #设置数据库编码
mysql>source /home/abc/abc.sql; #导入数据

首先连接数据库

然后创建空数据库abcde

然后切换数据库到abcde

然后修改编码集

然后导入我们的数据7654321.sql


然后我们就可以看到我们的数据库有一个abcd的库

耗时短,效率高

3.LOAD DATA INFILE

可先通过SELECT INTO OUTFILE方式，将数据导出到Mysql的C:\ProgramData\MySQL\MySQL Server 5.5\data目录下，再通过LOAD DATA INFILE方式导入。

3.1 select * from 表名 into outfile ‘/文件名.sql’;

select * from t_log into OUTFILE 'E:/111111.sql'

结果：

但是如果我们换一盘

select * from t_log into OUTFILE 'D:/111111.sql'

就会报错

因为我们没有权限
我们找到我们安装MySQL的目录，然后找到my.ini

给他加一个,改成D就行了
secure_file_priv=D;

3.2 load data infile ‘/文件名.sql’ into table 表名(列名1,…);

首先找到E盘中的对应文件,然后对应的列名要一模一样

load data infile 'E:/111111.sql' into table t_log(id,ip,userid,moduleid,content,createdate,url);

结果

耗时时间8秒8

二、视图

1.什么是视图

1）视图是一种虚拟的表，是从数据库中一个或多个表中导出来的表。
2）数据库中存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原数据表中
3） 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

2.视图的作用

1. 使操作简便化。
2. 增加数据的安全性。
3. 提高表的逻辑独立性。

3. 基本语法

CREATE VIEW 视图名 AS SELECT 语句;

4.案例

现在有表t_student,t_score,t_teacher,t_course

我们把学生的成绩放到一个视图里面去

CREATE VIEW v_student_score as 
 select * from t_student s,t_score sc,t_teacher t,t_course c
where s.sid = sc.sid and sc.cid = c.cid and c.tid = t.tid

如果直接运行就会报错

说明视图不能有重复列

 CREATE VIEW v_student_score as 
 select s.*,sc.score,c.*,t.tname from t_student s,t_score sc,t_teacher t,t_course c
where s.sid = sc.sid and sc.cid = c.cid and c.tid = t.tid

三、索引

1.什么是索引

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

2.索引的优缺点

优点：提高查询数据的速度。
缺点：创建和维护索引的时间增加了，同时占用硬盘空间。

3.索引分类

1. 普通索引：是最基本的索引，它没有任何限制；
   2) 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
   3) 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；
   4) 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；
   5) 全文索引：使用FULLTEXT参数可以设置，全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。主要作用是提高查询较大字符串类型的速度；只有MyISAM引擎支持该索引，MySQL默认引擎不支持；mysql5.7+
   分词器 中华/人民/共和国

4.创建索引

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

5.修改索引

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

6.删除索引

DROP INDEX 索引名 ON 表名

7.案例

1 普通索引案例

select * from t_log where moduleid = '100501111';

耗时1秒多

构建索引

CREATE INDEX idx_moduleid on t_log(moduleid)

需要4秒

然后我们在运行查询SQL语句

查询效率提高了

2.唯一索引案例

给url构建一个索引

**CREATE UNIQUE INDEX idx_url on t_log(url);**

这样直接运行会报错(重复)

所以对应列值不允许相同

3. 主键索引案例

create table 表名(
id int not null auto_increment primary key,
…
)

select * from t_log where id='00d3f9ffca154dd9aa280916ac538019'

所以我们通常查询用到id查询，这样效率快

4.组合索引案例

create index 索引名 on 表名(字段名1,字段名2,…)

 CREATE INDEX idx_userid_moduleid on t_log(userid,moduleid,url)

对于组合索引有一个原则，最左匹配原则

EXPLAIN select * from t_log 

可以发现没有经过索引

EXPLAIN select * from t_log where userid = ''; 

这个经过了一个索引idx_userid_moduleid

EXPLAIN select * from t_log where userid = '' and moduleid = '';

这个经过了索引idx_moduleid

EXPLAIN select * from t_log where userid = '' and moduleid = '' and url = '';

经过索引idx_moduleid
当我们直接经过url

EXPLAIN select * from t_log where url = '';

那么就不会经过索引了

四、执行计划

有表t_user

t_role

案例一：explain

EXPLAIN select * FROM t_user

其中id作用是觉得SQL语句的执行顺序

EXPLAIN select * from t_user u WHERE u.roleid = (select roleid from t_role where rolename = '管理员')

这个可以分析得来，id越大，优先级越高

案例二：id相同，从上往下一次执行
左外联

EXPLAIN select * from t_user u left join t_role r on u.roleid = r.roleid

右外联

EXPLAIN select * from t_user u right join t_role r on u.roleid = r.roleid

#注：通过left join 和 right join 验证；id一样（注意执行计划的table列），left join 先扫描a表，再扫描b表；right join 先扫描b表，再扫描a表
id越大优先级越高

案例三：select_type

SIMPLE(简单SELECT,不使用UNION或子查询等)

EXPLAIN select * FROM t_user

#2）PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
SUBQUERY(子查询中的第一个SELECT)

EXPLAIN select * from t_user u WHERE u.roleid = (select roleid from t_role where rolename = '管理员')

案例三:UNION

UNION(UNION中的第二个或后面的SELECT语句)
UNION RESULT: 从 union 表获取结果的 SELECT

EXPLAIN select u1.* from t_user u1 where u1.id = 3
union
select u2.* from t_user u2 where u2.roleid = 3

最后面会多一个取整的table

案例四：正确使用索引

使用 like 语句时，%在右边才会使用索引。

有效

EXPLAIN select * from t_user where username like '张%'

无效

EXPLAIN select * from t_user where username like '%张%'

or条件中有未建立索引的列才索引失效

无效

EXPLAIN select * from t_user where username='张三' or PASSWORD = 123456 

有效

EXPLAIN select * from t_user where username='张三' and PASSWORD = 123456 

条件的类型不一致

无效

EXPLAIN select * from t_user where username=1

有效

EXPLAIN select * from t_user where username='1'

order by

EXPLAIN select * from t_user ORDER BY username