{"id":1593,"date":"2016-03-02T11:09:54","date_gmt":"2016-03-02T10:09:54","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1593"},"modified":"2016-03-18T14:15:00","modified_gmt":"2016-03-18T13:15:00","slug":"30-mins-avec-mysql-query-rewriter","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2016\/03\/02\/30-mins-avec-mysql-query-rewriter\/","title":{"rendered":"30 mins avec MySQL Query Rewriter"},"content":{"rendered":"<p><em><a href=\"http:\/\/dasini.net\/blog\/2016\/02\/25\/30-mins-with-mysql-query-rewriter\/\">Read this post in English<\/a><\/em><\/p>\n<h1>TL;TR<\/h1>\n<p>Parfois des requ\u00eates probl\u00e9matiques tournent sur le serveur, mais il n&rsquo;est pas possible de r\u00e9gler le probl\u00e8me \u00e0 la source (requ\u00eates venant d&rsquo;un ORM par example )<\/p>\n<p>MySQL 5.7 fournit :<\/p>\n<ul>\n<li>Une API pre et post <em>parse query rewrite<\/em>\n<ul>\n<li>Les utilisateurs peuvent \u00e9crire leurs propre plugins<\/li>\n<li>Permet d&rsquo;\u00e9liminer le besoin d&rsquo;un proxy<\/li>\n<\/ul>\n<\/li>\n<li>Avec le <em>post-parse query plugin<\/em>, il est possible :\n<ul>\n<li>De r\u00e9\u00e9crire une requ\u00eate probl\u00e9matique sans faire de changement au niveau de l&rsquo;application<\/li>\n<li>Ajouter des <em>hints<\/em> pour les <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/index-hints.html\" target=\"_blank\">index<\/a> ou pour l&rsquo;<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/optimizer-hints.html\" target=\"_blank\">optimiseur<\/a><\/li>\n<li>Modifier l&rsquo;ordre des jointures<\/li>\n<li>&#8230;<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h1>API du Plugin Query Rewrite<\/h1>\n<p>Pour citer cet <a href=\"http:\/\/mysqlserverteam.com\/the-query-rewrite-plugins\/\" target=\"_blank\">article du MySQL Server Blog<\/a> :<\/p>\n<p>\u00a0\u00bb<\/p>\n<p>MySQL now offer two APIs for writing query rewrite plugins.<\/p>\n<p>&#8211; <strong>Pre-parse rewrite plugin API<\/strong>,\u00a0 is for when you know exactly \u2013 as in character-by-character exactly \u2013 what the offending queries look like. This one has a hook to intercept the query string right before it\u2019s parsed.<\/p>\n<p>&#8211; <strong>Post-parse rewrite plugin API,<\/strong> 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.<\/p>\n<p>\u00a0\u00bb<\/p>\n<p>Cet article traite du sujet <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/rewriter-query-rewrite-plugin.html\" target=\"_blank\"><strong>Rewriter plugin<\/strong><\/a>, un <em>post-parse query rewrite plugin<\/em>, inclus dans la distribution <a href=\"http:\/\/www.thecompletelistoffeatures.com\/\" target=\"_blank\">MySQL 5.7<\/a> (\u00e0 partir de la version MySQL 5.7.6).<\/p>\n<p>&nbsp;<\/p>\n<h1>Le plugin Rewriter<\/h1>\n<h2>Installation et v\u00e9rifications<\/h2>\n<p>La simplicit\u00e9 fait partie de la philosophie MySQL, l&rsquo;installation du plugin n&rsquo;y d\u00e9roge pas.<\/p>\n<p>Pour installer le plugin <em>Rewriter<\/em>, il faut lancer le script <strong>install_rewriter.sql<\/strong> localis\u00e9 dans le r\u00e9pertoire <strong>share<\/strong> de votre installation MySQL.<\/p>\n<pre class=\"\">~ $ mysql -u root -p &lt; install_rewriter.sql<\/pre>\n<p>Pour v\u00e9rifier:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SELECT * FROM mysql.plugin;\r\n+--------------------------+-------------+\r\n| name                     | dl          |\r\n+--------------------------+-------------+\r\n| rewriter                 | rewriter.so |\r\n+--------------------------+-------------+\r\n\r\nmysql&gt; SHOW GLOBAL VARIABLES LIKE 'rewriter%';\r\n+------------------+-------+\r\n| Variable_name    | Value |\r\n+------------------+-------+\r\n| rewriter_enabled | ON    |\r\n| rewriter_verbose | 1     |\r\n+------------------+-------+\r\n\r\nmysql&gt; SHOW GLOBAL STATUS LIKE 'rewriter%';\r\n+-----------------------------------+-------+\r\n| Variable_name                     | Value |\r\n+-----------------------------------+-------+\r\n| Rewriter_number_loaded_rules      | 0     |\r\n| Rewriter_number_reloads           | 1     |\r\n| Rewriter_number_rewritten_queries | 0     |\r\n| Rewriter_reload_error             | OFF   |\r\n+-----------------------------------+-------+<\/pre>\n<p>De nouveaux objets ont \u00e9t\u00e9 cr\u00e9\u00e9s<\/p>\n<pre class=\"lang:mysql decode:true\"># Stored procedure\r\n\r\nmysql&gt; SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES \r\nWHERE ROUTINE_SCHEMA='query_rewrite';\r\n+---------------------+--------------+\r\n| ROUTINE_NAME        | ROUTINE_TYPE |\r\n+---------------------+--------------+\r\n| flush_rewrite_rules | PROCEDURE    |\r\n+---------------------+--------------+\r\n\r\n\r\n# UDF\r\n\r\nmysql&gt; SELECT name, dl, type FROM mysql.func \r\nWHERE name LIKE '%rewrite%';\r\n+--------------------+-------------+----------+\r\n| name               | dl          | type     |\r\n+--------------------+-------------+----------+\r\n| load_rewrite_rules | rewriter.so | function |\r\n+--------------------+-------------+----------+\r\n\r\n\r\n\r\n# New table is created in a new schema\r\n\r\nmysql&gt; SHOW TABLES IN query_rewrite;\r\n+-------------------------+\r\n| Tables_in_query_rewrite |\r\n+-------------------------+\r\n| rewrite_rules           |\r\n+-------------------------+<\/pre>\n<p>La table <strong>rewrite_rules<\/strong> du sch\u00e9ma <strong>query_rewrite <\/strong>fournie un <strong>stockage\u00a0persistent<\/strong> des r\u00e8gles que le plugin <em>Rewriter<\/em> utilise pour d\u00e9cider quelles requ\u00eates r\u00e9\u00e9crire.<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW CREATE TABLE query_rewrite.rewrite_rules\\G\r\n*************************** 1. row ***************************\r\n       Table: rewrite_rules\r\nCreate Table: CREATE TABLE `rewrite_rules` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `pattern` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,\r\n  `pattern_database` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,\r\n  `replacement` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,\r\n  `enabled` enum('YES','NO') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'YES',\r\n  `message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,\r\n  `pattern_digest` varchar(32) DEFAULT NULL,\r\n  `normalized_pattern` varchar(100) DEFAULT NULL,\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;<\/pre>\n<p>Le plugin peut \u00eatre activ\u00e9 ou d\u00e9sactiv\u00e9 \u00e0 chaud :<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SET GLOBAL rewriter_enabled = ON;\r\n\r\nmysql&gt; SET GLOBAL rewriter_enabled = OFF;<\/pre>\n<p>Il est \u00e9galement possible de l&rsquo;activer par l&rsquo;interm\u00e9diaire du fichier de configuration (my.cnf | my.ini)<\/p>\n<pre class=\"lang:vim decode:true\">[mysqld]\r\n\r\nrewriter_enabled = ON<\/pre>\n<p>&nbsp;<\/p>\n<h1>R\u00e9\u00e9crire une requ\u00eate<\/h1>\n<p>Le plugin <em>post-parse rewrite<\/em> ne fonctionne qu&rsquo;avec les requ\u00eates <strong>SELECT<\/strong>.<\/p>\n<p>Toutes requ\u00eates autres que SELECT g\u00e9n\u00e8rent lors du <em>flush<\/em> des r\u00e8gles, dans la colonne de la table\u00a0<strong>rewrite_rules.message<\/strong> le message d&rsquo;erreur suivant:<\/p>\n<pre class=\"lang:vim decode:true\">          message: Pattern needs to be a select statement.<\/pre>\n<p>&nbsp;<\/p>\n<p>Le\u00a0plugin Rewriter est facile \u00e0 utiliser. Commen\u00e7ons par un exemple simple (voir simpliste) histoire de se faire la main. Transformons un SELECT n en un SELECT n+1 (n \u00e9tant un entier).<\/p>\n<p><span style=\"text-decoration: underline;\">Patterns<\/span><\/p>\n<p><strong><em>\u00a0-&gt;<\/em><\/strong> SELECT 1\u00a0\u00a0\u00a0\u00a0\u00a0 # Input<\/p>\n<p><strong><em>\u00a0&lt;=<\/em><\/strong> SELECT 2\u00a0\u00a0\u00a0 # Output<\/p>\n<h2>Ajouter la r\u00e8gle de r\u00e9\u00e9criture<\/h2>\n<p>Pour ajouter une r\u00e8gle dans le plugin Rewriter, il faut ajouter des enregistrements dans la table <strong>rewrite_rules<\/strong>.<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'SELECT ? + 1');<\/pre>\n<p>Le contenu de la table rewrite_rules est:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SELECT * FROM query_rewrite.rewrite_rules\\G\r\n*************************** 1. row ***************************\r\n               id: 1\r\n          pattern: SELECT ?\r\n pattern_database: NULL\r\n      replacement: SELECT ? + 1\r\n          enabled: YES\r\n          message: NULL\r\n    pattern_digest: NULL\r\nnormalized_pattern: NULL<\/pre>\n<p>Les stats du Rewriter montrent:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW GLOBAL status LIKE '%rewriter%';\r\n+-----------------------------------+-------+\r\n| Variable_name                     | Value |\r\n+-----------------------------------+-------+\r\n| Rewriter_number_loaded_rules      | 0     |\r\n| Rewriter_number_reloads           | 1     |\r\n| Rewriter_number_rewritten_queries | 0     |\r\n| Rewriter_reload_error             | OFF   |\r\n+-----------------------------------+-------+<\/pre>\n<p>&nbsp;<\/p>\n<h2>Flusher la r\u00e8gle de r\u00e9\u00e9criture<\/h2>\n<p>Ensuite il faut appeler la proc\u00e9dure stock\u00e9e <strong>flush_rewrite_rules()<\/strong> pour charger les r\u00e8gles dans le plugin.<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; CALL query_rewrite.flush_rewrite_rules();\r\nQuery OK, 0 rows affected (0,01 sec)\r\n<\/pre>\n<p>Lorsque le plugin charge les r\u00e8gles, il g\u00e9n\u00e8re, entre autres, une forme normalis\u00e9e de la requ\u00eate ainsi qu&rsquo;une valeur hash:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SELECT * FROM query_rewrite.rewrite_rules\\G\r\n*************************** 1. row ***************************\r\n               id: 1\r\n          pattern: SELECT ?\r\n pattern_database: NULL\r\n      replacement: SELECT ? + 1\r\n          enabled: YES\r\n          message: NULL\r\n    pattern_digest: 2c7e64d74a4f06d8ceff62d23ae9180c\r\nnormalized_pattern: select ?<\/pre>\n<p>Le <strong>?<\/strong> agit comme un &lsquo;marqueur&rsquo; qui correspond aux donn\u00e9es de la requ\u00eate. Il ne peut \u00eatre utilis\u00e9 que pour les donn\u00e9es, <strong>pas pour les mots cl\u00e9s SQL, ni les identifieurs<\/strong>,&#8230;\u00a0 De plus le ? ne doit pas \u00eatre entre guillemets ou apostrophes.<\/p>\n<p>Si le parsing de la requ\u00eate \u00e9choue, la proc\u00e9dure stock\u00e9e va g\u00e9n\u00e9r\u00e9e une erreur:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; CALL query_rewrite.flush_rewrite_rules();\r\nERROR 1644 (45000): Loading of some rule(s) failed.<\/pre>\n<p>Vous trouverez plus de d\u00e9tails dans la colonne query_rewrite.rewrite_rules.message.<\/p>\n<p>Exemple<\/p>\n<pre class=\"lang:mysql decode:true\">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\"<\/pre>\n<p>Les stats du Rewriter montrent:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW GLOBAL status LIKE '%rewriter%';\r\n+-----------------------------------+-------+\r\n| Variable_name                     | Value |\r\n+-----------------------------------+-------+\r\n| Rewriter_number_loaded_rules      | 1     |\r\n| Rewriter_number_reloads           | 2     |\r\n| Rewriter_number_rewritten_queries | 0     |\r\n| Rewriter_reload_error             | OFF   |\r\n+-----------------------------------+-------+<\/pre>\n<p>&nbsp;<\/p>\n<h1>Requ\u00eate r\u00e9\u00e9crite<\/h1>\n<p>Petit \u00e9tat des lieux. Nous avons, ajout\u00e9, puis <em>flush\u00e9<\/em> la r\u00e8gle.\u00a0 On peut donc maintenant ex\u00e9cuter une requ\u00eate qui correspond au pattern et voir le r\u00e9sultat&#8230;<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SELECT 1;\r\n+-------+\r\n| 1 + 1 |\r\n+-------+\r\n|     2 |\r\n+-------+\r\n1 row in set, 1 warning (0,00 sec)<\/pre>\n<p>Magique !!!<\/p>\n<p>La requ\u00eate \u00e0 \u00e9t\u00e9 r\u00e9\u00e9crite \u00ab\u00a0\u00e0 la vol\u00e9e\u00a0\u00bb. Cependant, quelques d\u00e9tails int\u00e9ressants sont cach\u00e9s dans le <em>warning<\/em>.<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW WARNINGS;\r\n+-------+------+------------------------------------------------------------------------+\r\n| Level | Code | Message                                                                |\r\n+-------+------+------------------------------------------------------------------------+\r\n| Note  | 1105 | Query 'select 1' rewritten to 'SELECT 1 + 1' by a query rewrite plugin |\r\n+-------+------+------------------------------------------------------------------------+<\/pre>\n<p>&nbsp;<\/p>\n<p>Les stats du Rewriter ont \u00e9t\u00e9 mis \u00e0 jour en cons\u00e9quence:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; SHOW GLOBAL status LIKE '%rewriter%';\r\n+-----------------------------------+-------+\r\n| Variable_name                     | Value |\r\n+-----------------------------------+-------+\r\n| Rewriter_number_loaded_rules      | 1     |\r\n| Rewriter_number_reloads           | 2     |\r\n| Rewriter_number_rewritten_queries | 1     |\r\n| Rewriter_reload_error             | OFF   |\r\n+-----------------------------------+-------+<\/pre>\n<p>Pour d\u00e9sactiver une r\u00e8gle existante, il suffit de modifier la colonne <strong>enabled<\/strong> et recharger la table dans le plugin:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;\r\n\r\nmysql&gt; CALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p>En passant il est possible de supprimer les enregistrements de la table:<\/p>\n<pre class=\"lang:mysql decode:true\">mysql&gt; TRUNCATE TABLE query_rewrite.rewrite_rules;  # Delete all the rows (all the rules)\r\n\r\nmysql&gt; CALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p>OK!\u00a0 maintenant que le concept est compris, voyons des exemples plus pertinents.<\/p>\n<p>&nbsp;<\/p>\n<h1>Exemples de r\u00e9\u00e9critures avec Rewriter<\/h1>\n<h2>Ex 1<\/h2>\n<p>R\u00e9\u00e9crire un jointure en sous-requ\u00eate; Pour des raisons de performance, la requ\u00eate doit \u00eatre r\u00e9\u00e9crite mais vous n&rsquo;avez pas acc\u00e8s au cot\u00e9 applicatif.<\/p>\n<p><span style=\"text-decoration: underline;\">Patterns<\/span><\/p>\n<p><em><strong>\u00a0-&gt;<\/strong><\/em> SELECT count(distinct emp_no) FROM employees.employees<strong> INNER JOIN<\/strong> employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) &lt; <em>{integer}<\/em>;<\/p>\n<p><em>\u00a0&lt;=<\/em> SELECT count(emp_no) FROM employees.employees WHERE emp_no <strong>IN<\/strong> ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) &lt; <em>{integer}<\/em>);<\/p>\n<p><span style=\"text-decoration: underline;\">Sans la r\u00e8gle<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT count(distinct emp_no) \r\nFROM employees.employees INNER JOIN employees.salaries USING(emp_no) \r\nWHERE DATEDIFF(to_date, from_date) &lt; 2000;\r\n+------------------------+\r\n| count(distinct emp_no) |\r\n+------------------------+\r\n|                 300024 |\r\n+------------------------+\r\n\r\n1 row in set (12,93 sec)<\/pre>\n<p><span style=\"text-decoration: underline;\">Ajout de la r\u00e8gle de r\u00e9\u00e9criture<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">INSERT INTO query_rewrite.rewrite_rules \r\n(\r\npattern, \r\nreplacement\r\n) \r\nVALUES\r\n(\r\n'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) &lt; ?', \r\n'SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) &lt; ?)'\r\n);\r\n\r\n\r\nCALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p><span style=\"text-decoration: underline;\">Avec la r\u00e8gle<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT count(distinct emp_no) \r\nFROM employees.employees INNER JOIN employees.salaries USING(emp_no) \r\nWHERE DATEDIFF(to_date, from_date) &lt; 2000;\r\n+---------------+\r\n| count(emp_no) |\r\n+---------------+\r\n|        300024 |\r\n+---------------+\r\n\r\n1 row in set, 1 warning (3,77 sec)<\/pre>\n<p>Le temps d&rsquo;ex\u00e9cution de la requ\u00eate est pass\u00e9 de 12.93 \u00e0 3.77 secondes<\/p>\n<pre class=\"lang:mysql decode:true\">SHOW WARNINGS;\r\n*************************** 1. row ***************************\r\n Level: Note\r\n  Code: 1105\r\nMessage: Query 'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) &lt; 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) &lt; 2000)' by a query rewrite plugin<\/pre>\n<p><span style=\"text-decoration: underline;\">Remarque<\/span><\/p>\n<p>Il y a quelques ann\u00e9es, j&rsquo;ai \u00e9cris cet article <a href=\"http:\/\/dasini.net\/blog\/2012\/05\/15\/jointure-vs-sous-requete\/\" target=\"_blank\">Jointure vs sous-requ\u00eate<\/a>\u00a0o\u00f9 je compare les temps d&rsquo;ex\u00e9cution d&rsquo;un jointure et de son \u00e9quivalent en sous-requ\u00eate. L&rsquo;<a href=\"http:\/\/mysqlserverteam.com\/what-to-do-with-optimizer-hints-after-an-upgrade\/\" target=\"_blank\">optimiseur s&rsquo;est bien am\u00e9lior\u00e9<\/a> depuis la version 5.5.<\/p>\n<p>&nbsp;<\/p>\n<h2>Ex 2<\/h2>\n<p>Borner le temps d&rsquo;ex\u00e9cution maximum d&rsquo;une requ\u00eate; ie ajouter le <em>hint<\/em> MySQL 5.7 \u00a0<strong>\/*+ <a href=\"http:\/\/mysqlserverteam.com\/server-side-select-statement-timeouts\/\" target=\"_blank\">MAX_EXECUTION_TIME<\/a>(X)*\/<\/strong>\u00a0c&rsquo;est \u00e0 dire que le temps d&rsquo;ex\u00e9cution de la requ\u00eate ne pourra exeder\u00a0X millisecondes.<\/p>\n<p><span style=\"text-decoration: underline;\">Patterns<\/span><\/p>\n<p><strong><em>\u00a0-&gt;<\/em><\/strong> SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = <em>{integer}<\/em>;<\/p>\n<p><strong><em>\u00a0&lt;=<\/em><\/strong> SELECT<strong> \/*+ MAX_EXECUTION_TIME(10000)*\/<\/strong> count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = <em>{integer}<\/em>;<\/p>\n<p><span style=\"text-decoration: underline;\">Sans la r\u00e8gle<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 110000;\r\n1 row in set (11,82 sec)\r\n\r\nSELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 70000;\r\n1 row in set (9,22 sec)<\/pre>\n<p><span style=\"text-decoration: underline;\">Ajout de la r\u00e8gle de r\u00e9\u00e9criture<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">INSERT INTO query_rewrite.rewrite_rules \r\n(\r\npattern, \r\nreplacement, \r\npattern_database\r\n) \r\nVALUES\r\n(\r\n'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = ?', \r\n'SELECT \/*+ MAX_EXECUTION_TIME(10000)*\/ count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary =  ?', \r\n'employees'\r\n);\r\n\r\n\r\nCALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p><span style=\"text-decoration: underline;\">Avec la r\u00e8gle<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 110000;\r\nERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded\r\n\r\nSELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = 70000;\r\n1 row in set (9,85 sec)<\/pre>\n<p>&nbsp;<\/p>\n<h2>Ex 3<\/h2>\n<p>Evolution du schema de la base apr\u00e8s une MEP; ie Une colonne est ajout\u00e9e (ou supprim\u00e9e) mais vous ne pouvez pas modifier la\/les requ\u00eate(s) qui utilise(nt) cette table.<\/p>\n<p><span style=\"text-decoration: underline;\">Patterns<\/span><\/p>\n<p><strong><em>\u00a0-&gt;<\/em><\/strong> SELECT first_name, last_name FROM employees.employees WHERE year(hire_date) = 2000;<\/p>\n<p><strong><em>\u00a0&lt;=<\/em><\/strong> SELECT first_name, last_name, <strong>birth_date<\/strong> FROM employees.employees WHERE year(hire_date) = 2000;<\/p>\n<p><span style=\"text-decoration: underline;\">Sans la r\u00e8gle<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT \r\n    first_name, \r\n    last_name \r\nFROM employees.employees WHERE year(hire_date) = 2000;\r\n+-------------+------------+\r\n| first_name  | last_name  |\r\n+-------------+------------+\r\n| Ulf         | Flexer     |\r\n\r\n...\r\n13 rows in set (0,01 sec)<\/pre>\n<p><span style=\"text-decoration: underline;\">Ajout de la r\u00e8gle de r\u00e9\u00e9criture<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">INSERT INTO query_rewrite.rewrite_rules \r\n(\r\npattern, \r\nreplacement, \r\npattern_database\r\n) \r\nVALUES\r\n(\r\n'SELECT first_name, last_name FROM employees.employees WHERE year(hire_date) = ?', \r\n'SELECT first_name, last_name, birth_date FROM employees.employees WHERE year(hire_date) =  ?', \r\n'employees'\r\n);\r\n\r\nCALL query_rewrite.flush_rewrite_rules();<\/pre>\n<p><span style=\"text-decoration: underline;\">Avec la r\u00e8gle<\/span><\/p>\n<pre class=\"lang:mysql decode:true\">SELECT \r\n    first_name, \r\n    last_name \r\nFROM employees.employees WHERE year(hire_date) = 2000;\r\n+-------------+------------+------------+\r\n| first_name  | last_name  | birth_date |\r\n+-------------+------------+------------+\r\n| Ulf         | Flexer     | 1960-09-09 |\r\n...\r\n\r\n13 rows in set (0,01 sec)<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\">D&rsquo;autres id\u00e9es<\/span> ?<\/p>\n<p>&#8211; Index hints : l&rsquo;optimiseur n&rsquo;utilisant pas le bon index, vous pouvez r\u00e9\u00e9crire la requ\u00eate en ajoutant les mots cl\u00e9s [<strong>USE<\/strong> | <strong>FORCE<\/strong> | <strong>IGNORE<\/strong>]\u00a0INDEX<\/p>\n<p>&#8211; Limiter la taille du r\u00e9sultat d&rsquo;un SELECT, vous pouvez r\u00e9\u00e9crire la requ\u00eate en ajoutant la clause LIMIT 1000 (ou plus, ou moins).<\/p>\n<p>En fait cela simule l&rsquo;option <strong>&#8211;safe-update<\/strong>\u00a0du client text\u00a0<strong>mysql<\/strong><\/p>\n<pre class=\"lang:sh decode:true\"> $ .\/mysql --help | grep dummy\r\n  -U, --i-am-a-dummy  Synonym for option --safe-updates, -U.<\/pre>\n<p>&#8230;<\/p>\n<p>Sky is the limit \ud83d\ude42 donc \u00e0 vous de jouer maintenant !<\/p>\n<p>A noter que le plugin\u00a0<strong>Rewriter<\/strong>\u00a0ne remplace pas un code optimal et des requ\u00eates correctement optimis\u00e9es \u00e0 la source&#8230;<\/p>\n<p>Cependant, ce plugin peut vraiment \u00eatre utile lorsque l&rsquo;acc\u00e8s \u00e0 la source est compliqu\u00e9 voir\u00a0impossible.<\/p>\n<p>Il est disponible dans <a href=\"http:\/\/dev.mysql.com\/downloads\/mysql\/\" target=\"_blank\">MySQL 5.7<\/a> qui est vraiment une superbe version ! Essayer le plugin\u00a0<strong>Rewriter<\/strong>\u00a0il m\u00e9rite d\u00e9finitivement plus de 30 minutes.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h1>Pour aller plus loin<\/h1>\n<p><span style=\"text-decoration: underline;\">Post-parse query rewrite plugin<\/span><\/p>\n<ul>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/rewriter-query-rewrite-plugin.html\" target=\"_blank\">The Rewriter Query Rewrite Plugin<\/a><\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/plugin-types.html#query-rewrite-plugin-type\" target=\"_blank\">Query Rewrite Plugins<\/a><\/li>\n<li><a href=\"http:\/\/mysqlserverteam.com\/the-query-rewrite-plugins\/\" target=\"_blank\">The Query Rewrite Plugins<\/a><\/li>\n<li><a href=\"http:\/\/www.slideshare.net\/MartinHanssonOracle\/the-query-rewrite-plugin-interface-writing-your-own-plugin\" target=\"_blank\">The Query Rewrite Plugin Interface: Writing Your Own Plugin<\/a><\/li>\n<\/ul>\n<p><span style=\"text-decoration: underline;\">Pre-parse query rewrite plugin<\/span><\/p>\n<ul>\n<li><a href=\"http:\/\/mysqlserverteam.com\/write-yourself-a-query-rewrite-plugin-part-1\/\" target=\"_blank\">Write Yourself a Query Rewrite Plugin: Part 1<\/a><\/li>\n<li><a href=\"http:\/\/mysqlserverteam.com\/write-yourself-a-query-rewrite-plugin-part-2\/\" target=\"_blank\">Write Yourself a Query Rewrite Plugin: Part 2<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Thank you for using MySQL!<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Parfois des requ\u00eates probl\u00e9matiques tournent sur le serveur, mais il n&rsquo;est pas possible de r\u00e9gler le probl\u00e8me \u00e0 la source (Requ\u00eates venant d&rsquo;un ORM par example)<\/p>\n<p>MySQL 5.7 fournit une API pre et post parse query rewrite. Les utilisateurs peuvent \u00e9crire leurs propre plugins ce qui permet d&rsquo;\u00e9liminer le besoin d&rsquo;un proxy.<br \/>\nAvec le post-parse query plugin, il est possible de r\u00e9\u00e9crire une requ\u00eate probl\u00e9matique sans faire de changement au niveau de l&rsquo;application, d&rsquo;ajouter des hints pour les index ou pour l&rsquo;optimiseur, de modifier l&rsquo;ordre des jointures&#8230;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8,270],"tags":[272,274],"class_list":["post-1593","post","type-post","status-publish","format-standard","hentry","category-mysql","category-rewrite-plugin-fr","tag-mysql-5-7-fr","tag-rewrite-plugin-fr"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-pH","jetpack-related-posts":[{"id":788,"url":"https:\/\/dasini.net\/blog\/2009\/10\/12\/mysql-query-cache\/","url_meta":{"origin":1593,"position":0},"title":"MySQL Query cache","author":"Olivier DASINI","date":"12 octobre 2009","format":false,"excerpt":"Le cache est toujours \u00e0 jour car en cas de modification d'une table, toutes les requ\u00eates en relations avec cette table sont invalid\u00e9es. Le cache de requ\u00eates est en g\u00e9n\u00e9ral utile lorsque: Les modifications sur les tables ne sont pas tr\u00e8s fr\u00e9quentes Beaucoup de requ\u00eates de lectures identiques Utilisation de\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":275,"url":"https:\/\/dasini.net\/blog\/2008\/12\/03\/les-nouveautes-de-mysql-51-part-25\/","url_meta":{"origin":1593,"position":1},"title":"Les nouveaut\u00e9s de MySQL 5.1 &#8212; (part 2\/5)","author":"Olivier DASINI","date":"3 d\u00e9cembre 2008","format":false,"excerpt":"Pouvoir automatiser ses t\u00e2ches de mani\u00e8re fiable et simple est le r\u00eave de tout administrateur de base de donn\u00e9es. Le programmateur d'\u00e9v\u00e8nements (Event Scheduler) est un planificateur de t\u00e2ches (CRON-like) embarqu\u00e9 dans MySQL 5.1.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":248,"url":"https:\/\/dasini.net\/blog\/2008\/11\/30\/mysql-5-les-vues-part-37\/","url_meta":{"origin":1593,"position":2},"title":"MySQL 5 : Les vues &#8212; (part 3\/7)","author":"Olivier DASINI","date":"30 novembre 2008","format":false,"excerpt":"Restrictions Lors de la cr\u00e9ation d'une vue, certaines contraintes doivent \u00eatre prises en compte : * Il n'est pas possible de cr\u00e9er un index sur une vue * La vue ne peut pas contenir de sous-requ\u00eates dans la clause FROM du SELECT. * Il n'est pas possible d'utiliser de variables\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1800,"url":"https:\/\/dasini.net\/blog\/2017\/03\/03\/faq-webinar-mysql-group-replication\/","url_meta":{"origin":1593,"position":3},"title":"FAQ Webinar MySQL Group Replication","author":"Olivier DASINI","date":"3 mars 2017","format":false,"excerpt":"Le 1er mars dernier, j'ai pr\u00e9sent\u00e9 lors d'un webinar, la technologie de haute disponibilit\u00e9 MySQL Group Replication. On a explos\u00e9 notre record d'affluence et j'ai \u00e9t\u00e9 inond\u00e9 de questions, preuve s'il en faut de votre int\u00e9r\u00eat, toujours plus important, pour la base de donn\u00e9es Open Source la plus populaire au\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1634,"url":"https:\/\/dasini.net\/blog\/2016\/08\/29\/meetup-mysql-group-replication-mysql-as-a-document-store\/","url_meta":{"origin":1593,"position":4},"title":"Meetup &#8211; MySQL Group Replication &#038; MySQL as a Document Store","author":"Olivier DASINI","date":"29 ao\u00fbt 2016","format":false,"excerpt":"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\u00e9e. Au programme: MySQL Group Replication & MySQL as a Document Store","rel":"","context":"Dans &quot;Conf\u00e9rence&quot;","block_context":{"text":"Conf\u00e9rence","link":"https:\/\/dasini.net\/blog\/category\/conference\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/mysqlhighavailability.com\/wp-content\/uploads\/2014\/09\/1.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1533,"url":"https:\/\/dasini.net\/blog\/2015\/11\/17\/30-mins-avec-json-en-mysql\/","url_meta":{"origin":1593,"position":5},"title":"30 mins avec JSON en MySQL","author":"Olivier DASINI","date":"17 novembre 2015","format":false,"excerpt":"Comme vous le savez MySQL 5.7 est GA. Cette nouvelle mouture de la base de donn\u00e9es open source la plus populaire au monde a plus de 150 nouvelles fonctionnalit\u00e9s. L'une d'entre elle est un type de donn\u00e9es JSON natif ainsi que les fonctions JSON associ\u00e9es. Prenons 30 minutes pour voir\u2026","rel":"","context":"Dans &quot;json&quot;","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json-fr\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1593","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=1593"}],"version-history":[{"count":12,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1593\/revisions"}],"predecessor-version":[{"id":1613,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1593\/revisions\/1613"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1593"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1593"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1593"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}