Post

[CS 학습] 인덱스란?

Posted
By waterricecake
37 min read
[CS 학습] 인덱스란?

1.1 디스크 읽기 방식

1.1.1 HDD와 SSD 차이

기존 자기방식의 HDD에 비해 flash memory (기록 보존이 가능한 반도체)를 활요한 SSD의 성능이 더 뛰어남

SSD의 경우 헤더를 움직이지 않기 때문에 작은 데이터를 읽고 쓰는 랜덤 I/O에서 큰 성능차이를 보임

DBMS에서는 작은 데이터를 읽고 쓰는 작업이 대부분 이므로 SSD가 최적

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

랜덤 io데이터 마다 읽어야할 데이터로 디스크 헤더를 이동
순차 io한번만 읽어야할 데이터로 디스크 헤더를 이동

→ 데이터를 여러번 쓰고 읽을 때에는 랜던 io에 더 많은 시간이 소요됨 (헤더의 이동시간이 병목이 되므로)

헤더가 없는 SSD의 경우 순차 io보다 랜덤 io의 전체 스루풋이 떨어짐 (좀더 확인해보기)

  • SSD의 경우 내부적으로 논리적인 주소를 물리적인 주소로 맵핑하는 맵핑테이블을 사용
  • 매핑 테이블을 업데이트하려면 플래시 메모리의 물리적인 위치를 찾아가야 하므로 랜덤 I/O의 경우에는 이 과정이 추가로 발생하여 전체 처리율이 떨어짐
  • 순차I/O의 경우 논리적인 위치를 통해 알아낸 물리적인 위치를 통해 나머지 항목들도 업데이트

1.2 인덱스란?

인덱스란 일종의 책의 색인 (찾아보기)

<칼럼값, 저장된 주소>의 키와 값의 쌍을 갖는다

DBMS의 인덱스 자료구조는 SortedList를 가진다.

 SortedListArrayList
자료 순서 기준값이 저장될때 마다 정렬됨값이 저장된 순서
쓰기, 수정, 삭제느림빠름
읽기빠름느림

sortedList의 경우 쓰기(INSERT), 수정(UPDATE), 삭제(DELETE)의 경우 속도가 느리지만 읽기(SELECT)의 경우 속도가 빠르다.

인덱스의 역할별 분류

  • 프라이머리 키 (PK)
    • 그 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스
    • 식별자라고도 불리며 NULL을 허용하지 않는다
  • 세컨더리 인덱스
    • PK를 제외한 나머지 인덱스
    • 유니크 인덱스의 경우 프라이머리키를 대체할 수도 있기에 대체키라고 함

저장 방식(알고리즘)별 인덱스

  • B-tree
    • 가장 일반적으로 쓰이는 인덱스
    • 기존 칼럼의 값을 변형하지 않음
  • Hash
    • 칼럼값으로 해시값을 계산하여 인덱싱
    • 기존 값을 변형해서 인덱싱하므로 값의 일부만 검색하거나 범위 검색시에는 사용할 수 없다
    • 주로 메모리 기반의 데이터베이스에서 사용

데이터 중복 허용 여부

쿼리를 실행할 옵티마이저에게 항상 1건의 레코드 이후에 추가적인 업무를 해야할 지 말아야할지 결정하게 하기 때문에 큰 차이가 발생한다. 또한 인덱스로 인한 MySQL의 처리리 방식의 변화나 차이점이 상당히 많다.

  • 유니크 인덱스
    • 인덱스의 값이 유일
  • 중복 인덱스 (Non-Unique)
    • 인덱스의 값 중복을 허용

1.3 B-Tree 인덱스

B-Tree (Balanced tree)

1.3.1 구조 및 특성

인덱스의 키값은 정렬이 되어있지만 데이터 파일의 레코드 경우 정렬되지 않는다.

