Retour sur le meetup – MySQL Group Replication & MySQL as a Document Store

septembre 7, 2016

Tags: group replication, ha, JSON, meetup, NoSQL

Ce mardi 6 septembre, un superbe meetup MySQL c’est déroulé à Paris dans les locaux d’ Executive MBA Epitech à Paris. Organisé et sponsorisé par Oracle MySQL et Openska.

Un grand merci à tous les participants, aux organisateurs et au sponsor (Oracle).

Merci également à Frédéric (EMEA MySQL Community Manager) et à Dimitri (MySQL Performance Architect).

Les présentations de la soirée:

MySQL Group Replication – Haute Disponibilité avec Multi-Masters

de Frédéric Descamps

MySQL 5.7 & JSON – Nouvelles Opportunités pour les Développeurs

de Frédéric Descamps

Vous pouvez également les récupérer le blog de Frédéric.

Pour aller plus loin

MySQL Group Replication

MySQL Group Replication: A Quick Start Guide
- http://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/
MySQL Group Replication: Extended Technical Documentation
- http://mysqlhighavailability.com/mysql-group-replication-extended-technical-documentation/
MySQL Group Replication for MySQL 5.7.15
- http://mysqlhighavailability.com/mysql-group-replication-for-mysql-5-7-15/
MySQL Group Replication: Important Behavior and Configuration Change
- http://mysqlhighavailability.com/mysql-group-replication-important-behavior-and-configuration-changes/
Tuning MySQL Group Replication for fun… and profit!
- http://mysqlhighavailability.com/tuning-mysql-group-replication-for-fun-and-profit/
MySQL Group Replication: securing the perimeter!
- http://mysqlhighavailability.com/mysql-group-replication-securing-the-perimeter/
Gone missing: the day a few group members went silent!
- http://mysqlhighavailability.com/gone-missing-the-day-a-few-group-members-went-silent/

MySQL as a Document Store

Develop By Example – New MySQL Document Store Series
- http://insidemysql.com/develop-by-example-new-mysql-document-store-series/
Develop By Example – Document Store Connections using Node.js
- http://insidemysql.com/develop-by-example-document-store-connections-using-node-js/
Develop By Example – Creating schemas and collections using Node.js
- http://insidemysql.com/develop-by-example-creating-schemas-and-collections-using-node-js/
Develop By Example – Document Store: working with collections using Node.js
- http://insidemysql.com/develop-by-example-document-store-working-with-collections-using-node-js/
Develop By Example – Document Store: Working with documents using Node.js
- http://insidemysql.com/develop-by-example-document-store-working-with-documents-using-node-js/
Develop By Example – Working with custom queries using Node.js
- http://insidemysql.com/develop-by-example-working-with-custom-queries-using-node-js/

Et encore beaucoup de bonnes choses à venir 🙂 alors restez à l’écoute !

En bonus quelques photos:

https://plus.google.com/photos/114583255370443901762/albums/6327640843829307985

Thank you for using MySQL!

Filed under:
Conférence, Group Replication, json, Meetup, MySQL, NoSQL, Présentation by Olivier DASINI

Meetup – MySQL Group Replication & MySQL as a Document Store

août 29, 2016

Tags: group replication, meetup, NoSQL

Oracle MySQL, Openska et Executive MBA Epitech ont le plaisir de vous inviter le mardi 6 septembre pour le premier meetup MySQL de la rentrée.

Au programme… du lourd, rien que du lourd 🙂

MySQL Group Replication: Haute Disponibilité avec Multi-Masters

Vous souhaitez avoir une architecture de base de données distribuée et hautement disponible ?
MySQL Group Replication est un plugin pour MySQL 5.7 qui fournit de la réplication (virtuellement) synchrone avec detection et gestion de conflits intégré. Ce plugin permet de passer facilement d’une architecture mono-serveur à une architecture distribuée multi-maîtres hautement disponible.

Venez découvrir les concepts ainsi que la meilleure façon de déployer une telle architecture.
http://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/

Using MySQL as a Document Store: JSON Datatype & NoSQL

Faire du SQL et du NoSQL avec la même base de données est-ce possible ?
La réponse est oui avec MySQL!!!
A partir de MySQL 5.7.12, vous pouvez utiliser MySQL comme un « Document Store » (Un document est un ensemble de paires (clé/valeur) stocké par MySQL dans un format JSON binaire). Et cerise sur le gateau, les attributs du document JSON peuvent être indexés.
De plus, en utilisant le tout nouveau X DevAPI, il est également possible de gérer ces documents via une interface CRUD ou un interface SQL traditionnelle.
https://dev.mysql.com/doc/refman/5.7/en/document-store.html

Intervenants:

Frederic Descamps : EMEA MySQL Community Manager

Olivier Dasini : MySQL Principal Solutions Architect EMEA

Informations et inscription:
http://www.meetup.com/OpenTech/events/232136370/

Update {7 septembre 2016}

Présentation et autres infos

Filed under:
Conférence, Group Replication, json, Meetup, MySQL, NoSQL, Présentation by Olivier DASINI

Oracle MySQL Tech Tour mai 2016

avril 27, 2016

Tags: conférences, group replication, ha, JSON, MySQL Cluster, MySQL replication, NoSQL, TDE

Le 10 Mai 2016, l’Oracle MySQL Tech Tour passe par Paris !
Venez rencontrer l’équipe MySQL France et discuter des nouveautés de MySQL 5.7 comme :

La haute disponibilité
Type de données JSON
MySQL Cluster
Combiner relationnel et NoSQL avec notre modèle hybride
Transparent Data Encryption
Replication multi source
Group replication
…

et bien d’autres choses.

Pour vous inscrire et connaitre le programme complet Cliquer sur le lien ci-dessous :

https://eventreg.oracle.com/profile/web/index.cfm?PKWebId=0x3469984798

Oracle MySQL Tech Tour

Date : mardi 10 mai 2016

Lieu : Remix Coworking – 24 cour des Petites Ecuries 75010

Horaire : 14:00 – 17:15

Entrée libre

Commentaires fermés

Filed under:
Conférence by Olivier DASINI

MySQL en tant que Document Store

avril 21, 2016

Tags: Document, JSON, NoSQL

Mise à jour – 12/04/2019
Je te recommande la lecture (en anglais) de MySQL JSON Document Store.

A partir de MySQL 5.7.12, la nouvelle version de la base de données la plus populaire permet aux devs et aux DBAs de déployer des bases MySQL qui implémentent un modèle document store, relationnel ou hybride (document ET relationnel) !
Plus d’info: http://dev.mysql.com/doc/refman/5.7/en/document-store.html

Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

Commentaires fermés

Filed under:
MySQL by Olivier DASINI

