MySQL partitioning의 기록 (22.11~22.12 HospitAl Talk 프로젝트)

아래의 리스트 파티션은 이후 데이터셋을 교체하고 좌표정보를 사용하게 되면서 사용하지 않게 되었다.

구별로만 병원을 찾아오는 것이, 사용자 위치기반 서비스에 온전히 들어맞지 않기 때문. (즉, 구별로 찾는 것이 아니라 각 병원마다 사용자 위치정보를 기반하여 거리계산을 해야 한다는 뜻.)

---

• 참조 : 
  • https://dev.mysql.com/doc/refman/8.0/en/partitioning-list.html
  • https://myinfrabox.tistory.com/103
  • https://vertabelo.com/blog/everything-you-need-to-know-about-mysql-partitions/
  • https://hoing.io/archives/8527
  • https://velog.io/@gillog/MySQL-Partition
  • https://backtony.github.io/mysql/2021-01-18-mysql-21/
  • https://dbguide.tistory.com/m/entry/MySQL-Partition-기능과-활용
  • https://m.blog.naver.com/thwlstmxk/221062700730
  • https://petme.tistory.com/m/entry/MySQL-테이블-파티셔닝
• 문제의식
  • 기존의 쿼리는 fulltext index 를 사용했다지만, 대량의 긴 텍스트가 아니라 주소에서 자치구 이름을 검색하는 정도
  • 모든 주소를 index로 등록할 필요가 있는지 의문 (무엇보다 한정된 리소스를 어디에 할애할 것인가의 문제)
  • 주소를 list partition 으로 나누면 index를 순회할 필요가 없으며 메모리 소모가 개선됨
  • 또 fulltext index의 갱신계열 쿼리들의 성능에 대한 tradeoff 문제도 개선됨
    • 추후 스케쥴링 이벤트로서의 data validation 기능구현이 잠재적 과제
  • 그러나 partition 으로 나뉜 table은 fulltext index가 지원되지 않음.
    • 진료과 검색은 결국 해당 구의 모든 병원을 서칭해야 한다.
      • 좌표 정보로 범위를 줄일 수 있지 않은가?
      • 그러나 현재 서비스의 구조상 where 절 안에 상수로서 좌표 범위를 줄이기가 쉽지 않음.
      • 각 자치구별로 하나의 원점좌표를 두고, 유저의 좌표값에 따라 4가지 경우의 수(동서남북, 즉 원점좌표 X보다 큰가 작은가, 원점좌표 Y보다 큰가 작은가) 를 만들어 상수로 된 범위를 특정할 수도.
      • 구현된다면 서칭 범위는 1/4로 줄일 수 있다.
      • 그러나 위의 문제는 당장 급하지 않다. 서비스 전체를 따지고 보면 이미 웹서버는 충분히 빠르기도 하고.
• 각 구별로 순서대로 수납했기 때문에, 그냥 PK 값으로 찾아서 업데이트.
• 비효율적으로 보이지만, 업데이트 관련하여 데이터가 망가지는 상황을 많이 겪어서… 이렇게까지 하고 싶었습니다.

UPDATE Hospitals SET province = 0 WHERE hospitalId < 3317;
UPDATE Hospitals SET province = 1 WHERE hospitalId BETWEEN 3317 AND 4447;
UPDATE Hospitals SET province = 2 WHERE hospitalId BETWEEN 4448 AND 5084;
UPDATE Hospitals SET province = 3 WHERE hospitalId BETWEEN 5085 AND 6274;
UPDATE Hospitals SET province = 4 WHERE hospitalId BETWEEN 6275 AND 7218;
UPDATE Hospitals SET province = 5 WHERE hospitalId BETWEEN 7219 AND 7941;
UPDATE Hospitals SET province = 6 WHERE hospitalId BETWEEN 7942 AND 8739;
UPDATE Hospitals SET province = 7 WHERE hospitalId BETWEEN 8740 AND 9233;
UPDATE Hospitals SET province = 8 WHERE hospitalId BETWEEN 9234 AND 10225;
UPDATE Hospitals SET province = 9 WHERE hospitalId BETWEEN 10226 AND 10723;
UPDATE Hospitals SET province = 10 WHERE hospitalId BETWEEN 10724 AND 11610;
UPDATE Hospitals SET province = 11 WHERE hospitalId BETWEEN 11611 AND 12404;
UPDATE Hospitals SET province = 12 WHERE hospitalId BETWEEN 12405 AND 13378;
UPDATE Hospitals SET province = 13 WHERE hospitalId BETWEEN 13379 AND 13973;
UPDATE Hospitals SET province = 14 WHERE hospitalId BETWEEN 13974 AND 15584;
UPDATE Hospitals SET province = 15 WHERE hospitalId BETWEEN 15585 AND 16207;
UPDATE Hospitals SET province = 16 WHERE hospitalId BETWEEN 16208 AND 16950;
UPDATE Hospitals SET province = 17 WHERE hospitalId BETWEEN 16951 AND 18595;
UPDATE Hospitals SET province = 18 WHERE hospitalId BETWEEN 18596 AND 19449;
UPDATE Hospitals SET province = 19 WHERE hospitalId BETWEEN 19450 AND 20508;
UPDATE Hospitals SET province = 20 WHERE hospitalId BETWEEN 20509 AND 20962;
UPDATE Hospitals SET province = 21 WHERE hospitalId BETWEEN 20963 AND 21895;
UPDATE Hospitals SET province = 22 WHERE hospitalId BETWEEN 21896 AND 22565;
UPDATE Hospitals SET province = 23 WHERE hospitalId BETWEEN 22566 AND 23296;
UPDATE Hospitals SET province = 24 WHERE hospitalId BETWEEN 23297 AND 24021;

 

• 파티션을 나누기 위한 프라이머리 키 설정.
  • 참조 : https://hoing.io/archives/8527

  ALTER TABLE hh99.Hospitals DROP primary key,
  ADD CONSTRAINT Hospitals_PK PRIMARY KEY (hospitalId,hospitalCode,province);

 

• 자치구 별로 list partion 나누기. (0은 강남구 …. 24는 중랑구. 프로젝트의 자치구 배열 참고.)

ALTER TABLE Hospitals PARTITION BY LIST (province)(
partition p0 VALUES IN (0),
partition p1 VALUES IN (1),
partition p2 VALUES IN (2),
partition p3 VALUES IN (3),
partition p4 VALUES IN (4),
partition p5 VALUES IN (5),
partition p6 VALUES IN (6),
partition p7 VALUES IN (7),
partition p8 VALUES IN (8),
partition p9 VALUES IN (9),
partition p10 VALUES IN (10),
partition p11 VALUES IN (11),
partition p12 VALUES IN (12),
partition p13 VALUES IN (13),
partition p14 VALUES IN (14),
partition p15 VALUES IN (15),
partition p16 VALUES IN (16),
partition p17 VALUES IN (17),
partition p18 VALUES IN (18),
partition p19 VALUES IN (19),
partition p20 VALUES IN (20),
partition p21 VALUES IN (21),
partition p22 VALUES IN (22),
partition p23 VALUES IN (23),
partition p24 VALUES IN (24)
);