极客时间已完结课程限时免费阅读

06丨数据库原理：为什么PrepareStatement性能更好更安全？

06丨数据库原理：为什么PrepareStatement性能更好更安全？-极客时间

06丨数据库原理：为什么PrepareStatement性能更好更安全？

2019-11-29 李智慧 来自北京
《后端技术面试 38 讲》
课程介绍

讲述：李智慧

时长11:19大小10.37M

做应用开发的同学常常觉得数据库由 DBA 运维，自己会写 SQL 就可以了，数据库原理不需要学习。其实即使是写 SQL 也需要了解数据库原理，比如我们都知道，SQL 的查询条件尽量包含索引字段，但是为什么呢？这样做有什么好处呢？你也许会说，使用索引进行查询速度快，但是为什么速度快呢？
此外，我们在 Java 程序中访问数据库的时候，有两种提交 SQL 语句的方式，一种是通过 Statement 直接提交 SQL；另一种是先通过 PrepareStatement 预编译 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();
看代码，似乎第一种方式更加简单，但是编程实践中，主要用第二种。使用 MyBatis 等 ORM 框架时，这些框架内部也是用第二种方式提交 SQL。那为什么要舍简单而求复杂呢？
要回答上面这些问题，都需要了解数据库的原理，包括数据库的架构原理与数据库文件的存储原理。

数据库架构与 SQL 执行过程

我们先看看数据库架构原理与 SQL 执行过程。
关系数据库系统 RDBMS 有很多种，但是这些关系数据库的架构基本上差不多，包括支持 SQL 语法的 Hadoop 大数据仓库，也基本上都是相似的架构。一个 SQL 提交到数据库，经过连接器将 SQL 语句交给语法分析器，生成一个抽象语法树 AST；AST 经过语义分析与优化器，进行语义优化，使计算过程和需要获取的中间数据尽可能少，然后得到数据库执行计划；执行计划提交给具体的执行引擎进行计算，将结果通过连接器再返回给应用程序。
应用程序提交 SQL 到数据库执行，首先需要建立与数据库的连接，数据库连接器会为每个连接请求分配一块专用的内存空间用于会话上下文管理。建立连接对数据库而言相对比较重，需要花费一定的时间，因此应用程序启动的时候，通常会初始化建立一些数据库连接放在连接池里，这样当处理外部请求执行 SQL 操作的时候，就不需要花费时间建立连接了。
这些连接一旦建立，不管是否有 SQL 执行，都会消耗一定的数据库内存资源，所以对于一个大规模互联网应用集群来说，如果启动了很多应用程序实例，这些程序每个都会和数据库建立若干个连接，即使不提交 SQL 到数据库执行，也就会对数据库产生很大的压力。
所以应用程序需要对数据库连接进行管理，一方面通过连接池对连接进行管理，空闲连接会被及时释放；另一方面微服务架构可以大大减少数据库连接，比如对于用户数据库来说，所有应用都需要连接到用户数据库，而如果划分一个用户微服务并独立部署一个比较小的集群，那么就只有这几个用户微服务实例需要连接用户数据库，需要建立的连接数量大大减少。
连接器收到 SQL 以后，会将 SQL 交给语法分析器进行处理，语法分析器工作比较简单机械，就是根据 SQL 语法规则生成对应的抽象语法树。
如果 SQL 语句中存在语法错误，那么在生成语法树的时候就会报错，比如，下面这个例子中 SQL 语句里的 where 拼写错误，MySQL 就会报错。
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 的别名，但是语法分析器在构建语法树到了id=1这里的时候就出错了，所以返回的报错信息可以提示，在'id = 1'附近有语法错误。
语法分析器生成的抽象语法树并不仅仅可以用来做语法校验，它也是下一步处理的基础。语义分析与优化器会对抽象语法树进一步做语义优化，也就是在保证 SQL 语义不变的前提下，进行语义等价转换，使最后的计算量和中间过程数据量尽可能小。
比如对于这样一个 SQL 语句，其语义是表示从 users 表中取出每一个 id 和 order 表当前记录比较，是否相等。
select f.id from orders f where f.user_id = (select id from users);
事实上，这个 SQL 语句在语义上等价于下面这条 SQL 语句，表间计算关系更加清晰。
select f.id from orders f join users u on f.user_id = u.id;
SQL 语义分析与优化器就是要将各种复杂嵌套的 SQL 进行语义等价转化，得到有限几种关系代数计算结构，并利用索引等信息进一步进行优化。可以说，各个数据库最黑科技的部分就是在优化这里了。
语义分析与优化器最后会输出一个执行计划，由执行引擎完成数据查询或者更新。MySQL 执行计划的例子如下：
执行引擎是可替换的，只要能够执行这个执行计划就可以了。所以 MySQL 有多种执行引擎（也叫存储引擎）可以选择，缺省的是 InnoDB，此外还有 MyISAM、Memory 等，我们可以在创建表的时候指定存储引擎。大数据仓库 Hive 也是这样的架构，Hive 输出的执行计划可以在 Hadoop 上执行。

