인덱스 확장

세컨더리 인덱스의 프라이머리 키

• InnoDB 스토리지 엔진은 프라이머리 키를 클러스터링 키로 생성하므로, 모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가진다.
• 아래의 테이블을 보자.

  CREATE TABLE dept_emp (
      emp_no INT NOT NULL,
      dept_no CHAR(4) NOT NULL,
      from_date DATE NOT NULL,
      to_date DATE NOT NULL,
      PRIMARY KEY(dept_no, emp_no),
      KEY ix_fromdate(from_date)
  ) ENGINE=InnoDB;

  • 프라이머리 키: (demp_no, emp_no)
  • 세컨더리 인덱스 ix_fromdate: from_date
  • 세컨더리 인덱스는 (데이터 레코드를 찾아가기 위해) 프라이머리 키를 명시된 순서대로 포함하므로, 최종적으로 (from_date, dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 작동한다.

Use Index Extensions

• use_index_extensions 옵티마이저 옵션은 InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할 지를 결정하는 옵션이다.
• 예전 MySQL 버전에서는 세컨더리 인덱스의 마지막에 자동 추가되는 프라이머리 키를 제대로 활용하지 못했다.
• MySQL 서버가 업그레이드되며 옵티마이저는 세컨더리 인덱스의 마지막에 프라이머리 키의 칼럼이 숨어있다는 것을 인지하고 실행 계획을 수립하도록 개선됐다.

  • 위의 테이블에선 ix_fromdate 인덱스의 마지막에 (dept_no, emp_no) 칼럼이 숨어있다는 것을 인지하고 있다.

  • 실행 계획의 key_len 칼럼은 이 쿼리가 인덱스를 구성하는 칼럼 중에서 어느 부분(어느 칼럼)까지 사용했는지를 바이트 수로 보여준다.

    -- 실행 계획의 key_len 칼럼은
    -- from_date 칼럼(3바이트)과 dept_emp 칼럼(16바이트)까지 사용하여 19바이트로 표시된다.
    SELECT COUNT(*) FROM dept_emp WHERE from_date='1987-07-25' AND dept_no='d001';
    
    -- 실행 계획의 key_len 칼럼은
    -- from_date 칼럼(3바이트)를 위한 3바이트만 표시된다.
    SELECT COUNT(*) FROM dept_emp WHERE from_date='1987-07-25';

• InnoDB의 프라이머리 키가 세컨더리 인덱스에 포함되어 있으므로 정렬 작업도 인덱스를 활용해서 처리하는 장점이 있다.

  • 아래 쿼리의 실행 계획에는 Extra 칼럼에 “Using Filesort”가 표시되지 않는다.

  • 이는 MySQL 서버가 별도의 정렬 작업 없이 인덱스 순서대로 레코드를 읽기만 함으로써 ORDER BY dept_no 를 만족했다는 것을 의미한다.

    SELECT * FROM dept_emp WHERE from_date='1987-07-25' ORDER BY dept_no;

Reference

참고 서적

📔 Real MySQL 8.0

참고 사이트

MySQL 8.3 문서: index-extensions