jdbc mysql预编译测试类_MySQL性能优化-预编译语句

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

什么是预编译语句？

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

词法解析、语义解析。

优化SQL语句，生成执行计划。

执行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线程。