Tìm hiểu về kế hoạch truy vấn

  • 11 phút

Hiểu rõ cách hoạt động của trình tối ưu hóa cơ sở dữ liệu là điều cần thiết trước khi tìm hiểu chi tiết về kế hoạch thực thi. SQL Server sử dụng trình tối ưu hóa truy vấn dựa trên chi phí, tính toán chi phí cho nhiều kế hoạch khả thi dựa trên số liệu thống kê mà nó có về các cột đang được sử dụng và các chỉ mục tiềm năng cho mỗi thao tác trong kế hoạch truy vấn. Thông tin này giúp trình tối ưu hóa xác định tổng chi phí cho mỗi kế hoạch. Truy vấn phức tạp có thể có hàng ngàn kế hoạch thực thi khả thi, nhưng trình tối ưu hóa không đánh giá từng kế hoạch duy nhất. Thay vào đó, nó sử dụng heuristics để xác định các kế hoạch có khả năng thực hiện tốt và sau đó chọn kế hoạch chi phí thấp nhất từ những người được đánh giá.

Vì trình tối ưu hóa truy vấn dựa trên chi phí, điều quan trọng là phải cung cấp dữ liệu đầu vào chính xác để đưa ra quyết định. SQL Server dựa vào số liệu thống kê để theo dõi việc phân phối dữ liệu trong các cột và chỉ mục và các số liệu thống kê này phải được cập nhật để tránh tạo kế hoạch thực thi tối đa phụ. Mặc dù SQL Server tự động cập nhật số liệu thống kê khi dữ liệu thay đổi trong bảng, nhưng có thể cần cập nhật thường xuyên hơn để thay đổi dữ liệu nhanh chóng. Trình tối ưu hóa xem xét nhiều yếu tố khi xây dựng kế hoạch, bao gồm mức độ tương thích của cơ sở dữ liệu, ước tính hàng dựa trên thống kê và chỉ mục sẵn có.

Khi người dùng gửi truy vấn đến công cụ cơ sở dữ liệu, quy trình sau đây sẽ xảy ra:

  1. Truy vấn được phân tích cú pháp thích hợp và nếu đúng, cây phân tích cú pháp của đối tượng cơ sở dữ liệu sẽ được tạo.
  2. Cây phân tích cú pháp sau đó được nhập vào một cấu phần công cụ cơ sở dữ liệu được gọi là Algebrizer để liên kết. Bước này xác thực rằng các cột và đối tượng trong truy vấn tồn tại và xác định các kiểu dữ liệu đang được xử lý. Đầu ra là một cây bộ xử lý truy vấn, dùng làm đầu vào cho bước tiếp theo.
  3. Tối ưu hóa truy vấn tốn nhiều CPU, vì vậy bộ máy cơ sở dữ liệu lưu trữ kế hoạch thực thi trong vùng bộ nhớ đặc biệt được gọi là bộ đệm ẩn kế hoạch. Nếu một kế hoạch cho truy vấn đã tồn tại, nó được truy xuất từ bộ đệm ẩn. Mỗi truy vấn trong bộ đệm ẩn có một giá trị băm được tạo dựa trên T-SQL trong truy vấn, được gọi là giá query_hash. Công cụ tạo query_hash cho truy vấn hiện tại và kiểm tra kết quả khớp trong bộ đệm ẩn kế hoạch.
  4. Nếu không tồn tại kế hoạch nào, Trình tối ưu hóa Truy vấn sẽ sử dụng trình tối ưu hóa dựa trên chi phí để tạo ra một vài tùy chọn kế hoạch thực thi dựa trên số liệu thống kê về các cột, bảng và chỉ mục được sử dụng trong truy vấn. Đầu ra là kế hoạch thực thi truy vấn.
  5. Truy vấn được thực hiện bằng kế hoạch thực thi từ bộ đệm ẩn kế hoạch hoặc kế hoạch mới được tạo ở bước trước đó. Đầu ra là kết quả truy vấn của bạn.

Ghi

Để tìm hiểu thêm về cách hoạt động của bộ xử lý truy vấn, hãy xem Hướng dẫn kiến trúc xử lý truy vấn

Hãy xem xét một ví dụ. Hãy cân nhắc truy vấn sau đây:

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

Trong ví dụ này, SQL Server kiểm tra sự tồn tại của cột OrderDate, ShipDate và SalesAmount trong bảng FactResellerSales . Nếu các cột này tồn tại, SQL Server tạo ra một giá trị băm cho truy vấn và kiểm tra bộ đệm ẩn kế hoạch cho một giá trị băm phù hợp. Nếu một giá trị băm phù hợp được tìm thấy, công cụ cố gắng để tái sử dụng kế hoạch. Nếu không tìm thấy giá trị băm phù hợp, SQL Server kiểm tra số liệu thống kê có sẵn trên cột OrderDatevà ShipDate .

