DBA03： MySQL存储引擎 、 数据导入导出 管理表记录 匹配条件

一、mysql存储引擎

1.1  MySQL工作原理

1. connectors

   与其他编程语言中的sql 语句进行交互，如php、java等。

2. Management Serveices & Utilities

   系统管理和控制工具

3. Connection Pool (连接池)

管理缓冲用户连接，线程处理等需要缓存的需求

4. SQL Interface (SQL接口)

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

5. Parser （解析器）

SQL命令传递到解析器的时候会被解析器验证和解析。

主要功能：

a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，后面SQL语句的传递和处理就是基于这个结构的

b.  如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的，语句将不会继续执行下去

6. Optimizer (查询优化器)

SQL语句在查询之前会使用查询优化器对查询进行优化(产生多种执行计划,最终数据库会选择最优化的方案去执行,尽快返会结果) 他使用的是“选取-投影-联接”策略进行查询。

用一个例子就可以理解： select uid,name from user where gender = 1;

这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤

这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤

将这两个查询条件联接起来生成最终查询结果.

7. Cache和Buffer (查询缓存)

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

8.Engine (存储引擎)

存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。

Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）

SQL 语句执行过程

   首先程序的请求会通过mysql的connectors与其进行交互，请求到处后，会暂时存放在连接池（connection pool)中并由处理器（Management Serveices & Utilities）管理。当该请求从等待队列进入到处理队列，管理器会将该请求丢给SQL接口（SQL Interface）。SQL接口接收到请求后，它会将请求进行hash处理并与缓存中的结果进行对比，如果完全匹配则通过缓存直接返回处理结果；否则，需要完整的走一趟流程：

   (1)由SQL接口丢给后面的解释器（Parser），上面已经说到，解释器会判断SQL语句正确与否，若正确则将其转化为数据结构。

   (2)解释器处理完，便来到后面的优化器（Optimizer），它会产生多种执行计划,最终数据库会选择最优化的方案去执行,尽快返会结果。

   (3)确定最优执行计划后，SQL语句此时便可以交由存储引擎（Engine）处理，存储引擎将会到后端的存储设备中取得相应的数据，并原路返回给程序。

这里有几点需要注意：

(1)如何缓存查询数据？

存储引擎处理完数据，并将其返回给程序的同时，它还会将一份数据保留在缓存中，以便更快速的处理下一次相同的请求。具体情况是，mysql会将查询的语句、执行结果等进行hash，并保留在cache中，等待下次查询。

(2)buffer与cache的区别？

从上面的图可以看到，缓存那里实际上有buffer和cache两个，那它们之间是否有什么不同呢？简单的说就是，buffer是写缓存，cache是读缓存。

 

1.2 MySQL 存储引擎

• 作为可插拔式的组件提供
   – MySQL 服务软件自带的功能程序,处理表的处理器
   – 不同的存储引擎有不同的功能和数据存储方式
• 默认的存储引擎
  – MySQL 5.0/5.1 —> MyISAM
  – MySQL 5.5/5.6 —> InnoDB

1.2.1  查看数据库服务支持的存储引擎

mysql>   show  engines;
InnoDB             DEFAULT

1.2.2  查看已有的表使用的存储引擎

show   create  table 表名；

1.2.3  修改数据库服务默认使用的存储引擎

]#vim /etc/my.cnf

  
  
  

   

    
    
    

     
     
     

    
    
    
    
    
    

     
     
     

      
      
      [
      
      
      mysqld]
     
     
     
    
    
    
   

    
    
    

     
     
     

    
    
    
    
    
    

     
     
     

      
      
      default-storage-engine=myisam
     
     
     
    
    
    
   

    
    
    

     
     
     

    
    
    
    
    
    

     
     
     

      
      
      …
     
     
     
    
    
    
  
  
  
  

]# systemctl  restart   mysqld

1.2.4  修改表/建表指定表的存储引擎

