Mysql批量更新性能优化学习

转自：https://juejin.cn/post/7043596855829069861

1.更新

对表做多行更新的时候通常会遇到以下两种情况：

1. 单语句批量更新（update a=a+1 where pk > 500）
2. 多语句批量更新（update a=1 where pk=1;update a=12 where pk=7;...）

多语句批量更新脚本：

#!/bin/bash

start_time=`date +%s%3N`
/ssd/tmp/mysql/bin/mysql -h127.0.0.1 -uroot -P3316 -pabc123 -e "use test;source /ssd/tmp/tmp/1000/update.sql;"
end_time=`date +%s%3N`
echo "执行时间为："$(($end_time-$start_time))"ms"

结果：

影响性能的地方主要原因： 

1. 如果会话是auto_commit=1，每次执行update语句后都要执行commit操作。commit操作耗费时间较久，会产生两次磁盘同步（写binlog和写redo日志）。在进行比对测试时，尽量将多个语句放到一个事务内，保证只提交一次事务。 
2. 向后端发送多语句时，后端每处理一个语句均会向client返回一个response包，进行一次交互。如果多语句使用一个事务的话，网络IO交互应该是影响性能的主要方面。之前在性能测试时发现网卡驱动占用cpu很高。//后面一句话的意思是，如果多语句使用一个事务，那么一次传输的数据请求就比较大，所以应该是传输数据占用内存、网络IO是主要影响。

 2.批量更新优化

2.1 使用prepare 

减少执行语句的解析时间来提高执行效率。但效率没有提升，一条update被拆分为2条，在MySQL客户端和MySQL进程之间的通讯次数增加了，所以增加了总耗时。

//具体没深入了解。

2.2 事务批量执行

以下脚本用于生成1000行update语句在一个事务内，更新c2的值等于1000以内的随机数：

#!/bin/bash
echo "begin;" > update.sql
for i in {1..1000}
do
        echo "update t1 set c2=$((RANDOM%1000+1)) where c1=$i;" >> update.sql
done
echo "commit;" >> update.sql

echo和print作用类似。生成sql语句如下：

 能明显优化：

 2.3 特殊sql

//没看，太复杂了。