Prepared 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 性能测试的帖子很少， 于是需要自己写测试工具。（ http://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 预处理
   
   

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

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