→ 데이터 파일 insert된 순서대로 저장되지 않고 삭제하여 빈공간이 생길경우 그 공간을 재활용하기 때문에 순서를 보장할 수 없다.

→ InnoDB의 경우 클러스터되어 디스크에 저장되므로 프라이머리 키 순서로 정렬되어 저장된다.

스토리지 엔진에 따른 구조 차이

  • MyISAM
    • ROWID(물리적인 주소값)를 세컨데리 키로 가지고 있어 이를 통해 데이터에 접근
  • InnoDB
    • PK가 ROWID역할을 함
    • PK값을 인덱스 키로 가지고 있는 B-Tree를 한번더 검색하여 데이터에 접근 (B-tree가 두개)

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

1.3.2.1 인덱스 키 추가

B-Tree에 저장될 때,

  1. 저장될 키 값의 위치 검색
  2. 위치가 결정 될 시 리프 노드에 저장
    1. 리프 노드가 꽉 차 있을 시 리프노드를 분리 → 상위 브랜치 노드까지 처리의 범위가 넓어짐

이러한 과정때문에 쓰기 작업에 비용이 더 많이 듬

  • MyISAM
    • Insert 문장이 실행 되면 즉시 새로운 키 값을 B-Tree 인덱스에 반영
  • InnoDB
    • 체인지 버퍼를 통해 인덱스 키 추가 작업을 지연시켜 나중에 처리
    • 프라이머리 키, 유니크 키의 경우 인덱스 중복 체크가 필요하기 때문에 즉시 처리해야함

1.3.2.2 인덱스 키 삭제

삭제시 해당 리프 노드를 찾아 삭제 마크 작업을 함

이후 삭제 마킹된 인덱스 키 공간은 방치 혹은 재활용하여 사용

마킹 시 디스크 쓰기가 필요 → 디스크 I/O 작업이 필요

MySQL 5.5 이상의 버전에서는 InnoDB에서 지연 처리 가능

1.3.2.3 인덱스 키 변경

원래 키값을 삭제 후 새로운 키 값을 추가하는 형태로 처리됨 (삭제 후 추가)

InnoDB에서 지연 처리 가능

1.3.2.4 인덱스 키 검색

  • 트리 탐색을 통해 인덱스를 루트 → 브랜치 → 리프 노드 순으로 비교 작업을 수행
  • 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용 가능
  • 키의 뒷부분이나 키 값에 변형이 이루어진 경우 빠른 검색 불가능
    • 함수나 연산을 통한 검색 혹은 정렬은 비효율적
    • 단순 검색 혹은 비교 조건의 검색에서만 효율적으로 검색 가능
  • InnoDB의 경우 검색 수행시 수행하는 인덱스를 잠근 후 테이블 레코드를 잠그는 방식으로 구현

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

1.3.3.1 인덱스 키 값의 크기

  1. 인덱스 페이지

    • 디스크에 데이터를 저장하는 가장 기본 단위
    • 루트, 브랜치, 리프 노드를 구분하는 기준 (단위)

    1. 이진 (Binary) 트리와 B(Balanced)-Tree의 차이점은 자식 노드의 개수가 가변적이라는 점

    2. MySQL (5.7)은 기본값이 16KB (설정에 따라 4KB ~ 64KB)

    3. 페이지당 키의 개수: N * 1024 / (n + k)

      (N : 인덱스 페이지 크기, n : 키 크기, k : 자식노드주소 크기)

    4. 키값이 커질 경우 페이지당 키의 개수가 작아짐

      1. 같은 양의 레코드를 읽어야 할때 순회해야할 횟수가 늘어남
      2. 인덱스를 캐시해둘 영역의 공간이 제한적이기 때문에 캐시해둘 레코드 수가 적어짐

      → 효율이 적어짐

1.3.3.2 B-Tree 깊이

  • 인덱스의 깊이를 제어할 수 있는 방법은 없으나 인덱스 키값의 d(깊이) 제곱에 반비례하여 저장할 수 있는 키값이 달라짐

