인덱스와 사거블쿼리

인덱스? 

 

INDEX(인덱스) 데이터베이스 에서 데이터 양이 많아지면서 자주 조회하는 컬럼에 인덱스를 적용하곤 한다.

인덱스는 쉽게말해 책으로 비유하자면 앞에 목차부분이 되겠다.

책을 읽는 사람들은 목차를 보고 해당 페이지에 어떤 주제가 있는지 미리 파악할 수 있고,

목차를 보고 원하는 부분을 빠르게 읽을 수 있다. 반대로 생각하면 책양은 적은데 목차가많으면 오히려 목차로 찾는것보다. 바로 책내용을 찾는게 빠를것이다.  데이터베이스도 마찬가지다. 데이터 양이많으면 인덱스가 좋고 데이터 양이 적은데 인덱스가 걸려 있으면 오히려 성능 낭비라고 보면된다. 

인덱스가 더 궁금하다면 위키백과!

 

 

사거블 쿼리(Search ARGument ABLE, Sargable)

DB 엔진이 인덱스를 잘 활용하려면 쿼리의 서술 논리절(WHERE, ORDER BY, GROUP BY, HAVING 절)이 인덱스를 사용해야 하는데, 이를 사거블 이라는 용어로 표현한다. 별 5개!!

 

별 5개ㅑ

다시말해, 데이터 양이 많아 자주 조회하는 컬럼에 인덱스를 적용했고,

그 인덱스를 잘 활용하려면 사거블 쿼리를 사용해야하는 말이다!!

 

사거블 쿼리(인덱스를 사용하는 쿼리)를 만드는 연산자는 다음 과 같다..

• =

• >, <, >=, <=

• BETWEEN

• LIKE (검색 문자열 앞에 %를 붙이지 않을때)

• IS [NOT] NULL

다음 연산자는 사거블이지만! 성능 향상 목적으로는 사용하지 않는다.

• <>

• IN

• OR

• NOT IN

• NOT EXISTS

• NOT LIKE

*****인덱스를 아예 사용하지 못할 때는 다음과 같다. (이것도 별 5개)

 

• WHERE 절 조건에서 한 개 이상의 필드에 대해 연산하는 함수를 사용하는 쿼리

• WHERE 절 에서 필드에 대해 수치 연산을 하는 경우

• LIKE '%프랭키데용%' 처럼 앞에% 를 사용하는 경우

 

쟈, 그럼 인덱스를 사용하지 못할때의 쿼리를 한번보쟈! (LIKE 쿼리는 생략)

인덱스를 상요못하는 쿼리 (넌 사거블 방식 쿼리)

 

1. WHERE 절 조건에서 한 개 이상의 필드에 대해 연산하는 함수를 사용하는 쿼리

예) 특정 연도에 태어난 직원만 조회하는 쿼리

SELECT 
    EmployeeID,
    EmployName
FROM emp
WHERE YEAR(EmpDOB) = 1990;

( EmpDOB 컬럼은 태어난 연도를 나타내는 컬럼이라 칭하자, 오라클은 YEAR() 함수를 제공하지 않으므로

EXTRACT 처럼 작성해야한다. )

 

1990년 이상 태어난 직원을 조회하는 평범함 쿼리이다. 이제 EmpDOB에 인덱스를 아래와 같이 넣었다고 가정하자

 

CREATE INDEX [EmpDOB]
 ON [emp]([EmpDOB] ASC);

 

해당 쿼리는 (WHERE 절 조건에서 한 개 이상의 필드에 대해 연산하는 함수를 사용하는 쿼리) 에 해당하는 넌사거블 형식의 쿼리이다. 각 로우에서 YEAR 함수를 호출해 비교하기에 인덱스를 사용못한다.

 

해당 쿼리를 인덱스를 사용하게 사거블 방식으로 아래와 같이 고쳐보자.

 

SELECT 
    EmployeeID,
    EmployName
FROM emp
WHERE EmpDOB >= CAST('1990-01-01' AS Date);

 즉, 인덱스가 적용된 컬럼에는 함수를 사용하면 안된다.

 

 

2. WHERE 절 에서 필드에 대해 수치 연산을 하는 경우

예) 직원 급여에 1.10 곱한것이 10000을 넘는 직원

SELECT 
    EmployeeID,
    EmployName
FROM emp
WHERE salary * 1.10 > 10000;

만약 salary 컬럼에 인덱스가 적용 되었다면 해당 쿼리는 넌사거블 쿼리(인덱스를 활용못하는) 이다.

 

아래 쿼리와 같이 수정하자.

SELECT 
    EmployeeID,
    EmployName
FROM emp
WHERE salary > 10000/1.10;

절때 인덱스가 적용된 컬럼에는 함수와, 수치 연산을 하면 안된다.!!! 

 

3. 마지막 NULL 컬럼을 찾는 쿼리로 마무리 하겠다.

예) 이름이 Brian인 사람 조회

 

EmployName 컬럼에 인덱스가 적용되었다고 가정하겠다.

위에 언급했듯이 인덱스가 적용된 컬럼에는 함수를 적용하면 안되므로 아래와 같이 작성하자.

SELECT 
    EmployeeID,
    EmployName
FROM emp
WHERE EmployName = 'Brian'
OR EmployName IS NULL;

 

글을 마치며

인덱스를 사용했으면, 인덱스가 사용되는 쿼리인지 안되는 쿼리인지도 살펴보아야 한다.

사거블 쿼리인지 넌사거블 쿼리인지.. 회사 규모가 크다면 이부분은 DBA분이 검토하실지도 모른다... 아마도.,.,ㅎㅎ

 

 

 

출처:

SQL 코딩의 기술 28장( 데이터베이스 엔진이 인덱스를 사용하도록 사거블 쿼리를 작성하자)