mysql 인덱스

8.1 디스크 읽기 방식

8.1.1 하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)

• HDD는 기계식 장치로 성능 향상에 한계가 있는 반면, SSD는 플래시 메모리를 사용하여 데이터를 빠르게 읽고 쓸 수 있습니다.
• SSD는 HDD보다 랜덤 I/O 성능이 압도적으로 뛰어나며, 이는 데이터베이스 서버용 스토리지에 최적의 장점입니다.

8.1.2 랜덤 I/O와 순차 I/O

• 순차 I/O는 디스크 헤더를 한 번 움직여 많은 데이터를 읽지만, 랜덤 I/O는 데이터 페이지마다 헤더를 움직여야 하므로 작업 부하가 훨씬 큽니다.
• 쿼리 튜닝의 목적은 대개 랜덤 I/O 횟수를 줄이는 것이며, 이는 인덱스를 통해 꼭 필요한 데이터만 읽는 것을 의미합니다.

8.2 인덱스란?

• 인덱스는 책의 맨 끝에 있는 '찾아보기'와 같이 칼럼의 값과 해당 레코드가 저장된 주소를 쌍으로 관리하는 자료 구조입니다.
• 인덱스는 저장 성능(INSERT, UPDATE, DELETE)을 희생하는 대신 읽기 성능(SELECT)을 극대화하기 위해 항상 정렬된 상태를 유지합니다.
• SortedList와 ArrayList는 데이터 파일과 같은 자료 구조를 사용

8.3 B-Tree 인덱스

B-Tree는 데이터베이스 인덱싱 알고리즘 중 가장 범용적으로 사용되는 구조입니다. 여기서 'B'는 'Binary(이진)'가 아니라 'Balanced(균형 잡힌)'를 의미하며, 칼럼의 원래 값을 변형시키지 않고 정렬된 상태를 유지하는 것이 특징입니다,.

8.3.1 구조 및 특성

B-Tree는 최상위의 루트 노드, 중간의 브랜치 노드, 그리고 최하위의 리프 노드로 구성됩니다.

• 데이터 연결: 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소 값을 가집니다.
• 스토리지 엔진별 차이:
  • MyISAM은 리프 노드에 레코드의 물리적인 주소(ROWID)를 가집니다.
  • InnoDB는 프라이머리 키(PK) 자체가 인덱스 역할을 하는 클러스터링 구조이므로, 세컨더리 인덱스의 리프 노드는 PK 값을 주소로 사용합니다. 따라서 세컨더리 인덱스로 검색 시 PK 인덱스를 한 번 더 탐색해야 합니다,.

8.3.2 B-Tree 인덱스 키 추가 및 삭제

• 키 추가: 새로운 키 값이 저장될 위치를 B-Tree 상에서 검색한 뒤 리프 노드에 저장합니다. 리프 노드가 꽉 차면 노드를 분리(Split)해야 하며, 이 과정이 상위 노드까지 영향을 미치므로 쓰기 비용이 일반 레코드 쓰기보다 약 1.5배 더 듭니다,.
  • InnoDB의 특징: 체인지 버퍼를 이용해 인덱스 키 추가 작업을 지연 처리할 수 있으나, 중복 체크가 필요한 유니크 인덱스는 즉시 처리해야 합니다.
• 키 삭제: 해당 키 값이 저장된 리프 노드를 찾아 삭제 마크만 하면 완료됩니다. 이 공간은 나중에 재사용되거나 방치될 수 있으며, InnoDB에서는 이 작업도 지연 처리가 가능합니다.
• 키 검색: 루트부터 브랜치를 거쳐 리프 노드까지 이동하는 '트리 탐색'을 거칩니다. 100% 일치 또는 값의 왼쪽 부분(Prefix)이 일치할 때만 사용할 수 있으며, 인덱스 키 값에 변형이 가해지면 B-Tree의 빠른 검색 기능을 사용할 수 없습니다,,.

8.3.3 B-Tree 인덱스 사용에 영향을 미치는 요소

