Morioh
Login
Duy Tien

Duy Tien

7 months ago

Giảm dung lượng lưu trữ SQL DB: Cách tối ưu hóa cơ sở dữ liệu của bạn

Tìm hiểu cách giảm dung lượng lưu trữ đã tiêu thụ trong SQL DB bằng cách sử dụng các kỹ thuật nén, phân vùng và lập chỉ mục. Tìm hiểu cách cải thiện hiệu suất và hiệu quả của cơ sở dữ liệu của bạn.

Ước lượng

Làm thế nào để tôi quyết định liệu có ý nghĩa gì khi làm việc theo hướng này? Đó là câu hỏi/quyết định đầu tiên cần được đưa ra.

Điều kiện trước

  • ứng dụng web nhiều người thuê trên nền tảng đám mây đã được sử dụng trong nhiều năm;
  • tất cả dữ liệu phải có sẵn và nhất quán - không lưu giữ/làm sạch/lưu trữ;
  • RDBMS trên đám mây (một số DB cho các mục đích khác nhau);
  • một số DB có hàng trăm hợp đồng biểu diễn và đang phát triển nhanh chóng;
  • ý tưởng - 'viết lại mọi thứ' không thể chấp nhận được;

Các bước đã sử dụng

1. Tìm các bảng DB lớn nhất bằng cách sử dụng bất kỳ SQL nào từ nhiều loại có sẵn trên Internet (có nhiều công cụ cho mục đích này - nhưng chúng tôi có khu vực quản lý DB riêng trong cổng thông tin của mình, khu vực này hiển thị số liệu thống kê chung theo DB, đối tượng, chỉ mục, phân mảnh , vân vân).

2. Xem lại các bảng đã tìm thấy' tính chất dữ liệu. Nếu có bất kỳ loại dữ liệu nào như nvarchar(max) và varbinary(max) (loại MS SQL được sử dụng làm ví dụ), các bảng này sẽ được sử dụng để ước tính/đánh giá thêm.

3. Viết tập lệnh SQL để ước tính tổng thể việc giảm dữ liệu. Đây là một đoạn SQL được sử dụng.

SELECT

SUM(DATALENGTH(TextColumnName) - DATALENGTH(COMPRESS(TextColumnName))) as DataReduction
,COUNT(*) as AffectedRows

FROM [dbo].[SomeTable]

WHERE TextColumnName IS NOT NULL
AND DATALENGTH(TextColumnName) > @thresholdParam

Tập lệnh này sẽ đưa ra ước tính cấp cao tính bằng byte trên mỗi cột. Chắc chắn, lợi ích thường tốt hơn do cấu trúc dữ liệu SQL cấp thấp và kích thước dữ liệu được lưu trữ trong một cột cụ thể liên tiếp. Giá trị ThresholdParam phải được tìm thấy trong từng trường hợp và loại dữ liệu cụ thể. Trong trường hợp của chúng tôi, dữ liệu văn bản là ~2k.

Khi có các con số kết quả, có thể đưa ra kết luận liệu việc này có đáng thực hiện hay không. Trong trường hợp của chúng tôi, chúng tôi đã có những con số đầy hứa hẹn (~ 5-10% mỗi bảng) và cuối cùng, tác động thậm chí còn nhiều hơn (do đặc thù của cấu trúc dữ liệu RDBMS).

Thực hiện

Quá trình triển khai mất ~ vài ngày làm việc của nhà phát triển và vài ngày thử nghiệm công việc QA.

Một lựa chọn tốt hơn để lưu trữ dữ liệu nén trong cùng một bảng DB là varbinary(max) (sau này, tôi sẽ trình bày một cách tốt hơn, nhưng phức tạp hơn từ góc độ triển khai). Việc có thêm (các) cột trên cùng một bảng là tùy chọn nhưng tốt hơn để đảm bảo tính nhất quán và thuận tiện cho dữ liệu.

Ví dụ. Hãy tưởng tượng có một cột như Mô tả nvarchar(max) - cột mới BinDescription varbinary(max) được thêm vào. Tập lệnh di chuyển dữ liệu sẽ sử dụng phương pháp tương tự được dùng trong tập lệnh SQL ước tính.

UPDATE TOP(@batchSize) tab
SET
tab.BinDescription = COMPRESS(tab.Description)
,tab.Description = NULL
FROM [dbo].[SomeTable] tab
WHERE tab.Description IS NOT NULL
AND DATALENGTH(tab.Description) > @threshold
AND tab.BinDescription IS NULL;

Tốt hơn hết bạn nên kết thúc tập lệnh bằng cách phân đợt để tránh các giao dịch kéo dài và bị gián đoạn. Tập lệnh của tôi có khả năng chịu lỗi và có thể bị gián đoạn bất cứ lúc nào và được thực thi lại. Nó tiếp tục xử lý dữ liệu từ điểm cam kết cuối cùng. Để tránh mức tiêu thụ CPU và IO cao ở cấp DB và không chặn người dùng, tốt hơn là nên đưa ra độ trễ bổ sung giữa các đợt (chúng tôi đã tải ~5% trên DB và việc di chuyển có thể được xử lý ngay cả trong thời gian làm việc).

Triển khai lớp ứng dụng

Việc triển khai cụ thể phụ thuộc vào DAL được sử dụng trong ứng dụng. Đối với DAL, sử dụng Dapper microORM, những thay đổi duy nhất sẽ là trong các truy vấn SQL được sử dụng trong ORM đó (hầu như không có thay đổi ứng dụng nào trong mã C#)—đại loại như thế này.

SELECT
ID
,Name
,COALESCE([Description], CAST( DECOMPRESS([BinDescription]) as NVARCHAR(MAX)) ) as Description
,OtherColum
FROM [dbo].[SomeTable]
WHERE ...

Đối với thao tác ghi, tốt nhất nên sử dụng EF của thứ gì đó tương tự như UnitOfWork, tính nhất quán, v.v.

Nếu EF được sử dụng để hỗ trợ nén và giải nén, thực thể cần được mở rộng với thuộc tính bổ sung có loại byte[]. Tất cả 'ma thuật' nên được thực hiện trong các kho lưu trữ (nếu sử dụng mẫu như vậy) hoặc trong bất kỳ dịch vụ truy cập dữ liệu nào khác. Điều đáng mừng là hàm SQL COMPRESS hoàn toàn tương thích với .net GZipStream.

Khi chúng ta cần lưu dữ liệu vào DB bằng EF, chỉ cần kiểm tra xem tải trọng có phù hợp để nén hay không (trong trường hợp của tôi có một dịch vụ hạng nhẹ với logic được đóng gói) và đặt tải trọng đã nén vào BinColumn và đặt lại OriginalColumn. Khi tải hành động, thao tác ngược lại được sử dụng - kiểm tra xem BinColumn có phải là null hay không, giải nén (sử dụng cuộc gọi trực tiếp hoặc một số dịch vụ) và gán cho OriginalColumn một tải trọng đã giải nén. Để gói gọn tất cả những điều 'ma thuật' kho lưu trữ (hoặc bất kỳ thứ gì khác), sẽ hiển thị một số DTO mà không có bất kỳ thuộc tính nào liên quan đến nhị phân/nén.

#sql  #sqlserver 

Giảm dung lượng lưu trữ SQL DB: Cách tối ưu hóa cơ sở dữ liệu của bạn
1.50 GEEK