分区依据

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 分区中。

mysql表分区实现-range（mysql表分区实际开发运用）

分区依据

list分区的使用

hash 分区：

根据用户的表达式的返回值来进行分区，返回值不能是负数

columns分区

1.每个子分区必须包含分区的名字，子分区的名字唯一

2.NULL 值处理

3.使用 RANGE 分区处理 NULL 值

示例：

4.使用 LIST 分区处理 NULL 值

相关推荐

C# 13 和 .NET 9 全知道 :13 使用 ASP.NET Core 构建网站 (1)

程序员的开源月刊《HelloGitHub》第 71 期

如何将AI助手接入微信（打开ai手机助手）

SparkSQL——DataFrame的创建与使用

使用过 Redis 分布式锁么，它是什么回事?

VUE循环语句的使用(v-for)（vuefor循环的key）

HiveOs系统教程最细手把手教学（hiveos启动）

HIVE SQL基础语法（hive-sql）

Spring Boot 概述（spring boot干嘛的）

《循环(for/while)》（循环while语句）