DB Index

index가 중요한 이유

예를 들어 다음의 select query 발생시, first_name에 index가 걸려있다면 full scan보다 더 빨리 찾을 수 있다.

• full scan : O(N)
• B tree based Index : O(log N)

SELECT *
FROM customer
WHERE first_name = 'Minsoo';

즉, index를 사용하는 이유는 다음과 같다.

• 특정 조건을 만족하는 튜플(들)을 빠르게 조회하기 위해서이다.
• 빠르게 정렬(order by)하거나 그룹핑(group by)하기 위해서이다.

index 거는 법

이름과 같이 중복을 허용하는 index를 만드는 방법은 다음과 같다.

CREATE INDEX player_name_idx ON player (name);

SELECT * FROM player WHERE name = 'Sonny';

그리고 unique index를 만드는 방법은 다음과 같다. ( 각 tuple을 unique하게 식별할 수 있다. )

CREATE UNIQUE INDEX team_id_backnumber_idx ON player(team_id, backnumber);

SELECT * FROM player WHERE team_id = 105 and backnumber = 7;

또한 처음에 table을 생성할 때 index를 만드는 방법은 다음과 같다.

CREATE TABEL player (
	id INT PRIMARY KEY,
    name VARCHAR(2) NOT NULL,
    team_id INT,
    backnumber INT,
    INDEX player_name_idx (name),
    UNIQUE INDEX team_id_backnumber_idx (team_id, backnumber)
);

index 동작 방식

Index(a)가 있는 상황에서 a equality 조건을 찾을 때,

Index(a)만 있는 상황에서 다른 조건도 같이 찾을 때, 다음과 같이 full scan이 발생하는 성능 이슈가 있다.

따라서 multi column index를 만들면 된다. 이때, index(a,b) 이면, a를 기준으로 정렬 후에 b를 기준으로 정렬한다.

아래의 a, b column에 대한 AND 조건으로 조회를 할 때, index(a,b)를 사용하면 query가 빠르게 처리된다.

하지만 index(a,b)는 b에 대해서는 정렬이 되어있지 않기 때문에 다음과 같은 쿼리에서는 오히려 full scan 이나 혹은 그 이상의 성능 저하를 초래할 수 있다.

SEELCT * FROM player WHERE b = 7;
SELECT * FROM player WHERE a = 110 OR b = 7;

따라서 사용되는 query에 맞춰서 적절하게 index를 걸어줘야 query가 빠르게 처리될 수 있는 것이다.

query가 어떤 index를 사용하는지 확인하기

다음의 쿼리를 실행하면 query가 어떤 index를 사용하는지 확인할 수 있다.

EXPLAIN SELECT * FROM player WHERE backnumber = 7;

 

이게 가능한 것은 DBMS에 존재하는 optimizer가 알아서 적절하게 index를 선택하기 때문이다. 하지만 간혹 부적절한 index를 선택하기도 하는데 이때 수동으로 index를 선택해주는 것이 필요하다. 이때는 다음 쿼리를 실행해주면 된다.

SELECT * FROM player USE INDEX (backnumber_idx) WHERE backnumber = 7;
SELECT * FROM player FORCE INDEX (backnumber_idx) WHERE backnumber = 7;
SELECT * FROM player IGNORE INDEX (backnumber_idx) WHERE backnumber = 7;

index는 막 만들어도 괜찮을까?

결론부터 말하면 불필요한 index는 만들면 안된다. 그 이유는 다음과 같다.

• table에 write할 때마다 index도 변경이 발생한다.
• 추가적인 저장 공간을 차지한다.

Covering index

index 만으로도 찾으려는 정보를 모두 조회할 수 있다면, 즉.

• 조회하는 attribute(s)를 index가 모두 cover할 때, covering index를 사용하면 조회 성능이 더 빠르다.

SELECT team_id, backnumber FROM player WHERE team_id = 5;

Full scan이 더 좋은 경우

• table에 데이터가 조금 있을 때
• 조회하려는 데이터가 테이블의 상당 부분을 차지할 때

그 외

• order by나 group by에도 index가 사용될 수 있다.
• foreign key에는 index가 자동으로 생성되지 않을 수 있다. -> join 관련
• 이미 데이터가 몇 백만 건 이상있는 테이블에 index를 생성하는 경우, 시간이 몇 분 이상 소요될 수 있고, DB 성능에 안좋은 영향을 줄 수 있다.

인용

https://www.youtube.com/watch?v=IMDH4iAQ6zM&ab_channel=%EC%89%AC%EC%9A%B4%EC%BD%94%EB%93%9C