[책 요약] Effective SQL - SQL 코딩의 기술 요약 (Ch2. 인덱스 설계와 프로그램적 처리)

Effective SQL, SQL 코딩의 기술이라는 책을 읽고 요약한 것으로, 실제 책과 동일하지 않을 수 있음. 개인 정리용

2.10 인덱스를 만들 때는 널을 고려하자.

 일부 데이터 베이스는 빈 문자열일 때, 이를 NULL으로 치환한다. 만약 NULL 데이터가 많은 상황에서 NULL 포함하지 않도록 인덱스를 생성한다면, scan에 대한 비용을 획기적으로 줄일 수 있다.

 

 또한 Mysql의 경우 NULL이라도 서로 다른 값으로 취급하기 때문에, 반드시 인덱스 설계시에 NULL을 어떻게 처리할 지에 대한 고민을 수행해야 한다.

 

2.11 인덱스와 데이터 스캔을 최소화하도록 인덱스를 신중히 만들자.

 -> 적절한 인덱스를 만들도록 데이터 분석하고, 생성된 인덱스가 잘 사용되는지 확인

 

 스캔 방식으로는 크게 테이블 스캔과 인덱스 스캔이 있고, 일반적으로는 인덱스의 크기가 테이블에 비하여 작으므로 인덱스 스캔이 더 효율적이고 빠르다. 

 하지만 쿼리에서 사용하는 조건에 따라 인덱스 스캔의 효용가치가 크지 않을 수도 있다. 또한 인덱스를 갱신하는 작업이 테이블 갱신 작업보다 비용이 더 많이 들 수 있다.
 게다가 인덱스는 주로 B Tree로 구현되어 있고 인덱스의 유형(클러스터, 비클러스터)에 따라서도 성능은 달라지게 된다. 마지막으로 인덱스를 생성할 때 컬럼의 순서도 인덱스 속도에 지대한 영향을 미친다.

 

 따라서 테이블에 형태와 인덱스의 유형 그리고 컬럼 순서등을 고려하여 인덱스를  작성하여야 한다.

 

2.12 단순 필터링 목적 이상으로 인덱스 사용

 -> where 절에 사용된 컬럼의 인덱스 유무는 성능에 영향을 미침.

 느린 쿼리(Slow query)의 첫번째 문제는 제대로 작성되지 않은 where절이다. 일반적으로 쿼리 수행시 3가지 중첩루프, 해시조인, 소트머지 join 알고리즘이 사용된다.

 (1) 중첩 루프 조인은 outer 쿼리가 테이블에서 결과를 가져오고, inner 쿼리로 해당되는 로우를 가져온다. 따라서 조인 조건에 참여하는 인덱스가 있으면 효과가 좋고, outer 쿼리의 결과가 작은 경우 좋은 성능을 낸다.

 (2) 해시 조인은 한쪽 테이블 데이터를 해시 테이블로 만들어 다른 테이블 로우에서 빠르게 탐색하는 방식이다. 따라서 조인되는 컬럼을 인덱스로 만들 필요가 없고, where 또는 on 절에 사용되는 컬럼을 인덱스로 해야 성능 이득을 얻을 수 있다.

 (3) 소트 머지 조인은 두 테이블을 각각 정렬한 다음, 두 항목을 결합 하는 방식(머지소트와 유사)이다. 따라서 조인 조건 컬럼만이 아니라 모든 후보 레코드를 표현할 수 있는 인덱스가 필요하다. 또한 인덱스 순서나 조인 방향, 순서에 따라 성능이 결정되지 않는다. (단, 정렬 순서대로 만들어진 인덱스가 있으면 정렬을 피할 수 있다.) ** Mysql은 해시, 소트머지 조인을 지원하지 않음

 

 또한 인덱스는 order by의 효율에도 영향을 미친다. 정렬 작업의 문제는 CPU 사용도 있지만 그 내용을 임시로 버퍼에 담아둔다는 점이다. 인덱스의 순서가 정렬의 순서와 같다면, 중간 결과를 저장할 필요가 없다.

 

