[RealMysql 8.0] 인덱스 8.3.5 ~ 8.9.2

• 다중 칼럼 인덱스
  • 두개 이상의 칼럼으로 구성된 인덱스를 다중 컬럼 인덱스(또는 복합 컬럼 인덱스)라고 한다.
  • 데이터 레코드 건수가 적을경우 브랜치 노드가 없는 경우가 있을 수 있다.
    • 루트 노드와 리프노드는 항상 존재
  • 인덱스의 두번째 컬럼은 첫번째 컬럼에 의해 정렬돼 있다. 
  • 인덱스를 늘리면 앞전의 컬럼에 의해 정렬된다.
  • 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치가 상당히 중요하다.
    • 2번째 칼럼이 정렬 순서가 빠르더라도 첫번째 칼럼에 의해 인덱스 리프 노드의 젤 아래에 위치할 수 있다.
• B-Tree 인덱스의 정렬 및 스캔 방향
  • 인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이나, 내림차순으로 정렬되어 저장된다.
  • 어느 방향으로 읽을지는 옵티마이저가 실시간으로 만들어 내는 실행 계획에 따라 결정된다.
• 인덱스의 정렬
  • 5.7버전 이전에는 칼럼 단위로 정렬 순서를 혼합해서 인덱스를 생성할 수 없어 이를 우회하기위해 숫자 칼럼의 경우 -1 을 곱한 값을 저장하는 우회방법을 사용했지만
  • 8.0 버전부터는 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됐다.

CREATE INDEX ix_test, ON table (a_column ASC, b_column DESC);

• 기타:
  • 5.7에서도 이러한 문법이 가능했지만 실제로는 오름차순으로만 만들어 졌을 것이다. 이는 앞으로 만들어질 호환성을 위해 문법상으로만 제공된 것이다.
• 인덱스 스캔 방향
  • 옵티마이저에 의해 인덱스의 읽기 방향이 선택된다.
• 내림차순 인덱스
  • Innodb 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지 간의 양방향 연결고리를 통해 전진 하느냐 후진 하느냐 차이만 있지만
  • 실제 InnoDB에서는 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수 밖에 없다.
    • 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
    • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조
  • 생각: 소량의 레코드는 굳이 내림차순 인덱스를 고려할 필요는 없으나, 많은 데이터라면 내림차순 인덱스를 고려해보자
    • 예시처럼 천만건에 1.5초 정도 차이나면, 더 많은데이터에 가공까지하면 더 차이가 많이 날테니..
• B-Tree 인덱스의 가용성과 효율성
  • 쿼리의 where 조건, group by, order by 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 알아보자
• 비교 조건의 종류와 효율성
  • 아래와 같은 코드가 있을경우

select * from test where a = 'a' and b = 'b';
//1번 인덱스 조건: index(a, b)
//2번 인덱스 조건: index(b,a)

• 효율을 살펴보면 1번 인덱스가 더 좋다.
  • 2번의 인덱스는 우선 b = 'b' and a ='a'인 레코드를 찾고, 이후 모든 레코드에 대해 a ='a' 비교하는 과정을 거친다.
  • 다중 컬럼 인덱스의 정렬 방식은 인덱스의 n 번쨰 키 값은 n-1 키 값에 대해서 정렬되기 때문
  • 정식명칭은 아니지만 1번 인덱스의 두조건 과 같이 작업 범위를 결정하는 조건을 "작업 범위 결정"이라 하고,
  • 2번 과 같이 비교 작업의 범위를 줄이지 못하고 단순한 조건은 "필터링 조건" 또는 "체크 조건" 이라 표현한다.
    • 즉 1번은 모두 작업 범위 결정이고
    • 2번은 b='b'조건만 작업 범위 결정이고 a='a'는 체크조건 이다.
• 인덱스 가용성
  • B-Tree 인덱스의 특징은 왼쪽 값을 기준해서 오른쪽 값이 정렬돼 있다는 것이다.
  • 아래와 같은 코드가 있다.

//case 1: index(a)
//case 2: index(b, c)

//select 1번:
select * from test where a like '%a';

//select 2번:
select * from test where c >= 12345;

• select 1번의 쿼리는 레인지 스캔 방식으로 인덱스를 이용할 수 없다 a 칼럼에 저장된 값의 왼쪽부터 한 글자씩 비교해 가면서 일치하는 레코드를 찾아야 하는데 조건절에 주어진 상숫값('%a')에는 왼쪽 부분이 고정되지 않았기 때문.

select * from test where a like 'a%';

• • 위 코드는 select 1번과 다르게 레인지 스캔을 사용할 수 있다.
  • select 2번 => 선행 인덱스 b 조건 없이 c 값으로만 검색하면 인덱스를 효율적으로 사용할 수 없다.
  • 다중 컬럼으로 구성된 인덱스이므로 b 칼럼에 대해 먼저 정렬한 후, 다시 c 칼럼값으로 정렬돼 있기 때문이다.
  • group by, order by 절에도 똑같이 적용된다.(추후 다시 알아봄)
