MySQL Execution Plan--使用Query Rewrite
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL Execution Plan--使用Query Rewrite,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3629字,纯文字阅读大概需要6分钟。
内容图文
![MySQL Execution Plan--使用Query Rewrite](/upload/InfoBanner/zyjiaocheng/515/c1294f22c4c0403ca02ca23d26d8ad93.jpg)
在MySQL的安装目录的share文件夹下,有两个文件用来安装和卸载Query Rewrite Plugin:
install_rewriter.sql: 安装脚本 uninstall_rewriter.sql: 卸载脚本
install_rewriter.sql文件中脚本为:
/* 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 */ CREATE DATABASE IF NOT EXISTS query_rewrite; CREATE TABLE IF NOT EXISTS query_rewrite.rewrite_rules ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, pattern VARCHAR(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, pattern_database VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin, 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, pattern_digest VARCHAR(32), normalized_pattern VARCHAR(100) ) DEFAULT CHARSET = utf8 ENGINE = INNODB; INSTALL PLUGIN rewriter SONAME ‘rewriter.so‘; CREATE FUNCTION load_rewrite_rules RETURNS STRING SONAME ‘rewriter.so‘; DELIMITER // CREATE PROCEDURE query_rewrite.flush_rewrite_rules() BEGIN DECLARE message_text VARCHAR(100); COMMIT; SELECT load_rewrite_rules() INTO message_text; RESET QUERY CACHE; IF NOT message_text IS NULL THEN SIGNAL SQLSTATE ‘45000‘ SET MESSAGE_TEXT = message_text; END IF; END // DELIMITER ; RESET QUERY CACHE;
安装完成后,可以使用下面脚本查看功能是否启用:
SHOW GLOBAL VARIABLES LIKE ‘rewriter_enabled‘; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | rewriter_enabled | ON | +------------------+-------+
演示Demo
1、插入重写规则
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement, pattern_database) VALUES ( "SELECT * FROM TB001 WHERE C1=?", "SELECT *,‘NEW‘ AS C11 FROM TB001 WHERE C1=?", "demodb" );
2、加载规则
## 加载重写规则 CALL query_rewrite.flush_rewrite_rules(); ## 查看当前重写规则 SELECT * FROM query_rewrite.rewrite_rules \G *************************** 1. row *************************** id: 7 pattern: SELECT * FROM TB001 WHERE C1=? pattern_database: demodb replacement: SELECT *,‘NEW‘ AS C11 FROM TB001 WHERE C1=? enabled: YES message: NULL pattern_digest: cf177a9a728143a27502f890698316e5 normalized_pattern: select `*` from `demodb`.`tb001` where (`C1` = ?) 1 row in set (0.00 sec)
3、测试重写:
SELECT * FROM TB001 WHERE C1=2; +--------+------+-----+ | ID | C1 | C11 | +--------+------+-----+ | AAA102 | 2 | NEW | | AAA112 | 2 | NEW | | AAA12 | 2 | NEW | | AAA122 | 2 | NEW | | AAA132 | 2 | NEW | +--------+------+-----+ SELECT * FROM TB001 WHERE C1=2 LIMIT 1; +--------+------+ | ID | C1 | +--------+------+ | AAA102 | 2 | +--------+------+
可以发现SQL语句中的换行或空格对重写规则无影响,但不能在重写模板基础上增加其他内容如LIMIT 字句。
MySQL Execution Plan--使用Query Rewrite
标签:gnu cte received use declare table creat show useful
本文系统来源:https://www.cnblogs.com/gaogao67/p/11757595.html
内容总结
以上是互联网集市为您收集整理的MySQL Execution Plan--使用Query Rewrite全部内容,希望文章能够帮你解决MySQL Execution Plan--使用Query Rewrite所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。