表类型(存储引擎)的选择

回到首页☞

和大多数数据库不同，MySql中有一个存储引擎的概念，我们目前常用的是innodb

show engines;

1、MYSQL存储引擎概述

插件式存储是MYSQL数据库最重要的特性之一，用户可以根据应用的需要选择如何存储和索引数据，是否使用事务等。MYSQL 5.0支持的引擎包括MyISAM,InnoDB,MEMORY,MERGE,EXAMPLE,NDB CLuster,ARCHIVE,CSV,BLACKHOLE,FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

创建新表时如果不指定存储引擎，那么系统就会使用默认存储引擎，MYSQL5.5之前默认存储引擎是MyISAM，5.5之后改为了InnoDB.查看当前的默认存储引擎：

show variables like 'table_type'

可以通过下面两种方法查询当前数据库支持的存储引擎，第一种方法为：

SHOW ENGINES \G

SHOW VARIABLES LIKE 'have%'

2、各种引擎的特性

2.1、MyISAM存储引擎

MyISAM是MySQL的默认存储引擎，不支持事务、不支持外键。优点是访问速度快，批量插入的速度高，对事务完整性没有要求或者以select、insert为主的应用基本上都可以使用这个引擎来创建表。

每个MyISAM在磁盘上有3个文件，

• .frm（存储表定义），
• .MYD(MYData，存储数据），
• .MYI（MYIndex，存储索引），

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。

MyISAM的表支持3种不同的存储格式，分别是

• 静态（固定长度）表（默认存储格式，优点是存储非常迅速，容易缓存，出现故障容易恢复，但占用的空间通常比动态表多，静态表的数据在存储的时候会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉，所以如果数据末尾原本就存在空格，也会被去掉，需要特别注意）、
• 动态表（记录的字段不是固定长度的，这样占用的存储空间相对较少，但是频繁地更新删除记录会产生碎片，而且出现故障的时候回复相对比较困难）
• 压缩表（由myisampack工具创建，占据非常小的磁盘空间，每个记录是被单独压缩的，所以只有非常小的访问开支）。

2.2、InnoDB存储引擎

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

InnoDB表的自动增长列可以手工插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。对于InnoDB表，自动增长列必须是索引，如果是索引，也必须是组合索引列的第一列。

MySQL支持外键的存储引擎只有InnoDB。在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

InnoDB存储表和索引有两种方式：

• 使用共享表空间存储（表结构保存在.frm文件中，数据和索引保存在Innodb_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件）；
• 使用多表空间存储（表结构依然保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中，如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO 均匀分布在多个磁盘上。即便在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB 把内部数据词典和未作日志放在这个文件中。）

2.3、MEMORY存储引擎

MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，格式是.frm，默认使用HASH索引。MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果

2.4、MERGE存储引擎

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有数据，对MERGE类型的表进行查询、更新、删除操作实际上是对内部的MyISAM表进行的。MERGE 表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm 文件存储表定义，另一个.MRG 文件包含组合表的信息，包括MERGE 表由哪些表组成、插入新的数据时的依据。可以通过修改.MRG 文件来修改MERGE 表，但是修改后要通过FLUSH TABLES 刷新。

3、存储引擎的选择

如果应用是以读操作和插入操作为主，只有少量的更新和删除操作，并且对事务完整性、并发性要求不是很高，那么适合选择MyISAM存储引擎。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB存储引擎比较合适。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。MERGE 表的优点在于可以突破对单个MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE 表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。

实际上我们业务系统都是以事务为核心的，所以基本选择InnoDB。

回到首页☞