📋랜덤 I/O vs 순차 I/O

• 랜덤 I/O는 임의의 위치에 있는 데이터를 읽거나 쓰는 입출력 작업입니다.
• 순차 I/O는 연속적인 순서대로 데이터를 읽거나 쓰는 입출력 작업입니다.
• 랜덤 I/O는 순차 I/O에 비해 헤드의 움직임이 많기 때문에 느립니다.
• 일반적으로 순차 I/O는 큰 데이터 블록을 처리할 때 빠른 성능을 제공하고, 랜덤 I/O는 특정 데이터를 찾아서 처리할 때 적합합니다.

📋인덱스

✏️정의와 장단점

인덱스는 컬럼의 값과 해당 레코드의 주소를 Key-Value 형태로 저장하는 자료 구조입니다.

장점

• 레코드를 빠르게 조회, 정렬, 그룹화할 수 있습니다.

단점

• 테이블에 삽입, 수정, 삭제를 할 때마다 인덱스에도 변경이 발생하기 때문에 오버헤드가 생깁니다.
  (B-Tree로 구현되어 있으면 트리의 구조 조정에도 시간이 소요될 수 있습니다.)
• 추가적인 저장 공간을 차지합니다.
• 이 때문에 불필요한 인덱스는 만들지 않는 것이 좋습니다.
  
  

✏️인덱스의 동작 방식(B Tree 기준)

1. 사용자 요청으로 인덱스가 사용될 수 있는 쿼리가 들어옵니다.
2. 조건에 해당하는 레코드를 찾기 위해 루트 노드를 시작으로 브랜치 노드를 거쳐서 해당하는 리프 노드를 찾습니다.
3. 리프 노드에 있는 레코드의 실제 주솟값을 따라 데이터 파일에서 레코드를 조회해 옵니다.
   
   

✏️인덱스 설정 기준

• 검색 조건에 자주 사용되고 카디널리티가 높은 컬럼을 인덱스로 설정하는 것이 좋습니다.
• 카디널리티가 높은 컬럼에 인덱스를 걸면 그만큼 검색 대상이 줄어들기 때문에 빠르게 레코드에 접근할 수 있습니다.

카디널리티

전체 행에 대한 특정 컬럼의 중복 수치를 나타내는 지표입니다. 중복도가 낮으면 카디널리티가 높다고 표현하고, 중복도가 높으면 카디널리티가 낮다고 표현합니다.
카디널리티는 상대적인 개념으로 이해해야 하는데, 예를 들어, 주민등록번호의 경우는 중복되는 값이 없으므로 카디널리티가 높다고 할 수 있고, 이에 비해 이름의 경우는 주민등록번호보다 중복되는 값이 많으므로, 이름은 주민등록번호에 비해 카디널리티가 낮다고 할 수 있습니다.

✏️커버링 인덱스

• 쿼리 조회 결과가 인덱스에 해당하는 컬럼값으로만 이루어진 것을 커버링 인덱스라고 합니다.
• 커버링 인덱스로 처리되는 쿼리는 원본 테이블의 레코드를 읽지 않아도 되기 때문에 그만큼 랜덤 엑세스가 줄어들고 조회 성능은 빨라집니다.
  
  

✏️멀티 컬럼 인덱스(복합 인덱스)

• 멀티 컬럼 인덱스는 2개 이상의 컬럼으로 이루어진 인덱스입니다.
• 인덱스를 생성할 때 지정하는 컬럼의 순서에 따라 정렬 방식이 달라지기 때문에 해당 부분을 고려해서 사용해야 합니다.
  
  

✏️B-Tree 인덱스 vs B+Tree 인덱스

• B-Tree 인덱스와 B+Tree 인덱스는 둘 다 Balanced Tree 자료구조로 구현된 인덱스 저장 방식입니다. 즉, 모든 리프 노드가 같은 레벨에 있는 상태이기 때문에, 평균 혹은 최악의 상황에서도 모두 O(logN)의 성능을 유지할 수 있습니다.
• B-Tree 인덱스와 달리 B+Tree 인덱스는 데이터를 찾기 위한 포인터와 실제 데이터가 리프 노드에만 저장되어 있습니다.
  (내부 노드에는 오로지 키만 저장됩니다.)
