为什么PrepareStatement性能更好更安全？

行业现实在于面试要求大而全，实际工CRUD。以前我也觉得什么数据库搭建，什么性能优化，什么缓存。都是DBA要考虑的，我们只管CRUD。什么？你们公司连DBA都没有？太落后了吧？现在发现，以前都是井底之蛙，没机会遇到性能问题，而不是没有性能问题。现在基本上出去面试，SQL优化，数据库优化，JDBC底层原理都是硬性指标了。闲话不扯，主题开始吧。

我们Java用SQL操作数据库，有两种方式：

 直接提交用Statement

statement.executeUpdate("UPDATE Users SET stateus = 2 WHERE userID=233");

 预编译PrepareStatement

PreparedStatement updateUser = con.prepareStatement("UPDATE Users SET stateus = ? WHERE userID = ?"); 
updateUser.setInt(1, 2); 
updateUser.setInt(2,233); 
updateUser.executeUpdate();

很显然，Statement看起来要比PrepareStatement要简单一点。但是一些ORM框架像Mybites都采用第二种。为什么不用简单的要用复杂的呢？这个灵魂拷问可不简单，你需要理解数据库架构和文件存储原理才能回答。

数据库架构与 SQL 执行过程

1. 数据库架构原理和SQL执行过程

关系型数据库系统RDBMS有很多种，但关系型数据库架构都差不多，包括支持SQL的Hadoop也一样。

以Mysql为例可分为两层，，server层和存储引擎层，server含有连接器，查询缓存，语法分析器，语义分析与优化器和执行引擎这些组件。所有跨存储引擎的功能都在这一层实现，比如存储过程，视图，触发器等

存储引擎负责数据存储和提取，不同的存储引擎事实上是对数据的不同存储方式。Mysql存储引擎有InnoDB，MYSIAM，Memory 等。从Mysql 5.5.5版本后默认的存储引擎是InnoDB。就是说如果想要使用其他存储引擎，就需要在create table的时候特殊指定例如engine=memory。关于InnoDB，MYSIAM存储引擎的存储原理，后面再写博客。

下面拆解一下Server层。

连接器

用来跟Client建立连接（说到建立连接不得不知道的就是TCP三次握手）、校验权限、维持和管理连接。

1. 在TCP三次握手后开始检验用户名密码，如果不正确，你就会收到"Access denied for user"的错误，然后客户端结束执行
2. 用户名密码验证通过后，开始读取权限表，以后的权限验证逻辑都依赖于本次连接时读到的权限

也就是说即使你本次连接后修改了权限表，也要等到下次创建新的连接后生效，本次连接权限还是连接创建后读到的权限。

连接建立后，如果客户端没有发送数据，改连接就出于空闲休眠状态，Mysql默认的超时断开时间是8h，这个是由wait_timeout这个参数决定的。也就是说超过8h后客户端发送请求就会报错：connection to MySQL server during query 这个时候只能重新建立连接来做事情

连接分为长连接和段连接

1. 长连接：客户端连续的请求都使用同一个连接
2. 短连接：客户端有限的几次请求使用同一个连接，请求完成后就断开，如需再次请求，需要重新建立连接

 连接的建立是个很复杂的过程，需要消耗一定的CPU资源，所以要避免连接的频繁穿件和销毁。尽可能使用长连接。但是长连接也有个问题就是连接空闲时，它依旧占用资源，长时间积累下来表现就是CPU占满，OOM，最后被重启。

解决这个问题：

1. 在每次大量查询之后把连接断开重新建立连接
2. Mysql 5.7及以上版本，在一个比较大的操作之后，可以用mysql_reset_connection来初始化连接资源。不需要断开连接，就可以让连接恢复到连接刚建立的状态

既然每个连接其实就是一个线程，不管有没有SQL提交，都会消耗一定的内存资源。并且现在流行的ORM框架像Mybites其实就是对JDBC的封装，所以像大规模的集群启动了若干实例，每个实例都与数据库建立连接，这样数据库内存资源是吃不消的。所以程序要通过连接池对连接进行管理，将闲置的连接给清理掉。微服务框架的使用也做了优化。