2.13 트리거 남발하지 말자.

 -> DRI 구현이 나은 성능을 보여준다. 트리거는 이식성이 좋지 않다. 트리가거 멱등성(A*1=A, A*1*1=A, idempotence)이 존재하는지 확인해야 한다. 

 

 데이터 무결성을 위하여 트리거를 하지 말고, 선언적 참조 무결성(DRI)을 사용하는 것이 낫다.

 

트리거의 경우에는 데이터가 변경되는 순간마다 호출되어야 한다. 따라서 아래와 같은 이유가 아니라면 피하는 것이 좋다.

(1) 중복 또는 파생 데이터 관리

(2) 복잡한 컬럼 제약 조건

(3) 복잡한 기본 값

(4) 데이터베이스 참조 무결성

 

 2.14 데이터 부분 집합을 포함하거나, 제외하려면 필터링된 인덱스를 사용하자.

 쿼리 결과 중 일부만 받기를 원한다고 Where 조건 절을 사용하더라도 반드시 IO 양이 줄어드는 것은 아니다. 필터링된 인덱스 혹은 부분 인덱스는 비클러스터 인덱스로 일부 로우의 집합만 포함하여 로우 개수와 인덱스 개수가 1대1 대응되지 않는다. 따라서 IO작업도 줄어든다.

 

 이러한 인덱스를 생성할 때에는 반드시 결정적 함수만 사용할 수 있고, 조건절에서도 범위가 아닌 정확히 일치하는 값만 조건으로 줄 수 있다.

 

2.15 프로그래밍으로 검사하지 말고 선언적 제약 조건을 사용하자.

 DB는 DB 자체적으로 무결성을 지켜야 한다. 따라서 insert, delete, update등 작업이 일어날 때 프로그래밍 적인 제약 검사가 아닌 아래의 선언적 제약 조건을 사용하자.

 (1) NOT NULL

 (2) UNIQUE

 (3) PRIMARY

 (4) FOREIGN

 (5) CHECK  - 수식을 통해 계산되는 특정 값 제약

 (6) DEFAULT 

 

 그 외에도 절차적 참조 무결성 방식이 있다.

 (1) 프로그래밍 코드 - 애플리케이션 코드

 (2) 저장 프로시저

 (3) 트리거

 앞서 언급한 대로, (1)만으로 하게된다면 데이터 베이스의 역할을 잃게 된다. (애플리케이션 코드가 DB와 항상 함께 사용되어야 함), 또한 (2)의 경우에는 규칙이 복잡해지고 유저별 실행 권한 부여등의 다른 차원의 작업이 필요하다. (3)의 경우에는 앞서 언급된 대로 성능 저하를 가져올 수 있다.

 

2.16 사용하는 SQL 종류를 파악하자.

 DBMS마다 SQL을 처리하는 방식이 다르다. 따라서 자신이 사용하는 DBMS에 따라 달라지는 내용을 알고 있어야 한다.
(1) 결과 집합 정렬 - NULL이 NOT NULL 보다 정렬 순서가 높은가 낮은가?

(2) 반환 결과 집합 개수 제한 - Fetch first인가, row_number 같은 윈도우 함수를 사용하는가 아니면 커서를 사용하는가?

(3) boolean 타입

(4) SQL 함수 - 이게 가장 많이 다름.

(5) Unique 제약 조건

 

 

2.17 인덱스 계산 결과 사용 시기를 파악하자.

 어떠한 컬럼에 대하여 계산을 해야하는 경우, 컬럼 인덱스를 사용할 수 있을까? 예를 들어 이름 (ABC)와 (aBC)의 경우 이름으로 인덱스를 만들었다면 조건절에 Upper 함수를 호출하여 비교해야 하고, 이는 테이블 스캔을 가져오게 된다.

 

 이러한 문제를 해결하기 위하여 인덱스를 생성할 때 결정적 함수를 이용할 수 있다. 단, 반드시 결정적 함수이여야 하고 각 DBMS마다 요구되는 사항이나 키워드가 다를 수 있다.

 

 만약 DBMS가 이를 지원하지 않는다면, 이를 위한 새 컬럼을 추가하고 해당 컬럼에 대하여 인덱스를 다시 생성하여 비슷한 효과를 낼 수 있다.