mysql分区表

分区的基本概念

允许将一个大的表、索引或其子集分割成多个较小的、更易于管理的片段，这些片段成为"分区"。每个分区都可以独立于其他分区进行存储、备份、索引和其他操作。这种技术主要是为了改善大型数据库表的查询性能、维护的方便性以及数据管理效率。

物理存储于逻辑分割

  • 物理上，每个分区可以存储在不同的文件或目录中，这取决于分区类型和配置。
  • 逻辑上，表数据根据分区键的值被分割到不同的分区里。

查询性能提升

  • 当执行查询时，MySQL能够确定哪些分区包含相关数据，并只在这些分区上进行搜索。这减少了需要搜索的数据量，从而提高了查询性能。
  • 对于范围查询或特定值的查询，分区可以显著减少扫描的数据量。

数据管理与维护

  • 分区可以使得数据管理更加灵活。例如，可以独立地备份、恢复或优化某个分区，而无需对整个表进行操作。
  • 对于具有时效性的数据，可以通过删除或归档某个分区来快速释放存储空间。

扩展性与并行处理

  • 分区技术使得数据库表更容易扩展到更大的数据集。当表的大小超过单个存储设备的容量时，可以使用分区将数据分布到多个存储设备上。
  • 由于每个分区可以独立处理，因此可以并行执行查询或其他数据库操作，从而进一步提高性能。

分区的原理和类型

InnoDB逻辑存储结构

InnoDB存储引擎的逻辑结构是一个层次化的体系，主要由表空间、段、区和页构成。

2024-7-114:40:24-1719816023326.png

  1. 表空间：是InnoDB数据的最高层容器，所有数据都逻辑地存储在这里。
  2. 段（segment）：是表空间的重要组成部分，根据用途可分为数据段、索引段和回滚段等。InnoDB引擎负责管理这些段，确保数据的完整性和高效访问。
  3. 区（Extent）：由连续的页组成，每个区默认大小为1MB，不论页的大小如何变化。为保证页的连续性，InnoDB会一次性从磁盘申请多个区。每个区包含64个连续的页，当默认页大小为16k时。在段开始时，InnoDB会先使用32个碎片页存储数据，以优化小表或特定段的空间利用率。
  4. 页（Page）：是InnoDB磁盘管理的最小单元，也被称为块。其默认大小为16KB，但可通过配置参数进行调整。页的类型多样，包括数据页、undo页、系统页等，每种页都有其特定的功能和结构。

分区原理

分区技术是将表中的记录分散到不同的物理文件中，即每个分区对应一个.idb文件。这是MySQL5.1及以后版本支持的一种高级功能，旨在提高大数据表的管理效率和查询性能。

2024-7-116:26:29-7f1d4830412b00125df8fdaec0c90014.jpg

  1. 分区类型：MySQL支持水平分区，即根据不同的条件将表中的行分配到不同的分区中。这些分区在物理上是独立的，可以单独处理，也可以作为整体处理。
  2. 性能影响：虽然分区可以提高查询性能和管理效率，但如果不恰当使用，也可能对性能产生负面影响。因此，在使用分区时应谨慎评估其影响。
  3. 索引与分区：在MySQL中，分区是局部的，意味着数据和索引都存储在各自的分区内。目前，MySQL尚不支持全局分区索引。
  4. 分区键与唯一索引：当表存在主键和唯一索引时，分区列必须是这些索引的一部分。这是为了确保分区的唯一性和查询效率。

通过合理利用分区技术，可以优化数据库性能、提高管理效率，并更好地适应大规模数据处理的需求。然而，为了充分利用这一功能，数据库管理员和开发者需要深入了解其工作原理和最佳实践。

分区类型

