Amazon Aurora MySQL Bài 27: Aurora Indexing Strategies: B-Tree, Full-Text, và tối ưu hóa index cho bảng lớn

Khi một bảng dữ liệu trong Aurora MySQL phát triển từ vài triệu lên đến hàng trăm triệu dòng (ví dụ: bảng lịch sử giao dịch, bảng log hoạt động, hoặc danh mục sản phẩm thương mại điện tử), câu lệnh SELECT không có chỉ mục (Index) sẽ lập tức đẩy CPU của cơ sở dữ liệu lên 100%.

Mặc dù Aurora có tầng lưu trữ phân tán hiệu năng cao, việc quét toàn bộ bảng (Full Table Scan) vẫn là thảm họa. Bài viết này sẽ mổ xẻ các chiến lược đánh chỉ mục (Indexing Strategies) chuyên sâu trên Aurora MySQL.

1. Bản chất của B-Tree Index trong Aurora MySQL

Mặc định, các bảng sử dụng engine InnoDB trong Aurora MySQL lưu trữ dữ liệu dưới dạng Clustered Index (Chỉ mục cụm):

• Clustered Index (Primary Key): Dữ liệu thực tế của các dòng trong bảng được sắp xếp và lưu trữ trực tiếp trên các lá của cây B-Tree dựa theo khóa chính (Primary Key). Do đó, việc tìm kiếm theo Primary Key luôn đạt hiệu năng tuyệt đối ($O(\log N)$).

• Secondary Index (Chỉ mục phụ): Các index bạn tự tạo thêm (ví dụ: INDEX idx_email (email)). Các node lá của Secondary Index không chứa dữ liệu dòng, mà chứa giá trị của Primary Key.

• Hiện tượng Key Lookup (Bookmark Lookup): Khi bạn thực hiện câu lệnh SELECT * FROM users WHERE email = 'hieu@example.com', Aurora phải mất 2 bước:

  1. Tìm trên cây B-Tree của idx_email để lấy ra giá trị Primary Key tương ứng.

  2. Dùng Primary Key đó nhảy ngược lại cây B-Tree của Clustered Index để lấy toàn bộ các cột còn lại của dòng dữ liệu (Row Data).

2. Các chiến lược tối ưu Index cho bảng lớn

Để tối ưu hóa hiệu năng đọc/ghi trên các bảng dung lượng lớn, hãy tuân thủ các chiến lược sau:

a. Sử dụng Composite Index (Chỉ mục kết hợp) theo quy tắc Leftmost Prefix

Khi bạn cần tìm kiếm theo nhiều điều kiện kết hợp (ví dụ: lọc đơn hàng theo user_id, status và created_at), thay vì tạo 3 index rời rạc, hãy tạo một Composite Index:

SQL

CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

• Quy tắc tiền tố bên trái (Leftmost Prefix): Index này chỉ phát huy tác dụng khi câu lệnh WHERE của bạn sử dụng cột đầu tiên (user_id) hoặc kết hợp theo đúng thứ tự từ trái sang phải (user_id + status, hoặc user_id + status + created_at). Nếu bạn chỉ tìm theo WHERE status = 'completed', Aurora sẽ bỏ qua index này.

b. Covering Index (Chỉ mục bao phủ) - Bí kíp tăng tốc truy vấn

• Vấn đề: Tránh việc truy vấn SELECT * trên các bảng hàng triệu dòng vì nó buộc Aurora phải thực hiện Key Lookup (nhảy qua lại giữa các cây index như đã phân tích ở trên).

• Giải pháp (Covering Index): Xây dựng index chứa toàn bộ các cột xuất hiện trong câu lệnh SELECT và WHERE.

  SQL

  -- Nếu câu lệnh của bạn chỉ cần lấy id, status và total_amount:
  SELECT id, status, total_amount FROM orders WHERE user_id = 123;
  -- Hãy tạo index bao phủ:
  CREATE INDEX idx_covering_order ON orders(user_id, status, total_amount);
  
  

• Kết quả: Aurora chỉ cần đọc kết quả trực tiếp từ các node lá của Index trong RAM (Buffer Pool) mà không cần thèm chạm tới bảng dữ liệu gốc, giúp tốc độ truy vấn nhanh gấp nhiều lần.

c. Tránh lạm dụng quá nhiều Index trên bảng Write-heavy

• Mỗi khi thực hiện INSERT, UPDATE, hoặc DELETE trên một bảng, Aurora không chỉ cập nhật dữ liệu gốc mà phải cập nhật lại toàn bộ các cây B-Tree Index liên quan.

• Nếu một bảng có quá nhiều Index thừa thãi không dùng tới, tốc độ ghi dữ liệu (Write Throughput) sẽ bị bóp nghẹt nghiêm trọng. Hãy định kỳ rà soát và xóa bỏ các Index không bao giờ được sử dụng (Unused Indexes).

3. Full-Text Search trên Aurora MySQL

Khi cần tìm kiếm chuỗi văn bản dài (ví dụ: tìm kiếm tên sản phẩm, mô tả bài viết), việc dùng LIKE '%keyword%' làm tê liệt database.

• Giải pháp: Sử dụng FULLTEXT INDEX đi kèm hàm MATCH() AGAINST():

  SQL

  -- Tạo Fulltext Index
  ALTER TABLE products ADD FULLTEXT INDEX fx_title_desc(title, description);
  
  -- Truy vấn tìm kiếm tối ưu
  SELECT * FROM products 
  WHERE MATCH(title, description) AGAINST('+laptop -gaming' IN BOOLEAN MODE);
  
  

• Lưu ý thực tế: Đối với các hệ thống E-commerce quy mô lớn yêu cầu tìm kiếm mờ (fuzzy search), tiếng Việt có dấu phức tạp hoặc khối lượng dữ liệu khổng lồ, việc đẩy dữ liệu sang Amazon OpenSearch Service (như đã đề cập ở Bài 22) vẫn là giải pháp tối ưu hơn so với việc ép Aurora MySQL xử lý Full-Text Search nặng nề.

4. Góc nhìn vận hành cho Backend Engineer

• Giám sát Index không sử dụng: Sử dụng Performance Insights hoặc truy vấn các bảng hệ thống của MySQL (performance_schema) để phát hiện các index không bao giờ được query (Unused Index) nhằm dọn dẹp, giúp tăng tốc độ ghi dữ liệu.

• Kiểm tra độ phân tán (Cardinality): Một Index chỉ hiệu quả khi cột đó có độ phân tán dữ liệu cao (Cardinality lớn, ví dụ user_id, email). Đánh index cho các cột chỉ có 2 giá trị như is_active (True/False) trên bảng hàng triệu dòng thường không mang lại hiệu quả vì Optimizer sẽ chọn Full Table Scan thay vì dùng index.