INSERT … ON DUPLICATE KEY UPDATE
- 일반적인 INSERT가 수행되나, PK나 UK에 중복이 발생하면 UPDATE문이 수행된다.
LODA DATA
- 데이터를 빠르게 적재하는 방법
- 단점
- 단일 스레드로 실행
- 데이터가 매우 크다면 시간이 매우 길어진다. 레코드 insert 및 인덱스 insert가 단일 쓰레드로 직렬 수행
- 단일 트랜잭션으로 실행
- 언두로그 사이즈가 커지며, 레코드를 읽는 쿼리들이 필요한 레코드를 찾는데 더 많은 오버헤드 발생
- 단일 스레드로 실행
- 해결방안
- LODA DATA로 적재할 파일을 여러개로 준비
- 테이블간 복사 작업이라면 INSERT .. SELECT 작업으로 진행, PK값을 기준으로 나눠서 진행
- LODA DATA로 적재할 파일을 여러개로 준비
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해서 update할 수 있다.
- 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 크기가 늘어나, 디스크 용량 및 조회시 오버헤드 발생
'MySQL' 카테고리의 다른 글
CH17 Innodb 클러스터 (0) | 2023.08.26 |
---|---|
Real MySQL 파티션 (0) | 2023.08.04 |
MySQL binlog format (0) | 2022.06.08 |
NULLABLE 컬럼에 대한 인덱스 사용 ( Oracle vs MySQL) (1) | 2022.05.10 |