Đánh chỉ mục hiệu quả khi sử dụng PostgreSQL

Hệ quản trị cơ sở dữ liệu PostgreSQL có nhiều loại chỉ mục - index khác nhau, cũng như các cách sử dụng chỉ mục khác nhau. Qua bài viết này, tôi muốn đưa ra một góc nhìn tổng quan về các loại chỉ mục hiện tại đang được PostgreSQL hỗ trợ, và giải thích cách sử dụng khác nhau của loại chỉ mục phổ biến nhất - B-Tree.

Sử dụng chỉ mục là một cách hiệu quả để truy xuất số lượng tương đối nhỏ các bản ghi từ bảng dữ liệu. Nó sẽ chỉ hữu ích khi số lượng các bản ghi được truy vấn ra từ một bảng không quá lớn (ví dụ, truy vấn bản ghi theo điều kiện - mệnh đề WHERE). Chỉ mục B-Tree sẽ rất hữu ích khi ta muốn tránh phải sắp xếp lại các bản ghi đã được lựa chọn.

1. Các loại chỉ mục:

Postgres hỗ trợ các loại chỉ mục sau:

• B-Tree: Hầu như, tất cả các cơ sở dữ liệu hiện nay đều hỗ trợ loại chỉ mục B-Tree, và trong hệ quản trị cơ sở dữ liệu PostgreSQL thì B-Tree là chỉ mục mặc định nếu như thực hiện câu lệnh truy vấn tạo chỉ mục mặc định CREATE INDEX. Chữ B trong B-Tree là viết tắt của từ Balanced - nghĩa là cân bằng, và ý tưởng của thuật toán là số lượng dữ liệu trên cả nhánh của cây dữ liệu sẽ tương đối bằng nhau. Do đó, số lần phải thực hiện tìm bản ghi ở hai bên luôn luôn giống nhau. Các câu truy vấn bằng hay truy vấn phạm vi sẽ rất hiệu quả nếu kết hợp sử dụng cùng với chỉ mục B-Tree, chúng hoạt động với tất cả các kiểu dữ liệu và cả giá trị NULL. Loại chỉ mục B-Tree được thiết kế để có thể làm việc tốt với cơ chế cache, thậm chí ngay cả khi chỉ cache một phần của dữ liệu.

• Hash: Chỉ mục Hash chỉ hữu ích khi truy vấn so sánh bằng, nhưng có thể bạn sẽ không bao giờ sử dụng đến loại chỉ mục này vì nó không an toàn, bạn sẽ cần phải tự đánh lại chỉ mục nếu như có lỗi xảy ra nhưng các đối tượng liên quan sẽ không được thông báo về sự thay đổi này, vì vậy ưu điểm của loại chỉ mục Hash so với loại chỉ mục B-Tree là không đáng kể.

• GIN - Generalized Inverted Indexes: Loại chỉ mục này sẽ hữu ích khi một chỉ mục phải ánh xạ tới nhiều giá trị trong cùng một bản ghi, trong khi đó, chỉ mục B-Tree lại chỉ được tối ưu hóa nếu như một bản ghi chỉ có một giá trị khóa duy nhất. Chỉ mục GIN cũng rất hữu ích trong việc đánh chỉ mục các giá trị là mảng, hoặc khi phải thực hiện các truy vấn là full-text search.

• GiST - Generalized Search Tree: Loại chỉ mục này cho phép bạn xây dựng cấu trúc cây cân bằng chung, và có thể được sử dụng cho nhiều loại so sánh ngoài hai loại so sánh bằng và so sánh phạm vi.Chỉ mục này cũng được sử dụng để đánh chỉ mục cho các kiểu dữ liệu hình học, cũng như thực hiện full-text search.

Trong bài viết này, chúng ta sẽ chỉ bàn luận về cách vận dụng tối đa loại chỉ mục mặc định B-Tree, các ví dụ sử dụng hai loại chỉ mục GIN và GiST các bạn có thể tham khảo thêm tại đây.

2. Tại sao câu truy vấn của bạn không sử dụng chỉ mục?

