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