15 mẫu phản thiết kế trong Firebird

bởi Alexey Kovyazin, 14-Jan-2025

Giới thiệu

Tài liệu này trình bày 15 anti-pattern phổ biến khi làm việc với cơ sở dữ liệu Firebird và cung cấp giải pháp cho từng vấn đề.

1. Nhiều truy vấn song song tới MON$

Anti-pattern: Một sai lầm rất phổ biến - trigger OnConnect, truy vấn tới MON$ATTACHMENTS để chọn thông tin chi tiết của người dùng cho mục đích kiểm toán, hoặc tính số lượng kết nối cho mục đích cấp phép.

Tại sao điều này xấu?

• Các bảng MON$ là bảng ảo được lưu trữ trong các tệp hệ thống fbNN_mon_xx, với thống kê hiệu suất, v.v.

• Tệp >1Gb có nghĩa là bạn đang sử dụng nó quá nhiều

• Chúng được thiết kế chỉ dành cho quản trị viên hệ thống sử dụng - tức là, 1-2 truy vấn song song, dành riêng cho quản trị viên

• 200+ kết nối với truy vấn song song tới MON$ sẽ làm chậm Firebird rất đáng kể, và 500+ truy vấn đồng thời sẽ “treo” Firebird với xác suất cao

Giải pháp:

• Không sử dụng MON$ cho các tác vụ không phải quản trị, tức là để đếm hoặc kiểm toán, tránh sử dụng chúng trong OnConnect

• Cho mục đích kiểm toán:

• Sử dụng Biến ngữ cảnh như CURRENT_USER, CURRENT_TIMESTAMP, v.v.

• Sử dụng Audit - tính năng gốc của Firebird, mạnh mẽ hơn nhiều so với trigger

• Cho mục đích cấp phép - sử dụng biến ngữ cảnh của người dùng

2. Tải Dashboard chậm

Anti-pattern: Tải các dashboard hoặc bảng điểm toàn diện tổng hợp tất cả đơn hàng và hóa đơn trong tháng hoặc năm qua khi khởi động ứng dụng, hoặc cập nhật một số chỉ số mỗi phút hoặc thường xuyên hơn.

SELECT
 SUM(total_sales) as yearly_sales,
 COUNT(DISTINCT customers) as customer_count,
 AVG(order_value) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-01';

Tại sao điều này xấu?

• Người dùng phải chờ vài giây để xem thống kê toàn công ty trước khi họ có thể bắt đầu công việc thực tế

• Từ góc độ Firebird - để liên tục chạy nhiều truy vấn song song, truy xuất lượng lớn dữ liệu, sắp xếp/nhóm chúng, Firebird sẽ sử dụng nhiều lõi CPU, đọc từ đĩa, bộ nhớ đệm, bộ nhớ dành riêng cho sắp xếp (và đôi khi sắp xếp ghi ra đĩa)

• Nó giống như xây dựng một báo cáo nhiều lần mỗi phút!

Giải pháp:

1. Giảm số lượng người dùng sẽ thấy dashboard:

• Thông thường Dashboard chỉ cần thiết cho nhà phân tích và ban quản lý, loại bỏ nó khỏi tải chung của ứng dụng

• Làm cho việc tải dashboard khi khởi động/cho một số form trở thành tùy chọn, bị tắt theo mặc định

• Tải dữ liệu dashboard bằng cách nhấn nút rõ ràng, không phải khi khởi động (tức là, làm cho nó như một báo cáo)

2. Tính toán dữ liệu dashboard bằng 1 tiến trình theo lịch (tức là, robot) và lưu chúng vào một bảng đơn giản sẵn sàng để truy xuất bằng truy vấn đơn giản

3. Sử dụng trigger để tổng hợp dữ liệu và lưu chúng sẵn sàng để sử dụng

4. Sử dụng cơ sở dữ liệu replica để tính toán dữ liệu dashboard (và tất cả các báo cáo nặng khác)

3. Tải các bản ghi không cần thiết

Anti-pattern: Tải tất cả dữ liệu không lọc vào grid khi mở ứng dụng hoặc form, bất kể nó có chứa hàng trăm nghìn bản ghi hay không.

procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Loads entire table into memory
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

Tại sao điều này xấu?

• Mặc dù grid chỉ hiển thị 50 bản ghi, người dùng phải cuộn qua hàng nghìn bản ghi thay vì sử dụng chức năng tìm kiếm

• Trong 99% trường hợp người dùng cần một tập con dữ liệu rất hẹp: ví dụ, các bản ghi bán hàng gần đây nhất

• Từ góc độ Firebird:

