1、分區(qū)表設(shè)計方案

當(dāng)設(shè)計 MySQL 分區(qū)表時，需要考慮以下幾個方面：分區(qū)策略、分區(qū)字段、分區(qū)數(shù)量和分區(qū)函數(shù)。下面是一個詳細的示例，展示了如何設(shè)計和執(zhí)行分區(qū)表的增刪改查操作。

設(shè)計分區(qū)表：

考慮一個訂單表的例子，我們可以按照訂單創(chuàng)建時間對表進行范圍分區(qū)。

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='這是一個分區(qū)表，按月份分區(qū)'
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
);

上述示例中，我們創(chuàng)建了一個名為 orders 的分區(qū)表，并按照 order_date 字段的年份進行范圍分區(qū)，總共有四個分區(qū)。

插入數(shù)據(jù)到分區(qū)表：

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);

上述示例向分區(qū)表插入了三條訂單數(shù)據(jù)，分別屬于不同的分區(qū)。

查詢分區(qū)表數(shù)據(jù)：

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

上述示例查詢了 orders 表中 2021 年的訂單數(shù)據(jù)。

更新分區(qū)表數(shù)據(jù)：

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

上述示例更新了 orders 表中指定訂單的金額。

刪除分區(qū)表數(shù)據(jù)：

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

上述示例刪除了 orders 表中 2022 年及之后的訂單數(shù)據(jù)。

這些示例涵蓋了分區(qū)表的設(shè)計和基本操作。但請注意，在實際使用分區(qū)表時，還需要根據(jù)具體需求和數(shù)據(jù)特征進行細致的設(shè)計和調(diào)整。同時，還應(yīng)考慮性能優(yōu)化、索引策略和維護操作等因素。

2、普通表轉(zhuǎn)換分區(qū)表

將一個已經(jīng)存在的普通表轉(zhuǎn)換為分區(qū)表需要進行以下步驟：

創(chuàng)建分區(qū)表

創(chuàng)建一個與原表結(jié)構(gòu)完全相同的分區(qū)表，但不包含分區(qū)定義。例如，假設(shè)我們要將現(xiàn)有的 my_table 表轉(zhuǎn)換為分區(qū)表。

CREATE TABLE my_partitioned_table (
    -- 復(fù)制原表的所有列和約束
    ...
) PARTITION BY ...; -- 分區(qū)定義留空

將原表的數(shù)據(jù)插入到分區(qū)表中?？梢允褂?nbsp;INSERT INTO ... SELECT 語句將數(shù)據(jù)復(fù)制到分區(qū)表中。

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

停止對原表的寫入操作，并記錄最后一次寫入的時間點（例如通過添加一個新列來記錄）。

重命名原表，并將分區(qū)表重命名為原表的名稱。

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

根據(jù)轉(zhuǎn)換前的原表的寫入時間點，更新分區(qū)表的分區(qū)定義。

ALTER TABLE my_table REORGANIZE PARTITION ...;

這里的 PARTITION ... 部分取決于您選擇的分區(qū)策略和分區(qū)字段。通過 REORGANIZE PARTITION 語句，您可以重新定義分區(qū)范圍，并將數(shù)據(jù)移動到相應(yīng)的分區(qū)中。

完成后，您可以根據(jù)需要重新建立索引、重新生成統(tǒng)計信息等操作。請注意，在進行此過程時，確保先進行適當(dāng)?shù)膫浞荩⒃陂_發(fā)環(huán)境中進行充分的測試，以避免數(shù)據(jù)丟失或其他潛在問題。此外，由于轉(zhuǎn)換過程可能會帶來一些停機時間或性能影響，請在合理的維護窗口內(nèi)進行轉(zhuǎn)換。

3、datetime字段按月分區(qū)

要按照 datetime 類型字段按月進行分區(qū)，可以使用 MySQL 的范圍分區(qū)策略和日期函數(shù)。下面是一個示例，演示如何按月對表進行分區(qū)：

