30 mins avec MySQL Query Rewriter
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
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 | +-----------------------------------+-------+
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 | +-------------------------+
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;
Le plugin peut être activé ou désactivé à chaud :
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
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.
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');
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
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 | +-----------------------------------+-------+
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)
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 ?
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.
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"
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 | +-----------------------------------+-------+
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)
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 | +-------+------+------------------------------------------------------------------------+
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 | +-----------------------------------+-------+
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();
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();
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)
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();
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)
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
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)
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();
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)
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)
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();
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)
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.
…
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
- The Rewriter Query Rewrite Plugin
- Query Rewrite Plugins
- The Query Rewrite Plugins
- The Query Rewrite Plugin Interface: Writing Your Own Plugin
Pre-parse query rewrite plugin
Thank you for using MySQL!
Architecte Solution Cloud chez Oracle
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–