批量插入更新 MySQL 服务优化

最新推荐文章于 2024-05-30 11:38:10 发布
原创 最新推荐文章于 2024-05-30 11:38:10 发布 · 718 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#SQL #MySQL #Hive #批量 #插入

通过解耦无效数据模式、加速Hive取数、合理设置并发数、采用高效数据格式及子进程分片读取等策略，将500万数据处理速度从5小时提升到80秒，内存消耗降至优化前的2.2%。

1 原执行步骤

  1. 从Hive中拉数据产出数据文件
  2. 主进程读文件，按子进程数切分
  3. 根据待插入集合，使用 ON DUPLICATE KEY UPDATE 批量更新或插入 table1（假设supply_id,order_id为联合唯一索引） 和 table2
INSERT INTO table1
(supply_id,order_id,num,end_time,status)
VALUES
(50441900,111,0.0,'2019-09-17 23:59:59',1),
(50441900,112,0.0,'2019-09-17 23:59:59',1)
ON DUPLICATE KEY UPDATE
supply_id=VALUES(supply_id),order_id=VALUES(order_id),num=VALUES(num),end_time=VALUES(end_time),status=VALUES(status)

INSERT INTO table2
(product_id,city_id,supply_id)
VALUES
(50,441900,50441900),
(50,442000,50442000)
ON DUPLICATE KEY UPDATE
product_id=VALUES(product_id),city_id=VALUES(city_id)
  1. 根据 table1 中supply_id下的列表集合，对在相同supply_id下order_id不在插入更新列表中且状态为1的记录设为无效
UPDATE table1 SET status='0'
WHERE status='1' AND supply_id='50441900' 
AND order_id NOT IN (111,112)

2 原执行步骤性能现状

在这里插入图片描述

  1. 执行速度慢，220万，执行3个小时（线上每小时500万，分业务刷入）
  2. 内存占用大，220万，9×6.2%=55.8%（60.8G），500万数据总共近700M

3 性能剖析及优化

优化整体性能提升：500万数据，速度5h->80s，内存消耗量为优化前的2.2%

3.1 优化点1：无效数据模式优化为前后两次PT数据对比取数

性能剖析：

根据 supply_id 列表集合，对在相同supply_id下order_id不在插入更新列表中且状态为1的记录设为无效，语句按条件无效，拼接时两层遍历，速度奇慢（1万条数据，耗时5分钟）；且依赖supply_id分片，造成数据倾斜（如：一批5w，一批100w）

优化：

解耦对supply_id分片的依赖，使用上一时段pt数据与当前pt数据的差集，直接用HiveSQL把无效数据拉出，同时无效的SQL使用效率更高的（INSERT INTO ON DUPLICATE KEY UPDATE）批量插入

无效数据和有效数据解耦，可均匀分片，避免数据倾斜；同时解决supply_id分片，对分片中间supply_id无法无效的问题

3.2 优化点2：hive取数加速

性能剖析：

在优化点1的基础上，HiveSQL由原模式的一条SQL变为两条，串行执行较慢，需加速

优化：

两条SQL并发执行，并加入HiveSQL提速配置

--hiveconf hive.execution.engine=tez 
--hiveconf hive.exec.parallel=true 
--hiveconf hive.exec.parallel.thread.number=8

3.3 优化点3：并发数、批量数合理设置

性能剖析：

原并发写库为8进程，两张表串行执行，批量执行数量为20

优化：

由于两张表可并发写库，可增加进程数，两张表的写库在不同进程间处理

而进程数的设置需要考虑线上库所在机器的CPU核数，考虑线上服务稳定运行，进程数要小于CPU核数

批量插入条数，要考虑到速度和主从延迟，如果批量插入数据过多，会导致主从延迟明显

针对公司机器40核，设置并发数为11，第一个进程写入table2，其它10进程并发写入table1，批量数设置为1000

3.4 优化点4：使用耗内存更少的数据格式

性能剖析：

在读数据使使用了pandas，处理过程中把dataframe转换为List，而该形式会存储字段名称，相同数据量，根据下面两次内存占用分析，可知该数据格式是datafrme内存占用的16倍

