Đánh index đừng chỉ dựa vào điều kiện WHERE!!!
Liệu có còn yếu tố gì ảnh hưởng đến câu lệnh query ngoài điều kiện WHERE không nhỉ?
Trước đây, tôi hay lầm tưởng rằng, khi đánh index thì cứ nhìn vào điều kiện trong WHERE mà triển, nhưng tôi đã lầm, vẫn có những yếu tố khác ngoài điều kiện WHERE có ảnh hưởng đến việc sử dụng các giải thuật quét index, mà dùng giải thuật khác thì chắc chắn là hiệu suất cũng khác nhau rồi. Anh em hãy cùng tôi tìm hiểu nhé.
1. Một số kiểu quét index trong Oracle
Khi sử dụng index, Oracle có thể chọn một trong số các cách sau để tối ưu hiệu suất khi truy vấn:
INDEX RANGE SCAN
Dùng khi truy vấn một khoảng giá trị liên tục trên cây index.
Hiệu quả khi index được đánh trên cột phù hợp với điều kiện
WHERE.
INDEX SKIP SCAN
Dùng khi truy vấn theo cột không là cột đầu tiên của composite index.
DB phải quét qua toàn bộ các nhóm giá trị của cột đầu tiên trước khi tìm đến cột cần quét.
Ít hiệu quả hơn
INDEX RANGE SCAN, nhưng vẫn nhanh hơn quét toàn bộ bảng.
INDEX FAST FULL SCAN
Đọc toàn bộ index nhanh hơn nhiều so với quét bảng (
TABLE ACCESS FULL).Quét song song nhiều block cùng lúc (có thể điều chỉnh bằng tham số hệ thống).
Không cần
TABLE ACCESS BY INDEX ROWID, vì dữ liệu đã có đủ trong index.
2. Thực tế: so sánh hiệu suất của các kiểu quét index
Tôi sẽ dùng bảng POSTS trong DB STACKOVERFLOW, có số bản ghi là 17142165. Và sẽ demo trên 2 trường là SCORE (điểm của bài post đó) và ANSWERCOUNT (số lượng câu trả lời trong bài post).
Và với câu lệnh sau:
select SCORE, ANSWERCOUNT from POSTS where ANSWERCOUNT > 10;
Trường hợp 0: chưa có index
Chưa có index gì thì chắc chắn là quét full bảng rồi. Execution plan:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30734 | 180K| 976K (1)| 00:00:39 |
|* 1 | TABLE ACCESS FULL| POSTS | 30734 | 180K| 976K (1)| 00:00:39 |
Trường hợp 1: đánh index trên cột ANSWERCOUNT
Tạo index:
create index ANSWERCOUNT_index on posts (ANSWERCOUNT);
Execution plan:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30734 | 180K| 14632 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| POSTS | 30734 | 180K| 14632 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ANSWERCOUNT_INDEX | 30734 | | 61 (2)| 00:00:01 |
✅ Tận dụng INDEX RANGE SCAN, quét nhanh hơn so với quét bảng.
❌ Có bước TABLE ACCESS BY INDEX ROWID, làm giảm hiệu suất khi lấy nhiều cột dữ liệu (do phải select cả bản ghi ra để lấy thêm dữ liệu trường SCORE, bước này nhiều khi còn tốn hiệu năng hơn cả quét index).
Trường hợp 2: Dùng Composite Index (SCORE, ANSWERCOUNT)
Tạo index và làm index cũ invisible đi:
create index SCORE_ANSWERCOUNT_index on posts (SCORE, ANSWERCOUNT);
alter index ANSWERCOUNT_index invisible ;
Khi truy vấn với điều kiện ANSWERCOUNT > 0:
Câu lệnh:
select SCORE, ANSWERCOUNT from POSTS where ANSWERCOUNT > 0;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5694K| 32M| 10604 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| SCORE_ANSWERCOUNT_INDEX | 5694K| 32M| 10604 (3)| 00:00:01 |
✅ Không có bước TABLE ACCESS BY INDEX ROWID → nhanh hơn (do dữ liệu cần lấy đã nằm hết trên dữ liệu của index rồi nên không cần access vào bản ghi nữa).
✅ Bước này có thể chạy song song trên nhiều block dữ liệu của index → Tốc độ cao hơn quét bảng (có thể cấu hình tham số của hệ thống, tuy nhiên cần có hiểu biết sâu để điều chỉnh cho phù hợp)
❌ Kém hiệu quả hơn INDEX RANGE SCAN do phải quét nhiều block dữ liệu hơn.
Nhưng nếu truy vấn ANSWERCOUNT > 10 thì sao?
Câu lệnh:
select SCORE, ANSWERCOUNT from POSTS where ANSWERCOUNT > 10;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30734 | 180K| 7005 (1)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | SCORE_ANSWERCOUNT_INDEX | 30734 | 180K| 7005 (1)| 00:00:01 |
✅ Không có bước TABLE ACCESS BY INDEX ROWID → nhanh hơn (do dữ liệu cần lấy đã nằm hết trên dữ liệu của index rồi nên không cần access vào bản ghi nữa).
❌ Vì SCORE là cột đầu tiên trong index, nên DB phải quét qua nhiều nhóm giá trị khác nhau → kém hiệu quả hơn INDEX RANGE SCAN.
Dưới đây là hình minh hoạ cấu trúc composite index với 2 cột SCORE và ANSWERCOUNT
Khi quét INDEX SKIP SCAN, DB sẽ quét qua hết các block index của cột SCORE, access vào đó và quét tiếp sub index là ANSWERCOUNT, bỏ qua ANSWERCOUNT 1 và 2. Nên gọi là INDEX SKIP SCAN.
Trường hợp 3: Đánh Composite Index (ANSWERCOUNT, SCORE)
Tạo index và làm index cũ invisible đi:
create index ANSWERCOUNT_SCORE_index on POSTS (ANSWERCOUNT, SCORE);
alter index SCORE_ANSWERCOUNT_index invisible ;
Execution plan:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30734 | 180K| 72 (2)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| ANSWERCOUNT_SCORE_INDEX | 30734 | 180K| 72 (2)| 00:00:01 |
✅ Tận dụng INDEX RANGE SCAN trực tiếp → Không cần quét dư thừa. ✅ Hiệu suất tốt hơn INDEX SKIP SCAN và INDEX FAST FULL SCAN trong trường hợp này.
3. Kết luận
Từ các trường hợp nêu trên, ta thấy việc chọn trường dữ liệu để select cũng có ảnh hưởng đến execution plan của câu lệnh. Các chiến lược này cũng có thể được áp dụng tương tự với các loại DB khác trong họ RDBMS.
Nếu có thể, tránh
TABLE ACCESS BY INDEX ROWIDbằng cách đưa các cột thường xuyên đượcSELECTvào index.INDEX FAST FULL SCANnhanh hơnTABLE ACCESS FULL, nhưng vẫn không tốt bằngINDEX RANGE SCANnếu điều kiện phù hợp.Khi tạo index, ưu tiên thứ tự các cột theo cách truy vấn thực tế, không chỉ dựa vào điều kiện
WHERE. Hi vọng bài viết này sẽ giúp anh em có thêm góc nhìn, cũng như chiến lược để tối ưu cho ứng dụng của mình.