Java程序使用预处理语句的性能提升

GreatSQL提供了对服务器端预处理语句（Prepared Statements）的支持。预处理语句可以利用了高效的客户机/服务器二进制协议。使用带有参数值占位符的预处理语句有以下好处:

• 每次执行时解析语句的开销更少。通常，数据库应用程序处理大量几乎相同的语句，只对语句中的文字值或变量值进行更改，如 SELECT 和 UPDATE 中的 WHERE,UPDATE 语句中的 SET 和 INSERT 语句中的VALUES。
• 防范SQL注入攻击。参数值可以包含未转义的SQL引号和分隔符。

本文编写Java程序，执行常规SQL语句和预处理语句，对比性能差异，量化预处理语句的性能提升。

1. 程序设计

通过Java程序进行DML操作，每次DML的数量是10万条，每50条一个提交批次。对比执行预处理语句和普通SQL语句，通过执行时间长短，判断执行的性能。

• 函数 testInsertPerformance 对比 INSERT 性能；
• 函数 testUpdatePerformance 对比 UPDATE 性能；
• 函数 testSelectPerformance 对比 SELECT 性能；
• 函数 testDeletePerformance对比 DELETE 性能；

1.1 测试表

 greatsql> CREATE DATABASE IF NOT EXISTS testdb1; greatsql> USE testdb1; greatsql> CREATE TABLE IF NOT EXISTS test_table ( id INT AUTO_INCREMENT PRIMARY KEY, col1 INT, col2 VARCHAR(100), col3 DATETIME );

1.2 Java程序代码

Java程序比较容易使用预处理SQL语句，主要有两点：

