MySQL運維之神奇的引數

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

背景(why)

主要是針對大表的誤操作。

如果只是更改了幾條記錄，那麼說不定業務方可以很容易的根據日誌進行恢復。即便沒有，也可以通過找binlog，進行逆向操作恢復。

如果被誤操作的表非常小，其實問題也不大，全備+binlog恢復 or 閃回 都可以進行很好的恢復。

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 tb;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb    | CREATE TABLE `tb` (
  `id` int(11) NOT NULL,
  `id_2` int(11) DEFAULT NULL COMMENT `lc22222233333`,
  `id_3` text,
  PRIMARY KEY (`id`),
  KEY `idx_2` (`id_2`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

• update 相關測試

UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both.

* 不帶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 aborted
ERROR 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，只有兩種場景會被限制

1. 無索引，無limit的情況
2. 無where條件, 無limit的情況

• delete相關測試

DELETE statements must have both

* 不帶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 條件有索引，但是沒有limit

dba: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 aborted
ERROR 1317 (70100): Query execution was interrupted

dba:lc> explain select * from tb where id_2 > 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb    | NULL       | ALL  | idx_2,idx_3   | NULL | NULL    | NULL | 245204 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

dba:lc> delete from tb where id_2 > 0 ;
^C^C -- query aborted
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted


* where 條件無索引，也沒有limit

dba: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 條件有索引，有limit

dba:lc> delete from tb where id = 205 limit 1 ;
Query OK, 1 row affected (0.00 sec)

* where 條件無索引，有limit

dba: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語句是沒有使用索引的。

這裡又會引發另外一個問題，可能是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，如果大家有更加新奇的想法，可以一起討論研究。