查询缓存

连接建立后会先去查查询缓存，之前查询的结果会以Key：Value的方式存放到查询缓存，key是查询语句，value是查询结果。如果命中就会直接返回value，如果没有命中就会执行下面后续过程，查询完成后再存进缓存。虽然查询缓存可以大大提高查询性能，但现实中我们只有在一些固定变的数据做查询缓存。如果数据是经常更新的，每次更新对一张表进行更新操作都会清空这张表所有的查询缓存，所以辛苦创建的缓存，还没来得及用就失效了，完全没必要。

好在MySQL提供了查询缓存按需使用的选择。不需要查询缓存可以将参数query_cache_type设置成DEMAND，如果要指定对某个查询进行查询缓存只需要在查询语句中用SQL_CACHE指定，如：

mysql> select SQL_CACHE * from T where ID=10；

要注意的是MySQL 8.0及以后版本已经将查询缓存的功能完全去掉。

语法分析器

连接器收到SQL后交给了语法分析器，而语法分析器的职责就是分析SQL语句的语法，最后生成语法树AST。SQL语句不符合语法，生成语法树的时候就会报错，比如故意写错where为whee：

mysql> explain select * from users whee id = 1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id = 1' at line 1

 语法分析器在分析到whee的时候还没有报错，因为whee有可能是users的别名，直到di = 1才报的错，所以有“near 'id = 1' at line 1”。

语法树不仅仅是用来做语法校验的，还是语义分析与优化器的基础，在保证原SQL语义不变的前提下，进行部分语句的定价转换，例如以下SQL

select f.id from orders f where f.user_id = (select id from users);

语义上等价于：

select f.id from orders f join users u on f.user_id = u.id;

语义分析与优化器就是对复杂嵌套的SQL语句，进行语义等价转化。并根据索引等信息进行优化。这就是各个数据库的黑科技了。语义分析与优化器最后输出一个执行计划由执行引擎完成执行。这个执行引擎是可以替换的，说执行引擎可能不熟悉，说存储引擎就比较熟悉了。MYSQL的innoDB、MyISAM等，只是底层实现采用的存储结构不一样,但是执行计划是通用的。Mysql我们在创建表的时候就可以指定执行引擎。Hive的执行计划在Hadoop上也是可以执行的。

2.PrepareStatement 执行 SQL 的好处

PrepareStatement有两个好处：

第一个好处是能预先提交带占位符的SQL到数据库，提前生成执行计划。当给定占位符参数，真正执行SQL语句的时候，执行引擎可以直接执行。这样效率会更好。

第二个好处是防止SQL注入，比如，没有使用PrepareStatement执行SQL，假如后面的查询条件username 是由用户输入的字符串。用户输入Alan

select * from users where username = 'Alan';

但是如果用户输入的字符串是

Alan';drop table users;--

 那么sql语句就会变成

select * from users where username = 'Alan';drop table users;--'

很显然，这是字符串的拼接。在执行时，这是两条sql语句，后面一条直接删除了users表，导致所有人所有服务都无法访问这个表，导致系统奔溃。

用了PrepareStatement后对SQL进行预编译，把需要用户输入的参数用一个占位符 ？ 代替如

select * from users where username =?

此时执行计划已经生成，不可能再生成新的sql，所以不会有sql注入的问题，就不会受到攻击。

另外补充一点，Mybites里面我们在mapper.xml中写用户输入参数最好用'#{}'而不是'${}',原因是'${}'不会参与预编译，在预编译之前就以字符串拼接的方式生成了SQL。而'#{}'会参与预编译，预编译时被替换成了?占位符，跟上面说的例子一样。但是有些场景必须得使用'${}'，像查询参数是表名。这就需要我们自己去对这个操作去做谨慎的限制了。