4장 악성 SQL 튜닝으로 초보자 탈출하기 - (1)

1. AUTO COMMIT

MySQL은 디폴트로 AUTO COMMIT이 활성화 되어 있기 때문에 INSERT, UPDATE, DELETE 작업 후에는 되돌릴 수가 없습니다. 그래서 작업 전에 백업을 하거나 AUTO COMMIT을 비활성화하여 결과를 보고 적용하거나 원 상태로 되돌리기 할 수 있습니다. 

-- AUTO COMMIT 확인 (1: 활성화, 0: 비활성화)
SELECT @@autocommit;

-- AUTO COMMIT 활성화
SET autocommit = 1;

-- AUTO COMMIT 비활성화
SET autocommit = 0;

-- 결과 적용
COMMIT;

-- 되돌리기
ROLLBACK;

 

2. 예제 맞도록 employee 데이터베이스 세팅

아래 예제는 "업무에 바로 쓰는 SQL 튜닝 - 양바른 지음, 한빛미디어" 책에 소개되는 예제입니다. 책에서 제공해주는 tuning 데이터베이스는 employees 데이터베이스를 개량하였습니다. tunning 데이터베이스는 테이블명, 컬럼명이 한글로 되어 있어 쿼리문을 작성할 때 한/영을 자주 변경해야 되어 불편합니다. 그래서 employees 데이터 베이스를 tuning 데이터베이스와 같도록 변경하여 사용하도록 하겠습니다. tuning 데이터베이스에 존재하는 값도 필요하기 때문에 tuning 데이터베이스를 설치해야 합니다.

 

2.1 departments

tuning 데이터베이스의 `부서` 테이블에는 `비고` 컬럼이 존재합니다. departments 테이블에 `note` 컬럼을 추가하고 레코드 별로 같은 값을 가질 수 있게 처리합니다.

-- 부서 테이블에 note 컬럼 추가
ALTER TABLE departments
  ADD COLUMN note VARCHAR(40) NULL;


-- 각 컬럼의 값을 tunning 데이터베이스 `부서` 테이블의 `비고` 컬럼과 같게 변경
UPDATE departments A
  JOIN tuning.`부서` B
    ON A.dept_no = B.`부서번호`
   SET A.note = B.`비고`;

 

2.2 salaries

tuning 데이터베이스의 `급여` 테이블에는 `사용여부` 컬럼이 존재합니다. salaries 테이블에 `yn_use` 컬럼을 추가하고 레코드 별로 같은 값을 가질 수 있게 처리합니다. 그리고 인덱스도 추가합니다.

ALTER TABLE salaries
  ADD COLUMN yn_use VARCHAR(1) NOT NULL DEFAULT '0';

ALTER TABLE salaries
  ADD INDEX idx_ynuse(yn_use);

UPDATE salaries
   SET yn_use = '1'
 WHERE to_date = '9999-01-01';

 

2.3 employees_commute

tuning 데이터베이스에는 `사원출입기록` 테이블이 존재하지만 employees 데이터베이스에는 존재하지 않습니다. `사원출입기록` 테이블과 매핑되는 `employees_commute` 테이블을 생성하고 데이터를 삽입한다.

CREATE TABLE employees_commute (
    commute_id INT(10) NOT NULL AUTO_INCREMENT,
    emp_no INT(10) NOT NULL,
    commute_time TIMtuningESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    commute_type CHAR(1) NOT NULL COLLATE 'utf8mb3_general_ci',
    door CHAR(1) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    location CHAR(1) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    PRIMARY KEY (commute_id, emp_no) USING BTREE,
    INDEX idx_location (location) USING BTREE,
    INDEX idx_commute_time (commute_time) USING BTREE,
    INDEX idx_door (door) USING BTREE
);


INSERT INTO employees_commute
(commute_id, emp_no, commute_time, commute_type, door, location)
SELECT A.순번, A.사원번호, A.입출입시간
     , A.입출입구분, A.출입문, A.지역
  FROM tuning.`사원출입기록` A;

 

2.4 employees

tuning 데이터베이스의 `사원` 테이블에는 PK, `입사일자`, '성별, 성` 인덱스가 존재한다. employees 테이블에는 PK, `hire_date`, `gender, birth_date`, `first_name` 인덱스가 있다. employees 테이블의 인덱스를 삭제하거나 변경하여 tuning 데이터베이스의 `사원` 테이블과 같은 인덱스를 가질 수 있도록 수정한다.

-- 기존 gender, birth_date 인덱스 삭제
ALTER TABLE employees
 DROP INDEX ix_gender_birthdate;
 
-- 기존 first_name 인덱스 삭제
ALTER TABLE employees
 DROP INDEX ix_firstname

-- gender, last_name 인덱스 생성
ALTER TABLE employees
  ADD INDEX idx_gender_lastname(gender, last_name);
  
-- first_name 콜레이트 변경
  ALTER TABLE employees
 CHANGE COLUMN first_name first_name VARCHAR(14) NOT NULL
COLLATE 'UTF8MB4_bin';