쿼리 성능과 최적화 (2/2)

INSERT

일반적으로 온라인 트랜잭션 서비스(OLTP)에서 INSERT 문장은 대부분 1건 또는 소량의 레코드를 INSERT하는 형태이므로 그다지 성능에 대해서 고려할 부분이 많지 않다. 오히려 많은 INSERT 문장이 동시에 실행되는 경우 INSERT 문장 자체보다는 테이블의 구조가 성능에 더 큰 영향을 미친다. 하지만 동시에 INSERT 성능과 SELECT 성능을 빠르게 만들 수 있는 구조는 없기 때문에 어느 정도 타협하면서 테이블 구조를 설계해야 한다.

고급 옵션

SELECT 문장만큼 다양하지는 않지만 INSERT 문장에도 사용할 수 있는 유용한 기능이 있다. 대표적으로 INSERT INGORE 옵션과 INSERT ... ON DUPLICATE KEY UPDATE 옵션을 살펴보자. 두 옵션 모두 유니크 인덱스나 프라이머리 키에 대해 중복 레코드를 어떻게 처리할지를 결정한다.

INSERT IGNORE

INSERT 문장의 IGNORE 옵션은 저장하는 레코드의 프라이머리 키나 유니크 인덱스 컬럼의 값이 이미 테이블에 존재하는 레코드와 중복되는 경우, 그리고 저장하는 레코드의 컬럼이 테이블의 컬럼과 호환되지 않는 경우 모두 무시하고 다음 레코드를 처리할 수 있게 해준다. 주로 IGNORE 옵션은 다음과 같이 여러 레코드를 하나의 INSERT 문장으로 처리하는 경우 유용하다.

INSERT IGNORE 옵션은 단순히 유니크 인덱스의 중복뿐만 아니라 데이터 타입이 일치하지 않아서 INSERT를 할 수 없는 경우에도, 위 예제처럼 INSERT를 하도록 만들 수 있다. 이 경우에는 NOT NULL 컬럼에 NULL이 입력되어 NULL 대신 숫자 컬럼의 기본 값이 0을 INSERT를 한 것이다.

실제 INSERT 문장은 성공했지만 4개의 경고 메시지가 있는 것을 확인할 수 있다. 프로그램 코드에서 중복을 무시하기 위해 INSERT IGNORE 옵션을 사용한다면 데이터 중복 이외의 에러가 발생할 여지가 없는지 면밀히 확인 후 적용하는 것이 좋다. 제대로 검증되지 않은 INSERT IGNORE 문장은 의도하지 않은 에러까지 모두 무시해버릴 수가 있다.

INSERT ... ON DUPLICATE KEY UPDATE

INSERT IGNORE 문장은 중복이나 에러 발생 건에 대해서는 모두 무시하겠지만 INSERT ... ON DUPLICATE KEY UPDATE 문장은 프라이머리 키나 유니크 인덱스의 중복이 발생하면 UPDATE 문장의 역할을 수행하게 해준다. MySQL 서버의 REPLACE 문장은 INSERT ... ON DUPLICATE KEY UPDATE 문장과 비슷한 역할을 하지만 내부적으로 REPLACE 문장은 DELETE과 INSERT의 조합으로 작동하고, INSERT ... ON DUPLICATE KEY UPDATE 문장은 중복된 레코드가 있다면 기존 레코드를 삭제하지 않고 기존 레코드의 컬럼을 UPDATE하는 방식으로 작동한다.

LOAD DATA 명령 주의 사항

일반적으로 RDMBS에서 데이터를 빠르게 적재할 수 있는 방법으로 LOAD DATA 명령이 자주 소개된다. MySQL 서버의 LOAD DATA 명령도 내부적으로 MySQL 엔진과 스토리지 엔진의 호출 횟수를 최소화하고 스토리지 엔진이 직접 데이터를 적재하기 때문에 일반적인 INSERT 명령과 비교했을 때 매우 빠르다고 할 수 있다. 하지만 MySQL 서버의 LOAD DATA 명령은 다음과 같은 단점이 있다.

단일 스레드로 실행
단일 트랜잭션으로 실행

