SQL Join 中函数使用对性能的影响与优化方法

在日常开发中，经常会遇到这样的场景：

需要在 大小写不敏感 或 格式化字段 的情况下进行表关联。

如果在 JOIN 或 WHERE 中直接使用函数，往往会带来严重的性能问题。

本文用一个新的示例来说明问题和优化方法。

一、问题场景

假设我们有两张表：

• 
  
• 用户表 user_info
  

  user_id   | username
  ----------+------------
  
  1         | Alice
  
  2         | Bob
  
  3         | Charlie
  

  
  
  
• 订单表 order_info
  

  order_id  | buyer_name
  ----------+------------
  1001      | alice
  1002      | BOB
  1003      | dave
  

  
  
  

我们希望根据用户名和买家名称进行关联（忽略大小写）。

原始写法（低效）

SELECT o.order_id, u.user_id, u.username
FROM order_info o
LEFT JOIN user_info u
ON LOWER(o.buyer_name) = LOWER(u.username);

问题：

• 
  
• LOWER() 包裹了字段，导致数据库无法使用索引。
  
• 每一行都要执行函数运算，性能下降。
  

二、优化方法

1. 子查询提前计算

通过子查询生成派生列，再进行关联。

SELECT o.order_id, u.user_id, u.username
FROM (
SELECT order_id, buyer_name, LOWER(buyer_name) AS buyer_name_lower
FROM order_info

) o
LEFT JOIN (
SELECT user_id, username, LOWER(username) AS username_lower
FROM user_info

) u
ON o.buyer_name_lower = u.username_lower;

优点：

• 
  
• 避免在 JOIN 时重复调用函数。
  
• 优化器有机会物化子查询并建立临时索引。
  

2. 建立函数索引（推荐）

如果这种需求非常频繁，可以在表上建立函数索引。

PostgreSQL 示例：

CREATE INDEX idx_username_lower ON user_info(LOWER(username));

CREATE INDEX idx_buyer_name_lower ON order_info(LOWER(buyer_name));

之后即使写：

SELECT ...
FROM order_info o
LEFT JOIN user_info u
ON LOWER(o.buyer_name) = LOWER(u.username);

数据库也能走索引，性能大幅提升。

3. 数据入库时统一格式

如果业务允许，可以在入库时统一转为小写，避免查询时做转换。

INSERT INTO user_info (user_id, username)
VALUES (1, LOWER('Alice'));

这样关联时直接比较即可：

ON o.buyer_name = u.username

三、总结

• 
  
• 在 JOIN 或 WHERE 中直接使用函数，会 导致索引失效，影响性能。
  
• 优化方法：
  
  1. 
     
  2. 子查询提前计算，避免在关联时重复调用函数；
     
  3. 建立函数索引（或虚拟列索引）；
     
  4. 入库时统一数据格式，彻底消除函数依赖。
     
  
  
  

📌 记忆要点：

• 
  
• 函数写在 JOIN → 慢
  
• 子查询提前算 → 好
  
• 函数索引 / 数据规范化 → 最优解