8) Generated 컬럼 및 함수 기반 인덱스

Generated Column

• 표현식으로 정의된 컬럼
• 정의된 표현식에 따라 컬럼의 값이 자동으로 생성
  • 표현식 = 고정된 값, 함수 또는 다른 컬럼들에 대한 연산 조합 등이 해당
• 사용자가 직접 값을 입력하거나 변경할 수 없음
• 두 가지 종류가 존재
  • Virtual Generated Column (가상 컬럼)
  • Stored Generated Column (스토어드 컬럼)

• 기본적으로 virtual 타입으로 생성 & null 값을 허용한다.
• primary key 로는 stored 타입만 허용한다.
• 하나의 테이블에서 가상 컬럼과 스토어드 컬럼 혼합해서 사용 가능하다.

 

• 컬럼의 값을 디스크에 저장하지 않는다.
• 컬럼의 값은 레코드가 읽히기 전 또는 before 트리거 실행 직후에 계산된다.
• 인덱스 생성이 가능하고 , 인덱스 데이터는 디스크에 저장이 된다.

• 컬럼의 값을 디스크에 저장한다.
• 컬럼의 값은 레코드가 insert 되거나 update 될 때 계산되어 저장 된다.
• 인덱스 생성이 가능하다.

 

Generated Column DDL 작업

• alter 명령으로 add / modfiy / change / drop / rename 가능하다
• 일반 컬럼을 스토어드 컬럼으로 , 스토어드 컬럼을 일반 컬럼으로 변경 가능하다
  • 가상 컬럼은 일반 컬럼으로 전환이 불가능하다
• 스토어드 컬럼 <-> 가상 컬럼 간 변경이 불가능하다
  • 새로 컬럼을 추가하고 삭제하는 방식으로만 전환 가능하다

online ddl 작업시에 ddl 명령에 실행 알고리즘을 직접 명시하는 것을 권장한다. 지정된 알고리즘으로 수행할 수 없는 경우에는 에러가 반환이 된다.

제일 빠른 instant 알고리즘을 시도해보고  불가능한경우에는 inplace 알고리즘의 lock=none 키워드를 추가해 잠금을 걸지 않는 inplace 방식을 시도하는게 좋다.

 

Genearted Column DDL 작업

가상 컬럼 추가 또는 변경 시 (Add / change / modify) 사용 가능한 유효성 검사 옵션

• Without Validation
  • 기본설정으로 기존데이터 무결성을 확인하지 않으며, 가능한 경우 in-place 방식으로 작업을 수행한다.
  • 계산된 값이 컬럼의 값 범위를 벗어날 수 있다. (경고 또는 에러가 발생함)
• With Validation
  • 테이블 데이터 복사를 수행한다
  • 작업중 dml 유입시 잠금 대기(metadata lock 대기) 발생
  • 작업시 계산된 값이 컬럼의 값 범위를 벗어나는 경우 명령문이 실패한다.

validation이 되지 않아 concat한 결과값이 아니라 에상과는 다른값이 결과로 나온다.

validation이 동작하여 해당 statement가 실패한 경우이다.

운영디비에서는 앵간 하면 사전에 검증을 따로 수행하고 without validation을 쓰는것이 바람직하다.

 

인덱스 사용

• 일반 컬럼과 동일하게 쿼리에서 인덱스 사용가능하다
• 쿼리에 Generated 컬럼명 대신 표현식을 사용해도 인덱스 사용이 가능하다
• 대신 표현식은 컬럼에 정의된 표현식과 완전히 일치해야 한다.
  • 컬럼에는 (col1 + 1) 로 정의돼있으나, 쿼리에서 (1 + col1) 로 사용 시 인덱스 사용이 불가능하다
• 또한 주어진 조건값과 컬럼 타입도 동일해야한다.
• = < > <= > >= BETWEEN , IN 연산자 사용시 이러한 최적화가 적용이 된다.

 

제한 사항

• 표현식에 아래 항목들은 사용이 불가능하다
• 비결정적함수 , 스토어드 프로그램 , 변수
• 서브 쿼리

• insert / update 시 generated 컬럼에 직접 값을 지정할 수 없으며, 지정할 수 있는 값은 "DEFAULT" 만 가능하다
• 트리거에서 NEW.col_name 이나 OLD.col_name으로 Generated 컬럼 참조 가능하다

---

Function Based Index

• 일반 인덱스는 컬럼 또는 컬럼의 prefix 만 인덱싱 가능하다
  • create index ix_col on tab (col);
  • create index ix_col20 on tab (col(20));
