Index

Optimizer (맛보기)

DBMS는 작성한 SQL 을 어떻게 실행할 것인지 실행계획을 수립하고, SQL 을 실행하는데 바로 이 실행 계획 수립을 옵티마이저가 하게 된다.

옵티마이저는 SQL 을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 DBMS 내부의 핵심엔진이다.

작성한 SQL 문을 Parser에서 문법검사 , 구문분석을 하고
옵티마이저에서 여러 실행 계획들을 세우게 된다.

딕셔너리에는 각종 통계정보들이 저장되어있어서 그 정보들을 기반으로 비용을 평가하고 최소비용의 실행계획을 결정한다.

결정된 실행계획대로 데이터를 처리하여 사용자에게 전달한다.

옵티마이저에는 2가지 종류가 있는데 우선순위를 가지고 실행계획을 결정하는 규칙기반 옵티마이저 , 전체적인 비용을 계산하여 제일 적은 비용(예상 소요시간, 자원 사용량등)이 드는 실행계획을 선택하는 비용기반 옵티마이저 방식이 있다.

대부분의 RDBMS 에서는 비용기반 옵티마이저를 사용하고 있다.

Index

-> 추가적인 쓰기 작업과 저장공간을 활용하여 데이터베이스 테이블의 검색속도를 향상시키기 위한 자료구조이다.

-> 인덱스는 항상 정렬된 상태로 유지해야 한다. 이는 삽입/수정할때에는 비효율적일 수 있다. (정렬의 관해서는 NON-Clustered Index 의 개념과 헷갈려서는 안된다)

종류

1. B-Tree 인덱스 (B+Tree 알고리즘 이용)
   • 대부분의 데이터베이스 인덱스에서 채택하는 알고리즘
   • 리프노드에만 key 와 data 저장.
   • full 스캔할때에도 효율적(연결리스트 이용) But 단일 데이터를 찾을때에는 B-TREE 가 더 효율적 일 수 있음
   • 항상 정렬된 상태를 유지한다.
   • 특정 기준보다 크거나 작은 값을 찾기 유용하다 (범위 탐색 유용).
   • O(logN)
   • 삽입과 삭제시에 정렬을 고려해야한다. (단점)
     
2. Hash 인덱스 (해쉬 테이블 이용)
   - 동등비교에서 굉장히 효과적이다. O(1)
   - 정렬할 필요가 없으시 삽입/삭제가 빠르다.
   - 정렬이 되어있지 않기때문에 특정기준에 따라 동등비교가 아닌 비교(<,>)를 할때에는 비효율적이다 (범위 탐색 어려움)
   .

구조

<사진참조 https://lng1982.tistory.com/144>

1. Clustered Index
   -> 군집화된 인덱스 즉, 데이터와 인덱스가 합쳐져있다.(밀접하게 연관되어 있다.) 라고 해석가능
   

리프노드에 데이터가 물리적으로 저장되어있다.

특징
1. 테이블당 단 하나의 클러스터 인덱스를 가질수 있다. (해당 컬럼 기준으로 자동 정렬)
-> 여러개의 클러스터 인덱스를 가지게 될 경우 정렬된 여러개의 복사본이 필요
2. PK 설정시 자동으로 클러스터 인덱스 생성.
3. 리프 페이지 자체가 데이터.
4. 검색 속도가 Non-Clustered Index 보다 빠르다. (But 삽입/수정/삭제 는 느리다.)

생각해보기 : PK 를 Auto-Increment 로 자주 사용하는 이유를 생각해보자.

2. Non-Clustered Index

리프노드에 데이터가 들어가는것이 아닌 , 데이터의 주소가 들어간다.

특징
1. 인덱스 페이지만 정렬되고, 원본데이터는 정렬되지 않는다.
2. 테이블 당 약 240개의 인덱스를 만들 수 있다.
3. UNIQUE KEY를 생성하면 자동으로 Non-Clustered index 가 생성된다. (InnoDB)
4. 데이터 수정/삭제/입력 속도가 상대적으로 빠르다.

