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

Sử dụng HINT trong tối ưu SQL

Bài viết này với mục tiêu giới thiệu và hướng dẫn cách thức sử dụng HINT để thay đổi chiến lược thực thi của câu lệnh. Đây là một kỹ thuật cần biết khi tham gia các công việc tối ưu SQL

Ghi chú: 

Tác giả của nội dung này: Trần Quốc Huy – CEO Wecommit.

Ghi chú: Nếu bạn muốn nhận thông báo về các bài viết tối ưu SQL, tối ưu Database mà tôi chia sẻ cho cộng đồng, hãy tham gia nhom sau (Miễn phí): Từ điển tối ưu 100x hiệu năng – Group cộng đồng (Miễn phí)

1. Sử dụng HINT cho mục đích gì?

Chúng ta cần nhớ rằng: một trong các yếu tố quyết định lớn nhất hiệu năng của một câu lệnh đó là SQL Execution Plans, hay còn gọi là chiến lược thực thi của câu lệnh.

Chiến lược thực thi này chính là các chỉ dẫn chi tiết hệ thống cần làm những công việc gì, theo thứ tự nào để thực hiện một câu lệnh SQL.(giống như tấm bản đồ Google Maps, hướng dẫn chúng ta đi từ điểm A đến điểm B ở ngoài đời).

Thông thường thì hệ thống sẽ tự động tính toán và quyết định nên chọn chiến lược thực thi nào để thực hiện một câu lệnh SQL từ người dùng.

Chúng ta sử dụng HINT với mục đích “Gợi ý chiến lược thực thi mà mình mong muốn” cho hệ thống, như vậy hệ thống sẽ ưu tiên lựa chọn theo chiến lược mà chúng ta chỉ định trong HINT.

Ví dụ như sau:

SELECT 
e.LAST_NAME, d.LOC
FROM huytq.emp e, huytq.dept d
WHERE e.deptno = d.deptno and e.salary < 500

Chiến lược thực thi của câu lệnh như sau:

Câu bên dưới là tôi sử dụng HINT

SELECT /*+ use_nl(e, d) leading(d) */
e.LAST_NAME, d.LOC
FROM huytq.emp e, huytq.dept d
WHERE e.deptno = d.deptno and e.salary < 500

Chiến lược thực thi mới

Sử dụng HINT trong tối ưu SQL

Tại đây có một số chú ý:

  • Đoạn lệnh bạn thấy trong phần SELECT : /*+ use_nl(e, d) leading(d) */ chính là HINT. Đây không phải là phần comment của câu lệnh nhé.
  • Đoạn HINT này có nghĩa là:
    • Sử dụng giải thuật Join giữa 2 bảng là Nested Loop Join (use_nl (e,d))
    • Khi sử dụng giải thuật Nested Loop Join, hệ thống sẽ sử dụng bảng Dept (bảng có alias là d để làm Driving Table).
  • Sau khi sử dụng HINT thì câu lệnh đã có một chiến lược thực thi HOÀN TOÀN MỚI, số bước cần thực hiện là 4 (từ số ID =0 đến ID =3), giảm 2 bước so với chiến lược thực thi cũ (ID =0 đến ID=5), tuy nhiên thời gian thực hiện đã tăng lên một cách khủng khiếp,
  • Thời gian thực hiện theo chiến lược mới là 135 giờ 11 phút 15s !!!!!

Kết lại:

  • Sử dụng HINT để thay đổi chiến lược thực thi của câu lệnh SQL
  • Sử dụng HINT có thể mang lại kết quả xấu hoặc tốt (tùy vào năng lực của người sử dụng HINT).
  • Không phải cứ số bước thực hiện chiến lược thực thi ngắn là câu lệnh SQL có thời gian thực thi nhanh hơn chiến lược thực thi có số bước thực hiện dài

2. HINT có thể sử dụng được với các loại cơ sở dữ liệu khác nhau (Oracle, SQL Server, MySQL…) không?

