Muốn tối ưu được SQL thì bạn cần hiểu bản chất hoạt động của câu lệnh SQL là gì.

Tôi đã áp dụng kiến thức này và thực hiện tối ưu rất nhiều các cơ sở dữ liệu

• Cơ sở dữ liệu Core ngân hàng, DWH tại ngân hàng: MSB, VBSP,…
• Cơ sở dữ liệu Core chứng khoán: KB, HNX, chứng khoán Bảo Việt. chứng khoán VCBS, TCBS…
• Các hệ thống của tập đoàn viễn thông
• Nhiều hệ thống khác nữa

Những gì tôi chia sẻ trong bài viết này đều có thể áp dụng thực tế.

1. SQL là gì và tại sao lại phải cần SQL

Hiểu một cách đơn giản, SQL là ngôn ngữ để chúng ta “nói chuyện” với Cơ sở dữ liệu.

Và đây là ngôn ngữ mà bạn có thể sử dụng chung với rất nhiều loại Cơ sở dữ liệu khác nhau:

• Sử dụng SQL với Oracle
• Sử dụng SQL với SQL Server
• Sử dụng SQL với PostgreSQL

• Sử dụng SQL với MySQL

SQL cũng phổ biến giống như Tiếng Anh vây,

Nếu bạn chưa từng biết về SQL thì có thể xem hướng dẫn cách học SQL tại đây: https://wecommit.com.vn/30s-to-learn-sql-basic/

2. Sáu bước thực thi nội bộ của một câu lệnh SQL là gì?

Về mặt nguyên lý, khi người dùng gửi một câu lệnh SQL tới, hệ quản trị cơ sở dữ liệu sẽ cần phải làm các bước sau.

 

Bước 1: Thực hiện kiểm tra cú pháp của câu lệnh. Ví dụ khi chúng ta gõ sai cú pháp

select * employees where emp_id=100

Hệ thống kiểm tra và biết được câu lệnh trên bị lỗi cú pháp, thông báo lỗi như sau

ERROR at line 1:
ORA-00923: FROM keyword not found where expected.

Bước 2: Kiểm tra ngữ nghĩa của câu lệnh.

• Nếu như câu lệnh đã hoàn toàn đúng cú pháp, hệ thống sẽ kiểm tra xem về mặt “ý nghĩa” thì người dùng đang muốn làm gì.
• Hệ thống kiểm tra xem table mà người dùng định tương tác có tồn tại không, nếu tồn tại thì người dùng có quyền để làm việc trên đó không, các cột mà người dùng định lấy dữ liệu có trong bảng hay không …
• Ví dụ: Khi chúng ta thực hiện câu lệnh

select * from employee where emp_id=100

• Về mặt ngữ pháp, câu lệnh này không có lỗi gì, nên hệ thống sẽ thực hện bước kiểm tra “ngữ nghĩa”. Sau khi kiểm tra thì thấy người dùng muốn làm việc với một OBJECT tên là EMPLOYEE (có thể là TABLE hoặc VIEW vì object này đứng đằng sau FROM). Hệ thống kiểm tra thông tin các objects mà mình có thì thấy rằng không tồn tại object nào tên là EMPLOYEE cả. Cảnh báo lỗi người dùng nhận được như sau:

ERROR at line 1:
ORA-00942: table or view does not exist.

Bước 3: Kiểm tra thông tin trong Shared Pool (bộ nhớ lưu giữ các chiến lược thực thi của những câu lệnh đã từng thực hiện trong hệ thống.Nếu bạn muốn tìm hiểu thêm về kiến trúc tổng quan của Oracle, bao gồm những vùng bộ nhớ quan trọng khác thì bạn có thể xem tại video mô tả bên dưới). Nếu như đã tìm thấy thông tin về chiến lược thực thi của câu lệnh trong Shared Pool thì thực hiện bước 6 (bỏ qua bước 4 và bước 5). Nếu chưa tìm thấy thông tin thì lần lượt thực hiện 3 bước còn lại (bước 4,5 và 6).

• Trường hợp nếu Oracle không tìm thấy thông tin trong Shared Pool và buộc phải làm đầy đủ cả 6 bước, chúng ta gọi là HARD PARSE.
• Trường hợp nếu Oracle tìm thấy thông tin và bỏ qua bước 4,5, chúng ta gọi là SOFT PARSE
• Trước khi hoàn thành bước số 3 này, Oracle sử dụng một giải thuật HASH để chuyển câu lênh SQL FULL TEXT ban đầu thành một mã SQL_ID. Hàm HASH này có thể thay đổi tùy vào phiên bản của Oracle database mà bạn sử dụng.
• Ở đây bạn có thể thấy một điều thú vị là:
  • Các câu lệnh SQL có giá trị FULL TEXT giống nhau 100% thì sẽ cùng có SQL_ID, mặc dù các câu lệnh này có thể chạy trên các hệ thống khác nhau (miễn là cùng phiên bản Oracle database).

 

Bước 4: Thực hiện tính toán và phân tích tất cả những chiến lược thực thi có thể sử dụng để thực hiện câu lệnh mà người dùng yêu cầu. và lựa chọn ra chiến lược thực thi có chi phí tối ưu.

Bước 5: Nhận chiến lược thực thi tối ưu và sinh ra 1 kế hoạch thực thi cụ thể, chi tiết cho câu lệnh. Kế hoạch thực thi của một câu lệnh có dạng như dưới đây:

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)

 

Bước 6: Câu lệnh thực thi theo kế hoạch đã được lựa chọn

 

