Hướng dẫn về Partitioning trong MySQL

Partitioning trong cơ sở dữ liệu MySQL là một phương pháp phân chia một bảng lớn thành các phần nhỏ hơn, được gọi là partition. Điều này giúp tối ưu hóa hiệu suất khi làm việc với lượng dữ liệu lớn, vì dữ liệu có thể được truy vấn hoặc quản lý dễ dàng hơn khi được phân đoạn theo các tiêu chí nhất định.

1. Các kiểu phân chia dữ liệu (Partitioning Types):

MySQL hỗ trợ các loại partitioning chính sau:

• Range Partitioning: Dữ liệu được chia dựa trên một khoảng giá trị.
• List Partitioning: Dữ liệu được chia dựa trên một danh sách giá trị.
• Hash Partitioning: Dữ liệu được chia ngẫu nhiên dựa trên giá trị hash của một cột.
• Key Partitioning: Giống với hash nhưng sử dụng chức năng hash của MySQL.

PARTITION có tạo bảng mới không?

Partitioning trong MySQL không tạo ra các bảng mới theo nghĩa tách biệt hoàn toàn. Thay vào đó, partition chia nhỏ dữ liệu của một bảng duy nhất thành các phần (partition) dựa trên các quy tắc đã định nghĩa (như Range, List, Hash, hoặc Key). Các phần này tồn tại trong cùng một bảng, nhưng được lưu trữ và quản lý riêng biệt ở cấp độ hệ thống để tối ưu hóa hiệu suất.

Tóm lại:

• Partition không tạo ra bảng mới mà chỉ chia nhỏ dữ liệu của một bảng duy nhất thành nhiều phần.
• Người dùng vẫn tương tác với bảng gốc như một bảng thông thường.
• MySQL tự động quản lý việc lưu trữ và truy vấn dữ liệu trong các partition khác nhau, tùy theo cách chia mà bạn định nghĩa.

Ví dụ:

Khi bạn tạo một bảng với partitioning, bảng này vẫn được xem là một bảng duy nhất. Các thao tác truy vấn, thêm, sửa hoặc xóa dữ liệu trên bảng đó vẫn diễn ra bình thường. Tuy nhiên, MySQL sẽ biết cách xử lý dữ liệu sao cho hiệu quả hơn bằng cách chỉ làm việc với các partition liên quan khi có truy vấn.

2. Ví dụ chi tiết về thiết kế Partitioning:

2.1 Range Partitioning (Phân chia dựa trên một khoảng giá trị):

Giả sử chúng ta có một bảng orders lưu trữ thông tin về các đơn hàng, và cột order_date là ngày đặt hàng. Bảng này sẽ được chia ra thành các partition theo từng năm để tối ưu hiệu suất khi tìm kiếm đơn hàng theo thời gian. Chúng ta sẽ chia bảng theo khoảng thời gian của cột order_date dựa trên từng năm.

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

• Ở đây, dữ liệu được chia thành các partition theo năm dựa trên YEAR(order_date).
• Các partition p2020, p2021, p2022, và p2023 sẽ chứa dữ liệu từ năm 2020 đến 2023 tương ứng.

Ví dụ thêm dữ liệu vào bảng orders:

Khi thêm dữ liệu vào bảng, MySQL sẽ tự động phân phối dữ liệu vào các partition tương ứng.

INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (1, 101, '2021-05-15', 250.00),
       (2, 102, '2020-08-10', 320.00),
       (3, 103, '2022-02-21', 450.00);

Dữ liệu với order_date = '2021-05-15' sẽ nằm trong partition p2021.

Dữ liệu với order_date = '2020-08-10' sẽ nằm trong partition p2020.

Dữ liệu với order_date = '2022-02-21' sẽ nằm trong partition p2022.

Lợi ích của Partitioning:

Cải thiện hiệu suất truy vấn: Nếu bạn chỉ truy vấn các đơn hàng trong năm 2021, MySQL chỉ cần truy vấn partition p2021 mà không cần duyệt qua các partition khác. Ví dụ:

SELECT * FROM orders WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';

Quản lý dữ liệu dễ dàng hơn: Bạn có thể dễ dàng xóa dữ liệu của năm 2020 mà không cần xóa từng dòng, chỉ cần xóa partition p2020.

ALTER TABLE orders DROP PARTITION p2020;

Tối ưu lưu trữ: MySQL chỉ cần tải các partition liên quan vào bộ nhớ, giúp tiết kiệm tài nguyên hệ thống khi làm việc với dữ liệu lớn.

Range Partitioning với nhiều khoảng hơn:

Khi bạn muốn sử dụng Range Partitioning với các khoảng thời gian hoặc giá trị cụ thể khác, bạn có thể thêm bao nhiêu partition tùy ý, miễn là chúng không bị trùng lặp và bao phủ toàn bộ phạm vi dữ liệu mà bạn cần.

Ví dụ chi tiết về Range Partitioning:

Giả sử bạn muốn chia bảng đơn hàng orders theo từng năm từ 2020 đến 2024, bạn có thể thêm partition cho năm 2024 và năm 2025.

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

Mô tả chi tiết từng partition:

• Partition p2020: Chứa các đơn hàng từ ngày 2020-01-01 đến 2020-12-31.
• Partition p2021: Chứa các đơn hàng từ ngày 2021-01-01 đến 2021-12-31.
• Partition p2022: Chứa các đơn hàng từ ngày 2022-01-01 đến 2022-12-31.
• Partition p2023: Chứa các đơn hàng từ ngày 2023-01-01 đến 2023-12-31.
• Partition p2024: Chứa các đơn hàng từ ngày 2024-01-01 đến 2024-12-31.

Thêm dữ liệu vào bảng:

Khi thêm dữ liệu vào bảng này, MySQL sẽ tự động phân bổ dữ liệu vào partition tương ứng dựa trên YEAR(order_date).

INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (4, 104, '2024-03-22', 500.00),
       (5, 105, '2023-11-15', 600.00),
       (6, 106, '2025-01-02', 750.00);  -- Sẽ bị lỗi nếu không có partition cho 2025

Xử lý partition cho năm 2025:

Để tránh lỗi khi thêm đơn hàng cho năm 2025, bạn có thể thêm partition cho năm 2025.

ALTER TABLE orders
ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

Giờ đây, bạn có thể thêm dữ liệu cho năm 2025 mà không gặp lỗi:

INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (6, 106, '2025-01-02', 750.00);

Xoá partition cũ khi không cần nữa:

Khi bạn không cần dữ liệu từ năm 2020, bạn có thể xóa partition tương ứng để tiết kiệm tài nguyên:

ALTER TABLE orders
DROP PARTITION p2020;

2.2 Hash Partitioning (Phân chia ngẫu nhiên theo hash):

Nếu bạn muốn phân chia dữ liệu dựa trên customer_id và sử dụng hash để phân phối dữ liệu ngẫu nhiên vào các partition, bạn có thể làm như sau:

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (order_id)
)
PARTITION BY HASH(customer_id) PARTITIONS 4;

Dữ liệu sẽ được phân chia vào 4 partition dựa trên giá trị hash của customer_id.

2.3 List Partitioning (Phân chia theo giá trị rời rạc)

List Partitioning trong MySQL là một kỹ thuật phân chia bảng dựa trên một danh sách các giá trị rời rạc thay vì một khoảng giá trị như trong Range Partitioning. Mỗi partition sẽ chứa dữ liệu khớp với một hoặc nhiều giá trị cụ thể. Đây là một kỹ thuật rất hữu ích khi bạn muốn phân vùng dữ liệu theo các danh mục không có tính tuần tự hoặc không liên tục, chẳng hạn như các vùng địa lý, mã quốc gia, hoặc loại sản phẩm.

Cách hoạt động của List Partitioning:

Trong List Partitioning, bạn chỉ định các giá trị cụ thể cho mỗi partition. Điều này rất hữu ích khi bạn muốn phân loại dữ liệu dựa trên các danh mục rõ ràng, chẳng hạn như mã quốc gia, loại sản phẩm, hoặc khu vực địa lý.

Ví dụ thiết kế List Partitioning:

Giả sử chúng ta có một bảng orders lưu trữ thông tin đơn hàng và chúng ta muốn phân chia bảng này theo vùng địa lý, nơi mỗi vùng được đại diện bằng một giá trị region_id.

Tạo bảng với List Partitioning:

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    region_id INT NOT NULL,  -- Mã vùng địa lý
    total_amount DECIMAL(10,2),
    PRIMARY KEY (order_id)
)
PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1),  -- Miền Bắc
    PARTITION p_central VALUES IN (2),  -- Miền Trung
    PARTITION p_south VALUES IN (3),  -- Miền Nam
    PARTITION p_other VALUES IN (4, 5)  -- Khu vực khác
);

Partition p_north sẽ chứa các bản ghi có region_id = 1 (đại diện cho miền Bắc).

Partition p_central chứa các bản ghi có region_id = 2 (miền Trung).Partition p_south chứa các bản ghi có region_id = 3 (miền Nam).