使用 PrepareStatement 执行 SQL 的好处

好了，了解了数据库架构与 SQL 执行过程之后，让我们回到开头的问题，应用程序为什么应该使用 PrepareStatement 执行 SQL？
这样做主要有两个好处。
一个是 PrepareStatement 会预先提交带占位符的 SQL 到数据库进行预处理，提前生成执行计划，当给定占位符参数，真正执行 SQL 的时候，执行引擎可以直接执行，效率更好一点。
另一个好处则更为重要，PrepareStatement 可以防止 SQL 注入攻击。假设我们允许用户通过 App 输入一个名字到数据中心查找用户信息，如果用户输入的字符串是 Frank，那么生成的 SQL 是这样的：
select * from users where username = 'Frank';
但是如果用户输入的是这样一个字符串：
Frank';drop table users;--
那么生成的 SQL 就是这样的：
select * from users where username = 'Frank';drop table users;--';
这条 SQL 提交到数据库以后，会被当做两条 SQL 执行，一条是正常的 select 查询 SQL，一条是删除 users 表的 SQL。黑客提交一个请求然后 users 表被删除了，系统崩溃了，这就是 SQL 注入攻击。
如果用 Statement 提交 SQL 就会出现这种情况。
但如果用 PrepareStatement 则可以避免 SQL 被注入攻击。因为一开始构造 PrepareStatement 的时候就已经提交了查询 SQL，并被数据库预先生成好了执行计划，后面黑客不管提交什么样的字符串，都只能交给这个执行计划去执行，不可能再生成一个新的 SQL 了，也就不会被攻击了。
select * from users where username = ?;

数据库文件存储原理

回到文章开头提出的另一个问题，数据库通过索引进行查询能加快查询速度，那么，为什么索引能加快查询速度呢？
数据库索引使用 B+ 树，我们先看下 B+ 树这种数据结构。B+ 树是一种 N 叉排序树，树的每个节点包含 N 个数据，这些数据按顺序排好，两个数据之间是一个指向子节点的指针，而子节点的数据则在这两个数据大小之间。
如下图。
B+ 树的节点存储在磁盘上，每个节点存储 1000 多个数据，这样树的深度最多只要 4 层，就可存储数亿的数据。如果将树的根节点缓存在内存中，则最多只需要三次磁盘访问就可以检索到需要的索引数据。
B+ 树只是加快了索引的检索速度，如何通过索引加快数据库记录的查询速度呢？
数据库索引有两种，一种是聚簇索引，聚簇索引的数据库记录和索引存储在一起，上面这张图就是聚簇索引的示意图，在叶子节点，索引 1 和记录行 r1 存储在一起，查找到索引就是查找到数据库记录。像 MySQL 数据库的主键就是聚簇索引，主键 ID 和所在的记录行存储在一起。MySQL 的数据库文件实际上是以主键作为中间节点，行记录作为叶子节点的一颗 B+ 树。
另一种数据库索引是非聚簇索引，非聚簇索引在叶子节点记录的就不是数据行记录，而是聚簇索引，也就是主键，如下图。
通过 B+ 树在叶子节点找到非聚簇索引 a，和索引 a 在一起存储的是主键 1，再根据主键 1 通过主键（聚簇）索引就可以找到对应的记录 r1，这种通过非聚簇索引找到主键索引，再通过主键索引找到行记录的过程也被称作回表。
所以通过索引，可以快速查询到需要的记录，而如果要查询的字段上没有建索引，就只能扫描整张表了，查询速度就会慢很多。
数据库除了索引的 B+ 树文件，还有一些比较重要的文件，比如事务日志文件。
数据库可以支持事务，一个事务对多条记录进行更新，要么全部更新，要么全部不更新，不能部分更新，否则像转账这样的操作就会出现严重的数据不一致，可能会造成巨大的经济损失。数据库实现事务主要就是依靠事务日志文件。
在进行事务操作时，事务日志文件会记录更新前的数据记录，然后再更新数据库中的记录，如果全部记录都更新成功，那么事务正常结束，如果过程中某条记录更新失败，那么整个事务全部回滚，已经更新的记录根据事务日志中记录的数据进行恢复，这样全部数据都恢复到事务提交前的状态，仍然保持数据一致性。
此外，像 MySQL 数据库还有 binlog 日志文件，记录全部的数据更新操作记录，这样只要有了 binlog 就可以完整复现数据库的历史变更，还可以实现数据库的主从复制，构建高性能、高可用的数据库系统，我将会在架构模块进一步为你讲述。

