목차
개요
흔히들 쿼리 퍼포먼스 분석등을 할 때 EXPLAIN 이나 DESCRIBE등을 이용해 실행 계획을 분석하고 쿼리 최적화를 하라는 말을 하고는 한다.
그런데 내 기준에서 이걸 보고 어떻게 해야 할지 가늠을 할 수가 없었다. 대략적으로만 파악을 하고, 실제 나오는 결과를 어떻게 분석하고 어떻게 튜닝해야 할 지 알 수 없었기에 해당 책에서 나온 내용을 토대로 실행 계획 분석 및 각각의 키워드가 무엇을 의미 하는지에 대해 알아보자.
Previous - 데이터 준비
테스트를 하기 위한 데이터를 준비해야 한다.
책에서는 직접 MySQL(or MariaDB)를 설치하고, 데이터를 추가해주고 있지만, 컴퓨터에 여러 DBMS를 더 설치하는 것도 꺼려지고, 좀 더 라이트하게 설치를 하고 싶기에 Docker를 이용해서 설정해주도록 한다.
1. Install Docker Desktop
만약 Docker가 설치되어 있지 않다면 위 링크를 통해 설치해주도록 하자.
설치가 완료 되었다면, 이제 MySQL(or MariaDB) 이미지를 받아서 마운트 시켜줘야 한다. 다음 명령어를 차례대로 실행 시켜보자.
1.
Pulling Image
docker pull ${imageName}:${version}
(Ex: docker pull mysql:8.0.32)
Bash
복사
이미지가 제대로 pulling되었는지는 docker images로 확인해봐도 되고 docker desktop의 GUI를 통해 확인해도 된다.
docker desktop의 images section
2.
Create Container
docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password --name catsbi-mysql -v /Users/catsbi/mysql:/var/lib/mysql mysql:8.0.32 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
Bash
복사
•
-d
: 데몬으로 실행 하며 백그라운드에서 돌아가게 만드는 명령어
•
-p 3306:3306
: 컨테이너 포트 포워딩 외부에서 3306 포트로 접속해면 내부적으로도 3306 포트에 연결된다. -p를 여러개 중첩 작성해서 여러 포트를 포워딩 할 수도 있다.
•
-e MYSQL_ROOT_PASSWORD=password
: 컨테이너에 환경 변수를 설정한다. root 계정 비밀번호를 password로 설정해주는 옵션이다.
•
--name catsbi-mysql
: 컨테이너의 이름을 부여한다.
•
-v /Users/catsbi/mysql:/var/lib/mysql
: 호스트와 컨테이너 볼륨 마운팅 옵션으로 호스트의 /Users/catsbi/mysql 경로와 컨테이너의 /var/lib/mysql 경로를 마운팅시킨다.
•
mysql:8.0.82
: 사용하고자 하는 Docker 이미지 명시
•
--character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
: 서버의 기본 문자셋과 정렬 순서 설정 옵션
3.
setUp data_setting.sql
a.
b.
다운로드 받은 파일들을 컨테이너로 복사하기
docker cp ${다운로드 경로}/SQLtune-main ${컨테이너명}:${복사할 경로}
(Ex: docker cp \Users\lhs0610\Downloads\SQLtune-main catsbi-mysql:/root/inside/)
Bash
복사
c.
컨테이너에서 mySQL에 복사한 데이터 import 시키기
docker exec -it catsbi-mysql bash
# cd ${복사된 경로} (ex: cd /root/inside/)
# mysql -uroot -p --port 3306 < data_setting.sql
Bash
복사
•
Enter password 요청 시 설정했던 비밀번호 입력
4.
Check data validation
mysql> use tuning;
Database change
show tables;
Bash
복사
이렇게 Docker를 이용한 DBMS 설정 및 학습에 필요한 데이터 세팅이 끝났다.
이제 직접 EXPLAIN 실행 계획을 실행했을 때 나오는 결과를 분석하며 각 열의 정보가 무엇을 의미하는지 알아보며 어떻게 쿼리를 분석하고 최적화 할 지 고민해보자.
기본 실행 계획 수행
사실 EXPLAIN말고도 실행 계획을 확인하는 키워드는 더 있다. (다음과 같다)
EXPLAIN SQL 문;
DESCRIBE SQL 문;
DESC SQL 문;
Bash
복사
공통적으로 내가 확인하고자 하는 SQL문 앞에 원하는 키워드를 작성하면 된다.
MySQL, MariaDB는 모두 비슷한 실행 결과를 보여주지만 MySQL이 paritions, filtered열이 추가되어 좀 더 많은 정보를 보여준다.
MariaDB는 10.0.5 버전 이후부턴 UPDATE, DELETE에서도 실행 계획 확인이 가능하다.
실행 계획 항목 분석
실행 계획의 결과로 나오는 열 항목들은 다음과 같다.
•
id
•
type
•
rows
•
select_type
•
possible_keys
•
filtered
•
table
•
key
•
Extra
•
partitions
•
key_len
각각의 항목들에 대해서 알아보도록 하자.
id
: 실행 순서를 표시하는 숫자로. SQL문이 수행되는 차례를 ID로 표기했다.
조인하는 경우에는 동일한 ID로 표시 된다.
Example
select_type
: SQL문을 구성하는 SELECT 문이 단순 FROM절에 위치했는지, 서브 쿼리인지, UNION 절로 묶인 SELECT문인지 등의 유형 정보를 나타낸다. 각 유형 정보는 다음과 같다.
1. SIMPLE
UNION이나 내부 쿼리가 없는 SELECT 문임을 의미한다. 단순한 SELECT문으로 작성된 경우
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 = 100000;
SQL
복사
2. PRIMARY
: 서브쿼리가 포함된 SQL문이 있을 때 첫 번째 SELECT 문에 해당하는 구문에 표시되는 유형
즉, 서브쿼리를 감싸는 외부 쿼리이거나, UNION이 포함된 SQL의 첫 번째 SELECT 키워드가 작성된 구문이다.
explain
select 사원.사원번호, 사원.이름, 사원.성, 급여.연봉,
(select max(부서번호)
from 부서사원_매핑 as 매핑
where 매핑.사원번호 = 사원.사원번호) 카운트
from 사원, 급여
where 사원.사원번호 = 10001 and 사원.사원번호 = 급여.사원번호;
#------------------------------------------------------------
explain
select 사원1.사원번호, 사원1.이름, 사원1.성 # PRIMARY
from 사원 as 사원1
where 사원1.사원번호 = 100001
union all
select 사원2.사원번호, 사원2.이름, 사원2.성
from 사원 as 사원2
where 사원2.사원번호 = 100002;
SQL
복사
3. SUBQUERY
독립적으로 수행되는 서브쿼리를 의미한다.
explain
select (select count(*)
from 부서사원_매핑 as 매핑) as 카운트,
(select MAX(연봉)
from 급여) as 급여;
SQL
복사
4. DERIVED
FROM절에 작성된 서브쿼리라는 의미로 FROM 절의 별도 임시 테이블인 인라인 뷰를 말한다.
explain
select 사원.사원번호, 급여.연봉
from 사원,
(select 사원번호, max(연봉) as 연봉
from 급여
where 사원번호 between 10001 and 20000
group by 사원번호) as 급여
where 사원.사원번호 = 급여.사원번호;
SQL
복사
5. UNION
UNION 이나 UNION ALL 구문으로 합쳐진 SELECT문 중 첫 번째 SELECT문을 제외한 이후 SELECT 구문에 해당한다. 첫 번째 구문은 PRIMARY 유형으로 평간된다.
explain
select 사원1.사원번호, 사원1.이름, 사원1.성
from 사원 as 사원1
where 사원1.사원번호 = 100001
union all
select 사원2.사원번호, 사원2.이름, 사원2.성
from 사원 as 사원2
where 사원2.사원번호 = 100002;
SQL
복사
6. UNION RESULT
UNION ALL이 아니라 UNION 구문으로 SELECT 절을 결합하는 경우.
UNION ALL과 다르게 DISTINCT로 중복되지 않기에 중복 체크하는 과정이 존재한다. 즉, 별도의 메모리 또는 디스크에 임시 테이블을 만들어 중복을 제거한다는 것을 알 수 있다.
그렇기에, SELECT 문이 중복되지 않는다는 게 보장된다면 UNION ALL로 바꾸는 튜닝을 할 수 있다.
explain
select 사원_통합.*
from (select max(입사일자) as 입사일자
from 사원 as 사원1
where 성별 = 'M'
union
select min(입사일자) as 입사일자
from 사원 as 사원1
where 성별 = 'M') as 사원_통합;
SQL
복사
7. DEPENDENT SUBQUERY
UNION, UNION ALL을 사용하는 서브 쿼리가 메인 테이블의 영향을 받는 경우 나타난다. 연결된 단위 쿼리 중 처음 작성된 단위 쿼리에 해당한다.
explain
select 관리자.부서번호,
(select 사원1.이름
from 사원 as 사원1
where 성별 = 'F'
and 사원1.사원번호 = 관리자.사원번호
union all
select 사원2.이름
from 사원 as 사원2
where 성별 = 'F'
and 사원2.사원번호 = 관리자.사원번호)
from 부서관리자 as 관리자;
SQL
복사
•
and 사원1.사원번호 = 관리자.사원번호 조건 절에서 외부 테이블을 참고한다.
•
성능적으로 불리하기에 SQL 튜닝 대상이 된다.
8. DEPENDENT UNION
DEPENDENT SUBQUERY와 동일하지만, 연결된 단위 쿼리 중 첫 번째 쿼리를 제외하는 경우에 해당한다.
explain
select 관리자.부서번호,
(select 사원1.이름
from 사원 as 사원1
where 성별 = 'F'
and 사원1.사원번호 = 관리자.사원번호
union all
select 사원2.이름
from 사원 as 사원2
where 성별 = 'F'
and 사원2.사원번호 = 관리자.사원번호)
from 부서관리자 as 관리자;
SQL
복사
•
7.DEPENDENT SUBQUERY와 쿼리문은 동일하지만, 두 번째 쿼리도 외부 테이블에서 값을 공급 받는다.
9. UNCACHEABLE SUBQUERY
메모리에 적재되어 재활용되어야 할 서브쿼리가 재활용되지 못하는 경우, 즉 서브쿼리가 캐싱 되지 못하는 경우를 의미한다. 보통 서브쿼리 안에
•
사용자 정의 함수가 있거나
•
사용자 변수가 포함되거나
•
RAND(), UUID() 함수 등을 사용해서
매 번 조회 시마다 결과가 달라지는 경우에 해당한다. 이 경우 이 쿼리가 자주 호출되는 SQL인지 파악해서 자주 실행된다면 서브쿼리 결과가 캐싱 될 수 있도록 튜닝 방향을 잡아야 한다.
explain
select *
from 사원
where 사원번호 = (select round(rand() * 1000000));
SQL
복사
10. MATERIALIZED
IN 절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤, 조인이나 가공 작업 수행 시 출력되는 유형.
explain
select *
from 사원
where 사원번호 in (select 사원번호 from 급여 where 시작일자 > '2002-07-01');
SQL
복사
table
테이블명을 표시하는 항목으로 평범하게 쿼리 실행 시 참조하는 테이블 뿐 아니라 임시 테이블을 만들어 별도의 작업을 수행하는 경우에도 <subquery#>, <derived#>등으로 출력 된다.
다음 쿼리의 실행 계획을 통해 좀 더 자세히 설명해보자.
explain
select 사원.사원번호, 급여.연봉
from 사원,
(select 사원번호, max(연봉) as 연봉
from 급여
where 사원번호 between 10001 and 20000
group by 사원번호) as 급여
where 사원.사원번호 = 급여.사원번호;
SQL
복사
위 쿼리의 정보를 간략하게 table위주로만 파악해보면 다음과 같다.
<derived2>가 1이라는 ID를 가지고 사원 테이블도 1이라는 ID를 가진걸로 보아 두 테이블이 조인했음을 알 수 있다. 그런데 여기서 derived 접두사 뒤의 넘버링이 ID를 지칭하기 때문에 급여 테이블과 조인 했음을 파악할 수 있다.
즉, FROM절의 서브 쿼리 구문으로 작성된 급여 테이블과 사원 테이블이 조인했다고 이해할 수 있다.
partitions
테이블이 분할 된 경우 해당 쿼리가 어느 분할(partition)을 사용하는지 나타낸다.
사전에 정의한 전체 파티션 중 특정 파티션에 선택적으로 접근 하는게 성능 측면에서 유리하다.
만약, 너무 많은 영역의 파티션에 접근한다면, 파티션의 기능을 제대로 활용하지 못하는 것이기에 파티션 정의를 튜닝 해 봐야 한다.
참고: partitioning대용량의 데이터를 관리하기 위한 전략 중 하나로 물리적으로 하나의 테이블을 여러 파티션으로 나눠 저장하는 기능.
type
테이블의 데이터를 어떻게 찾을지에 관한 정보를 나타내는 열이다.
예를 들어, 테이블을 풀 스캔(full-scan)할 지 인덱스를 통해 바로 데이터를 찾아갈 지 등을 파악할 수 있다.
1. system
테이블에 데이터가 없거나 한 개만 있는 경우. 성능은 최고다.
create table myisam_테이블
(
col1 INT(11) null default null
) engine = MYISAM;
insert into myisam_테이블
values (1);
explain
select *
from myisam_테이블;
SQL
복사
2. const
조회되는 데이터가 단 1건일 경우. 고유 인덱스나 기본 키를 사용해 단 1건의 데이터에만 접근하는 경우
explain
select *
from 사원
where 사원번호 = 10001;
SQL
복사
3. eq_ref
조인이 수행될 때 드리븐 테이블의 데이터에 접근하며 고유 인덱스 또는 기본 키로 단 1건의 데이터를 조회하는 방식으로 현재 테이블의 각 행에 대해, 다른 테이블에서 단 하나의 행만이 조인될 것을 의미한다.
explain
select 매핑.사원번호, 부서.부서번호, 부서.부서명
from 부서사원_매핑 as 매핑,
부서
where 매핑.부서번호 = 부서.부서번호
and 매핑.사원번호 between 100001 and 100010;
SQL
복사
위 쿼리에서 매핑.부서번호 = 부서.부서번호 조건절을 보면 두 테이블에서 모두 부서번호는 기본 키로 사용되기 때문에 매핑.부서번호에 매칭되는 부서.부서번호는 정확히 1개이고 그렇기에 eq_ref로 type정보를 가지게 된다.
4. ref
eq_ref 유형과 유사한 방식으로, 조인을 수행할 때 드리븐 테이블의 데이터 접근 범위가 2개 이상일 경우를 의미한다.
즉 드라이빙 테이블과 드리븐 테이블 테이블이 1:N 관계임을 의미한다. 데이터의 양이 적을 때는 성능적으로 크게 문제 될 부분이 없지만, 데이터양이 많아질수록 접근해야 할 데이터 범위가 넓어져 성능 저하의 원인이 될 수도 있다.
explain
select 사원.사원번호, 직급.직급명
from 사원,
직급
where 사원.사원번호 = 직급.사원번호
and 사원.사원번호 between 10001 and 10100;
SQL
복사
•
사원.사원번호는 기본키로 고유하지만, 직급.사원번호는 유일하지 않다.
5. ref_or_null
ref 유형과 비슷하지만, IS NULL구문에 대해 인덱스를 활용하도록 최적화된 방식이다.
MySQL, MariaDB는 NULL에 대해서도 인덱스를 활용해 검색할 수 있는데, 이 때 NULL이 가장 앞쪽에 정렬된다. 그래서 테이블에 검색할 NULL 데이터 양이 적을 경우에는 효과적이지만, NULL 데이터 양이 많아진다면 SQL 튜닝이 필요할 수 있다.
explain
select *
from 사원출입기록
where 출입문 is null
or 출입문 = 'A';
SQL
복사
•
출입문 열이 NULL이거나 A인지 검색한다.
•
인덱스_출입문 이라는 인덱스가 생성되어 있기에 출입문 IS NULL 조건문도 인덱스를 활용해 데이터를 검색할 수 있다.
6. range
테이블 내의 연속된 데이터 범위를 조회하는 유형.
=, <>, >, ≥, <, ≤, IS NULL, ≤>, between, in 연산을 통해 범위 스캔을 수행하는 방식으로 스캔의 범위가 넓어질수록 SQL튜닝 검토 대상이 된다.
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 10001 AND 100000;
SQL
복사
7. fulltext
텍스트 검색을 빠르게 처리하기 위한 전문 인덱스(full text index)를 사용해 데이터에 접근하는 방식.
8. index_merge
결합된 인덱스들이 동시에 사용되는 유형.
특정 테이블에 생성된 두 개 이상의 인덱스가 병합되어 동시에 적용된다. (exclude: fulltext)
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 10001 AND 100000
AND 입사일자 = '1985-11-21'
SQL
복사
•
사원 테이블의 입사일자 열에 대한 인덱스_입사일자 인덱스와
•
사원번호 열로 구성된 기본 키 모두를 통합해서 사용할 것 같다.
9. index
full-scan을 의미한다.
물리적인 인덱스 블록을 처음부터 끝까지 훑는 방식으로 스캔하는 대상이 테이블이 아닌 인덱스라는 점만 다를 뿐 ALL 유형과 유사하다.
EXPLAIN
SELECT 사원번호
FROM 직급
WHERE 직급명 = 'Manager'
SQL
복사
10. ALL
테이블의 처음부터 끝까지 읽는 full-scan 방식을 의미한다.
활용할 인덱스가 없거나, 인덱스를 활용하는게 오히려 비효율적이라고 옵티마이저가 판단할 경우 선택된다.
보통 인덱스를 추가하거나 기존 인덱스를 변경하는 식으로 SQL튜닝을 할 수도 있지만, 전체 테이블 중 10~20% 이상 분량의 데이터를 조회해야 한다면, ALL 유형이 오히려 효율적일 수 있다.
EXPLAIN
SELECT * FROM 사원;
SQL
복사
possible_keys
옵티마이저가 SQL문을 최적화 하고자 사용할 수 있는 인덱스 목록을 출력한다.
하지만, 실제로 사용한 인덱스가 아니라 후보군의 기본 키와 인덱스 목록만 보여주기에 실질적인 SQL 튜닝의 효용성은 없다.
key
옵티마이저가 SQL문을 최적화 하고자 사용한 기본 키(PK) 또는 인덱스명을 의미한다.
어느 인덱스로 데이터를 검색했는지를 확인할 수 있기에 해당 정보를 통해 비효율적인 인덱스를 사용했거나 인덱스를 사용하지 않았다면 SQL 튜닝을 고려할 수 있다.
•
테스트 쿼리
EXPLAIN SELECT 사원번호
FROM 직급
WHERE 직급명 = 'Manager';
SQL
복사
•
실행 결과
◦
PRIMARY KEY 를 사용한 것을 확인할 수 있다.
만약 기본키를 사용하지 않는 쿼리를 사용 할 경우 key 정보는 NULL이 될 것이다. 확인해보자.
EXPLAIN
SELECT *
FROM 사원;
SQL
복사
확인 결과 기본키나 인덱스를 전혀 사용하지 않았다는 것을 알 수 있고 추가적으로 type 정보가 ALL인 것으로 인덱스를 활용하지 않았음을 알 수 있다.
key_len
인덱스를 사용할 때는 전체 혹은 일부의 인덱스를 사용하는데, key_len은 이렇게 사용한 인덱스의 바이트 수(bytes)를 의미한다.
UTF-8 캐릭터셋 기준 INT 유형은 단위당 4바이트, VARCHAR 데이터 유형은 단위 당 3바이트이다.
다음 쿼리와 실행 계획을 보자.
EXPLAIN
SELECT 사원번호
FROM 직급
WHERE 직급명 = 'Manager''
SQL
복사
key_len 정보가 159인 것을 알 수 있다 어떻게 159가 나온 것일까?
해당 쿼리를 보면 데이터에 접근 시 직급명으로 조회를 했는데, 직급 테이블은 사원번호 + 직급명 + 시작일자를 복합키로 사용하고 있다.
•
사원번호는 INT 유형이고 4바이트에 해당
•
직급명은 VARCHAR(50)유형이고, 한 글자당 3바이트이기에 최대 151 바이트((50 * 3) + 1)
•
시작일자는 date 유형으로 3바이트
다 합쳐서 158 바이트인데, MySQL은 index에서 null값을 허용하는 컬럼을 확인하기 위한 추가 정보를 저장하기 위해 1바이트를 추가 사용하기에 159바이트가 된다.
ref
reference의 약자로 테이블 조인시 어떤 조건으로 해당 테이블에 액세스 되는 지를 알려준다.
다음 쿼리와 실행 계획을 보면 id가 동일하기에 사원 테이블과 직급 테이블이 조인했음을 알 수 있다.
드리븐 테이블인 직급 테이블의 데이터에 사원번호로 접근해 데이터를 검색하고 있다.
조건절의 사원.사원번호 = 직급.사원번호 을 봐도 알 수 있다.
explain
select 사원.사원번호, 직급.직급명
from 사원,
직급
where 사원.사원번호 = 직급.사원번호
and 사원.사원번호 between 10001 and 10100;
SQL
복사
rows
SQL문을 수행하려 할 때 접근하는 데이터의 모든 행(row)수를 나타내는 예측 항목.
MySQL내부의 행 정보는 계속 변동되기에 정확하진 않다는 점에 유의해야 하고, 최종 출력 될 행 수가 아니라는 점도 인지해야 한다.
이 rows의 값이 최종 결과 건수와 비교해서 차이가 너무 많이 날 경우 불필요하게 MySQL 엔진까지 데이터를 가져왔다는 의미라서 튜닝의 대상이 될 수 있다.
filtered
스토리지 엔진 → MySQL 엔진으로 가져온 데이터를 대상으로 필터 조건에 따라 필터링 된 건수를 백분율로 계산해 보여주는 항목이다.
예를 들어 where 사원번호 between 1 and 10 조건으로 100건의 데이터 중 10건의 데이터가 필터링 되었다면 filtered의 값은 10(%)가 될 것이다.
filtered 개념도
extra
SQL문을 어떻게 수행 할 것 인지에 대한 추가 정보를 보여주는 항목
세미콜론; 으로 구분해서 여러 정보를 나열한다.
1. Distinct
: 중복이 제거되어 유일한 값을 찾을 때 출력되는 정보.
SQL문에 distinct나 union 구문이 포함된 경우에 해당한다.
2. Using where
WHERE 절의 필터 조건을 사용해서 MySQL으로 가져온 데이터를 추출할 경우에 해당한다.
3. Using temporary
데이터의 중간 결과를 저장하고자 임시 테이블을 생성하겠다는 의미.
DISTINCT, GROUP BY, ORDER BY 같은 구문이 포함된 경우 해당한다.
4. Using index
인덱스만을 읽어 SQL문의 요청사항을 해결할 수 있는 경우를 말한다.
커버링 인덱스(covering index) 방식이라고도 불리며, 인덱스로 구성된 열만 SQL문에서 사용할 경우 이 방식을 활용한다.
5. Using filesort
정렬이 필요한 데이터를 메모리에 올려 정렬 작업을 수행하겠다는 의미.
정렬된 인덱스를 사용할 경우 불필요하지만, 그게 아니라면 정렬을 위해 메모리 영역에 데이터를 올리게 된다. 이 행위는 추가적인 정렬 작업 이기에 인덱스를 활용하도록 튜닝 대상이 될 수 있다.
6. Using join buffer
조인을 수행하기 위해 중간 데이터 결과를 저장하는 조인 버퍼를 사용한다는 의미로
드라이빙 테이블의 데이터에 먼저 접근한 결과를 조인 버퍼에 담고, 조인 버퍼와 드리븐 테이블 간에 일치하는 조인 키 값을 찾는 과정을 수행한다.
이런 과정들이 존재할 경우 해당 정보가 출력된다.
7. Using union/ Using intersect / Using sort_union
type 항목이 index_merge 유형일 경우 두 개 이상의 인덱스를 사용했다는 의미이고, 이 두 개 이상의 인덱스가 어떻게 병합 되었는지에 대한 정보를 extra 항목에서 출력한다.
•
Using union
: 인덱스들을 합집합처럼 모두 결합해 데이터에 접근하는 경우로 SQL문에서 OR 구문으로 작성된 경우에 해당한다.
•
Using intersect
: 인덱스들을 교집합처럼 추출하는 방식으로 SQL문에서 AND 구문으로 작성된 경우
•
Using sort_union
: Using union과 유사하지만 SQL구문에서 OR 구문이 동등 조건이 아닌 경우에 해당한다.
8. Using index condition
필터 조건을 스토리지 엔진으로 전달해 필터링 작업에 대한 MySQL 엔진의 부하를 줄이는 방식으로 스토리지 엔진에서 MySQL 엔진으로 전송하는 데이터양을 줄여서 성능 효율을 높일 수 있는 옵티마이저의 최적화 방식이다.
9. Using index condition(BKA)
Using index condition 방식과 유사하나 데이터 검색을 위해 배치 키 액세스를 사용한다.
참고: 배치키 액세스(Batch Key Access, BKA)란? MySQL에서 제공하는 조인 알고리즘 중 하나로 참조된 테이블의 행을 한 번에 하나씩 찾는게 아니라 인덱스 조건을 사용해 키 값의 그룹 또는 배치를 모아서 이들을 한 번에 찾는다.
데이터 접근이 캐시 효율적이고 디스카 I/O작업도 줄일 수 있지만, MySQL이 테이블을 읽을 때 사용하는 버퍼 크기를 조절해야 할 수도 있다.
10. Using index for group-by
SQL 문에 Group by 구문이다 Distinct 구문이 포함될 때는 인덱스로 정렬 작업을 수행해 최적화를 진행한다. 이 때 Using index for group-by는 인덱스로 정렬 작업을 수행하는 인덱스 루스 스캔일 때 출력되는 부가 정보이다.
11. Not exists
하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될 때 출력 되는 유형
왼쪽 외부 조인이나 우측 외부 조인에서 존재하지 않는 데이터를 명시적으로 검색할 때 발생한다.
예를 들어 다음 쿼리와 같이 SQL 문에서 t1과 t2 테이블의 조건에 일치하는 데이터가 없는 경우 그 값이 NULL이 될 수 있기에 일치하는 행을 하나 찾았으니 검색을 중지하게 되는데 이 상황에서 Not exists가 출력 된다.
SELECT *
FROM t1 LEFT JOIN t2 on (...)
WHERE t2.not_null_column IS NULL;
SQL
복사
튜닝을 언제 해야 할까?
실행 계획을 보고 각 열이 어떤 의미를 가지는지는 이제 알았다.
하지만, 언제 튜닝을 해야 할 지 구분 짓는 것은 쉽지 않다. 그래도 어느정도의 참고는 할 수 있는데 보통 select_type, type, extra 열을 보고 결정할 수 있다.
프로파일링
데이터베이스에서 우리는 수 많은 쿼리들을 실행하며 데이터를 조회하고 변경하고 혹은 삭제한다.
하지만, 이런 쿼리들이 얼마나 자원을 사용하고, 얼마나 시간이 걸리는지 등을 파악해 자원 사용율이나 시간등을 줄이는 최적화 작업이 필요할 수 있다. 특히 대량의 데이터를 다루는 경우 프로파일링은 꼭 필요한 작업이다.
다양한 DBMS에서는 고유의 프로파일링 도구를 제공하는데 MySQL은 위에서 언급한 EXPLAIN말고도 SHOW PROFILES 명령어를 이용해 프로파일링을 수행할 수 있으며, 이와 같은 도구로
•
실행 시간
•
CPU 사용량
•
I/O 통계
등 다양한 성능 메트릭을 얻을 수 있다.
물론 오픈소스나 별도의 프로파일링 도구를 이용할 수도 있다.
다만, 주의할점은 이 프로파일링 도구를 사용하기 위한 러닝커브도 있을 뿐더러 해당 도구를 사용하는 것 만으로도 데이터베이스 성능에 부담을 줄 수 있기에 프로덕션 환경에서는 주의해서 사용해야 한다. 여기서는 SQL문으로 프로파일링을 해보도록 하자.
1. SQL 프로파일링 실행하기
내가 프로파일링 하려는 DB가 프로파일링을 할 수 있는지 확인할 필요가 있다.
MySQL은 기본적으로 비활성화 되어 있어서, 활성화 작업을 해 줄 필요가 있다.
mysql> show variables like 'profiling%';
SQL
복사
해당, 쿼리를 실행시키면 다음과 같이 나올 것이다.
만약 profiling이 ON이라면 상관없지만 OFF일 경우 다음 쿼리로 ON으로 활성화 시켜준다.
set profiling = 'ON'
SQL
복사
이제 다음 쿼리를 실행한 다음 show profiles; 쿼리를 실행해보자.
SELECT 사원번호 FROM 직급 WHERE 사원번호 = 100000;
SQL
복사
IntelliJ 의 datagrip을 사용하기에 기타 다른 쿼리들도 많이 떠있기에 내가 실행한 쿼리를 찾을 필욘 있다.
이제 프로파일링을 할 수 있는데 다음과 같이 쿼리에 조회 범위와 쿼리 식별자를 넣어서 확인할 수 있다. 그럼 각종 상태(status)와 소요시간(duration) 이 나온다.
show profile ${range} for quert ${Query_ID}
SQL
복사
이러한 프로파일링 결과를 해석해보면 다음과 같다.
항목 | 설명 |
starting | SQL문 시작 |
checking permissions | 필요 권한 확인 |
Opening tables | 테이블을 열기 |
After opening tables | 테이블을 연 이후 |
System lock | 시스템 잠금 |
Table lock | 테이블 잠금 |
init | 초기화 |
optimizing | 최적화 |
preparing | 준비 |
executing | 실행 |
Sending data | 데이터 보내기 |
end | 끝 |
query end | 질의 끝 |
closing tables | 테이블 닫기 |
Unlocking tables | 잠금 해제 테이블 |
freeing items | 항목 해방 |
updating status | 상태 업데이트 |
cleaning up | 청소 |
추가적으로 프로파일링 쿼리를 실행할 때 범위(range)를 어떻게 지정하느냐에 따라 출력의 범위를 변경할 수도 있다.
•
ALL : 모든 정보를 표시
•
BLOCK IO : 블록 입력 및 출력 작업의 횟수를 표시
•
CONTEXT SWITCHES : 자발적 및 비자발적인 컨텍스트 스위치 수를 표시
•
CPU : 사용자 및 시스템 CPU 사용 기간을 표시
•
IPC : 보내고 받은 메시지의 수를 표시
•
PAGE FAULTS : 주 페이지 오류 및 부 페이지 오류 수를 표시
•
SOURCE : 함수가 발생하는 파일 이름과 행 번호와 함께 소스 코드의 함수 이름을 표시
•
SWAPS : 스왑 카운트 표시
이렇게 확장된 범위를 통해 출력되는 결과 정보는 다음과 같은 의미를 가진다.
항목 | 설명 |
QUERY_ID | Query_ID |
SEQ | 동일한 QUERY_ID를 갖는 행의 표시 순서를 보여주는 일련번호 |
STATE | 프로파일링 상태 |
DURATION | 명령문이 현재 상태에 있었던 시간(초) |
CPU_USER | 사용자 CPU 사용량(초) |
CPU_SYSTEM | 시스템 CPU 사용량(초) |
CONTEXT_VOLUNTARY | 자발적 컨텍스트 전환의 수 |
CONTEXT_INVOLUNTARY | 무의식적 컨텍스트 전환의 수 |
BLOCK_OPS_IN | 블록 입력 조작의 수 |
BLOCK_OPS_OUT | 블록 출력 조작의 수 |
MESSAGES_SENT | 전송된 통신 수 |
MESSAGES_RECEIVED | 수신된 통신 수 |
PAGE_FAULTS_MAJOR | 메이저 페이지 폴트의 수 |
PAGE_FAULTS_MINOR | 마이너 페이지 폴트의 수 |
SWAPS | 스왑 수 |
SOURCE_FUNCTION | 프로파일링된 상태로 실행되는 소스 코드의 기능 |
SOURCE_FILE | 프로파일링된 상태로 실행된 소스 코드의 파일 |
SOURCE_LINE | 프로파일링된 상태로 실행된 소스 코드의 행 |
