分区依据
mysql中表的数据到达5000万的数据时就需要对表进行分区以及分表操作了
分区键的作用：分区键用于决定数据的分区。根据分区的类型（范围分区、列表分区、哈希分区等），分区键的值被用来计算数据应该存储在哪个分区。
- 分区键是主键的一部分，或者是唯一索引的一部分，这样每个分区内部的记录可以通过 主键或唯一索引 来保证唯一性。
- 如果分区表没有这样的限制，可能会出现某些分区中的数据有重复的行，这会破坏分区表的完整性和一致性。
分区类型
range分区的使用场景
a.主要用于日期的分区（对于销售类的表，可以根据年来分区存放销售的记录）
create table sales(
money int not null,date datetime)engine=innodb
partition by range (year(date))(
partition values less than (2009),
partition p2009 values less than (2010),
partition p2010 values less than (2011)
);
insert into sales select 100,'2008-01-01';
insert into sales select 100.'2008-02-01';
insert into sales select 100.'2008-01-02';
insert into sales select 100,'2009-03-01';
insert into sales select 100,'2010-01-01'; 删除固定的数据时 我们只需要删除对应的分区
ALTER TABLE sales DROP PARTITION p2008; 需使用大写删除
在使用select * from sales where date >"2008-01-01" and date <'2008-02-01'
select * from sales where date >"2008-01-01" and date <='2008-01-31'
创建表： 创建range分区
create table t3(
id int)engine=innodb
partition by range(id)(
partition p0 values less than (10),
partition p1 values less than (20)
);
存储的数据文件如下t3#p#p0.ibd t3#p#p1.ibd 两个数据文件可发现
查看分区数据的分析情况select * from partitions where table_schema="test" and table_name="t3"\G;
当插入insert into t3 (id) value(40) 会出现（不满足分区条件）
mysql> insert into t3 (id) value(401);
ERROR 1526 (HY000): Table has no partition for value 401
如何解决：
添加对应的分区：
mysql> alter table t3 add partition(partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql>
mysql> insert into t3 (id) value(401);
Query OK, 1 row affected (0.01 sec)
select * from partitions where table_schema="test" and table_name="t3"\G; 会多出对应的分区root@wadequ-ThinkPad-L13:/var/lib/mysql/test# ls
classes.ibd students.ibd sys_base_menus.ibd sys_users.ibd teachers.ibd
courses.ibd sys_authorities.ibd sys_data_authority_id.ibd t3#p#p0.ibd t.ibd
student2course.ibd sys_authority_menus.ibd sys_user_authority.ibd t3#p#p1.ibd user.ibd
mysql> select * from partitions where table_schema="test" and table_name="t3"\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t3
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: `id`
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 10
TABLE_ROWS: 1
AVG_ROW_LENGTH: 16384
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2025-03-13 11:21:37
UPDATE_TIME: 2025-03-13 11:25:31
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t3
PARTITION_NAME: p1
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: `id`
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 20
TABLE_ROWS: 1
AVG_ROW_LENGTH: 16384
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2025-03-13 11:21:37
UPDATE_TIME: 2025-03-13 11:25:24
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
2 rows in set (0.00 sec)list分区的使用
List 分区：针对离散的值，分区需明确指定的值。当把指定的数据插入至指定分区表时，
用insert插入多行数据的过程中遇到分区为定义的值，myisaminnodb存储殷勤的处理完全不同，m
myisam 一条不成功，之前的成功值，会进入表中
。innodb只要一条不成功，所有都不成功
针对list分区中 myisam存储引擎在没有分区创建对应的值时 其前面的数据会执行插入，但是innodb会执行不成功的（其支持事务）
create table t_list (a int,b int)engine=innodb
partition by list(b)(partition p0 values in(1,3,5,7,9),
partition p1 values in (0,2,4,6,8));mysql> insert into t_list values (1,2),(2,4),(6,19),(5,3);
ERROR 1526 (HY000): Table has no partition for value 19
若处于分区之外，便会呈报上述的错误。
hash 分区：
根据用户的表达式的返回值来进行分区，返回值不能是负数
要在create table 语句上添加一个partition by hash(expr)句子，其中expr是一个返回一个整数的表达式，它可以仅仅是数字段类型为mysql整型的列名字
后面在添加一个partitions num子句，num是一个非负数
下面创建四个分区
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table t_hash(a int,b date)engine=innodb
-> partition by hash(YEAR(b))
-> partitions 4;
Query OK, 0 rows affected (0.26 sec)执行的数据结构如下
所有分区列必须是整形的数据
columns分区
区别于其他分区，分区条件必须是整型，如果不是整型也应该需要通过函数将其转化为整型 columns分时是rang list分区的进化
支持整型类型
日期类型date datetime其余的日期类型不予支持
字符串类型 char varcha binary varbinary ,blok和text类型的不予支持
mysql> show create table tt_column_range;
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt_column_range | CREATE TABLE `tt_column_range` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE COLUMNS(a,b)
(PARTITION p0 VALUES LESS THAN (0,10) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10,20) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (20,30) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (30,40) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (40,50) ENGINE = InnoDB) */ |创建子分区的方法案例
子分区：MYSQL数据库允许在rang和list的分区上再进行hask或者key子分区，
create table ts(a int,b date)engine=innodb
partition by range(year(b))
subpartition by hash(to_days(b))
subpartitions 3(
partition p0 values less than (2013),
partition p0 values less than (2014),
partition p1 values less than (2015)
partition p2 values less than maxvalue);
不同的方法创建子分区的方法
create table tssub(a int,b date)
partition by range(year(b)
subpartition by hash(to_days(b))(
partition p0 values less than(2014)(
subpartition s0,
subpartition s1)
partition p1 values less than (2015)(
subpartition s2,
subpartition s3
)
partition p2 values less than maxvalue(
subpartition s4,
subpartition s5
)
)1.每个子分区必须包含分区的名字，子分区的名字唯一
当你创建带有子分区的分区表时，每个子分区必须有唯一的名字，这样系统才能区分不同的子分区。这是为了避免命名冲突和确保对每个子分区的引用能够明确无误。
2.NULL 值处理
在 MySQL 的分区中，NULL 值有一个特殊的处理规则：在范围分区（RANGE）中，所有 NULL 值会被自动放置在最左边的分区，也就是分区的最小值区域。这是为了确保 NULL 值不被遗漏，并且能够正确地与其他数据进行比较。
3.使用 RANGE 分区处理 NULL 值
在范围分区中，NULL 值总是会被自动归到最左边的分区。这是由于 MySQL 认为 NULL 无法和任何值进行有效的比较，因此它会将其放在最小的分区中。
示例：
sql复制代码
CREATE TABLE t3 ( id INT ) ENGINE=InnoDB PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20) );
在上面的例子中，所有 id 值为 NULL 的记录会被自动插入到分区 p0 中，因为 NULL 被视为最小值。
4.使用 LIST 分区处理 NULL 值
对于 LIST 分区类型，NULL 值的处理稍微不同。LIST 分区要求你明确指定如何处理 NULL 值。如果没有明确指定，NULL 会被视为一个独立的类别，并放入一个默认的分区。
CREATE TABLE users ( id INT, region VARCHAR(50) ) ENGINE=InnoDB PARTITION BY LIST (region) ( PARTITION p0 VALUES IN ('North', 'South'), PARTITION p1 VALUES IN ('East', 'West') );
如果我们将 region 列的值设置为 NULL，NULL 会被放入一个默认的分区（如果没有为 NULL 特别定义的话）。为了明确处理 NULL 值，我们可以在 LIST 分区中明确指定 NULL 的处理方式。
CREATE TABLE users ( id INT, region VARCHAR(50) ) ENGINE=InnoDB PARTITION BY LIST (region) ( PARTITION p0 VALUES IN ('North', 'South'), PARTITION p1 VALUES IN ('East', 'West'), PARTITION p_null VALUES IN (NULL) );
在这个示例中，我们显式地将 NULL 值放入了一个名为 p_null 的分区。这样，所有 region 为 NULL 的记录会被插入到 p_null 分区中。