30 mins avec MySQL Query Rewriter

mars 2, 2016

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.

Pour vérifier:

De nouveaux objets ont été créés

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.

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

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

 

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:

 

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.

Le contenu de la table rewrite_rules est:

Les stats du Rewriter montrent:

 

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.

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:

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:

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

Exemple

Les stats du Rewriter montrent:

 

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…

Magique !!!

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

 

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

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

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

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

Ajout de la règle de réécriture

Avec la règle

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

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

Ajout de la règle de réécriture

Avec la règle

 

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

Ajout de la règle de réécriture

Avec la règle

 

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

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!

 

 

Leave a Reply