30 mins avec JSON en MySQL

novembre 17, 2015

Note: Cet article est inspiré de la présentation MySQL 5.7 + JSON de Morgan Tocker.

Note 2: L’article suivant peut t’intéresser également: 30 mins avec les fonctions JSON de MySQL.

Note 3: Tu peux également manipuler tes documents JSON avec MySQL Document Store.

Comme vous le savez MySQL 5.7 est GA. Cette nouvelle mouture de la base de données open source la plus populaire au monde a plus de 150 nouvelles fonctionnalités. L’une d’entre elle est un type de données JSON natif ainsi que les fonctions JSON associées.

Prenons 30 minutes pour voir ce que cela donne…

Récupérer des documents JSON

Commençons pas récupérer des données au format JSON. Mirco Zeiss fournit un gros fichier JSON qui représente San Francisco (SF Open Data) : https://github.com/zemirco/sf-city-lots-json

Pour pouvoir manipuler ces données avec MySQL, quelques modifications sont nécessaires:

$ grep "^{ \"type" citylots.json > properties.json

$ head -n1 properties.json 
{  
   "type":"Feature",
   "properties":{  
      "MAPBLKLOT":"0001001",
      "BLKLOT":"0001001",
      "BLOCK_NUM":"0001",
      "LOT_NUM":"001",
      "FROM_ST":"0",
      "TO_ST":"0",
      "STREET":"UNKNOWN",
      "ST_TYPE":null,
      "ODD_EVEN":"E"
   },
   "geometry":{  
      "type":"Polygon",
      "coordinates":[  
         [  
            [  
               -122.422003528252475,
               37.808480096967251,
               0.0
            ],
            [  
               -122.422076013325281,
               37.808835019815085,
               0.0
            ],
            [  
               -122.421102174348633,
               37.808803534992904,
               0.0
            ],
            [  
               -122.421062569067274,
               37.808601056818148,
               0.0
            ],
            [  
               -122.422003528252475,
               37.808480096967251,
               0.0
            ]
         ]
      ]
   }
}

Ça à l’air bon !

Note: La taille des documents JSON stockés dans une colonne de type JSON est limitée par la valeur de la variable système max_allowed_packet. (Cette limitation ne s’applique que lorsque le serveur stocke les documents. En mémoire la taille des document peut donc être supérieure).

Notre document JSON sera stocké dans la table InnoDB features:

