Thanaoscar

Bài 13: Tối Ưu Full Table Scan – Khi Nào Cần Index?

Thana Oscar's photo
Thana Oscar
·

4 min read

"Full Table Scan không phải kẻ thù – nhưng đừng để nó trở thành nút thắt cổ chai!"

1. Vấn Đề: Query Trên Bảng Lớn Bị Chậm Do Full Scan

Scenario:
Bảng transactions chứa 10 triệu giao dịch, với các cột:

  • transaction_id (PK)

  • user_id

  • amount

  • created_at

Thực hiện query:

SELECT user_id, SUM(amount)  
FROM transactions  
WHERE created_at BETWEEN '2024-01-01' AND '2024-03-31'  
GROUP BY user_id;

Kết quả: Thời gian thực thi ~12 giây.

2. Phân Tích Execution Plan

Bước 1: Chạy EXPLAIN ANALYZE (PostgreSQL)
GroupAggregate (cost=250000.00..300000.00 rows=50000 width=20) 
               (actual time=12000.123..12500.456 rows=15000 loops=1)  
  Group Key: user_id  
  -> Sort (cost=250000.00..275000.00 rows=1000000 width=20) 
          (actual time=12000.123..12400.789 rows=900000 loops=1)  
        Sort Key: user_id  
        Sort Method: external merge  Disk: 20000KB  
        -> Seq Scan on transactions (cost=0.00..150000.00 rows=1000000 width=20) 
                                    (actual time=0.012..8000.123 rows=900000 loops=1)  
              Filter: (created_at BETWEEN '2024-01-01' AND '2024-03-31')  
              Rows Removed by Filter: 9100000
Bước 2: Giải Mã Vấn Đề

  • Full Table Scan (Seq Scan): Query quét toàn bộ 10 triệu dòng → Tốn 8000 ms.

  • Sort trên Disk: Do không có index phù hợp, database phải sắp xếp 900,000 dòng trên disk → Tăng thời gian xử lý.

  • Bottleneck Chính:

    1. Full Scan trên created_at.

    2. Sort dữ liệu thô trước khi aggregate.

3. Tối Ưu: Sử Dụng Index Phù Hợp

Bước 1: Tạo B-tree Index Trên created_at
CREATE INDEX idx_transactions_created_at ON transactions(created_at);
Bước 2: Chạy Lại Query – Execution Plan Mới
GroupAggregate (cost=50000.00..55000.00 rows=15000 width=20) 
               (actual time=2500.123..3000.456 rows=15000 loops=1)  
  Group Key: user_id  
  -> Sort (cost=50000.00..51250.00 rows=50000 width=20) 
          (actual time=2500.123..2800.789 rows=900000 loops=1)  
        Sort Key: user_id  
        Sort Method: quicksort  Memory: 100000KB  
        -> Index Scan using idx_transactions_created_at on transactions 
                   (cost=0.42..25000.00 rows=900000 width=20) 
                   (actual time=0.032..1000.123 rows=900000 loops=1)  
              Index Cond: ((created_at >= '2024-01-01'::date) AND 
                           (created_at <= '2024-03-31'::date))
Kết Quả:

  • Thay Thế Full Scan Bằng Index Scan:

    • Cost giảm từ 150,000 → 25,000 (83%).

    • Thời gian quét dữ liệu từ 8000 ms → 1000 ms.

  • Sort trên Memory: Nhờ giảm số dòng cần xử lý, database sử dụng memory thay vì disk.

  • Tổng Thời Gian: Từ 12 giây → 3 giây (75% cải thiện).

4. Tổng Kết

Chỉ SốTrước Khi Tối ƯuSau Khi Tối Ưu
Total Cost300,00055,000
Execution Time12,500 ms3,000 ms
Phương Pháp Truy CậpFull Table ScanIndex Scan
Sort MethodDisk (External Merge)Memory (Quicksort)

Lý Do Hiệu Quả:

  • B-tree index cho phép tìm kiếm phạm vi (BETWEEN) hiệu quả.

  • Giảm I/O do chỉ đọc các dòng liên quan.

5. Bài Tập Thực Hành

Dataset Mẫu:
CREATE TABLE logs (  
    log_id BIGSERIAL PRIMARY KEY,  
    event_type VARCHAR(50),  
    event_time TIMESTAMP,  
    user_agent TEXT  
);  
-- Insert 5 triệu dòng dữ liệu
Yêu Cầu:

  1. Chạy query:

     SELECT event_type, COUNT(*)  
 FROM logs  
 WHERE event_time BETWEEN '2024-01-01' AND '2024-06-30'  
 GROUP BY event_type;

  2. Phân tích Execution Plan và xác định nguyên nhân Full Scan.

  3. Tạo index phù hợp và đo lường hiệu suất.

Câu Hỏi:
  • Tại sao đôi khi database vẫn chọn Full Scan dù đã có index?

6. Mở Rộng & Thảo Luận

Khi Nào Index Không Giúp Giảm Full Scan?

  • Selectivity Thấp: Khi điều kiện WHERE trả về >30% dòng (ví dụ: WHERE status IN ('active', 'pending')).

  • Index Không Cover: Nếu query cần đọc thêm cột không nằm trong index (ví dụ: SELECT *).

  • Thống Kê Sai: Database ước tính sai số dòng → chọn plan không tối ưu.

Loại Index và Ứng Dụng
Loại IndexTrường Hợp Sử DụngVí Dụ
B-treeTìm kiếm phạm vi, sắp xếpWHERE created_at BETWEEN ...
HashEquality check (=)WHERE user_id = 123
Covering IndexTránh truy cập bảng gốcCREATE INDEX ... INCLUDE (amount)
Partial IndexChỉ index subset dữ liệuWHERE status = 'active'
Case Study: Tối Ưu Query Với Covering Index
-- Thay vì chỉ index created_at:  
CREATE INDEX idx_transactions_created_at_covering 
ON transactions(created_at) INCLUDE (user_id, amount);

Execution Plan:

GroupAggregate  (cost=15000.00..18000.00 rows=15000 width=20)  
  -> Index Only Scan using idx_transactions_created_at_covering on transactions  
        Index Cond: (created_at BETWEEN '2024-01-01' AND '2024-03-31')
  • Ưu Điểm: Không cần đọc bảng gốc → Giảm I/O.
Cảnh Báo Khi Tạo Index

  • Chi Phí Duy Trì: Mỗi lần INSERT/UPDATE/DELETE, index phải được cập nhật → Ảnh hưởng hiệu suất ghi.

  • Sử Dụng Composite Index: Kết hợp các cột thường xuyên query cùng nhau (ví dụ: (user_id, created_at)).

Kết Luận

Full Table Scan không phải lúc nào cũng xấu – nhưng khi nó trở thành nút thắt, index là vũ khí tối ưu mạnh nhất. Trong ví dụ này, B-tree index đã giảm 75% thời gian thực thi. Trong bài tiếp theo (Bài 14), chúng ta sẽ giải quyết các vấn đề liên quan đến Sort và Hash Aggregate!

👉 Bài Tập Về Nhà: Tạo một covering index cho bảng logs và đo lường hiệu suất khi query event_type và event_time!

SQL