百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

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

wptr33 2025-04-07 20:06 6 浏览

分区依据

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

    相关推荐

    Linux高性能服务器设计

    C10K和C10M计算机领域的很多技术都是需求推动的，上世纪90年代，由于互联网的飞速发展，网络服务器无法支撑快速增长的用户规模。1999年，DanKegel提出了著名的C10问题：一台服务器上同时...

    独立游戏开发者常犯的十大错误

    ...

    学C了一头雾水该咋办?

    学C了一头雾水该怎么办？最简单的方法就是你再学一遍呗。俗话说熟能生巧，铁杵也能磨成针。但是一味的为学而学，这个好像没什么卵用。为什么学了还是一头雾水，重点就在这，找出为什么会这个样子？１、概念理解不深...

    C++基础语法梳理:inline 内联函数!虚函数可以是内联函数吗?

    上节我们分析了C++基础语法的const，static以及this指针，那么这节内容我们来看一下inline内联函数吧！inline内联函数...

    C语言实战小游戏:井字棋(三子棋)大战!文内含有源码

    井字棋是黑白棋的一种。井字棋是一种民间传统游戏，又叫九宫棋、圈圈叉叉、一条龙、三子旗等。将正方形对角线连起来，相对两边依次摆上三个双方棋子，只要将自己的三个棋子走成一条线，对方就算输了。但是，有很多时...

    C++语言到底是不是C语言的超集之一

    C与C++两个关系亲密的编程语言，它们本质上是两中语言，只是C++语言设计时要求尽可能的兼容C语言特性，因此C语言中99%以上的功能都可以使用C++完成。本文探讨那些存在于C语言中的特性，但是在C++...

    在C++中，如何避免出现Bug?

    C++中的主要问题之一是存在大量行为未定义或对程序员来说意外的构造。我们在使用静态分析器检查各种项目时经常会遇到这些问题。但正如我们所知，最佳做法是在编译阶段尽早检测错误。让我们来看看现代C++中的一...

    ESL-通过事件控制FreeSWITCH

    通过事件提供的最底层控制机制，允许我们有效地利用工具箱，适时选择使用其中的单个工具。FreeSWITCH是一个核心交换与混合矩阵，它周围有几十个模块提供各种功能特性。我们完全控制了所有的即时信息，这些...

    物理老师教你学C++语言(中篇)

    一、条件语句与实验判断...

    C语言入门指南

    当然！以下是关于C语言入门编程的基础介绍和入门建议，希望能帮你顺利起步：C语言入门指南...

    C++选择结构，让程序自动进行决策

    什么是选择结构？正常的程序都是从上至下顺序执行，这就是顺序结构...

    C++特性使用建议

    1.引用参数使用引用替代指针且所有不变的引用参数必须加上const。在C语言中，如果函数需要修改变量的值，参数必须为指针，如...

    C++程序员学习Zig指南(中篇)

    1.复合数据类型结构体与方法的对比C++类：...

    研一自学C++啃得动吗?

    研一自学C++啃得动吗?在开始前我有一些资料，是我根据网友给的问题精心整理了一份「C++的资料从专业入门到高级教程」，点个关注在评论区回复“888”之后私信回复“888”，全部无偿共享给大家！！！个人...

    C++关键字介绍

    下表列出了C++中的常用关键字，这些关键字不能作为变量名或其他标识符名称。1、autoC++11的auto用于表示变量的自动类型推断。即在声明变量的时候，根据变量初始值的类型自动为此变量选择匹配的...

    一周热门
    最近发表
    标签列表
    Copyright Your WebSite.Some Rights Reserved.