Real MySQL 8장 정리

인덱스

이거 매우매우매우 중요!!

인덱스?

• 책 마지막의 색인으로 비유한다.
• 말하자면 DBMS의 모든 데이터를 찾아오는거는 힘드니까 key-value로 값-주소를 인덱스로 만들어 두는 것이다.
  • 이거를 정해진 순서에 맞춰서 만들어두는것
• 정리하면, 본래 데이터는 순서 상관 없이 저장 되고 인덱스는 설정한 순서에 따라 정렬된 key value 쌍을 만들어둠 으로 이를 활용해서 해당 데이터를 찾는 방식이다.
  • 이거는 보면 알겠지만 select에서는 성능이 좋지만 update, delete, insert할 때에 관련 인덱스도 업데이트해야해서 그 때에 성능이 희생된다.
    • 너무 많이 만들면 안된다는것이다.
• 인덱스 분류
  • PK : 레코드 대표값(PK) 이게 식별자이고, 동시에 not null이며 중복할수 없다.
  • 세컨더리 인덱스 : PK제외 나머지 모든 인덱스이다.
• 인덱스 저장 방식 분류
  • B-Tree : 가장 일반적으로 쓰인다. 칼럼 값 변형 없이 원래의 값을 이용한 인덱싱 알고리즘
  • Hash : 칼럼의 값으로 해시값을 계산해서 인덱싱. 매우 빠른 검색을 지원하지만 prefix나 범위 검색에서는 잘 안쓰이고 메모리 기반 DB에서 많이 사용한다.(레디스같은거?)
• 데이터 중복 여부 분류
  • 유니크 인덱스
    • 옵티마이저에게 유니크 인덱스에서 동등조건 검색시(== 등) 하나만 찾으면 된다고 알려주는 효과
  • 논유니크 인덱스

B-Tree

• 가장 일반적이고, 가장 먼저 도입됐고, 가장 범용적
• 컬럼의 원래 값을 변형하지 않고 인덱스 내에서는 항상 정렬된 상태로 유지
• 트리 구조 -> 최상위에 하나의 루트노드가 있고 하위에 자식노드가 붙어있는 형태
  • 가장 하위를 리프 노드, 그리고 고 사이 애들을 브랜치 노드라고 한다.
  • 여기서 리프 노드는 실제 데이터 레코드를 찾아가기 위한 주솟값을 가진다.
  • 
    • 이런 느낌으로 결국 리프노드를 통해 데이터파일의 해당 실제 데이터를 찾아간다.
    • 보면 인덱스 키 값은 모두 정렬되어있다.
    • 참고로 데이터파일 레코드는 insert순서대로 저장되지 않는다.
      • 삭제 / 변경 시에 빈공간에 저장될 수 있어서
    • 키 값을 통해 진짜 레코드를 찾아가는것
    • MySQL 테이블 : 세컨더리 인덴스가 물리적 주소를 가지기 때문에 바로 이를 찾아감
    • InnoDB 테이블 : 인덱스의 PK값을 이용해 프라이머리키 인덱스를 찾아간 후에 거기 리프 페이지의 레코드를 읽어온다.
      • 즉 InnoDB는 인덱스 검색에서 데이터를 읽기 위해 한번 더 PK저장 B-Tree 검색이 필요하다.
        • 근데 이게 무조건 단점은 아니고 MyISAM이랑 InnoDB 인덱스 구조 간 장단점이 있다고 한다.

B-Tree 인덱스 키 추가/삭제

레코드 저장/변경의 경우 인덱스 추가나 삭제 작업 발생
그래서 추가/삭제가 어떻게 되는지를 알면 성능 예측이 가능할 것이다.

인덱스 키 추가

• 스토리지 엔진에 따라 B-Tree 저장 시 새로운 키값이 즉시 인덱스에 저장될수도 그렇지 않을 수도 있다.
• 저장 시에 키값을 이용해 B-Tree상의 적절한 위치를 검색해야하는데, 리프노드가 꽉 차서 이를 위해 리프 노드를 분리해야 하면 상위 브랜치 노드까지 처리 범위가 넓어진다.
  • B-Tree는 그래서 상대적으로 쓰기 작업이 비용이 많이 드는 것으로 알려짐
