데이터베이스는 결국 디스크에 데이터가 저장되는 최종 형태이다. 따라서 하드웨어 중 가장 처리 속도가 느린 디스크가 DB의 처리 속도를 결정한다고 봐도 무방하다. 하드웨어적 성능을 논외로 소프트웨어적으로 성능을 최대한 끌어 올릴 수 있가에 대한 해답이 인덱스이다.
인덱스란?
B-Tree 인덱스
B-Tree에서 B는 Binary가 아니라 Balanced라는 것을 잊지 말자.
1. 구조 및 특성
- 루트 노드 → (브랜치 노드) → 리프 노드의 트리 구조를 갖는다.
- 결국 B-Tree 인덱스도 테이블이라고 생각하면 된다.
- 리프 노드에는 프라이머리 키가 적혀 있으며, InnoDB에 경우 이 키를 가지고 디스크를 가서(클러스터링 인덱스) 다시 주소값과 매칭해서 실제 데이터 레코드를 찾아야 한다.
2. 키 추가 및 삭제
1. 키 추가
- 인덱스 페이지도 결국에는 디스크에 담겨져 있다가 메모리에 올라와 사용된다. 그래서 추가 및 변경 작업은 디스크를 방문 해야하는 작업이 들어갈 가능성이 많아진다.
- 결국 노드들도 테이블과 같은 데이터 페이지이므로, 한정된 용량이 있다. 그래서 B-tree가 계속해서 증가하다 한계치에 다다르면, 스플릿 작업을 통해 쪼개주게 된다. 근데 이 작업이 또 비용이다
→ 지연처리를 지원함으로써 단점을 보완한다. 하지만 프라이머리 키나 유니크 키와 같이 중복을 체크 해야하는 경우엔 지연 처리가 지원되지 않는다.
2. 키 삭제
- 디스크 작업이 필요하지만 InnoDB는 지연처리를 지원하여 크게 영향을 주지 않는다.
3. 키 변경
- 키 값 자체를 바로 변경하는 것이 아니라, 변경하고자 하는 키를 먼저 삭제 한 뒤, 키를 추가하는 형태이다. 위의 키 추가와 변경의 조합으로 이루어 진다.
4. 키 검색
- 인덱스를 잘 활용하기 위한 적절한 쿼리 사용이 요구된다. 그리고 함수나 where절에 인덱스를 변형하여 검색하는 경우에는 인덱스가 가용하지 않다.
3. 인덱스 사용에 영향을 미치는 요소
1. 인덱스 키 값의 크기
- 인덱스 페이지는 결국 [인덱스 키 + 자식노드 주소]로 구성된다. 이 구성의 크기가 크면 클수록 하나의 인덱스 페이지가 포함 할 수 있는 인덱스 키 덩어리는 줄어들게 된다. → 페이지가 많다는 것은 디스크에서 이 페이지 자료를 가져오는 횟수나 양이 많다는 의미이다. ⇒ 성능 저하
- 인덱스 페이지는 디스크에서 가져와져 결국 메모리상에 상주하며 사용되게 되는데, 인덱스 페이지가 크다면 당연히 메모리(InnoDB 버퍼 풀)의 공간도 많이 사용하게 된다.
2. B-Tree 깊이
- 깊이가 깊다는 의미는 인덱스 페이지가 여러개로 나뉘어져 있다는 의미이다. 그러므로 위에서 설명한 것과 같이 디스크 I/O가 더 많이 발생한다.
3. 선택도
- 유니크한 값의 수를 의미한다. 유니크 하다면 옵티마이저는 유니크 키에 해당하는 데이터를 찾으면 더 진행할 필요가 없으므로 빠르게 처리된다.
4. 읽어야 하는 레코드의 건수
- 인덱스는 필요한 조건에 맞는 데이터를 찾아서 읽어 온다는 것(작업 범위 결정 조건)이다. 그와 약간 다르게 전체를 가져오고 필터링(필터링 조건)해서 데이터를 얻는 방식이 있다.
- 무조건 인덱스로 인한 검색이 빠르다고 할 수 없다. 이것은 경우에 따라서 어떠한 것이 더 빠른지에 대한 수치 손익 분기점을 따져 봐야 한다. 전체 테이블 레코드의 20~25% 이상의 데이터를 읽어야 할 때는 모두 직접 읽고 필터링 하는 방식이 더 효율적이다 → 옵티마이저는 이러한 상황에서 유저가 인덱스로 검색하는 쿼리를 날려도 자동으로 필터링 방식으로 적용하여 검색하게 된다.
4. 인덱스를 통한 데이터 읽기
1. 인덱스 레인지 스캔
- 루트 → 브랜치 노드 → 리프 노드를 거친 뒤 (인덱스 탐색), 리프 노드에서 해당하는 인덱스 키를 범위화해서 읽는다.(인덱스 스캔)
- 가장 대표적인 인덱스 접근 방식이다. 쿼리에서 검색 조건을 잘 설정 해야지 레인지 스캔이 적용 된다.
→ 잘못 적용하면 전체 다 스캔하는 방식으로 적용된다.
- MyISAM과 같은 인덱스 레인지 스캔에서는 레인지 스캔이 디스크에서 계속되는 랜덤 I/O를 유발한다 → InnoDB는 디스크에서도 클러스터링 인덱스로 묶어 놨기 때문에 보다 좋은 성능을 발휘 한다.
2. 인덱스 풀 스캔
- 리프노드에서 처음부터 끝까지 모두 읽는 방식이다.
- 잘 사용되지 않으며, 쿼리가 잘못 작성되어 이러한 방식으로 적용 될 수 있다.
3. 루스 인덱스 스캔
- 이미 정렬화되고 범주화된 인덱스의 특성을 가지고, 듬성듬성하게 스캔하는 방식이다.
- 범주화 되어 있기 때문에 GroupBy에 사용되며, 순서가 정렬되어 있으므로 최대 최소값 찾는 쿼리에 활용된다.
- 다시 말해 주어진 쿼리에서도 효율적으로 생략할 것은 생략하고 효율적으로 뛰어 넘어서 찾는 방식이다.
4. 인덱스 스킵 스캔
- 설정된 인덱스가 여러개인데, 사용자가 쿼리에서 한개의 인덱스만 설정하여 쿼리를 날렸다면, 옵티마이저는 이 작업에 나머지 인덱스가 있는 새로운 쿼리를 추가하여 실행한다. → 멍청한 쿼리를 옵티마이저가 똑똑하게 수습
5. 다중 칼럼 인덱스
- 인덱스의 칼럼을 의미한다.
- 첫번째에 위치한 칼럼에 의존하여 두번째 칼럼의 위치가 결정된다. 같은 범주의 첫번째 칼럼에서의 두번째 칼럼 인덱스 값들은 정렬되어 있지만, 바로 다음에 다른 범주 첫번째 칼럼에서의 두번째 칼럼 인덱스 값은 위의 값과 순서상 연관이 없이 존재한다.
6. 인덱스의 정렬 및 스캔 방향
1. 인덱스의 정렬 - ASEC(오름차순), DESC(내림차순)
2. 인덱스 스캔 방향
- 인덱스의 내림차순,오름차순은 결국 왼쪽 자식 노드와 오른쪽 자식 노드의 위치 관계로 설정한다. 오름 차순인 경우에는 왼쪽 자식 노드가 오른쪽 자식 노드보다 인덱스 값이 작다.
3. 내림차순 인덱스
- 인덱스 스캔의 특징은 인덱스 레코드는 단방향(오름차순)으로 이루어져 있기 때문에 기본적으로 오름차순의 검색이 내림차순 검색보다 빠르다.
7. 인덱스의 가용성과 효율성
- 인덱스가 아무리 있어도, 쿼리문을 개떡 같이 작성해서 날리면 인덱스의 혜택을 누리지 못한다.
1. 비교 조건의 종류 그에 따른 효율성
- A Index(dept_no,emp_no), B Index(emp_no,dept_no)라는 두개의 인덱스가 있다면 아래의 쿼리를 날렸을 때, 어떤 것이 더 효과적일까?
- Select * from dept_emp where dept_no=’d002’ and emp_no ≥ 10114;
- 다중 칼럼 인덱스에선 먼저 나오는 인덱스 키 값을 기준으로 먼저 정렬하고, 그 다음 인덱스 키 값으로 정렬한다. 그렇다면, 범주를 먼저 쉽게 나눌 수 있는 a Index가 유리하다. B에 경우는 emp_no의 범주들을 모두 검색한 뒤, dept_no이 d002인지 체크하는 필터링 방식으로 작동한다.
- 작업 범위 결정조건<인덱스의 효용성을 높이는> vs 필터링 조건<인덱스를 적절히 사용 못함>
2. 인덱스의 가용성
3. 가용성과 효율성 판단
- 쿼리마다 조건에 따라 작업 범위 결정조건으로 적용돼 효율성이 높아질 수 있고, 그에 반해 필터링 조건으로 적영돼 효율성이 떨어질 수 있다.
- WHERE column <> ‘N’ ( <>는 ! = 와 같다) 와 같은 아닌 것을 골라내야 하는 경우엔 필터링이 적용
- WHERE column like ‘%승환’ (%는 문자열에 사용되는 와일드 카드, 이것은 맨 뒤가 승환으로 끝나는 모든 문자열). 인덱스는 문자열 키의 경우 앞 부분을 기준으로 정렬이 된다. 근데 그 정렬과 상관없이 문자열 뒷부분으로 찾으면 의미가 없다.
- where substring(column,1,1) = ‘x’ 와 같이 다른 연산자로 인덱스 칼럼이 변형된 후에 비교된 경우
R-Tree 인덱스 ( R은 사각형을 의미하는 rectagle)
클러스터링 인덱스
유니크 인덱스
외래키