3장 SQL 최적화의 원리
1절 옵티마이저와 실행 계획
1. 옵티마이저
- 옵티마이저 : SQL의 실행계획을 수립하고 SQL을 실행하는 DBMS의 소프트웨어
2. 옵티마이저 특징
- 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해 예상 비용 산정
- 여러 실행계획 중 최저 비용 계획 선택
3. 옵티마이저의 필요성
- 옵티마이저는 SQL실행계획을 PLAN_TABLE에 저장 (Oracle)
-> PLAN_TABLE을 조회해 실행계획 확인
MySQL은 실행계획을 보려면 ## EXPLAIN SQL문 ## 으로 가능하다.
#Oracle에서 실행계획 보는 법 DESC PLAN_TABLE; #MySQL에서 실행계획 보는 법 EXPLAIN SELECT ENAME, DNAME, SAL FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.EMPNO IN(SELECT EMPNO FROM EMP WHERE SAL>2000);
위에서 보면 해시 조인을 사용했음을 볼 수 있다.
그렇지만 나는 지금 SQLD 시험을 위해 Oracle의 실행계획을 봐야하므로 당장 MySQL의 실행계획은 필요없다...
2절 옵티마이저 종류
1. 옵티마이저 실행방법
기억할 것은 파싱(PARSING) -> 실행(EXECUTION) -> 인출(FETCH)
2. 규칙기반 옵티마이저
: 15개 우선순위를 기반으로 실행계획 수립
3. 비용기반 옵티마이저
: 총 비용을 계산해 실행 계획 수립
3절. 인덱스
1. 인덱스
: 데이터를 빠르게 검색할 수 있는 인덱스 키를 제공
- 인덱스로 오름차순, 내림차순 정렬 가능
- 하나의 테이블에 여러 인덱스 생성이 가능하고, 하나의 인덱스는 여러 칼럼으로 구성될 수 있음
- 테이블 생성시 적용한 기본키 칼럼은 자동으로 인덱스가 만들어짐
- LEAF BLOCK 에서 인덱스키를 읽으면 ROWID를 사용해 테이블의 행을 직접 읽을 수 있음
<인덱스의 특징 정리>
- 원하는 데이터를 쉽게 찾을 수 있도록 하는 색인과 유사한 개념
- 인덱스는 테이블을 기반으로 선택적으로 생성할 수 있는 구조
- 인덱스의 기본 목적은 '검색 성능의 최적화'
- 검색 조건을 만족하는 데이터를 인덱스를 통해 효과적으로 찾을 수 있음
- DML 작업은 테이블과 인덱스를 함께 변경하므로 느려지는 단점이 발생함
- 인덱스 중 BTREE 인덱스는 EQUAL 조건 뿐 아니라 비교 조건(범위 검색)에도 적용 가능
※ 부정형 비교에는 인덱스 사용 불가
※ LIKE 연산자는 피연산자값을 무조건 문자로 형변환하여 인덱스 사용 불가
<인덱스 종류>
- B-TREE 인덱스 : BRANCH BLOCK(분기 목적)과 LEAF BLOCK(인덱스를 구성하는 칼럼값으로 정렬)으로 구성
- CLUSTERED 인덱스 : LEAF 데이터가 곧 페이지 데이터
- BITMAP 인덱스 : 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하는 구조, 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 데이터 웨어하우스 및 AD-HOC 질의 환경을 위해 설계
2. 인덱스 생성
CREATE INDEX 인덱스명 ON 테이블
##인덱스 생성 CREATE INDEX IND ON EMP (ENAME ASC, SAL DESC);
ENAME은 오름차순으로 정렬하고, SAL은 내림차순으로 정렬하는 인덱스 생성
3. 인덱스 스캔
- INDEX UNIQUE SCAN
- INDEX RANGE SCAN
- INDEX FULL SCAN
3-1. 인덱스 유일 스캔 (INDEX UNIQUE SCAN)
: 인덱스의 키 값이 중복되지 않는 경우, 특정 하나의 인덱스 조회 -> 유일한 인덱스 키를 검색하므로 가장 속도가 빠름
#INDEX UNIQUE SCAN SELECT * FROM EMP WHERE EMPNO = 100;
3-2. 인덱스 범위 스캔 (INDEX RANGE SCAN)
: 특정 범위를 조회하는 WHERE문 사용시 발생
#INDEX RANGE SCAN 예시 SELECT * FROM EMP WHERE EMPNO >= 101;
3-3. 인덱스 전체 스캔 (INDEX FULL SCAN)
: 처음부터 끝까지 전체를 읽어들임 -> 전체를 읽기 때문에 검색 속도가 가장 떨어짐
#INDEX FULL SCAN 예시 SELECT ENAME, SAL FROM EMP WHERE ENAME LIKE '%' AND SAL > 0;
4절 실행계획 읽는 순서
안에서 밖 순으로 읽고 같은 위치면 위에서 아래순으로 읽는 것이 기본
<예제>
1. NESTED LOOPS
2. HASH JOIN
3. TABLE ACCESS (FULL) TAB1
4. TABLE ACCESS (FULL) TAB2
5. TABLE ACCESS (BY ROWID) TAB3
6. INDEX (UNIQUE SCAN) PK_TAB3
이 때 실행 순서는 3-4-2-6-5-1
5절 옵티마이저 조인
1. NESTED LOOP 조인 (빠른 조회에 유리)
: 하나의 테이블에서 데이터를 먼저 찾고(FULL SCAN) 그다음 테이블을 조인하는 방식
∴ 선행 테이블의 크기가 작은 것을 먼저 찾는 것이 중요
- 만약 실행계획에서 FULL ACCESS JOIN을 수행하면 NESTED LOOP의 성능은 떨어짐
- 먼저 조회되는 테이블 = 외부(선행) 테이블 / 나중에 조회되는 테이블 = 내부(후행) 테이블
※ RANDOM ACCESS가 많이 발생하는 방식이 NESTED LOOP 조인
2. SORT MERGE 조인 (배치 작업에 자주 사용)
: 두 테이블을 메모리 공간에 모두 로딩하고 SORT를 수행한 후에 병합 -> 데이터 양이 많아지면 성능 떨어짐
- 정렬할 데이터 양이 너무 많으면 정렬은 임시 영역에서 수행됨 -> 임시 영역은 디스크에 있기 때문에 성능 떨어짐
- EQUI/NON-EQUI(동등/비동등) 조인 모두 사용 가능
- SORT MERGE가 NESTED LOOP보다 효율적일 때 : 기본키, 외래키 관계에서 외래키에 인덱스가 없을 때
3. HASH 조인
: 두 개의 테이블 중 작은 테이블을 HASH 메모리에 로딩하고, 두 개의 테이블의 조인 키를 사용해 해시 테이블 생성
- EQUI(동등)조건에서만 가능
- CPU 연산을 많이 수행한다.
- 대용량 처리에 유리
- 데이터 건수가 적은 테이블을 선행테이블로 두는 것이 유리
- 조인 칼럼의 인덱스가 존재하지 않아도 사용 가능
- 랜덤 액세스에 유리
※ HASH 조인 시에는 선행 테이블이 충분히 메모리에 로딩되는 크기여야함
- 해시 조인의 순서 : 선행 테이블 필터링 -> 해시 테이블 생성 -> 1, 2번 선행 테이블 조건 수행 -> 후행 테이블 필터링 -> 후행 테이블에 해시 적용
드디어 SQLD 2과목까지 모두 복습했다.
맨 마지막에서 본 세 가지 조인 방식은 기출에 자주 나오니 꼭 숙지해야 하고, 인덱스는 지금 복습한 것보다 굉장히 구체적이고 어려운 문항이 많이 나와서 더 자세히 공부할 필요가 있다.
SQLD 복습 정리는 이쯤에서 끝내고, 사실 제일 좋은 단기 자격증 따는 법은 기출문제 반복적으로 풀고 오답 정리하는 것이므로 기출을 열심히 푸는 것을 추천한다.
나는 기출문제를 다 풀고 오답정리를 한 상태에서 애매하게 시간이 남아서 그동안 정리할 겸 포스팅을 한 것인데 나름대로 복습이 된 것 같다.
내일 모레 보는 시험 잘 보고 돌아오자!