創(chuàng)建分區(qū)表：

CREATE TABLE my_partitioned_table (
    id INT,
    event_date DATETIME
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='這是一個分區(qū)表，按月份分區(qū)'
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
);

上述示例中，我們創(chuàng)建了一個名為 my_partitioned_table 的分區(qū)表，并根據(jù) event_date 列的范圍進行分區(qū)。每個分區(qū)對應(yīng)一個月份，范圍是從每月的第一天到下一個月的第一天。

插入數(shù)據(jù)到分區(qū)表：

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');
    ```
上述示例向分區(qū)表插入了四條數(shù)據(jù)，分別屬于不同的月份。

查詢特定月份的數(shù)據(jù)：

SELECT * FROM my_partitioned_table PARTITION (p1);

上述示例查詢了 my_partitioned_table 表中 2023 年 2 月的數(shù)據(jù)。
通過按月分區(qū)，可以更加高效地查詢特定時間范圍內(nèi)的數(shù)據(jù)。同時，請確保在插入或更新數(shù)據(jù)時，將數(shù)據(jù)插入到正確的分區(qū)中，以避免跨分區(qū)查詢的性能問題。

4、datetime 只分月不分年實現(xiàn)

要實現(xiàn)只對分月而不分年的分區(qū)，可以使用MySQL的范圍分區(qū)策略。下面是一個示例，演示如何按照月份對表進行分區(qū)：

創(chuàng)建分區(qū)表：

CREATE TABLE my_partitioned_table (
    id INT,
    event_date DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='這是一個分區(qū)表，按月份分區(qū)'
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)
);

上述示例中，我們創(chuàng)建了一個名為 my_partitioned_table 的分區(qū)表，并根據(jù) event_date 列的月份進行分區(qū)。每個分區(qū)對應(yīng)一個月份，范圍是從1到12。

插入數(shù)據(jù)到分區(qū)表：

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');
    ```
上述示例向分區(qū)表插入了四條數(shù)據(jù)，分別屬于不同的月份。

