Performance Tuning SQL là gì? Cách tối ưu truy vấn hiệu quả

Performance Tuning SQL (điều chỉnh hiệu suất truy vấn SQL) là một nhiệm vụ khó khăn và chưa có điểm dừng. Bài viết dưới đây chúng tôi sẽ giải thích rõ khái niệm Performance Tuning SQL và đưa ra một số giải pháp để cải thiện các truy vấn SQL cho Server của bạn.

Performance Tuning SQL là gì?

Để Server hoạt động với hiệu suất cao bạn cần cải thiện các truy vấn SQL, quá trình này người ta gọi là Performance Tuning SQL. Mục đích chung là để giảm bớt tài nguyên được dùng để xử lý truy vấn và giúp người dùng nhận được kết quả trong thời gian ngắn nhất sau khi thực hiện truy vấn. Tham khảo những thông tin sau bạn sẽ biết cách xác định lúc nào truy vấn của bạn cần cải thiện và làm thế nào để cải thiện chúng.

Thời gian chạy truy vấn

Là một phần mềm chạy trên máy tính vì thế cơ sở dữ liệu cũng chịu những hạn chế tương tự như những phần mềm khác. Một trong những hạn chế đó là cơ sở dữ liệu chỉ xử lý đủ lượng thông tin mà phần cứng của nó có thể xử lý.

Trường hợp bạn muốn một truy vấn chạy nhanh hơn thì bắt buộc phải giảm số lượng phép tính mà cơ sở dữ liệu cần thực hiện. Để thực hiện được điều này bạn hãy tìm hiểu về những yếu tố ảnh hưởng đến số lượng phép tính và thời gian chạy truy vấn:

• Kích thước bảng: Sẽ mất rất nhiều thời gian để chạy truy vấn khi truy vấn của bạn truy cập vào các bảng có hàng triệu hàng.
• Joins (Kết hợp): Truy vấn của bạn sẽ bị chậm khi truy vấn kết hợp hai bảng vì lúc này số hàng tăng lên đáng kể.
• Tổng hợp: Chỉ truy xuất các hàng thì hiệu suất truy vấn nhanh, ngược lại nếu kết hợp nhiều hàng để tạo ra một kết quả thì bắt buộc phải tính toán lâu hơn.

Ngoài những yếu tố kể ở trên thì một số thứ liên quan đến cơ sở dữ liệu mà bạn không kiểm soát được cũng tác động đến thời gian chạy truy vấn, cụ thể:

• Người dùng khác đang chạy truy vấn: Cơ sở dữ liệu sẽ chạy chậm hơn và ngốn nhiều tài nguyên khi cùng một thời điểm có nhiều truy vấn đang được thực hiện.
• Phần mềm và tối ưu hóa cơ sở dữ liệu: Bạn không thể kiểm soát điều này tuy nhiên truy vấn của bạn vẫn chạy nhanh nếu bạn làm việc trong giới hạn cho phép.

Hướng dẫn một số cách để điều chỉnh hiệu suất truy vấn SQL

Bạn hãy bỏ qua những thứ mà mình không thể kiểm soát để tìm cách điều chỉnh hiệu suất SQL. Dưới đây là một số kinh nghiệm hay chúng tôi đã thực hiện và mang lại hiệu quả cao mà bạn nên thử:

Giảm kích thước bảng

Tốc độ truy vấn sẽ được cải thiện đáng kể khi bạn lọc dữ liệu tới mức tinh gọn chỉ gồm những quan sát mà bạn cần. Tùy thuộc vào từng vấn đề bạn cần giải quyết để có cách thức thực hiện phù hợp. Ví dụ với dữ liệu chuỗi thời gian bạn sẽ làm cho truy vấn chạy nhanh hơn khi giới hạn ở một khoảng thời gian ngắn.

{{EJS0}}

Bạn hoàn toàn có thể tinh chỉnh công việc của mình thành truy vấn cuối cùng trên một tập hợp con dữ liệu. Sau khi hoàn thành hãy loại bỏ giới hạn và tiến hành chạy công việc đó trên toàn bộ dữ liệu. Mặc dù thời gian để chạy truy vấn cuối cùng hơi lâu nhưng các bước trung gian lại diễn ra trong tích tắc.

Đó cũng chính là lý do mà lệnh LIMIT thường được dùng trong SQL. Thông thường sẽ mặc định LIMIT – 100 hàng đầu tiên của truy vấn vì tập hợp dữ liệu này đủ nhỏ để kết quả trả về nhanh chóng.

Tuy nhiên LIMIT – 100 sẽ không thực hiện bất cứ hành động nào để tăng tốc độ truy vấn cho bạn khi bạn tổng hợp thành một hàng như ví dụ bên dưới:

{{EJS1}}

LIMIT – 100 sẽ trả về kết quả bạn mong muốn và tăng tốc độ truy vấn khi bạn thực hiện nó trong một truy vấn con:

{{EJS2}}

Tóm lại khi làm việc với các truy vấn con bạn cần chứa LIMIT truy vấn con vào chứ không phải truy vấn bên ngoài. Mặt khác việc dùng lệnh LIMIT sẽ giúp truy vấn chạy nhanh hơn nhưng để tạo một kết quả tốt thì chưa thực sự hoàn hảo.

Giảm độ phức tạp khi joins các bảng

Ngoài giảm kích thước bảng thì bạn nên giảm dữ liệu trong truy vấn tại một thời điểm trước khi kết hợp các bảng với nhau. Làm được như thế thì hiệu suất truy vấn sẽ được cải thiện rõ rệt. Kết hợp thông tin các đội thể thao đại học với danh sách cầu thủ của những trường cao đẳng ở ví dụ sau sẽ giúp bạn hiểu rõ điều đó:

{{EJS3}}

Trong benn.college_football_players players có 26.298 hàng cần được đánh giá. Tuy nhiên bạn sẽ giảm được số hàng nếu benn.college_football_players đã tổng hợp các bảng trước đó.

{{EJS4}}

Kết quả trả về cho truy vấn trên là 252. Nếu muốn giảm chi phí của phép nối bạn hãy bỏ tổng hợp trên vào truy vấn con và kết hợp với nó trong truy vấn ngoài.

{{EJS5}}

Với 30.000 hàng thì cơ sở dữ liệu xử lý khá đơn giản nên bạn sẽ khó thấy được sự khác biệt. Thế nhưng trong trường hợp hàng trăm hàng nghìn trở lên thì vấn đề tổng hợp trước sẽ giúp truy vấn chạy nhanh đáng kể.

EXPLAIN

Bạn hãy thử thêm EXPLAIN vào trước bất cứ một truy vấn nào đang hoạt động để xem kế hoạch truy vấn. Có thể mức độ chính xác chưa chưa cao nhưng đây thực sự là một công cụ có nhiều ưu điểm:

{{EJS6}}

Sau khi thực hiện thao tác trên kế hoạch truy vấn sẽ xuất hiện. Kế hoạch đó hiển thị rõ ràng thứ tự mà truy vấn của bạn được thực thi:

Truy vấn đầu tiên sẽ dành cho mục nhập ở cuối danh sách. Đối chiếu với phần phía trên bạn sẽ thấy WHERE event_date sẽ được thực hiện đầu tiên. Tiếp theo 600 hàng sẽ được cơ sở dữ liệu quét. Bên cạnh số hàng có liệt kê rõ chi phí, số lượng hàng càng cao thì thời gian để thực hiện truy vấn càng lâu.

Tổng kết về Performance Tuning SQL