• 대략적으로는 레코드 추가 비용을 1로 치면 해당 테이블의 인덱스 키를 추가하는 비용을 1.5정도로 예측
  • 중요한건 이 비용이 메모리/CPU가 아니라 디스크로부터 인덱스 페이지를 읽고 쓰기해야해서 걸리는 시간이라는것
    • MyISAM이나 MEMORY의 경우는 INSERT시 즉시 새로운 값을 저장
    • InnoDB스토리지 엔진은 인덱스 키 추가 작업을 지연시킨다.
      • 다만 PK나 유니크 인덱스는 중복 체크가 필요하므로 즉시 B-Tree에 추가/제거한다.

인덱스 키 삭제

• 간단함
• 걍 리프 노드 찾아서 삭제 마크한다.
  • 이렇게 하면 마킹된 인덱스 키 공간은 계속 그대로 마킹하거나 재활용 가능
    • 마킹 또한 디스크 쓰기가 필요하므로 이것도 디스크 I/O가 필요하다.
      • 참고로 5.5이상 InnoDB스토리지 엔진에서는 이것도 버퍼링되어 지연 처리 가능

인덱스 키 변경

• B-Tree키 값이 변경되면 그것만 변경하는건 불가능하고... 삭제하고 새로운 키값으로 추가한다.
• 위의 삭제, 추가를 차례로 수행하는거임

인덱스 키 검색

• 위의 생성/삭제/변경에서 추가 비용을 감당하며 index를 만드는 이유가 검색이다.
• 트리 탐색(루트부터 시작해 최종 리프 노드까지 비교 작업 수행)
  • 참고로 이거는 Select뿐 아니라 UPDATE/DELETE같은거 할 때에도 찾아갈 때 수행한다.
• 100%일치 / prefix 일치 하는 경우 사용가능
  • 부등호 비교에서도 활용 가능하다.
  • 근데 키 값의 뒷부분 검색에서는 사용 불가함
• 중요한건 키 값에 변형이 가해진 후 비교되는 경우는 B-Tree의 빠른 검색을 사용할 수 없다는것
  • 이미 변형된 값은 B-Tree에 존재하는 값이 아니니까
    • 그래서 함수나 연산을 수행한 결과로 정렬한다거나 검색하는건 B-Tree 장점 이용 불가
• 이거 레코드 잠금이나 넥스트 키 락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있는데, UPDATE/DELETE시에 적절히 사용할 수 이쓴 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 요거랑 관련된 내용

사용에 영향을 미치는 요소

인덱스 구성 칼럼 크기, 레코드 건수, 유니크 인덱스 키 값 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.

• 키 값 크기
  • 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 혹은 블록이라하며 이게 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다.
  • 인덱스도 이 페이지 단위로 관리된다.
  • B-Tree는 자식 노드의 개수가 가변적인 구조이다.
    • 이 자식 노드의 개수가 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.
    • 자식 노드 주소라는게 대략 종류별로 6바이트 ~ 12바이트의 값을 가진다고 한다. 페이지 크기를 기본 단위 16kb로 가정한다면 대충 최대 12바이트로 생각해서 16*1024/(16+12)=585개 저장하게 될 것이다.
    • 그래서 만약에 select가 1000개 데이터를 읽어와야하면 이 상태에서는 2번 이상 디스크를 확인하게 된다는 -> 그래서 디스크로부터 읽어오는 횟수가 증가해 속도가 느려진다는 것이다.
  • 인덱스 키 값의 길이가 길어진다는건 전체적인 인덱스 크기가 커진다는 것을 의미한다.
    • 근데 인덱스 캐싱하는 InnoDB버퍼 풀이나 MyISAM키 캐시 영역은 크기가 제한적이니까 캐시 가능 레코드가 줄어들고 자연히 메모리 효율이 줄어들게 된다는 것이다.
• B-Tree 깊이
  • 이거는 중요한데 직접 제어할 수 없다.
  • 만약에 위의 키값 크기에서 고려한 것에서 인덱스 깊이가 3이면 538538538 약 2억개 정도 키값을 담을 수 있을 것이다.
  • 이 B-Tree 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제
    • 인덱스 키 값의 크기가 커지면 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값 개수 적어짐
  • 참고로 이거는 키 값의 크기는 가능하면 작게 만드는 것이 좋다는것
  • 아무리 대용량 DB여도 B-Tree의 깊이가 5단계 이상까지 깊어지는 경우는 흔치 않음
