MySQL分区表的使用

2024-11-3 23:03| 发布者: 284cc| 查看: 138| 评论: 0

摘要: 目录创建分区删除分区分区性能总结阐明：分区表，顾名思义，就是一张表根据规则，分别多个区，通过分区，实现一种“逻辑隔离”，这在Saas体系中是非经常见的。本文先容如何在MySQL中分区。创建分区在MyS

创建分区

在MySQL中，多种分区模式，如下：

Range：范围分区，根据数据库表某列数值分别，像日期、数值范例的主键值；
List：列表分区，可选定一个聚集，像group_name字段，有淘宝、天猫，按照差别的团体名分区；
Hash：哈希分区；
Key：键分区；
Subpartitioning：复合分区；

这里先容前面两种常见的分区，Range、List。

起首，创建两张表，用户表、团体表，如下：

[code]CREATE TABLE `tb_user` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `password` varchar(20) DEFAULT NULL, `create_date` datetime NOT NULL, PRIMARY KEY (`id`, `create_date`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT ='用户表，range分区'; [/code] [code]CREATE TABLE `tb_group` ( `id` int NOT NULL AUTO_INCREMENT, `group_name` varchar(20) DEFAULT NULL, `group_code` varchar(20) NOT NULL COMMENT '团体编码', PRIMARY KEY (`id`,`group_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='团体表，list分区'; [/code]

必要留意

创建分区表依赖的字段必要是主键大概联合主键的其中一个
而且，在创建分区后，依赖分区的字段不能修改名称；

给用户表创建分区，根据创建时间（create_date）字段，如下：

[code]-- 创建分区 alter table `tb_user` partition by range columns(create_date) ( partition tb_user_1735660800000 values less than ('2025-01-01 00:00:00'), partition tb_user_1767196800000 values less than ('2026-01-01 00:00:00')); [/code]

这表现，创建时间在2025年内的记录为一个分区，大于2025年，小于2026年的在第二个分区。使用Range分区必要留意以下几点：

Range分区，严格遵照递增分区，背面分区的less than 不能小于上一个分区；
Range分区，数据会落在符合条件的第一个分区，如2024年的数据，会落到小于2025年的分区里，而不会落在小于2026年的分区；

创建乐成，插入两条数据到用户表里；

[code]insert into tb_user(username, password, create_date) values ('张三', '123456', now()), ('李四', 'abcdef', '2025-09-22 14:05:45') [/code]

敲下面的SQL，看下分区环境：

[code]select partition_ordinal_position, partition_method, partition_expression, partition_description, table_name, table_rows from information_schema.partitions where table_name = 'tb_user'; [/code]

可以看到tb_user有两个分区，分区的字段，数值，以及背面两个分区各有一条记录，阐明上面插入的两条记录分到了两个分区里。

（注：下面展示的是所有数据库的tb_user表的分区环境，其他数据库有重名的表，没有重名的话，应该只有两条记录）

在这里插入图片描述

再试下，List分区，这次给tb_group创建一个分区，如下：

[code]-- 创建一个分区 alter table `tb_group` partition by list columns(group_code) ( partition tb_group_001 values in ('001')); [/code]

表现，当记录的团体编码是001时，为一个分区，下面再添加一个002分区；

[code]-- 添加一个分区 alter table tb_group add partition (partition tb_group_002 values in ('002')); [/code]

必要留意

创建分区和新增分区的SQL是不相同的；
而新增分区的前提，是这张表必要是一张分区表；

插入数据之前，先看一下分区环境，两个分区，都没有记录；

在这里插入图片描述

插入数据

[code]insert into tb_group(group_name, group_code) values ('总公司', '001'), ('分公司', '002'); [/code]

再看下分区环境，可以看到两条数据被分到了差别分区；

在这里插入图片描述

假如我们插入一条数据，团体编码是003，即不在任何一个分区里面，会怎么样，如下：

在这里插入图片描述

会报错，以是必要留意

没有符合条件的分区，数据会插入失败

删除分区

删除某张表的分区，用下面的SQL

[code]alter table tb_group drop partition tb_group_001; [/code]

表现，删除tb_group表的[code]tb_group_001[/code]分区，必要留意，

删除分区后，所处分区的数据也会被删除
别的，不能删除表的所有分区，大概仅剩的一个分区

综合前面的留意点，假如根据某个字段创建分区，后续发现计划不合理，想再修改字段范例大概首个分区的范围，只好删表重修了，以是分区前要考虑清楚。

分区性能

这里创建一张有100万条记录的表，表结构如下

[code]CREATE TABLE `test_user_1`( id INT auto_increment primary key , username VARCHAR(32), `password` VARCHAR(32), sex VARCHAR(6) ); [/code]

用下面这个存储过程，创建一百万条记录

[code]-- 创建存储过程 DELIMITER $$ CREATE PROCEDURE auto_insert() BEGIN DECLARE i INT DEFAULT 1; START TRANSACTION; WHILE(i<1000000)DO INSERT INTO `test_user_1`(username, password, sex) VALUES(CONCAT('zhangsan',i) ,MD5(i), 'male'); SET i=i+1; END WHILE; COMMIT; END$$ DELIMITER ; -- 调用 CALL auto_insert(); [/code]

在这里插入图片描述