[Mysql, Spring JPA] 파티셔닝 적용하기

 

 

 

서론

스프링 프로젝트에서 폐의약품 수거함 위치 정보를 저장하는 bin_location 이라는 테이블을 조회하는 시간이 오래 걸려서 파티셔닝을 적용해보기로 했다.

이 테이블은 시/도를 의미하는 addr_lvl1 컬럼과 시/순/구를 의미하는 addr_lvl2 로 주로 조회가 되고, 추가/수정/삭제 되는 작업이 드물게 일어난다. 테이블은 처음에 csv, geojson 파일에서 정보를 가져와서 저장된다.

addr_lvl1 컬럼의 값의 예: "서울특별시", "전라남도", "경기도"

 

 

 

1. 파티셔닝 적용 시행착오

1) LIST COLUMNS 파티셔닝 시도

 

Mysql에서는 string형 컬럼을 파티션 키로 사용하려면 LIST COLUMNS를 이용해야 한다.

String형인 컬럼 addr_lvl1 을 기준으로 파티셔닝을 하기 위해 처음에는 LIST COLUMNS 파티셔닝을 다음과 같이 시도해보았다.

 

하지만 Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function   에러가 났다.

알아보니 파티셔닝 키는 반드시 PK에 포함되어야 한다고 한다. 

 

 

 

2) 파티션 키 - PK 설정

String형인 addr_lvl1 컬럼을 PK로 설정하는게 꺼려져서 새로운 int형 컬럼 division_key 를 추가하고, 

LIST 파티셔닝 기법을 사용하기로 했다. 

처음에는 division_key 라는 컬럼이 없었기 때문에 다음과 같이 SQL문으로 수정했다.

 

 

 

 

 

3) Mysql workbench - import 오류

한편, bin_location 테이블의 행 수는 총 2346개이다. 개발 단계에서 매번 애플리케이션을 재시작할 때마다 create-drop 설정으로 테이블을 초기화한 후 저장 메서드를 실행시켜 csv, geojson 파일에서 저장하는 데 시간이 3분 가량 걸렸다.😅

 

테이블에 파일  import 기능을 사용하면, 테이블에 저장하는 작업을 빠르게 마칠 수 있을 것 같아서 시도해보았다.

 

 

Mysql workbench 에서 utf-8로 인코딩된 csv 파일을 import하려했는데, 한글을 읽지 못하고 인코딩 오류가 났다.

파일을 json 형식으로 바꿔서 재시도했으나 이번에는 0개의 행이 반영 되는 문제가 발생했다.

 

stackoverflow 에서 말하는대로 파일을 cp1250으로 인코딩해봤으나 여전히 해결이 안 되었다.

 

 

결국 파일 import 하는 방식으로 테이블을 저장하는 방법(=자바 코드에서 단일키 사용하는 방법)은 포기했다.

대신, 코드 상으로 직접 객체들을 테이블에 저장하도록 코드를 수정하기로 했다.

그러려면 자바 코드에서 복합키 설정을 해주어야 한다.

 

 

 

 

---

 

2. Spring Data JPA에 적용하기

1) JPA

JPA는 자바에서 사용하는 ORM(Object-Relational Mapping) 기술 표준이다.

JPA는 자바 애플리케이션과 JDBC 사이에서 동작하며, 객체와 관계형 데이터베이스의 데이터를 매핑해준다.

그런데 JPA와 테이블 파티션은 무관하다. 따라서 테이블 파티션은 테이블을 생성할 때 잘 만들어두고, JPA(Java 코드 상)에서는 생성된 테이블을 사용하기만 하면 된다.

 

 

2) 복합키 설정하기

Jpa는 복합키 기능을 @IdClass 또는 @EmbeddedId 2가지 방법으로 제공한다.

 

복합키를 사용하려면 다음 조건들을 만족해야 한다.

• 이 둘과 @GenereatedValue 는 함께 사용할 수 없다.
• no-arg constructor 를 가져야 한다.
• equals() 와 hashCode() 메서드를 정의해야 한다. <-- @EqualsAndHashCode 사용

 

@IdClass vs. @EmbeddedId

• @IdClass는 관계형 데이터베이스에 가까운 방법이고, @EmbeddedId는 더 객체지향에 가까운 방법이다.
• JPQL 쿼리 길이 차이

-- @IdClass
SELECT account.accountNumber FROM Account account

--  @EmbeddedId
SELECT book.bookId.title FROM Book book

• @IdClass는 수정할 수 없는 복합 키 클래스를 사용하는 경우 매우 유용할 수 있다.

복합 키의 일부에 개별적으로 접근하려는 경우에는 @IdClass를 사용할 수 있지만, 전체 식별자를 객체로 자주 사용하는 경우에는 @EmbeddedId를 사용하는 것이 좋다.

 

 

@EmbeddedId의 경우가 코드가 더 직관적으로 이해하기 쉬워서 선택했다.

 

 

 

 

그리고 처음에 테이블에 정보를 저장할 때 서비스 코드에서 다음과 같이 id 값을 지정해주었다.

 

 

 

 

 

3) 서버 실행 후 mysql workbench에서 파티셔닝 적용

서버를 실행해서 테이블들이 생성되면 아래의 SQL문을 mysql workbench에서 입력해서 파티셔닝을 적용한다.

 

 

다음과 같이 워크벤치의 Table Inspection (i 버튼) 기능에서 파티셔닝 적용 결과를 확인할 수 있다.

 

 

 

최종 DDL문 : 

 

 

 

 

 

 

Reference

https://dev.mysql.com/doc/refman/8.4/en/partitioning-columns-list.html

https://www.baeldung.com/jpa-composite-primary-keys