Real MySQl CH11 쿼리 작성 및 최적화

INSERT … ON DUPLICATE KEY UPDATE

• 일반적인 INSERT가 수행되나, PK나 UK에 중복이 발생하면 UPDATE문이 수행된다.

LODA DATA

• 데이터를 빠르게 적재하는 방법
• 단점
  • 단일 스레드로 실행
    • 데이터가 매우 크다면 시간이 매우 길어진다. 레코드 insert 및 인덱스 insert가 단일 쓰레드로 직렬 수행
  • 단일 트랜잭션으로 실행
    • 언두로그 사이즈가 커지며, 레코드를 읽는 쿼리들이 필요한 레코드를 찾는데 더 많은 오버헤드 발생
• 해결방안
  • LODA DATA로 적재할 파일을 여러개로 준비
    • 테이블간 복사 작업이라면 INSERT .. SELECT 작업으로 진행, PK값을 기준으로 나눠서 진행

Q. 데이터를 빠르게 저장하는 다른 방법은 뭐가 있을까?

• 기존에 존재하는 테이블이라면 인덱스 및 FK를 비활성화 시킨 후 INSERT
• 신규 테이블이라면 데이터 삽입 이후 인덱스 생성

Q. OLTP 환경에서 PK는 insert / select 중 어느 작업을 위한 컬럼을 생성하는게 좋을까

• SELECT 작업을 위해, 대부분의 작업이 select이기 때문

AUTO- increment 컬럼

insert에 최적화된 테이블을 생성하기 위한 요소

• auto increment가 PK인 경우
• 세컨더리 인덱스 최소화

auto-inc 컬럼을 PK로 사용하면 클러스터링 되지 않은 테이블의 효과를 볼 수 있다.

UPDATE / DELETE

• JOIN UPDATE
  • 다른 테이블과 join해서 update할 수 있다.
    • group by 및 order by절 사용 불가능
• JOIN DELETE
  • JOIN DELETE / JOIN UPDATE 모두 select로 변환해서 실행계획을 확인할 수 있다.

스키마 조작(DDL)

Online DDL

• 개념
  • 스키마를 변경하는 작업 도중에도 다른 커넥션에서 해당 테이블의 데이터를 변경하거나 조회하는 작업을 가능하게 함
• 구현 방법
  • DDL이 수행되는 동안 발생한 DML을 별도의 로그로 저장, DDL완료된 이후 로그를 테이블에 반영
• 실패 case
  • DDL이 오래 수행되고 해당 기간에 DML이 많이 발생하여 DML 로그 공간이 부족한 경우
  • DDL 이후 테이블 구조에 적합하지 않은 레코드가 INSERT되거나 UPDATE된 경우
• Online DDL이 불가능한 작업
  • PK삭제 작업 / 공간 인덱스 생성 / 컬럼 데이터 타입 변경 / CHARACTER SET 변경 / 파티션 적용 등
• PostgreSQL Onilne DDL
  • Online DDL이 불가능한 작업
  • PK 추가,변경 작업 / FK 추가,삭제 작업 등
• 온라인 DDL 알고리즘
  • INSTANT : 테이블 데이터는 전혀 변경하지 않고 메타 데이터만 변경하는 것
  • INPLACE : 임시 테이블로 데이터를 복사하지 않고 스키마 변경 수행, 테이블 리빌드 발생시 레코드 복사 작업은 없지만 테이블의 모든 레코드를 리빌드해야 하기 때문에 테이블의 크기에 따라 오랜 시간 소요
  • COPY : 변경된 스키마를 적용한 임시 테이블 생성 및 테이블의 레코드를 모두 임시 테이블로 복사한 후 최종적으로 임시 테이블을 RENAME해서 스키마 변경 완료 시킴
• 테이블 rebuild 및 copy작업 발생시 테이블이 모두 메모리에 적재되게 되므로 다른 테이블/인덱스가 메모리에서 밀려나서 response time이 증가할 수 있음

데이터베이스

