Việc thêm Index một cách mù quáng không phải là cách tuning đúng đắn. Chìa khóa thực sự nằm ở việc "đọc vị" suy nghĩ của Bộ tối ưu hóa (Cost-Based Optimizer - CBO) thông qua Kế hoạch thực thi (Execution Plan).
Khi anh bắt đầu phân tích Plan của SQL Server hay Postgres, anh sẽ thấy các thuật toán cốt lõi (như Hash Join, Nested Loop, Index Scan) hoàn toàn giống Oracle, chỉ khác nhau ở cách gọi tên và cách trình bày (đồ họa vs. văn bản).
Chào mừng bạn đến với Module 19: Quản lý hiệu năng CSDL: Tuning SQL (Phân tích Execution Plan).
1. Tổng quan
Execution Plan là bản đồ đường đi mà Database Engine sẽ sử dụng để lấy dữ liệu. Quá trình Tuning SQL bản chất là tìm ra câu trả lời cho 3 câu hỏi khi nhìn vào bản đồ này:
Access Path: Truy vấn đang quét toàn bảng (Full Scan) hay dùng Chỉ mục (Index Seek)?
Join Strategy: Hai bảng đang nối với nhau bằng thuật toán gì (Hash, Merge hay Nested Loop)?
Cardinality (Số dòng ước tính): Số dòng CBO dự đoán (Estimated Rows) có khớp với số dòng thực tế trả về (Actual Rows) không? Nếu chênh lệch quá lớn $\rightarrow$ Thống kê (Statistics) đang bị sai!
2. Chi tiết các nội dung
A. Phương thức trích xuất Kế hoạch thực thi (Execution Plan)
| Hệ thống | Lệnh lấy Kế hoạch Ước tính (Chưa chạy thật) | Lệnh lấy Kế hoạch Thực tế (Chạy thật & đo lường) |
| Oracle | EXPLAIN PLAN FOR ...; | Bật SET AUTOTRACE ON; hoặc dùng hint /*+ GATHER_PLAN_STATISTICS */ |
| SQL Server | Nút Display Estimated Execution Plan trong SSMS. | Nút Include Actual Execution Plan (Bật lên và F5 chạy lệnh). |
| PostgreSQL | EXPLAIN <câu_lệnh>; | EXPLAIN ANALYZE <câu_lệnh>; |
| MySQL | EXPLAIN <câu_lệnh>; | EXPLAIN ANALYZE <câu_lệnh>; (Hỗ trợ từ MySQL 8.0.18) |
| MongoDB | cursor.explain("queryPlanner") | cursor.explain("executionStats") |
B. Từ điển thuật ngữ (Mapping các toán tử giữa các hệ thống)
Đây là kim chỉ nam giúp anh ánh xạ kiến thức từ Oracle sang các hệ thống khác:
1. Quét toàn bảng (Full Table Scan) - Nỗi ám ảnh của I/O:
Oracle:
TABLE ACCESS FULLSQL Server:
Table Scan(đối với Heap Table) hoặcClustered Index Scan(Bản chất vẫn là quét toàn bảng vật lý).PostgreSQL:
Seq Scan(Sequential Scan).MySQL: Trong cột
typehiển thị là ALL.MongoDB:
COLLSCAN(Collection Scan).
2. Quét qua Chỉ mục (Index Access) - Tối ưu cho lượng dữ liệu nhỏ:
Oracle:
INDEX RANGE SCANhoặcINDEX UNIQUE SCAN.SQL Server: Index Seek (Đi thẳng từ Rễ xuống Lá của B-Tree, cực nhanh). Đừng nhầm với
Index Scan(Quét ngang toàn bộ lá của cây Index, chậm hơn).PostgreSQL:
Index Scan(Lấy data trực tiếp) hoặcBitmap Index Scan(Tạo bản đồ nhị phân trong RAM trước khi nhặt dữ liệu, tối ưu khi đọc nhiều dòng).MySQL: Trong cột
typehiển thị làref,eq_refhoặcrange.MongoDB:
IXSCAN.
3. Thuật toán nối bảng (Join Methods - Chỉ có ở RDBMS):
Nested Loop Join: Dùng khi 1 bảng rất nhỏ và 1 bảng có Index. Vòng lặp For lồng nhau. (Cả 4 RDBMS đều có).
Hash Join: Dùng khi nối 2 bảng rất lớn, không có Index. Hệ thống băm (hash) bảng nhỏ lên RAM, sau đó quét bảng lớn để đối chiếu. Rất ngốn CPU và RAM. (MySQL từ bản 8.0.18 mới chính thức hỗ trợ Hash Join).
Merge Join (Sort Merge Join): Dùng khi cả 2 bảng đều đã được sắp xếp sẵn (sorted) theo cột nối.
C. Kỹ năng đọc Plan thực chiến
Quy tắc đọc: SQL Server (SSMS) đọc đồ họa từ Phải sang Trái, Trên xuống Dưới. Nhóm Open Source (Postgres, MySQL) đọc dạng văn bản (Text Tree) từ Trong ra Ngoài (nút thụt lề sâu nhất sẽ chạy trước).
Mũi tên dầy (Thắt cổ chai): Trong SSMS của SQL Server, độ dày của các mũi tên nối các toán tử thể hiện dung lượng dữ liệu truyền qua. Mũi tên càng béo $\rightarrow$ dữ liệu càng lớn. Điểm nào mũi tên đang béo đột ngột xẹp lại (hoặc ngược lại) chính là điểm cần Tuning.
3. Tóm tắt lại nội dung của bài học
Nguyên tắc tối thượng khi Tuning: Đừng bao giờ tin vào Estimated Plan (Kế hoạch ước tính). Hãy luôn tìm cách lấy Actual Plan (Kế hoạch thực tế). Một kế hoạch ước tính có thể báo Cost rất thấp, nhưng khi chạy thực tế lại mất hàng giờ do tràn RAM (Spill to TempDB/Disk).
Ở PostgreSQL, lệnh
EXPLAIN ANALYZEthực sự THỰC THI câu lệnh. Nếu anh gõEXPLAIN ANALYZE DELETE FROM orders;, toàn bộ dữ liệu bảng orders sẽ bị xóa thật! Do đó, với DML (Insert/Update/Delete), phải nhốt nó vào trong một blockBEGIN; ... ROLLBACK;.Chuyển sang SQL Server, giao diện đồ họa SSMS biến việc đọc Plan thành một nghệ thuật trực quan. Anh chỉ cần di chuột vào toán tử cảnh báo (có dấu chấm than màu vàng), nó sẽ báo ngay là câu lệnh đang thiếu Index gì và gạch sẵn code tạo Index cho anh (Missing Index Recommendation).
4. Câu hỏi ôn tập
Hỏi (Oracle): Thuật toán nối (Join) nào thường được CBO lựa chọn khi anh cần nối hai bảng dữ liệu khổng lồ (hàng chục triệu dòng) mà không có bất kỳ Index nào hỗ trợ ở cột khóa ngoại?
Đáp: Hash Join.
Hỏi (SQL Server): Sự khác biệt cực kỳ lớn về hiệu năng giữa toán tử
Index SeekvàIndex Scanlà gì?Đáp:
Index Seeksử dụng cấu trúc B-Tree để tìm chính xác và nhảy thẳng đến các dòng dữ liệu cần thiết (rất nhanh).Index Scanphải quét ngang toàn bộ các trang lá (Leaf Pages) của cây chỉ mục từ đầu đến cuối (bản chất nó chậm gần bằng Table Scan).
Hỏi (PostgreSQL): Tại sao anh phải cực kỳ cẩn thận khi gắn thêm từ khóa
ANALYZEvào sau lệnhEXPLAINđối với các câu lệnhUPDATEhoặcDELETE?Đáp: Vì
EXPLAIN ANALYZEkhông chỉ hiển thị kế hoạch mà còn thực thi thực sự câu lệnh đó, dẫn đến việc dữ liệu bị cập nhật/xóa thật trên Database.
Hỏi (MySQL): Khi anh gõ lệnh
EXPLAIN SELECT ..., nếu cộttypetrả về giá trị là ALL, điều đó ám chỉ Database Engine đang làm gì?Đáp: Ám chỉ một "Full Table Scan" (Quét toàn bảng), nghĩa là CBO không dùng được Index nào cả và phải đọc toàn bộ dữ liệu vật lý từ ổ cứng lên.
Hỏi (MongoDB): Giá trị nào trong đầu ra của
explain("executionStats")chỉ ra rằng câu lệnh đã không dùng Index và phải duyệt toàn bộ collection?Đáp: Giai đoạn (Stage) có tên là
COLLSCAN.
5. Bài tập thực hành (Lấy Kế hoạch thực tế)
Đề bài tình huống: Hệ thống đang bị chậm bởi một câu lệnh truy vấn báo cáo cuối ngày: SELECT * FROM orders WHERE status = 'PENDING';.
Yêu cầu anh viết lệnh (hoặc thao tác) để trích xuất Kế hoạch Thực Tế (Actual Execution Plan) kèm theo thời gian chạy thật và số lượng dòng thật cho 5 hệ thống.
Đáp án:
1. Oracle: (Lấy từ bộ nhớ cache ngay sau khi chạy lệnh)
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM orders WHERE status = 'PENDING';
-- Sau đó lấy plan thực tế từ RAM:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
2. SQL Server: (Bật thống kê IO và Time trước khi chạy)
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS PROFILE ON; --(Hoặc bấm nút "Include Actual Execution Plan" trên thanh công cụ)
SELECT * FROM orders WHERE status = 'PENDING';
3. PostgreSQL: (Bắt buộc phải dùng ANALYZE)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PENDING';
4. MySQL / MariaDB: (Dành cho bản 8.0.18 trở lên)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PENDING';
5. MongoDB: (Thêm tham số executionStats)
use app_db;
db.orders.find({ status: "PENDING" }).explain("executionStats");
Hiểu được ngôn ngữ của Optimizer, hoàn toàn có thể chinh phục bất kỳ hệ quản trị nào mà không sợ "tối cổ" khi xử lý sự cố.
Chúng ta chỉ còn một học phần cuối cùng để khép lại khóa học đồ sộ này: Module 20: Tự động hóa tác vụ quản trị (Job, Alert & Scripting). Chúng ta sẽ "khoán" các công việc lặp đi lặp lại cho hệ thống tự lo.
TƯ VẤN: Click Here hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c/21c/23c/23ai, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
