Đã đăng vào Thứ Ba, 11:10 CH 7 phút đọc

162

Dùng Index Database sao cho chuẩn? Hướng dẫn thực chiến với 100 triệu dòng dữ liệu

Rất nhiều anh em developer chắc chắn đã gặp cảnh này: code ở máy local (dev) thì chạy vèo vèo, trang load tính bằng mili giây, SQL viết kiểu gì cũng nhanh. Nhưng vừa deploy lên production, đối mặt với traffic triệu view, câu query lập tức timeout, CPU của database dựng đứng lên 100%.

Để tránh cái cảnh "trên máy tôi vẫn chạy ngon mà", cách tốt nhất là anh em phải giả lập dữ liệu lớn ngay từ lúc phát triển. Chỉ khi lượng dữ liệu đủ lớn, những cái bẫy hiệu năng tiềm ẩn mới hiện nguyên hình.

Bước 1: Tạo ngay 100 triệu dòng dữ liệu ("Fake it till you make it")

Nếu bảng chỉ có vài nghìn dòng, việc quét toàn bộ bảng (Full Table Scan) đôi khi còn nhanh hơn dùng Index. Muốn test chiến lược Index, phải "tăng độ khó cho game".

Đừng dại dột viết script Python/PHP vòng lặp for để insert từng dòng, tốc độ mạng sẽ làm bạn nản lòng ngay. PostgreSQL có hàm generate_series cực xịn, hàm dưới đây có thể giúp bạn tạo 100 triệu dòng log giả lập chỉ trong vài phút.

-- Tạo hàm để sinh dữ liệu giả lập số lượng lớn cực nhanh
CREATE OR REPLACE FUNCTION populate_large_table(target_rows BIGINT)
RETURNS VOID AS $$
BEGIN
  -- Insert theo lô (batch) để tránh overhead của việc commit từng dòng
  INSERT INTO user_events (user_id, event_type, created_at)
  SELECT
    (random() * 1000000)::INTEGER, -- Giả lập 1 triệu user khác nhau
    CASE (random() * 3)::INTEGER    -- Random loại thao tác
      WHEN 0 THEN 'login'
      WHEN 1 THEN 'logout'
      WHEN 2 THEN 'purchase'
      ELSE 'view'
    END,
    NOW() - (random() * INTERVAL '365 days') -- Phân bổ trong 1 năm qua
  FROM generate_series(1, target_rows);
END;
$$ LANGUAGE plpgsql;

-- Thực thi (Lưu ý: Tốn dung lượng ổ cứng đấy nhé, thời gian tùy thuộc vào máy mạnh hay yếu)
-- SELECT populate_large_table(100000000);

Khi 100 triệu dòng này đã nằm trên ổ cứng, thử chạy SELECT * FROM user_events WHERE user_id = 12345. Bạn sẽ thấy thời gian chạy nhảy từ vài mili giây lên vài giây, thậm chí cả chục giây.

Lúc này, giá trị của Index mới bắt đầu tỏa sáng.

Bước 2: Index không phải cứ nhiều là tốt

Sai lầm kinh điển của người mới (newbie) là đánh index cho... tất cả các cột. Hãy nhớ: Index bản chất là đổi không gian lấy thời gian, và nó có cái giá của nó.

Đọc nhanh hơn, nhưng Ghi chậm hơn. Mỗi khi bạn INSERT, UPDATE, hay DELETE, database không chỉ sửa file dữ liệu mà còn phải cập nhật đồng bộ các cây Index liên quan. Nếu bảng có 5 cái Index, thêm 1 dòng dữ liệu đồng nghĩa với việc phải bảo trì 5 cái cây. Với các nghiệp vụ ghi nhiều đọc ít (như log, IoT sensor), quá nhiều Index là một thảm họa.

Chiến lược gợi ý:

Cột hay query: Đánh Index (Ví dụ: Khóa ngoại, timestamp).
Cột hay update: Cân nhắc kỹ trước khi đánh Index.
Cột có độ phân biệt thấp (Low Cardinality): KHÔNG đánh Index. Ví dụ: "Giới tính" hay "Trạng thái (0/1)". Nếu Database quét Index xong thấy vẫn phải lôi ra 50% số dòng của bảng để check, nó sẽ vứt luôn cái Index và quét Full Table cho nhanh. Index lúc này là vô dụng.

Bước 3: Đừng đoán mò, hãy xem Execution Plan

