I. Partitioning trong MySQL

Partitioning là cách chia nhỏ một bảng lớn thành nhiều phần nhỏ (gọi là partitions) để tăng tốc truy vấn và dễ quản lý dữ liệu hơn. Điều này đặc biệt hữu ích khi dữ liệu của bạn liên tục được cập nhật theo từng tháng, như trong các hệ thống đặt hàng, giao dịch hoặc logs.

---

1. Partitioning là gì?

Thay vì lưu tất cả dữ liệu vào một bảng lớn, bạn có thể chia nó thành từng phần dựa trên một cột nhất định, ví dụ như ngày tháng (order_date). Khi truy vấn dữ liệu của một tháng cụ thể, MySQL chỉ tìm kiếm trong partition đó thay vì toàn bộ bảng, giúp truy vấn nhanh hơn.

Ví dụ:

• Bảng orders có dữ liệu của năm 2024.
• Nếu bạn tìm kiếm đơn hàng của tháng 2/2024, MySQL chỉ quét partition của tháng đó mà không cần đọc dữ liệu tháng khác.

Ưu điểm của Partitioning

✅ Truy vấn nhanh hơn vì chỉ tìm trong partition cần thiết.
✅ Dễ dàng xóa dữ liệu cũ mà không làm ảnh hưởng đến dữ liệu mới.
✅ Giảm tải cho hệ thống khi cập nhật dữ liệu.

Nhược điểm

❌ Không hỗ trợ khóa ngoại (FOREIGN KEY).
❌ Không thể gộp các partition lại với nhau, chỉ có thể xóa hoặc thêm mới.
❌ Không phù hợp với bảng có ít dữ liệu (dưới 1 triệu dòng) vì có thể làm tăng độ phức tạp.

---

2. Cách Chia Partition Theo Tháng

Khi dữ liệu được cập nhật liên tục mỗi tháng, cách tốt nhất là sử dụng RANGE partitioning.

RANGE partitioning cho phép bạn chia bảng thành từng khoảng thời gian cụ thể, ví dụ mỗi partition sẽ chứa dữ liệu của một tháng.

---

3. Ví dụ Cụ Thể: Tạo Bảng orders Với Partition Theo Tháng

Giả sử bạn có bảng orders lưu thông tin đơn hàng với các cột:

• id: Mã đơn hàng
• order_date: Ngày đặt hàng
• customer_id: Mã khách hàng
• amount: Tổng tiền đơn hàng

Bạn muốn chia bảng này thành các partition theo từng tháng.

Tạo bảng với partitioning theo tháng

CREATE TABLE orders (
    id INT NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202401 VALUES LESS THAN (202402), -- Tháng 1/2024
    PARTITION p202402 VALUES LESS THAN (202403), -- Tháng 2/2024
    PARTITION p202403 VALUES LESS THAN (202404)  -- Tháng 3/2024
);

🔍 Giải thích:

• YEAR(order_date) * 100 + MONTH(order_date) chuyển ngày YYYY-MM-DD thành số YYYYMM.
  • Ví dụ: 2024-01-15 thành 202401.
• PARTITION p202401 VALUES LESS THAN (202402) chứa tất cả dữ liệu của tháng 1/2024.
• PARTITION p202402 VALUES LESS THAN (202403) chứa dữ liệu tháng 2/2024, v.v.

⏩ Lợi ích: Dữ liệu của từng tháng được lưu trong các partition riêng biệt.

---

4. Thêm Partition Cho Tháng Mới

Mỗi khi sang tháng mới, bạn cần thêm partition cho tháng đó bằng lệnh sau:

ALTER TABLE orders ADD PARTITION (
    PARTITION p202404 VALUES LESS THAN (202405) -- Thêm partition cho tháng 4/2024
);

⏩ Lợi ích: Bảng luôn sẵn sàng để lưu dữ liệu mới mà không ảnh hưởng đến dữ liệu cũ.

---

5. Xóa Partition Khi Không Cần Nữa

Nếu dữ liệu cũ không còn cần thiết (ví dụ, chỉ giữ dữ liệu trong 1 năm), bạn có thể xóa partition để giải phóng dung lượng:

ALTER TABLE orders DROP PARTITION p202401; -- Xóa dữ liệu tháng 1/2024

🚨 Cảnh báo: Khi bạn xóa partition, toàn bộ dữ liệu trong partition đó sẽ bị xóa vĩnh viễn!

---

