mySql 引擎详解

转载自https://www.cnblogs.com/IT-CPC/p/10883101.html

什么是mySql引擎，有啥用

mySql存储数据时，有不同的存储方式，这些方式都使用了不同的底层实现（如：存储机制，索引技巧，索引技巧，锁定水平），底层实现的差异带来的功能也就不同。在不同的场景下使用合适的存储方式就能让你的数据读写速度更快或者获得额外的功能。

mySql主要有什么存储引擎

主要的有：  MyIsam ,   Mrg_Myisam,   Memory,   Blackhole,   CSV,   Performance_Schema,   Archive,   Federated ,  InnoDB

 

可用通过 show engines 查看你的mySql的存储引擎

 

MyIsam

MyIsam 存储引擎独立于操作系统，简单说就是可用在windows上使用，也可用将数据转移到Lunex操作系统上。系统兼容性很好！！！。这种存储引擎在建表的时候，它会创建3个文件。分别是（.frm, .MYD, .MYI），简单说明一下：.frm 存储表的定义（也就是表结构啦），.MYD 就是表里面的数据，.MYD存储索引。这样的划分操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。 

特点:

1、不支持事务

2、不支持外键

3、查询速度很快。如果数据库insert和update的操作比较多的话采用表锁效率低（建议使用innodb）。

4、对表进行加锁

注：数据库锁参考：https://zhidao.baidu.com/question/158112371.html

 

 

支持3种不同的存储方式，分别是：静态表、动态表、压缩表

静态表：静态表的字段都是非变常类型的。优点是非常迅速，容易缓存，出现故障容易恢复；缺点是占用空间通常比动态表多（因为存储是会按照宽度定义补足空格）

注意：在取数据时，会将数据后面的空格都去掉，如果数据后面本身有空格，那么也会被去掉

动态表：记录不是固定长度的，这样的优点时空间占用少；缺点：频繁的跟新，删除表易造成碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能

压缩表：因为每个记录都是单独压缩的，所以只有非常小的访问开支

 

Mrg_Myisam

 

Mrg_Myisam 存储引擎，其实就是MyIsam的组合，它是将多个使用MyLseam引擎的表聚会，但是它内部没有数据，真正的数据依然是Mylsam引擎表中，但是可用进行删除更新等操作。

使用Mrg_Myisam的场景：同一种数据会根据数据的实践分为多个表，以达到提升效率的作用,这也就是我们常说的分库分表。如果这时要进行查询操作，会非常麻烦，因为你也不知道数据放在那个表里。Mrg_Myisam正式解决这种问题的存储引擎。Mrg_Myisam可以将多个表聚合成一个表统一查询，然后在删除Mrg_Myisam统一的表，原有数据不受影响。

 

Memory

Memory 采用的方案式使用内存当存储介质，优点式响应速度快。但存储到内存上也导致了一个致命的问题就是当mySql进程崩溃的时数据会丢失。此外Memory对存储的数据有一定的要求，要求存储的是长度不变的数据。

Memory索引支持

散列索引：散列索引的使用场景是数据查找时使用 == 匹配，但范围查询（<=, >=, <, >）较慢

B树索引：B树索引可以使用部分查询和通配查询，范围查询较快

Memory使用场景：

1、数据量小、访问非常频繁、在内存中存放数据，数据量过大会导致内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。

2、数据是临时数据，而且立即可用到。那么就比较合适存放在内存中。

3、存储在表中的数据如果丢失也没太大关系，不会造成损失。

 

 

Blackhole

江湖人称 “黑洞搜索引擎”。感兴趣的小伙伴可以参考其他的博客。

 

CSV

 

文件结构：

.frm表结构描述

.csv数据

.csm表状态、当前记录数量等

特点：

1、没有索引、不能为NULL、不能自增

2、更新和删除时先写入到临时文件，然后在rnd_end()函数中重新生成数据文件

3、可以对数据文件直接编辑（保存文本文件内容）

4、以CSV格式进行数据存储（逗号隔开，引号）

5、数据以文本方式存储在文件中

6、CSV存储引擎可以将csv文件作为mysql的表进行处理。存储格式就是普通的csv文件。

 

 

Performace_Schema