• 인덱스 키 값의 크기: 인덱스 페이지의 기본 크기는 16KB입니다. 키 값의 크기가 커질수록 한 페이지에 저장할 수 있는 키 개수가 줄어들어, 디스크 읽기 횟수가 늘어나고 B-Tree의 깊이(Depth)가 깊어져 성능이 저하됩니다,,.
• 선택도(기수성): 모든 인덱스 키 값 중 유니크한 값의 개수를 의미합니다. 선택도가 높을수록(유니크한 값이 많을수록) 검색 대상이 줄어들어 쿼리 처리가 빨라집니다,.
• 읽어야 하는 레코드 건수: 인덱스를 통해 레코드를 읽는 것은 직접 읽는 것보다 비용이 4배가 넘어가 25%를 넘어서면 인덱스를 사용하지 않고 테이블 풀 스캔을 하는 것이 더 효율적입니다

8.3.4 B-Tree 인덱스를 통한 데이터 읽기

1. 인덱스 레인지 스캔: 가장 대표적인 방식으로, 시작 위치를 찾은 뒤 필요한 범위만큼 순차적으로 읽습니다. 쿼리에서 필요로 하는 칼럼이 인덱스에만 있는 경우(커버링 인덱스) 디스크 I/O를 획기적으로 줄일 수 있습니다,,.
2. 인덱스 풀 스캔: 인덱스의 처음부터 끝까지 모두 읽는 방식입니다. 조건절의 칼럼이 인덱스의 첫 번째 칼럼이 아닐 때, 테이블 전체보다는 인덱스 크기가 작으므로 인덱스만 읽어 처리하는 방식입니다,.
3. 루스(Loose) 인덱스 스캔: 인덱스 레인지 스캔과 비슷하지만 중간에 필요치 않은 키 값은 무시하고 건너뜁니다. 주로 GROUP BY나 MAX(), MIN() 함수 최적화에 사용됩니다,.
4. 인덱스 스킵 스캔: 다중 칼럼 인덱스에서 선행 칼럼이 조건절에 없어도 옵티마이저가 선행 칼럼의 유니크한 값을 모두 추출하여 조건을 추가해주는 방식으로 검색을 수행합니다(MySQL 8.0 도입),,.

8.3.5 다중 칼럼(Multi-column) 인덱스

두 개 이상의 칼럼으로 구성된 인덱스로, 두 번째 칼럼의 정렬은 첫 번째 칼럼의 정렬에 의존합니다,. 따라서 인덱스 내 칼럼의 순서(위치)가 성능에 결정적인 영향을 미칩니다.

8.3.6 B-Tree 인덱스의 정렬 및 스캔 방향

• 정렬: MySQL 8.0부터는 각 칼럼의 정렬 순서를 혼합(ASC, DESC)하여 인덱스를 생성할 수 있습니다,.
• 스캔 방향: 옵티마이저는 쿼리에 따라 인덱스를 정순 또는 역순으로 스캔합니다,.
  • 주의: InnoDB에서 인덱스 역순 스캔은 페이지 내 레코드가 단방향으로만 링크된 구조적 특성 때문에 정순 스캔보다 약 28.9% 정도 더 느립니다,.

8.3.7 B-Tree 인덱스의 가용성과 효율성

• 왼쪽 값 기준(Left-most): B-Tree는 왼쪽 값을 기준으로 정렬되어 있으므로, 쿼리 조건에서 인덱스 칼럼의 왼쪽 부분이 없으면 인덱스 레인지 스캔을 사용할 수 없습니다.
• 인덱스 사용 불가 조건:
  • NOT-EQUAL 비교 (<>, NOT IN, IS NOT NULL)
  • LIKE '%...' (뒷부분 일치 검색)
  • 인덱스 칼럼의 변형 (함수나 연산 수행 후 비교)
  • 데이터 타입이 달라 형변환이 발생하는 경우

8.3.5 다중 칼럼(Multi-column) 인덱스

• 두 개 이상의 칼럼으로 구성된 인덱스로, 두 번째 칼럼은 첫 번째 칼럼에 의존하여 정렬되므로 칼럼의 순서가 매우 중요합니다.

8.3.6 B-Tree 인덱스의 정렬 및 스캔 방향

• 인덱스는 생성 시 지정한 순서(ASC, DESC)대로 정렬되지만, 옵티마이저가 필요에 따라 정순 또는 역순으로 스캔할 수 있습니다.

