bởi Sawan Saxena và Yugundhar Kalikapuram | vào ngày 05 JAN 2024 |

Đây là bài đăng đầu tiên trong loạt hai bài viết. Trong bài viết này, chúng tôi sẽ thảo luận về cách di chuyển các chế độ xem có chỉ mục của SQL Server sang các chế độ xem vật liệu trong Amazon Aurora PostgreSQL-Compatible Edition hoặc Amazon Relational Database Service (Amazon RDS) cho PostgreSQL. Bài viết thứ hai trong loạt bài này sẽ hướng dẫn bạn qua quá trình di chuyển các chế độ xem có chỉ mục của SQL Server sang các chế độ xem vật liệu trong Babelfish cho Aurora PostgreSQL.

Chế độ xem có chỉ mục của SQL Server được sử dụng để cải thiện hiệu suất của các truy vấn tổng hợp dữ liệu từ nhiều bảng. Ngược lại, chế độ xem vật liệu được sử dụng trong cơ sở dữ liệu PostgreSQL để lưu trữ kết quả truy vấn và làm cho nó có sẵn để truy xuất nhanh chóng. Sự khác biệt chính là chế độ xem vật liệu sử dụng hệ thống quy tắc như các chế độ xem nhưng lưu trữ kết quả dưới dạng bảng trên đĩa, trong khi chế độ xem có chỉ mục chỉ lưu trữ định nghĩa bảng được thay thế trong truy vấn chính và việc thực thi sẽ giống như đọc trực tiếp từ những bảng này.

Việc tạo một chỉ mục gom nhóm duy nhất trên một chế độ xem có chỉ mục phức tạp có thể cải thiện đáng kể hiệu suất cho các truy vấn thường xuyên. Chế độ xem có chỉ mục lưu trữ bộ kết quả giống như một bảng, cho phép các truy vấn tirời lợi từ chỉ mục gom nhóm mà không cần truy cập trực tiếp vào chế độ xem. Chỉ mục gom nhóm duy nhất cung cấp rằng các thay đổi trong bảng nguồn sẽ được theo dõi và phản ánh nhanh chóng trong chế độ xem. Mặc dù có thể sửa đổi dữ liệu trực tiếp thông qua chế độ xem có chỉ mục, nhưng thường không được khuyến khích. Ngoài ra, có thể tạo chỉ mục không gom nhóm trên chế độ xem để tăng cường hiệu suất của truy vấn. Tuy nhiên, bạn có thể tạo chỉ mục cơ sở dữ liệu trực tiếp trên chế độ xem vật liệu của PostgreSQL và cải thiện hiệu suất của các truy vấn truy cập chế độ xem vật liệu.

Tổng quan về giải pháp

Để di chuyển các chế độ xem có chỉ mục của SQL Server sang các chế độ xem vật liệu của PostgreSQL, thực hiện các bước sau:

1. Phân tích chế độ xem có chỉ mục của SQL Server – Hiểu cấu trúc và logic của chế độ xem có chỉ mục trong SQL Server, bao gồm các bảng cơ bản, các điều kiện kết nối và các phép tổng hợp liên quan.

2. Tạo chế độ xem vật liệu trong PostgreSQL – Chuyển đổi truy vấn của chế độ xem có chỉ mục SQL Server thành một truy vấn PostgreSQL tương ứng và tạo một chế độ xem vật liệu mới bằng câu lệnh CREATE MATERIALIZED VIEW. Xác minh rằng tên cột, tên bảng và điều kiện kết nối được điều chỉnh cho cú pháp PostgreSQL.

3. Xác định chỉ mục trên chế độ xem vật liệu – Xác định các cột liên quan cần chỉ mục để cải thiện hiệu suất truy vấn. Sử dụng câu lệnh CREATE INDEX để tạo chỉ mục trên chế độ xem vật liệu, xem xét các yêu cầu cụ thể của truy vấn của bạn.

