[大数据]数据库内核学习总结

数据库内核学习总结

mysql简介

MYSQL operates in a networked environment using a client/server architecture. A central program acts as a server and a various client programs connect to the server to make request.

MYSQL Server, or mysqld is the database server program.It manages access to the actual databases on disk or in the memory.

MYSQL Client are programs for communicating with the server to manipulate the information in the databases that the server manages. Example : mysql is the command line program that acts as a text-based front end for the server.

sql执行流程

如何设计表，能使得查得更快

小数据量

• 引擎选取
• 主键选取
• 字段数据类型选取
• 索引选取
• 表注释

大数据量

• 分库分表原则
• 负载均衡
• 数据分布原则
• 热点问题避免

查询优化

sql常见编写原则

• 尽可能的减少scan数据量
• count(1) 代替count(*)
• 多字段去重可以使用group by
• 尽可能使用等值比较查询，减少like

join机制

为了估计和比较时间复杂度，我们假设有两个表，表 A 和表 B；分别有 M 个 block(页)，m 个 row 以及 N 个 Block 和 n 个 row 的数据，并且假设 M < N 和 m < n。

NestedLoopJoin

暴力的双层for循环join

两层 for 循环：第一层 for 循环表 A 的每一个 row，然后嵌套内部另一层表 B 的 for 循环，循环体内做具体的 join 逻辑，如果满足条件，就输出一个 joint 的结果，代码示例如下图所示：

这种join机制在内存有限的情况下很容易就oom，如何优化内存使用呢？

要想办法避免对表 B 进行全表扫描。优化方法就是，如果表 B 在对应的 join 键上建立索引，那我们就能用 Index Scan 来取代全表扫描。这就是 NestedLoopIndexJoin。假设每次 Index Scan 的时间是常数
C，它的时间复杂度就是 M + m * C。

SortMergeJoin

对两个表分别根据 JOIN 键做排序，然后在 JOIN 时，对每个表维护一个指针，当两边指针的键值相同，就可以输出 JOIN 的 row。指针不断后移，直到一方终止为止

作组队聚合(group aggregation)的时候，有序的 tuple 就不需要额外建立 Hash 表，可以直接用 SortGroupByAgg 来实现。另外，如果系统在 JOIN 前发现一方或者两方都已经排序过，同样在 JOIN时就可以直接使用 MERGE JOIN。很多情况下，排序是一个一劳永逸的过程，一旦排序后，上层的语句可能都能获益。

HashJoin

在表 JOIN 的时候，哈希表依然作为排序的对手出现。这正是我们要介绍的最后一种实现 HashJoin，并且 HashJoin 再一次扮演了亮的角色。HashJoin 算法很直观，对相对较小的表表 A，根据 join condition来建立哈希表。然后对表 B，只要依次读入数据去和哈希表里的值去匹配，如果匹配，那就生成一个新的 tuple。代码如下图所示：

这种需要注意的就是需要将小表去拿来构建hash表，内存占用较少

shuffle join

shuffle join 是在分布式架构下产生的问题，分布式架构下，数据存储在不同的服务器上，在join的时候，需要将相同key的数据通过网络io发送到对应的机器上去，如何减少shuffle
传输的数据量也是优化的一个点，同时，尽可能的让两个表在设计的时候，按照同一种分布逻辑，相同key的数据尽量分布在同一台服务器上，就可以将shuffle join 转化为local join，大幅提升速度。

boardcast join

boardcast join 也是在分布式架构下衍生出来的问题，在上述情况下，如果是一个超大表join小表的情况下，如果进行shuffle join，就会使得大表要进行shuffle的数据量会比较大，优化的点就在于
将小表的全部的数据量广播到每一个分布式节点上去，将join变成local join，优化速度。

复杂查询编写原则

• 使用in时，子表数据量过大可以使用inner join来替代

• join时，尽可能使用主外键关联

• join时，子表尽可能只选取业务有意义的字段

索引优化

索引机制

hash

数据：

下面一句sql ：

SELECT * FROM titanic_survivor WHERE age = 10;

这个语句如何优化？

• 构建hash表，key存储age的值，value存储所有 age 为对应键值的 row 在存储系统中的相对位置 如下所示：

  

查询某一个age时，只需要根据hash表找到对应的存储位置，读取对应行号的数据即可，减少了数据的扫描量。

但是当把SQL语句改为如下时：