MySQL分区带来了许多优势，适用于各种使用场景：

  1. 性能提升：通过将数据分散到多个分区中，可以并行处理查询，从而提高查询性能。同时，对于涉及大量数据的维护操作（如备份和恢复），可以单独处理每个分区，减少了操作的复杂性和时间成本。
  2. 管理简化：分区可以使得数据管理更加灵活。例如，可以独立地备份、恢复或优化某个分区，而无需对整个表进行操作。这对于大型数据库表来说尤为重要，因为他可以显著减少维护时间和资源消耗。
  3. 数据归档和清理：对于具有时间属性的数据（如日志、交易记录等），可以使用分区来轻松归档旧数据或删除不在需要的数据。通过简单地删除或归档某个分区，可以快速释放存储空间并提高性能。
  4. 可扩展性：分区技术使得数据库表更容易扩展到更大的数据集。当表的大小超过单个存储设备的容量时，可以通过分区将数据分布到多个存储设备上，从而实现水平扩展。

2024-7-116:51:33-56404fa785da4b91fad38e9ac05d1bbe.jpg

如何实施分区

实施MySQL分区需要仔细规划和设计。以下时一些建议的步骤：

  1. 确定分区键：选择一个合适的列作为分区键，该列的值将用于将数据分配到不同的分区中。通常选择具有连续值或离散值的列作为分区键。
  2. 选择合适的分区类型：根据数据的特点和查询需求选择合适的分区类型（RANGE、LIST、HASH或KEY）。确保所选的分区类型 能够均匀地分布数据提高查询性能。
  3. 创建分区表：使用 CREATE TABLE 语句创建分区表，并指定分区表，并指定分区键和分区类型等参数。例如，使用RANGE分区类型创建一个按月分区的销售数据表：
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

create table sales
(
    sale_id   int            not null,
    sale_date date           not null,
    amount    decimal(10, 2) not null
) partition by range (year(sale_date))(
    partition p0 values less than (2022),
    partition p1 values  less than (2023),
    partition p2 values less than MAXVALUE
    );
  1. 查询和维护：一旦创建了分区表，就可以像普通表一样执行查询操作。MySQL会自动定位到相应的分区上执行查询。同时，可以独立地备份、恢复或优化每个分区。
  2. 监控和调整：定期控制分区的性能和存储使用情况，并根据需要进行调整。例如，可以添加新的分区来容纳新数据，或者删除旧的分区以释放存储空间。

分区表操作

包括创建分区表、修改分区和删除、合并、拆分等。

创建带有分区的表

RANGE分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

create table sales_range(
    id int not null ,
    sale_date date not null ,
    amount decimal(10,2) not null
)partition by  range (year(sale_date))(
    partition p0 values less than (2010),
    partition p1 values less than (2011),
    partition p2 values less than (2012),
    partition p3 values less than maxvalue
    );

LIST分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

create table sale_list(
    id int not null ,
    region varchar(10),
    amount decimal(10,2) not null
)partition by list columns (region)(
    partition pNorth values in ('North'),
    partition pSouth values in('South'),
    partition pEast values in('East'),
    partition pWest values in('West')
    );

HASH分区

1
2
3
4
5

create table sale_hash(
    id int not null ,
    sale_date DATE not null,
    amount decimal(10,2) not null 
)partition by HASH ( year(sale_date) ) partitions 4;

KEY分区

1
2
3
4
5
6

create table sale_key(
    id int not null ,
    sale_date date not null ,
    amount decimal(10,2) not null ,
    primary key (id,sale_date)
)partition by key(id) partitions 4;

修改分区表

添加分区

对于RANGE或LIST分区，可以使用alter table 语句添加分区：

1

alter table sales_range add partition (partition  p4 values less than (2013));

对于hash或key分区，由于它们是基于哈希函数进行分区的，因此不能直接添加分区，但可以通过重新创建表或调整分区来间接实现。

删除分区

可以使用alter table语句删除分区

1

alter table sales_range drop partition p0;

合并分区

对于相邻的RANGE或list分区，可以使用alter table 语句将它们合并为一个分区

1
2
3

alter table sales_range
    reorganize partition p1, p2 into (partition p1_2 values less than (2012)
        );
  • 分区合并限制：
  1. 相邻分区合并：在MySQL中，通常只能合并相邻的分区。这意味着你不能随意选择两个不相邻的分区进行合并。
  2. 分区类型和键的限制：与拆分操作类似，合并操作也受到分区类型和分区键的约束。不是所有类型的分区都可以轻松合并。
  3. 数据迁移和重建：合并分区时，可能需要进行数据迁移和索引重建，这可能会影响数据库的性能和可用性。

