Tối ưu truy vấn SQL: 07 cách cải thiện hiệu quả

Với lượng dữ liệu ngày càng tăng, chúng ta thường phải đối mặt với việc viết những câu lệnh phức tạp để truy xuất thông tin. Những câu lệnh chậm có thể gây ra tắc nghẽn, ảnh hưởng đến hiệu suất ứng dụng và trải nghiệm người dùng. Việc tối ưu hóa các câu lệnh SQL giúp cải thiện hiệu suất, giảm thiểu việc sử dụng tài nguyên và đảm bảo khả năng mở rộng.

Trong bài viết này, chúng ta sẽ tìm hiểu một số kỹ thuật tối ưu hóa câu lệnh SQL hiệu quả, phân tích ưu và nhược điểm của từng phương pháp để hiểu rõ hơn tác động của chúng đến hiệu suất của câu lệnh SQL.

1. Sử dụng chỉ mục (Index) đúng cách

Hãy tưởng tượng bạn đang tìm một cuốn sách trong thư viện mà không có danh mục. Bạn sẽ phải kiểm tra từng kệ sách và từng dãy sách cho đến khi tìm được cuốn sách mình cần. Chỉ mục trong cơ sở dữ liệu giống như danh mục trong thư viện, giúp bạn nhanh chóng tìm ra dữ liệu cần mà không phải quét qua toàn bộ bảng.

1.1. Cách thức hoạt động của Chỉ Mục

Chỉ mục là cấu trúc dữ liệu giúp tăng tốc độ truy xuất thông tin. Nó hoạt động bằng cách tạo một bản sao đã được sắp xếp của các cột được chỉ mục, giúp cơ sở dữ liệu nhanh chóng tìm ra những dòng dữ liệu phù hợp với truy vấn, tiết kiệm rất nhiều thời gian.

Có ba loại chỉ mục chính trong cơ sở dữ liệu:

• Chỉ mục phân cụm (Clustered indexes): Sắp xếp dữ liệu dựa trên giá trị của cột và thường dùng cho dữ liệu có tính tuần tự hoặc đã được sắp xếp, không có dữ liệu trùng lặp, như khóa chính (primary key).

• Chỉ mục không phân cụm (Non-clustered indexes): Tạo ra hai cột riêng biệt, thích hợp cho bảng ánh xạ hoặc từ điển.

• Chỉ mục văn bản đầy đủ (Full-text indexes): Dùng để tìm kiếm trong các trường văn bản lớn như bài viết hoặc email, thông qua việc lưu trữ vị trí của các từ trong văn bản.

Để sử dụng chỉ mục nhằm cải thiện hiệu suất của các câu truy vấn SQL, dưới đây là một số cách hiệu quả:

• Tạo chỉ mục cho các cột thường xuyên được truy vấn: Nếu bạn thường xuyên tìm kiếm bảng theo các cột như customer_id hoặc item_id, việc tạo chỉ mục cho những cột này sẽ giúp tăng tốc truy vấn rất nhiều. Dưới đây là cách tạo chỉ mục:

Cách tạo chỉ mục (Nguồn: Internet)

• Tránh sử dụng chỉ mục không cần thiết: Mặc dù chỉ mục rất hữu ích trong việc tăng tốc các câu truy vấn SELECT, nhưng chúng có thể làm chậm các thao tác INSERT, UPDATE và DELETE một chút. Điều này xảy ra vì mỗi khi bạn thay đổi dữ liệu, chỉ mục cần được cập nhật. Vì vậy, quá nhiều chỉ mục có thể làm chậm mọi thứ bằng cách gia tăng tải công việc khi thay đổi dữ liệu.

• Chọn loại chỉ mục phù hợp: Các cơ sở dữ liệu khác nhau cung cấp các loại chỉ mục khác nhau. Chúng ta nên chọn loại chỉ mục phù hợp nhất với dữ liệu và mô hình truy vấn của mình. Ví dụ, chỉ mục B-tree là lựa chọn tốt nếu chúng ta thường xuyên tìm kiếm các phạm vi giá trị.

2. Tránh sử dụng SELECT *

Đôi khi, ta có thể sẽ muốn dùng SELECT * để lấy tất cả các cột, kể cả những cột không cần thiết cho phân tích của mình. Mặc dù có vẻ nhanh gọn, nhưng cách này lại làm truy vấn trở nên kém hiệu quả và có thể làm giảm tốc độ.

Cơ sở dữ liệu phải đọc và chuyển tải nhiều dữ liệu hơn mức cần thiết, khiến bộ nhớ phải sử dụng nhiều hơn vì máy chủ cần phải xử lý và lưu trữ thông tin không cần thiết.

Vì vậy, một nguyên tắc chung là chỉ chọn những cột thật sự cần thiết. Cách này không chỉ giúp mã nguồn của bạn sạch sẽ và dễ hiểu mà còn giúp tối ưu hiệu suất.

Thay vì viết thế này:

Thì sẽ viết thành:

Xem thêm:

• Bí kíp chinh phục SQL: Cẩm nang toàn tập cho Data Analyst

• Excel có đang dần xâm chiếm đời sống của bạn?

• SQL - Nền tảng cơ sở dữ liệu quan trọng cho sự nghiệp của bạn

3. Tránh Lấy Dữ Liệu Thừa hoặc Không Cần Thiết

Chúng ta đã bàn về việc chỉ chọn những cột dữ liệu cần thiết để tối ưu hóa truy vấn SQL. Tuy nhiên, điều quan trọng không kém là giới hạn số lượng dòng dữ liệu cần lấy, không chỉ là cột. Khi số lượng dòng dữ liệu tăng lên, truy vấn sẽ thường chậm lại.

Chúng ta có thể sử dụng LIMIT để giới hạn số lượng dòng dữ liệu trả về. Tính năng này giúp tránh việc vô tình lấy quá nhiều dữ liệu khi chỉ cần làm việc với một vài dòng.

Hàm LIMIT rất hữu ích khi cần kiểm tra kết quả tạm thời hoặc xem lại đầu ra của một phép biến đổi mà chúng ta đang làm. Nó rất phù hợp cho việc thử nghiệm và kiểm tra cách mã nguồn hoạt động. Tuy nhiên, LIMIT có thể không thích hợp khi cần trả về toàn bộ bộ dữ liệu, như trong các mô hình dữ liệu tự động.

Dưới đây là ví dụ về cách LIMIT hoạt động:

Cách thức Hàm LIMIT hoạt động (Nguồn: Internet)

4. Tối ưu Subqueries

Trong một số trường hợp, khi viết truy vấn, chúng ta cảm thấy cần phải thực hiện lọc, tính toán, hoặc kết nối dữ liệu một cách động. Thay vì phải thực hiện nhiều truy vấn, chúng ta chỉ muốn dùng một truy vấn duy nhất.

Lúc này, subqueries có thể là giải pháp. Subqueries trong SQL là các truy vấn lồng bên trong một truy vấn khác, thường xuất hiện trong các câu lệnh SELECT, INSERT, UPDATE hoặc DELETE.

Mặc dù subqueries có thể rất mạnh mẽ và nhanh chóng, nhưng nếu không sử dụng hợp lý, chúng cũng có thể gây ảnh hưởng đến hiệu suất. Do đó, chúng ta nên hạn chế sử dụng subqueries và tuân thủ những phương pháp tối ưu sau:

• Thay thế subqueries bằng joins khi có thể: Joins thường nhanh hơn và hiệu quả hơn so với subqueries.

• Sử dụng common table expressions (CTEs) thay thế: CTE giúp chia nhỏ truy vấn thành những phần dễ đọc hơn thay vì một câu lệnh dài, giúp mã dễ hiểu và bảo trì hơn.

• Sử dụng uncorrelated subqueries: Uncorrelated subqueries là những truy vấn độc lập với truy vấn ngoài và chỉ cần thực hiện một lần, trong khi correlated subqueries được thực thi cho mỗi dòng trong truy vấn ngoài.

5. Giám sát và tối ưu hóa thống kê cơ sở dữ liệu

Việc giữ cho thống kê cơ sở dữ liệu luôn cập nhật là rất quan trọng để đảm bảo bộ tối ưu truy vấn có thể đưa ra quyết định chính xác và hiệu quả nhất về cách thực thi truy vấn.

