2023/03/28 ~ 2023/04/03

11.7.4.6 테이블 구조 복사

테이블 구조는 같지만 이름만 다른 테이블을 생성하는 경우
CREATE TABLE ... AS SELECT ... LIMIT 0 명령으로 테이블 생성은 가능하나 인덱스도 복사되진 않음
데이터는 복사하지 않고 테이블의 구조만 동일하게 복사하는 경우
- ex) CREATE TABLE temp_employees LIKE employees;
- employees 테이블에 존재하는 모든 칼럼과 인덱스가 같은 temp_employees 테이블 생성
데이터도 복사하는 경우
- ex) CREATE TABLE temp_employees LIKE employees; 실행 후 INSERT INTO temp_employees SELECT * FROM employees;
주의) MySQL 서버는 특정 테이블에 대해 트랜잭션 로그를 활성화/비활성화 하는 기능을 제공하지 않음 CREATE TABLE 명령 실행 후 INSERT SELECT 명령을 실행하는 것과 CREATE TABLE ... AS SELECT .. 명령을 실행하는 것은 성능적인 면에서 차이가 없고, 둘다 리두 로그를 기록한다.

11.7.4.7 테이블 삭제

레코드가 많지 않은 테이블을 삭제하는 작업은 서비스 도중이라고 해도 문제가 되지 않음
레코드가 많은 테이블은 상당히 부하가 큰 작업이므로 서비스 도중 수행하지 않는 것을 권장
DROP TABLE [ IF EXISTS ] table1;
참고) MySQL 서버의 데이터 파일이 리눅스의 ext4 / xfs 파일 시스템을 사용하지 않고 ext3 파일 시스템을 사용하는 경우 파일의 조각이 디스크의 이곳저곳에 분산되어 저장되므로 삭제 시 읽고 쓰기 작업을 많이 발생시킬 수 있음
InnoDB 스토리지 엔진의 어댑티브 해시 인덱스가 활성화 되어 있는 경우 테이블이 삭제되면 이 정보도 삭제해야 하므로 관련 테이블 삭제 시 부하가 커질 수 있음
- 어댑티브 해시 인덱스: InnoDB 버퍼 풀의 각 페이지가 가진 레코드에 대한 해시 인덱스 기능 제공 (4.2.12 절 참고)

11.7.5 칼럼 변경

11.7.5.1 칼럼 추가

MySQL 8.0 버전으로 업그레이드되면서 데이클 칼럼 추가 작업은 INPLACE 알고리즘을 사용하는 온라인 DDL로 처리 가능
- ALTER TABLE employees ADD COLUMN emp_telno VARCHAR(20), ALGORITHM=INSTANT;
제일 마지막 칼럼으로 추가하는 경우 INSTANT 알고리즘으로 즉시 추가됨
- ALTER TABLE employees ADD COLUMN emp_telno VARCHAR(20) AFTER emp_no, ALGORITHM=INPLACE, LOCK=NONE;

11.7.5.2 칼럼 삭제

칼럼 삭제 작업은 항상 테이블의 리빌드를 필요로 하므로 INSTANT 알고리즘을 사용할 수 없음
항상 INPLACE 알고리즘으로만 칼럼 삭제 가능
ALTER TABLE employees DROP [COLUMN] emp_telno, ALGORITHM=INPLACE, LOCK=NONE;
- COLUMN 키워드는 입력하지 않아도 무방

11.7.5.3 칼럼 이름 및 칼럼 타입 변경


-- 칼럼 이름 변경. 리빌드가 필요하지 않아 INSTNAT 알고리즘과 같이 빠르게 완료됨
-- 칼럼 to_date 를 end_date로 이름 변경
ALTER TABLE salaries CHANGE to_date end_date DATE NOT NULL,
    ALGORITHM=INPLACE, LOCK=NONE;

-- INT 칼럼을 VARCHAR 타입으로 변경. 칼럼 타입이 변경되는 경우 COPY 알고리즘이 필요하고 변경 중에 쓰기 작업은 불가
ALTER TABLE salaries MODIFY salary VARCHAR(20),
    ALGORITHM=COPY, LOCK=SHARED;

-- VARCHAR 타입의 길이 확장. 길이 변경되기 전 후 저장 공간의 차이에 따라 리빌드가 필요여부가 갈리게 됨
-- 예제의 경우 10에서 20으로 변경해도 칼럼 값이 최대 가질 수 있는 바이트 수의 변함이 없으므로 테이블 리빌드가 필요 없음
ALTER TABLE salaries MODIFY salary VARCHAR(20),
    ALGORITHM=INPLACE, LOCK=NONE;

-- VARCHAR 타입의 길이 축소. 완전히 다른 타입으로 변경되는 경우와 동일하게 COPY 알고리즘이 필요하고 변경 중 쓰기 작업 불가
ALTER TABLE salaries MODIFY salary VARCHAR(10),
    ALGORITHM=COPY, LOCK=SHARED;

11.7.4.3 테이블 구조 변경

ALTER TABLE 명령어 사용

