Một số nguyên nhân làm chậm câu truy vấn trong Postgresql

Database là thành phần quan trọng không thể thiếu trong các trang web và ứng dụng, nó đồng thời là cốt lõi của cách dữ liệu được lưu trữ và trao đổi trên internet. Truy xuất dữ liệu từ database là một phần quan trọng không thể thiếu khi quản lý database. Cách phổ biến nhất để lấy thông tin trong ngôn ngữ truy vấn có cấu trúc (SQL) từ database là thông qua câu lệnh SELECT.

Một câu truy vấn không hiệu quả sẽ gây ảnh hưởng tới tài nguyên của database, gây chậm hiệu suất và ảnh hưởng tới trải nghiệm của người dùng. Việc tối ưu hóa câu truy vấn là rất quan trọng và luôn cần thiết mà mọi lập trình viên cần ghi nhớ. Giả sử câu truy vấn bạn có thể tối ưu từ 2s xuống còn 0.2s, nghe cũng không nhiều nhỉ tuy nhiên nếu trong một ngày câu truy vấn đó được gọi 10000 lần nhân với 365 ngày thì dễ dàng thấy ta đã tiết kiệm được một khoảng thời gian và tiền bạc không hề nhỏ. Vì vậy trong bài viết này mình sẽ chia sẽ với mọi người một số nguyên nhân làm chậm câu truy vấn trong Postgresql và cách cải thiện, bắt đầu nào

1. SELECT quá nhiều field không cần thiết khi query

Thường thì ta dùng query SELECT * để lấy tất cả những trường của 1 table nào đó. Tuy nhiên nếu table có quá nhiều column thì việc sử dụng query SELECT * sẽ dẫn tới việc lấy phải những data không cần thiết Ví dụ về việc lấy data email, cell_phone của user

• Không hiệu quả:

SELECT * FROM users

• Hiệu quả:

SELECT email, cell_phone FROM users

2. Sử dụng CROSS JOIN Ví dụ:

SELECT users.userId, users.name, products.productName
FROM users, products
WHERE users.userId = products.userId

CROSS JOIN hay Cartesian Join sẽ nối lần lượt mỗi bản ghi của bảng thứ nhất với bảng thứ hai. Ở ví dụ trên, nếu table users có 1000 bản ghi và table products có 1000 bảng ghi thì kết quả trả về sẽ gồm 1 triệu bản ghi. Việc này gần như rất dư thừa và sẽ tiêu tốn rất nhiều tài nguyên. Ở ví dụ trên có thể sửa thành INNER JOIN như sau:

SELECT users.userId, users.name, products.productName
FROM users INNER JOIN products
ON users.userId = products.userId

Một số hệ thống DBMS (Database Management System) sẽ detect được WHERE joins và tự động chuyển thành INNER JOIN khi chạy, tuy nhiên mọi người nên sử dụng cách 2 để tạo được sự đồng bộ và dễ hiểu vì INNER JOIN được sử dụng trong tất cả hệ thống DBMS.

3. Hạn chế sử dụng LIKE

Từ khóa LIKE dùng khi tìm kiếm cho khả năng tìm ở phạm vi rộng nhất, nhưng cũng chính vì thế mà performance của nó không cao. Ví dụ yêu cầu tìm kiếm các tên người dùng bắt đầu với 'Ki'

SELECT id 
FROM users
WHERE name LIKE '%Ki%'

Câu truy vấn trên hoàn toàn đáp ứng được yêu cầu những đồng thời nó cũng trả về những data không mong muốn như 'Phan Kim Liên' Câu truy vấn tốt hơn sẽ là:

SELECT id 
FROM users
WHERE name LIKE 'Ki%'

4. Dùng EXISTS khi cần check sự tồn tại của 1 row dựa trên điều kiện nào đó

• Khi cần check xem trong có user nào có địa chỉ ở Đà Nẵng không
• Câu truy vấn dưới không tốt vì vẫn sẽ tiếp tục chạy mặc dù đã tìm được 1 row thỏa điều kiện mong muốn:

select count(*) from users u where u.address = 'Đà Nẵng'

Câu truy vấn sau có performance tốt hơn vì nó sẽ dừng ngay khi tìm được 1 row thỏa điều kiện mong muốn:

 select exists (select 1 from users u where u.address = 'Đà Nẵng')

5. Dùng JOIN thay vì subquery

Nếu không có sai khác về performance, theo mình nên dùng JOIN thay vì subquery để dễ hiểu hơn, đồng thời tránh cho query planner hoạt động không theo ý muốn ( cái này mọi người thấy sao )

6. Sử dụng index để tăng tốc độ truy vấn

Index là một kĩ thuật rất quen thuộc với mọi lập trình viên đặc biệt là backend, nó giúp tăng tốc độ truy vấn dữ liệu (SELECT), tuy nhiên sẽ tốn thêm bộ nhớ lưu trữ và làm chậm tốc độ UPDATE, INSERT, DELETE. Lí do vì khi chỉnh sửa 1 row thì Postgresql luôn phải tạo một bản sao mới của row đó, chứ không phải thay đổi trực tiếp trên row đang tồn tại, vì vậy index luôn được cập nhật mặc dù ta chỉ cập nhật một column không tồn tại index. Đọc thêm về HOT Update ở https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/

• Nên dùng index ở các coumn thường xuyên được truy vấn ( điều kiện trong WHERE, JOIN, ORDER BY)

• Không nên dùng index cho các table thường xuyên có update hay insert

• Không nên dùng index cho các column mà giá trị thay đổi nhiều

• Không nên dùng index khi số lượng record trong table không nhiều

• Sử dụng EXPLAIN để có cái nhìn chi tiết về câu query đang được thực thi thế nào, index nào đang được sử dụng, sử dụng thì có hiệu quả không...

  Một ví dụ đơn giản về index là lấy tất cả thông tin của user có cell_phone = '84991500000' từ table users có 1tr5 records : SELECT * FROM users u WHERE u.cell_phone = '84991500000';

• Trước khi tạo index: Câu truy vấn tốn 326ms để execute

Giờ tạo index ở table users cho column cell_phone có tên users_cell_phone_idx CREATE INDEX users_cell_phone_idx ON users(cell_phone);

• Chạy lại câu truy vấn sau khi tạo index: Thời gian execute câu truy vấn chỉ còn 2ms. Nhanh hơn hẳn đúng không nào!

Kết bài

Vậy là mình đã trình bày xong một số lỗi cơ bản làm câu truy vấn trong Postgresql bị chậm. Về index thì thực tế việc tạo index ở đâu để hoạt động như ta mong đợi không hề dễ dàng, có rất nhiều tình huống khác nhau và phức tạp tùy vào tình huống thực hiện câu truy vấn. Bài tiếp theo mình sẽ đi sâu hơn về cách EXPLAIN câu truy vấn, một số loại index thường gặp và các vấn đề thường gặp và cách tối ưu khi sử dụng index. Nếu có sai sót hay có gì cần bổ sung rất hy vọng nhận được các comment góp ý từ mọi người, cảm ơn đã theo dõi bài viết.