3.4 테이블 생성 및 변경

3.4.1 테이블 생성

MariaDB [test]> create table if not exists abc (col1 int, col2 varchar(100)) engine=innodb;

MariaDB [test]> show create table abc;
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| abc   | CREATE TABLE `abc` (
  `col1` int(11) DEFAULT NULL,
  `col2` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


MariaDB [test]> desc abc;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | int(11)      | YES  |     | NULL    |       |
| col2  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3.4.2 테이블 변경

3.4.2.1 오프라인 스키마 변경

• MariaDB 10.0버전 이상, MySQL 5.6 이상 버전에서 온라인 스키마 변경 기능이 도입되었다.
• 온라인 스키마 변경 전에는 임시테이블로 레코드를 복사하고, Rename하는 형태로 작업을 진행하였다.

3.4.2.2 MariaDB의 온라인 스키마 변경

• 온라인 처리가 가능한 DDL은 "컬럼의 추가와 삭제, 인덱스의 생성 및 삭제, 컬럼의 순서변경" 3가지 뿐이다.

MariaDB [test]> create table t1(fd1 int, fd2 enum('red', 'gree'));
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> alter online table t1 modify fd2 enum('red','green','blue');
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.

MariaDB [test]> alter online table t1 modify fd2 enum('red','gree','blue');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> alter online table t1 add fd3 int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.4.2.3 MySQL의 온라인 스키마 변경

• LOCK와 ALGORITHM 절을 이용하여 온라인 스키마 변경의 처리방법을 제어한다.

LOCK

# LOCK = {DEFAULT | NONE | SHARED | EXCLUSIVE }
* NONE : 온라인 DDL이 실행되는 동안 테이블에 읽기/쓰기가 가능
* SHARED : 읽기만 가능
* DEFAULT : LOCK를 명시하지 않은 것과 같음

ALGORITHM

# ALGORITHM = { DEFAULT | INPLACE | COPY }
* COPY : MySQL 5.5이하 버전에서 사용되던 방법으로 데이터를 임시 테이블로 모두 복사후, rename하는 방식
* INPLACE : MySQL 5.6이상 버전에서 지원되는 방법으로, 데이터를 바로 변경하되, 변경 작업시 일어나는 DML작업들은 별도의 로그로 보관했다가 데이터 변경 마지막에 일괄 적용하는 방법
* DEFAULT : ALGORITHM를 명시하지 않은 것과 같음

# inplace 방식은 온라인 변경 로그의 공간을 충분히 할당해야 메모리 공간 부족으로 작업이 실패하지 않는다.
MariaDB [test]> alter table t1 add fd4 varchar(20), lock=none, algorithm=inplace;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# auto-increment 컬럼은 LOCK=SHARED 모드로 지정해야 된다.
MariaDB [test]> alter table t1 add fd5 int auto_increment primary key, lock=none, algorithm=inplace;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED.

• 온라인 스키마 변경 작업별 처리 방식은 "교재 96페이지의 표 3-1"을 참조하자.

3.4.2.4 pt-online-schema-change

• Percona 툴킷(https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html)에 포함된 온라인 스키마 변경을 위한 3rd party 도구이다.
• MariaDB와 MySQL 모두 사용가능하다.
• 스키마를 변경하려는 테이블과 동일한 구조의 새로운 테이블을 만든 후에 기존 테이블의 데이터를 새로운 테이블로 조금씩 복사해서 스키마 변경을 수행하는 도구이다.

3.4.2.5 온라인 스키마 변경을 사용해도 될까?

• 온라인 스키마 변경 작업은 많은 시스템자원(CPU 등)을 사용하여 서비스에 악영향을 미칠 수 있으므로, 저자는 기존 3rd party 인pt-online-schema-change를 사용하는 것을 권하고 있다.

3.4.3 테이블 삭제

MariaDB [test]> drop table t1;
MariaDB [test]> drop table if exists t1;
* 주의사항 : ext3 파일 시스템에서 실제 물리적인 데이터 파일삭제 시 많은 시간을 소모하므로, 테이블 크기가 큰 경우 새벽시간에 작업하는 것이 좋다.