CREATE TABLE features (
 id int(11) NOT NULL AUTO_INCREMENT,
 feature json NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB;

Une autre façon de stocker des documents JSON est de les mettre dans une colonne de type VARCHAR ou TEXT.

La table features_TEXT va nous permettre de comparer les performances des types JSON et TEXT.

CREATE TABLE features_TEXT (
 id int(11) NOT NULL AUTO_INCREMENT,
 feature longtext NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB;

Note: Dans cet exemple le type TEXT n’est pas suffisamment large pour géré nos données JSON . (ERROR 1406 (22001): Data too long for column ‘feature’ at row 17360). LONGTEXT fera donc l’affaire.

Insertion des données dans les tables

Note: Le temps d’exécution d’une même requête pouvant varier largement d’une exécution à l’autre, sur mon (vieux) portable avec une petite configuration pour MySQL (e.g. Buffer pool = 128Mo). J’ai donc exécuté les requêtes plusieurs fois en utilisant mysqlslap:

mysqlslap -c1 -i <N> { Concurrence = 1 / Itération > 20 (en fonction de la durée totale de la requête) }.

Le temps d’exécution sera donc représenté la plupart du temps sous la forme: « Minimum number of seconds to run all queries: 59.392 seconds » au lieu de « Query OK, 206560 rows affected (59.39 sec) ».

Copier les données JSON dans la table features

LOAD DATA INFILE 'properties.json' INTO TABLE features (feature);

Minimum number of seconds to run all queries: 59.392 seconds

Copier les données JSON dans la table features_TEXT

LOAD DATA INFILE 'properties.json' INTO TABLE features_TEXT (feature);

Minimum number of seconds to run all queries: 39.784 seconds

Sur ma machine, charger 206560 enregistrements montre une différence de performance d’environ 40% en faveur du type TEXT par rapport au type JSON.

Cela s’explique par les fonctionnalités fournit par le type JSON de MySQL:

Validation automatique des documents JSON stockés. Tout document invalide produit une erreur.
Optimisation du stockage des données. Les documents JSON stockés dans des colonnes de type JSON sont convertis en un format interne qui permet un accès en lecture rapide.

Bien évidemment rien de comparable pour le type TEXT, ces fonctionnalités ont donc un coût lors de l’écriture dans la table, ce qui est plutôt logique.

Au niveau des méta-données:

SHOW TABLE STATUS LIKE 'features'\G
*************************** 1. row ***************************
          Name: features
        Engine: InnoDB
       Version: 10
    Row_format: Dynamic
          Rows: 184218
Avg_row_length: 1250
   Data_length: 230326272  #220 MB
  Index_length: 0
     Data_free: 3145728

SHOW TABLE STATUS LIKE 'features_TEXT'\G
*************************** 1. row ***************************
          Name: features_TEXT
        Engine: InnoDB
       Version: 10
    Row_format: Dynamic
          Rows: 188784
Avg_row_length: 1370
   Data_length: 258654208  #247 MB
  Index_length: 0
     Data_free: 4194304

Le point intéressant ici est que le type de données LONGTEXT consomme plus d’espace que le type JSON (au format de stockage optimisé) environ 20% plus.

Note: L’insertion des documents JSON peut également se faire avec une requête INSERT classique.

e.g. (le champs doc est de type JSON)

 INSERT INTO posts (doc) VALUES ('{"_id": "42bfbd1958a7e6119733dc53609b8b8d", "text": "My first post!", "title": "MySQL rocks"}');

Récupérer des données d’un document JSON

MySQL 5.7 fournit un ensemble de fonctions JSON.

Par exemple, JSON_EXTRACT renvoi des données d’un document JSON. A noter que, depuis MySQL 5.7.9 vous pouvez utiliser la syntaxe inlined JSON path expressions qui rend plus lisible les requêtes qui manipulent des données JSON:

e.g.

JSON_EXTRACT(col, « $.json_field ») est similaire à col->« $.json_field »

Table avec type de données JSON

SELECT DISTINCT feature->"$.type" AS json_extract FROM features\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: features
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 182309
    filtered: 100.00
       Extra: Using temporary

Minimum number of seconds to run all queries: 4.470 seconds

Table avec type de données TEXT

SELECT DISTINCT feature->"$.type" AS json_extract FROM features_TEXT\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: features_TEXT
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 177803
    filtered: 100.00
       Extra: Using temporary

Minimum number of seconds to run all queries: 29.365 seconds

Remarques

Récupérer les documents JSON implique, sans surprise, un Full Table Scan (FTS).

Cependant on peut constater la puissance du format interne JSON de MySQL qui permet un accès en lecture particulièrement rapide et efficace.

Dans cet exemple le temps d’exécution est environ 7 fois plus rapide (4.470 s vs 29.365 s) avec le type de données JSON comparé aux même données stockées dans une colonne de type TEXT.

Colonne générée (Generated Column)

Une colonne de type JSON ne peut être indexée. CEPENDANT il est possible de contourner cette restriction en créant un index sur une generated column qui permet d’extraire une valeur scalaire de la colonne JSON. Les generated columns peuvent être stockée / matérialisée (STORED) ou virtuelle / non matérialisée (VIRTUAL).

Créer une generated column virtuelle (VIRTUAL) est très rapide car les valeurs de la colonne ne sont pas stockées mais calculées à la volée lors de la lecture des enregistrements immédiatement après tout trigger BEFORE. Seules les méta-données sont modifiées en d’autres termes, il n’y a pas de reconstruction de la table.

En production, c’est en général une colonne générée virtuelle qui sera pertinente.

ALTER TABLE features ADD feature_type VARCHAR(30) AS (feature->"$.type") VIRTUAL;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE features_TEXT ADD feature_type VARCHAR(30) AS  (feature->"$.type") VIRTUAL;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

Note: Le désavantage de cette approche est que certaines données sont stockées 2 fois; dans la colonne générée et dans l’index.

Les nouvelles structures de tables sont:

CREATE TABLE features (
 id int(11) NOT NULL AUTO_INCREMENT,
 feature json NOT NULL,
 feature_type varchar(30) GENERATED ALWAYS AS (feature->"$.type") VIRTUAL,
 PRIMARY KEY (id)
) ENGINE=InnoDB

CREATE TABLE features_TEXT (
 id int(11) NOT NULL AUTO_INCREMENT,
 feature longtext NOT NULL,
 feature_type varchar(30) GENERATED ALWAYS AS (feature->"$.type") VIRTUAL,
 PRIMARY KEY (id)
) ENGINE=InnoDB

Au niveau des méta-données:

ANALYZE TABLE features, features_TEXT;

SHOW TABLE STATUS LIKE 'features'\G
*************************** 1. row ***************************
          Name: features
        Engine: InnoDB
       Version: 10
    Row_format: Dynamic
          Rows: 184218
Avg_row_length: 1250
   Data_length: 230326272  #220 MB
  Index_length: 0
     Data_free: 314572

SHOW TABLE STATUS LIKE 'features_TEXT'\G
*************************** 1. row ***************************
          Name: features_TEXT
        Engine: InnoDB
       Version: 10
    Row_format: Dynamic
          Rows: 188784
Avg_row_length: 1370
   Data_length: 258654208  #247 MB
  Index_length: 0
     Data_free: 4194304

Remarques

Identique !

Comme on le pressentait la taille des données n’a pas changée.

Y a t’il une différence de temps d’exécution entre récupérer des documents JSON à partir de la colonne virtuelle et à partir de la fonction JSON_EXTRACT ?

SELECT DISTINCT feature_type FROM features\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: features
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 195195
    filtered: 100.00
       Extra: Using temporary

Minimum number of seconds to run all queries: 2.790 seconds

SELECT DISTINCT feature_type FROM features_TEXT\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: features_TEXT
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 171004
    filtered: 100.00
       Extra: Using temporary

Minimum number of seconds to run all queries: 25.933 seconds

Remarques

Évidemment le plan d’exécution (QEP) est le même: FTS.

Cependant 2 commentaires:

Le type de données JSON de MySQL est encore plus efficace que le type de données TEXT, dans cet exemple le temps d’exécution est environ 8 fois plus rapide avec JSON.

Un FTS sur une generated column virtuelle (colonne: feature_type) est plus performant que l’utilisation, dans la clause du SELECT, de la fonction json_extract sur le document JSON (de 4.470 à 2.790).

Créer un index sur une colonne générée

A partir de MySQL 5.7.8, InnoDB supporte les index secondaires sur les colonnes virtuelles.

Ajouter ou supprimer un index secondaire dans une colonne virtuelle est une opération qui ne nécessite pas de recopier la table (in-place operation).

ALTER TABLE features ADD INDEX (feature_type);
Query OK, 0 rows affected (5.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE features_TEXT ADD INDEX (feature_type);
Query OK, 0 rows affected (27.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

La nouvelle structure des tables est:

CREATE TABLE features (
 id` int(11) NOT NULL AUTO_INCREMENT,
 feature` json NOT NULL,
 feature_type varchar(30) GENERATED ALWAYS AS (feature->"$.type") VIRTUAL,
 PRIMARY KEY (id),
 KEY feature_type (feature_type)
) ENGINE=InnoDB

CREATE TABLE features_TEXT (
 id int(11) NOT NULL AUTO_INCREMENT,
 feature longtext NOT NULL,
 feature_type varchar(30) GENERATED ALWAYS AS (feature->"$.type") VIRTUAL,
 PRIMARY KEY (id),
 KEY feature_type (feature_type)
) ENGINE=InnoDB

Au niveau des méta-données:

ANALYZE TABLE features, features_TEXT;

SHOW TABLE STATUS LIKE 'features'\G
*************************** 1. row ***************************
          Name: features
        Engine: InnoDB
       Version: 10
    Row_format: Dynamic
          Rows: 180400
Avg_row_length: 1276
   Data_length: 230326272  #220 MB
  Index_length: 5783552    #6 MB
     Data_free: 5242880

SHOW TABLE STATUS LIKE 'features_TEXT'\G
*************************** 1. row ***************************
          Name: features_TEXT
        Engine: InnoDB
       Version: 10
    Row_format: Dynamic
          Rows: 192445
Avg_row_length: 1344
   Data_length: 258654208  #247 MB
  Index_length: 5783552    #6 MB
     Data_free: 2097152

Remarque

L’index sur la colonne feature_type est matérialisé. Sa taille est approximativement 6 Mo.

Grâce à l’index, la requête devrait être plus efficace:

SELECT DISTINCT feature_type FROM features\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: features
        type: index
possible_keys: feature_type
         key: feature_type
     key_len: 33
         ref: NULL
        rows: 193763
    filtered: 100.00
       Extra: Using index

Minimum number of seconds to run all queries: 0.178 seconds

SELECT DISTINCT feature_type FROM features_TEXT\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: features_TEXT
        type: index
possible_keys: feature_type
         key: feature_type
     key_len: 33
         ref: NULL
        rows: 208134
    filtered: 100.00
       Extra: Using index

Minimum number of seconds to run all queries: 0.178 seconds

Comme prévu, l’optimiseur utilise l’index (feature_type) et la temps d’exécution de la requête est vraiment meilleur, et ce pour les 2 tables (de 2.790 à 0.178 pour la colonne JSON).

Pour conclure

MySQL 5.7 implémente le type de données JSON, ainsi qu’un ensemble de fonctions qui permet de Créer, Chercher, Modifier des valeurs JSON mais également de Renvoyer des valeur d’attributs JSON. C’est une superbe fonctionnalité et je suis persuadé que les développeurs sauront en faire bon usage.

La colonne générée (Generated Columns) est également une fonctionnalité très utile. Elle peut être utilisée, entre autre, pour indexer une fonction, ou comme cache pour expressions souvent utilisées, ou pour indexer du contenu XML… ou comme nous venons de le voir pour indexer des documents JSON.

MySQL 5.7 est vraiment une superbe version ! Essayez la, elle mérite définitivement plus de 30 minutes.

Vous voulez en savoir plus sur MySQL 5.7?

8 décember 2015, @Paris rejoignez nous sur l‘Oracle MySQL Tech Tour Paris
Other location: Oracle MySQL Tech Tour

Pour aller plus loin

30 mins avec les fonctions JSON de MySQL

MySQL Documentation

MySQL Server Blog

Native JSON Data Type and Binary Format

http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/

JSON functions

http://mysqlserverteam.com/json-labs-release-json-functions-part-1-manipulation-json-data/

http://mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/

https://mysqlserverteam.com/new-json-functions-in-mysql-5-7-22/

Inline JSON Path Expressions in MySQL 5.7

http://mysqlserverteam.com/inline-json-path-expressions-in-mysql-5-7/

Getting Started With MySQL & JSON on Windows

http://mysqlserverteam.com/getting-started-with-mysql-json-on-windows/

Effective Functional Indexes in InnoDB

http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/

MySQL 5.7

What Is New in MySQL 5.7

https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

Complete list of new features in MySQL 5.7

http://www.thecompletelistoffeatures.com/

Olivier DASINI

Architecte Solution Cloud chez Oracle
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–

Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–

4 Responses to “30 mins avec JSON en MySQL”

30 mins with JSON in MySQL | dasini.net - Journal d'un expert MySQL

janvier 11th, 2017 at 12:18

[…] Lire cet article en français […]
Oracle MySQL Tech Tour mai 2016 | dasini.net - Journal d'un expert MySQL

janvier 11th, 2017 at 12:22

[…] Type de données JSON […]
30 mins avec les fonctions JSON de MySQL | dasini.net - Journal d'un expert MySQL

août 6th, 2018 at 14:52

[…] Note: L’article suivant peut t’intéresser également: 30 mins avec JSON en MySQL. […]
200 !! | dasini.net - Journal d'un expert MySQL

avril 9th, 2019 at 11:06

[…] 30 mins avec JSON en MySQL […]

dasini.net – Journal d'un expert MySQL

30 mins avec JSON en MySQL

Récupérer des documents JSON

Insertion des données dans les tables

Récupérer des données d’un document JSON

Colonne générée (Generated Column)

Créer un index sur une colonne générée

Pour conclure

Pour aller plus loin

MySQL Documentation

The JSON data type

JSON Functions

CREATE TABLE and Generated Columns

MySQL Server Blog

Native JSON Data Type and Binary Format

JSON functions

Inline JSON Path Expressions in MySQL 5.7

Getting Started With MySQL & JSON on Windows

Effective Functional Indexes in InnoDB

MySQL 5.7

What Is New in MySQL 5.7

Complete list of new features in MySQL 5.7

Articles similaires

4 Responses to “30 mins avec JSON en MySQL”

Subscribe via Email

Articles récents

Archives

MySQL Blogs

dasini.net – Journal d'un expert MySQL

30 mins avec JSON en MySQL

Récupérer des documents JSON

Insertion des données dans les tables

Récupérer des données d’un document JSON

Colonne générée (Generated Column)

Créer un index sur une colonne générée

Pour conclure

Pour aller plus loin

MySQL Documentation

The JSON data type

JSON Functions

CREATE TABLE and Generated Columns

MySQL Server Blog

Native JSON Data Type and Binary Format

JSON functions

Inline JSON Path Expressions in MySQL 5.7

Getting Started With MySQL & JSON on Windows

Effective Functional Indexes in InnoDB

MySQL 5.7

What Is New in MySQL 5.7

Complete list of new features in MySQL 5.7

Articles similaires

4 Responses to “30 mins avec JSON en MySQL”

Subscribe via Email

Articles récents

Archives

MySQL Blogs

Étiquettes