테이블 자체에 대한 속성 변경은 주로 문자 집합이나 스토리지 엔진, 파티션 구조 등의 번겅

ALTER TABLE employees
  CONVERT TO CHARACTER SET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI,
  ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE employees ENGINE=InnoDB
ALGORITHM=INPLACE, LOCK=NONE;

테이블 스토리지 엔진을 변경하는 명령은 내부적인 테이블의 저장소를 변경하는 것이라서 항상 테이블의 모든 레코드를 복사하는 작업이 필요하다

ALTER TABLE 문장에 명시한 ENGINE이 기존과 동일하더라도 테이블의 데이터를 복사하는 작업이 실행되기 때문에 주의해야함

실제 테이블의 스토리지 엔진을 변경하는 목적으로도 사용되지만 테이블 데이터를 리빌드하는 목적으로도 사용

주로 레코드의 삭제가 자주 발생하는 테이블에서 데이터가 저장되지 않은 빈 공간(fragmentation)을 제거해 디스크 사용 공간을 줄이는 역할을 함

테이블이 사용하는 디스크 공간의 프래그멘테이션을 최소화하고, 테이블의 구조를 최적화하기 위한 OPTIMIZE TALBE이라는 명령이 있는데 내부적으로 ALTER TALBE ... ENGINE=InnoDB 명령과 동일한 작업을 수행함

11.7.4.4 테이블 명 변경

RENAME TABLE table1 TO table2;
RENAME TABLE db1.table1 TO db2.table2;

첫 번째 명령에서 단순히 테이블의 이름만 변경하는 작업은 메타 정보만 변경하기 때문에 빠르게 처리됨

두 번째 명령에서는 다른 DB로 테이블을 이동할 수 있는데, 메타 정보뿐만 아니라 테이블이 저장된 파일까지 다른 디렉터리로 이동해야한다

DB별로 별도 디렉터리가 할당됨

db1와 db2가 서로 다른 파티션에 만들어졌다고 가정하면 테이블을 db1에서 db2로 이동할 때

데이터 파일의 복사 작업이 필요하기 때문에 데이터 파일의 크기에 비례해서 시간이 소요될 것이다

일반적으로 유닉스나 윈도우에서 서로 다른 파티션으로 파일을 이동할 때

데이터 파일을 먼저 복사
복사 완료되면 원본 파티션의 파일을 삭제하는 형태로 처리

MySQL서버의 RENAME TABLE에서도 똑같이 작동한다

일정 주기로 테이블을 교체해야 할 때

RENAME TABLE batch TO batch_old
RENAME TABLE batch_new TO batch

다음과 같이 테이블을 교체할텐데 신규 테이블을 교체하는 동안 일시적으로 batch 테이블이 없어지는 시점이 발생해서 에러가 발생할 수 있다

이런 문제점을 막기 위해 RENAME 명령을 하나의 문장으로 묶어서 실행할 수 있다

RENAME TABLE batch TO batch_old
             batch_new TO batch;

여러 테이블의 RENAME 명령을 하나의 문장으로 묶으면

명시된 모든 테이블에 대해 잠금을 걸고 테이블의 이름 변경 작업을 실행한다

응용 프로그램 입장에서 batch 테이블을 조회하려고 하면 잠금이 걸려있기 때문에 대기하고 RENAME TABLE 명령이 완료되면 batch 테이블의 잠금이 해제되어 batch_new 였던 테이블을 읽게된다

잠깐의 잠금 대기가 발생할 뿐 에러가 발생하지는 않는다

11.7.4.5 테이블 상태 조회

MySQL의 모든 테이블은 다음과 같은 정보를 가진다

만들어진 시간
대략의 레코드 건수
데이터 파일의 크기 등

데이터 파일의 버전, 레코드 포맷 등과 같이 자주 사용되지는 않지만 중요한 정보도 가지고 있는데

SHOW TABLE STATUS ... 로 조회하면 된다

LIKE '패턴' 과 같이 특정 테이블의 상태만 조회하는 것도 가능

SHOW TABLE STATUS ... 으로 출력되는 내용에서 레코드 건수나 평균 크기는 MySQL 서버가 예측하고 있는 값이기에 테이블이 너무 작거나 크면 오차가 더 커질 수도 있다

다음과 같이 SELECT 쿼리를 이용해 조회할 수도 있다

SELECT * FROM information_schema.TABLES
WHERE TABLE_SCHEMA='employees' AND TABLE_NAM='employees' \G

infomation_schema DB는

MySQL 서버가 가진 스키마들에 대한 메타 정보를 가진 딕셔너리 테이블이 관리된다
- db 객체애 대한 메타 정보
- 테이블과 칼럼에 대한 간략한 통계 정보
- 전문 검색 디버깅을 위한 뷰(view)
- 압축 실행과 실패 횟수에 대한 집계
테이블들이 실제로 존재하는 테이블이 아니고 MySQL 서버 시작시 db와 테이블 등에 대한 다양한 메타 정보를 모아서 메모리에 모아두고 사용자가 참조할 수 있는 테이블이다

mojh7 / real-mysql-study