N : 인덱스 페이지 크기, n : 키 크기, k : 자식노드주소 크기, 깊이 : d

총 키의 수 : (N * 1024 / (n+k))^d ≒ 1/n^d

즉 인덱스 키값이 커질 수 록 저장할 수 있는 키들의 개수가 적어지는 것은 물로 깊이가 깊어지기 때문에 더 많은 디스크 읽기가 발생하게 되는 것이다

결론, 인덱스 키값의 크기는 되도록이면 작게 하는 것이 좋다

1.3.3.3 선택도(기수성)

선택도 (Selectivity) == 기수성 (Cardinality)

모든 인덱스 키 값 중 유니크한 값의 갯수 - N/n N : 총 인덱스 개수, n : 유니크한 값

중복된 값 ▼ 유니크한 값 ▲ 기수성 ▲ 검색대상 ▼ 처리속도 ▲

N : 총 인덱스 개수, n : 유니크한 인덱스

책의 예시 참고 잘 나와있음

요약하자면 인덱싱을 통해 데이터를 조회할때 N/n 건을 조회해야 하는데 그중 필요한 것은 1건이므로 N/n-1 번의 불필요한 데이터를 조회하게 됨, 따라서 유니크한 값이 늘어날 수록 불필요한 데이터 검색이 줄어들고 그만큼 효율적으로 검색을 할 수 있게됨

1.3.3.4 읽어야 하는 레코드 의 건수

일반적으로 인덱스를 통해 레코드를 읽는 것이 그렇지 않은 것보다 높은 비용이 들게 됨

MySQL의 코스트 모델에서 전자의 경우가 건당 4~5배의 비용이 드는 것으로 예측

→ 인덱스를 통해 읽어야할 레코드 수가 전체 레코드의 20~25% 미만일 경우 인덱스를 사용한 방식이 효율적

→ 그 이상은 테이블을 모두 읽는 것이 효율적

20~25%가 인덱싱을 통한 손익분기점이다.

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

1.3.4.1 인덱스 레인지 스캔

  • 검색해야할 인덱스의 범위가 정해졌을 때 사용하는 방식
    • 범위의 시작점을 찾기위해선 루트 → 브랜치 → 리프를 전부 들어가야함
  • 가장 대표적인 접근 방식
  • 인덱스 풀 스캔, 루스 인덱스 스캔에 비해 빠른 방법
  • 해당 인덱스를 구성하는 칼럼의 정순 혹은 역순으로 정렬된 상태로 레코드를 반환
  • 조건에 일치하는 리프 노드 1건당 1번의 랜덤 I/O가 일어남
    • 20~25%의 레코드 읽기 손익분기점을 생각해야함
  • 과정
    1. 인덱스 탐색 : 조건에 맞는 인덱스를 찾음
    2. 인덱스 스캔 : 탐색된 위치로 부터 인덱스를 차례대로 읽음
    3. 레코드 읽기 : 인덱스 스캔으로 부터 얻은 레코드 주소를 통해 레코드가 저장된 페이지를 가져오고 읽음

      • 3.은 쿼리가 필요로 하는 데이터에 따라 필요없을 수 있음 (커버링 인덱스)

        (findPKTop10이런건가?)

1.3.4.2 인덱스 풀 스캔

  • 인덱스 전부를 읽는 방식
  • 쿼리의 조건절에서 사용된 컬럼이 첫번째 칼럼이 아닌 경우 사용
  • 인덱스 레인지 스캔보다는 느리지만 테이블 풀 스캔보다는 빠름
  • 인덱스에 포함된 칼럼만으로 쿼리를 처리할 수 있는 경우 테이블 레코드를 읽을 필요가 없음
    • 인덱스의 전체 크기가 테이블 자체보다는 작기 때문
  • 인덱스를 전부 읽은 시점에서 인덱스를 효율적으로 사용했다고 할 수는 없다

