Series duthaho đi phỏng vấn: MySQL indexing

Anh Minh：Chào duthaho, cảm ơn em đã đến buổi phỏng vấn hôm nay. Anh thấy hồ sơ của em có kinh nghiệm với MySQL, đặc biệt là tối ưu hóa hiệu năng. Hôm nay mình sẽ tập trung vào indexing, một chủ đề quan trọng trong phát triển backend. Em sẵn sàng bắt đầu chưa?

duthaho：Dạ, em sẵn sàng ạ. Cảm ơn anh Minh đã cho em cơ hội. Em rất hào hứng để chia sẻ kiến thức về MySQL indexing và học hỏi thêm từ anh.

Anh Minh：Tốt lắm! Mở đầu, em có thể giải thích ngắn gọn index trong MySQL là gì, và tại sao nó quan trọng đối với hiệu năng cơ sở dữ liệu không?

duthaho：Dạ, index trong MySQL là một cấu trúc dữ liệu, thường là B+ tree hoặc đôi khi là hash, được tạo trên một hoặc nhiều cột để tăng tốc truy vấn. Nó giống như mục lục sách, giúp MySQL nhanh chóng định vị hàng mà không cần quét toàn bảng (full table scan). Ví dụ, với bảng users có cột email, index trên email sẽ khiến SELECT * FROM users WHERE email = 'john@example.com' chạy nhanh hơn.

Index quan trọng vì nó giảm I/O và CPU, đặc biệt với bảng lớn hoặc ứng dụng đọc nhiều. Tuy nhiên, index làm tăng dung lượng lưu trữ và chậm các thao tác ghi (INSERT, UPDATE, DELETE) do phải cập nhật cấu trúc index.

Anh Minh：Rõ ràng, duthaho. Em có thể giải thích tại sao MySQL thường dùng B+ tree cho index, và nó khác gì với hash index?

duthaho：Dạ, MySQL dùng B+ tree vì nó hiệu quả cho nhiều loại truy vấn, đặc biệt là range queries (như WHERE id BETWEEN 100 AND 200) và ORDER BY. B+ tree lưu giá trị theo thứ tự, với nút lá chứa dữ liệu hoặc con trỏ tới hàng, và nút trung gian hỗ trợ tìm kiếm nhanh. Điều này giúp giảm số lần truy cập đĩa.

Hash index thì chỉ tối ưu cho truy vấn đẳng thức (WHERE email = 'john@example.com'), vì nó ánh xạ giá trị thành hash key, không lưu thứ tự. Hash index không hỗ trợ range queries hay sắp xếp, nhưng nhanh hơn cho đẳng thức trên bảng nhỏ. Trong InnoDB, hash index chỉ được dùng nội bộ (adaptive hash index), còn B+ tree là mặc định cho index thông thường.

Anh Minh：Tốt lắm! Bây giờ, anh muốn đi sâu vào InnoDB. Em có thể giải thích clustered index là gì, và nó khác gì với secondary index? Nếu có thể, hãy đưa ví dụ với bảng users.

duthaho：Dạ, trong InnoDB, clustered index là index mà dữ liệu bảng được lưu trữ vật lý theo thứ tự của nó, thường dựa trên khóa chính (primary key). Mỗi bảng chỉ có một clustered index, và nút lá của B+ tree chứa toàn bộ dữ liệu hàng. Ví dụ, với bảng users (id, username, email, status, last_login) có khóa chính id, clustered index trên id lưu toàn bộ hàng theo thứ tự id. Truy vấn WHERE id = 1 sẽ truy cập trực tiếp clustered index, rất nhanh.

Secondary index (non-clustered index) lưu giá trị cột được index và khóa chính, không chứa toàn bộ hàng. Ví dụ, index trên email (idx_email) lưu các cặp (email, id). Truy vấn WHERE email = 'john@example.com' sẽ tìm id trong idx_email, rồi truy clustered index để lấy các cột khác (bookmark lookup).

Khác biệt chính:

• Clustered index chứa toàn bộ dữ liệu, chỉ có một cái; secondary index chỉ chứa cột index và khóa chính, có thể có nhiều.
• Secondary index cần bookmark lookup, làm chậm nếu lấy nhiều cột không có trong index.

Anh Minh：Rất rõ ràng! Em có nhắc đến bookmark lookup. Để kiểm tra index có được sử dụng hiệu quả không, em thường dùng công cụ nào, và em có thể giải thích cách đọc output của EXPLAIN không?

duthaho：Dạ, để kiểm tra index, em dùng lệnh EXPLAIN trước truy vấn để xem kế hoạch thực thi (execution plan) của MySQL. EXPLAIN cho biết optimizer chọn index nào, quét bao nhiêu hàng, và các bước thực hiện.

Ví dụ, với bảng users có index idx_email:

EXPLAIN SELECT id, username FROM users WHERE email = 'john@example.com';

