时间:2021-07-01 10:21:17 帮助过:9人阅读
在MySQL的安装目录的share文件夹下,有两个文件用来安装和卸载Query Rewrite Plugin:
- install_rewriter.sql: 安装脚本
- uninstall_rewriter.sql: 卸载脚本
- install_rewriter.sql文件中脚本为:
- <span style="color: #008080;">/*</span><span style="color: #008080;"> Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
- This program is free software; you can redistribute it and/or modify
- it under the terms of the GNU General Public License as published by
- the Free Software Foundation; version 2 of the License.
- This program is distributed in the hope that it will be useful,
- but WITHOUT ANY WARRANTY; without even the implied warranty of
- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
- GNU General Public License for more details.
- You should have received a copy of the GNU General Public License
- along with this program; if not, write to the Free Software Foundation,
- 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA </span><span style="color: #008080;">*/</span>
- <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;
- </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 (
- 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;">,
- 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;">,
- 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,
- 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;">,
- 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>
- <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;">,
- 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,
- pattern_digest </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">32</span><span style="color: #000000;">),
- normalized_pattern </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
- ) </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;
- 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;">;
- </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
- SONAME </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">rewriter.so</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
- DELIMITER </span><span style="color: #808080;">//</span>
- <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;"> query_rewrite.flush_rewrite_rules()
- </span><span style="color: #0000ff;">BEGIN</span>
- <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;">);
- </span><span style="color: #0000ff;">COMMIT</span><span style="color: #000000;">;
- </span><span style="color: #0000ff;">SELECT</span> load_rewrite_rules() <span style="color: #0000ff;">INTO</span><span style="color: #000000;"> message_text;
- RESET QUERY CACHE;
- </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;">
- 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;
- </span><span style="color: #0000ff;">END</span> <span style="color: #0000ff;">IF</span><span style="color: #000000;">;
- </span><span style="color: #0000ff;">END</span> <span style="color: #808080;">//</span><span style="color: #000000;">
- DELIMITER ;
- RESET QUERY CACHE;</span>
安装完成后,可以使用下面脚本查看功能是否启用:
- 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;">;
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----------------+-------+</span>
- <span style="color: #808080;">|</span> Variable_name <span style="color: #808080;">|</span> Value <span style="color: #808080;">|</span>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----------------+-------+</span>
- <span style="color: #808080;">|</span> rewriter_enabled <span style="color: #808080;">|</span> <span style="color: #0000ff;">ON</span> <span style="color: #808080;">|</span>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----------------+-------+</span>
演示Demo
1、插入重写规则
- <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span><span style="color: #000000;"> query_rewrite.rewrite_rules
- (pattern, replacement, pattern_database)
- </span><span style="color: #0000ff;">VALUES</span><span style="color: #000000;">
- (
- "</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;">?",
- "</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;">?",
- "demodb"
- );</span>
2、加载规则
- <span style="color: #000000;">## 加载重写规则
- CALL query_rewrite.flush_rewrite_rules();
- ## 查看当前重写规则
- </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
- </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;">
- id: </span><span style="color: #800000; font-weight: bold;">7</span><span style="color: #000000;">
- 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;">?
- pattern_database: demodb
- 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;">?
- enabled: YES
- message: </span><span style="color: #0000ff;">NULL</span><span style="color: #000000;">
- pattern_digest: cf177a9a728143a27502f890698316e5
- 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;"> ?)
- </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、测试重写:
- <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> TB001
- </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;">;
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+-----+</span>
- <span style="color: #808080;">|</span> ID <span style="color: #808080;">|</span> C1 <span style="color: #808080;">|</span> C11 <span style="color: #808080;">|</span>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+-----+</span>
- <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>
- <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>
- <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>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+-----+</span>
- <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> TB001
- </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;">;
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+</span>
- <span style="color: #808080;">|</span> ID <span style="color: #808080;">|</span> C1 <span style="color: #808080;">|</span>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------+------+</span>
- <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>
- <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