Tinh chỉnh cơ sở dữ liệu (Database Tuning)

1. Tổng quan

A. Khái niệm và mục đích

Tinh chỉnh cơ sở dữ liệu là hoạt động chẩn đoán nguyên nhân suy giảm hiệu năng của cơ sở dữ liệu và tối ưu hóa ở nhiều tầng thiết kế·DBMS·SQL nhằm cải thiện thời gian phản hồi (response time) và thông lượng (throughput). Dung lượng dữ liệu càng lớn và số người dùng đồng thời càng tăng thì sự cần thiết của nó càng lớn.

Lý do căn bản khiến tinh chỉnh DB quan trọng nằm ở chỗ "cùng dữ liệu·cùng phần cứng nhưng tùy cách thiết kế và truy vấn mà hiệu năng chênh nhau hàng chục đến hàng trăm lần". Một hệ thống chạy tốt khi dữ liệu còn ít, khi dữ liệu tích tụ và người dùng dồn về thì phản hồi chậm hẳn đi và dẫn tới sự cố dịch vụ. Khi đó, việc mở rộng phần cứng (scale-up) một cách mù quáng tốn kém và không phải giải pháp gốc rễ. Với một truy vấn trước đây mỗi lần đều quét toàn bộ (full table scan) bảng 1 triệu bản ghi vì không có chỉ mục, chỉ cần tạo chỉ mục phù hợp là cùng truy vấn đó chỉ đọc vài nghìn bản ghi rồi kết thúc, phản hồi nhanh tức thì. Tài nguyên (phần cứng) giữ nguyên mà hiệu năng được cải thiện vượt bậc.

Cụ thể hóa mục đích của tinh chỉnh thành chỉ số hiệu năng thì chia thành hai trục. Một là thời gian phản hồi, xem từng truy vấn kết thúc nhanh tới mức nào; hai là thông lượng, xem xử lý được bao nhiêu giao dịch trong một đơn vị thời gian. Trong giao dịch trực tuyến (OLTP) thì thời gian phản hồi ngắn quan trọng hơn, còn trong batch·phân tích khối lượng lớn (OLAP) thì thông lượng cao quan trọng hơn. Mục đích khác thì hướng tinh chỉnh cũng khác, nên bước đầu tiên trước khi tinh chỉnh là định nghĩa rõ mục tiêu "sẽ cải thiện điều gì". Tinh chỉnh không có mục tiêu dễ rơi vào bẫy cải thiện bên này nhưng hy sinh bên kia.

B. Các tầng đối tượng của tinh chỉnh

Tinh chỉnh được tiếp cận chủ yếu ở ba tầng. Thứ nhất, tinh chỉnh thiết kế làm cho bản thân cấu trúc dữ liệu như cấu trúc bảng·chỉ mục·phân vùng có lợi cho hiệu năng; đây là tầng căn bản nhất nhưng chi phí thay đổi lớn với hệ thống đang vận hành. Thứ hai, tinh chỉnh DBMS điều chỉnh cấp phát bộ nhớ·buffer cache·các tham số để tối ưu việc sử dụng tài nguyên của engine DBMS. Thứ ba, tinh chỉnh SQL cải thiện từng câu truy vấn và kế hoạch thực thi của nó; phạm vi thay đổi hẹp, rủi ro nhỏ mà hiệu quả lớn nên có tỷ lệ hiệu quả trên chi phí cao nhất. Trong thực tế, thường một số ít SQL kém hiệu quả chiếm phần lớn tải toàn hệ thống, nên việc tìm và cải thiện các "SQL có vấn đề" này trở thành cốt lõi của tinh chỉnh.

2. Các tầng tinh chỉnh và cấu trúc tổng thể

Tinh chỉnh DB không phải công việc một lần sửa một điểm, mà là quá trình tuần hoàn lặp lại chẩn đoán→phân tích→cải thiện→kiểm chứng. Sơ đồ cấu trúc dưới đây thể hiện ba tầng và các kỹ thuật tiêu biểu của từng tầng.

flowchart TB
  T["Tinh chỉnh DB"] --> D["Tinh chỉnh thiết kế"]
  T --> M["Tinh chỉnh DBMS"]
  T --> S["Tinh chỉnh SQL"]
  D --> D1["Phi chuẩn hóa"]
  D --> D2["Thiết kế chỉ mục"]
  D --> D3["Phân vùng"]
  M --> M1["Bộ nhớ·buffer cache"]
  M --> M2["Điều chỉnh tham số"]
  S --> S1["Phân tích kế hoạch thực thi"]
  S --> S2["Hint·viết lại SQL"]
  style T fill:#e8f0fe,stroke:#2f6fed,stroke-width:2px
  style S fill:#eef7ee,stroke:#2f8f2f,stroke-width:2px

