MySQL JSON, Generated Columns

 

MySQL 5.7.8 부터 JSON 을 지원한다. 

JSON 문서 내의 값에서 Generated Column을 생성한 다음 해당 컬럼을 인덱싱하면 실제로 JSON 필드를 인덱싱할 수 있다. 

 

JSON 테스트 데이터 

{
    "id": 1,  
    "name": "Sally",  
    "games_played":{    
       "Battlefield": {
          "weapon": "sniper rifle",
          "rank": "Sergeant V",
          "level": 20
        },                                                                                                                          
       "Crazy Tennis": {
          "won": 4,
          "lost": 1
        },  
       "Puzzler": {
          "time": 7
        }
     }
 }

테스트 테이블 생성

CREATE TABLE `players` (  
   `id` INT UNSIGNED NOT NULL,
   `player_and_games` JSON NOT NULL,
   `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
   PRIMARY KEY (`id`)
);

위 DDL문에서 아래 부분이 JSON 타입 컬럼 player_and_game 에서 name 속성 값을 참조하는 Generated Column (names_virtual) 을 지정한 부분이다. 

`names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name')

Generated Column 에 지정할수 있는 제약조건은 아래와 같다. 

[VIRTUAL|STORED] [UNIQUE [KEY]] [[NOT] NULL] [[PRIMARY] KEY]

VIRTUAL, STORED는 컬럼의 값이 실제로 저장되는지 여부를 나타낸다.

- VIRTUAL : 기본값, 컬럼의 값 저장안함, 저장 공간없음, 행을 읽을 때마다 계산해서 처리한다.

                    인덱스를 생성하면 값이 인덱스 자체에 저장된다.

- STORED : 데이터가 테이블에 저장(입력,수정)될때 컬럼의 값도 저장된다. 

                    인덱스는 일반적인 인덱스와 동일하게 처리된다. 

 

아래에 테스트 소스 깃헙에 있는 mysql-json.sql 파일에 있는 insert 문을 사용한다. 

INSERT INTO `players` (`id`, `player_and_games`) VALUES (1, '{  
    "id": 1,  
    "name": "Sally",
    "games_played":{    
       "Battlefield": {
          "weapon": "sniper rifle",
          "rank": "Sergeant V",
          "level": 20
        },                                                                                                                          
       "Crazy Tennis": {
          "won": 4,
          "lost": 1
        },  
       "Puzzler": {
          "time": 7
        }
      }
   }'
);
....

입력된 데이터 확인, names_virtual 컬럼에서 JSON 문서의 name 속성값 확인됨 

