代码之家 › 专栏 › 技术社区 › Rob Sanders

Couchbase 5.1 N1QL或不使用索引的运算符

n1ql couchbase database

Rob Sanders · 技术社区 · 6 年前

我有一个奇怪的例子,我的couchbase服务器中有一个索引,它是用命令构建的:

CREATE INDEX `idx_metadata` ON `dev`(`email`,`nickname`,`channelUuid`) WHERE (`type` = "user_metadata") WITH { "defer_build":true }`

如果我运行查询:

SELECT channelUuid FROM `dev` WHERE type="user_metadata" AND email="foo" AND nickname="bar"

然后它使用索引并按预期工作。

如果我简单地将AND更改为或类似:

SELECT channelUuid FROM `dev` WHERE type="user_metadata" AND (email="foo" OR nickname="bar")

然后它使用主索引,而不是专用索引。

根据要求,这里是对和查询的解释

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan2",
        "index": "idx_metadata",
        "index_id": "d6e2fb94ae221335",
        "index_projection": {
          "primary_key": true
        },
        "keyspace": "dev",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"test1@my-email.com\"",
                "inclusion": 3,
                "low": "\"test1@my-email.com\""
              },
              {
                "high": "\"Badger\"",
                "inclusion": 3,
                "low": "\"Badger\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Fetch",
        "keyspace": "dev",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "(((`dev`.`type`) = \"user_metadata\") and (((`dev`.`email`) = \"test1@my-email.com\") and ((`dev`.`nickname`) = \"Badger\")))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "self",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT * FROM `dev` WHERE type=\"user_metadata\" AND (email=\"test1@my-email.com\" AND nickname=\"Badger\");"
}

对于OR查询:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "PrimaryScan",
        "index": "#primary",
        "keyspace": "dev",
        "namespace": "default",
        "using": "gsi"
      },
      {
        "#operator": "Fetch",
        "keyspace": "dev",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "(((`dev`.`type`) = \"user_metadata\") and (((`dev`.`email`) = \"test1@my-email.com\") or ((`dev`.`nickname`) = \"Badger\")))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "self",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT * FROM `dev` WHERE type=\"user_metadata\" AND (email=\"test1@my-email.com\" OR nickname=\"Badger\");"
}

1 回复 | 直到 6 年前

vsr 6 年前

第二个或没有前导索引键有一个谓词。

You can create another index and it uses both indexes
CREATE INDEX `idx_metadata2` ON `dev`(`nickname`, `email`,`channelUuid`) WHERE (`type` = "user_metadata") WITH { "defer_build":true }`

或

SELECT channelUuid 
FROM `dev` 
WHERE type="user_metadata" AND email IS NOT NULL AND (email="foo" OR nickname="bar")

说明:

"k1" {"a":5}
"k2" {"b":10}
"k3" {"a":20, "b":10}
CREATE INDEX ix1 ON default(a,b);
When leading index key value is MISSING that document will not be indexed. i.e. ix1 will have only entries for "k1", "k3"

SELECT * FROM default WHERE a = 5 OR b = 10;

Above query can't use the index ix1 because due to OR clause. AND should be able to use ix1.
predicate a = 5 can use index ix1 and produce right results.
predicate b = 10  will result "k2", "k3" But index doesn't have "k2", can't use the index ix1

Bottom line to use index each OR clause predicate must have leading key as predicate.

If you create the following index.

CREATE INDEX ix2 ON default(b,a);

 a = 5 uses ix1 and b = 10 uses ix2 and does UNION.