친절한 SQL 튜닝 2장 공부 정리(ing)

2.1.1

데이터베이스 테이블에서 데이터를 찾는 방법은 두 가지다.

• 테이블 전체를 스캔한다. ex) 초등학교를 방문해 '홍길동' 학생을 찾을 때 1학년 1반부터 6학년 맨 마지막 반까지 모든 교실을 돌며 홍길동 학생을 찾는다.
• 인덱스를 이용한다. ex) 교무실에서 학생명부를 조회해 홍길동 학생이 있는 교실만 찾아간다.

-> 둘 중 어느쪽이 빠를까? 홍길동 학생이 많다면 전자가 빠르고, 몇 안되면 후자가 빠르다.

 

인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다.

인덱스 튜닝 방법으로 핵심요소는 크게 두 가지로 나뉜다.

 

1) 인덱스 스캔 효율화 튜닝 - 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것.

ex) 학생명부에서 시력이 1.0~1.5인 홍길동 학생을 찾는 경우

이름과 시력순으로 정렬해 두었다면 소량만 스캔하면 되지만,

시력과 이름순으로 정렬해 두었다면 똑같이 두 명을 찾는데도 많은 양을 스캔해야 한다.

 

2) 랜덤 액세스 최소화 튜닝 - 테이블 액세스 횟수를 줄이는 것.

ex) 시력이 1.0~1.5인 학생은 50명, 이름이 '홍길동'인 학생은 5명일 경우

이름만으로 정렬한 학생명부와 시력만으로 정렬한 학생명부가 따로 하나씩 있다면

이름순으로 정렬한 학생명부를 사용해야 더 효과적이다. 교실 찾아가는 횟수(50번->5번)를 줄일 수 있기 때문.

 

-> 랜덤 엑세스 최소화 튜닝이 성능에 미치는 영향이 더 크다!

SQL 튜닝은 랜덤 I/O와의 전쟁이다.

 

 

2.1.2

데이터베이스에서도 인덱스 없이 데이터를 검색하려면, 테이블을 처음부터 끝까지 모두 읽어야 한다.

반면, 인덱스를 이용하면 일부만 읽고 멈출 수 있다.

즉, 범위 스캔(Range Scan)이 가능하다. 왜? 인덱스가 정렬돼 있기 때문!

 

DBMS는 일반적으로 B*Tree인덱스를 사용한다.

출처: SLENDER ANKLE's 개발블로그

리프블록에 저장된 각 레코드는 키값 순으로 정렬돼 있을 뿐만 아니라 테이블 레코드를 가리키는 주소값, 즉 ROWID를 갖는다. 인덱스 키값이 같으면 ROWID 순으로 정렬된다.

인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위해서다.

 

 

2.1.3

인덱스 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정(정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정)

루트(Root)블록에서부터 시작해 리프(Leaf)블록까지 수직적 탐색이 가능하다.

 

2.1.4

인덱스 수평적 탐색 : 데이터를 찾는 과정(수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안나타날 때까지 인덱스 리프 블록을 수평적으로 스캔)

조건절을 만족하는 데이터를 모두 찾기 위해, ROWID를 얻기 위해 수평적 탐색을 한다.

 

2.1.5

결합인덱스 생성 시 컬럼 배치 순서

 

SELECT 이름, 성별

 FROM 사원

WHERE 성별 = '여자'

   AND 이름 = '유관순'

 

-> 인덱스를 「성별+이름」 순으로 구성하는 것보다(총 사원 50명 중에서 성별='여자'인 레코드 25번의 검사)

인덱스를 「이름+성별」 순으로 구성하는 것이(이름='유관순'인 레코드 2번 검사)

선택도가 낮은 '이름' 컬럼을 앞쪽에 두고 결합인덱스를 생성해야 검사 횟수를 줄일수 있어 성능에 유리하다.

(=> 분포도가 좋은 컬럼을 선두로 두는게 좋음. 성별은 중복 또는 분포도가 낮고 이름같은건 UNIQUE한 경우가 많으니까)

 

DBMS가 사용하는 B*Tree 인덱스는 다단계 구조다.

루트에서 브랜치를 거쳐 리프 블록까지 탐색하면서 '여자'이면서 '유관순'인 첫 번째 사원을 바로 찾아간다.

