这是学习笔记的第 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语句经过拆解和定位，可以拆解为多条语句进行优化和改进。