Có rất nhiều lí do để Postgres lựa chọn không cần phải sử dụng đến chỉ mục, và hầu hết tất cả lựa chọn đều đúng ngay cả khi không có bất kì một lí do rõ ràng nào. Với các câu truy vấn tương tự nhau, hoàn toàn có thể xảy ra trường hợp một câu truy vấn có sử dụng chỉ mục để tra cứu bảng, nhưng trong trường hợp khác thì lại không. Số lượng bản ghi được truy xuất ra từ một bảng có thể thay đổi dựa trên điều kiện mà câu truy vấn sử dụng. Ví dụ, một câu truy vấn như sau select * from foo where bar = 1; có thể sử dụng cơ chế tra cứu theo chỉ mục, nhưng với một câu truy vấn tương tự như vậy thì lại là không, select * from foo where bar = 2;, lí do là vì với điều kiện bar = 2 sẽ truy vấn ra nhiều bản ghi hơn. Thực tế, khi điều này xảy ra, tra cứu tuần tự lại nhanh hơn nhiều so với tra cứu theo chỉ mục, vì vậy thực tế là Postgres đã đánh giá chính xác chi phí thực hiện truy vấn trong trường hợp này.

3. Chỉ mục một phần - Partial Index

Chỉ mục một phần sẽ chỉ bao quát một tập con dữ liệu của một bảng, đây là loại chỉ mục có mệnh đề WHERE trong câu truy vấn đánh chỉ mục. Ý tưởng là mong muốn tăng hiệu quả khi sử dụng chỉ mục và giảm kích thước lưu trữ dữ liệu của chúng. Chỉ mục nhỏ hơn sẽ tốn ít bộ nhớ hơn, dễ dàng bảo trì và quét dữ liệu nhanh hơn.

Ví dụ: Giả sử bạn có một trang web, và bạn cho phép người dùng cắm cờ các nhận xét trên trang web của bạn bằng cách đặt một giá trị biến là flagged có kiểu dữ liệu boolean là true. Sau đó, bạn muốn xử lý các nhận xét đã bị gắn cờ, để làm điều này bạn có thể đánh chỉ mục như sau:

CREATE INDEX articles_flagged_created_at_index ON articles(created_at) WHERE flagged IS TRUE;

Câu truy vấn trên sẽ tạo ra một chỉ mục có kích thước dữ liệu tương đối nhỏ, và chỉ mục này cũng có thể được sử dụng cùng với những chỉ mục khác trong một câu truy vấn phức tạp hơn cần sử dụng đến nó.

4. Chỉ mục biểu thức - Expression Index

Loại chỉ mục biểu thức sẽ có ích đối với các câu truy vấn phù hợp với một số hàm SQL hoặc sự thay đổi dữ liệu của bạn. Postgres cho phép bạn đánh chỉ mục kết quả của các hàm SQL vì vậy việc tìm kiếm sẽ có hiệu quả tương tự như là đang tìm kiếm với các dữ liệu ban đầu. Ví dụ, bạn muốn người dùng lưu địa chỉ email để thực hiện chức năng đăng nhập, và bạn muốn xác thực email này khi đăng nhập. Trong trường hợp đó, nếu địa chỉ email hợp lệ và có thể lưu trữ, nhưng khi xác thực bạn mong muốn tìm kiếm sẽ thực hiện với điều kiện WHERE lower(email) = '<email - chữ viết thường>'. Cách duy nhất để sử dụng chỉ mục trong câu truy vấn như vậy là sử dụng một chỉ mục biểu thức như sau:

CREATE INDEX users_lower_email ON users(lower(email));

Một ví dụ phổ biến khác là tìm kiếm các bản ghi theo một ngày nhất định, trong khi chúng ta lưu trữ dữ liệu của trường ngày tháng theo kiểu dữ liệu là timestamps, nhưng mong muốn chỉ cần tìm những bản ghi có dữ liệu ngày tháng phù hợp. Một câu truy vấn tìm kiếm các bản ghi phù hợp với điều kiện tìm kiếm có chứa WHERE date(articles.published_at) = date('2017-06-26') có thể sử dụng được chỉ mục sau:

CREATE INDEX articles_day ON articles ( date(published_at) );

5. Chỉ mục Unique - Unique Index

Một chỉ mục Unique sẽ đảm bảo một bảng sẽ không thể có nhiều hơn một bản ghi có dữ liệu giống nhau. Có hai lý do để sử dụng chỉ mục Unique đó là: tính toàn vẹn dữ liệu và hiệu năng. Việc tra cứu dữ liệu có sử dụng chỉ mục Unique sẽ rất nhanh.

