Bài 38: Đọc EXPLAIN QUERY PLAN

Thầy Nam mở danh sách điểm của một học viên, nhưng trang chờ lâu hơn dự kiến. Danh sách vẫn đúng, nên sửa công thức tính điểm không giải quyết được vấn đề. Thầy cần biết máy đang dò mọi lần chấm hay tìm thẳng những lần thuộc học viên được chọn.

Kế hoạch mô tả cách tìm, không chứa điểm của học viên

Kế hoạch truy vấn (query plan) mô tả cách hệ quản trị dự định lấy dữ liệu. Trong SQLite, thêm EXPLAIN QUERY PLAN trước một câu đọc để xem mô tả ấy. Bạn vẫn phải chạy câu đọc riêng nếu muốn lấy điểm.

Trước hết, xem những dòng thầy Nam thực sự cần từ bảng scores:

SELECT id, score
FROM scores
WHERE student_id = 3
ORDER BY id;

Kết quả có hai lần chấm, với điểm 90 và 100. Điều kiện giữ những dòng có student_id = 3. Cột id ở kết quả là mã lần chấm, khác với mã học viên dùng để lọc.

Bây giờ giữ nguyên câu đọc và yêu cầu SQLite giải thích cách tìm:

EXPLAIN QUERY PLAN
SELECT id, score
FROM scores
WHERE student_id = 3
ORDER BY id;

Bảng này có các cột như id, parent, notused, detail. detail là SCAN scores: máy đọc lần lượt mọi lần chấm. Cột id ở đây đánh số nút của kế hoạch, không phải mã lần chấm. Vì vậy, đọc số đầu dòng kế hoạch thành điểm hoặc số học viên sẽ dẫn tới kết luận sai.

• Đọc detail để nhận ra bảng và cách truy cập.
• Đọc parent khi cần biết nút nào nằm dưới nút khác.
• Chạy câu đọc bình thường để kiểm cột, dòng và thứ tự kết quả.

Một nút (node) là một bước trong cây kế hoạch. Cột notused là cột phụ của đầu ra, không phải số dòng bị bỏ. Các con số đánh dấu nút giúp nối cấu trúc, nhưng không đo số phép so sánh.

Phân biệt quét nguồn với tìm một phần bằng chỉ mục

Nhãn SCAN mô tả việc quét nguồn. Nhãn SEARCH mô tả việc tìm một phần dòng theo điều kiện. Cả hai đều nói về cách truy cập, không nói rằng câu hỏi của người dùng đã được trả lời đúng.

Bảng scores chưa có chỉ mục riêng theo học viên. Khối thiết lập sau thêm chỉ mục score_owner vào bản dữ liệu riêng của ví dụ. Nó không sửa các dòng điểm:

Xem lại kế hoạch với cùng điều kiện:

EXPLAIN QUERY PLAN
SELECT id, score
FROM scores
WHERE student_id = 3
ORDER BY id;

Trên bộ mẫu này, detail cho thấy SEARCH với chỉ mục score_owner. Phần student_id=? mô tả điều kiện tra cứu theo mã. Dấu ? trong mô tả không có nghĩa câu đọc thiếu dữ liệu, vì câu gốc đã cho mã 3.

Chỉ mục bao phủ (covering index) chứa đủ thông tin để phục vụ lần đọc đó. Kế hoạch có thể đọc chỉ mục mà không phải tra bảng để lấy thêm cột. Đây là đặc tính của một truy vấn cụ thể, không phải danh hiệu cố định cho mọi lần dùng chỉ mục.

Giữ chỉ mục ấy nhưng bỏ điều kiện để xem một cách đọc khác:

EXPLAIN QUERY PLAN
SELECT student_id
FROM scores;

Mô tả là SCAN scores USING COVERING INDEX score_owner. Máy vẫn cần mọi mã học viên trong các lần chấm, nên không tìm một tập con. Ví dụ này bác bỏ cách hiểu rằng có chữ SCAN thì chắc chắn không dùng chỉ mục.

Với bảng nhỏ, quét cũng có thể hợp lý vì việc đi qua vài dòng tốn ít công. Thêm chỉ mục chỉ để đổi nhãn không chứng minh trang nhanh hơn. Bạn cần đo trên lượng dữ liệu gần với nhu cầu sử dụng.

Nhận ra công việc sắp xếp còn lại

Danh sách của thầy Nam còn cần điểm cao trước. Cây B tạm (temporary B-tree) là cấu trúc SQLite có thể dùng để tổ chức dữ liệu khi sắp xếp. Chữ tạm nói về cấu trúc phục vụ câu lệnh, không phải dữ liệu bị hỏng.

Chỉ mục theo học viên giúp lọc, nhưng chưa hứa cung cấp thứ tự theo điểm. Hãy đọc kế hoạch cho yêu cầu mới:

EXPLAIN QUERY PLAN
SELECT id, score
FROM scores
WHERE student_id = 3
ORDER BY score DESC, id;

Ngoài bước tìm theo học viên, kế hoạch có thêm USE TEMP B-TREE FOR ORDER BY. Bước này giải thích rằng máy còn cần sắp lại các dòng đã tìm. Nó không nói danh sách sai hay một chỉ mục vừa được lưu vĩnh viễn.

Thử chỉ mục score_owner_rank theo học viên, điểm giảm dần và mã lần chấm. Thiết lập này dùng bản dữ liệu mới, không cộng dồn chỉ mục từ mục trước:

