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

目录

一、导入导出

二、视图

三、索引

四、执行计划

---

一、导入导出

1、一般情况下的导入

选择要导入的数据库，再选择表，右键选择‘运行SQL文件’，选择你要导入的sql文件，点击开始等待即可。

缺点：耗时太长，效率低；

2、对于数据比较多的情况（使用mysqldump导入导出：耗时短）

导出：

 1 导出表数据和表结构
    mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql(这个名字随便叫)

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

   2 只导出表结构
    mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql

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

导入： 

 注意：首先建立空数据库

    mysql>create database abc;

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

  select * from t_log into outfile 'E:/123123.sql';
  select * from t_log into outfile 'D:/123123.sql';
 
  load data infile 'E:/123123.sqll' into table t_log(id,ip,userid,moduleid,content,createdate,url); 

 注意:出现异常需进行配置

找到以.ini结尾的文件并打开，在mysql下secure-file-priv = C\D\E\F:即可

 

二、视图

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+

   分词器  中华/人民/共和国

   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 表名(字段名)

select * from t_log where moduleid='100401666';
create index idx_moduleid on t_log(moduleid);

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

4) 组合索引案例
create index 索引名 on 表名(字段名1,字段名2,...)

补充说明：

批量导入：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) 每张表有多少行被优化器查

 示例一：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相同，从上往下一次执行

目的：sql执行的顺序

左外联：

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 rolenmae = '管理员')；

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

EXPLAIN select * from t_user where id=3
union
select * from t_user where 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 password = 123456 or username='张三'

 

有效

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

 

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

EXPLAIN select * from t_user where username = 1;

有效

EXPLAIN select * from t_user where username = '1';