6. Cách Chèn Dữ Liệu Vào Bảng Partitioned

Bạn không cần chỉ định partition khi chèn dữ liệu, MySQL sẽ tự động đưa vào partition phù hợp.

INSERT INTO orders (id, order_date, customer_id, amount)
VALUES (1, '2024-02-15', 101, 200.00);

Dữ liệu có order_date = '2024-02-15' sẽ được đưa vào partition p202402.

---

7. Truy Vấn Dữ Liệu Trong Partition

Khi bạn tìm kiếm dữ liệu trong một tháng cụ thể, MySQL chỉ truy vấn partition liên quan.

SELECT * FROM orders WHERE order_date BETWEEN '2024-02-01' AND '2024-02-28';

🔹 Lợi ích: Chỉ partition p202402 được quét, giúp truy vấn nhanh hơn nhiều so với việc quét cả bảng.

---

8. Tự Động Thêm Partition Mỗi Tháng

Để tránh quên thêm partition mới, bạn có thể tạo một EVENT để tự động thêm partition hàng tháng.

DELIMITER //
CREATE EVENT add_monthly_partition
ON SCHEDULE EVERY 1 MONTH
STARTS TIMESTAMP(CURRENT_DATE)
DO
BEGIN
    SET @next_partition = DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH), '%Y%m');
    SET @query = CONCAT('ALTER TABLE orders ADD PARTITION (PARTITION p', @next_partition, ' VALUES LESS THAN (', @next_partition + 1, '))');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;

🔹 Lợi ích:

• Tự động thêm partition mới mỗi tháng, không cần thao tác thủ công.
• Giảm rủi ro khi quên tạo partition mới khiến dữ liệu không thể lưu được.

---

9. Khi Nào Không Nên Dùng Partitioning?

Mặc dù partitioning rất hữu ích, nhưng có một số trường hợp bạn không nên dùng:
❌ Khi bảng có ít dữ liệu (< 1 triệu dòng).
❌ Khi cần dùng FOREIGN KEY (MySQL không hỗ trợ khóa ngoại trên bảng partitioned).
❌ Khi thường xuyên cần JOIN bảng khác, vì MySQL không tối ưu tốt các truy vấn JOIN trên partitioned tables.

---

II. Tối Ưu Hiệu Suất Cho Bảng orders Với 500K Dòng Mỗi Tháng

Khi bảng orders tăng 500,000 records mỗi tháng, cần tối ưu indexing và truy vấn để tránh hiệu suất giảm dần theo thời gian.

---

1. Sử Dụng Partitioning Theo Tháng

Với tốc độ tăng trưởng 500K records/tháng, partitioning theo tháng là lựa chọn phù hợp để giảm kích thước index và tăng tốc truy vấn.

Tạo Partition Theo Tháng

CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2),
    status ENUM('pending', 'completed', 'cancelled') NOT NULL,
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404)
);

✅ Lợi ích của partitioning:

• Truy vấn theo tháng nhanh hơn: MySQL chỉ đọc dữ liệu trong partition tương ứng.
• Giảm kích thước index: Vì index chỉ áp dụng cho từng partition, không phải toàn bảng.
• Xóa dữ liệu cũ nhanh chóng: Dùng DROP PARTITION thay vì DELETE, giúp tiết kiệm tài nguyên.

---

2. Chỉ Số (Index) Để Tăng Tốc Truy Vấn

Dữ liệu lớn cần các chỉ mục phù hợp để tối ưu truy vấn.

2.1. Index trên order_date

Nếu thường xuyên lọc dữ liệu theo ngày:

CREATE INDEX idx_order_date ON orders (order_date);

🔹 Lợi ích: Truy vấn theo ngày nhanh hơn, tránh full-table scan.

---

2.2. Index Kết Hợp (Composite Index)

Nếu truy vấn chủ yếu lọc theo customer_id và order_date, ta dùng composite index:

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

🔹 Lợi ích:

• Tăng tốc các truy vấn lọc theo customer_id và ngày.
• Lưu ý thứ tự cột: customer_id phải đứng trước vì MySQL sử dụng index từ trái sang phải.

Ví dụ truy vấn tối ưu:

SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2024-02-01';

---

2.3. Index Cho Trạng Thái (status)

Nếu hệ thống thường lọc theo trạng thái đơn hàng:

CREATE INDEX idx_status_date ON orders (status, order_date);

🔹 Lợi ích:

• Giúp tìm nhanh đơn hàng theo trạng thái (completed, cancelled, ...).
• Hữu ích khi có dashboard thống kê số lượng đơn hàng theo trạng thái.

Ví dụ truy vấn:

SELECT COUNT(*) FROM orders WHERE status = 'completed' AND order_date BETWEEN '2024-02-01' AND '2024-02-28';

---

3. Tối Ưu Truy Vấn

3.1. Tránh SELECT *, Chỉ Lấy Cột Cần Thiết

Tránh lấy toàn bộ cột nếu không cần, giúp giảm lượng dữ liệu MySQL phải xử lý.

SELECT id, order_date, amount FROM orders WHERE customer_id = 123;

✅ Lợi ích: Giảm tải cho hệ thống.

---

3.2. Sử Dụng Covering Index

Nếu truy vấn thường xuyên chỉ lấy các cột trong index, hãy đảm bảo index bao phủ (covering index).

CREATE INDEX idx_customer_amount_date ON orders (customer_id, amount, order_date);

Khi chạy truy vấn:

SELECT customer_id, amount FROM orders WHERE order_date >= '2024-02-01';

🔹 Lợi ích: MySQL chỉ đọc index, không cần truy cập dữ liệu trong bảng, giúp truy vấn nhanh hơn.

---

3.3. Sử Dụng EXPLAIN Để Kiểm Tra Truy Vấn

Dùng EXPLAIN để kiểm tra xem MySQL có sử dụng index không:

EXPLAIN SELECT * FROM orders WHERE order_date >= '2024-02-01';

Nếu EXPLAIN hiển thị Using index, chứng tỏ truy vấn đang sử dụng index hiệu quả.

---

4. Xử Lý Dữ Liệu Hàng Tháng Hiệu Quả

4.1. Xóa Dữ Liệu Cũ Nhanh Chóng

Thay vì dùng DELETE (rất chậm), hãy dùng DROP PARTITION để xóa dữ liệu cũ:

ALTER TABLE orders DROP PARTITION p202401;

🚀 Nhanh hơn gấp nhiều lần so với DELETE FROM orders WHERE order_date < '2024-01-01';.

---

4.2. Tự Động Thêm Partition Mỗi Tháng

Tạo EVENT tự động thêm partition mới hàng tháng:

DELIMITER //
CREATE EVENT add_monthly_partition
ON SCHEDULE EVERY 1 MONTH
DO
BEGIN
    SET @next_partition = DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH), '%Y%m');
    SET @query = CONCAT('ALTER TABLE orders ADD PARTITION (PARTITION p', @next_partition, ' VALUES LESS THAN (', @next_partition + 1, '))');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;

🔹 Lợi ích:

• Hệ thống luôn sẵn sàng chứa dữ liệu mới.
• Tránh lỗi khi quên tạo partition mới.

---

5. Tổng Kết

Tối ưu	Lợi ích
Partition theo tháng	Tăng tốc truy vấn, dễ xóa dữ liệu cũ
Index order_date	Tăng tốc truy vấn theo ngày
Composite Index (customer_id, order_date)	Tăng tốc truy vấn theo khách hàng
Covering Index (customer_id, amount, order_date)	Giảm thời gian đọc dữ liệu
EXPLAIN query	Kiểm tra MySQL có sử dụng index không
Tự động thêm partition mỗi tháng	Tránh lỗi và đảm bảo dữ liệu mới có chỗ lưu
Xóa partition thay vì DELETE	Nhanh hơn gấp nhiều lần

🚀 Với các tối ưu trên, hệ thống của bạn sẽ xử lý 500K records mỗi tháng một cách mượt mà, truy vấn nhanh và không lo bị chậm theo thời gian!

Một số lưu ý

Tính Năng	Có Partitioning	Không Partitioning
Tốc độ truy vấn	✅ Nhanh hơn khi tìm kiếm theo tháng	❌ Chậm khi bảng lớn
Xóa dữ liệu cũ	✅ Nhanh bằng DROP PARTITION	❌ Phải dùng DELETE, chậm hơn
Hỗ trợ khóa ngoại	❌ Không hỗ trợ	✅ Có thể dùng
Tối ưu JOIN	❌ Không tốt	✅ Tốt hơn

Partitioning phù hợp cho bảng lớn, truy vấn theo tháng, nhưng không phù hợp nếu cần JOIN nhiều bảng hoặc sử dụng khóa ngoại.