Database 인덱스

어떻게 DBMS가 SQL 쿼리를 실행하는지에 대해서 알아 볼 것이다.

어떻게 '인덱스'라는 것으로 쿼리를 최적화 하는지를 다룰 것이다.

 

1. 인덱스(Index)란 무엇인가

출처 : Udemy 강의

 - 인덱스란 DBMS나 SQL을 사용하는 툴이 제공하는 피처이다. 이 인덱스를 통해서 쿼리의 속도와 성능을 증가 시킬 수 있다. WHERE문을 사용하여 조건에 해당하는 것을 쿼리할 때, 인덱스가 이 검색하는 것을 도와주는 역할을 한다. 

WHERE문이 없는 쿼리는 테이블 전체를 가져 오기 때문에 더 빠르게 하지 않는다.

위의 그림에서 볼 수 있듯, WHERE + 조건을 통해서 쿼리 할 때, 테이블의 모든 row를 기준에 맞는지 검색해야 하는데, entry가 많고 큰 테이블일 경우 이 모든 row를 검색하는 것이 매우 느리고 비효율적이 될 수 있기 때문에 이 때 index를 사용하는 것이 성능 개선에 도움이 될 수 있다는 것이다.

table의 어떠한 column에도 index를 생성 할 수 있고, 위의 그림에서는 salary column에 대해서 별도의 index 테이블로 저장하여 추가적인 index list를 정렬된 기준으로 만들어, DBMS가 쿼리시 실제 테이블의 어떤 row가 index값에 속하는지를 추적하는 것이다. index는 정렬되어 있기 때문에, 쿼리를 실행하면 DBMS가 users 테이블로 바로 가는 것이 아닌, salary index를 보고 DBMS가 조건에 맞는 것을 찾아 특정 부분만 검색하는 것이다.

이렇게 함으로써 모든 index 값과 테이블의 row를 거칠 필요 없이 정렬된 기준에 따라 관련된 부분만 보면 되는 것이다 .

 

2. 그럼 왜 자동으로 Index를 만들지 않는가

위에서 index가 무엇이고, 왜 사용하는지에 대해서 알아보았다.

그러면 이렇게 성능을 개선시키는 index를 왜 모든 테이블 column에 자동으로 만들지 않는것인가?

Don't Use Too Many Indexes

출처 : Udemy 강의

문제는 너무 많은 index를 만들면 오히려 성능이 더 떨어지는 결과를 불러 일으킬 수 있다.

update와 delete시가 문제인데, Index 값이 변경되거나 삭제될 때, 기존의 테이블과 index 테이블 모두에서 변경되어야 하기 때문에 결국 쿼리가 더 느려지며 성능이 저하되게 되는 것이다.

따라서 column에 index를 사용하고 싶다면, where문을 많이 쓰는 곳에 사용하고, 빈번한 update가 일어나지 않는 곳에 사용해야 한다.

 

3. EXPLAIN과 EXPLAIN ANALYZE

쿼리의 성능을 확인하고, SQL 엔진과 DBMS가 명령어를 어떻게 실행하는지에 대한 정보를 확인 할 수 있다.

 

(1) EXPLAIN 키워드

 : 어떻게 under the hood에서 실행되는지 설명을 받을 수 있다.

EXPLAIN
SELECT * FROM addresses
WHERE street = 'Teststreet' AND city = 'Munich';

위 코드를 실행한 결과 (Postgresql)

(2) EXPLAIN ANALYZE

 : plan만 주는 것이 아니라, 실행 후 실제 실행 데이터까지 준다.

※ planning이란 : sq엔진이 어떻게 최적화된 최고의 결과를 얻을 수 있는지 결정하는 단계이다. (index를 사욯해야하는지 말아야 하는지 등)

EXPLAIN ANALYZE
SELECT * FROM addresses
WHERE street = 'Teststreet' AND city = 'Munich';

위 코드를 실행한 결과를 csv파일에 저장 (Postgresql)

