Real MySQL 8.0의 쿼리 팁

💡 해당 포스팅은 참고용으로 MySQL 사용 편리성을 위해 쿼리나 명령어, Real MySQL의 쿼리 사용 팁들을 단순 정리한다.

 

☁️ INSERT

온라인 트랜잭션 서비스에서 INSERT 문장은 대부분 1건 또는 소량의 레코드를 추가하기에 성능에 대해 고려할 부분이 많지 않다. 한 번의 많은 INSERT 문이 실행되는 경우 쿼리 문장 보다는 테이블 구조가 성능에 영향을 미치는 경우가 대다수이다.

하지만 많은 경우 INSERT의 성능과 SELECT의 성능을 동시에 빠르게 만들 수 있는 테이블 구조는 없다. 그래서 INSERT와 SELECT 성능을 어느 정도 타협하면서 테이블 구조를 설계해야 한다 

- <Real MySQL 8.0 2> 중에서

 

유용한 기능

INSERT IGNORE:

INSERT 실행 시 테이블의 유니크 한 컬럼의 값이 중복되는 경우 무시하고 다음 레코드를 처리하는 쿼리

INSERT IGNORE INTO salaries (emp_no, salary, from_date, to_date) 
VALUES  
    (10001, 60117, '1986-06-26', '1987-06-26'),  
    (10001, 62102, '1987-06-26', '1988-06-25'),  
    (10001, 66074, '1988-06-25', '1989-06-25'),  
    (10001, 66596, '1989-06-25', '1990-06-25'),  
    (10001, 66961, '1990-06-25', '1991-06-25');

INSERT IGNORE INTO salaries   
SELECT emp_no, (salary+100), '2020-01-01', '2022-01-01'   
FROM salaries 
WHERE to_date>='2020-01-01';

INSERT하고자 하는 데이터가 정교하지 않아도 되는 경우 INSERT를 실행하기 전에 레코드 건건이 중복 체크를 실행하지 않고 INSERT IGNORE 명령으로 처리하는 방식으로 자주 사용된다

<Real MySQL 8.0 2> (백은빈.이성욱 지음) 중에서

 

INSERT ~ ON DUPLICATE KEY UPDATE:

INSERT 실행 시 유니크한 컬럼의 중복이 발생하면 UPDATE 문장을 수행한다.

CREATE TABLE daily_statistic (
    target_date DATE NOT NULL,         
    stat_name VARCHAR(10) NOT NULL,
    stat_value BIGINT NOT NULL DEFAULT 0,         
    PRIMARY KEY(target_date, stat_name)       
);

INSERT INTO daily_statistic (target_date, stat_name, stat_value)          
VALUES (DATE(NOW()), 'VISIT', 1)         
ON DUPLICATE KEY UPDATE stat_value=stat_value+1;

 

테이블 구조

INSERT 문장의 성능은 쿼리 문장 자체 보다는 테이블의 구조에 영향을 받는 경우가 많다고 언급했었다.

하나의 INSERT 문장으로 수백, 수천 건의 레코드를 삽입한다면 상입될 레코드를 프라이머리 값(클러스터링 키 값)으로 정렬하는 것이 성능에 도움이 될 수 있다. InnoDB 스토리지 엔진은 클러스터링 키 값을 기준으로 레코드를 정렬하여 저장하기에 클러스터링 키를 기반으로 정렬된 레코드들을 저장하는 작업은 그렇지 않은 작업보다 성능이 좋다.

 

프라이머리 키 선정:

프라이머리 키는 INSERT 성능을 결정하는 가장 중요한 부분이다. 로그 테이블과 같이 주로 INSERT가 실행되는 테이블은 단조 증가 혹은 감소하는 패턴의 값(AUTO_INCREMENT)을 선택하는 것이 좋다. 하지만 사용자, 상품, 주문 테이블 같은 경우에는 주로 SELECT문이 실행되기에 SELECT에 유리한 컬럼을 프라이머리 키로 선정해야 한다.

 

