Giới thiệu
Trong các ứng dụng backend, việc xử lý dữ liệu lớn là một thách thức lớn, đặc biệt khi cần phân trang (pagination) trên một bảng MySQL chứa 10 triệu bản ghi. Một truy vấn không tối ưu có thể mất đến 7 giây để trả về kết quả, gây ảnh hưởng đến trải nghiệm người dùng. Trong bài viết này, chúng ta sẽ tìm hiểu cách sử dụng bảng tạm (temporary table) và đánh chỉ mục (index) để giảm thời gian truy vấn từ 7 giây xuống còn 1 giây, dựa trên một ví dụ thực tế.
Vấn đề: Truy vấn chậm với bảng 10 triệu dữ liệu
Hãy xem xét một truy vấn phân trang cơ bản trên bảng pre_go_crm_user với 10 triệu bản ghi:
SELECT SQL_NO_CACHE usr_id, usr_created_at_data, usr_email, usr_phone, usr_username, usr_created_at, usr_create_ip_at, usr_last_login_at, usr_last_login_ip_at, usr_login_times, usr_status FROM pre_go_crm_user WHERE usr_created_at_data > '2024-07-23 00:00:00' AND usr_created_at_data < '2024-08-22 23:59:59' ORDER BY usr_created_at_data ASC, usr_id ASC LIMIT 9000000, 50;- Vấn đề:
- LIMIT 9000000, 50: Với OFFSET lớn (9 triệu), MySQL phải quét qua 9 triệu bản ghi trước khi trả về 50 bản ghi, dẫn đến thời gian truy vấn lên đến 7 giây.
- Không có chỉ mục trên cột usr_created_at_data trong mệnh đề WHERE, khiến MySQL quét toàn bảng.
- ORDER BY trên nhiều cột (usr_created_at_data, usr_id) làm tăng chi phí sắp xếp.
Giải pháp: Sử dụng bảng tạm và đánh chỉ mục
Bước 1: Đánh chỉ mục (Index) trên cột trong mệnh đề WHERE
Để tăng tốc độ lọc dữ liệu, chúng ta cần tạo chỉ mục trên cột usr_created_at_data trong bảng pre_go_crm_user:
CREATE INDEX idx_usr_created_at_data ON pre_go_crm_user (usr_created_at_data);- Hiệu quả: Chỉ mục giúp MySQL nhanh chóng định vị các bản ghi thỏa mãn điều kiện WHERE, thay vì quét toàn bảng.
Bước 2: Sử dụng bảng tạm để chỉ truy vấn ID
Thay vì truy vấn trực tiếp trên bảng chính, chúng ta tạo một bảng tạm (temp) để lưu trữ danh sách usr_id của các bản ghi cần thiết:
SELECT SQL_NO_CACHE usr_id FROM pre_go_crm_user WHERE usr_created_at_data > '2024-07-23 00:00:00' AND usr_created_at_data < '2024-08-22 23:59:59' ORDER BY usr_created_at_data ASC, usr_id ASC LIMIT 9000000, 50 ) AS temp- Lợi ích:
- Bảng tạm chỉ chứa cột usr_id, giảm kích thước dữ liệu cần xử lý.
- Truy vấn trên bảng tạm nhanh hơn vì không cần lấy toàn bộ các cột.
Bước 3: Truy vấn bảng chính với bảng tạm
Sử dụng bảng tạm để join với bảng chính và lấy đầy đủ các trường:
SELECT SQL_NO_CACHE pre.usr_id, usr_created_at_data, usr_email, usr_phone, usr_username, usr_created_at, usr_create_ip_at, usr_last_login_at, usr_last_login_ip_at, usr_login_times, usr_status FROM ( SELECT SQL_NO_CACHE usr_id FROM pre_go_crm_user WHERE usr_created_at_data > '2024-07-23 00:00:00' AND usr_created_at_data < '2024-08-22 23:59:59' ORDER BY usr_created_at_data ASC, usr_id ASC LIMIT 9000000, 50 ) AS temp INNER JOIN pre_go_crm_user AS pre ON temp.usr_id = pre.usr_id ORDER BY usr_created_at_data ASC, usr_id ASC;- Hiệu quả:
- Truy vấn trên bảng tạm nhanh hơn vì chỉ xử lý cột usr_id.
- Join với bảng chính chỉ lấy 50 bản ghi, giảm tải đáng kể.
Kết quả
- Trước tối ưu: Truy vấn mất 7 giây do quét toàn bảng và OFFSET lớn.
- Sau tối ưu: Thời gian giảm xuống còn 1 giây nhờ:
- Chỉ mục trên usr_created_at_data tăng tốc độ lọc.
- Bảng tạm giảm khối lượng dữ liệu cần xử lý.
- Join hiệu quả với bảng chính.
Lợi ích và hạn chế
- Lợi ích:
- Tăng tốc độ truy vấn đáng kể (7s → 1s).
- Áp dụng được cho các bảng lớn hơn.
- Dễ triển khai trong các hệ thống backend.
- Hạn chế:
- Cần thêm bước tạo bảng tạm, tốn tài nguyên ban đầu.
- Nếu dữ liệu thay đổi thường xuyên, bảng tạm cần được cập nhật.
Kết luận
Tối ưu hóa truy vấn MySQL cho bảng 10 triệu dữ liệu không còn là bài toán khó nếu bạn sử dụng bảng tạm và đánh chỉ mục hợp lý. Phương pháp này không chỉ cải thiện hiệu suất mà còn nâng cao trải nghiệm người dùng trong các ứng dụng thực tế. Hãy thử áp dụng ngay hôm nay để thấy sự khác biệt!
