![]()
从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 official community 的版本,这帮
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 * from dept_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 * from dept_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的功能,可以将我们对程序中的一些不好的写法进行一个更改, 实际上执行的使我们替换后的语句.
当然这样的做法必须在验证这样的操作比原有操作性能更高,并且进行一段时间的验证后,才能实现.
![]()