1.3.4.3 루스 인덱스 스캔

  • 오라클과 같은 DBMS의 인덱스 스킵 스캔과 비슷하게 작동
  • 중간 중간 필요치 않은 인덱스 키 값은 무시하고 넘어감
  • Group BY 나 집합 함수 중 MAX() 나 MIN()의 최적화하는 경우에 사용

1.3.4.4 인덱스 스킵 스캔

  • 인덱스는 정렬의 기준이 되는 구성하는 칼럼의 순서가 중요

  • 선행되는 칼럼을 사용하지 않는 쿼리의 경우 비효율적으로 작용됨

    • MySQL 1.0부터 인덱스 스킵 스캔 최적화
    • 루스 인덱스 스캔의 경우 GROUP BY에서만 적용가능

  • 선행되는 칼럼의 유니크한 값을 모두 조회하고 유니크 한 값마다 칼럼 조건을 추가하여 다시 실행

    • ex)

      ALTER TABLE example ADD INDEX ix_a_b (a, b);

      SELECT a,b FROM example WHERE b >= 10;

      → 이 경우 인덱스를 사용 못함

      → 테이블 풀 스캔이 실행됨

      SELECT a,b FROM example WHERE a = 10 and b >= 10 → 이 경우 인덱스를 사용하게 됨

      이때 스킵 스캔의 설정을 키고 첫번째 경우를 다시 실행할 경우

      a의 유니크 값이 10과 20 두가지라고 했을때

      SELECT a,b FROM example WHERE a = 10 b >= 10;

      SELECT a,b FROM example WHERE a = 20 b >= 10;

      두개의 쿼리를 실행하는 것과 비슷한 형태 루스 인덱스 스캔과 동일한 방식으로 실행되게 된다.

  • 단점

    • WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값이 적어야함

      → 유니크한 값당 쿼리를 다시 실행하므로

    • 쿼리가 인덱스에 존재하는 칼럼으로 처리 가능해야 함 (커버링 인덱스)

      → 인덱스 칼럼이 아닐 경우 풀 테이블 스캔으로 넘어감

1.3.5 다중 칼럼 인덱스

  • 두개 이상의 칼럼을 포함하는 인덱스
  • 다중 칼럼 인덱스 혹은 복합 칼럼 인덱스, Concatenated Index 라고도 함
  • 선행 칼럼을 우선으로 정렬, 즉 후행의 경우 선행이 같은 경우에만 정렬이 됨

1.3.6 B-Tree 인덱스 정렬 및 스캔

  • 스캔의 방향은 항상 정렬 방향과 같지는 않음
  • 스캔 방향은 쿼리에 따라 옵티마이저가 실시간으로 실행 계획을 짦

1.3.6.1 인덱스의 정렬

1.3.6.1.1 인덱스 스캔 방향
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

