含JOIN与LIKE操作符的SQL查询性能优化咨询

原始查询语句 #

SELECT B.TransactionId 
FROM BillingInfo B
INNER JOIN OfficeCustomers OC ON B.CustomerId = OC.Id
INNER JOIN CustomerContact CC ON CC.Id = OC.ContactId
WHERE CC.FirstName + ' ' + CC.LastName LIKE '%yog%'
ORDER BY B.TransactionId 
    OFFSET (0) ROWS FETCH NEXT (50) ROWS ONLY

性能瓶颈分析 #

1. WHERE子句中拼接FirstName + ' ' + LastName后使用前缀通配符的LIKE匹配，完全无法利用CustomerContact表上的字段索引，触发全表扫描。
2. 若BillingInfo.TransactionId无合适索引，ORDER BY操作会产生额外的排序开销，拉长查询耗时。

优化建议 #

• 优化模糊匹配逻辑

  • 避免字段拼接后查询，改为分别匹配单个字段：

    WHERE CC.FirstName LIKE '%yog%' OR CC.LastName LIKE '%yog%'
    

    若FirstName或LastName有单独索引，可部分降低扫描成本（前缀通配符仍无法走索引扫描，但比拼接后全表扫描效率更高）。
  • 若业务允许，使用无前置通配符的匹配（如LIKE 'yog%'），可直接利用字段上的普通索引。
  • 必须支持任意位置模糊匹配时，创建全文索引：

    -- 创建全文目录和索引
    CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
    CREATE FULLTEXT INDEX ON CustomerContact (FirstName, LastName) KEY INDEX PK_CustomerContact_Id;
    -- 修改查询语句
    WHERE CONTAINS((CC.FirstName, CC.LastName), '"*yog*"')
    

    全文索引对任意位置模糊匹配的效率远高于LIKE。

• 优化关联与排序环节

  • 为关联字段创建覆盖索引，避免键查找：

    -- BillingInfo表：按CustomerId索引，包含TransactionId
    CREATE NONCLUSTERED INDEX IX_BillingInfo_CustomerId ON BillingInfo(CustomerId) INCLUDE(TransactionId);
    -- OfficeCustomers表：按ContactId索引，包含Id
    CREATE NONCLUSTERED INDEX IX_OfficeCustomers_ContactId ON OfficeCustomers(ContactId) INCLUDE(Id);
    

  • 若BillingInfo.TransactionId是聚集索引，ORDER BY可直接利用聚集索引排序，无需额外排序操作；若不是，可评估创建包含关联字段的排序索引。

• 调整查询执行顺序
  先过滤出符合条件的客户记录，再关联账单表，减少JOIN的数据量：

  SELECT B.TransactionId
  FROM (
      SELECT OC.Id
      FROM CustomerContact CC
      INNER JOIN OfficeCustomers OC ON CC.Id = OC.ContactId
      WHERE CONTAINS((CC.FirstName, CC.LastName), '"*yog*"') -- 替换为实际匹配条件
  ) filtered_customers
  INNER JOIN BillingInfo B ON B.CustomerId = filtered_customers.Id
  ORDER BY B.TransactionId
  OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
  

内容的提问来源于stack exchange，提问作者Yogeswaran K