Là quản trị viên cơ sở dữ liệu, chúng ta cần phải bị ám ảnh bởi các công cụ và phương pháp nâng cao hiệu suất cơ sở dữ liệu.
Trong PostgreSQL, chúng tôi có quyền truy cập vào lệnh EXPLAIN ANALYZE cho phép chúng tôi phân tích kế hoạch thực hiện và hiệu suất của một truy vấn cơ sở dữ liệu nhất định. Lệnh trả về thông tin chi tiết về cách cơ sở dữ liệu xử lý truy vấn. Điều này bao gồm chuỗi các hoạt động được thực hiện, chi phí truy vấn ước tính, thời gian thực hiện, v.v.
Sau đó, chúng tôi có thể sử dụng thông tin này để xác định các truy vấn cơ sở dữ liệu cũng như xác định và khắc phục các tắc nghẽn hiệu suất tiềm ẩn.
Hướng dẫn này thảo luận cách sử dụng lệnh EXPLAIN ANALYZE trong PostgreSQL để xem và tối ưu hóa hiệu suất truy vấn.
PHÂN TÍCH GIẢI THÍCH PostgreSQL
Lệnh này khá đơn giản. Trước tiên, chúng ta cần thêm lệnh EXPLAIN ANALYZE vào đầu truy vấn mà chúng ta muốn phân tích.
Cú pháp lệnh như sau:
GIẢI THÍCH PHÂN TÍCHSau khi bạn thực thi lệnh, PostgreSQL sẽ trả về kết quả chi tiết về truy vấn được cung cấp.
Hiểu đầu ra truy vấn EXPLAIN ANALYZE
Như đã đề cập, khi chúng ta chạy lệnh EXPLAIN ANALYZE, PostgreSQL sẽ tạo một báo cáo chi tiết về kế hoạch truy vấn và thống kê thực thi.
Đầu ra bao gồm một tập hợp các cột chứa thông tin hữu ích. Các cột kết quả như được hiển thị với ý nghĩa tương ứng của chúng:
KẾ HOẠCH HỎI – Cột này hiển thị kế hoạch thực hiện của truy vấn đã chỉ định. Kế hoạch thực hiện đề cập đến một chuỗi các hoạt động mà công cụ cơ sở dữ liệu thực hiện để hoàn thành truy vấn thành công.
KẾ HOẠCH – Cột thứ hai là cột KẾ HOẠCH. Điều này chứa một đại diện văn bản của từng hoạt động hoặc bước trong kế hoạch thực hiện. Một lần nữa, mỗi thao tác được thụt vào để biểu thị thứ bậc của các thao tác.
TỔNG CHI PHÍ – Cột tổng chi phí biểu thị tổng chi phí ước tính của truy vấn. Chi phí đề cập đến một biện pháp tương đối mà người lập kế hoạch truy vấn cơ sở dữ liệu sử dụng để xác định kế hoạch thực hiện tối ưu.
HÀNG THỰC TẾ – Cột này hiển thị số hàng chính xác được xử lý ở mỗi bước trong quá trình thực thi truy vấn.
THỜI GIAN THỰC TẾ – Cột này hiển thị thời gian thực tế được thực hiện bởi từng thao tác, bao gồm cả thời gian thực hiện thao tác và thời gian sử dụng tài nguyên.
KẾ HOẠCH THỜI GIAN – Cột này hiển thị thời gian mà công cụ lập kế hoạch truy vấn cần để tạo kế hoạch thực hiện. Điều này bao gồm tổng thời gian tối ưu hóa truy vấn và tạo kế hoạch.
THỜI GIAN THỰC HIỆN – Cột này hiển thị tổng thời gian để thực hiện truy vấn. Điều này cũng bao gồm thời gian lập kế hoạch và thời gian thực hiện truy vấn.
PostgreSQL GIẢI THÍCH PHÂN TÍCH Ví dụ
Chúng ta hãy xem xét một số ví dụ cơ bản về việc sử dụng câu lệnh EXPLAIN ANALYZE.
Ví dụ 1: Chọn Tuyên bố
Hãy để chúng tôi sử dụng câu lệnh EXPLAIN ANALYZE để hiển thị việc thực thi một câu lệnh chọn đơn giản trong PostgreSQL.
Khi chúng ta chạy câu lệnh trước đó, chúng ta sẽ nhận được kết quả như sau:
KẾ HOẠCH HỎI-------------------------------------------------- -----------------
Quét tuần tự trên wp_users (chi phí=0,00..10,38 hàng=10 chiều rộng=2256) (thời gian thực tế=0,009..0,010 hàng=7 vòng=1)
Bộ lọc: (id > 3)
Hàng bị Bộ lọc loại bỏ: 3
Thời gian lập kế hoạch: 0,995 ms
Thời gian thực hiện: 0,021 mili giây
(5 hàng)
Trong trường hợp này, chúng ta có thể thấy phần Kế hoạch truy vấn chỉ ra rằng truy vấn thực hiện quét tuần tự trên bảng wp_users. Dòng bộ lọc biểu thị điều kiện được sử dụng để lọc các hàng kết quả.
Sau đó, chúng tôi thấy “Hàng bị bộ lọc loại bỏ” hiển thị số lượng hàng bị loại bỏ bởi điều kiện bộ lọc.
Cuối cùng, thời gian thực hiện hiển thị tổng thời gian thực hiện của truy vấn. Trong trường hợp này, truy vấn mất 0,021 mili giây.
Ví dụ 2: Phân tích một liên kết
Hãy để chúng tôi thực hiện một truy vấn phức tạp hơn liên quan đến phép nối SQL. Đối với điều này, chúng tôi sử dụng cơ sở dữ liệu mẫu Pagila. Bạn có thể tải xuống và cài đặt cơ sở dữ liệu mẫu trên máy của mình cho mục đích trình diễn.
Chúng ta có thể chạy một phép nối đơn giản như sau:
giải thích phân tích CHỌN f.title, c.nameTỪ phim f
THAM GIA film_category fc BẬT f.film_id = fc.film_id
THAM GIA danh mục c BẬT fc.category_id = c.category_id;
Khi chúng tôi chạy truy vấn đã cho, chúng tôi sẽ thấy đầu ra như sau:
Hãy để chúng tôi khám phá kế hoạch truy vấn sau:
- Vòng lặp lồng nhau – Điều này cho biết rằng phép nối sử dụng chiến lược nối vòng lặp lồng nhau.
- Tham gia băm – Thao tác này tham gia film_category và các bảng phim bằng cách sử dụng thuật toán tham gia Hash. Thao tác này có chi phí là 77,50 và ước tính 1000 hàng. Tuy nhiên, thời gian thực tế cho thao tác này là 0,254 đến 0,439 mili giây và nó truy xuất 1000 hàng.
- Hash Cond – Điều này cho biết rằng điều kiện nối sử dụng phép nối Hash để khớp với cột film_id và cột film_category trong bảng phim.
- Quét tuần tự trên film_category – Thao tác này thực hiện quét tuần tự trên bảng film_category với chi phí là 16,00 và ước tính 1000 hàng. Thời gian thực tế cho thao tác này là 0,008 đến 0,056 mili giây và nó truy xuất 1000 hàng.
- Quét tuần tự trên phim – Truy vấn thực hiện quét tuần tự trên bảng phim với các hàng và chi phí ước tính và thực tế thu được trong thao tác này.
- Ghi nhớ – Thao tác này lưu trữ kết quả của phép nối giữa film_category và bảng phim để sử dụng sau này.
- Khóa bộ đệm – Điều này cho biết rằng khóa bộ đệm được sử dụng để ghi nhớ dựa trên cột category_id từ film_category.
- Chế độ bộ đệm ẩn – Điều này cho biết rằng truy vấn sử dụng chế độ bộ đệm logic.
- Số lần truy cập, Số lần bỏ lỡ, Số lần trục xuất, Số lần tràn – Ba dòng cung cấp số liệu thống kê về bộ đệm, số lần truy cập, số lần bỏ lỡ, số lần trục xuất và số lần tràn trong quá trình thực thi. Khối này cũng bao gồm việc sử dụng bộ nhớ trong khi thực hiện truy vấn.
- Quét chỉ mục bằng category_pkey – Điều này cho thấy hoạt động thực hiện quét chỉ mục trên bảng danh mục bằng cách sử dụng chỉ mục khóa chính.
- Chỉ mục Cond – Điều này cho thấy rằng việc quét chỉ mục dựa trên điều kiện phù hợp với cột category_id trong bảng danh mục.
- Thời gian lập kế hoạch – Dòng này hiển thị thời gian dành cho việc lập kế hoạch truy vấn là 3,005 mili giây.
- Thời gian thực hiện – Cuối cùng, dòng này hiển thị tổng thời gian thực hiện của truy vấn là 0,745 mili giây.
Ở đó bạn có nó! Thông tin chi tiết về việc thực hiện một phép nối đơn giản trong PostgreSQL.
Phần kết luận
Bạn đã khám phá ra sức mạnh và cách sử dụng câu lệnh EXPLAIN ANALYZE trong PostgreSQL. Câu lệnh EXPLAIN ANALYZE là một công cụ mạnh mẽ để phân tích và tối ưu hóa truy vấn. Sử dụng công cụ này để xây dựng các truy vấn hiệu quả và ít tốn tài nguyên hơn.