이렇게 EXPLAIN으로, index를 어디에 넣어야 실행 했을때 빨리지는 지를 알 수 있다.

항상 사용하는 DBMS documentation을 참고 하자

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

https://www.postgresql.org/docs/current/sql-explain.html

EXPLAIN

 

4. Index의 타입과 생성하는 방법.

(1) Index의 타입

1) 기술적인 구현 (Technical Implementation)

index를 어떻게 기술적으로 구현할 것인지, 어떠한 알고리즘을 사용하고 다른 비교 연산을 지원하는지, 어떤 것을 default로 사용하고 어떤것을 수동으로 설정할 수 있는지는 DBMS에 따라 달렸다.

B-TREE, HASH, GIST 등이 있으며, MySQL과 PostgreSQL은 default로 B-TREE를 사용한다.

 

(2) Index를 기능적인 분류

기능적으로 목적에 따라 다르게 사용되며, data 값의 종류에 따라서도 다르게 사용된다.

 

1) single-column index (가장 기본적인 타입)

 : 위의 예시에서 확인한 것 처럼 한 column에 대해서 인덱스를 생성하는 것이다.

생성하는 방법은 다음과 같다.

CREATE INDEX <index_name> ON <table_name> (<column_name>);

삭제하는 방법은 다음과 같다.

DROP INDEX <index_name>;

위 첫번째 그림에 대한 예제처럼, index를 생성하는데, Index를 생성하기 전과 생성 한 후의 EXPLAIN ANALYZE를 통해 성능 분석을 해보면 결과는 나는 다음과 같았다.

CREATE INDEX salaryidx ON users (salary)

Index 생성전 Postgresql

Index 생성후 Postgresql

결과를 보면, 여전히 index를 생성 후에도 모든 row를 도는 sequential scan을 하는 것을 볼 수 있다.

이는 데이터베이스 엔진이 Index가 있다고 무조건 가져다 쓰는것이 아니라, 데이터양이 적다 보니, index를 사용하지 않는 것이 빠를거라 판단하여 사용하지 않은 것이다. 데이터가 많으면 사용하게 될 것이다

하지만 MySQL의 경우를 보자.

(INDEX생성 전) "EXPLAIN" "-> Filter: (users.salary > 12000)  (cost=0.85 rows=2) (actual time=0.209..0.216 rows=3 loops=1)     -> Table scan on users  (cost=0.85 rows=6) (actual time=0.203..0.211 rows=6 loops=1) "

(INDEX생성 후) "EXPLAIN" "-> Index range scan on users using salaryidx, with index condition: (users.salary > 12000)  (cost=1.61 rows=3) (actual time=0.036..0.039 rows=3 loops=1) "

MySQL의 경우 인덱스 생성 후 index range scan으로 변경 되어 있는 것을 확인 할 수 있다.

2) Unique index

 : 최적화(optimization)뿐 아니라, 추가적인 protection도 받을 수 있는데, 값이 한번 이상 삽입될 수 없다. 

즉, where문에 최적화 시키는 것 뿐만 아니라, 중복값으로 부터 보호도 가능하다

users테이블의 email과 같은 곳에 사용하면 된다. 

생성하는 방법은 

A. CREATE TABLE시 UNIQUE constraint를 추가 하면 된다. 이때까지 사용했던 UNIQUE 키워드는 알고 보니 unique index를 생성하고 있었떤 것이다. 

B. 별도의 Index 생성

CREATE UNIQUE INDEX <index_name> ON <table_name> (<column_name>);

모든 인덱스에 사용하는 것이 아니라 

① WHERE 문으로 최적화가 필요하고 (Filtering)

② 중복된 값이 없는 곳에 사용하면 된다.

 

3) Multi-column index

: 여러 column을 단순히 그룹화 시켜 생성한 index로서, where문이 여러 조건이 결합되어 있을때 사용한다.

