MySQL - 水平分割槽

---

---

MySQL 的分割槽是一種可用於將資料庫表劃分為較小表（即分割槽）的技術。這些較小的表儲存在不同的物理位置，並被視為單獨的表。因此，可以單獨訪問和管理這些較小表中的資料。

但請注意，即使單獨管理較小表的資料，它們也不是獨立的表；也就是說，它們仍然是主表的一部分。

MySQL 中有兩種分割槽形式：水平分割槽和垂直分割槽。

MySQL 水平分割槽

MySQL 的水平分割槽用於將錶行劃分為多個分割槽。由於它劃分的是行，因此每個分割槽中都會存在所有列。可以單獨或集體訪問所有分割槽。

MySQL 水平分割槽方法有幾種型別：

MySQL 範圍分割槽

MySQL RANGE 分割槽用於根據特定列值的特定範圍將表劃分為分割槽。每個表分割槽包含列值落在該定義範圍內的行。

示例

讓我們建立一個名為 CUSTOMERS 的表，並使用“PARTITION BY RANGE”子句根據 AGE 列將其劃分為四個分割槽：P1、P2、P3 和 P4：

CREATE TABLE CUSTOMERS(
   ID int not null,
   NAME varchar(40) not null,
   AGE int not null,
   ADDRESS char(25) not null,
   SALARY decimal(18, 2)
   )
   PARTITION BY RANGE (AGE) (
   PARTITION P1 VALUES LESS THAN (20),
   PARTITION P2 VALUES LESS THAN (30),
   PARTITION P3 VALUES LESS THAN (40),
   PARTITION P4 VALUES LESS THAN (50)
);

在這裡，我們將行插入到上面建立的表中：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 19, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 31, 'Mumbai', 6500.00 ),
(5, 'Hardik', 35, 'Bhopal', 8500.00 ),
(6, 'Komal', 47, 'MP', 4500.00 ),
(7, 'Muffy', 43, 'Indore', 10000.00 );

以下是獲得的CUSTOMERS 表：

ID	姓名	年齡	地址	工資
1	Ramesh	19	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	31	Mumbai	6500.00
5	Hardik	35	Bhopal	8500.00
6	Komal	47	MP	4500.00
7	Muffy	43	Indore	10000.00

現在，我們在 CUSTOMERS 表中有一些資料後，我們可以使用以下查詢顯示分割槽狀態，以檢視資料如何在分割槽之間分佈：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='CUSTOMERS';

上述查詢將向我們顯示每個分割槽中的行數。例如，P1 有 1 行，P2 有 2 行，P3 有 2 行，P4 有 2 行，如下所示：

分割槽名稱	錶行數
P1	1
P2	2
P3	2
P4	2

顯示分割槽：

我們還可以使用 PARTITION 子句顯示特定分割槽中的資料。例如，要從分割槽 P1 檢索資料，我們使用以下查詢：

SELECT * FROM CUSTOMERS PARTITION (p1);

它將顯示分割槽 P1 中的所有記錄：

ID	姓名	年齡	地址	工資
1	Ramesh	19	Ahmedabad	2000.00

類似地，我們可以使用相同的語法顯示其他分割槽。

處理範圍之外的資料：

如果我們嘗試將不在任何已定義分割槽範圍內的值插入 AGE 列，則會失敗並出現錯誤，如下所示：

INSERT INTO CUSTOMERS VALUES 
(8, 'Brahmi', 70, 'Hyderabad', 19000.00 );

以下是獲得的錯誤：

ERROR 1526 (HY000): Table has no partition for value 70

截斷分割槽：

如果需要，我們還可以透過截斷分割槽來管理它們。例如，要清空分割槽 P2，我們可以使用以下查詢：

ALTER TABLE CUSTOMERS TRUNCATE PARTITION p2;

獲得的輸出如下所示：

Query OK, 0 rows affected (0.03 sec)

這將刪除分割槽 P2 中的所有資料，使其為空，如下所示：

SELECT * FROM CUSTOMERS PARTITION (p2);

以下是生成的輸出：

Empty set (0.00 sec)

我們可以使用以下 SELECT 查詢驗證 CUSTOMERS 表：

SELECT * FROM CUSTOMERS;

我們可以在下表中看到屬於 p2 分割槽的行已被刪除：

