我们如何在66毫秒内查询1680万SIRENE企业

It looks like only the source line was provided. Could you please share the text you’d like translated? Once I have the content, I’ll translate it into Simplified Chinese while preserving the formatting, markdown, and any code blocks or URLs.

挑战

我们的 establishment 表拥有 1680 万行数据。用户需要按以下字段进行搜索：

公司名称搜索必须能够处理：

• 部分匹配 – “Total” 应能找到 “TotalEnergies SE”
• 拼写错误 – “Miclein” 应能找到 “Michelin”
• 不区分重音 – “Societe Generale” 应匹配 “Société Générale”

天真的方法：ILIKE

SELECT *
FROM georefer.establishment
WHERE company_name ILIKE '%total%'
LIMIT 25;

EXPLAIN ANALYZE

Seq Scan on establishment
  Filter: (company_name ~~* '%total%')
  Rows Removed by Filter: 16799975
  Planning Time: 0.1ms
  Execution Time: 12,847ms

12.8 秒 → 对 1680 万 行进行完整顺序扫描。不可用。

进入 pg_trgm

PostgreSQL 的 pg_trgm 扩展会把字符串拆分为三元组（3 字符序列），并使用 GIN 索引高效地查找相似字符串。

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_establishment_name_trgm
ON georefer.establishment
USING GIN (company_name gin_trgm_ops);

现在使用三元组相似度：

SELECT *,
       similarity(company_name, 'total') AS sim
FROM georefer.establishment
WHERE company_name % 'total'
ORDER BY sim DESC
LIMIT 25;

EXPLAIN ANALYZE

Bitmap Heap Scan on establishment
  Recheck Cond: (company_name % 'total')
  -> Bitmap Index Scan on idx_establishment_name_trgm
       Index Cond: (company_name % 'total')
  Planning Time: 0.3ms
  Execution Time: 66ms

66 ms → 提升 194 倍，相较于朴素方法。

导入策略：3 个阶段

导入 1680 万行数据并非易事。我们采用三阶段的方法。

第 1 阶段 – 架构 + 暂存

CREATE TABLE georefer.establishment (
    id               SERIAL PRIMARY KEY,
    siren            VARCHAR(9)  NOT NULL,
    siret            VARCHAR(14) NOT NULL UNIQUE,
    company_name     VARCHAR(255),
    commercial_name  VARCHAR(255),
    legal_form       VARCHAR(10),
    naf_code         VARCHAR(6),
    employee_range   VARCHAR(5),
    postal_code      VARCHAR(5),
    city             VARCHAR(100),
    department_code  VARCHAR(3),
    is_headquarters  BOOLEAN DEFAULT FALSE,
    is_active        BOOLEAN DEFAULT TRUE,
    created_date     DATE,
    last_update      DATE
);

第 2 阶段 – 批量 COPY

COPY georefer.establishment
    (siren, siret, company_name, ...)
FROM '/tmp/sirene_active.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

COPY 的速度是批量 INSERT 的 10–50 倍。1680 万行大约在 8 分钟 内完成加载。

第 3 阶段 – 创建索引

在批量导入 之后 创建索引（提前构建会拖慢加载速度）。

-- 精确查询
CREATE INDEX idx_establishment_siren ON georefer.establishment(siren);
CREATE INDEX idx_establishment_siret ON georefer.establishment(siret);

-- 地理过滤
CREATE INDEX idx_establishment_postal ON georefer.establishment(postal_code);
CREATE INDEX idx_establishment_dept   ON georefer.establishment(department_code);
CREATE INDEX idx_establishment_city   ON georefer.establishment(city);

-- 模糊名称搜索
CREATE INDEX idx_establishment_naf   ON georefer.establishment(naf_code);
CREATE INDEX idx_establishment_name_trgm
    ON georefer.establishment USING GIN (company_name gin_trgm_ops);

综合查询：名称 + 地理过滤

SELECT *,
       similarity(company_name, 'boulangerie') AS sim
FROM georefer.establishment
WHERE company_name % 'boulangerie'
  AND department_code = '75'
  AND is_active = true
ORDER BY sim DESC
LIMIT 25;

结果：在约 45 毫秒内返回巴黎所有面包店，即使跨越 1680 万 行。

API 层

Spring Boot 服务通过 REST 暴露搜索功能。

# 按 SIREN 搜索
curl 'https://georefer.io/geographical_repository/v1/companies?siren=552120222' \
  -H 'X-Georefer-API-Key: YOUR_API_KEY'

# 按名称 + 行政区搜索
curl 'https://georefer.io/geographical_repository/v1/companies/search?name=michelin&department_code=63' \
  -H 'X-Georefer-API-Key: YOUR_API_KEY'

示例 JSON 响应

{
  "success": true,
  "data": [
    {
      "siren": "855200507",
      "siret": "85520050700046",
      "company_name": "MANUFACTURE FRANCAISE DES PNEUMATIQUES MICHELIN",
      "naf_code": "22.11Z",
      "employee_range": "5000+",
      "postal_code": "63000",
      "city": "CLERMONT-FERRAND",
      "is_headquarters": true
    }
  ]
}

性能概述

数字仅作示例；实际时间会因硬件和负载而异。

改进

经验教训

• 始终在批量导入后创建索引 – 在此之前创建会使导入速度慢约 10 倍。
• pg_trgm GIN 索引占用大量磁盘 – 我们的 16.8 M 行三元组索引约为 2.3 GB。
• 在创建索引时将 maintenance_work_mem 设置得高一些 – SET maintenance_work_mem = '1GB' 可将索引创建时间减半。
• 在批量加载时 COPY 总是胜过 INSERT – 对超过 10 K 行的数据使用 COPY。

试一试

GEOREFER 通过一个简洁的 REST API 暴露 1680 万条 SIRENE 企业信息：

• 免费套餐：每天 100 次请求，无需信用卡。
• 文档：
• 注册：

AZMORIS Engineering – “经久耐用的软件”