MySQL Index에 대해 알아보자

1. 개요

2. Index란?

3. Index 선정 기준

4. Index 동작 방식

5. 결론

1. 개요

MySQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색하는데 테이블이 크면 클수록 데이터를 탐색하는 시간도 많이 늘어나게 된다.

하지만, 인덱스를 사용하면 테이블 전체를 읽지 않아도 검색과 질의에 대한 처리가 빠르게 이루어진다.

2. Index란?

DB 테이블에서 원하는 데이터를 빠르게 검색하고 조회하기 위해 사용되는 데이터 구조이다.

인덱스가 없더라도 데이터베이스를 '작동' 하는데 있어서는 문제는 없다.

하지만 데이터베이스의 크기가 억대 단위로 크면 클 수록, 인덱스가 반드시 필요해진다. 인덱스는 데이터베이스의 성능(속도)를 크게 좌우하는 요소이기 때문이다.

따라서, 인덱스를 사용하는 SQL을 만들어 효율적으로 사용한다면 매우 빠른 응답 속도를 얻을 수 있고, 쿼리의 부하가 줄어들기 때문에 시스템 전체 성능이 향상되는 효과를 얻는다.

인덱스의 특징

• select 검색 속도를 크게 향상 시킨다.

• 인덱스 생성 시 DB 크기의 약 10% 정도되는 추가 공간이 필요하다.

• 인덱스 생성 시 시간이 걸린다. (몇분 씩 걸리기도 한다)

• insert, update, delete같은 데이터 변경 쿼리가 잦은 경우 paging이 빈번해져 성능이 악화될 수 있다.

• 데이터 조회에는 플러스지만, 데이터 변경이 자주 일어나면 오히려 성능 감소된다.

3. Index 선정 기준

1. WHERE 절에 사용되는 열 (WHERE 절에 사용되는 열이라도 자주 사용해야 가치가 있음)

2. SELECT 절에 자주 등장하는 컬럼들을 잘 조합해서 INDEX로 만들어두면 INDEX 조회 후 다시 데이터에서 조회할 필요가 없으므로 빠르게 검색이 가능하다.

3. JOIN절에 자주 사용되는 열에는 인덱스의 효율이 좋음.

4. ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬됐기 때문에 클러스터형 인덱스가 유리.

4. Index 동작 방식

B-Tree 인덱스 알고리즘

• B-Tree 인덱스는 칼럼의 값을 변형하지 않고, 원래의 값을 이용해 인덱싱하는 알고리즘이다.

• 이진트리의 확장 버전으로, 여러 개의 자식 노드를 가진 균형 잡힌 트리 형태를 띠고 있고 한 노드에 여러개의 키-값 쌍을 저장하며, 정렬된 상태를 유지한다.

• B-tree 인덱스는 특정 열의 값들을 정렬한 상태로 저장한다.

• 특정 값으로 검색을 수행하면 B-tree는 트리의 루트 노드부터 시작하여 해당 값을 찾을 때까지 이진 탐색과 유사한 방식으로 탐색한다.

• 루트 노드에서 자식 노드로 내려가면서 해당 값이 위치한 리프 노드를 찾게 된다.

• 이러한 과정을 통해 기존 테이블에서 Full Scan해서 O(N)이 걸려 데이터를 찾을때 보다 효율적으로 O(log N) 검색을 수행할 수 있게 된다.

B+Tree 인덱스 알고리즘

실제 데이터베이스는 B+Tree 데이터를 보관하는 경우가 많다.

• 데이터는 맨밑에만 리프노드에만 보관하고 그 위에 노드들은 탐색에 필요한 가이드만 제공을 한다.

• B-Tree와 마찬가지로 적은 횟수로 데이터를 찾을 수 있게 된다.

• B-Tree와 가장 다른점은 맨 아래 리프노드끼리도 화살표로 연결을 하여 범위 검색을 효율적으로 조회를 할 수 있게된다.

5. 결론

Index가 없는 경우

1. 모든행을 다 Full Sacn함

Index가 있는 경우

1. 인덱스에서 빠르게 조회

2. 연결된 원래 테이블 행을 가져옴

• 인덱스는 컬럼을 복사해서 정렬을 해두는 개념이기 때문에 인덱스를 만들때마다 DB 용량을 차지하게된다. 그래서 검색작업이 필요없는 컬럼은 인덱스를 만들 필요 없다

• insert, update, delete같은 데이터 변경 쿼리가 잦은 경우 인덱스에도 똑같이 반영을 해줘야하기 때문에 데이터를 추가하고 수정하는 작업이 추가로 들어간다.

참고

• https://www.youtube.com/watch?v=iNvYsGKelYs

• https://inpa.tistory.com/entry/MYSQL-📚-인덱스index-핵심-설계-사용-문법-💯-총정리#b-tree_인덱스_알고리즘

• http://www.tcpschool.com/mysql/mysql_index_create

Uploaded by N2T