30 mins avec MySQL Query Rewriter

mars 2, 2016

Tags: MySQL 5.7, Rewrite plugin

Read this post in English

TL;TR

Parfois des requêtes problématiques tournent sur le serveur, mais il n’est pas possible de régler le problème à la source (requêtes venant d’un ORM par example )

MySQL 5.7 fournit :

Une API pre et post parse query rewrite
- Les utilisateurs peuvent écrire leurs propre plugins
- Permet d’éliminer le besoin d’un proxy
Avec le post-parse query plugin, il est possible :
- De réécrire une requête problématique sans faire de changement au niveau de l’application
- Ajouter des hints pour les index ou pour l’optimiseur
- Modifier l’ordre des jointures
- …

API du Plugin Query Rewrite

Pour citer cet article du MySQL Server Blog :

MySQL now offer two APIs for writing query rewrite plugins.

– Pre-parse rewrite plugin API, is for when you know exactly – as in character-by-character exactly – what the offending queries look like. This one has a hook to intercept the query string right before it’s parsed.

– Post-parse rewrite plugin API, comes in right after parsing and acts on the parse tree. It offers the basic functionality to walk over the parsed query, which is a lot more efficient than dealing with a string.

Cet article traite du sujet Rewriter plugin, un post-parse query rewrite plugin, inclus dans la distribution MySQL 5.7 (à partir de la version MySQL 5.7.6).

Le plugin Rewriter

Installation et vérifications

La simplicité fait partie de la philosophie MySQL, l’installation du plugin n’y déroge pas.

Pour installer le plugin Rewriter, il faut lancer le script install_rewriter.sql localisé dans le répertoire share de votre installation MySQL.

~ $ mysql -u root -p < install_rewriter.sql

1	~ $ mysql -u root -p < install_rewriter.sql

Pour vérifier:

mysql> SELECT * FROM mysql.plugin;
+--------------------------+-------------+
| name                     | dl          |
+--------------------------+-------------+
| rewriter                 | rewriter.so |
+--------------------------+-------------+

mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
| rewriter_verbose | 1     |
+------------------+-------+

mysql> SHOW GLOBAL STATUS LIKE 'rewriter%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Rewriter_number_loaded_rules      | 0     |
| Rewriter_number_reloads           | 1     |
| Rewriter_number_rewritten_queries | 0     |
| Rewriter_reload_error             | OFF   |
+-----------------------------------+-------+

mysql> SELECT * FROM mysql.plugin;

+--------------------------+-------------+

| name | dl |

+--------------------------+-------------+

| rewriter | rewriter.so |

+--------------------------+-------------+

mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter%';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| rewriter_enabled | ON |

| rewriter_verbose | 1 |

+------------------+-------+

mysql> SHOW GLOBAL STATUS LIKE 'rewriter%';

+-----------------------------------+-------+

| Variable_name | Value |

+-----------------------------------+-------+

| Rewriter_number_loaded_rules | 0 |

| Rewriter_number_reloads | 1 |

| Rewriter_number_rewritten_queries | 0 |

| Rewriter_reload_error | OFF |

+-----------------------------------+-------+

De nouveaux objets ont été créés

# Stored procedure

mysql> SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA='query_rewrite';
+---------------------+--------------+
| ROUTINE_NAME        | ROUTINE_TYPE |
+---------------------+--------------+
| flush_rewrite_rules | PROCEDURE    |
+---------------------+--------------+


# UDF

mysql> SELECT name, dl, type FROM mysql.func 
WHERE name LIKE '%rewrite%';
+--------------------+-------------+----------+
| name               | dl          | type     |
+--------------------+-------------+----------+
| load_rewrite_rules | rewriter.so | function |
+--------------------+-------------+----------+



# New table is created in a new schema

mysql> SHOW TABLES IN query_rewrite;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules           |
+-------------------------+

# Stored procedure

mysql> SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES

WHERE ROUTINE_SCHEMA='query_rewrite';

+---------------------+--------------+

| ROUTINE_NAME | ROUTINE_TYPE |

+---------------------+--------------+

| flush_rewrite_rules | PROCEDURE |

+---------------------+--------------+

# UDF

mysql> SELECT name, dl, type FROM mysql.func

WHERE name LIKE '%rewrite%';

+--------------------+-------------+----------+

| name | dl | type |

+--------------------+-------------+----------+

| load_rewrite_rules | rewriter.so | function |

+--------------------+-------------+----------+

# New table is created in a new schema

mysql> SHOW TABLES IN query_rewrite;

+-------------------------+

| Tables_in_query_rewrite |

+-------------------------+

| rewrite_rules |

+-------------------------+

La table rewrite_rules du schéma query_rewrite fournie un stockage persistent des règles que le plugin Rewriter utilise pour décider quelles requêtes réécrire.

mysql> SHOW CREATE TABLE query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
       Table: rewrite_rules
Create Table: CREATE TABLE `rewrite_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pattern` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `pattern_database` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `replacement` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `enabled` enum('YES','NO') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'YES',
  `message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `pattern_digest` varchar(32) DEFAULT NULL,
  `normalized_pattern` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> SHOW CREATE TABLE query_rewrite.rewrite_rules\G

*************************** 1. row ***************************

Table: rewrite_rules

Create Table: CREATE TABLE `rewrite_rules` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`pattern` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

`pattern_database` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`replacement` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

`enabled` enum('YES','NO') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'YES',

`message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`pattern_digest` varchar(32) DEFAULT NULL,

`normalized_pattern` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Le plugin peut être activé ou désactivé à chaud :

mysql> SET GLOBAL rewriter_enabled = ON;

mysql> SET GLOBAL rewriter_enabled = OFF;

mysql> SET GLOBAL rewriter_enabled = ON;

mysql> SET GLOBAL rewriter_enabled = OFF;

Il est également possible de l’activer par l’intermédiaire du fichier de configuration (my.cnf | my.ini)

[mysqld]

rewriter_enabled = ON

[mysqld]

rewriter_enabled = ON

Réécrire une requête

Le plugin post-parse rewrite ne fonctionne qu’avec les requêtes SELECT.

Toutes requêtes autres que SELECT génèrent lors du flush des règles, dans la colonne de la table rewrite_rules.message le message d’erreur suivant:

          message: Pattern needs to be a select statement.

1	message: Pattern needs to be a select statement.

Le plugin Rewriter est facile à utiliser. Commençons par un exemple simple (voir simpliste) histoire de se faire la main. Transformons un SELECT n en un SELECT n+1 (n étant un entier).

Patterns

-> SELECT 1 # Input

<= SELECT 2 # Output

Ajouter la règle de réécriture

Pour ajouter une règle dans le plugin Rewriter, il faut ajouter des enregistrements dans la table rewrite_rules.

mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'SELECT ? + 1');

1	mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'SELECT ? + 1');

Le contenu de la table rewrite_rules est:

mysql> SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
               id: 1
          pattern: SELECT ?
 pattern_database: NULL
      replacement: SELECT ? + 1
          enabled: YES
          message: NULL
    pattern_digest: NULL
normalized_pattern: NULL

mysql> SELECT * FROM query_rewrite.rewrite_rules\G

*************************** 1. row ***************************

id: 1

pattern: SELECT ?

pattern_database: NULL

replacement: SELECT ? + 1

enabled: YES

message: NULL

pattern_digest: NULL

normalized_pattern: NULL

Les stats du Rewriter montrent:

mysql> SHOW GLOBAL status LIKE '%rewriter%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Rewriter_number_loaded_rules      | 0     |
| Rewriter_number_reloads           | 1     |
| Rewriter_number_rewritten_queries | 0     |
| Rewriter_reload_error             | OFF   |
+-----------------------------------+-------+

mysql> SHOW GLOBAL status LIKE '%rewriter%';

+-----------------------------------+-------+

| Variable_name | Value |

+-----------------------------------+-------+

| Rewriter_number_loaded_rules | 0 |

| Rewriter_number_reloads | 1 |

| Rewriter_number_rewritten_queries | 0 |

| Rewriter_reload_error | OFF |

+-----------------------------------+-------+

Flusher la règle de réécriture

Ensuite il faut appeler la procédure stockée flush_rewrite_rules() pour charger les règles dans le plugin.

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected (0,01 sec)

1 2	mysql> CALL query_rewrite.flush_rewrite_rules(); Query OK, 0 rows affected (0,01 sec)

Lorsque le plugin charge les règles, il génère, entre autres, une forme normalisée de la requête ainsi qu’une valeur hash:

mysql> SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
               id: 1
          pattern: SELECT ?
 pattern_database: NULL
      replacement: SELECT ? + 1
          enabled: YES
          message: NULL
    pattern_digest: 2c7e64d74a4f06d8ceff62d23ae9180c
normalized_pattern: select ?

mysql> SELECT * FROM query_rewrite.rewrite_rules\G

*************************** 1. row ***************************

id: 1

pattern: SELECT ?

pattern_database: NULL

replacement: SELECT ? + 1

enabled: YES

message: NULL

pattern_digest: 2c7e64d74a4f06d8ceff62d23ae9180c

normalized_pattern: select ?

Le ? agit comme un ‘marqueur’ qui correspond aux données de la requête. Il ne peut être utilisé que pour les données, pas pour les mots clés SQL, ni les identifieurs,… De plus le ? ne doit pas être entre guillemets ou apostrophes.

Si le parsing de la requête échoue, la procédure stockée va générée une erreur:

mysql> CALL query_rewrite.flush_rewrite_rules();
ERROR 1644 (45000): Loading of some rule(s) failed.

1 2	mysql> CALL query_rewrite.flush_rewrite_rules(); ERROR 1644 (45000): Loading of some rule(s) failed.

Vous trouverez plus de détails dans la colonne query_rewrite.rewrite_rules.message.

Exemple

message: Parse error in replacement: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

1	message: Parse error in replacement: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

Les stats du Rewriter montrent:

mysql> SHOW GLOBAL status LIKE '%rewriter%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Rewriter_number_loaded_rules      | 1     |
| Rewriter_number_reloads           | 2     |
| Rewriter_number_rewritten_queries | 0     |
| Rewriter_reload_error             | OFF   |
+-----------------------------------+-------+

mysql> SHOW GLOBAL status LIKE '%rewriter%';

+-----------------------------------+-------+

| Variable_name | Value |

+-----------------------------------+-------+

| Rewriter_number_loaded_rules | 1 |

| Rewriter_number_reloads | 2 |

| Rewriter_number_rewritten_queries | 0 |

| Rewriter_reload_error | OFF |

+-----------------------------------+-------+

Requête réécrite

Petit état des lieux. Nous avons, ajouté, puis flushé la règle. On peut donc maintenant exécuter une requête qui correspond au pattern et voir le résultat…

mysql> SELECT 1;
+-------+
| 1 + 1 |
+-------+
|     2 |
+-------+
1 row in set, 1 warning (0,00 sec)

mysql> SELECT 1;

+-------+

| 1 + 1 |

+-------+

| 2 |

+-------+

1 row in set, 1 warning (0,00 sec)

Magique !!!

La requête à été réécrite « à la volée ». Cependant, quelques détails intéressants sont cachés dans le warning.

mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------+
| Level | Code | Message                                                                |
+-------+------+------------------------------------------------------------------------+
| Note  | 1105 | Query 'select 1' rewritten to 'SELECT 1 + 1' by a query rewrite plugin |
+-------+------+------------------------------------------------------------------------+

mysql> SHOW WARNINGS;

+-------+------+------------------------------------------------------------------------+

| Level | Code | Message |

+-------+------+------------------------------------------------------------------------+

| Note | 1105 | Query 'select 1' rewritten to 'SELECT 1 + 1' by a query rewrite plugin |

+-------+------+------------------------------------------------------------------------+

Les stats du Rewriter ont été mis à jour en conséquence:

mysql> SHOW GLOBAL status LIKE '%rewriter%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Rewriter_number_loaded_rules      | 1     |
| Rewriter_number_reloads           | 2     |
| Rewriter_number_rewritten_queries | 1     |
| Rewriter_reload_error             | OFF   |
+-----------------------------------+-------+

mysql> SHOW GLOBAL status LIKE '%rewriter%';

+-----------------------------------+-------+

| Variable_name | Value |

+-----------------------------------+-------+

| Rewriter_number_loaded_rules | 1 |

| Rewriter_number_reloads | 2 |

| Rewriter_number_rewritten_queries | 1 |

| Rewriter_reload_error | OFF |

+-----------------------------------+-------+

Pour désactiver une règle existante, il suffit de modifier la colonne enabled et recharger la table dans le plugin:

mysql> UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;

mysql> CALL query_rewrite.flush_rewrite_rules();

mysql> UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;

mysql> CALL query_rewrite.flush_rewrite_rules();

En passant il est possible de supprimer les enregistrements de la table:

mysql> TRUNCATE TABLE query_rewrite.rewrite_rules;  # Delete all the rows (all the rules)

mysql> CALL query_rewrite.flush_rewrite_rules();

mysql> TRUNCATE TABLE query_rewrite.rewrite_rules; # Delete all the rows (all the rules)

mysql> CALL query_rewrite.flush_rewrite_rules();

OK! maintenant que le concept est compris, voyons des exemples plus pertinents.

Exemples de réécritures avec Rewriter

Ex 1

Réécrire un jointure en sous-requête; Pour des raisons de performance, la requête doit être réécrite mais vous n’avez pas accès au coté applicatif.

Patterns

-> SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) < {integer};

<= SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) < {integer});

Sans la règle

SELECT count(distinct emp_no) 
FROM employees.employees INNER JOIN employees.salaries USING(emp_no) 
WHERE DATEDIFF(to_date, from_date) < 2000;
+------------------------+
| count(distinct emp_no) |
+------------------------+
|                 300024 |
+------------------------+

1 row in set (12,93 sec)

SELECT count(distinct emp_no)

FROM employees.employees INNER JOIN employees.salaries USING(emp_no)

WHERE DATEDIFF(to_date, from_date) < 2000;

+------------------------+

| count(distinct emp_no) |

+------------------------+

| 300024 |

+------------------------+

1 row in set (12,93 sec)

Ajout de la règle de réécriture

INSERT INTO query_rewrite.rewrite_rules 
(
pattern, 
replacement
) 
VALUES
(
'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) < ?', 
'SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) < ?)'
);


CALL query_rewrite.flush_rewrite_rules();

INSERT INTO query_rewrite.rewrite_rules

(

pattern,

replacement

)

VALUES

(

'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) < ?',

'SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) < ?)'

);

CALL query_rewrite.flush_rewrite_rules();

Avec la règle

SELECT count(distinct emp_no) 
FROM employees.employees INNER JOIN employees.salaries USING(emp_no) 
WHERE DATEDIFF(to_date, from_date) < 2000;
+---------------+
| count(emp_no) |
+---------------+
|        300024 |
+---------------+

1 row in set, 1 warning (3,77 sec)

SELECT count(distinct emp_no)

FROM employees.employees INNER JOIN employees.salaries USING(emp_no)

WHERE DATEDIFF(to_date, from_date) < 2000;

+---------------+

| count(emp_no) |

+---------------+

| 300024 |

+---------------+

1 row in set, 1 warning (3,77 sec)

Le temps d’exécution de la requête est passé de 12.93 à 3.77 secondes

SHOW WARNINGS;
*************************** 1. row ***************************
 Level: Note
  Code: 1105
Message: Query 'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) < 2000' rewritten to 'SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) < 2000)' by a query rewrite plugin

SHOW WARNINGS;

*************************** 1. row ***************************

Level: Note

Code: 1105

Message: Query 'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) < 2000' rewritten to 'SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) < 2000)' by a query rewrite plugin

Remarque

Il y a quelques années, j’ai écris cet article Jointure vs sous-requête où je compare les temps d’exécution d’un jointure et de son équivalent en sous-requête. L’optimiseur s’est bien amélioré depuis la version 5.5.

Ex 2

Borner le temps d’exécution maximum d’une requête; ie ajouter le hint MySQL 5.7 /*+ MAX_EXECUTION_TIME(X)*/ c’est à dire que le temps d’exécution de la requête ne pourra exeder X millisecondes.

Patterns

-> SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = {integer};

<= SELECT /*+ MAX_EXECUTION_TIME(10000)*/ count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = {integer};

Sans la règle

SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 110000;
1 row in set (11,82 sec)

SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 70000;
1 row in set (9,22 sec)

SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 110000;

1 row in set (11,82 sec)

SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 70000;

1 row in set (9,22 sec)

Ajout de la règle de réécriture

INSERT INTO query_rewrite.rewrite_rules 
(
pattern, 
replacement, 
pattern_database
) 
VALUES
(
'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = ?', 
'SELECT /*+ MAX_EXECUTION_TIME(10000)*/ count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary =  ?', 
'employees'
);


CALL query_rewrite.flush_rewrite_rules();

INSERT INTO query_rewrite.rewrite_rules

(

pattern,

replacement,

pattern_database

)

VALUES

(

'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = ?',

'SELECT /*+ MAX_EXECUTION_TIME(10000)*/ count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = ?',

'employees'

);

CALL query_rewrite.flush_rewrite_rules();

Avec la règle

SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 110000;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 70000;
1 row in set (9,85 sec)

SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 110000;

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 70000;

1 row in set (9,85 sec)

Ex 3

Evolution du schema de la base après une MEP; ie Une colonne est ajoutée (ou supprimée) mais vous ne pouvez pas modifier la/les requête(s) qui utilise(nt) cette table.

Patterns

-> SELECT first_name, last_name FROM employees.employees WHERE year(hire_date) = 2000;

<= SELECT first_name, last_name, birth_date FROM employees.employees WHERE year(hire_date) = 2000;

Sans la règle

SELECT 
    first_name, 
    last_name 
FROM employees.employees WHERE year(hire_date) = 2000;
+-------------+------------+
| first_name  | last_name  |
+-------------+------------+
| Ulf         | Flexer     |

...
13 rows in set (0,01 sec)

SELECT

first_name,

last_name

FROM employees.employees WHERE year(hire_date) = 2000;

+-------------+------------+

| first_name | last_name |

+-------------+------------+

| Ulf | Flexer |

...

13 rows in set (0,01 sec)

Ajout de la règle de réécriture

INSERT INTO query_rewrite.rewrite_rules 
(
pattern, 
replacement, 
pattern_database
) 
VALUES
(
'SELECT first_name, last_name FROM employees.employees WHERE year(hire_date) = ?', 
'SELECT first_name, last_name, birth_date FROM employees.employees WHERE year(hire_date) =  ?', 
'employees'
);

CALL query_rewrite.flush_rewrite_rules();

INSERT INTO query_rewrite.rewrite_rules

(

pattern,

replacement,

pattern_database

)

VALUES

(

'SELECT first_name, last_name FROM employees.employees WHERE year(hire_date) = ?',

'SELECT first_name, last_name, birth_date FROM employees.employees WHERE year(hire_date) = ?',

'employees'

);

CALL query_rewrite.flush_rewrite_rules();

Avec la règle

SELECT 
    first_name, 
    last_name 
FROM employees.employees WHERE year(hire_date) = 2000;
+-------------+------------+------------+
| first_name  | last_name  | birth_date |
+-------------+------------+------------+
| Ulf         | Flexer     | 1960-09-09 |
...

13 rows in set (0,01 sec)

SELECT

first_name,

last_name

FROM employees.employees WHERE year(hire_date) = 2000;

+-------------+------------+------------+

| first_name | last_name | birth_date |

+-------------+------------+------------+

| Ulf | Flexer | 1960-09-09 |

...

13 rows in set (0,01 sec)

D’autres idées ?

– Index hints : l’optimiseur n’utilisant pas le bon index, vous pouvez réécrire la requête en ajoutant les mots clés [USE | FORCE | IGNORE] INDEX

