{"id":1533,"date":"2015-11-17T16:21:29","date_gmt":"2015-11-17T15:21:29","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1533"},"modified":"2024-12-10T15:46:23","modified_gmt":"2024-12-10T14:46:23","slug":"30-mins-avec-json-en-mysql","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2015\/11\/17\/30-mins-avec-json-en-mysql\/","title":{"rendered":"30 mins avec JSON en MySQL"},"content":{"rendered":"

Read this post in English<\/a><\/em><\/p>\n

Note<\/span>: Cet article est inspir\u00e9 de la pr\u00e9sentation\u00a0MySQL 5.7 + JSON<\/a> de\u00a0Morgan Tocker<\/a>.<\/em><\/p>\n

Note<\/span> 2: L’article suivant peut t\u2019int\u00e9resser \u00e9galement:\u00a030 mins avec les fonctions JSON de MySQL<\/a>.<\/em><\/p>\n

Note<\/em><\/span> 3: Tu peux \u00e9galement manipuler tes documents JSON avec MySQL Document Store<\/a>.<\/p>\n

Comme vous le savez MySQL 5.7<\/a> est GA<\/a>. Cette nouvelle mouture de la base de donn\u00e9es open source la plus populaire au monde a plus de 150 nouvelles fonctionnalit\u00e9s<\/a>. L’une d’entre elle est un type de donn\u00e9es JSON natif ainsi que les fonctions JSON associ\u00e9es<\/b>.<\/p>\n

Prenons 30 minutes pour voir ce que cela donne…<\/p>\n

R\u00e9cup\u00e9rer des documents JSON<\/h1>\n

Commen\u00e7ons pas r\u00e9cup\u00e9rer des donn\u00e9es au format JSON. Mirco Zeiss<\/a> fournit un gros fichier JSON qui repr\u00e9sente San Francisco (SF Open Data<\/a>) : https:\/\/github.com\/zemirco\/sf-city-lots-json<\/a><\/p>\n

Pour pouvoir manipuler ces donn\u00e9es avec MySQL, quelques modifications sont n\u00e9cessaires:<\/p>\n

$ grep \"^{ \\\"type\" citylots.json > properties.json\n\n$ head -n1 properties.json \n{  \n   \"type\":\"Feature\",\n   \"properties\":{  \n      \"MAPBLKLOT\":\"0001001\",\n      \"BLKLOT\":\"0001001\",\n      \"BLOCK_NUM\":\"0001\",\n      \"LOT_NUM\":\"001\",\n      \"FROM_ST\":\"0\",\n      \"TO_ST\":\"0\",\n      \"STREET\":\"UNKNOWN\",\n      \"ST_TYPE\":null,\n      \"ODD_EVEN\":\"E\"\n   },\n   \"geometry\":{  \n      \"type\":\"Polygon\",\n      \"coordinates\":[  \n         [  \n            [  \n               -122.422003528252475,\n               37.808480096967251,\n               0.0\n            ],\n            [  \n               -122.422076013325281,\n               37.808835019815085,\n               0.0\n            ],\n            [  \n               -122.421102174348633,\n               37.808803534992904,\n               0.0\n            ],\n            [  \n               -122.421062569067274,\n               37.808601056818148,\n               0.0\n            ],\n            [  \n               -122.422003528252475,\n               37.808480096967251,\n               0.0\n            ]\n         ]\n      ]\n   }\n}<\/pre>\n
\u00c7a \u00e0 l’air bon !<\/p>\n
Note: La taille des documents\u00a0JSON stock\u00e9s dans une colonne de type JSON est limit\u00e9e par la valeur de la variable syst\u00e8me max_allowed_packet<\/a>. (Cette limitation ne s’applique que lorsque le serveur stocke les documents. En m\u00e9moire la taille des document peut donc \u00eatre sup\u00e9rieure).
\n<\/em><\/p>\n
Notre document JSON sera stock\u00e9 dans la table InnoDB features<\/i>:<\/p>\n
CREATE TABLE features (\n id int(11) NOT NULL AUTO_INCREMENT,\n feature json NOT NULL,\n PRIMARY KEY (id)\n) ENGINE=InnoDB;<\/pre>\n
Une autre fa\u00e7on de stocker des documents JSON est de les mettre dans une colonne de type VARCHAR ou TEXT.<\/p>\n
La table features_TEXT<\/em> va nous permettre de comparer les performances des types JSON et TEXT.<\/p>\n
CREATE TABLE features_TEXT (\n id int(11) NOT NULL AUTO_INCREMENT,\n feature longtext NOT NULL,\n PRIMARY KEY (id)\n) ENGINE=InnoDB;<\/pre>\n
Note: Dans cet exemple le type\u00a0TEXT n’est pas suffisamment large pour g\u00e9r\u00e9 nos donn\u00e9es JSON . (ERROR 1406 (22001): Data too long for column ‘feature’ at row 17360). LONGTEXT fera donc l’affaire<\/em>.<\/p>\n
Insertion des donn\u00e9es dans les tables<\/h1>\n
Note: Le temps d’ex\u00e9cution d’une m\u00eame\u00a0requ\u00eate pouvant varier largement d’une ex\u00e9cution \u00e0 l’autre, sur mon (vieux) portable avec une petite configuration pour MySQL (e.g. Buffer pool = 128Mo). J’ai donc ex\u00e9cut\u00e9 les requ\u00eates plusieurs fois en utilisant mysqlslap<\/a>: <\/em><\/p>\n
mysqlslap -c1 -i <N> { Concurrence = 1 \/ It\u00e9ration > 20 (en fonction de la dur\u00e9e totale de la requ\u00eate) }.<\/em><\/p>\n
Le temps d’ex\u00e9cution sera donc repr\u00e9sent\u00e9 la plupart du temps sous la forme: \u00ab\u00a0Minimum number of seconds to run all queries: 59.392 seconds\u00a0\u00bb au lieu de \u00ab\u00a0Query OK, 206560 rows affected (59.39 sec)\u00a0\u00bb.<\/em><\/p>\n
Copier les donn\u00e9es JSON dans la table features<\/span><\/p>\n
LOAD DATA INFILE 'properties.json' INTO TABLE features (feature);<\/pre>\n
Minimum number of seconds to run all queries: 59.392 seconds<\/b><\/span><\/p>\n
Copier les donn\u00e9es JSON dans la table features_TEXT<\/span><\/p>\n
LOAD DATA INFILE 'properties.json' INTO TABLE features_TEXT (feature);<\/pre>\n
Minimum number of seconds to run all queries: 39.784 seconds<\/b><\/span><\/p>\n
Sur ma machine, charger 206560 enregistrements montre une diff\u00e9rence de performance d’environ 40%<\/strong> en faveur du type TEXT par rapport au type JSON.<\/p>\n
Cela s’explique par les fonctionnalit\u00e9s fournit par le type JSON de MySQL:<\/p>\n