Đọc kế hoạch thực thi — EXPLAIN QUERY PLAN

Bộ dữ liệu cho bài này — perf_sales (50.000 dòng bán hàng)

Khi bạn muốn biết một truy vấn nhanh hay chậm, thứ đầu tiên cần xem là kế hoạch thực thi (execution plan).

Với một câu lệnh SQL, cơ sở dữ liệu quyết định bên trong "đọc bảng nào theo thứ tự nào, và như thế nào".

Công cụ in quy trình đó dưới dạng văn bản là EXPLAIN QUERY PLAN.

Thêm tiền tố từ khóa này cho truy vấn và bạn sẽ thấy các bước thực thi thay vì kết quả thật.

Trước khi đi vào bài tập, hãy kiểm tra định nghĩa cột và một mẫu dữ liệu của bảng perf_sales được dùng xuyên suốt bài này. Việc sinh dữ liệu mất một lúc, nên lần chạy đầu có thể mất vài giây.

① Dùng PRAGMA table_info(perf_sales); để xem tên cột, kiểu, và khóa chính.

② Dùng SELECT * FROM perf_sales LIMIT 5; để xem trước 5 dòng đầu.

③ Dùng SELECT COUNT(*) FROM perf_sales; để xác nhận có 50.000 dòng.

SCAN và SEARCH — quét toàn bảng và quét chỉ mục

Hai từ đầu tiên cần học trong một kế hoạch thực thi là SCAN và SEARCH.

SCAN perf_sales nghĩa là đọc mọi dòng từ đầu bảng đến cuối (quét toàn bảng).

Khi bạn lọc theo một cột không có chỉ mục, cơ sở dữ liệu không biết dòng nào khớp, nên nó kiểm tra cả 50.000 dòng từng dòng một.

Trong khi đó SEARCH perf_sales USING INDEX tên_chỉ_mục (cột=?) nghĩa là theo chỉ mục để đến thẳng các dòng khớp (quét chỉ mục).

Tạo chỉ mục với CREATE INDEX và các bộ lọc trên cột đó sẽ dừng việc quét mọi dòng — kế hoạch chuyển từ SCAN sang SEARCH ... USING INDEX.

-- Lọc theo region: không có chỉ mục, nên đọc mọi dòng
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales

-- Tạo chỉ mục trên region, rồi xem cùng kế hoạch
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)

Hãy tưởng tượng tình huống: "đếm doanh số cho một nhân viên cụ thể chậm." Bạn sẽ so sánh kế hoạch thực thi trước và sau khi thêm chỉ mục. Chạy việc tạo chỉ mục và kiểm tra kế hoạch trong cùng một lần thực thi. (Chạy đúng sẽ làm xuất hiện phần giải thích.)

① Trước tiên, thêm tiền tố EXPLAIN QUERY PLAN cho truy vấn đếm dòng có emp_id bằng 7 và xem kế hoạch. Không có chỉ mục, bạn sẽ thấy SCAN.

② Sau đó xóa chỉ mục cũ với DROP INDEX IF EXISTS, xây chỉ mục trên emp_id với CREATE INDEX, và chạy lại cùng EXPLAIN QUERY PLAN. Xác nhận nó chuyển sang SEARCH ... USING INDEX.

USE TEMP B-TREE — vùng tạm để sắp xếp

USE TEMP B-TREE FOR ORDER BY (B-tree tạm) nghĩa là cột ORDER BY không có chỉ mục, nên cơ sở dữ liệu đang dựng một vùng nháp tạm để sắp xếp kết quả.

Cả 50.000 dòng đi vào vùng tạm đó để sắp xếp lại, nên chi phí tăng theo số dòng.

Nếu có chỉ mục có thể dùng cho sắp xếp, dòng này biến khỏi kế hoạch và thứ tự sẵn có của chỉ mục được dùng nguyên trạng.

-- Top 10 theo amount tăng dần: không có chỉ mục, nên cần vùng tạm cho sắp xếp
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SCAN perf_sales
--> USE TEMP B-TREE FOR ORDER BY

-- Tạo chỉ mục trên amount và sắp xếp có thể dùng lại thứ tự đó
DROP INDEX IF EXISTS ix_amount;
CREATE INDEX ix_amount ON perf_sales(amount);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SEARCH perf_sales USING INDEX ix_amount
-- USE TEMP B-TREE biến khỏi kế hoạch

