TIL

8.6 함수 기반 인덱스

칼럼의 값을 변형해서 만든 값에 대해 인덱스를 만들어야 할 때가 있는데 이 때 함수 기반 인덱스를 활용할 수 있다.

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

• 함수 기반 인덱스가 없던 MySQL 5.7에는 이 방법으로 요구 사항을 처리했다.
• 예를 들어 회원 정보를 저장하는 테이블이 아래와 같이 정의되어 있다고 하자.

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

• first_name과 last_name을 합쳐서 풀 네임으로 검색해야 하는 요구 사항이 추가되었다.
• 가상 칼럼 사용 X
  • full_name이라는 칼럼을 실제로 추가하고 이 칼럼에 인덱스를 생성
• 가상 칼럼 사용 O
  • 가상 칼럼을 추가하고 그 가상 칼럼에 인덱스를 생성

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

• 가상 칼럼은 테이블에 새 칼럼을 추가하는 것과 같은 효과를 내기에 테이블 구조가 변경된다는 단점이 있다.

8.6.2 함수를 이용한 인덱스

• 함수 기반 인덱스를 이용하면 테이블 구조를 변경하지 않고 인덱스를 생성할 수 있게 되었다.

CREATE TABLE user(
	...
	...
	INDEX ix_fullname ((CONCAT(first_name, ' ', last_name)))
);

• 테이블 구조 변경 없이 계산된 결과값의 검색을 빠르게 만들어준다.
• WHERE 조건절에 사용된 표현식이 그대로 사용돼야 인덱스를 태울 수 있다.

SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name)='Matt Lee';