동작방식 (사진 참조)

스캔방식

테이블 스캔 : 테이블에 존재하는 모든 데이터를 읽으면서 조건에 따라 결과 추출 하는 방식

-SQL문에 조건이 존재하지 않는 경우 : 테이블에 존재하는 모든 데이터가 답이 되는 경우를 의미한다. 
-SQL문에 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우 : 인덱스가 없다면 데이터 액세스 가능한 방법은 모든 테이블의 데이터를 읽는 방법 뿐이다. 
또한, 주어진 조건에 사용 가능한 인덱스는 존재하지만 함수를 사용해서 컬럼을 변경한 경우에도 인덱스 사용이 불가하다.
-옵티마이저 취사 선택 : 조건을 만족하는 데이터가 많은 경우, 인덱스가 존재해도 전체 테이블 스캔을 할 수 있다. 

인덱스 스캔 : 인덱스를 구성하는 컬럼값을 기반으로 데이터 추출

-인덱스 유일 스캔(index unique scan) : 유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식. 중복을 허락하지 않는다. =로 값이 주어지면 결과는 최대 1건이다. 
-인덱스 범위 스캔(index range scan) : 한 건 이상의 데이터를 추출하는 방식. 

카디널리티 (중복도)

카디널리티란 중복도를 말한다.
카디널리티가 클수록 중복된 데이터가 적다는 뜻이다.

우리는 인덱스를 고려할때 이 카디널리티를 참고하곤 한다.
예를 들어 카디널리티가 낮은 성별을 예로 들자면, 남자를 골라라! 했을때 인덱스를 사용하게 되면 굉장히 효율이 떨어진다.

왜인지는 위의 스캔방식을 보고 한번 생각해보면 좋을거같다.

생각해보기

많은 개발자들이 Database 테이블을 설계 또는 구현 할때 , primary key 에 Auto_Increment를 자주 사용한다.
이 이유 또한 인덱스와 관련이 있다고 생각한다.

물론 기본키가 무조건 Clustered Index 가 된다. 둘은 같은것이다. 라고 하는것은 틀리다. 기본키를 Non-Clustered 로 바꿀 수 있고 , Unique Key 를 Clustered Index 로 설정 할 수도 있다. (WHERE 조건의 빈번도에 따라서 또는 공통조건, 조인조건 등등을 따져봐야함)

하지만 , 기본키를 생성할때 자동으로 Clustered Index 가 생성 되는 것을 바라본다면 기본키는 기본키 제약조건 외에도 삽입,수정,삭제가 잘 일어나지 않는 static 한 값일 수록 인덱스 효율이 좋아질 것이다.

더하여 인덱스 키 값의 크기가 작으면 메모리에 적재하는 노드(페이지) 당 관리할 수 있는 인덱스가 많아져 효율적으로 사용할 수 있는 장점도 있다.

email 로 PK 를 잡았을때와 Auto_Increment로 잡았을때의 예를 생각해보고 Auto_Increment 에 어떤 이점이 있을지 생각해보자

인덱스 설계

1. 카디널리티가 높을수록 우리는 인덱스 설계를 고려해야한다.

2. 단일 테이블에 인덱스가 많으면 속도가 느려질 수있다. (테이블당 4~5개 권장)

3. 데이터 변경(삽입, 수정, 삭제) 작업이 얼마나 자주 일어나는지 고려해야 한다. (아무리 Non-Clustered Index 라도 결국 삽입 수정 삭제가 일어나면 인덱스에도 최신화를 해줘야하기 때문에)

4. 조회 활용도가 높으면 인덱스 설정에 좋은 컬럼이다.
   해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값.
   (WHERE의 대상 컬럼으로 많이 활용되는지로 판단하면 된다.)

5. 조인에 자주 사용되는 열에는 인덱스를 생성하는 것이 좋다.

다중 컬럼 인덱스

https://steady-coding.tistory.com/546 의 한부분..