MySQL 表格分区
什么情况下,考虑 表格分区?
项目中,经常会遇到数据量比较大的表格(百万、千万、亿),这个时候就要根据业务,考虑表格分区的设计思维了。
通过将表格的行分成多个逻辑分区,可以更快地执行涉及这些分区的查询,并降低对大量数据的访问需求。
MySQL 分区步骤
MySQL 表格分区是一种将一个表格分成多个较小的、独立的区域,以提高查询性能和管理方便性的技术。下面是操作 MySQL 表格分区的一般步骤:
- RANGE:根据列的值的范围进行分区。
- LIST:根据列的值的列表进行分区。
- HASH:根据列的哈希值进行分区。
- KEY:根据列的索引进行分区。
设计表格时,考虑好分区一般为:RANGE + LIST 、 HASH + KEY
例如,创建一个按年份分区的顾客表格:
-- 第一种:RANGE分区 根据年份将数据进行分区处理。
CREATE TABLE customers (
id INT,
name VARCHAR(50),
year INT,
partitioned_column VARCHAR(50)
) PARTITION BY RANGE (year) (
PARTITION p0 VALUES LESS THAN (2000),
PARTITION p1 VALUES LESS THAN (2005),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
-- 第二种:HASH分区 根据月份将数据进行分区处理
-- 此处,是划分了12个分区。
CREATE TABLE customers (
id INT,
name VARCHAR(50),
crt_date date,
partitioned_column VARCHAR(50)
) PARTITION BY HASH(MONTH(crt_date))
PARTITIONS 12;
-- 第三种:LIST分区 根据顾客状态进行分区处理
CREATE TABLE customers (
id INT,
name VARCHAR(50),
status char(1),
crt_date date,
partitioned_column VARCHAR(50)
) PARTITION BY list(status)
PARTITIONS 12;
-- 第四种:KEY分区 根据相同KEY写入同一个分区
CREATE TABLE customers (
id INT,
name VARCHAR(50),
status char(1),
crt_date date,
partitioned_column VARCHAR(50)
) PARTITION BY key(id)
PARTITIONS 12
-- 通过下面的命令,可以将原来的12个分区改为6个分区
ALTER TABLE customers COALESCE PARTITION 6;
INSERT INTO customers (id, name, year, partitioned_column) VALUES (1, 'John Doe', 2008, 'some value');
SELECT * FROM customers WHERE year = 2008;
ALTER TABLE customers ADD PARTITION (PARTITION p4 VALUES LESS THAN (2015));
使用SHOW TABLE STATUS
命令:查看表格的状态和分区信息。
# 查看所有表格的状态和分区信息
show table status;
使用** OPTIMIZE TABLE
命令:是优化表格的命令。也可以优化和修复分区。
它的作用主要是重建表索引,并压缩表碎片,使得数据物理排列更加紧密,从而加快查询速度。此外,它还可以更新统计信息和缓存,从而减少数据读取次数和IO操作,大大提高数据库效率。
场景:优化删除大量数据后的表,或者对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行很多更改后的优化。一般针对特定的表格,进行周期运行。
optimize table customers;
使用 ALTER TABLE ... REORGANIZE PARTITION
命令:可以合并相邻的分区。
-- 分解分区 将p0拆分为s0和s1
ALTER TABLE customers REORGANIZE PARTITION p0 INTO (
PARTITION s0 VALUES LESS THAN (3),
PARTITION s1 VALUES LESS THAN (5)
);
-- 合并分区 将s0和s1合并为p0
ALTER TABLE customers REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (5)
);
-- 合并分区 将s0和s1合并为 r0 r1
ALTER TABLE customers REORGANIZE PARTITION s0,s1 INTO (
PARTITION s0 VALUES LESS THAN (2),
PARTITION p0 VALUES LESS THAN (5)
);
使用 ALTER TABLE ... ADD PARTITION
命令:可以添加新的分区。
alter table customers add partition (partition p5 VALUES LESS THAN (2020))
使用 ALTER TABLE ... COALESCE PARTITION
命令:修改分区数量。
-- 通过下面的命令,将原来的12个分区改为6个分区
ALTER TABLE customers COALESCE PARTITION 6;
什么场景下,具体使用什么分区?
RANGE COLUMNS 用法
RANGE COLUMNS是RANGE分区的一种特殊类型,它与RANGE分区的区别如下:
CREATE TABLE rcx (
a INT,
b INT,
c CHAR(3),
d INT
)
PARTITION BY RANGE COLUMNS(a,d,c) (
PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
PARTITION p2 VALUES LESS THAN (15,30,'sss'),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);