详细介绍MySQL分区

枯河垂钓

已于 2025-04-29 10:02:21 修改

阅读量356

点赞数 6

分类专栏： # MySQL数据管理策略文章标签： mysql 数据库

于 2025-03-18 23:11:14 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_57024726/article/details/146354847

版权

MySQL数据管理策略专栏收录该内容

1 篇文章

订阅专栏

一、分区优势

优化查询

当表过大的时候，即使增加了索引，查询性能也不是很高，如果有了分区，查询相关数据时，可以只扫码必要的分区，可以让查询更快

方便维护管理

有一些表，我们需要定期删除掉历史的数据，如果采用delete会非常慢，并且有可能会导致从库延迟，我们就可以考虑分区，在删除数据时，针对特定的分区进行删除，而不影响其他的分区，可以提高管理和维护的效率。

单表能存储更多的数据

表分区之后，可以把不同的分区放在不同的磁盘上，这样可以存储更多的数据

二、分区的使用场景

大表的查询性能优化
冷热数据分离
历史数据管理

三、常用分区类型

分区类型	解释	适用场景举例	示例代码
RANGE 分区	基于属于一个给定连续范围的列值来分布数据。表被分割成不同的区域，每个区域有一个范围，数据会根据列值所在的范围被存储到对应的区域中。	按日期范围分区：例如，将销售数据按年份或月份分区，方便查询和管理特定时间段的数据。	CREATE TABLE sales ( id INT NOT NULL, sale_date DATE NOT NULL, amount DECIMAL(10,2) NOT NULL ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2019 VALUES LESS THAN (2020), PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022) );
LIST 分区	基于列值属于一个给定值列表来分布数据。每个分区对应一个列表，只有当列值在该列表中时，数据才会被存储到该分区。	按地区代码分区：例如，将员工数据按所属地区进行分区，便于对特定地区的数据进行管理和查询。	CREATE TABLE employees ( id INT NOT NULL, name VARCHAR(50) NOT NULL, region VARCHAR(20) NOT NULL ) PARTITION BY LIST (region) ( PARTITION north VALUES IN ('NY', 'NJ', 'CT'), PARTITION south VALUES IN ('TX', 'FL', 'GA'), PARTITION west VALUES IN ('CA', 'WA', 'OR') );
HASH 分区	基于用户定义的表达式计算出的哈希值来分布数据。MySQL 会根据哈希值将数据均匀地分布到不同的分区中。	按主键分区：例如，将日志数据按主键进行哈希分区，实现数据的均衡分布和性能优化。	CREATE TABLE logs ( id INT NOT NULL AUTO_INCREMENT, log_date DATE NOT NULL, log_time TIMESTAMP NOT NULL, message TEXT NOT NULL, PRIMARY KEY (id, log_date) ) PARTITION BY HASH (TO_DAYS(log_date)) PARTITIONS 4;
KEY 分区	类似于 HASH 分区，但 MySQL 会根据内部的哈希函数来计算哈希值，而不是用户定义的表达式。KEY 分区可以基于单个列或多列的值进行哈希计算。	按多列组合分区：例如，将客户数据按客户 ID 和注册日期的组合进行键分区，提高数据分布的灵活性和均匀性。	CREATE TABLE customers ( customer_id INT NOT NULL, name VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, registration_date DATE NOT NULL ) PARTITION BY KEY ( customer_id, registration_date ) PARTITIONS 8;
子分区（Subpartitioning）	子分区是对每个分区表的每个分区进行再次分割，也被称为复合分区。目前只有 RANGE 和 LIST 分区的表可以再进行子分区，子分区只能是 HASH 或者 KEY 分区。	数据量巨大时，单一分区可能无法满足性能需求，子分区可以将数据更细致地划分，从而提高查询效率。同时，如果查询经常涉及多个维度（如时间和地区），子分区可以针对这些维度进行分区，优化查询性能。	CREATE TABLE sales ( id INT NOT NULL, sale_date DATE NOT NULL, amount DECIMAL(10,2) NOT NULL ) PARTITION BY RANGE (YEAR(sale_date)) SUBPARTITION BY HASH (TO_DAYS(sale_date)) SUBPARTITIONS 2 ( PARTITION p2019 VALUES LESS THAN (2020), PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022) );