当前位置：首页 > news >正文

Mysql分区表

news 来源：原创 2024/5/13 7:03:49

什么时候使用Mysql分区表？

一般情况下，Mysql单表达到千万级别就可能会查询较慢。

在数据量比较大的情况下，可以考虑使用Mysql分区表。

分区可以将一张表从物理层面根据一定的规则将数据划分为多个分区，多个分区可以单独管理，提升效率。

假如一张表有一千万条数据，拆分成20个分区，在分区数据均匀的情况下，每个分区就只有大概50万数据。

查询全表，就需要在1千万数据中扫描。查询某个分区，就只在50万数据中扫描。

Mysql 分区表

MySQL提供了三种分区类型：范围分区(range)、列表分区(list) 和哈希分区(hash)。

范围分区是最常见的选择。

范围分区(range)

在创建表sql的后面，加上 PARTITION BY RANGE，就是范围分区。

PARTITION BY RANGE(分区字段)， RANGE函数的参数就是分区字段。

LESS THAN 表示小于。MAXVALUE 表示最大的整数。

PARTITION p1 VALUES LESS THAN (18) 表示分区字段小于18的，归到 p1分区。

CREATE TABLE tb_partition_range_test (id INT NOT NULL,age INT
)
PARTITION BY RANGE(age) (#age小于18的，归到 p1分区PARTITION p1 VALUES LESS THAN (18),#age大于18，小于30的，归到 p2 分区。PARTITION p2 VALUES LESS THAN (30),PARTITION p3 VALUES LESS THAN (60),PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

列表分区(list)

示例如下：

CREATE TABLE tb_partition_list_test (id INT NOT NULL,order_type INT
)
PARTITION BY LIST(order_type) (#order_type是1，4，7其中之一的，归到p1分区。PARTITION p1 VALUES  IN (1,4,7),PARTITION p2 VALUES  IN (2,5,8),PARTITION p3 VALUES  IN (3,6,9),PARTITION p4 VALUES  IN (0)
);

哈希分区(hash)

示例如下：

CREATE TABLE tb_emp
(emp_no varchar(20) not null ,
emp_name varchar(20),
birthdate date not null
)
PARTITION BY HASH(year(birthdate))
PARTITIONS 4;

mysql 分区表，指定分区查询

在表名后面加上 PARTITION(分区) 即可。

SELECT * FROM tb_partition_test PARTITION(p202311)

注意！！！

除了常规主键外，用来分区的字段也必须是主键。可以采用复合主键，比如 PRIMARY KEY (id,date)
注意，尽量不要跨分区查询，查询时间会比较久。

跨分区查询：

跨分区查询，如果没有拆分成多个分区范围去查询，就会扫全表，查询时间会比久。

比如按月分区，在查询时，需要把日期进行拆分，然后再用 UNION ALL 或者 java代码进行拼接。

查询日期范围为 03-15 到 04-05 的数据，可以先拆成 03-15到03-31，以及 04-01到 04-05的查询语句。

可以用 EXPLAIN 查看sql语句的执行计划，查看执行计划结果的字段 ROWS 扫描了多少行。

比如：

EXPLAIN SELECT * FROM tb_partition_test where order_date>='2023-03-15' and order_date< '2023-04-05';

增加分区

ALTER TABLE tb_test ADD PARTITION (PARTITION p8 VALUES LESS THAN (80));

删除分区

ALTER TABLE tb_test DROP PARTITION p8;

合并分区

ALTER TABLE tb_test REORGANIZE PARTITION  a,b INTO (PARTITION m VALUES IN (1,5,6,2,7,8));

拆分分区

ALTER TABLE tb_test REORGANIZE PARTITION  a,b,c INTO 
(PARTITION n VALUES IN (1,5,6,3,9,10),
PARTITION m VALUES IN (2,7,8));

Mysql按月分区：

对于按月分区来说，范围分区是最常见的选择，因为它可以根据日期的范围来分区。

而列表分区和哈希分区则需要手动指定分区，不太适合按月分区。

示例如下：

to_days(Date date)：返回从0000年（公元1年）至日期参数date的总天数。

CREATE TABLE `tb_partition_test` (`id` varchar(32) NOT NULL COMMENT 'id',`user_id` varchar(32) DEFAULT NULL COMMENT '用户id',`order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单时间',PRIMARY KEY (`id`,`order_date`),KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表'
PARTITION BY RANGE (to_days(order_date)) (
PARTITION p202301 VALUES LESS THAN (to_days('2023-02-01')),
PARTITION p202302 VALUES LESS THAN (to_days('2023-03-01')),
PARTITION p202303 VALUES LESS THAN (to_days('2023-04-01')),
PARTITION p202304 VALUES LESS THAN (to_days('2023-05-01')),
PARTITION p202305 VALUES LESS THAN (to_days('2023-06-01')),
PARTITION p202306 VALUES LESS THAN (to_days('2023-07-01')),
PARTITION p202307 VALUES LESS THAN (to_days('2023-08-01')),
PARTITION p202308 VALUES LESS THAN (to_days('2023-09-01')),
PARTITION p202309 VALUES LESS THAN (to_days('2023-10-01')),
PARTITION p202310 VALUES LESS THAN (to_days('2023-11-01')),
PARTITION p202311 VALUES LESS THAN (to_days('2023-12-01')),
PARTITION p202312 VALUES LESS THAN (to_days('2024-01-01')),
PARTITION p202401 VALUES LESS THAN (to_days('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (to_days('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (to_days('2024-04-01')),
PARTITION p202404 VALUES LESS THAN (to_days('2024-05-01')),
PARTITION p202405 VALUES LESS THAN (to_days('2024-06-01')),
PARTITION p202406 VALUES LESS THAN (to_days('2024-07-01')),
PARTITION p202407 VALUES LESS THAN (to_days('2024-08-01')),
PARTITION p202408 VALUES LESS THAN (to_days('2024-09-01')),
PARTITION p202409 VALUES LESS THAN (to_days('2024-10-01')),
PARTITION p202410 VALUES LESS THAN (to_days('2024-11-01')),
PARTITION p202411 VALUES LESS THAN (to_days('2024-12-01')),
PARTITION p201412 VALUES LESS THAN (to_days('2025-01-01')),
PARTITION p201501 VALUES LESS THAN (to_days('2025-02-01')),
PARTITION p202502 VALUES LESS THAN (to_days('2025-03-01')),
PARTITION p202503 VALUES LESS THAN (to_days('2025-04-01')),
PARTITION p202504 VALUES LESS THAN (to_days('2025-05-01')),
PARTITION p202505 VALUES LESS THAN (to_days('2025-06-01')),
PARTITION p202506 VALUES LESS THAN (to_days('2025-07-01')),
PARTITION p202507 VALUES LESS THAN (to_days('2025-08-01')),
PARTITION p202508 VALUES LESS THAN (to_days('2025-09-01')),
PARTITION p202509 VALUES LESS THAN (to_days('2025-10-01')),
PARTITION p202510 VALUES LESS THAN (to_days('2025-11-01')),
PARTITION p202511 VALUES LESS THAN (to_days('2025-12-01')),
PARTITION p202512 VALUES LESS THAN (to_days('2026-01-01')),
PARTITION p202601 VALUES LESS THAN (to_days('2026-02-01')),
PARTITION p202602 VALUES LESS THAN (to_days('2026-03-01')),
PARTITION p202603 VALUES LESS THAN (to_days('2026-04-01')),
PARTITION p202604 VALUES LESS THAN (to_days('2026-05-01')),
PARTITION p202605 VALUES LESS THAN (to_days('2026-06-01')),
PARTITION p202606 VALUES LESS THAN (to_days('2026-07-01')),
PARTITION p202607 VALUES LESS THAN (to_days('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (to_days('2026-09-01')),
PARTITION p202609 VALUES LESS THAN (to_days('2026-10-01')),
PARTITION p202610 VALUES LESS THAN (to_days('2026-11-01')),
PARTITION p202611 VALUES LESS THAN (to_days('2026-12-01')),
PARTITION p202612 VALUES LESS THAN (to_days('2027-01-01')),
PARTITION p202701 VALUES LESS THAN (to_days('2027-02-01')),
PARTITION p202702 VALUES LESS THAN (to_days('2027-03-01')),
PARTITION p202703 VALUES LESS THAN (to_days('2027-04-01')),
PARTITION p202704 VALUES LESS THAN (to_days('2027-05-01')),
PARTITION p202705 VALUES LESS THAN (to_days('2027-06-01')),
PARTITION p202706 VALUES LESS THAN (to_days('2027-07-01')),
PARTITION p202707 VALUES LESS THAN (to_days('2027-08-01')),
PARTITION p202708 VALUES LESS THAN (to_days('2027-09-01')),
PARTITION p202709 VALUES LESS THAN (to_days('2027-10-01')),
PARTITION p202710 VALUES LESS THAN (to_days('2027-11-01')),
PARTITION p202711 VALUES LESS THAN (to_days('2027-12-01')),
PARTITION p202712 VALUES LESS THAN (to_days('2028-01-01')),
PARTITION p202801 VALUES LESS THAN (to_days('2028-02-01')),
PARTITION p202802 VALUES LESS THAN (to_days('2028-03-01')),
PARTITION p202803 VALUES LESS THAN (to_days('2028-04-01')),
PARTITION p202804 VALUES LESS THAN (to_days('2028-05-01')),
PARTITION p202805 VALUES LESS THAN (to_days('2028-06-01')),
PARTITION p202806 VALUES LESS THAN (to_days('2028-07-01')),
PARTITION p202807 VALUES LESS THAN (to_days('2028-08-01')),
PARTITION p202808 VALUES LESS THAN (to_days('2028-09-01')),
PARTITION p202809 VALUES LESS THAN (to_days('2028-10-01')),
PARTITION p202810 VALUES LESS THAN (to_days('2028-11-01')),
PARTITION p202811 VALUES LESS THAN (to_days('2028-12-01')),
PARTITION p202812 VALUES LESS THAN (to_days('2029-01-01')),
PARTITION p2029 VALUES LESS THAN (MAXVALUE) )
;