>[MySQL] DB 인덱스의 구조와 적용

 

 

우리가 전화부번호부에서 '홍길동' 을 찾을려면 첫장부터 다 뒤져봐야 할것이다. 인덱스를 사용하면 가나다순 이나, 이름으로 접근할 수 있으므 이런 수고로움을 덜어준다.

■

1. 인덱스 (Index) 개념 

《Index 사전적 의미》
명사적 의미: 문서나 책에서 주제, 단어, 용어 등을 정리하고 색깔, 알파벳, 숫자 등을 이용하여 순서대로 나열한 목록
동사적 의미: '정보를 정리하여 색깔, 알파벳, 숫자 등을 이용하여 순서대로 나열하다'를 의미 => 색인화(indexing)

《 데이터베이스에서 사용하는 Index 의미 》
테이블의 하나 이상의 열(칼럼)에 대한 정렬된 데이터 구조로 검색 속도를 향상시키기 위해 사용된다. 즉 원본 테이블의 데이터를 기반으로 생성되는 추가적인 데이터 구조다.

 

 

(1) 특징 

① 정렬된 저장 구조: 보통 B-tree나 해시 테이블과 같은 정렬된 데이터 구조를 사용하여 데이터를 저장하는데 이러한 구조는 검색, 레코드 접근 및 순서 매김 동작을 효율적으로 수행할 수 있도록 한다.

② 검색 성능 향상: 값을 미리 정렬하여 저장하므로, 검색 시에 선형적인 탐색이 아니라 이진 검색과 같은 알고리즘을 사용하여 빠르게 데이터를 찾을 수 있다.  

③ 고유성 유지: 유니크 인덱스는 특정 열(칼럼)에 중복된 값을 허용하지 않으므로 데이터의 정확성과 일관성을 유지할 수 있다.

④ 레코드 접근 효율: key값을 기반으로 데이터를 정렬하고 저장하기 때문에 해당 레코드에 빠르게 접근할 수 있다. 

⑤ 쿼리 성능 최적화: 쿼리에서 인덱스가 사용되면 쿼리의 실행 계획이 최적화되어 성능이 향상된다. (ex. 특히 WHERE, JOIN 등)

 

(2) 고려사항 

① 추가적인 저장 공간: 데이터를 별도의 구조로 저장하므로 추가적인 저장 공간이 필요하다.

② 동기화: 데이터베이스나 검색 엔진과 동기화되어야 하며, 데이터의 변경 시에 색인을 업데이트하여 일관성을 유지해야 한다.

③ 업데이트 비용: 데이터의 삽입, 수정, 삭제 작업에 대한 비용이 증가

④ 과다 사용시 성능 저하 발생

 

---

■

2. 인덱스 (Index) 구조 

※ mysql 에서 제공하는 인덱스 종류: B-tree 인덱스,  해시 인덱스, 전체 텍스트 인덱스 등

 

인덱스는 키(key)와 해당하는 행의 식별자(rowid 또는 주소)을 사용하여 인덱스를 탐색하고 실제 테이블의 행을 가져온다.

 

예를 들어, emp 테이블에서 emp_id 컬럼에 대한 인덱스가 있다고 해보자. 이 인덱스는 emp_id 값을 키로 가지고 있고, 각 키에 해당하는 행의 식별자(rowid)를 포함하고 있다. 이때 인덱스를 사용하여 emp_id 값에 해당하는 행을 직접 읽는다.

 

★ 인덱스를 사용하여 테이블의 행을 직접 읽는 과정 ★ 

1. 인덱스 검색: 데이터베이스는 인덱스를 사용하여 특정 키 값에 해당하는 행의 식별자(rowid)를 찾는다.

2. 행의 식별자 조회: 인덱스에서 가져온 행의 식별자(rowid)를 사용하여 실제 테이블에서 해당하는 행을 찾는다. 

3. 행 읽기: 식별자(rowid)를 사용하여 테이블에서 해당하는 행을 읽어옴

 

.

 

인덱스를 스캔하고 테이블을 참조하는 것을 보여준다.

 

 

(1) 인덱스 구성요소 

▶ 루트 노드 (Root Node) 

:인덱스의 시작점, 모든 데이터 구조의 진입점 역할

▶ 브랜치 노드 (Branch Node) / 내부 노드 (Internal Node)

: 데이터를 보다 빠르게 찾기 위한 중간 노드, 각 노드는 다른 노드를 가리키는 포인터와 함께 키(key) 값들을 가지고 있음.

▶ 리프 노드 (Leaf Node) 

:실제 데이터가 저장되는 단말 노드, 각 노드는 테이블의 실제 데이터 레코드(행)를 가지고 있음

- double linked list 형태로 되어 있어서 양방향 참조가 탐색이 가능하다.

- 인덱스 키와 ROWID 로 구성되어 있고 인덱스 키는 정렬되어서 저장되어 있다.

▶ 키 (Key)

: 인덱스 키는 데이터 레코드의 위치를 가리키는 키와, 인덱스를 생성할 때 선택한 컬럼의 값을 포함 하고 있다.

▶ 포인터 (Pointer) 

:각 노드는 다른 노드를 가리키는 포인터를 갖고있음. 포인터는 인덱스의 다음 단계 노드를 가리키며, 데이터를 찾기 위해 이 포인터를 따라가면 된다.

---

★ 인덱스 설계 원칙

주로 자주 검색되는 열, 조인에 사용되는 열, 그리고 WHERE 절에서 사용되는 열에 인덱스를 생성하는 것이 좋다. 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 더 좋다.