Xem kế hoạch thực thi cho "top 10 doanh số theo amount tăng dần" khi chưa có chỉ mục. Ba bài tập tiếp theo chạy theo thứ tự để bạn so sánh kế hoạch trước và sau khi có chỉ mục.

① Ở console đầu tiên, thêm tiền tố EXPLAIN QUERY PLAN cho truy vấn sắp xếp perf_sales theo amount tăng dần và lấy 10 dòng đầu (sale_id, amount).

Xác nhận đầu ra chứa hai dòng: SCAN perf_sales và USE TEMP B-TREE FOR ORDER BY liên quan đến sắp xếp. Không có chỉ mục trên cột sắp xếp amount, cơ sở dữ liệu phải nạp 50.000 dòng vào vùng tạm và sắp xếp lại.

② Ở console thứ hai, chạy DROP INDEX IF EXISTS ix_amount; để xóa cái trước, rồi tạo chỉ mục tên ix_amount trên cột amount của perf_sales.

Tạo chỉ mục là chi phí một lần, nên không tính vào so sánh kế hoạch thuần. Console thứ ba sẽ chạy lại kiểm tra kế hoạch cho cùng truy vấn.

③ Ở console thứ ba, chạy lại cùng EXPLAIN QUERY PLAN như ở ①.

Xác nhận rằng dòng USE TEMP B-TREE FOR ORDER BY đã biến khỏi kế hoạch, thay bằng cái gì đó như SEARCH perf_sales USING INDEX ix_amount. Thứ tự của chỉ mục được dùng lại trực tiếp, nên sắp xếp ở vùng tạm không còn cần thiết.

USING COVERING INDEX — chỉ mục không chạm tới bảng

USING COVERING INDEX (Index-Only Scan, còn gọi là covering index trong tiếng Anh) nghĩa là mọi cột mà truy vấn cần đều nằm trong chỉ mục, nên cơ sở dữ liệu có thể dựng kết quả chỉ từ chỉ mục mà không đọc bản thân bảng.

Chỉ mục là một bản sao nhỏ chỉ của các cột bạn lấy từ bảng, nên bỏ qua việc đọc bảng là điều làm nó nhanh hơn.

Đây là một bước xa hơn SEARCH ... USING INDEX — khi kế hoạch hiển thị USING COVERING INDEX, bạn đang có cách đọc hiệu quả nhất.

-- Chỉ mục chỉ trên emp_id -> tìm dòng qua chỉ mục, rồi đọc amount từ bảng
DROP INDEX IF EXISTS ix_emp;
CREATE INDEX ix_emp ON perf_sales(emp_id);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING INDEX ix_emp (emp_id=?)
--   (bảng cũng được đọc)

-- Chỉ mục composite trên (emp_id, amount) -> mọi cột cần ở trong chỉ mục, không đọc bảng
DROP INDEX IF EXISTS ix_emp_amount;
CREATE INDEX ix_emp_amount ON perf_sales(emp_id, amount);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING COVERING INDEX ix_emp_amount (emp_id=?)

Lấy một truy vấn "lọc theo emp_id và chỉ trả về amount" và biến kế hoạch thành cái kết thúc trong chỉ mục mà không chạm tới bảng. Chạy việc tạo chỉ mục và kiểm tra kế hoạch trong cùng một lần thực thi.

① Xóa chỉ mục cũ với DROP INDEX IF EXISTS, rồi dùng CREATE INDEX để xây chỉ mục composite trên emp_id và amount, theo thứ tự đó.

② Thêm tiền tố EXPLAIN QUERY PLAN cho truy vấn lọc theo emp_id = 7 và chỉ chọn emp_id và amount, và xem kế hoạch. Đọc rằng kế hoạch kết thúc trong chỉ mục, vì mọi cột cần đều ở trong chỉ mục.

Kế hoạch truy vấn con — CORRELATED và MATERIALIZE

Truy vấn có truy vấn con có từ vựng riêng trong kế hoạch.

CORRELATED SCALAR SUBQUERY nghĩa là giá trị của truy vấn con phụ thuộc vào dòng ngoài hiện tại, nên nó được thực thi lại mỗi dòng ngoài.

Với 50.000 dòng ngoài, truy vấn con có thể chạy 50.000 lần — khi từ này xuất hiện trong kế hoạch, đó là ứng cử viên cho việc viết lại.

Trong khi đó MATERIALIZE nghĩa là kết quả của một truy vấn con hoặc CTE được tính một lần, lưu vào bảng tạm, và dùng lại.

