Real MySQL 8장 정리

이론 정리/Database

Real MySQL 8장 정리

철매존 2023. 12. 30. 00:42

728x90

인덱스

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

인덱스?

책 마지막의 색인으로 비유한다.
말하자면 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가지 중요한 특징
- 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생
- 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)를 발생시키지 않음