mysql存储引擎

MyISAM

Mysql5.5之前默认的存储引擎

MyISAM存储引擎由MYD和MYI组成

  create table testmysam ( id int PRIMARY key ) ENGINE=myisam

表压缩

 myisampack -b -f /usr/local/mysql/data/mall/testmysam.MYI

压缩后再往表里面新增数据就新增不了

insert into testmysam VALUES(1),(2),(3);

压缩后，需要

myisamchk -r -f /usr/local/mysql/data/mall/testmysam.MYI

适用场景

1.非事务型应用（数据仓库，报表，日志数据）

2.只读类应用

3.空间类应用（空间函数，坐标）

由于Innodb的发展，myisam已经停止维护

（绝大多数场景都不适合）

Innodb

1.Innodb是一种事务性存储引擎

2.完全支持事务的ACID特性

3.Redo Log 和 Undo Log

Redo Log

用于在实例故障恢复时，继续那些已经commit但数据尚未完全回写到磁盘的事务。
The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. 

Undo Log

记录了数据修改的前镜像。存放于ibdata中。（提一句，DDL操作会修改数据字典，该信息也存放在ibdata中）

用于在实例故障恢复时，借助undo log将尚未commit的事务，回滚到事务开始前的状态。

An undo log is a collection of undo log records associated with a single transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from the undo log records. Undo logs exist within undo log segments, which are contained within rollback segments. Rollback segments are physically part of the system tablespace. For related information, see Section 14.6, “InnoDB Multi-Versioning”.

4.Innodb支持行级锁（并发程度更高）

对比项	MyISAM	Innodb
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录，也会 锁住整个表 不适合高并发的操作	行锁，操作时只锁某一行 不对其他行又影响 适合高并发操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引，还会缓存真实数据 对内存要求高，而且内存大小对性能有影响

show VARIABLES like 'innodb_log_buffer_size'

CSV

1.以csv格式进行数据存储

2.所有列都不能为null

3.不支持索引（不适合大表，不适合在线处理）

4.可以对数据文件直接编辑（保存文件内存）

Archive

组成

以zlib对表数据进行压缩，磁盘I/O更少

数据存储在ARZ为后缀的文件中

特点

只支持insert和select操作

只允许在自增ID列上加索引

Memory

特点

1.文件系统存储，也称HEAP存储引擎，所有数据保存在内存中

2.只是HASH索引和BTree索引

3.所有字段都是固定长度varchar(10)=char(10)

4.不支持Blog和Text等大字段

5.Memory存储引擎使用表级锁

6.最大大小由max_heap_table_size参数决定

show VARIABLES like 'max_heap_table_size'

与临时表的区别

使用场景

1.hash索引用于查找或者是映射表（邮编和地区的对应表）

2.用于保存数据分析中产生的中间表

3.用于缓存周期性聚合数据的结果表

Ferderated

特点

1.提供了访问远程Mysql服务器上表的方法

2.本地不存储数据，数据全部放到远程服务器上

3.本地需要保存表结构和远程服务器的连接信息

使用场景

偶尔的统计分析及手工查询（某些游戏行业）

默认禁止的，启动时需要在启动时增加 federated参数,编辑my.cnf文件，常用的: vim /etc/my.cnf

 创建本地表时需指定远程mysql的连接信息

create database local; 

create database remote; 

create table remote_fed(id int auto_increment not null,c1 varchar(10) not null default '',c2 char(10) not null default '',primary key(id)) engine = INNODB 

INSERT into remote_fed(c1,c2) values('aaa','bbb'),('ccc','ddd'),('eee','fff'); 

CREATE TABLE `local_fed` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` varchar(10) NOT NULL DEFAULT '', `c2` char(10) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=federated CONNECTION ='mysql://root:yyb@127.0.0.1:3306/remote/remote_fed'

select * from local_fed; 

delete from local_fed where id = 2; 

select * from remote.remote_fed;