alter  table  表   engine=存储引擎名；
create  table  表（字段列表）engine=存储引擎名；

1.2.5 常用存储引擎的特点

innodb存储引擎特点:
支持事务  、 事务回滚、外键
支持行级锁     （客户端连接数据库服务器后，对表的数据做访问时，若表的存储引擎是innodb的话，会只给表中被访问的行加锁）
存储方式： 一个表对应2个存储文件
                 表名.frm    表结构
                 表名.ibd     数据和索引

myisam存储引擎特点:
不支持事务  、 事务回滚、外键
支持表级锁    （客户端连接数据库服务器后，对表的数据做访问时，若表的存储引擎是myisam的话，会给整张表加锁）
存储方式： 一个表对应3个存储文件
                表名.frm     表结构
                表名.MYD   数据
                表名.MYI     索引

memory存储引擎特点：

所有 的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

  1. 事务（Transactions）：一次sql操作从建立连接到操作完成断开连接的访问过程给称作事务。

         – 支持事务的可以做事务回滚 ：一次sql操作有任意一步没有执行成功会恢复所有操作。（对innodb存储引擎的表 访问时 必须任意一步操作都成功，才能完成操作。）

    2. mysql数据库服务使用事务日志文件记录对innodb存储引擎表执行的sql操作。
cd  /var/lib/mysql/
ib_logfile0 -|
                   |——> 记录SQL命令
ib_logfile1 -|
                                 insert into  t1  values(8888);
ibdata1 —-> 数据源（sql命令执行后产生的数据信息）

1.2.6 MySQL 锁机制

锁粒度：
表级锁（myisam）给整张表加锁
行级锁 (innodb)    只给表中当前被操作行加锁

锁的作用：解决对表的并发访问冲突问题。  (读锁和写锁是由sql语句决定的，行锁和表锁是由存储引擎决定的)
                select  *  from  t1  where   id <=20;
                 insert
                delete   from  t1;
                update  t1  set  name=”bob”  where  name=”lucy”;
                update  t1  set  name=”tom”  where  name=”jerry”;

锁类型：
读锁（共享锁）  当对一张表执行查询(select)操作时 会加读锁
写锁（排他锁或互斥锁） 当对一张表执行写（insert update  delete）操作时 会加写锁

1.2.7  存储引擎的选择

执行写操作多的表适合使用innodb存储引擎，可以并发访问。

update  t1  set  name=”bob” where  id=3;
update  t1  set  name=”bob” where  id=4;
执行查操作多的表适合使用myisam存储引擎,可以节省系统资源。

select  *  from t1 where id <=10;
select  *  from t1 where id >10;

二、数据导入导出（批量操作数据）

2.1  数据导入

基本用法
    – LOAD  DATA   INFILE  ”目录/文件名”   
      INTO  TABLE   库.表
      FIELDS TERMINATED   BY  ”列间隔符号”  
      LINES TERMINATED BY  ”\n”

注意事项
    – 字段分隔符要与文件内的一致
    –指定导入文件的绝对路径
    –导入数据的表字段类型要与文件字段匹配
    –禁用Selinux保护机制

1.查看数据库的默认导入导出文件存放目录
mysql>  show variables    like “secure_file_priv”;

2.修改默认导入导出文件目录
[root@host50 ~]# mkdir /mydata

[root@host50 ~]# chown mysql /mydata

[root@host50 ~]# vim /etc/my.cnf

[mysqld]

  ..   secure_file_priv=”/mydata”   .. 

[root@host50 mydata]# systemctl restart mysqld    //重启服务

mysql>  show variables    like “secure_file_priv”;

3.创建存储数据表（表结构参考/etc/passwd）
create  database  db3;
create  table  db3.usertab(       
username    char(50),
password     char(1),
uid               int(2),
gid              int(2),
comment   char(100),
homedir     char(100),
shell         char(50),
index(username)
);