인덱스
- 테이블의 데이터를 빠르게 찾기 위해 별도로 만들어 두는 정렬된 자료구조
- 인덱스는 조회 성능을 높이는 대신 저장 공간을 사용하고 INSERT, UPDATE, DELETE 비용을 증가시킴
=> 즉, 조회 성능 높이는 대신 저장 공간 소비하고 데이터 변경 작업 비용을 증가시키는 트레이드오프를 가짐
인덱스 관리
- 인덱스는 항상 최신의 정렬 상태를 유지해야 함.
그래서 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행되면 추가적인 연산을 해주어야하고 이로 인해서 오버헤드가 발생함.
[DML 비용 증가]
- INSERT : 새로운 데이터에 대한 인덱스 추가
- DELETE : 데이터를 삭제해도 인덱스 공간에서 즉시 지워지지 않고 '사용 안 함' 처리 됨 -> 인덱스 크기는 줄어들지 않음
- UPDATE : 데이터 수정되면 기존 인덱스 값을 삭제하고 새로운 위치에 인덱스를 생성하는 INSERT+DELETE 동시 발생
!!! 그래서 효율적인 DB 관리를 위해서는 읽기(조회) 비율이 압도적으로 높은 컬럼에만 인덱스를 생성
쓰기가 빈번한 테이블에는 인덱스를 최소화하는 설계
테이블에 인덱스가 걸려있지 않다면 컬럼 개수가 N개일 때 시간 복잡도 O(N)
테이블에 인덱스가 걸려있다면 시간복잡도 O(logN) -> B-tree 기반 인덱스
< 인덱스 사용 이유>
- 특정 조건들을 만족하는 데이터들을 빠르게 찾기 위함
인덱스 만들기
1. 이미 생성된 테이블에 인덱스 추가
player 테이블
id, name, team_id, backnumber
SELECT * FROM player WHERE name = 'sonny';
SELECT * FROM player WHERE team_id = 105 and backnumber = 7;
위의 쿼리에 대해서 각각 인덱스를 만들어보자
CREATE INDEX player_name_indx ON player(name); -> 이름에는 동명이인이 있을 수 있으므로 UNIQUE 불
CREATE UNIQUE INDEX team_backnumber_indx ON player(team_id, backnumber);
2. 테이블 생성시 인덱스도 함께 생성 -> 인덱스 이름은 지정 안 해도 자동으로 생성됨
INDEX player_name_indx (name),
INDEX UNIQUE team_backnumber_indx (team_id, backnumber)
< 특징 >
1) Multicolumn index/ composite index
-> INDEX UNIQUE team_backnumber_indx (team_id, backnumber) -> 이렇게 컬럼이 여러 개 있는 인덱스
2) primary key에는 index가 자동으로 생성됨
3) 테이블에 걸려있는 인덱스 정보를 알고 싶다면 SHOW INDEX FROM 테이블 명;
B-tree 기반의 index가 동작하는 방식
1)
a에 대한 인덱스를 생성하면 a에 대한 값들이 정렬이 된 형태로 저장
ptr : 포인터 -> 실제 members 테이블의 어떤 튜플과 연관되어있는지
if) a =9 인 튜플을 찾고 싶다
- 바이너리 서치를 사용
-> 중간 값을 잡음 -> 5보다 작은 값은 다 버림 -> 중간 값 위쪽의 데이터들에 중간값을 잡음 -> 9를 찾으면
ptr를 활용해서 실제 members 테이블의 튜플을 선택
2)
-> 위와 같은 사례를 방지하기 위해서는 a와 b를 하나로 묶은 인덱스가 추가로 필요함
3)
CREATE INDEX(a, b)
-> a에 대해서 먼저 정렬하고, a가 같은 값일 경우 b에 대해서 정렬
즉, Multicolumn index로 인덱스를 생성하면 인덱스의 attribute 순서가 중요하다는 점
4)
이런 경우에는 성능이 안나옴
=> a를 정렬한 후에 b를 정렬해야되는데 a에 대한 조건이 없고 b가 정렬이 되어있지 않으므로 INDEX(a,b)가 안 쓰이거나, 사용하더라도 성능이 안 나옴
즉, full scan 하는 거랑 거의 동일하거나 혹은 더 안 좋을 수 있음
=> 그래서 b에 대해서 인덱스를 따로 만들어줘야 함
[예제]
아래의 두 쿼리를 만족하는 인덱스는 없음
세 번째 인덱스를 사용한다고 하더라도 fullscan이 필요함
그리고 마지막 쿼리에서는 OR를 놓치면 안됨 -> OR는 또는 이므로 team_id =110에 대해서는 세 번째 인덱스로 처리할 수 있지만 backnumber =7은 fullscan을 해야됨
=> 사용되는 quey에 맞춰서 적절하게 index를 걸어줘야 query가 빠르게 처리될 수 있다.
DBMS의 optimizer가 알아서 적절하게 index를 선택
내가 직접 index를 고르고 싶다면?
1) 가급적 이 인덱스를 사용해 주세요
SELECT * FROM player USE INDEX(backnumber_idx)
WHERE backnumber = 7;
2) 이 인덱스를 쓰세요
SELECT * FROM player FORCE INDEX(backnumber_idx)
WHERE backnumber = 7;
3) 특정 인덱스 제외시키고 싶을 때는
IGNORE INDEX 사용
그러면 내가 마음대로 막 INDEX 만들어도 되는 거?
- 테이블에 WRITE할 때마다 INDEX도 변경 발생
- 인덱스를 생성하면 인덱스를 위한 데이터도 저장이 되어야 하므로 추가적인 저장 공간이 필요함
=> 불필요한 INDEX를 만들지 말자
Covering index
team_id와 backnumber를 가져올 건데 두 컬럼은 인덱스 안에 포함되어 있는 multicolumn index임
그러면, team_id =5에 해당하는 값을 찾아서 player 테이블에 가서 찾아올 필요가 없음
왜? 가져오려는 정보가 인덱스 안에 다 포함이 되어있기 때문에
-> 조회하는 attributes를 index가 모두 cover할 때
-> 조회 성능이 더 빠름
Hash index
1) hash table 사용해서 index 구현
2) 시간 복잡도 O(1)의 성능
3) rehashing에 대한 부담
=> rehashing : 데이터가 Hash table에 array 형태로 저장되어 있을텐데, 데이터가 쌓이면 hash table을 늘려줘야함
4) equality 비교만 가능, range 비교 불가능 -> 즉, = 와 != 만 가능하고 <=와 >= 는 불가
=> 범위를 비교하는 조건으로 튜플을 선택하는 것은 불가능함.
5) multicolumn index의 경우 전체 attributes에 대한 조회만 가능/ 속성 중 일부분만 사용하는 것은 불가
=> B-TREE기반 인덱스인 경우에는 (a, b)가 있을 때 a로만 조회를 하고 싶을 때도 (a, b) 인덱스를 사용할 수 있었는데
hash index에서는 이것이 불가능, (a, b) 모두를 포함하는 조건이어야만 사용 가능
Full scan이 더 좋은 경우
???? 누가 결정 ???? => optimizer
1) 테이블에 데이터가 조금 있는 경우 : 몇 십 ~ 몇 백
2) 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
=> 만약에 SELECT * FROM customer WHERE mobile_carrier = 'SK';
SK 사용자가 많을 것임 -> 이럴 경우라는 것
추가
1) order by나 group by에도 index가 사용될 수 있다
2) foreign key는 index가 자동으로 생성되지 않을 수 있다 (mysql은 자동 생성)
3) 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우에는 DB 성능에 안좋은 영향을 줄 수 있음