5. 파티션 (13.3), 스토어드 프로그램 (~14.2)

[DaehunGwak]

진도

13. 파티션 13.3 주의사항
14. 스토어드 프로그램 14.1 스토어드 프로그램의 장단점 14.2 스토어드 프로그램의 문법

일시

2022.08.14.(일) 21:00

[minkukjo]

MySQL 파티션 종류

• 레인지, 리스트, 해시, 키

레인지 파티션

• 1. 큰 테이블을 작은 크기의 파티션으로 분리
• 2. 필요한 파티션만 접근
• 2의 장점을 취하지 못하고 1만 집중하면 오히려 성능이 더 떨어진다고 한다.
• 이력(히스토리)관리 할 때는 레인지 파티션이 대부분 상황에서 좋다고 한다.

리스트 파티션

• 레인지 파티션과 유사하지만, 가장 큰 차이는 레인지 파티션은 키 값 하나하나를 리스트로 나열해야한다.
• 파티션 키 값이 코드 값이나 카테고리처럼 고정적인 경우
• 키 값이 연속되지 않고 정렬 순서와 관계 없이 파티션할 경우
• 파티션 키 값을 기준으로 레코드의 건수가 균일하며 검색 조건에 파티션 키가 자주 사용되는 경우
• 위와 같은 세 경우에 주로 사용된다고 한다.

해시 파티션

• 해시 펑션을 사용해 키 값을 넣으면 위치할 파티션의 위치를 계산하는 파티셔닝 방법이다.
• 레인지 파티션이나 리스트 파티션으로는 데이터를 균등하게 나누는게 어려운 경우
• 테이블의 모든 레코드가 비슷한 사용 빈도를 보이는데 테이블이 너무 커서 파티션 해야하는 경우
• 위와 같은 두 경우에 주로 사용된다고 한다.
• 해시 파티션이 분리/병합될 때 파티션의 개수가 변경되면 해시 함수 알고리즘 자체가 바뀌므로 전체 파티션이 영향을 받는다.
• 특정 해시 파티션은 삭제가 안된다.

키 파티션

• 해시 파티션과 거의 동일하다.
• 차이가 있다면 MySQL 서버가 선정된 파티션 키 값을 MD5 함수를 사용해 해시 값을 계산하고 그 값을 MOD연산해서 데이터를 각 파티션에 분배한다는 것이 유일한 차이이다.
• 키 파티션은 MD5 함수를 이용하므로 파티션 키가 반드시 정수 타입이 아니어도 된다. 해시 파티션으로 파티셔닝이 어렵다면 키 파티션을 고려해볼 수 있다.
• 해시 파티션에 비해 레코드를 더 균등하게 분배할 수 있기 때문에 키 파티션이 더 효율적이다.

리니어 해시 파티션/리니어 키 파티션

• 해시 파티션이나 키 파티션은 새로운 파티션이 추가되거나 파티션을 통합하는 경우 전체 테이블을 다시 재분배하는 작업이 필연적이다.
• 이를 최소화하기 위해 리이너 해시 알고리즘이 고안되었다.
• Power-of-two라는 2의 승수 알고리즘을 이용하게 되는데, 이 알고리즘은 파티션의 추가나 통합 시 다른 파티션에 미치는 영향을 최소화할 수 있다.
• 대신 파티션이 가지는 레코드의 건수는 일반 해시 파티션이나 키 파티션보다 덜 균등할 수 있다.
• 파티션의 조정이 잦은 테이블이라면 리니어를, 아니라면 기존 해시/키 파티션을 고려해보는게 좋다.

파티셔닝의 성능

• 파티션된 테이블에 쿼리가 수행될 때 파티션 프루닝이 수행되게 된다.
• 즉, 파티션된 테이블 쿼리의 성능은 테이블에서 얼마나 많은 파티션을 프루닝할 수 있는지가 관건이다.
• B-Tree 역시 파티션된 개수만큼 나누어져있기 때문에, 파티션 개수가 많고, 해당 쿼리가 모든 파티션을 다 봐야하는 쿼리라면 (e.q. 동등 비교) 그 쿼리의 성능은 매우 떨어지게 될 것이다.
• 하지만 파티션한 테이블 중 일부만 읽고 쓰는 경우라면 성능이 매우 향상될 것이다.
• 대용량 테이블을 파티션으로 쪼개서 서로 다른 MySQL 서버에 저장한다면 마치 샤딩과 같은 효과를 낼 것이다.
• 그러나 MySQL 서버의 파티션은 샤딩이 아니며, 파티션 프루닝이 얼마나 도움이 될지를 먼저 예측해보고 적용하는 것이 좋다.
• 특히 레인지 파티션 이외의 파티션을 적용할 때 파티션 프루닝이 얼마나 적용될지 더 많이 고민해보고 결정하는 것이 좋다고 저자는 말한다.

스토어드 프로그램

