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

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

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

分区依据

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

    相关推荐

    Python自动化脚本应用与示例（python办公自动化脚本）

    Python是编写自动化脚本的绝佳选择，因其语法简洁、库丰富且跨平台兼容性强。以下是Python自动化脚本的常见应用场景及示例，帮助你快速上手：一、常见自动化场景文件与目录操作...

    Python文件操作常用库高级应用教程

    本文是在前面《Python文件操作常用库使用教程》的基础上，进一步学习Python文件操作库的高级应用。一、高级文件系统监控1.1watchdog库-实时文件系统监控安装与基本使用：...

    Python办公自动化系列篇之六:文件系统与操作系统任务

    作为高效办公自动化领域的主流编程语言，Python凭借其优雅的语法结构、完善的技术生态及成熟的第三方工具库集合，已成为企业数字化转型过程中提升运营效率的理想选择。该语言在结构化数据处理、自动化文档生成...

    14《Python 办公自动化教程》os 模块操作文件与文件夹

    在日常工作中，我们经常会和文件、文件夹打交道，比如将服务器上指定目录下文件进行归档，或将爬虫爬取的数据根据时间创建对应的文件夹/文件，如果这些还依靠手动来进行操作，无疑是费时费力的，这时候Pyt...

    python中os模块详解（python os.path模块）

    os模块是Python标准库中的一个模块，它提供了与操作系统交互的方法。使用os模块可以方便地执行许多常见的系统任务，如文件和目录操作、进程管理、环境变量管理等。下面是os模块中一些常用的函数和方法：...

    21-Python-文件操作（python文件的操作步骤）

    在Python中，文件操作是非常重要的一部分，它允许我们读取、写入和修改文件。下面将详细讲解Python文件操作的各个方面，并给出相应的示例。1-打开文件...

    轻松玩转Python文件操作:移动、删除

    哈喽，大家好，我是木头左！Python文件操作基础在处理计算机文件时，经常需要执行如移动和删除等基本操作。Python提供了一些内置的库来帮助完成这些任务，其中最常用的就是os模块和shutil模块。...

    Python 初学者练习:删除文件和文件夹

    在本教程中，你将学习如何在Python中删除文件和文件夹。使用os.remove()函数删除文件...

    引人遐想，用 Python 获取你想要的“某个人”摄像头照片

    仅用来学习，希望给你们有提供到学习上的作用。1.安装库需要安装python3.5以上版本，在官网下载即可。然后安装库opencv-python，安装方式为打开终端输入命令行。...

    Python如何使用临时文件和目录（python目录下文件）

    在某些项目中，有时候会有大量的临时数据，比如各种日志，这时候我们要做数据分析，并把最后的结果储存起来，这些大量的临时数据如果常驻内存，将消耗大量内存资源，我们可以使用临时文件，存储这些临时数据。使用标...

    Linux 下海量文件删除方法效率对比，最慢的竟然是 rm

    Linux下海量文件删除方法效率对比，本次参赛选手一共6位，分别是：rm、find、findwithdelete、rsync、Python、Perl.首先建立50万个文件$testfor...

    Python 开发工程师必会的 5 个系统命令操作库

    当我们需要编写自动化脚本、部署工具、监控程序时，熟练操作系统命令几乎是必备技能。今天就来聊聊我在实际项目中高频使用的5个系统命令操作库，这些可都是能让你效率翻倍的"瑞士军刀"。一...

    Python常用文件操作库使用详解（python文件操作选项）

    Python生态系统提供了丰富的文件操作库，可以处理各种复杂的文件操作需求。本教程将介绍Python中最常用的文件操作库及其实际应用。一、标准库核心模块1.1os模块-操作系统接口主要功能...

    11. 文件与IO操作（文件io和网络io）

    本章深入探讨Go语言文件处理与IO操作的核心技术，结合高性能实践与安全规范，提供企业级解决方案。11.1文件读写11.1.1基础操作...

    Python os模块的20个应用实例（python中 import os模块用法）

    在Python中，...

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