分区表介绍

问题

单表数据量过大，查询的性能通常会变得很低

• 查询时磁盘的IO次数增加：MySQL的InnoDB引擎使用B+树维护索引，一个典型的索引节点大概可以存储60-120个数据记录，一个三层的索引大概可以存储百万条数据。如果订单表增加一个数量级，索引的层数也需要增加，查询订单时磁盘的IO次数也随着增加，影响性能。
• 索引占用存储空间：为了加快查询效率，订单表通常需要创建一些索引。但是，索引也需要占用一定的存储空间，并且会增加每次修改操作的时间成本。另外，当表中数据量过大时，更新操作可能会导致索引重新排序的问题，这些都将极大地降低索引效率和查询效率。

分区表

将一个逻辑表分解成多个分区表，以存储和管理物理数据的技术。每个分区实际上是一个独立的表，包含了与主表相同的列，并有一个特定的分区键，用于确定每行数据所属的分区。

好处：

• 提高查询效率：分区表将数据拆分为多个分区，减少表了的扫描范围，减少了磁盘IO次数，从而提高查询效率。
• 提高数据可用性：数据分布在多个节点，降低数据丢失的风险。如果系统上的一个节点失败，可以通过分区表快速将数据迁移到其他节点上。从而提升可用性。
• 减少维护成本：使用分区表可以提高维护的效率，在维护分区表时，只需要维护每个分区而不是整个表，可以减少人力成本和维护时间。
• 支持大型数据集：使用分区表可以在快速的响应时间内管理海量的数据，增加表的数量不会影响系统性能。

类型：Range分区、Hash分区、List分区和Key分区。

• RANGE分区：基于给定的有序区间将表中数据分成若干段，每一段称为一个分区。RANGE分区表可以使用任何能够进行比较大小的数据类型，如整数、日期等。适用于记录按区百分比分布更均匀的数据表regions这类数据。
• HASH分区：根据哈希函数将行数据分配到分区中。此方法适用于任何数据类型，并且具有随机性。此类型的分区通常用于随机分布的数据，如日志数据。
• LIST分区：List分区使用一个列表定义分区，每个分区包含了特定的值集合。如果记录的值包含在列表中，那么记录将存储在相应的分区中。
• KEY分区：类似于哈希分区，但不是基于哈希函数，而是根据列值上的一组键来执行分区。它只能用于 BLOB、TEXT、VARCHAR 或其他支持索引的数据类型。

工作原理

• 创建分区表：指定分区键，即表上哪一列的值作为分区依据。选择不同的分区类型将影响到后面的分区操作和存储方式。

