[SQL 튜닝] 큰 규모의 데이터 변경으로 인덱스에 영향을 주는 나쁜 SQL 문

큰 규모의 데이터 변경으로 인덱스에 영향을 주는 나쁜 SQL 문

현황 분석

| 튜닝 전 수행 결과 |

사원출입기록 테이블의 출입문 열에 'B'로 저장된 데이터를 'X'로 변경하는 쿼리

MYSQL에서 DML(data manipulation language) 문을 수행할 때 커밋은 기본적으로 자동 저장이 됨

튜닝 전 SQL 문은 UPDATE 문이므로 별도의 설정을 하지 않으면 바로 자동 저장됨

따라서 반복되는 UPDATE 문을 수행할 때 자동 저장되는 커밋 설정을 본인이 접속한 세션에 한해서만 자동 저장되지 않도록 변경

SELECT @@autocommit; /* 1 */
SET autocommit=0;
SELECT @@autocommit /* 0 */

- autocommit이 1이면 자동 커밋, 0이면 자동 커밋이 아님

- SET autocommit = 0; 명령문을 통해 자동 커밋되지 않도록 설정

- UPDATE 쿼리문 수행 후 ROLLBACK 수행

 

| 튜닝 전 수행 결과 |

EXPLAIN 
UPDATE 사원출입기록
SET 출입문 = 'X'
WHERE 출입문 = 'B';

- SQL문 결과 : 총 30만 건의 데이터 변경, 10s 소요

- 기본 키(key 항목 : PRIMARY)로 사원출입기록 테이블에 접근한 뒤 출입문 = 'B' 조건절에 해당하는 데이터만 'X'라는 출입문으로 변경(select_type 항목 : update)

 

튜닝 수행

- UPDATE 문은 수정할 데이터에 접근한 뒤에 SET 절의 수정값으로 변경하므로, 인덱스로 데이터에 접근한다는 측면에서 인덱스의 존재 여부는 중요함

- 조회한 데이터를 변경하는 범위에는 테이블뿐만 아니라 인덱스도 포함되므로, 인덱스가 많은 테이블의 데이터를 변경할 때는 성능적으로 불리함

SHOW INDEX FROM 사원출입기록;

- 4개 인덱스 중 SET 절에 변경될 열을 포함하는 인덱스 무엇인지 확인 → SET 출입문 = 'X', I_출입문 인덱스의 튜닝 여부 고민

- 사원출입기록 테이블과 I_출입문 인덱스의 데이터에 매번 UPDATE 문을 수행하느라 수십초가 소요

- 이때 사원출입기록 테이블과 같은 이력용 테이블에서는 보통 지속적인 데이터 저장만 이루어지므로, I_출입문 인덱스의 활용도가 없다면 삭제하여 튜닝 진행

- 만약 업데이트 작업이 새벽 또는 서비스에 미치는 영향이 적은 시간대에 수행되는 배치성 작업이라면, 인덱스를 일시적으로 삭제한 뒤 대량 업데이트 작업을 수행하고 다시 생성하는 방식으로 SQL문 효율을 높일 수 있음

ALTER TABLE 사원출입기록
DROP INDEX I_출입문;

 

튜닝 결과

| 튜닝 후 실행 계획 |

인덱스 생성한 뒤 SQL문 수행 (튜닝 전과 동일)

EXPLAIN
UPDATE 사원출입기록
SET 출입문 = 'X'
WHERE 출입문 = 'B';

- 10s → 1.96s (시간 단축)

- 기본 키(key 항목 : PRIMARY)로 데이터에 접근해서 데이터가 변경

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 5장 악성 SQL 튜닝으로 전문가 되기