這是學習筆記的第 2207篇文章
讀完需要
9
分鐘
速讀僅需7分鐘
今天同事問了一個關於DML的優化問題，SQL是一條Insert語句：
insert into crash_extend_bak select a.extend_id,a.crash_id,a.system_log,a.user_log,a.crash_stack,b.java_stack_map from crash_extend a where a.crash_id in (select b.crash_id from crash_record_bak b where a.crash_id=b.crash_id)
執行的時候拋出了下面的錯誤：
ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
看起來這個操作的代價比較高，導致binlog都承載不了這樣大的一個事務。
看到這裡，我的大體思路已經有了，首先是定位數據量，然後是定位瓶頸。
其中表crash_extend 的數據量為200萬左右，大小為1G左右，crash_record_bak 的數據量遠大於200萬，表的存儲容量在70G左右，具體的數量暫未知。
所以按照這些基本信息，我們來梳理基本的邏輯就有眉目了，輸出結果是以crash_extend 的為準，而且crash_extend 的extend_id的字段會和 crash_record_bak的字段 crash_id進行匹配。
所以我先拆解了邏輯，
查看crash_extend的數據量。
| 2130620 |
所以基本能夠確定整個查詢的量級。
然後我去掉了crash_record_bak的字段（該字段為json類型）進行查詢：
select a.extend_id,a.crash_id,a.system_log,a.user_log,a.crash_stack from crash_extend a,crash_record_bak b where a.crash_id=b.crash_id;
耗時在3分鐘左右。
然後寫入數據：
insert into crash_extend_bak(extend_id,crash_id,system_log,user_log,crash_sta
ck)select a.extend_id,a.crash_id,a.system_log,a.user_log,a.crash_stack from crash_extend a,crash_record_bak b where a.crash_id=b.crash_id;
Query OK, 2106507 rows affected (4 min 50.77 sec)
這個過程耗時為5分鐘左右。
接下來定位瓶頸的部分，對json字段進行更新。
改造為：
update crash_extend_bak a set a.java_stack_map=(select java_stack_map from cr
ash_record_bak b where a.crash_id=b.crash_id) where extend_id between xxxx and xxxx;
Query OK, 8867 rows affected (7.47 sec)
經過測試，基本是1萬條左右的效率是比較穩定的，所以拆解一下就可以保證基本的效率了。
可以看到一條Insert語句經過拆解和定位，可以拆解為多條語句進行優化和改進。
閱讀更多 楊建榮的學習筆記 的文章
