问题
单表数据量过大,查询的性能通常会变得很低
- 查询时磁盘的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