mysql partitioning, index

mysql에서 파티셔닝이과 인덱스는 둘다 querying속도를 빠르게 만들어줍니다. mysql 테이블을 튜닝? 할 일이 생겼는데 파티셔닝이나 인덱스를 걸어서 속도를 올려야 했습니다. 근데 사실 이 두개가 속도를 빠르게 한다는건 알고있었지만 어떤점이 다른지 궁금해서 이것저것 찾아보다 좋은 글을 가져와서 정리합니다..

가장 유용했던 답변은 https://blog.naver.com/hykzephyr/221463212941 에서 찾을수 있었습니다.

글 내용이 너무 좋아서 블로그에서 퍼왔습니다. 문제가 되거나 퍼온게 마음에 안드실 경우 댓글로 적어주시면 글 내리도록 하겠습니다.

파티션이란 MySQL 서버의 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 솔루션 입니다.

1. 파티션을 사용하는 이유

테이블의 데이터가 많아진다고 해서 무조건 파티션을 적용하는 것이 효율적인 것은 아닙니다. 하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 크거나, 데이터 특성상 주기적인 삭제 작업이 필요한 경우 등이 파티션이 필요한 대표적인 예입니다.

​

파티션이 필요한 예는 아래와 같습니다.

- 단일 INSERT와 단일 또는 범위 SELECT와 빠른 처리

데이터베이스에서 인덱스는 일반적으로 SELECT를 위한 것으로 보이지만 UPADTE와 DELETE, 그리고 INSERT 쿼리를 위해 필요한 때도 많습니다. 물론 레코드를 변경하는 쿼리를 실행하면 인덱스의 변경을 위한 부가적인 작업이 발생하긴 하지만 UPDATE나 DELETE 처리를 위해 대상 레코드를 검색하려면 인덱스가 필수적입니다. 하지만 이 인덱스가 커지면 커질수록 SELECT는 말할 것도 없고, INSERT나 UPDATE, 그리고 DELETE 작업도 당연히 느려진다는 점이 문제입니다.

- 데이터의 물리적인 저장소를 분리

데이터 파일이나 인덱스 파일이 파일 시스템에서 차지하는 공간이 크다면 그만큼 백업이나 관리 작업이 어려워집니다. 더욱이 테이블의 데이터나 인덱스를 파일 단위로 관리하고 있는 MySQL에서 더 치명적인 문제가 될 수도 있습니다. 이러한 문제는 파티션을 통해 파일의 크기를 조절하거나 각 파티션별 파일들이 저장될 위치나 디스크를 구분해서 지정해서 해결하는 것도 가능합니다. 하지만 MySQL에서는 테이블의 파티션 단위로 인덱스를 순차적으로 생성하는 방법은 아직 허용되지 않습니다.

- 파티션 테이블의 검색

SQL이 수행되기 위해 파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 조건은 다음과 같습니다.

1) WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?

2) WHERE 절의 조건이 인덱스를 효율적으로 사용(인덱스 레인지 스캔)할 수 있는가?

​

두 번째 내용은 파티션 테이블뿐 아니라 파티션되지 않은 일반 테이블의 검색 성능에도 똑같이 영향을 미치는 것입니다. 하지만 파티션 테이블에서는 첫 번째 선택사항의 결과에 의해 두 번째 선택사항의 작업 내용이 달라질 수 있습니다. 위의 두 가지 주요 선택사항의 각 조합이 어떻게 실행되는지 한번 살펴 보겠습니다.

​

1) 파티션 선택 가능 + 인덱스 효율적 사용 가능

두 선택사항이 모두 사용 가능할 때 쿼리가 가장 효율적으로 처리 될 수 있습니다. 이때는 파티션의 개수에 관계없이 검색을 위해 꼭 필요한 파티션의 인덱스만 레인지 스캔합니다.

​

2) 파티션 선택 불가 + 인덱스 효율적 사용 가능

WHERE 조건에 일치하는 레코드가 저장된 파티션을 걸러낼 수 없기 때문에 우선 테이블의 모든 파티션을 대상으로 검색해야 합니다. 하지만 각 파티션에 대해서는 인덱스 레인지 스캔을 사용할 수 있기 때문에 최종적으로 테이블에 존재하는 모든 파티션의 개수만큼 인덱스 레인지 스캔을 수행해서 검색하게 됩니다. 이 작업은 파티션 개수만큼의 테이블에 대해 인덱스 레인지 스캔을 한 다음 결과를 병합해서 가져오는 것과 같습니다.

​

3) 파티션 선택 가능 + 인덱스 효율적 사용 불가

검색하려는 레코드가 저장된 파티션을 선별할 수 있기 때문에 파티션 개수에 관계없이 검색을 위해 필요한 파티션만 읽으면 됩니다. 하지만 인덱스는 이용할 수 없기 때문에 대상 파티션에 대해 풀 테이블 스캔을 합니다. 이는 각 파티션의 레코드 건수가 많다면 상당히 느리게 처리될 것입니다.

​

4) 파티션 선택 불가 + 인덱스 효율적 사용 불가

WHERE 조건에 일치하는 파티션을 선택할 수가 없기 때문에 테이블의 모든 파티션을 검색해야 합니다. 하지만 각 파티션을 검색하는 작업 자체도 인덱스 레인지 스캔을 사용할 수 없기 때문에 풀 테이블 스캔을 수행해야 합니다.

​

위에서 살펴본 선택사항의 4가지 조합 가운데 마지막 세 번째와 네 번째 방식은 가능하다면 피하는 것이 좋습니다. 그리고 두 번째 조합 또한 하나의 테이블에 파티션의 개수가 많을 때는 MySQL 서버의 부하도 높아지고 처리 시간도 많이 느려지므로 주의가 필요합니다.

실제 MySQL 서버는 여러 파티션에 대해 인덱스 스캔을 수행할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 우선순위 큐(Priority Queue)에 임시로 저장합니다. 그리고 우선순위 큐에서 다시 필요한 순서(인덱스의 정렬 순서)대로 데이터를 가져가는 것입니다. 이는 각 파티션에서 읽은 데이터가 이미 정렬돼 있는 상태라서 가능한 방법입니다. 결론적으로 파티션 테이블에서 인덱스 스캔을 통해 레코드를 읽을 때 MySQL 서버가 별도의 정렬 작업을 수행하지 않습니다. 하지만 일반 테이블의 인덱스 스캔처럼 결과를 바로 반환하는 것이 아니라 내부적으로 큐 처리가 한번 필요한 것입니다. 머지&소트(Merge & Sort)라고 표시한 부분이 바로 우선 순위 큐 처리 작업을 의미합니다.

[출처] [MySQL] MySQL 파티션 개요|작성자 작은해결사

추가로 읽어볼 것.

multiple table vs indexed on single partitioned table

https://stackoverflow.com/questions/16721772/mysql-performance-multiple-tables-vs-index-on-single-table-and-partitions

why use both partitioninig and index on table 

https://logicalread.com/sql-server-partitioned-tables-and-indexes-w02/#.XPuc3NMzbfY