MySQL 分区表设计_mysql分区表创建

MySQL 分区表设计

1、分区表设计方案

当设计 MySQL 分区表时，需要考虑以下几个方面：分区策略、分区字段、分区数量和分区函数。下面是一个详细的示例，展示了如何设计和执行分区表的增删改查操作。

设计分区表：

考虑一个订单表的例子，我们可以按照订单创建时间对表进行范围分区。

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    total_amount DECIMAL(10, 2),
    PRIMARY KEY (order_id, order_date)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='这是一个分区表，按月份分区'
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
1
2
3
4
5
6
7
8
9
10
11
12
13

上述示例中，我们创建了一个名为 orders 的分区表，并按照 order_date 字段的年份进行范围分区，总共有四个分区。

插入数据到分区表：

INSERT INTO orders (order_date, customer_id, total_amount) VALUES
    ('2021-01-01', 1001, 50.00),
    ('2021-02-15', 1002, 100.00),
    ('2022-03-10', 1003, 200.00);
1
2
3
4

上述示例向分区表插入了三条订单数据，分别属于不同的分区。

查询分区表数据：

SELECT * FROM orders WHERE order_date >= '2021-01-01' AND order_date < '2022-01-01';
1

上述示例查询了 orders 表中 2021 年的订单数据。

更新分区表数据：

UPDATE orders SET total_amount = 150.00 WHERE order_id = 1 AND order_date = '2021-01-01';
1

上述示例更新了 orders 表中指定订单的金额。

删除分区表数据：

DELETE FROM orders WHERE order_date >= '2022-01-01';
1

上述示例删除了 orders 表中 2022 年及之后的订单数据。

这些示例涵盖了分区表的设计和基本操作。但请注意，在实际使用分区表时，还需要根据具体需求和数据特征进行细致的设计和调整。同时，还应考虑性能优化、索引策略和维护操作等因素。

2、普通表转换分区表

将一个已经存在的普通表转换为分区表需要进行以下步骤：

创建分区表

创建一个与原表结构完全相同的分区表，但不包含分区定义。例如，假设我们要将现有的 my_table 表转换为分区表。

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

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

INSERT INTO my_partitioned_table (...)
SELECT ... FROM my_table;
1
2

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

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

ALTER TABLE my_table RENAME TO my_table_old;
ALTER TABLE my_partitioned_table RENAME TO my_table;
1
2

根据转换前的原表的写入时间点，更新分区表的分区定义。

ALTER TABLE my_table REORGANIZE PARTITION ...;
1

这里的 PARTITION ... 部分取决于您选择的分区策略和分区字段。通过 REORGANIZE PARTITION 语句，您可以重新定义分区范围，并将数据移动到相应的分区中。

完成后，您可以根据需要重新建立索引、重新生成统计信息等操作。请注意，在进行此过程时，确保先进行适当的备份，并在开发环境中进行充分的测试，以避免数据丢失或其他潜在问题。此外，由于转换过程可能会带来一些停机时间或性能影响，请在合理的维护窗口内进行转换。

3、datetime字段按月分区

要按照 datetime 类型字段按月进行分区，可以使用 MySQL 的范围分区策略和日期函数。下面是一个示例，演示如何按月对表进行分区：

创建分区表：