Partition p_other chứa các bản ghi có region_id = 4 hoặc region_id = 5 (các khu vực khác).

Thêm dữ liệu vào bảng:

INSERT INTO orders (order_id, customer_id, order_date, region_id, total_amount)
VALUES (1, 101, '2023-09-01', 1, 500.00),  -- Đơn hàng ở miền Bắc
       (2, 102, '2023-09-05', 3, 750.00),  -- Đơn hàng ở miền Nam
       (3, 103, '2023-09-10', 4, 300.00);  -- Đơn hàng ở khu vực khác

Dữ liệu có region_id = 1 sẽ được lưu trữ trong partition p_north.

Dữ liệu có region_id = 3 sẽ được lưu trữ trong partition p_south.

Dữ liệu có region_id = 4 sẽ được lưu trữ trong partition p_other.

Lợi ích của List Partitioning:

• Quản lý dữ liệu dễ dàng hơn: Bạn có thể quản lý các nhóm dữ liệu riêng biệt dựa trên các danh mục.
• Tối ưu truy vấn: Truy vấn dữ liệu liên quan đến một giá trị cụ thể sẽ chỉ quét partition liên quan, giúp giảm thời gian truy vấn.

Ví dụ, nếu bạn chỉ cần dữ liệu từ miền Bắc (region_id = 1), MySQL sẽ chỉ truy vấn partition p_north:

Lưu ý khi sử dụng List Partitioning:

• Giá trị NULL không tự động được phân vùng: Nếu bạn không chỉ định partition cho giá trị NULL, MySQL sẽ không biết lưu giá trị NULL vào đâu và gây ra lỗi.
• Giới hạn về số lượng partition: MySQL có giới hạn số lượng partition tối đa (thường là 8192 partition).
• Dữ liệu có thể bị phân mảnh: Khi dữ liệu không được phân phối đều qua các partition, một partition có thể chứa nhiều dữ liệu hơn những partition khác, gây ra hiện tượng mất cân bằng.

List Partitioning rất hữu ích trong việc quản lý các bảng lớn có dữ liệu liên quan đến các danh mục hoặc nhóm phân loại rõ ràng. Nó giúp cải thiện hiệu suất và đơn giản hóa việc quản lý dữ liệu khi làm việc với các nhóm dữ liệu rời rạc.

2.4 Key Partitioning (dạng đặc biệt của Hash Partitioning)

Key Partitioning trong MySQL là một dạng đặc biệt của Hash Partitioning, trong đó việc phân chia bảng dựa trên một giá trị được băm (hash) từ một hoặc nhiều cột của bảng. MySQL sử dụng một thuật toán băm nội bộ để phân phối dữ liệu vào các partition khác nhau, dựa trên giá trị của cột hoặc các cột được chỉ định.

Cách hoạt động của Key Partitioning:

• Trong Key Partitioning, MySQL sử dụng giá trị của cột hoặc các cột được chỉ định, áp dụng hàm băm nội bộ của hệ thống để tính toán và gán dữ liệu vào các partition tương ứng.
• Không giống như Hash Partitioning, Key Partitioning không cho phép bạn chỉ định hàm băm của riêng mình. Thay vào đó, MySQL sẽ tự động chọn hàm băm dựa trên thuật toán nội bộ.

Khi nào sử dụng Key Partitioning?

• Key Partitioning thích hợp khi bạn muốn phân vùng dữ liệu một cách ngẫu nhiên nhưng vẫn dựa trên các giá trị của một hoặc nhiều cột trong bảng.
• Được sử dụng khi không có khoảng giá trị cụ thể hoặc danh sách giá trị cố định để chia dữ liệu, mà muốn chia dựa trên giá trị băm của một cột.

Ví dụ thiết kế bảng với Key Partitioning:

Giả sử chúng ta có một bảng users lưu trữ thông tin người dùng. Chúng ta sẽ sử dụng Key Partitioning để phân chia dữ liệu dựa trên cột user_id.

Tạo bảng với Key Partitioning:

CREATE TABLE users (
    user_id INT NOT NULL,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at DATE,
    PRIMARY KEY (user_id)
)
PARTITION BY KEY(user_id)  -- Phân vùng dựa trên cột user_id
PARTITIONS 4;  -- Chia bảng thành 4 partition

• Bảng users sẽ được chia thành 4 partition (p0, p1, p2, p3).
• Dữ liệu sẽ được phân chia vào các partition dựa trên giá trị user_id và một thuật toán băm nội bộ của MySQL.

