目录

一、数据的导入导出的三种方式

第一种导入方式：

1.使用工具，类型sqlyog、Navicat等导入导出数据

delete与truncate的区别  drop的区别 

第二种导入导出方式：（以命令的形式）

2.使用mysqldump导入导出 

导出： 

导入： 

 二、视图

 1.什么是视图

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

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

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

2.视图的作用 

①使操作简便化

②增加数据的安全性

③提高表的逻辑独立性 

3.基本语法

三、索引

1.什么是索引

2.索引的优缺点 

3.索引分类 

4.创建索引 

5.修改索引 

 6.删除索引

案例 

四、执行计划 

1.什么是执行计划 

2.执行计划的作用： 

3.执行计划包含的信息 

案例 

 示例一：explain

 示例二：id

示例三：select_type 

 示例四：正确使用索引 

---

一、数据的导入导出的三种方式

首先我们要先创建一个表：

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;

第一种导入方式：

1.使用工具，类型sqlyog、Navicat等导入导出数据

导入数据步骤：

 

---

我们以t_log表内的数据为例：导入的时长是每个导入的时长不一样，有点几秒有的2分钟等等

 

数据如果上百万数据，而且万一不能 用连接器就不行了

--耗时 2:22.23s  耗时时间长，效率低  

delete与truncate的区别  drop的区别 

目前我电脑上耗时：
-- delete 耗时 6.039s
delete from t_log;
-- truncate耗时 0.203s
truncate table t_log;

第二种导入导出方式：（以命令的形式）

2.使用mysqldump导入导出 

 2.1导入

2.1.1导入表数据和表结构

执行命令的时候无需登录服务端，但是执行命令需要携带数据库用户及密码
    mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql(这个名字随便叫)

    #/usr/local/mysql/bin mysqldump -uroot -pabc >abc.sql
    敲回车之后提示输出密码

2.1.2只导出表结构

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

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

  
    注：导出的数据在mysql的bin目录下

2.2导入

-- 进入服务端
    注意：首先建立空数据库

    mysql>create database abc;

2.2.1方法一

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

2.2.2方法二

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

下面就是操作示范：

这是我们就要找到mysql的安装包的bin目录下：输入cmd

导出： 

开权限：

注意bin目录： 

此时已经将数据导出了

导入： 

打开权限，并进入创建数据库

 

-- 耗时时间50s ，耗时时间短，效率高

单表操作：

导入单张表：

 

3.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,...); 
    
    -- 0.804s
    select * from t_log into outfile 'F:/111111.sql';

-- 要开权限，在my.ini 加上secure-file-priv：指定位置比如F/E
  -- 错误： The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 

 

导入表: 

 -- 耗时时间 8.812s
  

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

 二、视图

 1.什么是视图

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

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

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

2.视图的作用 

①使操作简便化

②增加数据的安全性

③提高表的逻辑独立性 

3.基本语法

CREATE VIEW 视图名 AS SELECT 语句;  

-- 视图创建成功的前提不能出现重复列
 

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

-- 错误：Duplicate column name 'sid' 有重复列

-- 怎么改
 

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

--查询出来有结果
select * from v_student_score;

 

三、索引

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 表名

案例 

1) 普通索引案例
create index 索引名 on 表名(字段名)

-- 0.576s
select * from t_log where moduleid = '100401999';

-- 创建一个索引 

-- 构建索引时长 4.594s
create index idx_moduleid on t_log(moduleid);
-- 0.001s
select * from t_log where moduleid = '100401999';

 

2) 唯一索引案例   对应列值不允许相同
create unique index 索引名 on 表名(字段名)
-- 比如说我要给url构建索引

create unique index idx_url on t_log(url);

-- 错误：Duplicate entry '/quartz/queryJobLst' for key 'idx_url'  重复

 

3) 主键索引案例
create table 表名(
   id int not null auto_increment primary key,
   ...
)

--  0.001s
select * from t_log where id='00d3f9ffca154dd9aa280916ac538019';

-- id的索引是最高的，其次才是普通索引

4) 组合索引案例      最左列段匹配原则
create index 索引名 on 表名(字段名1,字段名2,...)

-- 构建索引时长是相对长的 13.579s
create index idx_userid_moduleid_url on t_log(userid,moduleid,url);

-- 完全没有走索引

explain select * from t_log;

 -- 走了索引

explain select * from t_log where userid='';

 

-- 走了索引 

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

 -- 也走了索引

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

-- 一样的走了索引   最左也要userid

explain select * from t_log where userid='' and url='';

-- 现在我们不带userid

explain select * from t_log where url='';

-- 它走的是第一个索引，

explain select * from t_log where moduleid='';

 -- 索引的是提高我们查询效率

MySQL 如何调优

补充说明：

批量导入：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,...);  

四、执行计划 

 

 

  

1.什么是执行计划 

 使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。

     语法: Explain + sql

2.执行计划的作用： 

     1) 表的读取顺序
     2) 数据读取操作的操作类型
     3) 哪些索引可以使用
     4) 哪些索引被实际使用
     5) 表之间的引用
     6) 每张表有多少行被优化器查

3.执行计划包含的信息 

3.1 id -获取select子句的操作表顺序，有几种情况：

    1) id 相同的情况下执行顺序是由上到下。
     2) id 越大优先级越高，如果是子查询，ID 序列号会递增，id值越大，优先级越高，越先执行。
     3) id 相同又有不相同的，序列号大的会先执行，然后相同的从上到下执行。

3.2 select_type -查询的类别，主要用于区别普通查询，联合查询，子查询等的复杂查询

    1) simple: 简单的select 查询，不包含子查询或者 union
     2) primary: 查询中包含任何复杂的子部分，最外层查询则被标记
     3) subquery: 在 select 或者 where 列表中包含了子查询
     4) derived: 在from 列表中包含子查询被标记为 derived Mysql 会递归执行这些子查询，把结果放到临时表里
     5) union: 若在第二个 select 中出现 union之后，则被标记为 union 若union包含在 from 子句的子查询中，外层 select 将被标记为 derived
     6) union result: 从 union 表获取结果的 SELECT

案例 

 示例一：explain

EXPLAIN SELECT * FROM t_user;

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

 

 示例二：id

1）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表

2）id越大优先级越高 

示例三：select_type 

1）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='管理员' )

3）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

4）DERIVED(派生/衍生表的SELECT, FROM子句的子查询) 

 示例四：正确使用索引 

1）使用 like 语句时，%在右边才会使用索引。
有效：

EXPLAIN SELECT * FROM t_user where username like '张%'

 

无效：

EXPLAIN SELECT * FROM t_user where username like '%张%'

2）or条件中有未建立索引的列才索引失效
无效：

EXPLAIN select * FROM t_user where username ='张三' or `Password`=123456;

 

有效：

EXPLAIN select * FROM t_user where username ='张三' and `Password`=123456;

 

 3）条件的类型不一致
无效：

EXPLAIN select * from t_user where username=1

有效：

EXPLAIN select * from t_user where username='1'

4）!= 号（例外：如果是主键，则会走索引）
无效：

EXPLAIN select * from t_user where username!='张三'

5）order by

EXPLAIN select * from t_user where username!='张三'
order by roleid

7）组合索引（遵循最左前缀）