个人中心
gpt4 book ai didi

java - 使用参数化 IN 子句时 N1QL 查询超时

转载 作者：塔克拉玛干 更新时间：2023-11-03 03:56:38 24 4
gpt4 key购买 nike

使用 Couchbase 服务器 4.1.0(和 4.5)、Java SDK 2.2.8(也尝试使用 2.2.7、2.3.1 和 2.3.3)，我有一个利用二级索引的查询，当我在本地甚至通过 AWS 服务器上的 CBQ(CBQ 大约需要 3 毫秒)运行我的代码。但是，当我在 AWS 上运行我的应用程序时，我得到一个 TimeOutException 并且只有一个查询超时，其他查询则没有。请参阅下面的详细信息。

可能值得注意的是，我的 Couchbase 设置有 3 个桶。

示例文档:

"bucketName": {
    "userName": "User_A",
    "MessageContent": "This is a message",
    "docType": "msg",
    "ParentMsgId": "1234",
    "MsgType": "test",
    "expireTimestamp": 1454975772613,
    "publishTimestamp": 1455322362028,
    "id": "145826845",
    "urls": [],
    "subject": "this is a subject",
    "type": 1,
    "GroupId": "Group_1"
}

二级索引:

CREATE INDEX `indexName` ON `bucketName`(`ParentMsgId`,`docType`,`publishTimestamp`) USING GSI

从 N1qlQuery#n1ql() 中提取的示例查询

{"statement":
"select count(*) as msgCount from bucketName 
where ParentMsgId is not missing and docType = 'msg' 
and ParentMsgId IN $parentId 
and publishTimestamp between $startTime and $endTime
","$endTime":1470726861816,
  "$startTime":1470640461816,
  "$parenIds":["fa11845b-9ea5-4778-95fe-e7206843c69b"]
}

Java代码

public static final String COUNT_STATEMENT = "select count(*) as count " +
            "from bucketName " +
            "where ParentMsgId is not missing " + 
            "and docType = 'msg' " +
            "and ParentMsgId IN $parentIds " + 
            "and publishTimestamp between $startTime and $endTime";

public int getCountForDuration(Long startTime, Long endTime, Collection<String> parentIds){
    List<String> idList = new ArrayList<>(parentIds);
    JsonObject placeHolders = JsonObject.create()
                                        .put("parentIds", JsonArray.from(idList))
                                        .put("startTime", startTime)
                                        .put("endTime", endTime);
    N1qlQuery query = N1qlQuery.parameterized(COUNT_STATEMENT, placeHolders)            
    N1qlQueryResult result = bucket.query(query);
    ...
}

查询解释结果

cbq> explain select count(*) as msgCount from bucketName where ParentMsgId is not missing and docType = 'msg' and ParentMsgId IN ["01b88f7f-4de6-4daa-9562-a2c902e818ad"] and publishTimestamp between 1466445409000 and 1466531809000;
{
    "requestID": "61afcf02-3b3d-4c8a-aec6-b76c4c1f7b17",
    "signature": "json",
    "results": [
        {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "IndexScan",
                    "index": "indexName",
                    "keyspace": "bucketName",
                    "namespace": "default",
                    "spans": [
                        {
                            "Range": {
                                "High": [
                                    "successor(\"01b88f7f-4de6-4daa-9562-a2c902e818ad\")"
                                ],
                                "Inclusion": 1,
                                "Low": [
                                    "\"01b88f7f-4de6-4daa-9562-a2c902e818ad\""
                                ]
                            }
                        }
                    ],
                    "using": "gsi"
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Fetch",
                                "keyspace": "bucketName",
                                "namespace": "default"
                            },
                            {
                                "#operator": "Filter",
                                "condition": "(((((`bucketName`.`ParentMsgId`) is not missing) and ((`bucketName`.`docType`) = \"msg\")) and ((`bucketName`.`ParentMsgId`) in [\"01b88f7f-4de6-4daa-9562-a2c902e818ad\"])) and ((`bucketName`.`publishTimestamp`) between 1466445409000 and 1466531809000))"
                            },
                            {
                                "#operator": "InitialGroup",
                                "aggregates": [
                                    "count(*)"
                                ],
                                "group_keys": []
                            }
                        ]
                    }
                },
                {
                    "#operator": "IntermediateGroup",
                    "aggregates": [
                        "count(*)"
                    ],
                    "group_keys": []
                },
                {
                    "#operator": "FinalGroup",
                    "aggregates": [
                        "count(*)"
                    ],
                    "group_keys": []
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "as": "msgCount",
                                        "expr": "count(*)"
                                    }
                                ]
                            },
                            {
                                "#operator": "FinalProject"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.748194ms",
        "executionTime": "2.660232ms",
        "resultCount": 1,
        "resultSize": 3274
    }
}

日志

