Firebird 使用非最佳计划进行 INNER JOIN

在使用Firebird数据库时，我们经常会遇到查询性能不佳的情况。其中一个常见的问题是使用INNER JOIN时，数据库选择了非最佳的执行计划，导致查询速度变慢。本文将通过一个案例来说明这个问题，并提供解决方法。

sql
SELECT o.OrderID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

sql
CREATE INDEX idx_orders_customerid ON Orders (CustomerID);
CREATE INDEX idx_customers_customerid ON Customers (CustomerID);

sql
SELECT o.OrderID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

sql
-- 创建订单表
CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    CONSTRAINT pk_orders PRIMARY KEY (OrderID)
);
-- 创建客户表
CREATE TABLE Customers (
    CustomerID INT,
    CustomerName VARCHAR(100),
    CONSTRAINT pk_customers PRIMARY KEY (CustomerID)
);
-- 添加索引
CREATE INDEX idx_orders_customerid ON Orders (CustomerID);
CREATE INDEX idx_customers_customerid ON Customers (CustomerID);
-- 插入示例数据
INSERT INTO Orders (OrderID, CustomerID) VALUES (1, 1);
INSERT INTO Orders (OrderID, CustomerID) VALUES (2, 2);
INSERT INTO Orders (OrderID, CustomerID) VALUES (3, 1);
INSERT INTO Orders (OrderID, CustomerID) VALUES (4, 3);
INSERT INTO Customers (CustomerID, CustomerName) VALUES (1, '张三');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (2, '李四');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (3, '王五');
-- 查询订单和客户姓名
SELECT o.OrderID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;