LOAD DATA 명령으로 적재하는 데이터가 아주 많지 않다면 위 2가지 사항은 큰 문제가 되지 않지만 데이터가 매우 커서 실행 시간이 매우 길어진다면 다른 온라인 트랜잭션 쿼리들의 성능이 영향을 받을 수 있다. 우선 LOAD DATA 명령은 단일 스레드로 실행되기 때문에 적재해야 할 데이터 파일이 매우 크다면 시간이 매우 길어질 수 있고, 테이블에 여러 인덱스가 있다면 LOAD DATA 문장이 레코드를 INSERT하고 인덱스에도 키 값을 INSERT해야 한다. 그런데 테이블에 레코드가 INSERT되면 될수록 테이블과 인덱스 크기도 커지게 되고, 단일 스레드로 실행되기 때문에 시간이 지나면 지날수록 INSERT 속도는 현저히 떨어지게 된다.

또한, LOAD DATA 문장은 하나의 트랜잭션으로 처리되기 때문에 LOAD DATA 문장이 시작한 시점부터 언두로그(Undo Log)가 삭제되기 못하고 유지되어야 한다. 이는 언두 로그를 디스크로 기록해야 하는 부하를 만들기도 하고, 언두 로그가 많이 쌓이면 레코드를 읽는 쿼리들이 필요한 레코드를 찾는 데 더 많은 오버헤드를 만들어 내기도 한다.

성능을 위한 테이블 구조

대부분 INSERT 문장은 단일 레코드를 저장하는 형태로 많이 사용되기 때문에 INSERT 문장 자체는 튜닝할 수 있는 부분이 별로 없는 편이다. 따라서 INSERT 문장의 성능은 쿼리 문장 자체보다는 테이블의 구조에 의해 많이 결정된다.

대량 INSERT 성능

하나의 INSERT 문장으로 수백 건, 수천 건의 레코드를 INSERT한다면 INSERT될 레코드들을 프라이머리 키 값 기준으로 미리 정렬해서 INSERT 문장을 구성하는 것이 성능에 도움이 될 수 있다. 만약 INSERT하는 데이터가 정렬되어 있지 않으면 InnoDB 스토리지 엔진은 레코드를 INSERT할 때마다 프라이머리 키의 B-Tree에서 이곳저곳 랜덤한 위치의 페이지를 메모리로 읽어와 레코드가 저장될 위치를 찾아야 하기 때문에 처리가 느리게 된다.

반면 프라이머리 키로 정렬된 데이터 파일을 적재할 때는 INSERT할 레코드의 프라이머리 키 값이 직전에 INSERT된 값보다 항상 크기 때문에 메모리에는 프라이머리 키의 마지막 페이지만 적재되어 있으면 새로운 페이지를 메모리로 가져오지 않아도 레코드를 저장할 위치를 찾을 수 있다.

프라이머리 키 선정

위에서 살펴봤듯이 테이블의 프라이머리 키는 INSERT 성능을 결정하는 가장 중요한 부분이다. 테이블에 INSERT되는 레코드가 프라이머리 키의 순서와 무관하게 아주 랜덤하게 저장된다면 MySQL 서버는 레코드를 INSERT할 때마다 저장될 위치를 찾아야 한다.

InnoDB 스토리지 엔진을 사용하는 테이블의 프라이머리 키는 클러스터링 키인데, 이는 세컨더리 인덱스를 이용하는 쿼리보다 프라이머리 키를 이용하는 쿼리의 성능이 훨씬 빨라지는 효과를 낸다. 그래서 프라이머리 키는 단순히 INSERT 성능만을 위해 설계해서는 안 된다. 대부분 온라인 트랜잭션 처리를 위한 테이블들은 쓰기(INSERT, UPDATE, DELTE)보다는 읽기(SELECT) 쿼리의 비율이 압도적으로 높다.

Auto-Increment 컬럼

INSERT에 최적화된 테이블을 생성하기 위해서는 다음 두 가지 요소를 갖춰 테이블을 준비하면 된다.

단조 증가 또는 단조 감소되는 값으로 프라이머리 키 선정
세컨더리 인덱스 최소화

AUTO_INCREMENT를 통해 자동 증가 값을 프라이머리 키로 해서 테이블을 생성하는 것은 MySQL 서버에서 가장 빠른 INSERT를 보장하는 방법이다. MySQL 서버에서는 자동 증가 값의 채번을 위해서는 잠금이 필요한데, 이를 AUTO-INC 잠금이라고 한다. 그리고 이 잠금을 사용하는 방식을 변경할 수 있게 innodb_autoinc_lock_mode라는 시스템 변수를 제공한다. 이 설정은 InnoDB 이외의 스토리지 엔진을 사용하는 테이블에는 영향을 미치지 않는다.

