Tối ưu hóa các truy vấn SQL: Một ví dụ sử dụng MSSQL

Các truy vấn SQL của Microsoft SQL Server (MSSQL) không được tối ưu hóa có thể dẫn đến hiệu suất dưới trung bình, sử dụng quá mức tài nguyên, dữ liệu không nhất quán, lỗi bảo mật và khó bảo trì. Những sự cố này có thể ảnh hưởng đến chức năng, độ tin cậy và bảo mật của chương trình, điều này có thể khiến người dùng khó chịu và khiến bạn tốn nhiều tiền hơn.

Tối ưu hóa các truy vấn SQL trong MSSQL là rất quan trọng để đạt được hiệu suất nhanh và hiệu quả. Bằng cách triển khai các kỹ thuật như lập chỉ mục, đơn giản hóa truy vấn và thủ tục lưu trữ, người dùng có thể cân bằng hiệu suất truy vấn và hiệu suất sửa đổi dữ liệu, cuối cùng là cải thiện hiệu suất cơ sở dữ liệu tổng thể.

Tối ưu hóa truy vấn thông qua lập chỉ mục

Lập chỉ mục cơ sở dữ liệu tổ chức và sắp xếp dữ liệu trong các bảng cơ sở dữ liệu để giúp tìm kiếm nhanh hơn và hiệu quả hơn. Lập chỉ mục tạo các bản sao dữ liệu trong bảng và sắp xếp chúng để công cụ cơ sở dữ liệu có thể điều hướng chúng dễ dàng.

Khi thực hiện truy vấn, công cụ cơ sở dữ liệu sử dụng chỉ mục để tìm dữ liệu cần thiết và nhanh chóng trả về kết quả, do đó tăng tốc thời gian thực hiện truy vấn. Nếu không lập chỉ mục, công cụ cơ sở dữ liệu phải quét tất cả các hàng trong bảng để tìm dữ liệu cần thiết và điều này có thể tốn nhiều thời gian và tài nguyên, đặc biệt đối với các bảng lớn.

Tạo một chỉ mục trong MSSQL

Tạo một chỉ mục trong cơ sở dữ liệu quan hệ rất dễ dàng và MSSQL không bị bỏ qua. Bạn có thể dùng TẠO CHỈ SỐ câu lệnh để tạo một chỉ mục trong MSSQL.

 CREATE INDEX index_name
ON table_name (column1, column2, ...);
 

Trong mã SQL ở trên, tên chỉ mục là tên của chỉ mục, tên_bảng là tên của bảng, và cột1, cột2v.v. là tên của các cột sẽ được lập chỉ mục.

Đây là cách tạo một chỉ mục không được nhóm trên một khách hàng những cái bàn Họ cột với một câu lệnh SQL.

 CREATE NONCLUSTERED INDEX IX_Customers_LastName
ON Customers (LastName);
 

Câu lệnh tạo một chỉ mục không được nhóm gọi là IX_Customers_LastName trên Họ cột của khách hàng bàn.

Chi phí lập chỉ mục

Mặc dù việc lập chỉ mục có thể cải thiện đáng kể hiệu suất truy vấn nhưng nó cũng phải trả giá. Lập chỉ mục yêu cầu không gian đĩa bổ sung để lưu trữ chỉ mục và hoạt động lập chỉ mục có thể làm chậm các hoạt động sửa đổi dữ liệu chẳng hạn như chèn, cập nhật và xóa. Bạn phải cập nhật chỉ mục khi sửa đổi dữ liệu và thao tác cập nhật có thể tốn nhiều thời gian đối với các bảng lớn.

Do đó, điều cần thiết là phải cân bằng hiệu suất truy vấn và hiệu suất sửa đổi dữ liệu. Bạn chỉ nên tạo chỉ mục trên các cột mà bạn sẽ tìm kiếm thường xuyên. Điều cần thiết là phải thường xuyên theo dõi việc sử dụng tài nguyên chỉ mục và loại bỏ các chỉ mục không cần thiết.

Tối ưu hóa truy vấn thông qua đơn giản hóa truy vấn

Để phân tích dữ liệu, các truy vấn phức tạp có ích cho việc trích xuất dữ liệu. Tuy nhiên, các truy vấn phức tạp có ảnh hưởng đến hiệu suất và chúng có thể dẫn đến việc trích xuất dữ liệu không hiệu quả.

Đơn giản hóa các truy vấn liên quan đến việc chia nhỏ các truy vấn phức tạp thành các truy vấn nhỏ hơn, đơn giản hơn để xử lý nhanh hơn, ít tốn tài nguyên hơn.

Đơn giản hóa truy vấn cải thiện hiệu suất và giúp việc trích xuất dữ liệu dễ dàng hơn bằng cách chia các truy vấn phức tạp thành các truy vấn đơn giản hơn vì các truy vấn phức tạp có thể gây ra tắc nghẽn trong hệ thống. Chúng khó hiểu, khiến các nhà phát triển và nhà phân tích gặp khó khăn hơn trong việc khắc phục sự cố hoặc xác định các khu vực cần tối ưu hóa.

Đây là một ví dụ về truy vấn phức tạp chạy trên MSSQL cho một bảng đơn đặt hàng của khách hàng với mục tiêu là xác định các xu hướng và mẫu trong dữ liệu:

 SELECT
  customer_name,
  COUNT(order_id) AS total_orders,
  AVG(order_amount) AS average_order_amount,
  SUM(order_amount) AS total_sales
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status="completed"
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5
ORDER BY
  total_sales DESC;
 

