Tăng tốc ORDER BY / GROUP BY với chỉ mục

Loại bỏ sắp xếp tạm bằng chỉ mục

Khi bạn sắp xếp với ORDER BY và cột mục tiêu không có chỉ mục, cơ sở dữ liệu phải kéo mọi bản ghi ra và rồi sắp xếp chúng.

Kế hoạch hiển thị sắp xếp tạm này là USE TEMP B-TREE FOR ORDER BY (nó xây một cấu trúc làm việc tạm chỉ để sắp xếp lại).

Mọi thứ thay đổi một khi bạn tạo chỉ mục trên cột bạn đang sắp xếp theo.

Một chỉ mục đã ở theo thứ tự của cột đó ngay từ lúc nó được xây.

Đi theo thứ tự của chỉ mục và kết quả rơi ra ngay lập tức.

Sắp xếp tạm không còn cần thiết, và USE TEMP B-TREE biến mất khỏi kế hoạch.

Sắp xếp tạm xáo trộn lại toàn bộ 50.000 bản ghi vào một cấu trúc làm việc, nên chi phí tăng theo số bản ghi.

Ngay cả khi LIMIT 10 chỉ trả về 10 bản ghi đầu, chọn 10 bản ghi đầu đó vẫn có nghĩa là sắp xếp lại mọi bản ghi trước — tải không co lại.

Trong bài này, bạn sẽ thử "loại bỏ sắp xếp bằng chỉ mục" trong thực hành.

-- Ví dụ: lấy 10 bản ghi đầu sắp xếp theo amount
-- Với chỉ mục, cơ sở dữ liệu chỉ đi theo thứ tự của nó
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

EXPLAIN QUERY PLAN
SELECT sale_id, amount
FROM perf_sales
ORDER BY amount
LIMIT 10;

Hãy tưởng tượng yêu cầu: "Chúng ta muốn doanh số được sắp xếp theo ngày để xem một vài cái đầu." Lấy truy vấn sắp xếp theo sale_date và so sánh kế hoạch của nó có và không có chỉ mục, xem USE TEMP B-TREE FOR ORDER BY có xuất hiện không. Xóa và xây lại chỉ mục trong một lần thực thi console để kế hoạch tự đứng vững. (Chạy đúng sẽ làm xuất hiện phần giải thích.)

① Trước tiên, dùng DROP INDEX IF EXISTS để xóa chỉ mục trên sale_date, rồi hiển thị EXPLAIN QUERY PLAN cho 10 bản ghi đầu sắp xếp theo sale_date (trạng thái không chỉ mục).

② Sau đó tạo chỉ mục một cột trên sale_date.

③ Hiển thị kế hoạch cho cùng truy vấn sắp xếp lần nữa, và so sánh nó với ① để xem sắp xếp tạm có biến mất không.

GROUP BY cũng có thể tựa vào thứ tự của chỉ mục

GROUP BY cũng cần các bản ghi có cùng giá trị ngồi cạnh nhau để có thể gom nhóm chúng.

Không có chỉ mục, điều đó có nghĩa là sắp xếp tạm và USE TEMP B-TREE FOR GROUP BY trong kế hoạch.

Tạo chỉ mục trên cột gom nhóm và các bản ghi có cùng giá trị đã liền kề trong thứ tự của chỉ mục, nên cơ sở dữ liệu có thể tách ra các nhóm chỉ bằng cách đi qua nó.

Ví dụ, GROUP BY emp_id cùng với chỉ mục trên emp_id nghĩa là các bản ghi của mỗi emp_id đã liên tiếp trong thứ tự của chỉ mục, nên tổng hợp chạy không cần sắp xếp tạm.

Bao luôn cả cột được tổng hợp vào chỉ mục và bạn cũng tránh quay về bảng nền (Index-Only Scan từ bài trước).

-- Ví dụ: đếm mỗi region
-- Với chỉ mục trên region, GROUP BY không cần sắp xếp tạm
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Hãy tưởng tượng yêu cầu: "Chúng ta muốn số lượng doanh số mỗi nhân viên." Lấy truy vấn gom nhóm theo emp_id và so sánh kế hoạch của nó có và không có chỉ mục, xem USE TEMP B-TREE FOR GROUP BY có xuất hiện không.

① Trước tiên, dùng DROP INDEX IF EXISTS để xóa chỉ mục, rồi hiển thị EXPLAIN QUERY PLAN cho truy vấn đếm bản ghi mỗi emp_id (trạng thái không chỉ mục).

② Sau đó tạo chỉ mục một cột trên emp_id.

③ Hiển thị kế hoạch cho cùng truy vấn tổng hợp lần nữa, và so sánh nó với ① để xem sắp xếp tạm có biến mất không.

DISTINCT loại sắp xếp tạm của nó theo cùng cách

DISTINCT (loại trùng và chỉ trả về giá trị duy nhất) cũng cần các giá trị được xếp hàng để phát hiện trùng, nên không có chỉ mục nó kích hoạt sắp xếp tạm với USE TEMP B-TREE FOR DISTINCT.

Cơ chế giống GROUP BY: với chỉ mục trên cột mục tiêu, các giá trị giống nhau đã liền kề trong thứ tự, nên loại bỏ trùng chỉ là vấn đề so sánh từng hàng xóm.

Với SELECT DISTINCT product FROM perf_sales, nơi bạn muốn các giá trị duy nhất của một cột, chỉ mục trên product loại bỏ nhu cầu sắp xếp tạm.

Sắp xếp, gom nhóm, và loại trùng đều chia sẻ đặc tính là "các thao tác cần giá trị được xếp hàng" — và chỉ mục trên cột mục tiêu cho phép bạn né sắp xếp tạm cho bất kỳ cái nào trong số chúng.

-- Ví dụ: lấy các giá trị duy nhất của region
-- Chỉ mục trên region tránh sắp xếp tạm DISTINCT
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT DISTINCT region
FROM perf_sales;

Hãy tưởng tượng yêu cầu: "Chúng ta muốn danh sách mã sản phẩm đã xử lý, không có trùng lặp." Lấy truy vấn trả về các giá trị duy nhất của product và so sánh kế hoạch của nó có và không có chỉ mục, xem USE TEMP B-TREE FOR DISTINCT có xuất hiện không.

① Trước tiên, dùng DROP INDEX IF EXISTS để xóa chỉ mục, rồi hiển thị EXPLAIN QUERY PLAN cho truy vấn trả về các giá trị duy nhất của product (trạng thái không chỉ mục).

② Sau đó tạo chỉ mục một cột trên product.

③ Hiển thị kế hoạch cho cùng truy vấn loại trùng lần nữa, và so sánh nó với ① để xem sắp xếp tạm có biến mất không.