java – 使用参数化IN子句时N1QL查询超时
使用Couchbase服务器4.1.0(和4.5), Java
SDK 2.2.8(也尝试使用2.2.7,2.3.1和2.3.3),我有一个利用二级索引的查询,当我运行我的运行时运行正常代码本地甚至通过CBQ(CBQ大约需要3毫秒)在AWS服务器上.但是,在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 rep诊断出的潜在问题.
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.
翻译自：https://stackoverflow.com/questions/38845615/n1ql-query-times-out-when-using-parameterized-in-clause
正文到此结束
- 本文标签： https 代码 key client Transport id Proxy web ip UI json explain queue CXF Statement apache servlet HBase URLs Connection message Collection CountDownLatch java jetty cat Select Amazon Job IO ArrayList spring eclipse Travel parse final ACE NIO 索引 rmi 测试 tar HTML 参数 删除 ORM session App list http Service 本质 翻译 js CTO value core Security bug 服务器
- 版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
- 本文海报： 生成海报一 生成海报二
热门推荐
相关文章
近期评论
-
666
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