4. Thiết lập cơ chế làm mới – Xác định cách chế độ xem vật liệu sẽ được làm mới để cập nhật dữ liệu của nó. PostgreSQL hiện không cung cấp tính năng làm mới tự động cho chế độ xem vật liệu, vì vậy bạn phải làm mới chế độ xem thủ công định kỳ bằng câu lệnh REFRESH MATERIALIZED VIEW. Bạn có thể thiết lập lịch trình (sử dụng công việc cron và sử dụng tiện ích mở rộng pg_cron) hoặc các kích hoạt để khởi chạy quá trình làm mới.

5. Sửa đổi các truy vấn phụ thuộc – Xem xét và sửa đổi các truy vấn hoặc ứng dụng phụ thuộc vào chế độ xem có chỉ mục của SQL Server để sử dụng chế độ xem vật liệu mới của PostgreSQL thay thế. Cập nhật các truy vấn để tham chiếu đến chế độ xem vật liệu và điều chỉnh sự khác biệt cú pháp giữa SQL Server và PostgreSQL.

6. Kiểm thử và tối ưu – Kiểm thử kỹ lưỡng chế độ xem vật liệu di chuyển và các truy vấn phụ thuộc của nó để xác minh rằng chúng tạo ra kết quả mong đợi. Theo dõi hiệu suất truy vấn và xem xét các kỹ thuật tối ưu hóa tiếp theo, như chỉ mục bổ sung hoặc viết lại truy vấn, nếu cần thiết.

Biểu đồ sau đây mô tả các bước và quyết định quan trọng liên quan đến các đối tượng liên quan trong quá trình di chuyển này. Theo dõi dòng trực quan này để đảm bảo một chuyển động mượt, bảo toàn tính toàn vẹn của dữ liệu và các lợi ích về hiệu suất của chế độ xem vật liệu trong PostgreSQL thông qua dữ liệu được tính toán trước, giảm kích thước hàng, hàng được chọn trước và có thể ít tăng trưởng hơn so với bảng cơ bản.

Yêu cầu tiên quyết

Hoàn thành các bước tiên quyết sau:

1. Một AWS account.
2. Một cơ sở dữ liệu SQL Server phiên bản 2016 – 2022 phiên bản doanh nghiệp.
3. Aurora PostgreSQL-Compatible hoặc Amazon RDS cho PostgreSQL.
4. Các công cụ phát triển SQL như SQL Server Management Studio (SSMS) và DBeaver.
5. Kiến thức cơ bản về SQL Server và PostgreSQL.

Phân tích các chế độ xem có chỉ mục của SQL Server

Bước đầu tiên là phân tích các chế độ xem có chỉ mục của SQL Server và xác định các cột bảng và các hàm tổng hợp được sử dụng. Điều này sẽ giúp tạo ra các chế độ xem PostgreSQL tương đương.

Để minh họa, hãy chọn một bảng Sản phẩm mẫu và tạo chế độ xem có chỉ mục SQL Server sau:

-- Create the view

CREATE VIEW InventorySummaryView WITH SCHEMABINDING AS

SELECT ProductID, COUNT_BIG(*) AS TotalCount, SUM(Quantity) AS TotalQuantity

FROM Production.ProductInventory

GROUP BY ProductID;

-- Create the clustered index on the view

CREATE UNIQUE CLUSTERED INDEX IDX_InventorySummaryView ON InventorySummaryView (ProductID);

Để phân tích các dạng xem được lập chỉ mục trong SQL Server, hãy sử dụng truy vấn sau:

--Query for identifying the created view  

SELECT OBJECT_NAME(object_id) AS view_name, name AS index_name, index_id, type_desc,

is_unique, data_space_id, ignore_dup_key, is_primary_key,

is_unique_constraint, fill_factor, is_padded, is_disabled,

is_hypothetical, allow_row_locks, allow_page_locks,

has_filter, filter_definition, compression_delay

FROM sys.indexes

WHERE objectproperty(object_id,'IsIndexed') = 1

ORDER BY object_name(object_id), index_id;

Điều này cung cấp cho bạn danh sách các dạng xem được lập chỉ mục trong cơ sở dữ liệu SQL Server.

Chuyển đổi chế độ xem có chỉ mục của SQL Server thành chế độ xem vật liệu PostgreSQL

Bước tiếp theo là chuyển đổi chế độ xem có chỉ mục của SQL Server thành chế độ xem vật liệu PostgreSQL bằng cách sử dụng các bước sau:

