7장. 쿼리 작성 및 최적화 - 스키마 조작, SQL 힌트, 쿼리 성능 테스트

#스터디/MySQL

스키마 조작 (DDL)

데이터베이스의 구조 및 정의를 생성하는 쿼리를 DDL(Data Definition Language)라고 한다. Database, Table, 스토어드 프로시저나 함수 등을 생성/수정하는 대부분의 명령어가 이에 해당한다.

데이터 베이스

MySQL 서버 한개는 여러개의 데이터베이스를 가질 수 있는는데, MySQL의 데이터베이스는 단순히 단순히 테이블을 모아 그룹으로 만들어두는 개념이다.

그래서 데이터 베이스 단위로 변경하거나 설정하는 DDL은 CHARACTER SET이나 COLLATE정도이므로 간단하다.

데이터베이스 생성

CREATE DATABASE [IF NOT EXISTS] employees;
CREATE DATABASE [IF NOT EXISTS] employees CHARACTER SET utf8;
CREATE DATABASE [IF NOT EXISTS] employees CHARACTER SET utf8 COLLATE utf8_general_ci;

데이터베이스 생성시 문자열 관련 설정을 하지 않을 경우 시스템 변수의 기본 값을 사용한다.

데이터베이스 목록

SHOW DATABASES;
SHOW DATABASES LIKE '%emp%';

권한을 가지고 있는 목록만 표시한다.

데이터베이스 선택

USE employees;

# 데이터 베이스 미선택시 데이터베이스 이름을 명시해준다.
SELECT * FROM employees.departments;

데이터베이스 속성 변경

ALTER DATABASE employees CHARACTER SET=euckr;
ALTER DATABASE employees CHARACTER SET=euckr COLLATE=euckr_korean_ci;

데이터베이스 삭제

DROP DATABASE [IF EXISTS] employees;

테이블

테이블 생성

쿼리 P. 524 참고

테이블 구조 확인

SHOW CREATE TABLE

SHOW CREATE TABLE employees;

테이블의 메타정보 (*.FRM) 파일을 읽어서 이를 CRAETE TABLE 쿼리로 재작성 하는 방식
- 수정없이 사용가능
- 칼럼의 목록과 인덱스, 외래키 정보를 보여준다.

-> SQL 튜닝시 혹은 테이블의 구조 확인시 용이하다.

DESC or DESCRIBE

DESC employees;
DESCRIBE employees;

인덱스, 외래키의 정보를 제외한 칼럼의 정보만 노출하며 쿼리 형태가 아닌 읽기 좋은 표형태로 표시해준다.

테이블 구조 변경

ALTER TABLE employees CHARACTER SET 'euckr';
ALTER TABLE employees ENGINE='myisam';

문자 집합을 변경하는 경우 기존의 문자열은 보정하지 않고 새로 들어올 레코드에 대해서만 적용한다. (쿼리가 빨리 실행됨)
엔진을 변경하는 경우 모든 레코드를 복사한다.
- 쿼리의 엔진이 기존과 같더라도 레코드의 복사가 일어난다.
- 이 과정에서 테이블이 사용하는 디스크의 Fragmentation(빈 공간)이 제거된다.

## 참고 ##
테이블이 사용하는 디스크 공간의 Fragmentation(빈공간)을 최소화하고 테이블의 구조를 최적화하는 `OPTIMIZE TABLE`이란 명령어가 있다.
이 명령어는 내부적으로 `ALTER TABLE ... ENGINE='InnoDB'`와 동일한 명령을 수행한다.
결국 테이블 최적화란 테이블의 레코드를 새로운 테이블에 모두 복사함을 의미한다.

따라서 `ALTET TABLE`을 통해 칼럼을 추가하거나 삭제하는 등을 실행하면 별도로 `OPTIMIZE TABLE`을 실행할 필요가 없다.

RENAME TABLE

테이블의 이름 변경

# 순단이 발생하는 쿼리
RENAME TABLE emp_stat TO backup_emp_stat;
RENAME TABLE temp_emp_stat TO emp_stat;

