Thanaoscar

Bài 19: Tối ưu Query với Indexed Views

Thana Oscar's photo
Thana Oscar
·

6 min read

Indexed views (hay materialized views) là một công cụ mạnh mẽ trong SQL, cho phép lưu trữ kết quả của một truy vấn dưới dạng một bảng vật lý được đánh chỉ mục. Chúng giúp cải thiện đáng kể hiệu suất của các truy vấn phức tạp bằng cách giảm thiểu việc tính toán lại kết quả. Tuy nhiên, việc sử dụng indexed views cần được cân nhắc kỹ lưỡng để tránh các vấn đề về hiệu suất và quản lý dữ liệu. Bài viết này sẽ đi sâu vào khái niệm indexed views, cách tạo và sử dụng chúng, và khi nào nên sử dụng indexed views.

19.1. Indexed Views là gì?

Indexed views là các views được lưu trữ dưới dạng bảng vật lý và được đánh chỉ mục. Khác với regular views, indexed views lưu trữ kết quả của truy vấn cơ sở, giúp tăng tốc độ truy vấn bằng cách tránh tính toán lại kết quả mỗi lần truy vấn.

19.1.1. Cách tạo và sử dụng indexed views

  • Cú pháp tạo indexed view:

      CREATE VIEW view_name WITH SCHEMABINDING AS
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;
  CREATE UNIQUE CLUSTERED INDEX idx_name ON view_name(column1);

  • Ví dụ: Tạo một indexed view để lưu trữ tổng doanh thu theo từng phòng ban:

      CREATE VIEW SalesByDepartment WITH SCHEMABINDING AS
  SELECT d.department_id, d.department_name, SUM(s.amount) AS total_sales
  FROM departments d
  JOIN sales s ON d.department_id = s.department_id
  GROUP BY d.department_id, d.department_name;
  CREATE UNIQUE CLUSTERED INDEX idx_SalesByDepartment ON SalesByDepartment(department_id);

  • Sử dụng indexed view: Bạn có thể sử dụng indexed view như một bảng thông thường trong các truy vấn SQL.

      SELECT * FROM SalesByDepartment WHERE department_name = 'Sales';
19.1.2. Ưu và nhược điểm của indexed views

  • Ưu điểm:

    • Hiệu suất cao: Indexed views giúp tăng tốc độ truy vấn bằng cách lưu trữ kết quả của truy vấn cơ sở.

    • Giảm tải cho hệ thống: Indexed views giúp giảm số lượng tính toán cần thiết khi thực hiện các truy vấn phức tạp.

    • Tính nhất quán: Indexed views tự động cập nhật khi dữ liệu cơ sở thay đổi, đảm bảo tính nhất quán của dữ liệu.

  • Nhược điểm:

    • Chi phí lưu trữ: Indexed views yêu cầu không gian lưu trữ để lưu trữ kết quả của truy vấn.

    • Chi phí cập nhật: Khi dữ liệu cơ sở thay đổi, indexed views cần được cập nhật, điều này có thể làm chậm hiệu suất.

    • Hạn chế về tính linh hoạt: Indexed views chỉ hỗ trợ một số loại truy vấn nhất định và yêu cầu các ràng buộc cụ thể (ví dụ: WITH SCHEMABINDING).

19.2. Tối ưu query với indexed views

Indexed views có thể được sử dụng để tối ưu hóa các truy vấn phức tạp, nhưng cần được sử dụng một cách thông minh để tránh các vấn đề về hiệu suất.

19.2.1. Sử dụng indexed views để tăng tốc query

  • Vấn đề: Các truy vấn phức tạp với nhiều JOIN, GROUP BY, hoặc các phép toán tổng hợp có thể làm chậm hiệu suất.

  • Giải pháp: Sử dụng indexed views để lưu trữ kết quả của các truy vấn phức tạp, giúp tăng tốc độ truy vấn.

    • Ví dụ không tối ưu:

        SELECT d.department_id, d.department_name, SUM(s.amount) AS total_sales
  FROM departments d
  JOIN sales s ON d.department_id = s.department_id
  GROUP BY d.department_id, d.department_name;

    • Ví dụ tối ưu:

        CREATE VIEW SalesByDepartment WITH SCHEMABINDING AS
  SELECT d.department_id, d.department_name, SUM(s.amount) AS total_sales
  FROM departments d
  JOIN sales s ON d.department_id = s.department_id
  GROUP BY d.department_id, d.department_name;
  CREATE UNIQUE CLUSTERED INDEX idx_SalesByDepartment ON SalesByDepartment(department_id);

  SELECT * FROM SalesByDepartment WHERE department_name = 'Sales';
