java - 如何用最少的时间在mysql中转换大约1600万行？

Question

我在一个只有两列的表中有大约 160 万行，一个是 bigint 唯一索引，另一个是 longblob，在 base64 中有照片。我需要尽快解码这些 base64 照片。我尝试使用几个小时后中断的 java 程序，速度也不好。这是程序

Connection conn = null;
        Statement stmt = null;
        try {
            conn = DatabaseConnection.getConnection();
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            ResultSet uprs = stmt.executeQuery("SELECT uniqueid,photo FROM newphotodata");
            String query="insert into photo_data values (?,?)";
            PreparedStatement pstmt =  conn.prepareStatement(query);
            while (uprs.next()) {
                byte[] processed = Base64Utils.base64Decode(uprs.getString(2));
                pstmt.setString(1, uprs.getString(1));
                pstmt.setBytes(2, processed);
                pstmt.executeUpdate();
                uprs.deleteRow();
            }
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
                if(conn!=null)
                    conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

我采用的第二种方法是使用数据库触发器，它使用我创建的 base64_decode 存储过程。这是触发器

DELIMITER $$

DROP TRIGGER `delete_photo`$$

CREATE TRIGGER `delete_photo` BEFORE DELETE ON `newphotodata`
FOR EACH ROW
BEGIN
    INSERT INTO `photo_data` SELECT OLD.`uniqueid`, BASE64_DECODE(OLD.`photo`);
END$$

DELIMITER ;

处理速度还是太慢了。是否有任何其他方法可用于执行以下任务。我在 Redhat Linux 中运行 Mysql 5.0 版，内存为 96GB，intel Xeon x5672。

Answer 1

最好的方法是创建另一个表并使用预先构建的 mysql 函数插入解码后的照片以在 base64 上解码编码。

插入比更新快。

INSERT INTO photo_data 
SELECT OLD.uniqueid, FROM_BASE64(OLD.`photo`);

但是如果您不每隔几行提交一次，这个查询就会变得很长。所以最好的方法是创建一个过程，每 n 行进行一次提交。

不要为了获得最佳性能而退出 mysql。在 mysql 外部进行的每个转换都需要额外的努力。

已编辑:按uniqueId对原始数据进行排序。如果出现问题，您可以从最后插入的 ID 重新开始。您不需要删除原始数据。它可以提高你的表现。