技术

对容器云平台的理解 Prometheus 源码分析 并发的成本 基础设施优化 hashicorp raft源码学习 docker 架构 mosn细节 与微服务框架整合 Java动态代理 编程范式 并发通信模型 《网络是怎样连接的》笔记 go细节 codereview mat使用 jvm 线程实现 go打包机制 go interface及反射 如何学习Kubernetes 《编译原理之美》笔记——后端部分 《编译原理之美》笔记——前端部分 Pilot MCP协议分析 go gc 内存管理玩法汇总 软件机制 istio流量管理 Pilot源码分析 golang io 学习Spring mosn源码浅析 MOSN简介 《datacenter as a computer》笔记 学习JVM Tomcat源码分析 Linux可观测性 MVCC 学习存储 学计算 Gotty源码分析 kubernetes operator kaggle泰坦尼克问题实践 kubernetes自动扩容缩容 神经网络模型优化 直觉上理解机器学习 knative入门 如何学习机器学习 神经网络系列笔记 TIDB源码分析 《阿里巴巴云原生实践15讲》笔记 Alibaba Java诊断工具Arthas TIDB存储——TIKV 《Apache Kafka源码分析》——简介 netty中的线程池 guava cache 源码分析 Springboot 启动过程分析 Spring 创建Bean的年代变迁 Linux内存管理 自定义CNI IPAM 扩展Kubernetes 副本一致性 spring redis 源码分析 kafka实践 spring kafka 源码分析 Linux进程调度 让kafka支持优先级队列 Codis源码分析 Redis源码分析 C语言学习 《趣谈Linux操作系统》笔记 docker和k8s安全机制 jvm crash分析 Kubernetes监控 Kubernetes 控制器模型 Prometheus 学习 容器日志采集 容器狂占cpu怎么办? Kubernetes资源调度——scheduler 时序性数据库介绍及对比 influxdb入门 maven的基本概念 《Apache Kafka源码分析》——server Kubernetes objects之编排对象 源码分析体会 《数据结构与算法之美》——算法新解 Kubernetes源码分析——controller mananger Kubernetes源码分析——apiserver Kubernetes源码分析——kubelet Kubernetes介绍 ansible学习 Kubernetes源码分析——从kubectl开始 jib源码分析之Step实现 kubernetes实践 jib源码分析之细节 线程排队 跨主机容器通信 jib源码分析及应用 为容器选择一个合适的entrypoint kubernetes yaml配置 《持续交付36讲》笔记 mybatis学习 程序猿应该知道的 无锁数据结构和算法 CNI——容器网络是如何打通的 为什么很多业务程序猿觉得数据结构和算法没用? 串一串一致性协议 当我在说PaaS时,我在说什么 《数据结构与算法之美》——数据结构笔记 PouchContainer技术分享体会 harbor学习 用groovy 来动态化你的代码 《深入剖析kubernetes》笔记 精简代码的利器——lombok 学习 编程语言的动态性 rxjava3——背压 rxjava2——线程切换 spring cloud 初识 《深入拆解java 虚拟机》笔记 《how tomcat works》笔记 hystrix 学习 rxjava1——概念 Redis 学习 TIDB 学习 分布式计算系统的那些套路 Storm 学习 AQS1——论文学习 Unsafe Spark Stream 学习 linux vfs轮廓 mysql 批量操作优化 《自己动手写docker》笔记 java8 实践 中本聪比特币白皮书 细读 区块链泛谈 比特币 大杂烩 总纲——如何学习分布式系统 hbase 泛谈 forkjoin 泛谈 看不见摸不着的cdn是啥 《jdk8 in action》笔记 程序猿视角看网络 bgp初识 calico学习 AQS2——粗略的代码分析 我们能用反射做什么 web 跨域问题 《clean code》笔记 硬件对软件设计的影响 《Elasticsearch权威指南》笔记 mockito简介及源码分析 2017软件开发小结—— 从做功能到做系统 《Apache Kafka源码分析》——clients dns隐藏的一个坑 《mysql技术内幕》笔记2 《mysql技术内幕》笔记1 log4j学习 为什么netty比较难懂? 回溯法 apollo client源码分析及看待面向对象设计 学习并发 docker 环境(主要运行java项目)常见问题 Scala的一些梗 OpenTSDB 入门 spring事务小结 事务一致性 javascript应用在哪里 《netty in action》读书笔记 netty对http2协议的解析 ssl证书是什么东西 http那些事 苹果APNs推送框架pushy apple 推送那些事儿 编写java框架的几大利器 java内存模型 java exception Linux IO学习 network channel network byte buffer 测试环境docker化实践 netty(七)netty在框架中的使用套路 Nginx简单使用 《Linux内核设计的艺术》小结 Go并发机制及语言层工具 Macvlan Linux网络源代码学习——数据包的发送与接收 《docker源码分析》小结 docker中涉及到的一些linux知识 hystrix学习 Linux网络源代码学习——整体介绍 zookeeper三重奏 数据库的一些知识 Spark 泛谈 链式处理的那些套路 netty(六)netty回顾 Thrift基本原理与实践(二) Thrift基本原理与实践(一) 回调 异步执行抽象——Executor与Future Docker0.1.0源码分析 java gc Jedis源码分析 Redis概述 机器学习泛谈 Linux网络命令操作 JTA与TCC 换个角度看待设计模式 Scala初识 向Hadoop学习NIO的使用 以新的角度看数据结构 并发控制相关的硬件与内核支持 systemd 简介 异构数据库表在线同步 quartz 源码分析 基于docker搭建测试环境(二) spring aop 实现原理简述 自己动手写spring(八) 支持AOP 自己动手写spring(七) 类结构设计调整 分析log日志 自己动手写spring(六) 支持FactoryBean 自己动手写spring(九) 总结 自己动手写spring(五) bean的生命周期管理 自己动手写spring(四) 整合xml与注解方式 自己动手写spring(三) 支持注解方式 自己动手写spring(二) 创建一个bean工厂 自己动手写spring(一) 使用digester varnish 简单使用 关于docker image的那点事儿 基于docker搭建测试环境 分布式配置系统 JVM内存与执行 git spring rmi和thrift maven/ant/gradle使用 再看tcp 缓存系统 java nio的多线程扩展 《Concurrency Models》笔记 回头看Spring IOC IntelliJ IDEA使用 Java泛型 vagrant 使用 Go常用的一些库 Python初学 Goroutine 调度模型 虚拟网络 《程序员的自我修养》小结 VPN(Virtual Private Network) Kubernetes存储 Kubernetes 其它特性 访问Kubernetes上的Service Kubernetes副本管理 Kubernetes pod 组件 使用etcd + confd + nginx做动态负载均衡 如何通过fleet unit files 来构建灵活的服务 CoreOS 安装 CoreOS 使用 Go学习 JVM类加载 硬币和扑克牌问题 LRU实现 virtualbox 使用 ThreadLocal小结 docker快速入门

