인덱스
인덱스를 왜쓸까?
인덱스는 조회성능 개선 - 디스크 I/O를 줄이는게 핵심이다.
테이블의 특정 데이터를 생성한다면, 해당 컬럼의 데이터를 정렬 후, 별도의 메모리 공간에 데이터의 물리적 주소를 Key, Value 형태로 저장한다.
많은 쿼리들의 대부분의 요청은 GET요청 방식이다. 수정 삭제에서 손해를 보더라도 검색속도와 시스템의 부하가 작아지기 때문에 사용한다.
Where 절 일때, 특정 조건의 데이터를 찾기 위해서 FULL TABLE 스캔을 해야하는데 인덱스를 통해서 전부다 조회할 필요가 없어진다.
OrderBy, MIN/MAX 시, DB에서 직접 정렬을 해야하나 인덱스가 설정된 테이블은 정렬이 되어있으므로 읽기만 하면되므로 속도가 빠르다.
GroupBy 역시 정렬되어있는상태에서 그룹핑을 하는것이 떄문에 많은 부분을 넘겨서 읽을 수 있다.
그럼 인덱스처리를 다하지 왜 다 안하는가?
앞서 말한 것 처럼 인덱스는 항상 정렬 상태를 유지해야하므로 IUD(insert,update,delete)시 추가작업이 필요하다.
INSERT: 새로운 데이터에 대한 인덱스를 추가UPDATE: 기존의 인덱스를 사용하지 않음 처리, 갱신된 데이터에 대한 인덱스 추가DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업 수행
이처럼 인덱스의 수정도 추가적으로 필요하기 때문에 데이터의 수정이 잦은 경우 성능이 낮아진다. 또 데이터의 인덱스를 제거하는 것이 아니라 ‘사용하지 않음’으로 처리하고 데이터를 남겨두기에, IUD 작업이 많은 경우 실제 데이터에 비해 인덱스가 과도하게 커지는 문제점이 발생할 수 있다. 별도의 메모리 공간에 저장되기 때문에 추가 저장 공간이 많이 필요하게 된다. 아래의 예시를 보자
- 왼쪽에 index로 정리되어있는 테이블.
- ID 2 와 4를 삭제함.
- ID에 auto increment가 적용되어있을떄 2와 4가 비어있는 공간에 6과7이 들어감.
인덱스는 전체 데이터의 10 ~ 15% 이상의 데이터를 처리하거나, 데이터의 형식에 따라 오히려 성능이 낮아질 수 있다. 예를 들어 나이나 성별과 같이 값의 range가 적은 컬럼인 경우, 인덱스를 읽고 나서 다시 많은 데이터를 조회해야 하기 때문에 비효율적이다.
인덱스를 그럼 언제 쓸까?
- 데이터의 범위가 크고
- 중복이 적고 (카디널리티가 높다, 얼마나 유니크한지)
- 조회가 빈도가 많다
- 정렬한 이점이 많은경우 사용하는것이 좋다.
- 규모가 큰 테이블
- IUD가 적은 컬럼
- JOIN, WHERE, ORDER BY가 자주 호출되는 컬럼
필자의 경우 3번과 7번에 해당되는 경우라, cloudFront 서비스에서 인기 객체 지표를 보여주는 사이트를 통해 알아보았다.
보면 memberlist 랭킹을 나타내는 지표가 홈페이지 주소다음으로 많이 요청되는 것을 볼 수 있다.
인덱스 실행 계획
ALL: 테이블 전체를 스캔
Index가 없는경우, 있어도 할때가 있다.–> 옵티마이저가 전체갯수가 적거나 읽고자하는 데이터가 전체 데이터의 20~25퍼 이상일때 Full table Scan 발생
Range: 인덱스를 이용하여 범위 검색
index full scan: 인덱스 전체를 스캔 all보단 빠르다.
복합 인덱스란?
두 개 이상의 칼럼을 합쳐서 인덱스를 만드는것. 하나의 컬럼으로 인덱스를 만들었을 때 보다 더 적은 데이터 분포를 보여 탐색할 데이터 수가 줄어든다.
결합인덱스, 다중 컬럼 인덱스, Composite index라고도 함.
주로 WHERE절에서 조건 컬럼이 2개이상이고 AND절 일때 사용된다 OR에서는 금지
예 )서울사는 남자를 조회할 경우
1
2
3
4
SELECT place, sex
FROM people
WHERE place = 'seoul'
AND sex = 'M'
1
2
CREATE INDEX idx_name_sex
ON people(place, sex);
생성시 칼럼의 순서가 중요하다. 남녀가 반반이라고 가정해보자
place => sex 순으로 조회로 했을때 5천만-> 서울인구 1천만 -> 0.5천만 (5000+1000 = 6000번 탐색)
sex -> place 순으로 조회로 했을때 5천만-> 남자 2.5천만 -> 0.5천만 (5000+2500= 7500번검색)
후자의 방식대로 검사하면 같은 값이 나오지만 1500번 더 일을하게 하므로 생성 시, 순서를 고려해야한다.
커버링 인덱스란?
인덱스로 설정한 컬럼만 읽어 쿼리를 모두 처리할 수 있는 인덱스, 불필요한 디스크 I/O를 줄여 조회시간을 단축하는 것
Select문을 사용할떄 와일드카드 (*) 대신 원하는 값만 적용하여 인덱스 활용
인덱스 컨디션 푸쉬다운
MySQL이 인덱스를 사용하여 테이블에서 행을 검색하는 경우의 최적화를 의미한다. 인덱스 컨디션 푸시다운을 활성화하고 인덱스의 컬럼만 사용하여 where 조건의 일부를 평가할 수 있는 경우 MySQL 엔진은 WHERE 조건 부분을 스토리지엔진으로 푸시하는 경우다. 5.6부터는 자동으로 적용된다고 한다. 이로인해 그림처럼, DB 내부에서는 불필요한 I/O 및 데이터 전송 과정이 줄어들게 되고, 클라이언트 입장에서는 보다 빠른 응답 성능을 기대할 수 있다.
인덱스 자료구조
다양한 자료구조가 있지만 해시테이블과 B+Tree를 설명하고자한다.
해쉬테이블
Hashtable은 Key, Value를 한 쌍으로 데이터를 저장하는 자료구조이다. 해시 충돌이라는 변수를 제외하고 O(1)의 검색속도로 원하는데이터를 구할 수 있다.
하지만, 이 해쉬테이블은 인덱스에서 잘 사용하지않는다. key = value 이렇게 1대1 매칭되는 데이터에 대해 빠르지 부등호나 범위에 대해서는 빠르게 찾을 수 없기 떄문에 해쉬테이블을 사용하지 않는다고 한다.
B+ Tree
기존의 B-Tree 역시 어느 하나의 데이터의 검색은 효율적이지만, 범위, 부등호에 모든 데이터를 한 번 순회하는 데에는 트리의 모든 노드를 방문해야 하므로 비효율적이다. 이러한 B-Tree의 단점을 개선시킨 자료구조가 B+Tree이다.
B+Tree는 마지막 노드인 Leaf Node에 데이터를 저장하고 다른 중간노드에는 자식의 포인터만 저장한다. 특이한 점은 데이터가 leaf node에만 저장되기에 중간 노드에 key값이 중복될 수 있다.
이로인해 하나의 NODE에 더많은 포인터를 가질 수 있기에 낮은 트리 높이에도 많은 데이터를 저장하고 메모리를 확보할 수 있다. 물론, 특정 Key값에 접근하려면 leaf node까지 가야하는 단점이 있지만, 부등화와 범위 검색에 이점을 가져가고자 나온 자료구조이다.
랜덤 I/O 와 순차 I/O
순차 I/O가 훨씬 빠르게 작용한다 MySQL 특성상 순차I/O로 바꾸는게 어렵다.
랜덤I/O를 하되 row의 갯수를 줄이는데 있다.
랜덤 I/O는 읽어야하는 데이터가 물리적으로 불연속적으로 있기 때문에 디스크 헤더를 이동 시킨 다음 데이터를 읽는 것을 의미한다.
순차 I/O는 읽어야하는 데이터가 연속적으로 있어 쭉 읽기만 하는 경우를 의미한다.
데이터를 10번 읽어야 한다고 가정하자. 데이터 위치가 물리적으로 연속적이라면 디스크 헤드를 한번 이동시키면 되지만, 불연속적이라면 디스크 헤더를 10번 이동시켜야한다. 즉, Seek Time(디스크 헤드를 이동시킨 시간)이 사실상 디스크에 데이터를 읽고 쓰는데 걸리는 시간을 좌우한다. 따라서 디스크의 성능은 디스크 헤더의 이동 없이 얼마나 많은 데이터를 순차적으로 저장하느냐에 달렸다.
HDD와는 다르게 SSD는 디스크 원판이 없어서 랜덤 I/O와 순차 I/O가 큰 차이가 없을 것 같지만 SSD에서도 랜덤 I/O는 순차 I/O 대비 throughput이 떨어진다.
DBMS는 디스크에 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에서는 그룹 커밋이나 바이너리 로그, InnoDB 로그 버퍼등의 기능이 내장되어 있다.
쿼리를 튜닝한다고 랜덤 I/O가 순차 I/O로 변하는 상황은 많지 않다고 한다. 즉, 쿼리 튜닝의 목적은 랜덤 I/O를 줄이는 것이다. 즉, 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것이 중요하다.
인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하고, 풀 테이블 스캔은 순차 I/O를 사용한다. 따라서 큰 테이블의 레코드를 읽는 작업을 할 때 옵티마이저가 인덱스 대신 풀 테이블 스캔을 하도록 유도하는 경우도 있다고 한다.