So kế hoạch khi thứ tự cột khớp yêu cầu:

EXPLAIN QUERY PLAN
SELECT id, score
FROM scores
WHERE student_id = 3
ORDER BY score DESC, id;

Dòng sắp xếp tạm đã biến mất. Kế hoạch ghi SEARCH scores USING COVERING INDEX score_owner_rank (student_id=?). Chỉ mục này bao phủ vì có đủ student_id, score, id để lọc, sắp xếp và trả kết quả.

Đó là bằng chứng về cách chạy, chưa phải số đo thời gian. Cũng cần tính dung lượng và công cập nhật chỉ mục mỗi khi điểm thay đổi.

Chạy câu đọc để xác nhận danh sách vẫn đúng:

SELECT id, score
FROM scores
WHERE student_id = 3
ORDER BY score DESC, id;

Hai điểm vẫn là 100, 90 theo thứ tự giảm dần. Cột id giúp xác định thứ tự khi điểm bằng nhau. Nếu bỏ ORDER BY, bạn cũng bỏ yêu cầu thứ tự, dù lần chạy hiện tại tình cờ còn đúng.

Đọc các vòng ghép rồi kiểm kết quả trước khi đo

Khi ghép bảng, SQLite có thể đọc một nguồn trước, rồi tìm dòng khớp trong nguồn còn lại. Cách làm ấy gọi là ghép bằng vòng lặp lồng nhau (nested-loop join). Nguồn đọc bên ngoài quyết định những giá trị dùng cho lần tìm bên trong.

Xem kế hoạch lấy tên kèm điểm cho một học viên:

EXPLAIN QUERY PLAN
SELECT s.name, sc.score
FROM students AS s
JOIN scores AS sc ON sc.student_id = s.id
WHERE s.id = 3
ORDER BY sc.id;

SQLite tìm s theo khóa chính trước, với mô tả SEARCH s USING INTEGER PRIMARY KEY (rowid=?). Sau đó, với mỗi học viên tìm được, máy tìm sc bằng score_owner. Dòng thứ hai ghi SEARCH sc USING INDEX score_owner (student_id=?). Thứ tự truy cập do bộ tối ưu chọn, không do thứ tự viết bảng quyết định.

Một biểu thức trên cột lọc cũng có thể làm mất đường tìm bằng chỉ mục. Dù đã có score_owner, hãy xem kế hoạch khi cộng thêm 0 vào mã học viên:

EXPLAIN QUERY PLAN
SELECT id, score
FROM scores
WHERE student_id + 0 = 3
ORDER BY id;

detail là SCAN scores, dù chỉ mục score_owner vẫn tồn tại. Với cột mã số này, student_id + 0 = 3 giữ đúng những dòng như student_id = 3. Nhưng SQLite không dùng chỉ mục ấy để tìm thẳng qua biểu thức cộng. Hãy để cột đứng một mình, chuyển phép tính sang vế giá trị khi phù hợp.

Bài 37 đã giới thiệu chỉ mục biểu thức cho LOWER(name). Dùng chỉ mục thường trên tên để xem điều gì còn thiếu:

Kế hoạch sau lọc tên đã chuyển chữ thường nhưng chỉ mục vẫn lưu tên gốc:

EXPLAIN QUERY PLAN
SELECT id, name
FROM students
WHERE LOWER(name) = 'an';

Mô tả là SCAN students USING COVERING INDEX nm: máy quét cả chỉ mục, không tìm thẳng tên cần lấy. Chỉ mục bao phủ đủ cột đầu ra nhưng chưa phục vụ phép tìm trên biểu thức. Tên An dùng chữ ASCII để phép chuyển chữ thường của SQLite cho kết quả rõ ràng.

Trên bản dữ liệu riêng khác, tạo chỉ mục cho đúng biểu thức:

Giữ nguyên câu đọc để so kế hoạch:

EXPLAIN QUERY PLAN
SELECT id, name
FROM students
WHERE LOWER(name) = 'an';

Mô tả là SEARCH students USING INDEX lower_name (<expr>=?). Máy tìm theo biểu thức đã đánh chỉ mục thay vì quét mọi tên. So hai kế hoạch giúp giải thích vì sao có chỉ mục chưa chắc đã tìm thẳng được dữ liệu.

Khi đo, hãy giữ cùng dữ liệu, kết quả và điều kiện chạy. Ghi rõ lần chạy đầu hay lần đã có dữ liệu trong bộ nhớ, cùng số lượt đo. Kế hoạch không chứa thời gian chờ mạng hoặc thời gian trang vẽ bảng, nên chỉ đọc nó chưa tìm được mọi nguyên nhân chậm.

Lỗi thường gặp

Tóm tắt

• Kế hoạch mô tả đường truy cập, còn câu đọc bình thường trả dữ liệu.
• SCAN có thể quét bảng hoặc chỉ mục, còn SEARCH tìm một phần dòng.
• Sắp xếp tạm là một công việc cần xem xét, không phải thông báo dữ liệu hỏng.
• Cách ghép và thứ tự vòng lặp ảnh hưởng lượng công việc.
• Một thay đổi chỉ đáng giữ khi kết quả đúng và số đo phù hợp nhu cầu.

Kiểm tra nhanh

Đọc thêm

• SQLite giải thích từng phần của kế hoạch.
• SQLite mô tả lựa chọn đường truy cập.