当前位置:Gxlcms > 数据库问题 > MySQL Execution Plan--使用Query Rewrite

MySQL Execution Plan--使用Query Rewrite

时间:2021-07-01 10:21:17 帮助过:9人阅读

在MySQL的安装目录的share文件夹下,有两个文件用来安装和卸载Query Rewrite Plugin:

  1. install_rewriter.sql: 安装脚本
  2. uninstall_rewriter.sql: 卸载脚本
  1. install_rewriter.sql文件中脚本为:
  1. <span style="color: #008080;">/*</span><span style="color: #008080;"> Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
  2. This program is free software; you can redistribute it and/or modify
  3. it under the terms of the GNU General Public License as published by
  4. the Free Software Foundation; version 2 of the License.
  5. This program is distributed in the hope that it will be useful,
  6. but WITHOUT ANY WARRANTY; without even the implied warranty of
  7. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  8. GNU General Public License for more details.
  9. You should have received a copy of the GNU General Public License
  10. along with this program; if not, write to the Free Software Foundation,
  11. 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA </span><span style="color: #008080;">*/</span>
  12. <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">DATABASE</span> <span style="color: #0000ff;">IF</span> <span style="color: #808080;">NOT</span> <span style="color: #808080;">EXISTS</span><span style="color: #000000;"> query_rewrite;
  13. </span><span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span> <span style="color: #0000ff;">IF</span> <span style="color: #808080;">NOT</span> <span style="color: #808080;">EXISTS</span><span style="color: #000000;"> query_rewrite.rewrite_rules (
  14. id </span><span style="color: #0000ff;">INT</span> <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span> AUTO_INCREMENT <span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span><span style="color: #000000;">,
  15. pattern </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">10000</span>) <span style="color: #0000ff;">CHARACTER</span> <span style="color: #0000ff;">SET</span> utf8 COLLATE utf8_bin <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">,
  16. pattern_database </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">20</span>) <span style="color: #0000ff;">CHARACTER</span> <span style="color: #0000ff;">SET</span><span style="color: #000000;"> utf8 COLLATE utf8_bin,
  17. replacement </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">10000</span>) <span style="color: #0000ff;">CHARACTER</span> <span style="color: #0000ff;">SET</span> utf8 COLLATE utf8_bin <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">,
  18. enabled ENUM(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">YES</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">NO</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">CHARACTER</span> <span style="color: #0000ff;">SET</span> utf8 COLLATE utf8_bin <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span>
  19. <span style="color: #0000ff;">DEFAULT</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">YES</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
  20. message </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">1000</span>) <span style="color: #0000ff;">CHARACTER</span> <span style="color: #0000ff;">SET</span><span style="color: #000000;"> utf8 COLLATE utf8_bin,
  21. pattern_digest </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">32</span><span style="color: #000000;">),
  22. normalized_pattern </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
  23. ) </span><span style="color: #0000ff;">DEFAULT</span> CHARSET <span style="color: #808080;">=</span> utf8 ENGINE <span style="color: #808080;">=</span><span style="color: #000000;"> INNODB;
  24. INSTALL PLUGIN rewriter SONAME </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">rewriter.so</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  25. </span><span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">FUNCTION</span> load_rewrite_rules <span style="color: #0000ff;">RETURNS</span><span style="color: #000000;"> STRING
  26. SONAME </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">rewriter.so</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  27. DELIMITER </span><span style="color: #808080;">//</span>
  28. <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;"> query_rewrite.flush_rewrite_rules()
  29. </span><span style="color: #0000ff;">BEGIN</span>
  30. <span style="color: #0000ff;">DECLARE</span> message_text <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">);
  31. </span><span style="color: #0000ff;">COMMIT</span><span style="color: #000000;">;
  32. </span><span style="color: #0000ff;">SELECT</span> load_rewrite_rules() <span style="color: #0000ff;">INTO</span><span style="color: #000000;"> message_text;
  33. RESET QUERY CACHE;
  34. </span><span style="color: #0000ff;">IF</span> <span style="color: #808080;">NOT</span> message_text <span style="color: #0000ff;">IS</span> <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">THEN</span><span style="color: #000000;">
  35. SIGNAL SQLSTATE </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">45000</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">SET</span> MESSAGE_TEXT <span style="color: #808080;">=</span><span style="color: #000000;"> message_text;
  36. </span><span style="color: #0000ff;">END</span> <span style="color: #0000ff;">IF</span><span style="color: #000000;">;
  37. </span><span style="color: #0000ff;">END</span> <span style="color: #808080;">//</span><span style="color: #000000;">
  38. DELIMITER ;
  39. RESET QUERY CACHE;</span>

安装完成后,可以使用下面脚本查看功能是否启用:

  1. SHOW GLOBAL VARIABLES <span style="color: #808080;">LIKE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">rewriter_enabled</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  2. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----------------+-------+</span>
  3. <span style="color: #808080;">|</span> Variable_name <span style="color: #808080;">|</span> Value <span style="color: #808080;">|</span>
  4. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----------------+-------+</span>
  5. <span style="color: #808080;">|</span> rewriter_enabled <span style="color: #808080;">|</span> <span style="color: #0000ff;">ON</span> <span style="color: #808080;">|</span>
  6. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----------------+-------+</span>

 

演示Demo

1、插入重写规则

  1. <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span><span style="color: #000000;"> query_rewrite.rewrite_rules
  2. (pattern, replacement, pattern_database)
  3. </span><span style="color: #0000ff;">VALUES</span><span style="color: #000000;">
  4. (
  5. "</span><span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> TB001 <span style="color: #0000ff;">WHERE</span> C1<span style="color: #808080;">=</span><span style="color: #000000;">?",
  6. "</span><span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">NEW</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">AS</span> C11 <span style="color: #0000ff;">FROM</span> TB001 <span style="color: #0000ff;">WHERE</span> C1<span style="color: #808080;">=</span><span style="color: #000000;">?",
  7. "demodb"
  8. );</span>

2、加载规则

  1. <span style="color: #000000;">## 加载重写规则
  2. CALL query_rewrite.flush_rewrite_rules();
  3. ## 查看当前重写规则
  4. </span><span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> query_rewrite.rewrite_rules \G
  5. </span><span style="color: #808080;">***************************</span> <span style="color: #800000; font-weight: bold;">1</span>. row <span style="color: #808080;">***************************</span><span style="color: #000000;">
  6. id: </span><span style="color: #800000; font-weight: bold;">7</span><span style="color: #000000;">
  7. pattern: </span><span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> TB001 <span style="color: #0000ff;">WHERE</span> C1<span style="color: #808080;">=</span><span style="color: #000000;">?
  8. pattern_database: demodb
  9. replacement: </span><span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">NEW</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">AS</span> C11 <span style="color: #0000ff;">FROM</span> TB001 <span style="color: #0000ff;">WHERE</span> C1<span style="color: #808080;">=</span><span style="color: #000000;">?
  10. enabled: YES
  11. message: </span><span style="color: #0000ff;">NULL</span><span style="color: #000000;">
  12. pattern_digest: cf177a9a728143a27502f890698316e5
  13. normalized_pattern: </span><span style="color: #0000ff;">select</span> `<span style="color: #808080;">*</span>` <span style="color: #0000ff;">from</span> `demodb`.`tb001` <span style="color: #0000ff;">where</span> (`C1` <span style="color: #808080;">=</span><span style="color: #000000;"> ?)
  14. </span><span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)

