计算机——数据库

Buffer Pool

MySql的内存区有个Buffer Pool(128MB)里面以数组的形式存储从磁盘中读取的数据
我们每次执行Sql语句就会从先检查Buffer Pool中有没有，如果没有再从磁盘中读取，这样的话就可以减少与磁盘IO的次数。同样的，更新也是一样的道理，我们也是读取到内存中，然后在内存中进行修改，修改完了之后再在某个时间段进行持久化。
而硬盘中读取之后我们就会从Free链表的控制块中选取一个地址，进行存储，存储完了之后删除该地址块。
同样的如果我们更新了一个地址块的内容（因为更新的内容要重新的写入我们的硬盘嘛），所以我们会把所有更新了内容的地址块存入Flush链表，这样的话，后台线程就只需要遍历该Flush链表就可以把所有更新了的数据同步到硬盘中去了。
同时因为我们这个BufferPool的容量是有限的嘛，所以我们还有一个LRU链表，这个链表采用头插法，把所有最新访问过的块通过头插法的方式插入头部，然后如果满了话，就删除尾部节点。但是这样设置LRU链表的话是有问题的，因为你一旦你使用Select * from table这样的语句的话，那岂不是你所有的数据都要清空换血？但是我们肯定不能这么做呀，因为我们这个Select * 的数据可能只在非常短的时间里使用一下，所以LRU链表有了个分区机制，也就是冷热数据分区，对尾部3/8的数据化为冷数据区，所有的替换都是插入冷数据的头部，而冷数据如何转移到热数据呢？那就是冷数据的某个控制块被两次访问，而两次访问的时间间隔大于1s（这么做的目的就是避免短时间突然高频次的而错把某个数据当作热数据，但是我们想要的热数据其实是长时间经常被访问的）

事务（Transaction）

我们先来看看我们“Update”的时候发生了什么。

1. 开启事务
2. 执行Update语句多次（期间生成bin log， redo log， undo log）
3. 事务提交

这个时候每当我们去修改Buffer Pool里面的数据时，我们就会生成redolog对象，记住这个对象和修改的数据都是存在内存里的，所以我们需要持久化，而当我们事务提交的时候，我们就会去持久化我们的redolog对象（此时Buffer Pool里面的页数据是还没有被持久化的！），页数据等待后台线程去持久化的。
为什么我们要去持久化redolog对象们而不是直接持久化页数据，这是因为页数据存在磁盘里，这是随机读写的！（磁头来回移动非常耗时，消耗性能）而redolog所存储的位置是一开始就直接创建好的一整块区域（两块区域，一块48MB），你的磁头是顺序读写，按照顺序写入各事务执行了的SQL语句即可。同时redolog其实不仅记录了执行了SQL语句，同时还记录了数据的位置！这就是他和bin log的不同，bin log只记录SQL语句，所以他可以做主从存储，因为谁知道其他数据库里的数据是不是改变了位置呢。
最后再来说说undo log，其实我们更新数据操作（增删改）的时候，那么不仅生成了bin log，redolog还有就是undolog，这是用来rollback，其实他的生成非常简单，如果你是Select那么完全无所谓，如果是insert，那么你insert肯定有个ID，那么undolog里面就是delete 该ID，如果你是delete则相反，update的话undolog则会记录原值。

亿万数据级别数据库优化

最后再写一点关于数据库优化的思路：

1. 索引（首先第一步肯定是想到对常用的查询的条件建立索引，提高查询的效率）
2. 配置（比如说提高Buffer Pool的容量，这样就就不会频繁的去操作LRU链表和Flush链表，同时可以选择关闭双写，当然这样会不安全啦，不过你买个原子写的硬盘不就好了吗？）
3. 从业务层面思考如何减少查询量，更精确的查询
4. 使用非关系型数据库，比如Redis（但是这又要考虑你的两个数据库的数据一致性了）
5. 分库分表

聚集索引和非聚集索引分别是什么

聚集索引确保表中数据的顺序与索引的顺序完全一致，也就是所谓的物理顺序与索引顺序一致，一个表中只有能存在一个聚集索引。
非聚集索引则恰好相反，他在表中的数据和索引的顺序并不一致，同时表中也可以同时存在多个非聚集索引。

他们的优缺点也刚好相反：
聚集索引的优点就是查询效率高，因为索引的顺序与表数据严格一致，所以一旦你查询到了第一个索引，那么后面的数据都是连续的。但是缺点也同样明显，就是增删改比较慢，因为你的数据顺序和索引顺序要保持强一致，这就意味着你插入一个新的数据，那么就会对数据重新进行一次排序。

而非聚集索引则恰好相反，他因为不具备强一致性，所以你查到哪个索引就是哪个数据的位置，这个索引的前后与数据的前后位置信息并不对应。同样的，优势就是你可以直接增删改，不必重新排序保障强一致性。

B+树，主键索引
B+树其实是个冗余的结构模型，他的键值和叶子节点存的数据都重复了一份。
同时他的底层是个双向的指针。

页的概念：首先Innodb中，16kb的页是存储的基本单位，这就意味着每次最少要去硬盘中读取16KB的数据，这么做的主要目的就是减少与磁盘IO的次数。而我们在往页中插入数据的时候就会进行排序了。接下来我们来看看页的数据结构，页的用户数据区域其实就是一个排序好的链表结构，这样就很方便插入与删除，同时我们知道链表的查询是需要遍历的，所以这时候就出现了页目录，我们把每六条数据分为一块，设置为一章在目录，目录中存的就是该章初始的主键值和该主键值对应的地址值。这么做就可以通过查询目录快速的定位到所要查找的范围，当然，因为里面只有主键值，如果你是以主键以外的条件查询的话，你还是按照遍历链表来查询的。

但是当你满一页了之后，那么你就会创建新的一页了，如果你的主键值是任意的，那么你就会涉及到数据的迁移了，因为你要确保页内部是顺序增加的，而页与页之间也是顺序增加的。所以用自增的主键性能是最好的，因为你就不用迁移数据了，新的一页直接开始

但是这又出现了一个问题，你数据量不断增大，你页链表也越来越长，你就会发现你还是要遍历这个页链表来查询呀，那肯定不行，那我们采用同样的思路，构建出页的页目录不就好了吗。页的页目录中值存储每页的初始主键值和地址值，这种页目录，我们就称为索引页。

这个时候你就会发现你的数据和索引是存在一起的，所以我们称为聚簇索引。同时由于底部是双向指针，所以你的范围查找就变得很简单，比如a < 7或者a > 7，你通过索引直接找到a=7，然后通过链表的指针双向后向找到大于7以及前向找到小于7的即可。但是！如果你不是使用主键进行搜索的话，那么你的效率是很低的，因为你的索引是根据主键建立起来的啊，所以如果是使用b=8这样的条件来查询的话，你还是使用的是全表遍历查询。

联合索引

我们现在想要根据三个字段（b，c，d）建立联合索引，那么其实也不难，其实只要他能排序不就行了？我们根据首先比较b（b如果相同则继续比较），然后比较c（c如果相同则继续比较），最后比较d的顺序排出顺序即可，这也就是我们为什么使用最左匹配原则的原因，因为如果你不是缺少了最左边的也就是最高优先级比较的，那你就无从比较大小了，而如果你是缺少了右边的，那么你左边最高优先级的就已经比较出来了。请看下面

但是！我们注意到这里我们又把所有的数据存了一遍呀，记住，我们主键索引已经存了一遍所有的数据了，你这边又存了一遍，那么现在我想要更新一下e字段的数值，你在你“bcd联合索引”建立的树上要重新的修改e字段的信息，你还要去主键索引建立的B+树上去修改e字段的值，那你的效率简直不要太低了。而且你存那么多相同的数据干什么呢？所以我们只在这棵树上存索引值就够了呀（数据只存一遍），那么就会出现下面这样的结构（我们只存索引）：

这样你就会发现，那我们找不到我们想要的所有数据了呀，那存所有的数据效率太低，只存索引又不行，而数据都存在我们主键索引建立的那颗树上，那么其实直接存入主键不就好了嘛，通过主键去拿值呀。

我们获得了主键值就可以根据主键值去对应的主键索引建立的B+树中找到我们所需要的数据。这其实就是所谓的**“回表”**，我们通过联合索引查到主键值，然后再通过主键值查找到对应的数据。

说说表和表之间是怎么连接的：
这个可以分为内连接，外连接
内连接就是返回两表所有满足连接条件的数据。
外连接则是返回两表满足连接条件的数据以及不满足条件的数据（左连接和右连接）
当然还有一堆多，多对多（需要中间表），自连接（需要有个树型的层级结构，比如说有上下级的部门）

索引

索引是一个单独的存储在磁盘上的数据结构，包含对表中所有记录的指针。
优点就是提高查询效率
缺点就是索引作为单独的一个数据结构存在，需要额外的空间来存储，同时，数据的增删改同时也需要同步更新索引，这会影响到性能。

组合索引：

哈希索引：
其实底层就是个哈希表结构。
我们根据索引列的每一行值计算得出每行他们对应的HashCode，然后建立一个表，键为哈希值，值为地址值。这样的话如果我们要进行等值搜索的时候就非常快了，比如说你要搜name='SunLL’那么很简单根据SunLL获得他的HashCode，然后直接从表中取出对应那行记录的地址值即可。但是！一旦涉及到范围查询那就不一样了，因为Hash表的存储是无序的，你必须全表扫描才可以，这时B+Tree索引的优势就体现出来了，他是从左到右，从大到小实现的有序排列。而在我们实际使用中范围查询还是很有必要的。同时如果Hash碰撞大量发生，那么他的效率就会变得很差，所以其实他并不稳定。

模糊查询语句中如何使用索引？

在MySQL中模糊查询 mobile like ‘%8765’，这种情况是不能使用 mobile 上的索引的，那么如果需要根据手机号码后四位进行模糊查询，可以用一下方法进行改造。

我们可以加入冗余列（MySQL5.7之后加入了虚拟列，使用虚拟列更合适，思路相同），比如 mobile_reverse，内部存储为 mobile 的倒叙文本，如 mobile为17312345678，那么 mobile_reverse 存储 87654321371，为 mobile_reverse 列建立索引，查询中使用语句 mobile_reverse like reverse(’%5678’) 即可。

reverse 是 MySQL 中的反转函数，这条语句相当于 mobile_reverse like ‘8765%’ ，这种语句是可以使用索引的。

主键索引一定是聚簇索引，而聚簇索引不一定是主键索引，由于聚簇索引和主键索引都有唯一性，在一个表中都只能存在一个，所以很容易搞混。

模糊查询如果符合最左匹配原则的话，也可以使用索引。

MVCC
Multi Version Concurrent Control（多版本并行控制）
其实说人话就是，任何对数据修改的提交都不会覆盖之前的数据，而是生成一个新的版本与老版本共存，从而实现读的不加锁来提升性能。这个其实可以实现的隔离级别就是Read Committed（读已提交）和Repeatable Read（可重复读）。我们来看看具体的实现流程吧

1. A线程将X修改为1，且提交了
2. B线程读到X为1，然后执行操作，但是未提交
3. C线程读到X为1，然后将X修改为2，且提交上去了
4. B线程又读了一遍X，那么此时X的值为：？

那么如果我们是实现的是读已提交，那么我们就会读取最近一个Commit版本的值，那么X的值就是2了，而如果我们实现的可重复读，那么读到的值就会是1。
读已提交：读取的是最近一次Commit的版本
可重复读：读取的是事务开始前最近一次Commit的版本

BufferPool

Innodb里面的缓存，其实就是你执行sql语句的时候，从磁盘里面读取后复制放到BufferPool里面，这样下次读取的时候就直接从BufferPool中拿取就好了（因为BufferPool是在内存里的呀而不是在磁盘里的，这样不需要磁盘IO了，效率就更高）BufferPool默认128M。
那么上面解答了读取的问题，就是读取的话先检查BufferPool是否存在，如果不存在再去磁盘里面读取，那么如果是做update的操作又是如何呢？其实也是先复制到BufferPool里面，然后更新BufferPool里面的数据，当然我还需要写回去的。那么Update操作是如何做的呢？

1. 更新BufferPool里面的页数据
2. 生成一个redolog对象（你执行各种Update语句就会生成多个的redolog对象，而这些redolog对象是存在log Buffer中的）
3. 将redolog对象持久化到内存里（事务提交的时候发生）
4. 后台线程会将更新后的页数据持久化

那么为什么我们不直接在Commit的时候就提交我们更新的页数据呢？因为这样的效率太慢了！首先你要知道，我们页数据在磁盘上面的位置是不固定的，磁盘是采取**随机读取（随机IO）的，所以你磁盘的磁头可能就是到处飞，这样的效率是不高的。而redolog则不同，他在磁盘中有着固定的位置，也就是你的两个Log File（每个48MB），所以你的磁盘指针每次都是顺序读写（顺序IO）**的，这样效率就够了。同时你也避免了中途宕机导致的数据丢失，因为你的redolog记录着你所作的所有修改操作，下次重启的时候恢复就好了。当然如果你的redolog都满了的话，就要清空一个，并且在清空前持久化所有的页数据。
所以一个优化的方法就是调大log File的大小，这样就减小了清空log的几率。

另外，BufferPool区域其实是由三种链表来控制的，Free链表，Flush链表和LRU链表。Free链表记录着BufferPool中所有空闲地址块的地址，基本上就是你取出基节点的第一个节点给你复制到BufferPool中的页。而Flush链表则记录着所有被update过的页的地址块，一旦发生修改就添加到这个链表上。

说完redolog我们来看看bin log，bin log是拿来做主从备份的，他是记录着主数据库做的一堆SQL修改操作然后给从数据库，这样从数据库就可以还原出和主数据库一样的备份了。这里我们需要记住redo log是InndoDB特有的一种log而bin log则是所有MySQL Server都有的，所以不管你底层的实现引擎是什么，bin log都可以生效。那么问题来了，既然bin log在InnoDB上也可以用，那么我们为什么要重新创造出redo log呢？其实说白了bin log就是更通用，而redo log则是针对InnoDB做了更多的优化，bin log就是真的原封不动的记录了你所有的修改sql语句而redo log则是更详细地记录了哪页哪个位置做了什么样地修改，这样就更加容易且快速地恢复。

优化思路：增大BufferPool，这样的话其实就是增大了你的缓存嘛，那你缓存越大，能命中的概率就越大了嘛，从缓存中读取数据的效率是很高的。

双写缓冲区

我们知道我们数据库的一页是16KB而操作系统的一页则是4KB，那么其实我们是要分多次写的，那如果某次我们往操作系统里面写了8KB的时候，系统挂了（参考转账的那个例子，只扣钱不加钱），那么你其实还不是GG了？所以呢 为了确保这个持久性和一致性，双写缓冲区就出来了，每次你都直接往双写缓冲区里面写，如果中途挂了，没事呀！你的磁盘里的数据还是好好的，你之后使用redo log慢慢恢复继续写就是了。如果你成功写入了双写缓冲区但是双写缓冲区往磁盘写的途中挂了，那也没事呀！双写缓冲区是在磁盘里的，你恢复了之后，双写缓冲区继续往里写就是了。