Query (truy vấn) dữ liệu từ database là thuật ngữ không còn xa lạ đối với các developer, đặc biệt là các bạn làm Backend. Những câu query tốt sẽ đem lại nhiều lợi ích lâu dài như: giảm tiêu tốn tài nguyên hệ thống để tiết kiệm chi phí, tăng tốc độ phản hồi nhằm tối ưu trải nghiệm người dùng,… Bài viết hôm nay sẽ cung cấp thêm cho các bạn một số giải pháp để có được một câu query tốt hơn, hy vọng ít nhiều giúp các bạn có những bước khởi đầu cho việc tối ưu hóa một câu query.
Nội dụng bài viết sử dụng ví dụ ở 2 databases khá nổi tiếng là PostgreSQL và SQL Server (MSSQL). Các databases khác thì cũng sẽ tương tự.
Ok, bắt đầu thôi!
Phân tích câu query
Để có thể tối ưu hóa được câu query, điều đầu tiên bạn cần phải biết được câu query đó được database thực thi như thế nào. Execution plan chính là giải pháp cho việc này.
Nói đơn giản thì execution plan là biểu đồ mô tả lại quá trình mà database đã thực hiện để có thể trả về được kết quả của câu query. Quá trình sẽ có nhiều bước, mỗi bước sẽ có thông tin của đối tượng thực hiện (table/view/index…), chi phí (CPU/disk/IO…) và số kết quả trả về.
Dựa vào excution plan, chúng ta có thể biết được đâu là đối tượng chiếm nhiều thời gian và chi phí nhất để từ đó có các giải pháp tối ưu phù hợp.
Execution plan thường được thể hiện dưới dạng cây như sau:
Tuy nhiên để trực quan hơn, một công cụ cho phép thể hiện dưới dạng đồ thị với các màu thể hiện chi phí của từng giai đoạn. VD như Visual Studio (hình dưới) hoặc DBeaver Enterprice (trial):
Việc đọc hiểu plan đã có đủ tài liệu trên các trang chính thức của từng loại databases, nội dung bài viết sẽ không đề cập thêm. Sau đây sẽ đề cập tới một số vấn đề mà developer cần lưu ý để có thể có được câu query tốt nhất.
Index! Luôn phải có Index!
Để query hiệu quả thì Index sẽ luôn được nhắc đến. Bài viết sẽ không giải thích DB Index là gì, bạn có thể tự tìm hiểu thêm, rất nhiều. Chúng ta sẽ đi vào một số tips để sử dụng index hiệu quả.
Index sẽ có 2 loại: single (index 1 column) và compound (index nhiều columns). Việc sử dụng loại nào tùy vào nhu cầu query thường xuyên nhất của ứng dụng là gì.
VD: [MSSQL] Query dữ liệu chuyến luôn dựa vào 3 fields là Tuyến (BaseId), Ngày (Date) và Giờ (time) thì một Compound Index có 3 fields này sẽ là lựa chọn tốt nhất.
Với một compound index có 3 fields là ABC thì sẽ đáp ứng được các query có điều kiện là “where A” hoặc “where A & B” hoặc “where A & B & C” (không quan trọng thứ tự các điều kiện). Index này sẽ không được hit khi query điều kiện B hoặc B & C.
Tuy nhiên khi hit được một index thì không chắc câu query của bạn đạt performance tốt. Nó còn tùy vào số rows được trả về của index đó, tương ứng với số records mà DB sẽ scan trên table. Một index hiệu quả nên có số rows trả về ổn định không tăng dần theo thời gian.
VD: Một nhà xe thông thường cố định số chuyến xe sẽ chạy trong một ngày. Khi cần query số chuyến xe của một nhà xe theo ngày, câu query sẽ là:
Nếu index theo CompanyId, thì số rows sẽ tăng dần theo thời gian, tức là càng ngày sẽ phải scan càng nhiều records trên table. Do đó giải pháp tốt hơn đó là nên có một Compound Index theo (CompanyId, Date). Như vậy số rows trả về sẽ ít hơn và ổn định hơn do cố chuyến xe mỗi ngày không thay đổi nhiều.
Tuy nhiên, cái gì cũng có 2 mặt của nó. Mỗi index được tạo ra đều sẽ làm chậm việc cập nhật dữ liệu bảng (như insert, update, delete). Do mỗi khi dữ liệu thay đổi, Database đều phải cập nhật lại index tương ứng. Do đó, việc tạo index cần phụ thuộc rất nhiều vào nhu cầu truy vấn của từng ứng dụng là gì, tránh các index dư thừa.
Không sử dụng "SELECT *"
Khi viết một câu SQL, rất nhiều Developer thường sử dụng “select *” với một số lý do như: câu query ngắn gọn, sau này có thêm field trong table thì cũng không cần sửa lại câu query,… Tuy nhiên điều này sẽ dẫn đến việc DB sẽ phải query rất nhiều data, bao gồm cả những data không cần thiết, đặc biệt khi table có rất nhiều fields nhưng App chỉ sử dụng một vài fields trong số đó. Lãng phí tiếp theo đó là network sẽ tốn băng thông để truyền tải các dữ liệu dư từ DB tới App.
Do đó, chỉ query đúng các dữ liệu cần thiết. VD:
Ngoài ra, khi query đúng dữ liệu mình cần, trong một số trường hợp, DB chỉ cần lấy dữ liệu từ Index mà không cần scan table, lúc đó performance của câu query sẽ rất nhanh. Ví dụ:
Hạn chế các query Join quá phức tạp
Với bất kỳ câu query nào, các database system đều phải tìm cách tối ưu nhất các thực hiện câu query đó. Cụ thể, Query optimizer phải chọn một Execution plan tốt nhất trong vô vàn các plan và trong một khoảng thời gian ngắn nhất. Số lượng plan này tăng cấp giai thừa với số lượng table tham gia vào câu query.
Dựa vào cách các bảng được join với nhau thì các query có thể chia ra làm 2 loại:
Left-deep tree: bảng A join B, B join C, C join D,…. Các bảng được join lần lượt nối tiếp nhau
Bushy tree: bảng A join B, A join C, B join D, C join E,… Các bảng join không theo thứ tự nào.
Trong 2 loại trên, thì left-deep là loại có số lượng plan ít hơn rất nhiều so với kiểu bushy tree. VD cùng một câu query join 12 bảng khác nhau, thì số plan của left-deep là 479.001.600 khả năng, còn với bushy thì sẽ là 28.158.588.057.600 khả năng!!!
Một số giải pháp để tối ưu các câu query join quá nhiều bảng như sau:
Tách thành các câu queries nhỏ hơn, sau đó join kết quả các queries này lại với nhau.
Loại bỏ các joins, các bảng, các subquery không cần thiết.
Khi phải joins nhiều bảng, tìm giải pháp để có được left-deep tree.
Query với toán tử LIKE
Việc tìm kiếm chuỗi bằng toán tử LIKE sao cho tối ưu nhất không phải là bài toán đơn giản. Và các cơ sở dữ liệu quan hệ cũng không được thiết kế tối ưu nhất cho việc tìm kiếm chuỗi. Tuy nhiên trong một số trường hợp bắt buộc, khi query với LIKE để hiệu quả, bạn cần chú ý một số điểm sau:
Luôn phải có index ở cột được query
Và đảm bảo index đó được sử dụng trong plan
Một số bạn thường lầm tưởng rằng chỉ cần có index thì mọi query đều sẽ hiệu quả. Tuy nhiên tùy vào loại database mà bạn đang dùng và có thể cả cách bạn để dấu “%” trong câu query thì index mới được sử dụng hay không.
Ví dụ, đối với database SQL server, khi query LIKE mà toán tử % không nằm ở đầu thì vẫn hit được index.
Còn đối với database Postgres, index kiểu B-tree sẽ không hit được cho query like, mà phải sử dụng một số loại khác để thay thế như GIN/GIST…
Trong trường hợp không thể sử dụng bất kỳ index nào, cách khác để tối ưu với query LIKE đó là bổ sung thêm các điều kiện khác để giới hạn lại số records mà phải tìm kiếm phần chuỗi, từ đó tăng hiệu suất của câu query.
Kết luận
Trên đây chỉ là một số cách để các bạn lưu ý khi viết câu query sao cho hiệu quả. Tuy nhiên, điều cốt lõi vẫn là “biết người biết ta bách chiến bách thằng”, hiểu được câu query của bạn được thực thi thế nào (execution plan) thì bạn có thể còn vô số cách khác nữa để tối ưu nó.
Chúc bạn dev vui! Và không làm sập database nhé! ^^