DBeaver)index 만들기 / 성능평가

library.zip

2.66MB

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

빨리 강의하단에 있는 library.csv 파일 가져와서 얼마나 SELECT 성능이 향상되는지 테스트해봅시다.

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

 

 

▲ 근데 csv파일 가져오기할 때 Configure 메뉴에서 데이터타입을 varchar(300) 이상으로 설정해야 잘 가져올 수 있습니다.

책이랑 저자 이름 긴게 좀 많아서요 

 

 

 

 

 

 

 

 

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

 

 

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

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

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

 

 

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

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

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

- 실은 MySQL Workbench 프로그램이 훨씬 보기쉽게 분석을 해주기 때문에 그거 써보는 것도 좋습니다. 

- 아니면 SELECT 쿼리문 왼쪽에 EXPLAIN 키워드를 붙여 실행해도 비슷합니다. 

 

 

 

▲ 이것저것 눌러보면 우측에 이런 화면이 뜨는데 전부 알 필요는 없고 중요한 것만 보면 됩니다. 

 

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

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

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

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

 

access_type 혹은 type은 ALL만 피하면 됩니다. 

ALL이 기록되어있으면 테이블 전체 행을 full scan한다는 뜻이라 성능이 좋지 않습니다. 

ALL만 안나오면 됩니다. 

여기 들어갈 것들은 index, range, ref, const 이런 것들이 있습니다. 

 

 

filtered는 컴퓨터가 읽은 행을 출력결과에 넣는 비율인데

예를 들어 10개의 행을 읽었는데 1개의 행만 출력결과에 넣으면 10%로 계산됩니다. 

그래서 100%에 가까울 수록 좋습니다. 수치가 정확한건 아니라 보조지표로 활용하면 좋습니다. 

 

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

 

 

 

 

 

 

 

 

테이블에 index 생성하려면

 

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

index 만드는 법은 쉬운데 

 

 

 

▲ DBeaver에선 테이블마다 Indexes 메뉴가 있습니다.

거기서 우클릭하면 index 하나 생성가능 

 

 

 

 

▲ 그럼 창이 하나 뜨는데 원하는 컬럼 하나 선택해서 확인 누르고, 저장도 하면 끝입니다.

 

- 참고로 Btree(B+tree) 말고 다른 식으로 index를 만들 수 있는데

Rtree는 2차원 좌표값을 저장한 컬럼일 때 사용하고 (예를 들어 위도/경도) 

Full text는 긴 문장에서 원하는 단어 빠르게 찾고 싶을 때 씁니다. 글 검색기능만들 때 주로 사용합니다.

나머지는 중요하지 않아서 궁금하면 찾아봅시다. 

 

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

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

 

 

 

 

 

 

 

 

 

index 만든 후 성능을 다시 평가해보자 

 

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

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

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

 

 

 

 

▲ 아까는 cost가 6000얼마였는데 지금은 1로 감소했습니다.

access type도 ALL에서 ref 이런걸로 변경이 되었습니다. 

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

 

 

 

 

 

 

 

▲ MySQL workbench에서 쿼리문 작성 & 실행하고 우측 Execution plan 눌러보면 더 상세히 조회가능합니다. 

쿼리문이 길고 복잡할 수록 Workbench 켜서 분석해보는게 좋습니다. 실행 순서 그림도 그려줌 

 

 

 

 

 

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

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

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

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

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

 

 

 

 

 

 

PRIMARY KEY인 경우

 

ALTER TABLE library ADD COLUMN id int PRIMARY KEY AUTO_INCREMENT; 

이런 쿼리문 실행해서 library 테이블에 primary key 역할을 하는 id 컬럼을 생성해봅시다. 

그리고 id = 어쩌구인 컬럼을 찾으라고 쿼리문을 한 번 써봅시다.

이 쿼리문은 분석해보면 access_type이 const 이런 식으로 나올텐데 

NOT NULL + primary key 역할을 하는 컬럼은 = 등호로 검색시 const가 뜹니다. 빠르고 좋다는 뜻임 

 

그래서 primary key 컬럼은 기본적으로 검색이 매우 빠르기 때문에 index 만들 필요는 없습니다. 

 

 

 

 

 

다중컬럼 index

 

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

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

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

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

 

 

 

index 만들 때 여러개의 컬럼도 선택할 수 있습니다. 

 

 

 

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

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

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

 

이렇게 해두면 뭐가 좋냐면 

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

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

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

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

 

 

 

 

 

예를 들어 (name, age) 순으로 다중컬럼 index를 만들어놓았다면 

 

WHERE name = ? AND age = ? 

이런 쿼리를 작성할 때 그 (name, age) index를 사용해줍니다.

name, age를 각각 별도의 index로 만들어놓는 경우보다 쿼리 속도가 더 빠를 수 있음 

 

 

WHERE name = ? 

실은 이런 쿼리에도 그 (name, age) index를 사용해줍니다. (다른 DBMS는 아닐 수도)

 

 

WHERE age = ? 

이런 쿼리엔 (name, age) index 를 사용할 수 없습니다. 

그래서 다중컬럼 index는 컬럼넣는 순서도 중요합니다. 

 

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

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

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

참고하도록 합시다. 

 

 

 

 

 

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

 

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

실행하면 index 생성해줍니다. 쉬움 

 

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

다중컬럼 index 생성은 이렇게합니다.