[Real MySQL] 7. 쿼리 작성 및 최적화 (2)

INSERT

AUTO_INCREMENT

MySQL에서는 순차적으로 증가하는 숫자 값을 가져오기 위해 AUTO_INCREMENT라는 기능을 제공한다.
AUTO_INCREMENT는 테이블의 칼럼에 부여하는 옵션 형태로 사용하므로 자동 증가 기능은 하나의 테이블에서만 순차적으로 증가하게 된다.

INSERT 쿼리에서 ID를 직접 지정하는 경우

CREATE TABLE sample_tbl (
  member_id INT NOT NULL AUTO_INCREMENT,
  member_name VARCHAR(30) NOT NULL,
  PRIMARY KEY (member_id)
) ENGINE=INNODB;

INSERT INTO sample_tbl (member_name) VALUES ('Georgi Fellona');
INSERT INTO sample_tbl (member_id, member_name) VALUES (5, 'Georgi Fellona');

첫 번째 쿼리는 primary key인 id를 지정하지 않았지만, AUTO_INCREMENT에 의해 자동으로 다음 id값이 부여된다.
두 번째 쿼리는 id를 직접 지정한 경우이다.

• 강제 저장한 값이 AUTO_INCREMENT의 현재 값보다 작을 때는 AUTO_INCREMENT의 현재 값이 변하지 않는다.
• 강제 저장한 값이 AUTO_INCREMENT의 현재 값보다 클 때는 현재 값에 관계 없이 지정한 값에 1을 더한 값이 AUTO_INCREMENT의 다음 값으로 변경된다.

AUTO_INCREMENT 잠금

여러 커넥션에서 AUTO_INCREMENT를 동시에 사용할 때는 동기화 처리가 필요하다.
이를 위해 MySQL에서는 AutoIncrement 잠금이라는 테이블 단위의 잠금을 사용한다.
테이블 단위의 잠금이란 특정 시점을 잘라서 볼 때 한 테이블의 AutoIncrement 잠금은 반드시 하나의 커넥션만 가질 수 있다는 것을 의미한다.

하지만 AutoIncrement 잠금은 AUTO_INCREMENT의 현재 값을 가져올 때만 잠금이 걸렸다가 즉시 해제되기 때문에 성능상 문제가 될 때는 거의 없다.

REPLACE

REPLACE는 INSERT와 UPDATE의 기능을 묶은 쿼리와 같은 기능을 한다.

REPLACE INTO employees VALUES (10001, 'Brandon', 'Lee');

REPLACE INTO employees 
SET emp_no=10001, first_name='Brandon', last_name='Lee';

REPLACE 문장에서 주의해야 할 점은 중복된 레코드에 대한 판정 기준이다.
프라이머리 키나 유니크 키가 있다면 해당 키 모두에 중복된 값이 있는지 체크하고, 있다면 중복되는 레코드를 전부 삭제하고 넣으려는 데이터를 삽입한다.

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

REPLACE와 흡사하지만 중복된 레코드를 삭제하지 않고 UPDATE한다는 것이 유일한 차이다.

INSERT INTO sampel_tbl (c1, c2) VALUES (1, 100) ON DUPLICATE KEY UPDATE c2=c2+100;

LOAD DATA (LOCAL) INFILE

SELECT INTO OUTFILE... 쿼리에 대응하는 적재 쿼리다.
CSV 파일 포맷의 데이터 파일을 읽어 MySQL 서버의 테이블로 적재한다.
데이터 파일에서 각 칼럼의 값을 읽어서 바로 저장하기 때문에 INSERT 문장으로 저장하는 것보다 20배 빠르다고 알려져 있다.

LOAD DATA INFILE의 성능 향상

LOAD DATA INFILE 명령으로 대량의 데이터를 적재할 때 다음 옵션들도 함께 사용하면 좋다.

• AUTO-COMMIT

AUTO-COMMIT이 활성화된 상태에서는 레코드 단위로 INSERT가 실행될 때마다 COMMIT을 실행한다.
이 작업은 매번 레코드 단위로 로그 파일의 디스크 동기화 작업을 발생시킨다.
AUTO-COMMIT 모드를 비활성화하면 레코드가 INSERT 될 때마다 디스크에 플러시하는 작업을 피할 수 있다.

SET autocommit = 0;
LOAD DATA ...
COMMIT;
SET autocommit = 1;

• UNIQUE INDEX

대량 적재 대상 테이블에 UNIQUE 인덱스가 있다면 매번 레코드 단위로 중복 체크가 발생한다.
unique_checks 설정을 변경해 중복 체크를 건너뛰도록 설정할 수 있다.
물론 적재되는 데이터의 중복이 없다는 것을 꼭 먼저 확인해야 한다.

