MySQL 조회 최적화를 위한 인덱스 테스트 [EazyRandom]

클러스터 인덱스

클러스터 인덱스는 PK키 값을 가진 정렬된 보조 테이블을 통해 키값에 해당하는 행을 빠르게 검색하는 방법입니다.

-> MySQL에서 기본 키는 자동으로 클러스터형 인덱스 역할을 합니다.

PK키와 정렬이라는 두가지 조건으로 클러스터형 인덱스는 업데이트가 자주 일어나지 않는 정적이고 범위를 검색하는 경우에 주로 사용됩니다.

 

성능 테스트 -> 100만건의 데이터 

인덱스 성능을 테스트 하기 위해 아래와 같이 게시글 기능을 구현하고 100만 건의 데이터를 삽입했습니다.데이터는 EazyRandom을 통해 진행했습니다.

 

구현을 위해 사용되는 클래스는 아래와 같습니다.

• FixtureFactory 
• Repository의 bulkInsert메서드 => 대량의 데이터를 효율적으로 삽입하는데 도움을 줍니다.
• BulkInsertTest

public class PostFixtureFactory {

    public static EasyRandom get(Long memberId, LocalDate firstDate, LocalDate lastDate) {
        var idPrecicate = named("id")
                .and(ofType(Long.class))
                .and(inClass(Post.class));

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

        var param = new EasyRandomParameters()
                .excludeField(idPrecicate)
                .dateRange(firstDate, lastDate)
                .randomize(memberIdPrecicate, () -> memberId);
        return new EasyRandom(param);

    }
}

 

FixtureFactory 는 데이터의 Fixture를 생성하는 역할을 합니다. 
Fixture는 테스트 환경 설정과 데이터 생성에 중점을 둔 도구로 EasyRandom를 활용하여 테스트에 사용할 데이터를 생성합니다.
id와 memberId를 정의하고 id는 제외, memberId는 고정, 날짜 범위를 지정해 EasyRandom 인스턴스를 생성하고 반환합니다.

 

public void bulkInsert(List<Post> posts) {
    /*
    [일괄 저장]을 구현해주세요.
    */
    var sql = """
            INSERT INTO post (memberId, contents, createdDate, createdAt)
            VALUES (:memberId, :contents, :createdDate, :createdAt);
            """;
    SqlParameterSource[] params = posts
            .stream()
            .map(BeanPropertySqlParameterSource::new)
            .toArray(SqlParameterSource[]::new);
    jdbcTemplate.batchUpdate(sql, params);
}

 

sql쿼리를 통해 post에 삽입하기 위한 형태를 잡고 sqlParameterSource를 통해 posts 배열을 구현했습니다.
생성된 posts는 batchUpdate 메서드를 통해 일괄 삽입됩니다.

 

@SpringBootTest
public class PostBulkInsertTest {

    @Autowired
    private PostRepository postRepository;

    @Test
    public void bulkInsert() {
        var easyRandom = PostFixtureFactory.get(
                1L,
                LocalDate.of(2022, 1, 1),
                LocalDate.of(2022, 2, 1));
        var post = IntStream.range(0, 10000)
                .parallel()
                .mapToObj(i -> easyRandom.nextObject(Post.class))
                .toList();
        postRepository.bulkInsert(post);
    }
}

 

Repository에서 구현한 bulkInsert메서드를 테스트하는 클래스로 FixtureFactory에서 얻은 easyRandom 인스턴스를 활용해 Post 객체를 생성해 bulkInsert메서드의 변수로 활용했습니다.

.mapToObj(i -> easyRandom.nextObject(Post.class))을 통해 병렬로 easyRandom을 사용하여 Post객체를 1만 번 생성합니다.

Test코드에서는 @Transactional 어노테이션을 함께 사용하면, 테스트 완료 후 롤백하여 실제 데이터베이스에는 영향을 주지 않는 방법이 있습니다.

 

인덱스 설정

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);

 