• Mỗi lần mở yêu cầu đọc, lưu vào bộ nhớ đệm và truyền hàng nghìn bản ghi qua mạng

• Nếu bạn giữ dataset mở (trong Delphi), Firebird giữ bộ đệm, bản ghi đã sắp xếp trong không gian tạm (nếu có ORDER BY, GROUP BY, v.v.) cho đến khi đóng dataset

Giải pháp:

1. Giới hạn số lượng bản ghi với FIRST/SKIP/ROWS

2. Giới hạn số lượng bản ghi với một số tiêu chí, ví dụ, hiển thị các bản ghi được tạo/thay đổi trong 3 ngày gần đây

3. Nói chung, đóng truy vấn càng sớm càng tốt.

4. Truy vấn quá mức khi cuộn

Anti-pattern: Thực thi truy vấn trên sự kiện cuộn. Ví dụ, khi hiển thị dữ liệu trong grid hoặc bảng, thực hiện một truy vấn riêng CHO MỖI bản ghi, hoặc nếu bạn đang sử dụng ví dụ cổ điển về cuộn master-detail trong 2 grid không có độ trễ.

procedure TForm1.GridScrolled(Sender: TObject);
begin
 // query for each row
 FDQuery2.SQL.Text :=
 'SELECT additional_info FROM details ' +
 'WHERE id = ' + IntToStr(CurrentRowId);
 FDQuery2.Open;
end;

Tại sao điều này xấu?

• Thực hiện một truy vấn riêng CHO MỖI bản ghi trong grid động buộc Firebird xử lý hàng nghìn truy vấn nhỏ, tiêu tốn tài nguyên CPU không cần thiết

• Từ góc độ Firebird:

• Nhiều (hàng nghìn mỗi giây) truy vấn nhỏ sẽ tạo tải CPU đáng kể, bởi vì ngay cả khi truy vấn hiển thị 0ms trong thống kê, nó cần được chuẩn bị, thực thi, kết quả được truyền, v.v.

Giải pháp:

1. Tải nhiều hàng cùng một lúc bằng cách sử dụng thao tác hàng loạt

2. Nâng cao truy vấn chính cho grid để thực thi truy vấn chi tiết như một phần của nó

3. Thêm nút rõ ràng để tải chi tiết cho phần hiển thị của grid

4. Thêm độ trễ để thực thi truy vấn nhận chi tiết, để ngăn truy vấn ngay lập tức trong khi cuộn

5. Không bật tải chi tiết khi cuộn cho tất cả người dùng theo mặc định

5. Làm mới tự động không cần thiết

Anti-pattern: Làm mới dữ liệu grid tự động ở khoảng thời gian tối thiểu trong mỗi ứng dụng khách, với tính năng này được bật theo mặc định.

Tại sao điều này xấu?

• Điều này dẫn đến hàng trăm kết nối khách hàng chạy các truy vấn gần như giống hệt nhau để truy xuất cùng một bản ghi

• Nơi điều này xảy ra: làm mới tự động cho lịch trình, hoặc chọn vị trí hàng đợi, hoặc tìm kiếm “vị trí gần nhất”, v.v.

• Từ góc độ Firebird:

• Kết hợp tải dashboard và sự kiện cuộn: nhiều truy vấn cỡ trung bình tạo tải trên hệ thống

Giải pháp:

1. Tăng khoảng thời gian!

2. Triển khai làm mới rõ ràng (do người dùng kích hoạt)

3. Sử dụng làm mới có chọn lọc tập dữ liệu dựa trên thay đổi dữ liệu thực tế (streaming hoặc trigger hoặc event+streaming)

6. Cập nhật bản ghi thường xuyên

Anti-pattern: Cập nhật thường xuyên cùng một bản ghi trong các giao dịch khác nhau, tạo ra nhiều phiên bản bản ghi.

Tại sao điều này xấu?

• Một bản ghi với hàng chục phiên bản có thể làm giảm hiệu suất đáng kể, một bản ghi với hàng nghìn phiên bản có thể trở thành điểm nghẽn

• Từ góc độ Firebird: chuỗi phiên bản bản ghi cần được tái tạo để xác định phiên bản phù hợp của giao dịch cụ thể, nó yêu cầu nhiều thao tác đọc, kết quả là, thu gom rác trở nên chậm hơn đáng kể.

Giải pháp:

1. Di chuyển sang Firebird 4+, có thu gom rác trung gian

2. Không giữ giao dịch ghi chạy lâu, thực hiện thu gom rác đúng cách

3. Đối với Firebird <4, cân nhắc sử dụng DELETE+INSERT thay vì UPDATE

7. Sử dụng giao dịch ghi cho select chỉ đọc