CREATE TABLE my_partitioned_table (
    id INT,
    event_date DATETIME
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='这是一个分区表，按月份分区'
PARTITION BY RANGE COLUMNS(event_date) (
    PARTITION p0 VALUES LESS THAN ('2023-01-01'),
    PARTITION p1 VALUES LESS THAN ('2023-02-01'),
    PARTITION p2 VALUES LESS THAN ('2023-03-01'),
    PARTITION p3 VALUES LESS THAN ('2023-04-01'),
    ...
    PARTITION pn VALUES LESS THAN MAXVALUE
);
1
2
3
4
5
6
7
8
9
10
11
12

上述示例中，我们创建了一个名为 my_partitioned_table 的分区表，并根据 event_date 列的范围进行分区。每个分区对应一个月份，范围是从每月的第一天到下一个月的第一天。

插入数据到分区表：

INSERT INTO my_partitioned_table (id, event_date) VALUES
    (1, '2023-01-05'),
    (2, '2023-01-15'),
    (3, '2023-02-10'),
    (4, '2023-03-25');
    ```
上述示例向分区表插入了四条数据，分别属于不同的月份。
1
2
3
4
5
6
7

查询特定月份的数据：

SELECT * FROM my_partitioned_table PARTITION (p1);
1

上述示例查询了 my_partitioned_table 表中 2023 年 2 月的数据。
通过按月分区，可以更加高效地查询特定时间范围内的数据。同时，请确保在插入或更新数据时，将数据插入到正确的分区中，以避免跨分区查询的性能问题。

4、datetime 只分月不分年实现

要实现只对分月而不分年的分区，可以使用MySQL的范围分区策略。下面是一个示例，演示如何按照月份对表进行分区：

创建分区表：

CREATE TABLE my_partitioned_table (
    id INT,
    event_date DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='这是一个分区表，按月份分区'
PARTITION BY RANGE (MONTH(event_date)) (
    PARTITION p0 VALUES LESS THAN (2),
    PARTITION p1 VALUES LESS THAN (3),
    PARTITION p2 VALUES LESS THAN (4),
    PARTITION p3 VALUES LESS THAN (5),
    ...
    PARTITION pn VALUES LESS THAN (13)
);
1
2
3
4
5
6
7
8
9
10
11
12

上述示例中，我们创建了一个名为 my_partitioned_table 的分区表，并根据 event_date 列的月份进行分区。每个分区对应一个月份，范围是从1到12。

插入数据到分区表：

INSERT INTO my_partitioned_table (id, event_date) VALUES
    (1, '2023-01-05'),
    (2, '2023-01-15'),
    (3, '2023-02-10'),
    (4, '2023-03-25');
    ```
上述示例向分区表插入了四条数据，分别属于不同的月份。

#### 查询特定月份的数据：
```sql
SELECT * FROM my_partitioned_table PARTITION (p1);
1
2
3
4
5
6
7
8
9
10
11

上述示例查询了 my_partitioned_table 表中2月份的数据。

通过按照月份进行分区，可以更加高效地查询特定月份的数据。请注意，上述示例没有分区年份，如果需要包含多年的数据，可以将分区范围扩展到跨越多年的月份。同时，请确保在插入或更新数据时，将数据插入到正确的分区中，以避免跨分区查询的性能问题。

5、datetime 只分月不分年，查询范围数据

如果只对分月而不分年，并且想要查询两年内的数据，可以使用MySQL的范围-列表混合分区策略。以下是一个示例，演示如何实现该需求：

创建分区表：

CREATE TABLE my_partitioned_table (
    id INT,
    event_date DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='这是一个分区表，按月份分区'
PARTITION BY RANGE (YEAR(event_date))
SUBPARTITION BY LIST (MONTH(event_date)) (
    PARTITION p0 VALUES LESS THAN (2022) (
        SUBPARTITION s0 VALUES IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
    ),
    PARTITION p1 VALUES LESS THAN (2024) (
        SUBPARTITION s1 VALUES IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
    )
);
1
2
3
4
5
6
7
8
9
10
11
12
13

上述示例中，我们创建了一个名为 my_partitioned_table 的分区表，并根据 event_date 列的年份和月份进行分区。每个年份的分区再根据月份进行子分区。范围分区设置为2022年至2023年的数据。

插入数据到分区表：

INSERT INTO my_partitioned_table (id, event_date) VALUES
    (1, '2022-12-15'),
    (2, '2023-01-10'),
    (3, '2023-05-20'),
    (4, '2023-12-25');
1
2
3
4
5

上述示例向分区表插入了四条数据，跨越了两年的数据。

查询两年内的数据：

SELECT * FROM my_partitioned_table 
WHERE event_date >= '2022-01-01' AND event_date < '2024-01-01';
1
2

上述示例查询了 my_partitioned_table 表中2022年至2023年的所有数据。
通过使用范围-列表混合分区策略，并根据实际需求设置分区边界和子分区边界，可以实现只对分月不分年，并查询两年内数据的功能。

6、truncate 分区表

在MySQL中，可以使用 ALTER TABLE 语句来截断（empty）分区表的指定分区。以下是一个示例，演示如何对分区表进行分区截断：

ALTER TABLE my_partitioned_table TRUNCATE PARTITION partition_name;
1

在上述示例中，将 my_partitioned_table 替换为您的分区表的名称，将 partition_name 替换为要截断的分区的名称。
请注意，截断分区将删除该分区中的所有数据，并将分区清空。只有当分区表使用了范围分区或列表分区策略时才能进行分区截断操作。

如果想要截断多个分区，可以使用逗号分隔不同的分区名称，如下所示：

ALTER TABLE my_partitioned_table TRUNCATE PARTITION partition_name1, partition_name2;
1

上述示例将截断 partition_name1 和 partition_name2 两个分区。

请谨慎执行分区截断操作，因为它会永久删除分区中的数据，并且无法恢复。在执行此操作之前，请确保已经备份了重要的数据。

7、创建分区表，id主键报错

报错信息：

A Primary key must include all columns in the tables partition function

在创建按月分区表时，如果使用了分区函数对表进行分区，主键必须包含表中的所有列，并且还需要包含用于分区的列。这是因为根据分区函数将数据分配到不同的分区时，需要确保每个分区中的数据都具有唯一性。

以下是一个示例，展示如何在创建按月分区表时设置主键，其中包含分区列和其他列：

CREATE TABLE my_partitioned_table (
    id INT,
    event_datetime DATETIME,
    -- 其他列
    PRIMARY KEY (event_datetime, id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='这是一个分区表，按月份分区'
PARTITION BY RANGE (MONTH(event_datetime)) (
    -- 分区定义
    PARTITION p0 VALUES LESS THAN (2),
    PARTITION p1 VALUES LESS THAN (3),
    PARTITION p2 VALUES LESS THAN (4),
    PARTITION p3 VALUES LESS THAN (5),
    PARTITION p4 VALUES LESS THAN (6),
    PARTITION p5 VALUES LESS THAN (7),
    PARTITION p6 VALUES LESS THAN (8),
    PARTITION p7 VALUES LESS THAN (9),
    PARTITION p8 VALUES LESS THAN (10),
    PARTITION p9 VALUES LESS THAN (11),
    PARTITION p10 VALUES LESS THAN (12),
    PARTITION p11 VALUES LESS THAN (13)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在上述示例中，我们通过在 CREATE TABLE 语句中指定 PRIMARY KEY 来设置主键。主键包括了分区列 event_datetime 和其他列 id。这样可以确保每个分区中的数据具有唯一性。
请根据您的表结构和需求，调整主键的具体定义。

8、各分区 count 合计

要计算所有分区表中的数据条目总数，可以使用以下示例代码：

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

在上述示例中，请将 'your_partitioned_table' 替换为您实际的分区表名称。执行此查询后，将返回分区表中所有分区的数据条目总数。

请注意，这里使用了 INFORMATION_SCHEMA.PARTITIONS 系统表来获取分区表的相关信息。PARTITION_ROWS 是该表中存储的每个分区的数据行数。通过对所有分区的行数求和，可以得到整个分区表的数据条目总数。

当执行上述查询时，确保拥有足够的权限来访问 INFORMATION_SCHEMA.PARTITIONS 表，并且已正确指定分区表的名称。

9、分区剪枝 （Partition Pruning）

分区剪枝（Partition Pruning）是 MySQL 的优化器在执行查询时自动进行的一种技术，用于排除不相关的分区，以减少扫描的数据量。以下是一个示例来说明分区剪枝的工作原理：

假设有一个按时间分区的表 sales，其中包含 id、date 和 amount 字段。表按每年一个分区进行分区，命名为 p2020、p2021、p2022、等等。现在我们想查询某个时间范围内的销售额。

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

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

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

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

此外，分区剪枝还可以与其他查询优化技术（如索引使用、统计信息等）结合使用，以提高查询性能。

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

10、分区表预留空间（默认）

在 Navicat 中创建分区表时，可能会出现 "50100" 的情况，这是由于 Navicat 预留了一部分空间用于存储分区信息。

在 MySQL 中，对于每个分区表，都需要一个默认分区（也称为无效分区），以便处理不属于任何其他分区的数据。这个默认分区需要占用一定的空间，即 50100 字节。因此，在 Navicat 中创建分区表时，会为默认分区预留这部分空间。

当你在 Navicat 中创建分区表时，可以忽略这个默认分区，因为它只是用来处理无法匹配到其他分区的数据。如果你没有自定义默认分区的话，MySQL 会自动将这些数据放入默认分区中。

请注意，这个 "50100" 的大小是 MySQL 的默认值，如果你在 MySQL 配置中更改了默认值，那么在 Navicat 中创建分区表时，预留的空间大小可能会有所不同。

总结来说，Navicat 在创建分区表时会预留一部分空间用于默认分区，这是正常的行为，不需要过多关注。

11、mysql 复制表

非同库实现方案

在目标数据库中创建一个与源表结构相同的新表：

CREATE TABLE 目标库名.新表名 LIKE 源库名.原表名;
1

这将在目标数据库中创建一个名为 “新表名” 的新表，其结构与源数据库中的 “原表名” 相同。

将源表的数据插入到目标表中：

INSERT INTO 目标库名.新表名 SELECT * FROM 源库名.原表名;
1

这将从源表中选择所有数据，并将其插入到目标表中。

同库复制表

CREATE TABLE 新表名 LIKE 原表名;
INSERT INTO 新表名 SELECT * FROM 原表名;
1
2

12、mysql 8.0 以下 truncate 分区表锁表

在MySQL 5.7.30(系统版本)及更早版本中，使用 TRUNCATE TABLE 命令对分区表进行操作时会锁定整个表，这可能导致其他会话在执行期间被阻塞。

• 1.使用 DELETE 命令替代 TRUNCATE：如果 TRUNCATE TABLE 操作会导致表锁定问题，可以考虑改用 DELETE FROM命令来删除表中的所有行。DELETE命令是逐行删除的，因此不会锁定整个表。请注意，DELETE命令在删除大量数据时可能效率较低，因为它会记录日志和生成回滚段。

• 2.分段 TRUNCATE：将大的分区表拆分成多个较小的分区，然后分别执行 TRUNCATE TABLE 命令。这样可以减少锁定的粒度，并降低对整个表的锁定时间。但是，这种方法需要重构分区表结构，可能会造成一些额外的工作。

• 3.升级到MySQL 8.0或更高版本：MySQL 8.0引入了一项重要的改进，即针对TRUNCATE TABLE命令的分区锁定进行了优化。在MySQL 8.0及更高版本中，TRUNCATE PARTITION 语法可用于仅清空特定分区而不锁定整个表。因此，升级到MySQL 8.0或更高版本可能是一个解决方案。

不升级mysql、truncate方案

如果你不能升级MySQL版本，但仍然希望在线执行 TRUNCATE PARTITION 操作并避免锁表，可以考虑以下方法：

使用分区交换：将要清空的分区与一个空分区进行交换。这样可以实现快速清空分区的效果，而不会锁定整个表。具体步骤如下：

• 1.创建一个空的临时分区，可以是已存在的空分区或者新创建的分区。
  使用 ALTER TABLE 进行分区交换操作，将要清空的分区与空分区进行交换，例如：

ALTER TABLE your_table EXCHANGE PARTITION p_to_truncate WITH TABLE empty_partition;
1

这个操作是原子的，并且不会锁定整个表。
最后，删除交换后的空分区。
通过使用分区交换，你可以在不锁定整个表的情况下快速清空指定的分区。

• 2.使用临时表(离线)：将要清空的分区数据复制到一个临时表中，并通过 RENAME 操作进行切换。具体步骤如下：

创建一个临时表，结构与原分区表相同。
使用 INSERT INTO ... SELECT 将要清空的分区数据复制到临时表中。
使用 RENAME TABLE 进行表名切换，将原分区表重命名为备份表，将临时表重命名为原分区表的名称。
最后，删除备份表。
这种方法需要一定的额外存储空间来保存临时表和备份表，但可以实现在线清空分区而不锁定整个表。