Mysql如何做表分区

Java极客技术

发布于 2024-07-08 12:22:27

1170

发布于 2024-07-08 12:22:27

文章被收录于专栏：Java极客技术

我们都知道，在Mysql 中，如果数据量过大的话，就有可能在查询过程中会出现各种超时的情况，毕竟如果一个表的数据量过大的时候，一个简单的单表查询都会有点慢，所以，就有了各种中间件的存在，比如说 MyCat，ShardingJDBC 等分库工具，但是今天了不起不说这个，我们来说说这个Mysql自己的分区，我们不做分库操作。

Mysql数据分区

MySQL 的数据分区（Partitioning）是一个数据库功能，允许将一个表物理地分成多个独立的、更小的、更易于管理的片段，这些片段被称为分区。每个分区在逻辑上都是表的一部分，但在物理存储上，每个分区可以有自己的存储引擎、文件、索引等。

为什么要使用分区？

性能：对于某些查询，尤其是范围查询，分区可以显著提高性能，因为 MySQL 可以仅扫描需要的数据分区，而不是整个表。
管理：可以对单独的分区进行备份、删除或检查，这使得管理大型表变得更加容易。
归档：可以将旧数据移动到单独的分区，并轻松地从主表中删除这些分区，从而实现数据的归档。

如何进行分区？

MySQL 支持多种分区方法，包括：

RANGE 分区：基于列值的范围进行分区。
LIST 分区：基于列值的列表进行分区。
HASH 分区：基于用户定义的表达式的返回值的哈希值进行分区。
KEY 分区：类似于 HASH 分区，但 MySQL 服务器提供哈希函数。
COLUMNS 分区：是 RANGE 和 LIST 分区的扩展，允许基于多个列的值进行分区。

我们来详细说说这个分区的区别

RANGE分区

定义：基于属于一个给定连续区间的列值，把多行分配给分区。
用途：非常适合于基于时间范围的数据，如日志、交易记录等。
特点：分区键必须是整数、日期或日期时间类型。分区表必须至少包含一个RANGE分区。每个RANGE分区都定义了一个值的范围，如 PARTITION p0 VALUES LESS THAN (100)。
限制：不支持外键和全文索引。

LIST分区

定义：类似于RANGE分区，但它是基于列值匹配一个离散值集合中的某个值来进行选择。
用途：当数据可以按照某个离散值列表进行分组时，如地域、类别等。
特点：分区键可以是整数或枚举类型。定义时指定一个值列表，如 PARTITION p1 VALUES IN (1, 3, 5)。
限制：与RANGE分区类似，不支持外键和全文索引。

HASH分区

定义：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。
用途：当数据分布需要均匀或随机时，HASH分区可以确保数据在预先确定数目的分区中平均分布。
特点：分区键可以是任何MySQL中的有效表达式，只要它返回非负整数值。可以通过指定分区数量来控制数据的分布。
限制：不支持外键和全文索引。

KEY分区

定义：类似于HASH分区，但KEY分区的哈希函数是由MySQL服务器提供。
用途：与HASH分区类似，但使用MySQL内部的哈希函数。
特点：分区键可以是一列或多列，但所有列都必须是整数类型。 MySQL服务器会处理列的哈希值，并将数据分配到不同的分区。
限制：与HASH分区相同，不支持外键和全文索引。

COLUMNS分区

定义：MySQL 5.5及以上版本支持基于多个列的分区，这被称为COLUMNS分区。
用途：允许根据多列的值进行分区，提供了更大的灵活性。
特点：可以使用多个列作为分区键。支持RANGE和LIST分区。
限制：与上述分区类型类似的限制。

我们来看一下个示例，

假设我们有一个名为 sales 的表，它记录了销售数据，并且我们想要基于 sale_date 列进行 RANGE 分区。

CREATE TABLE sales (  
    sale_id  VARCHAR(100) NOT NULL, 
  sale_name VARCHAR(100) NOT NULL, 
    amount DECIMAL(10, 2) NOT NULL,  
  dsYear VARCHAR(20) NOT NULL
)  
PARTITION BY RANGE COLUMNS(dsYear) (  
    PARTITION p0 VALUES LESS THAN ('2021'),  
    PARTITION p1 VALUES LESS THAN ('2022'),  
    PARTITION p2 VALUES LESS THAN ('2023'),  
    PARTITION p3 VALUES LESS THAN  ('2024')  
);

在这个例子中，我们根据 dsYear 列的年份值将数据分成多个分区。注意 MAXVALUE 的使用，它表示最大的可能的值。

如果我们进行了分区，那么就要在查询中适当的去增加分区查询条件，和分库是一样的，查询的时候需要命中分库规则，这样的话，就不会进行全表的扫描。

然后我们创建一大笔数据，这样去查询一下，我们先来造数据，直接用存储过程来造数据，如果你使用的Navicat16，那你可以使用他自带的造数据的功能。

脚本如下：

CREATE DEFINER=`root`@`%` PROCEDURE `CreateTestData`()
BEGIN
    DECLARE v_counter INT DEFAULT 1;
    WHILE v_counter <= 1000000 DO
        INSERT INTO sales(sale_id, sale_name, amount,dsYear)
        VALUES(UUID_SHORT(), CONCAT('Data', v_counter), v_counter,'2021');
        SET v_counter = v_counter + 1;
    END WHILE;
END

我们这时候就可以通过查询是否命中分区来看一下查询速度了。

不过这种创建数据的方式那可是非常的耗时间的。