MySQL性能优化-预编译语句

MySQL, 数据库 / 作者：

MySQL性能优化的方法有很多，预编译是使用比较多，效果比较好的一种方法。本文将简单介绍什么是MySQL预编译语句，如何使用预编译语句，以及使用预编译语句在性能上能带来多少提高。

什么是预编译语句？

MySQL在执行SQL语句时，分为几个阶段。

  1. 词法解析、语义解析。
  2. 优化SQL语句，生成执行计划。
  3. 执行SQL并返回结果。

很多情况下，同一类SQL会反复执行，只是SQL中的某些具体的值不同，本质上属于同一类SQL。比如select中的where条件值不同，insert中的values值不同，update中的set值不同等等。这些SQL只要解析一次，以后再次执行时，就不再需要进行词法解析、语义解析、SQL语句优化、生成执行计划，这些过程都不再需要，只要告诉MySQL具体的参数值变了就行。通常我们称这类SQL为Prepared Statements。

使用预编译语句，同一类SQL，只要一次编译，多次运行，省去了不必要的重复的解析优化过程，大大提高了SQL语句执行性能，另外预编译功能也能防止SQL注入。

如何使用预编译语句？

通过prepare关键字来编译一个SQL语句，通过execute关键字来执行一个具体的SQL，通过deallocate关键字来释放一个预编译SQL语句。示例如下：

mysql> prepare sel from 'select * from t where c1=? and c2=?';
mysql> set @a='100',@b='200';
mysql> execute sel using @a,@b;
+------+------+------+---------------------+
| id   | c1   | c2   | ts                  |
+------+------+------+---------------------+
| 3275 | 100  | 200  | 2020-01-18 11:53:39 |
| 3277 | 100  | 200  | 2020-01-18 11:53:39 |
+------+------+------+---------------------+
2 rows in set (0.01 sec)

mysql> set @a='400',@b='400';
mysql> execute sel using @a,@b;
+------+------+------+---------------------+
| id   | c1   | c2   | ts                  |
+------+------+------+---------------------+
| 3279 | 400  | 400  | 2020-01-19 15:10:09 |
+------+------+------+---------------------+
1 row in set (0.00 sec)

mysql> deallocate prepare sel;
Query OK, 0 rows affected (0.00 sec)

除了直接在mysql命令行中使用预编译语句，也可以在JDBC中使用预编译功能。代码如下：

String sql = "insert into t select ?,?"; 
PreparedStatement statement = con.prepareStatement(sql);
statement.setString(1, "abc");
statement.setString(2, "abc"); 
statement.executeUpdate();

预编译语句性能有多少提高？

使用预编译语句：

-------------------QPS--TPS-----------
  time  |  ins   upd   del    sel   iud|
15:37:55| 1837     0     0      1  1837|
15:37:56| 1916     0     0      1  1916|
15:37:57| 1818     0     0      1  1818|
15:37:58| 1603     0     0      1  1603|
15:37:59| 1784     0     0      1  1784|
15:38:00| 1751     0     0      1  1751|
15:38:01| 1756     0     0      1  1756|
15:38:02| 1502     0     0      1  1502|
15:38:03| 2088     0     0      1  2088|
15:38:04| 1886     0     0      2  1886|
15:38:05| 1986     0     0      1  1986|
15:38:06| 1593     0     0      1  1593|
15:38:07| 1003     0     0      1  1003|
15:38:08| 1561     0     0      1  1561|
15:38:09| 2024     0     0      1  2024|

不使用预编译语句：

-------------------QPS--TPS-----------
  time  |  ins   upd   del    sel   iud|
15:49:08| 1557     0     0      1  1557|
15:49:09| 1583     0     0      2  1583|
15:49:10| 1593     0     0      1  1593|
15:49:11| 1677     0     0      1  1677|
15:49:12| 1841     0     0      1  1841|
15:49:13| 1568     0     0      1  1568|
15:49:14| 1692     0     0      1  1692|
15:49:15| 1631     0     0      1  1631|
15:49:16| 1774     0     0      1  1774|
15:49:17| 1642     0     0      1  1642|
15:49:18| 1748     0     0      1  1748|
15:49:19| 1520     0     0      2  1520|
15:49:20| 1666     0     0      1  1666|
15:49:21| 1657     0     0      1  1657|
15:49:22| 1659     0     0      1  1659|

使用预编译平均TPS：1740
不使用预编译平均TPS：1653

测试结果：使用预编译SQL，性能提高约5.2%

注：
测试使用的MySQL版本为5.7.19，1核1G，并发10线程。

发表评论