15 Mẫu phản thiết kế Firebird

by Alexey Kovyazin, 14-Jan-2025

Giới thiệu

Tài liệu này trình bày 15 phản mẫu 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$

Phản mẫu: Một lỗi rất phổ biến - kích hoạt OnConnect, truy vấn MON$ATTACHMENTS để chọn chi tiết người dùng cho mục đích kiểm toán, hoặc tính số kết nối cho mục đích cấp phép.

Tại sao nó không tốt?

Bảng MON$ là bảng ảo được lưu trữ trong các tệp hệ thống fbNN_mon_xx, với số liệu thống kê hiệu suất, v.v.
Tệp >1Gb nghĩa là bạn đang sử dụng nó quá nhiều
Chúng chỉ được thiết kế cho quản trị viên hệ thống sử dụng - tức là 1-2 truy vấn song song, chỉ 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 khả năng 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

Phản mẫu: 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.

sql

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 nó không tốt?

Người dùng phải chờ vài giây để thấy số liệu thống kê toàn công ty trước khi 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 dữ liệu khổng lồ, sắp xếp/nhóm chúng, Firebird sẽ sử dụng nhiều lõi CPU một cách mạnh mẽ, đọc từ đĩa, bộ nhớ đệm, bộ nhớ dành riêng cho việc sắp xếp (và đôi khi việc sắp xếp chuyển sang đĩa)
Nó giống như việc tạo một báo cáo nhiều lần mỗi phút!

Giải pháp:

Giảm số lượng người dùng sẽ xem dashboard:

Thông thường Dashboard chỉ cần thiết cho nhà phân tích và quản lý, loại trừ nó khỏi tải ứng dụng chung
Làm cho việc tải dashboard khi khởi động/cho một số biểu mẫu là tùy chọn, tắt theo mặc định
Tải dữ liệu dashboard bằng cách nhấp 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)