标签


mysql 批量操作优化

2018年05月18日

前言

近来负责的项目,接到运维反馈的一个问题,mysql 并发过高,同时数据量较大,导致主库和从库同步延迟比较大。

因为当下sql 是一条一条发给sql的,性能较低,因此计划通过批量方式优化性能。

难点:

  1. 搞懂原理
  2. 尽最大可能减少对现有代码的更改

批量优化的原理

介绍MySQL Jdbc驱动的rewriteBatchedStatements参数 要点如下:

  1. MySQL Jdbc驱动在默认情况下会无视executeBatch
  2. 只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL jdbc:mysql://ip:port/db?rewriteBatchedStatements=true

如何批量执行呢?

未打开rewriteBatchedStatements时,根据wireshark嗅探出的mysql报文可以看出,

batchDelete(10条记录)  =>  发送10次delete 请求
batchUpdate(10条记录)  =>  发送10次update 请求
batchInsert(10条记录)  =>  发送10次insert 请求

打开rewriteBatchedStatements后,根据wireshark嗅探出的mysql报文可以看出

batchDelete(10条记录)  =>  发送一次请求,内容为”delete from t where id = 1; delete from t where id = 2; delete from t where id = 3; ….”
batchUpdate(10条记录)  =>  发送一次请求,内容为”update t set … where id = 1; update t set … where id = 2; update t set … where id = 3 …”
batchInsert(10条记录)  =>   发送一次请求,内容为”insert into t (…) values (…) , (…), (…)”

对delete和update,驱动所做的事就是把多条sql语句累积起来再一次性发出去;而对于insert,驱动则会把多条sql语句重写成一条风格很酷的sql语句,然后再发出去。 官方文档说,这种insert写法可以提高性能(”This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements”)

代码方案

  1. sql 层面上,使用case
  2. java 代码层面上,executeBatch
  3. jdbc driver 层面上 加 rewriteBatchedStatements 参数
  4. jdbc framework 层面使用 batch 机制

jdbc

JDBC - Batch Processing

jdbc 原生代码的 批量执行

// Create statement object
Statement stmt = conn.createStatement();
// Set auto-commit to false
conn.setAutoCommit(false);
// Create SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(200,'Zia', 'Ali', 30)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create one more SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(201,'Raj', 'Kumar', 35)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create one more SQL statement
String SQL = "UPDATE Employees SET age = 35 " +
             "WHERE id = 100";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes
conn.commit();

