2021. 3. 12. 22:35ㆍ카테고리 없음
1. Title (주제) :
자주 쓰일법한 SQL 쿼리 튜닝법 - 기초편
2. Why (왜) :
자주쓰이는 SQL 문을 효율적으로 쓰기 위함이다.
3. How (어떻게) :
친절한 SQL 튜닝책을 보고 튜닝을 위한 기초를 닦을 것이다.
주제에 앞서
기본부터 정리해야할 필요성을 느꼈다.
SQL (Structed Query Language) :구조적 질의 언어
- 원하는 결과집합을 구조적,집합적으로 선언함지만, 그결과집합을 만드는 과정은 절차적일 수밖에 없다.
즉, 프로시저가 필요한데 옵티마이저가 프로그래밍을 대신해주는 셈이다.
DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행가능한 상태로 만드는 전과정을 'SQL최적화'라고 한다.
옵티마이저 : 사용자가 요청한 SQL을 가장효율적이고 빠르게 수행할 수 있는 최적의 처리경로를 선택해주는 DBMS의 핵심엔진을 말함
옵티마이저의 최적화 단계
1) 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아낸다
2) 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
3) 최저 비용을 나타내는 실행계획을 선택한다.
옵티마이저 힌트 : 옵티마이저가 항상최선의 선택을 하는 건 아니기 때문에 업무 특성을 활용해 개발자가 직적 더 효율적인 액세스 경로를 찾아낼 수도 있다. 이럴떄 옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수있다.
힌트 사용법은 /*+ 힌트 */*
예시
select /*+ordered usenl(c) */
e.사원명,c.고객명,c.전화번호
from 사원 e , 고객 c
where e.입사일자 >="19960101"
and c.관리사원번호 = e.사원번호예시와 같이 ordered 는 from 절에 나열된 순서대로 조인하되, usenl : NL 조인으로 하라는 것이다.
다만, 액세스방식 같이 기재하지않은 것은 옵티마이저에게 맡기겠단 의미다.
어떤 방식이 옳은지는 애플리케이션 환경에 따라 다르다.
다만,기왕에 힌트를 쓸거면 빈틈없이 기술해야한다.
-> 그렇담 어떤 시스템에 어떤 힌트를 써야 좋은 방법일까?
인덱스
인덱스는 큰테이블에서 소량 데이터를 검색할떄 사용한다.
(반대로 대량 데이터를 검색한다면 Full scan 이 더 낫다.)
인덱스 튜닝법 2가지
1) 인덱스 스캔 효율화 (sorting 잘되있다면 필요한 부분만 빠르게 찾는다)
2) 랜덤 엑세스 최소화 튜닝
( 검색조건에 더 최적화된 명부 선택 )
ex) 홍길동인 시력 1.0~1.5 를 찾는다.
홍길동 3명 시력 1.0~1.5 15명이라면
A 명부: 이름으로 정렬된 명부
B 명부 : 시력으로 정렬된 명부
이중 A 명부가 더 효율적이다.
DBMS 는 일반적으로 B*Tree 인덱스를 사용한다.
키값순으로 정렬되어있다.
ROWID : 테이블 레코드를 가리키는 주소값
인덱스 수직적 탐색: 조건을 만족하는 첫번째 레코드를 찾는 과정이다. (갈림길의 표지판 역할)
인덱스 수평적 탐색: 데이터를 찾는 과정
1) 조건절을 만족하는 데이터를 모두 찾기 위해서
2) ROWID를 얻기 위해서다.
리프블록은 양방향 연결리스트 구조다.
앞뒤 블록에 대한 주소값을 갖는다.
인덱스를 Range Scan 할 수 없을때
Range Scan 을 하려면 시작지점과 끝점을 알아야한다.
부분만 봐야하기때문이다.
즉, DB가 내가 찾으려는 조건으로 정렬되어 있지않으면
어디서 시작하고 끝내야 할지 모른다. 때문에
ex)생년월일로 정렬된 학생들을
Range Scan
가능: 2007년 5월 생을 찾으라면
Root -> 2007년 -> 2007년 5월 ~ 6월이 나오면 그 전까지로 산정해서 산출 하면됨
불가능 : 몇년도인진 몰라 5월생 찾아.
스캔 시작점과 끝점을 알수없다.
+ 소요시간 : like "대한%" < "%대한%"
'대한' 으로 시작하는 값은 특정 구간에 모여있어 Range scan 이 가능하지만,
'대한'을 포함하는 값은 전체에 흩어있어 Range Scan 이 불가능하다.
'인덱스를 정상적으로 사용한다'는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터
스캔하다가 중간에 멈추는 것을 의미한다.
인덱스는 항상 키 기준으로 정렬되어 있다.
따라서, 장비번호+변경일자+변경순번을 키로 갖는 테이블은
같은 장비번호 변경일자 시에 변경순번으로 sort되어 저장되어있다.
이때, 옵티마이저는 SQL에 orderby 가있어도 정렬연산을 수행하지 않는다.
PK 인덱스를 스캔하면서 결과집합은 어차피 변경 순번 순을 정렬되기 때문이다.
미리 자주쓰이는 인덱스를 만들순없나?
Index Full Scan 은 데이터 검색을 위한 최적의 인덱스가 없을때 차선으로 선택된다.