Về mặt đảm bảo tính toàn vẹn dữ liệu, thực hiện các thao tác kiểm tra dữ liệu ở tầng code bên trên thực sự không đảm bảo rằng dữ liệu khi được thêm vào cơ sở dữ liệu là duy nhất, bởi vì có thể và sẽ là do những người dùng đồng thời tạo ra các bản ghi không hợp lệ. Do đó, bạn nên luôn tạo ra các ràng buộc ở tầng cơ sở dữ liệu - hoặc là sử dụng một chỉ mục Unique hoặc là sử dụng một ràng buộc unique.

Có rất ít khác biệt giữa chỉ mục Unique và ràng buộc unique. Chỉ mục Unique có thể được coi là ở cấp độ thấp hơn so với các chỉ mục biểu thức và chỉ mục một phần, vì các chỉ mục biểu thức và chỉ mục một phần không thể được tạo ra như những ràng buộc unique. Ngay cả khi, việc đánh chỉ mục một phần duy nhất dựa trên biểu thức là khả thi.

6. Chỉ mục đa cột - Multi-column Index

Mặc dù Postgres có khả thể tạo ra chỉ mục multi-column, nhưng điều quan trọng ở đây là phải hiểu được khi nào thì nên làm như vậy. Postgres có khả năng kết hợp và sử dụng các chỉ mục đơn bên trong một truy vấn đa cột bằng cách thực hiện tra cứu chỉ mục bitmap. Nói chung, bạn có thể đánh chỉ mục trên mỗi cột cho tất cả các điều kiện truy vấn và trong hầu hết mọi trường hợp Postgres sẽ sử dụng những chỉ mục này. Vì vậy, hãy chắc chắn là bạn đã đánh giá và chứng minh được việc đánh chỉ mục đa cột là hợp lí trước khi thực hiện việc này. Các chỉ mục đều đi kèm với một chi phí và chỉ mục đa cột chỉ có thể tối ưu cho các câu truy vấn nếu như thứ tự các cột tham chiếu đúng theo thứ tự bên trong chỉ mục, trong khi đó, chỉ mục đơn lại cung cấp hiệu suất cho một số lượng lớn các câu truy vấn khác nhau.

Tuy nhiên, trong nhiều trường hợp việc đánh chỉ mục đa cột rõ ràng có nhiều ý nghĩa hơn. Chỉ mục trên hai cột (a, b) có thể được sử dụng trong câu truy vấn có điều kiện WHERE a = x AND b = y, hoặc chỉ có WHERE a = x, nhưng với điều kiện WHERE b = y thì lại là không. Vì vậy, nếu điều này phù hợp với các truy vấn trong ứng dụng của bạn thì sử dụng phương pháp đánh chỉ mục đa cột có thể được xem xét lại.

7. Sắp xếp tập bản ghi với chỉ mục B-Tree

Theo mặc định, chỉ mục B-Tree được sắp xếp theo thứ tự tăng dần. Trong một vài trường hợp, đây là cách cung cấp một cách sắp xếp khác cho một chỉ mục. Hãy xem xét trường hợp khi bạn hiển thị một danh sách các bài viết, và sắp xếp bài đăng theo ngày xuất bản mới nhất lên đầu tiên. Chúng ta có một trường là published_at trong bảng articles. Đối với các bài viết chưa được xuất bản thì trường published_at sẽ nhận giá trị NULL.

Trong trường hợp này, ta có thể tạo ra một chỉ mục như sau:

CREATE INDEX articles_published_at_index ON articles(published_at DESC NULLS LAST);

Vì ta sẽ thực hiện truy vấn bảng với thứ tự được sắp xếp theo trường published_at và sẽ giới hạn số lượng kết quả truy vấn, nên ta sẽ nhận được một chút lợi ích từ việc đánh chỉ mục có cách sắp xếp tương tự. Postgres sẽ tìm kiếm các bản ghi nó cần từ các chỉ mục theo đúng thứ tự và sau đó sẽ đến khối dữ liệu để lấy kết quả. Nếu chỉ mục không được sắp xếp thì Postgres sẽ thực hiện tra cứu tuần tự và sắp xếp lại kết quả.

