Best Way: Retrieve, Encrypt, and Update Millions of Records in MySQL Using Java 8

Question

<p>We have a requirement to read approximately 10 million records from a MySQL database, encrypt these records, and update the encrypted values ​​back into the fields. How to achieve this requirement in the most efficient way. </p>

This option is therefore ruled out. </p> <p><strong>2</strong>: The Executor function in Java has been used before in one of our applications to implement batch insertion. But the number of records does not exceed 50,000. We are asked to use a similar approach. Therefore, the use of Spring Batch is excluded. </p> <p>I'm looking for possible features to consider in Java 8/MySQL databases. </p> <p>Java 8: Use Executor for multi-threading, CompletableFuture for asynchronous processing, and Streaming. </p> <p>MySQL: Create index...</p>

Answer 1

• Any operation on 10M rows of data will take a long time and may result in timeouts.
• If you are storing into MySQL, you can only use VARBINARY or BLOB.
• Ensure that the declared data type is at least slightly larger than the source data. (SELECT MAX(LENGTH(col)) FROM tbl) can give the maximum length.
• Consider creating a new table and when copying the data convert it to other columns in the new table. Then test it.
• Consider processing 1000 rows of data per iteration, using PRIMARY KEY to keep track of "where you last left off", if feasible. (Do not use OFFSET). More information about chunking: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks
• Yes, use some kind of bulk insert. But limit the total size of the batch to around 1MB. (16MB is hard to pass). This may bring your data size below the 1K mentioned above.
• The time required to operate on the entire table will not make a big difference compared to a one-time operation, processing 1K rows at a time.
• When encrypting data, it may also be worth trying to compress it. This may result in a 3x smaller disk footprint.