• 함수 기반 인덱스는 "표현식"을 인덱싱 값으로 사용이 가능하다
  • create inedx f_index on tab ((col1 + col2), (col1 * col2));
  • create index f_index on tab (date(col));
• 쿼리의 조건절에서 컬럼을 가공하는 경우에 유용하게 사용이 가능하다
  • 사용하는 쿼리 -> select * from tab where (col1 + col2) > 10;
  • 쿼리를 위한 인덱스 -> create index f_index on tab ((col1 + col2));

 

동작 방식

• virtual generated 컬럼을 자동 생성 후 인덱싱
• 자동 생성된 virtual 컬럼은 일반적인 환경에서는 확인 불가
• 가상 컬럼의 이름은 !hidden!index_name!key_part!counter 형태로 지정되며 타입도 자동지정이 된다.
  • 숨켜진 column으로 위에처럼 debug 옵션을 줘야 보인다.

 

사용 방법

• 각각의 표현식은 반드시 괄호로 묶어서 명시된다.
  • create inedx f_index on tab ((col1 + col2), (col1 * col2));
• 일반 컬럼과 함께 복합 인덱스로도 구성 가능하다
  • create index f_index on tab (col1 , (lower(col2)), col3(20));
• 표현식 값에 대해 ASC & DESC 지정 가능하다
• UNIQUE 설정도 가능하다

 

1번째로 문자열 값의 특정 부분에 대해서 조회하는 경우이다.

특정 이메일 도메인의 데이터들을 확인하고 싶은 경우 나 특정 구 나 동에 해당하는 데이터들을 확인하려 하는 예시이다.

SELECT *
FROM users
WHERE substring_index(email, '@', -1) = 'gmail.com';

요런식의 쿼리를 날렸을 때 function index 같은 경우  ix_email_domain 의 함수식과 같은 경우이니 해당 인덱스가 사용이 된다.

 

주의사항

• 인덱스 생성 후 실행 계획을 반드시 확인
• 표현식을 정확하게 명시해야 인덱스 사용이 가능하다

인덱스가 (col1 +1) 로 정의되어 있음으로 그대로 사용한 경우 첫번째 처럼 key에 함수 인덱스가 제대로 사용된것을 확인 할 수 있다.

하지만 같은 result를 내더라도 순서가 반대인 (1 + col1) 의 경우에는 key가 null이다. 인덱스를 사용못하는것을 확인할 수 있다.

 

 

표현식 결과의 데이터 타입을 명확하게 확인해서 조건값 지정해야한다.

1번째 예시는 "2" 를 문자 값으로 줘서 인덱스를 활용못하는 것이고 2번째 예시처럼 int로 줘서 제대로 인덱스를 쓰는것을 알 수 있다.

 

col1, col2 둘다 double임으로 합한 결과 값도 double type이다. 따라서 인덱스를 사용하기 위해서는 결과 값이 double이어야 함수 인덱스를 제대로 사용한다.

표현식의 결과 값을 확인할 때 쓰이는 명령어이다.

이외에도 create table as select 등과 같은 구문을 사용할 수 있다.

주의 사항 정리

• 인덱스 생성 후 실행 계획을 반드시 확인
  • 표현식을 정확하게 동일한 형태로 명시해야 인덱스 사용이 가능하다
  • 정확하게 명시하더라도 사용하지 않는 경우도 있다.
    • select * from tab where LOWER(col) like "abc%" ==> Bug #104713 
    • lower과 관련된 인덱스가 있음에도 불구하고 like와 같이 쓰면 인덱스를 타지 않음
• 표현식 결과의 데이터 타입을 명확하게 확인해서 조건 값을 지정해야한다.
  • where MONTH(datetime) = string -> int
  • where doulbe + double = int -> double
• 기본적으로 일반 인덱스보다 추가적인 계산 비용이 발생한다.
  • 변경이 낮은 컬럼이거나 복잡한 표현식 사용 시에 오버헤드가 커질 수 있다.

 

제한사항

• 표현식에 비결정적(Non deterministic) 함수 사용 불가
• 일반 컬럼 및 prefix 길이가 지정된 컬럼은 키 값으로 지정이 불가하다
  • 괄호 없이 사용 하거나 substring 또는 cast 함수를 사용해야한다.
• 공간 인덱스나 전문검색 인덱스는 지원하지 않는다.
• primary key에 표현식은 포함이 불가능하다