1. Nếu không biết về chiến lược thực thi của câu lệnh, chúng ta như một người đi lạc mà không có bản đồ.

Chiến lược thực thi chính là tấm bản đồ để hệ thống có thể thực hiện theo yêu cầu của người dùng.

Khi chúng ta gửi một câu lệnh SQL tới hệ thống cũng giống với việc chúng ta yêu cầu một bác TÀI XẾ TAXI đưa chúng ta từ nhà đến công ty vậy.

Chúng ta có vô vàn phương án để đạt được mục tiêu (đi từ nhà đến công ty).

• Chúng ta có thể đi qua các phố chính
• Chúng ta cũng có thể đi đường vòng, đi đường tắt
• Chúng ta có thể có rất nhiều quyết định khác nhau mà vẫn đến được đích

Việc này tương đồng với khi chúng ta gửi một câu lệnh SQL, hệ thống có rất nhiều “phương án” để mang lại những kết quả mà ta yêu cầu:

• Hệ thống có thể thực hiện quét các bảng trong câu lệnh SQL theo những thứ tự khác nhau
• Để lấy thông tin trong một bảng, hệ thống có thể lựa chọn quét toàn bộ các block dữ liệu hoặc quét theo Index.
• Nếu lựa chọn quét thông tin qua Index, hệ thống có thể lựa chọn một trong các Index có sẵn.
• Ngay cả nếu đã quyết định sẽ quét 1 Index, hệ thống cũng có nhiều “giải thuật” để lọc thông tin trong index, ví dụ như INDEX RANGE SCAN, INDEX SKIP SCAN, INDEX FULL SCAN, INDEX FAST FULL SCAN…
• Và rất nhiều lựa chọn khác nữa

WOW, nếu bạn muốn sẽ thực hiện những bài toán tối ưu dữ liệu trọng yếu của ngân hàng, công ty chứng khoán; hay bạn muốn trải nghiệm chính tay mình cải thiện hiệu năng các chương trình từ vài chục phút xuống còn vài giây – như bản thân tôi đã trải nghiệm; có một thứ bạn chắc chắn cần hiểu rõ.

Thứ tôi đang nhắc tới chính là CHIẾN LƯỢC THỰC THI CỦA CÂU LỆNH.

2. Những lợi ích mà bạn thu được ở bài viết này

• Bạn sẽ biết một thứ mà những chuyên gia đều phải hiểu và nhuần nhuyễn trong các dự án tối ưu cho ngân hàng, chứng khoán.
• Bạn  được làm rõ các “THẮC MẮC” kinh điển của anh em lập trình.
• Bạn sẽ hiểu thực tế có thể cải thiện hơn 163% một câu lệnh (đây chỉ là ví dụ với dữ liệu nhỏ, thực tế dự án tôi làm cải thiện hàng nghìn, chục nghìn lần) ngay cả khi không viết lại câu lệnh, cũng chẳng cần phải bổ sung thêm Index
• Bạn biết cách ĐỌC và PHIÊN DỊCH từ chiến lược thực thi của câu lệnh sang ngôn ngữ mà chúng ta hiểu được.
• Nhiều điều khác nếu như bạn chú tâm

3. Cách đọc chiến lược thực thi câu lệnh

3.1. Ví dụ 1 – Hãy bắt đầu với một câu lệnh đơn giản nhất.

select * from emp

Chiến lược thực thi

• Đầu tiên: Thực hiện hành động “TABLE ACCESS FULL” (đây là hành động quét toàn bộ các BLOCK dữ liệu trong bảng) , đối tượng sét quét là bảng EMP.
• Thứ hai: Kết quả lấy được sẽ được trả ra

Lưu ý: Rất nhiều anh em lập trình viên hiểu nhầm hành động TABLE ACCESS FULL là QUÉT TOÀN BỘ các bản ghi trong bảng. ĐÂY CHÍNH LÀ HIỂU SAI BẢN CHẤT. Tôi đã từng viết một bài chứng minh hành động này thông qua việc: một TABLE có 0 bản ghi và bị TABLE ACCESS FULL nhưng thời gian thực hiện vẫn rất lâu. Nếu anh em nào chưa đọc thì có thể đọc bài viết đó tại đây