- ex) 정순에서 끝값을 찾을때 옵티마이저는 역순으로 조회하여 제일 처음의 값을 가져오도록 최적화 ##### 1.3.6.2 내림차순 인덱스
- 내림차순 인덱스 스캔이 오름차순 인덱스 스캔보다 느림
    
    - 페이지 잠금이 인덱스 정순 스캔에 적합
        
        - InnoDB의 경우 데드락을 방지하기 위해 정순 방향으로 페이지를 잠금
            
            → 정순의 경우 간단하지만 역순의 경우 복잡해짐 (트랜잭션을 새로 열어서 현 상황을 저장하고 작업을 재개)
            
    - 페이지 내 인덱스 레코드가 단반향 구조
        
    
    참고 : [https://tech.kakao.com/2011/06/19/mysql-ascending-index-vs-descending-index/](https://tech.kakao.com/2018/06/19/mysql-ascending-index-vs-descending-index/)
    
- 쿼리가 인덱스의 한반향에 몰려있어 특정 페이지의 병목 현상이 일어날 수 있을때 내림차순이 더 효과적

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

WHERE , GROUP BY , ORDER BY 절의 경우 상황에 따라 인덱스를 사용할 수 도 있고 아닐 수 있음

1.3.7.1 비교 조건의 종류와 효율성

  • 다중 칼럼 인덱스에서 각 칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교(=)와 부등호 (< >) 에 따라 달라짐

  • ex)

    SELECT * FROM example WHERE a = 1 AND b ≥ 1;

    CASE 1 : INDEX (a, b)

    CASE 2 : INDEX (b, a)

    1번의 경우 a = 1 and b >= 1 인 레코드를 찾고 그 a = 1 이 아닐때 까지 읽어옴

    → 우선적으로 a = 1 인 것들끼리 모여있으므로

    2번의 경우 a = 1 and b >= 1 인 레코드를 찾고 그 중에서 a = 1 인지 비교해야함

    → 우선적으로 b ≥ 1 인 것들끼리 모여있으므로

  • 필터링 : 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업

  • 해당 쿼리에 대하여 작업 범위를 정해주지 못하고 다시 필터링이 필요한 경우 효율적이지 못하게 됨

    • 필터링 조건 (체크 조건): 비교 작업의 범위를 줄이지 못하고 필터링이 필요하게 되는 조건
    • 작업 범위 결정 조건 (공식 명칭 x) : 비교 작업의 범위를 줄여주는 조건

  • 칼럼의 순서가 중요하다

1.3.7.2 인덱스의 가용성

  • 다중 칼럼 인덱스에서 선행 칼럼 조건없이 후행 칼럼 조건만으로는 효율적인 인덱스 사용이 불가능

1.3.7.3 가용성과 효율성 판단

  • 작업 범위 결정 조건으로 사용할 수 없는 경우
    • NOT_EQUAL로 비교 (<> NOT IN NOT BETWEEN IS NOT NULL)
      • 참고) 일반적인 DBMS에서는 인덱스에 null이 못들어가지만 MySQL에서는 들어감
    • LIKE ‘%??’ 앞부분이 아닌 뒷부분 일치 형태의 문자열 패턴
    • 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교
    • NOT-DETERMINISTIC 속성의 스토어 함수가 비교 조건에 사용된 경우
    • 데이터 타입이 서로 다른 비교
    • 문자열 데이터 타입의 콜레이션이 다른 경우

1.4 R-Tree 인덱스

1.4.1 구조 및 특성

  • MBR : 최소 경계 상자 (Minimum Bounding Rectangle) → 도형을 감싸고 있는 최소크기의 상자
  • 이때 각 상자들을 감쌀수 있는 상자들이 생기는데 이때 감싸고있는 정도에 따라 레벨이 나뉨
    • 최상위 레벨 → 루트 노드
    • 차상위 레벨 → 브랜치 노드
    • 최하위 레벨 → 리프 노드
  • 이때 R-Tree 란 이 사각형들의 포함 관계를 B-Tree 형태로 구현한 것을 의미함

1.4.2 R-Tree 인덱스의 용도

  • GPS (위도, 경도), CAD/CAM 소프트웨어 등

1.5 전문 검색 인덱스 (이거 재미있어 보여요)

  • B-Tree의 경우 좌측 일부 일치 혹은 전체 일치의 경우에만 검색이 가능
  • 문서 내용 전체의 특정 키워드 검색에 특화된 알고리즘

1.5.1 인덱스 알고리즘

  • 본문 내용에서 사용자가 검색할 키워드를 분석하고 이를 인덱스로 구축
  • 키워드를 분석하는 기법에 따라 분석 알고리즘이 구분됨

1.5.1.1 어근 분석 알고리즘

MySQL 서버 전문 검색 인덱스 작업

  • 불용어 처리
    • 가치 없는 단어를 필터링 후 제거
    • 불용어 소스코드가 정의 되어있으며 사용자가 설정 할 수도 있다
  • 어근 분석
    • 선정된 단어의 원형을 찾는 작업 → MySQL은 MeCab을 사용
    • 각 용어에 맞게 사용하려면 많은 시간과 노력이 필요

