mysql 分表策略及 MERGE 表的使用

2022-06-27 12:24:24 浏览数 (1)

概述

无论是大企业还是小公司，都有意无意的使用 mysql 来搭建数据存储服务，但是随着业务访问量、数据量的急剧膨胀，集中式数据存储越来越凸显出他的技术瓶颈，需要做读写分离。 而这恰恰也是 mysql 的一个优势所在，正是 mysql 的可扩展性，让 mysql 逐渐成为了企业的优先选择。

mysql 的分表设计

mysql 上设计数据库应该力求做到小快灵，单库数据量要小，数据库要快速响应，表设计要灵活。 不同的业务可以选择不同的分表原则，同时需要考虑怎么高效的水平扩展。 数据量过大，就要进行表的拆分，当然，如果某个表的访问压力过大，同样也是需要考虑分表的，将两个表分别放到两个服务器上来分担压力。 那么怎么分表，策略主要有以下几个：

配置表 数据表

将存储数据的表按照某种分表规则和查询路由进行分表，并新建配置表，存储和维护对应的分表规则和查询路由。 当然，如果分表规则太过灵活，这也意味着分表风险过高。

mod 取模分表

如果分 4 个表，那么用每条数据的自增ID对 4 取模，取得对应的分表编号，从而可以达到分散数据、分表的目的。 同时，随着业务发展带来的数据膨胀及访问压力的加大，进一步分表也是非常容易的。

MERGE 表分表

当然，MyISAM 引擎提供 MERGE 表特性，可以通过创建 MERGE 表简化分表联立。 但是，MERGE 表并不适用于解决我们上述提到的问题。 MERGE 表适合的场景是：在创建表的初期，预测到随着业务、数据的增长，会在某一时刻分表，于是当表数据达到该阈值（如200W）之后，使用 INSERT_METHOD=LAST 创建 MERGE 表及新表。 这样，新的数据将全部存入 MERGE 表，达到 200W 数据后再创建新的表，并通过 alter table 语句将新表加入到 MERGE 表的 UNION 列表中。 这种解决方案利用了 MyISAM 引擎提供的 MERGE 表特性，使用非常方便，并且可以让每个分表都具有相同规模，但是这需要在系统设计时充分考虑。

保证分表数据ID唯一性

分表最重要的一个问题就是需要保证不同分表之间的数据的ID唯一性。 oracle 数据库使用序列来保证ID的唯一性，序列凌驾于表之上，参考这样的设计，mysql 也可以通过维护序列表，分表id与序列表中id一一对应，这样新插入数据可以很方便的获得当前所需的 ID。 当然，如果使用某种保证 ID 唯一的规则，就无需考虑这个问题了，比如使用精确到毫秒的时间数字作为唯一ID。

分表数据的查询 — MySql MERGE 引擎分表

对于多个表，我们常常需要联合查询，那么使用 merge 表就会非常有效。 MERGE存储引擎把一组MyISAM数据表当做一个逻辑单元来对待，让我们可以同时对他们进行查询。

  • 需要注意的是，只有 MyISAM 引擎才支持 MERGE 表的特性

merge 表的创建

例如有下面两个表：

代码语言：javascript复制
CREATE TABLE `t1` (   
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   
    `log` varchar(45) ,   
    PRIMARY KEY (`id`) 
) ENGINE=MyISAM;
代码语言：javascript复制
CREATE TABLE `t2`(   
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   
    `log` varchar(45) ,   
PRIMARY KEY (`id`) 
) ENGINE=MyISAM;

无论表中是否已有数据，我们都可以创建 MERGE 表：

代码语言：javascript复制
CREATE TABLE `t` (   
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   
    `log` varchar(45) NOT NULL,   
PRIMARY KEY (`id`) 
) ENGINE=MERGE UNION=(t1, t2) INSERT_METHOD=LAST;

上述创建语句中，UNION 说明了 MERGE 表是哪几个表的联合。 INSERT_METHOD 说明了 MERGE 表的插入方式，可以取以下值：

MERGE 表 INSERT_METHOD 取值

取值

意义

0

不允许插入

FIRST

新数据插入到 UNION 中的第一个表

LAST

新数据插入到 UNION 中的最后一个表

可以通过 alter table 的方式修改上述设定。

代码语言：javascript复制
alter table t ENGINE=MERGE UNION=(t1,t2,t3) INSERT_METHOD=FIRST;

MERGE 表的查询

当我们执行：

代码语言：javascript复制
select * from t;

从效果上看，和执行：

代码语言：javascript复制
(select * from t1) union
(select * from t2)

是一样的。

MERGE 表的删除

删除 MERGE 表不会对子表造成任何影响。 如果要删除子表，则必须首先使用 alter table 语句将子表从 MERGE 表中的 UNION 中去除，然后才可以删除子表。

MySql 使用的其他问题

对于 MySql 的使用，分表只是冰山一角，还有太多的细节需要考虑，包括分表后基础数据的存储，分表大小的选择，数据库存储引擎的选择。 这些问题就需要 MySQL 的使用者多去了解 MySQL 的自身优势和实现方式，以及他可扩展性的优势，还有业界成熟的设计思路，结合自身业务特性，才能够设计出更加合理的系统。

自动驾驶 云数据库SQLServer 数据库 sql 存储

0 人点赞