<Database> 파티션 테이블 in Mysql

가.  테스트 테이블 만들기

 a.  호텔 예약 테이블

  -    파티션을 테스트할 테이블을 만들어보자 피곤하니 chatgpt 를 이용했다.

  -  컬럼이 너무 많으면 더미 데이터 만들기 힘들어서 아래로 줄여보았다.

create table hotel_reservation
(
    id            int auto_increment
        primary key,
    guest_name    varchar(50) not null,
    check_in_date date        null
);

 

 b.  더미 데이터 추가하기

  -  이거는 프로시저를 만들어서 추가할건데 이것 역시 피곤하니 chatgpt 에 물어보았다. 

  -  chatgpt도 완벽하지 않은 걸 보면서 직업이 없어지는 위기에서 안도감이 든다. 그대로 실행하면 실행속도가 엄청 느릴것이므로 수정해주어야한다.

create
    definer = root@`%` procedure insert_dummy_data()
BEGIN

  DECLARE i INT DEFAULT 1;
  start transaction ;
  WHILE i <= 10000000 DO
    INSERT INTO hotel_reservation
    (guest_name, check_in_date)
    VALUES
    (CONCAT('Guest ',CAST(i AS CHAR)), DATE_ADD('2023-01-01', INTERVAL CAST(FLOOR(rand() * 365) as UNSIGNED) DAY));
    SET i = i + 1;
  END WHILE;
  commit ;
END;

  - 여기서 transaction을 추가해주었다. 이유는 transaction이 없으면 매번 connection을 연결하고 commit을 하는데 그 시간이 상당히 느리기 때문이다.

 

나.  파티션 테이블 만들기

 a.  DDL

  -  이번에도 chatgpt에 만들어 달라고 해보자

  -  파티션 테이블 생성하는 것은 간단하다 create 문 뒤에 위처럼 partition by 와 range만 명시 해주면 된다. 

  -  여기서 주의할 점은 아래 오류가 발생하는데 partition 조건에는 반드시 pk가 들어가야 한다.

 

 b.  explain

  -  다 만들었으면 실행계획을 한번 조회해보면 달라진 부분이 보이는데 partitions 부분에 아까 만든 파티션들이 보인다.

  - 이번에는 아까 만든 파티션 조건을 검색해 보자

- 위와 다르게 전체 파티션이 아닌 지정한 파티션만 타는 것을 볼 수 있다.

 

 c. 주의할 사항

  -  between으로 날짜단위 검색 쿼리의 실행계획을 확인해보자

  -  여기까진 이상없는것 같은데 .. 좀 더 기간을 늘려보자

  - 하나의 파티션 조건의 범위를 넘어가게 되면 첫번째 파티션을 타는 경우도 있으니 주의 해야한다. 

  - 비슷한 경우로 이번엔 id로 조회하는 경우의 실행계획을 확인해보자

  - 이렇게 id만 조회하는 경우에도 전체 파티션을 타기 때문에 만약 파티션 조건을 알고 있다면 명시해주는 것이 성능에 좋다.

 

다.  그래서 장단점이 뭔데..

 a.  대량의 데이터 조회 속도!

  - 데이터가 적으면 관리요소가 늘어나는 파티션 테이블을 굳이 사용할 필요가 없지만 데이터가 많아질수록 조회 성능이 좋지 않을 것이다.

     => 인덱스를 걸어놨다고 해도 전체 데이터의 인덱스를 조회해야하기 때문에 느려진다. (파티션 테이블은 파티션 안에 인덱스가 있다)

  - 단순하게 생각해보면 전체 데이터를 처음부터 확인하는 것보다 폴더로 구분해서 내가 원하는 폴더만 들어가서 찾아보는게 빠르지 않을까..

  - 아래는 3천만건으로 테스트 해본 결과이다 (더미 데이터가 많지 않아서 큰 차이가 없지만 ..)