如何进行高效的Prepared SQL语句性能测试？

本文共计1285个文字，预计阅读时间需要6分钟。

Prepared Statement 简介Prepared Statement 即预编译SQL语句。它是一种SQL语句的预编译和优化方式。

什么是SQL预编译？SQL预编译是指将SQL语句编译成优化后的执行计划，这个计划在执行前就已经确定，可以重复使用。

普通SQL语句执行逻辑普通SQL语句执行时，需要经过以下步骤：

1.服务器层分析器对SQL语句进行词法分析和语法分析。

2.生成执行计划。

3.执行SQL语句。

4.返回结果。

执行计划分析

执行计划包括以下部分：- 服务器层分析器（图中圆圈部分）负责对SQL语句进行词法分析和语法分析。- 生成执行计划，包括查询优化器、执行器等。- 执行SQL语句。- 返回结果。

一：Prepere Statement 简介

 prepare statement 即 SQL 预处理。什么是 SQL 预处理？ 普通 SQL 语句执行的逻辑 需要经过 server 层 的 分析器 (图中圈住的部分) 对 sql 语句进行词法语法解析、sql 编译，

这需要一些性能开销，尤其在一些高并发的场景中可能是性能提升的一个突破点。Prepere Statement 就是干这个事的，他可以对 SQL 进行预编译。 查看 MySQL 官方文档，

有这么一条说明：

Prepere Statement 适用的场景是，SQL 语句 未发生改变，只是 query 的值发生了改变的情况。通俗的讲就是，比如 有这么一条 SQL insert into t1(name, age) values ( "siri", 18 )

当你要批量执行时，Prepare 方法 可以用 占位符的方式填充 SQL 值变化的部分，即 insert into t1(name, age) values ( ？, ？ ) ，可以由 Prepare 提交给 分析器预编译。下次再

执行的时候，直接 EXECUTE Statement 占位符填充，省去了再次 语法解析、编译的过程，达到一次编译，多次运行的效果。

那么，Prepere 在高并场景下，性能能提升多少呢，官方并未给出答案，下面准备实测一下：

二：Prepere Statement 性能测试

测试环境：（测试 批量 插入性能 ）

• 测试实例：MySQl 5,7
• 配置： 4 核 8G

网上关于 prepare 性能测试的帖子很少， 于是需要自己写测试工具。（ gitlab.xxxxx.com/master/mysql_prepare_test ）

工具关键部分如下：

// Prepare SQL syntax func PrepareExec(n int, wg *sync.WaitGroup) { // NameExec sqlStr := "insert into user(name,age) values(?,?)" // 占位符 stmt, _ := db.Prepare(sqlStr) // 开启 Prepare ,预编译 SQL 语句 defer stmt.Close() defer wg.Done() for i := 0; i <= n; i++ { name := RandString(10) _, err := stmt.Exec(name, 20) if err != nil { fmt.Println(err) } //rows, _ := ret.RowsAffected() //record := fmt.Sprintf("RowsAffected: %d", rows) //logger.Write(record) } } // NonPrepare SQL syntax func NonPrepareExec(n int, wg *sync.WaitGroup) { // NameExec defer wg.Done() for i := 0; i <= n; i++ { name := RandString(8) sqlStr := "insert into user(name,age) values(?,?)" _, err := db.Exec(sqlStr, name, 18) // 未 prepare 处理的普通 SQL if err != nil { fmt.Println(err) } //rows, _ := ret.RowsAffected() //record := fmt.Sprintf("RowsAffected: %d", rows) //logger.Write(record) } }

测试结果如下：

• 4 个线程，每个线程 100 个 insert：

未使用 prepare： ./prepare_test --prepare=false --t=4 --i=100 cost time：409.5343ms 使用 prepare： ./prepare_test --prepare=true --t=4 --i=100 cost time：275.1861ms

• 4 个线程，每个线程 300 个 insert:

未使用 prepare： ./prepare_test --prepare=false --t=4 --i=300 cost time：1.4089236s 使用 prepare： ./prepare_test --prepare=true --t=4 --i=300 cost time：791.6015ms

篇幅有限：测试汇总如下

• --t thread : 线程数
• --i insert : 每个线程 insert 语句总数

并发参数 未使用 Prepare 使用 Prepare 性能提升 t=4 i=100 409.5343ms 275.1861ms 32% t=4 i=300 1.4089236s 791.6015ms 42% t=4 i=500 2.1703388s 1.129176s 47% t=8 i=100 629.015ms 297.4847ms 52% t=8 i=300 3.2628256s 1.67031s 50% t=8 i=500 3.2897162s 3.2978884s 0%

从测试结果看，使用 Prepare 进行 批量插入，和 普通的 sql 相比，性能提升在 30%-50% 之间，但是当超出 实例性能时用不用 Prepare 没有什么变化。

排除 其他干扰因素，保守估计 Prepare 批量插入时性能会提升在 20% - 40% 之间，这个变化还是比较明显的。

三：总结

1. Prepere 的使用场景是，SQL 语句 未发生改变，只是 query 的值发生了改变的情况。尤其是高并发 批量 SQL 的场景。
2. Prepere 在批量 插入时 性能提升在 20% - 40%。 但是 select / update 有待测试，感兴趣的同学可以测一下。
3. 当 并发 达到 实例性能上限时，Prepare SQL 和 普通 SQL 的 性能没有明显变化。
4. MySQL 5.6 版本开始支持 Prepere 预处理
   
   

参考文档：
dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

原创系列，转载请注明出处，谢谢！