MySQL知识点补充

目录

一、MySQL之数据导入导出

二、视图

 三、索引

四、执行计划

---

一、MySQL之数据导入导出

MySQ数据导入导出大致有三种方法

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

二、视图

1、视图的定义

1.1视图是一张虚拟的表，是由数据库中一个或多个表的数据所导出来的表。

1.2数据库中存放了视图的定义，但是没有存放视图中的数据，数据还存储在原来的表中

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

2、视图的作用

1、提高了数据表安全性

2、操作起来更方便

3、提高了表的逻辑独立性

3、创建视图

通用语法：

creta view 视图名 as （select 列名 from 表名 where 条件语句 ）查询语句

注意：视图创建时SQL语句不能出现重复列 如图所示：

 三、索引

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 表名(字段名)
2) 唯一索引案例
create unique index 索引名 on 表名(字段名)
3) 主键索引案例
create table 表名(
   id int not null auto_increment primary key,
   ...
)
4) 组合索引案例
create index 索引名 on 表名(字段名1,字段名2,...)

四、执行计划

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

     3.3 table - 显示这一行的数据是关于那个表的

     3.4 type - 显示的是访问类型

     type是较为重要的一个指标，结果值从最好到最坏依次是：
     system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery 
     > index_subquery > range > index > all

     注：一般来说，得保证查询至少达到range级别，最好能达到ref

     1) system: 表中只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个可以忽略不计;
     2) const:  表示通过索引一次就找到了，const用于比较primary key或者unqiue索引，因为只匹配一条数据，
                所以很快，如将主键置于where条件中，Mysql 就能将该查询转换一个常量;
     3) eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见与主键或唯一索引扫描;
     4) ref:    非唯一索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索查询，组合索引查询;
     5) range:  只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，
                一般就是在你的 where 语句中出现了 between ,<,>,in 等查询这种范围扫描比全表扫描要好，
        因为它只需要开始与索引的某一点，而结束与另一点，不用扫描全部索引。
     6) ALL：   全表扫描;
     7) index： 扫描全部索引树;
     8) NULL：  MySQL在优化过程中分解语句，执行时甚至不用访问表或索引;

     3.5 possible_keys

     指出MySQL能使用哪个索引在该表中找到行。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。 

     简而言之：可能使用的key（索引）

     3.6 key 

     实际上使用的索引，如果没用索引，则为NULL，查询中若使用了覆盖索引，则该索引和查询的select 字段重叠。

     3.7 key_len

     显示MySQL决定使用的键长度。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。
  
     3.8 ref

     显示哪个字段或常数与key一起被使用

     3.9 rows

     这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的。
      
     3.10 Extra

     包含不合适在其他列中显示但十分重要的额外信息：

     1) Using index    此值表示mysql将使用覆盖索引，以避免访问表。
     2) Using where    mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
     3) Using temporary    mysql 对查询结果排序时会使用临时表。
     4) Using filesort    mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。