分區表和分表

分區表（Partitioning）

定義

分區表是將單個表的數據按照某種規則（如范圍、列表、哈希等）劃分為多個邏輯部分，每個部分稱為一個分區。
數據仍然存儲在一個物理表中，但邏輯上被分割為多個分區。

特點

邏輯劃分：分區是對表內數據的邏輯劃分，物理上仍然是一個表。
透明性：對用戶來說，分區表的操作與普通表無異，無需修改 SQL 查詢。
存儲位置：分區可以存儲在不同的物理位置（如不同的磁盤），以提升 I/O 性能。

常見分區方式

1. 范圍分區（RANGE Partitioning）

定義：按字段值范圍劃分分區。
特點：
- 適合連續范圍的數據（如時間、數值）。
- 需要指定每個分區的上限值。
示例：

CREATE TABLE sales (sale_id INT NOT NULL,sale_date DATE NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025)
);

2. 列表分區（LIST Partitioning）

定義：按字段的離散值劃分分區。
特點：
- 適合固定分類的數據（如地區、狀態）。
- 每個分區明確列出允許的值。
示例：

CREATE TABLE customers (customer_id INT NOT NULL,region VARCHAR(50) NOT NULL
)
PARTITION BY LIST COLUMNS(region) (PARTITION p_north VALUES IN ('North'),PARTITION p_south VALUES IN ('South'),PARTITION p_east VALUES IN ('East'),PARTITION p_west VALUES IN ('West')
);

3. 哈希分區（HASH Partitioning）

定義：按字段的哈希值將數據分布到多個分區。
特點：
- 數據均勻分布，適合負載均衡。
- 分區數量由 PARTITIONS 指定。
示例：

CREATE TABLE users (user_id INT NOT NULL,username VARCHAR(50)
)
PARTITION BY HASH(user_id)
PARTITIONS 4;

4. 鍵分區（KEY Partitioning）

定義：使用 MySQL 內部哈希函數對字段進行分區。
特點：
- 類似于哈希分區，但支持非整數字段。
- 自動選擇分區鍵。
示例：

CREATE TABLE products (product_id INT NOT NULL,product_name VARCHAR(50)
)
PARTITION BY KEY(product_name)
PARTITIONS 4;

5. 范圍列分區（RANGE COLUMNS Partitioning）

定義：按一個或多個字段直接分區，無需表達式。
特點：
- 支持多列分區。
- 適合日期、時間等字段。
示例：

CREATE TABLE orders (order_id INT NOT NULL,create_time DATETIME NOT NULL
)
PARTITION BY RANGE COLUMNS(create_time) (PARTITION p2022 VALUES LESS THAN ('2023-01-01'),PARTITION p2023 VALUES LESS THAN ('2024-01-01'),PARTITION p2024 VALUES LESS THAN ('2025-01-01')
);

6. 列表列分區（LIST COLUMNS Partitioning）

定義：按一個或多個字段的離散值分區。
特點：
- 支持多列分區。
- 適合多字段分類場景。
示例：

CREATE TABLE tasks (task_id INT NOT NULL,priority INT NOT NULL,region VARCHAR(50) NOT NULL
)
PARTITION BY LIST COLUMNS(priority, region) (PARTITION p_high_north VALUES IN ((5, 'North'), (10, 'North')),PARTITION p_low_south VALUES IN ((3, 'South'))
);

7. 子分區（Subpartitioning）

定義：在主分區的基礎上進一步劃分子分區。
特點：
- 支持組合分區（如 RANGE + HASH 或 LIST + HASH）。
- 提供更細粒度的分區管理。
示例：

