Một truy vấn mất vài mili giây năm ngoái có thể mất vài phút ngày hôm nay, đơn giản vì bảng đã lớn lên. Tối ưu hóa truy vấn SQL không nói về các thủ thuật thông minh mà thiên về một quy trình có thể lặp lại: tìm hiểu xem cơ sở dữ liệu đang làm gì, loại bỏ công việc lãng phí và kiểm tra xem việc đó có giúp ích hay không.

Chúng tôi đã thử nghiệm từng kỹ thuật bên dưới trên một triệu hàng được tạo trong SQLite 3.50. Thời gian là cách tốt nhất trong số nhiều lần chạy từ một máy tính xách tay, sử dụng cơ sở dữ liệu trong bộ nhớ. Chúng sẽ không khớp với hệ thống của bạn và mỗi PostgreSQL, MySQL và SQL Server đều có trình tối ưu hóa và định dạng kế hoạch riêng. Tập trung vào quy mô của từng cải tiến và các nguyên tắc đằng sau nó.

Kết quả trong nháy mắt

Thiết lập thử nghiệm

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

Một quy trình tối ưu hóa đơn giản

1. Tìm các truy vấn quan trọng. Sử dụng nhật ký truy vấn hoặc theo dõi chậm. Một truy vấn 200 mili giây chạy một nghìn lần một phút có thể quan trọng hơn một báo cáo hàng tháng dài 10 giây.
2. Đọc kế hoạch. Xem cơ sở dữ liệu thực sự đang làm gì.
3. Thay đổi một thứ. Thêm chỉ mục hoặc viết lại một phần truy vấn.
4. Đo lại, trên dữ liệu thực tế. Kế hoạch về 100 hàng thử nghiệm cho bạn biết rất ít về một triệu.
5. Hãy theo dõi theo thời gian. Các kế hoạch có thể thay đổi khi dữ liệu tăng lên và số liệu thống kê thay đổi.

Đọc kế hoạch thực hiện

Kế hoạch thực hiện là chiến lược từng bước của cơ sở dữ liệu để chạy truy vấn, bao gồm các chỉ mục mà cơ sở dữ liệu sử dụng và thứ tự tham gia các bảng. Tiền tố một truy vấn để xem nó:

Lưu ý rằng EXPLAIN ANALYZE trong PostgreSQL thực sự chạy truy vấn, vì vậy hãy cẩn thận khi sử dụng nó với UPDATE hoặc DELETE. Đây là những gì cần tìm kiếm đầu tiên:

Quét toàn bộ không tự động xấu. Nếu một truy vấn cần hầu hết một bảng nhỏ thì quét có thể là lựa chọn nhanh nhất. Trong PostgreSQL’s EXPLAIN ANALYZE, cũng so sánh các hàng ước tính với các hàng thực tế. Sự khác biệt lớn thường có nghĩa là số liệu thống kê trong bảng đã lỗi thời.

Thêm chỉ mục phù hợp

Truy vấn này tìm thấy đơn đặt hàng của một khách hàng:

SELECT id, total FROM orders WHERE customer_id = 123;

Trước khi thêm chỉ mục, kế hoạch là quét toàn bộ:

SCAN orders
-- 20 rows in 73 ms

Sau đó, chúng tôi đã thêm chỉ mục vào cột bộ lọc:

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

20 hàng tương tự quay lại nhanh hơn hàng nghìn lần vì cơ sở dữ liệu không còn đọc hàng triệu hàng để tìm chúng nữa. Không có kỹ thuật nào khác trong bài viết này mang lại cho chúng tôi chiến thắng lớn hơn. Ứng cử viên tốt đầu tiên là các cột được sử dụng trong WHERE, JOIN và ORDER BY, đặc biệt khi chúng thu hẹp kết quả xuống còn vài hàng. Hướng dẫn của chúng tôi về chỉ mục SQL giải thích cách chúng hoạt động.

Không bọc các cột được lập chỉ mục trong hàm

Dưới đây là hai cách để tìm đơn hàng tháng 3, với chỉ mục trên created:

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

Cả hai đều trả về các hàng giống hệt nhau, tuy nhiên hàng đầu tiên mất khoảng ba lần. Kế hoạch cho thấy tại sao: với một hàm trên cột, cơ sở dữ liệu không thể nhảy đến một vị trí trong chỉ mục đã sắp xếp, vì vậy nó phải SCAN qua mọi mục nhập. Phiên bản phạm vi có thể SEARCH thẳng đến đúng nơi. Điều kiện có thể sử dụng chỉ mục theo cách này được gọi là sargable.

Vấn đề tương tự cũng xuất hiện với các mẫu phổ biến sau:

Chúng tôi cũng đã kiểm tra cái cuối cùng đó. Ký tự đại diện hàng đầu được tạo ra SCAN customers, vì chỉ mục được sắp xếp từ chữ cái đầu tiên không thể giúp khớp ở cuối.

Chỉ số tổng hợp

Khi truy vấn lọc trên hai cột, chỉ mục một cột chỉ thực hiện một phần công việc:

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

Thứ tự cột rất quan trọng. Chỉ mục trên (status, created) hoạt động giống như một danh bạ điện thoại được sắp xếp theo họ rồi đến tên. Bạn có thể tra cứu họ riêng, nhưng không thể tra cứu tên riêng. Chỉ riêng truy vấn của chúng tôi trên status đã có thể sử dụng chỉ mục này. Chỉ lọc trên created nói chung không thể sử dụng nó một cách hiệu quả. Theo nguyên tắc chung, hãy đặt các cột được kiểm tra bằng đẳng thức trước và các cột theo phạm vi sau chúng.

