Bài viết này tôi sẽ giúp mọi người một cách tiếp cận để tối ưu câu lệnh Delete.
Trái với lầm tưởng của nhiều anh em lập trình viên (do chỉ đọc tài liệu, nhưng không hiểu rõ bản chất của Index): Cứ sử dụng Index thì các câu lệnh DML (trong đó có câu lệnh Delete) sẽ bị chậm đi. Bài viết này tôi sẽ Demo việc tăng tốc câu lệnh Delete từ thời gian chục phút xuống còn đơn vị giây bằng cách thức sử dụng Index.
Kỹ thuật này được tôi áp dụng tại nhiều dự án của Wecommit. Danh sách các dự án các bạn có thể kiểm chứng tại đây.
Tác giả của bài viết: Trần Quốc Huy – CEO Wecommit.
Lưu ý: Nếu bạn muốn nhận thông báo khi tôi có bài viết mới hoặc Video mới về nội dung tối ưu SQL, tối ưu Database.
Bạn tham gia nhóm cộng đồng Zalo sau (miễn phí): Click để tham gia cộng đồng của tôi
1. Điều phải nhớ đầu tiên: Câu lệnh DELETE cũng có chiến lược thực thi (SQL Execution Plans)
Để thực hiện tối ưu một câu lệnh, cách bắt đầu từ chiến lược thực thi (SQL Execution Plans) sẽ đảm bảo bạn có một “công thức” thành công và ổn định.
Lưu ý rằng: Không phải chỉ những câu lệnh SELECT mới có chiến lược thực thi, câu lệnh DELETE cũng có điều ấy.
Nếu bạn chưa từng nghe tới chiến lược thực thi của câu lệnh, bạn có thể đọc lại các bài chia sẻ khác của tôi tại đây
- SQL Execution Plan trong tối ưu SQL
- Hướng dẫn cách đọc SQL Execution Plans trong SQL Server
- Làm thế nào để đọc SQL Execution Plans trong Oracle
- Không viết lại câu lệnh nhưng vẫn có thể tối ưu vượt trội thời gian thực thi – sử dụng sql execution plans
2. Demo tối ưu câu lệnh Delete sử dụng Index
2.1. Thông tin Objects được sử dụng trong câu lệnh
Bảng wecommit_test đã được cập nhật thông tin statistics mới nhất (up to date).
Bảng này có 8.795.068 bản ghi, dung lượng của bảng ~ 1.5GB
2.2. Câu lệnh DELETE tại thời điểm bảng chưa có Index
Giả sử tôi cần xóa các bản ghi có giá trị cột ORDERQTTY bằng 3000
delete from wecommit_test where orderqtty=3000;
Chiến lược thực thi của câu lệnh lúc này:
Plan hash value: 284335732
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 785 | 3140 | 55268 (1)| 00:11:04 |
| 1 | DELETE | WECOMMIT_TEST | | | | |
|* 2 | TABLE ACCESS FULL| WECOMMIT_TEST | 785 | 3140 | 55268 (1)| 00:11:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ORDERQTTY"=3000)
Phân tích từ chiến lược thực thi trên:
- Đầu tiên câu lệnh cần quét toàn bộ các block dữ liệu của bảng WECOMMIT_TEST (sử dụng Operation = TABLE ACCESS FULL).
- Sau bước này, hệ thống mới thực hiện hành động DELETE (xóa các bản ghi thỏa mãn điều kiện ORDERQTTY=3000)
- Nếu đánh giá toàn bộ chi phí của câu lệnh DELETE:
- Bước TABLE ACCESS FULL chiếm gần như 100% thời gian của câu lệnh.
- Cụ thể bước này chiếm chi phí COST = 55268 và mất thời gian ước lượng là 11 phút 04 giây.
2.3. Thực hiện tăng tốc câu lệnh DELETE sử dụng Index
Thực hiện tạo Index trên cột ORDERQTTY của bảng WECOMMIT_TEST
Plan hash value: 2515685688
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 785 | 3140 | 4 (0)| 00:00:01 |
| 1 | DELETE | WECOMMIT_TEST | | | | |
|* 2 | INDEX RANGE SCAN| IDX_WECOMMIT_ORDERQTT | 785 | 3140 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDERQTTY"=3000)
- Đầu tiên hệ thống sử dụng giải thuật INDEX RANGE SCAN để tìm kiếm trên Index IDX_WECOMMIT_ORDERQTT
- Sau khi tìm được các bản ghi thỏa mãn điều kiện, hệ thống thực hiện lệnh DELETE
- Toàn bộ câu lệnh DELETE lúc này chỉ mất chi phí COST = 4, và thời gian thực hiện dự kiến là 1s
- Thực hiện tăng tốc câu lệnh DELETE thành công từ 11 phút 04s còn 1s
- Chi phí thực hiện của câu lệnh đã giảm từ 55268 xuống còn 4
3. Trong trường hợp câu lệnh không có mệnh đề WHERE, làm thế nào để tăng tốc câu lệnh DELETE
Nếu chúng ta muốn xóa toàn bộ dữ liệu của bảng (sử dụng DELETE nhưng không có mệnh đề WHERE), lúc này Index không mang lại tác dụng cải thiện hiệu năng.
Tại đây, thay vì sử dụng DELETE, các bạn có thể chuyển sang sử dụng TRUNCATE.
Việc sử dụng TRUNCATE sẽ có thời gian thực thi nhanh hơn rất nhiều lần so với sử dụng DELETE mà không có mệnh đề WHERE
Ví dụ
Thay vì sử dụng câu lệnh delete from wecommit_test
Chúng ta sẽ sử dụng câu lệnh sau
truncate table wecommit_test
Lý do tại sao TRUNCATE lại nhanh hơn hàng nghìn lần so với DELETE vì
- DELETE không có mệnh đề WHERE sẽ cần quét FULL toàn bộ các block dữ liệu của bảng.
- DELETE thực hiện sinh ra UNDO (đây là dữ liệu cần thiết để có thể thực hiện ROLLBACK).
- Cơ chế của TRUNCATE không cần thiết tạo ra UNDO (do đó TRUNCATE không thể ROLLBACK).
4. Nếu bạn muốn biết toàn bộ những kỹ thuật tối ưu SQL, tối ưu Cơ sở dữ liệu mà tôi đúc kết được trong hơn 10 năm làm dự án tại các tập đoàn lớn.
Hãy tìm hiểu chương trình Từ điển tối ưu 100x hiệu năng của tôi tại đây: Click vào đây,
5. Một số nội dung khác có liên quan đến bài viết
6. Thông tin tác giả
Tác giả: Trần Quốc Huy – Founder & CEO Wecommit.
Facebook: https://www.facebook.com/tran.q.huy.71
Youtube: https://www.youtube.com/channel/UCtsYzL7iN7rBCPnkjYp4XYw
Zalo: 0888549190
Email: huy.tranquoc@wecommit.com.vn