19.2.2. Tránh sử dụng indexed views không cần thiết

  • Vấn đề: Sử dụng indexed views không cần thiết có thể làm tăng chi phí lưu trữ và cập nhật, dẫn đến giảm hiệu suất tổng thể.

  • Giải pháp: Chỉ sử dụng indexed views khi thực sự cần thiết và đảm bảo rằng chúng được sử dụng một cách hiệu quả.

    • Ví dụ không tối ưu:

        CREATE VIEW EmployeeCount WITH SCHEMABINDING AS
  SELECT department_id, COUNT(*) AS employee_count
  FROM employees
  GROUP BY department_id;
  CREATE UNIQUE CLUSTERED INDEX idx_EmployeeCount ON EmployeeCount(department_id);

    • Ví dụ tối ưu:

        -- Chỉ tạo indexed view nếu truy vấn được thực hiện thường xuyên và có tác động lớn đến hiệu suất.

19.3. Khi nào nên sử dụng indexed views

19.3.1. Trường hợp phù hợp cho indexed views

  • Truy vấn phức tạp: Indexed views phù hợp cho các truy vấn phức tạp với nhiều JOIN, GROUP BY, hoặc các phép toán tổng hợp.

  • Dữ liệu ít thay đổi: Indexed views phù hợp cho các dữ liệu ít thay đổi, chẳng hạn như dữ liệu tham chiếu (reference data) hoặc dữ liệu lịch sử.

  • Truy vấn thường xuyên: Indexed views hiệu quả với các truy vấn được thực hiện thường xuyên và có kết quả không thay đổi nhiều.

19.3.2. Đánh đổi giữa indexed views và regular views

  • Indexed Views:

    • Ưu điểm: Hiệu suất cao, tự động cập nhật khi dữ liệu cơ sở thay đổi.

    • Nhược điểm: Yêu cầu không gian lưu trữ và chi phí cập nhật.

  • Regular Views:

    • Ưu điểm: Không yêu cầu không gian lưu trữ, linh hoạt hơn.

    • Nhược điểm: Hiệu suất thấp hơn do cần tính toán lại kết quả mỗi lần truy vấn.

Kết luận

Indexed views là một công cụ mạnh mẽ giúp tối ưu hóa các truy vấn phức tạp bằng cách lưu trữ kết quả của truy vấn cơ sở dưới dạng bảng vật lý được đánh chỉ mục. Bằng cách sử dụng indexed views một cách thông minh, bạn có thể cải thiện đáng kể hiệu suất của các truy vấn phức tạp. Tuy nhiên, việc sử dụng indexed views cần được cân nhắc kỹ lưỡng để tránh các vấn đề về hiệu suất và quản lý dữ liệu. Trong các bài tiếp theo, chúng ta sẽ đi sâu vào các kỹ thuật tối ưu hóa khác, chẳng hạn như sử dụng query rewrite và parallel processing.

Từ vựng chuyên môn (Glossary)

  • Indexed Views: Views được đánh chỉ mục.

  • Materialized Views: Views vật lý.

  • Regular Views: Views thông thường.

  • Schemabinding: Ràng buộc lược đồ.

  • Clustered Index: Chỉ mục cụm.

  • Query Optimization: Tối ưu hóa truy vấn.

  • Data Aggregation: Tổng hợp dữ liệu.

  • Reference Data: Dữ liệu tham chiếu.

  • Data Consistency: Tính nhất quán của dữ liệu.

  • Storage Cost: Chi phí lưu trữ.

  • Update Cost: Chi phí cập nhật.

sql optimize