인덱스가 Id,  ch_date, ch_order 순으로 생성되어 있을 경우 MIN 값을 구해도 별도의 정렬연산을 수행하지 않는다. 수직적 탐색을 통해서 가장 왼쪽지점에서 보는 최소 값이 바로 구하고자 하는 값이기 때문이다.


1
SELECT MIN(ch_date) FROM scott.SORT_TEST WHERE ID = ‘C’;
cs

MAX의 경우도 마찬가지이다. MIN과 다른 점은 왼쪽에서 찾는게 아니라 가장 오른쪽에 있는 데이터를 찾는다는 점이다.

1
SELECT MAX(ch_date) FROM scott.SORT_TEST WHERE ID = ‘C’;
cs

그래서 두 개의 실행계획을 살펴보면 인덱스 리프 블록의 왼쪽(MIN) 또는 오른쪽 (MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다.

1
SELECT MAX(TO_DATE(ch_date)) FROM scott.SORT_TEST WHERE ID = 'C';
cs

이 경우에는 MAX일지라도 ch_date가 내부적으로 변경을 한 뒤에 최대값을 찾기 때문에 정렬을 한뒤에 진행이 가능하다.


하지만 이걸 반대로 바꿔서 진행하면 최대 값을 찾고 그 값을 TO_DATE()로 변경하기 때문에 큰 문제 없이 FIRST 항목만 찾게된다.

출처 : 친절한 SQL 튜닝

  1. 2019.03.30 14:54

    비밀댓글입니다

인덱스 Range Scan이 가능하려면 가공하려는 컬럼의 데이터가 정렬되어 있어야 가능하다. 인덱스는 그렇기 때문에 정렬이 되어있다. 그래서 인덱스를 사용하는 이유가 있다. 

테이블 생성

1
2
3
4
create table sort_test (
id char,
ch_date date,
ch_order varchar(10));
cs


제약조건 추가

1
ALTER TABLE SCOTT.SORT_TEST ADD CONSTRAINT ODER_PRIMARY PRIMARY KEY (ID,CH_DATE,CH_ORDER);
cs


데이터


실행계획

PK를 기준으로 알아서 정렬이 되어있다. ( ID -> CH_DATE -> CH_ORDER 순서대로)


만약 별도로 order by를 sql에 지정한다고 해도 옵티마이저는 sort order by를 진행하지 않는다. 왜냐면 이미 pk 인덱스 생성시에 결과 집합에 대한 order by가 된 상태로 진행되기 때문이다.


만약 이렇게 정렬 연산을 생략가능하게 구성되어 있지 않다면 SORT ORDER BY가 추가 될 것이다.

인덱스에서 값을 찾을 때 인덱스 리프 블록에서 각 블록들은 더블 링크드 리스트로 연결되어있다. ASC 정렬인 경우에는 왼쪽에서 오른쪽 DESC인 경우에는 오른쪽에서 왼쪽으로 진행된다. 

각 DESC (내림차순)으로 사용하도록 쿼리를 작성하고 실행계획을 확인해도 별도의 SORT 작업은 진행하지 않는것을 알 수있다.

ORDER BY를 이용한 정렬 컬럼 가공
인덱스에서 사용되는 컬럼을 가공하면 INDEX가 타지 않는다고 알고있다. 근데 앞에서 살펴본 부분은 where조건에서 컬럼을 가공한 경우였다.

이번에는 인덱스에서 선언한 구조와 반대되는 쿼리를 짜보자. 위의 쿼리에서 PK 인덱스는 ID,CH_DATE,CH_ORDER 순서로 인덱스를 만들었다. 그렇기 때문에 ID -> CH_DATE -> CH_ORDER 순서로 정렬을 하면서 데이터를 찾는다.

하지만 다음과 같은 쿼리는 어떨까?

생성된 인덱스와 다른 기준으로 CH_DATE -> CH_ORDER 으로 정렬을 진행하였기 때문에 다시 정렬연산이 필요로 하게된다.


정리하면 인덱스 생성시에 sort가 되기 때문에 order by를 하여도 별도의 sort 연산을 하지 않는다.


인덱스 Range Scan이 되기 위한 선행 조건


학교이름, 나이, 이름, 주소로 구성된 테이블이 있다고 가정해보자.



빠른 검색을 위해서 인덱스를 학교 이름, 나이, 이름으로 구성해서 만들었다고 가정해보자.


CREATE  INDEX SCOTT.student_idx

ON SCOTT.STUDENT_TEST ("SCHOOL_NAME" ASC,"AGE" ASC,"NAME" ASC);


인덱스 구성의 순서로 인해 학교순으로 정렬하고, 나이로 정렬하고, 이름으로 정렬해서 데이터를 찾는다.


그렇기 때문에 이름을 조건으로 데이터를 검색하였을 때 결국 모든 리프노드를 다 검색해야한다.





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



그렇다면 만약 인덱스에 사용된 컬럼이 가공 되었으면 인덱스 Range Scan이 지정이 되지 않는거가??


다음 예를 살펴보자.


SELECT * FROM student_test WHERE name = '정철' and substr(SCHOOL_NAME, 0, 1) = :SCHOOL_NAME;


위와 같이 쿼리를 수행하려고 할 때 아래와 같이 인덱스를 구성해보자.


CREATE  INDEX SCOTT.student_idx

ON SCOTT.STUDENT_TEST ("NAME" ASC,"AGE" ASC,"SCHOOL_NAME" ASC);


그리고 실행쿼리에 대해 실행계획을 확인해 보면 인덱스 Range Scan이 가능한 것을 알 수 있다.


인덱스에 사용되는 컬럼이 조작되면 인덱스 Range Scan이 되지 않는다고 알고 있었는데 의아할 수도 있다.





인덱스 Range Scan이 가능한 이유는 인덱스를 구성하는 첫 번째 컬럼이 가공되지 않았기 때문이다.


인덱스 Range Scan을 사용하기 위해서는 인덱스를 사용하는 첫 번째 컬럼이 가공되지 않으면 사용이 가능하다.





인덱스를 타기만 하면 튜닝이 종료되는건가??


대부분의 개발자가 실행계획 확인 없이 SQL 작성한다. 그리고 인덱스 Range Scan이 지정된 것만 확인하면 추가적으로 확인하지 않는다.


위의 테이블에서 인덱스를 다음과 같이 지정해보자.


CREATE  INDEX SCOTT.student_idx

ON SCOTT.STUDENT_TEST ("NAME" ASC,"SCHOOL_NAME" ASC);


그리고 학생 검색을 위해 다음 쿼리 두 개를 살펴보자.


SELECT * FROM student_test WHERE name = '정철' and substr(SCHOOL_NAME, 0, 1) = :SCHOOL_NAME;

 SELECT * FROM student_test WHERE name = '정철' and SCHOOL_NAME LIKE :SCHOOL_NAME;

두 개의 쿼리 모두 인덱스 Range 스캔을 사용하지만 조건에 사용된 컬럼이 가공되었기 때문에 성능에 문제가 있다.


이를 해결하는 방법은 추후에 공부해보자.

  1. 동구 2018.07.08 18:41

    실행계획을 믿었다가 프로덕트 DB에서 속도가 안나오는거 보고 예전엔 많이 의아했는데....
    통계작업으로 인한 수치와 옵티마이저에 의해 언제든지 달라질 수 있다는 것 ㅜ

우리가 색인을 통해 단어를 찾는 순간을 생각해보자.

ㄱ.

가나

가방 장식

가시 방석

ㄴ.

나방

나방 나무

누에고치

나무 장식

누나


여기서 누에고치라는 단어를 찾을 때, 위에서 순차적으로 진행한다고 가정하였을 때 큰 어려움 없이 발견할 수있다. 이 방식을 Index Range Scan이라고 한다.

반대로 장식이 포함된 단어를 찾아보자. 찾기 어려운 건 아니여도 모든 색인을 전부 확인해봐야한다. 이렇게 모든 색인을 다 확인하고 나서 찾을 수 있는 방식을 Index Full Scan 방식이라고 한다.

그렇기 때문에 인덱스의 기준이되는 데이터 즉 컬럼을 가공하게되면 Range Scan이 불가능해진다. 정리하면 인덱스 기준이 가공되면 인덱스 스캔의 시작점을 찾는 수직적 탐색이 불가능해지기 때문이다.


몇 가지 쿼리를 예로 들어보자.


1
2
3
4
create table student (
name varchar2(255),
birth date);
cs


학생 테이블이 있을 때 생일이 1월로 시작하는 사용자를 찾기위해 다음과 같은 쿼리를 사용한다고 가정해보자.


1
select * from student where substr(birth, 52= '01';
cs



어디서 부터 스캔을 멈춰야할지 인덱스 스캔을 할 수가 없다. 또 다른 예를 들어서 확인해보자.



1
2
3
select * from student where nvl(birth, CURRENT_DATE) < '2018-08-12';
 
select * from student where name like '%edu%';
cs



어디서 인덱스 스캔을 멈추어야하는지 알 수없기 때문에 마찬가지로 인덱스 스캔을 진행할 수가 없다.





OR이나 IN절의 경우에는 내부적으로 UNION ALL으로 내부적으로 나뉘어서 각자 자신의 쿼리를 사용하여 인덱스를 사용할 수 있다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
where 전화번호 in ( tel_no1, tel_no2 )
 
 
 
                |
// 아래와 같이 변경
                ▽
 
 
 
select * from 고객 where 전화번호 = tel_no1
 
union all 
 
select * from 고객 where 전화번호 = tel_no2

cs


SQL 튜닝에서 사용되는 인덱스

인덱스 구조는 수평적 탐색과 수직적 탐색을 이룬다. 이것을 이해하고 나면 인덱스 구조에 대해 그림이 명확해진다.

인덱스를 우리가 중심적을 공부하는 이유는 SQL 튜닝에 초점이 맞쳐져있다. SQL 튜닝을 통해 원하고자 하는 데이터를 빠르게 얻기 위해서 인덱스에 대해 공부하는데 SQL을 사용하여 데이터를 찾는 방법은 이미 많이 알려진 두 가지 방식이 있다.

  • 테이블 전체 스캔
  • 인덱스를 이용

 

그럼 Table Full Scan 방식이 아닌 인덱스를 사용하기 위해 적절 한 튜닝의 핵심요소가 무엇인지 살펴보자.

만약 몸무게가 기재되어있는 학생 연명부가 있다고 가정해보자. 학생명과 몸무게로 정렬되어 있을때 학생을 찾기 위한 과정에서 학생을 찾고 몸무게 검색하는 과정이 거친다. 이런과정을 인덱스 스캔이라고 하는데, 이런 인덱스 스캔 과정에서 비용을 줄여야 한다.

그리고 만약 몸무게로만 정렬되어 있을 때 몸무게 접근 후 정렬되어 있지 않은 학생명을 찾기위해서 랜덤 I/O가 발생하는데 이런 랜덤 액세스를 최소화하도록 인덱스를 튜닝해야한다.

인덱스 스캔과 랜덤 액세스 두 가지 중 조금더 중요한 부분을 찾으라 한다면 랜덤 액세스 비용을 줄이는게 더 효율적이다.

위의 예에서 봤을때 시력을 찾아도 해당학생을 찾기위한 과정이 더 비용이 많이 들기 때문이다. 이처럼 SQL튜닝은 랜덤 I/O와의 전쟁이다.

이미지 출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148214&boardConfigUid=9&boardIdx=137&boardStep=1

 

인덱스 구조

인덱스는 대용량 테이블에서 필요한 데이터만 빠르고 효율적으로 액세스하기 위해 사용되는 오브젝트이다.

책에 주제에 대한 페이지 번호가 기재되어 있으면 필요한 내용을 빠르게 찾을 수 있는것처럼 인덱스를 이용하면 Range Scan을 사용하여 필요한 부분을 빠르게 읽을 수있다.

일반적으로 DBMS에서는 BTree 형태의 인덱스를 사용한다. 일반적으로 루트와 브랜치 블록에 있는 데이터는 하위 블록에 대한 주소값을 가지고 있으며, 이 것을 구분하는 키는 하위 블록에 저장된 키값의 범위를 나타낸다. 만약 사용자 ‘정철’ 데이터를 기준이라면 사용자 >= ‘정철’의 데이터는 우측 블록에 위치하는 것을 의미한다.

리프 블록에 저장된 각 레코드는 키값 순으로 정렬되어 있고, ROWID를 가지고 있다. 인덱스 키값이 동일할 경우 ROWID 순으로 정렬된다. ROWID는 데이터 블록주소와 로우 번호가 있기에 인덱스를 스캔하는 이유는 이 ROWID를 찾기 위해서이다. 왜냐하면 ROWID의 데이터 블록주소는 데이터 파일번호와 블록번호(데이터파일 내에서 부여한 상대적 순번)로 구성되어 있고, 로우번호(블록 내 순번)으로 구성되어 있기 때문에 데이터를 찾을 수 있기 때문이다.

 

인덱스 수직적 탐색

정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정으로 인덱스 스캔 시작지점을 찾는 과정이다.

인덱스 수직적 탐색은 루트 블록에서 부터 시작한다. 각 브랜치 블록에 저장된 인덱스 레코드는 하위 블록에 대한 주소값을 가진다. 그 주소값을 이용하여 인덱스를 스캔한다.

인덱스 수직적 탐색은 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동한다. 이 과정을 반복하면서 찾아가는 하위 블록에서 조건을 만족하는 첫 번째 블록을 찾아가는 과정이다.

 

인덱스 수평적 탐색

수직적 탐색을 통해 스캔 지점을 찾고나면 그 후 데이터가 더 안나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다. 즉 데이터를 찾는과정이다.

인덱스 리프 블록끼리는 서로 앞뒤 블록에 대해 주소값을 가지고 있기 때문에 수평적인 탐색이 가능하다. 이런 과정을 통해서 조건절에 만족하는 데이터를 모두 찾고 ROWID값을 얻는다.

결국 수직적 탐색은 수평적 탐색을 시작할 위치를 찾는것이고, 수평적탐색은 그렇게 찾은 위치에서 본격적으로 데이터를 찾는 작업을 시작한다.

 

결합 인덱스 구조와 탐색

두 개 이상의 컬럼을 결합해서 인덱스를 만들 수도 있다. 이런경우에도 찾고자 하는 값과 동일하거나 같은 값을 만나게 되었을 때, 하위 블록으로 이동하는 수직적 탐색을 먼저 진행한다.

만약 고객명과 성별을 하나의 인덱스로 묶어서 탐색한다고 하였을 때와 성별과 고객명으로 묶었을 때 서로 탐색하는 블록은 달라질 수 있다. 하지만 결국 블록을 탐색하는 개수는 동일하다. 서로 컬럼의 순서가 바뀔경우에 인덱스 탐색의 비용이 다를거라는 주장이 있으나 BTree의 경우 평면구조가 아니기 때문에 수직적, 수평적 탐색에서 발생하는 비용은 동일하다.

+ Recent posts