• 선택도(기수성)
  • 모든 인덱스키 값 가운데 유니크한 값의 수를 의미한다.
  • 그니까 키 값은 100개인데 유니크한 값의 수가 10개면 기수성은 10이다.
  • 중복된 값이 많으먼 -> 기수성은 낮아지고 -> 선택도가 떨어진다.
  • 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리됨
  • 참고로 선택도가 좋지 않아도 정렬이나 그루핑같은 작업을 할 때 인덱스를 만드는 것이 나을때도 있다.
• 읽어야 하는 레코드의 건수
  • 위의 내용에서 알 수 있듯, 인덱스를 타서 읽는거는 바로 읽는것보다 비용이 크다(찾아가는 작업)
  • 예를 들어 100만 건 저장된 데이터에서 50만 건을 읽는 쿼리가 있다면 그냥 싹다 읽어서 그중 필요없는 50만건을 버리는게 나을지, 아니면 인덱스로 50만건을 읽어오는게 나을지 판단해야 한다.
    • 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는게 직접 1건을 읽는것보다 4~5배 비용이 더 많이 드는 작업으로 예측한다.
      • 그래서 인덱스를 통해 읽어오는 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어가면 그냥 모두 직접 읽어서 필요한걸 가려내는게 더 효율적이다.

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

어떤 경우에 인덱스를 사용하도록 유도할지, 혹은 못하게 할지를 판단하려면 MySQL의 스토리지 엔진이 어떻게 인덱스를 이용해서 실제 레코드를 읽어내는지 알아야한다.

• 인덱스 레인지 스캔
  • 인덱스 접근 방법 중 가장 대표적인 접근 방식
  • 빠른 방법이다.
  • 루트 -> 브랜치 -> 리프로 가서 이제 그 인덱스를 통해 순서대로 쭈루루룩 읽는다.
    • 그리고 여기서 마지막으로 가면 다음 리프노드를 찾아서 다시 스캔 시작
  • 한가지 중요한것은 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다는 것
    • 그니까 한 건 마다 랜덤 I/O가 한번씩 일어난다는것
      • 이것 때문에 레코드가 20~25%가 넘으면 인덱스가 비효율적이라는거임
• 인덱스 풀 스캔
  • 인덱스를 쓰기는 하는데, 인덱스의 처음부터 끝까지 모두 읽는 방식
  • 대표적으로는 쿼리 조건절에 사용된 컬럼이 인덱스의 첫번째가 아닌 경우
    • A, B, C순서 인덱스에서 B나 C로 검색하는 경우!
      • 인덱스 크기가 테이블 크기보다 작으므로 테이블을 직접 처음부터 끝까지 읽는것보다 이게 효율적이다.
  • 쿼리가 인덱스에 명시된 컬럼만으로 조건 처리할 수 있는 경우 사용
    • 그래서 인덱스 뿐 아니라 데이터 레코드까지 읽어야하면 이 방식으로 처리되지 않는다.
• 루스 인덱스 스캔
  • 오라클의 인덱스 스킵 스캔이랑 비슷하다 함
  • 말 그대로 루즈하게~ 듬성듬성하게~ 읽는거를 의미함
  • 인덱스 레인지 스캔이랑 비슷하기는 한데, 중간에 필요치 않은 값은 무시하고 다음으로 넘어가는거
    • 대표적으로 GROUP BY나 MAX(), MIN() 같은 함수의 최적화!!
  • 이거 사용하려면 여러 조건을 만족해야 한다고 하는데, 이거는 나중에
• 인덱스 스킵 스캔
  • 인덱스의 핵심은 값이 정렬되어 있다는것
    • 그래서 인덱스 구성 컬럼 순서가 중요함
  • 이게 MySQL8.0 버전부터는 인덱스에 해당하는것을 스킵하고도 뒤에걸 통해 사용할 수 있도록 적용됨
    • A, B순서 인덱스에서 B를 통해 검색하는 경우 인덱스 스킵 스캔!
  • 근데 이 인덱스 스킵 스캔은 8.0버전에서 도입된거라 단점이 있다고 한다.
    • Where조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 개수가 적어야 한다.
      • 만약 유니크한 값의 개수가 매우 많다면 옵티마이저가 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요해지기 때문
    • 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 한다.(커버링 인덱스)
      • *처럼 다 가져오는경우(인덱스에 없는 걸 가져오는 경우) 문제가 있다고 한다.
        • 근데 이거는 MySQL 서버 옵티마이저가 개선되면 해결 가능하다고 한다.

