Liên hệ
IBSurgeon
Trang này được dịch bằng máy. Đọc bản gốc tiếng Anh. English

Thư viện IBSurgeon

IBAnalyst: Mẹo và Thủ thuật

Đoạn văn này được viết ban đầu vào năm 2012, có giá trị cho phiên bản 1.0 - 2.5, trong các phiên bản 3.0-5.0 có nhiều thay đổi không thể phản ánh hết. Vui lòng đọc tài liệu hoặc liên hệ với chúng tôi để được hỗ trợ: [email protected].

Một số câu hỏi chưa được giải đáp trong IBAnalyst Recommendations và/hoặc Help:

1. Làm thế nào để xây dựng lại chỉ mục trên các ràng buộc PRIMARY, FOREIGN hoặc UNIQUE?

A: Đối với Firebird phiên bản 1.0-2.5. Đúng, bạn không thể sử dụng ALTER INDEX xxx INACTIVE/ACTIVE trên các chỉ mục ràng buộc. Nếu bạn thấy chỉ mục sâu hoặc phân mảnh trên ràng buộc này, bạn có thể sử dụng thủ thuật đặc biệt (được gbak sử dụng khi phục hồi):

RDB$INDICES có cờ RDB$INDEX_INACTIVE là null hoặc 0 nếu chỉ mục đang hoạt động (sau CREATE INDEX hoặc ALTER INDEX ACTIVE). 1 có nghĩa là chỉ mục không hoạt động (sau ALTER INDEX INACTIVE). Nhưng cũng có giá trị 3 được sử dụng để chỉ ra các chỉ mục không hoạt động trên các ràng buộc. Vì vậy, bạn có thể đặt RDB$INDEX_INACTIVE=3 cho chỉ mục đó, COMMIT, sau đó trả giá trị về 0 và commit lại - chỉ mục sẽ được xây dựng lại.

Đối với Firebird 3.0-5.0 - chỉ cần thực hiện ALTER INDEX indexname ACTIVE

2. Tôi đã sử dụng tất cả các khuyến nghị của IBAnalyst nhưng điều này không giúp tăng tốc truy vấn.

A: Đây là vấn đề riêng biệt mà IBAnalyst không thể giúp. Có thể có 2 nguyên nhân của vấn đề:

  1. Chỉ mục có số liệu thống kê lỗi thời. Bạn có thể làm mới số liệu thống kê chỉ mục bằng lệnh SET STATISTICS INDEX xxx (xem chi tiết hơn http://www.ibase.ru/proc_selectivity/).

  2. Đơn giản là không có chỉ mục phù hợp cho một số điều kiện được sử dụng trong truy vấn

  3. Truy vấn quá phức tạp, hoặc trình tối ưu hóa không thể tối ưu hóa truy vấn, vì vậy cần phải cấu trúc lại truy vấn.

  4. Trong một số trường hợp, bạn sẽ thấy “bảng phân mảnh” ngay sau khi phục hồi.

Thông thường Firebird và Interbase (không có tham số -use_all_space) dự trữ khoảng 25% không gian trên các trang dữ liệu cho các thao tác chèn, cập nhật hoặc xóa trong tương lai (để đặt các phiên bản bản ghi). Tuy nhiên, với bất kỳ kích thước trang cơ sở dữ liệu nào (1, 2, 4 hoặc 8 k), bạn sẽ thấy ~50% phân mảnh cho các bảng có kích thước bản ghi nhỏ (khoảng ~12-20 byte, ví dụ: bảng có 2 trường integer có kích thước bản ghi trung bình = 12 byte).

Điều này là bình thường, hãy coi đây là một con số kỳ diệu (hoặc hành vi) của máy chủ.

Vì vậy, nếu bạn có các bảng bản ghi nhỏ như vậy, bạn có thể

a) bỏ qua cảnh báo “phân mảnh” cho các bảng đó

b) giảm “phân mảnh %” xuống 45%, ví dụ, trong hộp thoại IBAnalyst Options.

4. Phiên bản bản ghi cho bảng không được cập nhật

Nếu bạn thấy phiên bản bản ghi trên bảng không được cập nhật (ví dụ: bảng có nhật ký sự kiện) - đừng lo lắng, các phiên bản này được tạo ra bởi thao tác xóa.