# 순단이 발생하지 않는 쿼리
RENAME TABLE emp_stat TO backup_emp_stat, temp_emp_stat TO emp_stat;

순단이 발생하는 쿼리에선 두 개의 쿼리 사이에 실행된 SELECT 쿼리의 경우 테이블을 찾을 수 없다는 에러를 발생시킨다.
RENAME TABLE 명령어를 통해 한개의 트랜잭션에서 여러 테이블의 이름을 변경할 수 있다.
- 이 과정에서 네임락(Name Lock)이 걸린다.
- 네임락이 걸리면 SELECT 쿼리는 락이 풀릴 때까지 대기한다.
- RENAME TABLE의 실행시간은 보통 굉장히 짧기때문에 큰 문제가 되지 않는다.

## 참고 ##
`RENAME TABLE` 쿼리는 시스템적으로 Atomic한 작업이 아니다.
InnoDB의 경우 `RENAME TABLE` 쿼리 실행시 `*.FRM`파일과 스토리지 엔진내에서 관리되는 딕셔너리 데이터를 변경해야한다.
많은 데이터가 변경된후 `RENAME TABLE`을 실행하면 `*.FRM`파일과 딕셔너리 데이터의 미스매치가 일어나는 현상이 발생할 수 있다.

~MyISAM의 경우 발생하지 않는다.(`*.FRM`, `*.MYD`, `*.MYI` 세가지 파일의 이름만 변경한다)~

테이블의 DB 변경

RENAME TABLE db1.employees TO db2.employees;

db1 디렉토리의 employees 테이블의 데이터 파일을 db2디렉토리로 옮기는 작업이다.

## 참고 ##
만약 db1과 db2가 서로 다른 파일시스템에 있다고 가정해보자.
일반적으로 유닉스와 윈도우는 타 파일시스템간 복사시 데이터 파일을 복사하고 완료하면 원본을 지우는 식으로 동작한다.

따라서 파일시스템이 다른데 RENAME TABLE을 실행해 데이터베이스를 변경하려 하면 데이터파일의 크기에 비례해 시간이 소요된다.

테이블의 상태 조회

SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 'employees';

쿼리 실행시 나오는 정보 중 레코드 수나 레코드의 평균 크기는 통계를 바탕으로 예측한 값이라 정확하지 않다.

테이블 구조 복사

# 테이블 구조 복사 (칼럼, 인덱스, 외래키 등)
CREATE TABLE new_employees LIKE employees;

# 데이터 입력
INSERT INTO new_employees SELECT * FROM employees;

테이블 구조 복사 및 데이터 복사

# P.532
CRAETE TABLE ... AS SELECT ... FROM ...;

위 쿼리는 SELECT되는 칼럼과 저장할 칼럼의 이름의 매치를 통해서 데이터를 입력한다.
- 만약 이름이 다르면 테이블에 정의하지 않은 새로운 칼럼이 생기게 된다. (P.533)

테이블 삭제

MySQL 5.0, 5.1에선 테이블 삭제시 LOCK_open이라는 글로벌 잠금을 획득한다. 잠금이 걸려 있으면 쿼리가 완료되기전에 타 커넥션의 쿼리(SELECT, INSERT, UPDATE)를 전혀 실행하지 못한다.

특히 리눅스의 기본 파일 시스템인 EXT3는 파일 삭제나 메타 정보 변경이 느리기 때문에 주의가 필요하다.

칼럼 변경

칼럼 추가

ALTER TABLE employees ADD COLUMN emp_telno VARCHAR(20);
ALTER TABLE employees ADD COLUMN emp_telno VARCHAR(20) AFTER emp_no;

MySQL에서 칼럼을 추가하는 과정은 항상 새로운 테이블로 데이터를 복사하는 형태를 취한다.
- 레코드 수에 비례하여 시간이 걸린다.

칼럼삭제

ALTER TABLE employees DROP COLUMN emp_telno;

