RealMySQL 8.0 8장 인덱스

태량·2023년 2월 7일
0

RealMySQL

목록 보기
1/4
  • 08 인덱스
    1. 디스크 읽기 방식
    • 데이터베이스는 결국 디스크에 데이터가 저장되는 최종 형태이다. 따라서 하드웨어 중 가장 처리 속도가 느린 디스크가 DB의 처리 속도를 결정한다고 봐도 무방하다. 하드웨어적 성능을 논외로 소프트웨어적으로 성능을 최대한 끌어 올릴 수 있가에 대한 해답이 인덱스이다.

      1. HDD와 SSD의 차이
        • SSD는 HDD와는 다르게 플래쉬 메모리를 탑재함으로써 전기적 특성을 이용해 하드웨어적 속도가 10만 : 200 으로 월등한 차이가 있다.
      2. 랜덤 I/O 와 순차 I/O의 차이
        • SSD도 어쩔 수 없이 랜덤 I/O를 통해 디스크 쓰기를 할 수 밖에 없다. 디스크의 데이터는 어떻게든 변경되거나 삭제 되기 때문에 그 삭제된 공간을 활용하기 위해선 랜덤 I/O가 필요 할 수 밖에 없다. 하지만 랜덤은 비어있는 곳을 찾아야 하는 작업이 들어가기에 순차보다 성능이 떨어지기 마련이다.
        • 하드웨어적 RAID 캐시 메모리를 사용하여 이 문제를 보완하고자 한다.
        • DB에서 소프트웨적 보완 방법은 인덱스를 사용하고, 인덱스를 효율적으로 사용 할 수 있는 쿼리를 사용하는 방법이다. → 랜덤 I/O 횟수를 줄이는 것이 가장 큰 목적이다.
    1. 인덱스란?

      • 책의 색인에 많이 비유된다. 중요한 것은 색인에서도 [찾고자 하는 단어 - 해당 페이지]의 구조라는 것이다. 해당 페이지로 다시 찾아가야 원하는 정보를 얻을 수 있는 것이지 색인만 봐서는 원하는 데이터를 얻을 순 없다 → 인덱스도 마찬가지이다. 인덱스의 노드 구조들을 따라가서 리프 노드에 도달하면, 거기서 끝이 아니라 해당 페이지에 해당하는 프라이머리 키 값을 얻을 수 있고, 그 값으로 또 다시 디스크에서 실제 데이터가 있는 주소로 찾아 들어가야 원하는 테이블 레코드를 얻을 수 있는 것이다.
      • 자료구조적 특징은 인덱스는 순서가 정렬된 상태인 SortedList 형태라고 생각하면 되고, 디스크에 저장된 데이터 파일들은 정렬은 되지 않고, 연결만 되어진 ArrayList라고 생각하면 된다. 그러므로 전자는 추가와 삭제등 변경에는 불리하지만 검색에는 유리하며, 후자는 그 반대이다. 그러므로 인덱스는 후자의 단점을 전자에서 먼저 상쇄하여 보완하고자 함이다.
      • 인덱스는 크게 총 4가지로 분류 할 수 있다. 1) 역할 별 = 프라이머리 키 와 세컨더리 인덱스 키 2) 알고리즘 별 : B-tree 인덱스, Hash 인덱스, B+인덱스, 3) 중복 허용 여부 : 유니크 인덱스, Non 유니크 인덱스(세컨더리 인덱스) 4) 기능 별 : 전문(full) 검색용 인덱스, R-인덱스
    2. B-Tree 인덱스

      B-Tree에서 B는 Binary가 아니라 Balanced라는 것을 잊지 말자.

      1. 구조 및 특성
          - 루트 노드 → (브랜치 노드) → 리프 노드의 트리 구조를 갖는다.
          - 결국 B-Tree 인덱스도 테이블이라고 생각하면 된다.
          - 리프 노드에는 프라이머리 키가 적혀 있으며, InnoDB에 경우 이 키를 가지고 디스크를 가서(클러스터링 인덱스) 다시 주소값과 매칭해서 실제 데이터 레코드를 찾아야 한다.
      2. 키 추가 및 삭제
          1. 키 추가
              - 인덱스 페이지도 결국에는 디스크에 담겨져 있다가 메모리에 올라와 사용된다. 그래서 추가 및 변경 작업은 디스크를 방문 해야하는 작업이 들어갈 가능성이 많아진다.
              - 결국 노드들도 테이블과 같은 데이터 페이지이므로, 한정된 용량이 있다. 그래서 B-tree가 계속해서 증가하다 한계치에 다다르면, 스플릿 작업을 통해 쪼개주게 된다. 근데 이 작업이 또 비용이다
                  
                  → 지연처리를 지원함으로써 단점을 보완한다. 하지만 프라이머리 키나 유니크 키와 같이 중복을 체크 해야하는 경우엔 지연 처리가 지원되지 않는다.
                  
          2. 키 삭제
              - 디스크 작업이 필요하지만 InnoDB는 지연처리를 지원하여 크게 영향을 주지 않는다.
          3. 키 변경
              - 키 값 자체를 바로 변경하는 것이 아니라, 변경하고자 하는 키를 먼저 삭제 한 뒤, 키를 추가하는 형태이다. 위의 키 추가와 변경의 조합으로 이루어 진다.
          4. 키 검색
              - 인덱스를 잘 활용하기 위한 적절한 쿼리 사용이 요구된다. 그리고 함수나 where절에 인덱스를 변형하여 검색하는 경우에는 인덱스가 가용하지 않다.
      3. 인덱스 사용에 영향을 미치는 요소
          1. 인덱스 키 값의 크기
              - 인덱스 페이지는 결국 [인덱스 키 + 자식노드 주소]로 구성된다. 이 구성의 크기가 크면 클수록 하나의 인덱스 페이지가 포함 할 수 있는 인덱스 키 덩어리는 줄어들게 된다. → 페이지가 많다는 것은 디스크에서 이 페이지 자료를 가져오는 횟수나 양이 많다는 의미이다. ⇒ 성능 저하
              - 인덱스 페이지는 디스크에서 가져와져 결국 메모리상에 상주하며 사용되게 되는데, 인덱스 페이지가 크다면 당연히 메모리(InnoDB 버퍼 풀)의 공간도 많이 사용하게 된다.
          2. B-Tree 깊이
              - 깊이가 깊다는 의미는 인덱스 페이지가 여러개로 나뉘어져 있다는 의미이다. 그러므로 위에서 설명한 것과 같이 디스크 I/O가 더 많이 발생한다.
          3. 선택도
              - 유니크한 값의 수를 의미한다. 유니크 하다면 옵티마이저는 유니크 키에 해당하는 데이터를 찾으면 더 진행할 필요가 없으므로 빠르게 처리된다.
          4. 읽어야 하는 레코드의 건수
              - 인덱스는 필요한 조건에 맞는 데이터를 찾아서 읽어 온다는 것(작업 범위 결정 조건)이다. 그와 약간 다르게 전체를 가져오고 필터링(필터링 조건)해서 데이터를 얻는 방식이 있다.
              - 무조건 인덱스로 인한 검색이 빠르다고 할 수 없다. 이것은 경우에 따라서 어떠한 것이 더 빠른지에 대한 수치 손익 분기점을 따져 봐야 한다. 전체 테이블 레코드의 20~25% 이상의 데이터를 읽어야 할 때는 모두 직접 읽고 필터링 하는 방식이 더 효율적이다 → 옵티마이저는 이러한 상황에서 유저가 인덱스로 검색하는 쿼리를 날려도 자동으로 필터링 방식으로 적용하여 검색하게 된다.
      4. 인덱스를 통한 데이터 읽기
          1. 인덱스 레인지 스캔
              - 루트 → 브랜치 노드 → 리프 노드를 거친 뒤 (인덱스 탐색), 리프 노드에서 해당하는 인덱스 키를 범위화해서 읽는다.(인덱스 스캔)
              - 가장 대표적인 인덱스 접근 방식이다. 쿼리에서 검색 조건을 잘 설정 해야지 레인지 스캔이 적용 된다.
                  
                  → 잘못 적용하면 전체 다 스캔하는 방식으로 적용된다.
                  
              - MyISAM과 같은 인덱스 레인지 스캔에서는 레인지 스캔이 디스크에서 계속되는 랜덤 I/O를 유발한다 → InnoDB는 디스크에서도 클러스터링 인덱스로 묶어 놨기 때문에 보다 좋은 성능을 발휘 한다.
          2. 인덱스 풀 스캔
              - 리프노드에서 처음부터 끝까지 모두 읽는 방식이다.
              - 잘 사용되지 않으며, 쿼리가 잘못 작성되어 이러한 방식으로 적용 될 수 있다.
          3. 루스 인덱스 스캔
              - 이미 정렬화되고 범주화된 인덱스의 특성을 가지고, 듬성듬성하게 스캔하는 방식이다.
              - 범주화 되어 있기 때문에 GroupBy에 사용되며, 순서가 정렬되어 있으므로 최대 최소값 찾는 쿼리에 활용된다.
              - 다시 말해 주어진 쿼리에서도 효율적으로 생략할 것은 생략하고 효율적으로 뛰어 넘어서 찾는 방식이다.
          4. 인덱스 스킵 스캔
              - 설정된 인덱스가 여러개인데, 사용자가 쿼리에서 한개의 인덱스만 설정하여 쿼리를 날렸다면, 옵티마이저는 이 작업에 나머지 인덱스가 있는 새로운 쿼리를 추가하여 실행한다. → 멍청한 쿼리를 옵티마이저가 똑똑하게 수습
      5. 다중 칼럼 인덱스
          - 인덱스의 칼럼을 의미한다.
          - 첫번째에 위치한 칼럼에 의존하여 두번째 칼럼의 위치가 결정된다. 같은 범주의 첫번째 칼럼에서의 두번째 칼럼 인덱스 값들은 정렬되어 있지만, 바로 다음에 다른 범주 첫번째 칼럼에서의 두번째 칼럼 인덱스 값은 위의 값과 순서상 연관이 없이 존재한다.
      6. 인덱스의 정렬 및 스캔 방향
          1. 인덱스의 정렬 - ASEC(오름차순), DESC(내림차순)
          2. 인덱스 스캔 방향
              - 인덱스의 내림차순,오름차순은 결국 왼쪽 자식 노드와 오른쪽 자식 노드의 위치 관계로 설정한다. 오름 차순인 경우에는 왼쪽 자식 노드가 오른쪽 자식 노드보다 인덱스 값이 작다.
          3. 내림차순 인덱스
              - 인덱스 스캔의 특징은 인덱스 레코드는 단방향(오름차순)으로 이루어져 있기 때문에 기본적으로 오름차순의 검색이 내림차순 검색보다 빠르다.
      7. 인덱스의 가용성과 효율성
      - 인덱스가 아무리 있어도, 쿼리문을 개떡 같이 작성해서 날리면 인덱스의 혜택을 누리지 못한다.
          1. 비교 조건의 종류 그에 따른 효율성
              - A Index(dept_no,emp_no), B Index(emp_no,dept_no)라는 두개의 인덱스가 있다면 아래의 쿼리를 날렸을 때, 어떤 것이 더 효과적일까?
              - Select * from dept_emp where dept_no=’d002’ and emp_no ≥ 10114;
              - 다중 칼럼 인덱스에선 먼저 나오는 인덱스 키 값을 기준으로 먼저 정렬하고, 그 다음 인덱스 키 값으로 정렬한다. 그렇다면, 범주를 먼저 쉽게 나눌 수 있는 a Index가 유리하다. B에 경우는 emp_no의 범주들을 모두 검색한 뒤, dept_no이 d002인지 체크하는 필터링 방식으로 작동한다.
              - 작업 범위 결정조건<인덱스의 효용성을 높이는> vs 필터링 조건<인덱스를 적절히 사용 못함>
          2. 인덱스의 가용성
          3. 가용성과 효율성 판단
              - 쿼리마다 조건에 따라 작업 범위 결정조건으로 적용돼 효율성이 높아질 수 있고, 그에 반해 필터링 조건으로 적영돼 효율성이 떨어질 수 있다.
              - WHERE column <> ‘N’ ( <>는 ! = 와 같다) 와 같은 아닌 것을 골라내야 하는 경우엔 필터링이 적용
              - WHERE column like ‘%승환’  (%는 문자열에 사용되는 와일드 카드, 이것은 맨 뒤가 승환으로 끝나는 모든 문자열). 인덱스는 문자열 키의 경우 앞 부분을 기준으로 정렬이 된다. 근데 그 정렬과 상관없이 문자열 뒷부분으로 찾으면 의미가 없다.
              - where substring(column,1,1) = ‘x’ 와 같이 다른 연산자로 인덱스 칼럼이 변형된 후에 비교된 경우
    3. R-Tree 인덱스 ( R은 사각형을 의미하는 rectagle)

      • 2차원 데이터를 MBR(minimum boundary rectagle)로 분류하여 트리구조로 인덱싱 하는 것이다. 이것을 이용해 공간 좌표등에 이용된다. (반경 5km 이내의 음식점)
    4. 클러스터링 인덱스

      1. 클러스터링 인덱스란?
        • InnoDB가 디스크에 테이블 레코드를 프라이머리 키로 묶어서 저장하는 방식.
        • 묶는 과정이 필요하므로 저장이나 변경에서 속도가 느리지만, 디스크 랜덤 I/O를 커버함으로 검색에선 크게 빠른 성능을 갖는다.
      2. 세컨더리 인덱스에 미치는 영향
      3. 장점과 단점
      4. 주의 사항
        1. 클러스터링 인덱스 키의 크기
          • 클러스터링 인덱스는 결국 리프 노드에 value로 저장되어야 하기 때문에 세컨더리 인덱스의 크기에 영향을 준다. 디스크 용량과 메모리 용량에 영향을 준다.
        2. 프라이머리키와 AUTO-INCREMENT
          • AUTO-INCREMENT을 프라이머리 키로 사용해도 무방하지만, 특정 프라이머리 키를 사용하면, 업무상 명확한 의미를 내포함으로, 테이블 사용시 이점을 갖는다.
        3. 인조 식별자(Surrogate Key)
          • AUTO-INCREMENT가 프라이머리 키 역할을 하는 것을 말한다.
    5. 유니크 인덱스

      1. 유니크 인덱스와 일반 세컨더리 인덱스의 비교
        1. 인덱스 읽기
          • 디스크가 아닌 메모리에 탑재된 상태를 말한다. 그러므로 인덱스를 읽고 하는 과정은 CPU 과정이므로 읽기에 있어 유니크 인덱스가 큰 성능 차이를 보이진 않는다.
        2. 인덱스 쓰기
          • 유니크 인덱스를 추가하기 위해선 해당 인덱스가 중복되지 않는지 체크하는 작업이 동반되므로 성능이 느리다.
      2. 주의 사항
        • 유니크 인덱스와 일반 다른 인덱스는 같은 역할이므로 중복하지 않도록 주의한다.
    6. 외래키

    • 외래키는 부모 테이블과 자식 테이블간의 일관성을 맞춰야 하기에 잠금이 동반된다.
      • 하나의 테이블에서의 작업이 그 테이블에서만 끝나는게 아니라 다른 테이블에도 잠금 영향을 미칠 수 있음을 인지하라.
      1. 자식 테이블의 변경이 대기하는 경우
        • 자식 테이블의 외래키 변경인 경우 부모테이블에서 해당 외래키에 대해 체크 해야하므로, 부모 테이블이 잠금이 걸려 있다면 기다려야 한다.
      2. 부모 테이블의 변경 작업이 대기하는 경우
        • On delete CASCADE는 부모 테이블의 row가 삭제될 시 그에 종속되는 자식 테이블의 row도 자동 삭제하는 기능이다.
profile
좋은 영향력과 교류를 위하여

0개의 댓글