MySQL 数据分区是一种将数据表按照一定的规则分割成多个更小的部分（即分区）的功能，每个分区可以独立存储在不同的文件中。数据分区的主要优点是可以提高查询性能，优化存储管理，特别是在处理大规模数据时。

分区表使用

1. 数据分区的类型

MySQL 支持以下几种分区类型：

  • RANGE 分区：根据列值范围分区，每个分区包含一个特定范围内的行数据。
  • LIST 分区：类似于 RANGE 分区，但每个分区根据特定的列值列表进行分区。
  • HASH 分区：使用一个哈希函数来决定行数据存储在哪个分区中，通常用于将数据均匀地分布在各个分区中。
  • KEY 分区：类似于 HASH 分区，但使用 MySQL 内部的哈希函数进行分区。

2. 创建分区表

假设有一个订单表 orders，我们希望按照订单日期 order_date 来进行 RANGE 分区，每年一个分区。

CREATE TABLE orders (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

在这个例子中，orders 表根据 order_date 的年份进行分区。每个分区只存储特定年份的数据。

3. 修改现有表为分区表

将一个现有的非分区表转换为分区表，通常需要以下步骤：

  1. 创建一个新的分区表。
  2. 将现有表的数据插入到新的分区表中。
  3. 删除旧表，重命名新表。

例如，假设有一个非分区表 old_orders：

CREATE TABLE old_orders (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2),
    PRIMARY KEY (order_id)
);

将其转换为分区表：

CREATE TABLE orders_partitioned (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

INSERT INTO orders_partitioned SELECT * FROM old_orders;

DROP TABLE old_orders;

ALTER TABLE orders_partitioned RENAME TO old_orders;

4. 不同场景下的使用示例

  • 按日期范围分区：如前面的示例，根据订单日期按年份分区。

  • 按地理区域分区：假设有一个用户表 users，并且希望根据用户所在国家进行 LIST 分区：

    CREATE TABLE users (
    user_id INT NOT NULL,
    user_name VARCHAR(50),
    country_code CHAR(2),
    PRIMARY KEY (user_id)
)
PARTITION BY LIST (country_code) (
    PARTITION p_north_america VALUES IN ('US', 'CA', 'MX'),
    PARTITION p_europe VALUES IN ('GB', 'FR', 'DE'),
    PARTITION p_asia VALUES IN ('CN', 'JP', 'KR'),
    PARTITION p_other VALUES IN ('AU', 'BR')
);

  • 按哈希值分区：假设我们有一个日志表 logs，希望通过哈希分区来均衡数据分布：

    CREATE TABLE logs (
    log_id INT NOT NULL,
    log_message TEXT,
    log_date DATETIME,
    PRIMARY KEY (log_id)
)
PARTITION BY HASH(YEAR(log_date))
PARTITIONS 4;

5. 使用分区的注意事项

  • 分区键：分区表的 PRIMARY KEY 或 UNIQUE KEY 必须包含分区键。
  • 分区数限制：MySQL 对每个表的分区数有限制，通常是 1024 个。
  • 分区操作：添加、删除或合并分区可能会影响性能，因此需要谨慎操作。
  • 查询优化：对于大表，分区可以显著加速查询，但也需要确保查询条件中使用了分区键，以便 MySQL 可以有效地定位特定分区。

分区表的迁移

将一个已经在生产环境中使用的业务表转换为分区表是一项复杂且重要的操作。下面是一个详细的步骤指南，帮助你将普通的业务表安全地转换成分区表。

步骤 1: 评估现有表的结构

首先，检查当前业务表的结构和索引，了解表的主键和可能的分区依据（例如日期、地理位置等）。

SHOW CREATE TABLE your_table_name;

这一步帮助你确定适合的分区类型（如 RANGE、LIST、HASH 等）。

步骤 2: 选择合适的分区策略并创建分区表

根据业务需求选择合适的分区策略。假设你决定按日期字段 created_at 进行 RANGE 分区，可以创建一个新的分区表。

CREATE TABLE your_table_partitioned LIKE your_table_name;

ALTER TABLE your_table_partitioned
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

步骤 3: 准备数据迁移

如果业务允许，建议在迁移数据之前暂停对表的写操作，避免在迁移过程中出现数据不一致的情况。这可以通过临时停止应用程序写操作或锁定表来实现。

-- 锁定表以防止写操作
FLUSH TABLES your_table_name WITH READ LOCK;

步骤 4: 将数据迁移到分区表

将现有表的数据复制到新的分区表中。这一步可能会花费较长时间，具体取决于数据量。

INSERT INTO your_table_partitioned SELECT * FROM your_table_name;

步骤 5: 验证数据迁移的正确性

在继续下一步之前，确保新表中的数据与旧表中的数据完全一致。

-- 验证记录数量是否一致
SELECT COUNT(*) FROM your_table_name;
SELECT COUNT(*) FROM your_table_partitioned;

-- 对比特定数据样本
SELECT * FROM your_table_name WHERE id = some_id;
SELECT * FROM your_table_partitioned WHERE id = some_id;

步骤 6: 备份现有表

在完成数据验证后，建议备份旧表，以防在后续步骤中出现问题时可以快速恢复。

RENAME TABLE your_table_name TO your_table_backup;

步骤 7: 重命名分区表

将新的分区表重命名为旧表的名称，以确保业务系统可以继续正常使用而无需修改代码。

ALTER TABLE your_table_partitioned RENAME TO your_table_name;

步骤 8: 恢复写操作

如果在步骤 3 中停止了写操作，确认所有数据已经正确迁移后，可以解除表的锁定或恢复应用程序的写操作。

-- 解除表锁
UNLOCK TABLES;

步骤 9: 删除旧表（可选）

在确认分区表运行正常并且业务不再需要旧表时，可以选择删除旧表。

DROP TABLE your_table_backup;

注意事项

  1. 数据一致性：确保在数据迁移期间没有新的数据写入旧表，避免数据不一致的情况。可以通过锁定表或临时停止写操作实现。

  2. 备份和恢复计划：在进行任何结构性更改之前，务必备份表，以防止意外情况导致的数据丢失。

  3. 业务停机时间：对于需要 24/7 运行的业务，考虑使用在线数据迁移工具（如 pt-online-schema-change）以减少停机时间。

  4. 分区键选择：在选择分区键时，确保它与表的查询模式匹配，以便最大化查询性能。

  5. 测试环境验证：建议在测试环境中模拟整个过程，确保转换过程顺利无误。