인덱스란
해당 포스팅은 Mysql과 Inno DB를 기반으로 작성되었습니다 인덱스가 뭘까요? 책의 가장 뒤쪽, 단어로 해당 페이지를 가리키는 인덱스 페이지를 보신 일이 있나요?
대충 멋있어 보이는 인덱스 페이지
Database의 인덱스도 다르지 않습니다. 책의 색인과 마찬가지로, DB인덱스는 DB의 데이터를 쉽게 찾을 수 있게 해줍니다. 쉽게 찾는다는 것은, DB의 세계에선 조회 시간이 적게 걸린다 는 것을 의미합니다. Mysql에선 인덱스를 B-Tree(balanced tree의 준말이라는 것이 정설) 자료구조로 만들어서 Binary Search를 할 수 있는 기반을 마련합니다. 테이블의 데이터를 다 훑으면서(Full Table Scan)보다 뛰어난 조회 성능을 보일 수 있습니다.
인덱스의 종류
Mysql 인덱스엔 다양한 종류가 있지만, 대표적으로 사용되는 클러스티드 인덱스와 논 클러스티드 인덱스가 있습니다.
클러스티드 인덱스 (Clusted Index)
클러스티드 인덱스는 메모리에 순차적으로 저장되는 인덱스입니다. InnoDB에선 PK를 생성하면 PK가 자동으로 클러스티드 인덱스가 됩니다. 다른 RDBMS는 클러스터링 기능이 선택 사항이지만, Inno DB에서는 클러스터링이 default이기 때문에 사용자가 별도의 명령이나 옵션을 선택하지 않아도(PK를 생성하지 않아도) 내부적으로 사용자에게 보이지 않는 Clusted Index가 생성됩니다. 클러스터링이란 비슷한 값들을 최대한 모아서 저장하는 방식을 의미합니다.
스토리지 엔진은 사용자가 요청한 SQL문을 토대로 DB에 저장된 디스크나 메모리에서 필요한 데이터를 가져와 MySQL 엔진으로 전달하는 역할을 수행합니다. InnoDB, MyISAM, Memory 등이 있습니다. 주로 데이터베이스의 용도가 트랜잭션 발생으로 데이터를 처리하는 OLTP 인 경우가 많으므로 InnoDB를 주로 사용하게 됩니다.
Inno DB 는 Mysql의 스토리지 엔진이다!
B Tree 구조로 저장되는 데이터. 인덱스 페이지가 데이터를 찾아갈 수 있는 색인 역할을 한다
논클러스티드 인덱스 (Non-Clusted Index)
논클러스티드 인덱스는 테이블에서 칼럼에 대한 인덱스를 추가하는 것을 말합니다. CREATE INDEX 명령어를 통해 인덱스를 만들면 논클러스티드 인덱스 테이블이 추가로 생성됩니다. 해당 테이블엔 인덱스가 순차적으로 정렬되어 있고, 값으론 PK(클러스티드 인덱스) 값을 담고 있습니다.
논클러스티드 인덱스 탐색 방식
a. 수직적 탐색
인덱스 수직적 탐색은 루트 노드에서부터 시작합니다. 루트 노드와 브랜치 노드는 인덱스 키와 자식 노드 정보로 구성된 페이지(단위)입니다. 수직적 탐색 과정에 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 노드로 이동합니다. InnoDB의 경우 Secondary Index를 통해 알아낸 Primary Key로 한번 더 수직적 탐색이 이루어집니다.
b. 수평적 탐색
수직적 탐색을 통해 스캔 시작점을 찾았으면 수평적 탐색을 통해 데이터를 찾습니다.
인덱스 리프노드끼리는 양방향 Linked List이므로 서로 앞뒤 블록에 대한 주소값을 갖습니다.
필요한 컬럼을 인덱스가 모두 갖고 있어 인덱스만 스캔하고 끝나는 경우(커버링 인덱스)도 있지만, 그렇지 않을 경우 테이블도 액세스해야 합니다.
이 때, ROWID가 필요합니다.
ROWID는 데이터블럭 주소 + 로우 번호(블록내 순번)로 구성됩니다.
InnoDB의 경우, Primary Key 가 ROWID 역할을 합니다.
Primary Key는 Clustered Index, 즉 순차적으로 저장되어 있어 Data record의 물리적인 위치를 알 수 있기 때문입니다.
ROWID가 가리키는 데이터 페이지를 버퍼풀에서 먼저 찾아보고 못찾을 때만 디스크에서 블록을 읽습니다. (읽은 후에는 버퍼풀에 적재합니다.)
클러스티드 인덱스와 논클러스티드 인덱스 활용 예제
다음과 같은 테이블을 만들었습니다.
생성시 사원번호를 PK(클러스티드 인덱스)로 생성하였고, 이름을 대상으로 인덱스 테이블을 만들겠습니다.
create index idx_이름 on 사원 (이름);
SQL
복사
그리고 사원 테이블을 조회합니다.
select * from 사원; # A 쿼리
select * from 사원 where 성 = '김'; # B 쿼리
select * from 사원 where 사원번호 = 1; # C 쿼리
select * from 사원 where 이름 = '시형'; # D 쿼리
SQL
복사
table full scan
A 쿼리를 조회하면 디스크를 순회하며 모든 데이터를 읽어올 것입니다. B쿼리는 어떨까요? 디스크에서 전체 데이터를 읽어 where문에서 이름이 '김'인 row를 걸러내어 보여줄 것입니다. 이런 조회를 Table Full Scan이라고 합니다. 테이블 전체를 돌며 찾는다는 뜻입니다. 시간복잡도는 O(n)이 되겠네요.
index range scan
C쿼리와 D쿼리를 이용하면, 클러스티드 인덱스(사원번호)와 논클러스티드 인덱스(이름)을 이용할 수 있습니다. binary search를 이용하여 해당하는 row를 찾고 접근하므로, O(log n)의 시간복잡도로 찾을 수 있습니다.
질문!!
: 그렇다면 조회에 사용되는 모든 where 조건문에 맞춰 인덱스를 잡아두면 조회성능을 혁신적으로 개선할 수 있겠네요??? 대답은 '아니오' 입니다.
위의 대답이 아니오 인 이유를 이해하려면, mysql이 데이터를 읽어오는 방식을 이해해야 합니다. 조금은 긴 이야기가 될지도요!
mysql에서 쿼리가 동작하는 방식
쿼리 동작 방식
클라이언트 : mysql에 접속한 클라이언트(우리의 어플리케이션도 가능하겠죠)를 의미합니다. a. Query Caching
SQL문이 Key, 쿼리의 실행결과가 Value인 Map입니다. 다음의 확인절차를 거쳐 쿼리 캐시의 결과를 반환합니다.
•
요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
•
해당 사용자가 그 결과를 볼 수 있는 권한이 있는가?
•
트랜잭션 내에서 실행된 쿼리인 경우 가시 범위 내에 있는 결과인가?
•
호출 시점에 따라 결과가 달라지는 요소(RAND, CURRENT_DATE 등)가 있는가?
•
캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
•
쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
다만, 데이터가 변경되면 모두 삭제해야 하는데요. 이는 동시 처리 성능 저하를 유발하고 많은 버그의 원인이 되어 Query Cache는 MySQL 8.0으로 올라오면서 제거되었습니다.
b. Parsing
사용자로부터 요청된 SQL을 잘게 쪼개서 서버가 이해할 수 있는 수준으로 분리합니다.
c. Preprocessor
해당 쿼리가 문법적으로 틀리지 않은지 확인하여 부정확하다면 여기서 처리를 중단합니다. (일괄처리(batch) 내에 있다면 일괄처리 전체를 중단합니다.)
d. Optimization
실행계획(Exception Plan)이란, 이 단계에서의 출력을 의미합니다.
•
쿼리 분석: Where 절의 검색 조건인지 Join 조건인지 판단합니다.
•
인덱스 선택: 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정합니다.
•
조인 처리: 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정합니다.
e. Handler (Storage Engine)
MySQL 실행엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당합니다. MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행합니다.
Sequential access VS Random access
•
디스크의 성능(속도)은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다고 볼 수 있습니다
•
시퀀셜 액세스는 물리적으로 인접한 페이지를 차례대로 읽는 순차 접근방식입니다. 인접한 페이지를 여러 개 읽는 다중 페이지 읽기 방식으로 수행합니다. 디스크의 헤더를 이동하지 않으므로 데이터 접근 속도가 빠릅니다. 클러스티드 인덱스는 시퀀셜 엑세스를 합니다. PK를 기준으로 순서대로 디스크에 저장해놓았기 때문에, 범위를 알면 한번의 IO로 인접한 페이지를 차례대로 읽어와 메모리에 불러들일 수 있습니다.
•
랜덤 액세스는 물리적으로 떨어진 페이지들에 임의로 접근하는 임의 접근 방식입니다. 정해진 순서없이 이동하는 만큼 디스크의 물리적인 움직임이 필요하고 다중 페이지 읽기가 불가능해, 데이터의 접근 수행 시간이 오래 걸립니다. 논 클러스티드 인덱스는 정렬된 인덱스 칼럼으로 PK를 찾은 후, 해당 PK에 있는 데이터를 요청해 가져오기 때문에 랜덤 엑세스가 발생합니다.
Index Range Scan과 Table Full Scan
데이터베이스 테이블에서 데이터를 찾는 방법은 두 가지가 있습니다.
•
테이블 전체를 스캔한다.
•
인덱스를 이용한다.
Table Full Scan은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크를 읽어 한 블록에 속한 모든 레코드를 한번에 읽어들입니다. 이는 테이블이 클러스티드 인덱스로 정렬되어 있고, 이것은 곧 디스크에서의 파일 순서와 동일하기 때문에 가능합니다.
Index Range Scan은 랜덤 액세스와 Single Block I/O로 레코드 하나를 읽기 위해 매번 I/O가 발생합니다. 이는 논클러스티드 인덱스 테이블에서 Binary Search로 찾은 결과물(클러스티드 인덱스)을 가져와 다시 디스크에 해당하는 row를 찾아달라고 요청해야하기 때문에 그렇습니다.
따라서 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan이 유리합니다. 즉, 인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용합니다.
이외엔 Index Full Scan, Index Unique Scan, Index Loose Scan, Index Merge Scan이 있습니다. 본문에선 테이블 풀 스캔과 인덱스 레인지 스캔만 다룹니다.
아까의 사원 테이블 예제를 기억하시나요? 사원 중 이름이 '시형'에 해당하는 데이터를 찾는 D 쿼리는 큰 테이블에서 소량 데이터를 검색해야 하는 경우에 해당할 수 있습니다. 그러나 만약 사원 100만 건 중 60만 건의 이름이 '김' 이라면, B 쿼리를 수행할 때 모든 데이터를 불러들이고 칼럼을 조사해야하는 Table Full Scan이 빠를까요? 아니면 60만건의 single I/O를 거치는 Index Range Scan이 빠를까요?
인덱스 손익분기점
1건을 조회하든, 1000만건을 다 조회하든 Table Full Scan의 성능은 일정하게 유지됩니다. 논클러스티드 인덱스를 이용해 테이블을 액세스할 경우 랜덤 액세스 때문에 점점 느려집니다. Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이기 때문입니다.
테이블 데이터가 10~100만건 이내의 경우 조회 건수가 5~20% 가량에서 손익분기점이 형성됩니다. 조회건수가 늘수록 데이터를 버퍼캐시에서 찾을 가능성이 낮아지기 때문에, 1000만건 중 100만건 이상 액세스한다면 캐시 히트율은 극히 낮을 수밖에 없습니다.
위에 사례에선 테이블 풀 스캔을 이용하는 편이 낫겠네요. 하지만 인덱스 테이블을 생성한다고 반드시 인덱스 레인지 스캔 방식이 쓰이는 건 아닙니다. 쿼리 옵티마이저가 쿼리가 수행되기 전 쿼리의 비용을 계산하기 때문에(실제 결과와 항상 일치하진 않습니다) 쿼리 옵티마이저가 판단하기에 더 효율적인 방식으로 활용됩니다. 실제로 어떤 방식으로 조회되었는지 알고 싶다면 실행계획을 활용할 수 있습니다.
하지만 인덱스 테이블을 생성한다고 반드시 인덱스 레인지 스캔 방식이 쓰이는 건 아닙니다. 쿼리 옵티마이저가 쿼리가 수행되기 전 쿼리의 비용을 계산하기 때문에(실제 결과와 항상 일치하진 않습니다) 쿼리 옵티마이저가 판단하기에 더 효율적인 방식으로 활용됩니다. 실제로 어떤 방식으로 조회되었는지 알고 싶다면 실행계획을 활용할 수 있습니다.논클러스티드 인덱스 설정 시 주의사항
논클러스티드 인덱스를 모든 칼럼마다 만들어두면, 쿼리 옵티마이저가 알아서 최적화를 해주지 않을까? 싶을지라도, 논 클러스티드 인덱스를 남용하는 것은 성능 저하를 불러올 수 있습니다.
논클러스티드 인덱스는 조회 성능을 위해 CUD 성능을 희생하기 때문입니다. CUD 작업이 있을 때 마다, 인덱스 테이블에도 CUD가 일어납니다.
보통 논클러스티드 인덱스의 추가는 인덱스는 테이블의 10% 정도의 추가 공간을 필요로 합니다. 테이블 로우를 Insert나 update, delete 할 때 인덱스 테이블에도 추가로 연산을 해주어야 하기 때문에 추가 작업을 필요로 합니다. 무분별한 인덱스 추가는 CUD 비용을 증가시킨다는 점
기타 스캔 방식
마치며
실전편에서는 좀 더 실무에서 활용할 수 있을만한 팁과 예제로 돌아오겠습니다!! 다음 시간에 뵈요!
레퍼런스
책 - Real Mysql 8.0 1권, 업무에 바로 쓰는 SQL 튜닝
강의 - 우아한테크코스 쿼리 최적화 수업