거기서부터 두 건을 스캔한다. 인덱스를 「성별+이름」 순으로 구성하든 「이름+성별」순으로 구성하든 어느 컬럼을 앞에 두든 일량에는 차이가 없다. 인덱스를 어떻게 구성하든 읽는 인덱스 블록 I/O 개수는 같다.

 

2.2.1

인덱스 기본 사용법은 인덱스를 Range Scan 하는 방법을 의미한다.

인덱스는 정렬돼 있어 찾고자 하는 데이터가 서로 모여있기 때문에 시작점을 찾으면 빠르게 찾을 수 있다.

정렬돼 있더라도 가공한 값이나 중간값(중간에 포함된 값)으로는 스캔 시작점을 찾을 수 없다.

스캔하다가 중간에 멈출 수도 없다. 찾고자 하는 단어들이 흩어져 있기 때문이다.

 

즉, 인덱스 컬럼(정확히 말하면, 선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

-> 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것.

-> 리프 블록 일부만 스캔하는 Index Range Scan을 의미.

 

인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야만 한다. 즉, 일부가 아닌 전체를 스캔하는 Index Full Scan 방식으로 작동한다.

 

2.2.2

"인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용(Range Scan)할 수 없다."

왜? 인덱스 스캔 시작점을 찾을 수 없기 때문이다!!!

 

Index Range Scan에서 'Range'는 범위를 의미한다. 즉, 인덱스에서 일정 범위를 스캔한다는 뜻.

그러려면 '시작지점'과 '끝지점' 이 있어야 한다.

 

2007년 1월에 태어나 학생을 찾으려면 2007년 1월 1일 이후에 태어난 첫 번째 학생을 찾아 거기서부터 순서대로 스캔하다가 2007년 2월 1일 이후에 태어난 첫 번째 학생을 만나는 순간 멈추면 된다.

where 생년월일 between '20070101' and '20070131'

 

년도와 상관없이 5월에 태어난 학생을 찾아본다면 스캔 시작지점과 종료지점을 알 수 없다. 전교생을 다 스캔해야 한다.

인덱스에는 가공되지 않은 값이 저장돼 있는데, 가공된 값을 기준으로 검색하려면 스캔 시작점도 끝지점도 찾을 수 없다. 어디서 스캔을 멈춰야 할지 모른다.

 

'인덱스를 정상적으로 사용한다'는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다.

 

아래와 같은 조건절에는 Index Range Scan이 이 불가능하다. 단, OR 또는 IN 조건절은 옵티마이저의 쿼리변환 기능을 통해 Index Range Scan으로 처리되기도 한다.

where substr(생년월일, 5, 2) = '05'

where nvl(주문수량, 0) < 100

where 업체명 like '%대한%'

where (전화번호 = :tel_no or 고객명 = :cust_nm)

where 전화번호 in (:tel_no1, :tel_no2)

 

 

2.2.3

인덱스를 「소속팀 + 사원명 + 연령」 순으로 구성한다는 의미

= 데이터를 소속팀 순으로 정렬하고, 소속팀이 같으면 사원명 순으로 정렬하고, 사원명까지 같으면 연령 순으로 정렬한다.

 

select 사원번호, 소속팀, 연령, 입사일자, 전화번호

  from 사원

where 사원명 = '홍길동'

-> 이름이 같은 사원이더라도 소속팀이 다르면 서로 멀리 떨어져 있으므로 인덱스를 정상적으로 Range Scan할 수 없다.

이 조건으로 검색하면, 인덱스 스캔 시작점을 찾을 수 없고 리프 블록을 처음부터 끝까지 모두 스캔해야 한다.

인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다!!!

인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 Index Range Scan은 무조건 가능하다.

 

ex)

인덱스 : 기준연도 + 과세구분코드 + 보고회차 + 실명확인번호

 

select * from TXA1234

where 기준연도 = :stdr_year

  and substr(과세구분코드, 1, 4) = :txtn_dcd

  and 보고회차 = :rpt_tmrd

  and 실명확이번호 = :rnm_cnfm_no

 

-> 인덱스 선두 컬럼인 '기준연도'를 조건절에서 가공하지 않았으므로 Index Range Scan이 가능하다.

문제는, 인덱스를 Range Scan 한다고 해서 항상 성능이 좋은 건 아니라는 사실.

 