• 数据库连接字符串中增加useServerPrepStmts=true；
• SQL语句使用conn.prepareStatement进行预处理；

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.sql.Timestamp; import java.util.Date; public class SqlPerformanceTest { private static final String URL = "jdbc:mysql://192.168.134.208:3307/testdb1?useServerPrepStmts=true"; private static final String USER = "testuser"; private static final String PASSWORD = "testpass"; private static final int NUM_ITERATIONS = 100000; private static final int BATCH_SIZE = 50; public static void main(String[] args) { try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) { // 清空测试表 clearTable(conn); // 测试 INSERT 操作 testInsertPerformance(conn); // 测试 UPDATE 操作 testUpdatePerformance(conn); // 测试 SELECT 操作 testSelectPerformance(conn); // 测试 DELETE 操作 testDeletePerformance(conn); } catch (Exception e) { e.printStackTrace(); } } private static void clearTable(Connection conn) throws Exception { try (Statement stmt = conn.createStatement()) { stmt.execute("TRUNCATE TABLE test_table"); } } private static void testInsertPerformance(Connection conn) throws Exception { long startTime, endTime; // 清空测试表 clearTable(conn); // 普通 SQL 语句 startTime = System.nanoTime(); conn.setAutoCommit(false); // 关闭自动提交 try (Statement stmt = conn.createStatement()) { for (int i = 1; i <= NUM_ITERATIONS; i++) { stmt.executeUpdate("INSERT INTO test_table (id, col1, col2, col3) VALUES ("+ i + "," + i + ", 'value" + i + "', '" + new Timestamp(new Date().getTime()) + "')"); if (i % BATCH_SIZE == 0) { conn.commit(); // 每50条记录提交一次事务 } } conn.commit(); // 提交剩余的记录 } finally { conn.setAutoCommit(true); // 恢复自动提交 } endTime = System.nanoTime(); System.out.println("INSERT - Statement: " + (endTime - startTime) / 1000000.0 + " ms"); // 清空测试表 clearTable(conn); // 预处理 SQL 语句 startTime = System.nanoTime(); conn.setAutoCommit(false); // 关闭自动提交 try (PreparedStatement pstmt = conn.prepareStatement("INSERT INTO test_table (id,col1, col2, col3) VALUES (?, ?, ?, ?)")) { for (int i = 1; i <= NUM_ITERATIONS; i++) { pstmt.setInt(1, i); pstmt.setInt(2, i); pstmt.setString(3, "value" + i); pstmt.setTimestamp(4, new Timestamp(new Date().getTime())); pstmt.executeUpdate(); if (i % BATCH_SIZE == 0) { conn.commit(); // 每50条记录提交一次事务 } } conn.commit(); // 提交剩余的记录 } finally { conn.setAutoCommit(true); // 恢复自动提交 } endTime = System.nanoTime(); System.out.println("INSERT - PreparedStatement: " + (endTime - startTime) / 1000000.0 + " ms"); } private static void testUpdatePerformance(Connection conn) throws Exception { long startTime, endTime; // 普通 SQL 语句 startTime = System.nanoTime(); conn.setAutoCommit(false); // 关闭自动提交 for (int i = 1; i <= NUM_ITERATIONS; i++) { try (Statement stmt = conn.createStatement()) { stmt.executeUpdate("UPDATE test_table SET col1 = " + i + ", col2 = 'value" + i + "', col3 = '" + new Timestamp(new Date().getTime()) + "' WHERE id = " + i); if (i % BATCH_SIZE == 0) { conn.commit(); // 每50条记录提交一次事务 } } } conn.commit(); // 提交剩余的记录 endTime = System.nanoTime(); System.out.println("UPDATE - Statement: " + (endTime - startTime) / 1000000.0 + " ms"); // 预处理 SQL 语句 startTime = System.nanoTime(); conn.setAutoCommit(false); // 关闭自动提交 try (PreparedStatement pstmt = conn.prepareStatement("UPDATE test_table SET col1 = ?, col2 = ?, col3 = ? WHERE id = ?")) { for (int i = 1; i <= NUM_ITERATIONS; i++) { pstmt.setInt(1, i); pstmt.setString(2, "value" + i); pstmt.setTimestamp(3, new Timestamp(new Date().getTime())); pstmt.setInt(4, i); pstmt.executeUpdate(); if (i % BATCH_SIZE == 0) { conn.commit(); // 每50条记录提交一次事务 } } conn.commit(); // 提交剩余的记录 } finally { conn.setAutoCommit(true); // 恢复自动提交 } endTime = System.nanoTime(); System.out.println("UPDATE - PreparedStatement: " + (endTime - startTime) / 1000000.0 + " ms"); } private static void testDeletePerformance(Connection conn) throws Exception { long startTime, endTime; // 普通 SQL 语句 startTime = System.nanoTime(); conn.setAutoCommit(false); // 关闭自动提交 for (int i = 1; i <= NUM_ITERATIONS/2; i++) { try (Statement stmt = conn.createStatement()) { stmt.executeUpdate("DELETE FROM test_table WHERE id = " + i); if (i % BATCH_SIZE == 0) { conn.commit(); // 每50条记录提交一次事务 } } } conn.commit(); // 提交剩余的记录 endTime = System.nanoTime(); System.out.println("DELETE - Statement: " + (endTime - startTime) / 1000000.0 + " ms"); // 预处理 SQL 语句 startTime = System.nanoTime(); conn.setAutoCommit(false); // 关闭自动提交 try (PreparedStatement pstmt = conn.prepareStatement("DELETE FROM test_table WHERE id = ?")){ for (int i = NUM_ITERATIONS/2+1; i <= NUM_ITERATIONS; i++) { pstmt.setInt(1, i); pstmt.executeUpdate(); if (i % BATCH_SIZE == 0) { conn.commit(); // 每50条记录提交一次事务 } } conn.commit(); // 提交剩余的记录 } finally { conn.setAutoCommit(true); // 恢复自动提交 } endTime = System.nanoTime(); System.out.println("DELETE - PreparedStatement: " + (endTime - startTime) / 1000000.0 + " ms"); } private static void testSelectPerformance(Connection conn) throws Exception { long startTime, endTime; // 普通 SQL 语句 startTime = System.nanoTime(); for (int i = 1; i <= NUM_ITERATIONS; i++) { try (Statement stmt = conn.createStatement()) { ResultSet rs = stmt.executeQuery("SELECT * FROM test_table WHERE id = " + i); while (rs.next()) { // 处理结果集 } } } endTime = System.nanoTime(); System.out.println("SELECT - Statement: " + (endTime - startTime) / 1000000.0 + " ms"); // 预处理 SQL 语句 startTime = System.nanoTime(); try (PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM test_table WHERE id = ?")) { for (int i = 1; i <= NUM_ITERATIONS; i++) { pstmt.setInt(1, i); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { // 处理结果集 } } } endTime = System.nanoTime(); System.out.println("SELECT - PreparedStatement: " + (endTime - startTime) / 1000000.0 + " ms"); } }

2. 程序编译与运行

编译Java程序

 javac -cp .:mysql-connector-j-8.0.32.jar SqlPerformanceTest.java

运行Java程序

 java -cp .:mysql-connector-j-8.0.32.jar SqlPerformanceTest

3. 运行结果

 $ java -cp .:mysql-connector-j-8.0.32.jar SqlPerformanceTest INSERT - Statement: 27089.435867 ms INSERT - PreparedStatement: 24166.424328 ms UPDATE - Statement: 32034.818767 ms UPDATE - PreparedStatement: 29688.13851 ms SELECT - Statement: 23330.719737 ms SELECT - PreparedStatement: 20430.097589 ms DELETE - Statement: 14933.753122 ms DELETE - PreparedStatement: 13325.930952 ms

多运行Java程序，结果接近，按照其中一次进行数据统计：

操作类型	常规SQL语句执行时间（ms）	预处理语句执行时间（ms）	性能提升（%）
INSERT	27089	24166	10.79
UPDATE	32034	29688	7.32
SELECT	23330	20430	12.43
DELETE	14933	13325	10.77
合计	97386	87609	10.04

4. 总结

由于预处理语句比常规SQL语句，节省了SQL语句的解析时间，对于重复执行的SQL语句，使用预处理语句，可以明显地提高执行效率，性能提升约10%。