3.2. Ví dụ 2 – Tìm hiểu chiến lược thực thi khi Join nhiều bảng với nhau

select * from emp e, dept d where e.deptno=d.deptno and e.salary < 500

 

Đây là những thứ xảy ra trong đầu tôi khi nhìn vào chiến lược thực thi này, tôi sẽ diễn giải để các bạn có thể hiểu được “LUỒNG TƯ DUY” của tôi.

• Bạn có thể thấy rằng bước số 3 và số 5 (ở cột ID bên ngoài cùng tay trái) đang bị “THỤT LỀ PHẢI” sâu nhất, do đó hai bước này sẽ được thực hiện đầu tiên.
  • Bước số 3: hành động INDEX RANGE SCAN thực hiện trên IDX_SALARYTEST
  • Bước số 5: hành động INDEX UNIQUE SCAN thực hiện trên PK_DEPT
• Vậy đối với bước số 3 và số 5, bước nào sẽ thực hiện trước?
• Nếu như chúng ta có nhiều bước có cùng một “cấp độ” (bạn xem bằng mắt thường sẽ thấy chúng thẳng hàng với nhau), thứ tự ưu tiên sẽ được thực hiện TỪ TRÊN XUỐNG DƯỚI.
• Vậy bước số 3 sẽ thực hiện trước bước sô 5.
• Áp dụng đúng nguyên lý trên, chúng ta sẽ “dịch chiến lược thực thi bên trên” như sau
  • Bước thứ 1, Hệ thống thực hiện quét thông tin INDEX IDX_SALARYTEST sử dụng cách thức quét là INDEX RANGE SCAN
  • Bước thứ 2, Hệ thống sẽ thực hiện quét thông tin Index PK_DEPT sử dụng cách thức quét là INDEX UNIQUE SCAN
  • Bước thứ 3: Từ thông tin quét được đối với INDEX IDX_SALARYTEST, hệ thống sẽ có danh sách địa chỉ chi tiết (ROWID) các bản ghi thỏa mãn điều kiện lọc (SALARY < 500). Hệ thống thực hiện truy cập vào địa chỉ chi tiết này để tìm thông tin các cột còn lại của bảng. Đây chính là hành động TABLE ACCESS BY INDEX ROWID. Việc truy cập này sẽ được thực hiện trên bảng EMP (các bạn có thể nhìn thấy giá trị tương ứng này ở cột NAME).
  • Bước thứ 4: Từ thông tin quét được đối với index PK_DEPT, hệ thống sẽ có danh sách địa chỉ chi tiết (ROWID) của các bản ghi chứa giá trị Primary Key của bảng DEPT. Hệ thống sẽ thực hiện truy cập vào các địa chỉ này để có thông tin các cột còn lại trong bảng DEPT (do câu lệnh SELECT * nên hệ thống cũng cần trả ra thông tin các cột còn lại trong bảng DEPT)
  • Bước thứ 5: Thực hiện giải thuật Nest loops để tìm kiếm thông tin trùng khớp giữa 2 tập dữ liệu của bước thứ 3 và bước thứ 4
    • Tập dữ liệu thứ nhất: Các dữ liệu trong bảng EMP sau khi đã lọc điều kiện SALARY < 500
    • Tập dữ liệu thứ hai: Các dữ liệu trong bảng DEPT
    • Giải thuật Nest Loop Join sẽ thực hiện như sau:
      • Với mỗi giá trị dữ liệu trong tập dữ liệu thứ nhất, hệ thống sẽ so khớp giá trị theo điều kiện JOIN (e.deptno=d.deptno) với tập dữ liệu thứ hai.
        • Nếu giá trị so khớp thỏa mãn thì bản ghi đó sẽ là BẢN GHI CẦN TÌM KIẾM
        • Nếu giá trị so khớp không thỏa mãn thì loại
      • Thực hiện vòng lặp này cho đến khi hết toàn bộ tập dữ liệu ở bộ dữ liệu thứ nhất
  • Các kết quả thực hiện được ở bước thứ 5 sẽ trả ra cho người dùng.

Ghi chú: Nếu bạn muốn hiểu tường tận hơn nữa:

• Bạn muốn hiểu cụ thể INDEX RANGE SCAN thì hệ thống sẽ làm những công việc gì
• Bạn muốn biết trường hợp nào thì cơ sở dữ liệu sẽ ưu tiên sử dụng INDEX RANGE SCAN
• Bạn muốn biết nhiều thứ chuyên sâu hơn nữa đang diễn ra trong các dự án về Core banking, core chứng khoán.

Nếu bạn thực sự có quyết tâm và muốn biết tường tận các vấn đề này, bạn có thể tìm hiểu chương trình huấn luyện của tôi: Chương trình Từ điển tối ưu 100x hiệu năng

3.3. Ví dụ 3 – Hãy xem ví dụ này để hiểu rõ hơn nữa về chiến lược thực thi của câu lệnh

Chúng ta cùng xem ví dụ join của 2 bảng EMP và DEPT, về tư tưởng cũng giống với ví dụ bên trên, chỉ khác một điều rằng số cột cần lấy ra sẽ không phải là toàn bộ (thay vì SELECT * , chúng ta chỉ SELECT một số cột của bảng EMP)

select e.EMP_ID,e.DOB from emp e, dept d where e.deptno=d.deptno and e.salary < 500

Chiến lược thực thi câu lệnh

• Chiến lược thực thi mới không còn bước “TABLE ACCESS BY INDEX ROWID”
• Bước INDEX UNIQE SCAN trên PK_DEPT bây giờ đứng ngang hàng với bước TABLE ACCESS BY INDEX ROW ID trên bảng EMP (ở ví dụ trước thì bước này đứng ngang hàng với bước INDEX RANGE SCAN trên index IDX_SALARYTEST).

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

• Bước thực hiện đầu tiên sẽ là bước đang ở “SÂU NHẤT VÀO BÊN TAY PHẢI”, do đó chính là bước có ID= 3 (thực hiện quét Index IDX_SALARYTEST với hành động quét là INDEX RANGE SCAN)
• Trong hai bước đồng cấp tiếp theo (bước số 2 và số 4 ở cùng một cấp bậc – bạn có thể thấy chúng THẲNG HÀNG nếu nhìn bằng mắt thường):
  • Bước số 2 sẽ thực hiện trước vì thứ tự ưu tiên từ TRÊN xuống DƯỚI với các bước đồng cấp. Bước ID=2 là bước hệ thống thực hiện truy cập vào bảng dựa trên ROWID (TABLE ACCESS BY INDEX ROWID) đối với bảng EMP
  • Sau khi bước số 2 thực hiện, hệ thống sẽ thực hiện bước số 4: Quét index có tên PK_DEPT với hành động quét là INDEX UNIQUE SCAN
• Sau khi thực hiện xong 2 bước số 2 và số 4 bên trên. Hệ thống sẽ thực hiện giải thuật NEST LOOP JOIN để so khớp 2 tập dữ liệu đó
• Kết quả so khớp thành công sẽ được trả ra cho người dùng.

4. Thay đổi thứ tự viết câu lệnh thì có ảnh hưởng đến chiến lược thực thi không?

Trong quá trình đại diện cho Wecommit để huấn luyện tối ưu cơ sở dữ liệu cho những anh em DEV của các tập đoàn lớn, tôi nhận thấy một số băn khoăn của anh em như:

• Thay đổi thứ tự viết trong điều kiện WHERE có ảnh hưởng đến chiến lược thực thi hay không
• Thay đổi thứ tự của các bảng cần JOIN trong câu lệnh thì có ảnh hưởng đến chiến lược thực thi hay không

Tại bài viết này tôi sẽ lấy 1 số ví dụ để các bạn có thể tự mình kiểm chứng và không bao giờ lăn tăn vấn đề bên trên nữa.

Tôi thực hiện kiểm tra chiến lược thực thi của các câu lệnh sau

Câu lệnh thứ nhất:

SELECT e.EMP_ID, e.DOB
FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.salary < 500

Câu lệnh thứ hai: 

SELECT e.EMP_ID, e.DOB
FROM dept d, emp e
WHERE e.deptno = d.deptno AND e.salary < 500

Câu lệnh thứ ba:

SELECT e.EMP_ID, e.DOB
FROM dept d, emp e
WHERE e.salary < 500 and e.deptno = d.deptno 