Các loại cơ sở dữ liệu phổ biến hiện nay như Oracle, SQL Server, MySQL đều hỗ trợ sử dụng HINT.

2.1. Ví dụ sử dụng HINT với cơ sở dữ liệu MySQL

Bạn có thể đọc chi tiết hơn tài liệu với thuật ngữ Optimizer Hints MySQL.

Sử dụng HINT trong MySQL

Link chi tiết: https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

2.2. Ví dụ sử dụng HINT trong cơ sở dữ liệu SQL Server

Đây là ảnh dẫn chứng từ tài liệu của Micorsoft liên quan đến HINTS

Sử dụng HINT Sql server

Link chi tiết: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql?view=sql-server-ver16

2.3. Ví dụ sử dụng HINT trong cơ sở dữ liệu Oracle

Đây là ảnh dẫn chứng tài liệu HINT trong Oracle

Sử dụng HINT trong Oracle

Link chi tiết: https://docs.oracle.com/cd/E18283_01/server.112/e17118/sql_elements006.htm#SQLRF00219

3. Ví dụ về cách sử dụng HINT trong cơ sở dữ liệu Oracle hay dùng trong thực tế

3.1. Sử dụng HINT quét FULL TABLE SCAN

Sử dụng HINT yêu cầu hệ thống quét FULL bảng emp

select /*+FULL(emp) */ * from emp where id=100

Sử dung HINT Full Table Scan

Bảng EMP hiện tại đã có Index trên cột ID, tuy nhiên khi sử dụng /*+FULL(emp) */, hệ thống sẽ thực hiện TABLE ACCESS FULL đối với bảng EMP.

3.2. Sử dụng HINT bắt buộc câu lệnh phải sử dụng INDEX

select /*+INDEX(emp) */ * from emp where last_name='HUYTQ'

Sử dụng HINT Index

HINT /*+INDEX(emp) */ sẽ khiến câu lệnh ưu tiên chọn 1 Index của bảng EMP để thực hiện câu lệnh.

Hiện tại bảng EMP đang chỉ có INDEX trên cột ID, không liên quan gì đến cột đang tìm kiếm LAST_NAME cả.

Sử dụng HINT Index

Do Index không chứa thông tin của cột cần tìm kiếm, nên việc bắt hệ thống quét INDEX trong trường hợp này là một phương án tồi. Thời gian thực hiện của câu lệnh ước tính hơn 24 phút.

Với trường hợp này, nếu chúng ta bỏ HINT đi, hệ thống sẽ lựa chọn chiến lược thực thi như sau:

select * from emp where last_name=’HUYTQ’

Bạn có thể thấy rằng:
– Nếu thực hiện TABLE ACCESS FULL thì thời gian thực hiện chỉ cần 7 phút 58s là hoàn thành (giảm hơn 3 lần so với việc dùng INDEX).

3.3. Sử dụng HINT yêu cầu câu lệnh sử dụng đúng một INDEX nào đó.

Thực hiện tạo thêm Index trên cột Salary của bảng EMP

create index idx_salary_wecommit on emp(salary);

Sau khi tạo Index này, bảng EMP chúng ta có 2 Index như sau:

Chúng ta cùng xem xét chiến lược thực thi:

Khi không sử dụng HINT, hệ thống tự nhận chiến lược thực thi như sau:

select * from emp where salary=5000

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

Sử dụng HINT chỉ định rõ INDEX

Khi sử dụng HINT mà không chỉ rõ tên INDEX nào, thì hệ thống sẽ cho kết quả như sau:

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

Sử dụng HINT trong SQL

3.4. Sử dụng HINT để thay đổi thứ tự Join

HINT ORDERED chỉ định thứ tự Join Table sẽ thực hiện đúng theo thứ tự ĐƯỢC VIẾT trong câu lệnh SQL. Ví dụ như sau

