mysql千万大表分区耗时 mysql8 表分区

转载

huatechinfo 2023-07-28 15:02:14

文章标签 mysql千万大表分区耗时数据库 mysql java python 文章分类 MySQL 数据库

本文基于Mysql for windows 8.0.12版本

分表与表分区的区别：

分表

直接按照预定的规则，把表拆分。业务操作时需要计算完整的表名。

分区

MySQL从5.1开始支持表的水平分区的。分区是将一个表或索引分解成多个更小，更可管理的部分。每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。对于开发人员来说，这是无感知的，不需要关心数据库到底如何操作的。在ORM、Model中操作时比较方便。

mysql本身支持的主要的分区方式

有很多中间件，比如mycat, 支持分区, 分表, 读写分离, 故障转移等功能。

MySQL支持HASH分区、RANGE分区、LIST分区、KEY分区。

Hash分区：对用户定义的表达式所返回的值来进行分区。可以写partitions (分区数目)，或直接使用分区语句,比如partition p0 values in…..。
Range分区：是对一个连续性的行值，按范围进行分区；比如：id小于100；id大于100小于200；
List分区：跟range分区类似，不过它存放的是一个离散值的集合。
Key分区：与hash分区类似，只不过分区支持一列或多列，并且MySQL服务器自身提供hash函数。

如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。

HASH分区

MYSQL支持两种HASH分区，常规HASH(HASH)和线性HASH(LINEAR HASH) 。

常规HASH

常规hash是基于分区个数的取模（%）运算。根据余数插入到指定的分区。

CREATE TABLE orders2(
    order_id int unsigned NOT NULL AUTO_INCREMENT,
    shop_id int unsigned NOT NULL,
    amount decimal(10,2) NOT NULL,
    primary key(order_id, shop_id) -- 主键必须包含分区键
) engine=InnoDB PARTITION BY HASH(shop_id) PARTITIONS 10;

这个表中我们按店铺id对10取模来规划分区。

填充模拟数据:

-- 填充模拟数据
DROP PROCEDURE IF EXISTS orders2_fill;
DELIMITER $$
CREATE PROCEDURE orders2_fill(in shop_id_from int, in count int)
BEGIN
    DECLARE i int DEFAULT 0;
    DECLARE shop_id1 int;
    DECLARE shop_id2 int;
    DECLARE shop_id3 int;
    DECLARE shop_id4 int;
    DECLARE shop_id5 int;
    DECLARE amount1 decimal(10,2);
    DECLARE amount2 decimal(10,2);
    DECLARE amount3 decimal(10,2);
    DECLARE amount4 decimal(10,2);
    DECLARE amount5 decimal(10,2);
    WHILE i < count DO
        SET shop_id1 = shop_id_from + i * 5 + 0;
        SET shop_id2 = shop_id_from + i * 5 + 1;
        SET shop_id3 = shop_id_from + i * 5 + 2;
        SET shop_id4 = shop_id_from + i * 5 + 3;
        SET shop_id5 = shop_id_from + i * 5 + 4;
        SET amount1 = 1000 + Round(rand() * 10000,2);
        SET amount2 = 1100 + Round(rand() * 10000,2);
        SET amount3 = 1200 + Round(rand() * 10000,2);
        SET amount4 = 1300 + Round(rand() * 10000,2);
        SET amount5 = 1400 + Round(rand() * 10000,2);
        INSERT INTO orders2(shop_id, amount)VALUES
        (shop_id1,amount1),(shop_id2,amount2),(shop_id3,amount3),(shop_id4,amount4),(shop_id5,amount5);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
CALL orders2_fill(1, 1000); # 1-5000

查看分区情况:

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM `information_schema`.`PARTITIONS`
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='orders2';

可以看到上面插入的数据比较均匀的分布在各个partition中。

也支持根据公式计算分区规则

CREATE TABLE `orders` (
    `id` INT NOT NULL,
    `order_date` DATE NOT NULL DEFAULT '1970-01-01' COMMENT '下单日期',
)
PARTITION BY HASH( YEAR(order_date) )
PARTITIONS 10;

查看sql查询使用的分区

EXPLAIN SELECT * FROM orders2 WHERE shop_id=201;

msyql8中移除了 EXTENDED, PARTITIONS 关键字, 不需要再写: EXPLAIN PARTITIONS

线性HASH(LINEAR HASH)

LINEAR HASH和HASH的唯一区别就是PARTITION BY LINEAR HASH.

CREATE TABLE orders3 (
   id INT NOT NULL AUTO_INCREMENT,
   order_date DATE NOT NULL DEFAULT '2020-01-01' COMMENT '下单日期',
   PRIMAEY KEY(id, order_date)
) engine=InnoDB PARTITION BY LINEAR HASH( YEAR(order_date) ) PARTITIONS 6;

线性HASH的计算原理比较复杂, 这里忽略.

分区管理

常规hash的分区非常的简便，通过取模的方式可以让数据非常平均的分布每一个分区，但是由于分区在创建表的时候已经固定了。如果新增或者收缩分区的数据迁移比较大。

常规HASH和线性HASH的增加收缩分区的原理是一样的。增加和收缩分区后原来的数据会根据现有的分区数量重新分布。HASH分区不能删除分区，所以不能使用DROP PARTITION操作进行分区删除操作；

只能通过ALTER TABLE ... COALESCE PARTITION num来合并分区，这里的num是减去的分区数量；

可以通过ALTER TABLE ... ADD PARTITION PARTITIONS num来增加分区，这里是null是在原先基础上再增加的分区数量。

