字符串匹配查询全量行如何提升性能？评论系统查询逻辑及表结构优化

1. 优化现有查询逻辑，避免无效模糊匹配 #

你当前使用的WHERE origin LIKE 'https://somesite.com/page-1'没有通配符，本质和origin = 'https://somesite.com/page-1'精确匹配的效果完全一致，直接改成精确匹配可以获得更高的索引利用率和查询性能。

2. 单表结构优化（可支撑千万级以下数据量） #

完全不需要为每个站点单独建表，这种动态建表的方案后续加字段、做跨表统计的运维成本会指数级上升，单表存储足以支撑绝大多数业务场景，仅需做如下调整即可：

调整后表结构 #

table_name | column_name |          data_type          | 说明
------------+-------------+-----------------------------|------
 Comment    | id          | text                        | 主键，保持唯一属性
 Comment    | origin      | text                        | 页面完整访问地址
 Comment    | site_domain | text                        | 新增字段，存储所属站点域名，例：somesite.com
 Comment    | body        | text                        | 评论正文内容
 Comment    | create_time | timestamp with time zone    | 新增字段，存储评论发布时间，用于排序

索引配置 #

• 给origin字段加普通索引，单表场景下精确匹配单页评论的查询速度可保持在毫秒级，千万级数据量下也不会有明显性能下降
• 给site_domain加普通索引，满足后续按站点批量查询评论、统计站点评论量的需求
• 若业务默认按时间倒序展示评论，可直接创建(origin, create_time)联合索引，避免查询后再排序的额外开销

3. 超大数据量扩展方案 #

如果后续评论数据量超过千万级，再考虑分表也不迟，推荐使用按site_domain哈希分表方案：

• 提前固定分N张Comment表（例：8张、16张），不需要随站点新增动态建表
• 写入和查询时先通过site_domain的哈希值计算要操作的表编号，性能损失极低
• 后续改表结构只需要遍历N张表执行DDL即可，运维成本远低于动态建表方案

4. 可选极致优化方案 #

如果origin字段重复率极高（同一个页面存在上百条甚至更多评论），可以新增字典表压缩存储：

• 新增Page表，存储id、site_domain、page_url三个字段，每个页面对应唯一id
• Comment表删除origin字段，新增page_id字段关联Page表主键
• 该方案可以大幅降低Comment表的存储占用，关联查询性能也不会有明显损失，适合评论量极大的业务场景

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