MySQL的分区和分表

朴灿烈づ我的快乐病毒、 2022-05-31 11:48 258阅读 0赞

### 1. 分区 ###

MySQL中的分区是指将一个数据表按照某种规则（如时间范围、哈希等），划分为多个区块，各个区块所属的数据文件是相互独立的。分区对于SQL层来说，是一个完全封装的、底层实现的黑盒子；但对于底层的文件系统来说，每一个区块都对应 一个使用 \# 分割命名的表文件。

分区的一个主要目的是将数据按照一个较粗的粒度划分到不同的分区表中，这样，可以将相关的数据存放在一起，另外，如果想一次删除整个分区的数据也会变得非常方便。

分区的应用场景：

*  表的数据量非常大或者只在表的最后部分有热点数据，其他都是历史数据或冷数据。
 *  批量删除大量数据，可以使用删除整个分区的方式。
 *  分区表的数据可以分布到不同的磁盘上，从而高效地利用多个硬件设备。
 *  可以对独立的分区进行优化、检查、修复、备份和恢复等操作，易于维护。

分区的局限性：

*  一个表最多只能有1024个分区。
 *  表中如果有主键或唯一索引，则必须包含到分区字段中。
 *  分区表中无法使用外键。
 *  所有分区都必须使用相同的存储引擎。
 *  某些存储引擎不支持分区。

在创建表时，使用 PARTITION BY 子句，来定义每个分区中存放的数据。

在执行查询操作时，优化器会根据分区的定义过滤掉那些没有我们所要数据的分区，这样，查询就不会扫描所有分区，只查找包含我们所要数据的分区就可以了。

分区的类型：

*  范围分区（PARTITION BY RANGE 或 PARTITION BY RANGE COLUMNS）
 *  列表分区（PARTITION BY LIST）
 *  哈希分区（PARTITION BY HASH)
 *  索引分区（PARTITION BY KEY）

对于 RANGE 或 LIST 类型的分区，还支持子分区 SUBPARTITION，不过，生产环境中很少见。

#### 1.1 范围分区 ####

MySQL支持多种分区表，最常见的就是根据范围进行分区。

根据范围分区时，每个分区存储指定范围的数据，分区表达式可以是列（字段），也可以是包含列的表达式。

根据范围分区时，范围应该连续但是不重叠，使用PARTITION BY RANGE, VALUES LESS THAN关键字。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

下面就是一个将日志记录表 log\_1 根据时间范围来分区的例子。

根据具体的年份：

create table if not exists log_1 ( content varchar(200) not null default '' COMMENT '日志内容', create_time datetime not null default CURRENT_TIMESTAMP COMMENT '创建时间', KEY create_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日志记录表' PARTITION BY RANGE(YEAR(create_time))( PARTITION p_null VALUES LESS THAN (2017), PARTITION p_2017 VALUES LESS THAN (2018), PARTITION p_2018 VALUES LESS THAN (2019), PARTITION p_2019 VALUES LESS THAN (2020), PARTITION p_final VALUES LESS THAN MAXVALUE);

根据时间戳：

PARTITION BY RANGE ( UNIX_TIMESTAMP(create_time) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-04-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-07-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( MAXVALUE ) );

根据COLUMNS关键字：

添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势。

PARTITION BY RANGE COLUMNS( create_time ) ( PARTITION p0 VALUES LESS THAN ('2017-01-01 00:00:00'), PARTITION p1 VALUES LESS THAN ('2018-01-01 00:00:00'), PARTITION p2 VALUES LESS THAN ('2019-01-01 00:00:00'), PARTITION p3 VALUES LESS THAN ('2020-01-01 00:00:00'), PARTITION p4 VALUES LESS THAN MAXVALUE );

#### 1.2 列表分区 ####

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

PARTITION BY LIST(cate_id) ( PARTITION p1 VALUES IN (1,2,3), PARTITION p2 VALUES IN (4,5,6), PARTITION p3 VALUES IN (7,8,9), PARTITION p4 VALUES IN (10,11,12) );

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

#### 1.3 哈希分区 ####

Hash分区主要用来确保数据在预先确定数目的分区中平均分布，Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。

PARTITION BY HASH( YEAR(creat_time) )
    PARTITIONS 4;

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

PARTITION BY LINEAR HASH( YEAR(creat_time) )
    PARTITIONS 4;

#### 1.4 索引分区 ####

Key分区与Hash分区很相似，只是函数不同，定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。

PARTITION BY LINEAR KEY (id)
    PARTITIONS 4;

分区查询优化：

分区给查询优化带来了新的思路。分区的最大优点就是优化器可以根据分区的定义过滤掉一些分区。可以把分区当作是一种粗粒度的索引策略。

查询分区表时，必须在WHERE条件中加入分区列（即使看似多余的也要带上），这样就可以让优化器过滤掉无须访问的分区。

使用 EXPLAIN PARTITIONS 可以检查优化器是否进行了分区过滤。

EXPLAIN PARTITIONS SELECT * FROM log_1

上面这个查询语句将访问所有的分区，效率很低。下面，我们加入一个where条件。

EXPLAIN PARTITIONS SELECT * FROM log_1 WHERE create_time>'2017-01-01' AND create_time<='2017-12-31'

这时，查询就只会访问 p\_2017 分区，而过滤掉其他分区。

注意： MySQL只能在使用分区函数的列本身进行WHERE比较时才能过滤分区，而不能根据表达式的值来来过滤分区。

下面这条查询语句就无法过滤分区：

EXPLAIN PARTITIONS SELECT * FROM log_1 WHERE YEAR(create_time)=2017

这和查询语句中使用独立的列才能用到索引的道理是一样的。

### 2. 分表 ###

#### 2.1 合并表（Merge table） ####

合并表属于分表的一种方式，和分区表类似，在MyISAM中，各个子表被一个结构完全相同的逻辑表所封装，但合并表允许用户直接访问各个子表。而分区表的子表（分区）都是被MySQL隐藏的，只能通过分区表去访问子表。

合并表相当于一个容器，里面包含了多个真实的子表。

创建合并表时，使用 UNION 关键字来指定合并表中包含哪些子表。合并表必须采用 MERGE 引擎。

# 子表t1
    create table t1( id int not null primary key, name char(10) not null, )ENGINE=MyISAM;
    
    # 子表t2
    create table t2( id int not null primary key, name char(10) not null, )ENGINE=MyISAM;
    
    # 合并表m
    create table mrg( id int not null primary key, name char(10) not null, )ENGINE=MERGE UINON(t1,t2) INSERT_METHOD=LAST;

INSERT\_METHOD=LAST语法的作用是将所有的INSERT语句都发送给最后一个子表。FIRST或LAST关键字，可以控制将数据插入到合并表中的哪一个子表。当然，也可以直接在SQL中操作子表。

#### 2.2 切分表 ####

切分表是指将表中的数据按照水平分割或垂直分割的方式分配到多张表中。

通常，我们所说的分表是指水平分割，也就是传统的分表技术。

常用的分表方法有：

*  范围分表
 *  哈希分表

分表后，如果想对总数据进行统计（count、sum等），只能对所有子表统计后，再在应用层再次计算得出最后的统计数据。而分区则不受影响，直接统计分区表即可。

也就是说，分表后，原来的应用层代码需要做较大的改动。