SELECT * FROM titanic_survivor WHERE age BETWEEN 1 AND 10;

如果用hash索引解决，只需要去hash表中寻址10即可

但是如果是下面这种语句

SELECT * FROM titanic_survivor WHERE age > 10;

或者age不是int类型，而是double类型,那么构建hash表的代价就非常大，寻址次数也会随着查询的复杂和数据量的增大而增加

如何优化呢？

可以将hash表先排序然后进行二分查找，就可以不必全表扫描了

假设需要查询年龄在 20 至 40 岁之间的幸存者，可以通过二分查找先定位到年龄 22，然后依次顺序读取之后 26，35 和 38 岁的对应行的数据，当搜索至 54
岁时停止即可。概括一下方法：对相关列进行排序，用二分查找来快速定位然后顺序查询。二分查找 1 至 100 万中的某个数需要 20 次查询(2^20 约等于 100 万)

B- tree B+ tree

在构建排序hash table的基础上还能优化吗？

B 树相当于把二分查找变成了 N 分查找，假设 N 为 100，那查找范围为(1, 100 万)就只需要 3 次分叉了(100^3 = 100 万)。B+树和 B 树的区别就在于 B 树在非叶节点也会存储数据而B+树仅在页节点存储数据。下图示例为 B+树。

这类索引就称之为 B 树索引。B 树和 B+树通过引入有很多分枝的树节点来提高定位速度以此来提高整体查询速度，算得上是空间换时间的方法。同样，在工程中还有很多可优化的细节。比如对于
B+树，只有页节点存储具体的数据信息，内节点和根节点仅仅是用于快速定位，因此衍生出了合并前缀(prefix compression)以及删除无用后缀(suffix truncation)
等的优化方法。再举个常见的优化示例，B+树的页节点原本用来存储对应列值的行在数据文件中的相对位置，所以读取完索引后还需要根据相对位置去数据文件中读取数据。但对于固定的查询语句，可以提前知道其他哪些列也会经常被查询，把这些列的数据直接存储在索引中，进一步用空间来省去读取数据文件的时间。

SELECT sex FROM titanic_survivor WHERE age > 10;

这种语句就可以把性别信息放在索引中

CREATE INDEX btree_idx_age ON titanic_survivor(age) USING BTREE INCLUDE (sex);

hash索引和B树对比：

从理论上讲，对于点查询，哈希索引应该更快，而且存储空间相对 B 树索引更少。但是通过工程中的优化，可以让 B 树索引在点查询中也毫不逊色

B树的缺陷：

B 树的实现，增加和删除数据会牵涉到节点的分离和合并，是比较复杂的(没有同学在面试过程中遇到要求实现 B 树这类的变态问题吧)。尤其是在高并发的环境下，对于节点的操作需要加锁， 会进一步导致速度变慢。

现在假设我们要对性别或者舱位做索引，但这些列本来基数(distinct value cardinality)就非常小，B 树带来的快速定位优势就没有意义了。

bitmap

因此就引入了 位图索引

位图索引专门针对基数很小的列来做索引

SELECT * FROM titanic_survivor WHERE sex = 'female' AND cabin = 'A'

对于性别，我们构建位图索引，然后对性别进行位图与操作，读取结果为1的数据即可。

位图索引的优势在于索引占用的空间很小：对于每个基数，每一行数据只要 1bit 的存储。所以当列的基数很大时，位图索引就失去意义了。 那么如何解决基数较大时候的查询场景呢？

bloom filter

对于 基数较大的场景时，可以采用 bloom filter索引来进行优化。

Bloom Filter（布隆过滤器）是用于判断某个元素是否在一个集合中的数据结构，优点是空间效率和时间效率都比较高，缺点是有一定的误判率。

布隆过滤器是由一个Bit数组和n个哈希函数构成。Bit数组初始全部为0，当插入一个元素时，n个Hash函数对元素进行计算, 得到n个slot，然后将Bit数组中n个slot的Bit置1。

当我们要判断一个元素是否在集合中时，还是通过相同的n个Hash函数计算Hash值，如果所有Hash值在布隆过滤器里对应的Bit不全为1，则该元素不存在。当对应Bit全1时, 则元素的存在与否, 无法确定. 这是因为布隆过滤器的位数有限,由该元素计算出的slot, 恰好全部和其他元素的slot冲突. 所以全1情形, 需要回源查找才能判断元素的存在性。 查询时, BloomFilter可快速判断某个列中是否存在某个值。如果BloomFilter判定该列中不存在指定的值，就不需要读取数据文件；如果是全1情形，此时需要读取数据块确认目标值是否存在。另外，Bloom Filter索引无法确定具体是哪一行数据具有该指定的值。