小结

做应用开发需要了解 RDBMS 的架构原理，但是关系数据库系统非常庞大复杂，对于一般的应用开发者而言，全面掌握关系数据库的各种实现细节，代价高昂，也没有必要。我们只需要掌握数据库的架构原理与执行过程，数据库文件的存储原理与索引的实现方式，以及数据库事务与数据库复制的基本原理就可以了。然后，在开发工作中针对各种数据库问题去思考，其背后的原理是什么，应该如何处理。通过这样不断地思考学习，不但能够让使用数据库方面的能力不断提高，也能对数据库软件的设计理念也会有更深刻的认识，自己软件设计与架构的能力也会得到加强。

思考题

索引可以提高数据库的查询性能，那么是不是应该尽量多的使用索引呢？如果不是，为什么？你还了解哪些改善数据库访问性能的技巧方法？
欢迎你在评论区写下你的思考，也欢迎把这篇文章分享给你的朋友或者同事，一起交流进步。
分享给需要的人，Ta购买本课程，你将得18元
生成海报并分享

赞 12

提建议

© 版权归极客邦科技所有，未经许可不得传播售卖。 页面已增加防盗追踪，如有侵权极客邦将依法追究其法律责任。
上一篇
05丨文件系统原理：如何用1分钟遍历一个100TB的文件？
下一篇
07丨编程语言原理：面向对象编程是编程的终极形态吗？
unpreview
 写留言

