mysql一个分区多少数据合适 mysql分区表最大容量

转载

mob64ca1418736f 2023-11-09 08:51:47

文章标签 mysql一个分区多少数据合适 mysql 架构数据库分区表 文章分类 MySQL 数据库

一、分区表

1.分区表的使用场景
2.分区表的限制
3.分区表的原理
4.分区表的类型
5.在使用分区表的时候需要注意的问题

二、分库分表

一、分区表

1.分区表的使用场景

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
分区表的数据更容易维护（根据逻辑分区，批量删除大量数据可以使用清除整个分区的方式，也可以对一个独立分区进行优化、检查、修复等操作）
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
可以使用分区表来避免某些特殊的瓶颈（比如锁和IO速度）
可以备份和恢复独立的分区

2.分区表的限制

一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区（分区有限，依版本不同改动）
在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
分区表无法使用外键约束

3.分区表的原理

select
先打开并锁住所有表，优化器判断是否可以过滤部分分区，然后再访问各自的分区（过滤->访问）
insert
先打开并锁住所有表，然后确定哪个分区接收这条记录（确定分区->插入）
delete
先打开并锁住所有表，确定数据位置（哪个分区），再删除（确定分区->删除）
update
先打开并锁住所有表，确定数据位置（哪个分区），更新，更新后判断属于哪个分区，最后再删除原分区数据，再insert新分区(确定原数据分区->取出更新->删除原数据->寻找新分区->插入新分区)

4.分区表的类型

1.范围分区
比如id 1~100万一个分区,100,0001 ~ 200万第二个分区
语法：

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

2.列表分区
也是类似范围分区，不过是用in

PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

3.列分区
其实就是列表分区的升级版，支持非整形类型和多个列

PARTITION BY RANGE COLUMNS(c3)(
	PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
 	PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB
 );

4.hash分区

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

"4"是分区表的数量

5.key分区

PARTITION BY LINEAR KEY (col1) PARTITIONS 3;

类似hash分区，不过不支持表达式

6.组合分区
在分区的基础之上，再进行分区后存储

PARTITION BY RANGE(id)
SUBPARTITION BY HASH(sGrade) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(5),
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(15)
);