함수 기반 인덱스

• 일반적인 인덱스는 칼럼의 값 일부(칼럼의 값 앞부분) 또는 전체에 대해서만 인덱스 생성이 허용된다.
• 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때 함수 기반의 인덱스를 활용한다.
• MySQL 8.0부터 함수 기반 인덱스를 지원하기 시작했다.
• MySQL 서버에서 함수 기반 인덱스를 구현하는 방법은 다음 두 가지로 구분할 수 있다.
  • 가상 칼럼을 이용한 인덱스
  • 함수를 이용한 인덱스
• MySQL 서버의 함수 기반 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.
  • 인덱싱할 값을 계산하는 과정의 차이만 있을 뿐이다.

가상 칼럼을 이용한 인덱스

인덱스 생성

다음과 같은 사용자 정보 테이블이 있다.

CREATE TABLE user (
	user_id BIGINT,
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	PRIMARY KEY (user_id)
);

first_name과 last_name 칼럼을 합쳐서 검색해야 하는 요건이 생겼을 때,

• MySQL 이전 버전에서는 full_name이라는 칼럼을 추가하고 모든 레코드에 대해 full_name을 업데이트하는 작업을 거쳐야만 full_name 칼럼에 대해 인덱스를 생성할 수 있다.
• MySQL 8.0 버전부터는 full_name이라는 가상 칼럼을 추가하고, 그 가상 칼럼에 인덱스를 생성할 수 있다.

  ALTER TABLE user
  	ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
  	ADD INDEX ix_fullname (full_name);

  • full_name 칼럼에 대한 검색이 새로 만들어진 ix_fullname 인덱스를 이용해 실행 계획이 만들어지는 것을 확인할 수 있다.
    
  • EXPLAIN SELECT * FROM user WHERE fulll_name='Matt Lee';
• 가상 칼럼이 VIRTUAL 이나 STORED 옵션 중 어떤 옵션으로 생성됐든 관계없이 해당 가상 칼럼에 인덱스를 생성할 수 있다.
• 가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에, 실제 테이블의 구조가 변경된다는 단점이 있다.

함수를 이용한 인덱스

• 가상 칼럼은 MySQL 5.7 버전에서도 사용할 수 있었지만, 함수를 직접 인덱스 생성 구문에 사용할 수는 없었다.
• MySQL 8.0 버전부터는 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.

  CREATE TABLE user (
  	user_id BIGINT,
  	first_name VARCHAR(10),
  	last_name VARCHAR(10),
  	PRIMARY KEY (user_id),
  	INDEX ix_fullname ((CONCAT(first_name, ' ', last_name)))
  );

  • 함수를 직접 사용하는 인덱스는 테이블의 구조는 변경하지 않고, 계산된 결과값의 검색을 빠르게 만들어준다.
• 함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되어야 한다.
  • EXPLAIN SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name)='Matt Lee';
  • 만약 함수 생성시 명시된 표현식과 쿼리의 조건절에 사용된 표현식이 다르다면 MySQL 옵티마이저는 다른 표현식으로 간주해서 함수 기반 인덱스를 사용하지 못한다.
• CONCAT 함수에 사용된 공백 문자 리터럴 때문에 인덱스를 사용하지 않는 것으로 표시될 수도 있다.
  • 다음 3개의 시스템 변수의 값을 동일 콜레이션으로 일치시킨 후 테스트하면 된다.
  • collation_connection
  • collation_database
  • collation_server

가상 칼럼 vs 함수 직접 이용 인덱스

• 두 방법은 사용법과 SQL 문장의 문법에서 조금 차이가 있다.
• 하지만 두 방법은 내부적으로 동일한 구현 방법을 사용하므로, 어떤 방법을 사용하더라도 둘의 성능 차이는 발생하지 않는다.

Reference

참고 서적

📔 Real MySQL 8.0