1. Trích xuất định nghĩa của chế độ xem có chỉ mục của SQL Server bằng đoạn mã kịch bản sau:

SELECT OBJECT_NAME(object_id) AS view_name, definition

FROM sys.sql_modules

WHERE OBJECTPROPERTY(object_id, 'IsView') = 1;

2. Tạo một chế độ xem vật liệu trong PostgreSQL bằng cách sử dụng bộ kết quả của chế độ xem PostgreSQL

CREATE MATERIALIZED VIEW InventorySummaryView AS

SELECT ProductID, COUNT(*) AS TotalCount, SUM(Quantity) AS TotalQuantity

FROM adventureworks2k7.Production.ProductInventory

GROUP BY ProductID;

3. Chỉ mục cho chế độ xem vật liệu để cải thiện hiệu suất bằng cách sử dụng cú pháp sau

CREATE UNIQUE INDEX IDX_InventorySummaryView ON InventorySummaryView (ProductID);

4. Cấu hình hoặc lên lịch cập nhật cho chế độ xem vật liệu trong PostgreSQL bằng cách sử dụng cú pháp sau

ALTER MATERIALIZED VIEW InventorySummaryView CLUSTER ON IDX_InventorySummaryView;

REFRESH MATERIALIZED VIEW CONCURRENTLY InventorySummaryView;

Dọn dẹp

Để tránh phải trả các chi phí liên tục, hãy dọn dẹp các tài nguyên sau:

• Xóa các tài nguyên Aurora PostgreSQL-Compatible hoặc Amazon RDS for PostgreSQL đã cung cấp.
• Xóa các instance Amazon Elastic Compute Cloud (Amazon EC2) được tạo cho các công cụ phát triển SQL như SSMS và DBeaver.

Nhược điểm đã biết của giải pháp này:

• Loại dữ liệu – PostgreSQL và SQL Server hỗ trợ các loại dữ liệu khác nhau, do đó, bạn có thể cần sửa đổi chế độ xem có chỉ mục của SQL Server để duy trì tính tương thích với PostgreSQL. (Để biết thêm chi tiết, tham khảo)
• Hàm tổng hợp – Chế độ xem vật liệu trong PostgreSQL không hỗ trợ tất cả các hàm tổng hợp mà chế độ xem có chỉ mục của SQL Server hỗ trợ, vì vậy, bạn có thể cần điều chỉnh các truy vấn của mình tương ứng
• Sự cập nhật có sẵn – Những thay đổi được thực hiện vào các bảng cơ sở dữ liệu sẽ không được phản ánh trong chế độ xem vật liệu tự động cho đến khi nó được làm mới.
• Dung lượng lưu trữ – Chế độ xem vật liệu có thể chiếm thêm không gian lưu trữ, đảm bảo bạn có đủ không gian lưu trữ trong PostgreSQL instance của mình
• Thời gian làm mới – Tùy thuộc vào kích thước của chế độ xem vật liệu và phức tạp của truy vấn cơ sở dữ liệu cơ sở, thời gian làm mới có thể thay đổi. Bạn có thể theo dõi “vacuum_count” và “vacuum_cost” cho các bảng cơ bản để ước lượng tần suất cập nhật và chi phí làm mới tiềm ẩn. Ngoài ra, CPU, bộ nhớ và I/O đĩa có sẵn cũng có thể ảnh hưởng đến tốc độ làm mới.
• Nâng cấp – Định nghĩa và cơ chế lưu trữ của chế độ xem vật liệu có thể thay đổi giữa các phiên bản PostgreSQL. Nếu bạn kế hoạch nâng cấp lên một phiên bản PostgreSQL mới, đảm bảo rằng chế độ xem vật liệu của bạn tương thích với phiên bản mới. Sử dụng hàm “pg_upgrade_support” hoặc xem xét thủ công để xác định các vấn đề tương thích tiềm ẩn.

Các xem xét khi sử dụng chế độ xem vật liệu PostgreSQL thay cho chế độ xem có chỉ mục

Quan trọng để xem xét các khía cạnh sau đây của chế độ xem vật liệu.

