Last Updated on 1월 20, 2024 by Jade(정현호)
Contents
Partition(파티션) 이란
파티션이란 MySQL 서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 기능을 의미합니다.
일반적으로 DBMS의 파티션은 하나의 서버에서 테이블을 분산하는 것을 의미합니다.
파티션을 사용하는 이유
1) INSERT와 범위 SELECT의 빠른 처리
테이블의 사이즈의 커서 인덱스의 크기가 메모리 크기 보다 훨씬 큰 경우 파티션 테이블을 이용하여 분할하는 경우 인덱스도 각각 생성되기 때문에 작은 인덱스 크기로 인해 메모리에서 빠르게 쿼리 작업을 진행할 수 있습니다.
UPDATE 와 DELETE 문 처리시에도 대상 레코드를 검색하기 위해서 인덱스는 사용됩니다.
그렇기 때문에 커지면 커질수록 SELECT 외 UPDATE 와 DELETE 수행 속도가 느려질 수 있습니다
INSERT 시 B-Tree 구조로 인하여 만약 리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리(Split)가 되게 되며 Index Split 시에도 큰 인덱스보다는 작은 크기의 인덱스가 유리할 수 있습니다.
2) 주기적으로 삭제 등의 작업이 이루어지는 이력성 데이터의 효율적인 관리
비지니스 로직이나 제도화된 법에 의해서 필수 보관주기가 지난 데이터의 정리나 로그 성 데이터의 테이블을 파티션 테이블로 관리한다면 불필요한 데이터 삭제 작업은 단순히 파티션을 추가하거나 삭제하는 방식으로 간단하고 빠르게 해결할 수 있습니다.
DML 인 Delete 로 처리하게 된다면 삭제대상을 조회하는 부하/Operation 과 Redo/Undo를 생성하는 DML영역의 Operation 의 부하등이 동반되기 때문에 파티션을 사용한 파티션 단위의 Drop 을 이용하면 이런 부분을 대폭 줄일 수 있습니다.
3) 데이터의 물리적인 저장소를 분리
파티션을 통해 파일의 크기를 조절하거나 각 파티션별 파일들이 저장될 위치나 디스크를 구분해서 지정/저장할 수 있습니다.
파티션 테이블에서의 쿼리의 처리
Sample Table 생성
use test;
drop table `test`.`tb_part_test`;
CREATE TABLE `test`.`tb_part_test` (
key_col INT NOT NULL,
part_key_col DATETIME NOT NULL,
sales_code varchar(100) NOT NULL,
PRIMARY KEY(key_col,part_key_col)
)
PARTITION BY RANGE ( YEAR(part_key_col) ) (
PARTITION p2017 VALUES LESS THAN (2018),
PARTITION p2018 VALUES LESS THAN (2019),
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p999999 VALUES LESS THAN MAXVALUE
);
ALTER TABLE `test`.`tb_part_test`
ADD INDEX `idx1_test` (`sales_code`);
insert `test`.`tb_part_test` values(1,'2020/05/30','aa');
insert `test`.`tb_part_test` values(1,'2019/03/15','bb');
insert `test`.`tb_part_test` values(1,'2018/07/20','cc');
insert `test`.`tb_part_test` values(1,'2017/11/14','dd');
INSERT
MySQL 서버는 INSERT 되는 칼럼의 값 중 파티션 키인 part_key_col 컬럼 값을 이용해서 파티션 표현식을 평가하고, 그 결과를 이용해 레코드가 저장될 적절한 파티션을 결정하게 됩니다.
데이터 입력 시 적재가 될 파티션을 선택하는 과정에서 데이터의 입력은 일반 테이블에서의 Insert 와 동일 합니다.
UPDATE
쿼리의 WHERE 조건에 파티션 키 칼럼이 조건으로 존재한다면 그 값을 이용해 레코드가 저장된 파티션에서 빠르게 대상 레코드를 검색할 수 있습니다.
하지만 WHERE 조건에 파티션 키 칼럼의 조건이 명시되지 않았다면 변경 대상 레코드를 찾기 위해 모든 파티션을 검색해야 합니다.
파티션 키 컬럼외 컬럼을 업데이트 할 경우 보통의 갱신과 동일하며 파티션 키 컬럼이 변경될 때에는 기존 파티션에서 레코드 삭제, 변경하려는 데이터에 대한 파티션 키 평가, 표현식의 평가결과에 맞는 파티션에 저장하는 형태로 수행됩니다.
SELECT
파티션 테이블의 조회에서는 두 가지 부분이 중요 합니다.
- WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
- WHERE 절의 조건이 인덱스를 효율적으로 사용할 수 있는가?
Non-Partition Table의 조회시와 동일한 내용입니다
그래서 위의 두 조건의 조합에 따라 실행이 달라지며 아래와 같은 조합이 가능하다.
- 파티션 선택 가능 + 인덱스 효율적 사용 가능
: 두가지 조합이 모두 사용이 가능할 경우 가장 효율적으로 처리될 수 있습니다.
- 파티션 선택 불가 + 인덱스 효율적 사용 가능
: 파티션 조건으로 특정 파티션을 선택할 수 없기 때문에 모든 파티션에 대해서 검색을 필요하며
각 파티션내에 인덱스에 대해서는 인덱스 Range Scan 을 사용하게 됩니다.
정리하면 모든 파티션 개수만큼 인덱스 Range Scan 한 결과를 합친 것과 동일 합니다.
그렇기 때문에 파티션 수가 많을 수록 성능저하 나 부하가 많아질 수 있습니다.
- 파티션 선택 가능 + 인덱스 효율적 사용 불가
: 검색하려는 레코드의 파티션만 읽으면 되며, 해당 파티션에 대해 풀 테이블 스캔을 하게 됩니다.
파티션별 건수가 성능에 연관되며 파티션별 건수가 많게 되면 풀 스캔 하기 때문에 느리게 처리될 것입니다.
- 파티션 선택 불가 + 인덱스 효율적 사용 불가
: WHERE 절에 일치하는 파티션을 선택할 수도 없어 모든 파티션을 Scan 해야 하고 각 파티션별로도 풀 테이블 스캔을 수행해야 합니다.
가장 성능적으로도 느리고 MySQL 에도 부하를 주게 됩니다.
파티션 테이블 인덱스 스캔 과 정렬
다른 RDB 와 달리 MySQL의 파티션 테이블에서 인덱스는 전부 Local Index 형태입니다.
파티션 단위로 인덱스가 생성되며, 파티션과 별개인 테이블 전역적인 Global Index는 지원하지 않습니다.
아래 쿼리는 파티션 키 값이 아닌 다른 컬럼(sales_code) 으로 조회하고 있고, 정렬 또한 파티션 순서로 정렬되어 있지는 않습니다.
SELECT * FROM `test`.`tb_part_test`
WHERE 1=1
and sales_code BETWEEN 'aa' AND 'cc'
ORDER BY sales_code;
이와 같은 쿼리 수행 시 위의 조합 중 "파티션 선택 불가 + 인덱스 효율적 사용 가능" 에 해당합니다.
MySQL은 여러 파티션에 대해 인덱스 스캔을 수행할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 Priority Queue에 임시로 저장하게 되고 Priority Queue에서 다시 필요한 순서대로 데이터를 가져오게 됩니다
파티션 프루닝(pruning)
파티션 프루닝(pruning)은 MySQL 에서 WHEHE 구문에 사용된 조회 조건에 만족한 파티션만 추출하는 기능입니다.
최적화 단계에서 필요한 파티션만 골라내고 불필요한 것들은 배제하는 것입니다.
파티션 프루닝은 SELECT, DELETE, UPDATE 구문에서만 가능 합니다.
파티션 프루닝이 수행 여부나 내용을 확인은 SQL 플랜을 보고 확인할 수 있으며
5.7 이전 버전에서 파티션 프루닝 정보를 보고자 할 때는 EXPLAIN PARTITIONS 를 사용하면 됩니다.
5.7 버전부터는 EXPLAIN 명령어 결과에 PARTITIONS 정보가 포함되어 있습니다
EXPLAIN partitions 없이 EXPLAIN 만으로도 파티션 프루닝 정보가 표기됩니다.
(EXPLAIN output includes partition information)
참고로 EXPLAIN EXTENDED 명령어도 이전 버전과의 호환성(backward compatibility) 을 위해서 존재하고 5.7버전 부터는 EXPLAIN 명령어에 해당 기능이 포함되어 있습니다.
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 | +----+-------------+--------------+-------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
파티션 테이블 사용시 주의사항
1) 파일 오픈 개수
MySQL에서는 일반적으로 테이블을 파일 단위로 관리하고 InnoDB에서 innodb_file_per_table 를 보통 설정하여 사용하기 때문에 MySQL에서 동시에 오픈하게 되는 파일의 개수가 상당히 많아질 수 있습니다.
파티션의 수만큼 File 을 Open 하기 때문에 관련된 파라미터를 사전에 조정해주거나 파라미터 값에 도달하는지를 확인할 필요가 있습니다.
MySQL 엔진 측면에서 open_files_limit 과 OS 측면에서는 nofile 이 있습니다.
Open File Descriptor 와 관련된 파라미터입니다.
open_files_limit 의 기본값은 5000, with possible adjustment 입니다.
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 5000 |
+------------------+-------+
my.cnf 파일에 open_files_limit 값을 늘려줘야 하며 /etc/security/limits.conf 파일에서 nofile 를 지정하여 값을 늘려줄수 있습니다.
my.cnf
[mysqld]
open_files_limit=10000
/etc/security/limits.conf
mysql hard nofile 65536
mysql soft nofile 65536
2) MyISAM 사용시
MyISAM 사용시 5.1,5.5(5.6.5 이하 버전) 에서 DML/DDL에 의해서 파티션 된 테이블에 영향을 미치는 Table Level Lock 이 걸게 되고 즉, 명령문이 완료될 때까지 모든 파티션이 잠깁니다
예를 들어, 분할 된 MyISAM 테이블의 SELECT는 전체 테이블을 잠급니다.
그래서 파티션 수가 증가함에 따라 더 느리게 실행되거나(성능저하) 나 MySQL 에 부하를 가중시키게 됩니다
이러한 기능적 제한은 MySQL 5.6.6에 partition lock pruning 이 도입하여 MySQL 5.6에서 크게 감소되었습니다
MySQL 5.7에서는 partition lock pruning 은 많은 경우에 불필요한 잠금을 제거하며, 파티션 된 MyISAM 테이블에서 읽거나 업데이트하는 대부분의 명령문은 영향을 받는 파티션 만 잠 깁니다.
예를 들어, 분할 된 MyISAM 테이블의 SELECT는 SELECT 문의 WHERE 조건을 충족하는 행을 실제로 포함하는 파티션 만 잠급니다(5.5 버전과 차이를 보임)
5.5 예를 들어 분할 된 MyISAM 테이블의 SELECT는 전체 테이블을 잠급니다.
to
5.7
예를 들어, 분할 된 MyISAM 테이블의 SELECT는 SELECT 문의 WHERE 조건을 충족하는 행을 실제로 포함하는 파티션 만 잠급니다
InnoDB와 같은 스토리지 엔진을 사용하는 파티션 테이블에 영향을 미치는 문에는 해당되지 않으며, row-level locking 을 사용하고 partition pruning 전에 실제로 잠금을 수행 (또는 수행해야 함)하지 않습니다.
Ref link.
http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-locking.html
5.6.6 implements partition lock pruning
https://downloads.mysql.com/docs/refman-5.5-en.a4.pdf
19.5.4 Partitioning and Table-Level Locking
버전 별 제한 사항
파티션 테이블 사용 및 기능은 버전이 업그레이드됨에 따라 점차 많은 지원이 되고 있고 추가되고 있습니다.
그래서 사용하는 MySQL 버전에 따라서 파티션 테이블에 대한 제한 사항이나 기능의 제한이 있을 수 있습니다.
MySQL 5.1
- 최대 1024개의 파티션을 가질 수 있습니다 (서브 파티션까지 포함)
- 숫자값(INTEGER 타입 컬럼 또는 INTEGER 타입을 반환하는 함수 및 표현식)에 의해서만 파티션이 가능합니다.
MySQL 5.5 부터는 숫자 타입뿐 아니라 문자열이나 날짜 타입에 대해서 조건부 사용이 가능하도록 기능 개선되었습니다.
- 키 파티션은 해시 함수를 MySQL이 직접 선택하기 때문에 컬럼 타입 제한이 없습니다.
- 스토어드 루틴이나 UDF 그리고 사용자 변수 등을 파티션 함수나 식에 사용할 수 없습니다.
- 파티션 생성 이후 MySQL 서버의 sql_mode 파라미터 변경은 추천하지 않습니다.
- 파티션 테이블에서는 외래키 사용이 불가능합니다.
- 파티션 테이블은 전문 검색 인덱스 생성이 불가능합니다.
- 공간 확장 기능에서 제공되는 컬럼 타입(POINT, GEOMETRY, ..)은 파티션 테이블에서 사용이 불가능합니다.
- 임시 테이블(Temporary table)은 파티션 기능 사용 불가능 합니다.
- MyISAM 파티션 테이블의 경우 키 캐시를 사용할 수 없습니다.
Ref link https://12bme.tistory.com/52?category=682920
MySQL 5.5 and above
제한되는 구조
Stored Procedures, Stored Functions, UDFs, Plugins , Declared 변수 or 사용자 변수 는 사용할 수 없습니다
산술 및 논리 연산자 제한
산술 연산자 +,-및 *는 파티셔닝 표현식에서 사용할 수 있습니다.
하지만 그 결과는 정수 값 또는 NULL 이 되어야 합니다 ([LINEAR] KEY 파티셔닝의 경우 제외)
DIV 연산자도 지원되지만 "/" 연산자는 허용되지 않습니다.
비트 연산자인 |, &, ^, <<, >> 그리고 ~ 는 파티셔닝 표현식에서 허용되지 않아 사용이 불가 합니다.
HANDLER 문법 미지원
MySQL 5.5 에서는 HANDLER 문이 파티션 테이블에서 지원하지 않았습니다
HANDLER tbl_name OPEN [ [AS] alias]
HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...) [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST } [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE
이런 제약사항은 MySQL 5.7.1 부터 없어졌습니다(개선됨)
SQL mode 변경에 따른 영향
먼저 MySQL 에는 전역적/세션레벨에서 지정할 수 있는 sql_mode 가 있습니다
sql_mode 는 데이터에 대한 유효성 검사(validation check) 범위나 조건을 설정하는 변수로 Global/Session 레벨의 지정이 가능 합니다.
5.7 버전 기준
mysql> SELECT @@SESSION.sql_mode;
+----------------------------------------------------------------+
| @@SESSION.sql_mode |
+-----------------------------------------------------------------
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION
+----------------------------------------------------------------+
Default 값은 위와 같으며 아래와 같이 0으로 나누게 되면 warning 이 발생하게 됩니다.
mysql> select 1/0 from dual;
+------+
| 1/0 |
+------+
| NULL |
+------+
mysql> show warnings;
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
sql_mode 를 모두 제거(disable) 한 후 다시 확인해보면 특별한 warning 없이 진행되는 것을 확인할 수 있습니다.
mysql> set session sql_mode="";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT @@SESSION.sql_mode;
+----------------------+
| @@SESSION.sql_mode |
+----------------------+
| |
+----------------------+
1 row in set (0.00 sec)
mysql> select 1/0 from dual;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
파티션도 동일하게 sql_mode 에 영향을 받게 됩니다.
mysql> SELECT @@SESSION.sql_mode;
+----------------------------------------------------------------+
| @@SESSION.sql_mode |
+-----------------------------------------------------------------
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use test;
mysql> CREATE TABLE `test` (col1 INT)
partition by list (1 div col1)
( partition p0 values in (null),
partition p1 values in (1)
);
mysql> insert into `test` values (null),(0),(1);
ERROR 1365 (22012): Division by 0
-> ERROR_FOR_DIVISION_BY_ZERO 가 지정되어 있기 때문에 데이터 입력이 불가능 하게 됩니다. (1 div col1)
mysql> set session sql_mode="";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into `test` values (null),(0),(1);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from `test`;
+------+
| col1 |
+------+
| NULL |
| 0 |
| 1 |
+------+
3 rows in set (0.00 sec)
이와 같이 파티션 테이블은 생성시점의 sql_mode가 아닌 sql 실행시점의 sql_mode를 따르게 되기 때문에 sql_mode의 변경에 따라서 파티션 연산자 계산 관련된 User Define 에서 에러가 발생될 수 있습니다.
추가로 sql_mode 는 파티션 테이블의 replication 에도 영향을 주게 됩니다.
Master 와 Slave 간의 차이가 나는 sql_mode로 인하여 파티셔닝 표현식이 다르게 평가될 수 있습니다
이로 인하여 Master 와 Slave 의 파티션 간 데이터 분배가 달라지고 Master에서 성공한 파티션 테이블에 대한 입력이 Slave node에서는 실패할 수도 할 수도 있으므로 sql_mode 는 동일하게 변경하거나 변경에 유의해야 합니다.
Ref link
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
Performance considerations
파티션 성능에 영향을 미치는 요소는 다음과 같이 있습니다.
- File system operations
파티션의 생성이나 추가, 분할 등의 Operation시 파일시스템과 관련된 설정 등이 영향을 미치게 됩니다.
MySQL에서 large_files_support 이 설정되어 있는지, open_files_limit 이 파티션 개수 이상으로 적절하게 설정 되어있는지 확인이 되어야 하며
(추가로 OS에서는 limit.conf에서 nofile )
MyISAM 에서는 myisam_max_sort_file_size 도 늘려주면 성능향상에 도움이 될 수 있습니다.
InnoDB에서 innodb_file_per_table 파라미터를 사용하면 조금 더 효율적으로 파티션 테이블을 사용할 수 있습니다.
- MyISAM and partition file descriptor usage
MyISAM에서 파티션 테이블을 사용한다면 파티션당 2개의 파일을 사용 및 Open 하게 됩니다.
이 말의 의미는 MyISAM에서 파티션을 사용할 경우 File descriptor 관련된 수치를 확인이 필요나 관리가 필요 합니다.
ALTER TABLE에 의해서 파티션 분할 수행시 더 많은 File descriptor 를 사용할 수 있으므로 open_files_limit 변수나 OS의 nofile 도 충분한 값으로 설정이 필요 합니다.
- ALGORITHM and ALTER TABLE (MySQL5.6 and above)
파티션 테이블에 대해서 alter table 구문에서 ALGORITHM 절은 지원되지 않습니다.
ALGORITHM 와 관련된 내용은 아래 Online DDL 포스팅을 참조하시면 됩니다.
- Table locks
일반적으로 테이블에서 파티셔닝 작업을 실행하는 동안 테이블에 대한 쓰기 잠금(write lock/shared lock) 을 사용합니다
이러한 테이블의 읽기는 상대적으로 영향을 받지 않으며 대기중인 INSERT 및 UPDATE 작업은 파티션 작업이 완료되는 즉시 수행됩니다.
MySQL Online DDL과 관련된 제약사항으로 파티션에 대한 ALGORITHM 구문은 지원되지 않습니다.
(ALTER TABLE statements that change a table's partitioning cannot use ALGORITHM with DEFAULT, COPY, or INPLACE)
위의 MySQL Online DDL 포스팅을 참조해주세요
- Storage engine
파티션에 대한 작업이나 조회나 Update 쿼리 수행은 일반적으로 InnoDB나 NDB 보다 MyISAM이 더 빠르게 처리됩니다
- Indexes Partition pruning
파티션되지 않은 일반 테이블과 마찬가지로 인덱스를 적절하게 사용하면 파티션 테이블에 대한 쿼리 속도를 크게 높일 수 있습니다.
파티션의 설계와 쿼리 문구를 사용하여 파티션 프루닝을 사용하면 성능을 크게 개선할 수 있습니다
- Performance with LOAD DATA
MySQL 에서 파티션 테이블 사용시 LOAD DATA는 버퍼링을 사용하여 성능을 향상시킵니다.
이 기능을 사용을 위해서 버퍼는 파티션 당 약 130KB 의 메모리를 사용하게 됩니다.
Maximum number of partitions (서브 파티션까지 포함)
최대 1024개의 파티션을 가질 수 있습니다 - MySQL 5.5 기준
최대 8192개의 파티션을 가질 수 있습니다 - MySQL 5.6.7 이상 부터
Query cache not supported.
쿼리 캐시 기능은 파티션 테이블에서는 지원하지 않습니다.
MySQL 5.5.23부터는 분할 된 테이블과 관련된 쿼리에 대해 쿼리 캐시가 자동으로 비활성화됩니다.
Per-partition key caches
CACHE INDEX 문은 특정 키 캐쉬(cache)에 테이블 인덱스를 할당하는 것으로 이것은 MyISAM 테이블에서만 사용됩니다 [자세한 내용은]
MySQL 5.5(or Over) 에서는 CACHE INDEX 및 LOAD INDEX INTO CACHE 문을 사용하여 분할 된 MyISAM 테이블에 대해 키 캐시가 지원됩니다.
키 캐시는 하나 또는 여러 또는 모든 파티션에 대해 정의될 수 있으며 하나, 여러 또는 모든 파티션에 대한 인덱스는 키 캐시에 대해서 미리 로드 하여 사용할 수 있습니다.
Foreign keys not supported for partitioned InnoDB tables
InnoDB 스토리지 엔진을 사용하는 파티션 테이블에서 FK(외래 키)를 지원하지 않습니다.
ALTER TABLE ... ORDER BY
특정 컬럼을 기준으로 Sort 하게 도는 alter table ... order by 수행시, 각 파티션별 파티션 안에서 order가 조정됩니다.
ADD COLUMN ... ALGORITHM=INSTANT(버전 8.0)
MySQL 8.0에서 추가된 제약사항으로 파티션된 테이블에 컬럼 추가시 Online DDL의 ALGORITHM=INSTANT 옵션을 사용하면 더 이상 해당 테이블은 exchange partitions 이 불가합니다.
• 에러 메세지
Once one or more columns have been added to a partitioned table using ALGORITHM=INSTANT,
it is no longer possible to exchange partitions with that table.
FULLTEXT indexes 사용 불가
파티션 테이블에는 FULLTEXT indexes 가 지원되지 않습니다
Spatial columns
Spatial 은 지리정보(GIS) 나 공간 데이터를 지원하는 기능이고 그런 기능이 하는 컬럼은 파티션 테이블에서 사용이 불가 합니다.
Temporary tables
Temporary tables 은 파티셔닝 되지 않습니다.
Log tables
로그 테이블들은 파티션이 지원되지 않습니다.
파티션 키 데이터 유형
파티션 키는 반드시 integer(정수형) 컬럼 이거나 정수형 표현식이어야 합니다.
파티션 테이블에서 ENUM 컬럼은 사용할 수 없습니다.
ex) CREATE TABLE shirts (
...
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
2가지 예외는 존재합니다.
1) LINEAR KEY를 사용한 파티션을 사용할 경우 MySQL data type 이나 TEXT, BLOB 형도 파티션 키로 사용할 수 있습니다.
MySQL의 내부적인 Key-hashing 함수가 올바른 데이터 유형으로 생성하기 때문에 사용 가능 합니다
2) Range Column 이나 List Column 으로 파티셔닝을 할 때는 string형,Date형 그리고 Datatime 형 컬럼이 사용 가능 합니다.
Column index prefixes not supported for key partitioning
파티션 테이블 생성시 파티션 키에 대해서 prefix column은 무시되거나 사용할 수 없습니다.
-- 예제 테이블 1
CREATE TABLE t1 (
a VARCHAR(10000),
b VARCHAR(25),
c VARCHAR(10),
PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY() PARTITIONS 2;
위의 예제에서 컬럼 a 와 c는 primary key 생성시 prefix column 형태로 생성하고 있습니다.
이런 경우 prefix column 이 아닌 non prefix column 인 b 컬럼이 파티션 키가 되게 됩니다.
-- 예제 테이블 2
CREATE TABLE t2 (
a VARCHAR(10000),
b VARCHAR(25),
c VARCHAR(10),
PRIMARY KEY (a(10), b(5), c(2))
) PARTITION BY KEY() PARTITIONS 2;
또는
) PARTITION BY KEY(a,b,c) PARTITIONS 2;
위와 같이 생성시 Non Prefix Column 이 없기 때문에 ERROR 1503 이 발생하게 됩니다
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
MySQL 8.0 에서는 예제 1과 같이 생성시 warning 이 발생되고 향후에는 더 사용하지 못하게 하는 방향으로 진행될 예정이라고 합니다.
issues with subpartitions
파티션 안에 다시 파티셔닝 되는 파티션을 Sub Partition(서브 파티션) 이라고 하며 또는 Composite(복합) Partition 이라고도 합니다.
RANGE 와 LIST 으로 파티션을 해야 서브 파티션을 만들 수 있으며, 서브 파티션은 반드시 Hash 나 Key 파티션으로 생성해야 합니다.
추가로 SUBPARTITION BY KEY 사용시 파티션 Column 을 명시적으로 지정해야 합니다
PARTITION BY RANGE(id)
SUBPARTITION BY KEY() <-- 에러 발생
to
PARTITION BY RANGE(id)
SUBPARTITION BY KEY(id)
DELAYED option not supported.
INSERT DELAYED 구문을 사용하여 파티션 테이블에 row를 insert하는 것은 지원하지 않으며 시도시 에러가 발생합니다.
DATA DIRECTORY and INDEX DIRECTORY options
파티션 테이블 생성시 DATA DIRECTORY 와 INDEX DIRECTORY 옵션을 같이 사용하여 생성하였을 경우 아래 2개의 제약사항이 발생합니다.
1) 파티션 단위로(파티션 별로), 파티션 인덱스(local) 별로 생성되기 때문에 Table Level의 DATA DIRECTORY 및 INDEX DIRECTORY 옵션은 무시됩니다
2) Windows OS에서는 DATA DIRECTORY 및 INDEX DIRECTORY 옵션을 파티션과 서브파티션에 대해서 지원하지 않습니다.
Repairing and rebuilding partitioned tables
파티션 테이블에 대해서 CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, REPAIR TABLE 구문이 지원됩니다.
또한 ALTER TABLE ... REBUILD PARTITION 명령어를 통해 파티션의 Rebuild 를 할 수 있습니다.
ALTER TABLE ... REORGANIZE PARTITION을 사용하면 파티션이 다시 작성(rebuilt) 됩니다.
FOR EXPORT option (FLUSH TABLES).
InnoDB의 파티션 테이블에 대해서 FLUSH TABLES 의 FOR EXPORT 옵션은 지원되지 않았습니다
5.6 버전대에서는 5.6.16 부터 , 5.7 버전 대에서 5.7.4 버전 부터 지원합니다.
File name delimiters for partitions and subpartitions.
테이블 파티션 및 하위 파티션 파일 이름에는 #P# 나 #SP# 과 같은 구분 기호가 포함됩니다
파티션 키에서 사용 가능한 MySQL 내장 함수 목록
MySQL 5.1 이상 파티션 표현식에 사용할 수 있는 함수는 아래와 같습니다.
ABS(),CEILING(),DAY(),DAYOFMONTH(),
DAYOFWEEK(),DAYOFYEAR(),DATEDIFF(),
EXTRACT(),FLOOR(),HOUR(),MICROSECOND(),
MINUTE(),MOD(),MONTH(),QUARTER(),
SECOND(),TIME_TO_SEC(),TO_DAYS(),
WEEKDAY(),YEAR(), YEARWEEK()
파티션 과 PK 키 포함(유니크 키)
위의 Column index prefix 에서 일부 설명한 내용처럼 파티션 생성시 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 컬럼이 포함되어야 합니다
불가 예제
create table test_pt (
col1 int not null,
col2 int not null,
col3 int not null,
primary key(col1),
unique key(col2,col3)
) partition by hash(col1+col2)
partitions 4;
PK 키 컬럼인 col1 값 만으로 파티션 판단이 되지 않으며, 유니크 키인 col2,col3 만으로도 파티션 위치를 결정할 수 없습니다.
긴 글 읽어 주셔서 감사합니다. 이번 글은 여기에서 마무리하도록 하겠으며, 다음 포스팅에서 계속됩니다.
• MySQL 파티션 2번째 연재글
• MySQL 파티션 3번재 연재글
Reference
Reference Book
• Real MySQL 개발자와 DBA를 위한
Reference link
• downloads.mysql.com/refman-5.5-en.a4.pdf
• dev.mysql.com/sql-mode.html
• dev.mysql.com/partitioning-limitations-locking.html
• dev.mysql.com/5.6/partitioning-limitations.html
• dev.mysql.com/5.7/partitioning-limitations.html
• dev.mysql.com/8.0/partitioning-limitations.html
• MySQLKorea - CACHE INDEX
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
안녕하세요
좋은글 써주셔서 감사합니다.
파티셔닝에 대해 학습하다 좋은 글을 찾아서 그런데 현호님 글을 인용하여 제 블로그에 글을 작성하여도 괜찮을지 여쭤보고 싶습니다 🙂
안녕하세요
출처만 밝혀주신다면 얼마든지 자유롭게 인용 등이 가능 합니다.
방문 과 댓글 남겨주셔서 감사합니다.
좋은 하루 되세요