3. Hiểu cách thức hoạt dộng của câu lệnh SQL sẽ giúp chúng ta tối ưu được chúng như thế nào?

Dưới đây là một số kinh nghiệm từ dự án mà tôi đã triển khai

• Thứ nhất: Hãy tránh tối đa việc hệ thống liên tục phải thực hiện PHÂN TÍCH LẠI (HARD PARSE) câu lệnh.
• Thứ hai: Tập trung vào tối ưu chiến lược thực thi.

Tôi đã có một bài viết phân tích và demo về kỹ thuật để tránh hệ thống gặp hiện tượng HARD PARSE, các bạn có thể đọc nội dung này tại đây: Click vào đây.

Bài viết này tôi sẽ giải thích rõ sâu hơn về chiến lược thực thi.

3.1. Cơ chế để tạo ra chiến lược thực thi của câu lệnh SQL là gì?

Một câu lệnh SQL có thể có nhiều chiến lược thực thi (cũng giống như chúng ta có nhiều con đường để đi từ nhà tới trường).

Làm cách nào để một hệ quản trị cơ sở dữ liệu có thể lựa chọn được chiến lược thực thi tốt nhất cho câu lệnh SQL?

Trên thực tế, có 2 cách để cơ sở dữ liệu lựa chọn

• Sử dụng theo giải thuật RBO (Rule-Based Optimizer): Hiểu nôm na là sẽ có 1 số nguyên tắc cứng để lựa chọn (Ví dụ: Cứ sử dụng Index thì tốt hơn là không dùng Index)
• Sử dụng theo giải thuật CBO (Cost-Based Optimizer): Cân nhắc tất cả các phương án và quy đổi ra chi phí để thực hiện chiến lược thực thi đó. Chung cuộc lại, hệ thống sẽ chọn chiến lược thực thi nào có chi phí thấp nhất.

Hiện tại RBO là phương án đã cũ, những hệ quản trị cơ sở dữ liệu hiện nay đều sử dụng CBO, do đó tôi không đi vào nội dung này, tránh làm mất thời gian của bạn đọc.

3.2. Ví dụ về CBO – (Cost-Based Optimizer)

Chúng ta hãy cùng đánh giá câu lệnh sau

SELECT 
e.LAST_NAME, d.LOC
FROM huytq.wecommit_emp e, huytq.wecommit_dept d
WHERE e.deptno = d.deptno AND e.salary < 500

Hệ thống sẽ tự đánh giá các phương án có thể thực hiện câu lệnh này và lựa chọn một phương án mà nó cho rằng có chi phí (COST) thấp nhất.

Đưới dây là chiến lược thực thi của câu lệnh và chi tiết của COST mà hệ thống ước tính

Trong ví dụ trên;

• Chi phí của toàn bộ câu lệnh ước tính là 33k (Cost = 33K)
• Thức tự thực hiện được mô tả trong hình. Tôi có một bài viết chia sẻ cách thức đọc chiến lược thực thi, để bạn hiểu rõ trình tự thực hiện của các bước.

Bạn có thể đọc thêm những bài viết khác về chiến lược thực thi của câu lệnh tại đây:

• Hướng dẫn đọc SQL Execution Plans trong SQL Server: Click vào đây
• Hướng dẫn đọc SQL Exeuction Plans trong Oracle: Click vào đây

3.3. Làm thế nào đẻ chúng ta “BẮT HỆ THỐNG” phải lựa chọn chiến lược thực thi theo ý mình?

Trong quá trình tối ưu câu lệnh SQL, bạn cũng có thể bắt hệ thống phải thực hiện chiến lược thực thi theo ý chí của người lập trình, thay vì để hệ thống tự lựa chọn.

Kỹ thuật này gọi là HINT SQL.

Ví dụ xét câu lệnh tìm kiếm trên bảng EMP với điều kiện SALARY = 5000.

select  * from emp where salary=5000;

Nếu viết câu lệnh như trên, hệ thống sẽ mặc định lựa chọn chiến lược thực thi theo đúng giải thuật CBO mà tôi đã nói bên trên.

Trong trường hợp bạn chủ định muốn hệ thống phải sử dụng Index theo ý của bạn, thì câu lệnh sẽ thêm HINT INDEX như sau

Sử dụng HINT chỉ định hệ thống sử dụng INDEX IDX_ID_WECOMMIT (Index đánh trên cột ID)

select /*+ INDEX(emp IDX_ID_WECOMMIT) */* from emp where salary=5000

Chi tiết hơn của kỹ thuật HINT này tôi có chia sẻ tại một bài viết khác, bạn có thể đọc tại đây: Click vào đây‘

4. Nếu bạn muốn biết chi tiết tất cả các kỹ thuật về tối ưu SQL, những kỹ thuật đang được sử dụng hiệu quả trong các dự án tại ngân hàng, chứng khoán.

Những kỹ thuật trên chỉ là một phần nhỏ trong chương trình đào tạo tối ưu cơ sở dữ liệu cao cấp của chúng tôi.

Xem thông tin chi tiết chương trình tại đây: https://wecommit.com.vn/chuong-trinh-dao-tao-toi-uu-co-so-du-lieu-wecommit/

5. Tôi có một kho tài liệu được thiết kế đầy đủ, cập nhật kiến thức hàng tuần. Nếu bạn muốn xem các nội dung của kho tài liệu này (MIỄN PHÍ), bạn có thể làm theo cách sau

• Tham gia nhóm Zalo  (MIỄN PHÍ): https://wecommit.com.vn/zalo

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

Email: huy.tranquoc@wecommit.com.vn

Youtube: Trần Quốc Huy

Số điện thoại: 0888549190