innodb_autoinc_lock_mode=0: 항상 AUTO-INC 잠금을 걸고 한 번에 1씩만 증가된 값을 가져온다. 이는 MySQL 5.1 버전의 자동 증가 값 채번 방식인데, 단순히 이전 버전과의 호환성 및 성능 비교 테스트 용도로만 사용하기 위해 남겨둔 것이다. 서비스용 MySQL 서버에서는 이 방식을 사용할 필요가 없다.
innodb_autoinc_lock_mode=1 (Consecutive mode): 단순히 레코드 한 건씩 INSERT하는 쿼리에서는 AUTO-INC 잠금을 사용하지 않고 뮤텍스를 이용해 더 가볍고 빠르게 처리한다. 하지만 하나의 INSERT 문장으로 여러 레코드를 INSERT하거나 LOAD DATA 명령으로 INSERT하는 쿼리에서는 AUTO-INC 잠금을 걸고 필요한 만큼의 자동 증가 값을 한꺼번에 가져와서 사용한다.
innodb_autoinc_lock_mode=2 (Interleaved mode): LOAD DATA나 벌크 INSERT를 포함한 INSERT 계열의 문장을 실행할 때 더 이상 AUTO-INC 잠금을 사용하지 않는다. 이때 자동 증가 값을 적당히 미리 할당받아서 처리할 수 있으므로 가장 빠른 방식이다. 이 모드에서 채번된 번호는 단조 증가하는 유니크한 번호까지만 보장하며, INSERT 순서와 채번된 번호의 연속성은 보장하지 않는다. 그래서 쿼리 기반의 복제를 사용하는 MySQL에서는 소스 서버와 레플리카 서버의 자동 증가 값이 동기화되지 못할 수도 있으므로 주의해야 한다.

MySQL 8.0 버전부터 복제의 바이너리 로그 포맷 기본값이 STATEMENT에서 ROW로 변경됐기 때문에 MySQL 8.0 버전부터는 기본값이 2이다. MySQL 서버의 버전과 관계없이, 복제를 STATEMENT 바이너리 로그 포맷으로 사용 중이라면 innodb_autoinc_lock_mode 또한 1로 설정해야 한다.

UPDATE와 DELETE

MySQL 서버에서는 여러 테이블을 조인해서 한 개 이상 테이블의 레코드를 변경한다거나 삭제하는 기능도 제공한다. 특히 잘못된 데이터를 보정하거나 일괄로 많은 레코드를 변경 및 삭제하는 경우에 JOIN UPDATE와 JOIN DELETE 구문은 매우 유용하다.

UPDATE ... ORDER BY ... LIMIT n

DELETE FROM employees ORDER BY last_name LIMIT 10;

UPDATE와 DELETE는 WHERE 조건절에 일치하는 모든 레코드를 업데이트하는 것이 일반적인 처리 방식이다. 하지만 MySQL에서는 UPDATE나 DELETE 문장에 ORDER BY 절과 LIMIT 절을 동시에 사용해 특정 컬럼으로 정렬해서 상위 몇 건만 변경 및 삭제하는 것도 가능하다. 한 번에 너무 많은 레코드를 변경 및 삭제하는 작업은 MySQL 서버에 과부화를 유발하거나 다른 커넥션의 쿼리 처리를 방해할 수 있기 때문에 LIMIT을 이용해 조금씩 잘라서 변경하거나 삭제하는 방식을 손쉽게 구현할 수 있다.

JOIN UPDATE

두 개 이상의 테이블을 조인해 조인된 결과 레코드를 변경 및 삭제하는 쿼리를 JOIN UPDATE라고 한다. 조인된 테이블 중에서 특정 테이블의 컬럼값을 다른 테이블의 컬럼에 업데이트해야 할 때 주로 조인 업데이트를 한다. 또는 꼭 다른 테이블의 컬럼값을 참조하지 않더라도 조인되는 양쪽 테이블에 공통으로 존재하는 레코드만 찾아서 업데이트하는 용도로도 사용할 수 있다.

일반적으로 JOIN UPDATE는 조인되는 모든 테이블에 대해 읽기 참조만 되는 테이블은 읽기 잠금이 걸리고, 컬럼이 변경되는 테이블은 쓰기 잠금이 걸린다. 그래서 JOIN UPDATE 문장이 OLTP 환경에서는 데드락을 유발할 가능성이 높으므로 너무 빈번하게 사용하는 것은 피하는 것이 좋다.

