1、什么是表分区？
分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。
分区功能并不是在存储引擎层完成的，常见的存储引擎InnoDB、MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持分区。在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。
MySQL数据库在5.1版本及以上时添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。
MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。
2、表分区有什么好处？
分区的好处是：
- 可以让单表存储更多的数据
- 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
- 部分查询能够从查询条件确定只落在少数分区上，速度会很快（查询条件尽量扫描少的分区）
- 分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备
- 可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
-
可以备份和恢复单个分区
-
分区的限制和缺点：
- 一个表最多只能有1024个分区
- 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
- 分区表无法使用外键约束
- NULL值会使分区过滤无效
- 所有分区必须使用相同的存储引擎
3、表分区与分表的区别？
分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。 分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。
1
2
3
4
5
6
7
mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
row in set (0.00 sec)
4. mysql 分区表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- 查看状态
show table status
-- 新增分区表, 主键必须包含分区字段
CREATE TABLE log_test (
obj_id BIGINT NOT NULL AUTO_INCREMENT,
log_date datetime default CURRENT_TIMESTAMP,
timestamp date,
raw TEXT,
PRIMARY KEY (obj_id, timestamp)
) ENGINE=innodb
PARTITION BY RANGE (YEAR(timestamp) * 100 + MONTH(timestamp)) (
PARTITION p202301 VALUES LESS THAN (202301),
PARTITION p202302 VALUES LESS THAN (202302),
PARTITION p202303 VALUES LESS THAN (202303),
PARTITION p202312 VALUES LESS THAN (202312),
PARTITION p202501 VALUES LESS THAN (202501)
-- 如果后续没有添加分区的需求, 那么可以加最大分区
-- PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE)
);
-- 添加新的分区
ALTER TABLE log_test
ADD PARTITION (
PARTITION p202501 VALUES LESS THAN (202502),
PARTITION p202502 VALUES LESS THAN (202503),
PARTITION p202503 VALUES LESS THAN (202504),
PARTITION p202512 VALUES LESS THAN (202512)
-- 如果后续没有添加分区的需求, 那么可以加最大分区
-- PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE)
);
-- 合并分区 (保留p202302, 会删掉 p202301)
alter table log_test reorganize partition p202301,p202302 into (partition p202302 values less than (202302));
-- 查询所有分区信息
SELECT partition_name, subpartition_name, table_rows
FROM information_schema.partitions
WHERE table_name = 'log_test';
-- 删除分区(会删除数据)
ALTER TABLE log_test DROP PARTITION pMAXVALUE;
-- 删除表的所有分区（不会丢失数据）
alter table log_test remove partitioning;
-- 插入数据
INSERT INTO log_test (obj_id, timestamp, raw) VALUES ('1','2023-09-21T02:00:06.015390789-04:00','信息,信息')
-- 查询分区数据
SELECT * FROM log_test PARTITION (p202312);
pg 分区表使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 创建分区表
CREATE TABLE "public"."log_test" (
"obj_id" int8 NOT NULL,
"log_date" TIMESTAMP default CURRENT_TIMESTAMP,
"raw" text COLLATE "pg_catalog"."default",
"timestamp" date NOT NULL
)
PARTITION BY RANGE ("timestamp");
-- 设置主键
alter table "public".log_test add CONSTRAINT pk_id_dd PRIMARY KEY (obj_id, timestamp)
-- 创建分区
CREATE TABLE "public"."log_test_p1" PARTITION OF "public"."log_test" FOR VALUES FROM ('2023-09-16') TO ('2023-09-30');
-- 创建分区
CREATE TABLE "public"."log_test_p2" PARTITION OF "public"."log_test" FOR VALUES FROM ('2023-09-01') TO ('2023-09-15');
ALTER TABLE "public"."log_test" OWNER TO "postgres";
-- 删除分区(不会删除分区内的数据, 分区表会变成普通表)
ALTER TABLE "public"."log_test" DETACH PARTITION log_test_p1;
-- 插入数据
INSERT INTO log_test (obj_id, timestamp, raw) VALUES ('1','2023-09-21T02:00:06.015390789-04:00','信息,信息')
-- 查询分区数据
SELECT * FROM log_test_p1;