Table of contents
- Trường hợp chưa có index
- Trường hợp so sánh =
- Trường hợp so sánh >
- Trường hợp so sánh <
- Trường hợp between
- Trường hợp thêm order by
- Trường hợp order by desc
- Trường hợp thêm điều kiện and
- Trường hợp dùng function trong điều kiện where
- Trường hợp sử dụng composite index nhưng chỉ truy vấn theo cột không phải leading column
- Kết luận
Hôm nay là một ngày đẹp trời, và không vì lí do gì cả, hãy cùng nhau tìm hiểu một số tình huống thường gặp với index trong Oracle DB và xem cách tối ưu query như thế nào nhé. Chỉ cần sai một chút, query của bạn có thể mất cả phút để chạy, hay nặng hơn là “tèo” cả DB.
Tôi đang sử dụng Oracle DB cùng với tập data của StackOverflow.
Bảng POSTS được dùng nặng khoảng 27.5GB, có khoảng hơn 17tr bản ghi.
Trường hợp chưa có index
select * from POSTS where SCORE = 1;
Execution plan:
Plan hash value: 3606309814
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7348 | 9507K| 976K (1)| 00:00:39 |
|* 1 | TABLE ACCESS FULL| POSTS | 7348 | 9507K| 976K (1)| 00:00:39 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 1 - filter(""POSTS"".""SCORE""=1)"
Ok chưa có index thì full table scan là rõ rồi. Tạo index nào:
create index score_index on POSTS (SCORE);
Thời gian build được ra cây index là khá lâu do trong bảng POSTS này khá là nhiều dữ liệu.
[2025-02-08 17:41:52] completed in 34 s 80 ms
Trường hợp so sánh =
Khi đã có index, lúc này câu lệnh:
select * from POSTS where SCORE = 1;
sẽ có execution plan như sau:
Plan hash value: 2778087447
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7348 | 9507K| 5078 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| POSTS | 7348 | 9507K| 5078 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SCORE_INDEX | 7348 | | 17 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - access(""POSTS"".""SCORE""=1)"
Ta có thể thấy là tất cả các thông số như cost, time đã giảm đi rõ rệt.
Trường hợp so sánh >
select *
from POSTS
where SCORE > 10000
Plan hash value: 2778087447
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23035 | 29M| 15913 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| POSTS | 23035 | 29M| 15913 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SCORE_INDEX | 23035 | | 47 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SCORE">10000)
Số bản ghi có score > 10000 chiếm một phần nhỏ so với số bản ghi của cả bảng nên DB đã chọn INDEX RANGE SCAN.
Trường hợp so sánh <
Tương tự so sánh >
Trường hợp between
Tương tự so sánh >, INDEX RANGE SCAN sẽ được áp dụng khi mà câu lệnh where between sẽ cho ra một tập kết quả đủ nhỏ.
Trường hợp thêm order by
Order by cần được đặc biệt chú ý vì nó rất tiêu tốn tài nguyên của hệ thống.
select *
from POSTS
where SCORE > 50 order by score
Plan hash value: 4099675646
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 175K| 221M| 120K (1)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| POSTS | 175K| 221M| 120K (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | SCORE_INDEX | 175K| | 343 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SCORE">50)
Trường hợp order by desc
Kết quả execution plan vẫn như cũ, chỉ khác mỗi là dùng INDEX RANGE SCAN DESCENDING
Plan hash value: 2490828253
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 175K| 221M| 120K (1)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID | POSTS | 175K| 221M| 120K (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN DESCENDING| SCORE_INDEX | 175K| | 343 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SCORE">50)
Trường hợp thêm điều kiện and
select *
from POSTS
where SCORE > 50 and ANSWERCOUNT > 2;
Plan hash value: 2778087447
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 174K| 220M| 120K (1)| 00:00:05 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| POSTS | 174K| 220M| 120K (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | SCORE_INDEX | 175K| | 343 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 1 - filter(""ANSWERCOUNT"">2)"
" 2 - access(""POSTS"".""SCORE"">50)"
Ta thấy cost vẫn như vậy, vì bản chất là vẫn phải quét qua tất cả các row trên, access vào đó và lọc theo ANSWERCOUNT > 2, vậy nên ta kết luận khi thêm điều kiện and vào sẽ không bị ảnh hưởng đến hiệu năng.
Trường hợp có nhiều index
Khi tạo thêm index đơn lẻ, ta có thể phải tạo rất nhiều index, và nó có thể không hiệu quả nữa (insert, update, delete phải build lại index tree nhiều lần...). Vậy nên hãy dùng composite index nếu có thể.
Sử dụng composite index
Tạo một composite index:
create index score_answercount_index on POSTS (score, ANSWERCOUNT);
Cần phải chọn kĩ leading column, chính là cột đầu tiên trong định nghĩa của composite index, đây là cột quan trọng nhất. Nếu trong điều kiện where có so sánh leading column thì sẽ ăn index (tất nhiên vẫn phải theo tiêu chí là số bản ghi lọc được là rất ít so với số bản ghi của cả bảng).
select *
from POSTS
where SCORE > 10 and ANSWERCOUNT > 1;
Plan hash value: 1441565402
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 179K| 226M| 147K (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| POSTS | 179K| 226M| 147K (1)| 00:00:06 |
|* 2 | INDEX SKIP SCAN | SCORE_ANSWERCOUNT_INDEX | 179K| | 429 (4)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - access(""POSTS"".""SCORE"">10 AND ""ANSWERCOUNT"">1)"
" filter(""ANSWERCOUNT"">1)"
Nhưng nếu không có điều kiện so sánh leading column thì sẽ không ăn (trường hợp tập dữ liệu thoả mãn quá lớn).
select *
from POSTS
where ANSWERCOUNT > 1;
Plan hash value: 3606309814
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3050K| 3855M| 976K (1)| 00:00:39 |
|* 1 | TABLE ACCESS FULL| POSTS | 3050K| 3855M| 976K (1)| 00:00:39 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 1 - filter(""ANSWERCOUNT"">1)"
Trường hợp dùng function trong điều kiện where
select *
from POSTS
where to_char(SCORE) = '10';
Plan hash value: 3606309814
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 145M| 977K (1)| 00:00:39 |
|* 1 | TABLE ACCESS FULL| POSTS | 114K| 145M| 977K (1)| 00:00:39 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 1 - filter(TO_CHAR(""POSTS"".""SCORE"")='10')"
Lúc này, index không hoạt động, mà quét full bảng, vì trong cây index đang lưu giá trị ở dạng số, chúng ta đang thực hiện điều kiện trên to_char(score) chứ không phải score, nên index không hoạt động.
Giải pháp là chỉ cần đánh index cho cả hàm đó là được:
create index score_char_index on POSTS (to_char(SCORE));
Trường hợp này gọi là function-based index.
Trường hợp sử dụng composite index nhưng chỉ truy vấn theo cột không phải leading column
Với tập dữ liệu kết quả đủ nhỏ từ câu truy vấn, ta sẽ thấy INDEX SKIP SCAN
select *
from POSTS
where ANSWERCOUNT > 90;
Plan hash value: 1441565402
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 39750 | 7030 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| POSTS | 30 | 39750 | 7030 (1)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | SCORE_ANSWERCOUNT_INDEX | 30 | | 7005 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
" 2 - access(""ANSWERCOUNT"">90)"
" filter(""ANSWERCOUNT"">90)"
Lúc này, INDEX SKIP SCAN sẽ được áp dụng như sau: nhảy qua từng index score 1, 2, 7… tiếp tục truy cập vào sub index ANSWERCOUNT để lọc ra giá trị, tất nhiên nếu việc này quá tốn cost thì full table scan sẽ được áp dụng.
Kết luận
Trên đây là một số trường hợp thông dụng khi sử dụng truy vấn cùng với index. Nhìn chung, index rất hữu dụng, nhưng nếu lạm dụng hoặc đặt sai chỗ, thì dẫn đến nhiều thứ lắm, cuối cùng vẫn quy về tiền :D