[MySQL] 함수 기반 인덱스

칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축할 수 있습니다. MySQL 서버에서 함수 기반 인덱스를 구현하는 방법은 다음과 같습니다.

 

1. 가상 칼럼을 이용한 인덱스

2. 함수를 이용한 인덱스

 

함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 존재합니다.

가상 칼럼을 이용한 인덱스

다음과 같은 테이블이 있습니다.

mysql> CREATE TABLE user(
          user_id BIG_INT
          first_name VARCHAR(10),
          last_name VARCHAR(10),
          PRIMARY KEY (user_id)
       );

first_name와 last_name을 합쳐서 검색해야 하는 요건이 생겼습니다. 두 칼럼을 합친 full_name이라는 칼럼을 만들어 인덱스를 생성해도 되지만 다른 방법이 있습니다. 

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

full_name 컬럼을 generated column으로 만들 수 있다. VIRTUAL 속성을 추가하면 칼럼을 읽을 때만 계산되고 저장되지 않습니다. STORED 속성을 명시하면 레코드가 삽입, 수정될 때 해당 칼럼의 값도 저장됩니다. default는 VIRTUAL 입니다.

 

가상 칼럼은 텡이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있습니다.(저도 잘 이해가 되지 않아 추후 추가 설명을 덧붙이겠습니다.)

 

함수를 이용한 인덱스

MySQL 8.0 버전부터 테이블의 구조를 변경하지 않고, 함수를 직접 사용해 인덱스를 생성할 수 있게 됐습니다.

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

함수를 직접 사용하는 인덱스는 테이블의 구조를 변경하지 않습니다. 하지만 해당 인덱스를 사용하려면 조건절에 인덱스에 명시된 표현식과 정확히 일치하는 표현식을 사용해야 합니다. 설령 결과는 같더라도 표현식이 다르면 옵티마이저는 조건와 인덱스가 다르다 판단하여 함수 기반 인덱스를 사용하지 못합니다.

💡 가상 칼럼과 함수 기반 인덱스는 내부적으로 동일한 방법으로 구현돼 있습니다. 따라서 둘의 성능 차이는 없습니다.