MySQL分区表（一）
说明：分区表，顾名思义，就是一张表根据规则，分别多个区，通太过区，实现一种“逻辑隔离”，这在Saas体系中是非常常见的。本文介绍如何在MySQL中分区。创建分区
在MySQL中，多种分区模式，如下：
[*] Range：范围分区，根据数据库表某列数值分别，像日期、数值类型的主键值；
[*] List：列表分区，可选定一个集合，像group_name字段，有淘宝、天猫，按照差别的集团名分区；
[*] Hash：哈希分区；
[*] Key：键分区；
[*] Subpartitioning：子分区；
这里介绍前面两种常见的分区，Range、List。
起首，创建两张表，用户表、集团表，如下：
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分区';
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分区';
必要留意
[*] 创建分区表依靠的字段必要是主键大概联合主键的其中一个
[*] 而且，在创建分区后，依靠分区的字段不能修改名称；
给用户表创建分区，根据创建时间（create_date）字段，如下：
-- 创建分区
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'));
这表现，创建时间在2025年内的记载为一个分区，大于2025年，小于2026年的在第二个分区。使用Range分区必要留意以下几点：
[*] Range分区，严格遵循递增分区，后面分区的less than 不能小于上一个分区；
[*] Range分区，数据会落在符合条件的第一个分区，如2024年的数据，会落到小于2025年的分区里，而不会落在小于2026年的分区；
创建乐成，插入两条数据到用户表里；
insert into tb_user(username, password, create_date)
values ('张三', '123456', now()),
('李四', 'abcdef', '2025-09-22 14:05:45')
敲下面的SQL，看下分区环境：
select partition_ordinal_position,
partition_method,
partition_expression,
partition_description,
table_name,
table_rows
from information_schema.partitions
where table_name = 'tb_user';
可以看到tb_user有两个分区，分区的字段，数值，以及后面两个分区各有一条记载，说明上面插入的两条记载分到了两个分区里。
（注：下面展示的是所有数据库的tb_user表的分区环境，其他数据库有重名的表，没有重名的话，应该只有两条记载）
https://i-blog.csdnimg.cn/direct/09a9583fe654451988cc724b473f2311.png
再试下，List分区，这次给tb_group创建一个分区，如下：
-- 创建一个分区
alter table `tb_group`
partition by list columns(group_code) (
partition tb_group_001 values in ('001'));
表现，当记载的集团编码是001时，为一个分区，下面再添加一个002分区；
-- 添加一个分区
alter table tb_group
add partition (partition tb_group_002 values in ('002'));
必要留意
[*] 创建分区和新增分区的SQL是不相同的；
[*] 而新增分区的前提，是这张表必要是一张分区表；
插入数据之前，先看一下分区环境，两个分区，都没有记载；
https://i-blog.csdnimg.cn/direct/b838c1d207504b2bb44a69d73826bb4c.png
插入数据
insert into tb_group(group_name, group_code)
values ('总公司', '001'),
('分公司', '002');
再看下分区环境，可以看到两条数据被分到了差别分区；
https://i-blog.csdnimg.cn/direct/cee467febe9b44a399daecfc41684709.png
假如我们插入一条数据，集团编码是003，即不在任何一个分区里面，会怎么样，如下：
https://i-blog.csdnimg.cn/direct/44885ac8c84b4270bca3222628a17f70.png
会报错，所以必要留意
[*]没有符合条件的分区，数据会插入失败
删除分区
删除某张表的分区，用下面的SQL
alter table tb_group drop partition tb_group_001;
表现，删除tb_group表的tb_group_001分区，必要留意，
[*] 删除分区后，所处分区的数据也会被删除
[*] 另外，不能删除表的所有分区，大概仅剩的一个分区
综合前面的留意点，假如根据某个字段创建分区，后续发现设计不公道，想再修改字段类型大概首个分区的范围，只好删表重建了，所以分区前要考虑清楚。
分区性能
这里创建一张有100万条记载的表，表布局如下
CREATE TABLE `test_user_1`(
id INT auto_increment primary key ,
username VARCHAR(32),
`password` VARCHAR(32),
sex VARCHAR(6)
);
用下面这个存储过程，创建一百万条记载
-- 创建存储过程
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();
https://i-blog.csdnimg.cn/direct/fa2efd0e56d5454c9de55c832acb2644.png
分别为10个分区，每个分区存10万条
https://i-blog.csdnimg.cn/direct/c97a2f87e9c0482381b81cdbd69566a7.png
现在，来查询一条记载
select id, username, password, sex from test_user_1 where username='zhangsan500025';
1秒没到
https://i-blog.csdnimg.cn/direct/c58ea9626c1f413dbd4f8382b165b6df.png
现在，删除表重建，这次不建分区，再查一次，如下：
（没有分区）
https://i-blog.csdnimg.cn/direct/3d05f60fe02a46fa8e9f5a19e901817e.png
（1秒多点）
https://i-blog.csdnimg.cn/direct/a56096423b6e4079808009466e454824.png
诚实说，我也不知道建立分区对查询有没有优化，似乎是有点……大家可以创建一千万条记载试下
总结
本文介绍了MySQL分区，及创建分区时的一些留意点，汇总如下：
[*] 创建分区表依靠的字段必要是主键大概联合主键中的一个；
[*] 创建分区后，依靠分区的字段不能修改名称；
[*] RANGE分区，严格遵循递增分区，后面分区的less than 不能小于上一个分区；
[*] RANGE分区，数据会落在符合条件的第一个分区；
[*] 创建分区和新增分区的SQL差别，新增分区的前提，是这张表必要是一张分区表；
[*] 没有符合条件的分区，数据会插入失败
[*] 删除分区后，所处分区的数据也会被删除
[*] 不能删除表的所有分区，大概仅剩的一个分区
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]