MySQL的分区/分库/分表总结

前言

本文一切基于MySql InnoDB

概念

1. 分区：把一张表的数据分成多个区块，在逻辑上看最终只是一张表，但底层是由多个物理区块组成的
2. 分表：把一张表按一定的规则分解成多个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。
3. 分库：把一个库拆成多个库，突破库级别的数据库操作I/O瓶颈。

在mysql中，schema和库(database)是一个概念

数据库架构演变

一开始，我们只用单机数据库就足够满足业务需要了，但随着业务的拓展，带来越来越多的请求，我们将数据库的写操作和读操作进行分离，使用多个从库副本（Slaver Replication）负责读，使用主库（Master）负责写。从库从主库同步更新数据，保持数据一致。架构上的数据库主从同步，使得从库可以水平扩展，所以更多的读请求不成问题。

但是当用户量级上来后，写请求越来越多，该怎么办？加一个Master是不能解决问题的， 因为数据要保存一致性，写操作需要2个master之间同步，相当于是重复了，而且更加复杂。

这时就需要用到分库分表，对写操作进行切分。

什么情况下要分库分表

任何问题都是太大或者太小的问题，我们这主要面对的是数据量太大的问题。

1. 用户请求量太大
   

• 瓶颈：单服务器的TPS，内存，IO都是有限的。
• 解决方法：分散请求到多个服务器上； 其实用户请求和执行一个sql查询是本质是一样的，都是请求一个资源，只是用户请求还会经过网关，路由，http服务器等。

1. 单表数据量太大
   

• 瓶颈：索引膨胀，查询耗时长，影响正常CRUD。
• 解决方法：切分成多个数据集更小的表。

1. 单库数据量太大
   

• 瓶颈：单个数据库处理能力有限，单库所在服务器上磁盘空间不足，I/O有限；
• 解决方法：切分成更多更小的库

1 分区

首先，我们要明白分区的区是指什么！

我们在《【InnoDB详解二】MySQL文件系统和InnoDB存储结构》一文中提到过，MySQL的物理数据，存储在表空间文件（.ibdata1和.ibd）中， 这里讲的分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件 。

MySQL在5.1时添加了对水平分区的支持。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。

可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区，如：

mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_partition_engine | YES   |
+-----------------------+-------+
1 row in set (0.00 sec)

在如上列出的一个正确的SHOW VARIABLES 命令所产生的输出中，如果没有看到变量have_partition_engine的值为YES，那么MySQL的版本就不支持分区。

MySQL是面向OLTP的数据库，对于分区的使用应该更加小心，如果不清楚如何使用分区可能会对性能产生负面的影响。

1.1 MySQL分区类型

目前MySQL支持一下几种类型的分区：

1. RANGE分区：基于一个给定区间边界，得到若干个连续区间范围，按照分区键的落点，把数据分配到不同的分区；
2. LIST分区：类似RANGE分区，区别在于LIST分区是基于枚举出的值列表分区，RANGE是基于给定连续区间范围分区；
3. HASH分区：基于用户自定义的表达式的返回值，对其根据分区数来取模，从而进行记录在分区间的分配的模式。这个用户自定义的表达式，就是MySQL希望用户填入的哈希函数。
4. KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且使用MySQL 服务器提供的自身的哈希函数。

如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。

在实战中，十有八九都是用RANGE分区。

1.1.1 RANGE分区

RANGE分区是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。

但是记住，当插入的数据不在一个分区中定义的值的时候，会抛异常。

RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。

如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。

实战中可以用int类型的字段来存时间戳做分区列，那么只用存yyyyMM就好了，也不用关心函数了。

MySQL使用PARTITION命令来做分区，sql语句如下：

CREATE TABLE
    `Order` (
        INT NOT NULL AUTO_INCREMENT,
        `partition_key`
        INT NOT NULL,
        `amt`
        DECIMAL(5) NULL) PARTITION BY RANGE(partition_key)