AUTO_INCREMENT 컬럼은 클러스터링 되지 않는 테이블의 효과를 볼 수 있으며 가장 빠른 INSERT를 보장한다.

INSERT가 많은 테이블에 대해서는 인덱스의 개수를 최소화하는 것이 좋다. 레코드 삽입 시 모든 인덱스에도 값이 추가되어야 하기 때문이다. 하지만 1~2백만건 정도의 레코드를 가지는 테이블은 튜닝에 너무 많은 시간을 소모하지 않아도 된다.

 

 

☁️ UPDATE와 DELETE

온라인 트랜잭션 프로그램에서 UPDATE 와 DELETE 또한 주로 하나의 테이블에 대해 한 건 또는 소량의 레코드를 변경 또는 삭제하는 데 사용된다. 하지만 여러 테이블에 대한 레코드를 삭제하는 작업도 필요할 수 있다. MySQL에서는 여러 테이블을 조인해서 한 개 이상의 테이블 레코드를 삭제하거나 변경하는 기능을 제공한다.

 

UPDATE ~ ORDER BY ~ LIMIT n:

특정 컬럼으로 정렬하여 상위 몇 건만 변경하거나 삭제한다. 한 번의 너무 많은 데이터를 변경하거나 삭제하는 작업은 서버에 과부하를 유발하거나 다른 커넥션의 쿼리 처리를 방해할 수도 있다. 따라서 LIMIT를 통해 분할하려 변경하거나 삭제한다.

DELETE FROM employees ORDER BY last_name LIMIT 10;

 

JOIN UPDATE:

두 개 이상의 테이블을 조인하여 조인된 결과에 해당하는 레코드를 변경하거나 삭제한다. 특정 테이블의 컬럼값을 다른 테이블의 컬럼에 업데이트해야 할 때 주로 사용된다. JOIN UPDATE 문장에서는 GROUP BY, ORDER BY 절을 사용할 수 없다.

일반적으로 JOIN UPDATE는 조인되는 모든 테이블에 대해 읽기 참조만 되는 테이블은 읽기 잠금이 걸리고, 칼럼이 변경되는 테이블은 쓰기 잠금이 걸린다. 그래서 JOIN UPDATE 문장이 웹 서비스 같은 OLTP 환경에서는 데드락을 유발할 가능성이 높으므로 너무 빈번하게 사용하는 것은 피하는 것이 좋다. 하지만 배치 프로그램이나 통계용 UPDATE 문장에서는 유용하게 사용할 수 있다.

<Real MySQL 8.0 2> (백은빈.이성욱 지음) 중에서

UPDATE tb_test1 t1, employees e          
SET t1.first_name=e.first_name        
WHERE e.emp_no=t1.emp_no;

e.emp_no=t1.emp_no를 만족하는 레코드를 찾아서 t1.first_name을 e.first_name으로 변경한다.

 

JOIN DELETE:

여러 테이블을 조인한 다음 특정 조건에 만족하는 레코드를 삭제한다.

아래의 쿼리는 3개의 테이블을 특정 조건으로 조인한 결과에 대한 e 테이블의 레코드를 삭제한다.

DELETE e
FROM employees e, dept_emp de, departments d        
WHERE e.emp_no=de.emp_no AND de.dept_no=d.dept_no AND d.dept_no='d001';

여러 테이블의 레코드도 삭제할 수 있다.

DELETE e, de       
FROM employees e, dept_emp de, departments d        
WHERE e.emp_no=de.emp_no AND de.dept_no=d.dept_no AND d.dept_no='d001';

 

 

☁️ DDL

DBMS의 모든 오브젝트를 생성하거나 변경하는 쿼리를 DDL이라고 한다. DDL은 MySQL 서버가 업그레이드 되면서 DBMS 온라인 상태에서도 처리될 수 있게 성능이 개선되었지만 여전히 시간이 오래 걸리고 많은 부하를 발생시키는 작업이 존재하므로 주의해야 한다. (초보자는 오프라인 상태에서 사용하라는 의미같다)

 