Mệnh WHERE đề tham chiếu đến cột Ngày Vận chuyển được gọi là vị từ trong truy vấn này. Nếu có chỉ mục không bao gồm cột Ngày Vận chuyển, SQL Server có thể sẽ đưa chỉ mục đó vào kế hoạch, miễn là chi phí thấp hơn truy xuất dữ liệu từ chỉ mục liên cụm. Sau đó, trình tối ưu hóa sẽ chọn gói chi phí thấp nhất từ các tùy chọn sẵn có và thực thi truy vấn.

Kế hoạch truy vấn kết hợp một chuỗi các toán tử quan hệ để truy xuất dữ liệu và thu thập thông tin như số lượng hàng ước tính. Một yếu tố khác của kế hoạch thực thi là bộ nhớ cần thiết cho các hoạt động như tham gia hoặc sắp xếp dữ liệu, được gọi là cấp bộ nhớ. Việc cấp bộ nhớ làm nổi bật tầm quan trọng của số liệu thống kê. Nếu SQL Server ước tính một toán tử trả về 10.000.000 hàng khi nó thực sự trả về 100, một cấp bộ nhớ lớn hơn được phân bổ cho truy vấn. Cấp bộ nhớ quá lớn có thể gây ra hai vấn đề. Trước tiên, truy vấn có thể gặp RESOURCE_SEMAPHORE phải một chờ đợi, cho biết nó đang chờ SQL Server để phân bổ một lượng lớn bộ nhớ. SQL Server mặc định chờ 25 lần chi phí của truy vấn (tính theo giây) trước khi thực hiện, tối đa 24 giờ. Thứ hai, nếu không có đủ bộ nhớ sẵn dùng khi truy vấn được thực hiện, nó tràn đến tempdb, chậm hơn so với hoạt động trong bộ nhớ.

Kế hoạch thực thi cũng lưu trữ siêu dữ liệu khác về truy vấn, chẳng hạn như mức độ tương thích cơ sở dữ liệu, mức độ song song và các tham số được cung cấp nếu truy vấn được tham số hóa.

Có thể xem kế hoạch truy vấn ở dạng biểu thị đồ họa hoặc định dạng dựa trên văn bản. Các tùy chọn dựa trên văn bản được gọi bằng các lệnh SET và chỉ áp dụng cho kết nối hiện tại. Bạn có thể xem các gói này ở bất cứ đâu bạn có thể chạy truy vấn T-SQL.

Hầu hết các CSDL thích kế hoạch đồ họa vì chúng cho phép bạn xem toàn bộ kế hoạch, bao gồm hình dạng của kế hoạch. Có một số cách để xem và lưu kế hoạch truy vấn đồ họa. Công cụ phổ biến nhất cho mục đích này là SQL Server Management Studio. Ngoài ra, có các công cụ của bên thứ ba hỗ trợ xem kế hoạch thực hiện đồ họa.

Có ba loại kế hoạch thực thi khác nhau.

Kế hoạch Thực hiện Ước tính

Kiểu kế hoạch thực thi này được tạo bởi trình tối ưu hóa truy vấn. Siêu dữ liệu và kích cỡ của việc cấp bộ nhớ truy vấn dựa trên ước tính từ số liệu thống kê có trong cơ sở dữ liệu tại thời điểm biên soạn truy vấn. Để xem kế hoạch ước tính dựa trên văn bản, hãy chạy lệnh SET SHOWPLAN_ALL ON trước khi thực hiện truy vấn. Khi chạy truy vấn, bạn sẽ thấy các bước của kế hoạch thực thi nhưng truy vấn sẽ không được thực hiện và bạn sẽ không thấy bất kỳ kết quả nào. Tùy chọn SET vẫn có hiệu lực cho đến khi bạn đặt tùy chọn TẮT.

Kế hoạch Thực hiện Thực tế

Loại kế hoạch này giống như kế hoạch dự kiến; tuy nhiên, nó cũng bao gồm ngữ cảnh thực thi cho truy vấn. Ngữ cảnh này chứa số lượng hàng ước tính và thực tế, bất kỳ cảnh báo thực hiện nào, mức độ song song thực tế (số lượng bộ xử lý được sử dụng) và thời gian CPU và đã qua được sử dụng trong khi thực thi. Để xem kế hoạch thực tế dựa trên văn bản, hãy chạy lệnh SET STATISTICS PROFILE ON trước khi thực hiện truy vấn. Truy vấn thực hiện và bạn nhận được cả kế hoạch và kết quả.

Thống kê Truy vấn Trực tiếp

Tùy chọn xem kế hoạch này kết hợp kế hoạch ước tính và thực tế vào một kế hoạch hoạt hình hiển thị tiến độ thực hiện thông qua các toán tử. Nó làm mới mỗi giây và hiển thị số hàng thực tế chạy qua các toán tử. Một lợi ích khác của Thống kê Truy vấn Trực tiếp là nó hiển thị bản phân phát từ toán tử này sang toán tử khác, điều này có thể hữu ích trong việc khắc phục các vấn đề về hiệu năng. Vì kiểu kế hoạch này được tạo hiệu ứng hoạt hình nên nó chỉ sẵn dùng dưới dạng kế hoạch đồ họa.