-- 에러
UPDATE departments d, dept_emp de
   SET d.emp_count = COUNT(*)
WHERE de.dept_no = d.dept_no
GROUP BY de.dept_no;
 
-- 해결
UPDATE departments d,
 	   (SELECT de.dept_no, COUNT(*) AS emp_count
        FROM dept_emp de
        GROUP BY de.dept_no) dc
   SET d.emp_count = dc.emp_count
WHERE dc.dept_no = d.dept_no;

위의 GROUP BY를 포함한 JOIN UPDATE는 dept_emp 테이블에서 부서별로 사원의 수를 departments 테이블의 emp_count 컬럼에 업데이트하기 위해 만든 쿼리다. dept_emp 테이블에서 부서별로 사원의 수를 가져오기 위해 GROUP BY가 사용됐는데 첫 번째 쿼리는 JOIN UPDATE 문장에서는 GROUP BY나 ORDER BY 절을 사용할 수 없기 때문에 에러가 발생한다. 따라서 두 번째 쿼리처럼 문법적으로 지원하지 않는 SQL에 대해 서브쿼리를 이용한 파생 테이블을 사용하면 JOIN UPDATE할 수 있다.

JOIN DELETE

JOIN DELETE 문장을 사용하려면 단일 테이블의 DELETE 문장과는 조금 다른 문법으로 쿼리를 작성해야 한다.

DELETE e
FROM employees e, dept_emp de, departments d
WHERE e.emp_no = de.emp_no AND de.dept_no = d.dept_no AND d.dept_no = 'd001';

위 예제는 employees와 dept_emp, departments라는 3개의 테이블을 조인한 다음, 조인이 성공한 레코드에 대해 employees 테이블의 레코드만 삭제하는 쿼리다. 일반적으로 하나의 테이블에서 레코드를 삭제할 때는 "DELETE FROM table ... "과 같은 문법으로 사용하지만 JOIN DELETE 문장에서는 DELETE와 FROM 절 사이에 삭제할 테이블을 명시해야 한다.

DELETE e, de
FROM departments d
	STRAIGHT_JOIN dept_emp de ON de.dept_no = d.dept_no
    STRAIGHT_JOIN employees e ON e.emp_no = de.emp_no
WHERE d.dept_no = 'd001';

e, de처럼 여러 테이블에서 동시에 레코드를 삭제할 수 있으며, JOIN DELETE 또한 JOIN UPDATE와 마찬가지로 SELECT 쿼리로 변환해서 실행 계획을 확인해볼 수 있다. 옵티마이저가 적절한 조인 순서를 결정하지 못한다면 위 예제처럼 STRAIGHT_JOIN 키워드나 JOIN_ORDER 옵티마이저 힌트를 이용해 조인의 순서를 옵티아미저에게 지시할 수 있다.

스키마 조작(DDL)

DBMS 서버의 모든 오브젝트를 생성하거나 변경하는 쿼리를 DDL(Data Definition Language)이라고 한다. 스토어드 프로시저나 함수, DB나 테이블 등을 생성하거나 변경하는 대부분의 명령이 DDL에 해당한다. MySQL 서버가 업그레이드되면서 많은 DDL이 온라인 모드로 처리될 수 있게 개선됐지만 여전히 스키마를 변경하는 작업 중에는 상당히 오랜 시간이 걸리고 MySQL 서버에 많은 부하를 발생시키는 작업들이 있으므로 주의해야 한다.

온라인 DDL

MySQL 5.5 이전 버전까지는 MySQL 서버에서 테이블의 구조를 변경하는 동안에는 다른 커넥션에 DML을 실행할 수 없었다. 하지만 MySQL 8.0 버전으로 업그레이드되면서 대부분의 스키마 변경 작업은 MySQL 서버에 내장된 온라인 DDL 기능으로 처리가 가능해졌다.

온라인 DDL 알고리즘

온라인 DDL은 스키마를 변경하는 작업 도중에도 다른 커넥션에서 해당 테이블의 데이터를 변경하거나 조회하는 작업을 가능하게 해준다. 온라인 DDL은 ALGORITHM과 LOCK 옵션을 이용해 어떤 모드로 스키마 변경을 실행할지를 결정할 수 있다. 온라인 DDL 기능은 테이블의 구조를 변경하거나 인덱스 추가와 같은 대부분의 작업에 대해 작동한다.

