SQL 레벨업 03

 

서브 쿼리의 문제점

- 연산 비용 추가

- 즉 select 구문을 또 수행하는 것이기 때문에 서브 쿼리마다 select 구문을 실행해서 데이터를 만들어야 한다.

 

- 데이터 I/O 비용 발생

- 서브 쿼리는 일반 쿼리와 다르게 명시적인 제약 또는 인덱스가 작성된 테이블과 다르게 그러한 메타 정보가 전혀 없다.

- 따라서 옵티마이저가 쿼리를 해석하기 위해 필요한 정보를 서브 쿼리에선 얻을 수 없다.

- 이에 대한 대책으로 VIEW 병합(서브 쿼리 내부 로직가 외부 로직을 결합해서 하나의 실행 계획을 만드는 것)이라는 기법이 있긴 하다.

 

서브 쿼리를 사용해서 더 효율적인 상황도 있다

- 서브 쿼리와 결합을 윈도 함수로 대체하면 성능을 개선할 가능성이 있다.

- 결합 작업을 할 때 옵티마이저가 결합 대상 레코드를 줄이는 것을 잘 판별하지 못할 때 사람이 직접 연산 순서를 명시해주면 좋은 결과를 얻을 수 있다.

- 위처럼 서브 쿼리를 사용할 때 결합 대상 레코드 수를 사전에 압축해서 성능을 개선할 수 있다.

 

인덱스

카디널리티가 높을 것, 선택률이 낮을 것

- 카디널리티가 높다 -> 값이 분산되어있다 -> 값이 평균치에 많이 흩어져있다.

- 선택률이 낮다 -> 한 번의 선택으로 레코드가 조금만 선택되는 것을 말한다.

- 옛날에는 인덱스로 전체 레코드의 20퍼센트 이하가 될 때 인덱스를 사용하면 효과적이라고 했다.

- 최근 DBMS는 그것이 5퍼센트까지 낮아졌다. 앞으로도 더 낮아질 것. 그만큼 저장장치와 하드웨어가 발달해서 full scan도 굉장히 빨라졌음을 말한다.

 

인덱스를 사용하기 어렵거나 성능 향상을 기대하기 힘든 경우

- 1. 압축 조건이 존재하지 않음

- where 절이 없으면 인덱스를 안 쓰는 것...

- 2. 레코드를 제대로 압축하지 못하는 경우

- 인덱스마다 실제 레코드 값의 분포가 고르지 못한 경우가 있다. 이 경우 레코드 비율이 많은 인덱스를 사용하는 것은 그냥 full scan이 빠를 수 있다.

- 3. 입력 매개변수에 따라 선택률이 변동하는 경우

- index range scan의 범위를 말한다. 이 범위가 크면 역시나 full scan이 빠른 경우가 생길 수 있다.

- 또 옵티마이저는 이 인덱스를 사용했을 때 성능이 낮으면 full scan을 하는 유동적인 기능을 일반적으로 가지고 있지 않다.

- 예를 들어 10% hit 하는 인덱스는 full scan이, 1% hit 하는 인덱스는 인덱스를 사용하는 것이 낫지만 옵티마이저는 그냥 인덱스를 사용해버릴 수 있다.

- 이러한 동적 기능을 기대하려면 필드 값의 히스토그램을 검색할 수 있는 기능이 있어야 한다. Oracle, SQL Server에는 그런 기능이 있다고 한다.

-4. 중간, 후방 일치 LIKE 검색

- 당연하다. 인덱스 트리를 생각해보자. 이것이 가능할까?...

- LIKE 연산을 사용하는 경우는 인덱스 전방 위치의 경우만 가능하다(% 사과)

- 5. 색인 필드로 연산하는 경우

select *
from table
where col_1 * 1.1 >100;

단 아래 SQL은 인덱스 사용이 가능하다.

select *
from table
where col_1 > 100/1.1;

- 6. IS NULL을 사용할 경우

- 당연하다. 인덱스 트리를 생각해보자. 인덱스 트리엔 NULL이 존재하지 않는다.

- 물론 이게 가능한 DB도 있다곤 하지만 일반적이지 않다.

- 7. 부정형을 사용할 경우

- 당연하다. 인덱스 트리를 생각해보자. (!, <>, NOT IN,!=) 같은 조건의 탐색이 가능할까?...

해결법

1. 외부 설정으로 처리 - UI 설계로 처리

그냥 index를 사용했을 때 비효율적이 되는 쿼리를 수행하지 않도록 하면 된다.(?)

2. 데이터 마트로 대처

3. index only scan

- 즉 내가 원하는 데이터를 index로 만들고 그 index만 쿼리로 select 하게 하는 기법

- 즉 column 지향 DB를 의미하는 것과 비슷한 말이다.

- DBMS에 따라 지원하지 않는 경우도 있다. (현재 기준 주류 DB는 다 지원하는 것으로 보임)

- 한 개의 인덱스에 포함할 수 있는 필드 수에도 제한이 있다. 애초에 인덱스 사이즈는 무한하지 않고, 인덱스가 커지면 결국 그만큼 저장공간을 더 사용하는 것이기 때문에 원래 목적인 디스크 I/O를 줄이는 의미가 사라진다.

- 가장 큰 문제점은 인덱스가 비대해지면 갱신 시 수정되는 인덱스 오버헤드가 커지는 것이다.

- 정기적인 인덱스 리빌딩이 필요하고, SQL 구문에 새로운 필드가 추가된다면 사용할 수 없다.