生产数据库批量更新数据的一点实践

最近工作内容有一部分是在与SQL打交道,还是遇到了点坑,这里mark下。

背景

当前系统,面对数十万的数据表进行复杂update操作会出现问题,最终采用一条条的更新方式解决

程序源码

办法见源码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
/**
* @description
*
* 1. SQL查出目标数据主键,导出CSV
* 2. 创建单条update SQL模版
* 3. 执行该JS文件,会生成目标SQL
* 4. 目标机器执行SQL
*
* 注意:修改CHUNK_SIZE来控制逐条更新的记录量
*/
const csv = require('csv-parser');
const fs = require('fs');
const ids = [];
const CHUNK_SIZE = 10;
// 输入数据文件
const INPUT_FILE = 'quote_ids.csv';
// 目标生成SQL文件
const OUTPUT_FILE = 'repair_data.sql';
const createSQLTemplate = (idArr) => `update \`a-prod\`.quote a left join \`b-prod\`.geographic_unit b
on a.sales_org = b.sales_org and a.sales_office = b.sales_office and a.business_unit = b.business_unit and
a.country = b.country_iso_code
set a.geographic_unit_id=b.id
where a.id in ('${idArr.join('\',\'')}');\n\n\n`;
fs.createReadStream(INPUT_FILE)
.pipe(csv())
.on('data', (row) => {
ids.push(row.id);
})
.on('end', () => {
const chunkIds = ids.reduce((res, item, index) => {
let group = Math.floor(index / CHUNK_SIZE);
if (res[group] === undefined) {
res[group] = [];
}
res[group].push(item);
return res;
}, []);

fs.writeFile(OUTPUT_FILE, chunkIds.map(item => createSQLTemplate(item)).join(''), () => {
console.log('write success');
});
});

按照如上的方式即可避免一次性更新造成MySQL程序

思考

  • 当前面临的单表数据也无非20万,数据并不多,但是即使查询有时候也很慢,原因是有些字段比较大,比如是text,而查假如询需要展示这个字段就很慢,经验就是按需所取,这也就是为什么不提倡使用通配符*,当然查询如果再join几张表也会降低查询效率
  • 对于update操作,强烈要求增加where条件,否则影响范围太大,除非你很确定必须那么做
  • 生产如果出现大规模要更新数据,一方面其实我认为暴露了程序BUG,毕竟这种需求场景都不该存在,另一方面如果更新很麻烦,也暴露了表是否design的不合理呢