온라인 DDL 알고리즘

온라인 DDL은 ALGORITHM 과 LOCK 옵션을 사용하여 DDL을 통해 스키마를 변경하는 작업 도중에도 다른 커넥션에서 데이터를 조회하거나 변경하는 작업을 가능하게 해준다.

 

MySQL 서버에서는 old_alter_table 시스템 변수를 이용해 ALTER TABLE 명령이 온라인 DDL로 작동할지, 아니면 이전의 테이블 읽기 쓰기를 막고 스키마를 변경하는 방식으로 처리할지 결정한다. (기본값은 OFF, 온라인 DDL 활성화)

온라인 DDL이 실행되면 알고리즘을 선택하는데 각 알고리즘은 다음과 같다. (우선순위가 낮을 수록 더 큰 잠금과 많은 작업을 필요로 하고 서버의 부하도 많이 발생시킴)

• INSTANT: 테이블의 데이터를 전혀 변경하지 않고, 메타데이터만 변경한다.
• INPLACE: 임시 테이블로 데이터를 복사하지 않고 스키마 변경을 실행한다.
• COPY: 변경된 스키마를 적용한 테이블을 생성하고, 복사한 뒤 임시 테이블을 RENAME 한다.

LOCK 옵션은 다음과 같다.

• NODE: 아무런 잠금을 걸지 않는다.
• SHARED: 읽기 잠금을 건다. (변경이 불가능)
• EXCLUSIVE: 쓰기 잠금을 건다. (조회, 변경 불가능)

자세한 내용은 Real MySQL 8.0 2권 166 페이지 온라인 DDL을 참고하자.

 

 

☁️ 데이터 베이스 변경

테이블

테이블 생성: 테이블을 생성할 때는 아래와 같은 키워드를 사용할 수 있다.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb_test (  
    member_id BIGINT [UNSIGNED] [AUTO_INCREMENT],  
    nickname CHAR(20) [CHARACTER SET 'utf8'] [COLLATE 'utf8_general_ci'] [NOT NULL],
  home_url VARCHAR(200) [COLLATE 'latin1_general_cs'],  
    birth_year SMALLINT [(4)] [UNSIGNED] [ZEROFILL],  
    member_point INT [NOT NULL] [DEFAULT 0],  
    registered_dttm DATETIME [NOT NULL],  
    modified_ts TIMESTAMP [NOT NULL] [DEFAULT CURRENT_TIMESTAMP],  
    gender ENUM('Female','Male') [NOT NULL],  
    hobby SET('Reading','Game','Sports'),  
    profile TEXT [NOT NULL],  
    session_data BLOB,  

    PRIMARY KEY (member_id),  
    UNIQUE INDEX ux_nickname (nickname),  
    INDEX ix_registereddttm (registered_dttm)
) ENGINE=INNODB;

 

 

테이블 변경: 테이블 변경에서는 ALTER TABLE 명령을 사용한다.

테이블의 이름을 변경할 때는 RENAME 명령어를 사용한다. 아래와 같이 새로운 테이블을 생성하고 해당 테이블을 다른 테이블로 교체할 때 사용할 수 있는데, 아래의 쿼리에는 문제가 존재한다.

-- // 새로운 테이블 및 데이터 생성
CREATE TABLE batch_new (...);
INSERT INTO batch_new SELECT ...;

-- // 기존 테이블과 교체
RENAME TABLE batch TO batch_old;
-- // 이 잠깐의 시간에 batch 라는 테이블이 없어짐
RENAME TABLE batch_new TO batch;

batch라는 테이블이 사라지는 시점에 다른 세션에서 batch 테이블을 읽으면 에러가 발생한다. 따라서 아래와 같이 하나의 문장으로 묶어서 실행하면 된다.

RENAME TABLE 
    batch TO batch_old, 
    batch_new TO  batch;

이렇게 명시하면 batch, batch_new 테이블에 락을 걸고 이름 재정의 작업을 수행한다.

 

 

