合并表是一种早期的、简单的分区实现,和分区表相比有一些不同的限制,并且缺乏优化。合并表简而言之是由于一个表的数据太多,导致访问性能下降,从而把一个表分割成多个结构完全相同的物理表,然后再用合并表(引擎为Merge)把它们从逻辑上合并起来。合并表相当于一个容器,里面包含多个相互独立的真实表。可以在Create Table 中使用一种特别的UNION语法来指定包含的真实表。
一、合并表的语法如下:
CREATE TABLE customer_01 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(128) NOT NULL
)
COMMENT='客户子表'
COLLATE='utf8_general_ci'
ENGINE=MYISAM;
CREATE TABLE customer (
id INT NOT NULL PRIMARY KEY,
NAME VARCHAR(128) NOT NULL
)
COMMENT='客户合并表'
COLLATE='utf8_general_ci'
ENGINE=MERGE
UNION=(customer_01,customer_02,customer_03)
INSERT_METHOD=LAST;
其中customer_01,customer_02和customer_03的字段与索引与customer完全相同,存储引擎必须是MyISAM。INSERT_METHOD指定数据按照合并表的顺序插入到哪一个子表中,有三个值:0,FIRST,LAST。0表示未指定,FIRST表示插入到第一个表中,LAST表示插入到最后一个表中。所以UNION指定的子表顺序很重要,它表示合并表的组合顺序,查询扫描表时也是按照这个顺序扫描的。
二、合并表的长处
- 合并表的每个子表都是独立的物理表,允许用户单独访问各个子表,包括更新表数据和查询数据。
- 可以按照比较复杂的业务规则判断把数据保存到哪个子表中,当然必须直接插入子表中,而不是通过合并表插入。
- 可以自由地拆分子表,而对其他子表没有影响。
- 删除合并表时,对子表没有影响。
- 一个MyISAM子表可以是多个合并表的子表。
- 可以通过直接复制.frm、.MYI、.MYD文件,来实现在不用服务器之间复制各个子表。
- 在合并表中可以很容易添加新的子表:直接修改合并表的定义即可。
- 可以创建一个合并表,让它只包含需要的数据,例如只包含某个时间段的数据,而在分区表中是做不到这一点的。
- 如果想对某个子表做备份,恢复,修改,修复或别的操作,可以先将其从合并表中分离,操作结束后再将其合并进去。
- 可以使用myisampack来压缩所有的子表。
三、合并表的不足
- 合并表本身缺乏优化。
- 每个子表的主键(或唯一性索引)仅能确保自己的主键唯一性,而合并表不能确保所有表中数据的唯一性。
- 修改子表结构前,必须先把合并表删除,等所有子表结构都修改完毕且保持一致后,再创建合并表。
- 在使用CREATE语句创建合并表时,不会检查各个子表的兼容性。
- 根据合并表的特性,合并表上无法使用REPLACE语法。
- 自增字段的新增值是根据所有子表中此字段最大值加自增量计算的,而子表的自增字段的新增值是自身AUTO_INCREMENT最大值。若需要在多个表中插入数据,每个表的自增字段都要有间隔区间。若修改修改多个表,很难保证不重复,几乎不可维护。
- 如果一个查询访问合并表,那么它需要访问所有子表,所以与单个表相比性能并不能提高。
四、合并表的使用场景
使用合并表是为了解决性能问题,但查询整个合并表却不能提高性能,所以合并表是通过按一定的规则把数据拆分到多个子表中保存,并分别访问各子表来改善访问性能的。由于合并表并不能确保主键的唯一性,所以若自增主键,不适应使用合并表。
使用合并表的总前提是一个表中的数据量过大,影响到查询性能。并且数据可以存放在MyISAM数据引擎中。另外若还满足以下场景,可以使用合并表:
- 数据有比较清晰的分类,可以根据不同的分类查询不同的子表。
- 数据有比较清晰的分段(例如时间),可以根据不同的分段访问不同的子表。
- 随着时间的推移,旧数据的查询频次慢慢减少,甚至可以归档处理。
- 备份超大数据,切分成一个个数据表保存比单个保存更安全,管理起来也更方便些。
五、使用合并表的注意事项
- 执行范围查询时,需要在每个子表上各执行一次,这比直接访问单个表的性能要差很多,而且子表越多,性能越差。
- 全表扫描和单个普通表的全表扫描速度相同。
- 在合并表做唯一键和主键查询时,一旦找到一行数据,查询就会停止,不会再查询其他子表。
- 子表的读取顺序和CREATE TABLE语句中的顺序相同。如果需要频繁地按照某个特定顺序访问表,那么可以通过这个特性来让合并排序操作更高效。
- 若直接删除子表或修改子表结构会对合并表产生破坏性的影响,所以要慎重。
- 由于子表的数量越多,合并表的性能越差,所以要控制子表的数量。