Tính toán dữ liệu dashboard bằng 1 tiến trình theo lịch trình (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
Sử dụng trigger để tổng hợp dữ liệu và lưu trữ chúng sẵn sàng để sử dụng
Sử dụng cơ sở dữ liệu bản sao để tính toán dữ liệu dashboard (và tất cả các báo cáo nặng nữa)

3. Tải Các Bản Ghi Không Cần Thiết

Phản mẫu: Tải tất cả dữ liệu mà không lọc vào lưới khi mở ứng dụng hoặc biểu mẫu, bất kể nó chứa hàng trăm nghìn bản ghi.

delphi

procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Tải toàn bộ bảng vào bộ nhớ
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

Tại sao nó không tốt?

Mặc dù lưới 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 tập con rất hẹp của dữ liệu: 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 trữ trong bộ nhớ đệm và truyền hàng nghìn bản ghi qua mạng
Nếu bạn giữ tập dữ liệu mở (trong Delphi), Firebird giữ bộ đệm, các bản ghi được sắp xếp trong không gian tạm thời (nếu ORDER BY, GROUP BY, v.v.) cho đến khi đóng tập dữ liệu

Giải pháp:

Giới hạn số lượng bản ghi bằng FIRST/SKIP/ROWS
Giới hạn số lượng bản ghi bằng 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
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

Phản mẫu: Thực thi truy vấn trên các sự kiện cuộn. Ví dụ, khi hiển thị dữ liệu trong lưới 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 lưới mà không có độ trễ.

delphi

procedure TForm1.GridScrolled(Sender: TObject);
begin
 // truy vấn cho mỗi hàng
 FDQuery2.SQL.Text :=
 'SELECT additional_info FROM details ' +
 'WHERE id = ' + IntToStr(CurrentRowId);
 FDQuery2.Open;
end;

Tại sao nó không tốt?

Thực hiện một truy vấn riêng CHO MỖI bản ghi trong lưới độ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 ra tải CPU đáng kể, vì ngay cả khi truy vấn hiển thị 0ms trong số liệu thống kê, nó vẫn yêu cầu được chuẩn bị, thực thi, kết quả được truyền, v.v.

Giải pháp:

Tải nhiều hàng cùng một lúc bằng các thao tác hàng loạt
Tăng cường truy vấn chính cho lưới để thực thi truy vấn chi tiết như một phần của nó
Thêm nút rõ ràng để tải chi tiết cho phần hiển thị của lưới
Thêm độ trễ để thực thi truy vấn nhận chi tiết, để ngăn các truy vấn tức thời trong quá trình cuộn
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

Phản mẫu: Làm mới dữ liệu lưới 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 nó không tốt?

Điều này dẫn đến hàng trăm kết nối khách 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 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 “khe gần nhất”, v.v.
Từ góc độ Firebird:
Sự kết hợp của việc tải dashboard và các sự kiện cuộn: nhiều truy vấn cỡ trung bình tạo ra tải cho hệ thống

Giải pháp:

Tăng khoảng thời gian!
Triển khai làm mới rõ ràng (do người dùng kích hoạt)
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

Phản mẫu: Thường xuyên cập nhật 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 nó không tốt?

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 vật cản
Từ góc độ Firebird: chuỗi phiên bản bản ghi phải được tái cấu trúc để xác định phiên bản phù hợp của giao dịch cụ thể, 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:

Di chuyển lên Firebird 4+, có thu gom rác trung gian
Không giữ các giao dịch ghi chạy lâu, thực hiện thu gom rác đúng cách
Đố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 các lựa chọn chỉ đọc

Phản mẫu: Sử dụng giao dịch ghi cho các lựa chọn chỉ đọc dẫn đến các thao tác không cần thiết.

Tại sao nó không tốt?

Sử dụng giao dịch ghi cho các lựa chọn chỉ đọc dẫn đến nhiều ghi không cần thiết vào các trang tiêu đề
Sử dụng giao dịch ghi cho các thao tác chỉ đọc là không hiệu quả (TIP lớn khi cam kết tạo ra 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 khuôn khổ một kết nối duy nhất
Bảng tạm thời toàn cục có sẵn để sử dụng trong các 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):

sql

SELECT * FROM Table1 WHERE fieldName LIKE :param1

Tại sao nó không tốt?

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 để tìm kiếm chỉ mục hay không.

Thông thường các nhà phát triển cố gắng giải quyết 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%» - không thể sử dụng chỉ mục chút nào

Đ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 các 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 %, hãy ưu tiên STARTING WITH thay vì LIKE:

sql

WHERE fieldName STARTING WITH ?param1

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

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

sql

-- Tạo chỉ mục đảo ngược
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- Truy vấn sử dụng cả hai hướng
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 dần

Đối với các 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 có kết quả, chuyển sang 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 các 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ạ từ-ID riêng
Tìm kiếm 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 các thao tác chỉ đọc

Tại sao nó không tốt?

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

Giải pháp:

Sử dụng giao dịch chỉ đọc khi có thể, và đóng các giao dịch ghi càng sớm càng tốt
Sử dụng các 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

Phản mẫu: Tránh các truy vấn được 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.

delphi

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

Tại sao nó không tốt?

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ố được nhúng phải được chuẩn bị như mới
Việc 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 việc phân tích vấn đề
Khó nhóm các truy vấn theo văn bản
Tạo ra các lỗ hổng tiêm nhiễm SQL

Giải pháp:

delphi

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ì Khóa chính

Phản mẫu: Sử dụng trigger hoặc CHECK thay vì Khóa chính để kiểm tra tính toàn vẹn cơ sở dữ liệu.

Tại sao nó không tốt?

Điều này bỏ qua thực tế rằng xác thực Khóa chính 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 các 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 khóa chính
Tránh các kiểm tra tính toàn vẹn dư thừa
Giữ logic cơ sở dữ liệu đơn giản

12. Tạo ID bằng MAX()

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

sql

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

Tại sao nó không tốt?

Sử dụng MAX(id)+1 thay vì sequences (generators) cho các đị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 một giá trị MAX()
Sự kết hợp của Max()+1 và CHECK(select if unique) cũng không hoạt động!

Giải pháp:

sql

-- Sử dụng generator/sequence!
CREATE GENERATOR gen_user_id;
-- Sử dụng generator để tạo ID
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 không tốt?

Sử dụng GUID do hệ thống sinh ra thay vì gen_uuid() có thể ảnh hưởng đến hiệu suất chỉ mục
GUID do hệ thống sinh ra 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
Trong phiên bản 6 sẽ có UUID v7

14. Trường Tính Toán Không Hiệu Quả

Phản mẫu: Sử dụng trường tính toán với câu lệnh SELECT từ các 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.

sql

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 không tốt?

Trường tính toán được tính toán ngay tại thời điểm truy vấn, 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ó làm tăng mối liên kết 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ư phép nối chuỗi

Giải pháp:

sql

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. Nuốt Lỗi Không Ghi Log

Phản mẫu: Đừng nuốt các lỗi và cảnh báo của Firebird mà không ghi log!

delphi

try
 FDQuery1.Open;
except
 // Thất bại im lặng
end;

Tại sao điều này không tốt?

Việc che giấu 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 đề một cách nhanh chóng.

Giải pháp:

delphi

try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // Ghi log toàn diện
 Logger.Error('Kết nối cơ sở dữ liệu thất bại: ' + E.Message);
 ShowMessage('Không thể kết nối đến cơ sở dữ liệu. Vui lòng liên hệ bộ phận hỗ trợ.');
 // Ghi log ngữ cảnh bổ sung
 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 Nhà Tài Trợ 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/

Code