单列逗号分隔字符串与分表存储的SQL性能对比咨询

从你给出的SHOWPLAN_XML预估查询成本来看，原单表查询的性能明显更好——数据库给出的查询成本百分比，是指该查询消耗系统资源（CPU、磁盘IO、内存等）占总查询批次资源的比例，数值越低意味着资源消耗越少、执行效率越高。25%的成本远低于分表方案的75%，说明在当前场景下，单表查询更快、更省资源。

为什么分表后成本反而上升？ #

分表的思路本身是为了解决多值字段（逗号分隔的Users）的查询痛点，但这次分表带来的表关联开销抵消了拆分的收益：

• 原单表可以结合InxPValue（匹配PVALUE LIKE '%abc%'）、InxUsers（匹配Users LIKE '%ankit@gmail.com%'）和InxPrDocID（定位DocID）的索引组合，数据库能更高效地过滤出目标数据。
• 分表方案需要执行tblProperties和tblUsersDocument的inner join，即使两张表都有对应索引，关联操作本身需要额外资源来匹配DocID，再加上PVALUE LIKE '%abc%'这类前缀模糊查询的索引效率限制，最终导致预估成本大幅上升。

后续优化参考 #

如果未来数据量增长后，原单表的多值字段查询出现性能瓶颈，可以尝试：

• 在原表的Users字段上创建全文索引，替代LIKE '%xxx%'的模糊匹配，提升多值查询效率。
• 优化分表后的查询逻辑，先过滤tblUsersDocument再关联，减少关联的数据量：

  SELECT p.DocID 
  FROM tblProperties AS p 
  INNER JOIN (
      SELECT DocID FROM tblUsersDocument WHERE User = 'ankit@gmail.com'
  ) AS u ON p.DocID = u.DocID 
  WHERE p.PNAME = 'AssignDocument' AND p.PVALUE LIKE '%abc%';
  

内容的提问来源于stack exchange，提问作者Trimantra Software Solution