• 또한 B+Tree의 리프 노드는 연결 리스트로 서로 연결되어 있기 때문에, 범위 검색 성능이 좋습니다.

비 트리(B Tree, Balanced Tree)

이진 탐색 트리(BST, Binary Search Tree)는 자식 노드를 2개만 가질 수 있습니다. 자식 노드를 그 이상 가지려면 아래 그림과 같은 형태가 돼야 하는데, 이것이 비 트리입니다.

내부 노드(루트, 리프가 아닌 노드, internal node 혹은 branch node라고 부릅니다.)의 Key 개수가 n이라면 자식 노드의 수는 언제나 n + 1개여야 합니다. 따라서 아래와 같은 형태는 비 트리에서 나올 수 없습니다.

추가로 비 트리는 삽입과 삭제가 항상 리프 노드에서 시작됩니다.

✏️Hash 인덱스

• Hash 인덱스는 컬럼에 대해 해시값을 계산하고, 이를 저장하는 인덱스 구조입니다.
• 등가 검색이 매우 빠르다는 장점이 있지만, 값을 변형해서 인덱스로 사용하기 때문에, 범위 검색에서는 사용될 수 없다는 단점이 있습니다.
• 또한 멀티 컬럼 인덱스를 사용할 때, 하나의 컬럼만 사용할 수 없고, 해당하는 컬럼이 모두 사용되어야 한다는 단점도 있습니다.
  
  

✏️클러스터드 인덱스

• 클러스터드 인덱스는 테이블의 데이터가 특정 인덱스 키에 따라 물리적으로 정렬되어 저장되는 인덱스입니다.
• 테이블당 단 하나의 클러스터드 인덱스만 존재할 수 있습니다.
• 클러스터드 인덱스를 따로 지정하지 않으면, 기본 키(Primary Key)가 클러스터드 인덱스가 됩니다.
• MySQL의 InnoDB 스토리지 엔진은 기본적으로 데이터를 클러스터드 인덱스 구조로 저장합니다.

스토리지 엔진은 테이블의 데이터를 어떻게 저장하고 처리할지 결정합니다. InnoDB 스토리지 엔진은 그 종류의 하나로, 클러스터드 인덱스를 사용하고, 트랜잭션, 외래 키 등을 지원합니다.

✏️인덱스 스캔 방식(B+Tree 기준)

• Index Full Scan
  • 인덱스의 처음부터 끝까지 모두 스캔하는 방식입니다.
  • 테이블 풀 스캔보다 효율적입니다.
• Index Range Scan
  • 검색해야 할 인덱스의 범위가 결정됐을 때 사용되는 방식입니다.
  • 루트 노드와 내부 노드를 거쳐 검색 시작점이 되는 리프 노드를 찾고, 리프 노드 간의 링크를 통해 검색 종료점 리프 노드까지 스캔하면서 레코드를 랜덤 엑세스로 읽어 들입니다.
    
    

✏️인덱스가 잘 동작하고 있는지 확인하는 방법

• 쿼리에 대한 실행 계획을 보면서 어떤 인덱스를 사용하고, 어떤 스캔 방식을 사용하고 있는지 확인합니다.
• 실행 계획은 옵티마이저가 SQL을 어떻게 실행할지에 대한 계획입니다.
• SQL을 효율적으로 실행하기 위해 옵티마이저는 비용 기반 혹은 규칙 기반으로 실행계획을 생성하고 쿼리를 실행합니다.
• EXPLAIN
  
  • 쿼리의 예상 실행 계획을 보여주며, 실제로 쿼리를 실행하지 않습니다.
• EXPLAIN ANALYZE
  
  • 쿼리를 실제로 실행하기 때문에 더 정확한 성능 분석이 가능합니다.
    
    

✏️인덱스 사용 시 주의 사항

