mysql 함수를 이용한 인덱스 생성하기 feat.virtual column

mysql 함수를 이용한 인덱스 생성하기 feat.virtual column

mysql 함수를 이용한 인덱스 생성하기 feat.virtual column

안녕하세요.
이번에는 mysql에서 함수를 이용한 인덱스를 생성하는 방법은 안내 해드리려 합니다.
오라클에선 잘쓰던 함수를 이용한 인덱스를 Mysql 이나 Mariadb에서 사용 할 수 없어 답답 했었는데, 좋은 정보를 알게되어 공유를 해드립니다.

가상 컬럼 이란?

Mysql 5.7 부터 지원되는 가상 컬럼은 가상의 칼럼을 둬서 수식과 조건문을 사용해 데이터의 가공 결과를 저장하는 것을 말합니다. 사용 방법에는 PERSISTENT(stored)와 VIRTUAL(generated-only)이라는 두 가지 타입이 존재합니다. 디폴트는 PERSISTENT 입니다. PERSISTENT virtual columns은 실제 데이터가 데이터베이스에 저장되는 특성을 갖게 되며, VIRTUAL virtual columns은 실제 데이터가 데이터베이스에 저장되지 않고 그때 그때 계산돼 보여주는 역할을 합니다.

실습 예제

예를 들어서 아래와 같은 데이터가 있다고 가정 해볼께요. 

create table virtual_table
(
    id        int auto_increment primary key,
    nation_cd varchar(10),
    nation_nm varchar(20),
    city_nm varchar(20)
);

INSERT INTO virtual_table (id, nation_cd, nation_nm, city_nm) VALUES (1, 'KR_01', '대한민국', '서울');
INSERT INTO virtual_table (id, nation_cd, nation_nm, city_nm) VALUES (2, 'KR_02', '대한민국', '부산');
INSERT INTO virtual_table (id, nation_cd, nation_nm, city_nm) VALUES (3, 'US_01', '미국', '워싱턴');
INSERT INTO virtual_table (id, nation_cd, nation_nm, city_nm) VALUES (4, 'US_02', '미국', '뉴욕');
INSERT INTO virtual_table (id, nation_cd, nation_nm, city_nm) VALUES (5, 'US_03', '미국', 'LA');
INSERT INTO virtual_table (id, nation_cd, nation_nm, city_nm) VALUES (6, 'PH_01', '필리핀', '마닐라');
INSERT INTO virtual_table (id, nation_cd, nation_nm, city_nm) VALUES (7, 'PH_02', '필리핀', '세부');
INSERT INTO virtual_table (id, nation_cd, nation_nm, city_nm) VALUES (8, 'UK_01', '영국', '런던');
INSERT INTO virtual_table (id, nation_cd, nation_nm, city_nm) VALUES (9, 'UK_02', '영국', '첼시');

나라와 도시

이런 데이터가 엄청 많다고 가정을 합니다. 그리고 nation_cd에 인덱스가 생성 되어 있구요. 여기서 nation_cd에 조건을 주고 다음과 같이 select를 한다면 인덱스를 사용 하게 될까요?

select * from virtual_table
where nation_cd like 'US%';

like를 썼기때문에 인덱스를 타지 않을 것 입니다.
이럴때 바로 가상컬럼을 생성하고 인덱스를 만들어 사용 하면 되는 것 이죠!

 

 

 

사용 방법

가상컬럼을 생성하고 인덱스를 만드는 SQL은 다음과 같습니다.

alter table virtual_table
    add nation_cd_2 varchar(10) as (left(nation_cd, 2)) virtual,
    add index idx_super_id (nation_cd_2);

이렇게 생성 하면 virtual 컬럼이 생성이 된것 이고, left(nation_cd_2) 한 결과는 select 할때마다 서버에서 계산이 되어 출력이 됩니다. 저장이 되어 있는게 아니라는 것 이죠. 저장을 하는 방식으로 하려면 다음과 같이 하시면 됩니다.

alter table virtual_table
    add nation_cd_3 varchar(10) as (left(nation_cd, 3)) stored,
    add index idx_super_id_3 (nation_cd_3);

이렇게 stored 라고 명시를 하면 직접 저장이 되는 구조로 컬럼을 추가 할 수 있습니다.

그럼 한번 select 해볼까요?

가상 컬럼 추가 결과

위에서 빨간색은 virtual로 저장된 결과이고, 파란색은 실제로 stored 한 결과 입니다.
이렇게 가상 컬럼을 추가 하고 함수를 이용하여 인덱스를 만들어 사용 할 수 있습니다.
유용하게 잘 쓰시길 바랍니다.

감사합니다!!!

by.sTricky

2021.11.24 - [Database/mariaDB administrator] - mysql 실무에 유용한 sql 로직 모음 #02

mysql 실무에 유용한 sql 로직 모음 #02

2021.07.23 - [Database/mariaDB administrator] - mysql 테이블 및 컬럼 케릭터셋 변경 하기

mysql 테이블 및 컬럼 케릭터셋 변경 하기

2021.07.02 - [Database/mariaDB administrator] - mariadb partition table의 종류와 선택 기준 주의할 점

mariadb partition table의 종류와 선택 기준 주의할 점

2021.03.18 - [Database/mariaDB administrator] - replace into & insert into 차이점, 주의할점이 무엇인지 쉽고 간단하게 확인하기

replace into & insert into 차이점, 주의할점이 무엇인지 쉽고 간단하게 확인하기