优化Couchbase大数据集查询：将执行时间缩短至1秒内

问题背景 #

• 桶内总记录数：4,300,000条
• 当前查询执行时间：5-7秒
• RAM配置：分配90GB，已使用32GB
• 磁盘已使用：20GB
• 已为父、子文档创建全局二级索引（GSI）

数据结构示例 #

{"document_id" : "p-001",
"document_desc" : "parent doc 1",
"document_type" : "parent"
},
{
"document_id" : "c-001",
"parent_document_id" : "p-001",
"document_desc" : "child doc of parent 1",
"document_type" : "child",
"document_status" : "NEW",
"customer_id" : "c-001"
},
{
"document_id" : "c-002",
"parent_document_id" : "p-001",
"document_desc" : "child doc of parent 1",
"document_type" : "child",
"document_status" : "NEW",
"customer_id" : "c-001"
}

当前查询语句 #

SELECT * FROM 
test_document AS parent 
JOIN 
test_document AS child
ON child.parent_document_id = parent.document_id
WHERE child.customer_id = 'c-001'
AND child.document_status IN ('NEW', 'OLD', 'OLDER')

优化方案 #

1. 精准优化全局二级索引（GSI） #

• 为子文档创建覆盖索引，直接包含过滤条件和关联字段，避免回表查询，同时通过document_type缩小索引范围：

  CREATE INDEX idx_child_customer_status_parent ON test_document(parent_document_id)
  WHERE document_type = 'child'
  AND customer_id IS NOT NULL
  AND document_status IN ('NEW', 'OLD', 'OLDER')
  INCLUDE (customer_id, document_status);
  

• 为父文档创建定向索引，快速定位目标父文档：

  CREATE INDEX idx_parent_id ON test_document(document_id)
  WHERE document_type = 'parent';
  

2. 调整查询语句逻辑 #

• 优先过滤子文档再关联父文档，让查询先利用子文档索引筛选出数据，减少后续关联的数据量：

  SELECT parent.*, child.*
  FROM test_document AS child
  JOIN test_document AS parent
  ON parent.document_id = child.parent_document_id
  WHERE child.document_type = 'child'
    AND child.customer_id = 'c-001'
    AND child.document_status IN ('NEW', 'OLD', 'OLDER')
    AND parent.document_type = 'parent';
  

• 避免使用SELECT *，只查询业务需要的字段，减少数据传输和处理开销：

  SELECT parent.document_id, parent.document_desc,
         child.document_id, child.document_desc, child.document_status
  FROM test_document AS child
  JOIN test_document AS parent
  ON parent.document_id = child.parent_document_id
  WHERE child.document_type = 'child'
    AND child.customer_id = 'c-001'
    AND child.document_status IN ('NEW', 'OLD', 'OLDER')
    AND parent.document_type = 'parent';
  

3. 优化数据模型设计 #

• 若父子文档关联固定且查询频繁，可将子文档嵌套嵌入父文档，彻底消除JOIN操作：

  {
    "document_id": "p-001",
    "document_desc": "parent doc 1",
    "document_type": "parent",
    "children": [
      {
        "document_id": "c-001",
        "document_desc": "child doc of parent 1",
        "document_status": "NEW",
        "customer_id": "c-001"
      },
      {
        "document_id": "c-002",
        "document_desc": "child doc of parent 1",
        "document_status": "NEW",
        "customer_id": "c-001"
      }
    ]
  }
  

• 嵌套后的查询语句，性能会大幅提升：

  SELECT *
  FROM test_document
  WHERE document_type = 'parent'
    AND ANY child IN children SATISFIES 
        child.customer_id = 'c-001' 
        AND child.document_status IN ('NEW', 'OLD', 'OLDER') END;
  

4. 验证执行计划与资源配置 #

• 用EXPLAIN查看查询执行计划，确认是否正确使用了创建的索引，避免全表扫描：

  EXPLAIN SELECT parent.*, child.*
  FROM test_document AS child
  JOIN test_document AS parent
  ON parent.document_id = child.parent_document_id
  WHERE child.document_type = 'child'
    AND child.customer_id = 'c-001'
    AND child.document_status IN ('NEW', 'OLD', 'OLDER');
  

• 检查集群内存利用情况，当前RAM仅使用32GB（分配90GB），可调整索引内存配额，让更多索引数据驻留内存，减少磁盘IO。

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