So sánh hiệu năng Select * và Select 1 column - Hiểu lầm của rất nhiều anh em DEV

Mayfest2023

Bài viết này tôi sẽ giúp các anh em DEV hiểu đúng về bản chất trong quá trình tự học tối ưu SQL, đặc biệt liên quan đến vấn đề SELECT * và SELECT 1 cột. Những nguyên lý và Demo tôi chia sẻ trong bài viết này, có thể áp dụng đối với các loại cơ sở dữ liệu phổ biến: Oracle, SQL Server, PostgreSQL, MySQL…

1. Môi trường thực hiện Demo trong bài viết của tôi

Thực hiện Demo việc học tối ưu SQL và lý giải trên Cơ sở dữ liệu Oracle
Bảng được thực hiện đánh giá trong các bài viết: PAYMENT_TEST.
Một số thông tin về bảng này như sau
Bảng có 8894411 bản ghi

select count(*) from PAYMENT_TEST
8894411

Dung lượng của bảng : 1.140625 GB

select bytes/1024/1024/1024 "SIZE_GB" from user_segments where segment_name='PAYMENT_TEST'
SIZE_GB
1.140625

2. Nguyên lý và cách thức thực hiện đánh giá hiệu năng – phải biết khi học tối ưu SQL

Tại bài viết này, tôi chỉ đánh giá hiệu năng của việc xử lý câu lệnh trên Database Server, tôi** không xét đến các yếu tố bên ngoài** (như việc trả kết quẩ thu được, đường truyền giữa ứng dụng và database server). Việc đánh giá hiệu năng của 2 câu lệnh khi thực hiện trên Database server sẽ thực hiện thông qua nguyên lý: Đánh giá chiến lược thực thi của câu lệnh.

Lần lượt thực hiện phân tích chiến lược thực thi của câu lệnh SELECT * và SELECT 1 Cột.
So sánh chi phí giữa 2 chiến lược thực thi ấy, nếu câu lệnh nào có chi phí thấp hơn thì được coi là có hiệu năng tốt hơn.

Nếu bạn chưa biết về chiến lược thực thi, bạn có thể bài viết sau:

Học tối ưu SQL – Hiểu về 6 bước thực thi của câu lệnh và ứng dụng của nó trong tối ưu: đọc bài viết tại đây.
Học tối ưu SQL – Chiến lược thực thi của câu lệnh SQL là gì. đọc bài viết tại đây

3. Thực hiện các tình huống đánh giá hiệu năng giữa SELECT * và SELECT 1 CỘT

3.1. Trường hợp 1: Đánh giá khi Table không có bất kỳ Index nào cả

a. Chiến lươc thực thi của câu lệnh SELECT * như sau

select * from payment_test

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 3395M| 40309 (1)| 00:08:04 |
| 1 | TABLE ACCESS FULL| PAYMENT_TEST | 10M| 3395M| 40309 (1)| 00:08:04 |
----------------------------------------------------------------------------------

Kết quả:

Câu lệnh có chi phí thực hiện là COST = 40309
Thời gian ước lượng của chiến lược thực thi này là 8 phút 04 giây.
Câu lệnh sử dụng chiến lược thực thi là quét toàn bộ các block dữ liệu của bảng: TABLE ACCESS FULL
b. Chiến lượng thực thi của câu lệnh SELECT 1 COLUMN như sau

select price from payment_test

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8894K| 33M| 40194 (1)| 00:08:03 |
| 1 | TABLE ACCESS FULL| PAYMENT_TEST | 8894K| 33M| 40194 (1)| 00:08:03 |
----------------------------------------------------------------------------------

Bên trên tôi chỉ thực hiện SELECT 1 cột duy nhất (cột PRICE), tuy nhiên kết quả thu được gần như không hề khác gì so với việc SELECT *.

Cụ thể:

Câu lệnh có chi phí thực hiện là COST = 40194
Thời gian ước lượng của chiến lược thực thi này là 8 phút 03 giây.
Câu lệnh sử dụng chiến lược thực thi là quét toàn bộ các block dữ liệu của bảng: TABLE ACCESS FULL

3.2. Trường hợp 2: Đánh giá hiệu năng khi Table có sử dụng Index

Bước 1: Thực hiện tạo Index trên cột PRICE của bảng PAYMENT_TEST phục vụ cho Demo

create index idx_price on payment_test(price)

Bước 2: Thực hiện đánh giá hiệu năng của câu lệnh SELECT * và SELECT 1 cột

a. Đánh giá câu lệnh SELECT * như sau

select * from payment_test
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8894K| 950M| 40285 (1)| 00:08:04 |
| 1 | TABLE ACCESS FULL| PAYMENT_TEST | 8894K| 950M| 40285 (1)| 00:08:04 |
----------------------------------------------------------------------------------

Kết quả như sau:

Về mặt chiến lược thực thi: câu lệnh vẫn thực hiện giải thuật TABLE ACCESS FULL.
Chi phí thực hiện của câu lệnh là 40285
Thời gian thực hiện ước lượng: 8 phút 04s
b. Đánh giá câu lệnh SELECT 1 COLUMN – trên trường TXDATE

select txdate from payment_test where price=19300

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1697 | 20364 | 1276 (0)| 00:00:16 |
| 1 | TABLE ACCESS BY INDEX ROWID| PAYMENT_TEST | 1697 | 20364 | 1276 (0)| 00:00:16 |
|* 2 | INDEX RANGE SCAN | IDX_PRICE | 1697 | | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Kết quả như sau:

Chiến lược thực thi của câu lệnh SELECT 1 COLUMN (cột TXDATE) lúc này đã không còn là TABLE ACCESS FULL nữa, thay vào đó là một chiến lược sử dụng INDEX (giải thuật INDEX RANGE SCAN).
Với chiến lược thực thi mới này, câu lệnh có chi phí thực hiện chỉ là COST = 1276 (chỉ bằng ~3% so với chi phí thực hiện của câu lệnh SELECT *)
Thời gian của câu lệnh dự kiến chỉ mất 16s

c. Bây giờ tôi sẽ thực hiện thêm một trường hợp nữa: Đánh giá câu lệnh SELECT 1 COLUMN – nhưng trên trường PRICE

select price from payment_test where price=19300

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1697 | 6788 | 6 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_PRICE | 1697 | 6788 | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------

Kết quả như sau:

Chiến lược thực thi của câu lệnh bây giờ cũng sử dụng INDEX (với giải thuật INDEX RANGE SCAN), tuy nhiên khi xem kỹ ở chiến lược thực thi, chúng ta thấy câu lệnh lúc này đã không cần thực hiện bước TABLE ACCESS BY INDEX ROWID giống như với trường hợp SELECT cột PRICE.
Chi phí thực hiện của câu lệnh lúc này rất thấp: COST = 6.
Thời gian thực hiện của câu lệnh chỉ còn 01s

4. Một số kết luận từ tình huống Demo trên – hãy lưu ý nội dung này khi muốn học tối ưu SQL

Thứ nhất: Nếu chung về giải thuật thực hiện thì SELECT 1 COLUMN gần như có hiệu năng xử lý trên Database chẳng khác gì so với SELECT * cả.
Thứ hai: Cùng là SELECT 1 COLUMN, tuy nhiên các column khác nhau có thể có kết quả khác nhau rất nhiều (như ví dụ trên: 16s so với 1s).
CHỐT LẠI: ĐÁNH GIÁ DỰA TRÊN CHIẾN LƯỢC THỰC THI CỦA CÂU LỆNH nhế anh em. Đừng có học tối ưu SQL mà không hiểu rõ bản chất.
Ghi chú: Anh em có thể xem link bài viết gốc tại đây nhé: LINK BÀI VIẾT GỐC.