#### 查詢特定月份的數(shù)據(jù)：
```sql
SELECT * FROM my_partitioned_table PARTITION (p1);

上述示例查詢了 my_partitioned_table 表中2月份的數(shù)據(jù)。

通過按照月份進行分區(qū)，可以更加高效地查詢特定月份的數(shù)據(jù)。請注意，上述示例沒有分區(qū)年份，如果需要包含多年的數(shù)據(jù)，可以將分區(qū)范圍擴展到跨越多年的月份。同時，請確保在插入或更新數(shù)據(jù)時，將數(shù)據(jù)插入到正確的分區(qū)中，以避免跨分區(qū)查詢的性能問題。

5、datetime 只分月不分年，查詢范圍數(shù)據(jù)

如果只對分月而不分年，并且想要查詢兩年內(nèi)的數(shù)據(jù)，可以使用MySQL的范圍-列表混合分區(qū)策略。以下是一個示例，演示如何實現(xiàn)該需求：

創(chuàng)建分區(qū)表：

CREATE TABLE my_partitioned_table (
    id INT,
    event_date DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='這是一個分區(qū)表，按月份分區(qū)'
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)
    )
);

上述示例中，我們創(chuàng)建了一個名為 my_partitioned_table 的分區(qū)表，并根據(jù) event_date 列的年份和月份進行分區(qū)。每個年份的分區(qū)再根據(jù)月份進行子分區(qū)。范圍分區(qū)設(shè)置為2022年至2023年的數(shù)據(jù)。

插入數(shù)據(jù)到分區(qū)表：

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');

上述示例向分區(qū)表插入了四條數(shù)據(jù)，跨越了兩年的數(shù)據(jù)。

查詢兩年內(nèi)的數(shù)據(jù)：

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

上述示例查詢了 my_partitioned_table 表中2022年至2023年的所有數(shù)據(jù)。
通過使用范圍-列表混合分區(qū)策略，并根據(jù)實際需求設(shè)置分區(qū)邊界和子分區(qū)邊界，可以實現(xiàn)只對分月不分年，并查詢兩年內(nèi)數(shù)據(jù)的功能。

6、truncate 分區(qū)表

在MySQL中，可以使用 ALTER TABLE 語句來截斷（empty）分區(qū)表的指定分區(qū)。以下是一個示例，演示如何對分區(qū)表進行分區(qū)截斷：

ALTER TABLE my_partitioned_table TRUNCATE PARTITION partition_name;

在上述示例中，將 my_partitioned_table 替換為您的分區(qū)表的名稱，將 partition_name 替換為要截斷的分區(qū)的名稱。
請注意，截斷分區(qū)將刪除該分區(qū)中的所有數(shù)據(jù)，并將分區(qū)清空。只有當(dāng)分區(qū)表使用了范圍分區(qū)或列表分區(qū)策略時才能進行分區(qū)截斷操作。

如果想要截斷多個分區(qū)，可以使用逗號分隔不同的分區(qū)名稱，如下所示：

ALTER TABLE my_partitioned_table TRUNCATE PARTITION partition_name1, partition_name2;

上述示例將截斷 partition_name1 和 partition_name2 兩個分區(qū)。

請謹慎執(zhí)行分區(qū)截斷操作，因為它會永久刪除分區(qū)中的數(shù)據(jù)，并且無法恢復(fù)。在執(zhí)行此操作之前，請確保已經(jīng)備份了重要的數(shù)據(jù)。

7、創(chuàng)建分區(qū)表，id主鍵報錯

報錯信息：

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

在創(chuàng)建按月分區(qū)表時，如果使用了分區(qū)函數(shù)對表進行分區(qū)，主鍵必須包含表中的所有列，并且還需要包含用于分區(qū)的列。這是因為根據(jù)分區(qū)函數(shù)將數(shù)據(jù)分配到不同的分區(qū)時，需要確保每個分區(qū)中的數(shù)據(jù)都具有唯一性。

以下是一個示例，展示如何在創(chuàng)建按月分區(qū)表時設(shè)置主鍵，其中包含分區(qū)列和其他列：

CREATE TABLE my_partitioned_table (
    id INT,
    event_datetime DATETIME,
    -- 其他列
    PRIMARY KEY (event_datetime, id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='這是一個分區(qū)表，按月份分區(qū)'
PARTITION BY RANGE (MONTH(event_datetime)) (
    -- 分區(qū)定義
    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)
);

在上述示例中，我們通過在 CREATE TABLE 語句中指定 PRIMARY KEY 來設(shè)置主鍵。主鍵包括了分區(qū)列 event_datetime 和其他列 id。這樣可以確保每個分區(qū)中的數(shù)據(jù)具有唯一性。
請根據(jù)您的表結(jié)構(gòu)和需求，調(diào)整主鍵的具體定義。

8、各分區(qū) count 合計

要計算所有分區(qū)表中的數(shù)據(jù)條目總數(shù)，可以使用以下示例代碼：

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

在上述示例中，請將 'your_partitioned_table' 替換為您實際的分區(qū)表名稱。執(zhí)行此查詢后，將返回分區(qū)表中所有分區(qū)的數(shù)據(jù)條目總數(shù)。

請注意，這里使用了 INFORMATION_SCHEMA.PARTITIONS 系統(tǒng)表來獲取分區(qū)表的相關(guān)信息。PARTITION_ROWS 是該表中存儲的每個分區(qū)的數(shù)據(jù)行數(shù)。通過對所有分區(qū)的行數(shù)求和，可以得到整個分區(qū)表的數(shù)據(jù)條目總數(shù)。

當(dāng)執(zhí)行上述查詢時，確保擁有足夠的權(quán)限來訪問 INFORMATION_SCHEMA.PARTITIONS 表，并且已正確指定分區(qū)表的名稱。

9、分區(qū)剪枝 （Partition Pruning）

分區(qū)剪枝（Partition Pruning）是 MySQL 的優(yōu)化器在執(zhí)行查詢時自動進行的一種技術(shù)，用于排除不相關(guān)的分區(qū)，以減少掃描的數(shù)據(jù)量。以下是一個示例來說明分區(qū)剪枝的工作原理：

假設(shè)有一個按時間分區(qū)的表 sales，其中包含 id、date 和 amount 字段。表按每年一個分區(qū)進行分區(qū)，命名為 p2020、p2021、p2022、等等。現(xiàn)在我們想查詢某個時間范圍內(nèi)的銷售額。

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

在執(zhí)行上述查詢時，MySQL 的優(yōu)化器會自動應(yīng)用分區(qū)剪枝技術(shù)，只選擇與查詢條件相關(guān)的分區(qū)進行掃描。在這個示例中，優(yōu)化器會識別出只有 p2021 和 p2022 這兩個分區(qū)包含所需的數(shù)據(jù)，其他分區(qū)則可以被排除在外。

通過分區(qū)剪枝，優(yōu)化器會生成一個優(yōu)化的執(zhí)行計劃，只對涉及的分區(qū)進行掃描，從而減少了查詢的數(shù)據(jù)量和處理的開銷，提高了查詢的性能。

需要注意的是，在使用分區(qū)剪枝時，查詢條件必須與分區(qū)鍵相關(guān)才能生效。如果查詢條件不與分區(qū)鍵相關(guān)，優(yōu)化器將無法剪枝分區(qū)，會掃描所有的分區(qū)。

此外，分區(qū)剪枝還可以與其他查詢優(yōu)化技術(shù)（如索引使用、統(tǒng)計信息等）結(jié)合使用，以提高查詢性能。

總之，分區(qū)剪枝是 MySQL 的一種自動優(yōu)化技術(shù)，通過排除不相關(guān)的分區(qū)來減少查詢的數(shù)據(jù)量，從而提高查詢性能。它在處理大型分區(qū)表和時間范圍查詢時特別有用。

10、分區(qū)表預(yù)留空間（默認）

在 Navicat 中創(chuàng)建分區(qū)表時，可能會出現(xiàn) "50100" 的情況，這是由于 Navicat預(yù)留了一部分空間用于存儲分區(qū)信息。

在 MySQL 中，對于每個分區(qū)表，都需要一個默認分區(qū)（也稱為無效分區(qū)），以便處理不屬于任何其他分區(qū)的數(shù)據(jù)。這個默認分區(qū)需要占用一定的空間，即 50100 字節(jié)。因此，在 Navicat 中創(chuàng)建分區(qū)表時，會為默認分區(qū)預(yù)留這部分空間。

當(dāng)你在 Navicat 中創(chuàng)建分區(qū)表時，可以忽略這個默認分區(qū)，因為它只是用來處理無法匹配到其他分區(qū)的數(shù)據(jù)。如果你沒有自定義默認分區(qū)的話，MySQL 會自動將這些數(shù)據(jù)放入默認分區(qū)中。

請注意，這個 "50100" 的大小是 MySQL 的默認值，如果你在 MySQL配置中更改了默認值，那么在 Navicat 中創(chuàng)建分區(qū)表時，預(yù)留的空間大小可能會有所不同。

總結(jié)來說，Navicat在創(chuàng)建分區(qū)表時會預(yù)留一部分空間用于默認分區(qū)，這是正常的行為，不需要過多關(guān)注。

11、mysql 復(fù)制表

非同庫實現(xiàn)方案

在目標數(shù)據(jù)庫中創(chuàng)建一個與源表結(jié)構(gòu)相同的新表：

CREATE TABLE 目標庫名.新表名 LIKE 源庫名.原表名;

這將在目標數(shù)據(jù)庫中創(chuàng)建一個名為 “新表名” 的新表，其結(jié)構(gòu)與源數(shù)據(jù)庫中的 “原表名” 相同。

將源表的數(shù)據(jù)插入到目標表中：

INSERT INTO 目標庫名.新表名 SELECT * FROM 源庫名.原表名;

這將從源表中選擇所有數(shù)據(jù)，并將其插入到目標表中。

同庫復(fù)制表

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

12、mysql 8.0 以下 truncate 分區(qū)表鎖表

在MySQL 5.7.30(系統(tǒng)版本)及更早版本中，使用 TRUNCATE TABLE命令對分區(qū)表進行操作時會鎖定整個表，這可能導(dǎo)致其他會話在執(zhí)行期間被阻塞。

• 1.使用 DELETE 命令替代 TRUNCATE：如果 TRUNCATE TABLE 操作會導(dǎo)致表鎖定問題，可以考慮改用 DELETE FROM命令來刪除表中的所有行。DELETE命令是逐行刪除的，因此不會鎖定整個表。請注意，DELETE命令在刪除大量數(shù)據(jù)時可能效率較低，因為它會記錄日志和生成回滾段。

• 2.分段 TRUNCATE：將大的分區(qū)表拆分成多個較小的分區(qū)，然后分別執(zhí)行 TRUNCATE TABLE 命令。這樣可以減少鎖定的粒度，并降低對整個表的鎖定時間。但是，這種方法需要重構(gòu)分區(qū)表結(jié)構(gòu)，可能會造成一些額外的工作。

• 3.升級到MySQL 8.0或更高版本：MySQL 8.0引入了一項重要的改進，即針對TRUNCATE TABLE命令的分區(qū)鎖定進行了優(yōu)化。在MySQL 8.0及更高版本中，TRUNCATE PARTITION 語法可用于僅清空特定分區(qū)而不鎖定整個表。因此，升級到MySQL 8.0或更高版本可能是一個解決方案。

不升級mysql、truncate方案

如果你不能升級MySQL版本，但仍然希望在線執(zhí)行 TRUNCATE PARTITION 操作并避免鎖表，可以考慮以下方法：

使用分區(qū)交換：將要清空的分區(qū)與一個空分區(qū)進行交換。這樣可以實現(xiàn)快速清空分區(qū)的效果，而不會鎖定整個表。具體步驟如下：

• 1.創(chuàng)建一個空的臨時分區(qū)，可以是已存在的空分區(qū)或者新創(chuàng)建的分區(qū)。
  使用 ALTER TABLE 進行分區(qū)交換操作，將要清空的分區(qū)與空分區(qū)進行交換，例如：

ALTER TABLE your_table EXCHANGE PARTITION p_to_truncate WITH TABLE empty_partition;

這個操作是原子的，并且不會鎖定整個表。
最后，刪除交換后的空分區(qū)。
通過使用分區(qū)交換，你可以在不鎖定整個表的情況下快速清空指定的分區(qū)。

• 2.使用臨時表(離線)：將要清空的分區(qū)數(shù)據(jù)復(fù)制到一個臨時表中，并通過 RENAME 操作進行切換。具體步驟如下：

創(chuàng)建一個臨時表，結(jié)構(gòu)與原分區(qū)表相同。
使用 INSERT INTO ... SELECT 將要清空的分區(qū)數(shù)據(jù)復(fù)制到臨時表中。
使用 RENAME TABLE 進行表名切換，將原分區(qū)表重命名為備份表，將臨時表重命名為原分區(qū)表的名稱。
最后，刪除備份表。
這種方法需要一定的額外存儲空間來保存臨時表和備份表，但可以實現(xiàn)在線清空分區(qū)而不鎖定整個表。

到此這篇關(guān)于MySQL 分區(qū)表的詳細介紹的文章就介紹到這了,更多相關(guān)MySQL 分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評論