MariaDB에서 JSON 형식의 데이터 사용하기
JSON은 Maria 10.2 부터 지원 됨.
MariaDB [test]> select @@version ;
+---------------------+
| @@version |
+---------------------+
| 10.2.10-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)
#JSON 데이터 타입 지원 (내부적으로 longtext로 저장 됨)
MariaDB [test]> create table json_test (id int , data json ) ;
Query OK, 0 rows affected (0.24 sec)
MariaDB [test]> show create table json_test ;
+-----------+-------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------+
| json_test | CREATE TABLE `json_test` ( |
| | `id` int(11) DEFAULT NULL, |
| | `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)
# json_object 펑션을 이용하여 key, value 형식으로 Insert
MariaDB [test]> insert into json_test values (1 , json_object('Name' , 'YoungJoon CHOI' , 'Sex' , 'M' , 'Phone' , '111-2222')) ;
Query OK, 1 row affected (0.16 sec)
MariaDB [test]> commit ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from json_test ;
+------+-------------------------------------------------------------+
| id | data |
+------+-------------------------------------------------------------+
| 1 | {"Name": "YoungJoon CHOI", "Sex": "M", "Phone": "111-2222"} |
+------+-------------------------------------------------------------+
1 row in set (0.01 sec)
#특정 key 값만 조회 하고자 할때 json_value 함수를 사용
MariaDB [test]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
+------+----------------+----------+
| id | Name | Phone |
+------+----------------+----------+
| 1 | YoungJoon CHOI | 111-2222 |
+------+----------------+----------+
1 row in set (0.00 sec)
#특정 Key 값을 update 하고자 할때, json_replace 함수를 사용
MariaDB [test]> update json_test set data = json_replace(data,'$.Phone','222-3333') where id = 1 ;
Query OK, 1 row affected (0.21 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> commit ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
+------+----------------+----------+
| id | Name | Phone |
+------+----------------+----------+
| 1 | YoungJoon CHOI | 222-3333 |
+------+----------------+----------+
1 row in set (0.00 sec)
#하나 이상의 key값을 변경하고자 할때, json_set 함수를 사용
MariaDB [test]> update json_test set data = json_set(data,'$.Phone','333-4444', '$.Name','DongHyeok KIM') where id = 1 ;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> commit ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from json_test ;
+------+------------------------------------------------------------+
| id | data |
+------+------------------------------------------------------------+
| 1 | {"Name": "DongHyeok KIM", "Sex": "M", "Phone": "333-4444"} |
+------+------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [test]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
+------+---------------+----------+
| id | Name | Phone |
+------+---------------+----------+
| 1 | DongHyeok KIM | 333-4444 |
+------+---------------+----------+
1 row in set (0.00 sec)
MariaDB [test]> insert into json_test values (2 , json_object('Name' , 'Kildong HONG' , 'Sex' , 'M' , 'Phone' , '999-9999' , 'Birth', '2000-01-01')) ;
Query OK, 1 row affected (0.09 sec)
MariaDB [test]> commit ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from json_test ;
+------+----------------------------------------------------------------------------------+
| id | data |
+------+----------------------------------------------------------------------------------+
| 1 | {"Name": "DongHyeok KIM", "Sex": "M", "Phone": "333-4444"} |
| 2 | {"Name": "Kildong HONG", "Sex": "M", "Phone": "999-9999", "Birth": "2000-01-01"} |
+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
# json 데이터 중 일부 데이터 조회
MariaDB [test]> select count(*) from json_test where json_value(data,'$.Phone') like '999%' ;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
1 row in set (2.63 sec)
MariaDB [test]> explain select count(*) from json_test where json_value(data,'$.Phone') like '999%' ;
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | json_test | ALL | NULL | NULL | NULL | NULL | 2083498 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
# json의 일부 key값을 이용하는 가상 칼럼을 생성 후 Index 작업 가능
MariaDB [test]> alter table json_test add phone varchar(20) as (json_value(data,'$.phone')) ;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> select * from json_test limit 1 ;
+------+------------------------------------------------------------+-------+
| id | data | phone |
+------+------------------------------------------------------------+-------+
| 1 | {"Name": "DongHyeok KIM", "Sex": "M", "Phone": "333-4444"} | NULL |
+------+------------------------------------------------------------+-------+
1 row in set (0.00 sec)
MariaDB [test]> select * from json_test limit 2 ;
+------+----------------------------------------------------------------------------------+-------+
| id | data | phone |
+------+----------------------------------------------------------------------------------+-------+
| 1 | {"Name": "DongHyeok KIM", "Sex": "M", "Phone": "333-4444"} | NULL |
| 2 | {"Name": "Kildong HONG", "Sex": "M", "Phone": "999-9999", "Birth": "2000-01-01"} | NULL |
+------+----------------------------------------------------------------------------------+-------+
2 rows in set (0.00 sec)
Json안의 데이터는 Phone로 첫 글자가 대문자인데 칼럼 생성 시 phone 소문자로 생성해 주어서 참조 데이터가 안 나옴.
# 가상 칼럼 생성 시 물리적 파일 사이즈 변화
[root@CMaria ~]# cd /maria10
-bash: cd: /maria10: No such file or directory
[root@CMaria ~]# cd /
[root@CMaria /]# ls
backup bin boot dev etc home lib lib64 maria10.2.10 maria5.5.58 media mnt opt proc root run sbin slave srv sys test.sql tmp usr var xbackup
[root@CMaria /]# cd maria10.2.10/
[root@CMaria maria10.2.10]# ls
aria_log.00000001 CMaria.pid ibdata1 ibtmp1 mysql mysql-bin.000003 mysql-bin.000006 mysql-bin.000009 mysql-bin.000012 mysql-bin.000015 mysql-bin.000018 performance_schema test_mig
aria_log_control groonga.log ib_logfile0 maria10 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 mysql-bin.000010 mysql-bin.000013 mysql-bin.000016 mysql-bin.000019 SCM
CMaria.err ib_buffer_pool ib_logfile1 multi-master.info mysql-bin.000002 mysql-bin.000005 mysql-bin.000008 mysql-bin.000011 mysql-bin.000014 mysql-bin.000017 mysql-bin.index test
[root@CMaria maria10.2.10]# cd test
[root@CMaria test]# ls
db.opt json_test.frm json_test.ibd test_mediumtext.frm test_mediumtext.ibd test_text.frm test_text.ibd test_varchar.frm test_varchar.ibd
[root@CMaria test]# ls -altrh
total 229M
-rw-rw---- 1 mysql mysql 61 Dec 27 14:40 db.opt
-rw-rw---- 1 mysql mysql 6.4K Dec 27 14:40 test_varchar.frm
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_varchar.ibd
-rw-rw---- 1 mysql mysql 465 Dec 27 14:40 test_text.frm
-rw-rw---- 1 mysql mysql 466 Dec 27 14:40 test_mediumtext.frm
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_text.ibd
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_mediumtext.ibd
drwxr-xr-x 8 mysql mysql 4.0K Jan 2 20:00 ..
-rw-rw---- 1 mysql mysql 228M Feb 5 15:33 json_test.ibd
-rw-rw---- 1 mysql mysql 467 Feb 5 15:37 json_test.frm
drwx------ 2 mysql mysql 206 Feb 5 15:37 .
[root@CMaria test]# mysql
MariaDB [(none)]> use test ;
MariaDB [test]> alter table json_test add phone varchar(20) as (json_value(data,'$.Phone')) ;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
가상 칼럼이라서 바로 생성 된다.
MariaDB [test]> \! ls -altrh
total 229M
-rw-rw---- 1 mysql mysql 61 Dec 27 14:40 db.opt
-rw-rw---- 1 mysql mysql 6.4K Dec 27 14:40 test_varchar.frm
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_varchar.ibd
-rw-rw---- 1 mysql mysql 465 Dec 27 14:40 test_text.frm
-rw-rw---- 1 mysql mysql 466 Dec 27 14:40 test_mediumtext.frm
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_text.ibd
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_mediumtext.ibd
drwxr-xr-x 8 mysql mysql 4.0K Jan 2 20:00 ..
-rw-rw---- 1 mysql mysql 228M Feb 5 15:33 json_test.ibd
-rw-rw---- 1 mysql mysql 601 Feb 5 15:37 json_test.frm
drwx------ 2 mysql mysql 206 Feb 5 15:37 .
MariaDB [test]> explain select count(*) from json_test where phone like '999%' ;
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | json_test | ALL | NULL | NULL | NULL | NULL | 2083498 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
MariaDB [test]> create index json_test_phone_idx on json_test(phone) ;
Query OK, 0 rows affected (18.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
인덱스 생성은 실제 인덱스가 생성 되므로 물리적 크기가 증가하고, 생성하는 시간이 소요된다.
MariaDB [test]> \! ls -altrh
total 277M
-rw-rw---- 1 mysql mysql 61 Dec 27 14:40 db.opt
-rw-rw---- 1 mysql mysql 6.4K Dec 27 14:40 test_varchar.frm
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_varchar.ibd
-rw-rw---- 1 mysql mysql 465 Dec 27 14:40 test_text.frm
-rw-rw---- 1 mysql mysql 466 Dec 27 14:40 test_mediumtext.frm
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_text.ibd
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_mediumtext.ibd
drwxr-xr-x 8 mysql mysql 4.0K Jan 2 20:00 ..
-rw-rw---- 1 mysql mysql 1.1K Feb 5 15:39 json_test.frm
-rw-rw---- 1 mysql mysql 276M Feb 5 15:40 json_test.ibd
drwx------ 2 mysql mysql 206 Feb 5 15:40 .
MariaDB [test]> explain select count(*) from json_test where phone like '999%' ;
+------+-------------+-----------+-------+---------------------+---------------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+---------------------+---------------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | json_test | range | json_test_phone_idx | json_test_phone_idx | 63 | NULL | 1041749 | Using where; Using index |
+------+-------------+-----------+-------+---------------------+---------------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
빈 칼럼을 추가해도 사이즈는 증가 함.
MariaDB [test]> alter table json_test add tempcol varchar(20) ;
Query OK, 0 rows affected (45.76 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> \! ls -altrh
total 309M
-rw-rw---- 1 mysql mysql 61 Dec 27 14:40 db.opt
-rw-rw---- 1 mysql mysql 6.4K Dec 27 14:40 test_varchar.frm
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_varchar.ibd
-rw-rw---- 1 mysql mysql 465 Dec 27 14:40 test_text.frm
-rw-rw---- 1 mysql mysql 466 Dec 27 14:40 test_mediumtext.frm
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_text.ibd
-rw-rw---- 1 mysql mysql 96K Dec 27 14:40 test_mediumtext.ibd
drwxr-xr-x 8 mysql mysql 4.0K Jan 2 20:00 ..
-rw-rw---- 1 mysql mysql 1.2K Feb 5 15:47 json_test.frm
-rw-rw---- 1 mysql mysql 308M Feb 5 15:48 json_test.ibd
drwx------ 2 mysql mysql 206 Feb 5 15:48 .