Couchbase JOIN查询偏移量增大时性能优化求助

问题背景 #

正在优化一条Couchbase JOIN查询语句，当OFFSET（偏移量）增大时性能明显下降，使用Spring Data连接Couchbase数据库。

查询语句 #

SELECT  *
FROM    document_1 AS doc_1
JOIN    document_1 AS doc_2 
ON      doc_1.id_col_11 = doc_2.id_col_21
AND     doc_1.id_col_12 = doc_2.id_col_22
WHERE   doc_1.id_col_13 = 'ABC'
AND     doc_1.id_col_14= 'XYZ'
ORDER BY doc_1.created_date, document_1.id
OFFSET 0
LIMIT 20

注意：ORDER BY子句中的document_1.id疑似笔误，应为doc_1.id，建议修正，否则可能导致排序逻辑错误或额外性能开销。

已创建索引 #

CREATE idx_1 ON document_1 (id_col_11, id_col_13, id_col_14);
CREATE idx_2 ON document_1 (id_col_21, id_col_22);

性能测试数据 #

优化建议 #

• 替换OFFSET为键集分页：OFFSET的机制是跳过前N条数据，当N很大时需要扫描并丢弃大量数据，性能开销极高。改用键集分页，以上一页最后一条记录的created_date和id作为下一页的过滤条件，直接定位到起始位置：

  SELECT  需要的字段列表
  FROM    document_1 AS doc_1
  JOIN    document_1 AS doc_2 
  ON      doc_1.id_col_11 = doc_2.id_col_21
  AND     doc_1.id_col_12 = doc_2.id_col_22
  WHERE   doc_1.id_col_13 = 'ABC'
  AND     doc_1.id_col_14= 'XYZ'
  AND     (doc_1.created_date > '上一页最后created_date' 
          OR (doc_1.created_date = '上一页最后created_date' AND doc_1.id > '上一页最后id'))
  ORDER BY doc_1.created_date, doc_1.id
  LIMIT 20
  

  这种方式能利用索引直接定位，避免扫描无关数据，性能不受偏移量大小影响。

• 创建覆盖索引：当前索引未覆盖查询的过滤、排序和连接字段，导致查询需要回表读取主文档。创建包含所有必要字段的覆盖索引：

  CREATE INDEX idx_filter_join_sort ON document_1 (id_col_13, id_col_14, created_date, id, id_col_11, id_col_12);
  

  索引字段顺序遵循“过滤优先、排序次之、关联字段最后”的原则，让查询完全从索引获取数据，无需访问主文档，大幅提升性能。

• **避免SELECT ***：只查询业务需要的字段，减少数据传输量和文档解析开销，尤其是当文档包含大量非必要字段时，效果显著。

• 验证JOIN基数：检查JOIN操作产生的中间结果数量，如果doc_1匹配的doc_2数量过多，会导致大量数据处理。可以考虑调整数据模型（比如嵌入关联文档）或增加过滤条件减少JOIN的数据集。

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