Kế hoạch đó cũng cho biết bao gồm chỉ mục, nghĩa là chỉ mục chứa mọi thứ mà truy vấn cần, do đó bản thân bảng không cần phải đọc. Chỉ chọn các cột bạn cần sẽ khiến điều này dễ xảy ra hơn, đây là một lý do khác cần tránh SELECT *.

Phân trang nhanh hơn

Việc sử dụng OFFSET cho các trang sâu là một chi phí ẩn vì cơ sở dữ liệu vẫn phải duyệt qua tất cả các hàng bị bỏ qua:

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

Phân trang keyset vẫn nhanh cho dù bạn đi sâu đến đâu. Nó cũng tránh việc dịch chuyển hàng giữa các trang khi dữ liệu mới được thêm vào. Sự đánh đổi là người dùng không thể nhảy thẳng tới trang 5.000, điều này hiếm khi là nhu cầu thực sự. Trên các bảng có hàng rộng hoặc có nhiều hàng cần bỏ qua, khoảng cách có thể lớn hơn nhiều so với trong thử nghiệm của chúng tôi.

Viết các truy vấn gọn gàng hơn

• Chỉ chọn các cột bạn cần. SELECT * gửi nhiều dữ liệu hơn và điều này có thể ngăn chỉ mục che phủ truy vấn.
• Lọc sớm. Sử dụng WHERE để xóa các hàng trước khi nhóm và HAVING chỉ dành cho các điều kiện trên tập hợp. Xem NHÓM SQL THEO và CÓ.
• Sử dụng LIMIT khi bạn chỉ cần một vài hàng.
• Ưu tiên UNION ALL khi bạn không cần xóa các bản sao. Thử nghiệm UNION so với UNION ALL của chúng tôi cho thấy điều đó tiết kiệm được những gì.
• Tránh DISTINCT không cần thiết. Nó thường ẩn một phép nối tạo ra các bản sao.
• Sử dụng EXISTS để kiểm tra sự tồn tại. Quá trình này có thể dừng ở kết quả khớp đầu tiên, như hướng dẫn của chúng tôi về Truy vấn con SQL giải thích.
• Theo dõi N+1 truy vấn. Một truy vấn trên mỗi hàng trong một vòng lặp, thường do mã ứng dụng gây ra, thường là vấn đề thực sự. Một truy vấn nối hoặc theo đợt có thể thay thế hàng trăm chuyến đi khứ hồi.

Tham gia

Lập chỉ mục các cột bạn tham gia. Chúng tôi đã chạy báo cáo tổng chi tiêu cho khách hàng ở một thành phố và kế hoạch này chính xác là những gì bạn mong đợi:

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

Nó quét khách hàng, sau đó sử dụng chỉ mục để tìm đơn hàng của từng người. Nếu không có chỉ mục trên orders.customer_id, cơ sở dữ liệu sẽ phải thực hiện nhiều công việc hơn cho mỗi khách hàng. Chỉ mục trên khóa ngoại là một trong những bổ sung phổ biến và có giá trị nhất mà bạn có thể thực hiện. Để làm mới kiến ​​thức cơ bản của bạn, hãy xem Giải thích về các phép nối SQL.

Khi các chỉ số khiến bạn tốn kém

Các chỉ mục không miễn phí, vì vậy đừng thêm một chỉ mục cho mỗi cột:

• Ghi chậm hơn. Mọi thao tác chèn, cập nhật và xóa cũng phải cập nhật từng chỉ mục.
• Thêm dung lượng lưu trữ và bộ nhớ. Chỉ mục có thể phát triển lớn trên các bảng lớn.
• Ít lợi ích đối với các cột không chọn lọc. Chỉ mục trên cột đúng hoặc sai có thể không hữu ích nếu hầu hết các hàng khớp nhau.
• Dự phòng. Chỉ mục trên (a) thường không cần thiết nếu bạn đã có chỉ mục trên (a, b).

Kiểm tra số liệu thống kê sử dụng định kỳ và xóa các chỉ mục không sử dụng. Và giữ cho số liệu thống kê luôn mới với ANALYZE hoặc tương đương với cơ sở dữ liệu của bạn, vì trình tối ưu hóa dựa vào chúng để chọn một kế hoạch tốt. Đôi khi phần chậm là sự tranh chấp hơn là truy vấn. Các giao dịch giữ khóa trong thời gian dài có thể khiến truy vấn nhanh có vẻ chậm.

Những lỗi thường gặp

• Tối ưu hóa mà không đo lường. Nhiều dự đoán tự tin hóa ra lại sai.
• Thử nghiệm với dữ liệu nhỏ. Các kế hoạch và thời gian thay đổi đáng kể khi số lượng bảng tăng lên.
• Tin tưởng vào mẹo cho cơ sở dữ liệu khác. Lời khuyên cho một công cụ có thể không áp dụng cho công cụ khác.
• Thêm chỉ mục một cách mù quáng. Mỗi thao tác đều làm chậm quá trình ghi.
• Bỏ qua hiệu ứng ở lần chạy đầu tiên. Bộ nhớ đệm làm cho lần chạy thứ hai nhanh hơn, vì vậy hãy chạy nhiều lần.

Lời kết

Hầu hết các truy vấn chậm đều có nguyên nhân thông thường: thiếu chỉ mục, hàm trên một cột hoặc quá nhiều dữ liệu được tìm nạp. Hãy thực hiện truy vấn thực sự chậm nhất của bạn trong tuần này, chạy EXPLAIN trên đó và tìm kiếm bản quét toàn bộ trên một bảng lớn. Hãy sửa một thứ, đo lại và ghi chú những gì bạn đã thay đổi.

Câu hỏi thường gặp

Tiếp tục học hỏi