Mysql比知必会系列教程(二) --------各存储引擎

Mysql 各存储引擎

通过 show engenies命令进行查看支持的存储引擎。同样infomation_schema也提供了查询存储引擎的表select ENGINE from information_schema.ENGINES

MyISAM

默认的存储引擎：提供了键压缩功能。他使用某种压缩算法来保持连续的，相似的字符串索引值。myISAM存储引擎为AUTO_INCREATEMENT 数据列提供了更多的功能。每个MyISAM都有一个标识。支持全文检索，但需要通过FullTEXT索引

不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表

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

静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

动态表：记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能

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

Merge存储引擎

提供了把多个MyISAM数据表合并为一个逻辑单元的手段。查询一个merge表相当于查询其所有的成员数据表。是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

MEMORY存储引擎

Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围

Hash索引优点：
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
Hash索引缺点： 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；

Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

InnoDB存储引擎

支持提交回滚操作，确保数据在事务处理过程中万无一失。可以创建保存点的办法实现回滚。

在系统崩溃后自动恢复。

外键和引用完整性支持，包括递归删除和更新。

数据行级别锁定和多版本共存，使得InnoDB数据表在需要同时检索和更新时表现出非常好的并发性能。

在默认情况下，InnoDB会把数据表集中存储在一个共享的表空间。而不是像大多数其他引擎为不同的数据表创建不同的文件。InnoDB表空间可以由多个文件构成，还可以包含原始分区。Innodb表空间就像一个虚拟的文件系统，他存储和管理所有InnoDB数据表内容。

该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎的特点：支持自动增长列，支持外键约束。

选择合适的存储引擎

除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎。如果要用到全文索引建议考虑InnoDB加上Sphinx的组合，而不是使用支持全文索引的MyISAM.除非万不得已，否则建议不要使用多种存储引擎，否者会带来一些列复杂问题、以及一些潜在的bug和边界问题。

如果应用需要不同的存储引擎需考虑以下几个因素：

• 事务：
  如果应用需要事务支持，那么InnoDB是目前最稳定并且经过验证的选择，如果不需要事务，并且主要是insert和select操作，那么MyIsam是个不错的选择。
• 备份：
  备份的需求就会影响存储引擎的选择。如果定期的关闭服务器执行备份，那备份因素可以忽略。反之，需要热备份，那么选择InnoDB就基本的需求。
• 奔溃恢复：
  数据量比较大的时候，系统崩溃后MyISAM崩溃后发生损毁的概率比InnoDB要高的多。
• 特有的特性
  mysql也只有MyISAM支持地理空间搜索。如果一个存储引擎拥有一些关键的特性，同时却缺乏一些必要的特性，那么有时候不得不做折中的考虑，或者在架构设计上做取舍。

alter table mytable engine= InnoDB;