칼럼 삭제 역시 데이터를 복사하는 형태를 취한다.

칼럼 이름이나 칼럼 타입 변경

칼럼 이름 변경

ALTER TABLE employees CHANGE COLUMN first_name name VARCHAR(14) NOT NULL;

칼럼의 타입과 속성은 모두 기입해주어야 한다.
칼럼의 이름만 변경하는 작업은 실제 데이터의 변경 없이 메타 정보만 변경한다.
- InnoDB에선 느리다는데 한번 실행해보자

칼럼 타입 변경

ALTER TABLE employees MODIFY COLUMN first_name VARCHAR(200) NOT NULL;

칼럼 변경을 위한 ALTER TABLE 진행 상황

직접 적으로 칼럼 변경 상황을 알수 없으나 서버의 상태 값을 통해 유추할 수 있다.

SHOW GLOBAL STATUS LIKE 'Handler%';

두 가지 값을 통해 확인 가능하다.
- Handler_read_and_next: 풀 테이블 스캔 방식으로 테이블의 모든 레코드를 읽을 때 읽은 레코드 건수
- Handler_write: 테이블에 INSERT되는 레코드 건수

## 참고 ##
테이블의 레코드를 수정하는 경우 임시 테이블에 복사하는데, 이때 파일시스템에 만들어지는 `#sql-...`파일의 확인을 통해 진척도를 유추할 수 있다.

인덱스 변경

인덱스 추가

ALTER TABLE employees ADD PRIMARY KEY (emp_no);
ALTER TABLE employees ADD UNIQUE INDEX ux_emptelno (emp_telno);
ALTER TABLE employees ADD INDEX ix_emptelno (emp_telno);
ALTER TABLE employees ADD FULLTEXT ix_emptelno (emp_telno);
ALTER TABLE employees ADD SPATIAL ix_emptelno (emp_telno);

MySQL 5.1부터 인덱스만 생성하는 형태로 개선
인덱스 추가 쿼리 끝에 인덱스의 정렬 옵션을 줄 수 있는데, MySQL에선 지원하지 않는 기능이다. (항상 ASC 정렬)
- MySQL 8.0부터는 인덱스의 정렬 순서 설정을 지원한다.

인덱스 조회

SHOW INDEX FROM employees;

인덱스로 지정된 칼럼의 콜레이션 인덱스 알고리즘, Cardinality등을 리턴한다.

인덱스 삭제

ALTER TABLE employees DROP PRIMARY KEY;
ALTER TABLE employees DROP INDEX ix_emptelno;

PK를 제거할 시엔 레코드 복사가 일어난다.
인덱스만 제거하기 때문에 빠른 속도로 처리된다.

칼럼 및 인덱스 변경을 모아서 실행

ALTER TABLE employees 
	DROP INDEX ix_firstname,
	ADD INDEX ix_new_firstname (first_name),
	ADD COLUMN emp_telno VARCHAR(15);

테이블 변경 작업을 한꺼번에 처리하면 나눠서 할때보다 빠르게 처리된다.

인덱스 생성을 위한 ALTER TABLE 진행 상황

MySQL 5.1이상에서는 인덱스 생성시 데이터는 변경하지 않는다. 따라서 Handler_read_and_next 옵션만으로 진행 상황을 유추할 수 있다.

프로세스 조회

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

쾌적한 상태일 경우 Command 칼럼이 SLEEP 상태인 경우가 많ㅁ다.
중요 한것은 State 칼럼의 내용이다.
- Copying ..., Sorting ...으로 시작할때 주의깊게 살펴봐야한다. (쿼리 튜닝이 필요할 수 있다.)

프로세스 강제 종료

# 쿼리와 커넥션을 강제종료한다.
KILL {query-id};
# 커넥션은 유지하고 쿼리만 강제종료한다.
KILL QUERY {query-id};

쿼리 ID는 SHOW PROCESSLIST명령을 통해 얻을 수 있다.

시스템 변수 조회 및 변경

조회

SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE '%connections%';
SHOW SESSION VARIABLES LIKE '%timeout%';
SHOW VARIABLES LIKE '%timeout%';

GLOBAL키워드를 포함하면 글로벌 시스템 변수를 출력한다.
기본은 SESSION 변수 목록이다.

변경

SET GLOBAL max_connections=500;
SET wait_timeout=100;

마찬 가지로 GLOBAL키워드를 통해 세션 변수 변경 / 글로벌 시스템 변수 변경으로 나눈다.

경고나 에러 조회

쿼리의 실행 도중 에러가 발생하면 쿼리가 중지하고 에러메세지를 자동으로 보여준다. 그러나 에러가 아닌 경고 혹은 정보성 메세지는 자동으로 볼 수 없는데, 아래의 쿼리를 통해 확인할 수 있다.

SHOW WARNINGS;

# 만약, 에러가 발생했음에도 에러메세지가 보이지 않으면 아래 쿼리를 실행한다
SHOW ERRORS;

권한 조회

# MySQL에서 사용할 수 있는 모든 권한 목록
SHOW PRIVILEGES;

# 특정 유저가 가진 권한 조회
SHOW GRANTS FOR 'root'@'localhost';

SQL 힌트

SQL은 어떤 데이터를 가져올지 기술하는 언어이지, 어떻게 가져올지는 기술하지 않는다. 실제 데이터를 가져오는 방법은 MySQL의 옵티마이저다.

하지만 종종 최적으로 가져오지 못하는 경우가 있다. 이때 SQL문장에 키워드를 통해 옵티마이저에 힌트를 줄 수 있다.

힌트의 사용법

SELECT * FROM employees USE INDEX(PRIMARY) WHERE emp_no=10001;
SELECT * FROM employees /*! USE INDEX(PRIMARY) */ WHERE emp_no=10001;

주석의 처럼 사용하는 방법과 SQL문장의 일부로 사용하는 방식 2가지가 있다.

STRAIGHT_JOIN

조인을 사용하는 SQL을 작성하더라도 조인 순서는 SQL에 명시된 순서가 아닌 옵티마이저가 정한 순서대로 조인을 실행한다. STRAIGHT_JOIN을 사용할 경우 SQL에 명시된 순서대로 조인한다.

SELECT STRAIGHT_JOIN * FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

SELECT /*! STRAIGHT_JOIN */ * FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

힌트를 사용하지 않을 경우 옵티마이저에 의해 조인 순서가 결정되므로 순서를 유추할 수 없다.
힌트를 사용할 경우 쿼리에 표현한대로 employees, dept_emp, departments 순서로 조인한다.
가능하면 사용하지 않고 옵티마이저에게 위임하되 아래와 같은 경우엔 사용해도 괜찮다.
- 임시 테이블과 일반 테이블의 조인
  - 임시 테이블엔 인덱스가 없기 때문에 드라이빙 테이블로 사용하는 것이 좋다.
- 임시 테이블끼리의 조인
  - 둘다 인덱스가 없기 때문에 레코드수가 적은 쪽을 드라이빙 테이블로 사용하는것이 좋다.
- 일반 테이블끼리의 조인
  - 양쪽 모두 조인컬럼에 대한 인덱스가 있거나 혹은 없을때 레코드 건수가 적은 쪽을 드라이빙 테이블로 사용한다.

-> 위 경우에서 레코드 수는 조인 조건에 의해 선택되는 레코드 수를 의미한다.

USE INDEX / FORCE INDEX / IGNORE INDEX

종종 똑같은 칼럼으로 다른 조합의 인덱스를 여러개 생성해야 할때가 있다. 이런 경우 MySQL 옵티마이저가 최적의 인덱스를 선택하지 못하는 경우가 발생할 수 있다. 4개 이상의 칼럼이 포함된 경우 발생할 확률이 높고 2~3개 칼럼의 경우는 정상적으로 선택된다.

