Index 만들기 / 성능평가

index 만들어두면 성능이 향상된다고 했는데

library.csv 파일 가져와서 얼마나 SELECT 성능이 향상되는지 테스트해봅시다.

어떤 도서관의 소장도서 6만권의 정보가 들어있는 데이터셋입니다.

7MB

library.csv

csv파일 가져오기할 때 책이랑 저자 이름 긴게 좀 많아서 Configure 메뉴에서 데이터타입을 varchar(300) 이상으로 설정해야 잘 가져올 수 있습니다. 나머지 VACHAR컬럼은 길이 100으로 설정

execution plan (실행계획) 분석해보기

SELECT * FROM library WHERE 등록번호 = 'CEM97499';

방금 가져온 테이블에서 책 아무거나 하나만 출력해봅시다.

근데 뭔가 오래걸리는 것 같으면 실행계획부터 분석해봐도 됩니다.

원하는 SELECT 쿼리문에 커서 찍고 DBeaver 상단메뉴의 SQL 편집기 - 실행계획보기를 눌러보면 실행계획이 출력됩니다.

컴퓨터가 이 쿼리문을 어떻게 실행할지 계획짜놓은걸 볼 수 있는 화면인데

이걸 보고 성능평가같은걸 할 수 있습니다.

실행계획에서 TABLE ACESS 클릭해 보면 우측에 이런 화면이 뜨는데 중요한 것 몇개만 보겠습니다.

cost는 어림잡아 걸리는 시간이라고 생각하면 됩니다. 낮을 수록 좋습니다.

cost가 몇천, 몇만 정도라면 1초에 몇백건 실행해도 부담되지는 않습니다.

하지만 10만정도 되는 경우는 많이 실행할 경우 컴퓨터에 부담을 주거나 병목현상이 일어날 수 있습니다.

그래서 그 경우엔 index를 만들거나 해서 개선하는게 좋은 방법입니다.

TABLE ACCESS (FULL)만 피하면 됩니다.

테이블 전체 행을 full scan한다는 뜻이라 성능이 좋지 않습니다.

다른 Operation은 아래 블로그에 정리되어 있습니다.

실행계획의 Operation과 OptionTISTORY

나머지는 궁금하면 execution plan 읽는 법을 찾아보도록 합시다.

테이블에 index 생성하려면

그래서 지금 cost가 너무 높다고 판단되면 검색할 컬럼의 index를 만들어두면 됩니다.

index 만드는 법은 쉬운데

테이블의 index 탭에서 우클릭 하면 아래와 같은 창이 뜹니다.

하는 컬럼 하나 선택해서 확인 누르고, 저장하면 끝입니다.

- UNIQUE 제약을 줘도 상관없는, 행마다 서로 다른 값을 가진 컬럼이라면

Unique를 체크해서 만들어두면 검색 성능이 더 빨라질 수 있습니다. Unique index라고 합니다.

index 만든 후 성능평가

SELECT * FROM library WHERE 등록번호 = 'CEM97499' 

등록번호 컬럼에 index 만들었으면 그 컬럼에서 뭔가 검색하는 쿼리문을 작성해봅시다.

그리고 커서찍고 '실행계획보기' 눌러보십쇼

아까는 cost가 400대였는데 2로 줄었습니다.

Operation 도 INDEX(RANGE SCAN)으로 바뀌었네요.

이제 안심하고 쿼리문 돌릴 수 있겠군요.

Q. 왜 WHERE 등록번호 < 'CEM97499' 이런 범위검색은 index를 안쓰나요?

A. 현재 테이블의 전체 행은 6만개인데 위 결과를 출력해보면 행이 3만개나 출현합니다.

원래 범위검색시 출력할 행들이 많아서 출력할 행이 전체 행의 20%를 넘어서면

index를 굳이 안쓰는게 더 빠르다고 DBMS가 판단해서 index 안씁니다.

강제로 쓰라고 명령줄 수도 있는데 대부분의 상황에선 DBMS를 믿도록 합시다.

다중컬럼 index

SELECT * FROM 어쩌구 WHERE name = 'park' AND age = 20  

위 예시처럼 어떤 쿼리문을 작성시 컬럼2개 이상에서 검색작업을 수행하고 있는 경우

그 컬럼들에 각각 index를 만드는 것 보다

필요한 컬럼들을 전부 묶어서 index 만들어두면 성능이 향상될 수 있습니다.

여러개 동시에 체크하면 됩니다.

님이 체크한 순서대로 묶어서 index를 만들어줍니다.

위 사진에선 (등록번호, 서명, 저자) 를 전부 묶어서 index를 만들어주겠군요.

이렇게 해두면 뭐가 좋냐면

예를 들어 여러분이 a, b, c 컬럼이 있는데

이걸 (a, b, c) 이렇게 묶어서 index를 만들어뒀다면

a, b, c 컬럼 전부에 조건 걸어주는 쿼리를 작성할 때 컴퓨터가 알아서 사용한다는 것입니다.

a, b, c 컬럼에 독립적으로 index를 만들어두는 경우보다 더 빠르게 동작해서 다중컬럼 index를 만들어씁니다.

일반적인 상황에선 cardinality (구분명확도)가 높은 컬럼을 왼쪽에 넣는게 좋은데

예를 들어서 (이름, 주민번호) 컬럼이 있으면 주민번호같이 중복이 별로 없는 컬럼을 왼쪽에 넣어서

(주민번호, 이름) 이렇게 index를 만드는게 좋다는겁니다.

참고하도록 합시다.

SQL 문법으로 index 만들고 싶으면

CREATE INDEX 인덱스이름작명 ON 테이블명 (컬럼명);

CREATE INDEX 인덱스이름작명 ON 테이블명 (컬럼명1, 컬럼명2);

이런식으로 생성하면 됩니다.