정렬제거를 위한 인덱스 선정 방법

Tuning/INDEX

cororo2 2023. 9. 21. 13:33

인덱스를 이용해야 우리가 원하는 조회 성능을 보장 받을 수 있음
하지만 인덱스를 많이 생성하면 여러가지 부작용 발생
인덱스 부작용을 최대한 방지하려면 최적의 결합 칼럼 인덱스를 생성해야 하며 단일 칼럼 인덱스는 자제해야함

TABLE : 데이터가 INSERT되는 순서에 의해 저장되므로 어떤 칼럼에 의해 정렬된 데이터 저장되지 않음

INDEX : 인덱스를 구성하는 칼럼에 의해 정렬된 데이터 저장

- 인덱스의 첫번째 칼럼에 의해 정렬

- 첫번째 칼럼의 값이 동일한 데이터에 대해서 인덱스의 두번째 칼럼에 의해 정렬

- 위의 특징으로 인해 인덱스는 전체적으로 첫번쨰 칼럼에 의해 정렬된 데이터가 저장되며 두번째 세번째 칼럼에 는 정렬된 데이터가 저장되지 않음

<사전의 예시> 사전을 확인하면 A-Z 순서로 정렬, A로 시작하는 단어는 두번째 단어로 정렬

A-Z로 정렬된 후 AA, AB, ..., AZ로 끝남

AA로 시작하는 단어 중 AAA가 가장 먼저 나옴

DB의 인덱스도 사전의 인덱스와 동일

DB의 모든 정렬을 제거한다는것은 어려운일
모든 정렬을 제고한다면 시스템 부하가 없기때문에 엄청난 성능이 향상되지만 SQL은 인덱스에 의해 제어되어 모르는 사람이 운영하면 규칙이 무너질 수 있음
규칙이 무너지면 시스템은 문제가 발생할 수 있어 SQL에서 모든 정렬을 제거하는 것도 위험요소가 됨

이상적인 방법은 중요한 SQL문에 대해서만 인덱스를 이용해 정렬을 제거하는것

SELECT 카드번호, 사용액
  FROM 거래내역
 WHERE 카드번호 = '111'
 ORDER BY 거래일자 ;

SELECT 카드번호, 사용액
  FROM 거레내역
 WHERE 카드번호 = '111'
  AND 거래일자 BETWEEN '20080801' AND '20080830'
 ORDER BY 승인번호;

기존대로 인덱스를 WHERE절의 조건과 ORDER BY 절에 사용된 칼럼의 조합(카드번호 + 거래일자 + 승인번호 )으로 인덱스 생성시 정렬된 데이터는 추출되지 않음
이유는 거래일자 조건의 값이 하나의 값만을 의미하지 않아 승인번호 칼럼의 값으로 정렬된 데이터가 추출되지 않음
자동으로 승인 번호 칼럼의 값으로 정렬된 데이터를 추출하고자 한다면 아래와 같은 인덱스를 생성해야함
- 카드번호+승인번호+거래일자 인덱스
- 카드번호+승인번호 인덱스

-> 위와 같이 생하면 인덱스의 첫번째 칼럼이 하나의 값을 의미하여 인덱스의 두번째 칼럼인 승인번호 칼럼의 값으로 정렬된 데이터가 추출됨

카드번호+승인번호+거래일자 인덱스의 거래일자 칼럼은 처리 범위를 감소시키지는 못하며 확인 랜덤 액세스를 제거

정렬을 위한 인덱스는 다음과 같은 형태로 생성해야함

점 조건 + ...+점 조건+ ORDER BY 절의 첫번째 컬럼 + ORDER BY 절의 N번째 칼럼+선분 조건+...+선분조건

ORDER BY절에 의해 정렬된 데이터가 자동으로 추출되며 모든 선분 조건들은 랜덤 액세스만을 제거하는 조건이 됨

Data Study

postgreSQL #구루비,

Data Study