MySQL性能优化大数据量插入数据

最新推荐文章于 2024-07-01 11:42:01 发布

爱研究的小杰杰

最新推荐文章于 2024-07-01 11:42:01 发布

阅读量1.5k

点赞数

文章标签： MySQL 性能优化

本文链接：https://blog.csdn.net/weixin_37945084/article/details/100689370

版权

项目背景：对MySQL数据进行初始化加密
假设待加密表：表名student，列名A，拥有5000w数据量

方案的优化

新增列方案

这个方案缺陷在于，修改列、删除列会重建表，给所有数据行新增一段数据，所以会导致数据量越大越耗时。

如果无法绕过新增列，那么就得使用online DDL，最大限度减少锁的使用。
使用方式：
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
具体内容详见：官方详解Online DDL

测试结果：

数据量(万)	新增列	插入数据	删除列	总耗时
200	47min	31min	56min	2h17min
500	105min	81min	123min	5h15min

新增表方案

与上述不同的地方为：

新增一张副本表，用来作为密文存储，其余不再赘述。
初始数据加密不经过proxy，增量数据加密走proxy

测试结果：

数据量(万)	总耗时
200	0.5h
500	1h9.5min
1000	2h38min

总结
总耗时减少2倍时间
新增列方案重建了3次表，相当于新增表的3倍时间，经项目组所有成员审核过后，新增表方案会丢失触发器，其余与加密前一致，所以综合考虑选择新增表方案。
至于触发器丢失，如果可以获取触发器信息，那么选择重新触发器；如果不可以获取触发器信息，那么提示客户：不允许加密拥有触发器的表。

MySQL配置的优化

通过查阅资料，摘取有利于提升性能的参数，将其列出。

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysql.server]
pid-file = /var/lib/mysql/localhost.pid

[mysqld]
port = 3306
socket =/var/lib/mysql/mysql.sock

# 网络传输中一次消息传输量的最大值。系统默认值 为1MB，最大值是1GB，必须设置1024的倍数。
# 因为后续我们在代码中是使用prepareStatement.addBatch()，单次传输的值会比较大，所以这个参数必须加大。
max_allowed_packet = 32M
# 缓冲池大小，只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。
innodb_buffer_pool_size = 5120M
innodb_buffer_pool_instances=3
innodb_additional_mem_pool_size = 1024M 
innodb_log_files_in_group = 3 
innodb_lock_wait_timeout = 120 
# 刷新日志频率
open_files_limit = 10240 
back_log = 600
innodb_log_file_size=110M
# 连接数
max_connections = 3000 
max_connect_errors = 6000 
# 多线程
thread_cache_size = 300 
thread_concurrency = 16
thread_stack = 192KB
innodb_write_io_threads = 32
innodb_purge_threads=1
# 刷新脏页频率
innodb_max_dirty_pages_pct=90
wait_timeout=300

配置详解：

innodb缓冲池相关配置
MySQL性能优化

代码优化

由单线程的插入，改为多连接多线程插入，伪代码如下。

环境检查;
建立加密前环境;
创建Connection连接池;
创建线程池;
// 忽略外键检查
statement.executeUpdate("set foreign_key_checks = 0");
// 忽略唯一性检查
statement.executeUpdate("set unique_checks = 0");
查询数据;
while(结果集){
	获取空闲连接;
	创建prepareStatement;
	将数据加密好，拼接进preparestatement后;
	// 加入批量，原理就是将多个插入语句用逗号拼接起来，一次性发送，提升网络传输的效率
	prepareStatement.addBatch();
	if(达到批量){
		// Java侧可继续拼接数据，与此同时，MySQL也正在插入数据，减少等待时间，提高效率
		将prepareStatement扔给线程池去执行;
	}
}

通过在当前会话改动动态变量，取消检查，可以降低插入前的约束，减少判断，对效率明显提升。
具体内容详见：官方详解Dynamic System Variables（动态系统变量）

测试结果：

数据量(万)	总耗时
5000	3h48min

注：该结果是基于上述所有优化后的最终结果

总结

忽略外键、唯一性检查，减少MySQL插入时的约束，纯粹地进行插入操作。
使用简单的线程池或者生产者消费者模式，最终目标都是让MySQL不停地插入，只有像流水线一样，整体的效率才会上去。

最后总结
从最初的5000w预估50h都可能加密不完，到最后的3h43min加密完成，期间的解决思路是由大至小，由生产者到消费者。

2019年10月08日10:43:31 最新更新
MySQL5.1.40版本的更新内容

当连接属性cachePrepStmts和 useServerPrepStmts都设置为 true且服务器端准备好的语句都设置为不可缓冲时，发生内存泄漏，这导致准备好的语句无法被客户端关闭，并且准备好的语句的数量不断增加。
当发生上述内存泄漏时，它不会使Connector / J失败，因为当达到最大准备语句数时，Connector / J切换为使用客户端准备语句。但是，当 rewriteBatchedStatements还设置为true时，则不会发生切换到客户端预备语句的情况，并且当客户端想要创建比预备语句多得多的预备语句时，Connector / J抛出MySQLSyntaxErrorException（“ 不能创建超过max_prepared_stmt_count语句 ”）。允许的。
此修复程序纠正了准备语句的处理方式，从而避免了上述两个问题。（缺陷号22954007，错误＃80615）

使用服务器端准备语句且使用批量形式，最大占位符个数为65535
所以，最大执行语句=65535/字段数

由于以上限制，面对大字段数时，单次批量值下降，从而使得速度无法上升。
那么需要采用客户端准备语句，但目前遇到一个问题，客户端准备语句精度丢失。
如果解决精度丢失问题，客户端可以准备好语句，再发送给服务端，减少服务端压力。