어제보다 더 나은 개발자 되기

T 아카데미 

https://tacademy.sktechx.com/live/player/onlineLectureDetail.action?seq=129 

Effective SQL(SQL 코딩의 기술 발췌: 개인적인 comment 추가)

Better way 11 인덱스와 데이터 스캔을 최소화하도록 인덱스는 신중히 만들자

흔히 성능 문제는 인덱스가 부족하거나 올바르지 않은 인덱스를 만들어서 발생하기에 데이터베이스 엔진은 쿼리 조건을 만족하는 레코드를 찾는 데 필요 이상으로 많은 데이터를 처리할 가능성이 크다. 이런 프로세스를 인덱스 스캔 or 테이블 스캔으로 나눌 수 있다

데이터베이스 엔진이 적합한 레코드를 찾으려고 인덱스와 데이터 페이지를 스캔하며 데이터가 많을수록 인덱스 스캔을 완료하는 데 걸리는 시간은 길다.

à 데이터베이스 엔진 즉 옵티마이저는 데이터를 스캔하는 녀석이며 처리 건수에 성능 영향을 받는다.

인덱스는 데이터의 추출 문제를 해결해 주는 만능 해결사라고 여기는 것은 위험한 생각이다. 많은 인덱스가 데이터를 빠르게 가져오지도 않고 데이터 갱신 속도만 느리게 한다. 인덱스를 갱신하는 작업은 종종 테이블을 갱신하는 작업보다 비용이 많이 든다.

à 처음에 인덱스 만들 때 막 만들지 말아라

데이블 스캔보다 비클러스터 인덱스 스캔이 더 나은 성능을 발휘하는지 여부는 테이블 크기, 로우의 저장패턴, 로우의 길이, 쿼리가 반환하는 로우의 비율에 따라 다르다 흔히 전체 로우 중 최소 10%이상의 로우가 반환 될 때 테이블 스캔이 비클러스터 인덱스보다 나은 성능을 보이기 시작한다. 클러스터 인덱스는 보통 반환되는 로우의 비율이 높을수록 테이블 스캔보다 나은 성능을 발휘 한다.

à 검증이 필요하다 특히 반환되는 로우의 비율이라는 말은 잘 공감이 안 된다. 인덱스는 기본적으로 범위검색에 강하다 그렇다면 반환되는 비율이 높다는 것은 걸러지는 행이 별로 없다는 말로 이해된다… (경험으로 극복필요)

컬러의 카디널리티가 낮으면(인덱스 값의 다수가 같은 값일 때)인덱스의 효과가 미미하다. 인덱스를 사용했지만 테이블에서 최소 비율 이하의 데이터만 읽게 된다면 데이터베이스 엔진은 인덱스를 사용하지 않을 것이다. 게다가 인덱스는 테이블이 클 때만 사용하는 것이 좋다. 데이터베이스 엔진은 대부분 테이블이 작으면 그 데이터를 메모리에 올려놓는다. 데이터가 일단 메모리에 올라오면 무슨 작업을 하든 빠르게 데이터를 탐색한다. 여기서 “작다”는 기준은 로우의 개수, 개별 로우의 크기, 페이지에 로드되는 방식과 데이터베이스 서버의 가용 메모리 용량에 의존함을 의미한다.

à 인덱스로 사용하는 컬럼은 중복이 없으면 좋다. 그리고 메모리에 올리기 위해서는 가능한 작은 집합적으로 연산이 되야 한다.

Better way 12 인덱스를 단순 필터링 이상의 목적으로 사용하자

SQ문에서 검색 조건은 Where 절에 기술하는데 여기서 데이터를 빠르게 찾는다는 인덱스의 핵심 목적이 진가를 발휘하다. 느린 쿼리의 첫 번째 범인은 제대로 작성되지 않은 Where 절이다.

à 조건 검색에 사용할 데이터가 뭔지 미리 알자

