탐색할 attribute에 index 설정 여부에 따른 성능 차이
- full scan(=table scan)으로 찾아야 하기 때문에
- O(n)의 시간 복잡도가 생긴다.
- 인덱스가 걸려있다면 O(logN)(B-tree based index일 경우)의 시간 복잡도가 생긴다.
Index를 쓰는 이유
- 조건을 만족하는 튜플(들)을 빠르게 조회하기 위해
- 빠르게 정렬(order by)하거나 그룹핑(group by)하기 위해
기존 table에 index 만들어주기
1
2
3
4
5
6
7
SELECT * FROM player WHERE name = 'Sonny';
CREATE INDEX player_name_idx ON player (name); -- 이름과 같이 중복이 있을 수도 있는 attribute의 인덱스를 걸어줄 수 있다.
SELECT * FROM player WHERE team_id = 105 and backnumber = 7;
CREATE UNIQUE INDEX team_id_backnumber_idx ON player (team_id, backnumber);`
테이블 생성 시점에 index 만들어주기
1
2
3
4
5
6
7
8
CREATE TABLE Player(
id INT PRIMARY KEY, -- 대부분의 RDBMS에서는 primary key에 index가 자동으로 생성됨
name VARCHAR(20) NOT NULL,
team_id INT,
backnumber INT,
INDEX player_name_idx (name), -- 이때는 index 이름 생략 가능
UNIQUE INDEX team_id_backnumber_idx (team_id, backnumber) -- 이름 생략 가능. multicolumn index 혹은 composite index라고 부름
);
Index 조회
1
SHOW INDEX FROM player;
주의 사항
- column A, B가 있을 때
- A에 대한 Index, B에 대한 Index, (A,B)에 대한 Index 전부 다른 인덱스이다.
- 즉, (A, B)에 대한 Index만 생성했을 때, (A,B)를 연동한 탐색은 성능을 향상 시키지만 A에 대한 조회 혹은 B에 대한 조회와 같이 개별적인 탐색에서는 성능 향상이 되지 않는다.
- 특히 아래와 같은 실수를 범할 수 있다.
1
2
3
SELECT * FROM table1 WHERE A = 110 AND B = 56; -- index로 인해 조회 성능 향상
SELECT * FROM table1 WHERE A = 110 OR B = 56; -- index로 인한 성능 향상이 애매함. B에 대한 index가 존재하지 않기 때문에 B의 조건에 일치하는 것에 대한 full scan을 하게 된다. 때문에 B에 대한 index 또한 만들어줘야 성능이 좋아진다.
query 실행 시 사용되는 index 확인하기
1
EXPLAIN SELECT * FROM table1 WHERE A = 8;
- 이때 출력된 table의 key부분이 사용된 index이름이다.
- 보통은 query를 보낼 때 optimizer가 적절한 index를 선택해준다. 하지만 간혹 기대한 index를 사용하지 않을 수 있기 때문에 아래와 같이 명시해서 query를 보낼 수 있다.
1
2
3
4
5
SELECT * FROM player USE IDNEX (backnumber_idx) WHERE backnumber =9; -- 강제가 아닌 권장
SELECT * FROM player FORCE IDNEX (backnumber_idx) WHERE backnumber =9; -- 강제
SELECT * FROM player IGNORE IDNEX (backnumber_idx) WHERE backnumber =9; -- 특정 인덱스 사용 제한
Covering index
- 조회하고 출력하려는 정보들이 index 테이블에서 관리하고 있는 정보들이라면 index table 조회 후 원본 table를 탐색할 필요 없이 바로 출력 가능한 것
- 조회 성능이 더 빠르기 때문에 종종 covering index를 고려하여 index를 생성하기도 한다.
index는 막 만들어도 될까?
- table에 write할 때 마다 index도 변경 발생
- 추가적인 저장 공간 차리
- 때문에 불필요한 index는 만들지 않는 것이 좋다.
B-tree 기반의 index가 동작하는 방식
- 다음 포스팅에서 구체적으로 다룰 예정
Hash index
- hash table을 사용하여 index를 구현
- 시간 복잡도 O(1)의 성능
- rehashing에 대한 부담
- rehashing : 데이터가 많아져서 용량을 늘리는 행위
- equality 비교만 가능, range 비교 불가능
- multicolumn index의 경우 전체 attributes에 대한 조회만 가능
Full scan이 더 좋은 경우
- table에 데이터가 조금 있을 때(몇 십, 몇 백건 정도)
- 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
- 보통 optimizer가 판단해줌
그 외
- order by나 group by에도 index가 사용될 수 있다.
- foreign key에는 index가 자동으로 생성되지 않을 수 있다(join 관련)
- 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안좋은 영향을 줄 수 있다.
- 트래픽이 적은 시간에 적용해야됨