– Limiter la taille du résultat d’un SELECT, vous pouvez réécrire la requête en ajoutant la clause LIMIT 1000 (ou plus, ou moins).

En fait cela simule l’option –safe-update du client text mysql

 $ ./mysql --help | grep dummy
  -U, --i-am-a-dummy  Synonym for option --safe-updates, -U.

1 2	$ ./mysql --help \| grep dummy -U, --i-am-a-dummy Synonym for option --safe-updates, -U.

…

Sky is the limit 🙂 donc à vous de jouer maintenant !

A noter que le plugin Rewriter ne remplace pas un code optimal et des requêtes correctement optimisées à la source…

Cependant, ce plugin peut vraiment être utile lorsque l’accès à la source est compliqué voir impossible.

Il est disponible dans MySQL 5.7 qui est vraiment une superbe version ! Essayer le plugin Rewriter il mérite définitivement plus de 30 minutes.

Pour aller plus loin

Post-parse query rewrite plugin

Pre-parse query rewrite plugin

Thank you for using MySQL!

Commentaires fermés

Filed under:
MySQL, Rewrite plugin by Olivier DASINI

JSON et colonnes générées avec MySQL

novembre 30, 2015

Tags: generated column, JSON, NoSQL

JSON

Le 24 novembre dernier, lors du Forum PHP, Tomas Ulin (Oracle’s MySQL VP of Engineering) a parlé de l’utilisation de JSON dans MySQL « MySQL 5.7 & JSON: New opportunities for developers« .

Voici les réponses à quelques questions qui m’ont été posées:

Comment se comporte mysqldump avec les colonnes générées ?
Comment utiliser la commande LOAD DATA INFILE avec des colonnes générées ?
JSON est il sensible à la casse dans MySQL ?

Le contexte, une table InnoDB catalog qui contient un champs doc de type JSON ainsi que des colonnes générées virtuelles isbn & publisher:

