꿈꾸는 개발자, DBA 커뮤니티 구루비

7.4 파티션

7.4.1. 명시적 파티션 지정

다음 SQL문에 직접 파티션 명시 가능

SELECT
DELETE
INSERT
REPLACE
UPDATE
LOAD DATA
LOAD XML
FROM 절 뒤 테이블명 뒤에 PARTITION 절을 명시하고 해당 파티션을 입력한다.

7.4.1.1 명시적 파티션 지정 사용법


MariaDB [employees]> CREATE TABLE tb_partition_employees(
    -> emp_no int NOT NULL,
    -> birth_date date NOT NULL,
    -> first_name varchar(14) NOT NULL,
    -> last_name varchar(16) NOT NULL,
    -> gender enum('M','F') NOT NULL,
    -> hire_date date NOT NULL
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE COLUMNS(hire_date)(
    -> PARTITION p0 VALUES LESS THAN ('1990-01-01'),
    -> PARTITION p1 VALUES LESS THAN ('2000-01-01'),
    -> PARTITION p2 VALUES LESS THAN ('2010-01-01')
    -> );
Query OK, 0 rows affected (0.49 sec)

MariaDB [employees]> Insert Into tb_partition_employees SELECT * FROM employees;
Query OK, 300024 rows affected (13.55 sec)
Records: 300024  Duplicates: 0  Warnings: 0

MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees;
+----------+
| COUNT(*) |
+----------+
|   300024 |
+----------+
1 row in set (1.14 sec)


MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p0);
+----------+
| COUNT(*) |
+----------+
|   164797 |
+----------+
1 row in set (0.55 sec)

MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p1);
+----------+
| COUNT(*) |
+----------+
|   135214 |
+----------+
1 row in set (0.50 sec)

MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p1,p2);
+----------+
| COUNT(*) |
+----------+
|   135227 |
+----------+
1 row in set (0.31 sec)

MariaDB [employees]> insert into tb_partition_employees PARTITION(p0) values (1,'1984-01-12','Matt','Lee','M','2009-01-01');
ERROR 1748 (HY000): Found a row not matching the given partition set
MariaDB [employees]> select * from tb_partition_employees where emp_no=10001;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.90 sec)

MariaDB [employees]> select * from tb_partition_employees PARTITION(p2) where emp_no=10001;
Empty set (0.00 sec)

MariaDB [employees]>  update tb_partition_employees PARTITION(p2) set gender='F' where emp_no=10001;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

MariaDB [employees]>  update tb_partition_employees PARTITION(p0) set gender='F' where emp_no=10001;
Query OK, 1 row affected (0.89 sec)
Rows matched: 1  Changed: 1  Warnings: 0

7.4.1.2 명시적 파티션 지정 기능의 용도

명시적인 파티션 지정 기능은 옵티마이저가 파티션 프루닌ㅇ을 제대로 처리하지 못할 떄 명시적으로 사용할 파티션을 지정해 주는 용도로 사용할 수 있다.


MariaDB [employees]> create function getDate()
    -> RETURNS DATE
    -> NOT DETERMINISTIC --> 파티션 프루닝기능 제거역활
    -> RETURN '2000-01-02';
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> explain partitions 
    -> select * from tb_partition_employees where hire_date=getDate()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_partition_employees
   partitions: p0,p1,p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299356
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [employees]> explain partitions
    -> select * from tb_partition_employees PARTITION(p2) where hire_date=getDate()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_partition_employees
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 13
        Extra: Using where
1 row in set (0.01 sec)

7.4.2 파티션 테이블 스페이스 교체(Exchange)

MySQL 5.6 과 MariaDB 10.0 추가 기능
Exchange 기능 제약사항
파티션 테이블과 스와핑할 테이블 구조가 동일
스와핑을 할 테이블은 임시테이블이면 안됨
스와핑될 테이블은 파티션 조건을 만족해야한다.(파티션 범위)
스와핑할 두 테이블은 다른 테이블들과 참조관계가 없어야한다.(Foreign Key)
해당 테이블에 대한 INSERT , ALTER , DROP , CREATE 권한 필요


MariaDB [employees]> create table tb_swap(
    -> emp_no int NOT NULL,
    -> birth_date date NOT NULL,
    -> first_name varchar(14) NOT NULL,
    -> last_name varchar(16) NOT NULL,
    -> gender enum('M','F') NOT NULL,
    -> hire_date date NOT NULL
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

MariaDB [employees]> insert into tb_swap values(1,'1985-01-12','Matt','Lee','M','1989-10-10');
Query OK, 1 row affected (0.01 sec)

MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p0);
+----------+
| COUNT(*) |
+----------+
|   164797 |
+----------+
1 row in set (0.36 sec)

MariaDB [employees]> select count(*) from tb_swap;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

MariaDB [employees]> alter table tb_partition_employees EXCHANGE PARTITION p0 WITH TABLE tb_swap;
Query OK, 0 rows affected (0.14 sec)


MariaDB [employees]> SELECT COUNT(*) from tb_partition_employees PARTITION(p0);
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

MariaDB [employees]> select count(*) from tb_swap;
+----------+
| count(*) |
+----------+
|   164797 |
+----------+
1 row in set (0.36 sec)

MariaDB [employees]> alter table tb_partition_employees EXCHANGE PARTITION p1 WITH TABLE tb_swap;
ERROR 1737 (HY000): Found a row that does not match the partition