mysql> select * from players;
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| id | player_and_games                                                                                                                                                                                           | names_virtual |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
|  1 | {"id": 1, "name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Sergeant V", "level": 20, "weapon": "sniper rifle"}, "Crazy Tennis": {"won": 4, "lost": 1}}}                  | Sally         |
|  2 | {"id": 2, "name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": {"rank": "Major General VIII", "level": 127, "weapon": "carbine"}, "Crazy Tennis": {"won": 10, "lost": 30}}}            | Thom          |
|  3 | {"id": 3, "name": "Ali", "games_played": {"Puzzler": {"time": 12}, "Battlefield": {"rank": "First Sergeant II", "level": 37, "weapon": "machine gun"}, "Crazy Tennis": {"won": 30, "lost": 21}}}           | Ali           |
|  4 | {"id": 4, "name": "Alfred", "games_played": {"Puzzler": {"time": 10}, "Battlefield": {"rank": "Chief Warrant Officer Five III", "level": 73, "weapon": "pistol"}, "Crazy Tennis": {"won": 47, "lost": 2}}} | Alfred        |
|  5 | {"id": 5, "name": "Phil", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Lt. Colonel III", "level": 98, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 130, "lost": 75}}}          | Phil          |
|  6 | {"id": 6, "name": "Henry", "games_played": {"Puzzler": {"time": 17}, "Battlefield": {"rank": "Captain II", "level": 87, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 68, "lost": 149}}}             | Henry         |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
6 rows in set (0.00 sec)

players 테이블 컬럼 정보 확인 

mysql> show columns from players;
+------------------+--------------+------+-----+---------+-------------------+
| Field            | Type         | Null | Key | Default | Extra             |
+------------------+--------------+------+-----+---------+-------------------+
| id               | int unsigned | NO   | PRI | NULL    |                   |
| player_and_games | json         | NO   |     | NULL    |                   |
| names_virtual    | varchar(20)  | NO   |     | NULL    | VIRTUAL GENERATED |
+------------------+--------------+------+-----+---------+-------------------+

Extra 에 VIRTUAL GENERATED 으로 Generated Column 이  VIRTUAL 인것을 보여준다. 

STORED 로 지정된 컬럼의 경우 STORED GENERATED 로 표시된다. 

 

Battlefield 레벨, 승리한 테니스 게임, 패배한 테니스 게임 및 퍼즐 게임 시간 컬럼을 추가한다.

ALTER TABLE `players` ADD COLUMN `battlefield_level_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Battlefield.level') NOT NULL AFTER `names_virtual`;  
ALTER TABLE `players` ADD COLUMN `tennis_won_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".won') NOT NULL AFTER `battlefield_level_virtual`;  
ALTER TABLE `players` ADD COLUMN `tennis_lost_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".lost') NOT NULL AFTER `tennis_won_virtual`;  
ALTER TABLE `players` ADD COLUMN `times_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Puzzler.time') NOT NULL AFTER `tennis_lost_virtual`;

mysql> show columns from players;
+---------------------------+--------------+------+-----+---------+-------------------+
| Field                     | Type         | Null | Key | Default | Extra             |
+---------------------------+--------------+------+-----+---------+-------------------+
| id                        | int unsigned | NO   | PRI | NULL    |                   |
| player_and_games          | json         | NO   |     | NULL    |                   |
| names_virtual             | varchar(20)  | NO   |     | NULL    | VIRTUAL GENERATED |
| battlefield_level_virtual | int          | NO   |     | NULL    | VIRTUAL GENERATED |
| tennis_won_virtual        | int          | NO   |     | NULL    | VIRTUAL GENERATED |
| tennis_lost_virtual       | int          | NO   |     | NULL    | VIRTUAL GENERATED |
| times_virtual             | int          | NO   |     | NULL    | VIRTUAL GENERATED |
+---------------------------+--------------+------+-----+---------+-------------------+

 

Generated Column을 검색하는 쿼리 실행계획 확인 

mysql> EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

해당 컬럼에 인덱스 생성하고 실행계획 확인, 생성한 인덱스 인덱스 잘탄다. 

mysql> CREATE INDEX `names_idx` ON `players`(`names_virtual`);  
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: ref
possible_keys: names_idx
          key: names_idx
      key_len: 82
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

다른 Generated Column 들도 인덱스를 생성하고, 생성된 인덱스를 확인한다. 

mysql> CREATE INDEX `times_idx` ON `players`(`times_virtual`);  
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX `won_idx` ON `players`(`tennis_won_virtual`);  
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX `lost_idx` ON `players`(`tennis_lost_virtual`);  
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX `level_idx` ON `players`(`battlefield_level_virtual`);  
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM `players`;
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| players |          0 | PRIMARY   |            1 | id                        | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | names_idx |            1 | names_virtual             | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | times_idx |            1 | times_virtual             | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | won_idx   |            1 | tennis_won_virtual        | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | lost_idx  |            1 | tennis_lost_virtual       | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | level_idx |            1 | battlefield_level_virtual | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.03 sec)

 

STORED 로 지정된 Generated Column 을 사용하는 경우 

1. PRIMARY KEY 를 잡아야될경우 
2. 일반 BTREE 대신 FULLTEXT 또는 RTREE 인덱스가 필요한 경우 
3. 인덱스를 사용하지 않는 스캔이 많이 발생하는 컬럼일 경우 

 

테스트용 테이블 생성 

CREATE TABLE `players_two` (  
    `id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED NOT NULL,
    `player_and_games` JSON NOT NULL,
    `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL,
    `times_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Puzzler.time') NOT NULL,
    `tennis_won_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".won') NOT NULL,
    `tennis_lost_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".lost') NOT NULL,
    `battlefield_level_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Battlefield.level') NOT NULL, 
    PRIMARY KEY (`id`),
    INDEX `times_index` (`times_virtual`),
    INDEX `names_index` (`names_virtual`),
    INDEX `won_index` (`tennis_won_virtual`),
    INDEX `lost_index` (`tennis_lost_virtual`),
    INDEX `level_index` (`battlefield_level_virtual`)
);

PK 컬럼을 STORED 로 지정하였다. 

`id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED NOT NULL,

SHOW COLUMNS 로 컬럼 확인 

mysql> SHOW COLUMNS FROM `players_two`;
+---------------------------+-------------+------+-----+---------+-------------------+
| Field                     | Type        | Null | Key | Default | Extra             |
+---------------------------+-------------+------+-----+---------+-------------------+
| id                        | int         | NO   | PRI | NULL    | STORED GENERATED  |
| player_and_games          | json        | NO   |     | NULL    |                   |
| names_virtual             | varchar(20) | NO   | MUL | NULL    | VIRTUAL GENERATED |
| times_virtual             | int         | NO   | MUL | NULL    | VIRTUAL GENERATED |
| tennis_won_virtual        | int         | NO   | MUL | NULL    | VIRTUAL GENERATED |
| tennis_lost_virtual       | int         | NO   | MUL | NULL    | VIRTUAL GENERATED |
| battlefield_level_virtual | int         | NO   | MUL | NULL    | VIRTUAL GENERATED |
+---------------------------+-------------+------+-----+---------+-------------------+

 

출처 :

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

https://dev.mysql.com/blog-archive/indexing-json-documents-via-virtual-columns/

테스트 소스 :

https://github.com/compose-ex/mysql-json-indexing-generated-columns/blob/master/mysql-json.sql