缓存

• 适当选取外部缓存数据库，减轻大量重复查询的压力

查询分析

查询(执行)计划分析

编译阶段

解析阶段

当用户通过SQL客户端输入sql后，数据库会将sql先用parser 解析为一颗抽象语法树，这颗树包含了SQL的语法解析，词法检测，来判断用户输入的sql在语法和词法上有没有错误

解析后：

注意：sql的编写和人的思维一样，是一个从上到下的过程，但是数据库执行查询的时候，是一个自下而上的过程。

上述的图中，根节点是一个SELECT 包含 （Projection-expression 和 GroupBy-expression）同时还具有一个子查询的子节点，它的select 包含 Projection-expression 和 Where-clause

解析器解析完成后：

绑定阶段

会将数据库的元数据信息和语法树绑定起来，绑定器的作用就是将语法树通过和数据库的元数据(metadata)结合，为它附上语义(semantic)。比如语句里有SELECT…FROM student，绑定器会去查询元数据确认 student表是否存在;如果存在，是否有 class 和 id 两个属性;对于属性的后续操作是否符合规则-比如，对于 SUBSTR()这个方法，输入表达式必须是字符串类型等的一系列检查。检查过程是自底向上对整棵语法树的节点依次进行，检查的同时也把相关表的元数据，属性的元数据附在语法树上，最后生成含有语义的语法树(bound AST)。绑定器在绑定的过程中就能察觉到上述
SQL 的问题而返回编译错误。一旦绑定完成，这个 SQL 语句就算是通过编译过程了

优化器阶段

通过parser解析和bonder绑定后，用户输入的SQL会形成绑定元数据的一颗抽象的语法树，此时，优化器需要介入，将这个语法树经过优化后，会形成一个物理的执行计划，交给执行器进行执行。优化器的最终目的就是按照人为的目的去优化用户输入的sql，来达到相应的目的（加速查询或者加大并发等）。

为什么需要优化器参与呢，而不是直接将绑定了元数据的语法树直接给到执行器执行呢？

sql是一种声明式的语言，他只是告诉了数据库，我希望返回什么格式的数据，但并没有告诉系统，要怎么去一步一步执行算子来得到最终结果。例如排序，快排和冒泡排序都能够是先排序，但是时间复杂度和空间复杂度是不一样的，优化器就是在一些可优化的维度上，尽可能的让生产的执行计划变得更为优秀。

query rewrite

SELECT

class.name AS class_name,

student.name AS student_name, 

student.id AS student_id

FROM

class, student

WHERE

class.id = student.class_id AND   

student.name = 'ZhangSan';

上述的一个sql,返回的是这个学校所有叫 ZhangSan 的学生的姓名，学号，以及班级。转化为语法树之后如下所示：

这里只用到了最基本的 projection，equality join，和 filtering operator，当这个语法树转化为物理算子的执行计划的时候，就会有优化的地方。

物理执行计划是自下而上的，首先就是scan数据，按照执行计划需要去scan student表和class表，然后对其进行join ，join完成之后，对数据进行filter操作，选取3个column作为输出。这个时候就会发现，如果从scan的时候，就能够把join的条件下推，或者是filter条件也下推，那么不仅scan的数据量会变小，join的数据量也会变小，时间和空间消耗都能够得到优化。于是 query rewrite之后就形成了如下：

通过上述两个重写规则，我们使得自下而上读取的数据量减小到最少，继而减少后续处理的数据量，以此来优化执行时间。并且，这些重写规则，属于有百利而无一弊，只要规则允许，就应该进行重写。

常见优化器

RBO

基于规则的优化器，即人为的提前根据优化规则，提前构建好对应的优化规则，sql经过解析后，再根据这些规则选取合适的rewrite，最终形成一颗物理执行计划。

CBO

基于cost的优化器，即基于代价的优化器。这个优化器会通过定期抽样统计各个表中数据的存储情况，如大小，行数，数据类型等等资源信息，生成一个复杂的计算代价的cost模型，当用户输入SQL后，经过cost模型计算代价后，会选取一个代价最优的物理执行计划进行执行，来达到优化的效果。