CREATE TABLE orders3(
    id int NOT NULL AUTO_INCREMENT,
    shop_id int NOT NULL,
    primary key(id, shop_id)
) engine=InnoDB PARTITION BY HASH(shop_id) PARTITIONS 5;
INSERT INTO orders3(shop_id)
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19);
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM `information_schema`.`PARTITIONS`
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='orders3';

mysql千万大表分区耗时 mysql8 表分区_python

新增分区: 我们给表orders3增加4个分区

ALTER TABLE orders3 add PARTITION partitions 4;

因为牵涉到数据重新分区, 执行会比较耗时
调整分区后, 数据都还在, 但是查看分区表信息没有看到统计数据

mysql千万大表分区耗时 mysql8 表分区_数据库_02

mysql千万大表分区耗时 mysql8 表分区_python_03

INSERT INTO orders3(shop_id)VALUES(20),(21);

插入2笔数据后

mysql千万大表分区耗时 mysql8 表分区_mysql_04

目前测试的windows版本的mysql(8.0.12)是有这个统计数据更新的bug

缩减分区

我们再给表orders3减少3个分区, 使用 COALESCE:

ALTER TABLE orders3 COALESCE PARTITION 3;

mysql千万大表分区耗时 mysql8 表分区_数据库_05

移除表的分区

ALTER TABLE orders3 REMOVE PARTITIONING;

注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

移除分区后, 分区文件会被移除, 合并到一个文件里面去.

mysql千万大表分区耗时 mysql8 表分区_数据库_06

RANGE 分区

RANGE分区模式下, 行数据基于属于一个给定的连续区间的列值被放入分区。

当插入的数据不在一个分区中定义的值的时候，会抛出异常。

RANGE分区主要用于日期列的分区，比如交易表，销售表等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型，那么只用存yyyyMM就好了。也不用关心函数了。

CREATE TABLE `orders` (
    `order_id` INT NOT NULL AUTO_INCREMENT COMMENT '订单号',
    `order_month` INT NOT NULL COMMENT '下单月份, 格式:yyyymm',
    `amount` DECIMAL(10,2) NULL,
    PRIMARY KEY (`order_id` , `order_month`)
) engine = InnoDB 
PARTITION BY RANGE (order_month) PARTITIONS 6 (
    PARTITION part0 VALUES LESS THAN (202001) , 
    PARTITION part1 VALUES LESS THAN (202002) , 
    PARTITION part2 VALUES LESS THAN (202003) , 
    PARTITION part3 VALUES LESS THAN (202004) , 
    PARTITION part4 VALUES LESS THAN (202005),
    PARTITION part5 VALUES LESS THAN (202006)
);

插入数据:

INSERT INTO `orders`(order_month, amount)
VALUES(202001, 500+round(rand()*1000,0)),(202001, 500+round(rand()*1000,0)),
(202002, 500+round(rand()*1000,0)),(202002, 500+round(rand()*1000,0)),
(202003, 500+round(rand()*1000,0)),(202003, 500+round(rand()*1000,0)),
(202004, 500+round(rand()*1000,0)),(202004, 500+round(rand()*1000,0)),
(202005, 500+round(rand()*1000,0)),(202005, 500+round(rand()*1000,0));

上面的数据顺利插入.

插入数据时注意: 如果插入了分区未包含的下单日期, 比如 202006, 则会报错: "Error Code: 1526. Table has no partition for value 202006", 因为分区定义中的"LESS THEN 2006" 并不包含 202006。这种情况下，如果我们不知道上限是多少, 可以在最后添加一个 MAXVALUE 分区，如下：

ALTER TABLE `orders` ADD PARTITION (PARTITION p_max VALUES LESS THAN MAXVALUE);

分区操作

增加分区

我们现在要增加一个202006月份的数据分区, 按如下操作即可:

ALTER TABLE `orders` ADD PARTITION (PARTITION p6 VALUES LESS THAN (202006));

这种range的分区方式，不会像前面一部分的HASH分区那样需要进行数据的移动。我们只要这样简单的增加一个分区，即可实现即时扩展来满足业务需求。

删除分区

注意: 使用 drop删除分区, 会直接删除分区对应的数据库文件, 相关的数据也会丢失, 必须慎重操作。
可能对于在转移旧数据后, 删除旧数据比较适用, 速度比delete from table快太多了。

alter table employees drop  PARTITION p4;

拆分合并分区

拆分与合并, 都使用 REORGANIZE PARTION p_name INTO () .

我们准备把202001之前的数据拆分成2018和2019两个年份, 先插入这2个年份的数据:

INSERT INTO `orders`(order_month, amount)
VALUES(201801, 500+round(rand()*1000,0)),(201802, 500+round(rand()*1000,0)),
(201901, 500+round(rand()*1000,0)),(201902, 500+round(rand()*1000,0))

mysql千万大表分区耗时 mysql8 表分区_java_07

我可以看到数据都在p0分区下. 现在我来拆分p0:

ALTER TABLE orders REORGANIZE PARTITION part0 INTO (
    PARTITION p2018 VALUES LESS THAN (201901),
    PARTITION p2019 VALUES LESS THAN (202001)
);

mysql千万大表分区耗时 mysql8 表分区_python_08

我可以看到分区p0已经不存在了.

现在来看下数据查询:

mysql千万大表分区耗时 mysql8 表分区_java_09

那我们现在再把p2018和p2019合并为p0:

ALTER TABLE orders REORGANIZE PARTITION p2018,p2019 INTO (
    PARTITION p0 VALUES LESS THAN (202001)
);

mysql千万大表分区耗时 mysql8 表分区_java_10

查询结果证明已经合并成功.

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java 迭代list Java 迭代器逆序

下一篇：sql server数据库定义字段的大小 sql 定义字段类型

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