广东省建筑网站,邱县网站建设,wap啥梗,wordpress自定义文章添加标签问题
单表数据量过大#xff0c;查询的性能通常会变得很低
查询时磁盘的IO次数增加#xff1a;MySQL的InnoDB引擎使用B树维护索引#xff0c;一个典型的索引节点大概可以存储60-120个数据记录#xff0c;一个三层的索引大概可以存储百万条数据。如果订单表增加一个数量级…问题
单表数据量过大查询的性能通常会变得很低
查询时磁盘的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) //分区字段必须包含在主键字段
) ENGINEInnoDB DEFAULT CHARSETutf8
/*!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
