Vòng đời truy vấn và bộ lập kế hoạch
Cải thiện hiệu năng truy vấn trong PostgreSQL
Amy McCarty
Instructor
Vòng đời truy vấn cơ bản
| Hệ thống | Bước phía trước | Quy trình phía sau | |
|---|---|---|---|
| 1 | Parser | Gửi truy vấn đến CSDL | Kiểm tra cú pháp. Dịch SQL sang cú pháp thân thiện với máy dựa trên quy tắc hệ thống. |
| 2 | Planner & Optimizer | Đánh giá và tối ưu tác vụ truy vấn | Dùng thống kê để tạo kế hoạch. Tính cost và chọn kế hoạch tốt nhất. |
| 3 | Executor | Trả về kết quả | Thực thi theo kế hoạch truy vấn. |
Bộ lập kế hoạch và tối ưu truy vấn
Thích ứng với thay đổi cấu trúc SQL
- Tạo cây kế hoạch
- Nút tương ứng các bước
- Hình dung bằng EXPLAIN
- Ước lượng cost cho mỗi cây
- Thống kê từ pg_tables
- Tối ưu hóa theo thời gian
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
Thống kê từ pg_tables
SELECT * FROM pg_class
WHERE relname = 'mytable'
-- sample of output columns
| relname | relhasindex |
SELECT * FROM pg_stats
WHERE tablename = 'mytable'
-- sample of output columns
null_frac | avg_width | n_distinct |
- Chỉ mục cột
- Đếm giá trị null
- Độ rộng cột
- Giá trị phân biệt
EXPLAIN
- Cửa sổ vào kế hoạch truy vấn
- Bước và ước lượng cost
- Không chạy truy vấn
- Quét tuần tự bảng cheeses
- Ước lượng cost và kích thước
EXPLAIN
SELECT * FROM cheeses
Seq Scan on cheeses
(cost=0.00..10.50 rows=5725 width=296)
EXPLAIN: Scan
- Bước trong kế hoạch truy vấn
- Trả về các hàng
Seq Scan trên cheeses (cost=0.00..10.50 rows=5725 width=296)
- Seq Scan: quét toàn bộ hàng của bảng
EXPLAIN: Cost
- Không đơn vị
- So sánh cấu trúc có cùng đầu ra
- Không nên so sánh truy vấn có đầu ra khác
Seq Scan trên cheeses (cost=0.00..10.50 rows=5725 width=296)
- 0.00.. : thời gian khởi động
..10.50 : tổng thời gian
tổng thời gian = khởi động + chạy
EXPLAIN: Size
- Ước lượng kích thước
Seq Scan trên cheeses (cost=0.00..10.50 rows=5725 width=296)
- rows: số hàng cần xét để chạy
- width: bề rộng bytes mỗi hàng
EXPLAIN với WHERE clause
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep')
Seq Scan on cheeses (cost=0.00..378.90 rows=3 width=118)
-> Filter: (species = ANY ('{"goat","sheep"}'::text[]))
- Đọc từ dưới lên
- Bước 1: Filter
- Bước 2: Sequential scan
- Mệnh đề WHERE
- Giảm số hàng cần quét và tăng tổng cost
EXPLAIN với index
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') -- index on species column
Bitmap Index Scan using species_idx on cheeses (cost=0.29..12.66 rows=3 width=118)
Index Cond: (species = ANY ('{"goat","sheep"}'::text[]))
- Bước 1: Bitmap Index Scan
- Index Cond giải thích bước quét
- INDEX
- Cost khởi động tăng từ 0
- Tổng cost giảm từ 379
Hãy luyện tập!
Cải thiện hiệu năng truy vấn trong PostgreSQL