Đừng mặc định rằng cứ viết WHERE user_id = ... là database sẽ dùng index. Trình tối ưu hóa (Optimizer) đôi khi lười (hoặc khôn) hơn chúng ta tưởng.

Luôn dùng EXPLAIN (hoặc EXPLAIN ANALYZE trong PG) để xem database thực sự đang làm gì:

EXPLAIN ANALYZE SELECT * FROM user_events WHERE user_id = 42;

Nếu thấy Index Scan: Chúc mừng, index đã hoạt động.
Nếu thấy Seq Scan (Sequential Scan): Nghĩa là nó đang quét toàn bộ bảng. Cần check lại xem dữ liệu phân bố có đều không, hoặc điều kiện query có khớp với index không.

Các kiểu Index thường dùng & Cách tránh hố

1. Composite Index (Index phức hợp): Thứ tự là chìa khóa

Khi điều kiện query gồm nhiều trường, ví dụ tìm "lịch sử của user A trong ngày B", index đơn lẻ thường không đủ nhanh. Bạn cần Composite Index:

CREATE INDEX idx_user_date ON user_events(user_id, created_at);

Chú ý thứ tự (Nguyên tắc Leftmost Prefix):

Index này chạy tốt cho WHERE user_id = ?.
Nó chạy tốt cho WHERE user_id = ? AND created_at = ?.
NHƯNG, nếu chỉ query WHERE created_at = ?, index này phế hoàn toàn. Luôn đặt cột được lọc thường xuyên nhất sang bên trái cùng.

2. Unique Index: Vừa nhanh vừa an toàn

Nếu nghiệp vụ yêu cầu Email hoặc SĐT không được trùng, hãy dùng Unique Index. Nó vừa tăng tốc tìm kiếm, vừa là chốt chặn cuối cùng ở tầng database để ngăn dữ liệu rác (dirty data) do lỗi logic code gây ra.

CREATE UNIQUE INDEX idx_unique_email ON users(email);

3. Các loại Index chuyên dụng

Full-Text: Đừng dùng LIKE '%keyword%' để tìm kiếm trong văn bản dài; nó chậm như rùa. MySQL và PG đều có Index Full-Text chuyên dụng.
GIN Index: Đặc sản của PG, chuyên trị JSONB hoặc dữ liệu mảng (Array).
Bitmap Index: Phù hợp cho Data Warehouse, tốt cho các query tổ hợp trên các trường ít giá trị (như Trạng thái, Tag).

Đơn giản hóa hạ tầng để test

Với người mới (hoặc cả senior), việc cài đặt và quản lý nhiều phiên bản database để test hiệu năng thực sự rất phiền phức.

Đây là lúc ServBay tỏa sáng.

ServBay cho phép bạn cài đặt database chỉ với một cú click. Quan trọng hơn, nó hỗ trợ chạy đồng thời đa instance. Nghĩa là bạn có thể bật MySQL 8.0 và MariaDB cùng lúc, hoặc chạy song song PostgreSQL 12 và 16 để so sánh.

Điều này cực tiện lợi khi test migrate dữ liệu hoặc so sánh hiệu năng của cùng một câu SQL phức tạp trên các phiên bản database khác nhau.

Triển khai một chạm: Bao gồm MySQL, PostgreSQL, MongoDB, Redis, MariaDB... không cần tìm bộ cài hay cấu hình Brew/Docker lằng nhằng.
Mì ăn liền (Out-of-the-Box): Cài xong là biến môi trường tự nhận, gõ psql hay mysql trong terminal là vào được luôn.
Hệ thống sạch sẽ: Các service chạy độc lập. Nó quản lý stack DB và môi trường Python của bạn trong sandbox, đảm bảo khi xóa đi không để lại file rác trong hệ thống.

Lời kết

Không có "đáp án mẫu" cho việc tối ưu database, chỉ có sự đánh đổi (trade-offs).

Bạn có chấp nhận ghi chậm để đọc nhanh không? Có chấp nhận tốn dung lượng ổ cứng để đổi lấy thời gian query không? Tất cả phụ thuộc vào nghiệp vụ, thậm chí là việc bạn có chấp nhận dữ liệu bị delay vài giây hay không.

Thực hành là tiêu chuẩn duy nhất để kiểm nghiệm chân lý. Hãy thử tạo 100 triệu dòng dữ liệu trên máy và tự mình trải nghiệm nhé.