如何正确使用弹簧数据的辅助索引

Question

我有一个名为order的沙发桶，其中包含近2000个文档，我为这个桶创建了一个二级索引(idx_customer)，以优化我的查询：

查询在couchbase查询监视器中执行时命中索引。

但我认为当从spring数据存储库中播放时，它会错过索引。主要是因为存储库中的排序和分页使我的查询能够转换为其他缺少索引的查询。

CREATE INDEX idx_customer ON `order` (
buyer.contact.firstName , 
buyer.contact.lastName , 
ALL DISTINCT ARRAY aoc.`communicationValue` FOR aoc IN buyer.contact.communicationChannel
WHEN aoc.`communicationChannelCode`= "EMAIL" END)
WHERE _class = "com.lbk.entities.OrderEntity"

我使用这个存储库的spring数据在我的spring-boot应用程序中查询订单：

import com.lbk.entities.OrderEntity;
import com.lbk.entities.OrderMetadataEntity;
import org.springframework.data.couchbase.core.query.N1qlPrimaryIndexed;
import org.springframework.data.couchbase.core.query.N1qlSecondaryIndexed;
import org.springframework.data.couchbase.core.query.Query;
import org.springframework.data.couchbase.core.query.ViewIndexed;
import org.springframework.data.couchbase.repository.CouchbasePagingAndSortingRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import java.util.List;
@ViewIndexed(designDoc = "orderEntity")
@N1qlSecondaryIndexed(indexName = "idx_customer")
@N1qlPrimaryIndexed
public interface OrdersRepository extends CouchbasePagingAndSortingRepository<OrderEntity, String> {
List<OrderMetadataEntity> findAllBy();
Page<OrderMetadataEntity> findAllBy(Pageable page);
@Query("#{#n1ql.selectEntity} WHERE #{#n1ql.filter} "
+ "AND orderTypeCode = $1 "
+ "AND (orderCategory != $2 OR orderCategory is not valued ) "
+ "AND buyer.contact.firstName is not null"
)
Page<OrderMetadataEntity> findOrders(String orderTypeCode, String excludedOrderCategory, Pageable page);
@Query("#{#n1ql.selectEntity} WHERE #{#n1ql.filter} "
+ "AND creationDateTime >= STR_TO_MILLIS($1) AND creationDateTime <= STR_TO_MILLIS($2) "
+ "AND orderTypeCode = $3 "
+ "AND (orderCategory != $4 OR orderCategory is not valued )"

+"AND buyer.contact.firstName不为null")页面查找OrdersByCreationDateTimeBetween(字符串开始，字符串结束，字符串orderTypeCode，字符串excludedOrderCategory，可分页页面)；

}

但性能和日志显示，对于sur，我没有使用索引有什么问题？以及如何从我的存储库中正确查询二级索引？

对于此分页的N1QL查询：

@Query("#{#n1ql.selectEntity} WHERE #{#n1ql.filter} "
+ "AND orderTypeCode = $1 "
+ "AND (orderCategory != $2 OR orderCategory is not valued ) "
+ "AND buyer.contact.firstName is not null")
Page<OrderMetadataEntity> findOrders(String orderTypeCode, String excludedOrderCategory, Pageable page);

我在日志中有三个流动的查询：

1. 查询文档计数
2. 查询以获取文档的第一页
3. 查询以获取子文档(SELECT ARRAY_LENGTH(orderLineItem))(我想知道为什么！！)

以下是春季数据日志：