内存占用分析（100万数据，主进程切分数据，使用List形式）：

Line #    Mem usage    Increment   Line Contents
================================================
    19     91.2 MiB     91.2 MiB   @profile
    20                             def get_data1_list():
    33     91.2 MiB      0.0 MiB       try:
    34     91.2 MiB      0.0 MiB           result_supply_list = list()
    35     91.2 MiB      0.0 MiB           result_detail_list = list()
    36     91.2 MiB      0.0 MiB           sids_list = list()
    37                            
    41     91.2 MiB      0.0 MiB           columns = ['product_id', 'city_id', 'supply_id', 'order_id', 'num', 'end_time', 'status']
    42    266.9 MiB    175.8 MiB           df = pd.read_table('data.txt', sep='\t', header=None, names=columns, dtype='str')
    43                            
    44    328.0 MiB     61.1 MiB           data_supply = df[['product_id', 'city_id', 'supply_id']]
    47    328.1 MiB      0.0 MiB           data_supply['status'] = str(1)
    48    290.3 MiB      0.0 MiB           data_supply = data_supply.drop_duplicates()
    49                            
    50    389.5 MiB     99.2 MiB           data_detail = df[['supply_id', 'order_id', 'num', 'end_time', 'status']]
    53    401.2 MiB     11.8 MiB           data_detail = data_detail.drop_duplicates()
    54                            
    55    403.5 MiB      2.3 MiB           detail_supply_id = list(set(data_detail['supply_id']))
    56    403.5 MiB      0.0 MiB           len_detail = len(detail_supply_id)
    57    403.5 MiB      0.0 MiB           avg_len = len_detail / conf.MULTI_CNT
    58                            
    59   2602.2 MiB      0.0 MiB           for i in range(1, conf.MULTI_CNT + 1):
    60   2602.2 MiB      0.0 MiB               if i != conf.MULTI_CNT:
    61   2602.2 MiB      0.2 MiB                   s_ids = detail_supply_id[(i-1) * avg_len: i * avg_len]
    62                                         else:
    63   2351.8 MiB      0.0 MiB                   s_ids = detail_supply_id[(i - 1) * avg_len: i * len(detail_supply_id)]
    64   2605.4 MiB     12.6 MiB               detail = data_detail.loc[data_detail['supply_id'].isin(s_ids)]
    65   2440.8 MiB     39.2 MiB               tmp_detail = detail.to_json(orient='records')
    66   2602.2 MiB    354.4 MiB               detail_list = pd.json.loads(tmp_detail)
    67   2602.2 MiB      0.0 MiB               result_detail_list.append(detail_list)
    68   2602.2 MiB      0.0 MiB               sids_list.append(s_ids)

内存占用分析（100万数据，主进程切分数据，直接使用dataframe）：

Line #    Mem usage    Increment   Line Contents
================================================
    19     77.6 MiB     77.6 MiB   @profile
    20                             def get_data1_list():
    33     77.6 MiB      0.0 MiB       try:
    34     77.6 MiB      0.0 MiB           result_supply_list = list()
    35     77.6 MiB      0.0 MiB           result_detail_list = list()
    36     77.6 MiB      0.0 MiB           sids_list = list()
    37                             
    38     77.6 MiB      0.0 MiB           columns = ['product_id', 'city_id', 'supply_id', 'order_id', 'num', 'end_time', 'status']
    39    257.2 MiB    179.6 MiB           df = pd.read_table('data.txt', sep='\t', header=None, names=columns, dtype='str')
    40                             
    41    310.6 MiB     53.5 MiB           data_supply = df[['product_id', 'city_id', 'supply_id']]
    42    272.9 MiB      0.0 MiB           data_supply = data_supply.drop_duplicates()
    43    272.9 MiB      0.0 MiB           data_supply['status'] = str(1)
    44                             
    45    364.4 MiB     91.6 MiB           data_detail = df[['supply_id', 'order_id', 'num', 'end_time', 'status']]
    46    375.9 MiB     11.5 MiB           data_detail = data_detail.drop_duplicates()
    47                             
    48    375.9 MiB      0.0 MiB           len_detail = data_supply.shape[0]
    49    375.9 MiB      0.0 MiB           avg_len = len_detail / conf.MULTI_CNT
    50                             
    51    473.8 MiB      0.0 MiB           for i in range(1, conf.MULTI_CNT + 1):
    52    469.2 MiB      0.0 MiB               if i != conf.MULTI_CNT:
    53    458.0 MiB      0.0 MiB                   s_ids = data_supply['supply_id'][(i-1) * avg_len: i * avg_len]
    54                                         else:
    55    469.2 MiB      0.0 MiB                   s_ids = data_supply['supply_id'][(i - 1) * avg_len:]
    56    473.8 MiB     14.7 MiB               detail = data_detail.loc[data_detail['supply_id'].isin(s_ids)]
    57    473.8 MiB      0.0 MiB               result_detail_list.append(detail)
    58    473.8 MiB      0.0 MiB               sids_list.append(s_ids)

