미스터 역마살
Mysql 파티셔닝 본문
728x90
반응형
파티션 생성시 주의사항
- 스토어드루틴이나 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|
;
728x90
'Database > MYSQL' 카테고리의 다른 글
Mysql Audit Log (0) | 2022.08.03 |
---|---|
Mysql 모니터링 스크립트 (0) | 2022.08.01 |
Mysql DBA 업무 SQL (0) | 2022.08.01 |
Mysql InnoDB (0) | 2022.07.31 |
[Mysql 에러] Truncated incorrect INTEGER value : 'AWS' (0) | 2022.07.31 |
Comments