스키마조작(DDL)
DDL(Data Definition Language) = DBMS 서버의 모든 오브젝트를 생성하거나 변경하는 쿼리
ex) 스토어드 프로시저나 함수, DB, 테이블 등을 생성하거나 변경하는 대부분의 명령
스키마를 변경하는 작업중에는 상당히 로랜시간이 걸리고 MySQL 서버에 많은 부하가 발생하기 때문에 주의 해야함
온라인 DDL
MySQL 5.5 이전 버전은 테이블의 구조가 변경되는 동안 커넥션 연결이 어려웠지만, 온라인 DDL 기능으로 스키마 변경을 하면서 커넥션 연결이 가능해짐
ALGORITHM, LOCK 옵션을 통해 어떤 모드로 스키마를 변경할 지 선택 가능
스키마 변경 알고리즘의 우선순위가 낮을수록 MySQL 서버는 스키마 변경을 위해서 더큰 잠금과 많은 작업을 필요로 하고, 부하가 발생함
INSTANT : 테이블의 데이터는 전혀 변경하지 않고, 메타 데이터만 변경하고 작업을 완료함
테이블이 가진 데이터 양과 무관하게 짧은 시간 소요
스키마 변경 도중 읽고 쓰기는 대기(시간이 짧아서 괜찮음)
LOCK 옵션 명시 할 수 없음
INPLACE : 임시테이블로 데이터를 복사하지 않고 스키마 변경을 수행함, 다만, 내부적으로 테이블 리빌드를 실행함
레코드 복사 작업은 없지만, 리빌드를 해야하기 때문에 테이블의 크기에 따라 시간 소요가 달라짐
스키마 변경 중에도 읽고 쓰기가 가능함
COPY : 변경된 스키말르 적용한 임시테이블이 생성되고, 테이블의 레코드를 모두 임시 테이블로 복사 후 최종적으로 임시 테이블을 RENAME해서 스키마 변경을 완료함
읽기만 가능함
ALGORITHM, LOCK 옵션이 명시되지 않으면, 서버가 적정한 수준의 알고리즘과 잠금 수준을 선택함
INPLACE, COPY 는 3가지 LOCK 옵션을 명시 함
ALTER TABLE 문장에 ALGORITHM, LOCK 절을 명시해서 온라인 스키마 변경의 처리 알고리즘을 강제 할 수 있음
온라인 DDL의 실패 케이스
- ALTER TABLE 명령이 장시간 실행되고, 동시에 다른 커넥션에서 DML이 많이 실행되는 경우, 온라인 변경 로그의 공간이 부족한 경우
- ALTER TABLE 명령이 실행되는 동안 ALTER TABLE 이전의 테이블 구조에서는 문제가 없었지만, 이후 테이블 구조에서 적합하지 않은 레코드가 INSERT, UPDATE 될 경우
- 스키마 변경을 위해서 필요한 잠금 수준보다 낮은 잠금 옵션이 사용된 경우
- 온라인 스키마 변경은 LOCT=NONE으로 실행된더라도 변경 작업의 처음과 마지막 과정에서 잠금이 필요한데, 잠금을 획득하지 못하고 타임아웃이 난 경우
- 디스크의 임시 디렉터리를 사용 할 때 공간이 부족한 경우
온라인 DDL 진행상황 모니터링
performance_schema 를 통해 진행상황을 모니터링 가능
데이터베이스 변경
MySQL에서 하나의 인스턴스는 1개 이상의 데이터베이스를 가질 수 있음
RDBMS는 스키마, 데이터베이스를 분리해서 관리
MySQL은 스키마, 데이터베이스가 동격(굳이 스키마를 명시하지 않음)
#데이터베이스 생성
CREATE DATABASE employees;
#데이터베이스 목록
SHOW DATABASE;
#데이터베이스 선택
USE employees;
#데이터베이스 삭제
DROP DATAVASE employees;
테이블 스페이스 변경
MySQL 서버는 테이블 별로 전용의 테이블스페이스를 사용했음
InnoDB 스토리지 엔진 시스템 테이블 스페이스만 제너럴 테이블 스페이스를 사용
제너럴 테이블스페이스는 여러테이블의 데이터를 한꺼번에 저장하는 테이블스페이스를 의미
8.0부터 사용자 테이블을 제너럴 테이블스페이스로 저장하는 기능이 추가됨
- 제너럴 테이블스페이스를 사용하면 파일 핸들러를 최소화
- 테이블 스페이스 관리에 필요한 메모리 공간을 최소화
제너럴 테이블스페이스는 테이블의 갯수가 많은 경우 유용함
테이블 생성
각 컬럼은 칼럼명 + 칼럼타입 + [타입별옵션] + [NULL 여부] + [기본값] 순서로 명시
테이블 구조 조회
SHOW CREATE TABLE, DESC
SHOW CREATE TABLE 사용히 테이블의 CREATE TABLE 문장을 표시(최초 명령X, 메타데이터 읽어서 재 작성)
칼럼의 목록과 인덱스, 외래키 정보를 동시에 보여주기 때문에 SQL을 튜닝하거나 테이블의 구조를 확인 할때 사용
DESC
DESCRIBE 약어 테이블의 칼럼 정보를 표의 형태로 보여줌
인덱스 칼럼의 순서나 외래키, 테이블 자체의 속성은 보여주지 않음
테이블 구조 변경
ALTER TABLE 사용
테이블 자체의 속성을 변경, 인덱스, 칼럼 추가 삭제 용도로 사용
테이블 리빌드 작업은 주로 레코드의 삭제가 자주 발생하는 테이블에서 데이터가 저장되지 않는 빈공간을 제거해 디스크 사용 공간을 줄이는 작업
OPTIMIZE TABLE : 테이블이 사용하는 디스크 공간의 프래그멘테이션을 최소화 하고, 테이블의 구조를 최적화 함
테이블 명 변경
RENAME TABLE 사용
테이블 이름 변경 뿐만 아니라, 다른 데이터베이스로 테이블을 이동할 때도 사용
RENAME TABLE db1table1 TO db2.tabel2
테이블 상태 조회
SHOW TABLE STATUS employees
테이블 구조 복사
CREATE TABLE temp_employees LIKE employees
구조가 같은 테이블 복사하여 생성
INSERT INTO tempemployees SELECT * FROM employees
데이터까지 복사
테이블 삭제
DROP TABLE table
레코드가 적은 테이블 삭제는 서비스 도중 실행해도 괜찮음
다만, 양이 많은 테이블 삭제시 MySQL 서버에 부하가 증가하여 다른 커넥션 쿼리 속도에 영향을 줄 수 있음(서비스 도중엔 조심히 사용)