MySQL-表分区和分表

最新推荐文章于 2025-08-13 10:08:49 发布

原创最新推荐文章于 2025-08-13 10:08:49 发布 · 1.2k 阅读

2 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

33 篇文章

订阅专栏

本文详细介绍了数据库中的表分区技术，包括RANGE、LIST、HASH和KEY四种分区方式，并通过具体示例展示了每种分区的应用场景。此外，还讨论了分表的概念及其在Merge存储引擎中的实现。

1.表分区和分表

表分区
分表：分表分为垂直拆分和水平拆分

2.表分区技术

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
LIST分区：类似于RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
KEY分区：类似于HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供自身的哈希函数

2.1 RANGE分区

create table if not EXISTS employees(
	id int not null,
	fname VARCHAR(32),
	lname VARCHAR(32),
	hired date not null DEFAULT '1970-01-01 00:00:00',
	separated date not null DEFAULT '9999-12-31 00:00:00',
	job_code int,
	store_id int
)ENGINE=InnoDB

PARTITION BY RANGE (YEAR(hired))(
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

PARTITION BY RANGE (store_id)(
PARTITION p0 VALUES LESS THAN (1000),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (3000),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

查看数据表文件的存放路径，进入datadir目录

show variables like '%datadir%';

如果表引擎是 MyIsam，每个分区会生成一个 .MYD 文件，一个.MYI文件()

.frm 存储表结构
.par 存储分区
.MYD 存储data数据
.MYI 存储索引

2.1 LIST分区

create table if not EXISTS employees(
	id int not null,
	fname VARCHAR(32),
	lname VARCHAR(32),
	hired date not null DEFAULT '1970-01-01 00:00:00',
	separated date not null DEFAULT '9999-12-31 00:00:00',
	job_code VARCHAR(32),
	store_id int
)ENGINE=InnoDB

PARTITION BY LIST (store_id)(
PARTITION p0 VALUES IN (1),
PARTITION p1 VALUES IN (2),
PARTITION p2 VALUES IN (3,4)
);

PARTITION BY LIST COLUMNS(job_code)(
PARTITION p0 VALUES IN ('a'),
PARTITION p1 VALUES IN ('b'),
PARTITION p2 VALUES IN ('c','d')
);

2.3 HASH分区

用 YEAR(hired) 返回值的哈希值进行分区

create table if not EXISTS employees(
	id int not null,
	fname VARCHAR(32),
	lname VARCHAR(32),
	hired date not null DEFAULT '1970-01-01 00:00:00',
	separated date not null DEFAULT '9999-12-31 00:00:00',
	job_code VARCHAR(32),
	store_id int
)ENGINE=InnoDB

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

2.3 HASH分区（用的不多）

他的key可以不是整数类型，如字符串等类型,key分区支持除了blob和text类型之外的其他类型。key分区不支持用户自定义表达式

create table if not EXISTS employees(
	id int not null,
	fname VARCHAR(32),
	lname VARCHAR(32),
	hired date not null DEFAULT '1970-01-01 00:00:00',
	separated date not null DEFAULT '9999-12-31 00:00:00',
	job_code VARCHAR(32),
	store_id int
)ENGINE=InnoDB

PARTITION BY KEY(job_code)
PARTITIONS 4
;

3.分表

3.1 Merge存储引擎分表

Merge引擎是一组MyISAM表的组合,组合的分表结构必须完全相同,Merge表本身没有数据

CREATE TABLE t1 ( 
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
message CHAR(32))ENGINE=MyIsam;
CREATE TABLE t2 ( 
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
message CHAR(32))ENGINE=MyIsam;

CREATE TABLE total (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(32)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

#加入子表
CREATE TABLE t3( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message CHAR(32))ENGINE=MyIsam;
ALTER TABLE total UNION=(t1,t2,t3);