Thống kê và bộ tối ưu — ANALYZE và độ chọn lọc

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

Bài này nói về cách cơ sở dữ liệu quyết định dùng chỉ mục nào.

Mảnh ghép then chốt là thống kê.

Thống kê là các sự thật phân bố về dữ liệu của bạn — "bảng có bao nhiêu dòng", "một cột có bao nhiêu giá trị khác biệt" — và bạn thu thập chúng bằng lệnh ANALYZE.

Từ các thống kê đó cơ sở dữ liệu tính độ chọn lọc (selectivity — ước lượng tỷ lệ dòng còn lại sau điều kiện) và quyết định dùng chỉ mục hay đọc cả bảng.

Bạn sẽ làm việc với bảng bán hàng perf_sales (50.000 dòng).

Nó có các cột với cardinality rất khác nhau: emp_id (30 nhân viên khác biệt), region (4 giá trị: East / West / North / South), product (200 giá trị, P001 đến P200), và status (chủ yếu paid, cộng pending và refunded).

Bạn sẽ xem kế hoạch thay đổi thế nào trước và sau ANALYZE.

Trước bài tập, kiểm tra định nghĩa cột, một mẫu dữ liệu, và cách các giá trị phân bố qua mỗi cột trong perf_sales. Việc sinh dữ liệu mất một lúc, nên lần chạy đầu có thể dừng 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.

③ Đếm dòng theo giá trị region để xem các giá trị phân bố thế nào (cardinality).

ANALYZE và sqlite_stat1 — thu thập thống kê

Chạy ANALYZE và cơ sở dữ liệu đi qua các bảng và chỉ mục, thu thập thống kê, và lưu vào bảng nội bộ tên sqlite_stat1.

sqlite_stat1 ghi lại các sự thật như "trung bình bao nhiêu dòng tương ứng với một giá trị duy nhất của chỉ mục này".

Chạy ANALYZE xây thống kê dựa trên dữ liệu thực của bạn, giúp bộ tối ưu chọn kế hoạch thực thi chính xác.

Dùng ANALYZE một mình để bao toàn bộ bảng, hoặc ANALYZE tên_bảng để nhắm vào một bảng cụ thể.

-- Lập chỉ mục hai cột với cardinality khác nhau (status=3 giá trị / qty=20 giá trị)
DROP INDEX IF EXISTS ix_status;
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_status ON perf_sales(status);
CREATE INDEX ix_qty    ON perf_sales(qty);

-- Thu thập thống kê và xem gì được lưu (trung bình dòng mỗi giá trị)
ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1
WHERE tbl = 'perf_sales' ORDER BY idx;
--> ix_status: dòng như 50000 16667 (khoảng 16667 dòng mỗi giá trị)
--> ix_qty:    dòng như 50000 2500 (khoảng 2500 dòng mỗi giá trị)

Bạn sẽ thực sự thu thập thống kê nắm bắt "mỗi chỉ mục có thể thu hẹp đến mức nào" và đọc kết quả. Làm việc tạo chỉ mục, thu thập thống kê, và xác minh trong một lần thực thi. (Chạy đúng sẽ làm xuất hiện phần giải thích.)

① Xóa chỉ mục cùng tên với DROP INDEX IF EXISTS, rồi dùng CREATE INDEX để xây một chỉ mục trên emp_id và một trên region.

② Chạy ANALYZE; để thu thập thống kê.

③ Từ sqlite_stat1, lấy các dòng có tbl là perf_sales và xem cột stat cho mỗi chỉ mục (tổng số dòng cộng trung bình dòng mỗi giá trị).

Độ chọn lọc thay đổi kế hoạch — cột trả công so với cột không

Độ chọn lọc là ước lượng "tỷ lệ dòng còn lại sau bộ lọc".

Tỷ lệ đó càng nhỏ, độ chọn lọc càng cao và chỉ mục càng trả công.

emp_id = 7 thu hẹp về khoảng 1.667 dòng (khoảng 3% bảng), nên độ chọn lọc cao và chỉ mục đáng dùng.

Ngược lại, status = 'paid' để lại khoảng 40.000 dòng (khoảng 80% bảng), nên độ chọn lọc thấp và đọc mọi dòng theo thứ tự thắng nhảy qua chỉ mục từng dòng một.

