Giới thiệu
Phân trang sâu trong MySQL là một nút thắt hiệu suất phổ biến trong phát triển thực tế, đặc biệt rõ ràng khi phân trang qua các tập dữ liệu lớn. Nhiều lập trình viên thường xuyên sử dụng cú pháp LIMIT offset, size để thực hiện phân trang. Tuy nhiên, khi truy vấn số trang lớn hoặc truy cập dữ liệu ở vị trí xa trong tập kết quả, phương pháp đơn giản này có thể dẫn đến các vấn đề hiệu suất nghiêm trọng. Các offset quá lớn buộc cơ sở dữ liệu phải quét và loại bỏ một lượng lớn dữ liệu, dẫn đến thời gian phản hồi tăng vọt và tiêu thụ tài nguyên hệ thống quá mức.
Bài viết này đi sâu vào nguyên nhân gốc rễ của các vấn đề phân trang sâu, xác thực một giải pháp hiệu quả—Deferred Join—thông qua các thử nghiệm toàn diện, và so sánh sự khác biệt về hiệu suất trước và sau khi tối ưu hóa, cung cấp cho các lập trình viên một cách tiếp cận thực tiễn để cải thiện.
1. Môi Trường Thí Nghiệm và Chuẩn Bị Dữ Liệu
1.1 Cấu Hình Phần Cứng
| Thành phần | Thông số |
|---|---|
| CPU | 4-core |
| RAM | 8GB |
| Lưu trữ | 1TB SSD |
| Phiên bản MySQL | 5.7.24 |
1.2 Thiết Kế Cấu Trúc Bảng
sql
CREATE TABLE `access_logs` (
`log_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`request_url` varchar(500) DEFAULT NULL,
`http_method` varchar(10) DEFAULT NULL,
`response_code` int(11) DEFAULT NULL,
`response_time_ms` int(11) DEFAULT NULL,
`ip_address` varchar(50) DEFAULT NULL,
`user_agent` varchar(200) DEFAULT NULL,
`referrer` varchar(200) DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`log_id`),
KEY `idx_time` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=UTF8MB4;2. Phương Pháp Tạo Dữ Liệu
Chúng tôi đã tạo ra 2 triệu bản ghi thử nghiệm, đủ để kiểm tra phân trang sâu.
sql
DELIMITER $$
CREATE PROCEDURE populate_test_data(IN log_count INT)
BEGIN
DECLARE i INT DEFAULT 1;
SET i = 1;
WHILE i <= log_count DO
INSERT INTO access_logs(user_id, request_url, http_method, response_code, response_time_ms, ip_address, user_agent, referrer)
VALUES (
FLOOR(1 + RAND()*user_count),
CONCAT('/api/v1/resource/', FLOOR(RAND()*1000)),
ELT(FLOOR(1 + RAND()*4), 'GET','POST','PUT','DELETE'),
ELT(FLOOR(1 + RAND()*5), 200, 200, 200, 404, 500),
FLOOR(RAND()*3000),
CONCAT('192.168.', FLOOR(RAND()*255), '.', FLOOR(RAND()*255)),
ELT(FLOOR(1 + RAND()*4), 'Chrome','Safari','Firefox','Edge'),
ELT(FLOOR(1 + RAND()*4), 'google.com','bing.com','baidu.com','direct')
);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL populate_test_data(2000000);3. Thí Nghiệm So Sánh Hiệu Suất
3.1 SQL Gốc
sql
SELECT
log_id,
user_id,
request_url,
ip_address,
created_at
FROM
access_logs
ORDER BY
created_at
LIMIT
1800000, 10;3.2 SQL Được Tối Ưu Hóa
Chúng tôi đã cố gắng viết lại bằng SQLFlash.
Câu truy vấn SQL đã viết lại:
sql
SELECT t1.log_id, t1.user_id, t1.request_url, t1.ip_address, t1.created_at
FROM access_logs t1
INNER JOIN (
SELECT log_id
FROM access_logs
ORDER BY created_at
LIMIT 1800000, 10
) t2 ON t1.log_id = t2.log_id
ORDER BY t1.created_at;3.3 So Sánh Chỉ Số Hiệu Suất
| Chỉ số | SQL Gốc | SQL Được Tối Ưu | Cải thiện |
|---|---|---|---|
| Thời gian thực hiện | 2.34s | 0.46s | 80.3% nhanh hơn |
| Số hàng quét | 1,800,010 | 20 | 99.998% giảm |
| Hoạt động quay lại bảng | 1,800,010 | 10 | 99.999% giảm |
3.4 Phân Tích SQLFlash
So với SQL gốc, SQL được viết lại đạt được tối ưu hóa thông qua việc sử dụng chỉ mục phủ và các truy vấn tách rời:
- Giảm hoạt động quay lại bảng: Truy vấn con quét chỉ mục mà không quay lại bảng, giảm từ 1,800,010 xuống 10 hoạt động.
- Phạm vi quét vật lý nhỏ hơn: Mật độ lưu trữ cao của các mục chỉ mục giảm đáng kể số trang quét.
- Thứ tự thực hiện được tối ưu hóa: Lọc các khóa chính trước và sau đó lấy dữ liệu đầy đủ, tránh việc truyền dữ liệu thừa.
4. Phân Tích Kế Hoạch Thực Hiện Sâu
4.1 Kế Hoạch Thực Hiện SQL Gốc
sql
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| 1 | SIMPLE | access_logs | NULL | ALL | NULL | NULL | NULL | NULL | 1987219 | 100.00 | Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+Phân tích: SQL gốc thực hiện quét toàn bộ bảng với filesort trong bộ nhớ, xử lý gần 2 triệu hàng.
4.2 Kế Hoạch Thực Hiện SQL Được Tối Ưu
sql
+----+-------------+-------------+------------+--------+---------------+----------+---------+-----------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+--------+---------------+----------+---------+-----------+---------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 1800010 | 100.00 | NULL |
| 1 | PRIMARY | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 8 | t2.log_id | 1 | 100.00 | NULL |
| 2 | DERIVED | access_logs | NULL | index | NULL | idx_time | 6 | NULL | 1800010 | 100.00 | Using index |
+----+-------------+-------------+------------+--------+---------------+----------+---------+-----------+---------+----------+-------------+Phân tích: Kế hoạch thực hiện tối ưu hóa sử dụng quét chỉ mục để lấy khóa chính, sau đó thực hiện khớp khóa chính hiệu quả.
5. Kết Luận
Thông qua phân tích so sánh, chúng tôi xác nhận rằng Deferred Join là một giải pháp hiệu quả để tối ưu hóa các truy vấn phân trang sâu trong MySQL. Phương pháp này chia truy vấn thành hai giai đoạn: đầu tiên sử dụng các chỉ mục phủ để nhanh chóng xác định các ID khóa chính cần thiết, sau đó lấy dữ liệu hàng đầy đủ thông qua liên kết khóa chính.
Ưu điểm chính:
- Giảm đáng kể hoạt động quay lại bảng: Từ 1.8 triệu xuống chỉ còn 10 hoạt động (giảm 99.99%).
- Tải I/O vật lý thấp hơn: Giảm đáng kể phạm vi quét đĩa bằng cách sử dụng dữ liệu chỉ mục có mật độ cao.
- Loại bỏ quét toàn bộ bảng: Sử dụng thứ tự chỉ mục để tránh các hoạt động filesort tốn kém.
- Tính linh hoạt mạnh mẽ: Có thể áp dụng cho bất kỳ tình huống phân trang sâu nào với các trường sắp xếp đã chỉ mục.
Phương pháp Deferred Join cung cấp chi phí sửa đổi thấp và tính ứng dụng rộng rãi, khiến nó trở thành giải pháp ưu tiên cho các vấn đề hiệu suất phân trang sâu trong MySQL. Đối với các ứng dụng thực tiễn, chúng tôi khuyến nghị triển khai tối ưu hóa này cho các truy vấn phân trang vượt quá 100 trang.
Ngoài ra, hãy xem xét việc kết hợp với các chiến lược tối ưu hóa khác như giới hạn độ sâu phân trang tối đa ở cấp độ doanh nghiệp và phân trang dựa trên con trỏ để xây dựng các giải pháp phân trang toàn diện hơn.