Truy vấn tìm kiếm tên khách hàng và thông tin bán hàng từ các đơn đặt hàng đã hoàn thành vào năm 2022 trong khi lọc những khách hàng có ít hơn năm đơn đặt hàng theo tổng doanh số bán hàng theo thứ tự giảm dần.

Truy vấn có thể cung cấp những hiểu biết có giá trị, nhưng nó phức tạp và cuối cùng sẽ mất nhiều thời gian hơn để xử lý, đặc biệt nếu mệnh lệnh bảng có nhiều mục.

Bạn có thể đơn giản hóa truy vấn bằng cách chia nhỏ truy vấn thành các truy vấn nhỏ hơn để thực hiện từng truy vấn một.

 
SELECT
  customer_name,
  COUNT(order_id) AS total_orders
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status="completed"
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5;

SELECT
  customer_name,
  AVG(order_amount) AS average_order_amount
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status="completed"
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5;

SELECT
  customer_name,
  SUM(order_amount) AS total_sales
FROM
  orders
WHERE
  order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND order_status="completed"
GROUP BY
  customer_name
HAVING
  COUNT(order_id) > 5
ORDER BY
  total_sales DESC;
 

Cách tiếp cận đơn giản hóa này tách nhiệm vụ lấy tên khách hàng và tổng số đơn đặt hàng, số lượng đơn đặt hàng trung bình và tổng doanh số bán hàng cho từng khách hàng thành các truy vấn riêng lẻ. Mỗi truy vấn có các mục đích xác định và được tối ưu hóa cho các tác vụ cụ thể, giúp cơ sở dữ liệu dễ dàng xử lý theo yêu cầu hơn.

Mẹo đơn giản hóa truy vấn

Khi bạn đang đơn giản hóa các truy vấn, điều cần thiết là tập trung vào một tác vụ mỗi lần để tạo các truy vấn được tối ưu hóa cho tác vụ cụ thể. Tập trung vào một nhiệm vụ có thể giúp cải thiện hiệu suất đáng kể.

Ngoài ra, điều quan trọng là sử dụng các quy ước đặt tên tốt để làm cho mã dễ hiểu và dễ bảo trì hơn. Bạn có thể dễ dàng xác định các vấn đề tiềm ẩn và các lĩnh vực cần cải thiện trong hệ thống.

Tối ưu hóa truy vấn thông qua các thủ tục được lưu trữ

Các thủ tục được lưu trữ là tập hợp các câu lệnh SQL được viết sẵn được lưu trữ trong cơ sở dữ liệu. Bạn có thể sử dụng thủ tục lưu trữ để thực hiện các hoạt động khác nhau, từ cập nhật đến chèn hoặc truy xuất dữ liệu từ cơ sở dữ liệu. Các thủ tục được lưu trữ có thể chấp nhận các tham số. Bạn có thể gọi chúng từ các ngôn ngữ lập trình khác nhau, biến chúng thành một công cụ mạnh mẽ để phát triển ứng dụng.

Đây là một ví dụ về việc tạo một thủ tục được lưu trữ cho MSSQL để trả về mức lương trung bình của nhân viên trong một bộ phận:

 CREATE PROCEDURE [dbo].[GetAverageSalary]
@DepartmentName VARCHAR(50)
AS
BEGIN
SELECT AVG(Salary) as AverageSalary
FROM Employees
WHERE Department = @DepartmentName
END
 

Trong thủ tục được lưu trữ, bạn đã xác định một tham số được gọi là @Tên bộ phận và đã sử dụng Ở ĐÂU mệnh đề lọc kết quả theo bộ phận. Bạn cũng đã sử dụng AVG chức năng tính lương trung bình của nhân viên trong bộ phận.

Bạn có thể thực thi các thủ tục được lưu trữ trong MSSQL với điều hành tuyên bố.

Đây là cách bạn có thể thực hiện thủ tục được lưu trữ ở trên:

 EXEC [dbo].[GetAverageSalary] @DepartmentName="Sales"
 

Trong câu lệnh thực thi thủ tục được lưu trữ, bạn chuyển Việc bán hàng giá trị như tên bộ phận. Câu lệnh sẽ trả về mức lương trung bình của nhân viên trong bộ phận bán hàng.

Thủ tục được lưu trữ cải thiện hiệu suất truy vấn như thế nào?

Các thủ tục được lưu trữ có thể cải thiện đáng kể hiệu suất truy vấn. Đầu tiên, các thủ tục được lưu trữ làm giảm lưu lượng mạng bằng cách thực thi các câu lệnh SQL ở phía máy chủ thay vì truyền dữ liệu qua lại giữa máy khách và máy chủ, do đó giảm lượng dữ liệu được gửi qua mạng và giảm thời gian phản hồi truy vấn.

Thứ hai, bạn có thể biên dịch các thủ tục lưu trữ vào bộ nhớ đệm, nghĩa là lưu trữ kế hoạch thực hiện trong bộ nhớ. Khi bạn gọi thủ tục được lưu trữ, máy chủ sẽ truy xuất kế hoạch thực hiện từ bộ nhớ thay vì biên dịch lại các câu lệnh SQL, do đó giảm thời gian thực hiện cho thủ tục được lưu trữ và cải thiện hiệu suất truy vấn.

Bạn có thể thiết lập MSSQL trên Ubuntu

MSSQL đã có những bước tiến đáng kể trong việc hỗ trợ Ubuntu và các bản phân phối Linux khác. Microsoft đã nhận ra sự phổ biến ngày càng tăng của Linux trong doanh nghiệp và quyết định mở rộng tính khả dụng của máy chủ cơ sở dữ liệu hàng đầu của họ sang các nền tảng Linux.