Khi triển khai thử nghiệm TiDB cho hệ thống logistics kế thừa từ MySQL, một truy vấn đơn giản nhưng gây áp lực lớn lên cụm đã nhanh chóng nổi bật trong bảng giám sát chậm — dù chỉ là câu lệnh SELECT trên một bảng duy nhất với 5 điều kiện WHERE và LIMIT 200.

Dưới đây là truy vấn gốc:

SELECT id, common_job_type, org_code, key_one, key_two, key_three,
       key_four, key_five, key_six, key_seven, key_eight,
       permission_one, permission_two, permission_three,
       cur_thread, group_id, max_execute_count, remain_execute_count,
       total_execute_count, pre_excutetime, related_data, delay_time,
       error_message, flag, ynflag, create_time, update_time,
       create_user, update_user, ip, version_num
FROM job_cm_data
WHERE group_id = 'GROUP_MATERIAL'
  AND cur_thread = 1
  AND pre_excutetime < '2022-04-27 11:55:00.018'
  AND ynflag = 1
  AND flag = 0
ORDER BY id
LIMIT 200;

Thời gian thực thi đo được là 1.17 giây, chủ yếu tiêu tốn ở giai đoạn Selection_40 trong Coprocessor — nơi tiến trình phải đọc lại hàng trăm nghìn bản ghi từ đĩa để lọc tiếp sau bước quét chỉ mục.

Phân tích kế hoạch thực thi cho thấy bộ quét chỉ mục idx_group_id trả về 258.733 rowid, nhưng sau khi tra cứu ngược (index lookup) và áp dụng các điều kiện còn lại, kết quả cuối cùng là 0 dòng. Điều này cho thấy chỉ mục hiện tại gần như vô hiệu: nó không loại bỏ đủ dữ liệu sớm, mà lại gây chi phí I/O và CPU nặng do phải tải quá nhiều bản ghi không cần thiết.

Thống kê phân bố dữ liệu xác nhận giả thuyết:

SELECT group_id, COUNT(*) AS cnt
FROM job_cm_data
GROUP BY group_id
ORDER BY cnt DESC;

Kết quả:

Với hơn 83% dữ liệu tập trung vào một giá trị duy nhất, chỉ mục trên cột group_id vi phạm nguyên tắc cơ bản: tỷ lệ chọn lọc (selectivity) phải cao. Việc xóa chỉ mục này khiến truy vấn chuyển sang quét toàn bảng — và bất ngờ thay, thời gian giảm xuống còn ~500ms. Tuy nhiên, giải pháp này gây hệ lụy: các truy vấn khác trên cùng bảng (với group_id khác) đều chậm đi rõ rệt, đồng thời tạo ra điểm nóng đọc (read hotspot) trên TiKV do toàn bộ khối dữ liệu nằm trong ít region và tập trung vào một node.

Giải pháp bền vững đòi hỏi một chỉ mục hỗ trợ hiệu quả cho tất cả các điều kiện lọc — đặc biệt là điều kiện có khả năng loại bỏ phần lớn dữ liệu ngay từ đầu.

Phân tích tính chọn lọc của từng cột:

• ynflag = 1: toàn bộ bảng đều có giá trị 1 → loại khỏi chỉ mục.
• cur_thread: 6 giá trị phân bố đều → selectivity thấp.
• group_id: 6 giá trị, nhưng một giá trị chiếm >83% → không phù hợp làm tiền tố.
• pre_excutetime: ~35.000 giá trị khác nhau → tốt, nhưng là điều kiện phạm vi (<), nên nếu đặt đầu tiên sẽ chặn việc sử dụng các cột sau trong chỉ mục ghép.
• flag: phân bố cực kỳ lệch — hầu hết là 2 (277.832 bản ghi), chỉ 30 bản ghi có giá trị 4, và 34.132 bản ghi có giá trị 1. Quan trọng hơn: tất cả truy vấn nghiệp vụ thực tế đều dùng flag = 0, dù giá trị này chưa xuất hiện trong mẫu thống kê — nghĩa là nó gần như là một "bộ lọc hoàn hảo".

Vì vậy, chỉ mục tối ưu được đề xuất là:

ALTER TABLE job_cm_data ADD INDEX idx_flag_time (flag, pre_excutetime);

Chỉ mục này tuân thủ hai nguyên tắc then chốt:

1. Nguyên tắc khớp trái (leftmost prefix): flag — cột có tính chọn lọc cao nhất — đứng đầu, đảm bảo điều kiện bằng (=) được xử lý trước.
2. Thứ tự ưu tiên theo mức độ lọc: Sau khi lọc qua flag = 0, số bản ghi còn lại rất nhỏ (dưới 50), nên việc áp dụng điều kiện phạm vi pre_excutetime < ... trên tập con này trở nên cực kỳ hiệu quả.

Kết quả thực thi mới:

• Thời gian trung bình: 1.8 ms (không cache).
• Tỷ lệ cải thiện: ~666 lần so với phiên bản ban đầu (1170 ms / 1.8 ms ≈ 650).
• Số dòng quét trong chỉ mục giảm từ 258.733 xuống còn vài chục.
• Không còn hiện tượng read hotspot: tải đọc phân bổ đều trên các TiKV node.

Một lưu ý quan trọng: nếu truy vấn mở rộng sang các giá trị flag khác (ví dụ flag = 1 hoặc flag = 4), chi phí quét ngược vẫn kiểm soát được nhờ kích thước tập con nhỏ — ví dụ với flag = 1 (34.132 bản ghi), thời gian thực thi vẫn dưới 10ms trong điều kiện tiêu chuẩn.

Để tăng thêm độ an toàn và khả năng mở rộng, có thể bổ sung ràng buộc thời gian tối thiểu (ví dụ pre_excutetime > '2022-04-01') nhằm giới hạn vùng dữ liệu cần quét — đặc biệt hữu ích khi bảng phát triển theo thời gian.