테이블 복사: 아래의 쿼리를 통해 테이블의 구조를 복사할 수 있다.

-- 데이터는 제외하고 테이블 구조만 복사 (인덱스도 복사)
CREATE TABLE temp_employees LIKE employees;

-- 데이터 복사
INSERT INTO temp_employees SELECT * FROM employees;

 

 

테이블 삭제: 레코드가 많지 않은 테이블을 삭제하는 작업을 온라인 상태에서도 문제가 되지 않는다.

DROP TABLE [IF EXISTS] table1;

하지만 용량이 매우 큰 테이블을 삭제하는 작업은 부하가 큰 작업에 속한다. 디스크에 분산되어 저장되어 있는 데이터도 모두 삭제해야 하기 때문에 많은 디스크 I/O가 발생할 수 있다. 따라서 테이블이 크다면 서비스 도중에 삭제하는 작업은 수행하지 않는 것이 좋다.

또 한가지 유의해야 할 점은 삭제 대상이 되는 테이블의 페이지 중 많은 페이지가 어댑티브 해시 인덱스에 의해 참조되고 있다면 어댑티브 해시 인덱스의 정보도 모두 삭제해야 하기 때문에 서버의 부하가 높아진다.

 

인덱스

MySQL 8.0 버전에서 대부분의 인덱스 변경 작업이 온라인 DDL로 처리 가능하도록 개선됐다.

인덱스 추가: 인덱스의 추가는 ALTER TABKE ~ ADD INDEX 명령어로 수행할 수 있다.

-- 프라이머리 키 추가
ALTER TABLE employees ADD PRIMARY KEY (emp_no), 
    ALGORITHM=INPLACE, LOCK=NONE;

-- 유니크 컬럼 인덱스
ALTER TABLE employees ADD UNIQUE INDEX ux_empno (emp_no),
    ALGORITHM=INPLACE, LOCK=NONE;

-- 일반 컬럼 인덱스
ALTER TABLE employees ADD INDEX ix_lastname (last_name),
    ALGORITHM=INPLACE, LOCK=NONE;

-- 전문 검색 인덱스
ALTER TABLE employees ADD FULLTEXT INDEX fx_firstname_lastname (first_name, last_name),
    ALGORITHM=INPLACE, LOCK=SHARED;

인덱스는 아래와 같은 명령어로 조작할 수 있다.

-- 인덱스 조회
SHOW INDEX FROM employees;

-- 인덱스 이름 변경
ALTER TABLE salaries RENAME INDEX ix_salary TO ix_salary2,
    ALGORITHM=INPLACE, LOCK=NONE;

-- 새로운 인덱스 생성하고 교체하기
ALTER TABLE employees
    ADD INDEX index_new (first_name, last_name),
    ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE employees
    DROP INDEX ix_firstname,
    RENAME INDEX index_new TO ix_firstname,
    ALGORITHM=INPLACE, LOCK=NONE;

-- 인덱스 삭제
ALTER TABLE employees DROP PRIMARY KEY, ALGORITHM=COPY, LOCK=SHARED;

 

프로세스 조회 및 강제 종료

SHOW PROCESSLIST;

KILL QUERY {id} -- 쿼리 강제 종료
KILL {id} -- 커넥션 강제 종료

 

 

☁️ 쿼리 성능 테스트

작성된 쿼리의 효율과 개선점을 확인하려면 실행 계획을 살펴보는 것이 중요하다. 실행 계획에 문제될 부분이 보이지 않는다면 쿼리를 실행해 보고 문제가 존재하는지 살펴본다. 쿼리를 직접 실행하며 눈으로 성능을 체크하는 데는 여러 가지 방해 요소가 존재하는데 이를 간과하고 성능을 판단하면 큰 문제로 이어질 수 있다.

쿼리의 성능에 영향을 미치는 요소

• 운영체제의 캐시
• 버퍼 풀
• 독립된 MySQL 서버
• 쿼리 테스트 횟수 (일반적으로는 버퍼 풀이 워밍 업된 상태에서 진행)