서론
스프링 프로젝트에서 폐의약품 수거함 위치 정보를 저장하는 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