Bài viết này sẽ mang lại các giá trị gì cho người đọc
- Nhận thức được FULL TABLE SCAN không phải là điều TỆ NHẤT ảnh hưởng hiệu năng của câu lệnh.
- Demo nguyên lý trong các Cơ sở dữ liệu Oracle, PostgreSQL, MySQL, MSSQL.
- Khi tối ưu câu lệnh SQL, tôi thường xem ngay ĐIỀU GÌ ĐẦU TIÊN?
Danh sách toàn bộ các bài viết tối ưu khác, bạn có thể xem tổng hợp tại đây: https://wecommit.com.vn/tong-hop-link-cac-bai-viet-hay-tren-trang-wecommit-com-vn/
Ghi chú: Truy cập nhóm Zalo Tư Duy – Tối Ưu – Khác Biệt để nhận mật khẩu và đọc toàn bộ các bài viết về tối ưu của tối cho cộng đồng (liên tục cập nhật bài mới hàng tuần): Click để tham gia nhóm (MIỄN PHÍ)
1. Điều gì mà tôi phải chú ý đầu tiên khi tối ưu câu lệnh SQL
Trong các dự án mà tôi thực hiện tối ưu, khi đánh giá một câu lệnh, tôi luôn làm 1 việc, đó là:
- Xem chiến lược thực thi của câu lệnh ấy có phải thực hiện hành động SORT hay không?
Tại sao lại phải đánh giá việc này, mọi người sẽ thấy chi tiết trong các Demo sau
2. Demo thời gian ảnh hưởng hiệu năng của hành động Sort trong các cơ sở dữ liệu khác nhau
2.1. Demo trong cơ sở dữ liệu Oracle
Để các bạn có thể hình dung được mức độ ảnh hưởng hiệu năng của công việc SORT, chúng ta cùng xem xét ví dụ sau.
Ghi chú:
- Bảng emp hiện tại chưa có bất kỳ Index nào
- Bảng này có 1 triệu 100K bản ghi
Thực hiện đánh giá hiệu năng của 2 câu lệnh sau:
select * from emp;
select * from emp order by id;
Phân tích chiến lược thực thi của câu lệnh đầu tiên:
select * from emp;
Chiến lược thực thi của câu lệnh thứ hai:
select * from emp order by id;
Đánh giá:
- Câu lệnh thứ hai có thời gian cần thực hiện chậm hơn 500% so với câu lệnh đầu tiên (41 phút 28s so với 7 phút 59s)
- Hai câu lệnh khi thực hiện đều phải thực hiện quét toàn bộ dữ liệu của bảng EMP, điều khác nhau duy nhất là câu lệnh thứ 2 phải thực hiện bước sắp xếp dữ liệu (SORT ORDER BY).
- Tại đây ta có thể thấy công việc SORT cực kỳ tiêu tốn tài nguyên và mất rất nhiều thời gian để thực hiện.
2.2. Demo trong cơ sở dữ liệu MySQL
Thực hiện đánh giá trên bảng ORDERDETAILS.
Thông tin của bảng như sau:
- Bảng này có 2996 bản ghi
- Cấu trúc của bảng như bên dưới
mysql> select count(*) from orderdetails;
+———-+
| count(*) |
+———-+
| 2996 |
+———-+
1 row in set (0.06 sec)
mysql> desc orderdetails;
+—————–+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+—————+——+—–+———+——-+
| orderNumber | int | NO | PRI | NULL | |
| productCode | varchar(15) | NO | PRI | NULL | |
| quantityOrdered | int | NO | | NULL | |
| priceEach | decimal(10,2) | NO | MUL | NULL | |
| orderLineNumber | smallint | NO | | NULL | |
+—————–+—————+——+—–+———+——-+
5 rows in set (0.00 sec)
Thực hiện phân tích 2 câu lệnh
select * from orderdetails;
select * from orderdetails order by orderlinenumber;
Chiến lược thực thi của 2 câu lệnh như sau:
Câu lệnh thứ nhất:
select * from orderdetails;
| EXPLAIN
| -> Table scan on orderdetails (cost=302.10 rows=2996) (actual time=0.088..1.317 rows=2996 loops=1)
Câu lệnh thứ hai
select * from orderdetails order by orderlinenumber;
| EXPLAIN
| -> Sort: orderdetails.orderLineNumber (cost=302.10 rows=2996) (actual time=2.023..2.390 rows=2996 loops=1)
-> Table scan on orderdetails (cost=302.10 rows=2996) (actual time=0.062..1.220 rows=2996 loops=1)
Chúng ta có thể thấy thời gian để thực hiện việc sắp xếp dữ liệu chiếm nhiều hơn 150% so với thời gian thực hiện quét full bảng ORDERDETAILS!!
Chi tiết cách lấy chiến lược thực thi và Demo bạn có thể xem Video ở cuối bài này.
2.3. Demo trên cơ sở dữ liệu PostgreSQL
Tại đây chúng ta cũng sẽ đánh giá 2 câu lệnh
select * from film;
select * from film order by length;
Thông số của bảng film được đề cập chi tiết trong Video bên dưới bài
Thực hiện phân tích chiến lược thực thi câu lệnh thứ nhất
postgres=# explain select * from film;
QUERY PLAN
———————————————————-
Seq Scan on film (cost=0.00..65.00 rows=1000 width=386)
(1 row)
Đánh giá chiến lược thực thi câu lệnh thứ hai
postgres=# explain select * from film order by length;
QUERY PLAN
—————————————————————-
Sort (cost=114.83..117.33 rows=1000 width=386)
Sort Key: length
-> Seq Scan on film (cost=0.00..65.00 rows=1000 width=386)
(3 rows)
Tại đây anh em cũng có thể thấy rằng thông số của việc SORT cao hơn rất nhiều lần so với việc chỉ thực hiện quét toàn bộ dữ liệu của bảng (đối với PostgreSQL thì việc này thể hiện ở đoạn Seq Scan, tương đương với việc FULL TABLE SCAN)
2.4. Demo trên cơ sở dữ liệu MSSQL
Phần này anh em có thể xem trong Video demo bên dưới bài nhé.
3. Vì sao sắp xếp dữ liệu lại có thể tiêu tốn tài nguyên nhiều đến thế?
Khi thực hiện công việc SORT, hệ thống cần phải sử dụng các tài nguyên
- Thứ nhất: Sử dụng CPU, với dữ liệu cần sắp xếp càng lớn thì hệ thống càng cần nhiều CPU để thực hiện.
- Thứ hai: Sử dụng bộ nhớ để làm bước đệm cho quá trình sắp xếp dữ liệu. Điều đáng chú ý ở đây là:
- Nếu phần đệm cho việc sắp xếp này quá lớn (vượt ngưỡng cung cấp của Cơ sở dữ liệu), hệ thống sẽ sử dụng một phần tài nguyên từ Temporary Tablespace (bản chất là lấy từ ổ cứng). Khi việc này xảy ra, câu lệnh sẽ có hiệu năng rất tê.
Làm thế nào biết được một câu lệnh có xảy ra hiện tượng sắp xếp dữ liệu sử dụng tài nguyên tử ổ cứng (Disk Sort) hay không?
Trong các trường hợp này, chúng ta không chỉ dựa vào mỗi chiến lược thực thi của câu lệnh, mà còn cần xem xét thêm những thông tin thống kê của câu lệnh khi thực hiện. Ví dụ như sau
945 recursive calls
22 db block gets
141748 consistent gets
223893 physical reads
0 redo size
72307227 bytes sent via SQL*Net to client
807198 bytes received via SQL*Net from client
73336 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
4. Tối ưu câu lệnh có SORT như thế nào?
Có một kỹ thuật vô cùng đơn giản mà anh em có thể áp dụng: Đó là tạo INDEX.
Tại đây tôi thực hiện tạo thêm Index trên cột ID của bảng EMP
create index idx_id_emp_wecommit on emp(id);
Đánh giá lại câu lệnh sau khi tạo Index
- Câu lệnh trước khi có Index
select * from emp order by id;
- Câu lệnh sau khi có Index
select * from emp order by id;
Kiểm tra chiến lược thực thi hiện tại:
Hệ thống đã loại bỏ toàn bộ bước thực thi SORT ORDER BY, do đó thời gian của câu lệnh giảm từ 41 phút 28s xuống còn 24ph 25s
5. Có phải bất kỳ trường hợp nào thêm Index cũng xử lý được vấn đề của SORT ORDER BY không?
Câu trả lời là KHÔNG!.
Anh em xem video Demo và phân tích để hiểu rõ hơn vấn đề này nhé.
6. Video DEMO và phân tích chi tiết
7. Suy nghĩ sâu hơn và thảo luận áp dụng trong thực tế.
Tất cả các thành viên nhóm đặc quyền tại Wecommit đã được biết về bí kíp biến tất cả những thứ mình học trở nên có giá trị.
Chi tiết về bí kíp các cấp độ của việc học, tại sao chỉ Biết và Hiểu là 2 cấp độ thấp nhất và tạo ra giá trị chuyển đổi cực kỳ ít: https://wecommit.com.vn/bi-kip-cac-cap-do-cua-viec-hoc/
Đối với nội dung bài chia sẻ này, các anh em cùng suy nghĩ và trả lời những câu hỏi sau:
- Cách thức nào có thể chuyển hóa những gì tôi chia sẻ trong bài này lên tối thiểu cấp độ thứ 3.
- Điều cần làm ngay bây giờ là gì?
- Anh em hãy cùng thảo luận nhé và phản hồi với tôi nhé.
8. Liên hệ với tôi
Tác giả : Trần Quốc Huy – Founder & CEO Wecommit
Facebook : https://www.facebook.com/tran.q.huy.71
Email : huy.tranquoc@wecommit.com.vn
Youtube : Trần Quốc Huy
Số điện thoại: 0888549190