解释几个问题

MySQL分区处理NULL值的方式

MySQL中，当涉及到分区时，系统并不会特别禁止NULL值。不论是列的实际值还是用户自定义的表达式结果，MySQL通常会将NULL值视为0进行处理。然而，这种行为可能并不总是符合数据完整性和准确性的要求。为了避免这种隐式的NULL到0的转换，最佳实践是在设计数据库表时，对相关列明确声明为“NOT NULL”。这样做可以确保数据的准确性和一致性，同时避免由于NULL值被错误地解释为0而导致的潜在问题。因此，在设计分区表时，应该谨慎考虑NULL值的处理方式，并根据需要采取相应的预防措施。

此外，如果确实需要存储NULL值，并且不希望MySQL将其视为0，可以考虑使用其他特殊值（如某个不可能在实际业务中出现的标识值）来代替NULL，或者在设计分区策略时明确考虑NULL值的处理逻辑。这样可以在保持数据完整性的同时，更好地满足业务需求。

分区列必须主键或唯一键的一部分

在MySQL中，当表存在主键（primary key）或唯一键（unique key）时，分区的列必须是这些键的一个组成部分的原因主要涉及到数据的完整性和查询性能：

数据完整性：

主键和唯一键用于保证表中数据的唯一性。如果分区列不是这些键的一部分，那么在不同分区中可能存在具有相同主键或唯一键值的数据行，这将破坏数据的唯一性约束。 查询性能：

分区的主要目的是为了提高查询性能，特别是针对大数据量的表。如果分区列不是主键或唯一键的一部分，那么在进行基于主键或唯一键的查询时，MySQL可能需要在所有分区中进行搜索，从而降低了查询性能。 数据一致性：

当表被分区时，每个分区实际上可以看作是一个独立的“子表”。如果分区列不是主键或唯一键的一部分，那么在执行更新或删除操作时，MySQL需要确保跨所有分区的数据一致性，这会增加操作的复杂性和开销。 分区策略：

MySQL的分区策略是基于分区列的值来将数据分配到不同的分区中。如果分区列不是主键或唯一键的一部分，那么分区策略可能会变得复杂且低效，因为系统需要额外处理主键或唯一键的约束。

分区与性能考量

技术的运用需要恰到好处才能发挥其优势。以显式锁为例，虽然功能强大，但使用不当可能导致性能下降或其他不良后果。同样地，分区技术也并非万能的性能提升工具。

分区确实可以为某些SQL查询带来性能上的提升，但其主要价值在于提高数据库的高可用性管理。在应用分区技术时，我们需要根据数据库的使用场景来谨慎选择。

数据库应用大体上可分为OLTP（在线事务处理）和OLAP（在线分析处理）两类。对于OLAP应用来说，分区能够显著提升查询性能，因为分析类查询往往需要处理大量数据。按时间进行分区，例如按月划分用户行为数据，可以使得查询只需扫描相关分区，从而提高效率。

然而，在OLTP应用中，使用分区则需更为谨慎。这类应用通常不会查询大表中超过10%的数据，而是通过索引快速检索少量记录。例如，对于包含1000万条记录的表，如果查询使用了辅助索引但未涉及分区键，可能导致性能下降。原本在单个B+树中3次逻辑IO就能完成的操作，在10个分区的情况下可能需要(3+3)*10次逻辑IO（分别访问聚集索引和辅助索引）。

因此，在OLTP应用中采用分区表时，务必进行充分的性能测试和优化。

为了便于开发者观察SQL查询对分区的利用情况，可以使用EXPLAIN PARTITIONS语句与SELECT查询结合，从而清晰地看到哪些分区被查询涉及。

Mysql
Licensed under CC BY-NC-SA 4.0
comments powered by Disqus
© 2020 - 2025 韩永发的博客
使用 Hugo 构建
主题 Stack 由 Jimmy 设计