ID	姓名	年齡	地址	工資
1	Ramesh	19	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
6	Komal	47	MP	4500.00
7	Muffy	43	Indore	10000.00

MySQL 列表分割槽

MySQL 列表分割槽用於根據特定列的離散值集將表劃分為分割槽。每個分割槽包含與定義集內特定值匹配的行。

示例

在此示例中，我們將建立一個名為 STUDENTS 的表，並根據“DEPARTMENT_ID”列使用“PARTITION BY LIST”子句將其劃分為四個分割槽 (P1、P2、P3 和 P4)：

CREATE TABLE STUDENTS(
   ID int,
   NAME varchar(50),
   DEPARTMENT varchar(50),
   DEPARTMENT_ID int
   )
   PARTITION BY LIST(DEPARTMENT_ID)(
   PARTITION P1 VALUES IN (3, 5, 6, 7, 9),
   PARTITION P2 VALUES IN (13, 15, 16, 17, 20),
   PARTITION P3 VALUES IN (23, 25, 26, 27, 30),
   PARTITION P4 VALUES IN (33, 35, 36, 37, 40)
);

在這裡，我們將行插入到上面建立的表中：

INSERT INTO STUDENTS VALUES 
(1, 'Ramesh', "cse", 5),
(2, 'Khilan', "mech", 20),
(3, 'kaushik', "ece", 17),
(4, 'Chaitali', "eee", 33),
(5, 'Hardik', "IT", 36),
(6, 'Komal', "Hotel management", 40),
(7, 'Muffy', "Fashion", 23);

以下是獲得的STUDENTS 表：

ID	姓名	系別	系別ID
1	Ramesh	cse	5
2	Khilan	機械工程	20
3	Kaushik	電子工程	17
7	Muffy	時尚設計	23
4	Chaitali	電子電氣工程	33
5	Hardik	資訊科技	36
6	Komal	酒店管理	40

我們可以使用以下查詢顯示 STUDENTS 表的 partition 狀態，檢視資料如何在各個 partition 中分佈：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='STUDENTS';

此查詢的輸出將顯示每個 partition 中的行數。例如，P1 有 1 行，P2 有 2 行，P3 有 1 行，P4 有 3 行：

分割槽名稱	錶行數
P1	1
P2	2
P3	1
P4	3

MySQL 雜湊分割槽

MySQL 的 HASH 分割槽是使用基於特定列的雜湊函式將表資料劃分為多個 partition。資料將在各個 partition 之間均勻分佈。

示例

在以下查詢中，我們使用 PARTITION BY HASH 子句建立名為 EMPLOYEES 的表，該表基於“id”列具有四個 partition：

CREATE TABLE EMPLOYEES (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   department VARCHAR(50) NOT NULL,
   salary INT NOT NULL
 )
   PARTITION BY HASH(id)
   PARTITIONS 4;

在這裡，我們將行插入到上面建立的表中：

INSERT INTO EMPLOYEES VALUES 
(1, 'Varun', 'Sales', 50000),
(2, 'Aarohi', 'Marketing', 60000),
(3, 'Paul', 'IT', 70000),
(4, 'Vaidhya', 'Finance', 80000),
(5, 'Nikhil', 'Sales', 55000),
(6, 'Sarah', 'Marketing', 65000),
(7, 'Tim', 'IT', 75000),
(8, 'Priya', 'Finance', 85000);

獲得的 EMPLOYEES 表如下：

id	姓名	部門	薪資
4	Vaidhya	財務	80000
8	Priya	財務	85000
1	Varun	銷售	50000
5	Nikhil	銷售	55000
2	Aarohi	市場營銷	60000
6	Sarah	市場營銷	65000
3	Paul	資訊科技	70000
7	Tim	資訊科技	75000

記錄根據“id”列在四個 partition 中均勻分佈。您可以使用以下 SELECT 查詢驗證 partition 狀態：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='EMPLOYEES';

獲得的表如下：

分割槽名稱	錶行數
P0	2
P1	2
P2	2
P3	2

關鍵分割槽

MySQL 的鍵分割槽是根據主鍵或唯一鍵的值將表資料劃分為多個 partition。

示例