• 인덱스의 키값이 너무 길어지게 하지 않습니다.
  • 인덱스 정보들을 담고 있는 페이지 수가 증가합니다.
  • 디스크로부터 메모리로 페이지를 읽어야 하는 횟수가 늘어나는 것을 뜻하고, 이는 I/O 비용 증가와 성능 저하의 원인이 됩니다.
• 데이터가 자주 변경되는 컬럼에 인덱스를 적용하는 건 피하는 게 좋습니다.
  • 인덱스가 자주 업데이트되므로 성능이 저하될 수 있습니다.
• 멀티 컬럼 인덱스를 사용하는 경우, 컬럼의 순서를 고려해야 합니다.
  • 자주 사용되는 조건 순서에 따라 인덱스를 설정하는 것이 좋습니다.
    
    

✏️GROUP BY 사용시 인덱스가 걸리는 조건

• GROUP BY 절에 명시된 컬럼이 인덱스 컬럼의 순서와 같아야 합니다.
• 예를 들어 멀티 컬럼 인덱스 (a, b, c)는 GROUP BY 절의 컬럼이 (a, b, c), (a, b), (a) 중 하나일 때 걸립니다.
  
  

📋쿼리 힌트

• 쿼리 힌트는 실행 계획을 조정하기 위해 옵티마이저에게 특정한 지시를 제공하는 명령어입니다.
• 쿼리 힌트를 사용하여 옵티마이저가 쿼리를 처리하는 방식을 직접 제어할 수 있습니다.
• 쿼리 힌트는 주로 성능 최적화나 예상치 못한 실행 계획을 수정하고자 할 때 사용합니다.
• 예를 들어, 보통은 옵티마이저가 알아서 적절한 인덱스를 선택해 주지만, 간혹 이상한 인덱스를 선택할 때가 있습니다. 이럴 때는 인덱스를 직접 선택해서 사용해야 하는데, 아래와 같은 명령어를 통해 인덱스 사용을 제어할 수 있습니다.

SELECT * FROM user USE INDEX (age_idx) WHERE age = 20;
SELECT * FROM user FORCE INDEX (age_idx) WHERE age = 20;

📋기타

• ORDER BY와 GROUP BY 절에도 인덱스가 사용될 수 있습니다.
• 외래키에는 인덱스가 자동으로 생성되지 않을 수 있습니다. (MySQL은 자동 생성)
• 이미 수백만 건 이상의 데이터가 있는 테이블에 인덱스를 생성할 경우, 시간이 몇 분 이상 소요될 수 있으며, 이 과정에서 삽입, 수정, 삭제 작업의 성능이 저하될 수 있습니다. 따라서 서비스의 트래픽이 적은 시간에 인덱스를 추가하는 것이 좋습니다.
• 스토리지
  • Secondary Storage
    • 데이터를 영구적으로 저장할 수 있는 모든 저장 매체를 포함하는 개념입니다.
    • HDD, SSD, 외장 드라이브 등
  • 디스크
  • 데이터 파일
    • MySQL에서 데이터베이스와 관련된 데이터를 구조화하여 저장하는 파일입니다.
    • 테이블의 데이터 등
  • 메모리
    • DBMS가 현재 실행 중인 쿼리와 관련된 데이터를 메모리에 로드합니다.
  • 쿼리 처리 흐름 예시
    1. 사용자가 MySQL 서버에 쿼리를 보냅니다.
    2. MySQL 서버는 쿼리를 분석하여 필요한 데이터를 결정합니다.
    3. MySQL 서버는 Secondary Storage에 있는 데이터 파일에서 필요한 데이터를 읽어옵니다.
       (데이터 파일은 디스크에 저장되어 있습니다.)
    4. 읽어온 데이터가 메모리로 로드되고, CPU가 데이터에 빠르게 접근할 수 있게 됩니다.
    5. 결과가 사용자에게 반환됩니다.

---

✳️ 참고 자료

• 유튜브 쉬운코드 - 인덱스
• 유튜브 쉬운코드 - B Tree
• 카디널리티란?