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
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.
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
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
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
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'
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ả.
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
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
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
select /*+ RESULT_CACHE */ * from emp e, dept d where e.deptno=d.deptno and e.salary=400
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
- Hướng dẫn cách đọc Execution Plans trong SQL Server: Click vào đây
- Hướng dẫn cách đọc Execution Plans trong Oracle: Click vào đây
4.3. Một số kỹ thuật tối ưu khác, không cần sử dụng HINT
- Tối ưu cơ sở dữ liệu dựa vào bản chất 6 bước thực thi của câu lệnh: Click vào đây
- Tối ưu câu lệnh Insert: Click vào đây.
- Tối ưu câu lệnh Update: Click vào đây
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
- Tham gia nhóm Zalo (MIỄN PHÍ): https://wecommit.com.vn/zalo
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