数据库优化（学习笔记）

前言：

数据库优化确实是比较重要的板块，也是面试数据开发岗位几乎必考的面试题。

本菜鸡这学期学了一门数据库相关课《Data Storing and Retrieving》，字面意思理解也就是说最重要的两大板块：一是数据存储，二是数据检索。这两个板块的优化，我认为其实就是数据库的优化。

其实这门课，个人觉得往深了读就非常的CS，像本人这种本科非CS的，基本上只能Dive到数据结构这一层，再往下的那些非常具体的数据存储读取方式，就无能为力了。

但我会试着用比较浅显易懂的描述，让一些不是CS背景的同学也能够看懂，最好能指出我的错误，大家一起成长嘛hh。

所以以下内容，都是

本人理解，非绝对正确!!!

本人理解，非绝对正确!!!

本人理解，非绝对正确!!!

欢迎指正交流，拒绝阴阳怪气。

首先上来的几个lecture，我认为其实算是数据库的语言基础，例如关系代数、sql语句以及约束等等，其实最终目的就是让你能够运用SQL语言来写一些操作，这个我认为是一个基本工具，没什么好说的，多用就熟了。唯一要说的是在学SQL之前，学习一下ER图和关系代数，确实会对SQL的逻辑有更加清晰的认识，对一些复杂的SQL语句编写很有帮助。

下图是一张较为完整的数据库操作的层次图，这些SQL语句的执行和操作，体现在较为上层，即较为应用层的层次，对应图中是前面两个：

• Query optimization and execution

• relational operator

接下来就是数据库的分析（有分析就有改进/优化嘛）了。下面我们重现“通过一个操作（SQL语句）读取DB中数据”的完整过程。

• Files and Access Methods

• Disk space management(数据在外部硬盘中的存储管理)

• Buffer management(这里的buffer，缓存，我理解就是硬盘存储加载到内存中的管理机制)

1. 首先我们要知道，我们的数据是怎么存储的：（以本地存储为例子，分布式最后面再展开总结，这里的知识在计算机原理里面更详细、正确）

在我的理解中，计算机的存储其实大致分为两种，一种是读写比较快但是空间有限的内存（RAM），一种是读写比较慢但是空间比较大的磁盘或者硬盘（Disks）。

至于为啥他们的读写有快慢之分，主要原因是物理材料和工作原理的不同。而且内存失电会丢失存储，硬盘不会。

所以我们平时的数据为了存储安全，肯定是存放在硬盘中的，那么照硬盘的规矩，就按地址存储；等到需要CPU对某些数据进行操作时，才加载到内存中。

这里有比较重要的一个点就是，内存去硬盘里读取数据，并不是只读那么一条数据，比如我现在要读取一个数据，地址存放在0x30-0x3F，那内存不会只读这么一片，他可能会把0x30-0x3F上上下下几十K的存储都搬进内存。

2. File organization，数据在外部硬盘中的存储：

关系型数据库，常见地会被分割为多个pages，每个page的存储大小为4KB-8KB。

数据在外部存储单元中，主要有三种存储方式：

• Heap(random order) file：无序，底层结构类似于链表；

那比如说有100,000个用户，如果我们不借助于任何结构或者算法的帮助，像个憨憨，我们想筛选出年龄大于30岁的用户，我们的数据库系统会需要怎么做呢。

我们需要先从硬盘里面，拿出5页数据（比如内存最多能加载5页），逐条查看这5页数据中的每一行，看看年龄是否大于30。最终，我们扫描了整个数据库100,000条数据，才得到了结果。

• Sorted file：数据会以某个key为依据，对数据库进行aggregate和排序；

比如这100,000个用户，以ID为依据，从小到大排好存储。

• Indexes(tree, hashing)：把外部存储中的数据，使用一些数据结构进行整理、存储，主流为b树和hashing。
  这里注意！！后面的索引文件中也有indexes，他们都是一种结构，数据文件和索引文件中各自都可能有index，他们不是同一个。

经一位大神好友实力普及，在外部存储中的存储方式是由文件的格式决定的，这个我以后深入了解再来补坑。

这里先对indexes的分类，两种主要索引方式（b树索引和hash索引）进行讲解好了。

• B树索引（B+ Tree Index）：（应用最广）

其实看完图，基本上大家就对这个结构有个大体的认知，逻辑不用多讲，就是像树一样分支。父节点左边指向value比自己小的子节点，右边指向从自己开始的value大的子节点。

唯一的细节是：我们在初建立这个b树结构的时候，会在最末端的各个子节点页里面，留下一些空着的空间。这么做的目的是考虑到将来你可能需要在此处加入新的值。如果不留这个空间，后续的数据都得向后推进，这种牵一发动全身的做法不可取。

由于存在这样一个page里占用空间的最低比例，还衍生出几个小问题：

• 如果删掉数据导致page中的占用小于这个最低比例怎么办？答案是会跟后面借。
• 如果小于最低比例，跟后面借导致后面的也小于最低比例怎么办？答案是不接着借了，直接把两个page合并（因为这个最低比例通常都有50%，所以合并两页占用空间少的page不会导致溢出）。
• 如果空着的空间也占满了怎么办？答案是往后推。

但无论怎么做，总体上结构始终需要维持b树的模型规则，比如向后面借或者往后面推的时候，都需要留意父节点的变化。

Tree Index最底层的“叶子”结点里面，存放着Data entries，包含着数据文件（选型1）或数据文件存放位置（选型2、3）的信息。

Data Entries有三种选型：

• Alternative1（选型1）：<KEY k, Data records>

选型1比较特殊，他直接把数据的整个元组tuple，存放在这个索引文件的叶结点上。由于索引文件也是不可能存放在内存的，而是存放在硬盘中的。

所以这个选型1就相当于将整个数据源按一种结构整理了一次，在叶结点就能找到数据，没必要再根据record去找record。

因此选型1就是一种外部存储的数据结构。

• Alternative2（选型2）：<KEY k, Record ID of data>

Record id，我个人理解，是外部存储文件的索引search key。

比如外部存储中的数据文件是sorted file，按属性age分类。那索引文件的叶结点里面会存储age值，以供去外部存储中找数据。

于是在这样一个系统里，如果你给“薪水”属性建立了二次索引。当你需要找薪水等于10000的人，你会现在索引文件中找到所有的叶结点like：<10000, （age value）>。

然后根据所有的age value，以age为依据，就可以在外部存储中高效检索数据。

• Alternative3（选型3）：<KEY k, List if record id of data>

原理和选型2接近，只不过索引文件叶结点里面存放的是列表list，以选型2中的例子来说，就是所有的age value合成一个列表，选型3：<10000，{age1,age2,age3} >

• hashing index：

这玩意比较复杂一点点，这里贴个很好的youtube上的讲解：https://youtu.be/ezJqCvtdpsA

我在钻这个hash index的时候搜到的，一个湾湾教授，如果你像我一样暂时不关系java层的实现，可以只看前面十分钟左右的讲解。

前面我们解释了外部存储的结构类型，目前用的最多的，是Tree Index，其中B+树应用最多。

当数据库DB被创建时，数据库系统会在外部存储创建一个以主键（Primary key）作为依据的B+树，当我们根据主键来查找数据时，效率会大大提高。

那要是我们不是通过主键查找呢？实际上，现实应用中我们大多数情况下都不是通过主键去查询数据的。

比如说我们在一个记录用户信息的数据库中，通过年龄age去查询，要怎么办呢？

两种方法：1. 扫描全表，和heapfile那样；

                  2. 建立一个二次索引，如：建议树状结构，把age作为分支依据，在叶子结点里面存储<age，id>，如此一来我们通过id在主索引（创建DB的时候以主键搭建的索引）中找到数据。

二次索引/辅助索引（secondary index）：

图片来源：https://ieda.ust.hk/dfaculty/ajay/courses/ielm230/lecs/lec6_indexes.pdf

但二次索引也不总是高效的，这就是为什么我们说：

“不要给没必要建索引的非主键列建索引”

你想想，数据库建成的时候，已经根据主键建好了一个树索引。假设所有的100,000个数据已经按id排列好了。

现在你想要查询age>30的人，你就得现在二次索引中找到age>30的数据对应的ID，再根据ID去主索引中找数据。

而且有可能出现像<31,10000>,<32,50000>,<33,10001>这样的查询顺序，导致在id=10000附近的page被反复加载到内存里，消耗I/O。

这显然不是一个高效的查找数据的方式，而且从外部存储读取数据到内存中，是非常消耗计算机资源的。那么我们的目的，或者说如果有更好的方法，是为了什么呢？

我们希望有这么一种方法，可以尽量减少从硬盘中读取数据的次数。

在缓存机制工作的背景下，即我们每一次从外部存储加载数据进内存，并在内存中找到自己想要的数据后，内存中的数据不被释放。下一次寻找数据时先从内存的缓存中检视是否存在准备找的数据。

那么当这个二次索引的查询键和主键接近线性关系，比如我们想按salary查询数据，而且salary=2*id的时候。此时我们通过salary查询的时候，就能减少许多的I/O消耗。

我们就称这样的索引是一个聚簇索引（clustered index，网上有很多版本的翻译，例如聚合索引、集群索引等等，一位清华计算机大佬说，准确的应该是聚簇索引），否则就是一个非聚簇索引。下面有图解

这里注意，聚簇索引其实更像是一个目的，或者说是一个结论。它不是一种很具体的，拟定好的算法或者结构。

严谨逻辑上，你应该说，“我们运用了b树结构的二次索引，从而实现了聚簇索引”，而不是说，“我们运用了聚簇索引”。

在学习的时候，我在Google上搜索clustered index，大部分人对他的定义是：

“If order of data records is the same as, or ‘close to’, order of data entries, then called clustered index”

我认为这是一个非常模糊的定义，从上述内存I/O的角度理解，我觉得更加严谨且符合逻辑。

Data entries中，选项1<key，data records>是绝对的聚簇索引（clustered index），因为我们说过，聚簇的准确含义应该是内存读取的I/O是否高效。从这里来看，只要你通过KEY k去查询（检索）文件，对于k>30这种情况，你可以仅仅扫描到你想要的所有数据，而不需要浪费I/O。

同时，主索引（外部存储中，DB创建的时候，依据主键创建的index）正是一个data entries为选项1的B+树结构，所以主索引就是聚簇索引。

而二次索引在绝大多数情况下都是非聚簇索引，只有在上文中提到的存在缓存机制以及查询键和主键顺序相近的时候，才可能是聚簇索引。

对于常用的B+树索引结构来说，实现聚簇索引（clustered index），就是一种检索数据的优化方式了。

到此，

我们就解释了，我们是如何通过索引文件，找到索引文件叶结点中的data entry，再通过data entry里的信息找到外部存储中的数据，来实现数据库的高效检索的。

最后讲一下我所能理解的分布式存储系统吧。（极其浅的理解，分布式系统确实是个很大的天坑啊，寒假计划学习一下hadoop和spark再来补坑）

像目前主流的分布式管理平台，Hadoop和spark，都是从MapReduce模型发展来的。

分布式文件系统：（下图为Google File system，GFS）

在GFS出现之前，主流的分布式文件系统，为了达到高可靠性和高容错性，都力求硬件的高性能。

而GFS提出之后，则是想利用软件，或者说文件系统的合理设计，利用成千上万的PC机，搭建一个具有高可靠性的海量数据文件系统。换句话说，就是拿软件条件换硬件条件。

1. Client 把要读去的文件名和 offset，根据配置的 chunk 大小（默认64MB），计算出文件的 chunk 索引，然后加文件名和索引一起发送到 master；

2. master 会返回对应 chunk 副本位置信息（坑标记！既然分布式文件中又chunk文件的多个备份，master是如何决定让client去拿哪一份的），client 以文件名+chunk索引作为 key 缓存此数据；（https://www.jianshu.com/p/9bd469091cc1）

3. 之后 client 会直接和包含此 chunk 的 chunkserver 联系获得文件数据；为了充分利用通信带宽，Client会和尽量多的chunk server进行联系并从中读取数据，而之所以能和尽量多的chunk server拿数据，应该就是GFS主服务器所想要和控制的结果。

MapReduce

MapReduce是运行在分布式文件系统上的一种程序模型，其实我理解就是希望能利用到chunk server这些具有计算能力的结点中的计算资源，顾名思义分为Map和Reduce两步（中间会有一步shuffling）。

• Map

各结点服务器，通过一个用户定义的Map function对输入数据进行处理，然后输出一个键值对（key-value pair）的集合。

• Shuffling（Group by key）

对所有的键值对（key-value pairs）进行归类，然后输出一个key-(list of values)到Reduce process。

shuffling产生一个重要的问题：负载均衡

如果有十万条数据需要对性别groupby，如果只有100条是女性，其他为男性，则groupby操作只会把数据分到两个结点进行计算，男性数据的计算因木桶效应会严重拖慢最终的速度。

负载均衡解决方法：可以增加一列，对男性群体进一步打散。增加一列A，在该列给男性的数据分配0-9随机数。在计算聚合结果的时候，先对（性别，A）进行groupby，如此做，在这一步的时候可以将数据分配到10个结点进行并行运算，防止出现较严重的计算效率短板。最终再对性别特征进行聚合。

• Reduce

通过一个用户定义的reduce function对从shuffling进来的key-(list of values)进行处理。如果出现上述的负载均衡问题，则不均衡结点的数据计算会消耗更多的时间。

这种MapReduce的模型可以处理SQL的几乎所有基本操作。

比如求集合R和集合S的交集（Intersection）

Map Process中，输入（key k，tuple t），各结点就对输入数据进行处理，输出统计结果（tuple t，1）

Shuffle Process中，对各结点的输出数据进行整合，比如tuple t如果出现两次，则会输出整合结果（tuple t，{1,1} ）

Reduce Process中，对于shuffle输出的整合结果进行计算，经由计算 sum{1,1}=2，可以知道tuple t在R和S里面都出现过，所以这个tuple t就是想要的计算结果，所以最终Redece Process输出所有的（tuple t，null）。

以下部分内容转自——MapReduce过程详解与性能优化：MapReduce过程详解及其性能优化 - 老董 - 博客园

• MapReduce的过程详解：

过程：MapPartitionkvbuffer写数据到磁盘CopyMergeSortReduce

计算模型比较简单和局限，很多实际问题不能够简化成map和reduce这两个过程