1.5.1.2 n-gram 알고리즘

  • 어근 분석의 단점을 보안하기 위해 도입
  • n 글자씩 잘라서 인덱싱 하는 방법 (2-gram이 가장 대표적) (왜 중복은 이야기 안하시죠?), (인덱스 생성되는 과정도 말씀해주셔야죠) (술만 마시지 말구여)
  • 인덱싱 이후 불용어처리를 하여 인덱싱을 함

1.5.1.3 불용어 변경 및 삭제

기본적으로 주어지는 불용어 소스코드의 경우 오히려 사용자를 더 혼란스럽게 할 수 있기 때문에 불용어를 직접 등록하는 것을 권장

→ 방법은 책 참고 (마법의 단어)

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

전문 검색 인덱스를 사용하기 위해선 다음 조건을 만족해야함

  • 쿼리 문장이 전문 검색을 위한 문법 (MATCH ~ AGAINST ~)
  • 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유

1.6 함수 기반 인덱스

칼럼 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야할때 사용

실제 구조는 B-Tree와 동일

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

  • 가상 컬럼을 추가하여 그 컬럼을 인덱스로 설정하여 인덱스를 생성
  • 실제 테이블 구조가 변경된다는 단점

1.6.2 함수를 이용한 인덱스 (예시좀 넣어줘요) (사진을 못찾았어 그걸못봤어) 아아…..ㅇㅈ 그러니까 이걸 예시를 넣었어야지 미안해 알러뷰

  • MySQL 1.0부터 테이블의 구조를 변경하지 않고 직접 함수를 사용한 인덱스를 생성 가능
  • 사용시 조건절에 인덱스에 사용한 함수를 그대로 사용해야함
    • 옵티마이저가 다른 표현식으로 간주하여 함수 기반 인덱스를 사용하지 못할 수도있음

1.7 멀티 밸류 인덱스 (Hongo 아닌가요?)

  • 전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건 당 1개의 인덱스키를 가짐
  • 멀티 벨류 인덱스 : 하나의 레코드에 여러개의 인덱스키를 가진 인덱스
    • 정규화에 위배되는 형태
    • JSON의 배열 타입 필드에 대한 인덱스
  • 다음 함수를 사용해야 옵티마이저가 실행계획을 수립
    • MEMBER OF()
    • JSON_CONTAINS()
    • JSON_OVERLAPS()

1.8 클러스터링 인덱스

  • 프라이머리 키를 기준으로 비슷한 값 끼리 묶어서 저장
  • InnoDB 스토리지 엔진에서만 지원

1.8.1 클러스터링 인덱스

  • 테이블의 프라이머리 키에 대해서만 적용
  • 프라이머리 키가 변경될 경우 그 레코드의 물리적 위치도 변경
    • InnoDB에서는 PK가 ROWID(레코드가 저장된 물리적 주소)역할을 하기 때문
    • 클러스터링 인덱스는 PK에 의해 저장 위치가 결정
    • 클러스터링 인덱스의 리프노드에는 레코드이 모든 칼럼이 저장됨
  • PK가 없는 InnoDB의 경우
    • 클러스터링 키 선택의 우선순위
      1. PK가 있을 경우 PK를 우선
      2. NOT NULL 옵션의 유니크 인덱스 중 첫번쨰 인덱스를 선택
      3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한후 선택
    • 3.의 경우 사용자에게 PK가 노출 되지 않으며 아무의미 없는 값으로 클러스터링 되므로 어떠한 해택도 없음 → PK를 명시하는 것이 좋음

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

  • 모든 세컨더리 인덱스에는 PK 값이 저장되어 있음

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