SELECT /*+ ORDERED */
e.LAST_NAME, d.LOC
FROM huytq.emp e, huytq.dept d
WHERE e.deptno = d.deptno AND e.salary < 500

Trong câu lệnh trên, trong mệnh đề FROM, hai bảng của phép JOIN đang viết theo thứ tự:

  • Bảng EMP đứng trước, sau đó là bảng DEPT
  • Khi sử dụng HINT ORDERED, hệ thống sẽ thực hiện JOIN với bảng EMP được thực hiện trước.

Bây giờ chúng ta thực hiện viết lại câu lệnh SQL, trong mệnh đề FROM sẽ đưa bảng DEPT lên trước. Câu lệnh như sau

SELECT /*+ ORDERED */
e.LAST_NAME, d.LOC
FROM huytq.dept d, huytq.emp e
WHERE e.deptno = d.deptno AND e.salary < 500

Trong chiến lược thực thi, hệ thống lúc này đã lựa chọn thực hiện truy cập vào bảng DEPT trước.
Một điều đáng chú ý ở đây là:
– Nếu để ý kỹ, bạn sẽ thấy giải thuật được lựa chọn ở 2 tình huống là khác nhau hoàn toàn:

  • Tại trường hợp đầu tiên, hệ thống sử dụng giải thuật NESTED LOOP JOIN
  • Tại trường hợp thứ hai, hệ thống sử dụng giải thuật HASH JOIN

3.5. Sử dụng HINT để lựa chọn giải thuật JOIN

Các giải thuật JOIN được mô tả rất chi tiết trong chương trình https://wecommit.com.vn/chuong-trinh-dao-tao-toi-uu-co-so-du-lieu-wecommit/.  Tại bài viết này tôi không đi vào mô tả lại từng giải thuật mà chỉ tập trung Demo sử dụng HINT liên quan đến các giải thuật Join mà thôi.

Sử dụng HINT USE_NL để hệ thống sử dụng giải thuật Nested Loop Join giữa các bảng

SELECT /*+ USE_NL(e,d)*/
e.LAST_NAME, d.LOC
FROM huytq.dept d, huytq.emp e
WHERE e.deptno = d.deptno AND e.salary < 500

Sử dụng HINT USE_HASH để hệ thống sử dụng giải thuật HASH JOIN

SELECT /*+ USE_HASH(e,d)*/
e.LAST_NAME, d.LOC
FROM huytq.dept d, huytq.emp e
WHERE e.deptno = d.deptno AND e.salary < 500

Sử dụng HINT USE_MERGE để hệ thống sử dụng giải thuật SORT MERGE JOIN

SELECT /*+ USE_MERGE(e,d)*/
e.LAST_NAME, d.LOC
FROM huytq.dept d, huytq.emp e
WHERE e.deptno = d.deptno AND e.salary < 500

 

4. Một số bài viết khác bạn có thể quan tâm

4.1. Sử dụng HINT RESULT_CACHE  để tăng tốc câu lệnh về ~0s

Đây là một HINT vô cùng hiệu quả trong việc tối ưu SQL, với HINT này, câu lệnh của bạn có thể tăng tốc gần như ngay lập tức về thời gian ~0s.
Ví dụ cách thức sử dụng HINT:
select /*+ RESULT_CACHE */ * from emp e, dept d where e.deptno=d.deptno and e.salary=400
Chi tiết của nội dung này, bạn có thể xem ở một bài viết khác của tôi: https://wecommit.com.vn/su-dung-result-cache-hint-trong-toi-uu-sql/

4.2. Hướng dẫn đọc chiến lược thực thi của câu lệnh để hiểu mọi thứ thay đổi thế nào khi sử dụng HINT

4.3. Một số kỹ thuật tối ưu khác, không cần sử dụng HINT

5. 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/

6. 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

7. 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

Views: 2978





    Câu hỏi bảo mật

    Trả lời

    Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *