由mysql分区想到的分表分库的方案

在分区分库分表前一定要了解分区分库分表的动机。

对实时性要求比较高的场景，使用数据库的分区分表分库。

对实时性要求不高的场景，可以考虑使用索引库(es/solr)或者大数据hadoop平台来解决(如数据分析，挖掘，报表等)或者混合使用(如es+hbase/mongodb)。

…分区解决冷热数据分离的问题;

…分库解决互联网的高并发问题；

…分表解决互联网的高容量问题；

…分库分表解决高并发和高容量的问题。

今天细细品味了一下mysql分区的官方资料，有一点点收获，记录下来。

1.mysql的分区

官方文档介绍的比较详细，这里就以实例为主介绍。

1.1 分区类型

1.range分区　

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT \'1970-01-01\',
    separated DATE NOT NULL DEFAULT \'9999-12-31\',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

　　2.list分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT \'1970-01-01\',
    separated DATE NOT NULL DEFAULT \'9999-12-31\',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

　　3.多列分区

　　多列分区有分为range分区和list分区

　多列range分区

mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,\'ggg\'),
    ->     PARTITION p1 VALUES LESS THAN (10,20,\'mmm\'),
    ->     PARTITION p2 VALUES LESS THAN (15,30,\'sss\'),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );

　　多列list分区

CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN(\'Oskarshamn\', \'Högsby\', \'Mönsterås\'),
    PARTITION pRegion_2 VALUES IN(\'Vimmerby\', \'Hultsfred\', \'Västervik\'),
    PARTITION pRegion_3 VALUES IN(\'Nässjö\', \'Eksjö\', \'Vetlanda\'),
    PARTITION pRegion_4 VALUES IN(\'Uppvidinge\', \'Alvesta\', \'Växjo\')

4.Linear hash分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT \'1970-01-01\',
    separated DATE NOT NULL DEFAULT \'9999-12-31\',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

　　5. Key分区

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

　　6.Sub分区

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

　　7.对控制的处理

　　range分区，null 分到最低的分区；

　　list分区，如果包含null的列，则进去，否则会报错，如下实例

mysql> CREATE TABLE ts1 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY LIST(c1) (
    ->     PARTITION p0 VALUES IN (0, 3, 6),
    ->     PARTITION p1 VALUES IN (1, 4, 7),
    ->     PARTITION p2 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts1 VALUES (9, \'mothra\');
ERROR 1504 (HY000): Table has no partition for value 9

mysql> INSERT INTO ts1 VALUES (NULL, \'mothra\');
ERROR 1504 (HY000): Table has no partition for value NULL

　　hash分区和key分区，null做0处理，示例如下：

mysql> INSERT INTO th VALUES (NULL, \'mothra\'), (0, \'gigan\');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM th;
+------+---------+
| c1   | c2      |
+------+---------+
| NULL | mothra  |
+------+---------+
|    0 | gigan   |
+------+---------+
2 rows in set (0.01 sec)

　　1.2 分区管理

　　Range和list分区管理

　　ALTER TABLE … DROP PARTITION 删除分区

　　　　 ALTER TABLE ... ADD PARTITION 增加分区

　　　　ALTER TABLE …REORGANIZE PARTITION 移动分区

　　Hash和key分区管理

　　　　不能通过DROP来删除分区，可以ALTER TABLE … COALESCE PARTITION来合并分区

　　　　 ALTER TABLE ... ADD PARTITION 增加分区

　　表间的分区交换和子分区表的交换

　　　　ALTER TABLE … EXCHANGE PARTITION

　　维护分区

　　　　表的维护：CHECK TABLE,OPTIMIZE TABLE,ANALYZE TABLE, REPAIR TABLE

　　　　分区的维护：ALTER TABLE …

　　　　　　　　　　　　Rebuilding partitions
　　　　　　　　　　　　Optimizing partitions
　　　　　　　　　　　　Analyzing partitions
　　　　　　　　　　　　Repairing partitions
　　　　　　　　　　　　Checking partitions
　　　　　　　　　　　　TRUNCATE PARTITION

　　　　获取分区信息　　　　　　　　　

　　　　　　　　　　　　SHOW CREATE TABLE
　　　　　　　　　　　　SHOW TABLE STATUS
　　　　　　　　　　　　INFORMATION_SCHEMA.PARTITIONS
　　　　　　　　　　　　EXPLAIN SELECT

　　1.3 小结

　　　　从分区表的设计思想上来看，支持多张分区方式：range，list，多列，linear hash，key，sub分区

　　　另外，还提供对分区的管理。

2.分库或者分表

　　　分区，分表，分库解决的问题不一样，但解决思路或者架构设计有相通的地方，我们可以借鉴分区表的设计思维来构建分表分库的实现。

　　分区具有的功能：

　　分区屏蔽的对用户dml和select的细节，分表或者分库db代理应该也可以实现，分表或者分库db代理保存db的元数据和映射情况，对用户来说，应该屏蔽细节，不应该暴露给用户

分区的管理提供了相应的命令，分表或者分库db代理也应该实现该功能

　　分区不具有的功能：

　　　　监控，日志，可视化等方面分区做的不够，分表或者分库db代理可以做的更好。

　　想到了hadoop的hdfs架构设计

3.mycat的实现

　　根据上面的思路，是否有响应的开源实现呢？找到一个比较相近的开源实现mycat：

一个彻底开源的，面向企业应用开发的大数据库集群
支持事务、ACID、可以替代MySQL的加强版数据库
一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
一个新颖的数据库中间件产品

总结：

　　mycat在国内使用的不少，可以试用。如果想定制自己的mycat，可以参考mysql分区的实现和hadoop存储hdfs的架构思想。

参考资料：

【1】https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-partitioning-keys-unique-keys.html

【2】http://www.mycat.io/

本文链接：https://www.cnblogs.com/davidwang456/p/10898923.html

由mysql分区想到的分表分库的方案

由mysql分区想到的分表分库的方案的更多相关文章

随机推荐

热门专题

目录导航