储存引擎

• 而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎

 

• mysql-5.5.5开始,InnoDB作为默认存储引擎，之前是MyISAM，更早是ISAM你能用的数据库引擎取决于mysql在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译MYSQL。

 

 

• 以下是常用引擎的特性对比：

 

 

 

InnoDB 存储引擎

• MySQL5.5.5 之后，InnoDB 作为默认存储引擎，是一种事务性存储引擎。完全支持事务的ACID特性。执行行级锁，并发程度高，支持索引

 

• InnoDB 主要特性有：

 

• InnoDB 给 MySQL 提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID 兼容)存储引擎。

 

• InnoDB 锁定在行级并且也在 SELECT 语句中提供一个类似 Oracle 的非锁定读。这些功能增加了多用户部署和性能。在 SQL 查询中，可以自由地将 InnoDB 类型的表与其他 MySQL 的表的类型混合起来，甚至在同一个查询中也可以混合。

 

• InnoDB 是为处理巨大数据量的最大性能设计。它的 CPU 的效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

InnoDB 被用在众多需要高性能的大型数据库站点上。 InnoDB 不创建目录，使用 InnoDB 时，MySQL 将在 MYSQL 数据目录下创建一个名为 ibdata1 的 10MB 大小的自动扩展数据文件，以及两个名为ib_logfile() 和 ib_logfile1 的 5MB 大小的日志文件。

 

•  InnoDB 存储引擎完全与 MySQL 服务器整合，InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。

 

• InnoDB 将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与 MyISAM 表不同，比如在 MyISAM 表中每个表被存在分离的文件中。

InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为 2GB 的操作系统上。

 

• InnoDB 支持外键完整性约束（FOREIGN KEY)。 存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB 会被每一行生成一个 6B 的 ROWID，并以此作为主健。

 

 

 

MyISAM存储引擎

• MyISAM 拥有较高的插入、查询速度，但不支持事务。在 MySQL5.5.5 之前的版本中，MyISAM 是默认存储引擎。 

 

• MyISAM是 MyISAM 的 ISAM 扩展格式和缺省的数据库引擎(5.5之前)。除了提供 ISAM 里所没有的索引和字段管理的大量功能，MyISAM 还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行 OPTIMIZE TABLE 命令，来恢复被更新机制所浪费的空间。

 

• ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。
• ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MySQL能够支持这样的备份应用程序。

 

• MyISAM 还有一些有用的扩展，例如用来修复数据库文件的 MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。 

 

• MyISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。 

 

• MyISAM 主要特性有：

 

• 大文件（达 63 位文件长度）在支持大文件 系统和操作系统上被支持。

 

• 当把删除、更新及插入操作混合使用的时候，动态尺寸的行产生更少的碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块来自动完成。

 

• 每个 MyISAM 表最大索引数是 64，这也可以通过编译来改变。对于键长度超过 250B 的情况，一个超过 1024B 的键将被用上。

 

• BLOB 和 TEXT 列可以被索引。

 

• NULL 值被允许在索引的列中。这个值占每个键的 0~1 个字节。

 

• 所有数字键值以高字节优先被存储以允许一个更高的索引压缩。

 

• 每表一个 AUTO_INCREMENT 列的内部处理。MyISAM 为 INSERT 和 UPDATE 操作自动更新这一列。使得 AUTO_INCREMENT 列更快（至少 10%）。在序列顶的值被删除除之后就不能再利用。

 

• 可以把数据文件和索引文件放在不同目录。

 

• 每个字符列可以有不同的字符集。

 

• 有 VARCHAR 的表可以固定或动态记录长度。VARCHAR 和 CHAR 列可以多达 64KB

 

• 使用 MyISAM 引擎创建数据库，将生产 3 个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm 文件存储表定义，数据文件的扩展名为 .MYD(MYData)，索引文件的扩展名是 .MYI（MYIndex)。

 

 

 

memory（HEAP）存储引擎

• 它的数据是放在内存中的，访问速度非常得快，比ISAM和MYISAM都快，memory表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。

 

• 但是一旦服务关闭，表中的数据就会丢失掉，但是表的结构会保存下来，所以它所管理的数据是不稳定的。另外在数据行被删除的时候，HEAP也不会浪费大量的空间

 

• MEMORY 主要特性有：

 

• 每个表可以多达 32 个索引，每个索引 16 列，以及 500B 的最大键长度。

 

• 引擎执行 HASH 和 BTREE 索引。

 

• 可以在一个表中有非唯一键。

 

• 不支持 BLOB 或 TEXT 列。

 

• 表使用一个固定的记录长度格式。

 

• 支持 AUTO_INCREMENT 列和对包含 NULL 值的列索引。

 

• 表内容被存在内存中，内存是 MEMORY 表和服务器在查询处理时的空闲中创建的内部表共享。

 

•  表在所有客户端之间共享（就像其他任何非 TEMPORARY 表）。

注

• 当不再需要表的内容时，要释放被表使用的内存，应该执行 DELETE FROM 或 TRUNCATE TABLE，或者删除整个表（使用 DROP TABLE)。

 

 

 

archive 存储引擎

• Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。

 

• 在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。

 

• Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

注

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

 

 

 

 

MRG_MYISAM（MERGE）存储引擎

• MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。

 

• Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

 

 

 

CSV 存储引擎

• 使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。

 

• 该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。csv的编码转换需要格外注意

 

 

 

BLACKHOLE 存储引擎（黑洞引擎）

• 该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

 

 

 

PERFORMANCE_SCHEMA 存储引擎

• 该引擎主要用于收集数据库服务器性能参数。

 

• 这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。

 

• MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

 

 

 

Federated存储引擎

• 该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。

 

• Federated存储引擎可以使你在本地数据库中访问远程数据库中的数据，针对federated存储引擎表的查询会被发送到远程数据库的表上执行，本地是不存储任何数据的。

 

• 缺点：

 

1.对本地虚拟表的结构修改，并不会修改远程表的结构

 

2.truncate 命令，会清除远程表数据

 

3. drop命令只会删除虚拟表，并不会删除远程表

 

4.不支持 alter table 命令

 

5. select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题，但是按主键或索引列查询，则很快，如以下查询就非常慢（假设 id 为主索引）

 

select id from db.tablea where id >100 limit 10 ;

 

而以下查询就很快：

 

select id from db.tablea where id >100 and id<150

 

6.  如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常。

 

7. 类似 where name like "str%" limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢，是因为federated引擎会将所有满足条件的记录读取到本地，再进行 limit 处理。