Khi thống kê có sẵn, bộ tối ưu (trình lập kế hoạch truy vấn) đọc độ chọn lọc và so sánh chi phí kế hoạch chỉ mục so với quét toàn bộ.

Với chỉ mục composite hoặc nhiều ứng cử viên, nó dựa vào thống kê để ưu tiên "chỉ mục thu hẹp về ít dòng hơn".

Bài tập tiếp theo cho thấy ngay cả với cùng bảng và cùng chỉ mục, kế hoạch có thể thay đổi tùy độ chọn lọc của cột WHERE và liệu các cột bạn chọn có trong chỉ mục hay không.

-- Lập chỉ mục một cột độ chọn lọc cao (amount: gần như duy nhất) và một thấp (status: 3 giá trị),
-- rồi sau ANALYZE so sánh các kế hoạch cho truy vấn cùng hình dạng
DROP INDEX IF EXISTS ix_amount;
DROP INDEX IF EXISTS ix_status;
CREATE INDEX ix_amount ON perf_sales(amount);
CREATE INDEX ix_status ON perf_sales(status);
ANALYZE;

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount = 500000;
--> SEARCH perf_sales USING INDEX ix_amount (amount=?)

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'paid';
--> độ chọn lọc thấp làm kế hoạch nghiêng SCAN trở thành khả năng

So sánh "điều kiện hầu như không dòng nào khớp" với "điều kiện gần như mọi dòng khớp", thể hiện là truy vấn chọn cột không có trong chỉ mục. Với COUNT(*), SQLite có thể hoàn tất đếm chỉ dùng chỉ mục, nên chỉ mục được dùng bất kể độ chọn lọc. Để xem SCAN thực sự thắng trên bộ lọc độ chọn lọc thấp, bài này dùng hình dạng SELECT sale_id, amount — lấy `amount`, không có trong chỉ mục.

① Xóa chỉ mục cùng tên với DROP INDEX IF EXISTS, xây một chỉ mục trên emp_id và một trên status, rồi chạy ANALYZE; để thu thập thống kê.

② Thêm EXPLAIN QUERY PLAN và xem kế hoạch cho truy vấn chọn sale_id và amount cho các dòng có emp_id = 7 (độ chọn lọc cao: khoảng 1.667 dòng / 3%).

③ Sau đó thêm EXPLAIN QUERY PLAN và xem kế hoạch cho cùng hình dạng với status = 'paid', và đọc cách phía emp_id và phía status chia giữa SEARCH và SCAN (độ chọn lọc thấp: khoảng 40.000 dòng / 80%).

Trên console khóa học này, độ chọn lọc thấp vẫn có thể giữ INDEX SCAN

Trên console trình duyệt ở đây (SQLite chạy trong bộ nhớ), kế hoạch bước ③ của status = 'paid' có thể vẫn giữ SEARCH perf_sales USING INDEX ix_status (status=?). Lý do.

- Nó chạy trong bộ nhớ: trên cơ sở dữ liệu disk, khoảng cách giữa "I/O ngẫu nhiên qua chỉ mục" và "SCAN tuần tự" lớn, đó là lý do độ chọn lọc thấp ưu ái SCAN. Trong bộ nhớ, cả hai đều nhanh và khoảng cách thu hẹp.

- Các mục chỉ mục được sắp theo giá trị: ix_status được sắp theo status, nên khối 'paid' được đọc như phạm vi liền nhau — không có truy cập ngẫu nhiên.

- Truy cập bảng dựa trên rowid rẻ: SQLite có thể lấy dòng bảng trực tiếp qua rowid nội bộ, nên ngay cả việc đọc bảng do chỉ mục dẫn dắt vẫn giữ chi phí hợp lý.

Hành vi chuyển rõ ràng sang `SCAN` khi độ chọn lọc giảm dễ quan sát hơn trên PostgreSQL hoặc Oracle, các RDBMS với lưu trữ disk thực và bộ tối ưu cost-based đầy đủ. Bài học từ bài tập này là có một nguyên lý đang vận hành: ngay cả với chỉ mục giống hệt, sự kết hợp giữa độ chọn lọc và cột được chọn có thể lật lựa chọn của bộ tối ưu. Ngưỡng chính xác giữa SCAN và SEARCH di chuyển theo engine, sự hiện diện của disk, và độ chính xác của thống kê.

Tối ưu hóa cost-based và biến bind — bên trong các cơ sở dữ liệu khác

