GreatSQL 提供了对服务器端预处理语句（Prepared Statements）的支持。预处理语句可以利用了高效的客户机/服务器二进制协议。使用带有参数值占位符的预处理语句有以下好处:
每次执行时解析语句的开销更少。通常，数据库应用程序处理大量几乎相同的语句，只对语句中的文字值或变量值进行更改，如 SELECT 和 UPDATE 中的 WHERE,UPDATE 语句中的 SET 和 INSERT 语句中的 VALUES。防范 SQL 注入攻击。参数值可以包含未转义的 SQL 引号和分隔符。本文编写 Java 程序，执行常规 SQL 语句和预处理语句，对比性能差异，量化预处理语句的性能提升。
程序设计通过 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 SqlPerformanceTest3. 运行结果 $ java -cp .:mysql-connector-j-8.0.32.jar SqlPerformanceTestINSERT - Statement: 27089.435867 msINSERT - PreparedStatement: 24166.424328 msUPDATE - Statement: 32034.818767 msUPDATE - PreparedStatement: 29688.13851 msSELECT - Statement: 23330.719737 msSELECT - PreparedStatement: 20430.097589 msDELETE - Statement: 14933.753122 msDELETE - PreparedStatement: 13325.930952 ms 多运行 Java 程序，结果接近，按照其中一次进行数据统计：
操作类型 常规 SQL 语句执行时间（ms） 预处理语句执行时间（ms） 性能提升（%）INSERT 27089 24166 10.79UPDATE 32034 29688 7.32SELECT 23330 20430 12.43DELETE 14933 13325 10.77 合计 97386 87609 10.044. 总结由于预处理语句比常规 SQL 语句，节省了 SQL 语句的解析时间，对于重复执行的 SQL 语句，使用预处理语句，可以明显地提高执行效率，性能提升约 10%。
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL 是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为 MySQL 或 Percona Server 的可选替换，用于线上生产环境，且完全免费并兼容 MySQL 或 Percona Server。
行业拓展
分享一个面向研发人群使用的前后端分离的低代码软件——JNPF。
基于 Java Boot/.Net Core 双引擎，它适配国产化，支持主流数据库和操作系统，提供五十几种高频预制组件，内置了常用的后台管理系统使用场景和实用模版，通过简单的拖拉拽操作，开发者能够高效完成软件开发，提高开发效率，减少代码编写工作。
JNPF 基于 SpringBoot+Vue.js，提供了一个适合所有水平用户的低代码学习平台，无论是有经验的开发者还是编程新手，都可以在这里找到适合自己的学习路径。
此外，JNPF 支持全源码交付，完全支持根据公司、项目需求、业务需求进行二次改造开发或内网部署，具备多角色门户、登录认证、组织管理、角色授权、表单设计、流程设计、页面配置、报表设计、门户配置、代码生成工具等开箱即用的在线服务。
评论