---

■

3. MySQL  인덱싱  

(1) 인덱스 생성  

- 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 칼럼으로 구성될 수 있다.

- 인덱스 키는 기본적으로 오름차순으로 정렬한다.

# column_name1 은 오름차순, column_name2 내림차순으로 인덱스를 생성한다.
CREATE INDEX idx_name ON table_name(column_name1 ASC, column_name2 DESC );

# name 컬럼에 대해 인덱스를 생성한다.
# name 컬럼이 인덱스 키가 된다.
CREATE INDEX index_customers ON CUSTOMERS (name);

 

 

(2) 기존 테이블에 인덱스 추가 및 변경  

# 기존 테이블에 새로운 인덱스 추가
ALTER TABLE table_name ADD INDEX index_name (column_name);

# 인덱스 삭제
ALTER TABLE table_name DROP INDEX index_name;

 

 

(3) 인덱스 스캔 (Index Scan)

① IndexScan (Full Index Scan): 인덱스의 모든 키를 순차적으로 검색하여 일치하는 행을 찾는다.

② Index Range Scan: SELECT 문에서 특정범위를 조회하는 WHERE 문을 사용할 경우 발생한다. (ex. like, between) 해당하는 범위 내의 키를 검색하여 조건을 만족시키는 행을 찾는다. 

③ Index Unique Scan: 인덱스 키 값이 중복되지 않은 경우, 발생한다.

④ Index Merge Scan: 여러 개의 인덱스를 병합하여 조건에 맞는 행을 찾는다. 

 

 

(4) 인덱스 정보 조회

 생성한 인덱스는 다음과 같은 문법을 통해 확인할 수 있다.

SHOW INDEX FROM 테이블이름

column
Table	테이블의 이름을 표시함.
Non_unique	인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함.
Key_name	인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함.
Seq_in_index	인덱스에서의 해당 필드의 순서를 표시함.
Column_name	해당 필드의 이름을 표시함.
Collation	인덱스에서 해당 필드가 정렬되는 방법을 표시함.
Cardinality	인덱스에 저장된 유일한 값들의 수를 표시함.
Sub_part	인덱스 접두어를 표시함.
Packed	키가 압축되는(packed) 방법을 표시함
Null	해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고, 저장할 수 없으면 ''를 표시함
Index_type	인덱스에 사용되는 메소드(method)를 표시함.
Comment	해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보를 표시함.
Index_comment	인덱스에 관한 모든 기타 정보를 표시함.

 

 

---

■

4. MySQL  인덱스 실행 확인 

 

EXPLAIN을 사용하여 인덱스 사용 여부 및 쿼리의 실행 방식을 확인할 수 있다. 

EXPLAIN SELECT * FROM 테이블명 WHERE 조건절;

 

 

EXPLAIN 문 실행 결과

 

① id: EXPLAIN 결과에서 각 행의 식별자입니다. 여러 테이블의 조인이 있을 경우, 각 행에 대해 고유한 id 부여

② select_type: 쿼리의 실행 유형

(SIMPLE: 단순한 SELECT 쿼리, PRIMARY: 최상위 쿼리, SUBQUERY: 서브쿼리, DERIVED: FROM 절에서 파생된 테이블 등등)

③ table: 행을 읽는 데 사용되는 테이블의 이름

④ partitions: 쿼리에서 사용된 파티션 정보입니다.

⑤ type: 레코드를 읽는 방법

- ALL: 풀 테이블 스캔

- INDEX: 인덱스 풀 스캔

- RANGE: 인덱스 범위 스캔

- REF: 인덱스를 사용하여 테이블의 특정 레코드를 찾음

- CONST: 상수 테이블을 읽음

- EQ_REF: 유일한 키를 기반으로 테이블의 한 레코드만 읽음 등등

⑥ possible_keys: 쿼리에서 고려된 인덱스 목록입니다.

⑦ key: 실제로 사용된 인덱스의 이름입니다.

⑧ key_len: 사용된 인덱스의 길이입니다.

⑨ ref: 테이블의 열 또는 상수를 사용하여 인덱스에서 행을 찾는 방법

⑩ rows: 쿼리 실행 결과로 검색된 행 수. 이 값이 작을수록 쿼리 성능이 더 좋다.

① filtered: 행 필터링 비율

② Extra: 추가 정보를 제공 (ex. 조인 타입이나 파일 정렬 등의 정보)

 

 

---

■

5. 인덱스 참조 안할경우 

일반적으로 데이터베이스 옵티마이저는 쿼리를 실행할 때 여러 실행 계획을 고려하고 최적의 방법을 선택하지만 때때로 옵티마이저가 최적의 인덱스를 선택하지 못하는 경우가 있을 수 있다. 이럴 때 USE INDEX 힌트를 사용하여 쿼리 실행에 특정 인덱스를 강제한다.

SELECT * FROM 테이블명 USE INDEX (인덱스명)
WHERE 조건;

 

 

---

■

6. 테이블의 통계 정보 업데이트 

이후에 테이블에 새로운 데이터가 추가되면 해당 인덱스는 업데이트 되지 않는다. 따라서 정기적으로 테이블의 통계 정보를 업데이트 한다.  데이터베이스 옵티마이저는 이 통계 정보를 사용하여 쿼리 실행 계획을 최적화한다.

#ANALYZE TABLE: 테이블의 통계 정보를 수집.(각 컬럼에 대한 데이터 분포, 카디널리티, NULL 값의 비율 등)
ANALYZE TABLE 테이블명;

 

 

 

 

참고

https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html