分区表在逻辑上为一个表,在物理上存储在多个文件中。

1.HSAH分区表

在使用分区表前,先检查先安装的mysql是否支持分区表

mysql> SHOW PLUGINS;

如果partition值为ACTIVE就表示当前mysql支持分区表

ssl

下面以用户登录日志表为例:

CREATE TABLE customer_login_log(
	customer_id INT(10) UNSIGNED NOT NULL COMMENT '用户登录ID',
	login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
	login_ip INT(10) UNSIGNED NOT NULL COMMENT '登录IP',
	login_type TINYINT(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '用户登录日志表'
PARTITION BY HASH(customer_id)
	PARTITIONS 4;

分区表非分区表除了在建表语句上不同,在物理文件上也是不同的。

ssl

HASH分区的特点

根据MOD(分区键,分区数)的值把数据行存储到表的不同分区中,数据可以平均的分布在各个分区中。HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型。

ssl

第一个sql分区键是整型的,直接使用HASH函数进行建表。第二个sql由于使用login_time作为分区键,login_time不是整型,所以要用UNIX_TIMESTAMP函数将login_time转换成整型,然后在使用HASH函数建表。

将非整形转换整型函数

ssl

2.RANGE分区

RANGE分区:按范围进行分区

特点:

下面我们还是以用户登录表为例:

CREATE TABLE customer_login_log(
	customer_id INT(10) UNSIGNED NOT NULL COMMENT '用户登录ID',
	login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
	login_ip INT(10) UNSIGNED NOT NULL COMMENT '登录IP',
	login_type TINYINT(4) NOT NULL COMMENT '登录类型:0未成功 1成功'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '用户登录日志表'
PARTITION BY RANGE (customer_id)(
	PARTITION p0 VALUES LESS THAN (10000),
	PARTITION p1 VALUES LESS THAN (20000),
	PARTITION p2 VALUES LESS THAN (30000),
	PARTITION p3 VALUES LESS THAN MAXVALUE
);

p0分区存放customer_id小于10000(0-9999),p1分区存放customer_id在(10000-19999),p2分区存放customer_id在(20000-29999)范围内,customer_id大于30000都存放在p3分区。

RANGE分区的适用场景

RANGE分区的适用场景:

3.LIST分区

LIST分区特点:

下面还是以用户登录日志表为例:

CREATE TABLE customer_login_log(
	customer_id INT(10) UNSIGNED NOT NULL COMMENT '用户登录ID',
	login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
	login_ip INT(10) UNSIGNED NOT NULL COMMENT '登录IP',
	login_type TINYINT(4) NOT NULL COMMENT '登录类型'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '用户登录日志表'
PARTITION BY LIST (login_type)(
	PARTITION p0 VALUES IN (1,3,5,7,9)
	PARTITION p1 VALUES IN (2,4,6,8)
);

这里指定login_type作为分区键,login_type取值为(1,3,5,7,9)放在p0分区,值为(2,4,6,8)放在p1分区。

4.如果选择合适的分区表

如何为customer_login_log选择合适的分区表?首先我们要分析用户登录日志表的业务场景,用户登录表主要记录用户每次登录的日志,即用户每登录一次,就会在customer_login_log中产生一条记录,由此可见,用户登录表中的数据增长是非常快的。假设公司要求用户登录日志保存一年,一年后可以删除。

在这样的业务场景下,使用RANGE分区比较合适,我们可以很方便的根据分区的范围来删除分区数据,特别是在数据量非常大的时候。

确定好分区类型之后,接下来还需要确定分区键,在用户登录表中只有login_time是时间字段,后面删除分区数据也是按照时间来删除的,所以就选login_time作为分区键

下面sql是按照login_time来建立的分区表

	CREATE TABLE customer_login_log(
	customer_id INT(10) UNSINGED NO NULL,
	login_time DATETIME NOT NULL,
	login_ip INT(10) UNSIGNED NOT NULL,
	login_type TINYINT(4) NOT NULL
)ENGINE=INNODB
PARTITION BY RANGE(YEAR(login_time))(
	PARTITION p0 VALUES LESS THAN (2015),
	PARTITION p1 VALUES LESS THAN (2016),
	PARTITION p2 VALUES LESS THAN (2017)
);

mysql 电商项目实战

mysql 数据库设计规范(1) mysql 数据库范式(2) mysql 用户模块设计(3) mysql 分区(4)