Closed cotchan closed 2 years ago
mongodb
chat_room_id
user_id
product_id
identifier
db.participants.createIndex({chat_room_id:1, user_id:1});
Optional<Participant> findByChatRoomIdAndUserId(String chatRoomId, Long userId); boolean existsByChatRoomIdAndUserId(String chatRoomId, Long userId);
db.participants.createIndex({user_id:1});
List<Participant> findAllByUserIdAndOutIsFalseOrderByUpdatedAtDesc(Long userId, Pageable pageable);
> db.participants.getIndexes() [ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" }, { "v" : 2, "key" : { "chat_room_id" : 1, "user_id" : 1 }, "name" : "chat_room_id_1_user_id_1" }, { "v" : 2, "key" : { "user_id" : 1 }, "name" : "user_id_1" } ]
db.participants.explain().find({ $and: [{"chat_room_id": "12345"}, {"user_id": 1} ] });
결과
IXSCAN
indexName
"chat_room_id_1_user_id_1"
"executionStats" : { "executionSuccess" : true, "nReturned" : 0, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 0, "executionStages" : { "stage" : "FETCH", "nReturned" : 0, "docsExamined" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 0, "indexName" : "chat_room_id_1_user_id_1",
db.participants.explain("executionStats").find({ $and: [{"user_id": 1},{"out": false} ]}).sort({"updated_at":-1});
"user_id_1"
"executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 0, "totalKeysExamined" : 1, "totalDocsExamined" : 1, "executionStages" : { "stage" : "SORT", "nReturned" : 1, "sortPattern" : { "updated_at" : -1 }, "totalDataSizeSorted" : 249, "inputStage" : { "stage" : "FETCH", "filter" : { "out" : { "$eq" : false } }, "nReturned" : 1, "executionTimeMillisEstimate" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 1, "keyPattern" : { "user_id" : 1 }, "indexName" : "user_id_1",
db.chat_rooms.createIndex({product_id:1, identifier:1});
Optional<ChatRoom> findByProductIdAndIdentifier(@Param("productId") Long productId, @Param("identifier") String identifier); Long countAllByProductId(Long productId); boolean existsByProductIdAndIdentifier(Long productId, String identifier);
> db.chat_rooms.getIndexes() [ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" }, { "v" : 2, "key" : { "product_id" : 1, "identifier" : 1 }, "name" : "product_id_1_identifier_1" } ]
db.chat_rooms.explain("executionStats").find({ $and: [{"product_id": 12345}, {"identifier": "12345"} ] });
"product_id_1_identifier_1"
"executionStats" : { "executionSuccess" : true, "nReturned" : 0, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 0, "executionStages" : { "stage" : "FETCH", "nReturned" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 0, "indexName" : "product_id_1_identifier_1",
db.chat_rooms.explain("executionStats").find({"product_id":100}).count();
"executionStats" : { "executionSuccess" : true, "nReturned" : 0, "executionTimeMillis" : 0, "totalKeysExamined" : 3, "totalDocsExamined" : 0, "executionStages" : { "stage" : "COUNT", "nReturned" : 0, "inputStage" : { "stage" : "COUNT_SCAN", "nReturned" : 2, "indexName" : "product_id_1_identifier_1",
db.chat_rooms.explain("executionStats").find({ _id: { $in: ['1234','234','3241']} }).sort({"updated_at":-1});
"_id_"
"executionStats" : { "executionSuccess" : true, "nReturned" : 0, "executionTimeMillis" : 0, "totalKeysExamined" : 1, "totalDocsExamined" : 0, "executionStages" : { "stage" : "SORT", "nReturned" : 0, "sortPattern" : { "updated_at" : -1 }, "inputStage" : { "stage" : "FETCH", "nReturned" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 0, "keyPattern" : { "_id" : 1 }, "indexName" : "_id_",
작업 내용
mongodb
)에 인덱스 적용을 위해 기존에 작업한 쿼리가 원하는 인덱스를 제대로 타는 지 확인목차
chat_room_id
,user_id
에 대한 복합인덱스 적용user_id
에 대한 단일인덱스 적용product_id
,identifier
에 대한 복합인덱스 적용Participant
Participant에
chat_room_id
,user_id
에 대한 복합인덱스 적용Participant에
user_id
에 대한 단일인덱스 적용findByChatRoomIdAndUserId(String chatRoomId, Long userId) 쿼리 검증
결과
IXSCAN
: 인덱스 스캔indexName
을 통해 생성한"chat_room_id_1_user_id_1"
인덱스를 사용했음을 알 수 있다.findAllByUserIdAndOutIsFalseOrderByUpdatedAtDesc(Long userId, Pageable pageable) 쿼리 검증
결과
IXSCAN
: 인덱스 스캔indexName
을 통해 생성한"user_id_1"
인덱스를 사용했음을 알 수 있다.ChatRoom
ChatRoom에
product_id
,identifier
에 대한 복합인덱스 적용findByProductIdAndIdentifier(Long productId, String identifier) 쿼리 검증
결과
IXSCAN
: 인덱스 스캔indexName
을 통해 생성한"product_id_1_identifier_1"
인덱스를 사용했음을 알 수 있다.countAllByProductId(Long productId) 쿼리 검증
결과
IXSCAN
: 인덱스 스캔indexName
을 통해 생성한"product_id_1_identifier_1"
인덱스를 사용했음을 알 수 있다.findAllByChatRoomIds(List chatRoomIds, Sort sort) 쿼리 검증
결과
IXSCAN
: 인덱스 스캔indexName
을 통해"_id_"
인덱스를 사용했음을 알 수 있다.