一: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 进行 批量插入,和 普通的 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

原创系列,转载请注明出处,谢谢!

03-20 21:11