[Data] 인덱스로 조회 최적화시키기

 

 

데이터베이스 성능의 핵심은 디스크의 랜덤 I/O(접근)을 최소화시키는 것이다. 

인덱스를 활용하면 조회를 최적화시킬 수 있다. 

 

 

인덱스

인덱스는 정렬된 자료구조로 이를 통해 탐색범위를 최소화할 수 있다. 

인덱스도 테이블이기 때문에 쿼리가 날아오면 인덱스를 조회한 후 찾은 결과를 다시 원본데이터로 돌아가 얻게 된다. 

 

 

 

인덱스 자료구조 

• HashMap
  1. 단건 검색 속도 ➔ O(1)
  2. 범위 탐색 ➔ O(N)
  3. like 'AB%' 와 같은 쿼리문을 사용할 때 키를 하나하나 꺼내서 비교해야함

 

• List
  1. 정렬되지 않은 리스트 탐색 ➔ O(N)
  2. 정렬된 리스트 탐색 ➔ O(logN)
  3. 정렬되지 않은 리스트의 정렬 시간 복잡도 ➔ O(N) ~ O(N*logN) 
  4. 삽입 및 삭제 비용 매우 높음

 

• Tree
  1. 트리 높이에 따라 시간 복잡도가 결정됨 👉 트리의 높이 최소화하는 것이 중요 
  2. 한쪽으로 노드가 치우치지 않도록 균형 잡아주는 트리 사용 ➔ B+ Tree

 

• B+ Tree
  1. 삽입 및 삭제 시 항상 균형을 이룸
  2. 하나의 노드가 여러 개의 자식 노드를 가질 수 있음
  3. 리프노드에만 데이터 존재 👉 연속적인 데이터 접근 시 유리  

 

 

 

클러스터 인덱스 

• 클러스터 인덱스는 데이터 위치를 결정하는 키 값이다
  •  정렬된 자료구조로 클러스터 키 위치에 따라서 데이터 주소 결정됨.
  • 클러스터 키 삽입/갱신 시 성능 이슈 발생

• MySQL의 PK는 클러스터 인덱스다.

💡 Auto Increment vs UUID

🔹 Auto Increment : 빠르고 Key를 한눈에 보기 쉬우나 분산 시스템에 적합하지 않음. 

🔹 UUID : 어디서든지 만들고 고유성을 보장할 수 있어 분산 시스템에 용이하나 성능의 저하 발생 (클러스터 인덱스의 정렬 비용 큼)

• MySQL에서 PK를 제외한 모든 인덱스는 PK를 가지고 있다. 
  • PK는 클러스터 인덱스이기 때문에 데이터 삽입/삭제 시 PK에 해당하는 데이터 주소가 변경된다. 만약 인덱스가 PK가 아닌 데이터 주소를 가지고 있다면 데이터 순서가 바뀔 때 인덱스도 같이 갱신되어야하기 때문에 부하가 크다. 그러므로 인덱스는 PK를 가지고 있다 !

 

 

---

 

Bulk Insert 구현 

 

Spring Data JPA를 사용한다면 list를 받는 insert가 saveAll()일 때 save의 루프를 돌면서 호출한다. 

Bulk insert를 한다면 JDBC를 사용하는 것이 성능상 유리할 수 있다. 

 

@RequiredArgsConstructor
@Repository
public class PostRepository {

    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private static final String TABLE = "post";

    public void bulkInsert(List<Post> posts) {
        var sql = String.format("""
                INSERT INTO `%s` (memberId, contents, createdDate, createdAt)
                VALUES (:memberId, :contents, :createdDate, :createdAt)
                """, TABLE);

        SqlParameterSource[] params = posts
                .stream()
                .map(BeanPropertySqlParameterSource::new)
                .toArray(SqlParameterSource[]::new);

        // batchUpdate -> 리스트 형태의 SQL 쿼리와 매개변수 배열로 이루어진 인자를 받아들여, 여러 개의 SQL 쿼리를 일괄적으로 실행
        namedParameterJdbcTemplate.batchUpdate(sql, params);
    }
}

public class PostFixtureFactory {

    static public EasyRandom get(Long memberId, LocalDate firstDate, LocalDate lastDate) {

        var idPredicate = named("id")
                .and(ofType(Long.class))
                .and(inClass(Post.class));

        var memberIdPredicate = named("memberId")
                .and(ofType(Long.class))
                .and(inClass(Post.class));

        var param = new EasyRandomParameters()
                .excludeField(idPredicate)
                .dateRange(firstDate, lastDate)
                .randomize(memberIdPredicate, () -> memberId);

        return new EasyRandom(param);
    }
}