다중 컬럼 인덱스

• 실제 서비스에서는 2개 이상 컬럼 포함 인덱스가 많이 쓰임
• 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다.
  • 그래서 이거 인덱스 내 컬럼의 순서가 매우 중요하다.

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

• 인덱스의 정렬
  • 인덱스 생성 시점에 오름차순/내림차순 설정 가능
  • MySQL 5.7까지는 컬럼단위로 인덱스 생성 불가능했다.
    • 그래서 숫자 컬럼에는 -1을 곱하는 방식으로 우회
      • 8.0부터는 정렬 순서 혼합도 가능!
• 인덱스 스캔 방향
  • 인덱스는 읽어올 때 거꾸로 읽을수도 있다(오름차순/내림차순 읽기 가능)
• 내림차순 인덱스
  • 위에서 MySQL서버에서 쿼리가 실제 내림차순/오름차순 상관업싱 인덱스 읽는 순서만 바꿔서 해결할 수 있다고 했었다.
    • 근데 과연 동일한 성능이 맞을지?
  • 만약 1112개의 데이터를 가지고 있을 때에
  • SELECT * FROM t1 ORDER BY tid ASC LIMIT 1111, 1;
  • SELECT * FROM t1 ORDER BY tid DESC LIMIT 1111, 1;
  • 요렇게 있을 때에 ASC의 경우는 큰거 하나만 반환하면 된다.
  • 근데 DESC의 경우는 limit offset에서 다 확인해서(아래서부터 올라가면서) 확인 후에 반환해야 한다.
    • DESC여서 옵티마이저가 거꾸로 시작했는데 맨 위 값 하나를 가져오는데 풀스캔이 생긴것
  • 이게 정순보다 느릴 수밖에 없는 이유가 2개 있는데
    • 페이지 잠금이 인덱스 정순 스캔에 적합
    • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

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

• 비교 조건의 종류와 효율성
  • 다중 컬럼 인덱스에서 컬럼 순서와 조건이 ==, >, < 이런거인지 등에 따라 인덱스 컬럼의 활용 형태와 효율이 달라진다.
  • 예를 들어 A, B 인덱스가 있는데 검색을 B, A 순서로 하는 경우
    • 정렬이 B는 A에 의존해서 생기는데 B로 먼저 검색하고 A를 가져오면 실제로 B는 뭔가 비교 작업 범위를 줄이지 못하고 필터링만 되기 때문
      • 이를 필터링 조건 / 체크 조건이라 하고, 처리 성능을 높이지는 못한다(오히려 느리게 만들 때가 많음)
• 인덱스의 가용성
  • B-Tree는 왼쪽 값에 기준해서 오른쪽 값이 정렬된다.
  • 이거는 다중 컬럼 인덱스의 컬럼에 대해서도 적용된다.
  • 예를 들어 LIKE에서 %mer 같은 식으로 검색할 때
    • 글의 왼쪽부터 해서 정렬할텐데, 왼쪽에 해당하는 부분이 %처리되면 당연히 인덱스의 의미가 없어지는 것이다.
  • 혹은 A, B를 통해 인덱스가 있는데 B로만 검색한다면
    • 이것도 A기준으로 되고 하는데 B로 검색하면 의미 없음...
• 가용성과 효율성 판단
  • NOT EQUAL로 비교된 경우
    • <>, NOT IN, NOT BETWEEN, IS NOT NULL
  • LIKE '%??'
  • 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
  • NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
  • 데이터 타입이 서로 다른 비교(인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우)
  • 문자열 데이터 타입의 콜레이션이 다른 경우

R-Tree 인덱스

• 공간 인덱스 : R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스
• 기본 내부 메커니즘은 B-Tree랑 흡사한데 얘는 2차원 공간 개념 값이다.
• GPS같은거에서 MySQL의 공간 확장을 이용하면 위치 기반 서비스 구현이 간단히 가능하다 한다.
  • 공간 데이터를 저장할 수 있는 데이터 타입
  • 공간 데이터의 검색을 위한 공간 인덱스(R-Tree알고리즘)
  • 공간 데이터의 연산 함수(거리 또는 포함 관계의 처리)
• 구조 및 특성
  • MySQL은 공간 정보의 저장 및 검색을 위해 기하학적 도형 정보를 관리할 수 있는 데이터 타입을 제공한다.
  • 공간 정보 검색을 위한 R-Tree 알고리즘을 이해하려면 MBR(Minimum Bounding Rectangle)을 이해해야 하는데, 해당 도형을 감싸는 최소 크기 사각형을 의미한다.
    • 이 사각형들의 포함 관계를 B-Tree 형태로 구현한게 R-Tree 인덱스이다.
    • 
    • 요거를
    • 
    • 요렇게 하는거

R-Tree 인덱스의 용도

• MBR정보를 이용해 B-Tree형태로 구축해서 Rectanble R 이름을 가지고 공간 인덱스라고도 한다.
• 위도, 경도 좌표 저장에 주로 사용되고, CAD/CAM처럼 좌표 시스템에 기반을 둔 정보에 대해서는 모두 적용 가능
• 포함 관계를 비교하는 함수로 검색을 수행하는 경우에만 인덱스 사용 가능
  • 대표적으로 현재 사용자의 위치로부터 반경 5KM이내의 음식점 검색 등

전문 검색 인덱스

• 이전까지의 인덱스는 일반적으로 크지 않은, 혹은 이미 키워드화한 작은 값에 대한 인덱싱 알고리즘이었다.
• 문서 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에는 InnoDB나 MyISAM 스토리지 엔진에서 제공하는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다.
• 이럴 때 문서 전체에 대한 분석/검색을 위한 인덱싱 알고리즘을 전문검색(Full Text search) 인덱스라고 한다.
• 여기서는 InnoDB 스토리지 엔진 중심으로 소개

인덱스 알고리즘

• 전문 검색에서는 문서 본문 내용에서 사용자가 검색하게 될 키워드를 분석해 내고, 빠른 검색용으로 사용할 수 있게 인덱스를 구축한다.
• 여러 방법이 있는데, 인덱싱 기법에 따라 단어의 어근 분석이랑 n-gram 분석 알고리즘으로 구분할 수 있다 함
  • 어근 분석 알고리즘
    • MySQL에서 전문 검색 인덱스는 두 가지 중요한 과정을 거쳐 색인 작업을 수행한다.
      • 불용어 처리
        • 별 가치 없는 단어를 필터링해서 제거하는 작업
        • 불용어 갯수는 많지 않아서 알고리즘 구현 코드에 모두 상수로 정의하는 경우가 많다. 혹은 DB화해서 사용
      • 어근 분석
        • 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업
        • MySQL서버에서는 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원한다.
        • 한글은 단어 변형 자체는 거의 없어서 어근 분석보다는 형태소 분석해서 명사 조사 구분 기능이 더 중요하다 함 -> 언어별로 방식이 다르고 한글은 MeCab을 이용하는편(원래 일본어 기준인데 한글도 이걸 사용)
          • 이거 쓰려면 단어 사전이랑 문장 해체해서 각 단어 품사를 식별할 수 있는 구조 인식이 필요하다.
  • n-gram 알고리즘
    • 위의 MeCab에서 형태소 분석은 매우 전문적인 전문검색(ㅋㅋ)알고리즘이라서 많은 노력과 시간이 필요
    • 이를 보완하기 위해 n-gram이 도입됨.
    • 형태소 분석이 문장을 이해하는 알고리즘이라면 n-gram은 키워드를 검색해내기 위한 인덱싱 알고리즘 이라고 할 수 있다.
    • 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법
    • 알고리즘이 단순하고 언어별 이해가 필요없긴한데, 만들어진 인덱스의 크기는 큰편

전문 검색 인덱스의 가용성

• 전문 검색 인덱스를 사용하려면 반드시 두 가지 조건을 갖춰야 한다.
  • 쿼리 문장이 전문 검색을 위한 문법(MATCH ... AGAINST ...)을 사용
  • 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유

함수 기반 인덱스

• 일반적으로 인덱스는 컬럼의 값 일부(앞부분) 혹은 전체에 대해서만 생성이 허용된다.
• 근데 컬럼 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데, 이러한 경우 함수 기반의 인덱스를 활용
• 방법은 두가지
  • 가상 컬럼 이용 인덱스
  • 함수 이용 인덱스
• 참고로 이거 인덱싱할 값을 계산하는 과정의 차이만 있을 뿐이고 실제 내부 구조나 유지관리는 B-Tree랑 동일하다고 한다.

가상 컬럼 이용 인덱스

• 예를 들어 A, B 컬럼을 합쳐서 검색하는 경우 CONCAT(A, ' 중간끼욧 ', B) 같은
  • 이거를 가상 컬럼에 추가하고 그 가상 컬럼으로 인덱스 생성 가능
• VIRTUAL STORED 어떤 옵션으로 생성됐든 관계없이 해당 가상 컬럼에 인덱스 생성 가능
  • 이거 차이는 나중에...
• 가상 컬럼은 새로운 컬럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블 구조가 변경된다는 단점이 있다.

함수를 사용한 인덱스

• MySQL8.0부터 테이블 구조를 변경하지 않고 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.
  • 예를 들어 위처럼 CONCAT함수를 쓴다로 가정하면 인덱스 만들 때에도
    • INDEX ix_fullname ((CONCAT(A, ' 중간끼욧 ', B)))
    • 이렇게 똑같이 해야한다. (똑같이 안하면 완전 다르게 간주)
    • 참고로 저거 가운데 공백문자때문에 동일하게 인식을 안할 수 있는데 시스템 변수의 값을 동일 콜레이션으로 일치시키고 해보면 된당

멀티 밸류 인덱스

• 전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다.
  • 즉 인덱스키 : 레코드 데이터 -> 1:1 관계
• 멀티밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 인덱스이다.
  • 일반적인 RDBMS기준으로 이런 인덱스는 정규화에 위배되는 형태이다.
• JSON배열 타입의 필드에 저장된 원소들에 대한 인덱스 요건 발생으로 인함...
• JSON검색할 때에 쓰이는거다
• 아래 함수들을 이용해야한 옵티마이저가 인덱스 활용 실행계획을 수립한다.
  • MEMBER OF()
  • JSON_CONTAINS()
  • JSON_OVERLAPS()

클러스터링 인덱스

• 클러스터링 : 여러 개를 하나로 묶는다.
• InnoDB 스토리지 엔진에서만 지원한다.
• 테이블의 PK에 대해서만 적용되는 내용이다.
  • 즉 PK값이 비슷한 레코드끼리 묶어서 저장하는것
  • 여기서 중요한건 PK값에 의해 레코드 저장 위치가 결정된다는 것이다.
  • 그리고 PK값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는것
    • 그래서 인덱스 알고리즘보다는 테이블 레코드의 저장 방식이라 볼 수 있다??
    • 클러스터링 인덱스랑 클러스터링 테이블은 동의어로 사용되기도 함
• PK기반 검색이 매우 빠르며 레코드 저장이나 PK변경이 상대적으로 느리다.
• 만약에 PK가 없는 InnoDB테이블은 어떻게 클러스터링 테이블로 구성될까?
  • 1) PK가 있으면 기본적으로 이거를 클러스터링 키로 선택
  • 2) NOT NULL옵션의 유니크 인덱스 중 첫번째를 클러스터링 키로 선택
  • 3) 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후 클러스터링 키로 선택
• 그러니까 적절한게 없으면 내부적으로 생성하는거고 쓰지도 못한다.
  • 한마디로 그냥 PK를 명시적으로 생성하자

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

• MyISAM이나 MEMORY같은거는 처음 INSERT한 공간에서 이동하지 않고, 이게 레코드 아이디 역할을 해서 PK나 세컨더리 인덱스가 이를 통해 실제 데이터를 찾아온다고 했다.
  • 그래서 PK나 세컨더리 인덱스 간 차이 X
• InnoDB에서는 세컨더리 인덱스가 실제 주소를 갖고 있다면 위에서처럼 PK가 변경되는 경우 그 위치도 변경되니 모든 인덱스에서 이걸 바꿔줘야 할 것이다.
  • 그래서 세컨더리 인덱스가 PK를 저장하는거임ㅇㅇ

클러스터링 인덱스의 장/단점

• 위에서 보면 굳이 한번 더찾는 수고가 드는 것 같음. 근데 더 큰 장점을 준다고 한다.
• 장점
  • PK로 검색할 때 성능이 매우 빠르다(특히 PK범위 검색하는경우)
  • 테이블의 모든 세컨드리 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많다(커버링 인덱스하고 한다.)
• 단점
  • 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 이 키 값이 클경우 전체적으로 인덱스의 크기가 커짐
  • 세컨더리 인덱스를 통해 검색할 때 PK로 다시 검색해야 함 -> 처리 성능 이슈
  • INSERT할 때에도 PK에 의해 위치 경정 -> 처리 성능 이슈
  • PK변경할때도 당연히 DELETE이후 INSERT -> 처리 성능 이슈
• 비교해보면 [장점 = 빠른 읽기], [단점 = 느린 쓰기(INSERT, UPDATE, DELETE)]
• 보통은 웹서비스에서는 읽기가 훨씬 많죠ㅇㅇ

주의사항

• 클러스터링 인덱스 키의 크기
  • 모든 세컨더리 인덱스가 PK값을 포함한다.
  • 그래서 PK크기가 커지면 세컨더리 인덱스도 자동으로 커짐
    • PK는 신중하게 선택하자
• PK는 AI보다는 업무적 컬럼으로 생성(가능하면..)
  • 이걸로 검색할 때 쓸 수 있다면 위의 장점을 활용할 수 있으니까!
  • 근데 보통 PK는 자연키는 안쓰는게 좋다고 듣기는 했는데(변할 수 있으니까) 그게 애매해보인다.
• PK는 반드시 명시할것
  • 왜냐면 어차피 없으면 만드는데 InnoDB가 만든거는 뭐 활용도 못하니까 만들자
  • ROW기반 복제나 InnoDB Cluster에서는 모든 테이블이 PK를 가져야만 하는 정상적인 복제 성능을 보장하기도 하므로
• AI컬럼을 인조 식별자로 사용할 경우
  • 여러 개의 컬럼이 복합으로 PK가 만들어지는경우 길어지게 된다.
    • 근데 그래도 이걸로 세컨더리 대신 쓸 수 있다면 그대로 쓰는게 좋다.
  • 세컨더리가 필요한데 PK도 크다? 걍 AI로 만드는게 나을듯
    • 위에서 저거 자연키 대신에 인조키 쓰는겨

유니크 인덱스

• 사실 인덱스보다는 제약조건에 가깝다.
• 말 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미한다.
• MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없음
  • 근데 NULL도 저장될 수 있는데, 얘는 딱히 값이 아니라 여러개 저장 ㄱㄴ
• PK는 자동으로 NOTNULL
• 일반 세컨더리 인덱스랑 비교하면
  • 구조상 아무 차이가 없긴한데 성능적으로 보면
  • 인덱스 읽기
    • 유니크 인덱스는 한건만 읽어도 되는데 세컨더리는 더 읽어야 하므로 유니크가 성능상 좋다고 하는데
      • 이거는 디스크 읽기가 아니라 CPU에서 컬럼값 비교하는거라 성능 영향은 미미함
    • 그래서 읽어야 할 레코드 건수가 같다면 성능 차이는 거의 없다고 봐도 됨
  • 인덱스 쓰기
    • 오히려 이거는 쓸 때 유니크 인덱스는 유니크한지 알기 위해 체크하는 과정이 필요하다 -> 그래서 더 느림
    • 그리고 체크할 때는 읽기잠금, 쓸때는 쓰기잠금을 걸어서 데드락이 빈번히 발생한다.
    • InnoDB에서 인덱스 키 저장을 버퍼링하기 위해 체인지 버퍼를 쓰는데 유니크 인덱스는 중복체크를 해야해서 버퍼링 불가능 -> 많이 느림ㅇㅇ
• 주의사항
  • 꼭 필요하면 이걸 생성하는게 좋은데, 성능을 생각해서 불필요하게 유니크 인덱스를 생성하는건 지양해야함
  • 하나의 테이블에서 같은 컬럼에 유니크 인덱스랑 일반 인덱스를 중복해서 생성하는 경우가 있는데 역할이 같으므로 굳이 두개 만들필요 없음
  • 당연하지만 PK랑 유니크 인덱스 생성도 필요없음
  • 정리하자면 유일성이 꼭 보장되어야 하는 경우 말고는 굳이...? 차라리 세컨더리 인덱스ㅇㅇ

외래키

• MySQL 이거는 InnoDB에서만 생성 가능
• 외래키 제약 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성된다.
  • 외래키 제거 전까지는 이 인덱스 삭제 불가
• 외래키 관리에 2가지 중요한 특징
  • 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생
  • 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)를 발생시키지 않음