MySQL 서버에서는 old_alter_table 시스템 변수를 이용해 ALTER TABLE 명령이 온라인 DDL로 작동할지, 아니면 예전 방식(테이블의 읽고 쓰기를 막고 스키마를 변경하는 방식)으로 처리할지를 결정할 수 있다. MySQL 8.0 버전에서는 old_alter_table 시스템 변수의 기본값은 OFF로 설정되어 있기 때문에 자동으로 온라인 DDL이 활성화된다. ALTER TABLE 명령을 실행하면 MySQL 서버는 다음과 같은 순서로 스키마 변경에 적합한 알고리즘을 찾는다.

ALGORITHM=INSTANT로 스키마 변경이 가능한지 확인 후, 가능하다면 선택
ALGORITHM=INPLACE로 스키마 변경이 가능한지 확인 후, 가능하다면 선택
ALGORITHM=COPY 알고리즘 선택

스키마 변경 알고리즘의 우선순위가 낮을수록 MySQL 서버는 스키마 변경을 위해 더 큰 잠금과 많은 작업을 필요로 하고 서버의 부하도 많이 발생시킨다.

INSTANT: 테이블의 데이터는 전혀 변경하지 않고, 메타데이터만 변경하고 작업을 완료한다.스키마 변경 도중 테이블의 읽기 쓰기는 대기하게 되지만 스키마 변경 시간이 매우 짧기 때문에 다른 커넥션의 쿼리 처리에는 크게 영향을 미치지 않는다.
INPLACE: 임시 테이블로 데이터를 복사하지 않고 스키마 변경을 실행한다. 하지만 내부적으로 테이블의 리빌드를 실행할 수 있기 때문에 테이블의 크기에 따라 많은 시간이 소요될수 있으며 스키마 변경 중에도 테이블의 읽기와 쓰기 모두 가능하다.
COPY: 변경된 스키마를 적용한 임시 테이블을 생성하고, 테이블의 레코드를 모두 임시 테이블로 복사한 후 최종적으로 임시 테이블을 RENAME해서 스키마 변경을 완료한다. 이 방법은 테이블 읽기만 가능하고 DML(INSERT, UPDATE, DELETE)은 실행할 수 없다.

온라인 DDL 명령은 아래와 같이 알고리즘과 함께 잠금 수준도 함께 명시할 수 있다. ALGORITHM과 LOCK 옵션이 명시되지 않으면 MySQL 서버가 적절한 수준의 알고리즘과 잠금 수준을 선택하게 된다.

ALTER TABLE salaries CHANGE to_date end_date DATE NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

온라인 DDL에서 INSTANT 알고리즘은 테이블의 메타데이터만 변경하기 때문에 매우 짧은 시간 동안의 메타데이터 잠금만 필요로 한다. 그래서 INSTANT 알고리즘을 사용하는 경우에는 LOCK 옵션은 명시할 수 없다. INPLACE나 COPY 알고리즘을 사용하는 경우 LOCK은 다음 3가지 중 하나를 명시할 수 있다.

NONE: 아무런 잠금을 걸지 않음
SHARED: 읽기 잠금을 걸고 스키마 변경을 실행하기 떄문에 스키마 변경 중 읽기는 가능하지만 쓰기(INSERT, UPDATE, DELETE)는 불가함
EXCLUSIVE: 쓰기 잠금을 걸고 스키마 변경을 실행하기 때문에 테이블의 읽고 쓰기가 불가함

MySQL 서버에서 사용할 수 있는 스키마 변경 작업은 매우 다양하기 때문에 모든 명령이 온라인 DDL을 지원하는지 아닌지를 기억하기를 쉽지 않다. 이러한 경우에는 다음 예제와 같이 ALTER TABLE 문장에 LOCK과 ALGORITHM 절을 명시해서 온라인 스키마 변경의 처리 알고리즘을 강제할 수 있지만 무조건 그 알고리즘으로 처리되는 것은 아니다. 명시된 알고리즘으로 온라인 DDL이 처리되지 못한다면 단순히 에러만 발생시키고 실제 스키마 변경 작업은 시작되지 않기 때문에 의도하지 않은 잠금과 대기는 발생하지 않기 때문에 다음 순서로 ALGORITHM과 LOCK 옵션을 시도해보면서 해당 알고리즘이 지원되는지 여부를 판단한다.

