ĐĂNG KÝ ĐỂ NHẬN THÔNG TIN MỚI NHẤT

Tất cả mọi người

Sql Execution Plan trong việc tối ưu SQL

Tháng Mười Hai 8, 2022Tháng Mười Hai 13, 2022 Trần Quốc Huy

Sql Execution Plan hay còn gọi là chiến lược thực thi của câu lệnh SQL – đây là một vấn đề buộc phải biết nếu như bạn cần tối ưu SQL.

1. Sql Execution Plan là gì?

Để bất kỳ một hệ quản trị cơ sở dữ liệu nào thực thi được câu lệnh SQL của bạn, nó đều cần biết “cụ thể sẽ cần làm các bước gì, sẽ phải lấy dữ liệu theo cách thức nào, join nhứ thế nào, làm bước nào trước, làm bước nào sau”. Tất cả bước ấy gom lại thành một thứ: chiến lược thực thi của câu lệnh SQL, hoặc thuật ngữ chuyên ngành gọi là Sql Exeuction Plan.

Dưới đây là hình ảnh Sql Execution Plan trong một số loại cơ sở dữ liệu phổ biến.

1.1. Sql Execution Plan trong cơ sở dữ liệu Oracle

select * from huytq.wecommit_emp_normal where salary > 5000;

1.2. Sql Execution Plan trong cơ sở dữ liệu MySQL

mysql> explain select * from customers where customerNumber=103;

1.3. Sql Execution Plan trong cơ sở dữ liệu SQL Server

select * from HumanResources.Department where name like ‘H%’

Sql Execution Plan trong tối ưu câu lệnh tại SQLServer

1.4. Sql Execution Plan trong cơ sở dữ liệu PostgreSQL

postgres=# explain select * from customer where customer_id=12;

Sql Execution Plan trong tối ưu PostgreSQL

2. Làm thế nào để lấy được thông tin Sql Execution Plan?

Anh em có một số cách thức “kinh điển” để lấy SQL Execution Plan:

Cách 1: Thực hiện lấy thông tin Sql Execution Plan thông qua công cụ. Lúc này chiến lược thực thi sẽ được hiển thị dạng đồ họa, có màu sắc, anh em mới tập tối ưu SQL có thể dùng cách này.
Cách 2: Lấy thông tin này khi đăng nhập trực tiếp trên Server cài đặt cơ sở dữ liệu: Hệ thống sẽ hiển thị SQL Execution Plan dạng text (dạng này lúc đầu anh em mới nhìn có thể thấy hơi xấu, chưa quen mắt). Bản thân tôi khá yêu thích cách làm này và thường xuyên sử dụng nó trong các dự án tối ưu mà tôi thực hiện.

2.1. Sử dụng TOOL để lấy Sql Execution Plan

Anh em chỉ cần biết rằng: gần như toàn bộ các công cụ hỗ trợ quản trị, lập trình khi kết nối vào cơ sở dữ liệu đều có thể cho anh em biết được chiến lược thực thi của câu lệnh.

Nếu anh em đang sử dụng công cụ nào thì có thể search cụm từ khóa “Show Sql Execution Plan + “tên Tool” anh em đang sử dụng là sẽ ra cách làm sau 1, 2 phút tìm kiếm.

2.1.1. Cách sử dụng Toad để lấy chiến lược thực thi Cơ sở dữ liệu Oracle

Cách lấy sql execution plan

2.1.2.Cách sử dụng Microsoft SQL Server Management Studio để lấy Sql Execution Plan

2.2. Lấy trực tiếp Sql Execution Plan trên database server

Có rất nhiều option để thực hiện việc này, cách thức đơn giản nhất mà bạn có thể áp dụng như sau

Đối với Cơ sở dữ liệu Oracle: Bật autotrace khi đăng nhập vào sqlplus
- Ví dụ: SQL> set autotrace traceonly
Đối với Cơ sở dữ liệu PostgreSQL: Bạn thêm từ khóa Explain ở trước câu lệnh SQL
- Ví dụ : postgres=# explain select * from customer where id=12;
Đối với Cơ sở dữ liệu MySQL: Bạn thêm từ khóa Explain ở trước câu lệnh SQL
- Ví dụ: mysql> explain select * from customers where customerNumber=103;

3. Tại sao Sql Execution Plan lại đặc biệt quan trọng khi chúng ta thực hiện tối ưu SQL?

Tại phần 1 tôi đã dẫn chứng cho các anh em thấy một điều:

Tất cả các cơ sở dữ liệu đều phải phân tích Sql Execution Plan

Bây giờ tôi sẽ giúp mọi người hiểu sâu hơn: Lý do gì chúng ta phải biết về SQL Execution Plan thì mới thực hiện tối ưu được.

Bây giờ tôi sẽ xét một ví dụ như sau:

Chúng ta có 03 câu lệnh:

Câu lệnh thứ nhất: select * from emp where salary < 500
Câu lệnh thứ hai: select * from emp where salary < 5000
Câu lệnh thứ ba: select salary from emp where salary < 500

Ba câu lệnh bên trên về mặt cách thức thì không có gì khác nhau cả:

Đều làm việc với bảng EMP
Lọc dữ liệu đều lọc qua cột SALARY

Tuy nhiên, ba câu lệnh này có hiệu năng KHÁC NHAU rất nhiều.

