mysql数据库表分区设计实现

---

前言

数据库单表数据量不断增大，设计表分区来提高数据库的查询效率。

一、什么是mysql表分区？

mysql表分区是将一个大表拆分成更小的，可管理的部分的技术，通过将数据分布在多个分区中，可以提高查询性能，简化备份和恢复操作，并允许更有效的处理大量数据。
表分区可以根据不同的条件进行分割，例如按范围、列表、哈希或者键值分区。每个分区都类似于独立的子表，具有自己的存储结构和索引，这意味着可以根据查询的条件搜索特定分区，而不必要扫描政整个表。
使用表分区的好处:
1、查询性能提升：分区可以使得查询只针对特定的分区进行，从而减少了需要扫描的数据量，提高了查询效率。
2、管理简化：可以更加轻松的管理较小的分区，包括备份、恢复、数据加载和删除等操作，此外，还可以更方便地执行维护操作，如优化索引，重建分区等
3、数据保护和容错：通过将数据分散在多个分区中，即使某个分区发生故障，也可以保留其它分区数据的完整性和可用性
4、存储效率提升：可以将不同分区设置为不同的存储介质，例如将历史数据存储在较慢的磁盘上，而将当前数据存储在更快的固态驱动器上，从而提高存储效率
需要注意的是，表分区功能在MySQL的某些版本和存储引擎中有一些限制和差异，因此在使用表分区之前，请确保你的MySQL版本和存储引擎支持所需的分区功能。

二、表分区有哪几种方案? 如何实现

2.1 范围分区（Range Partitioning）

适合场景：

1、创建订单表

CREATE TABLE order_payments (
    payment_id INT PRIMARY KEY, #支付编号
    order_id INT, #订单编号
    payment_date DATETIME, #支付日期
    payment_amount DECIMAL(10, 2), #支付金额
    payment_method VARCHAR(50), #支付方式
    card_number VARCHAR(50), #信用卡号码
    card_expiry_date DATETIME, #信用卡到期日期
    payment_status VARCHAR(20) #支付状态
);

执行结果如下：

2、添加订单的索引为联合索引（需要修改为索引联合索引，不然后面会报错）

ALTER TABLE `order_payments` DROP PRIMARY KEY,ADD PRIMARY KEY (payment_id, order_id,payment_date);

执行结果如下：

3、按照订单时间范围来分区

ALTER TABLE order_payments
PARTITION BY RANGE(TO_SECONDS(payment_date))
(
  PARTITION p2023q1 VALUES LESS THAN (TO_SECONDS('2023-04-01 00:00:00')),
  PARTITION p2023q2 VALUES LESS THAN (TO_SECONDS('2023-07-01 00:00:00')),
  PARTITION p2023q3 VALUES LESS THAN (TO_SECONDS('2023-10-01 00:00:00')),
  PARTITION p2023q4 VALUES LESS THAN (TO_SECONDS('2024-01-01 00:00:00')),
  PARTITION p2024q1 VALUES LESS THAN (TO_SECONDS('2024-04-01 00:00:00')),
  PARTITION p2024q2 VALUES LESS THAN (TO_SECONDS('2024-07-01 00:00:00')),
  PARTITION p2024q3 VALUES LESS THAN (TO_SECONDS('2024-10-01 00:00:00')),
  PARTITION p2024q4 VALUES LESS THAN (TO_SECONDS('2025-01-01 00:00:00'))
);

执行结果如下：

4、向数据库插入数据(创建存储过程，循环插入2023到2025年的数据，数据时间间隔为半个小时1次)

备注：这一步也可以自行造2023年到2024年的数据测试

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_data`()
BEGIN
    DECLARE start_date DATETIME DEFAULT '2023-01-01 00:00:00';
    DECLARE end_date DATETIME DEFAULT '2025-01-01 00:00:00';
    DECLARE order_id INT DEFAULT 1;
    DECLARE payment_id INT;
    DECLARE payment_date DATETIME;

    SET payment_id = (order_id * 10) + 1;

    WHILE order_id <= 10000 DO
        SET payment_date = start_date;

        WHILE payment_date < end_date DO
            INSERT INTO test.order_payments (payment_id, order_id, payment_date, payment_amount, payment_method, card_number, card_expiry_date, payment_status)
            VALUES (payment_id, order_id, payment_date, RAND() * 1000, 'Credit Card', CONCAT('**** **** **** ', RIGHT(CAST(FLOOR(RAND() * POWER(10, 16)) AS CHAR), 4)), DATE_ADD(payment_date, INTERVAL 30 MINUTE), 'Paid');

            SET payment_id = payment_id + 1;
            SET payment_date = DATE_ADD(payment_date, INTERVAL 30 MINUTE);
        END WHILE;

        SET order_id = order_id + 1;
    END WHILE;
END

执行结果如下：

注意存储过程需要赋予权限，不然执行不了

最后执行存储过程执行一段时间，然后点击停止。

最后数据库数据如下图

5、查询分区数据插入情况

 SELECT PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'order_payments';

这里就是每一个分区的数据量
![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/e337fb951804428a8e861e664bf060a1.png

6、根据分区查询数据

#只查询2023年三季度的数据

select * from order_payments partition(p2023q3)

如下图

2.2 列表分区 （List Partitioning）

适合场景：

在这里插入代码片

2.3 哈希分区 （hash Partinioning）

适合场景：

在这里插入代码片

2.4 键值分区 （Key Partitioning）

适合场景：

在这里插入代码片

2.5 列分区（Column Partitioning）

适合场景：

在这里插入代码片

2.6 轮换分区（Round-Robin Partitioning）

适合场景：

在这里插入代码片

2.7 复合分区（Composite Partitioning）

适合场景：

在这里插入代码片

2.8 子分区（Subpartitioning）

适合场景：

在这里插入代码片

三 总结