3、测试重写:

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> TB001
  2. </span><span style="color: #0000ff;">WHERE</span> C1<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">;
  3. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+-----+</span>
  4. <span style="color: #808080;">|</span> ID <span style="color: #808080;">|</span> C1 <span style="color: #808080;">|</span> C11 <span style="color: #808080;">|</span>
  5. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+-----+</span>
  6. <span style="color: #808080;">|</span> AAA102 <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> NEW <span style="color: #808080;">|</span>
  7. <span style="color: #808080;">|</span> AAA112 <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> NEW <span style="color: #808080;">|</span>
  8. <span style="color: #808080;">|</span> AAA12 <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> NEW <span style="color: #808080;">|</span>
  9. <span style="color: #808080;">|</span> AAA122 <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> NEW <span style="color: #808080;">|</span>
  10. <span style="color: #808080;">|</span> AAA132 <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> NEW <span style="color: #808080;">|</span>
  11. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+-----+</span>
  12. <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> TB001
  13. </span><span style="color: #0000ff;">WHERE</span> C1<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">2</span> LIMIT <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
  14. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+</span>
  15. <span style="color: #808080;">|</span> ID <span style="color: #808080;">|</span> C1 <span style="color: #808080;">|</span>
  16. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+</span>
  17. <span style="color: #808080;">|</span> AAA102 <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span>
  18. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+</span>

可以发现SQL语句中的换行或空格对重写规则无影响,但不能在重写模板基础上增加其他内容如LIMIT 字句。

MySQL Execution Plan--使用Query Rewrite

标签:gnu   cte   received   use   declare   table   creat   show   useful   

人气教程排行