Thêm dữ liệu vào bảng:

INSERT INTO users (user_id, username, email, created_at)
VALUES (1, 'john_doe', '[email protected]', '2023-08-01'),
       (2, 'jane_doe', '[email protected]', '2023-08-02'),
       (3, 'alice_smith', '[email protected]', '2023-08-03'),
       (4, 'bob_jones', '[email protected]', '2023-08-04');

• Dữ liệu với user_id = 1 có thể rơi vào partition p0, user_id = 2 vào partition p1, và tương tự cho các giá trị khác. Tuy nhiên, thứ tự phân chia vào partition nào sẽ do hàm băm nội bộ của MySQL quyết định.

Lợi ích của Key Partitioning:

1. Phân chia dữ liệu đồng đều: Vì hàm băm nội bộ của MySQL được thiết kế để phân phối dữ liệu đều đặn giữa các partition, điều này giúp giảm thiểu hiện tượng mất cân bằng dữ liệu giữa các partition.
2. Không cần quản lý thủ công: Với Key Partitioning, bạn không cần phải tự xác định danh sách giá trị hoặc khoảng giá trị. MySQL sẽ tự động phân phối dữ liệu dựa trên giá trị của cột khóa và thuật toán băm.
3. Tối ưu cho các bảng lớn: Khi số lượng bản ghi tăng lên, việc phân chia bảng thành nhiều partition giúp tối ưu hóa hiệu suất truy vấn và quản lý bảng.

Truy vấn và thao tác với partition trong Key Partitioning:

Truy vấn dữ liệu trong bảng có Key Partitioning không khác gì so với các bảng không phân vùng. Bạn không cần chỉ định partition trong câu lệnh SQL, MySQL sẽ tự động tìm partition chứa dữ liệu dựa trên hàm băm.

Ví dụ truy vấn:

SELECT * FROM users WHERE user_id = 2;

MySQL sẽ tự động tính toán hàm băm của user_id = 2 và truy vấn đúng partition chứa dữ liệu tương ứng.

Thêm hoặc xóa partition trong Key Partitioning:

Khi dữ liệu trong bảng tăng lên, bạn có thể thêm partition để quản lý dữ liệu hiệu quả hơn:

Thêm partition:

ALTER TABLE users
ADD PARTITION PARTITIONS 6;

• Bảng users sẽ được chia thành 6 partition thay vì 4 partition ban đầu. MySQL sẽ tự động phân phối lại dữ liệu vào các partition mới khi cần.

Xóa partition:

Bạn cũng có thể xóa partition nếu cần, nhưng lưu ý rằng dữ liệu trong partition bị xóa sẽ bị mất.

ALTER TABLE users
COALESCE PARTITION 2;  -- Giảm số lượng partition xuống còn 2

Lưu ý khi sử dụng Key Partitioning:

• Không chỉ định hàm băm: Khác với Hash Partitioning, bạn không thể chọn hàm băm của riêng mình, MySQL sẽ tự động chọn hàm băm nội bộ.
• Hiệu suất phụ thuộc vào số partition: Số lượng partition cần phải đủ lớn để cân bằng dữ liệu, nhưng không nên quá nhiều vì sẽ gây quá tải hệ thống.
• Không tương thích với các bảng có khóa ngoại (foreign key): MySQL không hỗ trợ partitioning trên các bảng có ràng buộc khóa ngoại.

Key Partitioning là một kỹ thuật hữu ích khi bạn cần phân chia dữ liệu một cách ngẫu nhiên nhưng vẫn dựa trên các giá trị của cột. Nó đặc biệt phù hợp cho các bảng lớn khi không có phạm vi giá trị rõ ràng để chia, và việc phân phối dữ liệu dựa trên thuật toán băm nội bộ của MySQL giúp giảm thiểu sự mất cân bằng giữa các partition.

Partitioning trong MySQL là một giải pháp mạnh mẽ giúp chia một bảng lớn thành nhiều phần nhỏ hơn, tối ưu hóa hiệu suất truy vấn và quản lý dữ liệu dễ dàng. Bằng cách phân chia dữ liệu dựa trên các quy tắc như phạm vi giá trị (Range), danh sách giá trị (List), hoặc thuật toán băm (Hash, Key), Partitioning giúp giảm tải hệ thống, tăng tốc độ xử lý và cải thiện hiệu quả bảo trì dữ liệu. Đây là một công cụ quan trọng cho các hệ thống xử lý dữ liệu lớn và cần tối ưu hóa hiệu suất hoạt động.

• MySQL
• Partition MySQL
• Partitioning