java.lang.Thread.run(Thread.java:745)
org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:533)
org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:598)
org.eclipse.jetty.server.nio.BlockingChannelConnector$BlockingChannelEndPoint.run(BlockingChannelConnector.java:293)
org.eclipse.jetty.server.BlockingHttpConnection.handle(BlockingHttpConnection.java:50)
org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:218)
org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:582)
org.eclipse.jetty.server.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:919)
org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:441)
org.eclipse.jetty.server.Server.handle(Server.java:349)
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:110)
org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:149)
org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:875)
org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:186)
org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:409)
org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:117)
org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:941)
org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:227)
org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:483)
org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)
org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:70)
org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:480)
org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:547)
org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:201)
javax.servlet.http.HttpServlet.service(HttpServlet.java:693)
org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:150)
org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:225)
org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:130)
org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:194)
org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:214)
org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:237)
org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:262)
org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:94)
org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58)
org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:89)
org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:168)
org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180)
java.lang.reflect.Method.invoke(Method.java:498)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
biz.te2.core.services.beacon.impl.BeaconResource.getVenuesBeaconData(BeaconResource.java:105)
xxx.xxx.xxx.getBeaconHealthForRangeAndVenue(BeaconHealthServiceImpl.java:40)
xxx.xxx.xxx..getAllMessagesCount(BeaconHealthServiceImpl.java:80)
com.sun.proxy.$Proxy146.getMessageCountForDuration(Unknown Source)
org.apache.aries.proxy.impl.ProxyHandler.invoke(ProxyHandler.java:78)
org.apache.aries.proxy.impl.DefaultWrapper.invoke(DefaultWrapper.java:31)
org.apache.aries.proxy.impl.ProxyHandler$1.invoke(ProxyHandler.java:50)
java.lang.reflect.Method.invoke(Method.java:498)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
xxx.xxx.xxx.getMessageCountForDuration(MessageCouchbaseRepo.java:364)
xxx.xxx.xxx.getN1qlQueryRows(MessageCouchbaseRepo.java:372)
com.couchbase.client.java.CouchbaseBucket.query(CouchbaseBucket.java:582)
com.couchbase.client.java.CouchbaseBucket.query(CouchbaseBucket.java:656)
com.couchbase.client.java.util.Blocking.blockForSingle(Blocking.java:74)
java.util.concurrent.CountDownLatch.await(CountDownLatch.java:277)
java.util.concurrent.locks.AbstractQueuedSynchronizer.tryAcquireSharedNanos(AbstractQueuedSynchronizer.java:1328)
java.util.concurrent.locks.AbstractQueuedSynchronizer.doAcquireSharedNanos(AbstractQueuedSynchronizer.java:1037)
java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)

这是一个查询示例，它按照要求正常工作。

public static final String EXPERIENCE_ID_STATEMENT = "Select id " +
            "from read as exp " +
            "where id is not missing " +
            "and docType = 'experience' " +
            "and venueId = $venueId " +
            "and exp.rule.poiIds is not missing " +
            "and any poi in exp.rule.poiIds satisfies poi = $poiId end";

与其他查询相比，此查询的唯一独特之处在于它使用 IN 子句并通过参数化的 JsonArray 接收字段。

没有网络延迟。我不认为这是一个问题，因为其他查询正在运行，并且它们基本上是一个接一个地链接调用(我还测试了单独运行这个查询，但它仍然执行得非常慢)。

App 和 CB 都在 AWS 上。我已经在同一 AWS 服务器和不同服务器上进行了测试，并且在这两种情况下都存在问题。我有一个客户在 AWS 上，但不在 AWS 上，两者都有问题。 客户端 是指一种调用我的应用程序的机制。调用查询时它仍然会超时。

我的 collectinfo couchbase 日志在这里 s3.amazonaws.com/cb-customers/TE2/

最佳答案

我发现它与 IN 子句中值的参数化有关。当我从查询中删除参数化时，我能够像 CBQ 一样快地运行。我唯一的选择是放弃参数化。我试图调整二级索引，将 ParentMsgId 移动到字段列表的末尾，这对我的情况没有帮助。

从 couchbase 代表诊断出的潜在问题。

the underlying issue is that once you put the IN values in a parameter, the optimiser can't assume the IN clause only has one value. Since experienceId leads the index, we can only use an equality to travel down the index and start counting the keys that apply because we would have to skip and scan. I would suggest creating the index as (docType, publishTimestamp, ParentMsgId), that way we can still scan the index on the conditions set on docType, publishTimestamp, and apply the filter on experienceId later on in the pipeline.

关于java - 使用参数化 IN 子句时 N1QL 查询超时，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/38845615/

24 4 0
文章推荐： java - 删除 Jetty 上的 Html 扩展
文章推荐： algorithm - 伪代码:输入 3 个整数，乘以最大的 2
文章推荐： string - 我有一个特定的 1's and 0' 字符串，我想在另一个字符串中找到最合适的匹配项，最大误差为 20%
文章推荐： java - 如何使用 JAX-RS 和 Spring 编写正确/可靠的事务代码
塔克拉玛干
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com