Tinh chỉnh hiệu suất thực thi (Performance tuning) là một vấn đề rất khó và không có một quy tắc vàng nào cả cho vấn đề này. Về mặt lý thuyết, việc tinh chỉnh hiệu năng của database phải được thực hiện bởi các DBA (Database Administrator) lâu năm.
Tuy nhiên họ sẽ không có đủ thời gian để rà soát lại tất cả mọi thay đổi của các procedure, do đó học một số kiến thức căn bản về điều chỉnh thực thi sẽ giúp bạn tiết kiệm thời gian cho việc phải làm việc lại với các mã nguồn.
Sau đây là danh sách 20 gợi ý cơ bản nhất mà bất kỳ một database developer nào cũng cần phải thực hiện trong khi điều chỉnh hiệu năng, thực hiện những điều này đảm bảo hiệu năng sẽ được cải thiện một cách chắc chắn.
1) Đầu tiên trong quá trình lấy yêu cầu dự án và thiết kế database nên hiểu rõ nghiệp vụ cụ thể của dự án để thiết kế kiến trúc logic database và chọn loại database cho phù hợp.
- Nếu dự án cần tốc độ đọc ghi nhiều và ràng buộc toàn vẹn không phải là vấn đề sống còn thì nên thiết kế database bỏ bớt các quan hệ khóa chính, khóa ngoại, không dùng trigger. (Có thể sử dụng Sqlserver, Mysql hoặc NoSQL)
- Nếu dự án cần nhất quán dữ liệu và ràng buộc toàn vẹn là vấn đề sống còn thì nên thiết kế có đầy đủ khóa chính, khóa ngoại, trigger...thiết kế theo tuân theo dạng chuẩn, chuần càng cao thì sự trùng lắp dữ liệu càng ít và ngược lại. (Nên sử dụng loại database RDBMS Sqlserver, Oracle...)
- Nếu dự án lớn thì việc sử dụng kết hợp nhiều loại database để đạt được mục đích về tốc độc, hiệu năng, toàn vẹn dữ liệu thì cũng là điều bình thường. Tuy nhiên khi đó thì kiến trúc database của dự án phức tạp và cần có sự tổ chức kiến trúc chặt chẽ, thống nhất ngay từ đầu.
2) Tạo Primary key trên mỗi bảng (trừ khi bạn đủ thông thạo để có thể cấu hình một kế hoạch tốt hơn), thiết lập cho PK này là Clustered Index (Chú ý nếu bạn tạo PK trong SSMS thì điều này là mặc định)
3) Tạo index cho tất cả các cột được xác định là Foreign key, nếu bạn biết chắc cột khóa ngoại này có giá trị là duy nhất thì cần tạo thuộc tính cho index của các FK này là Unique.
4) Tạo UNIQUE INDEX cho các cột được dùng nhiều và thường xuyên trong điều kiện WHERE để truy cập database (email, username...)
5) Luôn tham chiếu đến các đối tượng bằng tên sở hữu đầy đủ trong four-part name, viết dbo.sysdatabases thay vì chỉ viết sysdatabases. (chỉ với SQL Server )
6) Sử dụng tùy chọn set nocount on ở phần đầu của mỗi procedure và set nocount off ở cuối mỗi procedure. (chỉ với SQL Server )
7) Nghĩ kỹ về LOCK. Nếu bạn không viết phần mềm về ngân hàng, thì vấn đề là có thể bạn sẽ nắm lấy cơ hội để thực hiện việc đọc dữ liệu một cách tự do thoải mái. Gợi ý là bạn có thể dùng với từ khóa NOLOCK, nhưng sẽ đơn giản hơn nếu sử dụng tùy chọn SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ở đầu mỗi procedure và thiết lập về READ COMMITTED ở cuối.
8) Chỉ select trả về những colum cần thiết và chỉ rõ các cột, hạn chế sử dụng SELECT * FROM hết mức có thể.
9) Sử dụng TRANSACTION khi thích hợp, và không cho bất kỳ người dùng nào tương tác vào khi transaction đang thực hiện.
10) Bỏ qua việc sử dụng bảng tạm hết mức có thể, nếu cùng quá bắt buộc phải tạo bảng tạm, bạn nên tạo bảng bằng khai báo tường minh bằng mệnh đề tạo bảng CREATE TABLE #Temp. (chỉ với SQL Server )
11) Bỏ qua sử dụng NOT IN, thay vào đó hãy sử dụng LEFT JOIN, mặc dù về tính dễ đọc và dễ viết mã thì NOT IN trông có vẻ rõ ràng hơn. Hãy Sử dụng EXISTS, NOT EXISTS thay cho NOT IN.
12) Khi sử dụng SQL động, hãy sử dụng sp_executesql với tham số được đặt tên (hơn là sử dụng EXEC hoặ EXECUTE), vì với thủ tục này, bạn có cơ hội sử dụng lại kế hoạch thực thi (query plan), tiết kiệm đáng kể thời gian cho trình biên dịch lập lại kế hoạch thực thi khi bạn chạy lại thủ tục tương ứng. (chỉ với SQL Server )
13) Tạo lập thói quen lập tài liệu về thủ tục của bạn trước và sau mỗi lần thay đổi. Luôn giữ ý niệm rằng, nếu sau mỗi lần thay đổi, CPU phải tăng thêm 10 - 15% năng lực cho việc đọc, ghi thì rất có thể thủ tục của bạn cần phải xem xét lại.
14) Tránh việc cứ phải "đi qua đi lại" (round trips) giữa server và client, thay vào đó, hãy gửi các yêu cầu cần thiết một lần tới máy chủ thực thi và trả về một tập hợp nhiều kết quả là một trong những cách để giải quyết vấn đề này.
15) Bỏ qua Index hints và JOIN hints.
16) Sử dụng tính năng Partition của database để phân chia dữ liệu logic database thành nhiều file lưu trữ vật lý, khi dữ liệu trong database hoặc table quá lớn. (Vì thao tác truy xuất I/O của database là thao tác tốn rất nhiều hiệu năng của server và file dữ liệu càng lớn thì tốc độ truy xuất càng chậm và càng chiếm nhiều hiệu năng)
17) Khi dự án lớn và dữ liệu đòi hỏi tính an toàn và sẵn sàng cao (High availability) của database thì nên sử dụng tính năng Replicate, Cluster, Mirror...
18) Sử dụng công cụ đo hiệu năng có sẵn (Performance tool) của database hoặc Third party để theo dõi: số lượng đọc, ghi, số lượng lệnh kết nối gọi đến server, thời gian thực hiện truy vấn, hiệu năng server bị chiếm giữ (RAM, CPU, HDD) ... Hãy kết hợp với DBA để làm việc này sẽ hiệu quả hơn.
19) Việc chạy script để thực hiện Job schedule (Crontab) hoặc Backup database định kỳ nên thực hiện vào lúc ít người sử dụng sản phẩm nhất.
20) Việc backup database nên thực thiện có kế hoạch hẳn hoi để tránh trường hợp mất mát dữ liệu, đồng thời không phải lúc nào cũng backup full database mà phải có kế hoạch hợp lý như backup full, backup incremental, backup log...
Nếu bạn thực hiện được 20 gợi ý như trên, dự án của bạn sẽ đảm bảo được về tộc độ, độ an toàn và tin cậy cao khi vào vận hành thực sự.
Sẽ còn nhiều thứ phải tiếp tục nghiên cứu về database khi chúng ta xây dựng một mô hình ứng dụng, mạng, kiến trúc...ở các dự án phần mềm, website.
Xin cảm ơn bạn đã đọc bài viết của chúng tôi, xin chúc bạn thành công !