EXPLAIN ANALYZE
SELECT * FROM addresses
WHERE street = 'Teststreet' AND city = 'Munich';

위와 같이 두 column이 꽤 자주 사용된다는 것을 알고 최적화 시키고 싶다면, 두개의 single-column index로 streetidx, cityidx를 각각 생성할 수 있따. 하지만 이 두 column은 결합해 사용의 빈도가 높다면, multi-column index를 사용하는것이 더 바람직하다.

column의 조합을 최적화하는 것을 가능하게 하기 때문에, column의 조합이 WHERE문에서 사용될때 사용하는것이 바람직하다.

설정 방법은 다음과 같다.

CREATE INDEX multiaddr ON addresses (street, city);

이때 유의해야 할점은 순서가 중요한 것인데, Index의 생성 전후의 아래 코드 EXPLAIN ANALYZE의 결과를 보면 다음과 같다.

EXPLAIN ANALYZE
SELECT * FROM addresses
WHERE street = 'Teststreet' AND city = 'Munich';

(INDEX 생성 전) "EXPLAIN" "-> Filter: ((addresses.city = 'Munich') and (addresses.street = 'Teststreet'))  (cost=0.85 rows=1) (actual time=0.048..0.058 rows=3 loops=1)     -> Table scan on addresses  (cost=0.85 rows=6) (actual time=0.041..0.048 rows=6 loops=1) "

(INDEX 생성 후) "EXPLAIN" "-> Index lookup on addresses using multiaddr (street='Teststreet', city='Munich')  (cost=0.80 rows=3) (actual time=0.102..0.104 rows=3 loops=1) "

인덱스를 생성 후 결과를 보면 Index lookup이라고 index를 사용한 것을 볼 수 있으며 cost가 감소한 것을 볼 수 있다. 

 

※ multi column index를 이루는 개별 column을 포함하는 WHERE 문에서도 사용이 가능하다.

예를들어, WHERE street = "Teststreet"; 이라고만 street의 조건만 설정해도, index가 사용된다.

하지만 WHERE city = "Munich"; 라고 city 조건만 설정하면 index가 사용되지 않는다. 

이것이 바로 순서가 중요한 이유이다.

여러 column으로 multi -column index를 만들면, 쿼리에서 설정한 column들의 조합이 사용되는 곳에 최적화를 시켜준다. 특히 이러한 column의 조합으로 이루어진 WHERE의 AND문으로 연결된 곳에서 최적화를 시켜준다.

multi-column index의 일부를 사용할 수도 있는데, 이것은 왼쪽에서 오른쪽으로 사용이 가능하다.

따라서 OR로 이어진 WHERE문에서는 사용이 불가능 하며, 처음 설정한 column인 street column에서만 사용이 가능하다. (column_A, column_B, column_C) 설정 시 column A의 조건, colum_A와 column_B의 AND 조건,  colum_A와 column_B, column_C의 AND 조건에서만 사용이 가능한 것이다.

 

4) Partial index (MySQL에서는 지원 X)

: 테이블의 일부만 index하는데 사용할 수 있다. column을 지정하고, 모든 row에 대해서 index를 생성하는 것이 아니라, 특정 row에 대해서만 index를 설정 가능하다. 

예를들어 salary가 12000이상인 것만 partial index로 만들 수 있다. 

salary가 12000이하인 곳에서는 쿼리가 많이 돌지 않을 때. 12000이상일때 WHERE를 통한 필터링이 많이 이루어 질때 모든 salary를 index에 저장하는 것이 비효율 적이므로, 이럴 경우 사용 된다.  

생성 방법은 다음과 같다.

CREATE INDEX <index_name> ON <table_name> (<column_name>)
WHERE salary > 12000;

 

Reference: 

Udemy 강의 :

https://www.udemy.com/course/sql-the-complete-developers-guide-mysql-postgresql/

SQL - The Complete Developer's Guide (MySQL, PostgreSQL)