Kĩ thuật này chủ yếu phù hợp với chỉ mục đơn khi bạn cần sắp xếp các giá trị NULL ở cuối cùng của tập kết quả, đây cũng là thứ tự mặc định vì chỉ mục có thể được quét từ bất kì hướng nào. Điều này càng khẳng định sử dụng chỉ mục đơn trở nên phù hợp hơn là sử dụng chỉ mục đa cột khi câu truy vấn yêu cầu cùng một lúc nhiều trật tự sắp xếp, giống như a ASC, b DESC.

8. Quản lý và bảo trì chỉ mục

Trong Postgres, các chỉ mục không chứa toàn bộ dữ liệu của bản ghi. Ngay cả khi một chỉ mục được sử dụng trong một câu truy vấn và tìm ra các bản ghi phù hợp, Postgres sẽ vẫn lấy dữ liệu của bản ghi từ đĩa cứng. Ngoài ra, các thông tin về khả năng hiển thị bản ghi (đã nói qua trong bài viết MVCC) cũng không được lưu trữ trên chỉ mục, do đó Postgres cũng phải lấy những thông tin này từ đĩa cứng.

Hãy ghi nhớ, trong một số trường hợp việc sử dụng chỉ mục không thực sự có ý nghĩa. Việc đánh chỉ mục như thế nào cần phải được lựa chọn kĩ càng để giảm số lần phải tra cứu từ đĩa cứng, mang lại đúng giá trị khi sử dụng nó. Ví dụ, tra cứu khóa chính trong một bảng dữ liệu lớn tốt hơn khi sử dụng cùng với chỉ mục, thay vì thực hiện tra cứu tuần tự để tìm bản ghi phù hợp với điều kiện của câu truy vấn, Postgres có thể tìm các bản ghi mục tiêu trong chỉ mục, và sau đó lấy chúng ra từ đĩa cứng đã được lựa chọn. Đối với bảng có dữ liệu nhỏ, ví dụ tra cứu bảng thông tin các thành phố - cities, chỉ mục có thể không cần thiết, thậm chí nếu bạn thực hiện tìm kiếm theo tên thành phố. Trong trường hợp này, Postgres có thể quết định bỏ qua chỉ mục và sử dụng quét tuần tự. Postgres có thể quyết định thực hiện quét tuần tự với bất kì câu truy vấn nào mà cho ra số lượng kết quả đủ lớn. Nếu bạn đánh chỉ mục trên cột đó, có thể nó sẽ không bao giờ được sử dụng đến, trong khi chúng vẫn tiêu tốn chi phí lưu trữ và bảo trì.

Khi bạn áp dụng đánh chỉ mục cho cơ sở dữ liệu của bạn trên môi trường Production, hãy nhớ là việc đánh chỉ mục sẽ khiến khả năng ghi dữ liệu của bảng bị chậm đi. Với các bảng có dữ liệu lớn thì điều này có thể khiến hệ thống bị sập trong nhiều giờ. May mắn là Postgres cho phép bạn đánh chỉ mục đồng thời, CREATE INDEX CONCURRENTLY, việc này sẽ tốn nhiều thời gian hơn để thực hiện nhưng sẽ không phải khóa Write. Thông thường, lệnh CREATE INDEX sẽ khóa Write, nhưng Read thì không.

Và cuối cùng, chỉ mục sẽ bị phân mảnh và không được tối ưu hóa sau một thời gian sử dụng, đặc biệt khi các bản ghi trong bảng thường xuyên bị cập nhật hoặc xóa. Khi gặp trường hợp như vậy, cần phải thực hiện câu truy vấn REINDEX để tạo lại chỉ mục tối ưu và cân bằng nhất. Tuy nhiên, hãy thận trọng khi đánh lại các chỉ mục lớn cho đến khi các bảng liên quan bị khóa khả năng ghi dữ liệu. Một giải pháp khác cũng có thể cho kết quả tương tự đó là tạo chỉ mục song song trên cùng một bảng hoặc cùng một nhóm các cột, nhưng với tên chỉ mục khác nhau, sau đó xóa bỏ chỉ mục gốc và đổi tên chỉ mục dự bị. Với phương pháp này sẽ không cần phải thực hiện khóa các bảng.