SELECT * FROM employees WHERE emp_no=10001;
SELECT * FROM employees FORCE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees USE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees IGNORE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees FORCE INDEX(ix_firstname) WHERE emp_no=10001;

USE INDEX
- 가장 자주 사용되는 인덱스 힌트, 옵티마이저에게 특정 인덱스를 사용하도록 권장한다.
FORCE INDEX
- USE INDEX보다 더 영향이 큰 인덱스다.
- 그러나 USE INDEX로 인덱스 선택이 안되는 경우 FORCE INDEX로도 선택이 안되는 경우가 있다.
IGNORE INDEX
- 인덱스를 권장하는 것과 반대로 특정 인덱스를 사용하지 못하게 할때 사용한다.

-> 테이블 내의 레코드 수는 시시각각 변하기 때문에 인덱스를 특정하기보다 옵티마이저가 잘 선택할 수 있도록 튜닝하는 것이 좋다.

## 참고 ##
USE INDEX FOR JOIN
USE INDEX FOR ORDER BY
USE INDEX FOR GROUP BY

위와 같은 키워드가 있으나 잘 사용하지 않는다.

SQL_CACHE / SQL_NO_CACHE

SELECT SQL_NO_CACHE COUNT(*) FROM employees WHERE last_name='Facello';
SELECT /*! SQL_NO_CACHE */ COUNT(*) FROM employees WHERE last_name='Facello';

쿼리 캐시는 MySQL의 설정에 따라 동작하게 하는 것이 일반적이다.
- SQL_CACHE는 잘 사용하지 않는다.
SQL_NO_CACHE의 경우 쿼리 성능 테스트를 위해 자주 사용된다.

SQL_CALC_FOUND_ROWS

SELECT에 LIMIT가 사용되는 경우 조건을 만족하는 레코드를 LIMIT수 만큼만 찾으면 쿼리 수행을 멈춘다. 그 때문에 조건에 맞는 레코드의 수를 정확하게 알 수 없다. SQL_CALC_FOUND_ROWS를 사용하는 경우 조건에 해당하는 레코드가 총 몇건인지 계산한다.

SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;
SELECT FOUND_ROWS() AS total_record_count;

개발자들의 편의를 위해 만들어진 기능으로 프로덕션 쿼리에는 사용하지 않는 것이 좋다.

기타 힌트

SQL_BIG_RESULT
SQL_SMALL_RESULT
SQL_BUFFER_RESULT
HIGH_PRIORITY

-> 필요할때 문서를 찾아보자.

쿼리 성능 테스트

작성된 쿼리의 성능을 테스트해야할 경우가 종종 생긴다. 성능을 확인하려면 직접 실행해보는 방법이 가장 일반적이다. 하지만 이렇게 테스트하는 경우 성능에 영향을 주는 변수가 너무 많다.

변수를 줄이고 올바른 성능 테스트를 할 수 있는 방법을 알아보자.

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

운영체제의 캐시
- MySQL 서버는 시스템 콜을 통해 데이터를 불러온다.
  - 대부분의 운영체제는 한번 읽은 데이터는 별도의 캐시 영역에 보관했다가 재요청시 디스크를 읽지 않고 캐시를 리턴한다.
  - 운영체제는 프로세스별로 디스크 캐시를 하기 때문에 초기화를 위해선 서비스를 재시작하거나 캐시 삭제 명령을 실행해야 한다.
    - echo 3 > /proc/sys/vm/drop_caches
- InnoDB의 경우 Direct I/O를 사용하기 때문에 운영채제의 캐시가 큰 영향을 미치지 않는다.
- MyISAM의 경우 운영체제 캐시에 의존도가 높아서 성능의 차이가 큰편이다.
MySQL 서버의 버퍼 풀(InnoDB 버퍼풀, MyISAM 키 캐시)
- MySQL에서는 데이터 파일을 페이지 단위로 캐시하는 기능을 제공한다.
  - InnoDB의 버퍼 풀 (데이터 페이지, 인덱스 페이지, 인덱스 버퍼)
  - MyISAM의 키 캐시
