DB index 활용

스브코·2022년 1월 20일

Spring TIL troubleshoot 개발일지 항해99 항해99 12주차

DB indexing 이란?

DB indexing은 데이터베이스의 저장공간을 조금 더 활용하여 테이블의 검색 속도를 향상시키기 위한 자료구조라고 한다. 인덱싱이란것은 번역하면 색인을 남긴다는 뜻인데,

우리가 책에서 특정 키워드가 어느 페이지에 있는지 찾을때 보는 색인페이지와 같은 원리라고 생각하면 된다.

이렇게 색인 페이지를 확인하여 책에서 우리가 찾는 내용의 페이지를 바로 찾을수 있듯이, DB indexing을 활용하여 테이블의 동작 속도를 향상시키고 데이터를 빠르게 찾을수 있게 됩니다.

주로 인덱싱을 사용하면 좋은 케이스로는

table에 row의 수가 많고, insert 보다 select나 update가 많을 경우
index를 사용하고자 하는 column에 중복값이 없거나/굉장히 적을 경우

SQL로 보자면 where절에 영향을 준다.

검색을 할때 주로 'Id"인 primary key로 검색을 하는 경우를 제외하고 (primary key로 지정시 자동으로 unclustered index가 unique로 생성됨),

select * from 'student' where 'student_name' = 'John Kim'

JPA query method: findAllByStudentName(String name)

위의 쿼리 검색 처럼 특정 칼럼을 이용하여 검색을 하기도 하는데, 이때 해당컬럼 'student_name'에 indexing을 해주면 성능이 향상된다는 의미이다.

이유는 색인이 들어가게 되면 데이터가 들어간 순서대로의 full scan이 아닌 색인 테이블로 빠르게 해당 위치를 찾아간다. 같은 의미이지만, 조금 기술적으로 설명하자면, 색인된 칼럼으로 조회 요청시 O(n)의 일반 scan 속도 보다 빠른 O(logN)의 B-tree 방식으로 scan을 할 수 있게 된다.

프로젝트에 적용

현재 thandbag 프로젝트에서 JPA에서 기본적으로 제공하는 query method를 제외하고 생성한 사용자 정의 query method들은 다음과 같다.

    Page<Alarm> findAllByUserIdOrderByIdDesc(Long userId, Pageable pageable);
    
    void deleteAllByPostId(Long postId);
    
    Optional<ChatContent> findFirstByChatRoomOrderByCreatedAtDesc(ChatRoom chatRoom);
    
    List<ChatContent> findAllByChatRoomOrderByCreatedAtAsc(ChatRoom room);
    
    List<ChatContent> findAllByUserNotAndChatRoomAndIsRead(User user, ChatRoom chatRoom, Boolean isRead);
    
    List<ChatRoom> findAllByPubUserIdOrSubUserId(Long id, Long id2);
    
    ChatRoom findByPubUserIdAndSubUserId(Long id, Long id2);
    
    Boolean existsAllByPubUserIdAndSubUserId(Long id, Long id2);
    
    CommentLike findByUserIdAndComment(long userId, Comment comment);
    
    List<CommentLike> findAllByComment(Comment comment);
    
    boolean existsByCommentAndUserId(Comment comment, Long userId);
    
    @Query(value = "select p from Post p where p.share = true and (p.title like %:keyword% or p.content like %:keyword% or p.user in (select u from User u where u.nickname like %:keyword%))")
    Page<Post> findAllByShareTrueAndContainsKeywordForSearch(@Param("keyword") String keyword, Pageable pageable);
    
    List<Post> findAllByUser(User user);
    
    Page<Post> findAllByUserOrderByCreatedAtDesc(User user, Pageable pageable);
    
    Page<Post> findAllByShareTrueOrderByCreatedAtDesc(Pageable pageable);
    
    List<Post> findAllByShareTrueOrderByCreatedAtDesc();
    
    Optional<User> findByUsername(String username);
    
    Optional<User> findByNickname(String nickname);
    
    Optional<User> findByKakaoId(Long kakaoId);

이중에 index를 넣기에 적합한 query method는 아래 2개라고 판단하였다.

Optional<User> findByUsername(String username);
    
Optional<User> findByNickname(String nickname);

검색쿼리인 아래의 메소드는 어차피 full scan이 필요하여서 불필요하게 indexing을 하지 않았고,

    @Query(value = "select p from Post p where p.share = true and (p.title like %:keyword% or p.content like %:keyword% or p.user in (select u from User u where u.nickname like %:keyword%))")
    Page<Post> findAllByShareTrueAndContainsKeywordForSearch(@Param("keyword") String keyword, Pageable pageable);

아래 쿼리메소드들은 중복 value가 너무 많아서, cardinality나 selectivity측면에서 효율적이지 않다고 판단하여 제외 시켰다.

    List<ChatRoom> findAllByPubUserIdOrSubUserId(Long id, Long id2);
    
    ChatRoom findByPubUserIdAndSubUserId(Long id, Long id2);
    
    Boolean existsAllByPubUserIdAndSubUserId(Long id, Long id2);
    Optional<User> findByKakaoId(Long kakaoId);

아래의 쿼리들은 외래키로 참조되어 인덱스가 이미 생성이 되있어서 제외시켰다.

    List<CommentLike> findAllByComment(Comment comment);
    
    boolean existsByCommentAndUserId(Comment comment, Long userId);
    
    Optional<ChatContent> findFirstByChatRoomOrderByCreatedAtDesc(ChatRoom chatRoom);
    
    List<ChatContent> findAllByChatRoomOrderByCreatedAtAsc(ChatRoom room);
    
    List<ChatContent> findAllByUserNotAndChatRoomAndIsRead(User user, ChatRoom chatRoom, Boolean isRead);

선택된 아래의 2쿼리는 주로 마이페이지에서 유효성 검사를 하거나 닉네임을 변경할때 사용된다.

    Optional<User> findByUsername(String username);
    
    Optional<User> findByNickname(String nickname);

insert보다 select와 update 구문이 더 많이 발생하여 활용도 측면에서 indexing 하기에 적합하다고 판단하여 'username'과 'nickname'을 아래와 같이 indexing 해주었다.

@Entity
@Table(indexes = {@Index(name = "n_index", columnList = "nickname", unique = true),
        @Index(name = "u_index", columnList = "username", unique = true)})
public class User extends Timestamped {