40万级多左连接表多列全文检索+分页统计性能优化咨询

落地实现方案（无需更换数据库，PostgreSQL原生能力即可满足需求） #

40万级数据量完全不需要引入额外检索引擎，用PostgreSQL自带的pg_trgm扩展+辅助表方案即可覆盖所有需求，性能足够支撑到百万级甚至千万级数据规模。

1. 全文检索优化 #

你提到的拼接检索字段+GIN索引的思路完全可行，直接落地即可：

• 首先开启pg_trgm扩展（Windows版PostgreSQL默认自带，无需额外安装）：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

• 新建检索辅助表，存储拼接后的检索内容与关联ID，同时冗余排序字段避免后续关联开销：

CREATE TABLE purchasing_search (
    purchasing_id INT4 PRIMARY KEY,
    search_content TEXT NOT NULL,
    sort_id INT4 NOT NULL,
    CONSTRAINT fk_purchasing_search FOREIGN KEY (purchasing_id) REFERENCES purchasing(id) ON DELETE CASCADE ON UPDATE CASCADE
);

• 为检索字段创建GIN索引，为排序字段创建B树索引：

-- 支持大小写不敏感的局部模糊匹配
CREATE INDEX idx_purchasing_search_content ON purchasing_search USING GIN (search_content gin_trgm_ops);
-- 优化分页排序性能
CREATE INDEX idx_purchasing_search_sort_id ON purchasing_search USING BTREE (sort_id DESC);

• 配置触发器自动同步检索内容：
  分别为purchasing、purchasing_mandant_names、company_codes三张表配置增删改触发器，只要基础表数据发生变更，自动更新purchasing_search表的search_content字段，无需手动维护数据一致性。
  触发器函数示例：

CREATE OR REPLACE FUNCTION sync_purchasing_search()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO purchasing_search (purchasing_id, search_content, sort_id)
    SELECT 
        p.id,
        CONCAT_WS(' ', p.mandant_id::TEXT, pmn.name, p.company_code::TEXT, cc.name, p.sequence_number::TEXT, p.year::TEXT, p.month::TEXT),
        p.id
    FROM purchasing p
    LEFT JOIN purchasing_mandant_names pmn ON p.mandant_id = pmn.id
    LEFT JOIN company_codes cc ON p.company_code = cc.id
    WHERE p.id = COALESCE(NEW.id, OLD.id)
    ON CONFLICT (purchasing_id) DO UPDATE SET search_content = EXCLUDED.search_content, sort_id = EXCLUDED.sort_id;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

• 初始化全量检索数据：

INSERT INTO purchasing_search (purchasing_id, search_content, sort_id)
SELECT 
    p.id,
    CONCAT_WS(' ', p.mandant_id::TEXT, pmn.name, p.company_code::TEXT, cc.name, p.sequence_number::TEXT, p.year::TEXT, p.month::TEXT),
    p.id
FROM purchasing p
LEFT JOIN purchasing_mandant_names pmn ON p.mandant_id = pmn.id
LEFT JOIN company_codes cc ON p.company_code = cc.id
ON CONFLICT (purchasing_id) DO NOTHING;

2. 分页与总数统计优化 #

你提到的先查ID再拉全量数据的思路完全正确，配合上面的辅助表性能提升非常明显：

• 总数统计直接查询辅助表，无需关联多张表：

-- 走GIN索引过滤，40万数据下count基本在毫秒级返回
SELECT COUNT(*) FROM purchasing_search WHERE search_content ILIKE '%你的检索关键词%';

• 分页查询匹配的ID列表：

-- 排序走B树索引，即使跳大页码性能也远高于关联多张表后分页
SELECT purchasing_id FROM purchasing_search 
WHERE search_content ILIKE '%你的检索关键词%'
ORDER BY sort_id DESC
OFFSET 偏移量 LIMIT 每页条数;

• 用拿到的ID列表查询全量业务数据：

-- ID走主键索引，关联查询性能极高
SELECT p.id,
    p.mandant_id AS mandant_code,
    pmn.name AS mandant_name,
    p.company_code,
    cc.name AS company_name,
    p.sequence_number,
    p.year,
    p.month,
    pp.page_path
FROM purchasing p
LEFT JOIN purchasing_pages pp ON p.id = pp.purchasing_id
LEFT JOIN purchasing_mandant_names pmn ON p.mandant_id = pmn.id
LEFT JOIN company_codes cc ON p.company_code = cc.id
WHERE p.id = ANY(ARRAY[上一步拿到的ID列表])
ORDER BY p.id DESC;

3. 扩展建议 #

如果后续数据量涨到千万级以上，再考虑引入Elasticsearch这类专用检索引擎，当前规模下上述方案足够使用，且维护成本极低，完全适配你方Windows服务器环境。

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