Mysql 파티셔닝

파티션 생성시 주의사항

• 스토어드루틴이나 UDF, 사용자 변수등을 파티션 표현식에 사용할 수 없다.
• 파티션 표현식은 일반적으로 칼럼 그 자체 또는 mysql 내장 함수를 사용할 수 있는데, 여기서 일부 함수들은 파티션 생성은 가능하지만 파티션 프루닝은 지원하지 않을 수 있다.
  • ABS(), CEILING(), EXTRACT(), FLOOR(), MOD(), DATEDIFF(), DAY(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), HOUR(), MICROSECOND(), MINUTE(), MONTH(), QUARTER(), SECOND(), TIME_TO_SEC(), TO_DAYS(), TO_SECONDS(), UNIX_TIMESTAMP(), WEEKDAY(), YEAR(), YEARWEEK()
• 파티션 프루닝을 지원하는 내장 함수
• 프라이머리 키를 포함해서 테이블의 모든 유니크 인덱스는 파티션 키 컬럼을 포함해야 한다.
• 파티션된 테이블의 인덱스는 모두 로컬 인덱스이며 동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질 수 있다. 또한 파티션 개별로 인덱스를 변경하거나 추가할 수 없다.
• 동일 테이블에 속한 모든 파티션은 동일 스토리지 엔진만 가질 수 있다.
• 최대(서브 파티션까지) 파티션 개수는 8192개이다.
• 파티션 생성 이후 mysql 서버의 sql_mode 시스템 변수 변경은 데이터 파티션의 일관성을 깨뜨릴 수 있다.
• 파티션 테이블에서는 외래키를 사용할 수 없다.
• 파티션 테이블은전문 검색 인덱스 생성이나 전문검색 쿼리를 사용할 수 없다.
• 공간 데이터 저장 하는 컬럼 타입(POINT, GEOMETRY..)은 파티션 테이블에서 사용할 수 없다.
• 임시 테이블은 파티션 기능 사용할 수 없다.

 

 

파티션과 open_files_limit 시스템 변수 설정

Mysql에서는 일반적으로 테이블은 파일단위로 관리하기 때문에 Mysql에서 동시에 오픈된 파일의 개수가 상당히 많아 질 수 있다. 이를 제한하기 위해 open_files_limit 시스템 변수에 동시에 오픈할 수 있는 적절한 파일의 개수를 설정 할 수 있다. 파티션 되지않은 일반 테이블은 테이블 1개당 오픈된 파일의 개수가 2~3개 수준이지만 파티션 (테이블에서는 파티션 개수 x 2~3) 개가 된다.

에를 들어 1024개의 파티션 가운데 2개의 파티션만 접근해도 된다고 하더라도 일단 동시에 모든 파티션의 데이터 파일을 오픈해야 한다.

그래서 파티션을 많이 사용하는 경우에는 open_files_limit 시스템 변수를 적절히 높은 값으로 다시 설정 해줄 필요가 있다.

 

파티셔닝 테스트 쿼리

Mysql에서는 일반적으로 테이블은 파일단위로 관리하기 때문에 Mysql에서 동시에 오픈된 파일의 개수가 상당히 많아 질 수 있다. 이를 제한하기 위해 open_files_limit 시스템 변수에 동시에 오픈

/*--------------------------------------------------------------
 * 	파티셔닝 테스트
 *-------------------------------------------------------------- */

/*----------------------- 
 *  01. 사전 준비
 *----------------------- */
-- 년도별 건수 확인
SELECT YEAR(A.HIST_DT)
     , COUNT(*) AS CNT
  FROM DUMMY_TEST_TB A
 GROUP BY YEAR(A.HIST_DT)
 ;
 

-- 데이터 분산 하기
UPDATE DUMMY_TEST_TB A
   SET A.HIST_DT = STR_TO_DATE(concat('2018',substring(date_format(A.HIST_DT,'%Y%m%d%H%i%s'),5)),'%Y%m%d%H%i%s')
 WHERE YEAR(A.HIST_DT) = 2021
   AND MOD(HOUR(A.HIST_DT),2) >= 1
  LIMIT 900000
;

/*--------------------------------- 
 *  02. 파티션 테이블 생성
 *--------------------------------- */