Thống kê giúp mô tả sự phân bổ dữ liệu trong bảng (ví dụ: số lượng dòng, tần suất xuất hiện các giá trị và cách phân bố các giá trị giữa các cột). Bộ tối ưu dựa vào những thông tin này để ước tính chi phí thực thi truy vấn. Nếu thống kê bị lỗi thời, bộ tối ưu có thể chọn kế hoạch thực thi không hiệu quả, chẳng hạn như sử dụng chỉ mục sai hoặc thực hiện quét toàn bộ bảng thay vì sử dụng chỉ mục để quét, điều này sẽ làm giảm hiệu suất truy vấn.

Hầu hết các cơ sở dữ liệu đều hỗ trợ việc cập nhật thống kê tự động để đảm bảo tính chính xác. Ví dụ, trong SQL Server, cấu hình mặc định sẽ tự động cập nhật thống kê khi có thay đổi lớn về dữ liệu. Tương tự, PostgreSQL có tính năng auto-analyze, tự động cập nhật thống kê khi có một ngưỡng thay đổi dữ liệu nhất định.

Tuy nhiên, chúng ta cũng có thể cập nhật thống kê thủ công nếu việc cập nhật tự động không đủ hoặc khi cần can thiệp thủ công. Trong SQL Server, có thể dùng lệnh UPDATE STATISTICS để làm mới thống kê cho một bảng hoặc chỉ mục cụ thể, trong khi trong PostgreSQL, lệnh ANALYZE sẽ được sử dụng để cập nhật thống kê cho một hoặc nhiều bảng.

6. Tránh việc sắp xếp và nhóm dữ liệu không cần thiết

Khi làm việc với dữ liệu, chúng ta thường muốn sắp xếp và nhóm dữ liệu để dễ dàng tìm ra thông tin hữu ích. Thông thường, chúng ta sử dụng ORDER BY và GROUP BY trong các câu truy vấn SQL.

Tuy nhiên, cả hai câu lệnh này có thể gây tốn tài nguyên tính toán, đặc biệt khi làm việc với các bộ dữ liệu lớn. Khi sắp xếp hay tổng hợp dữ liệu, cơ sở dữ liệu sẽ phải quét toàn bộ dữ liệu, rồi sắp xếp, nhóm lại và/hoặc áp dụng các hàm tổng hợp, thường sử dụng các thuật toán rất tốn tài nguyên.

Để tối ưu hóa câu truy vấn, chúng ta có thể áp dụng một số mẹo sau:

• Giảm việc sắp xếp: Chỉ sử dụng ORDER BY khi thực sự cần thiết. Nếu không cần sắp xếp, bỏ qua câu lệnh này sẽ giúp giảm đáng kể thời gian xử lý.

• Dùng chỉ mục: Khi có thể, hãy chắc chắn rằng các cột trong ORDER BY và GROUP BY đã được lập chỉ mục.

• Đẩy việc sắp xếp lên ứng dụng: Nếu có thể, thay vì làm việc này trong cơ sở dữ liệu, chúng ta có thể chuyển việc sắp xếp lên lớp ứng dụng.

• Tổng hợp dữ liệu từ trước: Với các câu truy vấn phức tạp có GROUP BY, ta có thể tổng hợp dữ liệu sẵn từ trước hoặc sử dụng materialized view để cơ sở dữ liệu không phải tính toán lại cùng một phép tổng hợp nhiều lần.

Xem thêm:

• SQL vs Excel: Cuộc chiến phân tích dữ liệu

• Phân tích dữ liệu với SPSS: Khám phá thế giới thống kê hiện đại

• Business Analyst và những điều cần biết

Trong bài viết này, chúng ta đã tìm hiểu các chiến lược và cách tối ưu hóa câu truy vấn SQL, từ việc lập chỉ mục và kết nối dữ liệu đến các truy vấn con và các tính năng đặc thù của từng cơ sở dữ liệu. Áp dụng những kỹ thuật này giúp bạn cải thiện đáng kể hiệu suất của các câu truy vấn, giúp cơ sở dữ liệu chạy mượt mà hơn.

Hãy nhớ rằng, tối ưu hóa câu truy vấn SQL là một công việc cần thực hiện liên tục. Khi dữ liệu của bạn ngày càng lớn và ứng dụng phát triển, bạn sẽ cần thường xuyên theo dõi và tối ưu hóa các câu truy vấn để chúng luôn hoạt động với hiệu suất tốt nhất.