• 데이터베이스 생성 시 문자 집합이나 콜레이션을 변경한다
  • 콜레이션은 정렬시 사용되는 규칙이다. 따라서 콜레이션이 다른 경우 인덱스를 활용하게 되지 못할 수도 있다. ex) us collation을 사용할 경우 한글 like 검색 인덱스 미사용

테이블

• OPTIMIZE TABLE
  • 레코드의 삭제가 자주 일어나는 테이블은 빈 공간(fragmentation)이 생기게 되고 이를 해결하기 위해 optimize table 명령을 사용하게 된다. optimize table은 내부적으로 alter table … ENGINE=innDB명령과 동일한 작업을 수행한다. 결국 innoDB 테이블에서 테이블 최적화란 테이블의 레코드를 한 건씩 새로운 테이블에 복사하는 것
• RENAME TABLE
  • 위와같이 테이블을 교체하게 되면 교체되는 순간에 테이블이 존재하지 않는 기간이 생기므로 오류가 발생할 수 있다. 이같은 문제를 해결하기 위해 테이블 교체는 아래와 같이 수행한다

  RENAME TABLE batch TO batch_old,
  	batch_new TO batch

  • 테이블 rename시 해당 테이블을 참조하는 테이블의 fk도 rename된 테이블을 바라보도록 변경된다.따라서 rename을 통한 테이블 교체시 테이블을 참조하는 fk를 새로 생성해야 한다.
• RENAME TABLE batch TO batch_old RENAME TABLE batch_new TO batch

Q. rename table 사용시 해당 테이블을 참조하는 fk도 변경될까?

• 테이블 구조 복사
  • 위의 쿼리를 수행하면 모든 컬럼과 인덱스를 동일하게 생성한다.
• CREATE TABLE temp_test LIKE test;

Q. CREATE TABLE .. LIKE 구문 수행시 FK도 생성될까?

• NO

컬럼 변경

• 칼럼 추가
  • 맨 마지막에 컬럼을 추가하는 것은 INSTANT 처리가 가능하지만
  • BEFORE / AFTER 를 통해 위치를 지정해서 컬럼을 추가하는 것은 테이블 REBUILD가 필요하다.
• 칼럼 이름 및 컬럼 타입 변경
  • 타입을 변경하는 경우는 COPY작업 필요 (INT → CHAR)
  • VARCHAR 타입의 길이를 축소하는 경우는 COPY 알고리즘 필요
  • VARCHAR(64)이상으로 길이를 확장하는 경우 테이블 REBUILD 필요

인덱스

• 인덱스 가시성 변경
  • INVISIBLE 옵션을 통해서 MySQL서버가 쿼리를 수행할 때 해당 인덱스를 사용할 수 있게 할지 말지를 설정 가능
• sys.schema에서 unused index 조회 가능 → 사용하지 않는 인덱스 파악 가능

테이블 변경 묶음 실행

• 하나의 테이블에 대해서 여러가지 스키마 변경을 해야하는 경우 ALTER TABLE을 여러번 수행하지 않고, 한번에 묶어서 수행하는 것이 좋다.

ALTER TABLE test ADD INDEX idx_1;
ALTER TABLE test ADD INDEX idx_1;

• 위 구문으로 생성하면 각 인덱스를 생성할 때마다 테이블의 레코드를 풀스캔해서 인덱스를 생성하게 된다.

ALTER TABLE test ADD INDEX idx_1, ADD INDEX idx_2

• 위 구문으로 수행시 인덱스 하나를 생성할 때보단 오래 걸리겠지만, 테이블 풀스캔은 한번만 하게 된다.

활성 트랜잭션 조회

• 트랜잭션이 종료되지 않고 오랫동안 활성상태로 남아있는 경우 MySQL 성능에 악 영향을 미칠 수 있다.
• information_schama.innodb_trx를 통해 조회 가능

Q. 트랜잭션이 종료되지 않고 오랫동안 활성상태로 남아있는 경우 MySQL 성능에 악 영향을 미치는 이유는?

• mvcc undo log 크기가 늘어나, 디스크 용량 및 조회시 오버헤드 발생