🪴ttuan's garden

3 min read

Nested Loop Join - JOIN: Phân tách và kết hợp

Cách phổ biến nhất database thực thi join: hoạt động y hệt vòng lặp for-each. Hiểu điều này thì join không còn phức tạp: join = hai query độc lập, một chạy 1 lần (driving table), một chạy N lần trong loop (driven table). Mỗi query cần index riêng.

SELECT employee.* FROM employee
JOIN department USING(department_id)
WHERE employee.salary > 100000 AND department.country = 'NR';
# Database thực thi giống pseudo-code này:
results_employee = SELECT * FROM employee WHERE salary > 100000  # driving
for emp in results_employee:
    result = SELECT * FROM department                            # driven
             WHERE department_id = emp.department_id AND country = 'NR'
    if result: output(emp)
-- Index cho driving table (employee):
CREATE INDEX idx_emp_salary ON employee (salary);
-- Index cho driven table (department):
-- department_id đến từ join condition, country từ WHERE
CREATE INDEX idx_dept ON department (department_id, country);

Thứ tự JOIN không cố định - và vì sao điều đó quan trọng

SQL là declarative language: bạn nói muốn gì, database tự quyết làm thế nào. Thứ tự viết JOIN ≠ thứ tự thực thi.

employee: 10,000 rows, 511 rows salary > $100k
department: 500 rows, 2 departments ở Nauru (country='NR')
 
Approach 1 (employee first): 511 lookups vào department → ~512 operations
Approach 2 (department first): 2 lookups vào employee   → ~3 operations
→ Approach 2 nhanh hơn 170 lần!

Database chỉ chọn approach 2 nếu: (1) statistics cho thấy ‘NR’ match ít rows, (2) index phù hợp tồn tại cho reverse lookup. Nếu bạn chỉ tạo index cho approach 1, database buộc phải dùng approach chậm.

Quy tắc: Luôn tạo index hỗ trợ join theo MỌI thứ tự có thể:

-- Approach 1 (employee → department):
CREATE INDEX ON employee (salary);
CREATE INDEX ON department (department_id, country);
-- Approach 2 (department → employee):
CREATE INDEX ON department (country);
CREATE INDEX ON employee (department_id, salary);

Số lượng bảng join ảnh hưởng optimizer

Join 2 bảng → 2 thứ tự. Join 4 bảng → 24 thứ tự × số index = rất nhiều permutations. Database không kiểm tra hết → có thể chọn plan không tối ưu. Giữ số bảng join dưới 6-8 cho performance dự đoán được.

Liên quan

Graph View

Backlinks