Bộ máy bạn đã thấy — "ước lượng độ chọn lọc từ thống kê, rồi chọn kế hoạch rẻ nhất" — gọi là bộ tối ưu cost-based (CBO).

Ở các cơ sở dữ liệu lớn như Oracle và SQL Server, bộ máy này có nhiều tầng bên trong hơn.

Một là parsing (phân tích câu lệnh SQL): phân tích một câu lệnh SQL lần đầu và xây kế hoạch là hard parse, còn dùng lại kế hoạch đã phân tích trước đó là soft parse.

Một tầng khác là biến bind (bind variable: cơ chế không đặt giá trị trực tiếp vào SQL mà để lại một slot như ? và truyền giá trị lúc chạy), cùng với bind peeking — nhìn vào giá trị bộ tối ưu được đưa cho và chọn kế hoạch dựa trên giá trị đó lệch ra sao.

Đây là các cơ chế nội bộ của engine thực thi cơ sở dữ liệu lớn. Biết các khái niệm làm các thảo luận tinh chỉnh dễ theo dõi hơn nhiều.

Trên console trình duyệt của khóa học này, bạn có thể quan sát kế hoạch phản ứng với độ chọn lọc thế nào (phần trước), nhưng sự chuyển đổi giữa hard và soft parse và hành vi bind peeking không tái hiện được ở đây như callout dưới giải thích, nên bạn sẽ làm việc qua các khái niệm với sơ đồ và mẫu code chỉ đọc.

Hard / soft parse và bind peeking là cơ chế nội bộ kiểu Oracle

Sự chuyển đổi giữa hard và soft parse và bind peeking (chọn lại kế hoạch dựa trên giá trị được truyền lệch ra sao) sống bên trong engine thực thi của các cơ sở dữ liệu lớn như Oracle và SQL Server.

Console trình duyệt của khóa học này không phơi bày các view cần thiết để quan sát các trạng thái nội bộ này (nghĩ đến V$SQL của Oracle), nên không có cách nào để trình diễn chúng trong console.

Ở đây bạn sẽ lấy khái niệm từ sơ đồ và mẫu code chỉ đọc dưới đây.

Mặt khác, việc thu thập thống kê bằng ANALYZE và cách kế hoạch phản ứng với độ chọn lọc, cả hai đã đề cập trước, là quan sát được thật.

Trái tim của tối ưu hóa cost-based — "thống kê -> độ chọn lọc -> chọn kế hoạch" — là điều bạn có thể xác nhận thực hành trong console của khóa học này, nên thành thạo cái đó trước.

-- Ý tưởng cách trông như thế nào ở Oracle (chỉ đọc; đừng chạy trên console khóa học này)
-- Biến bind truyền giá trị vào sau (:s được điền lúc chạy)
-- SELECT * FROM perf_sales WHERE status = :s;
--
-- Chuỗi SQL giống hệt dùng lại kế hoạch = soft parse
-- Chuỗi hơi khác cũng xây lại = hard parse
-- Ở Oracle bạn sẽ kiểm tra parsing qua V$SQL.SQL_TEXT, nhưng ở đây không

-- Điều bạn có thể quan sát thực sự trên console khóa học này:
-- khi thống kê đã thu thập, ước lượng độ chọn lọc dựa trên dữ liệu thực
ANALYZE;
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'pending';
--> SEARCH perf_sales USING INDEX ... (status=?)

Của tối ưu hóa cost-based, phần console khóa học này thực sự cho bạn xem là "thu thập thống kê đổi ước lượng", và bạn sẽ xác minh trên cột product (200 giá trị, độ chọn lọc cao). Làm việc tạo chỉ mục và kiểm tra kế hoạch trước và sau khi thu thập thống kê trong một lần thực thi.

① Xóa chỉ mục cùng tên với DROP INDEX IF EXISTS, rồi tạo chỉ mục trên cột product.

② Thêm EXPLAIN QUERY PLAN và xem kế hoạch cho truy vấn đếm dòng có product là 'P050', khi chưa có thống kê.

③ Chạy ANALYZE; để thu thập thống kê, rồi chạy lại EXPLAIN QUERY PLAN cùng truy vấn. Vì product có 200 giá trị và độ chọn lọc cao, kế hoạch nên tiếp tục dùng chỉ mục sau ANALYZE luôn.