@SpringBootTest
public class PostBulkInsertTest {

    @Autowired
    private PostRepository postRepository;

    @Test
    public void bulkInsert(){
        var easyRandom = PostFixtureFactory.get(
                3L,
                LocalDate.of(2023, 11, 1),
                LocalDate.of(2023, 12, 1));

        var stopWatch = new StopWatch();
        stopWatch.start();

        var posts = IntStream.range(0, 1000000)
                .parallel()
                .mapToObj(i -> easyRandom.nextObject(Post.class))
                .toList();

        stopWatch.stop();
        System.out.println("객체 생성 시간 : " + stopWatch.getTotalTimeSeconds());

        var queryStopWatch = new StopWatch();
        queryStopWatch.start();

        postRepository.bulkInsert(posts);

        queryStopWatch.stop();
        System.out.println("DB insert 시간 : " + queryStopWatch.getTotalTimeSeconds());
    }
}

 

bulkInsert() 결과 쿼리가 리스트로 넘어가고 하나만 생성되는 것을 확인할 수 있다. 

 

 

100만 건의 데이터를 삽입하게 되면 메모리 사이즈 부족으로 Java heap space 에러가 발생할 수 있다.

이럴 때는 heap size를 늘려주면 된다. Help > Edit Custom VM options..에서 2048 ➔ 4096으로 늘려주었다. 

Preferences - Gradle- Run test using: IntelliJ IDEA 로 설정해야 적용된다.

그러면 오른쪽 하단에 늘어난 메모리를 확인할 수 있다.

 

100만 건 데이터를 Insert 할 때 DB insert 시간은 약 11초 소요된다. 

 

💡 EasyRandom 

👉 Java 객체를 랜덤으로 생성하는 라이브러리로, 정의한 클래스 타입의 객체를 랜덤하게 생성하기 때문에 테스트 데이터를 생성하거나 더미데이터를 만들 때 주로 사용한다. 객체의 필드가 많을 때 일일이 값을 설정하지 않아도 되기 때문에 편리하다. 

➔ 다양한 파라미터를 적용해서 알맞게 사용하면 된다. 

➔ nextObject() 메서드는 주어진 클래스의 객체를 생성하고 해당 클래스의 필드를 랜덤으로 채워서 반환한다. 

 

 

 

인덱스 활용 시 성능 차이 비교

create index POST__index_member_id
    on POST (memberId);

create index POST__index_created_date
    on POST (createdDate);

create index POST__index_member_id_created_date
    on POST (memberId, createdDate);

POST 테이블에 index를 추가한다.

---

memberId는 1 ~ 4번까지 존재하고, createdDate은 1900년 1월 ~ 2023년 12월까지 존재한다. 

memberId가 3인 회원은 약 200만 건 insert한 상태이다. 

 

🔹 인덱스 사용하지 않고 조회 👉 약 863ms 소요

 

 

🔹 인덱스 사용하고 조회 👉 약 4s 소요 

 

💢 인덱스를 활용했는데, 성능이 더 악화된 이유 

👉 인덱스가 없을 때는 테이블만 확인하면 되는데 인덱스가 있는 경우, memberId가 3번에 해당하는 200만 건의 데이터는 인덱스도 보고, 테이블도 확인해야한다. ➔ 인덱스가 탐색범위를 좁혀주지 못해 시간 2배 이상 소요

👉 만약, createdDate 인덱스를 활용한다면 반대로 성능이 개선된다. 



createdDate의 식별값은 약 19700개로 많이 분포되어있는 반면, memberId는 4개뿐이다. createdDate의 식별값이 상대적으로 고르게 분포되어있기 때문에 보다 균형적인 인덱스 트리를 이룰 것이므로 성능이 개선된다. 

 

 

🔹 복합 인덱스 사용해서 조회 시 👉 성능 개선 

🔸 복합 인덱스 : memberId로 먼저 탐색 시작해서 탐색 범위 줄임 ➔ createdDate 탐색 

 

 

createdDate 인덱스 하나만 사용했을 시에는 약 127ms 소요 

복합 인덱스 사용시 약 62ms 소요 👉 2배 이상 성능 개선 

 

💡 인덱스 사용 시 고려해야할 점

1️⃣ 파라미터로 들어오는 것의 데이터 분포도 
2️⃣ 어떤 컬럼이 orderBy, groupBy 되어 있는지 ..

 

 

참고 

https://github.com/j-easy/easy-random

https://engineering.linecorp.com/ko/blog/mysql-workbench-visual-explain-index