Join 알고리즘
Join 시 여러 테이블에 접근해야 하는데, 이때 접근하는 우선순위를 정합니다.
Driving, Driven TB
Driving TB (
== Outer TB): 먼저 접근하는 TB
Driven TB (== Inner TB): Driving TB 접근 이후 접근하는 TB
ex) Driving TB: 학생, Driven TB: 비상연락망
SELECT 학생.학번, 학생.이름
비상연락망.관계, 비상연락망.연락처
FROM 학생
JOIN 비상연락망
ON 학생.학번 = 비상연락망.학번
WHERE 학생.학번 IN (1, 100);Nested Loop Join (중첩 루프 조인)
MySQL에서 실행계획을 보면 nested loop join 이 많이 보입니다.
업무에 바로 쓰는 SQL 튜닝(1) 에서 설명한 것과 같이 MySQL은 nested loop join을 주로 사용하기 때문입니다.
Nested Loop Join이란, Driving TB 데이터 1건 당 Driven TB을 반복해서 조회하는 것 입니다
ex)
- 학생 TB(Driving TB)에는 학번 1 ~ 100 을 가지는 데이터가 100건 있습니다
- 비상연락망 TB(Driven TB)에는 1000건의 데이터가 있습니다
- 두 테이블에는 인덱스가 걸려있지 않습니다
위의 sql을 실행하면 몇 번 데이터에 접근할까요?
- Driving TB에서 학번이 1인 데이터를 찾는다 (
100) - Driven TB에서 학번이 1인 데이터를 찾는다 (
1000) - Driving TB에서 학번이 100인 데이터를 찾는다 (
100) - Driving TB에서 학번이 100인 데이터를 찾는다 (
1000)
-> 총 2,200 번 데이터에 접근한다
하지만, 보통의 TB에는 자주 사용되는 조건에 대해 index를 걸기 때문에 index를 건다면 데이터 접근 횟수가 줄어들 것 입니다.
- 기본키: 클러스터형 인덱스
- 나머지: 비고유 인덱스 (랜덤 액세스)
오브젝트 스캔
- 테이블 스캔: 인덱스를 거치지 않고 바로 디스크에 위치한 TB 접근
- 인덱스 스캔: 인덱스를 통해 TB 데이터를 찾아가는 것
회사에서 SQL 튜닝 요청할 때, 혹은 쿼리 성능을 파악하면서 아래의 용어들을 많이 사용하고 듣습니다.
테이블 풀 스캔
인덱스를 타지 않고 테이블에서 바로 데이터를 훑는 방식입니다
- where 조건에 인덱스 탈 컬럼이 없을 때, 전체 데이터 대비 대량의 데이터가 필요할 때 사용
- 인덱스 없이 사용하는 유일한 방식
-> 당연히 모든 데이터를 읽어야 하니 성능 측면에서 좋지 않습니다
인덱스 풀 스캔
인덱스를 처음부터 끝까지 수행하는 방식입니다
- 조건절 선두에 인덱스 선두 컬럼이 없을 때 사용 (참고)
- 테이블 보다는 적은 양이므로 성능상으로 인덱스 풀 스캔 > 테이블 풀 스캔
-> 하지만 이것도 검색 범위를 줄여야 함
인덱스 범위 스캔
인덱스를 범위 기준으로 스캔한 뒤, 결과를 바탕으로 테이블을 찾아가는 방식입니다
- SQL문에
BETWEEN+AND구문이거나,<, >, LIKE구문 등 비교연산 및 구문에 포함될 때 인덱스 범위 스캔 수행 - 좁은 범위에서는 효율적이나, 넓은 범위에서는 비효울적
인덱스 고유 스캔
기본키, 고유인덱스로 테이블에 접근하는 방식입니다
- where절에
eq(=)조건으로 작성 - 해당 join열이 인덱스의 선두로 지정되었을 때 사용
-> 스캔 방식 중 가장 효율적인 스캔 방법
디스크 접근
- page: 데이터를 검색하는 최소단위
- page 단위로 데이터 읽고 쓰기 수행
- MySQL은 원하는 데이터를 찾기 위해 데이터가 저장된 storage의 page에 접근
시퀀셜 액세스
물리적으로 인접한 page를 차례로 읽는 순차 접근 방식입니다
- 테이블 풀 스캔에서 활용. 인접한 page를 여러개 읽는 다중 page 읽기 방식으로 수행
- disk header 움직임을 최소화 하여 작업시간, 리소스 점유 비용을 줄일 수 있다
랜덤 액세스
물리적으로 떨어진 page들에 임의로 접근하는 임의 접근 방식입니다
- page의 물리적 위치를 고려하지 않고 접근
- 데이터 접근 수행이 오래걸린다
- 최소한의 page에 접근할 수 있도록 접근범위를 줄이는 것이 필요
조건 유형
where 조건문 기준으로 데이터가 저장된 디스크에 접근하게 됩니다
액세스 조건
맨 처음 disk에서 검색하는 조건. SQL 튜닝에서 가장 중요한 핵심 사항입니다
- 오브젝트 스캔 유형에서 어떤 스캔 방식을 활용할지 정합니다
- 옵티마이저는 where절의 특정 조건문을 이용해 소량의 데이터를 가져오고, 인덱스를 통해 시간 낭비를 줄이는 조건문을 선택합니다
- storage엔진의 데이터에 접근 하고, MySQL 엔진으로 데이터를 가져옵니다
ex)
SELECT *
FROM TAB
WHERE ID = 1 -- ID에 인덱스가 걸려있음
AND CODE = 'A';ID = 1조건: 액세스 조건 -> 일부 데이터에 접근하게 된다
만약 CODE = 'A'를 액세스 조건으로 선택했다면, 많은 양의 데이터에 접근해야 한다
필터 조건
disk에서 가져온 데이터에서 추가로 추출하거나 가공하는 연산하는 조건입니다
- 액세스 조건을 이용해 MySQL로 데이터를 가지고 오고, 그 데이터를 기준으로 불필요한 데이터를 제거하거나 가공하는 조건
ID = 1조건으로 가져온 데이터에서 `CODE = 'A' 로 필터링 한다- 필터 조건에 따라 필터링할 데이터가 없는 경우가 훌륭한 SQL이라 할 수 있다 (I/O가 적어야 성능적으로 효율적이기 때문)
응용 용어
선택도 (selectivity)
열의 where 조건절에 따라 선택되는 데이터 비율
선택도 = 선택한 데이터 건수 / 전체 데이터 건수
- 중복되는 데이터가 많다 -> "선택도가 높다"
- " 선택도가 낮은" 열에 인덱스를 걸어야 데이터를 빨리 찾을 수 있음
ex) 학번: 선택도가 낮음 / 성별: 선택도가 높음
카디널리티 (cardinality)
하나의 데이터 유형으로 정의되는 데이터 행의 갯수
카디널리티 = 전체 데이터 건수 * 선택도
- 중복도가 높다 -> 카디널리티 낮다
- 중복도가 낮다 -> 카디널리티 높다
ex) 주민등록번호: 카디널리티 높음 / 이름: 카디널리티 중간 / 성별: 카디널리티 낮음
hint
데이터베이스가 데이터를 빨리 찾을 수 있게 추가 정보를 전달하는 것
ex) USE INDEX
SELECT 학번, 전공코드
FROM 학생 /*! USE INDEX (학생_IDX01) */
WHERE 이름 = '유재석';SELECT 학번, 전공코드
FROM 학생 USE INDEX (학생_IDX01)
WHERE 이름 = '유재석';-> 학생_IDX01 인덱스 를 사용해서 데이터를 찾아주세요
- 만약
학생_IDX01인덱스가 삭제된다면 오류 메세지가 출력된다 (오라클의 경우엔 무시됨) - 힌트를 작성해도 옵티마이저가 비효율적이라고 생각이 들면 무시될 수 있다