bulkInsert로 대용량의 데이터 삽입이 완료된 후 조회를 하게되면 대량의 데이터로 인해 많은 시간이 소요됩니다.
따라서 index를 설정해 색인 과정에서 선별적으로 검사하게 시켜야 합니다.

index를 사용하기에 앞서 데이터의 분포를 확인해야 합니다.
왜냐하면 인덱스를 사용하면 조회 시에 인덱스를 먼저 스캔하고 인덱스에 저장된 데이터 레코드를 통해 실제 데이터베이스에서 데이터를 읽어오기 때문입니다.
따라서, 인덱스와 실제 DB 두가지를 조회해야 하기 때문에 인덱스에서 실제 DB에 있는 데이터에 비해 크게 변별할 구색을 갖추지 못한다면 오히려 조회 성능이 떨어질 수 있습니다.

 

---

게시글 기능 구현

private final Long id;
private final Long memberId;
private final String content;
private final LocalDate createdDate;
private final LocalDate createdAt;

 

게시글을 위한 Post 클래스의 필드값은 위와 같습니다.

 

@Repository
@RequiredArgsConstructor
public class PostRepository {

    private final NamedParameterJdbcTemplate jdbcTemplate;
    private static final String TABLE_NAME = "Post";
    public Post save(Post post) {
        if (post.getId() == null) {
            return insert(post);
        } else {
            throw new UnsupportedOperationException();
        }
    }
    private Post insert(Post post) {
        SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate.getJdbcTemplate())
                .withTableName(TABLE_NAME)
                .usingGeneratedKeyColumns("id");

        SqlParameterSource params = new BeanPropertySqlParameterSource(post);
        var id = jdbcInsert.executeAndReturnKey(params).longValue();

        return Post.builder()
                .id(id)
                .memberId(post.getMemberId())
                .content(post.getContent())
                .createdDate(post.getCreatedDate())
                .createdAt(post.getCreatedAt())
                .build();
    }
}

 

다른 Repository와 같이 NamedParameterJdbcTemplete을 주입받아 구현하고 있습니다.
if 함수를 통해 id값이 null값인 경우, insert메서드로 삽입하게 되며 null 값이 아닌 경우, 즉 해당 번호 글이 존재하는 경우 경고를 통해 중복 게시를 막았습니다.

insert 메서드는 주어진 Post 객체를 데이터베이스에 삽입하고, 이 과정에서 자동으로 생성된 키(primary key)를 얻어와서 해당 값을 포함한 새로운 Post 객체를 반환합니다.

 

    public Long createPost(PostCommand command) {
        var post = Post
                .builder()
                .memberId(command.memberId())
                .content(command.content())
                .build();
        return postRepository.save(post).getId();
    }
}

 

서비스는 간단하게 빌더패턴을 사용해 Command에서 전달된 정보를 사용해 Post객체를 생성했습니다.
생성된 Post는 save되며, 생성 시 자동 생성된 id값(식별자)를 받아 Long형태로 반환됩니다.

 

조회 기능 추가

public static RowMapper<DailyPost> DAILY_POST_ROW_MAPPER = (rs, rowNum) -> new DailyPost(
        rs.getLong("memberId"),
        rs.getDate("createdDate").toLocalDate(),
        rs.getLong("post_count")
);

public List<DailyPost> getCount(DailyPostCommand command) {
    /*
    [일자, 회원, 게시글수]를 조회하는 쿼리를 작성해주세요.
    */
    var sql = """
            SELECT createdDate, memberId, count(*) as post_count
            FROM post
            WHERE memberId = :memberId AND createdDate BETWEEN :firstDate AND :lastDate
            GROUP BY createdDate, memberId;
            """;
    SqlParameterSource params = new BeanPropertySqlParameterSource(command);
    return jdbcTemplate.query(sql, params, DAILY_POST_ROW_MAPPER);
}

 

일자, 회원ID, 게시글 수를 조회하는 DailyPostCount를 구현했습니다.
컨트롤러는 이전과 같이 작성했습니다.