일반적인 인덱스는 칼럼의 값, 또는 값의 앞부분 일부에 대해서만 인덱스를 생성할 수 있다. 만약 칼럼의 값이 변형되어서 만들어진 값에 대해서 인덱스를 구축하고 싶다면, 이때는 함수 기반의 인덱스를 활용한다. MySQL은 8.0 버전부터 함수 기반 인덱스를 지원하기 시작했다. MySQL에서 함수 기반 인덱스는 다음의 두 가지 방법으로 만들 수 있다.

가상 칼럼을 이용한 인덱스

다음과 같이 사용자 정보를 저장하는 테이블이 있다고 해보자.

create table user (
	user_id BIGINT,
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	PRIMARY KEY (user_id)
);

이런 상태에서 first_name과 last_name을 합쳐서 검색해야 한다고 해보자. 이전에는 새로 full_name이라는 컬럼을 추가하고, 모든 행에 대해 full_name 컬럼에 대해 업데이트를 진행하고, 그러고 나서야 full_name에 인덱스를 생성할 수 있었다.

하지만 MySQL 8.0부터는 다음과 같이 가상 컬럼을 추가하고 가상 컬럼에 인덱스까지 생성할 수 있다.

alter table user
	add full_name VARCHAR(30) as (concat(first_name,' ',last_name)) virtual, --가상 컬럼 추가
	add index ix_fullname (full_name); --가상컬럼에 가상 인덱스 추가

이 가상컬럼은 이전에 김영한님의 강의에서도 다룬적이 있다.

JSON인덱스와 성능 최적화 - 1 (가상 컬럼)

가상컬럼이 VIRTUAL , STORED 중 어떤 옵션으로 생성되었든 관계없이 해당 가상 컬럼에 인덱스를 생성할 수 있다.

함수를 이용한 인덱스

가상 컬럼 자체는 MySQL 5.7버전에서도 사용할 수 있었지만, 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)))

함수 기반 인덱스는 사용할때 조건이 있다. where 절에 반드시 함수 기반 인덱스에 명시된 표현식이 그대로 사용되어야 한다. 그렇지 않다면 옵티마이저는 함수기반 인덱스를 사용하지 못한다.

참고로 가상 컬럼을 이용한 인덱스와 함수를 이용한 함수기반인덱스는 내부적으로 동일한 구현방법을 사용한다.