Anti-pattern: Sử dụng giao dịch ghi cho select chỉ đọc dẫn đến các thao tác quá mức.

Tại sao điều này xấu?

• Sử dụng giao dịch ghi cho select chỉ đọc dẫn đến nhiều lần ghi không cần thiết các trang header

• Sử dụng giao dịch ghi cho thao tác chỉ đọc là không hiệu quả (TIP lớn trong khi commit tạo tải bổ sung trên máy chủ)

Giải pháp:

• Sử dụng giao dịch chỉ đọc riêng cho các thao tác không thay đổi dữ liệu

• Firebird là một trong số ít cơ sở dữ liệu cho phép mở nhiều giao dịch trong khung của một kết nối duy nhất

• Bảng tạm toàn cục có sẵn để sử dụng trong giao dịch chỉ đọc

8. Sử dụng LIKE :param

Truy vấn sau với tham số sẽ không sử dụng chỉ mục cho fieldName (ngay cả khi chỉ mục tồn tại):

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Tại sao điều này xấu?

Vì LIKE cho phép tìm kiếm ký tự đại diện (%), có thể thay thế bất kỳ số lượng ký tự nào, Firebird không thể xác định trước liệu giá trị tham số có phù hợp cho tìm kiếm chỉ mục hay không.

Thông thường các nhà phát triển cố gắng khắc phục điều này bằng cách nhúng giá trị tham số vào văn bản truy vấn:

• fieldName LIKE «Alex%» - có thể sử dụng chỉ mục

• fieldName LIKE «%Alex» - không thể sử dụng chỉ mục tiêu chuẩn

• fieldName LIKE «%Alex%» - hoàn toàn không thể sử dụng chỉ mục

