12.[SQL] Query Tuning - Partition 파티션

[SQL] Query Tuning - Partition

파티션이란

• 파티션 - 구획 분할
  • MySQL 서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 기능을 의미
  • 큰 table이나 index를, 관리하기 쉬운 partition이라는 작은 단위로 물리적으로 분할하는 것
• 파티션을 사용하면 대용량 작업을 더 빠르게 처리할 수 있음
• 파티션 단위의 조회 및 DML → 부하를 분산시켜줌
  • DML 인 Delete 로 처리하게 된다면 삭제대상을 조회하는 부하/Operation 과 Redo/Undo를 생성하는 DML영역의 Operation 의 부하등이 동반되기 때문에 파티션을 사용한 파티션 단위의 Drop 을 이용하면 이런 부분을 대폭 줄일 수 있습니다.

왜 쓰는가?

• 주기적으로 삭제 등의 작업이 이루어지는 이력성 데이터의 효율적인 관리
• INSERT와 범위 SELECT의 빠른 처리
  • 특히, Full Scan에서 데이터 Access의 범위를 줄여 성능 향상을 가져온다

단점

• table간 JOIN에 대한 비용이 증가한다.
• table과 index를 별도로 파티셔닝할 수 없다.
  table과 index를 같이 파티셔닝해야 한다.

수평 파티셔닝

• 하나의 테이블 행들 단위로 다른 테이블에 분산
  • 일반적으로 하나의 DB안에서 이루어짐

수직 파티셔닝

• 특정컬럼을 쪼개서 따로 저장
• 스키마를 나누는 것
• 하나의 엔티티를 두개 이상으로 분리

---

파티셔닝 분할 기준

1. 범위 분할 (range partitioning)
   분할 키 값이 범위 내에 있는지 여부로 구분한다.
   예를 들어, 우편 번호를 분할 키로 수평 분할하는 경우이다.
2. 목록 분할 (list partitioning)
   값 목록에 파티션을 할당 분할 키 값을 그 목록에 비추어 파티션을 선택한다.
   예를 들어, Country 라는 컬럼의 값이 Iceland , Norway , Sweden , Finland , Denmark 중 하나에 있는 행을 빼낼 때 북유럽 국가 파티션을 구축 할 수 있다.
3. 해시 분할 (hash partitioning)
   해시 함수의 값에 따라 파티션에 포함할지 여부를 결정한다.
   예를 들어, 4개의 파티션으로 분할하는 경우 해시 함수는 0-3의 정수를 돌려준다.
4. 합성 분할 (composite partitioning)
   상기 기술을 결합하는 것을 의미하며, 예를 들면 먼저 범위 분할하고, 다음에 해시 분할 같은 것을 생각할 수 있다.
   컨시스턴트 해시법은 해시 분할 및 목록 분할의 합성으로 간주 될 수 있고 키 공간을 해시 축소함으로써 일람할 수 있게 한다.

Range partition

• 가장 기초적인 방법
• 주로 날짜 컬럼을 기준으로 파티셔닝(파티션 화)한다.
• 방문일자, 주문일자 등을 기준으로 파티셔닝

create 방문테이블 ( 방문번호 number, 방문일자 varchar2(8), 고객ID varchar2(5), 
            배송일자 varchar2(8), 방문횟수 number ... )
partition by range(방문일자) (
    partition by P2020_Q1 value less than ('20200101') -- 1월 기준 
    ,partition by P2020_Q2 value less than ('20200201')-- 2월 기준 
    ,partition by P2020_Q3 value less than ('20200301')
    ,partition by P2020_Q4 value less than ('20200401')
    ,partition by P2020_Q5 value less than ('20200501')
    ,partition by P2020_Q6 value less than ('20200601')
    ,partition by P2020_Q7 value less than ('20200701')
    ,partition by P2020_Q8 value less than ('20200801')
    ,partition by P2020_Q9 value less than ('20200901')
    ,partition by P2020_Q10 value less than ('20201001')
    ,partition by P2020_Q11 value less than ('20201101')
    ,partition by P2020_Q12 value less than (MAXVALUE) 
                    -- 방문일자 >= '20201101'
)

• 각 레코드를 파티션 키 값에 따라 분할하여 저장하며,
• 조회를 할 때에도 검색 조건을 만족하는 파티셔만 골라서 읽을 수 있어서 성능 향상에 도움을 줌 (읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외)

파티션 테이블 조회

-- 2020년 1월 부터 ~ 4월 30일까지 방문한 고객 
select *
from 방문테이블
where 방문일자 >= '20200101'
and 방문일자 < '20200501' 

• 아래와 같다.

1.파티션명 지정
select * 
from table_name  partition(P2020_Q1, P2020_Q2, P2020_Q3, P2020_Q4)

2. 파티션 조건 지정 (파티션명을 모를 경우)
select ~~
from table_name  partition for ('7')   
-- 파티션 키가 되는 값 지정, 숫자, 날짜등...

파티션 테이블 데이터 삭제

--파티션 DELETE문
DELETE FROM 테이블명 PARTITION (파티션명)
WHERE
조건;

파티션 어딨는지 어떻게 알아?

• EXPLAIN PARTITIONS 사용 파티션 알아보기

mysql> EXPLAIN PARTITIONS
SELECT * FROM `test`.`tb_part_test` 
WHERE 1=1
and part_key_col between '2019/01/01' and '2020/12/31' 
ORDER BY sales_code;

+----+-------------+--------------+-------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type |     table    |  partitions |  type | possible_keys |    key    | key_len |  ref | rows | filtered |         Extra            |
+----+-------------+--------------+-------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 |   SIMPLE    | tb_part_test | p2019,p2020 | index |      NULL     | idx1_test |   302   | NULL |   1  |   100.00 | Using where; Using index |
+----+-------------+--------------+-------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

---

reference

출처:
https://dev4u.tistory.com/628 [데브포유:티스토리]

https://imnkj.tistory.com/55?category=822144