ALGORITHM=INSTANT 옵션으로 스키마 변경을 시도
실패하면 ALGORITHM=INPLACE, LOCK=NONE 옵션으로 스키마 변경을 시도
실패하면 ALGORITHM=INPLACE, LOCK=SHAREd 옵션으로 스키마 변경을 시도
실패하면 ALGORITHM=COPY, LOCK=SHARED 옵션으로 스키마 변경을 시도
실패하면 ALGORITHM=COPY, LOCK=EXCLUSIVE 옵션으로 스키마 변경을 시도

INPLACE 알고리즘

INPLACE 알고리즘은 임시 테이블로 레코드를 복사하지는 않더라도 내부적으로 테이블의 모든 레코드를 리빌드해야 하는 경우가 많다. 이러한 경우 MySQL 서버는 다음과 같은 과정을 거친다.

INPLACE 스키마 변경이 지원되는 스토리지 엔진의 테이블인지 확인
INPLACE 스키마 변경 준비(스키마 변경에 대한 정보를 준비해서 온라인 DDL 작업 동안 변경되는 데이터를 추적할 준비). 4번 단계까지 잠깐의 배타적 잠금이 필요하다.
테이블 스키마 변경 및 새로운 DML 로깅(이 작업은 실제 스키마 변경을 수행하는 과정으로, 이 작업이 수행되는 동안은 다른 커넥션의 DML 작업이 대기하지 않는다. 이렇게 스키마를 온라인으로 변경함과 동시에 다른 스레드에서는 사용자에 의해서 발생한 DML들에 대해서 별도의 로그로 기록)
로그 적용(온라인 DDL 작업 동안 수집된 DML 로그를 테이블에 적용)
INPLACE 스키마 변경 완료(COMMIT)

데이터베이스 변경

MySQL에서 하나의 인스턴스는 1개 이상의 데이터베이스를 가질 수 있다. 다른 RDBMS에서는 스키마(Schema)와 데이터베이스를 구분해서 관리하지만 MySQL 서버에서는 스키마와 데이터베이스는 동격의 개념이다.

CREATE TABLE [IF NOT EXISTS] employees; -- 데이터베이스 생성
SHOW DATABASES; -- 데이터베이스 목록
USE employees; -- 데이터베이스 선택
ALTER DATABASE employees CHARACTER SET=euckr; -- 데이터베이스 속성 변경
DROP DATABASE [IF EXISTS] employees; -- 데이터베이스 삭제

테이블 변경

테이블은 사용자의 데이터를 가지는 주체로서, MySQL 서버의 많은 옵션과 인덱스 등의 기능이 테이블에 종속되어 사용된다.

테이블 생성

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb_test (
	member_id BIGINT [UNSIGNED] [AUTO_INCREMENT],
    nickname CHAR(20) [CHARACTER SET 'utf9'] [COLLATE 'utf8_general_ci'] [NOT NULL],
    home_url VARCHAR(200),
    member_point INT [NOT NULL] [DEFAULT 0]
    session_data BLOB,
    
    UNIQUE INDEX ux_nickname (nickname),
    INDEX ix_registereddttm (registered_dttm)
) ENGINE=INNODB;

TEMPORARY 키워드를 사용하면 해당 데이터베이스 커넥션(세션)에서만 사용 가능한 임시 테이블을 생성한다. 각 컬럼은 "컬럼명 + 컬럼타입 + [타입별 옵션] + [NULL 여부] + [기본값]"의 순서로 명시한다.

테이블 구조 조회

MySQL에서 테이블의 구조를 확인하는 방법은 SHOW CREATE TABLE 명령과 DESC 명령으로 두 가지가 있다. SHOW CREATE TABLE 명령은 컬럼의 목록과 인덱스, 외래키 정보를 동시에 보여주기 때문에 SQL을 튜닝하거나 테이블의 구조를 확인할 때 주로 이 명령을 사용한다.

DESC 명령은 DESCRIBE의 약어 형태의 명령으로 둘 모두 같은 결과를 보여준다. DESC 명령은 테이블의 컬럼 정보를 보기 편한 표 형태로 표시해준다. 하지만 인덱스 컬럼의 순서나 외래키, 테이블 자체의 속성을 보여주지 않기 때문에 테이블의 전체적인 구조를 한 번에 확인하기는 어렵다.

인덱스 변경

MySQL 8.0 버전에서는 대부분의 인덱스 변경 작업이 온라인 DDL로 처리 가능하도록 변경되었고, 인덱스의 종류별로 추가 및 변경, 삭제하는 방법을 살펴보자.

