MYSQL Rewriter plugin 我那什么拯救你,垃圾系统
从SQL 语言, DB 和 开发人员之间对SQL的撰写的目的和本质的理解就有不同,一个是要以逻辑为基础满足业务需求即可,性能方面的需求这变得不是核心人物,而DB 人员则对SQL的撰写和优化作为一个核心的目的,性能是重要的,所以矛盾就产生了. 如何解决这样的矛盾,其实日常无非几个方法 1 DB 懂得业务并且在项目之初就介入,在表设计和优化方前期就下功夫,避免开发和DB之间的矛盾,当然能做到这样的单位少之又少,原因很复杂,但大多不是技术上的问题. 2 在硬件上面做文章,软件设计的不好,硬件上给力,什么SSD ,大内存, 都给上,这样双方的矛盾也能缓解,所谓弄用钱办的事情,都不是事情,但对于项目和开发,以及DB,都不是什么好事,这些人都没有进步,这样的事情大部分发生在国企,或事业单位. 3 就是今天的要说,DB 如果管不了开发,项目,那有没有方法通过自己的手段来挽救,你所管辖的系统. 自MYSQL 8.012 可以开始进行相关的 INSERT ,UPDATE ,DELETE ,REPLACE, 提出了query rewrite plugin 组件.在此之前组件仅仅支持SELECT 语句的变更. 首先我们先确认,当前的MYSQL 是否有相关的插件和支持,这里使用的MYSQL8.019 officialcommunity 的版本,这帮 show global variables like 'lc_messages_dir'; 然后我们将组件加载到当前的MYSQL中,到响应的目录,直接执行 insert_rewriter.sql 加载到当前系统 执行后我们的MYSQL会多一个数据库,query_rewrite select plugin_name,plugin_status, plugin_version from information_schema.plugins where plugin_name='Rewriter'; 在确认以上信息后,下面就可以进行一些简单的样例 1 经常有人直接对数据库中的大表进行操作,这样的操作直接影响了系统的安全性,属于需要防范和进行取消的操作,但实际上即使有了相关的数据库审核和数据库的规范,但实际上还是不能将这一行的行为杜绝. 那下面的工作就可以帮助到你,这里我们的employees 数据库中有一个表dept_emp 表比较大3000万数据,但经常有人 select * from dept_no 这个表并且没有条件. 那我们就可以通过这个rewrite 的plug-in 将这一行的语句直接封杀. insert into queery_rewrite.rewrite_rules (id,pattern_database,pattern,replacement) values (1,'employees','select * from dept_emp','select * from dept_emp limit 1'); 我们在所在的数据库上直接执行上述语句,并且 call query_rewrite.flush_rewrite_rules(); 执行上述存储过程,让上述规则生效. 在此查询的时候,语句就不会将所有的记录抛出了,而是直接替换这个语句变成 select * fromdept_emp limit 1; 这样的形式 那如此就完事了,当然没有,如果他写成这样的形式, select * from dept_emp limit 100000;那就会显示10万条数据吗? 显然不是的我们稍加处理,让他这样的操作也不可以,当然事先要知道如此逻辑在业务中是可以通过的. insert into query_rewrite.rewrite_rules (id,pattern_database,pattern,replacement) values (2,'employees','select * from dept_emp limit ?','select * from dept_emp limit 10'); call query_rewrite.flush_rewrite_rules(); 如此执行上述语句后,他在忙活也只能显示10条数据. 这里面 ? 问好代表一个变量,也就是无论limit 后面填写什么变量,最后都是变换成 select * fromdept_emp limit 10; 那么下面我们模拟一个复杂的操作 举例我们下面的语句中我们不想让用户使用in 而是想使用 exists update employees set hire_date = '1986-08-28' where emp_no in ( select b.emp_no from (select * from employees) as b where b.hire_date > '1986-06-26'); update employees set hire_date = '1986-08-28' where exists ( select * from (select * from employees) as b where b.hire_date > '1986-06-26'); 在操作完毕后,我们直接运行 下面的语句, 或者通过explain 来看都是执行了这个 in 的语句,但我们打开general log后, 记录的执行的语句 所以通过rewrite 这个MYSQL的功能,可以将我们对程序中的一些不好的写法进行一个更改, 实际上执行的使我们替换后的语句. 当然这样的做法必须在验证这样的操作比原有操作性能更高,并且进行一段时间的验证后,才能实现. 本文分享自微信公众号 - AustinDatabases（AustinDatabases）。如有侵权，请联系 support@oschina.cn 删除。本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
