Tại sao tạo quá nhiều INDEX lại nguy hiểm? Sai lầm khiến database phình to và chậm dần theo thời gian

Nhiều lập trình viên khi thấy SQL chậm thường có phản xạ rất quen thuộc:

"Thêm INDEX đi là nhanh thôi."

Điều này đúng, nhưng chỉ đúng một nửa.

INDEX thực sự giúp tăng tốc truy vấn cực mạnh. Tuy nhiên nếu lạm dụng, database có thể rơi vào tình trạng ngược lại: dung lượng tăng khủng khiếp, INSERT chậm dần, UPDATE ì ạch và tối ưu query ngày càng khó khăn.

Không ít hệ thống thực tế có table dữ liệu chỉ vài trăm MB nhưng INDEX lại chiếm tới vài GB. Đây là một vấn đề rất phổ biến trong các dự án lớn khi developer thêm INDEX liên tục mà không kiểm soát.

Thực tế, INDEX không hề “miễn phí”. Mỗi INDEX đều có cái giá rất đắt phía sau.

INDEX thực chất là một cấu trúc dữ liệu riêng

Nhiều người nghĩ INDEX chỉ là một “đánh dấu” đơn giản trên column.

Nhưng thực tế không phải vậy.

Mỗi INDEX thường là một cấu trúc dữ liệu hoàn chỉnh, phổ biến nhất là B+Tree.

Điều đó có nghĩa:

• INDEX có dữ liệu riêng
• INDEX có block storage riêng
• INDEX có page riêng
• INDEX có cache riêng
• INDEX cần được cập nhật liên tục

Nói đơn giản:

Mỗi INDEX gần như là một "bản sao tối ưu hóa" của dữ liệu.

Vì vậy càng nhiều INDEX thì database càng phải lưu thêm rất nhiều dữ liệu phụ.

Vì sao INDEX có thể lớn hơn cả table?

Đây là chuyện xảy ra rất thường xuyên.

Ví dụ:

• Table dữ liệu: 200MB
• INDEX: 2GB

Nghe có vẻ vô lý nhưng hoàn toàn có thể xảy ra.

Lý do là vì:

• Có quá nhiều INDEX
• Nhiều Composite Index trùng nhau
• INDEX trên VARCHAR dài
• INDEX nhiều column
• Data fragmentation
• Overhead của B+Tree

Ví dụ:

INDEX(a)
INDEX(a, b)
INDEX(a, b, c)
INDEX(a, b, c, d)

Rất nhiều developer tạo kiểu này mà không nhận ra rằng các INDEX đang overlap cực mạnh.

Database phải lưu nhiều cây B+Tree gần giống nhau.

INDEX làm INSERT chậm như thế nào?

Mỗi lần INSERT dữ liệu, database không chỉ ghi vào table.

Nó còn phải:

• Cập nhật tất cả INDEX liên quan
• Rebalance B+Tree
• Split page nếu đầy
• Ghi thêm log
• Cập nhật cache

Nếu table có:

• 2 INDEX → ghi 2 lần
• 10 INDEX → ghi 10 lần
• 30 INDEX → ghi cực nặng

Đây là lý do nhiều hệ thống:

• SELECT nhanh
• Nhưng INSERT cực chậm

vì bị “ngợp” bởi quá nhiều INDEX.

UPDATE và DELETE còn tệ hơn

Khi UPDATE column có INDEX, database thường phải:

• Xóa key cũ khỏi INDEX
• Insert key mới vào INDEX
• Rebalance cây

Điều này khiến UPDATE trên table nhiều INDEX cực kỳ tốn tài nguyên.

DELETE cũng tương tự vì database phải xóa dữ liệu khỏi toàn bộ INDEX liên quan.

INDEX quá nhiều làm optimizer khó chọn kế hoạch tối ưu

Ít người để ý rằng càng nhiều INDEX thì Query Optimizer càng khó làm việc.

Database phải:

• Phân tích nhiều execution path
• Estimate cost cho từng INDEX
• So sánh cardinality
• Tính toán statistics

Khi số lượng INDEX quá lớn:

• Optimizer có thể chọn sai INDEX
• Execution Plan trở nên khó đoán
• Query đôi khi chậm bất thường

Đây là lý do “nhiều INDEX hơn” không đồng nghĩa với “nhanh hơn”.

Composite Index bị lạm dụng rất nhiều

Một sai lầm cực phổ biến:

INDEX(a)
INDEX(a, b)
INDEX(a, b, c)
INDEX(a, b, c, d)

Trong B+Tree, INDEX(a, b, c) thường đã có thể hỗ trợ:

• a
• (a, b)
• (a, b, c)

Nghĩa là nhiều INDEX nhỏ phía trước có thể đã dư thừa.

Rất nhiều hệ thống production bị phình INDEX chỉ vì lỗi này.

INDEX trên VARCHAR dài rất tốn dung lượng

Ví dụ:

email VARCHAR(255)
title VARCHAR(500)
url TEXT

Nếu INDEX trực tiếp trên các column dài:

• Page size tăng mạnh
• B+Tree phình to
• Cache efficiency giảm
• Disk I/O tăng

Đây là lý do nhiều hệ thống lớn:

• Dùng prefix index
• Dùng hash
• Dùng generated column
• Tránh index TEXT dài

RAM cũng bị ảnh hưởng nặng

Database thường cache INDEX trong RAM.

Nếu INDEX quá lớn:

• Buffer pool bị chiếm dụng
• Cache miss tăng
• Disk read nhiều hơn
• Hiệu năng toàn hệ thống giảm

Nhiều người chỉ nhìn disk size mà quên mất RAM mới là thứ bị ảnh hưởng nặng nhất.

Khi nào nên xóa INDEX?

Nên xem xét xóa khi:

• INDEX không được dùng
• INDEX overlap với INDEX khác
• Query đã thay đổi
• Cardinality quá thấp
• Write performance đang quá chậm

Trong production lớn, việc audit INDEX định kỳ là cực kỳ quan trọng.

Nguyên tắc tạo INDEX đúng

• Chỉ tạo INDEX cho query thực sự cần
• Ưu tiên query chạy nhiều
• Tránh INDEX trùng lặp
• Theo dõi EXPLAIN
• Kiểm tra execution plan
• Đo write performance sau khi thêm INDEX
• Định kỳ dọn INDEX dư thừa

Kết luận

INDEX là một trong những công cụ tối ưu mạnh nhất của database, nhưng cũng là con dao hai lưỡi rất nguy hiểm.

Quá nhiều INDEX có thể khiến:

• Disk usage tăng khổng lồ
• INSERT và UPDATE chậm mạnh
• RAM bị chiếm dụng lớn
• Optimizer hoạt động kém hiệu quả
• Hệ thống khó scale hơn