Ba tầng bổ trợ lẫn nhau. SQL viết tốt đến đâu mà không có chỉ mục thì cũng có giới hạn, có chỉ mục mà buffer cache thiếu thì I/O đĩa trở thành nút thắt cổ chai. Tuy nhiên thứ tự ưu tiên cải thiện kinh tế nhất thường là "xác định SQL có vấn đề bằng chẩn đoán → tinh chỉnh SQL·chỉ mục → khi cần thì tinh chỉnh thiết kế·DBMS → cuối cùng mới mở rộng phần cứng". Vì nguyên tắc là bắt tay từ những gì rủi ro và chi phí nhỏ trước.

A. Quy trình tinh chỉnh: đo lường và chẩn đoán

Tinh chỉnh phải dựa trên "dữ liệu" chứ không phải "cảm tính". Nếu động vào chỗ này chỗ kia khi chưa tìm đúng nút thắt, chỉ tích tụ các thay đổi vô hiệu và còn gây tác dụng phụ. Vì vậy điểm xuất phát của tinh chỉnh luôn là đo lường và chẩn đoán. Công cụ tiêu biểu là kế hoạch thực thi (Execution Plan). Kế hoạch thực thi là đường xử lý mà bộ tối ưu hóa (Optimizer) lập ra để xử lý truy vấn, cho thấy dùng chỉ mục nào, kết nối (join) theo cách·thứ tự nào, số bản ghi xử lý dự kiến là bao nhiêu.

Các tín hiệu cần chú ý khi chẩn đoán có thể thu gọn thành vài loại: quét toàn bảng (Full Table Scan) trên bảng dung lượng lớn, hiện tượng đã tạo chỉ mục mà không được dùng, trường hợp thứ tự join bị đảo khiến kết quả trung gian phình to, và trường hợp thông tin thống kê cũ khiến optimizer ước lượng sai phân phối dữ liệu thực tế. Thêm vào đó, dùng các công cụ như SQL trace hay AWR·performance view để xếp hạng SQL nào tiêu tốn nhiều tài nguyên (CPU·I/O·thời gian), ta có thể tập trung xử lý từ các truy vấn hàng đầu có hiệu quả cải thiện lớn. Vòng tuần hoàn "đo lường→xác định vấn đề hàng đầu→cải thiện→đo lại" như vậy là bộ khung của tinh chỉnh.

3. Các kỹ thuật tinh chỉnh ở giai đoạn thiết kế

Tinh chỉnh ở giai đoạn thiết kế làm cho bản thân cấu trúc dữ liệu có lợi cho hiệu năng; đây là cách căn bản nhất và hiệu quả lớn nhưng cũng phải thận trọng tương ứng. Vì thay đổi cấu trúc trong hệ thống vận hành đã tích lũy dữ liệu kéo theo chi phí di chuyển dữ liệu và rủi ro về tính nhất quán.

Kỹ thuật	Nội dung	Đánh đổi
Phi chuẩn hóa	Cố ý cho phép trùng lặp để giảm join (hiệu năng truy vấn↑)	Gánh nặng quản lý nhất quán khi cập nhật↑
Thiết kế chỉ mục	Tạo chỉ mục trên cột thường được truy vấn	Chi phí cập nhật chỉ mục khi chèn·sửa↑
Phân vùng	Chia bảng lớn để thu hẹp phạm vi truy cập	Vô hiệu nếu thiết kế khóa phân vùng thất bại
Kiểu dữ liệu phù hợp	Tối ưu kích thước·định dạng để tăng hiệu quả lưu trữ·I/O	Thu nhỏ quá mức cản trở khả năng mở rộng

A. Phi chuẩn hóa (Denormalization)

Phi chuẩn hóa là kỹ thuật cố ý gộp lại các bảng đã bị chuẩn hóa chia nhỏ hoặc đặt cột trùng lặp để phục vụ hiệu năng truy vấn. Chuẩn hóa loại bỏ trùng lặp dữ liệu để nâng tính nhất quán, nhưng mỗi lần truy vấn phải join nhiều bảng nên chi phí lớn khi truy vấn khối lượng lớn. Ví dụ, nếu màn hình danh sách đơn hàng mỗi lần đều phải join bảng khách hàng·sản phẩm để lấy tên, có thể lưu trùng tên khách hàng·tên sản phẩm vào bảng đơn hàng để loại bỏ join. Tuy nhiên điều này sinh ra gánh nặng phải cập nhật cả bản trùng khi bản gốc thay đổi, nên chỉ nên áp dụng chọn lọc cho dữ liệu "truy vấn nhiều, cập nhật ít". Phi chuẩn hóa là một đánh đổi rõ ràng giữa hiệu năng truy vấn và chi phí quản lý nhất quán, áp dụng bừa bãi sẽ dẫn tới vấn đề lớn hơn là bất nhất dữ liệu.

