MySQL Online DDL(4) [Primary key Operations]

B-Tree Nonclustered Index와 다르게 정렬이 되고 데이터 전체를 저장하는 Primary key 는

Index들과 다른 자동을 하는데 보통 Table Rebuild 해야 되는 상황이 발생되기 때문이다.

 

Primary key 추가

InPlace 알고리즘을 사용할 수 있으나 Table Rebuild가 무조건 일어난다.

다음과 같은 순서로 작업이 이루어 진다.

1.      기존 Table의 data를 원하는 Index구조의 Temporary Table로 복사하여 구축

2.      원본 테이블의 이름을 다른 Temporary Table 이름으로 변경

3.      생성 되었던 새 Primary key가 구축된 Temporary Table을 원본 테이블의 이름으로 변경

4.      원본 Table을 Database에서 제거

 

기본적으로 위의 순서로 작업되나 몇 가지 이슈가 또한 있다.

1.      InPlace 알고리즘을 사용하지 못하는 상황이 있다.
ex > 해당 열의 NULL 조건을 NOT NULL로 변경해야 하는 경우

2.      Copy 알고리즘을 사용하는 경우 기존의 Data에 null 존재할 시 다음과 같은 기본값이 자동으로 추가됨
(해당은 오라클 비권장 비표준 작업)

A.     숫자형 : 0

B.      문자열형 : ‘’

C.      날짜형 : 0000-00-00 00:00:00

3.      SQL_MODE가 strict_trans_tables 또는 strict_all_tables 일 시 InPlace 알고리즘 사용 시에도 같은 기본값 작업 발생된다.

4.      새 Temporary Table로 복사하는 중 서버가 종료되면 데이터가 손실되진 않지만 Temporary Table을 복구하는 프로세스를 진행하는 고비용 작업을 완료해야 된다.

 

Copy와 InPlace 알고리즘이 큰 차이가 없음에도 InPlace를 사용하면 좀 더 효율적인 이유는 다음과 같다,.

1.      RedoLog와 UndoLog의 Loging 이 일어나지 않음에 따라 Log File write의 overhead를 줄일 수 있다.

2.      Secondary Index가 있을 시 항목이 미리 정렬되어 있으므로 순서대로 저장이 쉽다.

3.      Secondary Index에 Random Access가 발생되지 않으므로 alter buffer 사용을 없다.

 

다음과 같은 Syntax로 작업이 가능하다.

show indexes from alter_test_tbl2;

Empty set (0.00 sec)

 

ALTER TABLE alter_test_tbl2 ADD PRIMARY KEY (a), ALGORITHM=INPLACE, LOCK=NONE;

 

show indexes from alter_test_tbl2;

+-----------------+------------+----------+-------------+------------+

| Table           | Non_unique | Key_name | Column_name | Index_type |

+-----------------+------------+----------+-------------+------------+

| alter_test_tbl2 |          0 | PRIMARY  | a           | BTREE      |

+-----------------+------------+----------+-------------+------------+

 

Primary Key 제거

Copy 알고리즘만 사용이 가능 Primary Key가 없는 새로운 Temporary Table을 생성하여 작업하는 것이다.

show indexes from alter_test_tbl2;

+-----------------+------------+----------+-------------+------------+

| Table           | Non_unique | Key_name | Column_name | Index_type |

+-----------------+------------+----------+-------------+------------+

| alter_test_tbl2 |          0 | PRIMARY  | a           | BTREE      |

+-----------------+------------+----------+-------------+------------+

 

ALTER TABLE alter_test_tbl2 DROP PRIMARY KEY, ALGORITHM=COPY, LOCK=SHARED;

 

show indexes from alter_test_tbl2;

Empty set (0.00 sec)

 

Primary Key 다른 Column으로 변경

InPlace 알고리즘 사용이 가능하고 비용은 결국 rebuild 이기 때문에 고비용 발생

show indexes from alter_test_tbl2;

+-----------------+------------+----------+-------------+------------+

| Table           | Non_unique | Key_name | Column_name | Index_type |

+-----------------+------------+----------+-------------+------------+

| alter_test_tbl2 |          0 | PRIMARY  | a           | BTREE      |

+-----------------+------------+----------+-------------+------------+

 

ALTER TABLE alter_test_tbl2 DROP PRIMARY KEY, 

ADD PRIMARY KEY (b), ALGORITHM=INPLACE, LOCK=NONE;

 

+-----------------+------------+----------+-------------+------------+

| Table           | Non_unique | Key_name | Column_name | Index_type |

+-----------------+------------+----------+-------------+------------+

| alter_test_tbl2 |          0 | PRIMARY  | b           | BTREE      |

+-----------------+------------+----------+-------------+------------+

 

참조

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations