SQL 튜닝 공부 (1장 SQL처리과정과 I/O, 2장인덱스 기본) 정리
1장 SQL처리과정과 I/O
SQL은 구조적 질의 언어다.
옵티마이저가 프로그래밍을 대신해주는 것
DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 것을 최적화 라고 한다.
1. SQL 파싱
사용자로부터 SQL을 전달받으면 SQL파서가 파싱한다.
파싱 트리 생성
syntax 체크
semantic체크
2. SQL 최적화
다양한 실행계획을 생성해서 효율적인 하나를 찾는 것
3. 로우 소스 생성
실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅
SQL 옵티마이저
서버 프로세스가 가진 함수(백그라운드 프로세스 아님)
DBMS 핵심 엔진
실행계획 생성
예상비용 산정
최저비용 선택
SQL옵티마이저 힌트
+ 사용
인자 사이에 콤마는 가능하지만 힌트와 힌트 사이에는 불가능
스키마명까지 붙이면 안됨
테이블 별명이 있다면 별명을 사용해야 함
캐시에 있는 것을 사용 => 소프트 파싱
캐시에 없을 경우 처음부터 만드는 것 => 하드 파싱
라이브러리 캐시
한 번 사용하고 버리기 아깝고 비용이 많이드는 프로시저 같은 것들을 담아두기 위한 공간
SQL 소문자, 대문자, 힌트, 스페이스바 등으로 같은 쿼리지만 다른걸로 인식하고 라이브러리 캐시 공간을 잡아먹는다.
SQL이 느린이유 => 디스크 I/O 때문
테이블 스페이스 -> 세그먼트 -> 익스텐드 -> 블록
오라클 블록 지원 => 2KB, 4KB,16KB, 32KB(OS에 따라)
MS SQL => ONLY 8KB
시퀀셜 액세스
차례대로, 순차적으로 스캔
랜덤 액세스
무작위 스캔, 블록씩 접근
논리적 I/O
SQL을 처리하는데 발생한 총 블록
SQL 성능을 향상을 위해 줄여야 함
SQL 튜닝을 통해 I/O를 줄여야 함
물리적 I/O
디스크상에서 발 생한 총 블록, 메모리 I/O보다 10000배쯤 느림
DB 버퍼캐시에서 찾지 못해 디스크에서 찾을 경우
시스템 상황에 따라 달라지는 외생변수
싱글블록 I/O
한 번에 한 블록씩 메모리에 적재
일반적으로 인덱스와 테이블 블록 모두 싱글블록 사용
멀티블록 I/O
한 번에 여러 블록씩 메모리에 적재
많은 양 처리에 효율적
한 번에 1MB처리가 최대
테이블 풀 스캔
시퀀셜 액세스 + 멀티 블록 I/O 방식
대량 데이터 적합
인덱스 레인지 스캔
랜덤 액세스 + 싱글 블록 I/O방식
소량 데이터 적합
버퍼 락
서로 다른 프로세스가 경합하여 데이터 정합성에 위반되지 않기 위해 오라클이 하는 것
2장 인덱스 기본
수직적 탐색
인덱스 스캔 시작지점을 찾는 것
루트 부터 시작
조건에 맞는 첫번째 레코드를 찾는다.
수평적 탐색 => 데이터를 찾는 것
양방향 연결 리스트 구조
좌에서 우로, 우에서 좌로 수평탐색 가능
조건에 만족하는 모든 데이터를 찾는다.
ROWID를 찾는다.
인덱스 튜닝 핵심요소 2가지
1.인덱스 스캔 효율화 튜닝
2.랜덤 액세스 최소화 튜닝
본질은 랜덤 I/O를 줄이는 것!
*인덱스 컬럼을 가공하면 인덱스 레인지 스캔을 할 수 없다. => 인덱스 스캔 시작점을 찾을 수 없기 때문이다.
따라서 조건절에서 좌변 인덱스 컬럼을 가공해서 사용하면 안된다.
인덱스 레인지 스캔을 하기 위해서 인덱스 선두 컬럼이 조건절에 가공되지 않은 상태로 있어야한다.
인덱스를 탄다 == 인덱스 레인지 스캔 처리한다.
인덱스 레인지 스캔이 가능한 것은 데이터가 정렬 돼있기 때문이다.
SQL쿼리에 ORDER BY가 있더라도 인덱스가 정상적으로 탔다면 ORDER BY 가 실행계획에 보이지 않는다.
MIN 함수 => 최하위 가장 왼쪽 부터 탐색
MAX 함수 => 최하위 가장 오른쪽 부터 탐색
*오라클에서 문자형과 숫자형이 만나면 숫자형이 무조건 이긴다. => 숫자형 컬럼 기준으로 문자형을 변환시킨다는 뜻 단, LIKE 조건은 문자형 기준이기 때문에 숫자형을 비교할 경우 숫자형은 문자형으로 바뀐다.
*오라클에서 날짜형과 문자형이 만나면 날짜형이 이긴다.
자동 형변환 규칙
DECODE문을 쓰면
DECODE(1,2,3,4...) 인자값이 들어가는데
1이 2이면 3의 결과를 아니면 4의 결과를
만약 3이 문자형이고 4가 숫자형이라면 4는 문자형으로 반환한다.
즉 3의 기준으로 값을 반환한다.
+ 3을 NULL로 할 경우 데이터 타입은 VARCHAR2다
자동 형변환 규칙때문에 개발자가 형변환을 안해도 옵티마이저가 자동으로 한다. 그렇기 때문에 형변환을 사용하지 않는다고 연산 횟수가 줄어드는 것도 아니다.
인덱스 레인지 스캔
인덱스 루트부터 리프 블록까지 수직적으로 탐색하고 필요한 범위만 스캔한다.
인덱스 풀 스캔
수직적 탐색 없이 인덱스 리프블록부터 수평적으로 처음부터 끝까지 전체 스캔한다. (테이블 풀 스캔이 부담될 경우 옵티마이저는 인덱스 풀 스캔을 선택한다.)
인덱스 유니크 스캔
수직적 탐색으로만 데이터를 탐색한다.유니크 인덱스를 = 조건으로 탐색한다. ( 데이터를 한 건이라도 찾는 순간 종료한다. )
인덱스 스킵 스캔
조건절에서 인덱스 선행 컬럼이 빠졌을 때 선행 컬럼의 딕셔너리 밸류 개수가 적고 후행 컬럼의 딕셔너리 밸류 개수가 많을 경우 사용된다. ( 딕셔너리 밸류가 적은 컬럼 예) 성별, 딕셔너리 밸류가 많은 컬럼 예) 접수번호 )
스캔 방식을 유도하거나 방지할 때 사용되는 힌트
Index_ss, no_index_ss
인덱스 패스트 풀 스캔
인덱스 풀 스캔보다 빠른 것
인덱스 트리구조를 무시하고 멀티블록 I/O방식으로 스캔
인덱스 레인지 스캔 디센딩
인덱스 레인지 스캔이랑 동일하지만 방향이 다른 것
인덱스를 뒤에서부터 앞으로 스캔하기 때문에 내림차순 정렬 효과를 볼 수 있음
1~2장 정리 끝!