萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL 分區表實戰詳解:從原理到管理操作的完整指南

分區表 vs 分表:本質區別

維度分區表分表
邏輯視圖單表多表
應用改造無感知 (透明)需修改 SQL/路由邏輯
管理操作DDL 秒級 (ADD/DROP PARTITION)應用層遷移數據
索引局部索引 (每分區獨立)全局索引需自建
跨分區查詢優化器自動裁剪應用層 UNION

分區類型完整語法 (含 5.5+ COLUMNS 增強)

1. RANGE 分區 —— 歷史數據歸檔首選


CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

2. RANGE COLUMNS (5.5+) —— 直接用 DATE 列,無需函數


CREATE TABLE sales_v2 (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE COLUMNS (sale_date) (
    PARTITION p2023_q1 VALUES LESS THAN ('2023-04-01'),
    PARTITION p2023_q2 VALUES LESS THAN ('2023-07-01'),
    PARTITION p2023_q3 VALUES LESS THAN ('2023-10-01'),
    PARTITION p2023_q4 VALUES LESS THAN ('2024-01-01')
);

3. LIST 分區 —— 業務枚舉分組


CREATE TABLE orders (
    id INT,
    region_id INT
)
PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1,2,3),
    PARTITION p_south VALUES IN (4,5,6),
    PARTITION p_other VALUES IN (7,8,9,0)
);

4. LIST COLUMNS (5.5+) —— 直接用字符串枚舉


CREATE TABLE products (
    id INT,
    category VARCHAR(20)
)
PARTITION BY LIST COLUMNS (category) (
    PARTITION p_electronics VALUES IN ('phone','laptop','tablet'),
    PARTITION p_clothing VALUES IN ('shirt','pants','shoes')
);

5. HASH 分區 —— 均勻分布熱點數據


CREATE TABLE logs (
    id INT,
    created_at DATETIME
)
PARTITION BY HASH (MONTH(created_at))
PARTITIONS 12;

6. KEY 分區 —— MySQL 內部哈希 (支持非整型)


CREATE TABLE sessions (
    session_id CHAR(32),
    user_id INT
)
PARTITION BY KEY (user_id)
PARTITIONS 8;

分區前提檢查


-- 確認實例支持分區
SHOW VARIABLES LIKE 'have_partition_engine';
-- 或 5.7+
SHOW PLUGINS WHERE Name='partition';

分區管理操作實戰

新增分區 (預創建未來分區)


ALTER TABLE sales ADD PARTITION (PARTITION p2024 VALUES LESS THAN (2025));

刪除分區 (數據同步刪除,不可恢復)


ALTER TABLE sales DROP PARTITION p2020;

合併/重組分區 (季度合併為半年)


ALTER TABLE sales REORGANIZE PARTITION p2023_q1,p2023_q2,p2023_q3 INTO (
    PARTITION p2023_h1 VALUES LESS THAN ('2023-07-01'),
    PARTITION p2023_h2 VALUES LESS THAN ('2024-01-01')
);

查看分區信息


SELECT TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS, DATA_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA='db_name' AND TABLE_NAME='sales';

驗證分區裁剪 (關鍵性能指標)


EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';
-- partitions 列應僅顯示 p2023_q1
-- 若顯示 ALL/多個分區 → SQL 未命中分區裁剪,需改寫

避坑指南 (血淚總結)

  1. ❌ 分區鍵不在 WHERE 中 → 全分區掃描,性能反而比不分區差
  2. ❌ 分區列允許 NULL → NULL 值擠入最小分區,嚴重傾斜,**必須 NOT NULL**
  3. ❌ 分區鍵不在主鍵/唯一鍵中 → ERROR 1503,**調整主鍵包含分區鍵**
  4. ❌ 過度分區 (如按天分區 10 年 = 3650 分區) → 元數據開銷大、打開表慢,**單分區 1-5千萬行**
  5. ❌ 外鍵約束 → 分區表不支持 FK,業務層保證
  6. ✅ 覆蓋索引包含分區鍵 → 索引下推更高效
  7. ✅ 定時任務預創建未來分區、歸檔歷史分區
copyright © 萬盛學電腦網 all rights reserved