Đó là tối ưu hóa trả chi phí tính toán một lần cho một kết quả trung gian mà bạn tham chiếu nhiều lần.

Từ nào trong hai từ này xuất hiện cho bạn biết truy vấn con đang "được tính mỗi lần" hay "tính một lần và dùng lại".

-- Truy vấn con scalar tương quan: lấy lại "amount lớn nhất cho region này" mỗi dòng ngoài
EXPLAIN QUERY PLAN
SELECT s.sale_id, s.amount
FROM perf_sales s
WHERE s.amount = (
  SELECT MAX(amount) FROM perf_sales x WHERE x.region = s.region
)
LIMIT 10;
--> dòng CORRELATED SCALAR SUBQUERY xuất hiện

-- Bảng dẫn xuất (truy vấn con trong mệnh đề FROM) được tính một lần
EXPLAIN QUERY PLAN
SELECT t.region, t.s FROM (
  SELECT region, SUM(amount) AS s FROM perf_sales GROUP BY region
) t
WHERE t.s > 100000000;
--> dòng MATERIALIZE có thể xuất hiện

Xem kế hoạch cho "với mỗi dòng, chỉ lấy doanh số vượt mức trung bình amount của nhân viên đó, top 10" và đọc cấu trúc chạy lại truy vấn con mỗi dòng.

① Thêm tiền tố EXPLAIN QUERY PLAN cho truy vấn, với mỗi dòng trong perf_sales, dùng truy vấn con tương quan để tính "amount trung bình cho cùng emp_id" và lấy 10 dòng mà amount vượt mức đó.

② Đọc, trong đầu ra, dòng chỉ ra truy vấn con scalar tương quan và SCAN perf_sales cho cả truy vấn ngoài và trong. Lưu ý đây là hình dạng nặng trên bảng lớn vì truy vấn con bên trong được đánh giá một lần mỗi dòng ngoài.

Cách điều này tương ứng với các RDBMS khác — MySQL / PostgreSQL / Oracle

Từ vựng kế hoạch trong bài này đến từ EXPLAIN QUERY PLAN của SQLite, nhưng MySQL / PostgreSQL / Oracle thể hiện cùng các khái niệm dưới tên khác nhau.

Bắt đầu bằng việc kiểm tra mỗi cơ sở dữ liệu tạo kế hoạch thực thi thế nào, rồi so sánh bảng cheat sheet từ vựng kế hoạch — bạn sẽ có thể đọc kế hoạch ở cơ sở dữ liệu khác mà không cần học thêm nhiều.

Cách in kế hoạch thực thi ở mỗi cơ sở dữ liệu

• SQLite: Thêm tiền tố EXPLAIN QUERY PLAN cho truy vấn (ví dụ EXPLAIN QUERY PLAN SELECT ...;). Nó không chạy truy vấn, chỉ in kế hoạch. EXPLAIN thuần hiển thị bytecode mức thấp, nên để con người đọc bạn muốn EXPLAIN QUERY PLAN
• MySQL: Thêm tiền tố EXPLAIN cho truy vấn (ví dụ EXPLAIN SELECT ...;). MySQL 8.0+ cũng có EXPLAIN ANALYZE SELECT ...; cho thời gian chạy thực và số dòng thực. Cho đầu ra JSON, dùng EXPLAIN FORMAT=JSON SELECT ...;
• PostgreSQL: Thêm tiền tố EXPLAIN cho truy vấn (ví dụ EXPLAIN SELECT ...;). Đó chỉ là kế hoạch ước lượng. Cho thực tế, dùng EXPLAIN ANALYZE SELECT ...;, và cho thông tin chi tiết bộ đệm I/O, dùng EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
• Oracle: Mẫu hai bước — EXPLAIN PLAN FOR <truy_vấn>; lưu kế hoạch vào bảng nội bộ, và SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); in nó. Để xem nhanh, tùy chọn session SET AUTOTRACE ON cũng được

Cheat sheet từ vựng kế hoạch

Câu chữ khác nhau, nhưng những gì cơ sở dữ liệu làm bên trong về cơ bản là giống nhau. Mục tiêu của bài này là đọc được "có đang đọc mọi dòng không, có đang thu hẹp bằng chỉ mục không, có cần vùng làm việc để sắp xếp không, có đang chạy truy vấn con đi đi lại lại không" từ đầu ra EXPLAIN của mỗi cơ sở dữ liệu.