B. Thiết kế chỉ mục

Chỉ mục là cấu trúc giúp tìm nhanh vị trí dữ liệu giống như mục lục của sách, phần lớn có dạng B-tree (B-Tree). Có chỉ mục thì chỉ chọn đọc số ít dòng thỏa điều kiện, nên truy vấn nhanh hơn đáng kể so với quét toàn bộ. Khái niệm quan trọng trong thiết kế chỉ mục là cardinality (độ đa dạng của giá trị) và độ chọn lọc (selectivity). Cột có ít loại giá trị (cardinality thấp) như giới tính thì hiệu quả chỉ mục nhỏ, còn cột có giá trị gần như duy nhất như số định danh công dân·số tài khoản thì hiệu quả chỉ mục lớn. Với chỉ mục phức hợp gộp nhiều cột, thứ tự cột quan trọng, nên đặt phía trước các cột hay dùng trong điều kiện và có độ chọn lọc cao. Chỉ mục bao phủ (covering index) — xử lý truy vấn chỉ bằng chỉ mục (chỉ đọc chỉ mục mà không truy cập bảng) — mang lại lợi thế hiệu năng bổ sung.

C. Phân vùng (Partitioning)

Phân vùng là kỹ thuật chia một bảng lớn — về logic vẫn là một — thành nhiều mảnh về mặt vật lý. Ví dụ, nếu phân vùng dữ liệu đơn hàng nhiều năm theo tháng, khi truy vấn dữ liệu của một tháng cụ thể chỉ truy cập phân vùng tương ứng và bỏ qua phần còn lại (cắt tỉa phân vùng, partition pruning). Chọn tiêu chí chia như khoảng (Range)·danh sách (List)·băm (Hash) phù hợp với workload. Hiệu quả của phân vùng đạt tối đa khi khóa phân vùng khớp với điều kiện truy vấn; nếu điều kiện không dùng khóa phân vùng thì phải lục soát mọi phân vùng và hiệu quả biến mất. Ngoài ra, dễ xóa·lưu trữ nguyên cả phân vùng cũ nên cũng có lợi cho quản lý dữ liệu lịch sử dung lượng lớn.

D. Tinh chỉnh tầng DBMS (bộ nhớ·tài nguyên)

Nếu tinh chỉnh thiết kế·SQL xử lý "đọc cái gì và đọc thế nào", thì tinh chỉnh tầng DBMS xử lý "giữ dữ liệu đã đọc hiệu quả tới mức nào". Cốt lõi là cân bằng giữa bộ nhớ và I/O đĩa. DBMS đưa các khối dữ liệu hay dùng lên buffer cache để giảm truy cập đĩa; nếu cache này thiếu thì dữ liệu đã đọc phải đọc lại từ đĩa nhiều lần (cache miss) và hiệu năng giảm. Ngược lại cũng không thể tăng vô hạn, nên cốt lõi của tinh chỉnh DBMS là tìm kích thước phù hợp dựa trên các chỉ số như tỷ lệ trúng cache (cache hit ratio).

Các phép toán cần không gian làm việc trung gian như sắp xếp·hash join, khi bộ nhớ làm việc (sort/hash area) thiếu sẽ tạo vùng tạm trên đĩa để xử lý (sắp xếp trên đĩa), chậm hơn nhiều so với xử lý trong bộ nhớ. Vì vậy workload dạng batch có nhiều sắp xếp·tổng hợp khối lượng lớn nên cấp bộ nhớ làm việc rộng rãi. Như vậy, tinh chỉnh tham số DBMS là công việc điều chỉnh bộ nhớ·mức song song·cách commit, v.v. phù hợp với tính chất workload (OLTP hay OLAP), có ưu điểm là nâng được hiệu năng tổng thể mà không phải động vào mã ứng dụng. Tuy nhiên một tham số ảnh hưởng tới toàn hệ thống, nên trước khi áp dụng vào môi trường vận hành phải kiểm chứng bằng kiểm thử tải đầy đủ.

4. Tinh chỉnh SQL, optimizer và hint