精选留言(25)

  • ⛽️🦆
    2019-11-29
    您好，老师： 回答上述问题 1.创建多的索引，会占用更多磁盘空间。如果有一张很大的表，索引文件的大小可能达到操作系统允许的最大文件限制； 2.对于DML操作的时候，索引会降低他们的速度。因为MySQL不仅要把搞定的数据写入数据文件，而且它还要把这些改动写入索引文件; 改善数据库性能： 1.索引优化，选择合适的索引列，选择在where、group by、order by、on 从句中出现的列作为索引项，对于离散度不大的列没有必要创建索引。 2.索引字段越小越好。 3.SQL语句的优化、数据表结构的优化。 3.1：选择可存数据最小的数据类型，选择最合适的字段类型，进行数据的存储; 3.2：数据量很大的一张表，应该考虑水平分表或垂直分表； 3.3：尽量不要使用text字段，如果非要用，那么应考虑将它存放另一张表中； 4.数据库配置的优化： 4.1：连接数的配置，因为大量的连接，不进行操作，那样也会占用内存。 4.2：查询缓存的配置，但在MySQL 8.0就删除了此功能。 5.硬件的配置; 额外加说一下，常见性能的问题： 1.条件字段函数的操作，给索引字段做了函数计算，就会破坏索引值，因此优化器就放弃了走树搜索能够; 2.隐式类型转换，比如数据库字段是varchar类型，创建的索引，但是使用的时候传入的是int类型，那么会走全表扫面; 3.隐式字符编码转换，如果join 两表的时候，两表的字符集不同，也不能用上索引；
    展开
    共 6 条评论
    109
  • 无形
    2019-12-01
    最近在广告检索中接入了用户画像标签，实现了把一大串嵌套json格式的标签数据表达式，类似dnf表达式，解析为可计算的广告匹配模型，其中就有类似sql一样，对表达式进行格式分析、数据、比较符检验、复杂的逻辑关系转换为容易处理的计算单元，最后生成一个树状匹配模型，通过对模型输入用户数据，进行匹配，并返回符合的用户。 这有点像用户画像标签就是一条SQL，对SQL进行语法分析，生成匹配模型（类比SQL的执行计划），当下次输入用户数据的时候用生成好的匹配模型直接处理数据，不必再重复解析画像标签
    展开
    8
  • 尹宗昌
    2019-11-29
    索引可以提高查询性能，但是一定要考虑维护索引的成本。空间占用、插入修改的性能影响都要衡量
    7
  • 杯莫停
    2020-08-12
    看到预编译这个概念就想到Mybites的变量占位符$和#，前者没有预编译直接以字符串的方式被append到sql语句中，以至于有sql注入被黑客攻击的可能。而#会被预编译成一个符号，在使用的时候再把参数值填进来，这样就避免了sql注入风险。至于原理还要去看源码。
    4
  • Zend
    2019-12-01
    请问一下老师PreparedStatement经过语法分析生成的抽象语法树，再经过语义分析和优化器处理后生成的执行计划，会有缓存吗？

    作者回复: 会

    共 2 条评论
    4
  • Zend
    2019-12-01
    请问老师，在进行事务操作时，事务日志文件会记录更新前的数据记录，这个记录更新前的数据记录是 什么意思，是把更新之前的数据都查询出来，记录到事务日志文件嘛

    作者回复: 是的，更新数据本来就会先执行查询操作。

    4
  • 陈衎
    2020-03-29
    索引不是越多越好，索引多了对磁盘容量产生代价。增删改，存储过程等都会涉及到索引的修改，导致效率下降。还有比如字段查询量少，重复多，比如性别等，是没必要建立索引的。觉得索引还是要看使用场景吧，比如数据仓库。 优化性能和提高查询效率，个人觉得是一种编码习惯和思想过程，比如小表驱动大表，根据实际合理创建表结构范式，建立联合查询一次到位避免回表，清楚select ，join，group by，having，where，order by这些执行顺序。设计健壮灵活的数据表结构。
    展开
    2
  • LIKE
    2020-07-01
    看文章讲解，聚簇索引，不止备份了整个表数据，增删改查开销也大，所以应减少不必要的聚簇索引，除主键外其他可考虑使用非聚簇索引。 那如何指定索引为非聚簇索引呢？

    作者回复: 主键索引就是聚簇索引，非主键索引就是非聚簇索引

    2
  • 开心小毛
    2020-02-15
    请问李老师，既然数据库连接会占用内存，那么传统web应用为什么不干脆在每一次接受http请求时建立数据库连接，然后再在http访问结束后断开数据库连接？ 建立数据库连接会耗CPU吗？（假设数据库服务自身已经有线程池等待被连接，web应用也缓存了地址密码等配置信息）

    作者回复: 建立数据库连接会很耗时，每次建立连接会更消耗数据库的资源。

    共 3 条评论
    1
  • 无形
    2019-12-01
    数据变动的时候同时需要更新索引，索引多了，数据变动的效率就会降低，索引也是文件，会占用更多的磁盘空间 。 另外，我之前提到的要动手实现的高性能检索系统，在数据的存储部分，用文件存储，为了提高文档的检索效率，为文件创建了索引，索引是这种格式id:start:length， 文档id:在文件中的起始位置:文档的长度，索引文件是排序过得，例如下面这种格式 0000000001:0000000000:0000000100 0000000002:0000000100:0000000300 0000000007:0000000400:0000001000 在不加载完整索引文件的情况下，这样可以通过offset快速读取每个索引数据，因为是排序过的，二分查找快速查找到文档的起始位置和大小，就可以迅速在文件中找到文档，测试了下，文档数据量160w，最多20次就可以查找到文档，耗时200-300微妙，如果对索引文件进行分片存储还会更快
    展开
    2
  • 乘坐Tornado的线程魔...
    2019-11-30
    请问下，文中的第一个B+树示意图的第一层中间节点，8和34中间的部分是不是也应该指向一个子节点？

    作者回复: 是的，图中只是示意，没有画出全部节点。

    共 2 条评论
  • 而立斋
    2019-11-30
    李老师，堪比百科全书啊！非常接地气！给老师提个建议，文章中有些是针对MySQL说的，有些不是，希望老师能区分一下。 索引么，也是一种空间换时间的思路，细思极恐。改善数据库访问性能的手段也不应该仅限于索引这种手段。
    1
  • 俊伟
    2019-11-29
    1.索引较多，插入删除的时候会有额外的开销。 2.索引字段尽量小，因为索引字段的大小会影响每一个b+树节点数据块中的数据项的个数。 3.多了解业务，有些地方可以使用索引有些则不用。 4.可以综合利用联合索引和单独索引
    2
  • 米兰的小铁匠
    2021-06-25
    思考总结：PrepareStatement预编译、然后设置可变参数再执行提交。 1、介绍了Sql执行流程： 查询：连接器-查询缓存-分析器-优化器-执行器 更新：获取数据（数据页内存中判断）-更新（更新到内存中）prepare redolog-完成后binlog-redolog 2、PrepareStatement：预处理使效率高、防止Sql注入 3、底层结构：B+树 多叉树：矮，文件io少 节点不存储数据，只是索引 链表讲叶子节点串联一起，方便区间查找 一般情况，根节点会存储再内存中，其他节点存储再磁盘
    展开
    1
  • Geek_5180b5
    2021-05-06
    处理中占位符参数的替换，就是在真正执行sql的时候才替换，可以这样理解嘛。。
    共 1 条评论
  • pcz
    2021-02-22
    老师：PrepareStatement提交带占位符的sql进行预处理的意思是：执行到语义分析和优化器这一步，但是不执行引擎？那这个sql的赋值过程是什么样的？

    作者回复: ast优化后，依然是一个ast，赋值的时候，将真实值替换ast的expression节点即可

  • escray
    2020-10-18
    用一篇文章来讲数据库原理，感觉还是有点困难的。如果想要入门，那么推荐《SQL必知必会》专栏；如果想要深入学习 MySQL，那么推荐丁奇的《MySQL实战45讲》。 PrepareStatement 应该是属于编程语言还是数据库驱动？很久以前，用 C# 连接 Oracle 的时候就接触过 PrepareStatement 的概念，Java 连接 MySQL 或者 Oracle 肯定也有，毫无疑问 SQL Server 也不会缺少这个概念。 而且有点也都类似，在提高性能的同时避免注入攻击。 我的印象里隐约有，使用 PrepareStatement 并不利于数据库的 SQL 查询结果缓存，没有找到出处。但是现在新版本的 MySQL 似乎已经弃用了查询缓存。 能理解数据库存储原理（B+树），能分清楚主键聚簇索引和非聚簇索引，能看懂执行计划的程序员，都是好程序员。
    展开
    1
  • Geek__风雨
    2020-06-13
    分析器，优化器（待执行的sql），执行器，执行引擎
  • FreezeSoul
    2020-03-28
    感谢，之前只是知道preparestatement会生成语法树避免的注入，现在才明白是生成执行计划，避免的注入
  • 探索无止境
    2020-03-03
    老师您好，文中提到“每个节点存储 1000 多个数据，这样树的深度最多只要 4 层，就可存储数亿的数据”，严谨来说应该是三层即可，1000*1000*1000，我是这么计算的

    作者回复: 树要有个根节点，算一层~