Real MariaDB (2017년)
테이블 생성 및 변경 0 0 3,817

by 구루비스터디 MariaDB 테이블 MariaDB 스키마 [2019.08.11]


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.

온라인 스키마 변경 작업이 실패할 수 있는 경우

  • alter table 명령이 장시간 실행되고, 다른 트랜젝션에서 DML이 많이 실행되어 온라인 변경 로그의 공간이 부족한 경우
  • alter table 이후 테이블 구조에 적합하지 않는 레코드가 insert되거나 update되는 경우
  • 스키마 변경을 위해서 필요한 잠금 수준보다 낮은 잠금 옵션이 사용된 경우
  • LOCK=NONE로 실행되더라도, 변경 작업의 처음과 마지막 과정에서는 잠금이 필요한 데, 이 잠금을 획득하지 못하고 타임아웃이 발생하게 되는 경우
  • 온라인으로 인덱스 생성 시, 정렬을 위해 임시 디렉터리를 사용하는데, 이 공간이 부족하는 경우
  • 온라인 스키마 변경 작업별 처리 방식은 "교재 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 모두 사용가능하다.
  • 스키마를 변경하려는 테이블과 동일한 구조의 새로운 테이블을 만든 후에 기존 테이블의 데이터를 새로운 테이블로 조금씩 복사해서 스키마 변경을 수행하는 도구이다.
수행과정
1. 기존 테이블에 insert/delete/update AFTER 트리거를 생성하여, 데이터 변경 시 새로운 테이블로 전달 할 수 있도록 한다.
2. 모든 레코드를 복사하고 나면, RENAME 명령어를 통해 기존 테이블과 새 테이블의 이름을 변경한다.
3. RENAME 명령어는 한 트랜잭션 내에서 처리할 수 있기 때문에, RENAME 수행도중 다른 트랜젝션 쿼리는 실패하지 않는다.
4. 주의사항 : 기존 테이블에 이미 AFTER 트리거가 있는 경우 사용할 수 없고, PK와 유니크 인덱스가 있는 경우 데드락 상태가 빈번하게 발생할 수 있다.


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 파일 시스템에서 실제 물리적인 데이터 파일삭제 시 많은 시간을 소모하므로, 테이블 크기가 큰 경우 새벽시간에 작업하는 것이 좋다.


"구루비 데이터베이스 스터디모임" 에서 2017년에 "Real MariaDB" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4184

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입