컬럼을 인덱스로 만들었는지 여부는 테이블 간 조인이 얼마나 효율적으로 수행되는지에 영향을 미친다. 요컨대 조인을 이용하면 정규화된 모델의 데이터를 특정 처리에 적합한 역정규화된 형태로 변환할 수 있다.(비즈니스 모델 조인은 여러 테이블에 분산된 데이터를 결합하므로 여러 페이지에 있는 데이터를 더 많이 읽느라 디스크 탐색 대기 시간에 민감하다. 따라서 적절하게 인덱스를 만드는 것은 조인이 응답하는 시간에 큰 영향을 준다.

쿼리를 수행할 때 사용하는 조인은 한번에 두 테이블만 접근한다. SQL이 더 많은 테이블을 조인하는 경우 더욱더 많은 단계가 필요하다 먼저 두 테이블을 조인해 중간 결과 집합을 만든 후 이 결과 집합과 다음 테이블을 조인하는 식으로 처리한다.

à 조인의 경우 인덱스에 영향을 받고 조인으로 비즈니스 모델을 만들 수 있고 여러 테이블을 조인하는 경우 한번에 두 테이블만 처리가능 하므로 조인 순서에 영향을 받는다.

중접 루프 조인은 가장 기본적인 조인 알고리즘이다. 선행 쿼리가 한 테이블에서 결과 집합을 가져오고 두번째 쿼리는 선행 쿼리 결과집합의 각 로우에 대응하는 데이터를 다른 테이블에서 가져온다. 따라서 중첩 루프조인은 조인 조건에 참여하는 컬럼을 인덱스로 만들었을 때 가장 효과가 좋다. 선행 쿼리가 작은 결과 집합을 반환할 때 중첩 루프 조인은 좋은 성능을 보인다. 그렇지 않으면 옵티마이저는 다른 조인 알고리즘을 선택한다.

à NL 조인은 조인조건에 참여(ON, Select까지) 하는 컬럼을 인덱스로 만들고 선행테이블은 작은 결과 집합이 나오는 것으로 하자 결과집합이기 때문에 단순히 작은 테이블이 아니라 조건에 의해 걸리진 작은 결과 집합이다.

해시 조인은 참여하는 한쪽 테이블데이터를 해시 테이블로 만든 후 다른 쪽 테이블의 각 로우를 매우 빠르게 탐색할 수 있다. 해시 조인은 해시 테이블을 사용하므로 조인되는 컬럼을 인덱스로 만들 필요가 없다 해시 조인의 성능을 향상할 수 있는 인덱스는 Where와 ON 절에 사용되는 컬럼에 대한 인덱스다 현실적으로 해시 조인의 성능은 수평적(좀 더 적은 로우) 수직적(좀 더 적은 컬럼)으로 해시 테이블을 줄이면 성능이 좋다.

à 해시조인의 인덱스는 Select 절 컬럼보다는 On이나 Where절에 우선해서 만들고 선행 테이블의 결과 집합이 적으면 메모리에서 처리가 가능해 성능이 좋고 대량의 데이터를 처리할 때 알고리즘 복잡도가 O(1)이기 때문에 선호된다.

소트머지 조인은 조인 조건에 따라 두 테이블을 각각 정렬한 후 지퍼처럼 정렬된 두 항목을 결합하는 식으로 수행된다. 소트머지 조인은 테이블의 조인 순서가 별 의미가 없고 성능에 미치는 영향이 작다 동시에 두 테이블에 접근한다고 보면 된다. 일단 데이터가 정렬되면 소트머지 조인은 좋은 성능을 발휘하지만 양쪽 데이터를 정렬하는 비용이 크기 때문에 잘 사용하지 않는다. 반면에 해시조인은 한쪽만 미리 처리하면 되므로 양이 많으면 해시조인이 훨씬 낫다.

à 정렬된 상태와 범위 검색을 할 수 있는 컬럼의 인덱스가 필요하다 다른 조인과 가장 큰 차이는 조인 순서가 크게 영향이 없다는 부분이다. 해시조인이 부담이 될 때 고려해 볼 수 있겠다.

Better way 46 실행 계획의 작동 원리를 이해하자

SQL 데이터베이스를 사용하는 사람이라면 누구나 SQL 쿼리를 최적화하거나 특히 인덱스나 모델 설계에서 필요한 스키마 변경을 수행 할 수 있도록 실행 계획을 읽는 방법과 실행 계획의 의미를 이해해야 한다.

데이터를 효율적인 방식으로 가져오려고 물리적인 단계를 기술하는 단조로운 작업에서 개발자를 해방하는 것이 SQL의 목표라는 점을 일깨우고 싶다. 즉 얻으려는 데이터를 선언적으로 기술하고 데이터를 가져오는 최상의 방법은 옵티마이저에게 일임하는 것이다. 실행 계획과 물리적인 구현 내용을 설명할 때 SQL이 제공하는 추상화는 배제할 것이다.  

à 어렵게 말한 것 같다 내가 이해하기로는 모델링과 같은 추상화 적인 부분과 DBMS가 해석하는 영역은 구분되어 있다는 말인 것 같다

컴퓨터이기 때문에 작업을 수행하는 방식이 완전히 다를 것이라고 가정하지만 그렇지 않다 동일한 작업을 처리할 때 거치는 물리적인 단계는 사람이 수행하는 것과 다르지 않다 결론적으로 실행 계획을 읽을 때 쿼리에 대해 데이터베이스 엔진이 수행하는 물리적인 단계를 파악하는 것이 좋다

à 물리적인 처리 단계를 이해하면 실행계획과 쿼리를 보고 내부적으로 어떻게 처리하는 지 알 수 있다는 말   즉 디버깅 같은 과정을 하지 않아도 쿼리가 어떻게 실행되는지 해석하는데 도움이 되는 것

인덱스 시스템은 여러분이 작성할 쿼리의 종류에 매우 크게 의존한다는 점이다. (관련 예제 설명은 인덱스를 생성할 때 컬럼의 순서나 INCLUDE 절로 Lookup을 제거할 수 있다는 것을 설명한다)

à 좋은 인덱스 생성은 좋은 모델 혹은 좋은 설계부터 시작된다. (책의 첫 인덱스는 클러스터 인덱스로 볼 수 있고 뒷장의 인덱스는 비클러스터드 인덱스로 볼 수 있고 룩업이라는 것은 비클러스터드 인덱스에서 페이지 번호를 보고 실제 책에서 내용을 찾는 것을 말한다 이런 DBMS의 물리적인 동작을 머릿속에서 그릴 수 있다

데이블을 스캔하는 실행 계획을 보는데 인덱스가 있음에도 실행 계획에 사용되지 않는다면 어떤 이유인지 찾고 분석해서 개선하거나 수정할 수 있어야 한다. (인덱스는 만능도 아니고 비용이다)

à 실행 계획을 보고 인덱스 사용여부를 파악하라는 말

데이터베이스 엔진은 쿼리를 수행하는 더 나은 방법을 찾고 이에 맞게 실행 계획을 다시 세울 정도로 똑똑하게 처리했다. 이 특성은 데이터베이스 엔진 자체가 사용자가 질의한 쿼리로 제한된다는 점을 강조하는 것이다. 잘 작성되지 않은 쿼리를 보낸다면 데이터베이스 엔진도 어쩔 수 없이 나쁜 실행 계획을 생성할 것이다.

à 쿼리작성과 모델링을 잘하자

물리적인 동작을 처리하는 순서가 꽤 많다. 하지만 어떤 순서로 처리해야 좀 더 효율적인지는 데이터 분포에 의존한다. 따라서 매개변수화 된 쿼리(저장 프로시저)는 특정 값에서 월등한 성능으로 수행되지만 다른 값에서는 현격히 느리게 수행될 수 있다. 데이터베이스 엔진은 매개변수화 된 쿼리에 대응하는 실행 계획을 저장해 놓는다.

à Ad-Hoc 쿼리와 차이점 저장 프로시저는 실행 계획을 캐싱한다 라는 의미로 보면 될 것 같다 즉 저장 프로시저는 캐싱된 실행계획을 사용하지만 그 데이터 분포가 변하는 것에 캐싱된 정보로 인해 잘 못 판단할 수 있다는 말이다. 즉 노후화된 시스템은 인덱스 통계정보를 갱신하거나 인덱스를 수정해서 DBMS에서 다시 실행계획을 캐싱하도록 검토하고 확인할 필요가 있다. (유지보수하면서 나올 문제를 말하는 것 같은데.. 다만 SP를 만들면서 이와 같은 경우를 예측할 수 있을까?? 일단 경험을 쌓자)

실행계획의 단계를 보고 효율적인지 판단한다. 효율성은 데이터 분포의 영향을 받는다 결론적으로 나쁜 연산은 없다. 다만 쿼리에 적합한 연산이 무엇인지 분석한다.

à 실행계획을 볼 줄 알자

좋은 실행 계획을 얻으려면 한 쿼리에 국한되지 않는 인덱스를 추가한다. 데이터베이스의 전반적인 가용성을 고려해 추가한 인덱스가 가능한 많은 쿼리에 적용되는지 확인하고 저장프로시저의 경우 시간이 흘러도 최적화를 잘하는지 확인하자 (DBMS에서 재컴파일 옵션이 어떻게 동작하는지도 확인해 보자)

à 인덱스 통계(분포와 관련)를 주기적으로 갱신해 주고 필요하면 기존 캐싱을 지우고 실행계획을 다시 생성하도록 하자

인덱스와 통계적 상관관계

개요

인덱스와 카디널리티와 선택도에 대한 관계를 알아보고자 테스트를 하고자 한다.

요인(factor)

- 선택도 (X1)

- 카디널리티 (X2)

- 검색 범위 (X3)

- 인덱스 개수 (X4)

테스트 가설

n  Y(수행시간) = aX1 + bX2 + cX3 + dX4

단 계수에 대한 가중치나 차등을 둘 것이 아니면 1로 생각한다.

예를 들면 아래 규칙에 따라 가중치를 계산한다면 

1.     선택도에 따라

A.     1 / 0.5 / 0.3 / 0.1 / 0.01

2.     카디널리티에 따라

A.     10000 / 100000 / 1000000

3.     검색 범위에 따라

A.     90% / 70% / 50% / 30% / 10% / 1%

4.     인덱스 개수

A.     1개, 2개, 3개, 4개

경우에 따라 결과에 영향을 미치는 차이가 있다는 설계를 하지 않으면 계수에 대한 영향은 각각 같다

모든 경우를 테스트 하는 것은 아니고 카디널리티에 대해서 구분하고자 한다.

테스트 Case

1.     카디널리티를 낮은 -> 높은 순서로 인덱스를 구성

2.     카디널리티를 높은 -> 낮은 순서로 인덱스를 구성

테이블 형태

전체 Row는 약 1700만건으로 생성하고 각각의 카디널리티를 구해 본다

인덱스를 2가지 형태로 생성

첫 번째 인덱스는 is_bonus, from_date, group_no순으로 카디널리티가 낮은 순에서 높은 순 (중복도가 높은 순에서 낮은순으로) 으로, 두번째 인덱스는 group_no, from_date, is_bonus순으로 카디널리티가 높은 순에서 낮은 순 (중복도가 낮은 순에서 높은순으로) 으로 생성

select SQL_NO_CACHE *               -- 쿼리 Cache를 사용하지 않음

from salaries

use index (IDX_SALARIES_INCREASE)  -- 옵티마이저에서 인덱스 사용을 변경하지 않도록 강제로 지정

where from_date = '1998-03-30'

and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406')

and is_bonus = true;

select SQL_NO_CACHE *

from salaries

use index (IDX_SALARIES_DECREASE)

where from_date = '1998-03-30'

and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406')

and is_bonus = true;

테스트 방법

2가지 쿼리에 대해 각각 10회씩 진행

테스트 결과

결과 요약

성능 수치로 봤을 때 카디널리티가 높은 순에서 낮은 순으로 구성하는 게 성능이 더 뛰어나다 

옵티마이저와 물리 조인 수행 원리

서론

- DBMS의 엔진이 동작하는 원리

- 용어 정리

l  옵티마이저

-      사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 DBMS의 엔진

-      사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행

-      옵티마이저가 선택한 실행 방법의 적절성 여부에 따라 수행속도에 영향을 미침

옵티마이저를 이해하는 것이 튜닝의 시작이고 옵티마이저를 제어하는 것이 튜닝의 기본

l  옵티마이저에 의해 발생하는 조인 3가지

NL조인 , Sort Merge 조인, Hash 조인이 있다. 이것은 물리적인 조인이기 때문에 개발자가 직접 사용하게 되는 조인은 아니다

개발자가 직접 제어하는 조인은 내부조인이나 외부조인과 같은 논리적인 조인을 선언해서 만든다.

Ludy의 경우 Merge Into 구문도 조인이 있다 

(조인 성능에 대해 검색을 해보면 UPSERT로 동작하기 때문에 구문하나에 UPDATE, INSERT를 수행하는 것에 대해 UPDATE INSERT를 따로 하는 것에 비해 성능적 이점을 말하고 있다 단 당연한 것이지만 데이터가 많아 질 때 약 200만건 이상인 데이터를 Merge 구문으로 처리할 때는 느려질 수 있음을 주의시키고 있다. 즉 기존의 UPDATE시행 이후에 @@ROWCOUNT를 확인한 후 변경된 내용이 없으면 INSERT를 해야 하는 세밀한 트랜잭션처리를 필요로 하지 않기 때문에 단일 구문의 이점은 있다 개인적인 견해지만 대량의 데이터는 적정수준을 정해 나누어서 UPSERT하는 경우가 더 나을 수 있어 보인다.)

l  옵티마이저 역할

논리적인 조인을 옵티마이저라는 녀석이 DBMS 내부에서 물리적인 조인으로 표현하고 만드는 것

보통 CBO라는 비용기반 옵티마이저가 사용되며 최적의 실행 방법 결정이라는 것은 어떤 방법으로 처리한 것이 최소 일 량(비용)으로 동일한 일을 처리할 수 있을 지 결정하는 것이다.

개발자는 옵티마이저가 최적의 실행 방법을 결정하는 것을 도와줄 수 있다. (인공지능은 아님)

단순히 조인종류를 아는 것보다 이 옵티마이저가 어떻게 물리적인 조인으로 변경시키는 지 알자

l  실행 계획: 옵티마이저에 의한 최적의 실행 방법

구성하는 요소 

- 조인순서: 참조하는 테이블의 순서

- 조인기법: NL, Hash, Sort Merge

- 액세스 기법: 테이블 스캔, 인덱스 스캔

- 최적화 정보: 비용(cost) 주어진 조건을 만족한 결과 집합 수

조인 조건을 만족한 건(Cardinality) 수

Bytes(결과집합의 메모리 양)

- 연산: 조인기법, 액세스기법 등 여러 가지 조작을 통해서 원하는 결과를 얻어내는 작업

Ex)

l  옵티마이저가 연산으로서 Full Table Scan을 선택하는 경우

1.     SQL 문에 조건이 존재하지 않는 경우

2.     SQL 문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우

3.      조건을 만족하는 데이터가 많은 경우 옵티마이저 자체적으로 판단할 때 Index scan가 아닌 Full Scan 고려

4.     많은 데이터를 엔진 내부에서 병렬처리 방식으로 처리하는 경우나 SELECT * 같은 전체를 가져올 때

l  옵티마이저가 이용하는 통계정보

통계정보를 사용하는 이유

일단 옵티마이저는 만능이 아니다. 옵티마이저도 사람이 만든 SW이다.

실제로 SQL문을 처리해 보지 않은 상태에서 결정해야 하는 어려움이 있다

-      DBMS 별로 옵티마이저의 성능을 비교해 볼 수 있는 부분이라고 볼 수 있겠다

기본적으로 통계정보를 이용한다. (http://www.dbguide.net/db.db?cmd=view&boardUid=148218&boardConfigUid=9&categoryUid=216&boardIdx=139&boardStep=1)

1. 선택도 – 예상되는 ROW 비율

2. 카디널리티 – 예상 되는 ROW 건 수

3. 히스토그램(데이터의 분포) – 평균 분포에도 영향을 받음

l  옵티마이저 힌트

SQL 서버에서의 힌트는 3가지 이다.

1. 테이블 힌트 – 테이블 명 다음에 WITH를 통해 지정한다(fastfirstrow, holdlock, nolock)

2. 조인 힌트 – FROM 절에 지정하며 두 테이블간 조인 전략에 영향을 미친다. (Loop, hash, merge, remote)

3. 쿼리 힌트 – 쿼리당 맨 마지막에 한번만 지정할 수 있는 쿼리 힌트는 OPTION 절을 이용한다

l  카디널리티

- Cardinality: 사전적 의미로는 집합원의 개수 그렇다면 원소의 개수

- 카디널리티가 낮은 경우에서 속성의 예를 들면 성별, 부서, 지역이 있다.

- 성별의 경우 남자, 여자 두 가지 경우만 가능하므로 매우 낮다고 할 수 있다

- 주민번호, 사원번호와 같은 경우 조직원이 많을수록 카디널리티가 높다

- 간단히 생각하면 중복을 제외하고 고유한 속성을 뽑았을 때 발생할 수 있는 경우의 수이다.

- 데이터베이스로 한정해보면 테이블에서 Primary Key 고유한 값인 카디널리티로서 높은 경우의 수를 가지고 있다.

l  카디널리티 설명 예제

학급이라는 엔터티를 만들어 학년이라는 성적표를 만든다면 반 번호가 기본 키가 되고 그 학급의 학생의 수가

카디널리티가 될 수 있다 (학급(1) : 학생(N)) 학생 한 명 한 명은 고유한 값이다.

또는 특정 쿼리문을 실행시켜서 나오는 결과 값(Row)를 카디널리티라고 한다.

즉 학교라는 개념에서는 학급이 카디널리티일 수 도 있고 학급이라는 개념에서는 학생이 카디널리티일 수 있다.

다른 예로 사원테이블의 전체 레코드 수가 1000개 일 때 WHERE 부서 = ‘인사팀’ 이면 그 중 인사 팀 사원이 10명이되면 10/1000 = 0.01 이 선택도가 되고 출력되는 Rows의 개수 1000 * 0.01 = 10 

즉, 카디널리티는 10이다.

l  카디널리티를 이해해야 하는 이유

DB의 옵티마이저에서 특정 값을 찾는 항목으로 인덱스를 사용하게 된다. 없으면 인덱스를 사용하지 않고 Full Scan 이 되어서라도 찾아준다. 중요한 건 현재 접근하는 컬럼을 조건으로 접근할 때 얼마나 많이 걸러내지는 지 이해가 되는가? 이다.

선택도가 확률이라고 하면 해당 카디널리티 즉 경우의 수(확률과 전체를 곱했을 때 나타나는 기대값)는 전체 데이터의 개수에 영향을 받게 된다. 데이터가 많을 때는 해당 조건으로 얼마나 많이 걸러지는 지 즉 중복이 최소한일수록 해당 조건으로 많이 걸러지게 된다. 당연히 중복이 있는 컬럼을 인덱스로 사용 안 한다고 생각하겠지만 Non Clustered 인덱스는 꼭 중복이 없는 컬럼만 사용된다고 보장할 수 없다. WHERE 절의 조건에 의해 검색되는 경우도 마찬가지이다.

중복된 data를 검색하면 중복이기 때문에 동일한 값의 범위에 해당하게 되고 인덱스 컬럼이 여러 개 이어서 순서대로 후행에 대한 조건도 같이 검색해야 될 경우 한행 한행 하나씩 Scan하면서 찾아야 할 수 있다. 검색으로 찾아야 하는 data의 중복이 많거나 또는 DB 인덱스의 Leaf 블록에 동일한 것이 많을수록 블록 전체를 Scan 해야 하기 때문에 모든 조건을 만족하는 원하는 데이터를 찾는 경우 범위를 좁혀가는 검색에서는 중복을 고려하는 시간이 더 걸릴 수 있다. 동일한 항목을 제낀다?? 제한다?? 라는 표현이 맞을 지 모르겠지만 인덱스 블록에 Access를 하고 순차적으로 선형 검색을 하면 순서대로 전체를 스캔 하게 되고 인덱스 블록에 중복이 없어 정렬된 상태에서 인덱스 스캔을 하면 범위 단위로 검색 대상을 좁힐 수 있는 경우 성능이 더 좋다고 할 수 있다.

랜덤 IO와 순차 IO의 비교: http://12bme.tistory.com/138

알고리즘 적으로는 선형 O(N) > 이진트리 O(logN) 이기 때문에 성능이 더 좋다고 한다. 

이렇게 집합에서 원소가 어떻게 분포되었는지에 따라 스캔속도가 차이가 나므로 옵티마이저는 인덱스를 어떤 녀석으로 하느냐에 따라 성능에 영향을 받는 다는 것이다.

개발자는 물리적인 조인에 대해 직접 관여는 하지 않지만 인덱스 설정에 따라 영향은 줄 수 있다는 말이기도 하다. 일반적으로 옵티마이저는 여러 인덱스가 있을 때 선택도가 높은 즉, 중복발생확률이 낮은 인덱스를 사용한다고 한다.

더 멀리 나가면 DB 설계를 할 때 어떤 속성들을 컬럼으로 쓰냐에 따라 장기적인 인덱스 관리나 검색성능에 영향을 미치게 되므로 집합의 분포를 알고 어떤 속성을 모델에 넣을지 정하는 것도 모델링의 중요한 포인트라는 것이기도 하다.

l  선택도와 카디널리티의 이해

확률이 예를 들기 좋은 것 같다.

카디널리티라는 주머니를 만들고 거기에 빨주노초파남보 의 무지개 색 공을 집어넣으면 이 경우 선택도는 7/7 = 1 이다. 모두 유니크 한 색을 가지고 있기 때문이다.

그러면 빨간공 3개 노란공 2개 보라색공 2개를 넣으면 이 경우 선택도는 몇 일 까?? 빨간 공의 확률은 3/7 노란공은 2/7 보라색공은 2/7 이다. 이건 단순히 시행(반복)이라는 시도와 맞물려서 말할 수 있는 확률 인 것이고 공이라는 개념으로 접근하면 3개의 색만을 가지므로 그 공이라는 엔터티 테이블에서 색깔이라는 속성은 선택도가 3/7 즉 약 0.44 이다. 앞의 무지개색인 경우에 비해 선택도가 작아졌다. 

그리고 그 빨주노초파남보 이면서 각각 1~7까지의 숫자가 매겨져 있는 상황이라면

빨 | 주 | 노 | 초 | 파 | 남 | 보

1   | 2  |  3  |  4 |  5  | 6   | 7

이런 상황에서 숫자와 색깔이라는 속성의 선택도는 어떻게 될까??

확률을 얘기 했으니 숫자로 하면 7/7 색으로 하면 7/7 이다. 이것은 시행에 의해 획득할 수 있는 한번의 공과 같지만 유일성이 확보가 되니 선택도는 1이고 특정 색이나 숫자가 검색되는 카디널리티는 1 이다.

근데 예제를 바꾸면

빨 | 주 | 노 | 초 | 파 | 남 | 보

1  |  2  |  2 |  2  |  3 |  4  |  4

이런 상황에서는 선택도가 변하나? 색으로는 7/7이고 숫자로는 4/7 이다.

여기서 어떤 속성을 기준으로 하느냐에 따라 확률이 달라졌고 그에 따라 선택도(카디널리티)도 달라졌다.

여기서 속성을 색과 숫자로 나눈 이유는 인덱스를 어디다 거는 기준이 뭐냐?? 라는 말을 하고 싶어서이다.

최소한의 기준은 앞에서 선택도가 높고 를 말했다.

한번 비교해 보자

색깔: 선택도 - 7/7 와 빨간공 하나 카디널리티 7 * 1 = 7이다.

숫자: 선택도 – 4/7 와 4번 공 하나 카디널리티 7 * (4/7) = 4이다.

숫자 일 때 선택도가 크고 카디널리티가 크다 이런 경우 어떤 속성을 인덱스로 하는 것이 더 나을까??

이걸 옵티마이저에서 증명하고자 한다. 그래서 아래 내용은 가설적인 내용이다. (팩트는 아닐 수 있다)

확률 공식을 보면

 n은 테이블 전체 로우 수 p는 선택도 E(X)는 카디널리티 집합 수

즉 카디널리티는 일정한 기댓값 즉 평균의 속성을 가지고 있다.

카디널리티가 비용에서 고려되는 이유는 결과집합의 수를 알기 위해서이다. 비율이 작아 선택도가 1% 밖에 되지 않더라도 그 집합의 전체 개수의 영향을 받는다. 100개중의 1%와 10000개 중의 1%은 결과 집합 수가 다르다. 옵티마이저는 선택도에 대한 통계를 이용하기 때문에 인덱스를 사용할지 안 할지를 통계정보에서 얻을 수 있다. 카디널리티는 검색되는 대상 집합이 되기 때문에 인덱스나 조건에 의해 어떻게 걸러지냐에 따라 속도와 관계 있으므로 카디널리티의 결과 집합의 수에 따라 조인 테이블의 순서나 인덱스 컬럼의 순서를 확인 해야 할 수도 있다.

사실 위의 내용을 고민하지 않아도 인덱스를 만들거나 하는 것에는 크게 상관이 없다. 보통 자연속성의 Id가 없으면 인조키를 만들어서 라도 유일키를 만들기 때문에 적어도 PK의 중복을 제거하는 인덱스를 만드려고 개발상에 에로사항을 겪을 일이 적다. 다만 과거 히스토리를 조건 검색하거나 통계쿼리를 작성해야 하거나 기존의 쿼리를 튜닝 해야 하는 경우는 옵티마이저를 이해해 한 튜닝을 해야 할 수 있다.

조인을 만드는 것도 옵티마이저에게 성능판단을 위임하는 것이기 때문에 개발자가 DBMS를 어느 정도 이해해야 하나? 라는 의문을 가지면 SELECT가 되기 위해 입력하는 조건과 출력되는 컬럼을 빨리 가져오는 쿼리를 만들 줄 아는 것이라고 말할 수 있다. 그리고 옵티마이저는 분명 만능이 아니고 최적화에 실패할 수 있다. 벤더사 별로 인덱스를 가지고 검색을 할 때 동작방법이나 원리는 DBMS마다 다르기도 하다. MySQL은 Merge나 Hash 조인이 없다. DBMS 마다 다른 특성이 있기 때문에 하나의 DB 환경만 공부할 것이 아니면 어느 정도 내부적인 특성도 알아야 한다.

위에 내용에 대해 궁금하게 접근한 이유는 이미 누구나 알고 있는 인덱스의 동작원리보다는 어떤 녀석이 인덱스가 되어야 하지?? 넌 어떤 점이 좋으니 인덱스가 되고 넌 어떤 점이 안 좋으니 인덱스가 될 수 없다 하는 기준을 세우고 그것을 수치화 하거나 테스트를 해보고 싶어서 이다. 어떤 녀석을 인덱스로 고민하고 또 그것을 활용하는 옵티마이저는 이 인덱스의 어떤 점에 끌려 자기만의 인공지능적인 최적화를 수행 할 수 있을까? 라는 물음이다.

즉 가능하면 개발자가 데이터베이스에서 인덱스를 만들어야 한다면 적어도 그 테이블의 속성이나 데이터의 분포를 보고 기준이 되는 부분을 한번 생각해보고 정하고자 한다. 물론 옵티마이저에서 위의 선택도나 카디널리티나 기댓값이나 이런 부분이 통계수치를 이용한다고만 써있고 아직 내부를 직접 구현해 보거나 정확한 계산 수치는 더 연구해 봐야 한다. 다만 위키에 넣을 테스트 내용을 검증할 때 어느 부분을 고민했고 검증하려고 했는지 그 근거로 인덱스를 고려할 컬럼을 정한다면 (PK 말고 다른 컬럼을 조건 검색할 때) 통계적인 수치 부분이나 그 속성의 Unique 특성을 고려해서 해당 컬럼에 인덱스를 걸고 그 인덱스를 옵티마이저가 판단했을 때 (개발자가)내가 제어 가능한 수준으로 기준을 산정하기 위해 옵티마이저와 인덱스와 통계적 근거를 고려 했다.  

인덱스는 따로 위키 설명이 있으니 생략하려고 했으나 언급을 안 할 수가 없을 것 같아 정리한다.

l  인덱스

인덱스는 검색을 빨리 하기 위한 용도로 사용

인덱스를 여러 개 사용할 때는 넌클러스터 인덱스를 사용.

인덱스를 여러 개 사용할수록 옵티마이저가 잘못된 인덱스를 선택할 확률이 높아짐

인덱스를 하나의 컬럼에만 걸어야 한다면 선택도가 가장 큰 경우 즉 중복을 제거 했을 때 집합 원소의 개수가 가장 많은 속성을 지정(보통 PK나 ID 속성들이 당연하게 쓰임 이걸 고려하는 것은 WHERE 절이나 INCUDE 절에 쓰이는 추가적인 Index 사용이다)

예를 들면 성별을 인덱스로 지정하면 카디널리티가 2밖에 되지 않으므로 인덱스로 걸러지는(Range(범위)로 걸러진다고 할 때) 경우가 50% 밖에 걸러지지가 않는다. 100 개 중에 유일한 하나일 경우라면 1%를 찾는 Unique Index가 되고 한번에 99%가 걸리지는 경우이다.

위의 내용을 보고 다시 궁금증을 가지면 인덱스로 걸러낸다는 개념은 무엇일까???

그리고 우리가 사용하는 테이블은 기본적으로 어떻게 설계하길래 성능이슈가 있게 된 것일까??

확실히 말할 수 있는 것은 사원번호나 주민번호와 같은 속성의 값을 대표 값으로 할 수 있는 것은 현재 구성원 Member 라는 집합(SET)에서 유일성과 대표성을 설명할 수 있는 속성이 된다(모델링을 한다면)

또는 10 이하의 자연수의 집합을 표현 하면 S = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10} 일 때 원하는 숫자를 찾으려면 중복이 없기 때문에 유일한 값 하나를 찾을 수 있다 이렇게 유일한 속성이라는 것은 이 값 하나만 찾으면 내가 찾을 수 있는 최소한의 리소스로 나머지 전체를 알 수 있는 이유와 같다 즉, DB용어로는 함수종속이라고 하는데

| 사원번호 ß 사원정보들 |

사원에 대한 정보들이 사원번호에 함수종속 하므로 사원번호만 알면 그 사원의 정보를 모두 알 수 있다 즉 최소한의 정보만 알아도 관련 정보를 다 알 수 있으니 최소한의 리소스로 원하는 정보를 찾을 수 있는 것이다. 성능관점은 이것으로 이해하면 걸러진다는 개념은 내가 찾는 원소의 최소한의 속성 하나로 나머지 비상관 원소들을 모두 걸러버릴 수 있으므로 필터율이 매우 높은 필터라고 할 수 있다 즉 필터 성능이 좋으면 인덱스의 성능도 좋다 모든 경우라고 할 수는 없고 = 검색에 탁월 해진다(범위는 지정하기 나름)

위의 설명에서 인덱스의 유일성의 필요에 대해서 성능과 관련 지어 봤다면 조건상 모든 인덱스가 유일하게 쓰이지 않을 수도 있다는 말을 했다. 그렇다면 그 컬럼이 Null도 들어갈 수 있다는 말이다. Null에 대한 처리는 오라클과 MS Sql이 다르기 때문에 해당되는DBMS 별로 알아야 한다.

기본적으로 데이터베이스의 엔터티, 릴레이션, 혹은 테이블이라고 하는 이 복잡한 개념은 해당 컬럼이 정규테이블이라면 다른 컬럼들과 원자적 혹은 독립적인 속성이어야 한다고 한다. 즉 본인이 변경되었다고 다른 컬럼의 변경에 영향을 미치면 안 된다는 것이다 (PK 제외) 만약 변경이 된다면 정규적이지 않다고 하게 된다. 그리고 튜플이라고 하는 이 Row 값 중에 다른 Row와 구별되는 대표값이 Null이 있다면 일단 다른 Row와 구별이 되지 않게 되고 해당 Row를 대표하는 경우도 못하게 된다 즉 고아행이 되어서 영영 지워지거나 이용되지 않은 채로 테이블에 남을 수도 있다. 대표값이 구별되면서 Not Null이어야 대표값만 가지고 관련 정보들을 찾을 수 있다 인덱스가 이런 용도이고 이런 용도로 인덱스를 사용하려면 원천적인 모델링도 잘 해놔야 2개이상의 조건 검색에 대응하기 좋은 모델이라고도 할 수 있다. 인덱스는 데이터의 얼굴 같은 느낌이다. 얼굴만 보고 그 사람을 알고 얼굴을 보고 그 사람을 찾아야 한다. (굳이 예를 들자면)

인덱스 스캔

-      인덱스의 순서에 따라 (A,B)로 지정되면 A에 대해 정렬이 되고 A가 동일할 경우 B로 정렬한다.

인덱스 유일 스캔

-      유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식

-      중복을 허락하지 않는 인덱스 스캔이며 수평적 탐색이 없이 수직적 탐색이 이루어짐

인덱스 범위 스캔

-       인덱스를 이용해서 한 건 이상의 데이터를 추출하는 방식

-       Leaf 블록을 필요한 범위만 스캔하는 방식

-       인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야 한다.

-       필요한 범위만 스캔

인덱스 역순 범위 스캔

-       인덱스의 리프 페이지를 내림차순으로 검색하는 방식 최대값을 쉽게 찾는 것과 같은 이치

인덱스 전체 스캔

-       Leaf 블록 전체를 스캔 하는 방식 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 사용된다.

-       가능하면 인덱스가 있는 경우가 낫고 전체 데이터에서 극히 일부를 추출하는 경우라면 Full Table Scan보다 낫지만 Range Scan보다는 떨어진다.

-       인덱스 컬럼에서 1순위가 아닌 컬럼을 이용해 검색할 때 발생한다.

-    Index를 정상적으로 사용하기 보다는 전체를 검색

SQL Server의 클러스터형 인덱스

-       인덱스의 Leaf 페이지가 곧 데이터 페이지이며 인덱스 키 컬럼을 Leaf 페이지에 같이 저장하기 때문에 테이블을 랜덤 엑세스하지 않는다(Lockup과정X)

-       Leaf 페이지를 찾으면 바로 모든 컬럼의 값을 얻을 수 있으며 인덱스로 정렬이 되어 있다, 모든 데이터가 인덱스 키 컬럼 순으로 물리적으로 정렬

전체 데이터 중에 일부의 데이터를 찾는다면 인덱스를 이용해 원하는 데이터를 쉽게 찾지만 테이블의 데이터를 찾을 때는 하나의 블록씩 읽는 인덱스 스캔 방식보다는 여러 블록씩 읽은 전체 테이블 방식이 유리할 수도 있다.

l  조인

두 개 이상의 테이블을 하나의 집합으로 만드는 연산이다. FROM 절에 두 개 이상의 테이블이 나열이 되면 테이블 순서에 따라 조인이 이루어 진다. 예를 들어 A, B, C 3개의 테이블을 조인하게 되면 순서에 따라 A와 B를 먼저 조인해서 그 결과와 C를 조인한다. A, C, B 순서일 경우는 A와 C를 먼저하고 B를 나중에 한다.

l  DB의 조인

논리적: 내부조인(Natural, Inner) 외부조인(Outer), 크로스조인

물리적: NL 조인, Sort merge Join, Hash 조인(그 밖에 더 있으나 여기까지만)

실행계획을 이해했고 조인의 기본적인 처리 절차를 파악해둠으로써 나중에 조인을 깊이 이해 하기 위한 사전준비일 뿐이다…(책에서)

1. 내포조인

2. 정렬병합

3. 해시조인

4. 세미조인(다루지 않음)

5. 카티전 조인(다루지 않음)

6. 아우터 조인(다루지 않음)

7. 인덱스 조인(다루지 않음)

기본적으로 조인의 원리를 생각하려면 SQL 즉 코드로 논리적인 조인을 만들 것이고 그 조인을 구성하는 Table 즉 대상으로부터 data를 취득할 때 DBMS가 어떻게 데이터를 취합하고 어떻게 데이터를 저장하고 그 저장된 데이터에서 원하는 조건절에 맞게 가져오는 지 궁금증을 가지는 데에서부터 시작한다.

즉 어떻게 내부조인이나 외부조인 같은 방법을 사용해서 쿼리(질의)를 하고 그 결과는 DBMS에서 출력해 주며 디스크 or 메모리 상에서 어떠한 연산(알고리즘) 에 의해 빠른 속도 or 느린 속도로 결과를 만들어주게 되는 것인가?

여기서 어떠한 연산?? 이 부분을 아는 것이 물리적인 조인의 수행 원리를 아는 목표이다.

기본적으로 생각 해야 하는 것은 적어도 우리는 이미 설계가 완료된 테이블로 구성된 DB에서 작업이 일어날 것이며 그렇기 때문에 정규테이블간의 관계에 따라 필요한 데이터를 얻기 위해 조인을 하게 되고 그때 그 테이블을 필터하는 범위나 컬럼의 선택도나 사용하고 있는 인덱스에 의해 쿼리의 성능이 정해진다

자 그렇다면 1. 내포조인 2. 정렬병합 3. 해시조인에 대해 간략히 언급하고 넘어간다

 일단 기본은 내포조인이다. 즉 중첩된 루프를 돌면서 Outer table에서 Inner Table로 조건이 일치되는 data를 찾는 것이 가장 보편적으로 사용되는 조인이다. NL 조인은 중첩된 반복문과 유사하다

FOR 선행 테이블 읽음 --> 외부 테이블(Outer Table)

     FOR 후행 테이블 읽음 --> 내부 테이블(Inner Table)

          (선행 테이블과 후행 테이블 조인)

여기서 기본적으로 사용되는 자료구조는 B+Tree 이며 Root 부터 Branch를 지나 Leaf를 찾는 것이 알고리즘의 핵심이다. 이 알고리즘을 사용할 때 정렬되냐 정렬이 안되냐에 따른 차이는 있지만 기본적으로 NL 조인은 랜덤엑세스를 해서 찾아간다 그래서 (Index)Table Scan을 하면 안 되고 Index Seek or Range Scan을 해서 범위로 접근해야 기본적인 성능이 좋다고 본다

그 한번의 랜덤액세스가 중첩된 Roof를 돌면서 반복되므로 Scan이 아닌 Seek or Range를 가지는 부분이 성능의 핵심이다.

대량의 데이터 조인시 랜덤 액세스가 많이 발생되므로 인덱스 유무에 따라 성능 차이가 많이 난다 주로 소량의 데이터나 부분범위 처리에 적합하다. NL조인의 경우 인덱스가 있어도 드라이빙 집합(Target)의 개수가 많아 시작단계에서 랜덤 액세스가 많으면 성능상 좋지 않을 수 있다.

Sort Merge의 경우는 위의 Case에 정렬된 자료구조를 가지고 있다고 생각하면 된다 즉 내부적으로 정렬 프로세스를 거치니 초기 작업은 오래 걸려도 실제로 Merge 하는 작업은 적게 걸릴 수 있다. 

NL의 단점을 극복하기 위해 생겼다고 한다 각각 조인하는 대상으로부터 결과 집합을 정렬하는 과정을 거치고 양쪽을 개별적으로 읽어들 인 다음 Merge하는 것으로 NL 조인보다 인덱스의 영향을 적게 받는다

Hash의 경우는 Sort Merge가 정렬프로세스를 가지고 있어서 정렬의 단점을 극복하려고 생겼다고 한다 즉 이 부분은 알고리즘적으로 접근해야 하는데 O(logn)도 충분히 훌륭한 것이지만 해시 맵을 만들면 해시 알고리즘은 O(N)이기 때문에 정렬을 하지 않고 검색이 가능하다고 한다. 단 해시결과가 유일한 지 검증하는 부분이 추가되므로 꼭 O(N)은 아니라고 한다 어쨌든 데이터가 무지막지하게 많아서 알고리즘 적으로 해시 맵을 만들어서 검색하는 것이 더 탁월해 사용하는 조인방법이다. 조인 컬럼에 적당한 인덱스가 없어 NL조인이 비효율적이고 소트부하가 심할 때 고려된다.

NL 조인은

랜덤액세스 기반이어서 대량의 데이터 조인시 불리

인덱스 유뮤에 따라 성능 차이가 많이 남

선행 테이블의 결과 집합에 따라 속도에 영향을 많이 받음

소량의 데이터나 부분범위 처리에 적합

소트 머지 조인은

집합을 정렬하는 과정을 거침

양쪽을 개별적으로 읽어 들여서 한번에 두 테이블을 연결하는 NL조인에 비해 인덱스의 영향을 적게 받음

해시조인은

선행 테이블의 조인 키를 기준으로 해쉬 함수를 적용

조인컬럼에 적당한 인덱스가 없어 NL 조인이 비효율적일 때

인덱스가 있어도 드라이빙 집합의 개수가 매우 많아 랜덤액세스가 많을 때

소트 부하가 심할 때

수행 빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때

하드웨어 자원이 좋을 때(메모리에 올려서 빠른 처리가 가능)

조인은 기본적으로 복수의 대상간 각각의 내부 구현방법으로 조건이 맞는 경우를 찾는 것이고 NL의 경우 B+Tree 구조상에 랜덤액세스의 부담 때문에 그 부담을 줄이고자 정렬된 상태에서 액세스를 하는 Sort Merge 조인이 생겨났고 정렬을 미리 해야 하는 전 처리에 대한 부담으로 유일한 해시값을 생성해서 그 해시를 이용해 조인을 하는 것이 해시 조인이다. 

여기서 중요한 것은 인덱스 설계를 잘하는 것이다 인덱스에 따라 옵티마이저가 보다 좋은 액세스경로를 찾을 수 있게 하는 근본적인 방법이다. 인덱스는 현재 발생할 수 있는 상황과 데이터의 분포도나 결합도 선택도 등을 고려해서 설계되어야 한다

본론 à PPT or Test Case (따로 작성)

위에서 언급한 내용으로 확인 한 순서

물리적인(DBMS) 조인 수행 원리 à DBMS 내부에서 SW 스스로 동작하는 방식

옵티마이저 à 조인을 제어하는 DBMS의 엔진

카디널리티(선택도) à 옵티마이저가 실행계획 작성에 활용하는 통계 data

확률 à 선택도의 개념이 사건이 발생하는 확률의 개념과 비슷

인덱스 à 실행계획을 변경시킬 수 있는 Input 값

조인 방법 à 논리적 조인 / 물리적 조인의 구분

자료구조(B+Tree) à 인덱스 검색의 기본 방식

알고리즘 à 해시 조인의 복잡도가 O(1)이기 때문에(자료의 수와 관계X) 대량 검색에 탁월

Sort, Loop의 부담이 없음

인덱스 설계 à 선두 컬럼의 사용, 유일 인덱스 여부 고려, 검색조건 확인, 출력 값 확인, 조인 컬럼 확인

성능(튜닝) à 아직 도달하지 못한 부분 튜닝 전에 트랜잭션이나 동시성제어(Lock)에 대한 선행 필요

통계적 테스트 확인

1.     카디널리티가 큰 집합의 경우 큰 à 작은 순과 작은 à 큰 순으로 인덱스 생성 후 비교

     범위를 제한다 제낀다?? 라는 부분이 성능에 영향이 있는지 가설과 확인

카디널리티에 대한 성능 테스트

출처: http://jojoldu.tistory.com/243