장점- PK 검색시 그속도가 매우 빠름
  • 인덱스로만 처리된 수 있는 경우가 많음 —— 단점- 클러스터링 키 값이 커질경우 전체적으로 크기가 커짐
  • 세컨더리 인덱스를 통한 검색시 처리 성능이 느림
  • INSERT할 때 처리 성능이 느림
  • PK 변경시 레코드를 DELETE하고 INSERT하는 작업이 필요

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

1.8.4.1 클러스터링 인덱스 키의 크기

  • 모든 세컨더리 인덱스가 PK를 값을 포함함
  • 클러스터링 인덱스 키의 크기가 커질 경우 급격하게 전체 인덱스의 크기가 커짐

1.8.4.2 프라이머리 키는 AUTO-INCREMENT 보다는 업무적인 칼럼으로 생성 ( 가능한 경우)

  • MyISAM과는 다르게 InnoDB에서는 무엇을 PK로 선택하느냐에 따라서 그 성능차이가 많이남
  • 칼럼의 크기가 크더라도 해당 레코드를 대표할 수 잇는 칼럼을 PK로 설정해야함

1.8.4.3 프라이머리 키는 반드시 명시할 것

  • PK가 명시되지 않을 경우 의미없는 AI 칼럼을 생성하고, 클러스터링의 장점을 볼 수 없기때문에 명시하는 것이 좋음

1.8.4.4 AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우

  • 여러개의 칼럼으로 복합적인 PK를 만들 경우
    • 세컨더리 인덱스가 필요치 않을 경우 그대로 씀
    • 세컨더리 인덱스가 필요하고 PK의 크기가 클경우 AUTO__INCREMENT칼럼을 추하여 프라이머리 키로 설정 → 인조 식별자(Surroget Key)
      • 로그 테이블과 같은 INSERT 위주의 테이블 들은 AUTO_INCERMENT를 이용한 인조 식별자 PK가 성능 향상에 좋음

1.9 유니크 인덱스

  • MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없음
    • 유니크 인덱스에서는 NULL값 저장 가능
    • NULL은 특정 값이 아니므로 2개 이상 저장 가능
    • MyISAM에서는 NULL이 허용되

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

유니크한 인덱스와 그렇지 않은 일반 세컨더리 인덱스는 구조상 차이가 없음

1.9.1.1 인덱스 읽기

  • 읽기에서 성능차는 미미함

  • 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린것

    → 인덱스 자체의 성능때문에 느린것은 아님

1.9.1.2 인덱스 쓰기

  • 유니크 인덱스의 쓰기 작업에서는 중복값 체크 과정이 추가됨

    • 중복값 체크 시 읽기 잠금, 쓰기 작업 시 쓰기 잠금

      → 데드락이 자주 발생

    • InnoDB의 체인지 버퍼가 사용됨

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

  • 유일성이 보장되어야 하는 칼럼에서만 유니크 인덱스를 생성 그렇지 않다면 세컨더리 인덱스를 생성하는 방법을 고려해볼것

1.10 외래키

  • MySQL에서 외래키는 InnoDB에서만 생성 가능
  • 외래키 제약 설정 시 자동으로 연관된 테이블에 인덱스가 생성
  • InnoDB의 외래키 관리
    • 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생
    • 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생 시키지 않음

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

  • 자식 테이블의 외래 키 칼럼 변경은 부모 테이블의 확인이 필요
    • 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있을 경우 자식 테이블은 대기
  • 자식 테이블의 외레키가 아닌 칼럼은 잠금 확장이 일어나지 않음

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

  • 자식테이블이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE)에 의해 부모 테이블의 변경시 자식 테이블의 확인이 일어남

  • 물리적으로 외래키를 생성시 자식 테이블에 레코드가 추가되는 경우 부모 테이블을 확인해야함

    → 이때 체크작업이 일어나면서 읽기 잠금이 걸림

CS, 스터디, DB
DB
This post is licensed under CC BY 4.0 by the author.