MySQL運維之神奇的參數MySQL運維之神奇的參數

sql_safe_updates <a href="http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_safe_updates">http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_safe_updates</a>

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

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

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

but，如果你要恢複的表非常大，比如：100g，100t，對于這類型的誤操作，恐怕神仙都難救。

是以，我們這裡通過這個神奇的參數，可以避免掉80%的誤操作場景。 ps: 不能避免100% ，下面的實戰會告訴大家如何破解。

表結構

update 相關測試

update statements must have a where clause that uses a key or a limit clause, or both.

結論： 對于update，隻有兩種場景會被限制

無索引，無limit的情況

無where條件, 無limit的情況

delete相關測試

delete statements must have both

測試結果證明： 關于delete相關，官方文檔描述有誤。

結論： 對于delete，隻有兩種場景會被限制

綜上所述：不管是update，還是delete ，被限制的前提隻有兩個

好了，通過以上的知識，大家都應該很了解，接下來就是實施的問題了。

對于新業務，新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 隻針對兩種場景是不允許的，那就是：

那麼我們就擷取dml語句後面的字段和關鍵字，用來構造我們的全表dml

恩，這樣分析下來，是不是感覺很完美了？ 還是那句話，理想和現實總有差距，那麼來幾條牛逼的漏網之魚看看呗

至少以上兩種類型是抓不到的，是以，還是有問題，那麼繼續找方法。

重新分析下我們的初心，我們的目的是啥？沒錯，我們的目的就是要先找到沒有使用索引的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 了。

注意：

如果線上設定sql_safe_updates = 1 後，業務還有零星的dml被拒絕，業務方可以考慮如下解決方案：

1）如果你確定你的sql語句沒有任何問題，可以這樣： set sql_safe_updates=0; 但是開發必須考慮到這樣做的後果。

2) 可以改寫sql語句，讓其使用上索引字段。

3）為什麼這邊沒有讓大家使用limit呢？因為在大多數場景下，dml + limit = 不确定的sql 。 很可能導緻主從不一緻。 ( dml + limit 的方式，是線上禁止的)

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