Google Cloud SQL超大表下SQLAlchemy查询执行过慢问题求助

你当前性能瓶颈的核心原因是%{}%格式的前后通配模糊查询，无法利用普通B树索引，每次查询都会触发全表扫描：

• 待查询URL存在时，扫描到匹配数据就会提前返回，所以近期新增的记录查询效率尚可
• 待查询URL不存在时，必须扫描完整个表的所有数据才会返回无结果，因此耗时可达分钟级
  另外你代码中的yield_per(200)配置完全没有作用，你只需要取第一条结果，该配置只会增加额外的数据库交互开销，没有任何性能收益。

1. 优先确认业务查询需求是否为精确匹配 #

如果你的业务实际是判断完全相同的URL是否存在，只是误用了like模糊查询，直接改为精确匹配即可：

Session = sessionmaker(bind=engine)
matching_url = None
with Session.begin() as session:
    matching_url = session.query(Link.id).filter(Link.URL == url).first()

只要给Link.URL字段创建普通B树索引，查询耗时就能降到毫秒级。如果URL长度较大，也可以新增一个url_hash字段存储URL的MD5/SHA1哈希值，查询时先匹配哈希再匹配原URL，索引体积更小、查询速度更快。

2. 若确实需要模糊匹配 #

方案A：调整为前缀匹配 #

如果业务允许只匹配URL以指定内容开头，将通配符改为仅后缀匹配：

formatted_url = "{}%".format(url)

这种查询可以直接使用普通B树索引，性能和精确匹配相当。

方案B：使用支持模糊匹配的专用索引 #

如果必须要全模糊匹配（URL任意位置包含指定内容），根据你使用的数据库创建对应索引：

• MySQL：给URL字段创建全文索引，查询时用MATCH() AGAINST()语法替代like
• PostgreSQL：安装pg_trgm插件，给URL字段创建GIN/GIST类型的Trigram索引，索引创建后like '%xxx%'也可以走索引查询

对应PostgreSQL的查询示例：

Session = sessionmaker(bind=engine)
matching_url = None
with Session.begin() as session:
    matching_url = session.query(Link.id).filter(Link.URL.like(f"%{url}%")).first()

索引创建SQL参考：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_link_url_trgm ON link USING GIN (url gin_trgm_ops);

3. 辅助优化手段 #

• 高频查询可以增加Redis或本地内存缓存，缓存已存在URL的查询结果，命中缓存无需查库
• 定期归档历史冷数据，缩减当前业务表的总数据量
• 读写分离，将这类查询请求分流到从库执行，避免影响主库的写入操作

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