• 사실 현대의 프로그래밍에선 객체지향과 같은 패러다임과 도메인 주도 개발과 같은 개발 방법론이 대두되면서 SQL로 모든 것을 해결하자!와 같은 발상은 구시대적인 발상이 되어버렸다.
• 특히 애플리케이션에서 처리할 수 있는 로직들을 DB에서 다 처리하자!와 같은 생각은, 분산 시스템이 보편화된 모던 아키텍처에서 더더욱 환영받지 못하는 아이디어이다.
• 그래서 일단 스토어드 프로그램을 깊게 공부해본 적도, 그러려는 시도조차 안했었지만 어떤 개념인지 정도는 스윽 훑어보고 장을 끝 마쳤다.
• 스토어드 프로그램의 장점을 DB 입장에서 썼는데... 솔직히 하나도 공감 안됐다.
• 특히 여러 언어를 쓸 때 DB에 스토어드 프로그램을 쓰면 개발 언어나 도구와 관계 없이 일관된 기능(일련 번호 생성)을 사용할 수 있다는 대목이 킬링 포인트. ( 스노플레이크를 쓰세요... )

[Edwin222]

파티션

• 레인지 파티션

  • 키의 범위로 지정되는 파티션
  • 필요한 파티션에만 접근하는 장점을 잘 활용할 수 있는 구조. (대체로 이력 기반 접근이므로)
  • 삭제시에 중간에 있는걸 삭제할수는 없다. 삭제는 앞, 추가는 뒤로만 가능.

• 리스트 파티션

  • 레인지랑 비슷하지만 키의 범위가 아니라, 키값 하나하나를 파티션에서 지정
  • 추가 삭제는 레인지와 같다고 하는데 얘도 중간에 지우거나 끼우기 못 하나?? 왜지?? 같은 생각을 함

• 해시 파티션

  • 해시 함수로 파티션 키를 해싱해서 들어갈 파티션을 지정. 해시 함수는 그냥 모듈러 연산이라 파티션 키는 정수여아야 한다.
  • 일부 파티션만 활발하게 쓰는 경우에 대해선 적합하지 않고 사용빈도가 고르게 분포하면서도 사이즈가 커서 쪼개고 싶은 경우에 해당.
  • 모듈러 연산 특성상, 파티션 갯수가 변동하면 전체 레코드를 재배치해야한다.
  • 파티션 갯수를 줄이거나 늘리는 연산만 가능하고, 병합 삭제 등은 제공하지 않음. (의미도 없음)

• 키 파티션

  • 해시 파티션과 비슷한데 해시값 계산에 쓸 값이나 표현식을 사용자가 지정한다.

  • 정수 아니라도 된다는 것

  • 해당 키값을 MD5 해싱하고 그 값을 다시 모듈러 연산해서 분배함.

스토어드 프로그램

• 데이터베이스 내적으로 절차적 처리를 제공하는 프로그램. 장단이 있음.

• 장점 중 그럴싸했던 것

  • 네트워크 경유 횟수를 줄일 수 있다.

  • 로직 처리 일부를 DB쪽에 이양해서 패킷이 오가는 횟수를 줄일 수 있다.

  • 이를 통해 로직 복잡도를 줄일 수 있으면서도 처리 전체에서의 원자성을 로직 없이도 보장하고, (교환 횟수 x 레이턴시 x 2) 만큼의 지연을 (레이턴시 x 2)로 감소시킬 수 있음.

  • 기능의 추상화

  • 애플리케이션 딴에서 코드 중복을 줄일수 있다는 건 어거지로 짜맞춘 내용 같고(오히려 단점일 수 있다), 그보다는 DB 내적으로 처리를 완결하고 애플리케이션 쪽에 불필요한 인터페이스를 노출하지 않을 수 있다는 점에서 공감.

• 장점 중 위험하다고 느껴졌던 것

  • 개발 업무의 구분
  • 흔히 감지하는 환상이지만 나쁜 결과로 귀결되는 경우가 너무 많다.
  • 누구는 무엇만 한다 식으로 개발 업무가 갈리고 갈려버리면 시야가 좁아져서, 안맞는 틀에다 억지로 자기가 아는 도구를 끼워넣으려고 노력하는 와중에 구조가 산으로 가는 일이 자주 생기더라 (경험상)
  • 누군가는 전체 그림을 봐야 한다.. 그러려면 조직이 책에서 언급하는 식으로 갈리는 건 안 됨

• 단점

  • 처리 속도가 느리다.

  • 경우에 따라 치명적일 수 있으니 잘 측정하고 판단해서 껴넣어야 할 듯. 다만 대체로 복잡도가 있는 로직은 애플리케이션 레벨에서 처리하지 않을까 싶어서 큰 단점은 아닐 것 같다.

  • 속도가 느린 것도 사실 스토어드 프로그램이 느려서 문제가 되는 경우보단 그냥 슬로우 쿼리라서 문제인 경우가 더 많을 듯?

  • 코드 파편화

  • 위험하다고 느껴졌던 장점 쪽에서 언급했던, 망할 수 있는 대표적인 사례 중 하나.

문법

너무 많아... 프로그래밍이 거기서 거기지 같은 느낌으로 대충 훑고 넘김. 개념만 몇가지 정리한다.

• CREATE PROCEDURE로 프로시저, CREATE FUNCTION으로 함수

  • 프로시저는 연속된 쿼리 처리, 함수는 특정 타입의 값을 반환.

• BEGIN / END가 블록을 열고 닫는다.

• CALL로 프로시저 호출, 함수는 그냥 함수 호출하듯이..

• 트리거 : 특정 상황에서 자동으로 지정한 작업을 수행하는 일종의 훅 펑션 같은것

  • 별 생각없이 쿼리 썼다가 의문의 트리거 호출 같은거 많이 있을듯 ㅋㅋ

• 이벤트: 스케줄러에 따른 정기적 프로그램 호출. 뭔가 이벤트 리스닝은 아님.