Tinh chỉnh SQL là hoạt động cải thiện câu truy vấn và kế hoạch thực thi, phạm vi thay đổi hẹp nên rủi ro nhỏ mà hiệu quả lớn — cốt lõi của tinh chỉnh. Nhân vật trung tâm ở đây là optimizer. Phần lớn DBMS hiện đại dùng bộ tối ưu dựa trên chi phí (CBO, Cost-Based Optimizer), ước lượng chi phí của nhiều đường xử lý dựa trên thông tin thống kê (số bản ghi của bảng, phân phối giá trị cột, v.v.) và chọn đường rẻ nhất. Do đó nếu thông tin thống kê lệch với dữ liệu thực, optimizer sẽ lập kế hoạch sai lệch. Đây là lý do thường gặp trường hợp hiệu năng tụt mạnh vì không cập nhật thông tin thống kê sau khi dữ liệu thay đổi hàng loạt.

Optimizer không phải lúc nào cũng tìm ra đường tối ưu. Khi optimizer lập kế hoạch sai do thống kê không chính xác, join phức tạp, phân phối dữ liệu lệch, nhà phát triển có thể dùng hint (Hint) để trực tiếp chỉ định cách thực thi và sửa lại. Tuy nhiên hint là cưỡng chế ghi đè phán đoán của optimizer, nên khi phân phối dữ liệu thay đổi thì có thể lại thành độc hại, tuyệt đối không lạm dụng. Nên ưu tiên cập nhật thống kê·viết lại SQL để dẫn dắt optimizer tự lập kế hoạch tốt, còn hint dùng như biện pháp cuối cùng.

Loại hint	Nội dung	Bối cảnh sử dụng
Đường truy cập	Chỉ định dùng/không dùng chỉ mục (INDEX, FULL)	Khi optimizer không dùng hoặc dùng sai chỉ mục
Cách join	Chỉ định phương pháp join (Nested Loop, Hash, Sort Merge)	Cưỡng chế join phù hợp với kích thước dữ liệu
Thứ tự join	Chỉ định thứ tự join bảng (ORDERED, LEADING)	Khi muốn giữ kết quả trung gian nhỏ
Xử lý song song	Chỉ định thực thi song song (PARALLEL)	Tăng thông lượng trong batch·tổng hợp lớn

A. Cách join và viết lại SQL (trường hợp cụ thể)

Việc chọn cách join ảnh hưởng lớn tới hiệu năng tùy quy mô dữ liệu. Khi join một bảng nhỏ với một bảng lớn có chỉ mục tốt, Nested Loop join có lợi. Ngược lại nếu cả hai đều lớn, Hash join — biến một bên thành bảng băm để so khớp — nhanh hơn nhiều. Trường hợp tinh chỉnh điển hình là một truy vấn mất hàng chục phút vì optimizer do lỗi thống kê chọn Nested Loop cho join dung lượng lớn, được rút xuống vài giây nhờ đổi sang hint Hash join.

Viết lại SQL cũng là kỹ thuật mạnh. Ví dụ đổi truy vấn con tương quan (thực thi lặp truy vấn con cho từng dòng) thành join, hoặc viết lại điều kiện bọc hàm quanh cột chỉ mục (WHERE SUBSTR(col,1,2)='AB') khiến chỉ mục bị vô hiệu thành không dùng hàm (WHERE col LIKE 'AB%') để dùng được chỉ mục. Bản chất của tinh chỉnh SQL là trau chuốt truy vấn sao cho "kết quả như nhau nhưng optimizer lập được kế hoạch tốt hơn".

5. Nâng cao: Tính hai mặt của chỉ mục và chiến lược tinh chỉnh thực tế

Chỉ mục thường được coi là "chìa khóa vạn năng của tinh chỉnh", nhưng cũng là điểm bị hiểu lầm nhiều nhất trong thực tế. Chỉ mục làm truy vấn (SELECT) nhanh hơn, đổi lại gây chi phí phải cập nhật cả chỉ mục mỗi khi chèn·sửa·xóa (INSERT/UPDATE/DELETE). Nếu đặt năm chỉ mục trên một bảng, mỗi lần chèn một dòng vào bảng đó phải cập nhật cả năm chỉ mục nên hiệu năng ghi giảm mạnh. Do đó với bảng mà truy vấn áp đảo thì dùng chỉ mục tích cực, còn với bảng ghi thường xuyên thì chỉ chọn lọc các chỉ mục thực sự cần. Quan niệm "càng nhiều chỉ mục càng tốt" là sai, và chỉ mục không được dùng (unused index) chỉ tiêu tốn không gian lưu trữ và chi phí ghi nên phải định kỳ kiểm tra và loại bỏ.

