MySQL 5.7 버전부터 새롭게 지원되는 Online DDL 기능이 있다.
1. Index rename
2. varchar 컬럼 확장
해당 기능이 MariaDB 5.5 / MySQL 5.6 / MySQL 5.7.8 버전에서 각각 어떻게 실행되는지 살펴본다.
먼저 index rename 을 각 버전에서 실행한 결과이다.
-- ===================================================
-- MariaDB 5.5
-- ===================================================
: MariaDB 5.5 는 index rename 명령을 지원하지 않는다. 실행 시 에러가 발생하며,
인덱스명을 변경하려면 drop index, create index 명령으로 수행해야 한다.
mysql> alter table test_tbl rename index idx_test_tbl to idx_test_tbl_normal ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'index idx_test_tbl to idx_test_tbl_normal' at line 1
-- ===================================================
-- MySQL 5.6
-- ===================================================
: MySQL 5.6 은 index rename 명령을 지원하지 않는다. 실행 시 에러가 발생하며,
인덱스명을 변경하려면 drop index, create index 명령으로 수행해야 한다.
mysql> alter table test rename index idx_test to idx_test_rename ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index idx_test to idx_test_rename' at line
그러나 MySQL 5.6 에서는 인덱스 추가가 Online 으로 수행이 가능하기 때문에,
-- 1. 변경하고자 하는 인덱스 명으로 새로운 인덱스를 추가한다.
-- 2. old 인덱스를 drop 한다.
이와 같은 순서로도 Online 작업을 유도할 수 있지만, 동일 컬럼으로 인덱스를 중복하여 생성하게 되므로 ( old 인덱스를 drop 하기 전 까지 )
인덱스의 크기가 클 경우, 저장공간의 여유가 있어야 한다.
동일 컬럼으로 인덱스를 생성할 경우 다음과 같은 warning 이 발생한다.
| Note | 1831 | Duplicate index 'idx_xxx' defined on the table 'test.test_tbl'. This is deprecated and will be disallowed in a future release. |
-- ===================================================
-- MySQL 5.7.8-rc
-- ===================================================
MySQL 5.7.8 버전에서는 rename index 명령이 지원되고, INPLACE 방식으로 동작함 ( table copy 하지 않음 )
mysql> select @@version ;
+--------------+
| @@version |
+--------------+
| 5.7.8-rc-log |
+--------------+
mysql> show create table test_tbl \G
*************************** 1. row ***************************
Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`val` varchar(60) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_test_tbl` (`id`,`val`)
) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
-- idx_test_tbl 인덱스를 idx_test_tbl_normal 로 변경한다.
mysql> alter table test_tbl rename index idx_test_tbl to idx_test_tbl_normal ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 0.01 초 만에 완료된 것을 확인할 수 있다.
mysql> show create table test_tbl \G
*************************** 1. row ***************************
Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`val` varchar(60) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_test_tbl_normal` (`id`,`val`)
) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
두 번째로, MySQL 5.7 버전에서 varchar 컬럼 길이 확장이 online 으로 가능한데 이 부부은 한가지 제약사항이 있다.
* INPLACE ( table copy 하지 않고 ) 로 varchar 타입 컬럼 길이를 확장 할 수 있는 경우
-- 1. VARCHAR 0 ~ 255 bytes 내에서 확장하는 경우
-- 2. VARCHAR 256 ~ bytes 내에서 확장하는 경우
만일 컬럼 크기가 255 byte 이내인 컬럼을 256 bytes 이상으로 변경하려고 하면 Table copy 로 동작한다. 즉 INPLACE 로 동작하지 않는다.
이 제약사항에 대한 내용은 아래에서 설명한다.
* 참고 : 다음 설명부터 컬럼의 "글자 수" 와 "bytes 수" 라는 표현을 주의한다.
MySQL 의 VARCHAR 타입은 괄호 안에 있는 값 - VARCHAR( N ) , N 이 글자 수 이다. byte 수 가 아니다.
예를 들어, character set 이 utf8 인 경우 한 글자의 max byte 가 3byte 로, VARCHAR(255) 는 255 * 3 = 764 byte 까지 저장 가능하다.
위 설명한 제약사항을 바탕으로 하여,
varchar 컬럼 크기를 255 byte 이내의 값으로 변경할 때 online 으로 수행되는지 테스트를 진행해본다.
테스트 MySQL 의 character set 은 utf8mb4 로 max length 가 4byte 이다. 255 byte / 4 = 63 자 이며,
varchar(20) 인 컬럼을 varchar(63) 으로 변경 해본다.
varchar(20) 은 20 * 4byte (utf8mb4) = 80 bytes
varchar(63) 은 63 * 4byte (utf8mb4) = 254 bytes
80 bytes 에서 254 bytes 로 변경하는 것 이므로, 0 ~ 255 byte 이내의 값으로 변경하는 것이다.
이 경우에는 Online 으로 동작한다.
Create Table: CREATE TABLE `test_tbl` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`val` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_second` (`id`,`val`)
) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4
mysql> alter table test_tbl change column val val varchar(60) not null ;
Query OK, 0 rows affected (4.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test_tbl \G
CREATE TABLE `test_tbl` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`val` varchar(60) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_test_tbl_normal` (`id`,`val`)
) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4
다른 세션에서 DDL 명령을 실행중인 세션 상태를 보면, altering table 로 표시되는 것을 볼 수 있다. ( online - Inplace 로 실행되는 것이다. )
mysql> show processlist ;
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------------+
| 37 | root | localhost | hong | Query | 3 | altering table | alter table test_tbl change column val val varchar(60) not null |
| 40 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------------+
실제로 online 으로 적용이 되는지, 아닌지 명령을 실행하기 전에 확인하고자 한다면
ALTER 구문에 ALGORITHM 옵션을 포함하여 실행하면 미리 확인할 수 있다.
참고로 MySQL 5.7 에서도 컬럼 축소를 할 경우엔 online 으로 수행이 불가능하다.
-- 실행 가능할 경우
alter table test_tbl change column val val varchar(60) not null , ALGORITHM=INPLACE ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 실행 불가능할 경우
alter table test_tbl change column val val varchar(20) not null , ALGORITHM=INPLACE ;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
위 테스트에서는 varchar 컬럼 크기 변경을 0 ~ 255 byte 범위 내에서 변경한 것이다.
다음은 0 ~ 255 byte 범위에서 변경이 아닌,
varchar(20) -> varchar(190) 으로 컬럼 사이즈를 변경해본다.
character set 은 utf8mb4 를 기준으로 한다.
varchar(20) : 20 * 4 = 80 byte
varchar(190) : 190 * 4 = 760 byte 로
실제로는 80 byte -> 760 byte 로 컬럼 크기를 변경하는 것이다.
-- 다음과 같이 ALGORITHM 옵션을 함께 실행하면 INPLACE ( Online ) 으로 실행 불가능 한 것을 확인할 수 있다.
mysql> alter table test_tbl change column val val varchar(190) NOT NULL , ALGORITHM=INPLACE ;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
-- 실제로 명령을 실행하면, thread status 가 copy to tmp table 임을 확인할 수 있다.
-- 이 DDL 구문이 실행중일 때 다른 스레드에서는 DML 명령 실행시, DDL 구문이 완료할 때 까지 대기하게 된다.
mysql> alter table test_tbl change column val val varchar(190) NOT NULL ;
Query OK, 2135808 rows affected (29.58 sec) <-- copy to tmp table 로 수행될 경우, rows affected 가 전체 테이블 row 로 나타난다.
Records: 2135808 Duplicates: 0 Warnings: 0
-- 다른 세션에서 DDL 구문을 실행중인 세션의 상태를 확인하면 copy to tmp table 로 나타난다.
mysql> show full processlist ;
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------------------+
| 28 | root | localhost | hong | Query | 2 | copy to tmp table | alter table test_tbl change column val val varchar(90) NOT NULL |
| 30 | root | localhost | hong | Query | 0 | starting | show full processlist |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------------------+
그러면 256 bytes 이상의 범위에서 컬럼 사이즈를 변경하면 어떻게 될까 ?
현재 varchar(190) 인 컬럼의 bytes 수는 760 이다. ( 190 * 4bytes = 760 bytes )
이 컬럼을 varchar(255) 로 변경해보자.
character set 은 utf8mb4 를 기준으로 한다.
varchar(190) : 190 * 4 = 760 bytes
varchar(255) : 255 * 4 = 1020 bytes
760 bytes 를 1020 bytes 로 변경한다.
-- ALGORITM 옵션으로 Online 동작 여부를 확인한다.
mysql> alter table test_tbl change column val val varchar(255) NOT NULL , ALGORITHM=INPLACE ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- Online으로 동작하는 것을 확인하고 실제로 명령을 실행해본다.
mysql> alter table test_tbl change column val val varchar(255) NOT NULL ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 0.00 초 내로 완료된 것을 확인할 수 있다.
mysql> show create table test_tbl \G
*************************** 1. row ***************************
Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`val` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
-- ===================================================
-- varchar 타입의 특성
-- ===================================================
varchar 컬럼 확장이 0 ~ 255 byte 이내 / 256 byte 이상 범위에서만 각각 in-place 로 동작하는 varchar 타입의 특성 때문이다.
varchar 는 가변길이 문자열을 저장하는 타입으로,
문자열 저장 시, 맨 앞의 1 또는 2 byte 를 컬럼에 저장된 문자열의 byte 길이를 나타낸다.
여기서 1 또는 2 bytes 에 문자열 byte 길이를 저장할 때는 2 진수로 저장한다.
1 byte = 8 bit 로 이진수 여덟자리를 저장할 수 있다.
위에서 설명한 바와 같이, VARCHAR 의 괄호 안의 값은 저장 가능한 문자열 갯수이다.
만일 varchar(255) 의 경우 255 자 문자열을 저장할 수 있다는 것을 의미한다. 저장 가능한 byte 수는 character set 마다 값이 다르다.
( ex. utf8 은 max length 가 3byte 이므로, 255 개 문자열 * 3byte = 756 bytes )
이 때, 255 자가 들어있다는 것을, varchar 저장 시 맨 앞의 1 byte 에 2진수로 표현하면
10진수 255 = 2진수 11111111 이다.
그런데, 256 자가 저장되어있다는 것을 표현하려면 2진수 여덟자리로는 표현이 불가능하다.
10진수 255 = 2진수 1 0000 0000
결과적으로 2진수로 256 개의 문자열이 저장되어있다는 것을 저장하려면 2byte 가 필요하다.
2진수 16자리로 표현해야 한다는 것이다. ( 0000 0001 0000 0000 )
그래서 varchar(255) 를 varchar(256) 으로 변경을 하려고 하면, 맨 앞의 문자열 길이를 저장하는 바이트 수를 1byte 에서 2byte 로 변경해야한다.
이러한 문제로 0 ~ 255 자 / 256 자 이상 각각의 범위 내에서는 online 으로 컬럼 사이즈 변경이 가능하지만
그렇지 않을 경우 맨 앞자리 바이트 수를 늘려줘야 하기 때문에 테이블을 재생성하여, 데이터가 저장된 블록을 재 할당시키는 방식으로 동작한다.
MySQL 5.7 의 online DDL new feature 는 varchar 타입에 한정된 것으로, 다른 타입을 변경하면 table copy 로 동작한다.
MySQL 버전이 올라가면서 Online 기능들도 추가되고 성능적인 부분도 개선되고 있지만,
서비스중에 DDL ( ALTER ) 구문을 실행할 때에는 항상 주의깊게 모니터링을 해야 한다는 점을 명심한다.
-- unsigned 로 변경해도 INPLACE 로 동작 안됨
-- session1
mysql> alter table test_tbl change column id id int(10) unsigned not null auto_increment ;
-- session2
show full processlist ;
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------------+
| 28 | root | localhost | hong | Query | 5 | copy to tmp table | alter table test_tbl change column id id int(10) unsigned not null auto_increment |
| 30 | root | localhost | hong | Query | 0 | starting | show full processlist |
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------------+
-- bigint 로 변경해도 INPLACE 로 동작 안됨
alter table test_tbl change column id id bigint(20) not null auto_increment ;
show full processlist ;
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------+
| 28 | root | localhost | hong | Query | 3 | copy to tmp table | alter table test_tbl change column id id bigint(20) not null auto_increment |
| 30 | root | localhost | hong | Query | 0 | starting | show full processlist |
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
