MySQL分区表的使用

太上点评

说明：分区表，顾名思义，就是一张表根据规则，划分多个区，通过分区，实现一种“逻辑隔离”，这在Saas系统中是非常常见的。本文介绍如何在MySQL中分区。

创建分区

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

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

这里介绍前面两种常见的分区，Range、List。
首先，创建两张表，用户表、集团表，如下：

1. CREATE TABLE `tb_user`
   
2. (
   
3.     `id`          int      NOT NULL AUTO_INCREMENT,
   
4.     `username`    varchar(20) DEFAULT NULL,
   
5.     `password`    varchar(20) DEFAULT NULL,
   
6.     `create_date` datetime NOT NULL,
   
7.     PRIMARY KEY (`id`, `create_date`)
   
8. ) ENGINE = InnoDB
   
9.   DEFAULT CHARSET = utf8mb4
   
10.   COLLATE = utf8mb4_general_ci COMMENT ='用户表，range分区';

复制代码

1. CREATE TABLE `tb_group` (
   
2.   `id` int NOT NULL AUTO_INCREMENT,
   
3.   `group_name` varchar(20) DEFAULT NULL,
   
4.   `group_code` varchar(20) NOT NULL COMMENT '集团编码',
   
5.   PRIMARY KEY (`id`,`group_code`)
   
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='集团表，list分区';

复制代码

需要注意

• 创建分区表依靠的字段需要是主键或者联合主键的其中一个
  
• 而且，在创建分区后，依靠分区的字段不能修改名称；
  

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

1. -- 创建分区
   
2. alter table `tb_user`
   
3.     partition by range columns(create_date) (
   
4.         partition tb_user_1735660800000 values less than ('2025-01-01 00:00:00'),
   
5.         partition tb_user_1767196800000 values less than ('2026-01-01 00:00:00'));

复制代码

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

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

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

1. insert into tb_user(username, password, create_date)
   
2. values ('张三', '123456', now()),
   
3.        ('李四', 'abcdef', '2025-09-22 14:05:45')

复制代码

敲下面的SQL，看下分区情况：

1. select partition_ordinal_position,
   
2.        partition_method,
   
3.        partition_expression,
   
4.        partition_description,
   
5.        table_name,
   
6.        table_rows
   
7. from information_schema.partitions
   
8. where table_name = 'tb_user';

复制代码

可以看到tb_user有两个分区，分区的字段，数值，以及后面两个分区各有一条记录，说明上面插入的两条记录分到了两个分区里。
（注：下面展示的是所有数据库的tb_user表的分区情况，其他数据库有重名的表，没有重名的话，应该只有两条记录）

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

1. -- 创建一个分区
   
2. alter table `tb_group`
   
3.     partition by list columns(group_code) (
   
4.         partition tb_group_001 values in ('001'));

复制代码

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

1. -- 添加一个分区
   
2. alter table tb_group
   
3.     add partition (partition tb_group_002 values in ('002'));

复制代码

需要注意

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

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

插入数据

1. insert into tb_group(group_name, group_code)
   
2. values ('总公司', '001'),
   
3.        ('分公司', '002');

复制代码

再看下分区情况，可以看到两条数据被分到了不同分区；

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

会报错，所以需要注意

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

删除分区

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

1. alter table tb_group drop partition tb_group_001;

复制代码

表示，删除tb_group表的

1. tb_group_001

复制代码

分区，需要注意，

• 删除分区后，所处分区的数据也会被删除
  
• 另外，不能删除表的所有分区，或者仅剩的一个分区
  

综合前面的注意点，如果根据某个字段创建分区，后续发现设计不合理，想再修改字段类型或者首个分区的范围，只好删表重建了，所以分区前要考虑清楚。

分区性能

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

1. CREATE TABLE `test_user_1`(
   
2.         id INT auto_increment primary key ,
   
3.         username VARCHAR(32),
   
4.         `password` VARCHAR(32),
   
5.         sex VARCHAR(6)
   
6. );

复制代码

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

1. -- 创建存储过程
   
2. DELIMITER $$
   
3. CREATE PROCEDURE auto_insert()
   
4. BEGIN
   
5.     DECLARE i INT DEFAULT 1;
   
6.         START TRANSACTION;
   
7.     WHILE(i<1000000)DO
   
8.         INSERT INTO `test_user_1`(username, password, sex) VALUES(CONCAT('zhangsan',i) ,MD5(i), 'male');
   
9.         SET i=i+1;
   
10.     END WHILE;
    
11.         COMMIT;
    
12. END$$
    
13. DELIMITER ;
    
14. 
    
15. -- 调用
    
16. CALL auto_insert();

复制代码

划分为10个分区，每个分区存10万条

现在，来查询一条记录

1. select id, username, password, sex from test_user_1 where username='zhangsan500025';

复制代码

1秒没到

现在，删除表重建，这次不建分区，再查一次，如下：
（没有分区）

（1秒多点）

老实说，我也不知道建立分区对查询有没有优化，好像是有点……大家可以创建一千万条记录试下

总结

本文介绍了MySQL分区，及创建分区时的一些注意点，汇总如下：

• 创建分区表依靠的字段需要是主键或者联合主键中的一个；
  
• 创建分区后，依靠分区的字段不能修改名称；
  
• RANGE分区，严格遵循递增分区，后面分区的less than 不能小于上一个分区；
  
• RANGE分区，数据会落在符合条件的第一个分区；
  
• 创建分区和新增分区的SQL不同，新增分区的前提，是这张表需要是一张分区表；
  
• 没有符合条件的分区，数据会插入失败
  
• 删除分区后，所处分区的数据也会被删除
  
• 不能删除表的所有分区，或者仅剩的一个分区
  

到此这篇关于MySQL分区表的使用的文章就介绍到这了,更多相关MySQL分区表内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/328718skg.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！