Kinh nghiệm truy vấn bảng lịch (time-series / historical) có dữ liệu lớn

Wiki — Kinh nghiệm truy vấn bảng lịch (Time-series / Historical) có dữ liệu lớn

1. Tổng quan về Partition trong cơ sở dữ liệu

Partitioning là kỹ thuật chia một bảng lớn thành nhiều phân vùng nhỏ hơn dựa trên giá trị của một hoặc nhiều cột, thường là cột thời gian. Đây là yêu cầu bắt buộc đối với các bảng log, transaction hoặc sự kiện có dữ liệu tăng trưởng liên tục theo thời gian.

Mục tiêu của partitioning

• Giảm I/O nhờ partition pruning.
• Tăng tốc độ truy vấn.
• Dễ dàng bảo trì dữ liệu (xóa hoặc archive theo partition).
• Giảm kích thước index.
• Tăng khả năng mở rộng.

Khi nào nên sử dụng partition

• Bảng lớn hơn 10 triệu dòng.
• Truy vấn chủ yếu theo thời gian.
• Cần xóa dữ liệu cũ theo tháng/quý/năm.
• Cần archive nhanh dữ liệu lịch sử.

2. Nguyên tắc chung

• Partition theo cột thời gian khi workload chủ yếu lọc theo ngày/tháng.
• Partition pruning là yếu tố quan trọng để giảm I/O.
• Không dùng hàm trên cột partition trong WHERE.
• Index vẫn quan trọng, partition không thay index.
• Tránh CAST/COLLATE trên cột partition key.

3. Lựa chọn kiểu partition

3.1 RANGE

Phù hợp cho dữ liệu thời gian. Thường partition theo ngày, tuần, tháng, quý.

3.2 RANGE COLUMNS

Khuyến nghị khi dùng DATETIME/DATE.

3.3 LIST / LIST COLUMNS

Phân vùng theo danh sách giá trị, ít dùng cho dữ liệu lịch.

3.4 HASH / KEY

Phân đều dữ liệu khi không phụ thuộc thời gian.

3.5 Subpartition

Kết hợp nhiều dạng partition, nhưng hiếm khi cần thiết.

4. Ví dụ thiết kế partition theo tháng

CREATE TABLE transaction_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  msisdn VARCHAR(20),
  package_code VARCHAR(50),
  request_time DATETIME,
  type TINYINT,
  error_code VARCHAR(10),
  price DECIMAL(10,2),
  user_type TINYINT,
  INDEX idx_request_time (request_time),
  INDEX idx_msisdn_request_time (msisdn, request_time),
  INDEX idx_package_request_time (package_code, request_time)
)
PARTITION BY RANGE COLUMNS(request_time) (
  PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
  PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
  PARTITION p202503 VALUES LESS THAN ('2025-04-01'),
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

5. Nguyên tắc viết truy vấn để kích hoạt partition pruning

• Không dùng hàm trên cột partition key.
• Dùng điều kiện dạng khoảng:

WHERE request_time >= '2025-11-01 00:00:00'
  AND request_time < '2025-11-02 00:00:00';

• Dùng EXPLAIN PARTITIONS để kiểm tra partition được truy vấn.

6. Index kết hợp với Partition

• Partition không thay thế index.
• Một số phiên bản MySQL yêu cầu partition key nằm trong UNIQUE/PRIMARY KEY.
• Thiết kế composite index tùy vào query patterns.

7. Kỹ thuật quản lý partition

7.1 Thêm partition định kỳ

ALTER TABLE transaction_log
ADD PARTITION (
  PARTITION p202601 VALUES LESS THAN ('2026-02-01')
);

7.2 Xóa partition cũ

ALTER TABLE transaction_log DROP PARTITION p202201;

7.3 Exchange Partition

ALTER TABLE transaction_log
EXCHANGE PARTITION p202201 WITH TABLE transaction_log_archive
WITHOUT VALIDATION;

8. Bulk Load / Insert / Delete

• Bulk insert theo partition key.
• LOAD DATA INFILE cho tốc độ cao.
• Hạn chế sử dụng INSERT ON DUPLICATE KEY trong trường hợp nhiều session song song.

9. Truy vấn JOIN

• Optimizer vẫn có thể prune nếu điều kiện WHERE có liên quan đến partition key.
• Nên lọc theo partition key càng sớm càng tốt.

10. Locking và Concurrency

• Partition không giảm contention nếu nhiều phiên giao dịch cập nhật cùng một hàng.
• Thao tác DDL như DROP/ADD partition có thể gây metadata lock.
• Tránh thực hiện DDL partition trong giờ cao điểm.

11. Công cụ kiểm tra và debug

• EXPLAIN PARTITIONS
• SHOW CREATE TABLE
• SHOW INDEX
• ANALYZE TABLE
• SHOW ENGINE INNODB STATUS
• INFORMATION_SCHEMA.PARTITIONS

12. Lỗi thường gặp

• Dùng hàm trong WHERE.
• Dùng CAST/COLLATE trên partition key.
• Tạo quá nhiều partition nhỏ.
• Tạo partition theo năm khi dữ liệu lớn.
• Chạy DDL trong giờ cao điểm.
• Không có kế hoạch purge.

13. Checklist triển khai

• Chọn granularity phù hợp: monthly/daily.
• Luôn dùng range queries.
• Thiết kế composite index hợp lý.
• Kiểm tra yêu cầu UNIQUE KEY.
• Lên lịch job ADD và DROP partition.
• Test EXPLAIN PARTITIONS.

14. Quy trình triển khai partition cho bảng hiện tại

1. Kiểm tra schema hiện tại.
2. Tạo bảng mới có partition.
3. Chuyển dữ liệu theo batch.
4. RENAME TABLE để hoán đổi.
5. Bật job bảo trì partition.

15. Ví dụ kiểm tra partition pruning

EXPLAIN PARTITIONS
SELECT COUNT(*)
FROM transaction_log
WHERE request_time >= '2025-11-01 00:00:00'
  AND request_time < '2025-11-02 00:00:00';

16. Kết luận

Partition theo thời gian là giải pháp tối ưu cho các bảng lịch có dữ liệu lớn. Thiết kế partition, index và truy vấn phải đồng bộ để đạt hiệu quả. Bảo trì partition là công việc định kỳ và cần thực hiện cẩn trọng để tránh ảnh hưởng hệ thống.