Ghi chú:

• Câu lệnh thứ hai tôi đã đổi vị trí giữa bảng DEPT và bảng EMPT so với câu lệnh đầu tiên
• Câu lệnh thứ ba tôi quyết định đổi cả vị trí trong mệnh đề FROM và đổi cả vị trí đằng sau mệnh đề WHERE so với câu lệnh đầu tiên

Đây là chiến lược thực thi của hai câu lệnh

Chiến lược thực thi của câu lệnh đầu tiên

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

 

Tôi đã chụp ảnh đính kèm câu lệnh với chiến lược thực thi tương ứng để bạn thấy tính tin cậy của kết quả Demo.

Tại đây ta có thể thấy: CHIẾN LƯỢC THỰC THI CỦA CÁC CÂU LỆNH HOÀN TOÀN GIỐNG NHAU:

• Với các chiến lược thực thi ngắn, bạn có thể xem chi tiết từng bước để so sánh, hoặc bạn có thể sử dụng cách thức đơn giản, nhanh chóng (mà tôi vẫn thường sử dụng trong dự án thực tế)
• Kiểm tra giá trị Plan Hash Value (giá trị tôi bôi màu vàng bên trên). Các giá trị này giống nhau chứng tỏ hệ thống đang sử dụng cùng 1 chiến lược thực thi.

Ghi chú: Bài viết được chia sẻ bởi Trần Quốc Huy – CEO Wecommit.

 

5. Một số điều thú vị của chiến lược thực thi

5.1. Chuyển đổi ngầm định chiến lược thực thi

Trong quá trình phân tích chiến lược thực thi câu lệnh, hệ quản trị cơ sở dữ liệu có thể thực hiện các phép biến đổi một cách thông minh (với mục tiêu tìm ra cách tốt nhất, chiếm ít tài nguyên nhất để đạt được mục đích câu lệnh).

Đại đa số các anh em lập trình (ngay cả Senior) cũng không để ý sự chuyển đổi ngầm định này. Đây là điều tôi đã trực tiếp nhận ra khi tham gia những dự án tối ưu lớn cùng Wecommit. Các bạn có thể xem thông tin các dự án mà tôi và các anh em tại Wecommit đã trực tiếp thực hiện tại đây.

Trong ví dụ này tôi có một bảng lưu thông tin của nhân viên tên là EMP.  Tôi thực hiện tạo Index trên 2 cột (LAST_NAME, FIRST_NAME) như sau:

CREATE INDEX HUYTQ.IDX_LAST_FIRST ON HUYTQ.EMP
(LAST_NAME, FIRST_NAME).

Bây giờ người dùng cần thực hiện câu lệnh SQL sau:

SELECT *
FROM emp
WHERE last_name = 'TRAN' OR first_name = 'HUY'

Trước khi đọc nội dung bên dưới, bạn hãy thử đoán xem:

• Để tìm được kết quả của câu lệnh trên, hệ thống sẽ cần thực hiện chiến lược như thế nào?
• Hệ thống có sử dụng index IDX_LAST_FIRST mà tôi đã tạo ra hay không?

Dưới đây là kết quả thực hiện thực tế trên hệ thống.

• Hệ thống đã tự động biến đổi câu lệnh ban đầu của chúng ta (với điều kiện OR) thành câu lệnh UNION ALL như sau

SELECT *
FROM emp
WHERE last_name = 'TRAN'
UNION ALL
SELECT *
FROM emp
WHERE first_name = 'HUY'
and LNNVL(last_name = 'TRAN')

5.2. Khác biệt hàng hơn 163% thời gian thực thi với cùng một logic câu lệnh SQL

Bây giờ tôi sẽ tiến hành kiểm thử nếu cùng 1 câu lệnh, nhưng cơ sở dữ liệu chọn các giải thuật khác nhau (chiến lược thực thi khác nhau), thì thời gian sẽ ảnh hưởng như thế nào.

Câu lệnh được vô cùng dễ hiểu

SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno

Nếu chiến lược thực thi quyết định sử dụng giải thuật HASH_JOIN để tìm các bản thi thỏa mãn điều kiên WHERE e.deptno = d.deptno