Chiến lược tinh chỉnh thực tế có thể tóm tắt như sau. Thứ nhất, theo nguyên lý Pareto, tập trung vào số ít SQL hàng đầu chiếm phần lớn tải. Dùng performance view lọc ra các truy vấn tiêu thụ tài nguyên nhiều nhất và cải thiện thì với ít công sức vẫn thu hiệu quả lớn. Thứ hai, duy trì thông tin thống kê mới nhất. Sau thay đổi·nạp dữ liệu hàng loạt nhất định phải cập nhật thống kê để optimizer phán đoán đúng. Thứ ba, nhất định so sánh định lượng trước và sau tinh chỉnh. Đo kế hoạch thực thi·thời gian phản hồi·số khối đọc logic trước và sau cải thiện để kiểm chứng hiệu quả, và xác nhận không có tác dụng phụ (hiệu năng truy vấn khác giảm). Thứ tư, xem xét cả vấn đề ở tầng ứng dụng. Truy vấn N+1 (anti-pattern gửi truy vấn từng cái một trong vòng lặp) hay thiếu connection pool không thể giải quyết chỉ bằng tinh chỉnh DB, nên phải chẩn đoán tích hợp ứng dụng-DB.

6. Các lưu ý và hàm ý

Từ góc nhìn Kỹ sư chuyên nghiệp, tinh chỉnh DB không nên là liệt kê các kỹ thuật rời rạc mà phải được tiếp cận như chiến lược quản lý hiệu năng có hệ thống·kinh tế dựa trên chẩn đoán.

Đo lường·chẩn đoán là điểm xuất phát của tinh chỉnh. Chỉ bắt tay sau khi đã tìm đúng nút thắt (truy vấn chậm·quét toàn bộ·lỗi thống kê) bằng phân tích kế hoạch thực thi·SQL trace·performance view. Tinh chỉnh theo cảm tính không hiệu quả hoặc gây tác dụng phụ. Nguyên tắc "không đo thì không thể cải thiện" xuyên suốt toàn bộ việc tinh chỉnh.
Chỉ mục là con dao hai lưỡi. Truy vấn nhanh hơn nhưng gánh nặng cập nhật tăng, nên phải tổng hợp mẫu truy vấn·cập nhật và cardinality·độ chọn lọc để chỉ chọn lọc các chỉ mục thực sự cần. Chỉ mục quá mức làm hại hiệu năng ghi và chỉ mục không dùng chỉ lãng phí tài nguyên nên cần kiểm tra định kỳ.
Ưu tiên tinh chỉnh hơn mở rộng phần cứng. Để mặc sự kém hiệu quả (quét toàn bộ·SQL kém hiệu quả) mà chỉ tăng máy chủ thì chi phí tăng và sớm chạm giới hạn. Kinh tế nhất là tận dụng tối đa tài nguyên bằng tinh chỉnh SQL·chỉ mục, rồi khi vẫn thiếu mới mở rộng. Đặc biệt trong môi trường đám mây, truy vấn kém hiệu quả dẫn thẳng tới tính phí (chi phí tính toán·I/O) nên giá trị kinh tế của tinh chỉnh càng lớn.
Quản lý thông tin thống kê và optimizer. Optimizer dựa trên chi phí phụ thuộc vào thông tin thống kê, nên phải tự động hóa·định kỳ hóa việc cập nhật thống kê sau thay đổi hàng loạt để optimizer luôn phán đoán chính xác. Hint chỉ dùng hạn chế như biện pháp cuối cùng; về căn bản, dẫn dắt optimizer bằng thống kê·viết lại SQL mới là cách bền vững.
Nhìn tích hợp ứng dụng-DB. Các yếu tố ở tầng ứng dụng như truy vấn N+1·connection pool·chiến lược cache không thể giải quyết chỉ bằng tinh chỉnh DB. Phải chẩn đoán cả các truy vấn kém hiệu quả phát sinh khi dùng ORM, các truy vấn lặp không cần thiết thì mới cải thiện căn bản được hiệu năng tổng thể.

Tài liệu tham khảo

Oracle Database SQL Tuning Guide: https://docs.oracle.com/en/database/oracle/oracle-database/
Use The Index, Luke (hiệu năng chỉ mục·SQL): https://use-the-index-luke.com/

Tóm tắt một câu: Tinh chỉnh DB là hoạt động tìm nút thắt bằng chẩn đoán dựa trên phân tích kế hoạch thực thi rồi tối ưu ở các tầng thiết kế (phi chuẩn hóa·chỉ mục·phân vùng)·DBMS·SQL (viết lại truy vấn·hint), cân bằng giữa đánh đổi truy vấn·cập nhật của chỉ mục và quản lý thống kê của optimizer — một biện pháp cải thiện hiệu năng kinh tế đi trước việc mở rộng phần cứng.