SQL多INNER JOIN性能优化及转为通用可编程函数的实现方案

问题1：多次关联同表的性能影响、优化建议及更优查询方案 #

性能影响 #

• 筛选n个属性就要关联n次file_attribute表，关联次数越多，数据库执行计划生成成本越高，大数量级下很容易出现优化器选错索引、关联顺序不合理的问题，性能下降会非常明显
• 多次关联会放大中间结果集的内存占用，当file_attribute表数据量超过百万级时，查询耗时会呈线性上升

优化建议 #

• 优先加覆盖索引：在file_attribute表上建立联合索引 (attribute_id, value, file_id)，这个索引可以直接覆盖所有属性筛选需要的字段，查询时不需要回表取数据，能把单条件查询的性能拉满
• 替换多次JOIN的查询写法：不用每次加条件就多JOIN一次表，改用「条件过滤+分组聚合校验匹配数」的写法，示例如下：

-- 等价于之前两个属性筛选的查询，不管多少个属性都只需要JOIN一次file_attribute
SELECT f.id, f.name 
FROM file f
INNER JOIN file_attribute fa ON f.id = fa.file_id
WHERE 
  -- 所有要匹配的属性条件都写在OR块里
  (fa.attribute_id = '1aa2a8e9-a004-44bf-9ec7-0c20733380da' AND fa.value = '.pdf')
  OR (fa.attribute_id = '11a2a8e9-a004-44bf-9ec7-0c20733380da' AND fa.value = '101')
GROUP BY f.id, f.name
-- 匹配数等于传入的属性数量，说明所有条件都满足
HAVING COUNT(DISTINCT fa.attribute_id) = 2;

如果不需要属性值模糊匹配的场景，这个写法的性能远好于多次JOIN，属性越多优势越明显。

问题2：封装支持动态属性筛选的自定义函数 #

这里以PostgreSQL为例实现，支持JDBC直接传入JSON格式的筛选参数，兼容0到n个属性筛选，也支持扩展file表字段过滤：

第一步：定义函数（PL/pgSQL实现） #

CREATE OR REPLACE FUNCTION search_files(p_attr_filters jsonb, p_file_filters jsonb DEFAULT '{}'::jsonb)
RETURNS SETOF file AS $$
DECLARE
  v_sql text;
  v_attr_count int;
BEGIN
  -- 基础查询语句
  v_sql := 'SELECT f.* FROM file f WHERE 1=1';

  -- 处理file表的字段筛选，可自行扩展支持的字段，避免SQL注入
  IF p_file_filters ? 'name' THEN
    v_sql := v_sql || format(' AND f.name ILIKE %L', '%' || (p_file_filters->>'name') || '%');
  END IF;
  -- 可继续添加其他file表字段的判断逻辑，比如创建时间、大小等

  -- 处理属性筛选
  v_attr_count := jsonb_array_length(p_attr_filters);
  IF v_attr_count > 0 THEN
    -- 拼接属性过滤条件
    v_sql := v_sql || ' AND EXISTS (
      SELECT 1 FROM file_attribute fa 
      WHERE fa.file_id = f.id 
      AND (';
    -- 遍历属性筛选条件拼接OR块
    FOR i IN 0..v_attr_count-1 LOOP
      IF i > 0 THEN
        v_sql := v_sql || ' OR ';
      END IF;
      v_sql := v_sql || format(
        '(fa.attribute_id = %L AND fa.value = %L)',
        (p_attr_filters->i->>'attribute_id')::uuid,
        p_attr_filters->i->>'value'
      );
    END LOOP;
    v_sql := v_sql || ') GROUP BY fa.file_id HAVING COUNT(DISTINCT fa.attribute_id) = ' || v_attr_count || ')';
  END IF;

  -- 执行动态SQL返回结果
  RETURN QUERY EXECUTE v_sql;
END;
$$ LANGUAGE plpgsql STABLE;

第二步：调用方式 #

JDBC调用时直接把Java里的List

-- 仅按属性筛选
SELECT * FROM search_files('[{"attribute_id": "1aa2a8e9-a004-44bf-9ec7-0c20733380da", "value": ".pdf"}, {"attribute_id": "11a2a8e9-a004-44bf-9ec7-0c20733380da", "value": "101"}]'::jsonb);

-- 同时按文件名和属性筛选
SELECT * FROM search_files(
  '[{"attribute_id": "1aa2a8e9-a004-44bf-9ec7-0c20733380da", "value": ".pdf"}]'::jsonb,
  '{"name": "Verwandlung"}'::jsonb
);

如果用的是MySQL等其他数据库，也可以用对应数据库的存储过程语法实现类似逻辑，参数传递改用JSON类型即可，JDBC都能兼容。

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