优化：

弃用List形式，直接使用dataframe，不做转换

内存占用分析（500万数据，主进程切分数据，直接使用dataframe）：
内存占用从220万（9×6.2%=55.8%，60.8G）降为500万（9×1.5%=13.5%，14.3G），整体内存耗用量为之前的12%

在这里插入图片描述

3.5 优化点5：子进程分片读取数据

性能剖析：

从上面内存占用分析可知，主进程、子进程内存占用相同，原因是python子进程是对主进程内存的直接复制，在主进程中读取数据全集，子进程内存占用也为数据全集

考虑python多线程，虽然多线程可以共用内存，但只能使用1个CPU，不利于加速

尝试从python内存管理方面解决，被引用变量不会被内存回收，主动删除（del）已赋值的大dataframe，无明显内存占用下降

优化点5：

换个思路，主进程不读入整个数据全集，传入子进程切分数据位置，在子进程读入需要数据，所有子进程内存耗用量加和即为整个数据集大小

子进程内存耗用量从1.5%->0.3%，主进程几乎不耗费内存

加上优化4，整体内存消耗量优化为之前的2.2%（原内存耗用是优化后的46.5倍）

# 按分片读取数据集
df = pd.read_table(conf.DATA_PATH, sep='\t', header=None, names=columns, dtype='str', skiprows=detail_batch_size * process_num, nrows=detail_batch_size)

在这里插入图片描述

3.6 优化点6：SQL拼接使用更快的方式

性能剖析：

SQL拼接为遍历， 数据量大时拼接SQL速度异常慢

优化：

SQL拼接直接用dataframe处理，dataframe内部矩阵处理，CPU占用率从一直95%，降到偶尔5%

df_query = "(" \
           + df_detail["product_id"].map(str) \
           + "," + df_detail["city_id"].map(str) \
           + "','" + df_detail["supply_id"].map(str) \
           + "'," + df_detail["order_id"].map(str) \
           + "," + df_detail["num"].map(str) \
           + ",'" + df_detail["end_time"].map(str) \
           + "'," + df_detail["status"].map(str) \
           + ")"
query_values_str = df_query.str.cat(sep=",")
 
# sql执行语句
query_str = "INSERT INTO " + self.table1.get_table_name() + query_header_str + " VALUES " + query_values_str + " ON DUPLICATE KEY UPDATE " + query_update_str
self.eng.execute(query_str)

--------------------------文档信息--------------------------
版权声明：本文为博主原创文章，未经博主允许不得转载
署名(BY) ：dkjkls（dkj卡洛斯）
文章出处：http://blog.youkuaiyun.com/dkjkls