Vì vậy, bạn sẽ biết có bao nhiêu bản ghi hiện tại trong bảng và có bao nhiêu bản ghi đã bị xóa.

Điều này chỉ đúng nếu MaxVer = 1. Nếu > 1, thì bảng này đang được cập nhật bởi một số ứng dụng. Nếu bạn thực sự chắc chắn rằng bảng này không bao giờ được cập nhật, tốt hơn nên đặt trigger “before update” với exception để tìm ra ứng dụng nào thực hiện cập nhật.

5. Blob có thể gây phân mảnh bảng.

Engine lưu trữ blob theo 3 cách khác nhau:

    1. nếu nội dung blob vừa với trang dữ liệu (đủ không gian trống), nó sẽ được lưu trữ trên trang dữ liệu đó gần bản ghi của nó (hoặc phiên bản).
    1. nếu nội dung blob không vừa với trang dữ liệu, nó sẽ được lưu trữ trên trang riêng biệt
    1. nếu trong trường hợp 2 blob không vừa trên một trang dữ liệu, trang con trỏ được tạo ra để trỏ đến các trang blob thích hợp.

Trường hợp 1 xảy ra tùy thuộc vào kích thước blob được lưu trữ và kích thước trang cơ sở dữ liệu. Ví dụ: nếu bạn có kích thước trang 4K và blob có kích thước trung bình ~5K, chúng được lưu trữ không phải trên các trang dữ liệu mà trên các trang blob bổ sung.

Nhưng nếu bạn sao lưu cơ sở dữ liệu và phục hồi với kích thước trang 8K, blob sẽ vừa với trang dữ liệu và chúng sẽ được lưu trữ cùng với bản ghi, gây phân mảnh bản ghi cao.

IBAnalyst đánh dấu các bảng này là Pale (cột Records) và gợi ý hiển thị số bản ghi ước tính cho bảng đó (dựa trên số trang dữ liệu) và giá trị lấp đầy trung bình thực tế (%).

Nếu truy vấn của bạn đọc bất kỳ trường nào ngoại trừ blob từ bảng đó, quét tự nhiên, join hoặc aggregate sẽ chạy rất chậm.

Giải pháp duy nhất để tránh điều này: tạo bảng bổ sung (liên kết 1-1 với bảng gốc) và di chuyển tất cả các cột blob có kích thước trung bình nhỏ hơn kích thước trang sang bảng đó.

Trong trường hợp đó, đừng cố sao lưu/phục hồi với kích thước trang lớn hơn! Điều này sẽ khiến các blob không thể vừa với các trang dữ liệu với kích thước trang hiện tại sẽ được đặt trên các trang dữ liệu trong quá trình phục hồi với kích thước trang lớn hơn. Vì vậy, các bảng có blob của bạn sẽ bị phân mảnh nhiều hơn trước.

Cũng không nên phục hồi với kích thước trang nhỏ hơn, vì điều này có thể làm giảm hiệu suất cho các chỉ mục và bảng không có blob.

Bạn cũng không nên cố gắng thay đổi trường blob thành trường varchar - trường varchar luôn được lưu trữ như một phần của bản ghi, vì vậy bản ghi có thể có 2 hoặc nhiều phân đoạn (được đặt trên 2 hoặc nhiều trang dữ liệu) nếu nó không vừa với trang dữ liệu.

p.s. IBAnalyst có thể báo cáo các bảng này “nhầm lẫn”, ví dụ: bảng có các trường blob với dữ liệu, nhưng chúng đã bị xóa khỏi cấu trúc bảng. Thật không may, không có tùy chọn cấu hình cho cảnh báo đó, vì chúng tôi tính toán chính xác từ dữ liệu được báo cáo bởi máy chủ (thống kê).

6. Mối quan hệ VerLen và RecLength

a) VerLen >= 90% RecLength: các phiên bản bạn thấy trong cột Version chủ yếu là xóa bản ghi. Càng nhiều bản ghi bị xóa, RecLength sẽ càng nhỏ (tối đa 0 byte). VerLen cũng có thể lớn hơn RecLen nếu bạn cập nhật bảng với dữ liệu chuỗi lớn hơn so với dữ liệu được lưu trữ trong các bản ghi gốc.

b) VerLen <=80% RecLength: các phiên bản chủ yếu là cập nhật bản ghi.