Nếu chiến lược thưc thi lựa chọn giải thuật NESTED LOOPS để tìm các bản thi thỏa mãn điều kiên WHERE e.deptno = d.deptno

Ghi chú:

• Chúng ta có thể chỉ định cơ sở dữ liệu lựa chọn những giải thuật mà chúng ta coi là đúng.
• Với từng cơ sở dữ liệu khác nhau, thuật ngữ này sẽ có tên gọi khác nhau, nhưng về bản chất thì cùng chung một mục đích.
• Trong ví dụ trên, tôi đã sử dụng chỉ định Oracle sử dụng giải thuật NESTED LOOPS JOIN thông qua cú pháp /*+use_nl(e,d)*/ (trong Oracle gọi đây là HINT)

Phân tích kết quả:

• Về thời gian thực thi:
  • Thời gian thực thi của câu lệnh  thứ hai (dự kiến mất 13 phút 2s) bằng 162% thời gian thực thi câu lệnh thứ nhất (dự kiến mất 7 phút 59s)
• Về chi phí phải thực thi câu lệnh
  • Chi phí thực hiện của câu lệnh thứ hai (65132) bằng 163.3% so với chi phí thực hiện (Cost) của câu lệnh đầu tiên (có giá trị bằng 39882)

Như vậy, nếu chọn nhầm chiến lược thực thi, câu lệnh sẽ chạy tiêu tốn tài nguyên và thời gian gấp hơn 1.5 lần!!

Tại đây bạn có thể thấy rằng cú pháp viết câu lệnh chỉ là một phần nhỏ trong quá trình chúng ta thực hiện tối ưu. Bạn cần phải hiểu rất rõ về các chiến lược thực thi, nếu như muốn trực tiếp đưa phương án và thực hiện phương án tối ưu trên những hệ thống yêu cầu khắt khe về chất lượng như: các hệ thống Core giao dịch chứng khoán, hệ thống Core banking.

Trong thực tế chuyện này có hay xảy ra không?

6. Sau khi đã hiểu về chiến lược thực thi, bạn có thể đọc tiếp các nội dung khác về tối ưu SQL dưới đây

• Sử dụng HINT trong tối ưu SQL: click vào đây.
• 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

7. Trong các dự án tối ưu thực tế, có trường hợp nào hệ thống lựa chọn nhầm chiến lược thực thi hay không?

Câu trả lời là CÓ.

Và việc này xảy ra khá nhiều, nếu bạn nào đã từng phải tối ưu các hệ thống Core chứng khoán. một số bài toán có thể gặp phải như:

• Hệ thống tự nhiên hoạt động chậm, khi kiểm tra thì phát hiện câu lệnh SQL trong ngày hôm nay tự nhiên nhận 1 chiến lược thực thi hoàn toàn khác (hệ thống lựa chọn giải thuật JOIN không hợp lý hoặc một số trường hợp hệ thống lại lựa chọn nhầm thứ tự thực hiện quá trình JOIN)
• Khi bị chậm, anh em lập trình kiểm tra thì thấy rằng: câu lệnh không có gì thay đổi cả, gần đây cũng không cập nhật gì mới.

Tất cả chi tiết những bài toán này sẽ được tôi trực tiếp truyền đạt trong chương trình huấn luyện đặc biệt, cùng với giải pháp đã được áp dụng thành công trên những hệ thống có yêu cầu chất lượng vô cùng khắt khe.

Có thể nói rằng, nếu bạn biết được những kinh nghiệm chuyên sâu về tối ưu Cơ sở dữ liệu này, bạn sẽ hoàn toàn khác biệt so với rất nhiều anh em lập trình hiện nay. Bạn khác biệt vì bạn mang lại GIÁ TRỊ RẤT LỚN.

Nếu bạn muốn biết thêm về chương trình huấn luyện của tôi, bạn có thể click tại đây.

8. Nếu bạn muốn xem các giải pháp tối ưu được áp dụng trong những hệ thống Production giao dịch 24×7

Nếu bạn muốn được tôi đồng hành và chia sẻ toàn bộ những kinh nghiệm và bí kíp của tôi tích lũy được trong quá trình làm dự án về tối ưu, bạn có thể tham khảo chương trình này. Click vào đây để xem chương trình.

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