求助帖:
有一个 MySQL 表,总数据量 2-3 亿,每天新增 60w 数据。表有个字段用于记录图片文件信息( file_id ),一年大概有 100w 条记录的 file_id 有值。现在这个表需要清洗数据,需要将近一年的图片文件数据( 100w 条)进行更新,具体是将文件下载下来,重新上传到文件平台生成 file_id ,然后替换旧的 file_id 。 文件主要是一些截图、照片,文件大小大约 2M 左右。
想问下大佬们怎么去处理比较好?
1
tysonnn OP 表做了分区,没有分库分表
|
2
qping 2023-03-12 19:08:29 +08:00
看了下应该有三步吧
1 、捞出近一年图片数据(约 100w 条) 2 、依次下载 3 、重新上传文件平台生成新的 file_id 4 、替换数据库中旧的 file_id 瓶颈应该是在 2 和 3 吧,IO 比较多 同时处理的线程数量估计也不能太多,可以做个试验试试最佳线程数量 |
3
siknet 2023-03-12 19:12:27 +08:00 via Android
老哥,我正在学这个,大体积的 xlsx 或者 csv 怎么快速导入 mysql ? navicat 还是 MySQL 命令行
|
4
qping 2023-03-12 19:15:56 +08:00
漏了:
预估数据量 100 万* 2M = 2T 写个脚本处理,跑个 1 万条看下时间,看能不能接受? 这么多小文件的读写比较考验磁盘,要是有权限的话,先在文件服务器上运行脚本,拷贝文件打包,然后下载,可以节约下网络传输的损耗 |
5
ksc010 2023-03-12 19:39:10 +08:00
用异步队列的方式
1. 查询出 100W 数据记录 放入队列 A 2. 开启 worker:消耗队列 A 下载文件,上传文件,将新 fileid 和老的数据主键,放入队列 B 这里的 worker 可以根据 负载情况,确定开启多少个 3. 在开启一个 worker2 消耗 队列 B 将新的 fileid 存入数据库 |
6
xoxo419 2023-03-12 20:58:59 +08:00
条件和效果要求是怎么样呢,多长时间内需要处理完 是否是周期性的?
语言有要求吗 go or java 以及可使用分配的服务器 |
7
noparking188 2023-03-12 21:37:26 +08:00 1
我以前就做数据清洗掉,经常有类似需求。
我写了个工具辅助做数据清洗,楼主有需要可以参考下,https://github.com/zhiweio/porter 描述:Porter 是一个数据清洗辅助工具,能够将 MySQL 、MongoDB 和文本文件( CSV/TSV/JSON )等数据源数据全量扫描推入到 Redis 队列,支持断点续传、自定义等待延迟和 Batch Size 。 流程:全量遍历数据不断推进 Redis 队列,然后另外写脚本并发取数后清洗再更新,写业务逻辑就行,不需要考虑多线程编程,脚本多起几个任务就行,更推荐的是用 Lambda Function 。 以前做过的方案,仅供参考 |
8
tysonnn OP @xoxo419 对性能和时间没要求,能处理完就行,一天或者一个月都可以。Java 语言。16 个 docker 实例,4 核 16g
|
9
akira 2023-03-12 22:45:35 +08:00
没有什么好办法 就是按照你说的流程重新跑一遍。 就算有工具 估计也不一定能直接用
|
10
zddwj 2023-03-13 02:33:43 +08:00 via Android
这个好像没什么难点吧,就写个脚本扫表,然后下载上传,程序测试好了再运行,然后控制好服务器负载,条件允许的话可以考虑省掉下载的步骤,直接在服务器端或者文件平台端运行脚本,压不压缩无所谓了,图片文件一般也压不动
|
11
zddwj 2023-03-13 03:04:01 +08:00 via Android
Java Programming Requested
admin@**.com 你会写 java 程序吗 是的,我可以编写 Java 程序。Java 是一种广泛使用的编程语言,它具有广泛的应用领域,包括 Web 应用程序、移动应用程序、游戏、企业应用程序等。作为一个人工智能语 admin@**.com 写一个 java 程序,从 mysql 表 posts 中读取 file_id 字段 好的,以下是一个 Java 程序示例,使用 JDBC 连接到 MySQL 数据库,查询 posts 表中的 file_id 字段: java Copy code import java.sql.*; public class ReadFileIdFromMysql { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database_name"; String username = "your_username"; String password = "your_password"; try { // Connect to MySQL database Connection conn = DriverManager.getConnection(url, username, password); System.out.println("Connected to MySQL database!"); // Create a SQL statement String sql = "SELECT file_id FROM posts"; Statement stmt = conn.createStatement(); // Execute the SQL statement and get the results ResultSet rs = stmt.executeQuery(sql); // Loop through the results and print out the file IDs while (rs.next()) { int fileId = rs.getInt("file_id"); System.out.println("File ID: " + fileId); } // Clean up the resources rs.close(); stmt.close(); conn.close(); } catch (SQLException e) { System.out.println("Error connecting to MySQL database: " + e.getMessage()); } } } 在该示例中,我们首先定义了连接 MySQL 数据库所需的 URL 、用户名和密码。然后,我们使用 DriverManager 类建立了与数据库的连接,并创建了一个 Statement 对象来执行查询。执行查询后,我们使用 ResultSet 对象遍历结果并打印出每个 file_id 的值。最后,我们关闭了资源并处理了任何可能的异常。 请注意,您需要将 url 变量中的 your_database_name 替换为您实际使用的数据库名称,将 username 和 password 变量中的值替换为您自己的 MySQL 用户名和密码。另外,您还需要在代码中引入 JDBC 驱动程序,这可以通过在您的项目中包含相应的 jar 文件来完成。 admin@**.com 使用 getfileurl 函数 file_id 为参数获取图片链接,然后传输图片到文件平台 好的,以下是一个 Java 程序示例,使用 getfileurl()函数从 MySQL 表中获取图片链接,然后使用 Java 的 URL 和 URLConnection 类将图片上传到文件平台: java Copy code import java.io.*; import java.net.*; import java.sql.*; public class UploadImageToPlatform { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/your_database_name"; String username = "your_username"; String password = "your_password"; String platformUrl = "http://your_platform_url/upload"; String platformApiKey = "your_platform_api_key"; try { // Connect to MySQL database Connection conn = DriverManager.getConnection(url, username, password); System.out.println("Connected to MySQL database!"); // Create a SQL statement to get file URL int fileId = 1; // Replace with the actual file ID String sql = "SELECT file_url FROM posts WHERE file_id = " + fileId; Statement stmt = conn.createStatement(); // Execute the SQL statement and get the results ResultSet rs = stmt.executeQuery(sql); rs.next(); String fileUrl = rs.getString("file_url"); // Upload image to file platform URL platformApi = new URL(platformUrl); HttpURLConnection conn2 = (HttpURLConnection) platformApi.openConnection(); conn2.setRequestMethod("POST"); conn2.setRequestProperty("Api-Key", platformApiKey); conn2.setDoOutput(true); OutputStream os = conn2.getOutputStream(); os.write(fileUrl.getBytes()); os.flush(); if (conn2.getResponseCode() != HttpURLConnection.HTTP_OK) { throw new RuntimeException("Failed : HTTP error code : " + conn2.getResponseCode()); } BufferedReader br = new BufferedReader(new InputStreamReader((conn2.getInputStream()))); String output; System.out.println("Output from Server .... \n"); while ((output = br.readLine()) != null) { System.out.println(output); } // Clean up the resources rs.close(); stmt.close(); conn.close(); } catch (SQLException e) { System.out.println("Error connecting to MySQL database: " + e.getMessage()); } catch (MalformedURLException e) { System.out.println("Invalid platform URL: " + e.getMessage()); } catch (IOException e) { System.out.println("Error uploading image to platform: " + e.getMessage()); } } } 在该示例中,我们首先定义了连接 MySQL 数据库所需的 URL 、用户名和密码,以及文件平台的 URL 和 API 密钥。然后,我们使用 getfileurl()函数从 MySQL 表中获取图片链接,将其写入到 OutputStream 对象中,并使用 HttpURLConnection 类将其上传到文件平台。如果上传成功,则我们打印出从文件平台返回的响应。最后,我们关闭了资源并处理了任何可能的异常。 请注意,您需要将 fileId 变量中的值替换为您实际使用的文件 ID 。此外,您还需要将 platformUrl 和 platformApiKey 变量中的值替换为您自己的文件平台的 URL 和 API 密钥。 |
13
DinnyXu 2023-03-13 09:44:16 +08:00
我感觉大多数人都没理解到 OP 的意思,查个数据,上传并更新 OP 不会写这些代码?
|
14
DinnyXu 2023-03-13 09:48:34 +08:00
@DinnyXu 主要的瓶颈是这个单表有 2-3 亿的数据,查询更新个 100w 数据不是轻轻松松吗,而是如何在 2-3 亿的表中去更这 100w 条数据。
这里 op 还没有把表结构发出来,我们不知道这个表是否有对应的索引,如果没有索引,在单表 2-3 亿的数据量下去查询,效率极低,每次只能分页查询一定的数据量。 个人建议:op 第一步先针对改表的索引进行查询,使查询语句尽量走在索引上,然后分页查询一定的数据,并开启轮询进行更新,上传和更新不一定是一个步骤,可以分为多个步骤。 通过分页查询出这些数据,集体上传,并获取 url ,上传完毕后,再分页进行更新。 |
15
QuinceyWu 2023-03-13 10:02:16 +08:00
|