[데이터베이스] - 데이터베이스 실행계획

Jobmania·2023년 8월 7일

데이터베이스

목록 보기

4/4

INDEX를 걸었음에도 조회성능이 나오지 않는 경우가 있을 수 있다.

데이터 베이스 실행 계획 이란?

실행계획은 쿼리 옵티마이저가 데이터를 조회하기 위한 계획
즉, SQL을 데이터베이스에서 어떻게 처리하여 데이터를 가져올 것인가를 수립하는 절차

Query Optimization : 이 단계 에서

쿼리를 분석 : where 검색 조건인지 , join 조건 인지 판단
인덱스 선택 : 각 테이블의 조건과 인덱스 통계정보를 이용, 사용할 인덱스 판단.
조인 처리 : 여러 테이블 조인인경우, 어떤 순서로 테이블을 읽을 지 결정.

실행계획을 확인하는 방법

MySql 워크벤치를 통해 확인 가능.

MySql 예시 테이블

SELECT city.name, city.Population 
FROM city
inner join country
on city.CountryCode = country.Code
where country.GovernmentForm = 'Republic'
order by city.Population DESC
limit 0, 5;

full table scan,index range / full / unique scan인지 시각화하여 보여준다.

인덱스 스캔

인덱스 범위 스캔(index range scan)은 인덱스를 활용하여 특정 범위내 데이터를 검색

주로 WHERE 절의 조건이 범위 연산자 (예: BETWEEN, >, <)를 포함할 때 사용

인덱스 전체 스캔(index full scan) 의 경우 인덱스에 저장된 모든 키값을 읽어오는 경우이다.

주로 정렬을 사용하거나, Distinct 연산을 수행할때 사용

고유 인덱스 스캔(unique scan)은 특정 열에 대한 고유한 값을 검색하는 방법

주로 PRIMARY KEY나 UNIQUE 인덱스를 사용하여 고유한 값의 레코드를 찾을 때 사용하며, 해당 값이 존재하는지 여부를 빠르게 확인할 수 있으며, 고유한 값을 가진 레코드를 찾는데 사용

풀 테이블 스캔

테이블 전체 검색 (Full Tabel scan)은 테이블의 모든 레코드를 처음부터 끝까지 읽어서 조건에 맞는 데이터를 찾는 방법

모든 데이터를 스캔하므로 조건에 일치하지 않는 데이터도 읽어야 하기 때문에 더 많은 I/O 비용이 소요

ROWID SCAN(행 아이디 스캔)

조건절에 ROWID를 직접 명시할 경우
INDEX SCAN을 통해 ROWID를 추출한 후 테이블에 접근할 경우

단일 행 접근이 매우 빠르기 때문에 ROWID SCAN가 유리하다고 판단되면서 ROWID SCAN을 탈 수 있는 상황이라면 유도해주는 것이 좋음.

Explain 사용을 통해 실행계획을 확인하자!

explain
SELECT city.name, city.Population 
FROM city
inner join country
on city.CountryCode = country.Code
where country.GovernmentForm = 'Republic'
order by city.Population DESC
limit 0, 5;

그 중 봐야할 것은 각 항목에 대한 내용이다. 성능을 개선시킬 수 있게 확인할 요소들이다.

1. id

쿼리 내 참조 되는단위 식별 값을 의미
select 단위로 id 식별값이 다르게 부여된다.
조인시 조인되는 쿼리들은 한 단위로 부여된다.

2. select_type : 해당 타입에 대한 설명

SIMPLE : 서브쿼리나 UNION이 없는 단순한 쿼리를 의미한다.
PRIMARY : 서브쿼리나 UNION이 존재할때 가장 외부에 있는 select 문을 의미
SUBQUERY: FROM 절 이외에서 사용되는 서브 쿼리임을 뜻한다.
DEPENDENT SUBQUERY ; SUBQUERY와 같은 유형이지만 자신 기준 외부 select 문에 의존성을 가진 서브 쿼리

의존의 예시

SELECT 
    (SELECT c2.some_column FROM table2 AS c2 WHERE 
    c1.some_column = c2.some_column)
FROM table1 AS c1;

DERIVED : FROM 절에 사용되는 서브 쿼리
DEPENDENT SUBQUERY : 서브쿼리가 바깥쪽 SELECT 쿼리에 정의된 칼럼을 사용 하는 경우
DEPENDENT UNION : 외부에 정의된 칼럼을 UNION으로 결합된 쿼리에서 사용하는 경우
MATERIALZED : IN 절 구문의 서브쿼리를 임시 테이블로 생성한 뒤 조인을 수행
UNCACHEABLE SUBQUERY : RAND(), UUID() 등 조회마다 결과가 달라지는 경우, 캐시할수 없는 경우

select_type : dependent type은 조회시마다, 외부 테이블에 access하게 되므로 성능에 악역향을 미칩니다. Rand함수들을 활용하면 uncacheable이 나오는데, 이 또한 마찬가지입니다.

3. type

system : 테이블에 데이터가 없거나 한 개만 있는 경우
const : 조회되는 데이터가 단 1건일 때
eq_ref : 조인이 수행될 때 드리븐 테이블의 데이터에 PK 혹은 고유 인덱스로 단 1건의 데이터를 조회할 때
ref : eq_ref와 같으나 데이터가 2건 이상일 경우
index : 인덱스 풀 스캔
range : 인덱스 레인지 스캔
all : 테이블 풀 스캔

인덱스 레인지 풀 스캔, 혹은 테이블 풀 스캔을 줄일 수 있는 방향으로 개선해야 합니다.

4. extra

Distinct : 중복 제거시
Using where : WHERE 절로 필터시
Using temporary : 데이터의 중간 결과를 저장하고자 임시 테이블을 생성, 보통 DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우 임시 테이블을 생성
Using index : 물리적인 데이터 파일을 읽지 않고 인덱스만 읽어서 처리, 커버링 인덱스
Using Filesort : 정렬 시

filesort나 group by를 위한 temp 테이블 생성보다 인덱스를 활용하여 sorting/group by를 수행할 수 있다면 성능을 개선

추가적으로 만약 인덱스를 안타는 쿼리등 성능에 관한 방향!

참고해볼만한 책

출처1
출처2
출처3
출처3