架构师训练营 1 期第 9 周：性能优化（三）- 总结

关注
发布于: 2020 年 11 月 22 日
本文主要介绍数据库的基本原理。包括数据库的基本架构，数据记录的存储结构和数据库事务原子性保证等内容。
数据库的基本架构一条SQL语句首先通过连接器提交到数据库，然后连接器把SQL语句交给语法分析器去进行语法分析，生成一棵语法树，然后再对这棵语法树进行语义分析，它真正执行的语义操作转换成关系代数操作的树结构，然后再在这个树结构上进行相关的优化，一些不必要的操作可以舍弃，一些可以合并的操作进行合并，最终使执行效率更高，最后优化完了以后，根据这棵树生成一个执行计划，交给执行引擎把相关的操作在数据库系统中执行。
连接器：数据库连接器会为每个连接请求分配一块专用的内存空间用于会话上下文管理。建立连接对数据库而言相对比较重，需要花费一定的时间，因此应用程序启动的时候，通常会初始化建立一些数据库连接放在连接池里，这样当处理外部请求执行SQL操作的时候， 就不需要花费时间建立连接了。
语法分析器：把SQL语句构建成抽象语法树，后面的所有工作都是在抽象语法树上完成的，当我们提交的SQL语句语法有错误的时候，构建语法树时就会出错。
语义分析与优化器：就是要将各种复杂嵌套的SQL进行语义等价转化，得到有限几种关系代数计算结构（主要有选择，投影，交集，并集，连接等），并利用索引等信息进一步进行优化。各个数据库的性能差别主要是在它们在语法优化的时候，是否有更好的语法优化点或机制，可以对更复杂的或特定情况下的SQL语句进行性能优化，从而执行效率更好，用户体验更好，市场占有率更高。
执行引擎：根据优化完的语法树生成执行计划，交给执行引擎去执行，执行引擎找到对应的表，相关索引，要处理的数据行数，导数据库的存储文件上把相关数据查找到，然后返回最终的结果
为什么 PrepareStatement 更好？
PrepareStatement 会预先提交带占位符的 SQL 到数据库进行预处理，提前生成执行计划，当给定占位符参数，真正执行 SQL 的时候，执行引擎可以直接执行，效率更好一点。 
PrepareStatement 可以防止 SQL 注入攻击。如下SQL注入将一条SQL语句转化为3条SQL，导致users表被恶意用户删除。使用PrepareStatement后数据库已经预先生成执行计划，注入信息直接带入执行计划，不会重新生成执行计划，从而防止了SQL注入攻击。
数据库数据记录的存储结构很多数据库如MySQL的数据记录是通过B+树存储的。
B+树是一种多层的检索数，它的根节点有多个数据，当我们查找数据的时候，根据数据的范围，逐层找到下层节点，最终找到数据所在叶子节点。B+数所有要查找的数据最终都会在叶子节点出现，同时所有叶子节点构建成了链表，通过链表可以遍历所有叶子节点里的数据。
聚簇索引：把行记录在叶子节点上这种记录方式叫做聚簇索引
聚簇索引的数据库记录和索引存储在一起。 
MySQL 数据库的主键就是聚簇索引，主键 ID 和所在的记录行存储在一个B+树中。通过有限的节点遍历，可以快速的找到对应的主键并取出对应行记录
非聚簇索引：当我们需要在非主键子段上建立索引的时候，会构建另一个B+数，这个B+数叶子节点没有必要把行记录再记录一遍，只记录主键子段就可以了，这种索引叫做非聚簇索引
非聚簇索引在叶子节点记录的就不是数据行记录，而是聚簇索引，也就是主键。
通过非聚簇索引找到主键索引，再通过主键索引到聚簇索引上找到行记录的过程也被称作回表。
添加必要的索引优化SQL查询性能
在几百万行的数据库中查找一个条记录，如果没有索引，就需要全表扫描，检索所有的行记录，才能找到需要的记录。如果有索引，查询速度机会快很多。
没有索引，表记录行数越多，性能越差，同时对数据库的负载压力也很大，有了索引就可以通过索引在B+树上快速的查找到想要的结果。所以优化SQL，优化数据库查询最主要的手段就是添加必要的索引。
索引对查询的速度优化很多，但是索引越多，构建的B+树越多，插入或删除数据时更新的B+树叶越多，性能变差，所以只添加必要的索引
合理使用索引
不要盲目添加索引，尤其在生产环境中
添加索引的alter操作会消耗较长的时间（分钟级）
Alter操作期间，所有数据库的增删改操作全部阻塞，对应用而言，因为连接不能释放，事实上，查询也被阻塞。
删除不用的索引，避免不必要的增删开销
使用更小的数据类型创建索引，索引是要存放在B+树的节点上的，字段越小同样的节点可以存放更多的数据，也意味着一次查找拿到一个数据节点，里面得到的数据更多，查询速度更快，性能更好
int 4字节 bigint 8字节，Timestamp 4字节 Datetime 8字节
数据库事务事务特性 ACID 
原子性（Atomicity）: 事务要么全部完成，要么全部取消。 如果事务崩溃，状态回到事务之前（事务回滚）。 
隔离性（Isolation）: 如果2个事务T1和T2同时运行，事务T1和T2最终的结果是相同的，不管T1和T2谁先结束，隔离性主要依靠锁实现。 
持久性（Durability）: 一旦事务提交，不管发生什么（崩溃或者出错），数据要保存在数据库中。 
一致性（Consistency）: 只有合法的数据（依照关系约束和函数约束）才能写入数据库。
数据库事务日志：保证数据库事务的原子性
进行事务操作时，事务日志文件会记录更新前的数据记录，然后再更新数据库中的记录， 如果全部记录都更新成功，那么事务正常结束，如果过程中某条记录更新失败，那么整个事务全部回滚，已经更新的记录根据事务日志中记录的数据进行恢复，这样全部数据都恢复到事务提交前的状态，仍然保持数据一致性。
LSN：一个按时间顺序分配的唯一事务记录日志序列号。
TransID：产生操作的事务ID。
PageID：被修改的数据在磁盘上的位置。
PrevLSN：同一个事务产生的上一条日志记录的指针。
UNDO：取消本次操作的方法，按照此方法回滚。
REDO：重复本次操作的方法。
一个日志记录里，记录了UNDO和REDO两个日志，一个是原始的记录，一个是操作完成后的记录，最后由事务去决定，是要去REDO一次把它去完成，还是去UNDO一次恢复到原始状态，不管是要哪个，都可以把对应的值从日志中取出来，然后去重新执行一次，使事务达成它的一致性，满足它的原子性操作，要么全部完成，要么全部取消，而不会部分的被更新。
﻿