Mặc dù các hệ quản trị CSDL quan hệ ngày nay mới hơn và phần cứng nhanh hơn, có thể chạy hầu hết các truy vấn SQL với thời gian đáp ứng ngắn, nhưng luôn có lý do để cải tiến. Phần này đưa ra 10 lời khuyên mà mọi cán bộ phát triển/ DBA nên lưu ý khi thiết kế CSDL hoặc viết câu truy vấn SQL. Ở đây cũng đề cập đến những lỗi chung mà các cán bộ phát triển hay gặp và cách phòng ngừa chúng.
Mục lục
Ví dụ về Cơ sở dữ liệu
Để giải thích những câu truy vấn SQL được giới thiệu trong mục này, chúng ta sẽ đưa ra 1 CSDL với các bảng bên dưới.
Table schema for Customer
| Field Name | Data Type | Primary Key |
|---|---|---|
| CustomerID | Integer | Yes |
| FirstName | Varchar(30) | |
| LastName | Varchar(30) | |
| City | Varchar(30) | |
| State | Varchar(10) | |
| Phone | Varchar(15) | |
| AccountCreatedOn | Datetime |
Indexes for Customer
| Index Name | Fields | Clustered |
|---|---|---|
| CustPK | CustomerID | Yes |
| IdxState | State | |
| IdxPhone | Phone | |
| IdxName | FirstName, LastName | |
| IdxCity | City, State |
Table schema for Orders
| Field Name | Data Type | Primary Key |
|---|---|---|
| OrderID | Integer | Yes |
| CustomerID | Integer | |
| OrderDate | Datetime | |
| OrderCost | Decimal | |
| Shipped | Char(1) |
Indexes for Orders
| Index Name | Fields | Clustered |
|---|---|---|
| OrderPK | OrderID | Yes |
| IdxCustDate | CustomerID, OrderDate |
1 – Sử dụng các công cụ thống kê CSDL
Tài nguyên quan trọng nhất để tối ưu hóa SQL là các công cụ thống kê được tổng hợp với các bảng khác nhau trong danh sách. Công cụ thống kê là các thông tin về indexes và phân bổ của nó. Bộ tối ưu dùng các thông tin này để quyết định đường dẫn có chi phí tối thiểu. Không cập nhật hoặc mất thông tin thống kê sẽ làm cho công cụ tối ưu hoạt động không hiệu quả gây ra tăng thời gian phản hồi.
Bảng dưới đây liệt kê các lệnh SQL cho các CSDL khác nhau được dùng để cập nhật thống kê. Để tìm hiểu chi tiết hơn, tham khảo các hướng dẫn của các RBDMS tương ứng.
| Oracle: | ANALYZE command or DBMS_UTILITY package |
| DB2: | RUNSTATS command |
| MS SQL Server: | UPDATE STA ISTICS |
| Informix: | UPDATE STATISTICS |
| Sybase ASE: | UPDATE STATISTICS |
Bộ tối ưu luôn luôn có xu hướng lựa chọn 1 đường dẫn với chi phí tối thiểu tức là trả về số lượng tối thiểu các dòng trong thời gian ngắn nhất. Tại sao bộ tối ưu lại dựa vào việc thống kê? Hãy xem câu truy vấn dưới đây.
| select * from customer where city = ‘New York City’ and phone = ‘212-555-1212’ |
Lưu ý là truy vấn trên bao gồm 2 trường trong mệnh đề “WHERE” và có 2 index được định nghĩa tương ứng với từng trường. Một lưu ý rất quan trọng là bộ tối ưu chỉ dùng 1 index trong 1 bảng.
Do đó, nó phải quyết định index nào sẽ được dùng. Từ khi số lượng điện thoại sẽ trả về với số lượng nhỏ nhất các dòng, truy vấn sẽ nhanh hơn nếu bộ tối ưu luôn sử dụng IdxPhone. Tuy nhiên, nếu thống kê không được cập nhật, bộ tối ưu không biết index nào tốt hơn và có thể quyết định sử dụng IdxCity của trường ‘city’ được hiển thị đầu tiên trong mệnh đề WHERE. Mỗi lần bạn cập nhật thống kê, CSDL sẽ biết nhiều hơn về phân bổ dữ liệu và sẽ lựa chọn đúng index cho truy vấn.
2 – Tạo các index tối ưu
Bộ tối ưu SQL phụ thuộc nhiều vào các index được định nghĩa cho bảng đặc thù. Index có 2 tác dụng: không index sẽ làm chậm tốc độ truy vấn SELECT, quá nhiều index sẽ làm chậm các truy vấn DML. Do đó, cần phải lựa chọn cân bằng các index. Bên cạnh số lượng index, các trường và thứ tự của chúng cũng rất quan trọng.
Khi tạo các index, ước lượng số lượng các giá trị duy nhất của cột sẽ có với từng trường riêng biệt. Ví dụ, idxCity index trong ví dụ không thực sự tốt để đánh index. Khi bạn muốn tìm kiếm khách hàng trong thành phố New York, nó có thể trả về hàng nghìn dòng, sau đó được tìm kiếm tuần tự. Các index này giúp tăng tốc truy vấn SELECT và giảm thời gian phản hồi cho câu lệnh DML.
Composite index – Các index bao gồm nhiều hơn 1 trường được gọi là composite index. Loại index này nên được tạo nếu bạn viết câu truy vấn trong đó có nhiều trường trong mệnh đề WHERE và tất cả các trường này sẽ cho kết quả ít dòng hơn so với dùng 1 trường.
Ví dụ, trong CSDL mẫu trên, index “IdxCustDate” là 1 composite index. Index này chỉ thích hợp nếu tỉ lệ giữa khách hàng và số lượng đặt hàng của họ là cao nghĩa là trung bình khách hàng đặt hàng nhiều hơn 1000 đơn hàng.
Clustered index – 1 clustered index xác định thứ tự vật lý của dữ liệu trong bảng – nghĩa là dữ liệu thực được sắp xếp dựa vào trường trong index. Điều này tương tự như thư mục điện thoại, trong đó sắp xếp dữ liệu bởi trường tên. Chỉ có duy nhất 1 clustered index trong 1 bảng. Những index này đặc biệt hiệu quả với cột trong đó thường được tìm kiếm cho 1 dải giá trị.
3 – Thận trọng trong việc sử dụng hàm trong câu truy vấn
Cán bộ phát triển thường dùng các hàm hoặc phương thức trong các truy vấn SQL của họ. Xem xét ví dụ dưới đây.
| select * from Customer where YEAR(AccountCreatedOn) = 2005 and MONTH(AccountCreatedOn) = 6 |
Lưu ý là mặc dù AccountCreatedOn có 1 index nhưng với câu truy vấn trên index không được sử dụng. Viết lại câu truy vấn trên theo cách bên dưới sẽ giúp tăng hiệu năng.
| Select * From Customer Where AccountCreatedOn between ‘6/1/2005’ and ‘6/30/2005’ |
4 – Xác định trước mức tăng trưởng dự kiến
Như đã nêu bên trên các index có ảnh hưởng tiêu cực đến các truy vấn DML. Một cách để giảm thiểu các ảnh hưởng này là xác định giá trị hợp lý cho fill factor khi tạo index.
Khi index được tạo, dữ liệu cho cột được index được lưu trong ổ cứng. Khi dòng dữ liệu mới được thêm vào bảng hoặc giá trị trong cột được đánh index thay đổi, CSDL sẽ tổ chức lại vùng lưu trữ của dữ liệu để dành không gian cho dòng mới. Việc tổ chức lại này có thể mất thêm chi phí cho truy vấn DML. Tuy nhiên, nếu bạn biết trước được mức tăng trưởng của dữ liệu, hãy định nghĩa trước mức tăng trưởng cho index.
| Oracle: | PCTFREE – Percent Free |
| DB2: | PCTFREE – Percent Free |
| MS SQL Server: | FILL FACTOR |
| Informix: | FILL FACTOR |
| Sybase ASE: | FILL FACTOR |
5- Chỉ định gợi ý tối ưu trong SELECT
Mặc dù trong phần lớn các trường hợp, bộ tối ưu sẽ lựa chọn index thích hợp cho bảng dựa trên các thống kê, nhưng sẽ tốt hơn nếu thỉnh thoảng chúng ta chỉ rõ tên index trong truy vấn SELECT. Xem xét ví dụ dưới đây
| SELECT * FROM customer WITH ( Index(IdxPhone)) WHERE city = ‘New York City’ and phone = ‘212-555-1212’ |
Chú ý mệnh đề “WITH” bổ sung sau FROM. Ví dụ này là sử dụng với MS SQL Server.
6 – Sử dụng EXPLAIN
Hầu hết các CSDL trả về sơ đồ thực thi cho tất cả các câu lệnh SELECT được tạo bởi bộ tối ưu. Sơ đồ này rất hữu ích trong việc tối ưu các câu truy vấn SQL. Bảng bên dưới liệt kê các cú pháp SQL với các CSDL khác nhau.
| Oracle: | EXPLAIN PLAN FOR >Your query< |
| DB2: | EXPLAIN PLAN SET queryno = xxx for >Your query< |
| MS SQL Server: | Set SHOWPLAN_ALL ON >Your query< |
| Informix: | SET EXPLAIN |
| Sybase ASE: | Set SHOWPLAN_ALL ON >Your query< |
Bạn cũng có thể dùng các công cụ của hãng thứ 3 như WinSQL Professional của Synametrics Technologies để chạy lệnh EXPLAIN.
7 – Tránh các ràng buộc khóa ngoại
Ràng buộc khóa ngoại đảm bảo toàn vẹn dữ liệu với chi phí hiệu năng nhất định. Do đó, nếu hiệu năng là yêu cầu chính đối với bạn thì có thể đưa các toàn vẹn dữ liệu lên lớp ứng dụng (tầng Application).
Ví dụ điển hình của việc thiết kế CSDL không dùng ràng buộc khóa ngoại là các table (system table) mà hệ thống CSDL tự sinh ra. Các bảng này chứa các thông tin meta về CSDL của người dùng. Mặc dù có các quan hệ giữa các bảng này xong hoàn toàn không có các khóa ngoại.
8 – Nhiều đầu đọc sẽ tốt hơn 1
I/O ổ cứng là phần tài nguyên chậm nhất của máy tính, là một yếu tố ảnh hưởng lớn đến CSDL. Nhiều CSDL cho phép người dùng chia CSDL của họ trên nhiều ổ cứng vật lý khác nhau. Thậm chí một số còn cho phép chia nội dung của 1 bảng trên nhiều ổ cứng. Khi sử dụng nhiều ổ cứng vật lý, hoạt động I/O sẽ tăng tốc độ truy vấn dữ liệu.
9 – Lựa chọn dữ liệu giới hạn
Càng lấy ít dữ liệu, truy vấn sẽ chạy càng nhanh hơn. Phương án tốt hơn lọc phía client là lọc nhiều nhất có thể phía server. Điều này sẽ giảm dữ liệu được gửi trên đường truyền và tốc độ sẽ nhanh hơn. Loại bỏ các cột rõ ràng hay được tính toán như ví dụ dưới đây.
| Select FirstName, LastName, City From Customer Where City = ‘New York City’ |
Trong ví dụ trên, bạn có thể dễ dàng loại bỏ cột “City”, là cột luôn có giá trị “New York City”. Mặc dù điều này dường như không có ảnh hưởng lớn, tuy nhiên nó sẽ đáng kể nếu tập kết quả là lớn.
10 – Xóa các index trước khi tải dữ liệu
Cân nhắc việc xóa các index trong bảng trước khi tải số lượng lớn các lô của dữ liệu. Việc này làm cho lệnh insert chạy nhanh hơn. Mỗi lần lệnh insert hoàn thành, bạn có thể tạo tại index. Nếu bạn đang insert hàng nghìn dòng trong 1 hệ thống trực tuyến, sử dụng bảng tạm để tải dữ liệu. Đảm bảo chắc chắn rằng bảng tạm này không có index.
Di chuyển dữ liệu từ bảng này sang bảng khác nhanh hơn nhiều so với tải dữ liệu từ nguồn bên ngoài, bạn có thể xóa index trong bảng chính, di chuyển dữ liệu từ bảng tạm tới bảng cuối và cuối cùng là tạo lại các index.
Nguồn: Internet
