Name: MySQL 8 Administration et optimisation
Rating: 4 (2 reviews)

Colonnes générées

1. Introduction

Les colonnes générées sont apparues avec MySQL 5.7. L’idée est très simple : il s’agit de colonnes dont les valeurs sont dérivées d’une ou plusieurs autres colonnes de la même table. Les colonnes générées peuvent être indexées, ce qui permet d’ajouter des index fonctionnels pour, par exemple :

Indexer des chaînes de caractères de droite à gauche plutôt que de gauche à droite.
Indexer seulement la date pour une colonne contenant à la fois la date et l’heure.
Indexer la somme de deux colonnes.
Indexer un champ spécifique d’un document JSON.

Ce dernier cas de figure est particulièrement intéressant : les colonnes générées vont prendre toute leur importance lorsqu’il va s’agir de rendre performantes les requêtes sur des documents JSON. C’est d’ailleurs pour cette raison que la description des colonnes générées est incluse dans ce chapitre.

MySQL propose deux types de colonnes générées : les colonnes virtuelles, dont les valeurs sont calculées à la volée et jamais stockées sur disque, et les colonnes persistantes, qui sont des colonnes classiques stockées sur disque, mais dont la valeur est calculée...

Support JSON

1. Le type de données JSON

Le format JSON (acronyme de JavaScript Object Notation ou notation objet de JavaScript) est devenu incontournable pour les développeurs web ces dernières années. Un document JSON est un document texte respectant quelques règles très simples et permettant de décrire un grand nombre de situations avec des couples clé/valeur. Il est alors facile d’utiliser des documents JSON pour la communication entre différents services applicatifs.

Voici un exemple simple de document JSON :

{  
 "mois": "Janvier",  
 "Recrutements": 200,  
 "Départs": 10  
}

Et un exemple un peu plus complexe pour lequel un champ contient un tableau de paires clé/valeur au lieu d’une simple valeur :

{  
 "CA": [  
   {"semaine": 1, "total": 10000},  
   {"semaine": 2, "total": 9900},  
   {"semaine": 2, "total": 11000}  
 ],  
 "mois": "Janvier"  
}

Les variations sont infinies, et vous pouvez consulter le site https://www.json.org/json-fr.html pour plus de détails sur le format JSON.

Le format XML a longtemps été le format texte favori pour les échanges...

Exemple d’opérations sur des colonnes JSON

De très nombreuses fonctions sont disponibles pour travailler avec les colonnes JSON. La liste complète de ces fonctions peut être consultée à cette page : https://dev.mysql.com/doc/refman/8.0/en/json-functions.html

Voyons seulement quelques exemples simples des possibilités offertes.

L’opération la plus simple consiste à filtrer sur un champ spécifique des documents JSON. Avec la table créée précédemment, si vous voulez compter le nombre d’enregistrements dont l’attribut nom vaut Jean, vous écrirez la requête suivante :

mysql> SELECT COUNT(*) FROM personnes WHERE details->"$.nom" = 
'Jean';   
+----------+   
| count(*) |   
+----------+   
|        1 |   
+----------+

Imaginez maintenant que vous souhaitiez retrouver la ville de toutes les personnes se nommant Jean. Un premier essai pourrait être :

mysql > SELECT details->"$.ville" FROM personnes  
WHERE details->"$.nom" = 'Jean';   
+--------------------+   
| details->"$.ville" |   
+--------------------+   
| "Paris"            |   
+--------------------+

Le nom de la ville est bien récupéré...

Document Store

1. SQL et NoSQL

À partir des années 1970, les bases de données relationnelles sont devenues les principaux systèmes de stockage de données. Ces bases reposent sur le modèle relationnel décrit à la fin des années 1960 par Codd. Pour simplifier, l’idée est de créer des conteneurs avec une structure prédéfinie qui seront appelés des tables, de stocker les données dans les tables sous forme de lignes respectant toutes le format de leur table, de structurer les tables en respectant une logique particulière qu’on appelle 3^e forme normale (voir le chapitre Optimisation) et d’avoir un langage de requêtage standardisé qui sera nommé SQL.

Dans ce modèle relationnel, la structuration des données en suivant la 3^eforme normale joue un rôle important en évitant la duplication de données. À l’origine, éviter la duplication de données avait deux rôles principaux :

Économiser l’espace disque.
Éviter les problèmes de cohérence des données. En effet, si une donnée existe en plusieurs endroits, il peut arriver qu’une modification ne soit pas effectuée partout. On se retrouve alors avec des données corrompues puisqu’il est en général impossible de savoir quelle est la bonne version des données.

Aujourd’hui, l’économie d’espace disque n’est plus un argument pertinent pour la 3^e forme normale. Par contre, l’absence de duplication des données est toujours une propriété intéressante. Évidemment, structurer les données en respectant la 3^e forme normale a quelques inconvénients, liés à l’éclatement des données en plusieurs tables. Il est alors bien souvent indispensable d’écrire les requêtes sous forme de jointures entre tables pour retrouver une information complète.

Pendant de nombreuses années, le recours aux jointures n’a pas posé de problème particulier : il suffisait de créer les bons index. Mais tout a changé dans les années 2000, avec en particulier l’émergence de sites web à très fort trafic et à très forte...

Support JSON et Document Store