8.3.7 B-Tree 인덱스의 가용성과 효율성

• 왼쪽 값 기준(Left-most) 정렬 특성 때문에, 인덱스 칼럼의 왼쪽 부분이 없거나 변형된 경우 인덱스 레인지 스캔을 사용할 수 없습니다.

8.4 R-Tree 인덱스

8.4.1 구조 및 특성

• 2차원의 공간 데이터를 관리하며, 도형을 감싸는 최소 크기의 사각형인 MBR들의 포함 관계를 B-Tree 형태로 구현합니다.

8.4.2 R-Tree 인덱스의 용도

• GPS 기반의 위치 서비스나 CAD/CAM 등 좌표 시스템 기반 정보 검색에 사용됩니다.

8.5 전문 검색 인덱스

8.5.1 인덱스 알고리즘

• 문서 전체 내용을 인덱싱하며, 단어의 뿌리를 찾는 어근 분석(MeCab)과 일정 글자씩 자르는 n-gram 알고리즘 등이 사용됩니다.

8.5.2 전문 검색 인덱스의 가용성

• MATCH (...) AGAINST (...) 구문을 사용해야 하며, 해당 칼럼에 전문 검색 인덱스가 미리 생성되어 있어야 합니다.

8.6 함수 기반 인덱스

8.6.1 가상 칼럼을 이용한 인덱스

• 가상 칼럼을 추가하고 해당 칼럼에 인덱스를 생성하여 검색 성능을 높입니다.

8.6.2 함수를 이용한 인덱스

• MySQL 8.0부터 테이블 구조 변경 없이 함수 식 자체를 인덱스로 생성할 수 있습니다.

8.7 멀티 밸류 인덱스

• 하나의 레코드가 여러 개의 키 값을 가질 수 있는 형태로, 주로 JSON 배열 타입 필드의 원소들을 인덱싱할 때 사용합니다.

8.8 클러스터링 인덱스

8.8.1 클러스터링 인덱스

• 프라이머리 키(PK) 값에 의해 레코드의 물리적인 저장 위치가 결정되는 방식으로, PK 값 순서대로 정렬되어 저장됩니다.

8.8.2 세컨더리 인덱스에 미치는 영향

• InnoDB의 세컨더리 인덱스는 레코드 주소 대신 PK 값을 저장하므로, 인덱스 검색 후 PK 인덱스를 한 번 더 검색하는 과정을 거칩니다.

8.8.3 클러스터링 인덱스의 장점과 단점

• 장점: PK 기반 검색 및 범위 스캔이 매우 빠르며 커버링 인덱스 처리에 유리합니다.
• 단점: 레코드 저장 및 PK 변경 시 성능이 느리고 전체적인 인덱스 크기가 커질 수 있습니다.

8.8.4 클러스터링 테이블 사용 시 주의사항

• PK가 커지면 세컨더리 인덱스의 크기도 함께 커지므로 PK를 신중히 선택해야 하며, 가급적 업무적인 PK를 사용하되 없을 경우 AUTO_INCREMENT를 활용하는 것이 좋습니다.

8.9 유니크 인덱스

8.9.1 유니크 인덱스와 일반 세컨더리 인덱스의 비교

• 읽기 성능은 차이가 없으나, 쓰기 시 중복 체크 과정이 필요하고 체인지 버퍼를 사용할 수 없어 일반 인덱스보다 느립니다.

8.9.2 유니크 인덱스 사용 시 주의사항

• 유니크 인덱스는 일반 인덱스의 역할을 포함하므로, 동일 칼럼에 두 가지 인덱스를 중복해서 생성할 필요가 없습니다.

8.10 외래키

8.10.1 자식 테이블의 변경이 대기하는 경우

• 자식 테이블에서 외래키 칼럼을 변경할 때 부모 테이블의 해당 레코드에 쓰기 잠금이 걸려 있으면, 잠금이 해제될 때까지 대기해야 합니다.

8.10.2 부모 테이블의 변경 작업이 대기하는 경우

• 부모 레코드를 삭제할 때 자식 테이블에 ON DELETE CASCADE 설정이 있다면, 자식 레코드의 잠금이 해제될 때까지 부모 테이블의 삭제 작업이 대기합니다.