Output mẫu:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | users | ref | idx_email | idx_email | 302 | const | 1 | Using index condition

• type: Cho biết cách truy cập bảng:
  • ref: Dùng index để tìm kiếm đẳng thức, tốt vì chỉ quét ít hàng.
  • ALL: Full table scan, thường chậm.
  • index: Quét toàn index, nhanh hơn ALL nhưng vẫn không lý tưởng.
• possible_keys: Các index có thể dùng (idx_email).
• key: Index được chọn (idx_email).
• rows: Số hàng ước tính cần quét (1 là rất tốt).
• Extra: Thông tin bổ sung:
  • Using index: Covering index, chỉ dùng index, không cần truy bảng.
  • Using index condition: Index condition pushdown, lọc sớm.
  • Using filesort: Cần sắp xếp ngoài, có thể chậm.

Nếu EXPLAIN cho thấy type: ALL hoặc rows lớn, em sẽ kiểm tra xem có index phù duthaho không, hoặc chạy ANALYZE TABLE để cập nhật thống kê.

Anh Minh：Tuyệt, duthaho! Em hiểu rõ cách dùng EXPLAIN. Liên quan đến thống kê, em có thể giải thích index cardinality là gì, và tại sao nó quan trọng? Ngoài ra, ANALYZE TABLE giúp gì cho cardinality?

duthaho：Dạ, index cardinality là số lượng giá trị duy nhất trong index. Ví dụ, trong bảng users với 10,000 hàng, index trên id (khóa chính) có cardinality 10,000, còn index trên status (0 hoặc 1) có cardinality 2.

Cardinality quan trọng vì nó cho optimizer biết selectivity của index. Index có cardinality cao (như idx_email, ~9,900) sẽ trả về ít hàng hơn, được ưu tiên cho truy vấn như WHERE email = 'john@example.com'. Index có cardinality thấp (như idx_status) có thể bị bỏ qua, dẫn đến full table scan nếu quét index không hiệu quả.

ANALYZE TABLE cập nhật thống kê, bao gồm cardinality, bằng cách quét bảng hoặc lấy mẫu (ở InnoDB). Ví dụ:

ANALYZE TABLE users;

Nó đảm bảo optimizer biết chính xác số giá trị duy nhất, tránh chọn kế hoạch kém. Ngoài cardinality, ANALYZE TABLE cập nhật:

• Số hàng (row count).
• Histogram (MySQL 8.0+, cho cột không index).
• Kích thước hàng trung bình và trang index.
• Clustering factor (độ phân tán của secondary index).

Ví dụ, nếu thêm 9,000 hàng mà không chạy ANALYZE TABLE, optimizer có thể nghĩ idx_email có cardinality thấp, chọn full table scan. Sau ANALYZE TABLE, EXPLAIN sẽ cho thấy type: ref, key: idx_email.

Anh Minh：Rất tốt! Anh thấy em nắm vững thống kê. Tiếp theo, anh muốn hỏi về prefix index. Em có thể giải thích nó là gì, khi nào dùng, và nhược điểm của nó?

duthaho：Dạ, prefix index là index chỉ lưu N ký tự đầu của cột chuỗi (CHAR, VARCHAR, TEXT), thay vì toàn bộ giá trị, để tiết kiệm dung lượng. Ví dụ, với cột email (VARCHAR(100)) trong users, em tạo:

CREATE INDEX idx_email_prefix ON users (email(10));

Khi dùng:

• Cột chuỗi dài, như email hoặc TEXT, để giảm kích thước index.
• Truy vấn prefix, như WHERE email LIKE 'john.doe@%'.
• Giới hạn kích thước key (767 hoặc 3072 bytes trong InnoDB).
• Tiết kiệm bộ nhớ/disk hoặc cải thiện tốc độ ghi.

Nhược điểm:

• Giảm selectivity: Cardinality thấp hơn (e.g., email(5) có 500 giá trị duy nhất so với 9,900 của full index), khiến truy vấn trả về nhiều hàng.
• Hạn chế truy vấn: Không hỗ trợ LIKE '%example.com' hoặc so sánh toàn cột.
• Bookmark lookup: Nếu không phải covering index, cần truy clustered index.
• Chọn độ dài khó: Em dùng SELECT COUNT(DISTINCT LEFT(email, N)) để tìm N tối ưu.

Ví dụ, EXPLAIN SELECT * FROM users WHERE email LIKE 'john.doe@%' sẽ cho type: range, key: idx_email_prefix, nhưng nếu prefix quá ngắn, rows sẽ lớn, giảm hiệu quả.

Anh Minh：duthaho, em giải thích rất rõ! Bây giờ, mình chuyển sang full-text index. Nó là gì, và khi nào nên dùng thay vì LIKE '%term%'?

duthaho：Dạ, full-text index là index đặc biệt cho cột CHAR, VARCHAR, hoặc TEXT, dùng inverted index để tìm kiếm từ khóa nhanh trong văn bản. Nó hỗ trợ tìm kiếm ngôn ngữ tự nhiên, xếp hạng độ liên quan, và chế độ Boolean.