인덱스 추가

아래는 MySQL 서버에서 사용 가능한 인덱스의 종류나 인덱싱 알고리즘별로 대략 사용 가능한 ALTER TABLE ADD INDEX 문장의 형태를 나열했다.

ALTER TABLE employees ADD PRIMARY KEY (emp_no), ALGORITHM = INPLACE, LOCK = NONE;

ALTER TABLE employees ADD UNIQUE INDEX ux_empno (emp_no),
		ALGORITHM = INPLACE, LOCK = NONE;

ALTER TABLE employees ADD INDEX ix_lastname (last_name),
		ALGORITHM = INPLACE, LOCK = NONE;
        
ALTER TABLE employees ADD FULLTEXT INDEX fx_firstname_lastname (first_name, last_name),
		ALGORITHM = INPLACE, LOCK = SHARED;

ALTER TABLE employees ADD SPARIAL INDEX fx_loc (last_location),
		ALGORITHM = INPLACE, LOCK = SHARED;

전문 검색을 위한 인덱스와 공간 검색을 위한 인덱스는 INPLACE 알고리즘으로 인덱스 생성이 가능하지만 SHARED 잠금이 필요하다는 것을 알 수 있다. 그러나 나머지 B-Tree 자료 구조를 사용하는 인덱스의 추가는 프라이머리 키라고 하더라도 INPLACE 알고리즘에 잠금 없이 온라인으로 인덱스 생성이 가능한 것을 알 수 있다.

인덱스 조회

SHOW INDEX FROM employees;

인덱스 이름 변경

-- 인덱스 이름 변경
ALTER TABLE salaries RENAME INDEX ix_salary TO ix_salary2,
	ALGORITHM=INPLACE, LOCK=NONE;
    
-- 기존 인덱스(ix_firstname)를 삭제하고, 동시에 새로운 인덱스(index_new)의 이름을 ix_firstname으로 변경
ALTER TABLE employees
	DROP INDEX ix_firstname,
    RENAME INDEX index_new TO ix_firstname,
    ALGORITHM=INPLACE, LOCK=NONE;

인덱스 가시성 변경

ALTER TABLE employees ALTER INDEX ix_firstname VISIBLE/INVISIBLE;

MySQL 서버에서 인덱스를 삭제하는 작업은 ALTER TABLE DROP INDEX 명령으로 즉시 완료된다. 하지만 한 번 삭제된 인덱스를 새로 생성하는 것은 매우 많은 시간이 걸릴 수도 있기 떄문에 한 번 생성되면 거의 삭제하지 못하는 경우가 많다. 하지만 MySQL 8.0 버전부터는 인덱스의 가시성을 제어할 수 있는 기능이 도입되어 MySQL 서버가 쿼리를 실행할 때 해당 인덱스를 사용할 수 있게 할지 말지를 결정할 수 있다.

인덱스 삭제

ALTER TABLE employees DROP PRIMARY KEY, ALGORITHM=COPY, LOCK=SHARED;
ALTER TABLE employees DROP INDEX ux_empno, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE employees DROP INDEX fx_loc, ALGORITHM=INPLACE, LOCK=NONE;

MySQL 서버의 인덱스 삭제는 일반적으로 매우 빨리 처리된다. 세컨더리 인덱스 삭제 작업은 INPLACE 알고리즘을 사용하지만 실제 테이블 리빌드를 필요로 하지는 않는다. 하지만 프라이머리 키의 삭제 작업은 모든 세컨더리 인덱스의 리프 노드에 저장된 프라이머리 키 값을 삭제해야 하기 때문에 임시 테이블로 레코드를 복사해서 테이블을 재구축해야 한다.

테이블 변경 묶음 실행

ALTER TABLE employees
    ADD INDEX ix_lastname (last_name, first_name),
    ADD INDEX ix_birthdate (birth_date),
    ALGORITHM=INPLACE, LOCK=NONE;

2개의 ALTER TABLE 명령으로 인덱스를 각각 생성하면 인덱스를 생성할 때마다 테이블의 레코드를 풀스캔해서 인덱스를 생성하게 된다. 하지만 위처럼 하나의 ALTER TABLE 명령으로 모아서 실행하면 MySQL 서버는 테이블의 레코드를 한 번만 풀 스캔해서 2개의 인덱스를 한꺼번에 생성할 수 있게 된다. 따라서 2개의 인덱스를 한 번에 생성하면 인덱스 하나를 생성할 때보다는 더 많은 시간이 걸리겠지만 2개의 인덱스를 각각 ALTER TABLE 명령으로 생성하는 데 걸리는 시간보다는 훨씬 시간을 단축할 수 있다.

