Database/mariaDB administrator

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

sTricky 2021. 12. 16. 09:56

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

mysql 실무에 유용한 sql 로직 모음 #02 지난 1편에 이어서 mysql 실무에 유용한 sql 로직 모음 2탄을 준비했습니다. 지난번과 마찬가지로 케이스 별로 유용한 sql 로직들에 대한 실습과 설명으로 진행

stricky.tistory.com

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

 

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

mysql 테이블 및 컬럼 케릭터셋 변경 하기 mysql이나 mariadb를 사용하면서 간혹 테이블이나 컬럼의 케릭터셋을 변경해야 하는 경우가 생깁니다. 물론 데이터베이스 전체의 케릭터셋을 변경 할 수도

stricky.tistory.com

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

 

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

mariadb partition table의 종류와 선택 기준 주의할 점 안녕하세요. 오늘은 mariadb의 파티션 테이블에 대해서 서 mariadb에서도 partition table을 지원 하고 있습니다. partition table의 종류 와 특징 mariad..

stricky.tistory.com

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

 

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

replace into & insert into 차이점, 주의할점이 무엇인지 쉽고 간단하게 확인하기 안녕하세요. MYSQL이나 mariadb를 쓰시는 여러분들께 오늘은 replace into 와 insert into의 차이점을 말씀 드려보겠습니다. 자..

stricky.tistory.com