• 가용성과 효율성 판단
  • 기본적으로 B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없다.
    • 사용할 수 없다 => 작업 범위 결정 조건으로 사용할 수 없다는 것을 의미. 경우에 따라서는 체크 조건으로 인덱스를 사용할 수는 있다.
    • NOT-EQUALS 로 비교된 경우
      • <>, "not in", "not between", "is not null"
    • like '%??'(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
    • 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
      • where substring(column, 1,1,) = 'x'
      • where dayofmonth(column) =1
    • NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
    • 데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)
      • where char_column = 10
    • 문자열 데이터 타입의 콜레이션이 다른 경우
  • R-Tree 인덱스
  • 전문 검색 인덱스
  • 함수 기반 인덱스
  • 멀티밸류 인덱스
  • 클러스터링 인덱스
    • InnoDb스토리지 엔진에서만 지원하며, mysql 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키를 기준으로) 들끼리 묶어서 저장하는 형태로 구현된다
    • 클러스터링 인덱스
      • 테이블의 프라이머리 키에 대해서만 적용되는 내용이다.
      • 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다.
      • 프라이머리 키 값에 의해 레코드의 저장 위치가 결졍되며, 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다.
      • 프라이머리 키 값에 의해 레코드의 저장 위치가 결졍되므로, 레코드의 저장 방식이라고 볼 수 있다.
      • 기타:
        • 일반적으로 B-Tree 인덱스도 인덱스 키 값으로 이미 정렬되어 저장된다.이 또한 어떻게 보면 인덱스의 키 값으로 클러스터링된 것으로 생각할 수 있다.
        • 하지만 이러한 일반적인 B-Tree 인덱스를 클러스터링 인덱스라고 부르지는 않는다.
        • 테이블의 레코드가 프라이머리 키 값으로 정렬되어 저장된 경우만 "클러스터링 인덱스" 또는 "클러스터링 테이블"이라 한다.
    • 프라이머리 키가 없는 InnoDB 테이블은 다음과 같은 우선순위대로 프라이머리 키를 대체할 칼럼을 선택한다
      1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
      2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택
      3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
  • 세컨더리 인덱스에 미치는 영향

create table employees (
	emp_no INT NOT NULL,
    first_name VARCHAR(20) NOT NULL,
    PRIMARY KEY(emp_no),
    INDEX ix_firstname(first_name)
);

select * from emplyees where first_name = 'abc';

• -
  • 위와 같은 코드가 있다 치면,
    • MyISAM: ix_firstname 인덱스를 검색해서 레코드의 주소 확인 -> 레코드의 주소를 이용해 최종 레코드를 가져옴
    • InnoDB: ix_firstname 인덱스를 검색해 레코드의 프라이머리 키 값 확인 -> 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져옴
• 클러스터링 인덱스의 장점과 단점
  •  

장점	- 프라이머리 키(클러스터링 키)로 검색할 떄 처리 성능이 매우 빠름(특히, 프라이머리 키를 범위검색하는 겨웅 매우 빠름) - 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(커버링 인덱스)
단점	- 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크리가 커짐 - 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림 - INSERT 할 때 프라이머리 키에 의해 레코드의 저장 위치가 결졍되기 때문에 처리 성능이 느림 - 프라이머리 키를 반영할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요하기 때문에 처리 성능이 느림

- 빠른 읽기 느린 쓰기로 요약할 수 있다.

• 클러스터링 테이블 사용 시 주의사항
  • auto_increament 라도 사용해서 프라이머리 키를 반드시 설정해주자.
• 유니크 인덱스
  • 유니크는 사실 인덱스라기보다는 제약조건에 가깝다.말 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는데, mysql 에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.
  • 유느키 인덱스에서 null 도 저장될 수 있는데 null은 특정 값이 아니므로 2개 이상 저장될 수 있다.
    • 프라이머리 키도 따지고보면 null이 허용되지 않는 유니크 인덱스와 같지만 InnoDB 테이블의 프라이머리 키는 클러스터링 키의 역할도 하므로 유니크 인덱스와는 근본적으로 다르다.
• 유니크 인덱스와 일반 세컨더리 인덱스의 비교
  • 인덱스 읽기
    
    • 별 차이없으며, 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린것이지, 인덱스 자체의 특성 때문에 느린것은 아니다.
  • 인덱스 쓰기
    • 유니크 인덱스의 경우에는 유니크 인덱스의 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다.
    • 유니크 인덱스에서 중복된 값을 체크할 떄는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이는 빈번한 데드락을 발생시킨다.
    • InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼가 사용되어 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만 유니크 인덱스는 반드시 중복 처리를 해야 하므로 작업 자체를 버퍼링하지 못한다.
    • 이러한 이유 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 더 느리게 작동한다.
  • 유니크 인덱스 사용 시 주의사항
    • 유일성이 꼭 보장돼야 하는 칼럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스를 생성하는 방법도 한 번씩 고려해 보자.