MySql批量插入性能优化(多值插入)【通用实体服务类TCommonService】
专注于计算机研发知识和资讯分享
02-15 1522
mybatis-plus内置的批量插入方法并不是真正的批量写入，而是通过executeBatch分批提交。 所以想提升批量插入的性能可以采用多值插入。多值插入的方式1: JDBC URL批量处理参数配置， 在mysql连接信息中添加rewriteBatchedStatements=true使得执行效率大幅提升。多值插入的方式2: 通过sql注入器注入InsertBatchSomeColumn方法实现了insert的多值插入，提升了批量插入的性能。通过sql注入器注入InsertBatchSomeColumn
Mysql批量插入更新性能优化
01-21
对于数据量较大的插入和更新，因io/cpu等性能瓶颈，会产生大量的时间消耗，目前主流的优化主要包括预编译、单条sql插入多条数据、事务插入等，下面详细介绍一下： 　　单条插入（Mybatis） 　　INSERT INTO SYS_CITY (CITY_CODE， CITY_NAME， PROVINCE_NAME， ALIAS， ABBRE_PY) VALUES 　　(${cityCode}， ${cityName}， ${provinceName}， ${alias}， ${abbrePy}) 　　单条预编译插入（Mybatis） 采用预编译可以节约mysql服务的解析时间，mytat
Mybatis批量更新数据及其优化
空空墓志铭的博客
08-29 5547
临时关联更新操作不能应用在Trascational事务中，创建临时表的操作在事务中不支持，需要做其他处理；：定时任务中，从其他平台同步数据，并更新当前平台数据库，表数据3W+，分批更新某个字段，耗时巨大，约30min，尝试性能优化。2.先删后增，取出原数据内存中更新后，先将全表数据删除，再insert插入；或者设置标志字段，先增后删，思路一致。：当前场景需要去更新某个字段，且数据量较大，几百条数据每批进行更新，应用foreach循环更新时，耗时巨大；：使用临时表关联全表更新，一次关联，一次更新；
大数据量联合主键的插入更新解决方案
ShiningRunner的博客
11-29 6935
数据量大了之后想对一张表的数据进行插入/更新就会非常慢！目前环境是一张主表数据量在一亿的样子，之前做插入更新处理是用的KETTLE抽取工具，然而这张表是四个字段的联合主键，这样判断起来效率就会非常低，现在跑任务直接会卡死不动。网上找了一些类似的解决方案
MySQL批量更新操作优化
最新发布
王仁一的博客
05-30 1315
根据以上理论分析与实际验证，我们找到了一种对批量更新场景的优化方式。
mysql批量update数据优化
qq_42976538的博客
01-09 519
mysql批量update数据优化 有一张表goods中有20W条数据，现在需要把某个字段的值做md5加密后更新。 一条一条循环更新性能差不说，还容易造成数据库阻塞。 <?php set_time_limit(0); ini_set('memory_limit', '1G'); $mysqli = new mysqli('127.0.0.1', 'root', 'root', 'wx',...
MYSQL开发性能研究之批量插入数据的优化方法
12-16
本文主要探讨了在MySQL中如何通过批量插入优化性能，对比了几种不同的批量插入方法，并进行了实验验证。 首先，传统的单条插入方式在面对大量数据时效率低下，因为每条插入语句都需要经过解析、优化和执行等多个...
MySQL实现批量插入以优化性能的教程
12-15
以下是一些关于如何利用MySQL的InnoDB存储引擎优化批量插入性能的方法： 1. **单条SQL语句插入多条数据**： 将多条独立的INSERT语句合并成一个包含多个VALUES子句的单一SQL语句，可以显著提高插入速度。这是因为...
mysql批量update优化_让MySQL飞起来！别小看这21种写SQL的好习惯
weixin_30069591的博客
01-23 1334
前言每一个好习惯都是一笔财富，本文分SQL后悔药、SQL性能优化、SQL规范优雅三个方向，分享写SQL的21个好习惯，谢谢阅读，加油哈~一、SQL后悔药 1、操作delete或者update语句，加个limit在执行删除或者更新语句，尽量加上limit，以下面的这条 SQL 为例吧：delete from euser where age > 30 limit 200;因为加了li...
mysql批量update优化,mysql 批量操作优化（转一）
weixin_36369259的博客
03-18 320
mysql批量插入数据优化hi.baidu.com/yiewgckawnlpuwe/item/80adbb9d5c263eb282d29571mysql批量插入数据优化100W条数据：比上次的代码性能提高了3倍。j=2,即每2千条提交一次时间为45687ms，j=10,即每1万条提交一次时间为40890msj=20,即每2万条提交一次时间为72000msj=100，每10万条提交一次时间为：643...
MySQL批量SQL插入性能优化
12-15
对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此，优化数据库插入性能是很有意义的。 　　经过对MySQL innodb的一些性能测试，发现一些可以提高insert效率的方法，供大家参考参考。 　　1. 一条SQL语句插入多条数据。 　　常用的插入语句如： 　　INSERT INTO `insert_table` (`datetime`， `uid`， `content`， `type`) 　　VALUES ('0'， 'userid_0'， 'content_0'， 0
mysql批量更新
frank1q的专栏
12-13 2618
转载一篇大批量更新mysql数据的办法，为了便于阅读根据原文做了缩减。 mysql 批量更新如果一条条去更新效率是相当的慢, 循环一条一条的更新记录,一条记录update一次，这样性能很差，也很容易造成阻塞。 mysql 批量更新共有以下四种办法 1、.replace into 批量更新 replace into test_tbl (id,dr) values (1,'2'
oracle 批量update的性能优化
stevenberg的专栏
03-16 2803
在处理7亿行记录的Oracle表上运行update SQL语句时，需要考虑使用适当的索引、批量更新、并行更新、定期收集统计信息以及使用临时表等优化策略来提高性能。合理选择和组合这些优化策略可以显著减少update操作的执行时间，并提升整个系统的性能。通过持续的性能优化和监测，可以确保大型表的update操作能够高效地执行。这种方式可以减少对原始表的锁定时间，并且可以更高效地执行大量的更新操作。
批量数据插入高效 转发
weixin_30378623的博客
12-26 116
publicstaticvoidinsert(){ //开时时间 Longbegin=newDate().getTime(); //sql前缀 Stringprefix="INSERTINTOtb_big_data(count,create_time,random)VA...
记一次MySQL批量更新SQL优化，性能提升几十个数量级
wumi0002的博客
03-01 8166
一、问题背景 原始需求是这样的，100个计数器硬件设备每隔5秒给服务器上报计数信息，服务端收到消息后先将上报的元数据存储起来，再每隔10s批量对之前上报的所有的元数据按时间段进行批量累加统计更新（题外话，为什么要每隔10s处理一次，而不是上报一次就累加一次？是为了不频繁操作数据库） 二、实现方案 在看实现方案前，我们先看一下表的设计： CREATE TABLE `aps_produce_statistics` ( `produce_id` bigint(20) NOT NULL COMMENT.
mysql主从复制延时的问题定位解决
qq_27413937的博客
12-02 822
MySQL主从复制的延时一直是业界困扰已久的问题。延时的出现会降低主从读写分离的价值，不利于数据实时性较高的业务使用MySQL。 延时问题的重要性 如果主从复制之间出现延时，就会影响主从数据的一致性。 复制延时问题，，在只读从库的场景下，若从库产生复制延时，也可能会对业务造成一定影响，比如在业务上表现为读写不一致——新增/修改数据查不到等现象。 由此可见，主从复制的延时问题在数据库运营中需要特别关注。一般来说，DBA在库上执行’SHOWSLAVE STATUS’，并且观察 ‘S...
在MySQL对于批量更新操作的一种优化方式
GreatDB的博客
06-08 1238
引言 批量更新数据，不同于这种 update a=a+1 where pk > 500，而是需要对每一行进行单独更新 update a=1 where pk=1;update a=12 where pk=7;… 这样连续多行update语句的场景，是少见的。 可以说是偶然也是一种必然，在GreatDB 5.0的开发过程中，我们需要对多语句批量update的场景进行优化。 两种多行更新操作的耗时对比 在我们对表做多行更新的时候通常会遇到以下两种情况 1.单语句批量更新（update a=a+1 wher
mysql批量update优化_MySQL 查询 批量插入 批量更新 优化的详情介绍
weixin_26765363的博客
02-03 419
最近在研究mysql的时候，遇到了mysql批量插入、批量更新效率不高的问题，之前一直在用sqlserver，mysql本身效率还是不错的，这里把提升效率方法记录下，就不对比时间了，实际测试结果效率提升很多。创建表结构1 DROP TABLE IF EXISTS `b_student`;2 CREATE TABLE `b_student` (3 `id` int(11) NOT NULL AU...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值