• Hiệu suất – Chế độ xem vật liệu có thể cải thiện hiệu suất truy vấn, nhưng điều này đi kèm với chi phí làm mới định kỳ và tăng thêm không gian lưu trữ. Bạn cần đánh giá một cách cẩn thận lợi ích và nhược điểm của việc duy trì một chế độ xem vật liệu để đồng bộ với dữ liệu cơ bản đòi hỏi làm mới định kỳ. Những việc làm mới này có thể chậm chạp, đặc biệt là đối với các tập dữ liệu lớn, có thể ảnh hưởng đến hiệu suất ứng dụng trong quá trình thực hiện.

• Tối ưu hóa truy vấn – Chế độ xem vật liệu là hiệu quả nhất khi chúng được sử dụng để cải thiện hiệu suất của các truy vấn thường xuyên được sử dụng. Chế độ xem vật liệu cải thiện hiệu suất bằng cách tính toán trước dữ liệu được truy cập thường xuyên, giảm tải trên các bảng cơ bản, cung cấp các lối vào truy cập tối ưu hóa và caching. Tuy nhiên, quan trọng để đánh giá các sự đánh đổi như không gian lưu trữ bổ sung và thời gian làm mới.

• Làm mới dữ liệu – Chế độ xem vật liệu phải được làm mới định kỳ để đảm bảo rằng chúng phản ánh dữ liệu mới nhất từ các bảng cơ bản. Bạn có thể xác định tần suất làm mới dựa trên yêu cầu duy trì cập nhật dữ liệu.

• Chỉ mục – Chế độ xem vật liệu có thể được chỉ mục để cải thiện hiệu suất truy vấn, nhưng bạn phải đảm bảo rằng các chỉ mục được tối ưu hóa cho trường hợp sử dụng của bạn. Bạn có thể đạt được chỉ mục chế độ xem vật liệu tối ưu bằng cách phân tích mô hình truy vấn, chọn cột có liên quan được sử dụng trong các mệnh đề WHERE và ORDER BY, lựa chọn loại chỉ mục và kiểm tra thống kê sử dụng chỉ mục.

• Tính tương thích – Đảm bảo ứng dụng của bạn tương thích với cài đặt chế độ xem vật liệu PostgreSQL trước khi di chuyển từ SQL Server.

• Kiểm thử – Trước khi di chuyển chế độ xem có chỉ mục từ SQL Server sang chế độ xem vật liệu trong PostgreSQL, bạn cần kiểm thử kỹ lưỡng các truy vấn của mình để đảm bảo rằng chúng tạo ra kết quả mong đợi và thực hiện tốt.

Kết luận

Bài viết đã chỉ ra cách di chuyển chế độ xem có chỉ mục từ SQL Server sang chế độ xem vật liệu trong Aurora PostgreSQL hoặc Amazon RDS cho PostgreSQL có thể cải thiện đáng kể hiệu suất truy vấn cho ứng dụng của bạn. Tuy nhiên, có những hạn chế và xem xét mà bạn cần lưu ý, như tương thích kiểu dữ liệu, hỗ trợ hàm tổng, kiểm soát giao dịch, yêu cầu không gian lưu trữ, thời gian làm mới, và tối ưu hóa truy vấn. Bằng cách đánh giá những yếu tố này và kiểm thử truy vấn của bạn, bạn có thể thực hiện một quá trình di chuyển thành công và cải thiện hiệu suất cho ứng dụng của mình.

Chúng tôi mời bạn để lại ý kiến của bạn trong phần bình luận.

Về tác giả:

Yugundhar Kalikapuram là Chuyên gia tư vấn chính tại AWS Professional Services và là chuyên gia về chủ đề di chuyển cơ sở dữ liệu sang Amazon RDS. Ông có 15 năm kinh nghiệm và là người có niềm đam mê nhiệt thành với các hệ thống quản lý cơ sở dữ liệu quan hệ và công nghệ NoSQL.

Sawan Saxena là Chuyên gia tư vấn chính của nhóm Dịch vụ chuyên nghiệp AWS. Anh giúp khách hàng xây dựng kiến trúc, hiện đại hóa và di chuyển các giải pháp cơ sở dữ liệu của họ sang AWS.