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线程。
文章评论