mysql模板(mysql模式)

　　神奇的参数—— sql_safe_update

　　官方文档说明：

　　https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_safe_updates

注：兰春同学是安居客DBA Leader，负责数百台MySQL服务器的运维工作。对MySQL数据库非常有激情，一直与我不断在完善MySQL最优配置文件模板。

　　BTW：区区在网易内部推动过sql_safe_update，但是未能成功。诸多原因，想想很是可惜。

　　背景(why)

　　参数sql_safe_update主要用于防范大表的误操作。因为如果只是更改了几条记录，那么说不定业务方可以很容易的根据日志进行恢复。即便没有，也可以通过找二进制日志（binlog）进行逆向操作恢复。如果被误操作的表非常小，其实问题也不大，全备+binlog恢复 or 闪回（点击：拿走不谢，Flashback for MySQL 5.7）都可以进行很好的恢复。

　　But，如果你要恢复的表非常大，比如：100G，100T，对于这类型的误操作，恐怕神仙都难救。所以，这里通过这个神奇的参数，可以避免掉80%的误操作场景。 PS: 不能避免100% ，下面的实战会告诉大家如何破解。

　　生产环境的误操作案例分享

update xx set url_desc='防不胜防'

　　WHERE 4918=4918 AND SLEEP(5)-- xYpp' where id=7046

　　若这表，线上有500G，一次误操作，要恢复500G的数据，需要中断服务很长时间。如果设置了sql_safe_updates，此类事故就可以很华丽的避免掉了。

原理和实战

　　表结构：

　　dba:lc> show create table tbG

　　******************* 1. row ****************

　　Table: tb

　　Create Table: CREATE TABLE `tb` (

　　`id` int(11) NOT NULL,

　　`id_2` int(11) DEFAULT NULL COMMENT 'lc33',

　　`id_3` text,

　　PRIMARY KEY (`id`),

　　KEY `idx_2` (`id_2`)

　　) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　1 row in set (0.01 sec)

UPDATE相关测试

　　* 不带where 条件

　　dba:lc> update tb set id_2=2 ;ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

　　* where 条件有索引，但是没有limit

　　dba:lc> update tb set id_3 = 'bb' where id > 0;^C^C -- query abortedERROR 1317 (70100): Query execution was interrupted

　　* where 条件无索引，也没有limit

　　dba:lc> update tb set id_3 = 'bb' where id_3 = '0';ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

　　* where 条件有索引，有limit

　　dba:lc> update tb set id_3 = 'bb' where id > 0 limit 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

　　* where 条件无索引，有limit

　　dba:lc> update tb set id_3 = 'bb' where id_3 > 0 limit 1;Query OK, 0 rows affected (0.26 sec)Rows matched: 0 Changed: 0 Warnings: 0

　　结论：对于UPDATE操作，只有两种场景会被限制：

无索引，无limit的情况

无where条件, 无limit的情况

DELETE相关测试

　　* 不带where条件dba:lc> delete from tb ;

　　ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column* where 条件有索引，但是没有limitdba:lc> delete from tb where id = 0 ;Query OK, 0 rows affected (0.00 sec)dba:lc> delete from tb where id > 0 ;^C^C -- query abortedERROR 1317 (70100):

　　Query execution was interrupteddba:lc> delete from tb where id_2 > 0 ;^C^C -- query aborted^C^C -- query aborted

　　ERROR 1317 (70100): Query execution was interrupted* where 条件无索引，也没有limitdba:lc> delete from tb where id_3 = 'a' ;

　　ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column* where 条件有索引，有limitdba:lc> delete from tb where id = 205 limit 1 ;Query OK, 1 row affected (0.00 sec)* where 条件无索引，有limitdba:lc> delete from tb where id_3 = 'aaaaa' limit 1 ;

　　Query OK, 1 row affected (0.00 sec)

　　测试结果：关于DELETE操作相关，官方文档描述有误

　　结论：对于DELETE操作，只有两种场景会被限制

　　1. 无索引，无limit的情况

　　2. 无where条件, 无limit的情况

　　综上所述：不管是UPDATE，还是DELETE，被限制的前提只有两个

　　1. 无索引，无limit的情况

　　2. 无where条件, 无limit的情况

　　好了，通过以上的知识，大家都应该很了解，接下来就是实施的问题了。对于新业务，新DB，直接设置这样的参数就好了，再测试环境也设置，这样开发在测试环境就能发现问题，不会在新业务上产生这样危险的语句。