프로세스 조회 및 강제 종료

MySQL 서버에 접속된 사용자의 목록이나 각 클라이언트 사용자가 현재 어떤 쿼리를 실행하고 있는지는 SHOW PROCESSLIST 명령으로 확인할 수 있다. SHOW PROCESSLIST 명령의 결과에는 현재 MySQL 서버에 접속된 클라이언트의 요청을처리하는 스레드수만큼의 레코드가 표시된다. 각 컬럼에 포함된 값의 의미는 다음과 같다.

Id: MySQL 서버의 스레드 아이디이며, 쿼리나 커넥션을 강제 종료할 때는 이 컬럼(Id) 값을 식별자로 사용한다.
User: 클라이언트가 MySQL 서버에 접속할 때 인증에 사용한 사용자 계정을 의미한다.
Host: 클라이언트의 호스트명이나 IP 주소가 표시된다.
db: 클라이언트가 기본으로 사용하는 데이터베이스의 이름이 표시된다.
Command: 해당 스레드가 현재 어떤 작업을 처리하고 있는지 표시한다.
Time: Command 컬럼에 표시되는 작업이 얼마나 실행되고 있는지 표시한다. 단위는 초이다.
State: Command 컬럼에 표시되는 내용이 해당 스레드가 처리하고 있는 작업의 큰 분류를 보여준다면 State 컬럼에는 소분류 작업 내용을 보여준다.
Info: 해당 스레드가 실행 중인 쿼리 문장을 보여준다. 쿼리는 화면의 크기에 맞춰서 표시 가능한 부분까지만 표시된다. 쿼리의 모든 내용을 확인하려면 SHOW FULL PROCESSLIST 명령을 사용하면 된다.

출처

Real MySQL 8.0 2권: 개발자와 DBA를 위한 MySQL 실전 가이드

저작자표시

'교육 및 책 > Real MySQL' 카테고리의 다른 글

데이터 타입 (1)	2024.01.01
쿼리 작성과 최적화 (1/2) (0)	2023.12.13
실행 계획 (0)	2023.11.30
옵티마이저와 힌트 (2/2) (1)	2023.11.13
옵티마이저와 힌트 (1/2) (0)	2023.11.10

자바시작 워니

쿼리 성능과 최적화 (2/2)

INSERT

고급 옵션

INSERT IGNORE

INSERT ... ON DUPLICATE KEY UPDATE

LOAD DATA 명령 주의 사항

성능을 위한 테이블 구조

대량 INSERT 성능

프라이머리 키 선정

Auto-Increment 컬럼

UPDATE와 DELETE

UPDATE ... ORDER BY ... LIMIT n

JOIN UPDATE

JOIN DELETE

스키마 조작(DDL)

온라인 DDL

온라인 DDL 알고리즘

INPLACE 알고리즘

데이터베이스 변경

테이블 변경

테이블 생성

테이블 구조 조회

인덱스 변경

인덱스 추가

인덱스 조회

인덱스 이름 변경

인덱스 가시성 변경

인덱스 삭제

테이블 변경 묶음 실행

프로세스 조회 및 강제 종료

'교육 및 책 > Real MySQL' 카테고리의 다른 글

티스토리툴바

쿼리 성능과 최적화 (2/2)

INSERT

고급 옵션

INSERT IGNORE

INSERT ... ON DUPLICATE KEY UPDATE

LOAD DATA 명령 주의 사항

성능을 위한 테이블 구조

대량 INSERT 성능

프라이머리 키 선정

Auto-Increment 컬럼

UPDATE와 DELETE

UPDATE ... ORDER BY ... LIMIT n

JOIN UPDATE

JOIN DELETE

스키마 조작(DDL)

온라인 DDL

온라인 DDL 알고리즘

INPLACE 알고리즘

데이터베이스 변경

테이블 변경

테이블 생성

테이블 구조 조회

인덱스 변경

인덱스 추가

인덱스 조회

인덱스 이름 변경

인덱스 가시성 변경

인덱스 삭제

테이블 변경 묶음 실행

프로세스 조회 및 강제 종료

'교육 및 책 > Real MySQL' 카테고리의 다른 글

관련글

티스토리툴바