CREATE TABLE orders (order_id INT NOT NULL,order_date DATE NOT NULL,customer_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH(customer_id) (PARTITION p2022 VALUES LESS THAN (2023) (SUBPARTITION p2022_sp1,SUBPARTITION p2022_sp2),PARTITION p2023 VALUES LESS THAN (2024) (SUBPARTITION p2023_sp1,SUBPARTITION p2023_sp2)
);

總結對比

分區方式	特點	示例字段類型
范圍分區	按連續范圍分區	數值、日期
列表分區	按離散值分區	地區、狀態
哈希分區	按哈希值均勻分布	用戶 ID、產品 ID
鍵分區	使用 MySQL 內部哈希函數	字符串、其他字段
范圍列分區	按字段直接分區，支持多列	日期、時間
列表列分區	按字段離散值分區，支持多列	分類字段
子分區	主分區基礎上進一步劃分子分區	組合分區（RANGE+HASH）

優點

提升查詢性能：查詢時只需掃描相關分區，減少 I/O 開銷。
簡化管理：可以通過刪除整個分區快速清理歷史數據。
支持大表：適合單表數據量較大的場景。

缺點

單表限制：分區表本質上仍然是一個表，不能解決跨表查詢的問題。
分區規則固定：一旦分區規則確定，后續調整較復雜。

分表（Sharding）

MySQL 本身不直接支持分表，需要通過應用層或中間件實現

定義

分表是將一個大表拆分為多個獨立的物理表，每個表存儲一部分數據。
數據分布在多個表中，通常需要通過應用層或中間件來管理。

特點

物理分離：分表是對數據的物理分離，每個表是一個獨立的實體。
分布式架構：分表通常與分布式數據庫結合使用。
手動管理：需要開發人員或中間件負責路由查詢到正確的表。

常見分表方式

(1) 水平分表（Horizontal Sharding）

按行拆分，每張表存儲部分記錄。例如，按用戶 ID 范圍分表：
- users_0 存儲用戶 ID 為 0-999 的記錄。
- users_1 存儲用戶 ID 為 1000-1999 的記錄。
示例：按用戶 ID 范圍分表。

手動創建分表

-- 表1：存儲用戶ID為0-999的記錄
CREATE TABLE users_0 (user_id INT NOT NULL,username VARCHAR(50)
);-- 表2：存儲用戶ID為1000-1999的記錄
CREATE TABLE users_1 (user_id INT NOT NULL,username VARCHAR(50)
);

插入數據

根據用戶 ID 決定插入哪個表：

-- 用戶ID為500，插入users_0
INSERT INTO users_0 (user_id, username) VALUES (500, 'Alice');-- 用戶ID為1500，插入users_1
INSERT INTO users_1 (user_id, username) VALUES (1500, 'Bob');

查詢數據

需要手動路由查詢：

-- 查詢用戶ID為500
SELECT * FROM users_0 WHERE user_id = 500;-- 查詢用戶ID為1500
SELECT * FROM users_1 WHERE user_id = 1500;

(2) 垂直分表（Vertical Sharding）

按列拆分，將不同的字段分布到不同的表中。例如：
- users_basic 存儲用戶的基本信息（ID、姓名）。
- users_profile 存儲用戶的詳細信息（地址、興趣愛好）。
示例：將用戶的基本信息和詳細信息分開。

創建分表

-- 基本信息表
CREATE TABLE users_basic (user_id INT NOT NULL,username VARCHAR(50)
);-- 詳細信息表
CREATE TABLE users_profile (user_id INT NOT NULL,address VARCHAR(200),phone VARCHAR(20)
);

插入數據

分別插入基本信息和詳細信息：

INSERT INTO users_basic (user_id, username) VALUES (1, 'Alice');
INSERT INTO users_profile (user_id, address, phone) VALUES (1, '123 Main St', '123-456-7890');

查詢數據

需要聯表查詢：

SELECT b.user_id, b.username, p.address, p.phone
FROM users_basic b
JOIN users_profile p ON b.user_id = p.user_id
WHERE b.user_id = 1;

優點

擴展性強：支持水平擴展，適合超大規模數據集。
提升性能：減少單表數據量，降低查詢和寫入壓力。
靈活性高：可以根據業務需求靈活設計分表策略。

缺點

管理復雜：需要額外的邏輯處理跨表查詢和事務。
數據一致性：分布式環境下的數據一致性和事務管理更復雜。
查詢復雜度增加：需要手動處理表路由和聚合操作。

對比總結

特性	分區表	分表
數據存儲	單表內邏輯分區	多個獨立物理表
實現方式	數據庫內置功能	應用層或中間件實現
透明性	對用戶透明，無需修改查詢	需要手動處理表路由和查詢
擴展性	有限，依賴單實例的硬件資源	強，支持分布式架構
適用場景	單表數據量較大，但仍在單機范圍內	數據量極大，需分布式存儲和計算
跨表查詢	不涉及，仍是單表	需要額外處理
事務支持	數據庫原生支持	分布式事務需額外實現

使用場景示例

分區表

日志系統：按日期范圍分區，方便清理歷史數據。

CREATE TABLE logs (log_id INT,log_time DATETIME
) PARTITION BY RANGE (YEAR(log_time)) (PARTITION p2022 VALUES LESS THAN (2023),PARTITION p2023 VALUES LESS THAN (2024)
);