mysql分库分表分区

xingliuhua 收录于笔记

2019-03-07 约 1682 字预计阅读 4 分钟

传统的分库分表都是通过应用层逻辑实现的，对于数据库层面来说，都是普通的表和库。分区是数据库层面的。

分库

database不是文件，只起到namespace的作用，所以MySQL对database大小当然也是没有限制的，而且对里面的表数量也没有限制。为什么要分库呢？

为了解决单台服务器的性能问题，当单台数据库服务器无法支撑当前的数据量时，就需要根据业务逻辑紧密程度把表分成几撮，分别放在不同的数据库服务器中以降低单台服务器的负载。

比如一个论坛系统的数据库因当前服务器性能无法满足需要进行分库。先垂直切分，按业务逻辑把用户相关数据表比如用户信息、积分、用户间私信等放入user数据库；论坛相关数据表比如板块，帖子，回复等放入forum数据库，两个数据库放在不同服务器上。

分表

当表中的数据库巨大的时候就要考虑分表了，不然这将产生大量随机I/O，随之，数据库的响应时间将大到不可接受的程度。另外，索引维护（磁盘空间、I/O操作）的代价也非常高。

竖向分表

顾名思义，就是竖着开刀，把表中的一些字段分出去。

我们知道innodb主索引叶子节点存储着当前行的所有信息，减少字段可以让内存加载更多的数据。
受操作系统中文件大小限制。

横向分表

顾名思义，就是横着开刀，这样就把表一些行分出去。

随着数据量的增大，table行数巨大，查询的效率越来越低。
同样受限于操作系统中的文件大小限制，数据量不能无限增加，当到达一定容量时，需要水平切分以降低单表（文件）的大小。

至于怎么横着切要看需求了，可以根据主键范围，可以对主键进行hash等。

表分区

上面的分库、分表都是在应用层实现，拆分要对应用层有很大的调整，比如要实现全局主键生成器，无法再做关联查询等。

但是表分区完全不用考虑这些东西，因为它是在数据库层，对应用层是透明的。

表分区其实也是横向拿刀，切法有下面几种

range分区

根据范围分区，范围应该连续但是不重叠，使用PARTITION BY RANGE, VALUES LESS THAN关键字。

注意：不使用COLUMNS关键字时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 MAXVALUE
);

根据TIMESTAMP范围：

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

List分区

根据具体数值分区，每个分区数值不重叠，使用PARTITION BY LIST、VALUES IN关键字。跟Range分区类似，不使用COLUMNS关键字时List括号内必须为整数字段名或返回确定整数的函数。

数值必须被所有分区覆盖，否则插入一个不属于任何一个分区的数值会报错。

Hash分区

Hash分区主要用来确保数据在预先确定数目的分区中平均分布，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 HASH(store_id)
PARTITIONS 4;

Hash分区也存在与传统Hash分表一样的问题，可扩展性差。MySQL也提供了一个类似于一致Hash的分区方法－线性Hash分区，只需要在定义分区时添加LINEAR关键字

Key分区

Key分区与Hash分区很相似，只是Hash函数不同，定义时把Hash关键字替换成Key即可

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

子分区

子分区是分区表中每个分区的再次分割。创建子分区方法：

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
    );

分区的使用

分区的目的是为了提高查询效率，如果查询范围是所有分区那么就说明分区没有起到作用，我们用explain partitions命令来查看SQL对于分区的使用情况。

一般来说，就是在where条件中加入分区列。

总结

分库分表是应用层面的，分区是数据库层面的。分区对程序改动最小。分表有横向、竖向，但是分区只有横向。

目录