　　对于老业务，怎么办呢？我们的做法：因为我们的MySQL是5.6，所以另外一个神奇的功能就是P_S（performance schema）, 通过P_S，我们可以获取哪些query语句是没有使用索引的。

mysql模板(mysql模式),mysql模板(mysql模式),mysql模板,模板,html,91,第1张

　　这里又会引发另外一个问题，可能是Performance schema的bug，它竟然无法统计DML操作是否使用索引。经过大量的测试后证明：events_statements_summary_by_digest 表里面的SUM_NO_INDEX_USED，SUM_NO_GOOD_INDEX_USED ，对DML操作无效。

　　既然如此，我们所幸对DML语句自己进行分析，将DML转换成对应的SELECT语句。比如： update tb set id = S where id = S; 转换成 select * from tb where id = '1' ...... 然后根据SELECT语句，进行EXPLAIN分析，如果type=ALL表示没有使用索引，这样的语句就是我们认为的全表DML语句了。

　　然而，理想很丰满，现实很骨感。这样的做法很快就出现了问题，因为这里需要自己构造真实的SQL，由于数据分布以及构造的语句不可能真实，所以得到的执行计划谬之千里，type=None。所以，以上方法很可能导致全表的DML没有被抓取出来，so 我们开始想其他办法。

　　说来也简单，参数sql_safe_udpates 只针对两种场景是不允许的，那就是：

　　1. where条件后面无索引，无limit的情况

　　2. 无where条件的情况 , 无limit的情况

　　那么我们就获取dml语句后面的字段和关键字，用来构造我们的全表DML：

　　1. 检查dml 是否是带有limit的语句如果有，允许通过 -- ( 有limit ，肯定可以执行 ) 如果没有，则往下继续判断2. 判断dml SQL有无where条件如果没有，则直接拒绝 -- （没有where，没有limit，肯定是全表扫描的更新，直接拒绝）如果有，则继续往下判断3. 判断where后面的字段是否符合索引前缀原理如果符合，则允许执行 -- （ where条件后面字段有索引，无limit，允许通过）如果不符合，则拒绝 -- （ where条件后面字段无索引，无limit，直接拒绝）

　　恩，这样分析下来，是不是感觉很完美了？还是那句话，理想和现实总有差距，那么来几条牛逼的漏网之鱼看看呗

　　1. 类型转换导致的问题update tb set id=2 where id_change = 1;

　　-- 注意：字段id_change是varchar类型。

　　2. 函数UPDATE pay_log_id SET id=LAST_INSERT_ID(id + 1)

　　至少以上两种类型是抓不到的，所以，还是有问题，那么继续找方法。

　　重新分析下我们的初心，我们的目的是啥？没错，我们的目的就是要先找到没有使用索引的DML，突然脑海中飘来一句话，MySQL自身是否可以打印出没有使用索引的语句呢？

　　果然，去官方文档上一搜index关键字，结果log_queries_not_using_indexes就是我们迫切需要的，但是它会将select也打印出来，不过没关系，我们将select过滤掉即可。

　　so，最后的终极解决方案就是：在测试环境加上log_queries_not_using_indexes=1(long_query_time=1000，这样可以不用混淆)，然后测试环境跑一个月，将没有使用索引的DML语句统统抓住来解决掉，这样就可以安心的上线sql_safe_updates=1 了。

　　注意：

　　当log_queries_not_using_indexes=1 和 sql_safe_updates=1 同时设置的时候：

　　1) delete from tb_1 ; --会被sql_safe_updates拒绝，不会记录到slow log中

　　2) update tb_1 set id = 1; --会被sql_safe_updates拒绝，同时也会被记录到slow log中以上就是两者的区别，善用

　　总结

　　如果线上设置sql_safe_updates = 1 后，业务还有零星的dml被拒绝，业务方可以考虑如下解决方案：

　　1）如果你确保你的SQL语句没有任何问题，可以这样： set sql_safe_updates=0; 但是开发必须考虑到这样做的后果。

　　2) 可以改写SQL语句，让其使用上索引字段。

　　3）为什么这边没有让大家使用limit呢？因为在大多数场景下，dml + limit = 不确定的SQL 。很可能导致主从不一致。 ( dml + limit 的方式，是线上禁止的)

　　各位看官，以上神器请大家慢慢享用。关于PS和sys，如果大家有更加新奇的想法，可以一起讨论研究。