- MySQL이 가동되면 버퍼 풀을 Purge하는 기능이 없다. (재시작을 해야한다)
MySQL 쿼리 캐시
- MySQL은 이전에 실행한 쿼리를 캐시해두고 같은 쿼리가 들어오면 연산하지 않고 캐시된 결과를 반환한다.
- RESET QUERY CACHE명령을 통해 초기화가 가능하나 모든 쿼리 캐시가 날아가기 때문에 성능 테스트엔 적합하지 않다.
- SQL_NO_CACHE를 사용하여 테스트 함이 바람직하다.
독립된 MySQL서버
- MySQL서버가 기동중인 장비에 웹이나 타 프로그램이 함께 실행되면 리소스 점유 경쟁을 해야하기때문에 성능에 영향을 줄 수 있다.

-> 실제로 프로덕션은 캐시나 버퍼가 올라간 상태로 동작하는 경우가 많기 떄문에 성능 테스트라 함은 워밍업된 상태를 가정하는 경우가 많다.

쿼리 성능 테스트

P.560 ~ P.561

쿼리 프로파일링

MySQL은 쿼리가 처리되는 동안 단계별로 시간이 얼마나 걸렸는지 확인하는 기능을 제공한다. 기본적으론 비활성상태로 설정을 통해 활성화 시켜줘야한다.

프로파일링 활성화

SET PROFILING=1;

프로파일링 쿼리

# 분석된 쿼리의 목록을 확인한다.
# profiling_history_size 옵션을 통해 저장할 사이즈를 지정한다.
SHOW PROFILES;

# 쿼리 목록중 특정 쿼리의 분석 정보를 본다.
SHOW PROFILE FOR QUERY 1;

# 가장 최근 분석된 쿼리의 정보를 본다.
SHOW PROFILE;

# 각 리소스별 분석
SHOW PROFILE CPU FOR QUERY 2;
SHOW PROFILE MEMORY FOR QUERY 2;
SHOW PROFILE BLOCK IO FOR QUERY 2;

# MySQL의 소스파일 어떤 줄에서 시간이 걸렸나 확인할 수 있다.
SHOW PROFILE SOURCE FOR QUERY 2;

## 참고 ##
분석을 하면 주로 Sending Data에 시간이 크게 표기되는 경우가 있는데,
이는 Sending Data가 단순히 클라이언트로 데이터를 전송하는 것 뿐 아니라 쿼리의 실행 결과를 테이블로 읽으면서 전송하는 것을
포함하기 때문이다.

Files

7-3.md

Latest commit

History

7-3.md

File metadata and controls

7장. 쿼리 작성 및 최적화 - 스키마 조작, SQL 힌트, 쿼리 성능 테스트

스키마 조작 (DDL)

데이터 베이스

데이터베이스 생성

데이터베이스 목록

데이터베이스 선택

데이터베이스 속성 변경

데이터베이스 삭제

테이블

테이블 생성

테이블 구조 확인

테이블 구조 변경

RENAME TABLE

테이블의 상태 조회

테이블 구조 복사

테이블 구조 복사 및 데이터 복사

테이블 삭제

칼럼 변경

칼럼 추가

칼럼삭제

칼럼 이름이나 칼럼 타입 변경

칼럼 변경을 위한 ALTER TABLE 진행 상황

인덱스 변경

인덱스 추가

인덱스 조회

인덱스 삭제

칼럼 및 인덱스 변경을 모아서 실행

인덱스 생성을 위한 ALTER TABLE 진행 상황

프로세스 조회

프로세스 강제 종료

시스템 변수 조회 및 변경

경고나 에러 조회

권한 조회

SQL 힌트

힌트의 사용법

STRAIGHT_JOIN

USE INDEX / FORCE INDEX / IGNORE INDEX

SQL_CACHE / SQL_NO_CACHE

SQL_CALC_FOUND_ROWS

기타 힌트

쿼리 성능 테스트

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

쿼리 성능 테스트

쿼리 프로파일링