Executing N1QL query: {"args":["%mohammed%","%mohammed%",null,"220","EXECLUDED_CATEGORY"],"statement":"SELECT COUNT(*) AS count FROM `order` WHERE `_class` = \"com.lbk.entities.OrderEntity\" AND ( LOWER(buyer.contact.firstName) LIKE $1 OR LOWER(buyer.contact.lastName) LIKE $2 OR ANY communicationChannel IN buyer.contact.communicationChannel SATISFIES ( communicationChannel.communicationChannelCode = 'EMAIL' AND communicationChannel.communicationValue  = $3 ) END )  AND orderTypeCode = $4 AND (orderCategory != $5 OR O2.orderCategory is not valued )AND creationDateTime in (select RAW max(O2.creationDateTime) from `order` O2 WHERE ( LOWER(O2.buyer.contact.firstName) LIKE $1 OR LOWER(O2.buyer.contact.lastName) LIKE $2 OR ANY communicationChannel IN O2.buyer.contact.communicationChannel SATISFIES ( communicationChannel.communicationChannelCode = 'EMAIL' AND communicationChannel.communicationValue  = $3 ) END )  AND ANY communicationChannel IN O2.buyer.contact.communicationChannel SATISFIES ( communicationChannel.communicationChannelCode = 'EMAIL' AND communicationChannel.communicationValue is not null ) END AND O2.orderTypeCode = $4 AND (O2.orderCategory != $5 OR O2.orderCategory is not valued) group by ( ARRAY item.communicationValue FOR item IN O2.buyer.contact.communicationChannel WHEN item.communicationChannelCode = 'EMAIL' END ) )AND buyer.contact.firstName IS NOT NULL","scan_consistency":"statement_plus"}
Executing N1QL query: {"args":["%mohammed%","%mohammed%",null,"220","EXECLUDED_CATEGORY"],"statement":"SELECT META(`order`).id AS _ID, META(`order`).cas AS _CAS, `order`.* FROM `order` WHERE `_class` = \"com.lbk.entities.OrderEntity\" AND ( LOWER(buyer.contact.firstName) LIKE $1 OR LOWER(buyer.contact.lastName) LIKE $2 OR ANY communicationChannel IN buyer.contact.communicationChannel SATISFIES ( communicationChannel.communicationChannelCode = 'EMAIL' AND communicationChannel.communicationValue  = $3 ) END )  AND orderTypeCode = $4 AND (orderCategory != $5 OR O2.orderCategory is not valued )AND creationDateTime in (select RAW max(O2.creationDateTime) from `order` O2 WHERE ( LOWER(O2.buyer.contact.firstName) LIKE $1 OR LOWER(O2.buyer.contact.lastName) LIKE $2 OR ANY communicationChannel IN O2.buyer.contact.communicationChannel SATISFIES ( communicationChannel.communicationChannelCode = 'EMAIL' AND communicationChannel.communicationValue  = $3 ) END )  AND ANY communicationChannel IN O2.buyer.contact.communicationChannel SATISFIES ( communicationChannel.communicationChannelCode = 'EMAIL' AND communicationChannel.communicationValue is not null ) END AND O2.orderTypeCode = $4 AND (O2.orderCategory != $5 OR O2.orderCategory is not valued) group by ( ARRAY item.communicationValue FOR item IN O2.buyer.contact.communicationChannel WHEN item.communicationChannelCode = 'EMAIL' END ) )AND buyer.contact.firstName IS NOT NULL ORDER BY `creationDateTime` DESC LIMIT 18 OFFSET 0","scan_consistency":"statement_plus"}
Executing N1QL query: {"args":["069cf983-8ed7-4b8f-845d-175593d4ca49"],"statement":"SELECT ARRAY_LENGTH(orderLineItem) FROM `order` WHERE `_class` = \"com.lbk.entities.OrderEntity\" AND META().id = $1","scan_consistency":"statement_plus"}

感谢

Answer 1

查询将无法使用idx_customer索引，因为它不符合条件。请中的"在Couchbase N1QL中设计查询索引"https://blog.couchbase.com/n1ql-practical-guide-second-edition

该索引是部分索引，只有_class="com.lbk.entities.OrderEntity"的文档的条目，但您的查询都没有该谓词，因此无法使用该索引。此外，查询谓词(谓词的每个OR部分)必须有一个前导索引键作为查询谓词才能使用索引。对于日志中的查询，情况并非如此。如果需要，可以尝试非部分索引。

CREATE INDEX idx_customer1 ON `order` (_class, 
buyer.contact.firstName , 
buyer.contact.lastName , 
ALL DISTINCT ARRAY aoc.`communicationValue` FOR aoc IN buyer.contact.communicationChannel
WHEN aoc.`communicationChannelCode`= "EMAIL" END);