MariaDB 가상 필드를 통한 JSON 인덱싱 방법(Generated Columns)

Indexing a Generated Column to Provide a JSON Column Index.

 

저장되는 데이터들이 많아지고, 또 그 데이터들의 형태가 복잡 + 다양하게 활용되고 있는데요. 때문에 관계형 데이터베이스(Relational Database)에서도 json 타입을 지원하고, 또 사용자들에게도 많이 사용되는 추세입니다.

 

'mysql'의 경우 MySQL 8.0.13부터 json 데이터 타입 필드의 인덱싱을 지원하는 반면, 'mariadb'의 경우 아직까지 json 타입의 필드에 대한 인덱싱이 지원되지 않고 있는데요.

아래 내용을 통해 'mariadb에서 가상 필드를 통한 json 타입 필드의 Indexing 방법'을 살펴보겠습니다.

(해당 방법은 MySQL에서도 적용될 수 있습니다.)

 

/*

MySQL 5.7.8부터 json 데이터 타입을 지원하기 시작했으며, MySQL 5.7에서는 아래 내용을 통해 살펴볼 방식과 같은 Generated Columns 방식을 사용하여 json 타입에 인덱싱을 적용하였지만, MySQL 8.0.13부터는 json type에 인덱스를 바로 생성할 수 있는 기능이 추가되었습니다.

mariadb의 경우 MariaDB 10.2.7부터 json 형식의 데이터가 지원되기 시작했으며, 내부적으로 longtext로 저장됩니다.

*/

 

---

Generated Columns (VIRTUAL | STORED)

 

해당 방식에서는 실제 테이블에 값을 저장하는 'PERSISTENT' 유형과, 값을 저장하지 않고 테이블을 쿼리 할 때 동적으로 생성되는 'VIRTUAL' 유형이 있는데요.

persistent 유형을 'computed columns'라고 하며, virtual 유형을 'virtual columns'라고 합니다.

 

해당 포스팅에서는 virtual columns를 사용한 방식을 위주로 살펴볼 것이며, 포스팅 맨 하단에 computed columns를 사용한 방식과의 차이점도 간단하게 정리되어 있다는 점 참고 부탁드리겠습니다.

 

 

---

예시로 사용될 product table

CREATE TABLE product (
    id INT NOT NULL AUTO_INCREMENT, 
    product_id VARCHAR(20) NOT NULL, 
    properties LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, 
    created_at DATETIME, 
    PRIMARY KEY (id)
);

(create table 구문)

 

{
    "price": 19,
    "name": "Chocolate Chip Cookies",
    "category_id": 2,
    "stock": 30
}

(json example)

 

 

virtual 적용 전

virtual colums을 적용하기 전 json type 필드인 properties의 category_id 값으로 데이터를 조회하는 내용입니다.

두 번째 explain을 사용하여 실행계획을 확인하는 구문의 결과 보면 조회에서 테이블 풀스캔이 일어나는 것을 확인할 수 있는데요.

 

이 상황에서 가상 칼럼(virtual column)을 통한 인덱스를 적용해 보도록 하겠습니다.

(마지막에 있는 데이터베이스의 용량을 확인하는 부분은 아래 가상 칼럼 생성 후 비교를 위한 것으로 아래에서 다시 언급할 예정입니다.)

 

 

add virtual column

ALTER TABLE product ADD COLUMN category_id INT GENERATED ALWAYS AS (JSON_EXTRACT(properties, '$.category_id')) VIRTUAL;

가상 칼럼을 추가하는 쿼리문입니다. 'GENERATED ALWAYS AS' 구문을 통해 가상 칼럼을 생성할 수 있으며, 마지막에 'VIRTUAL'이 아닌 'STORED' 옵션을 사용하면 해당 필드에 stored 방식이 적용됩니다.

(default 값은 virtual이지만 여기서는 보여주기 위해 명시하였습니다.)

 

쿼리 결과 맨 하단에 category_id 칼럼(VIRTUAL GENERATED)이 추가된 것을 확인할 수 있습니다.

 

 

virtual 적용 후

virtual column 생성 후 해당 테이블의 전체 용량을 다시 조회해 보면 virtual column 생성 전과 같은 것을 확인할 수 있는데요. 즉, 실제로 해당 데이터가 테이블에 저장되어 있는 것은 아니라는 것을 알 수 있습니다.

그리고 가상 칼럼 적용 전과 마찬가지로 category_id가 2인 데이터를 조회했을 때 같은 결과가 나오는 것을 확인할 수 있으며, explain 구문을 통해서 category_id가 key로 적용된 것(= 인덱싱이 적용)을 확인할 수 있습니다.

 

정리를 하자면 mariadb에서 json 데이터 타입 인덱싱을 위해서는 다음과 같이 virtual 필드를 이용한 방법을 사용할 수 있으며, 가상 필드는 메타 데이터에만 존재하는 것으로 행 데이터에 대한 메모리 또는 디스크의 추가 저장 공간이 필요하지 않습니다.

필요에 따라 생성되는 view와 유사하다고 볼 수 있으며, 저장 용량은 필요하지 않지만 조회 시마다 추가적인 계산 작업이 발생되는 것은 피할 수 없는 부분입니다.

 

 

---

STORED 방식과 비교

virtual의 경우 테이블이 쿼리 될 때 동적으로 생성됩니다. 즉, 위에서 이야기한 것처럼 실제로 테이블에 저장되지 않으며, 저장 공간이 필요하지 않은데요.

stored의 경우 값이 실제로 테이블에 저장되는 방식으로, 열의 값을 결정하는 표현식이 포함될 수 있다는 것이 큰 차이점이고, 따라서 해당 행의 데이터가 insert 되거나 update 될 때 값이 저장됩니다.

 

또한 virtual column의 경우 기본 키(primary key) 적용이 불가능한 반면, stored column은 기본 키로 지정할 수 있다는 차이점도 있습니다.

 

 

 

< 참고 자료 >

https://mariadb.com/kb/en/generated-columns/
https://www.compose.com/articles/mysql-for-json-generated-columns-and-indexing/