Chúng tôi không thể phân biệt chính xác hơn các trường hợp này vì số liệu thống kê hiển thị kích thước bản ghi và phiên bản trung bình cho toàn bộ bảng, trong khi số phiên bản hiển thị cho các giao dịch đồng thời có thể khác nhau.

7. Tại sao IBAnalyst gọi một số chỉ mục là “xấu”?

Các chỉ mục có giá trị selectivity thấp hơn 0.01 được đánh dấu là “xấu” trong IBAnalyst (Xem trợ giúp Index view). Có một số nguyên nhân để gọi một chỉ mục cụ thể là xấu:

  1. Selectivity của chỉ mục đó thấp hơn 0.01. Về lý thuyết, trình tối ưu hóa không nên sử dụng chỉ mục đó, nhưng nó vẫn sử dụng nếu không có chỉ mục nào khác tồn tại (cho mệnh đề where, order by hoặc join, ít nhất)

  2. Chỉ mục như vậy gây ra quá trình thu gom rác rất chậm. Vấn đề này không tồn tại trong InterBase 7.1/7.5 và sẽ được khắc phục trong Firebird 2.0

  3. Chỉ mục này làm cho quá trình phục hồi rất chậm và nó được tạo rất chậm (create/alter index active). Điều này là do chuỗi số bản ghi lớn cho một khóa chỉ mục.

  4. Nếu chỉ mục này được sử dụng trong mệnh đề where, việc sử dụng bộ nhớ sẽ phụ thuộc vào giá trị đang được tìm kiếm (kích thước bitmask). Vì chuỗi bản ghi có thể lớn (nhiều khóa trùng lặp), mức tiêu thụ bộ nhớ cũng sẽ lớn.

  5. Nếu chỉ mục đó được sử dụng trong “order by” và có nhiều bản trùng lặp chủ yếu ở các giá trị khóa thấp hơn (tùy thuộc vào thứ tự sắp xếp chỉ mục), sẽ có nhiều lần đọc trang chỉ mục, làm chậm truy vấn.

Đó là vì IBAnalyst không thể bỏ qua sự tồn tại của các chỉ mục như vậy.

Trường hợp xấu nhất cho chỉ mục là khi nó có cột Uniques = 1, tức là tất cả các giá trị cho cột được lập chỉ mục đều giống nhau. Các chỉ mục này được liệt kê trong “Useless indices” tại trang Summary.

Tất nhiên, đối với ứng dụng của bạn, một chỉ mục như vậy có thể là “tốt”. Ví dụ: nếu bản ghi có cờ “archive” trong một số cột và ứng dụng của bạn tìm kiếm theo chỉ mục trên cột đó chỉ cho dữ liệu hiện tại, không phải dữ liệu đã lưu trữ. Vì vậy, tùy thuộc vào bạn, chúng tôi gọi chỉ mục đó là “xấu” có đúng hay không.

8. Điều gì nếu chỉ mục “xấu” được tạo bởi ràng buộc Foreign Key?

Chà, đoạn trước cho thấy tốt hơn nên xóa các chỉ mục “xấu” (nếu bạn không sử dụng nó để tìm kiếm các khóa có ít bản trùng lặp hơn các khóa khác). Tuy nhiên, nếu một chỉ mục như vậy được tạo bởi khóa ngoại, bạn chỉ có thể xóa nó bằng cách xóa khóa ngoại. Việc xóa khóa ngoại sẽ vô hiệu hóa ràng buộc kiểm tra quan hệ, điều này có thể không được chấp nhận.

Bạn có thể thay thế FK bằng trigger, nhưng có một số hạn chế. FK kiểm soát quan hệ bản ghi bằng chỉ mục và chỉ mục “nhìn thấy” tất cả các khóa cho tất cả các bản ghi độc lập với trạng thái giao dịch. Nhưng trigger chỉ hoạt động trong ngữ cảnh giao dịch của máy khách. Vì vậy, khi thay thế FK bằng trigger, bạn phải đảm bảo rằng:

  • Bản ghi sẽ không bị xóa khỏi bảng chính, hoặc bị xóa ở chế độ “snapshot table reserving”
  • Cột được sử dụng bởi PK trong bảng chính sẽ không bao giờ được sửa đổi. Bạn có thể hạn chế điều này bằng trigger before update.

Nếu bạn duy trì các điều kiện này, bạn có thể xóa Foreign Key cụ thể. Tất nhiên, không tạo chỉ mục thủ công trên cột đó.