Điều này dẫn đến các vấn đề khác (xem #10 bên dưới).

Giải pháp:

1. Sử dụng STARTING WITH cho tiền tố chuỗi đã biết

Khi giá trị tìm kiếm của bạn không bao giờ bắt đầu bằng ký tự đại diện %, ưu tiên STARTING WITH hơn LIKE:

WHERE fieldName STARTING WITH ?param1

2. Tối ưu hóa tìm kiếm chuỗi hai chiều

Đối với chuỗi với mẫu tiền tố hoặc hậu tố đã biết, sử dụng chỉ mục đảo ngược:

-- Create reversed index
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Query using both directions
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. Triển khai chiến lược tìm kiếm tiến bộ

Đối với chuỗi xuất hiện ở đầu/cuối/giữa (nhưng không đồng thời):

• Đầu tiên thử tìm kiếm chỉ mục nhanh với STARTING WITH

• Nếu không tìm thấy kết quả, quay lại tìm kiếm LIKE chậm hơn

4. Tối ưu hóa tìm kiếm dựa trên từ

Khi tìm kiếm từ hoàn chỉnh (được phân tách bằng dấu cách, dấu phẩy, v.v.):

• Tạo bảng ánh xạ word-ID riêng

• Tìm kiếm thông qua bảng ánh xạ thay vì văn bản gốc

5. Đối với khả năng tìm kiếm toàn văn toàn diện:

• Cân nhắc sử dụng IBSurgeon Full Text Search UDR

• Giải pháp mã nguồn mở này cung cấp chức năng tìm kiếm văn bản nâng cao

9. Không đóng giao dịch cho thao tác chỉ đọc

Tại sao điều này xấu?

• Giữ giao dịch mở trong thời gian dài có thể buộc Firebird duy trì nhiều phiên bản cũ cho các giao dịch snapshot tiềm năng

Giải pháp:

• Sử dụng giao dịch chỉ đọc ở nơi có thể, và đóng giao dịch ghi càng sớm càng tốt

• Sử dụng phiên bản Firebird hiện đại (4+) để giảm tác động của chuỗi phiên bản bản ghi

• Triển khai sweep đúng cách

10. Vấn đề tham số hóa truy vấn

Anti-pattern: Tránh truy vấn đã chuẩn bị và tham số hóa, thay vào đó nhúng giá trị tham số trực tiếp vào văn bản truy vấn.

FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = ''' +
 EditUsername.Text + '''';
FDQuery1.Open;

Tại sao điều này xấu?

• Thực hành này làm giảm hiệu suất cho các truy vấn lặp lại

• Mỗi truy vấn với giá trị tham số nhúng phải được chuẩn bị như mới

• Chuẩn bị có thể lâu và tốn thời gian cho các bảng lớn

• Làm phức tạp phân tích vấn đề

• Khó nhóm truy vấn theo văn bản

• Tạo lỗ hổng SQL injection

Giải pháp:

FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = :username';
FDQuery1.ParamByName('username').AsString :=
 EditUsername.Text;
FDQuery1.Open;

11. Kiểm tra tính toàn vẹn sai: trigger/CHECK thay vì Primary Key

Anti-pattern: Sử dụng trigger hoặc CHECK thay vì Primary Key để kiểm tra tính toàn vẹn cơ sở dữ liệu.

Tại sao điều này xấu?

• Điều này bỏ qua việc xác thực Primary Key sử dụng chế độ đặc biệt để đọc phiên bản hiện tại của bản ghi, bất kể mức độ cô lập giao dịch của người dùng.

• Thực hiện kiểm tra PK với trigger trong giao dịch người dùng làm tăng khả năng trùng lặp và làm phức tạp logic không cần thiết

Giải pháp:

• Sử dụng primary key

• Tránh kiểm tra toàn vẹn dư thừa

• Giữ logic cơ sở dữ liệu đơn giản

12. Tạo ID với MAX()

Anti-pattern: Sử dụng MAX(id)+1 cho định danh mới là không đáng tin cậy và không hiệu quả.

INSERT INTO users (id, name)
VALUES ((SELECT MAX(id) + 1 FROM users),
 'John Doe');

Tại sao điều này xấu?

• Sử dụng MAX(id)+1 thay vì sequence (generator) cho định danh mới

• MAX(id)+1 không đảm bảo tính duy nhất với các tham số giao dịch phổ biến - hai giao dịch song song có thể nhận cùng giá trị MAX()

• Kết hợp Max()+1 và CHECK(select if unique) cũng không hoạt động!

Giải pháp:

-- Use generator/sequence!
CREATE GENERATOR gen_user_id;
-- Use generator for ID generation
INSERT INTO users (id, name)
VALUES (
 GEN_ID(gen_user_id, 1),
 'John Doe' );

13. Sử dụng GUID không hiệu quả

Tại sao điều này lại xấu?

• Sử dụng GUID do hệ thống tạo thay vì gen_uuid() có thể ảnh hưởng đến hiệu suất chỉ mục

• GUID do hệ thống tạo có tính ngẫu nhiên cao

Giải pháp:

• Sử dụng hàm gen_uuid()

• Cân nhắc sử dụng BIGINT thay thế

• Trong phiên bản 6 sẽ có UUID v7

14. Trường tính toán không hiệu quả

Mẫu phản diện: Sử dụng trường tính toán với các SELECT đến bảng khác làm giảm đáng kể hiệu suất của các thao tác SELECT đơn giản.

CREATE TABLE orders (
 id INTEGER,
 total_amount COMPUTED BY (
 (SELECT SUM(item_price) FROM order_items
 WHERE order_items.order_id = orders.id)));

Tại sao điều này lại xấu?

• Trường tính toán được tính toán ngay lập tức, và chúng không được thiết kế để thực hiện logic phức tạp, và có thể làm phức tạp đáng kể các nỗ lực tối ưu hóa

• Nó tăng cường mối quan hệ giữa các bảng

• Chỉ nên sử dụng trường tính toán cho các phép tính nhẹ với các trường của bảng, như nối chuỗi

Giải pháp:

CREATE TABLE orders (
 id INTEGER PRIMARY KEY,
 cached_total_amount DECIMAL(10,2));

CREATE TRIGGER update_order_total
BEFORE INSERT OR UPDATE ON orders
AS
BEGIN
 NEW.cached_total_amount = (
 SELECT SUM(item_price)
 FROM order_items
 WHERE order_items.order_id = NEW.id
 );
END;

15. Ức chế lỗi mà không ghi log

Mẫu phản diện: Đừng ức chế lỗi và cảnh báo của Firebird mà không ghi log!

try
 FDQuery1.Open;
except
 // Silent failure
end;

Tại sao điều này lại xấu?

• Ẩn lỗi ngăn cản việc chẩn đoán và gỡ lỗi đúng cách. Ghi log lỗi đúng cách là rất quan trọng để hiểu và giải quyết vấn đề nhanh chóng.

Giải pháp:

try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // Comprehensive logging
 Logger.Error('Database connection failed: ' + E.Message);
 ShowMessage('Unable to connect to database. Please contact support.');
 // Log additional context
 Logger.LogStackTrace(E);
 end;
end;

Thông tin liên hệ

• Gửi câu hỏi của bạn đến [email protected]

• Trở thành Người ủng hộ Firebird (từ 10 EUR/tháng) và tham gia các hội thảo trực tuyến nâng cao kín!

• https://store.firebirdsql.org/p/firebird-associate-donation-eur/