우리가 흔히 말하는 '인덱스를 탄다'는 표현 = '인덱스를 Range Scan한다'와 같은 의미인 셈.

 

 

인덱스는 「주문일자+상품번호」 순으로 구성됐고, 이 테이블에 쌓이는 데이터량은 하루 평균 100만건이라 가정하자.

아래 조건절은 인덱스 선두 컬럼인 주문일자가 조건절에 있고, 가공하지 않은 상태이므로 인덱스를 Range Scan하는 데 문제가 없다.

SELECT *
  FROM 주문상품
 WHERE 주문일자 = :ord_dt
   AND 상품번호 LIKE '%PING%';
   
SELECT *
  FROM 주문상품
 WHERE 주문일자 = :ord_dt
   AND SUBSTR(상품번호, 1, 4) = 'PING';

위 SQL에서 상품번호는 스캔 범위를 줄이는 데 전혀 역할을 하지 못한다.

첫 번째 SQL은 중간 값 검색, 두 번째 SQL은 컬럼을 가공했기 때문.

따라서 위 조건절을 처리할 때 인덱스에서 스캔하는 데이터량은 주문일자 조건을 만족하는 100만 건이다.

이를 두고, 인덱스를 잘 탄다고 말할 수 있을까?

 

 

2.2.4

인덱스는 정렬돼있기 때문에 소트 연산 생략 효과도 부수적으로 얻게 된다.

 

PK : 장비번호 + 변경일자 + 변경순번

SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
   AND 변경일자 = '20180316'
ORDER BY 변경순번

PK 인덱스에서 장비번호, 변경일자가 같은 레코드는 변경순번 순으로 정렬돼 있다.

위와 같이 장비번호와 변경일자를 모두 '=' 조건으로 검색할 때 PK 인덱스를 사용하면 결과집합은 변경순번 순으로 출력된다.

출력 결과집합은 어차피 변경순번 순으로 정렬되기 때문에 옵티마이저는 SQL에 ORDER BY가 있어도 정렬연산을 따로 수행하지 않는다. 내림차순(Desc) 정렬도 마찬가지.

 

 

2.2.5

"인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다"에서 말하는 '인덱스 컬럼'은 대개 조건절에 사용한 컬럼을 말한다. 그런데 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있다.

 

아래 SQL도 정렬 연산을 생략할 수 있다.

SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
ORDER BY 변경일자, 변경순번

 

아래 SQL은 정렬연산을 생략할 수 없다.

인덱스에는 가공하지 않은 상태로 값을 저장했는데, 가공한 값 기준으로 정렬해 달라고 요청했기 때문이다.

SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
ORDER BY 변경일자 || 변경순번

 

PK : 주문일자 + 주문번호

아래 SQL은 SORT ORDER BY 연산이 나타난다. 왜?

SELECT *
  FROM (
    SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
      FROM 주문 A
     WHERE A.주문일자 = :dt
       AND A.주문번호 > NVL(:next_ord_no, 0)
    ORDER BY 주문번호
 )
 WHERE ROWNUM <= 30

ORDER BY절에 기술한 '주문번호'는 TO_CHAR 함수로 가공한 주문번호를 가리키기 때문이다.

해결방법은 ORDER BY절 주문번호에 A(주문 테이블 Alias)를 붙여주기만 하면 된다.

( ORDER BY 주문번호 -> ORDER BY A.주문번호 )

 

 

2.2.6 SELECT-LIST에서 컬럼 가공

인덱스 : 장비번호 + 변경일자 + 변경순번

SELECT MIN(변경순번)
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
   AND 변경일자 = '20180316'

위와 같이 변경순번 최소값을 구할 때도 정렬 연산을 따로 수행하지 않는다.

수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째 읽는 레코드가 바로 최소값이기 때문.

MAX(변경순번)의 경우도 마찬가지. 가장 오른쪽 지점으로 내려가 첫번 째 읽는 레코드가 바로 최대값이다.

 

SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)

-> 정렬 연산을 생략할 수 없다. 인덱스에는 문자열 기준으로 정렬돼 있는데, 이를 숫자값으로 바꾼 값 기준으로 최종 변경순번을 요구했기 때문.

 

SELECT NVL(TO_NUMBER(MAX(변경순번)), 0)

-> 정렬 연산 생략 가능.

 

 

2.2.7