在以下查詢中，我們建立名為 PERSON 的表，在“id”列上進行鍵分割槽。我們將表分為四個 partition，主鍵為“id”：

CREATE TABLE PERSON (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(50) NOT NULL,
   address VARCHAR(100) NOT NULL,
   PRIMARY KEY (id)
   )
   PARTITION BY KEY(id)
   PARTITIONS 4;

在這裡，我們將行插入到上面建立的表中：

INSERT INTO PERSON VALUES 
(1, 'Krishna', 'Krishna@tutorialspoint.com', 'Ayodhya'),
(2, 'Kasyap', 'Kasyap@tutorialspoint.com', 'Ayodhya'),
(3, 'Radha', 'Radha@tutorialspoint.com', 'Ayodhya'),
(4, 'Sarah', 'Sarah@tutorialspoint.com', 'Sri Lanka'),
(5, 'Sita', 'Sita@tutorialspoint.com', 'Sri Lanka'),
(6, 'Arjun', 'Arjun@tutorialspoint.com', 'India'),
(7, 'Hanuman', 'Hanuman@tutorialspoint.com', 'Sri Lanka'),
(8, 'Lakshman', 'Lakshman@tutorialspoint.com', 'Sri Lanka');

獲得的 PERSON 表如下：

id	姓名	郵箱	地址
1	Krishna	Krishna@tutorialspoint.com	阿約提亞
5	Sita	Sita@tutorialspoint.com	斯里蘭卡
4	Sarah	Sarah@tutorialspoint.com	斯里蘭卡
8	Lakshman	Lakshman@tutorialspoint.com	斯里蘭卡
3	Radha	Radha@tutorialspoint.com	阿約提亞
7	Hanuman	Hanuman@tutorialspoint.com	斯里蘭卡
2	Kasyap	Kasyap@tutorialspoint.com	阿約提亞
6	Arjun	Arjun@tutorialspoint.com	印度

同樣，資料根據“id”列在各個 partition 中均勻分佈，您可以使用以下查詢驗證 partition 狀態：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='PERSON';

獲得的輸出如下所示：

分割槽名稱	錶行數
P0	2
P1	2
P2	2
P3	2

MySQL 子分割槽

MySQL 的子分割槽用於根據另一列進一步劃分 partition，通常與其他分割槽方法（如 RANGE 或 HASH）結合使用。

示例

讓我們建立一個 CUSTOMER_ORDERS 表，在“order_date”列上進行 RANGE 分割槽，然後我們將根據“order_date”的月份進行雜湊子分割槽：

CREATE TABLE CUSTOMER_ORDERS (
   order_id INT NOT NULL,
   customer_name VARCHAR(50) NOT NULL,
   order_date DATE NOT NULL,
   order_status VARCHAR(20) NOT NULL
   )
   PARTITION BY RANGE (YEAR(order_date))
   SUBPARTITION BY HASH(MONTH(order_date))
   SUBPARTITIONS 2(
   PARTITION p0 VALUES LESS THAN (2022),
   PARTITION p1 VALUES LESS THAN (2023),
   PARTITION p2 VALUES LESS THAN (2024)
);

在這裡，我們將行插入到上面建立的表中：

INSERT INTO CUSTOMER_ORDERS VALUES 
(1, 'John', '2021-03-15', 'Shipped'),
(2, 'Bob', '2019-01-10', 'Delivered'),
(3, 'Johnson', '2023-01-10', 'Delivered'),
(4, 'Jake', '2020-01-10', 'Delivered'),
(5, 'Smith', '2022-05-01', 'Pending'),
(6, 'Rob', '2023-01-10', 'Delivered');

獲得的 CUSTOMERS_ORDERS 表如下：

訂單ID	客戶姓名	訂單日期	訂單狀態
1	John	2021-03-15	已發貨
2	Bob	2019-01-10	已送達
4	Jake	2020-01-10	已送達
5	Smith	2022-05-01	待處理
3	Johnson	2023-01-10	已送達
6	Rob	2023-01-10	已送達

您可以使用以下查詢顯示 CUSTOMER_ORDERS 表並驗證 partition 狀態：

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME='CUSTOMER_ORDERS';

獲得的表如下：

分割槽名稱	錶行數
P0	0
P0	3
P1	0
P1	1
P2	0
P2	2