MySql 5.5新出的一个存储引擎，主要是用来收集数据库服务器的性能参数。但是我们无法创建该类型的表，它都是有MySql系统自己创建的。

主要提供如下功能：

1、提供进程的详细信息包含了 锁、互斥变量、文件信息

2、保持历史的事件汇总信息，为MySql服务器的性能做出详细的判断。

3、对于新增和删除监控时间点都非常容易，并可以随意的改变Mysql服务器的监控周期

这个需要在配置文件my.cnf中进行配置才能生效。

 

Archive 

archive是归档的意思，仅仅支持插入和查询两种功能，在MySQL5.5以后支持索引功能，他拥有很好的压缩机制，使用zlib压缩库，在记录请求的时候实时的进行压缩，经常被用来作为仓库使用。适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度但是对查询的支持较差

 

Federated

特点：

1、专门针对远程数据库的实现一般情况下在本地数据库中建表会在数据库目录中生成相应的表定义文件，并同时生成相应的数据文件。 
但通过FEDERATED引擎创建的表只是在本地有表定义文件，数据文件则存在于远程数据库中通过这个引擎可以实现类似Oracle 下DBLINK的远程数据访问功能

2、本地的表结构必须与远程的完全一样。 

3、远程数据库目前仅限MySQL 

4、不支持事务 

5、不支持表结构修改 

 

InnoDB

InnoDB是一个事务型的存储引擎，有行级锁定和外键约束,提供了具有提交，回滚和崩溃恢复的事务安全，但是对比MyLSAM引擎，写的效率会比差一些，并且会占用更多的磁盘空间以保持数据和索引。

 

特点: 

1. 更新多的表，适合处理多重并发的更新请求。

2. 支持事务。

3. 可以从灾难中恢复（通过bin-log日志等）。

4. 外键约束。只有他支持外键。

5. 支持自动增加列属性auto_increment。

 

 

下面是各引擎的优劣和使

Mylsam:

优点：系统兼容性好，属于查询速度贼快的那种。使用表格锁定的机制，来优化多个并发的读写操作。支持索引、字段管理

缺点：不支持事务、外键、如果数据库insert和update的操作比较多的话采用表锁效率低（建议使用innodb）、不能在表损坏后恢复数据

使用场景：想得到更好的系统兼容性，很高效的查询速度。但是这个东东不支持事务、外键、如果更新表较多的话对效率也会有影响。

 

Memory:

优点：数据访问快（使用内存做媒介），这是最大的特点

缺点：会占用服务器内存，如果内存非常紧张不推荐使用，而且数据可能会发送丢失，数据只能是临时数据，数据量不能太大，不然内存会溢出的。

使用场景：数据量小，访问频繁，数据丢失影响不大，是临时数据，而且立即可以使用，内存空间也比较宽松、

 

csv:

优点：数据存储为CSV文件格式，不用进行转换，可以对数据文件直接编辑

缺点：不支持索引，不能为空，不能自增

使用场景:适合做为数据交换的中间表（能够在服务器运行的时候，拷贝和拷出文件，可以将电子表格存储为CSV文件再拷贝到MySQL数据目录下，就能够在数据库中打开和使用。同样，如果将数据写入到CSV文件数据表中，其它web程序也可以迅速读取到数据。

Performace_Schema：

特点：系统内部使用，你也用不到。主要用于收集数据库服务器性能参数。

 

Federated：

优点：针对远程数据库实现、本地虚拟表与远程实体表之间是 TCP 长连接，并且是多个客户端利用的。所以不用担心因频繁建立连接带来的网络开销缺点：远程数据库仅限MySql、不支持： 事务、表结构修改、 alter table 命令 

使用场景：针对远程数据库实现。

 

InnoDB 

使用场景：如果要提供提交、回滚、崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

 InnoDB 和 MyISAM之间的区别： 

 

1>.InnoDB支持事物，而MyISAM不支持事物

2>.InnoDB支持行级锁，而MyISAM支持表级锁

3>.InnoDB支持MVCC, 而MyISAM不支持

4>.InnoDB支持外键，而MyISAM不支持

5>.InnoDB不支持全文索引，而MyISAM支持。

 

Archive：

使用场景：如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive】