[RealMysql 8.0] 인덱스 8.3.4 ~ 8.3.4.4

• B-Tree 인덱스를 통한 데이터 읽기
  • 각 스토리지 엔진이 어떻게 인덱스를 이용해서 실제 레코드를 읽어 내는지 알아보자.
  • 아래는 mysql이 인덱스를 이용하는 방법을 알아본다.
• 1.인덱스 레인지 스캔
  •  인덱스 접근 방법 가운데 가장 대표적인 접근 방식이다.
  • 인덱스를 통해 레코드를 한 건만 읽는 경우와, 한 건 이상을 읽는 경우를 각각 다름 이름으로 구분하지만 해당 절에서는 모두 인덱스 레인지 스캔이라 한다.(추후 10장에서 다시 언급)
  • 검색해야 할 인덱스의 범위가 결졍됐을 떄 사용하는 방식이다.검색하려는 값의 수나 결과 레코드 건수에 관계없이 레인지 스캔이라 한다.
  • B-Tree 인덱스에서 루트와 브랜치 노드를 이용해 스캔 시작 위치를 검색하고, 그 지점부터 필요한 방향(오름, 내림)으로 인덱스를 읽어 나간다.
    • 중요한 것은 어떤 방식으로 스캔하든 관계없이, 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다.(별도의 정렬 과정이 수반되는것이 아니라, 인덱스 자체의 정렬특성 때문에 자동으로 그렇게 된다)
  • 인덱스 레인지 스캔은 다음고 같이 크게 3단계를 거친다.
    1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다.이 과정을 인덱스 탐색 이라고 한다.
    2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔이라고 한다.
    3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어 온다.
    4. 1번 2번 단계의 작업이 얼마나 수행됐는지를 확인할 수 있는 상태값을 제공한다. <SHOW STATUS LIKE 'Handler_%'>
  • 쿼리가 필요로 하는 데이터에 따라 3번 과정은 필요하지 않을 수도 있는데, 이를 커버링 인덱스라고 한다.
    • 쿼리를 수행하는 데 필요한 모든 데이터를 포함하는 인덱스를 의미하므로 데이터베이스 엔진은 실제 테이블에서 해당 행을 검색하지 않고도 인덱스에서 직접 정보를 검색할 수 있습니다.
    • 커버링 인덱스: 추후 챕터에서 나옴 => 보면 좋은 블로그 https://jojoldu.tistory.com/476
• 2.인덱스 풀 스캔
  • 인덱스 레인지 스캔과 마찬가지로 인덱스를 사용한다.
  • 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.
  • 대표적으로 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식을 사용한다
    • ex)인덱스는(A, B, C) 칼럼의 순서로 만들어져 있지만 쿼리의 조건절은 B 칼럼이나 C 칼럼으로 검색하는 경우
  • 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용된다.
  • 인덱스 풀 스캔은 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한후, 인덱스의 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔하는 방식을 인덱스 풀 스캔이라 한다.
    • 이 방식은 인덱스 레인지 스캔보다 빠르지 않지만 테이블 풀 스캔보다는 효율적이다. => 인덱스에 포함된 칼럼만으로 쿼리를 처리할 수 있는 경우 테이블의 레코드를 읽을 필요가 없기 때문이다.

-- 테이블 생성
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(255),
  email VARCHAR(255)
);

-- 단일 인덱스 생성
CREATE INDEX idx_username ON users (username);

-- 복합 인덱스 생성
CREATE INDEX idx_username_email ON users (username, email);

-- 인덱스 풀 스캔 테스트 (첫번째 컬럼이 아닌 인덱스 사용)
EXPLAIN SELECT * FROM users WHERE email='test@example.com';

-- 인덱스 탐색 테스트 (첫번째 컬럼인 인덱스 사용)
EXPLAIN SELECT * FROM users WHERE username='test';

• 루스 인덱스 스캔
  • 인덱스 레인지 스캔과 비슷하게 동작하지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다
  • 일반적으로 group by 또는 집합 함수 가운데 max(), min() 함수에 대해 최적화를 하는 경우에 사용된다.
    • 인덱스가( a,b)조합으로 있을때 쿼리 => select a, min(b)...같은경우 min(b) 는 첫번째 레코드만 읽으면 되기때문에.
  • Mysql 5.7 버전까지는 기능이 제한적이었지만, 8.0 버전부터 최적화를 지원하기 시작함.
• 인덱스 스킵 스캔
  • 복합 인덱스의 경우(A, B) B 의 조건만 검색하면 인덱스를 제대로 사용하지 못하는데,
  • 8.0부터는 옵티마이저가 A 칼럼을 건너뛰어서 B 칼럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능을 도입했다.
  • 하지만 아래와 같은 단점이 있다
    • WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함
      •  스캔 성능을 향상시키기 위해서는 검색되는 레코드의 개수가 적어야 한다는 것을 의미
    • 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함(커버링 인덱스)
  • 아~인덱스 부분 재밌다~