ibatis

公司最早基于 ibatis 弄了一套数据访问 方面的代码生成工具,ibatis 虽老,但大部分时候够用。

Performing Batch processing with iBATIS and Spring

ibatis 两个核心类

SqlMapClient,由FactoryBean构建,传入sql文件地址及dataSource对象, 包括以下方法, SqlMapSession 负责实际的crud 操作

SqlMapSession openSession();
SqlMapSession openSession(Connection var1);
/** @deprecated */
SqlMapSession getSession();
void flushDataCache();
void flushDataCache(String var1);

SqlMapClientTemplate的核心是T execute(SqlMapClientCallback<T> action) 方法,其基本逻辑是

  1. 获取SqlMapSession
  2. 选取java.sql.Connection,或者是SqlMapSession 或者 从spring context 中获取(以支持@Transactional 事务)
  3. 关联Connection 和 SqlMapSession session.setUserConnection(springCon);
  4. action.doInSqlMapClient(session)
  5. release connetion 和 close session

可以看到

  1. 其基本套路 跟jdbc 是一样一样的,只是SqlMapSession 封装了 sql 的获取的部分
  2. SqlMapClientCallback 在java 中就是一个 函数引用,execute 就是一个高阶函数

因此ibatis 提供 批量操作的思路 为

  1. dao 类中提供 int insertBatch(List<T> elements) 方法
  2. 执行execute 方法,SqlMapClientCallback 中的逻辑 与jdbc 大致一样

     getSqlMapClientTemplate().execute(new SqlMapClientCallback() {
      	public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {
       		executor.startBatch();
       		executor.update("insertSomething", "myParamValue");
       		executor.update("insertSomethingElse", "myOtherParamValue");
       		executor.executeBatch();
       		return null;
           }
     });
    

mybatis

Mybatis之批量执行SQL语句

因此从代码上看,mybatis 相对 ibtatis 最大程度上屏蔽了 批量处理的 细节。

mybatis中批量插入的两种方式(高效插入)几种姿势:

  1. foreach 标签
  2. ExecutorType.BATCH

自己实现一个框架

笔者尝试写了一个框架,在不影响代码逻辑的前提下,通过拦截 数据操作的执行,将sql 语句存储到 队列中。

批量处理 分为整体批量和分类批量,缺点如下:

  1. 整体批量,即批量操作时,按原来的操作序列,可能insert、update、delete 交替进行,sql 语句也不一定都在一个表中。

    1. 不能使用事务
    2. 因为操作被延迟进行,所以db 操作无法立即反馈
  2. 分类批量,insert/update/delete 分类批量,除了继承整体批量的缺点外

    1. 操作序列的实际顺序 与 业务调用顺序 可能被打乱,会干扰业务逻辑的正确性

所谓的ibatis 批量,其基本原理(不准确):

  1. SqlMapExecutor.startBatch
  2. SqlMapExecutor.insert(sql), 为sql 创建 PreparedStatement,执行PreparedStatement.addBatch(sql),同一个Batch内有一个Batch 对象维护 PreparedStatement 列表
  3. SqlMapExecutor.insert(sql)
  4. SqlMapExecutor.executeBatch(); Batch 内的所有 PreparedStatement 执行 executeBatch

整体批量情况下,查看com.ibatis.sqlmap.engine.execution.SqlExecutor.Batch.addBatch(StatementScope statementScope, Connection conn, String sql, Object[] parameters) 代码可知,连续两次sql 不一样(参数不算)时,ibatis 会为 sql 创建新的 PreparedStatement,而批量 也是 在一个PreparedStatement内批量才有意义的。

也就是

ibatis batch start
	PreparedStatement batch1 ==> sql1
	PreparedStatement batch2 ==> sql2,sql3,sql4
	PreparedStatement batch3 ==> sql5,sql6
ibatis batch end

若是要保持 业务调用 sql 的顺序,可以使用Statement,可以insert,update,delete 随便来,但相对PreparedStatement有可能会引起性能下降。

实践心得

  1. query 无需批量,insert 有时不能批量,因为某些场景下,先检查本地再插入,因为插入的延后,导致重复数据或唯一索引冲突。因此 通常对update/delete 批量,因为他们通常有where 条件,大多数时候延迟一点没关系
  2. 要处理 分库分表情况。事先按分表+insert/update/delete 对sql 分组
  3. 批量策略,批量阈值 + 间隔相结合,符合一个条件便立即处理累积数据。在条件都不符合前,重启项目对导致数据丢失。要么处理这种情况,要么批量阈值/间隔设置的小点

性能评估

jdbc批处理原理分析

如何评估 批量化操作之后,mysql的负载下降?是否可以最终解决主库与从库同步延迟大的问题?