当前位置：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   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！