CREATE TABLE `catalog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  `isbn` varchar(1000) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,
  `publisher` varchar(1000) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `isbn` (`isbn`)
) ENGINE=InnoDB;

CREATE TABLE `catalog` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`doc` json DEFAULT NULL,

`isbn` varchar(1000) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,

`publisher` varchar(1000) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,

PRIMARY KEY (`id`),

UNIQUE KEY `isbn` (`isbn`)

) ENGINE=InnoDB;

Quelques documents JSON :

{
    "isbn": "978-2-7460-7864-2",
    "title": "MySQL 5.6 Administration et optimisation",
    "publisher": "ENI",
    "language": "FR"
}, {
    "isbn": "978-2212126341",
    "title": "Audit et optimisation de MySQL 5",
    "publisher": "Eyrolles",
    "language": "FR"
}, {
    "isbn": "978-2-7460-5516-2",
    "title": "MySQL 5 Administration et optimisation",
    "publisher": "ENI",
    "language": "FR"
}

{

"isbn": "978-2-7460-7864-2",

"title": "MySQL 5.6 Administration et optimisation",

"publisher": "ENI",

"language": "FR"

}, {

"isbn": "978-2212126341",

"title": "Audit et optimisation de MySQL 5",

"publisher": "Eyrolles",

"language": "FR"

}, {

"isbn": "978-2-7460-5516-2",

"title": "MySQL 5 Administration et optimisation",

"publisher": "ENI",

"language": "FR"

}

à insérer dans la table catalog:

mysql> INSERT  catalog (doc) VALUES 
('{"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "publisher": "ENI", "language": "FR"}'), 
('{"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "publisher": "Eyrolles", "language": "FR"}'), 
('{"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "publisher": "ENI", "language": "FR"}');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT catalog (doc) VALUES

('{"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "publisher": "ENI", "language": "FR"}'),

('{"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "publisher": "Eyrolles", "language": "FR"}'),

('{"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "publisher": "ENI", "language": "FR"}');

Query OK, 3 rows affected (0.06 sec)

Records: 3 Duplicates: 0 Warnings: 0

La table catalog contient donc 3 enregistrements:

mysql> SELECT * FROM catalog;
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
| id | doc                                                                                                                      | isbn                | publisher  |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
|  1 | {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-7864-2" | "ENI"      |
|  2 | {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"}       | "978-2212126341"    | "Eyrolles" |
|  3 | {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   | "978-2-7460-5516-2" | "ENI"      |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM catalog;

+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

| id | doc | isbn | publisher |

+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

| 1 | {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-7864-2" | "ENI" |

| 2 | {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"} | "978-2212126341" | "Eyrolles" |

| 3 | {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-5516-2" | "ENI" |

+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

3 rows in set (0.00 sec)

Sauvegarder et restaurer une table contenant des colonnes générées

Q: Comment se comporte mysqldump avec les colonnes générées ?

Faire une sauvegardes des données avec mysqldump, le résultat étant redirigé vers le fichier test.catalog.sql :

$ mysqldump test catalog > test.catalog.sql

1	$ mysqldump test catalog > test.catalog.sql

En visualisant le dump on s’appercoit que:

Les colonnes générées sont bien présentent dans la structure de la table.
La commande INSERT ne renseigne que les colonnes « classiques » id et doc

$ cat test.catalog.sql
-- MySQL dump 10.13  Distrib 5.7.9, for linux-glibc2.5 (x86_64)
-- Server version    5.7.9
...
-- Table structure for table `catalog`
--

CREATE TABLE `catalog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  `isbn` varchar(20) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,
  `publisher` varchar(20) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `isbn` (`isbn`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `catalog`
--

INSERT INTO `catalog` (`id`, `doc`) VALUES (1,'{\"isbn\": \"978-2-7460-7864-2\", \"title\": \"MySQL 5.6 Administration et optimisation\", \"language\": \"FR\", \"publisher\": \"ENI\"}'),(2,'{\"isbn\": \"978-2212126341\", \"title\": \"Audit et optimisation de MySQL 5\", \"language\": \"FR\", \"publisher\": \"Eyrolles\"}'),(3,'{\"isbn\": \"978-2-7460-5516-2\", \"title\": \"MySQL 5 Administration et optimisation\", \"language\": \"FR\", \"publisher\": \"ENI\"}');
...

$ cat test.catalog.sql

-- MySQL dump 10.13 Distrib 5.7.9, for linux-glibc2.5 (x86_64)

-- Server version 5.7.9

...

-- Table structure for table `catalog`

CREATE TABLE `catalog` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`doc` json DEFAULT NULL,

`isbn` varchar(20) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,

`publisher` varchar(20) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,

PRIMARY KEY (`id`),

UNIQUE KEY `isbn` (`isbn`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- Dumping data for table `catalog`

INSERT INTO `catalog` (`id`, `doc`) VALUES (1,'{\"isbn\": \"978-2-7460-7864-2\", \"title\": \"MySQL 5.6 Administration et optimisation\", \"language\": \"FR\", \"publisher\": \"ENI\"}'),(2,'{\"isbn\": \"978-2212126341\", \"title\": \"Audit et optimisation de MySQL 5\", \"language\": \"FR\", \"publisher\": \"Eyrolles\"}'),(3,'{\"isbn\": \"978-2-7460-5516-2\", \"title\": \"MySQL 5 Administration et optimisation\", \"language\": \"FR\", \"publisher\": \"ENI\"}');

...

Avant de restaurer le dump dans la table, je vais la vider:

mysql> TRUNCATE TABLE catalog;
Query OK, 0 rows affected (0.25 sec)

1 2	mysql> TRUNCATE TABLE catalog; Query OK, 0 rows affected (0.25 sec)

La table ne contient plus de donnée:

mysql> SELECT * FROM catalog;
Empty set (0.00 sec)

1 2	mysql> SELECT * FROM catalog; Empty set (0.00 sec)

Chargement des données dans la table:

mysql> source test.catalog.sql
Query OK, 0 rows affected (0.00 sec)
...

mysql> source test.catalog.sql

Query OK, 0 rows affected (0.00 sec)

...

mysql> SELECT * FROM catalog;
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
| id | doc                                                                                                                      | isbn                | publisher  |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
|  1 | {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-7864-2" | "ENI"      |
|  2 | {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"}       | "978-2212126341"    | "Eyrolles" |
|  3 | {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   | "978-2-7460-5516-2" | "ENI"      |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

mysql> SELECT * FROM catalog;

+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

| id | doc | isbn | publisher |

+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

| 1 | {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-7864-2" | "ENI" |

| 2 | {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"} | "978-2212126341" | "Eyrolles" |

| 3 | {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-5516-2" | "ENI" |

+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

Ta damm!!!
La table a bien été restaurée, les colonnes générées virtuelles sont, comme convenu, recalculées à la volée.

Importer un fichier texte dans une table avec des colonnes générées

Q: Comment utiliser la commande LOAD DATA INFILE avec des colonnes générées ?

L’export des données se fait avec SELECT … INTO OUTFILE:

mysql> SELECT * FROM catalog INTO OUTFILE '/tmp/test.catalog.tsv';
Query OK, 3 rows affected (0.01 sec)

1 2	mysql> SELECT * FROM catalog INTO OUTFILE '/tmp/test.catalog.tsv'; Query OK, 3 rows affected (0.01 sec)

$ cat /tmp/test.catalog.tsv
1    {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"}    "978-2-7460-7864-2"    "ENI"
2    {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"}    "978-2212126341"    "Eyrolles"
3    {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}    "978-2-7460-5516-2"    "ENI"

$ cat /tmp/test.catalog.tsv

1 {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} "978-2-7460-7864-2" "ENI"

2 {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"} "978-2212126341" "Eyrolles"

3 {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"} "978-2-7460-5516-2" "ENI"

Avant d’importer le fichier texte dans la table, je vais la vider:

mysql> TRUNCATE TABLE catalog;
Query OK, 0 rows affected (0.34 sec)

mysql> SELECT * FROM catalog;
Empty set (0.00 sec)

mysql> TRUNCATE TABLE catalog;

Query OK, 0 rows affected (0.34 sec)

mysql> SELECT * FROM catalog;

Empty set (0.00 sec)

Import des données dans la table:

mysql>  LOAD DATA INFILE '/tmp/test.catalog.tsv' INTO TABLE test.catalog;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from catalog;
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
| id | doc                                                                                                                      | isbn                | publisher  |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
|  1 | {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-7864-2" | "ENI"      |
|  2 | {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"}       | "978-2212126341"    | "Eyrolles" |
|  3 | {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   | "978-2-7460-5516-2" | "ENI"      |
+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+
3 rows in set (0.01 sec)

mysql> LOAD DATA INFILE '/tmp/test.catalog.tsv' INTO TABLE test.catalog;

Query OK, 3 rows affected (0.05 sec)

Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from catalog;

+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

| id | doc | isbn | publisher |

+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

| 1 | {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-7864-2" | "ENI" |

| 2 | {"isbn": "978-2212126341", "title": "Audit et optimisation de MySQL 5", "language": "FR", "publisher": "Eyrolles"} | "978-2212126341" | "Eyrolles" |

| 3 | {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"} | "978-2-7460-5516-2" | "ENI" |

+----+--------------------------------------------------------------------------------------------------------------------------+---------------------+------------+

3 rows in set (0.01 sec)

L’import de fichiers, avec la commande LOAD DATA INFILE, dans une table qui contient des colonnes générées ne diffère en rien de l’import dans une table sans colonne générée.

Sensibilité à la casse du contenu JSON

Q: JSON est il sensible à la casse dans MySQL ?

Rechercher tous les documents où l’éditeur est ENI (en majuscule):

mysql> SELECT doc FROM catalog WHERE doc->"$.publisher"='ENI';
+--------------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------+
| {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} |
| {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   |
+--------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> SELECT doc FROM catalog WHERE doc->"$.publisher"='ENI';

+--------------------------------------------------------------------------------------------------------------------------+

| doc |

+--------------------------------------------------------------------------------------------------------------------------+

| {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} |

| {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"} |

+--------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.01 sec)

Rechercher tous les documents où l’éditeur est eni (en minuscule):

mysql> SELECT doc FROM catalog WHERE doc->"$.publisher"='eni';
Empty set (0.00 sec)

1 2	mysql> SELECT doc FROM catalog WHERE doc->"$.publisher"='eni'; Empty set (0.00 sec)

Les documents JSON sont donc sensible à la casse.

Il est évidemment possible d’utiliser une fonction pour modifier la casse:

mysql> SELECT doc FROM catalog WHERE doc->"$.publisher"=UPPER('eni');
+--------------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------+
| {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} |
| {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   |
+--------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> SELECT doc FROM catalog WHERE doc->"$.publisher"=UPPER('eni');

+--------------------------------------------------------------------------------------------------------------------------+

| doc |

+--------------------------------------------------------------------------------------------------------------------------+

| {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} |

| {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"} |

+--------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.01 sec)

Cependant 2 contraintes:
– Si la colonne est indexée ce dernier ne pourra pas être utilisé.
– Ça fonctionne si et seulement si le mot est écrit dans la base entièrement en majuscule.

Une meilleure solution consiste à créer et a indexer une colonne générée qui contient les données en minuscule.

mysql> ALTER TABLE catalog ADD COLUMN publisher_lower varchar(20) GENERATED ALWAYS AS (LOWER(JSON_UNQUOTE(doc->"$.publisher"))) VIRTUAL;

1	mysql> ALTER TABLE catalog ADD COLUMN publisher_lower varchar(20) GENERATED ALWAYS AS (LOWER(JSON_UNQUOTE(doc->"$.publisher"))) VIRTUAL;

La colonne générée contient les directives suivantes:

LOWER : transformer en minuscules les chaines de caractères
JSON_UNQUOTE : supprimer les guillemets
VIRTUAL : la colonne générée est virtuelle. Les données ne sont pas stockées mais calculées à la volée.

La structure de la tables est maintenant:

mysql> SHOW CREATE TABLE catalog;
CREATE TABLE `catalog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  `isbn` varchar(20) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,
  `publisher` varchar(20) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,
  `publisher_lower` varchar(20) GENERATED ALWAYS AS (LOWER(doc->"$.publisher")) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `isbn` (`isbn`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

mysql> SHOW CREATE TABLE catalog;

CREATE TABLE `catalog` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`doc` json DEFAULT NULL,

`isbn` varchar(20) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,

`publisher` varchar(20) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,

`publisher_lower` varchar(20) GENERATED ALWAYS AS (LOWER(doc->"$.publisher")) VIRTUAL,

PRIMARY KEY (`id`),

UNIQUE KEY `isbn` (`isbn`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

La colonne publisher_lower contient la version tout en minuscule des données de la colonne publisher.

mysql> SELECT publisher, publisher_lower FROM catalog;
+------------+-----------------+
| publisher  | publisher_lower |
+------------+-----------------+
| "ENI"      | "eni"           |
| "Eyrolles" | "eyrolles"      |
| "ENI"      | "eni"           |
+------------+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT publisher, publisher_lower FROM catalog;

+------------+-----------------+

| publisher | publisher_lower |

+------------+-----------------+

| "ENI" | "eni" |

| "Eyrolles" | "eyrolles" |

| "ENI" | "eni" |

+------------+-----------------+

3 rows in set (0.00 sec)

Ajout de l’index:

mysql> ALTER TABLE catalog ADD KEY idx_publisher_lower(publisher_lower);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE catalog ADD KEY idx_publisher_lower(publisher_lower);

Query OK, 0 rows affected (0.37 sec)

Records: 0 Duplicates: 0 Warnings: 0

Nouvelle structure de table:

mysql> SHOW CREATE TABLE catalog;
CREATE TABLE `catalog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  `isbn` varchar(20) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,
  `publisher` varchar(20) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,
  `publisher_lower` varchar(20) GENERATED ALWAYS AS (LOWER(doc->"$.publisher")) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `isbn` (`isbn`),
  KEY `idx_publisher_lower` (`publisher_lower`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

mysql> SHOW CREATE TABLE catalog;

CREATE TABLE `catalog` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`doc` json DEFAULT NULL,

`isbn` varchar(20) GENERATED ALWAYS AS (doc->"$.isbn") VIRTUAL,

`publisher` varchar(20) GENERATED ALWAYS AS (doc->"$.publisher") VIRTUAL,

`publisher_lower` varchar(20) GENERATED ALWAYS AS (LOWER(doc->"$.publisher")) VIRTUAL,

PRIMARY KEY (`id`),

UNIQUE KEY `isbn` (`isbn`),

KEY `idx_publisher_lower` (`publisher_lower`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Maintenant les données étant stockées dans une collation non sensible à la casse (utf8_general_ci) la requête n’est plus sensible à la casse:

mysql> SELECT doc FROM catalog WHERE publisher_lower = 'ENI' AND publisher_lower = 'eni' AND publisher_lower = 'eNi';
+--------------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------+
| {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} |
| {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"}   |
+--------------------------------------------------------------------------------------------------------------------------+

mysql> SELECT doc FROM catalog WHERE publisher_lower = 'ENI' AND publisher_lower = 'eni' AND publisher_lower = 'eNi';

+--------------------------------------------------------------------------------------------------------------------------+

| doc |

+--------------------------------------------------------------------------------------------------------------------------+

| {"isbn": "978-2-7460-7864-2", "title": "MySQL 5.6 Administration et optimisation", "language": "FR", "publisher": "ENI"} |

| {"isbn": "978-2-7460-5516-2", "title": "MySQL 5 Administration et optimisation", "language": "FR", "publisher": "ENI"} |

+--------------------------------------------------------------------------------------------------------------------------+

La commande EXPLAIN confirme que l’index est vu et utilisé:

mysql> EXPLAIN SELECT doc FROM catalog WHERE publisher_lower = 'eNi'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: catalog
   partitions: NULL
         type: ref
possible_keys: idx_publisher_lower
          key: idx_publisher_lower
      key_len: 63
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL

mysql> EXPLAIN SELECT doc FROM catalog WHERE publisher_lower = 'eNi'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: catalog

partitions: NULL

type: ref

possible_keys: idx_publisher_lower

key: idx_publisher_lower

key_len: 63

ref: const

rows: 2

filtered: 100.00

Extra: NULL

En fait on vient d’implémenter un index sur une fonction (functional index).

Vous voulez en savoir plus sur MySQL 5.7 ?
Rejoignez nous sur l’Oracle MySQL Tech Tour Paris le 8 décember 2015.

Infos & inscriptions

Note: cet article vient en complément de l’article 30 mins avec JSON en MySQL

Je vous invite également à consulter:

BGOUG15: JSON support in MySQL 5.7 from Georgi Kodinov

Filed under:
json, MySQL by Olivier DASINI

30 mins avec JSON en MySQL

novembre 17, 2015

Tags: generated column, JSON, NoSQL

Read this post in English

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
            ]
         ]
      ]
   }
}

$ 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;

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;

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);

1	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);

1	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'\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

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"}');

1	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

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

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 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

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 (

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

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

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

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

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

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 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

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 (

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

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

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

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

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

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/

Filed under:
json, MySQL by Olivier DASINI

Slides du meetup

septembre 25, 2015

Tags: meetup

Le 17 septembre j’ai été invité par le MySQL User Group Fr pour parler d’un retour d’expérience sur une migration MySQL 5.5 vers 5.6.

Voici ma présentation:

Upgrade to MySQL 5.6 without downtime from Olivier DASINI

Commentaires fermés

Filed under:
Conférence, MariaDB, Meetup, MySQL, Percona server, Présentation by Olivier DASINI

Meetup MySQL à Paris

septembre 14, 2015

Tags: meetup

Ce jeudi 17 septembre 2015, à l’initiative du MySQL User Group France (lemug.fr), je vais présenter un retour d’expérience sur une migration MySQL 5.5 vers 5.6 dans les locaux de Dailymotion à Paris.

Le programme :

Aurélien Lequoy – DBA MySQL et CEO de Esysteme – présentera PmaControl son outil pour administrer et superviser MySQL.
Serge Frezefond – Architecte Cloud chez MariaDB Corp – parlera des nouveautés MariaDB 10.1 liées à la sécurité.
Olivier Dasini – Sr. MySQL DBA – Upgrade to MySQL 5.6 without downtime.

Infos et inscriptions:

http://www.lemug.fr/2015/invitation-meetup-pma-control-securite-bonnes-pratiques-le-17-septembre/

Et c’est gratuit !!! 🙂

Merci à Dailymotion & MariaDB Corp

Commentaires fermés

Filed under:
Conférence, MariaDB, Meetup, MySQL, Percona server, PHP, Présentation by Olivier DASINI

UPDATE db SET age = age + 1 WHERE product=’MySQL’

janvier 7, 2014

2013 à été une année très riche en ce qui concerne l’écosystème MySQL / MariaDB / Percona Server. Voici un petit récap technique (incomplet) pour les 3 acteurs majeurs:

MySQL @Oracle

En bref

Oracle à repris la main et le lead technique en 2013 avec MySQL 5.6: http://dimitrik.free.fr/blog/archives/2013/02/mysql-performance-mysql-56-vs-mysql-55-vs-mariadb-55.html

Facebook migre vers la 5.6: https://github.com/facebook/mysql-5.6

De belle promesses avec MySQL 5.7: http://dimitrik.free.fr/blog/archives/2013/10/mysql-performance-the-road-to-500k-qps-with-mysql-57.html

Avec MySQL 5.7 & memcached: http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html

État des lieux

MySQL 5.6 GA depuis le 05/02/2013

Changes in MySQL 5.6.10 (2013-02-05, General Availability)

http://dev.mysql.com/downloads/mysql/

MySQL 5.7 DMR, dernière version le 03/12/2013

Changes in MySQL 5.7.1 (2013-04-23, Milestone 11)

http://dev.mysql.com/downloads/mysql/

MySQL cluster 7.3 GA depuis le 18/06/2013

Changes in MySQL Cluster NDB 7.3.2 (5.6.11-ndb-7.3.2) (2013-06-18, General Availability)

http://dev.mysql.com/downloads/cluster/

MySQL utilities GA dernière version le 02/08/2013

Changes in MySQL Utilities 1.3.4 (2013-08-02, General Availability)

http://dev.mysql.com/downloads/tools/

Événements français

Meetup Oracle/LeMug le 10 octobre 2013

https://dasini.net/blog/2013/10/01/mysql-techday-le-programme/

https://dasini.net/blog/2013/10/14/retour-sur-le-mysql-techday/

MariaDB & SkySQL

En bref

Monty Program Ab et SkySQL ont fusionnés: http://www.skysql.com/news-and-events/press-releases/skysql-merges-with-mariadb-developers

MariaDB Galera Cluster est passée GA, en attendant le tour de MariaDB 10.

Wikimedia migre en MariaDB 5.5: https://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariadb/

Google migre vers MariaDB 10: http://www.zdnet.com/google-quietly-dumps-oracle-mysql-for-mariadb-7000020670/

Des distribs Linux qui embarques MariaDB: https://mariadb.com/kb/en/distributions-which-include-mariadb/

Et une multitude de moteur de stockage: https://mariadb.com/kb/en/mariadb-storage-engines/

État des lieux

MariaDB 5.5 GA depuis le 23/05/2013

https://downloads.mariadb.org/

MariaDB Galera Cluster 5.5 GA depuis le 30/08/2013

https://downloads.mariadb.org/

MariaDB 10.0.7 Beta: dernière version le 27/12/2013

https://blog.mariadb.org/mariadb-10-0-7-now-available/

https://downloads.mariadb.org/

Événements français

Meetup SkySQL / LeMug: 17 décembre 2013

http://www.lemug.fr/2013/17-decembre-meet-up-la-nuit-mariadb/

Percona

En bref

Des outils (toolkit, XtraBackup,…) qui font le bonheur des DBA. A noter que le Q4 à vu la sortie en GA de Percona Server 5.6.

État des lieux

Percona Server 5.6 GA: depuis le 7/10/2013

http://www.percona.com/software/percona-server/ps-5.6

Percona Server 5.5 GA: dernière version le 20/12/2013

http://www.percona.com/downloads/Percona-Server-5.5/

Percona Server XtraDB Cluster 5.5 GA: dernière version le 3/12/2013

http://www.percona.com/software/percona-xtradb-cluster

Percona Server XtraDB Cluster 5.6 RC: dernière version le 18/12/2013

http://www.percona.com/doc/percona-xtradb-cluster/5.6/

XtraBackup

http://www.percona.com/software/percona-xtrabackup

Toolkit

http://www.percona.com/software/percona-toolkit

Monitoring Plugins

http://www.percona.com/software/percona-monitoring-plugins

Data Recovery Tool for InnoDB

http://www.percona.com/software/mysql-innodb-data-recovery-tools

Événements français

Pas de meetup Percona / LeMug en 2013. Il aurait dû avoir lieu fin 2013, mais un stupide problème de salle à fait capoter l’affaire. Ce n’est que partie remise.

N’hésitez pas à compléter si j’ai oublié des choses 🙂

Beaucoup de bonnes choses en 2013, c’est vraiment de bon augure pour 2014.

Je profite de ce “post” pour évidemment vous souhaiter une excellente année 2014. Que celle-ci soit remplie de bonheur, de santé, de réussite et de belles requêtes optimisées 🙂

SQLment votre,

Olivier DASINI

Commentaires fermés

Filed under:
MariaDB, MySQL, Percona server by Olivier DASINI

Partager :

WordPress:

Partager :

WordPress:

Partager :

WordPress:

Partager :

WordPress:

TL;TR

API du Plugin Query Rewrite

Le plugin Rewriter

Installation et vérifications

Réécrire une requête

Ajouter la règle de réécriture

Flusher la règle de réécriture

Requête réécrite

Exemples de réécritures avec Rewriter

Ex 1

Ex 2

Ex 3

Pour aller plus loin

Partager :

WordPress:

Sauvegarder et restaurer une table contenant des colonnes générées

Importer un fichier texte dans une table avec des colonnes générées

Sensibilité à la casse du contenu JSON

Partager :

WordPress:

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

Partager :

WordPress:

Partager :

WordPress:

Partager :

WordPress:

MySQL @Oracle

En bref

État des lieux

Événements français

MariaDB & SkySQL

En bref

État des lieux

Événements français

Percona

En bref

État des lieux

Événements français

Partager :

WordPress:

Subscribe via Email

Articles récents

Archives

MySQL Blogs

Étiquettes