결합 인덱스란 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것을 말합니다. 주로 단일 컬럼으로는 나쁜 분포도를 가지지만 여러 개의 컬럼을 합친다면 좋은 분포도를 가지고, Where절에서 AND 조건에 많이 사용되는 컬럼들을 결합 인덱스로 구성합니다.
결합 인덱스 컬럼 선택
1.where절에서 and 조건으로 자주 결합되어 사용되면서 각각의 분포도 보다 두 개 이상의 컬럼이 결합될 때 분포도가 좋아지는 컬럼들
2.다른 테이블과 조인의 연결고리로 자주 사용되는 컬럼들
3.order by에서 자주 사용되는 컬럼들
4.하나 이상의 키 컬럼 조건으로 같은 테이블의 컬럼들이 자주 조회될 때
결합 인덱스의 컬럼 순서 결정
결합 인덱스를 만들 때 결합 인덱스를 구성하는 컬럼들의 배열 순서는 아주 중요하기에 신중하게 결정하여야 합니다.컬럼의 순서를 잘못 배열하면 결합 인덱스의 발동 확률이 매우 낮아질 수 있기 때문입니다.만약 select 문의 where절에 결합 인덱스의 첫 번째 컬럼을 조건에 사용하였다면 그 질의문은 결합 인덱스를 사용할 수 있습니다. 하지만 개발자가 결합 인덱스의 두번째 컬럼만을 where 절에 조건으로 사용하고 결합 인덱스를 사용하고자 했다면 실행계획은 인덱스를 사용하지 못합니다.따라서 쿼리문 작성 시 결합 인덱스를 사용하고자 한다면 반드시 결합 인덱스의 컬럼 중 선행하는 컬럼부터 조건에 지정하여 사용하여야 합니다.조건은 컬럼 전체를 순서대로 사용할 수도 있고, 아니면 선행하는 일부 컬럼을 순서대로 사용할 수 있습니다.
결합 인덱스 컬럼의 설정 시 고려해야 할 우선순위
1.where절 조건에 많이 사용되는 컬럼이 우선시
2. Equal('=')로 사용되는 컬럼 우선
3.분포도가 좋은 컬럼을 우선
4.자주 이용되는 순서대로 결합 인덱스 컬럼의 순서 결정
결합 인덱스 사용 예시
결합 인덱스 생성
create index emp_pay_idx on emp_pay(급여년월, 급여코드, 사원번호);
emp_pay 테이블에서 급여년월, 급여코드, 사원번호 컬럼으로 emp_pay_idx라는 결합 인덱스를 생성하였습니다.
select * from emp_pay where 급여년월 = '202107';
select * from emp_pay where 급여년월 = '202107' and 급여코드 ='정기급여';
select * from emp_pay where 급여년월 = '202107' and 급여코드 = '정기급여' and 사원번호 = '20210401';
select 문장의 where 절에서는 다음과 같은 조건 조합에서 인덱스가 사용되게 됩니다.
결합 인덱스의 효율성이 떨어지는 경우
결합 인덱스도 일반적인 인덱스와 마찬가지로 데이터들이 정렬되어 보관되기 때문에 소수의 데이터를 빠르게 찾는 것에는 유리하지만 아래와 같이 스캔이 많이 생기게 된다면 효율성이 떨어지게 됩니다. 아래의 예시들은 emp_pay_idx 인덱스를 사용하기는 하지만 스캔이 많이 생기는 경우로 인덱스의 효율성이 떨어지는 경우들의 예시입니다.
select * from emp_pay where 급여년월 LIKE '2021%' and 급여코드 = '정기급여';
위 조건절의 경우 결합 인덱스의 첫 번째 컬럼인 급여년월의 조건이 있더라도 Equal(=)이 아닌 범위 연산자인 LIKE '2021%' 조건을 사용했으므로, 세개의 칼럼이 모두 필요한 emp_pay_idx 인덱스를 찾을 때 두번째 칼럼인 급여코드에 대한 조건을 B*Tree에서 쉽게 찾을수가 없게 됩니다. 이는 결합 인덱스가 각 칼럼별로 정렬이 되어 있는 것이 아니라 첫번째, 두번째, 세번째 칼럼이 결합이 되어 정렬이 되어있기 때문입니다. 이때 급여코드에 대한 조건은 인덱스를 찾아가는 검색조건이 아니라 인덱스 값이 조건에 맞는지 여부를 검증하는 체크 조건이 됩니다.
select * from emp_pay where 급여년월 = '202107' and 사원번호 = '20210401';
위 조건절의 경우는 결합 인덱스의 첫번째 칼럼인 급여년월의 조건이 equal(=)이더라도 두번째 컬럼인 급여코드에 대한 조건이 없으므로 세번째 칼럼인 사원번호 조건을 검색 조건이 아닌 체크 조건으로 밖에 사용할 수 없게 됩니다. 즉 결합 인덱스에서 급여년월인 모든 데이터를 찾아서 사원번호 조건에 맞는지 일일이 확인하는 풀 테이블 스캔이 일어나고 있는 셈입니다.
SELECT *
FROM TABLE
WHERE 1=1
AND Data = 'SMITH'
;
만약 인덱스 처리를 하면 다음과 같이 처리 할 수 있다. 마치 책에 있는 목차라고 생각하면 된다.
실제 DB 관련 작업을 할 때 대부분의 속도 저하는 바로 select문 특히 조건 검색 where절에서 발생하는데 가장 먼저 생각해 볼 수 있는 대안으로 Index를 생각할 수 있기도 하고, SQL 튜닝에서도 Index와 관련된 문제사항과 해결책이 많기 때문이다.
인덱스(Index)를 사용하는 이유 : 조건 검색 Where 절의 효율성
인덱스의 가장 큰 특징은 데이터들이 정렬이 되어있다는 점이다. 이 특징으로 조건 검색이라는 영역이 굉장히 장점이 된다. 테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드는 내부적으로 순서가 없이 뒤죽박죽 저장된다. 하지만 인덱스 테이블은 데이터들이 정렬되어 있기 때문에 해당조건 where에 맞는 데이터들을 빠르게 찾아낼 수 있다. 위의 Index 테이블에서 SMITH 부분은 정렬이 되어있다.
인덱스(Index)를 사용하는 이유 : 정렬 Order by 절의 효율성
인덱스(Index)를 사용하면 Order by에 의한 Sort 과정을 피할 수 있다. Order by는 굉장히 부하가 많이 걸리는 작업이다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생된다. 하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 된다. 이미 정렬 되어 있기 때문에 가져오기만 하면 된다.
인덱스(Index)를 사용하는 이유 : MIN ,MAX의 효율적인 처리가 가능하다.
이것 또한 데이터가 정렬되어 있기에 얻을 수 있는 장점입니다. MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만 가져오면 되기에 FULL TABLE SCAN 할 필요없다.
인덱스의 단점
인덱스가 주는 혜택이 있으면 그에 따른 부작용도 있습니다.인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지 시켜줘야 한다는 점입니다.그렇기에 레코드 내에 데이터값이 바뀌는 부분이라면 악영향을 미칩니다. INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 INDEX 테이블 내에 있는 값들을 다시 정렬을 해야겠죠. 그리고 INDEX 테이블, 원본 테이블 이렇게 두 군데에 데이터 수정 작업해줘야 한다는 단점도 있습니다.
그리고 검색시에도 인덱스가 무조건 좋은 것이 아닙니다. 인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫습니다. 그리고 인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요합니다. 무턱대고 INDEX를 만들어서는 결코 안 될 말입니다.
인덱스(Index)의 관리
앞서 설명했듯이 인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있습니다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 계속 정렬을 해주어야 하고 그에 따른 부하가 발생합니다. 이런 부하를 최소화하기 위해 인덱스는 데이터 삭제라는 개념에서 인덱스를 사용하지 않는다 라는 작업으로 이를 대신합니다.
INSERT: 새로운 데이터에 대한 인덱스를 추가합니다.
DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행합니다.
UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가합니다.
인덱스 생성 전략
생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다. 인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 Index 테이블이 생성됩니다. 이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋습니다. 가장 최선은 PK로 인덱스를 거는것이겠죠. 중복된 값이 없는 인덱스 테이블이 최적의 효율을 발생시키겠고. 반대로 모든 값이 같은 컬럼이 인덱스 컬럼이 된다면 인덱스로써의 가치가 없다고 봐야 할 것입니다.
1.조건절에 자주 등장하는 컬럼
2.항상 = 으로 비교되는 컬럼
3.중복되는 데이터가 최소한인 컬럼 (분포도가 좋은) 컬럼
4.ORDER BY 절에서 자주 사용되는 컬럼
5.조인 조건으로 자주 사용되는 컬럼
B * Tree 인덱스
인덱스에는 여러가지 유형이 있지만 그 중에서도 가장 많이 사용하는 인덱스의 구조는 밸런스드 트리 인덱스 구조입니다. 그리고 B TREE 인덱스 중에서도 가장 많이 사용하는것은 B*TREE 와 B+TREE 구조를 가장 많이 사용되는 인덱스의 구조입니다.
B * Tree 인덱스는 대부분의 DBMS 그리고 오라클에서 특히 중점적으로 사용하고 있는 가장 보편적인 인덱스입니다. 구조는 위와 같이 Root(기준) / Branch(중간) / Leaf(말단) Node로 구성됩니다. 특정 컬럼에 인덱스를 생성하는 순간 컬럼의 값들을 정렬하는데, 정렬한 순서가 중간 쯤 되는 데이터를 뿌리에 해당하는 ROOT 블록으로 지정하고 ROOT 블록을 기준으로 가지가 되는 BRANCH블록을 정의하며 마지막으로 잎에 해당하는 LEAF 블록에 인덱스의 키가 되는 데이터와 데이터의 물리적 주소 정보인 ROWID를 저장합니다.
인덱스 사용 예시
인덱스 생성
--문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)
--예제
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);
--예제 컬럼 중복 X
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);
위와같이 쿼리문을 작성하면 INDEX를 생성할 수 있습니다. UNIQUE 키워드를 붙이면 컬럼값에 중복값을 허용하지 않는다는 뜻입니다.
인덱스 조회
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';
인덱스를 생성하면 USER_INDEXES 시스템 뷰에서 조회할 수 있습니다. 방금 CUSTOMERS 테이블에 만들었던 EX_INDEX가 첫번째 ROW에 있군요.
인덱스 삭제
--문법
DROP INDEX [인덱스 명]
--예제
DROP INDEX EX_INDEX;
인덱스는 조회성능을 극대화하기 위해 만든 객체인데 너무 많이 만들면 insert, delete, update시에 부하가 발생해 전체적인 데이터베이스 성능을 저하합니다. 고로 안쓰는 인덱스는 삭제시키는것이 좋습니다.
인덱스 리빌드
인덱스를 리빌드하는 이유
인덱스 파일은 생성 후 insert, update, delete등을 반복하다보면 성능이 저하됩니다. 생성된 인덱스는 트리구조를 가집니다. 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어집니다. 이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는것이 좋습니다.
인덱스(Index)를 남발하지 말아야 하는 이유
개발을 진행할때에 대개 개발서버와 운영서버를 나누어서 관리합니다. 대부분 개발서버에서 개발을 할때에는 적은량의 데이터를 가지고 로직검사를 하며 로직검사에 통과한 코드들이 운영서버에 업데이트가 되죠. 하지만 개발서버에는 잘 동작하던 로직들이 운영서버의 많은량의 데이터들을 처리하다보면 성능이슈가 많이 발생합니다. 그 성능이슈의 주요원인은 바로 데이터베이스에 있습니다. 데이터베이스 관리자는 성능문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성입니다.
문제를 쉽게 해결을 위해 쿼리 속도 문제가 날때마다 인덱스를 추가하는것은 바람직하지 못합니다. 성능 이슈가 나서 인덱스를 만들고 또 다른 SQL에서문에서 성능이슈가 발생하여 또 인덱스를 만들었다고 합시다. 이렇게 문제가 발생할때마다 인덱스를 생성하면서 인덱스가 계속 추가되면 생성된 인덱스를 참조하는 하나의 쿼리문을 빠르게는 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래합니다. 그렇기에 인덱스를 생성하는것 보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야합니다. 인덱스생성은 꼭 마지막 수단으로 강구해야 할 문제입니다.
정리
인덱스(Index)는 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조를 일컫는다. 인덱스는 테이블 내의 1개의 컬럼, 혹은 여러 개의 컬럼을 이용하여 생성될 수 있다. 고속의 검색 동작뿐만 아니라 레코드 접근과 관련 효율적인 순서 매김 동작에 대한 기초를 제공한다. 인덱스를 저장하는 데 필요한 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작다. (왜냐하면 보통 인덱스는 키-필드만 갖고 있고, 테이블의 다른 세부 항목들은 갖고 있지 않기 때문이다.) 관계형 데이터베이스에서는 인덱스는 테이블 부분에 대한 하나의 사본이다.
인덱스는 고유 제약 조건을 실현하기 위해서도 사용된다. 고유 인덱스는 중복된 항목이 등록되는 것을 금지하기 때문에 인덱스의 대상인 테이블에서 고유성이 보장된다.
위에서 말씀드렸듯이 검색의 속도를 높여주기 때문입니다. 근데 어떻게 검색의 속도를 높여주는것이며 어떻게 사용되는지 감이 안잡히기 때문에 이 내용에 대하여 설명해보겠습니다!
예를 들어 오른쪽 테이블의 Physics값을 조회해본다고 가정해보겠습니다. 해당 과정을 표현하면
Select 절을 활용하여 조회
어느 위치에 데이터가 존재하는지 모르기 때문에 Table Full scan 진행
이처럼 테이블의 전체 데이터를 조회하기 때문에 데이터의 수가 적은 테이블이면 영향이 덜하겠지만,
만약 수십만개의 데이터가 들어있는 테이블의 데이터를 조회하는데 조회 기능이 자주 사용되는 서비스라면 성능이 굉장히 떨어지게 될것입니다!
그렇기 때문에 왼쪽과 같이 인덱스를 따로 생성하여 해당 데이터만 빠르게 찾을 수 있게 됨으로써 다이나믹한 성능 향상을 기대할 수 있는 부분입니다!
어떻게 동작하는것인가?
위의 예시를 이어서 설명하겠습니다!
해당 테이블을 생성시 생성하고 싶은 인덱스 컬럼을 지정
생성 후 인덱스 조회 시, WHERE 절이 포함된 쿼리로 조회
인덱스로 저장된 Key-Value값을 참조해서 결과 출력
위와 같이 진행되게 됩니다!
다음과 같이 B-tree 알고리즘을 통하여 조회하게 되고, 리프노드로 도착하기 까지 자식 노드에 대한 포인터가 저장되어 있어 탐색에 있어서 한개의 경로만 조회하면 되기 때문에 조회에 있어서 굉장히 효율적인 알고리즘이라고 합니다!
언제 사용해야하나?
주로 검색 및 조회를 할때 큰 효율성을 낼 수 있다고 합니다!
기본적으로 이진 트리를 사용하기 때문에 이미 정렬이 되어있는 상태에서 추가, 수정, 삭제가 자주 일어나게 되면 인덱스에서도 마찬가지로 해당 동작들이 수행되기 때문에 성능 저하를 초래할 수 있다고 합니다!
예를 들어, 한 쇼핑몰에 여러가지 카테고리가 존재할때 해당 카테고리의 상품들을 조회할때 이러한 인덱스 기능을 잘 사용하게 된다면 큰 효율을 발휘하게 되지만, 인스타그램같은 소셜 서비스들은 끊임없이 게시글이 작성되고 수정, 삭제되기 때문에 오히려 인덱싱을 하게되면 엄청난 성능 저하가 되기 때문입니다!
인덱스 선정 기준
인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있습니다. 단일 인덱스를 여러 개 생성할 수도, 여러 컬럼을 묶어 복합 인덱스를 설정할 수도 있습니다.
그러나 무조건 많이 설정하는게 검색 속도 향상을 높여주지는 않습니다. 인덱스는 데이터베이스 메모리를 사용하여 테이블 형태로 저장되므로 개수와 저장 공간은 비례합니다. 따라서,
조회시 자주 사용하고
고유한 값 위주로
인덱스를 설정하는게 좋습니다.
그럼 어떤 컬럼에 인덱스를 설정하는게 좋을까?
인덱스는 한 테이블당 보통 3~5개 정도가 적당합니다. 물론 테이블의 목적 등에 따라 개수는 달라질 수 있습니다.
인덱스는 컬럼을 정해서 설정하는 것이므로 후보 컬럼의 특징을 잘 파악해야 합니다. 아래 4가지 기준을 사용하면 효율적으로 인덱스를 설정할 수 있습니다.
카디널리티 (Cardinality)
선택도 (Selectivity)
활용도
중복도
카디널리티 (Cardinality)
✔️카디널리티가 높을 수록 인덱스 설정에 좋은 컬럼입니다. = 한 컬럼이 갖고 있는 값의 중복 정도가 낮을 수록 좋습니다.
컬럼에 사용되는 값의 다양성 정도, 즉 중복 수치를 나타내는 지표입니다. 후보 컬럼에 따라 상대적으로 중복 정도가 낮다, 혹은 높다로 표현됩니다.
예를 들어, 10개 rows를 가지는 ‘학생’ 테이블에 ‘학번’과 ‘이름’ 컬럼이 있다고 해봅시다.
‘학번’은 학생마다 부여 받으므로 10개 값 모두 고유합니다.
중복 정도가 낮으므로 카디널리티가 낮습니다.
‘이름’은 동명이인이 있을 수 있으니 1~10개 사이의 값을 가집니다.
중복 정도가 ‘학번’에 비해 높으므로 카디널리티가 높다고 표현할 수 있습니다.
면접 답변 : 고유한 값이 도출되는 것을 기반으로 인덱싱을 잡는 편 입니다. 예를들면 상품명이나 옵션보다는 상품번호, 주문번호, 배송번호 등 고유한 값에 인덱싱을 거는 편입니다.
선택도 (Selectivity)
✔️ 선택도가 낮을 수록 인덱스 설정에 좋은 컬럼입니다. 5~10% 정도가 적당합니다.
데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표입니다. 선택도는 아래와 같이 계산합니다.
= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100 = 컬럼의 값들의 평균 row 수 / 테이블의 총 row 수 * 100
예를 들어, 10개 rows를 가지는 ‘학생’ 테이블에 ‘학번’, ‘이름’, ‘성별’ 컬럼이 있다고 해봅시다. 학번은 고유하고, 이름은 2명씩 같고, 성별은 남녀 5:5 비율입니다.
‘학번’의 선택도 = 1/10*100 = 10%
SELECT COUNT(1) FROM '학생' WHERE '학번' = 1;(모두 고유하므로 특정 값: 1)
‘이름’의 선택도 = 2/10*100 = 20%
SELECT COUNT(1) FROM '학생' WHERE '이름' = "김철수";(2명씩 같으므로 특정 값: 2)
‘성별’의 선택도 = 5/10*100 = 50%
SELECT COUNT(1) FROM '학생' WHERE '성별' = F;(5명씩 같으므로 특정 값: 5)
즉, 선택도는 특정 필드값을 지정했을 때 선택되는 레코드 수를 테이블 전체 레코드 수로 나눈 것입니다.
즉 , 위의 예시에선 '학번' 정도를 index로 잡는 것이 좋다.
면접 답변 : 고유한 값이 도출되는 것을 기반으로 인덱싱을 잡는 편 입니다. 예를들면 상품명이나 옵션보다는 상품번호, 주문번호, 배송번호 등 고유한 값에 인덱싱을 거는 편입니다.
활용도
✔️활용도가 높을 수록 인덱스 설정에 좋은 컬럼입니다.
해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값입니다. 수동 쿼리 조회, 로직과 서비스에서 쿼리를 날릴 때 WHERE 절에 자주 활용되는지를 판단하면 됩니다.
면접 답변 : 저희 팀에서 자주 사용하는 기초 데이터 중 order_srl, buy_srl 같은 활용도가 높은 컬럼을 인덱싱하는 편 입니다.
중복도
✔️중복도가 없을 수록 인덱스 설정에 좋은 컬럼입니다.
중복 인덱스 여부에 대한 값입니다.
인덱스 성능에 대한 고려 없이 마구잡이로 설정하거나, 다른 부서 다른 작업자의 분리된 요청으로 같은 컬럼에 대해 인덱스가 중복으로 생성된 경우를 볼 수 있습니다.
인덱스도 속성을 가집니다. 인덱스는 테이블 형태로 생성되므로, 속성을 컬럼으로 관리합니다.
면접 답변 : 타팀에서 같은 테이블을 사용할 때 이미 인덱싱 처리되어 있는 것을 확인하고 인덱싱 신청을 하는 편 입니다.
insert, update, delete (Command)의 성능을희생하고 대신select (Query)의 성능을 향상시킵니다. 여기서 주의하실 것은 update, delete 행위가 느린것이지,update, delete를 하기 위해 해당 데이터를 조회하는것은 인덱스가 있으면 빠르게 조회가 됩니다. 인덱스가 없는 컬럼을 조건으로 update, delete를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제 해야하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는것을 추천드립니다.
B-tree 인덱스
인덱스 탐색은 Root -> Branch -> Leaf -> 디스크 저장소 순으로 진행됩니다.
예를 들어 Branch (페이지번호 2) 는 dept_no가 d001이면서 emp_no가 10017 ~ 10024까지인 Leaf의 부모로 있습니다.
즉,dept_no=d001 and emp_no=10018로 조회하면 페이지 번호 4인 Leaf를 찾아 데이터파일의 주소를 불러와 반환하는 과정을 하게 됩니다.
인덱스의두번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있습니다.
즉, 두번째 컬럼의 정렬은 첫번째 컬럼이 똑같은 열에서만 의미가 있습니다.
만약 3번째, 4번째 인덱스 컬럼도 있다면 두번째 컬럼과 마찬가지로 3번째 컬럼은 2번째 컬럼에 의존하고, 4번째 컬럼은 3번째 컬럼에 의존하는 관계가 됩니다.
디스크에서 읽는 것은 메모리에서 읽는것보다 성능이 훨씬 떨어집니다.
결국 인덱스 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐, 인덱스 Root에서 Leaf까지 오고가는 횟수를 얼마나 줄이느냐에 달려있습니다.
인덱스의 갯수는 3~4개 정도가 적당합니다.
너무 많은 인덱스는 새로운 Row를 등록할때마다 인덱스를 추가해야하고, 수정/삭제시마다 인덱스 수정이 필요하여 성능상 이슈가 있습니다.
인덱스 역시 공간을 차지합니다. 많은 인덱스들은 그만큼 많은 공간을 차지합니다.
특히 많은 인덱스들로 인해옵티마이저가 잘못된 인덱스를 선택할 확률이 높습니다.
인덱스 키 값의크기
InnoDB (MySQL)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며,인덱스 역시 페이지 단위로 관리됩니다. (B-Tree 인덱스 구조에서 Root, Branch, Leaf 참고)
페이지는 16KB 로 크기가 고정되어 있습니다.
만약 본인이 설정한 인덱스 키의 크기가 16 Byte 라고 하고, 자식노드(Branch, Leaf)의 주소(위 인덱스 구조 그림 참고)가 담긴 크기가 12 Byte 정도로 잡으면,16*1024 / (16+12) = 585로 인해 하나의 페이지에는 585개가 저장될 수 있습니다. 여기서 인덱스 키가 32 Byte로 커지면 어떻게 될까요? 16*1024 / (32+12) = 372로 되어 372개만 한 페이지에 저장할 수 있게 됩니다.
조회결과로 500개의 row를 읽을때 16byte일때는 1개의 페이지에서 다 조회가 되지만, 32byte일때는 2개의 페이지를 읽어야 하므로 이는 성능 저하가 발행하게 됩니다.
인덱스의 키는 길면 길수록 성능상 이슈가 있습니다.
인덱스 컬럼 기준
먼저 말씀드릴 것은 1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다는 점입니다.
카디널리티(Cardinality)란 해당 컬럼의중복된 수치를 나타냅니다. 예를 들어 성별, 학년 등은 카디널리티가 낮다고 얘기합니다. 반대로 주민등록번호, 계좌번호 등은 카디널리티가 높다고 얘기합니다.
인덱스로 최대한 효율을 뽑아내려면,해당 인덱스로 많은 부분을 걸러내야 하기 때문입니다. 만약 성별을 인덱스로 잡는다면, 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 50%밖에 걸러내지 못합니다. 하지만 주민등록번호나 계좌번호 같은 경우엔인덱스를 통해 데이터의 대부분을 걸러내기 때문에빠르게 검색이 가능합니다.
여러 컬럼으로 인덱스 구성시 기준
자 그럼 여기서 궁금한 것이 있습니다. 여러 컬럼으로 인덱스를 잡는다면 어떤 순서로 인덱스를 구성해야 할까요? 카디널리티가 낮은->높은순으로 구성하는게 좋을까요? 카디널리티가 높은->낮은순으로 구성하는게 좋을까요? 실제 실험을 통해 확인해보겠습니다.
테스트 환경은 AWS EC2 Ubuntu 16.04를 사용했습니다. 최대한 극적인 비교를 위해 메모리는 1G, 디스크는 마그네틱(SSD X)을 사용했습니다.
테이블 형태는 아래와 같습니다.
CREATE TABLE `salaries` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
`is_bonus` tinyint(1) unsigned zerofill DEFAULT NULL,
`group_no` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
첫번째 인덱스는is_bonus, from_date, group_no순으로카디널리티가 낮은순에서 높은순 (중복도가 높은 순에서 낮은순으로)으로, 두번째 인덱스는group_no, from_date, is_bonus순으로카디널리티가 높은순에서 낮은순 (중복도가 낮은 순에서 높은순으로)으로 생성했습니다.
CREATE INDEX IDX_SALARIES_INCREASE ON salaries
(is_bonus, from_date, group_no);
CREATE INDEX IDX_SALARIES_DECREASE ON salaries
(group_no, from_date, is_bonus);
사용한 쿼리
select SQL_NO_CACHE *
from salaries
use index (IDX_SALARIES_INCREASE)
where from_date = '1998-03-30'
and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406')
and is_bonus = true;
select SQL_NO_CACHE *
from salaries
use index (IDX_SALARIES_DECREASE)
where from_date = '1998-03-30'
and group_no in ('abcdefghijklmn10494','abcdefghijklmn3968', 'abcdefghijklmn11322', 'abcdefghijklmn13902', 'abcdefghijklmn100', 'abcdefghijklmn10406')
and is_bonus = true;
즉, 여러 컬럼으로 인덱스를 잡는다면카디널리티가 높은순에서 낮은순으로 (group_no, from_date, is_bonus) 구성하는게 더 성능이 뛰어납니다
인덱스
책의 제일 끝에 있는 찾아보기 (목차랑은 다른개념이다.)
데이터 : 책의 내용 / 인덱스 : 찾아보기
책의 찾아보기와 인덱스의 공통점 : 정렬
프로그래밍 언어의 자료구조로 인덱스와 데이터 파일을 비교
SortedList : 인덱스와 같은 자료구조
저장되는 값을 항상 정렬된 상태로 유지하는 자료구조
장점 : 이미 정렬되어 있기 때문에 아주 빠르게 원하는 값을 찾을 수 있음(Select)
ArrayList : 데이터와 파일과 같은 자료구조
값을 저장되는 순서대로 그대로 유지하는 자료구조
DBMS의 인덱스는 데이터의 저장(Insert, update, delete) 성능을 희생하고 대신 데이터의 읽기 속도를 높이는 기능
Select 문장의 Where 조건절에 사용되는 모든 컬럼을 인덱스로 생성하면 저장성능이 떨어지고, 인덱스 크기가 커져 오히려 역효과
인덱스 데이터 저장 방식(알고리즘) 별로 구분
B-tree 인덱스
컬럼의 값을 번형하지 않고, 원래의 값을 이용하여 인덱싱
Hash 인덱스
컬럼의 값으로 해시 값을 계산해서 인덱싱
매우 빠른 검색을 지원
실습으로 DB 인덱싱 알아보기
실습1 : 모든 동물 테이블을 검색하고, nocope를 검색한다. 그리고 인덱싱 처리해서 실행시간을 비교한다.
위와 같은 Animal 테이블이 있다고 가정하자.
그리고 천만 건의 데이터 중를 모두 select count(*) from Animals; 해주고 또 nocope를 검색하는 쿼리를 날려준다.
각각 3.6초, 4.5초가 걸렸다. 엄청 오래 걸린다...즉, 서비스에서는 못쓴다는 것이다.
인덱스 추가하기
CREATE INDEX ANIMAL_NAME ON ANIMALS(NAME);
동물 이름에 index를 추가해보자
그러고 성능비교를 해보자 그랬더니 0.003초로 엄청나게 빨라졌다.
어떻게 동작하는 것인가?
B-Tree : Balanced Tree
위의 그림에서 5를 찾고 싶다라고 가정하면 7과 비교해서 작은 왼쪽트리로 가고 만약 9를 찾고 싶다하고 하면 가운데 트리로 이동하는 개념이다.
위의 상황을 살펴보자. 동물이름은 비트리안에서 오름차순으로 정렬이 되어있다. 만약 nabi를 찾고 싶다면 meow와 비교해서 뒷편에 있을 테니 2번째 브랜치로 이동하여 탐색하는 개념이다.
실습2 : 동물 테이블에서 키가 30cm~50cm 사이인 동물의 평균 키는?
해당 쿼리를 실행 시켜보았다. 4.3초... 이것도 서비스에서는 못쓴다.
인덱스 적용하기
CREATE INDEX ANIMAL_WEIGHT ON ANIMALS(weight);
몸무게에 where절이 걸렸으므로 weght에 인덱스를 걸어준다.
하지만...
같은 쿼리를 실행하면 4.2초로 성능 차이가 없다. weight가 전혀 인덱스를 안탄것인데 왜 그런지 알아보자.
between 이므로 시작점 30과 끝점 50을 찾는다.
만약 위처럼 평균 키(height)가 아닌 평균 몸무게(weight)로 검색했다면 인덱스를 잘 타고 0.6초 만에 검색이 되었을 것이다. 왜냐면 해당 쿼리문을 실행 시키기 위한 모든 정보가 인덱스 안에 있기 때문이다.
즉, 위의 쿼리는 키에 따른 몸무게 정보를 알기 위해서 full scan 과정을 또 겪었을 것이며 인덱스를 타지 않았다.
해결 방법
각 몸무게에 대한 키정보를 인덱싱한다.
다음과 같이 키, 몸무게 정보를 담은 인덱싱을 한다.
CREATE INDEX animal_wh on Animal(weight,height)
결과
0.7초로 단축하며 인덱스를 타는 것을 알 수 있다.
마지막으로 인덱스를 잘 타는지 확인하고 싶다면 analyze 키워드를 맨 앞에 쓰면 된다.