Để có thể trả lời được các câu hỏi như:

Mỗi câu lệnh khi thực hiện sẽ tiêu tốn tài nguyên ra sao?
Câu lệnh đang bị chậm vì NGUYÊN NHÂN GỐC nào?
Tôi nên tập trung vào tối ưu objects nào trong câu lệnh SQL?

Lúc này nếu chỉ nhìn vào thuần túy (dạng TEXT) của câu lệnh, chúng ta rất khó để trả lời các câu hỏi bên trên.

Tuy nhiên khi sử dụng SQL EXECUTION PLAN thì mọi việc lại rất đơn giản và rõ ràng.

Chúng ta cùng xem chiến lược thực thi của 3 câu lệnh bên trên nhé

3.1. Chiến lược thực thi – Sql Exectuion Plan của câu lệnh thứ nhất

SQL> select * from emp where salary < 500;

SQL EXECUTION PLAN

3.2. Chiến lược thực thi – Sql Exeuction Plan của câu lệnh thứ hai

SQL> select * from emp where salary < 5000;

SQL EXECUTION PLAN

3.3. Chiến lược thực thi – Sql Exeuction Plan của câu lệnh thứ ba

select salary from emp where salary < 500;

Sql Execution Plan

3.4. Nhận xét và phân tích chiến lược thực thi của 3 câu lệnh bên trên

Về chi phí thực hiện của câu lệnh (anh em xem ở cột COST):

Câu lệnh thứ 3 chiếm ít tài nguyên nhất: Cost =3
Chi phí của câu lệnh thứ 2 là nhiều nhất: Cost = 39883 (gấp hơn 13.000 lần so với câu số 3!!!0
Chi phí của câu lênh thứ nhất cũng nhỏ, nhưng vẫn gáp 3 lần so với câu lệnh thứ 3.

Về thời gian thực hiện ƯỚC TÍNH: đúng là một trời một vực giữa các câu lệnh

Câu lệnh thứ nhất và thứ 3 ước tính thời gian thực hiện rất nhanh ~ 01s
Câu lệnh thứ hai ước tính thời gian thực hiện gần 8 phút !!!!
Việc này cũng rất logic với số lượng tài nguyên (COST) của câu lệnh đã thống kê bên trên.

Một số điều thú vị mà Sql Execution Plan cho thấy:

Câu lệnh thứ nhất và thứ ba: cùng sử dụng Index (IDX_WECOMMIT_SALARY), tuy nhiên chi phí hai câu lệnh này lệch nhau 3 lần. Việc này chứng to khi phân tích câu lệnh, không phải cứ CHĂM CHĂM nhìn vào cái INDEX là xong việc.
Câu lệnh thứ hai cho ta thấy: rõ ràng bảng có INDEX đấy, nhưng câu lệnh vẫn có thể không sử dụng INDEX, mà chuyển sang TABLE ACCESS FULL (quét toàn bộ block dữ liệu của bảng).

Sẽ có rất nhiều câu hỏi nếu bạn đi sâu vào Sql Execution Plan và bạn sẽ đi đến tận cùng, hiểu được chính xác bản chất tối ưu SQL

Bạn sẽ hiểu được trường hợp nào thì Cơ sở dữ liệu sẽ bỏ qua Index
Tại sao cùng 1 Index, nhưng hiệu năng mang lại có thể khác xa nhau (nhiều trường hợp lệch nhau hàng trăm lần)
Tại sao cùng 1 câu lệnh, nhưng có thể có rất nhiều SQL Execution Plan khác nhau
Rất nhiều câu hỏi thú vị “khai phá” khác nữa

Nếu bạn muốn làm chủ hoàn toàn TƯ DUY và KỸ NĂNG tối ưu SQL, tối ưu cơ sở dữ liệu, thì chương trình này sẽ phù hợp với bạn: https://wecommit.com.vn/chuong-trinh-dao-tao-toi-uu-co-so-du-lieu-wecommit/

4. Tham gia nhóm Zalo Tư Duy – Tối ưu – Khác biệt của tôi (Miễn phí) để cập nhật các tài nguyên, bài viết về tối ưu (có mới hàng tuần):

Link tham gia nhóm: https://wecommit.com.vn/zalo

Mã QR Code tham gia nhóm:

5. Nếu bạn muốn có toàn bộ các kinh nghiêm, kiến thức về tối ưu mà tôi đã tích lũy được qua rất nhiều dự án trọng yếu (tối ưu Core ngân hàng, Core chứng khoán, các hệ thống ERP, hệ thống Billing viễn thông…)

Tìm hiểu chương trình tại đây: https://wecommit.com.vn/chuong-trinh-dao-tao-toi-uu-co-so-du-lieu-wecommit/

6. Nếu bạn muốn xem các Video tôi Demo và giải thích về tối ưu

Bạn có thể xem các video tôi phân tích về tối ưu trên kênh Youtube của tôi.

Ví dụ: Một bài phân tích về sai lầm và sự quan trọng của Index trong Foreign Key column

7. Nếu bạn muốn liên hệ với tôi

Tác giả: Trần Quốc Huy – CEO Wecommit

Zalo: 0888549190

Email: huy.tranquoc@wecommit.com.vn

Views: 5865

Trả lời Hủy