9. Tại sao trong hàng phần trăm phiên bản dữ liệu chỉ có 12 megabyte dữ liệu, nhưng tôi có cơ sở dữ liệu 140 megabyte?

  1. IBAnalyst ở đây hiển thị khối lượng dữ liệu “thuần”, không tính các cấu trúc cơ sở dữ liệu khác (chỉ mục, siêu dữ liệu…) và phân mảnh trang.

  2. Sau khi phục hồi, InterBase và Firebird để lại một số không gian trống (15-25%) trên các trang dữ liệu để làm cho các thao tác cập nhật/xóa trong tương lai nhanh hơn.

  3. Có hành vi cụ thể của máy chủ khi nó để lại các trang dữ liệu bị phân mảnh ~50%, nếu kích thước bản ghi của bảng đó thấp, khoảng 11-22 byte.

10. Làm thế nào để cải thiện hiệu suất trình tối ưu hóa trong trường hợp cập nhật thường xuyên

Số liệu thống kê chỉ mục được lưu trữ trong cột RDB$INDICES.RDB$STATISTICS và được cập nhật theo 3 cách:

  1. SET STATISTICS INDEX

  2. ALTER INDEX ACTIVE, hoặc CREATE INDEX …

  3. quá trình phục hồi (tất cả các chỉ mục được xây dựng lại cũng như “ALTER INDEX ACTIVE”)

Trình tối ưu hóa sử dụng thông tin thống kê này để chuẩn bị truy vấn. Sử dụng các giá trị thống kê, trình tối ưu hóa có thể quyết định rằng chỉ mục “đủ tốt” hoặc “không hữu ích” để truy xuất bản ghi.

Nếu số liệu thống kê không được cập nhật trong một thời gian dài, trình tối ưu hóa có thể tạo ra một kế hoạch kém vì các giá trị thống kê hiện có không tương ứng với tình trạng thực tế, vì dữ liệu bảng có thể thay đổi đáng kể (ví dụ: số lượng bản ghi tăng 5-10 lần, hoặc ngược lại, tất cả các bản ghi bị xóa).

Bạn có thể thay thế kế hoạch truy vấn tự động kém bằng PLAN tường minh cho một truy vấn cụ thể, nhưng đây không phải là cách tiếp cận tốt, vì dữ liệu có thể thay đổi đáng kể sau khi kế hoạch được phát triển.

Cách thay thế (và đúng) là làm mới số liệu thống kê định kỳ bằng cách áp dụng câu lệnh SET STATISTICS cho tất cả các chỉ mục. Bạn có thể lên lịch chạy script SQL để làm mới số liệu thống kê bằng ISQL hoặc công cụ có sẵn gidx (chỉ dành cho Windows).

Nếu bạn có một số bảng được tải lại định kỳ với các bản ghi khác nhau, cách tiếp cận này sẽ không giúp ích. Hãy xem xét ví dụ:

  • Bảng A được tải dữ liệu 4-5 lần mỗi ngày.
  • Sau khi xử lý dữ liệu được tải, tất cả các bản ghi trong bảng A bị xóa

Trong trường hợp này, chúng ta có thể thấy 2 giá trị thống kê chính xác cho các chỉ mục trên bảng A - khi nó được tải dữ liệu và khi nó trống. Vì vậy, số liệu thống kê được tính toán lại trên bảng đã tải sẽ vô dụng khi bảng trống và ngược lại.

Để tránh điều này, bạn cần tính toán lại số liệu thống kê cho các chỉ mục trên bảng A chỉ khi bảng được lấp đầy dữ liệu. Tốt nhất là trước khi các truy vấn trên bảng đó được chạy.

Kể từ phiên bản 1.91, IBAnalyst hiển thị sự khác biệt số liệu thống kê chỉ mục và cho phép bạn tính toán lại bất cứ lúc nào. Trước tiên, bạn cần xem thông tin bản ghi bảng - đó có phải là số lượng bản ghi trung bình thông thường hay không. Nếu có, bạn có thể tính toán lại selectivity chỉ mục một cách chắc chắn. Nếu không - có lẽ tốt hơn là không nên chạm vào số liệu thống kê chỉ mục, vì nó có thể khiến trình tối ưu hóa tạo ra các kế hoạch truy vấn thậm chí tệ hơn.