Khi dùng thay LIKE '%term%':

• Tìm kiếm trong văn bản lớn, như cột bio (TEXT) trong users chứa mô tả người dùng.
• Cần tính năng ngôn ngữ tự nhiên (stemming, e.g., “running” khớp “run”).
• Truy vấn phức tạp, như tìm “software -manager” (Boolean mode).
• Hiệu năng cao trên bảng lớn, vì LIKE '%term%' gây full table scan.

Ví dụ, tạo index:

CREATE FULLTEXT INDEX idx_bio_fulltext ON users (bio);

Truy vấn:

SELECT id, username
FROM users
WHERE MATCH(bio) AGAINST('software engineer');

• EXPLAIN cho thấy type: fulltext, key: idx_bio_fulltext, nhanh hơn LIKE '%software%' (full table scan).

Anh Minh：Tuyệt vời! Giờ anh muốn hỏi về JSON, một xu hướng phổ biến. Làm thế nào để index JSON column, và generated column giúp gì?

duthaho：Dạ, MySQL không index trực tiếp cột JSON, nhưng em có thể dùng generated column hoặc functional index (MySQL 8.0.13+).

Generated column trích xuất giá trị JSON thành cột scalar, sau đó index cột này. Ví dụ, với users có cột profile JSON (e.g., {"role": "admin", "age": 30}):

ALTER TABLE users
ADD COLUMN role VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(profile->'$.role')) STORED,
ADD INDEX idx_role (role);

Truy vấn WHERE role = 'admin' dùng idx_role, nhanh hơn WHERE profile->>'$.role' = 'admin' (full table scan).

Functional index index trực tiếp biểu thức JSON:

CREATE INDEX idx_age ON users ((JSON_UNQUOTE(profile->'$.age')));

Hỗ trợ WHERE profile->>'$.age' = '30'.

Generated column hữu ích vì:

• Tương thích MySQL 5.7+.
• Cho phép index B-tree hoặc full-text trên giá trị JSON.
• Dễ đọc và bảo trì hơn.

Ví dụ, EXPLAIN SELECT id FROM users WHERE role = 'admin' cho thấy type: ref, key: idx_role, rows: ~1.

Anh Minh：duthaho, em rất vững về JSON! Cuối cùng, anh muốn hỏi về multi-valued index (MVI). Nó là gì, và nó giải quyết vấn đề gì với JSON arrays?

duthaho：Dạ, multi-valued index, giới thiệu trong MySQL 8.0.17, là secondary index cho mảng JSON, tạo nhiều bản ghi index cho mỗi phần tử mảng, ánh xạ tới hàng. Nó giải quyết vấn đề tìm kiếm giá trị trong mảng JSON, vốn chậm vì full table scan.

Ví dụ, profile trong users có mảng skills (e.g., {"skills": ["python", "sql"]}). Tạo MVI:

ALTER TABLE users
ADD INDEX idx_skills ((CAST(profile->'$.skills' AS CHAR(50) ARRAY)));

Truy vấn:

SELECT id, username
FROM users
WHERE 'python' MEMBER OF (profile->'$.skills');

• MVI tạo entry như ("python", id=1), ("sql", id=1), cho phép lookup nhanh.
• EXPLAIN cho thấy type: ref, key: idx_skills, rows: ~2.

Lợi ích:

• Thay thế full table scan bằng index lookup.
• Đơn giản hóa schema, tránh bảng chuẩn hóa như user_skills.
• Hỗ trợ MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS.

Hạn chế:

• Tăng chi phí ghi vì nhiều entry.
• Chỉ hỗ trợ scalar values, không index mảng lồng nhau.

Anh Minh：duthaho, anh thực sự ấn tượng! Em đã bao quát từ cơ bản đến nâng cao, từ clustered index, cardinality, đến JSON và multi-valued index, còn dùng EXPLAIN để kiểm tra. Cảm ơn em đã chia sẻ kiến thức sâu rộng. Anh sẽ thảo luận với team và sớm phản hồi. Em có câu hỏi gì cho anh không?

duthaho：Dạ, em cảm ơn anh Minh và team đã cho em cơ hội. Em muốn hỏi về các dự án database ở công ty – team có đang dùng các tính năng như multi-valued index hay JSON nhiều không, và có thách thức gì nổi bật không?

Anh Minh：Câu hỏi hay! Team hiện dùng JSON cho dữ liệu linh hoạt, như cấu hình user, và đang thử nghiệm multi-valued index cho tìm kiếm tags. Thách thức là cân bằng hiệu năng đọc/ghi và quản lý schema động. Anh sẽ chia sẻ thêm nếu em vào team nhé! Cảm ơn em, chúc em ngày tốt lành!

duthaho：Dạ, em cảm ơn anh. Chúc anh ngày tốt lành ạ!