DROP TABLE partition_dummy_test_tb ;
CREATE TABLE PARTITION_DUMMY_TEST_TB
(
	  HIST_SN INT(11) NOT NULL AUTO_INCREMENT 
	, HIST_DT DATETIME
	, CRUD_TY VARCHAR(1)
	, SCRIN_SN INT(11)
	, SCRIN_ID1 VARCHAR(100)
	, SCRIN_ID2 VARCHAR(100)
	, SCRIN_ID3 VARCHAR(100)
	, SCRIN_ID4 VARCHAR(100)
	, SCRIN_ID5 VARCHAR(100)
	, SCRIN_ID6 VARCHAR(100)
	, SCRIN_ID7 VARCHAR(100)
	, SCRIN_ID8 VARCHAR(100)
	, SCRIN_ID9 VARCHAR(100)
	, SCRIN_ID10 VARCHAR(100)
    , PRIMARY KEY(HIST_SN, HIST_DT)
)PARTITION BY RANGE(YEAR(HIST_DT))(
	  PARTITION p_2018 VALUES LESS THAN (2018)
	, PARTITION p_2019 VALUES LESS THAN (2019)
	, PARTITION p_2020 VALUES LESS THAN (2020)
	, PARTITION p_2021 VALUES LESS THAN (2021)
	, PARTITION p_2022 VALUES LESS THAN (2022)
	, PARTITION p_2023 VALUES LESS THAN (2023)
	, PARTITION p_2024 VALUES LESS THAN (2024)
	, PARTITION p_2025 VALUES LESS THAN (2025)
	, PARTITION p_2026 VALUES LESS THAN (2026)
	, PARTITION p_2027 VALUES LESS THAN (2027)
	, PARTITION p_max  VALUES LESS THAN MAXVALUE
);


/*--------------------------------- 
 *  03. 데이터 INSERT
 *--------------------------------- */
-- 테이블 사이즈 확인
SELECT TABLE_NAME 
     , ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 /1024 / 1024),3) AS SIZE_GB
     , TABLE_ROWS 
  FROM information_schema.TABLES A
 WHERE A.TABLE_SCHEMA  = 'testdb'
 ;

/*
dummy_test_tb	2.426	10986122
partition_dummy_test_tb	0.000	0
*/


-- 데이터 입력 및 시간체크
SELECT SYSDATE();
-- 2021-10-03 16:48:58


INSERT INTO PARTITION_DUMMY_TEST_TB
SELECT * FROM DUMMY_TEST_TB;

COMMIT;

SELECT SYSDATE();
-- 2021-10-03 16:49:33
-- 2.4GB 데이터 INSERT시 1분 소요




/*--------------------------------- 
 *  04. 데이터 검증
 *--------------------------------- */
SELECT COUNT(1) AS "PARTITION_DUMMY_TEST_TB" FROM PARTITION_DUMMY_TEST_TB
UNION ALL 
SELECT COUNT(1) AS "DUMMY_TEST_TB" FROM DUMMY_TEST_TB
;


-- 파티션 정보 확인
SELECT A.TABLE_SCHEMA 
     , A.TABLE_NAME
     , A.PARTITION_NAME 
     , A.PARTITION_ORDINAL_POSITION 
     , A.TABLE_ROWS 
  FROM information_schema.PARTITIONS A
 WHERE A.TABLE_SCHEMA = 'testdb'
 ;




/*--------------------------------- 
 *  05. 테이블 이름 변경
 *--------------------------------- */
RENAME TABLE DUMMY_TEST_TB TO DUMMY_TEST_TB_OLD,
             PARTITION_DUMMY_TEST_TB TO DUMMY_TEST_TB
;




/*--------------------------------- 
 *  06. 쿼리 테스트
 *--------------------------------- */
-- SHOW TABLE STATUS LIKE 'DUMMY_TEST_TB';

-- 파티션 테이블 실행게획
EXPLAIN  
SELECT A.*
  FROM DUMMY_TEST_TB A
 WHERE 1=1
   -- AND A.HIST_DT BETWEEN STR_TO_DATE('20210101','%Y%m%d') AND STR_TO_DATE('20211231','%Y%m%d')
  -- AND A.HIST_DT BETWEEN '2021-01-01' AND '2021-12-31'
   AND A.SCRIN_ID1 = 'SCRIN_ID1-4220751'
;

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows   |filtered|Extra      |
--+-----------+-----+----------+----+-------------+---+-------+---+-------+--------+-----------+
 1|SIMPLE     |A    |p_2022    |ALL |             |   |       |   |4362654|   11.11|Using where|
 
 ;

-- 일반 테이블 실행게획
EXPLAIN  
SELECT A.*
  FROM DUMMY_TEST_TB_OLD A
 WHERE A.HIST_DT BETWEEN STR_TO_DATE('20210101','%Y%m%d') AND STR_TO_DATE('20211231','%Y%m%d')
;

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows    |filtered|Extra      |
--+-----------+-----+----------+----+-------------+---+-------+---+--------+--------+-----------+
 1|SIMPLE     |A    |          |ALL |             |   |       |   |10986122|   11.11|Using where|
 ;