一条Insert语句怎么优化和解构

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