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

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

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

分区依据

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进阶五之自动读写分离mysql-proxy

    自动读写分离目前，大量现网用户的业务场景中存在读多写少、业务负载无法预测等情况，在有大量读请求的应用场景下，单个实例可能无法承受读取压力，甚至会对业务产生影响。为了实现读取能力的弹性扩展，分担数据库压...

    Postgres vs MySQL_vs2022连接mysql数据库

    ...

    3分钟短文 | Laravel SQL筛选两个日期之间的记录，怎么写?

    引言今天说一个细分的需求，在模型中，或者使用laravel提供的EloquentORM功能，构造查询语句时，返回位于两个指定的日期之间的条目。应该怎么写？本文通过几个例子，为大家梳理一下。学习时...

    一文由浅入深带你完全掌握MySQL的锁机制原理与应用

    本文将跟大家聊聊InnoDB的锁。本文比较长，包括一条SQL是如何加锁的，一些加锁规则、如何分析和解决死锁问题等内容，建议耐心读完，肯定对大家有帮助的。为什么需要加锁呢？...

    验证Mysql中联合索引的最左匹配原则

    后端面试中一定是必问mysql的，在以往的面试中好几个面试官都反馈我Mysql基础不行，今天来着重复习一下自己的弱点知识。在Mysql调优中索引优化又是非常重要的方法，不管公司的大小只要后端项目中用到...

    MySQL索引解析(联合索引/最左前缀/覆盖索引/索引下推)

    目录1.索引基础...

    你会看 MySQL 的执行计划(EXPLAIN)吗?

    SQL执行太慢怎么办？我们通常会使用EXPLAIN命令来查看SQL的执行计划，然后根据执行计划找出问题所在并进行优化。用法简介...

    MySQL 从入门到精通(四)之索引结构

    索引概述索引(index)，是帮助MySQL高效获取数据的数据结构(有序)，在数据之外，数据库系统还维护者满足特定查询算法的数据结构，这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构...

    mysql总结——面试中最常问到的知识点

    mysql作为开源数据库中的榜一大哥，一直是面试官们考察的重中之重。今天，我们来总结一下mysql的知识点，供大家复习参照，看完这些知识点，再加上一些边角细节，基本上能够应付大多mysql相关面试了（...

    mysql总结——面试中最常问到的知识点(2)

    首先我们回顾一下上篇内容，主要复习了索引，事务，锁，以及SQL优化的工具。本篇文章接着写后面的内容。性能优化索引优化，SQL中索引的相关优化主要有以下几个方面：最好是全匹配。如果是联合索引的话，遵循最...

    MySQL基础全知全解!超详细无废话!轻松上手~

    本期内容提醒：全篇2300+字,篇幅较长，可搭配饭菜一同“食”用，全篇无废话(除了这句)，干货满满，可收藏供后期反复观看。注：MySQL中语法不区分大小写，本篇中...

    深入剖析 MySQL 中的锁机制原理_mysql 锁详解

    在互联网软件开发领域，MySQL作为一款广泛应用的关系型数据库管理系统，其锁机制在保障数据一致性和实现并发控制方面扮演着举足轻重的角色。对于互联网软件开发人员而言，深入理解MySQL的锁机制原理...

    Java 与 MySQL 性能优化:MySQL分区表设计与性能优化全解析

    引言在数据库管理领域，随着数据量的不断增长，如何高效地管理和操作数据成为了一个关键问题。MySQL分区表作为一种有效的数据管理技术，能够将大型表划分为多个更小、更易管理的分区，从而提升数据库的性能和可...

    MySQL基础篇:DQL数据查询操作_mysql 查

    一、基础查询DQL基础查询语法SELECT字段列表FROM表名列表WHERE条件列表GROUPBY分组字段列表HAVING分组后条件列表ORDERBY排序字段列表LIMIT...

    MySql:索引的基本使用_mysql索引的使用和原理

    一、索引基础概念1.什么是索引？索引是数据库表的特殊数据结构（通常是B+树），用于...

    一周热门
    最近发表
    标签列表
    赣ICP备2024040476号-12