后端技术面试38讲
李智慧
同程艺龙交通首席架构师,前Intel&阿里架构师,《大型网站技术架构》作者
立即订阅
3590 人已学习
课程目录
已更新 13 讲 / 共 38 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 掌握软件开发技术的第一性原理
免费
软件的基础原理 (8讲)
01丨程序运行原理:程序是如何运行又是如何崩溃的?
02丨数据结构原理:Hash表的时间复杂度为什么是O(1)?
03丨Java虚拟机原理:JVM为什么被称为机器(machine)?
04丨网络编程原理:一个字符的互联网之旅
05丨文件系统原理:如何用1分钟遍历一个100TB的文件?
06丨数据库原理:为什么PrepareStatement性能更好更安全?
07丨编程语言原理:面向对象编程是编程的终极形态吗?
答疑丨Java Web程序的运行时环境到底是怎样的?
软件的设计原理 (3讲)
08丨软件设计的方法论:软件为什么要建模?
09丨软件设计实践:如何使用UML完成一个设计文档?
10 | 软件设计的目的:糟糕的程序员比优秀的程序员差在哪里?
不定期加餐 (1讲)
加餐 | 软件设计文档示例模板
后端技术面试38讲
登录|注册

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

李智慧 2019-11-29
做应用开发的同学常常觉得数据库由 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 经过语义分析与优化器,进行语义优化,使计算过程和需要获取的中间数据尽可能少,然后得到数据库执行计划;执行计划提交给具体的执行引擎进行计算,将结果通过连接器再返回给应用程序。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《后端技术面试38讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(13)

  • ⛽️🦆
    您好,老师:
    回答上述问题
    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 两表的时候,两表的字符集不同,也不能用上索引;
    2019-11-29
    3
    26
  • 尹宗昌
    索引可以提高查询性能,但是一定要考虑维护索引的成本。空间占用、插入修改的性能影响都要衡量
    2019-11-29
    2
  • Zend
    请问一下老师PreparedStatement经过语法分析生成的抽象语法树,再经过语义分析和优化器处理后生成的执行计划,会有缓存吗?

    作者回复: 会

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

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

    2019-12-01
    1
  • Paul Shan
    聚簇索引,叶子节点指向了记录。
    非聚簇索引,叶子节点指向了主键。
    索引肯定不是越多越好,不然就会默认创建。索引有成本,成本来自于存储成本和插入删除时候的维护成本,如果查询得到的好处不足以抵消维护和存储成本,就是不值得的。
    2019-12-05
  • 无形
    最近在广告检索中接入了用户画像标签,实现了把一大串嵌套json格式的标签数据表达式,类似dnf表达式,解析为可计算的广告匹配模型,其中就有类似sql一样,对表达式进行格式分析、数据、比较符检验、复杂的逻辑关系转换为容易处理的计算单元,最后生成一个树状匹配模型,通过对模型输入用户数据,进行匹配,并返回符合的用户。
    这有点像用户画像标签就是一条SQL,对SQL进行语法分析,生成匹配模型(类比SQL的执行计划),当下次输入用户数据的时候用生成好的匹配模型直接处理数据,不必再重复解析画像标签
    2019-12-01
  • 无形
    数据变动的时候同时需要更新索引,索引多了,数据变动的效率就会降低,索引也是文件,会占用更多的磁盘空间

    另外,我之前提到的要动手实现的高性能检索系统,在数据的存储部分,用文件存储,为了提高文档的检索效率,为文件创建了索引,索引是这种格式id:start:length,
    文档id:在文件中的起始位置:文档的长度,索引文件是排序过得,例如下面这种格式
    0000000001:0000000000:0000000100
    0000000002:0000000100:0000000300
    0000000007:0000000400:0000001000
    在不加载完整索引文件的情况下,这样可以通过offset快速读取每个索引数据,因为是排序过的,二分查找快速查找到文档的起始位置和大小,就可以迅速在文件中找到文档,测试了下,文档数据量160w,最多20次就可以查找到文档,耗时200-300微妙,如果对索引文件进行分片存储还会更快
    2019-12-01
  • 一步
    SQL 的分析器 包括词法分析器和语法分析器,经过词法分析器生成 AST ,不是语法分析
    2019-11-30
  • 乘坐Tornado的线程魔法师
    印象中丁奇的《MySQL实战45讲》中,也讲到了回表。但是文中提到的MySQL是聚簇索引,不需要回表。是不是这里面有些概念需要根据具体情况再进一步拆分讨论。并不能一概而论?
    2019-11-30
    7
  • 乘坐Tornado的线程魔法师
    请问下,文中的第一个B+树示意图的第一层中间节点,8和34中间的部分是不是也应该指向一个子节点?

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

    2019-11-30
  • 老王的老李头
    李老师,堪比百科全书啊!非常接地气!给老师提个建议,文章中有些是针对MySQL说的,有些不是,希望老师能区分一下。
    索引么,也是一种空间换时间的思路,细思极恐。改善数据库访问性能的手段也不应该仅限于索引这种手段。
    2019-11-30
  • 俊伟
    1.索引较多,插入删除的时候会有额外的开销。
    2.索引字段尽量小,因为索引字段的大小会影响每一个b+树节点数据块中的数据项的个数。
    3.多了解业务,有些地方可以使用索引有些则不用。
    4.可以综合利用联合索引和单独索引
    2019-11-29
  • 幸福来敲门
    2019-11-29
收起评论
13
返回
顶部