MySql性能优化之合并表

    合并表是一种早期的、简单的分区实现,和分区表相比有一些不同的限制,并且缺乏优化。合并表简而言之是由于一个表的数据太多,导致访问性能下降,从而把一个表分割成多个结构完全相同的物理表,然后再用合并表(引擎为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指定的子表顺序很重要,它表示合并表的组合顺序,查询扫描表时也是按照这个顺序扫描的。

二、合并表的长处

  1. 合并表的每个子表都是独立的物理表,允许用户单独访问各个子表,包括更新表数据和查询数据。
  2. 可以按照比较复杂的业务规则判断把数据保存到哪个子表中,当然必须直接插入子表中,而不是通过合并表插入。
  3. 可以自由地拆分子表,而对其他子表没有影响。
  4. 删除合并表时,对子表没有影响。
  5. 一个MyISAM子表可以是多个合并表的子表。
  6. 可以通过直接复制.frm、.MYI、.MYD文件,来实现在不用服务器之间复制各个子表。
  7. 在合并表中可以很容易添加新的子表:直接修改合并表的定义即可。
  8. 可以创建一个合并表,让它只包含需要的数据,例如只包含某个时间段的数据,而在分区表中是做不到这一点的。
  9. 如果想对某个子表做备份,恢复,修改,修复或别的操作,可以先将其从合并表中分离,操作结束后再将其合并进去。
  10. 可以使用myisampack来压缩所有的子表。

三、合并表的不足

  1. 合并表本身缺乏优化。
  2. 每个子表的主键(或唯一性索引)仅能确保自己的主键唯一性,而合并表不能确保所有表中数据的唯一性。
  3. 修改子表结构前,必须先把合并表删除,等所有子表结构都修改完毕且保持一致后,再创建合并表。
  4. 在使用CREATE语句创建合并表时,不会检查各个子表的兼容性。
  5. 根据合并表的特性,合并表上无法使用REPLACE语法。
  6. 自增字段的新增值是根据所有子表中此字段最大值加自增量计算的,而子表的自增字段的新增值是自身AUTO_INCREMENT最大值。若需要在多个表中插入数据,每个表的自增字段都要有间隔区间。若修改修改多个表,很难保证不重复,几乎不可维护。
  7. 如果一个查询访问合并表,那么它需要访问所有子表,所以与单个表相比性能并不能提高。

四、合并表的使用场景

    使用合并表是为了解决性能问题,但查询整个合并表却不能提高性能,所以合并表是通过按一定的规则把数据拆分到多个子表中保存,并分别访问各子表来改善访问性能的。由于合并表并不能确保主键的唯一性,所以若自增主键,不适应使用合并表。

    使用合并表的总前提是一个表中的数据量过大,影响到查询性能。并且数据可以存放在MyISAM数据引擎中。另外若还满足以下场景,可以使用合并表:

  1. 数据有比较清晰的分类,可以根据不同的分类查询不同的子表。
  2. 数据有比较清晰的分段(例如时间),可以根据不同的分段访问不同的子表。
  3. 随着时间的推移,旧数据的查询频次慢慢减少,甚至可以归档处理。
  4. 备份超大数据,切分成一个个数据表保存比单个保存更安全,管理起来也更方便些。

五、使用合并表的注意事项

  1. 执行范围查询时,需要在每个子表上各执行一次,这比直接访问单个表的性能要差很多,而且子表越多,性能越差。
  2. 全表扫描和单个普通表的全表扫描速度相同。
  3. 在合并表做唯一键和主键查询时,一旦找到一行数据,查询就会停止,不会再查询其他子表。
  4. 子表的读取顺序和CREATE TABLE语句中的顺序相同。如果需要频繁地按照某个特定顺序访问表,那么可以通过这个特性来让合并排序操作更高效。
  5. 若直接删除子表或修改子表结构会对合并表产生破坏性的影响,所以要慎重。
  6. 由于子表的数量越多,合并表的性能越差,所以要控制子表的数量。

    

    

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

WalsonTung

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值