8.6 함수 기반 인덱스

8.6 함수 기반 인덱스

일반적인 인덱스는 컬럼 값의 앞부분이나 전체에 대해서 인덱스를 생성이 허용

때로는 컬럼 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야할 때도 있은데

이러한 경우에 함수 기반 인덱스를 활용하면 좋음

함수기반 인덱스 구현 방법

• 가상 컬럼을 이용한 인덱스
• 함수를 이용한 인덱스

함수기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이가 있을 뿐이지 내부적인 구조와 유지관리는 B-Tree와 같다.

8.6.1 가상 컬럼을 이용한 인덱스

create table user(
    user_id bigint,
    first_name varchar(10),
    last_name varchar(10),
    primary key (user_id)
);

위와 같은 테이블에서 fitst_name 과 last_name을 합쳐서 검색해야 하는 요건이 생겼다면?

alter table user
add full_name varchar(30) as (concat(first_name, ' ', last_name)) virtual,
add index ix_fullname (full_name);

위처럼 가상 컬럼을 추가하고 그 가상컬럼에 인덱스를 생성할 수 있게 됨.

virtual 이나 stored 옵션중 어떤 걸 사용해도 상관 없다.

가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같은 효과를 내서 실제 테이블의 구조가 변경된다.(15.8 에서 자세히 설명)

8.6.2 함수를 이용한 인덱스

가상 컬럼은 5.7 버전에도 있었지만 함수를 직접 인덱스 생성 구분에 사용할 수는 없었음.

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)))
);

함수를 직접 사용하는 인덱스는 테이블의 구조는 변경하지 않고 계산된 결과값의 검색을 빠르게 만들어줌.

함수 기반 인덱스를 활용하려면 함수 기반 인덱스에 명시도니 표현식이 그대로 사용되어야함.

표현식이 다르면 옵티마이저가 다른 표현식으로 간주해 인덱스를 사용하지 못함.

만약 위의 쿼리가 인덱스가 안탔다면 ' '이 문제일 확률이 높다.

이경우

• collation_connection
• collation_database
• collation_server
  이 세가지 시스템 변수의 값을 일치시켜서 테스트를 수행하자.