SET unique_checks = 0;
LOAD DATA ...
SET unique_checks = 1;

• FOREIGN KEY

데이터를 적재하는 테이블에 FOREIGN KEY가 있다면 매번 레코드의 INSERT 시점마다 FOREIGN KEY 값이 존재하는지 여부를 확인해야 한다.
foreign_key_checks 설정을 변경하면 해당 무결성 체크를 수행하지 않고 바로 적재할 수 있다.

SET foreign_key_checks = 0;
LOAD DATA ...
SET foreign_key_checks = 1;

---

DDL

테이블

• SHOW CREATE TABLE : 테이블의 CREATE TABLE 문장을 표시해준다. 하지만 사용자가 최초 테이블 생성 시 실행한 내용을 그대로 보여주는 것은 아니고, MySQL 서버가 메타 정보(*.FRM 파일)를 읽어서 재작성하여 보여주는 것이다.
  • 특별한 수정 없이 바로 사용할 수 있는 CREATE TABLE 명령을 만들어주기 때문에 상당히 유용하다.

칼럼 변경

• 칼럼 추가 : ALTER TABLE sample_tbl ADD COLUMN col_3 VARCHAR(20) [AFTER col_2];
  • 뒤에 AFTER 명령을 붙이면 기존 칼럼 뒤에 새 칼럼을 추가한다.
• 칼럼 삭제 : ALTER TABLE sample_tbl DROP COLUMN col_3;

인덱스 변경

• 인덱스 추가 : ALTER TABLE sample_tbl ADD INDEX idx_1 (col_1);
  • 단순 INDEX라고만 표기하면 중복이 허용되는 일반 보조 인덱스를 생성한다.
  • INDEX 대신 PRIMARY KEY 를 사용하면 프라이머리 키를 생성하는 구문이다.
  • INDEX 대신 UNIQUE INDEX 를 사용하면 중복을 허용하지 않는 인덱스를 생성하는 구문이다.
  • 그 외에 MyISAM 스토리지 엔진에서 사용할 수 있는, 전문 검색 인덱스 FULLTEXT INDEX 나 공간 검색 인덱스 SPATIAL INDEX 도 있다.
• 인덱스 조회 : SHOW INDEX FROM 명령을 사용하면 테이블의 인덱스를 조회할 수 있다.
• 인덱스 삭제 : ALTER TABLE sample_tbl DROP INDEX idx_1;

프로세스 조회

SHOW PROCESSLIST 명령으로 각 클라이언트 사용자가 현재 어떤 쿼리를 사용하고 있는지를 확인할 수 있다.

프로세스 목록의 칼럼

각 칼럼의 의미는 다음과 같다.

• Id : MySQL 서버의 스레드 아이디로, 쿼리나 커넥션 강제 종료 시 사용한다.
• User : 클라이언트가 MySQL 서버에 접속할 때 인증에 사용한 사용자 계정
• Host : 클라이언트의 호스트명 혹은 IP 주소
• db : 클라이언트가 기본으로 사용하는 데이터베이스
• Command : 해당 스레드가 현재 어떤 작업을 처리하고 있는지 표시
• Time : Command 칼럼에 표시되는 작업이 얼마나 실행되고 있는지 표시
• State : Command가 해당 스레드의 대분류 작업 내용이라면, State는 소분류 작업 내용이다.
• Info : 해당 스레드가 실행 중인 쿼리 문장

Command 칼럼의 값이 Query이면서 Time이 상당히 큰 값을 가지고 있다면 쿼리가 장시간 동안 실행되고 있음을 의미한다.
특히 State를 주의 깊게 봐야하는데, Copying ... 이나 Sorting ... 으로 시작하는 값이 표시될 때는 유의해야 한다.

프로세스 강제 종료

KILL QUERY 4228;
KILL 4228;

첫 번째 명령은 ID가 4228인 스레드의 쿼리만 종료시키는 명령이고, 두 번째 명령은 스레드까지 강제 종료시키는 명령이다.

스레드를 강제 종료시키면 해당 스레드의 커넥션에서 처리하고 있던 트랜잭션이 정상적으로 종료되지 않을 수 있기 때문에, 먼저 KILL QUERY 명령으로 쿼리를 종료시킨 후에 상황을 모니터링하면서 스레드를 종료시키는 것이 좋다.

---

참고

위 내용은 이성욱님의 Real MySQL (위키북스) 에서 일부 내용을 간단하게 정리한 것입니다.
세부적인 내용은 책을 직접 구입하셔서 읽어보시는 것을 추천드립니다.