PARTITIONS 5(
    PARTITION part0 VALUES LESS THAN(201901),
    PARTITION part1 VALUES LESS THAN(201902),
    PARTITION part2 VALUES LESS THAN(201903),
    PARTITION part3 VALUES LESS THAN(201904),
    PARTITION part4 VALUES LESS THAN(201905),
    PARTITION part4 VALUES LESS THAN MAXVALUE;

RANGE分区通过使用PARTITION BY RANGE(expr)实现，其中“expr” 可以是某个列值，如id，no，partition_key等。或一个基于某个列值并返回一个整数值的表达式，如YEAR(date)。不过值得注意的是，expr的返回值，不可以为NULL。
其中，MAXVALUE 表示最大的可能的整数值。如果没有设置MAXVALUE这个分区，那么此时如果insert一个partition_key大于201905的记录，MySQL就会抛出异常，插入失败。
VALUES LESS THAN的排列必须从小到大顺序列出，这样MySQL才能识别一个一个的区间段。

这时候我们先插入一些数据：

INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

现在我们查询一下，通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区，不会搜索所有分区:

因为partition_key是分区键。当然，我们也可以直接指定搜索哪个分区:

SELECT * FROM Order PARTITION (part0,part1) WHERE status amt > 1000

注意，如果sql语句不指定分区，则会走所有分区，性能反而会不升反降。所以分区表后，select语句必须走分区键。

涉及聚合函数SUM()、COUNT()的查询时，如果不指定分区，那么会在每个分区上并行处理。例如执行这条语句 SELECT COUNT(1) FROM Order ，则会在每个分区上都同时运行查询；

一个例子不够，我们再举一个例子，来看看expr是个函数表达式的场景，假如现在有如下雇员表：

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 RANGE (YEAR(separated)) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

在这个方案中，在1991年前离职的所有雇员的记录保存在分区p0中，1991年到1995年期间离职的所有雇员的记录保存在分区p1中， 1996年到2000年期间离职的所有雇员的记录保存在分区p2中，2000年后离职的所有工人的信息保存在p3中。

当需要删除“旧的”数据时，使用分区会有意想不到的效果。

假如我们想删除所有在1991年前就已经离职的雇员的记录，你只需简单地使用 ALTER TABLE employees DROP PARTITION p0;

对于有大量行的表，这比运行 DELETE FROM employees WHERE YEAR(separated) <= 1990 要有效得多。

1.1.2 LIST分区

MySQL中的LIST分区在很多方面类似于RANGE分区。和RANGE分区一样，LIST分区的每个分区必须明确定义。它们的主要区别在于，LIST分区是基于枚举出的值列表分区，RANGE是基于给定连续区间范围分区；

LIST分区通过使用 PARTITION BY LIST(expr) 来实现，我们假定有20个音像店，分布在4个有经销权的地区，如下表所示：

要按照属于同一个地区商店的记录保存在同一个分区的原则来分割表，可以使用下面的CREATE TABLE语句：

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

可以看到，和RANGE分区相比，LIST分区的VALUES IN后面接的是枚举值列表，不像RANGE是用VALUES LESS THAN来定义区间边界。

如果试图插入字段值（或分区表达式的返回值）不在分区值列表中的任何一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，那么下面的查询将失败： INSERT INTO employees VALUES (224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21); 。因为“store_id”字段值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。
要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。所以将要匹配的任何值都必须在值列表中能够找到。

1.1.3 HASH分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，我们必须明确指定一个给定的区间或列值集合，来指定哪些记录进入哪些分区；而在HASH分区中，MySQL自动完成分配记录到区间的工作，你所要做的只是确定一个用来做哈希的字段或者表达式，以及指定被分区的表将要被分割成的分区数量。

要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个 PARTITION BY HASH (expr) 子句，其中“expr”同样可以是一个返回一个整数的表达式，或者仅仅是字段类型为整型的某个字段。

此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

例如，下面的语句创建了一个使用基于“store_id”列进行哈希处理的表，该表被分成了4个分区：

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；

如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。

注意，expr不应该设置的过于复杂，因为每当插入或更新（或者可能删除）一行时，这个表达式都要计算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。

最有效率的哈希函数是只对单个表列进行计算，并且它的结果值随字段值进行一致地增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，MySQL就可以越有效地使用该表达式来进行HASH分区。

当使用了“PARTITION BY HASH”时，MySQL将基于用户提供的函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为N ，其中“N = MOD(expr, num)”。

1.1.4 KEY分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供。

MySQLCluster使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

“CREATE TABLE ... PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

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

1.1.5 子分区

子分区是分区表中每个分区的再次分割。例如，考虑下面的CREATE TABLE 语句：

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

表ts有3个RANGE分区。这3个分区（p0, p1, 和 p2）中的每一个分区又被进一步分成了2个子分区。实际上，整个表被分成了3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，p0分区的子分区里，只会保存“purchased”列中值小于1990的那些记录。

在MySQL 5.1中，对于已经通过RANGE或LIST分区了的表再进行子分区是可能的。子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（composite partitioning）。

为了对个别的子分区指定选项，使用SUBPARTITION 子句来明确定义子分区也是可能的。例如，创建在前面例子中给出的同一个表的、一个更加详细的方式如下：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
        PARTITION p0 VALUES LESS THAN (1990)
            SUBPARTITION s0,
            SUBPARTITION s1
        PARTITION p1 VALUES LESS THAN (2000)
            SUBPARTITION s2,
            SUBPARTITION s3
        PARTITION p2 VALUES LESS THAN MAXVALUE
            SUBPARTITION s4,
            SUBPARTITION s5
    );

不过有几点要注意的语法项：

1. 每个分区必须有相同数量的子分区。
2. 如果在一个分区表上的某个分区上使用SUBPARTITION来明确定义子分区，那么就必须定义其他所有分区的子分区。

子分区可以用于特别大的表，在多个磁盘间分配数据和索引。假设有6个磁盘，分别为/disk0， /disk1， /disk2等。现在考虑下面的例子：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
        PARTITION p0 VALUES LESS THAN (1990)
            SUBPARTITION s0a
                DATA DIRECTORY = '/disk0'
                INDEX DIRECTORY = '/disk1',
            SUBPARTITION s0b
                DATA DIRECTORY = '/disk2' 
                INDEX DIRECTORY = '/disk3'
        PARTITION p1 VALUES LESS THAN (2000)
            SUBPARTITION s1a
                DATA DIRECTORY = '/disk4/data' 
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1b
                DATA DIRECTORY = '/disk5/data' 
                INDEX DIRECTORY = '/disk5/idx'
        PARTITION p2 VALUES LESS THAN MAXVALUE