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

目录

前言：

一、导入导出

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

2.使用mysqldump导入导出

二，视图

1.什么是视图   

 2.视图的作用

 3.基本语法

 三、索引

四、执行计划：

---

前言：

今天分享的知识还是与MySQL相关，上次分享的知识里还剩下导入导出和视图没有讲，今天顺便补充一下

一、导入导出

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

首先需要建好相对应的表，这里博主用的是t_log表，然后需要有大量的数据（没有大量的数据就可以手动，博主用到的是navicat软件）

 

 点开始开始就行，看到进度条满了就点关闭

 查询后：

 但是这种方式耗时事件长，数据如果上百万数据，上亿数据耗时非常长，而且万一不能用连接器就不行了

2.使用mysqldump导入导出

先找到安装的bin目录

select * from t_log
-- delete 与 truncate 的区别  drop(表也顺便删除了）
-- delete 耗时 1.696s
DELETE from t_log
-- truncate 耗时 0.171s
truncate table t_log

truncate 删除数据时间快

-- 耗时20多秒,耗时时间长
1.使用工具，类似Sqlyog、Navicat等导入导出数据。

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


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,...); 

注意：执行命令的时候无需登录服务端，但是执行命令需要携带数据库用户以及密码

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

切换数据库，设置编码集：

 

source 数据库名

 数据导入完成：

 耗时时间更短，效率高

 

 LOAD DATA INFILE

 这里创表不成功是因为没有配置my.ini文件

去配置：

导入表： 

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

 

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

二，视图

1.什么是视图   

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

 2.视图的作用

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

 3.基本语法

CREATE VIEW 视图名 AS SELECT 语句;

 三、索引

   1.什么是索引
 
   索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
类似于图书的目录，方便快速定位，寻找指定的内容。
 
   2.索引的优缺点
 
   优点：提高查询数据的速度。
   缺点：创建和维护索引的时间增加了，同时占用硬盘空间。
 
   3.索引分类
 
   1) 普通索引：是最基本的索引，它没有任何限制；
 
   2) 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
 
   3) 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；
 
   4) 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；
 
   5) 全文索引：使用FULLTEXT参数可以设置，全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。主要作用是提高查询较大字符串类型的速度；只有MyISAM引擎支持该索引，MySQL默认引擎不支持；mysql5.7+
 
   分词器  1/2/3

 
   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.11s
select * from t_log where moduleid='11029'
--构建索引时长3.498s
create index idx_moduleid on t_log(moduleid);
--0s
select * from t_log where moduleid='11029'
2) 唯一索引案例 对应列值不允许相同
create unique index 索引名 on 表名(字段名)
 
create UNIQUE INDEX idx_url on t_log(url);
3) 主键索引案例
create table 表名(
   id int not null auto_increment primary key,
   ...
)
--0s
select * from t_log where id='280916ac538019'
4) 组合索引案例
create index 索引名 on 表名(字段名1,字段名2,...)
--> 时间: 5.941s  最左列匹配原则
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=''
 
EXPLAIN SELECT *FROM t_log WHERE url='';
 
EXPLAIN SELECT * from t_log WHERE moduleid='';
 
 
 
 
 
 

四、执行计划：

#示例一：explain
EXPLAIN SELECT * from t_user;
EXPLAIN SELECT * from t_user u LEFT JOIN t_role r on u.roleid=r.roleid
 
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 !='张三'
#6）order by
EXPLAIN select * from 
ORDER BY username
#7）组合索引（遵循最左前缀）
#有效
select 
#无效

好啦，今天的分享就到这里，咱们下次再见！