-- 根据订单下单时间的年份，将数据分别划分到三个分区中，分别是p_2021、p_2022和p_2023
CREATE TABLE `orders` (
  `order_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `product_id` int unsigned NOT NULL,
  `order_time` datetime NOT NULL,
  PRIMARY KEY (`order_id`,`order_time`) //分区字段必须包含在主键字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (year(`order_time`))
(PARTITION p_2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
 PARTITION p_2022 VALUES LESS THAN (2023) ENGINE = InnoDB,
 PARTITION p_2023 VALUES LESS THAN (2024) ENGINE = InnoDB) */;

• 插入数据：INSERT INTO orders (order_id, user_id, product_id, order_time) VALUES (1, 1001, 2001, ‘2023-06-01 10:00:00’);

• 查询数据：在查询分区表时，MySQL会根据查询条件中的分区键值范围定位到对应的分区，然后只扫描该分区中的数据，避免无关分区数据的扫描访问，大幅降低查询数据开销。

• 新建分区：ALTER TABLE orders ADD PARTITION (PARTITION p_2024 VALUES LESS THAN (2025));

• 普通表转分区表：

  • 创建一个与原表结构完全相同的分区表，但不包含分区定义

    CREATE TABLE my_partitioned_table (
        -- 复制原表的所有列和约束
        ...
    ) PARTITION BY ...; -- 分区定义留空
    

  • 将原表的数据插入到分区表中。可以使用 INSERT INTO ... SELECT 语句将数据复制到分区表中。

  • 停止对原表的写入操作，并记录最后一次写入的时间点（例如通过添加一个新列来记录）。

  • 重命名原表，并将分区表重命名为原表的名称。

  • 根据转换前的原表的写入时间点，更新分区表的分区定义。ALTER TABLE my_table REORGANIZE PARTITION …;

    • REORGANIZE PARTITION 语句，重新定义分区范围，并将数据移动到相应的分区中。

  • 可以根据需要重新建立索引、重新生成统计信息等操作。

• 各分区count合计：

  SELECT SUM(PARTITION_ROWS) -- TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS 
  WHERE TABLE_NAME = 'your_partitioned_table';
   AND PARTITION_NAME IS NULL;
  

• 分区剪枝

  • 是 MySQL 的优化器在执行查询时自动进行的一种技术，用于排除不相关的分区，以减少扫描的数据量

  • SELECT SUM(amount)
    FROM sales
    WHERE date BETWEEN '2021-01-01' AND '2022-12-31';
    

  • 在执行上述查询时，MySQL 的优化器会自动应用分区剪枝技术，只选择与查询条件相关的分区进行扫描。优化器会识别出只有 p2021 和 p2022 这两个分区包含所需的数据，其他分区则可以被排除在外。

  • 通过分区剪枝，优化器会生成一个优化的执行计划，只对涉及的分区进行扫描，从而减少了查询的数据量和处理的开销，提高了查询的性能。

  • 注意，在使用分区剪枝时，查询条件必须与分区键相关才能生效。如果查询条件不与分区键相关，优化器将无法剪枝分区，会扫描所有的分区。

  • 分区剪枝是 MySQL 的一种自动优化技术，通过排除不相关的分区来减少查询的数据量，从而提高查询性能。它在处理大型分区表和时间范围查询时特别有用。

• 维护数据：在维护分区表时，MySQL会自动锁定需要维护的分区，而不是整张表的数据，以保证维护的效率。同时，也可以单独清空或重建某个分区，批量操作数据时可以显著提升效率。

• 优化查询：特定查询场景下，分区表拆分可以显著提高查询效率，如特定的日期范围查询、根据某个分区键值排序、查找特定某个分区，均可借助MySQL分区表的方式优化查询效率。

设计分区表：

• 选择合适的分区键：分区键决定了分区表行如何分布到分区内，以及如何检索和更新数据。常见的分区键有日期、地理位置、ID等。具体选择应根据业务逻辑来确定，例如订单表可以在日期上进行分区，地理信息表可以在地理位置上进行分区。
• 考虑分区的大小和数量：每个分区的大小和数量应该根据数据大小和查询模式来考虑。如果数据量超过单个分区的容量，则无法放在同一分区里，需要更多的分区来存储。同时，分区的数量也会影响查询速度，过多的分区会降低查询效率。
• 确定分区键的最大值：分区键的最大值决定了数据的分区数量和数据的存储位置。如果数据量是无限的，则需要特别注意分区最大值的范围。如果最大值太小，则需要及时分区，否则跨越分区将不得不扫描整个表，会影响查询性能。
• 对不同的分区类型进行评估：MySQL支持多种分区类型，包括Range、List、Hash、Key等。不同的分区类型可以根据分区键的类型和数量来选择，以最大化查询性能和管理效率。

分区表维护：

• 检查分区表：当MySQL分区表发生异常时，需要检查分区表是否存在错误、分区的状态是否正常。MySQL提供了一些命令和函数供检查分区表，如SHOW CREATE TABLE显示表的创建语句，检查分区键是否合法，SHOW TABLE STATUS查看分区表的统计信息，若有异常则使用ALTER TABLE修复、检查或重建分区等。
• 修改分区表：修改分区表有添加、删除和合并分区等操作，可以使用ALTER TABLE语法实现。
  • 添加分区：ADD PARTITION，删除分区：DROP PARTITION，合并分区：COALESCE PARTITION。
  • 注意：修改分区表可能会影响分区表上的数据，因此在执行分区操作前一定要先进行备份和归档操作。
• 备份和恢复：对MySQL的分区表进行备份的方式与常规的备份方法不同，需要通过备份分区数据进行。备份可以使用如mysqldump, mk-parallel-restore等工具，或者手动复制每个分区的数据文件。当需要恢复分区表时，需要先还原分区表的数据，然后使用ALTER TABLE语法添加或恢复分区。维护MySQL分区表的关键是要及时备份并定期检查表、分区状态。定期备份可以使用MySQL的工具或第三方工具，检查分区状态可以手动或使用MySQL的工具来执行。通过备份分区数据来保证数据重要性，通过检查分区表来发现异常并及时修复和调整，在数据维护和管理上，能够更加有管理可控性。
• 分区表的局限性：分区表虽然能够大幅提高查询性能和方便维护数据，但在一些特定的情况下，分区表也存在一些限制和局限性：
  • 对于一些不包括分区键的查询，扫描整张表数据会影响查询性能。
  • 分区表无法有效应对数据分布不均的情况，容易造成些分区数据过大而导致性能下降。
  • 分区表的建立需要仔细考虑分区键的选择和范围，如果分区键的范围选择不当，会导致数据跨分区查询或更新，影响性能和数据正确性。

https://mp.weixin.qq.com/s/IPcQNnbNi87xtP9HR0nHAQ

https://blog.csdn.net/shang_xs/article/details/131895007