mysql之存储引擎

一、MySQL存储引擎

　　MySQL中的数据用各种不同的技术存储在不同格式的文件(或者内存)中，每一种技术都使用不同的存储机制、索引技巧、锁定方式，并最终提供广泛的不同的功能和能力，这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。

　　与其他数据库（例如Oracle 和SQL Server等数据库中只有一种存储引擎）不同的是，MySQL默认配置了许多不同的存储引擎，选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

二、MySQL常见存储引擎

  (1) MyISAM:  高速引擎，拥有较高的插入，查询速度，但不支持事务

  (2) InnoDB:   5.5版本后MySQL的默认存储引擎，支持事务和行级锁定，比MyISAM处理速度稍慢

  (3) Memory :  内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失

  (4) MRG_MyISAM（MERGE）:   将多个表联合成一个表使用，在超大规模数据存储时很有用

  (6) Falcon:   一种新的存储引擎，支持事物处理，传言可能是InnoDB的替代者

  (7) Archive:   将数据压缩后进行存储，非常适合存储大量的独立的，作为历史记录的数据，但是只能进行插入和查询操作

  (8) CSV:   CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据交换)

三、常用存储引擎说明：

MyISAM:

　　它不支持事务，也不支持外键，但是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。

　　每个MyISAM在磁盘上存储成3个文件：.frm(存储表定义)，.MYD(MYData，存储数据)，.MYI(MYIndex，存储索引)

　　MyISAM的表还支持3种不同的存储格式：

 　　　　(1) 静态(固定长度)表：静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多

 　　　　(2) 动态表：如果数据表中出现varchar、xxxtext等类型字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以频繁修改数据后，数据表中的数据就可能离散的存储在内存中，　　                     进而导致执行效率下 降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理，并且出现故障的时候恢复相对比较困难。

 　　　　(3) 压缩表：以上两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。

　　MyISAM存储引擎特别适合在以下几种情况下使用：

 　　　   (1) 选择密集型的表，MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点；

 　　　   (2) 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。

InnoDB:

　　它是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案，InnoDB还引入了行级锁定和外键约束。（innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的）

　　InnoDB在以下场合下使用是最理想的选择：

 　　  (1) 更新密集的表，InnoDB存储引擎特别适合处理多重并发的更新请求；

 　　  (2) 事务，InnoDB存储引擎是支持事务的标准MySQL存储引擎；

 　　  (3) 外键约束，MySQL支持外键的存储引擎只有InnoDB；

 　　  (4) 支持自动增加列AUTO_INCREMENT属性；

 　　 (5) 自动灾难恢复，与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

　　一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。 

MEMORY:

　　使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。

　　一般在以下几种情况下使用Memory存储引擎：

　　(1) 目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。

　　(2) 如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。

　　(3) 存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

　　Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。

MERGE:

　　MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景。

　　对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

ARCHIVE:

　　Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。（只支持insert和select操作（支持行级所和缓冲区，可以实现高并发的插入），只允许在自增ID列上加索引，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。当数据量非常大的时候Archive的插入性能表现会较MyISAM为佳）

　　每个archive表在磁盘上存在两个文件：.frm(存储表定义)  .arz(存储数据)

　　由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

CSV:

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

　　数据以文本方式存储在文件中（Innodb则是二进制），.CSV文件（存储表内容），.CSM文件（存储表的元数据如表状态和数据量），.frm文件（存储表结构信息）。

　　CSV存储引擎表特点：

　　　　(1) 以CSV格式进行数据存储（逗号隔开，引号）
　　　　(2) 所有的列必须都是不能为NULL的
　　　　(3) 不支持索引（不适合大表，不适合在线处理）
　　　　(4) 可以对数据文件直接编辑（保存文本文件内容）

　　CSV存储引擎引用场景：

　　　　适合做数据交换的中间表(csv文件可直接拿出来使用)

常见存储引擎 特点总结：

特点	MyISAM	InnoDB	MEMORY	MERGE	ARCHIVE	CSV
事务安全		支持
支持外键		支持
锁机制	表锁	行锁	表锁	表锁	表锁	表锁
B树索引	支持	支持	支持	支持
哈希索引			支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持	支持
数据可压缩	支持				支持	N/A
空间使用	低	高	N/A	低	N/A	N/A
内存使用	低	高	中	低	N/A	N/A
批量插入速度	高	低	高	高	N/A	N/A

MySQL创建表时，存储引擎的使用：

例如：创建表时，指定engine

-- MyISAM
CREATE TABLE `table_type_myism` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- CSV
CREATE TABLE `table_type_csv` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8;

MySQL默认存储引擎的修改：

　　在数据库安装文件中，找到 my.ini 文件，将 default-storage-engine= xxxx 即可。