인덱스 - 함수 기반 인덱스

함수 기반 인덱스

칼럼의 값을 변형해서 만들어진 값에 대해, 인덱스를 구축할 때도 있다. 이 경우 함수 기반의 인덱스를 활용하는데 이 방법은 두 가지로 구분된다.

• 가상 칼럼 활용한 인덱스
• 함수를 이용한 인덱스

함수 기반 인덱스의 내부적 구조 및 유지 방법은 B-Tree 인덱스와 동일하다.

가상 칼럼을 이용한 인덱스

만약 다음과 같은 테이블이 있다면

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

이 경우 first_name, last_name을 합쳐 검색하는 요건이 생기면,

8.0 이전 버전에서는 둘을 합친 full_name 칼럼을 생성하고 이 칼럼에 대해 인덱스를 생성해야 했다.

하지만 8.0 이후부터는 가상 칼럼을 추가해 그 칼럼에 인덱스를 생성할 수 있다.

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

위와 같이 가상 칼럼에 인덱스를 생성하면, fullname 칼럼과 ix_fullname 인덱스를 활용해 검색이 가능하다

EXPLAIN SELECT * FROM user WHERE full_name = 'lee geonhoe';

가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

가상 칼럼은 일반 칼럼과 달리 db에 저장되지는 않고 실행중에 계산된다던가, where절에 나타나지만 컬럼값 자체에 대한 변경이 불가하다는 차이가 있다.

함수를 이용한 인덱스

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) = 'lee geonhoe';