数据库技术支持文档
说明
对平时工作学习遇到的数据库相关知识和技巧记录，会对一些优秀知识讲解文章的摘录，包括PostgreSQL、MySQL、Oracle等
| 版本 | 说明 | 日期 | 作者 |
|---|---|---|---|
| 1.0 | 初稿 | 2021-05-28 | pitt1997 |
MySQL
MySQL 数据怎么存储？MySQL 中的数据在磁盘上，它到底是如何进行存储的？长什么样？
扫盲：存储引擎是作用在表上的。
主要命令
查询当前数据库支持的存储引擎
mysql> show engines;
查询当前默认的存储引擎
mysql> show variables like '%storage_engine%';
查询表的相关信息（先使用具体的数据库）
use '数据库名';
show table status like '表名';
查看是否开启了binlog
mysql> show variables like 'log_%';
查看指定binlog文件的内容
mysql> show binlog events in 'mysql-bin.000001';
只查看第一个binlog文件的内容
mysql> show binlog events;
储存引擎
MySQL 中的数据用各种不同的技术存储在文件（或者内存）中，这些不同的技术以及配套的相关功能在MySQL 中被称作存储引擎。不同的存储引擎，我们的数据存储的格式也会不一样。
MySQL 中常用的存储引擎有两种：MyISAM 和 InnoDB。并且存储引擎是作用在表上的!
MySQL 5.5之前，MyISAM 是默认的存储引擎。MySQL 5.5开始，InnoDB 是默认的存储引擎。
主要区别
| MyISAM | InnoDB | |
|---|---|---|
| 事务 | 不支持 | 支持 |
| 表/行锁 | 只有表锁 | 还引入了行锁 |
| 外键 | 不支持 | 支持 |
| 全文索引 | 支持 | 版本5.6 开始支持 |
| 读写速度 | 更快 | 更慢 |
相关命令
查询当前数据库支持的存储引擎
mysql> show engines;
查询当前默认的存储引擎
mysql> show variables like '%storage_engine%';
查询表的相关信息（先使用具体的数据库）
use '数据库名';
show table status like '表名';
查询说明详细资料参考链接：链接
| 字段 | 字段解析 |
|---|---|
| name | 表名 |
| Engine | 存储引擎 |
| Version | 版本 |
| Row_format | 行格式 |
| … | … |
MyISAM
每个 MyISAM 表都以3个文件存储在磁盘上。这些文件的名称以表名开头，以扩展名指示文件类型。
.frm 文件（frame）存储表结构；
.MYD 文件（MY Data）存储表数据；
.MYI 文件（MY Index）存储表索引。
MySQL 里的数据默认是存放在安装目录下的 data 文件夹中，也可以自己修改。
MyISAM中的B+tree
.MYI 文件组织索引的方式就是 B+tree。叶子节点的 value 处存放的就是索引所在行的磁盘文件地址。
底层查找过程
首先会判断查找条件 where 中的字段是否是索引字段，如果是就会先拿着这字段去 .MYI 文件里通过 B+tree 快速定位，从根节点开始定位查找；
找到后再把这个索引关键字（就是我们的条件）存放的磁盘文件地址拿到 .MYD 文件里面找，从而定位到索引所在行的记录。
注意：表逻辑上相邻的记录行数据在磁盘上并不一定是物理相邻的。
InnoDB
一张 InnoDB 表底层会对应2个文件在文件夹中进行数据存储。
.frm 文件（frame）存储表结构；
.ibd 文件（InnoDB Data）存储表索引+数据。
下面我创建了以 InnoDB 作为存储引擎的一张表 t_user_innodb。
很显然，InnoDB 把索引和数据都放在一个文件里存着了。毫无疑问，InnoDB 表里面的数据也是用 B+tree 数据结构组织起来的。
下面我们来看看它具体是怎么存储的。
.ibd 存储数据的特点就是 B+tree 的叶子节点上包括了我们要的索引和该索引所在行的其它列数据。
底层查找过程：
首先会判断查找条件 where 中的字段是否是索引字段，如果是就会先拿着这字段去 .ibd 文件里通过 B+tree 快速定位，从根节点开始定位查找；
找到后直接把这个索引关键字及其记录所在行的其它列数据返回。
聚集（聚簇）索引
聚集索引：叶子节点包含了完整的数据记录。
简单来说就是索引和它所在行的其它列数据全部都在一起了。
很显然，MyISAM 没有聚集索引，InnoDB 有，而且 InnoDB 的主键索引就是天然的聚集索引。
有聚集索引当然就有非聚集索引（稀疏索引）。对于 MyISAM 来说，它的索引就是非聚集索引。因为它的索引和数据是分开两个文件存的：一个 .MYI 存索引，一个 .MYD 存数据。
为什么要有主键？
为什么 DBA 都建议表中一定要有主键，而且推荐使用整型自增？
因为 InnoDB 表里面的数据必须要有一个 B+tree 的索引结构来组织、维护我们的整张表的所有数据，从而形成 .idb 文件。
那和主键有什么关系？
如果 InnoDB 创建了一张没有主键的表，那这张表就有可能没有任何索引，则 MySQL会选择所有具有唯一性并且不为 null 中的第一个字段的创建聚集索引。
如果没有唯一性索引的字段就会有一个隐式字段成为表的聚集索引：而这个隐式字段，就是 InnoDB 帮我们创建的一个长度为 6字节 的整数列 ROW_ID，它随着新行的插入单调增加，InnoDB 就以该列对数据进行聚集。
使用这个 ROW_ID 列的表都共享一个相同的全局序列计数器（这是数据字典的一部分）。为了避免这个 ROW_ID 用完，所以建议表中一定要单独建立一个主键字段。
为什么推荐使用整型自增？
首先整型的占用空间会比字符串小，而且在查找上比大小也会比字符串更快。字符串比大小的时候还要先转换成 ASCII 码再去比较。
如果使用自增的话，在插入方面的效率也会提高。
不使用自增，可能时不时会往 B+tree 的中间某一位置插入元素，当这个节点位置放满了的时候，节点就要进行分裂操作（效率低）再去维护，有可能树还要进行平衡，又是一个耗性能的操作。
都用自增就会永远都往后面插入元素，这样索引节点分裂的概率就会小很多。
MySQL的一级索引和二级索引
一级索引和二级索引即对应聚集索引和非聚集索引，叶子节点存放主索引和数据的树，称为聚集索引树；叶子节点存放辅助索引和主索引的树，称为非聚集索引树。
一级索引（聚集索引）
索引和数据存储在一起，都存储在同一个B+tree中的叶子节点。一般主键索引都是一级索引
二级索引（非聚集索引）
除聚集索引之外的所有索引都叫做二级索引，也称辅助索引。
它的叶子节点则不会存储其它所有列的数据，就只存储主键值。
底层查找过程：
每次要找数据的时候，会根据它找到对应叶子节点的主键值，再把它拿到聚集索引的 B+tree 中查找，从而拿到整条记录。
优点：保持一致性和节省空间。
SQL执行过程
我们的系统到底是如何和 MySQL 交互的？MySQL 如何帮我们存储数据、又是如何帮我们管理事务？MySQL 在接受到我们发送的 SQL 语句时又分别做了哪些事情？
MySQL 驱动
我们的系统在和 MySQL 数据库进行通信的时候，总不可能是平白无故的就能接收和发送请求，就算是你没有做什么操作，那总该是有其他的“人”帮我们做了一些事情，基本上使用过 MySQL 数据库的程序员多多少少都会知道 MySQL 驱动这个概念的。就是这个 MySQL 驱动在底层帮我们做了对数据库的连接，只有建立了连接了，才能够有后面的交互
这样的话，在系统和 MySQL 进行交互之前，MySQL 驱动会帮我们建立好连接，然后我们只需要发送 SQL 语句就可以执行 CRUD 了。一次 SQL 请求就会建立一个连接，多个请求就会建立多个连接，那么问题来了，我们系统肯定不是一个人在使用的，换句话说肯定是存在多个请求同时去争抢连接的情况。我们的 web 系统一般都是部署在 tomcat 容器中的，而 tomcat 是可以并发处理多个请求的，这就会导致多个请求会去建立多个连接，然后使用完再都去关闭，这样会有什么问题呢？
Java系统在通过 MySQL 驱动和 MySQL 数据库连接的时候是基于 TCP/IP 协议的，所以如果每个请求都是新建连接和销毁连接，那这样势必会造成不必要的浪费和性能的下降，也就说上面的多线程请求的时候频繁的创建和销毁连接显然是不合理的。必然会大大降低我们系统的性能，但是如果给你提供一些固定的用来连接的线程，这样是不是不需要反复的创建和销毁连接了呢？相信懂行的朋友会会心一笑，没错，说的就是数据库连接池。
数据库连接池：维护一定的连接数，方便系统获取连接，使用就去池子中获取，用完放回去就可以了，我们不需要关心连接的创建与销毁，也不需要关心线程池是怎么去维护这些连接的。
常见的数据库连接池有 Druid、C3P0、DBCP，连接池实现原理在这里就不深入讨论了，采用连接池大大节省了不断创建与销毁线程的开销，这就是有名的「池化」思想，不管是线程池还是 HTTP 连接池，都能看到它的身影。
数据库连接池
到这里，我们已经知道的是我们的系统在访问 MySQL 数据库的时候，建立的连接并不是每次请求都会去创建的，而是从数据库连接池中去获取，这样就解决了因为反复的创建和销毁连接而带来的性能损耗问题了。不过这里有个小问题，业务系统是并发的，而 MySQL 接受请求的线程呢，只有一个？
其实MySQL 的架构体系中也已经提供了这样的一个池子，也是数据库连池。双方都是通过数据库连接池来管理各个连接的，这样一方面线程之前不需要是争抢连接，更重要的是不需要反复的创建的销毁连接。
至此系统和 MySQL 数据库之间的连接问题已经说明清楚了。那么 MySQL 数据库中的这些连接是怎么来处理的，又是谁来处理呢？
网络连接必须由线程来处理
对计算基础稍微有一点了解的的同学都是知道的，网络中的连接都是由线程来处理的，所谓网络连接说白了就是一次请求，每次请求都会有相应的线程去处理的。也就是说对于 SQL 语句的请求在 MySQL 中是由一个个的线程去处理的。
那这些线程会怎么去处理这些请求？会做哪些事情？
SQL 接口
MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理。
查询解析器
假如现在有这样的一个 SQL
SELECT stuName,age,sex FROM students WHERE id = 1
但是这个 SQL 是写给我们人看的，机器哪里知道你在说什么？这个时候解析器就上场了。他会将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言，至于怎么解析的就不需要在深究了，无非是自己一套相关的规则。
现在 SQL 已经被解析成 MySQL 认识的样子的，那下一步是不是就是执行吗？理论上是这样子的，但是 MySQL 的强大远不止于此，他还会帮我们选择最优的查询路径。
什么叫最优查询路径？就是 MySQL 会按照自己认为的效率最高的方式去执行查询
具体是怎么做到的呢？这就要说到 MySQL 的查询优化器了
MySQL 查询优化器
查询优化器内部具体怎么实现的我们不需要是关心，我需要知道的是 MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面, IO 成本和 CPU 成本
IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关
CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。
MySQL 优化器 会计算 「IO 成本 + CPU」 成本最小的那个索引来执行
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-em8KBQZm-1629818634034)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\)]
优化器执行选出最优索引等步骤后，会去调用【存储引擎接口】，【存储引擎接口】需要由【执行器】进行调用，开始去执行被 MySQL 解析过和优化过的 SQL 语句
执行器
执行器是一个非常重要的组件，因为前面那些组件的操作最终必须通过执行器去调用存储引擎接口才能被执行。执行器最终最根据一系列的执行计划去调用存储引擎的接口去完成 SQL 的执行
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GuIshQeo-1629818634035)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\)]
存储引擎
查询优化器会调用存储引擎的接口，去执行 SQL，也就是说真正执行 SQL 的动作是在存储引擎中完成的。数据是被存放在内存或者是磁盘中的（存储引擎是一个非常重要的组件，开头也已经介绍）
我们以一个更新的SQL语句来说明，SQL 如下
UPDATE students SET stuName = '小强' WHERE id = 1
当我们系统发出这样的查询去交给 MySQL 的时候，MySQL 会按照我们上面介绍的一系列的流程最终通过执行器调用存储引擎去执行，流程图就是上面那个。在执行这个 SQL 的时候 SQL 语句对应的数据要么是在内存中，要么是在磁盘中，如果直接在磁盘中操作，那这样的随机IO读写的速度肯定让人无法接受的，所以每次在执行 SQL 的时候都会将其数据加载到内存中，这块内存就是**【 InnoDB 】**中一个非常重要的组件：缓冲池 Buffer Pool
Buffer Pool （缓冲池）是 InnoDB 存储引擎中非常重要的内存结构，顾名思义，缓冲池其实就是类似 Redis 一样的作用，起到一个缓存的作用，因为我们都知道 MySQL 的数据最终是存储在磁盘中的，如果没有这个 Buffer Pool 那么我们每次的数据库请求都会磁盘中查找，这样必然会存在 IO 操作，这肯定是无法接受的。但是有了 Buffer Pool 就是我们第一次在查询的时候会将查询的结果存到 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中，如下图
按照上面的那幅图，这条 SQL 语句的执行步骤大致是这样子的
- innodb 存储引擎会在缓冲池中查找 id=1 的这条数据是否存在
- 发现不存在，那么就会去磁盘中加载，并将其存放在缓冲池中
- 该条记录会被加上一个独占锁（总不能你在修改的时候别人也在修改吧，这个机制本篇文章不重点介绍，以后会专门写文章来详细讲解）
undo 日志文件：记录数据被修改前的样子
undo 顾名思义，就是没有做，没发生的意思。undo log 就是没有发生事情（原本事情是什么）的一些日志
我们刚刚已经说了，在准备更新一条语句的时候，该条语句已经被加载到 Buffer pool 中了，实际上这里还有这样的操作，就是在将该条语句加载到 Buffer Pool 中的时候同时会往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来。
这样做的目的是什么？
Innodb 存储引擎的最大特点就是支持事务，如果本次更新失败，也就是事务提交失败，那么该事务中的所有的操作都必须回滚到执行前的样子，也就是说当事务失败的时候，也不会对原始数据有影响，看图说话
这里说句额外话，其实 MySQL 也是一个系统，就好比我们平时开发的 java 的功能系统一样，MySQL 使用的是自己相应的语言开发出来的一套系统而已，它根据自己需要的功能去设计对应的功能，它即然能做到哪些事情，那么必然是设计者们当初这么定义或者是根据实际的场景变更演化而来的。所以大家放平心态，把 MySQL 当作一个系统去了解熟悉他。
到这一步，我们的执行的 SQL 语句已经被加载到 Buffer Pool 中了，然后开始更新这条语句，更新的操作实际是在Buffer Pool中执行的，那问题来了，按照我们平时开发的一套理论缓冲池中的数据和数据库中的数据不一致时候，我们就认为缓存中的数据是脏数据，**那此时 Buffer Pool 中的数据岂不是成了脏数据？**没错，目前这条数据就是脏数据，Buffer Pool 中的记录是小强 数据库中的记录是旺财 ，这种情况 MySQL是怎么处理的呢，继续往下看
redo 日志文件：记录数据被修改后的样子
除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中，其他的操作是在内存中完成的，内存中的数据的特点就是：断电丢失。如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的。这个时候 redo 日志文件就需要来大显神通了
注：redo 日志文件是 InnoDB 特有的，他是存储引擎级别的，不是 MySQL 级别的
redo 记录的是数据修改之后的值，不管事务是否提交都会记录下来，例如，此时将要做的是update students set stuName='小强' where id=1; 那么这条操作就会被记录到 redo log buffer 中，啥？怎么又出来一个 redo log buffer ,很简单，MySQL 为了提高效率，所以将这些操作都先放在内存中去完成，然后会在某个时机将其持久化到磁盘中。
截至目前，我们应该都熟悉了 MySQL 的执行器调用存储引擎是怎么将一条 SQL 加载到缓冲池和记录哪些日志的，流程如下：
- 准备更新一条 SQL 语句
- MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
- 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
- innodb 会在 Buffer Pool 中执行更新操作
- 更新后的数据会记录在 redo log buffer 中
上面说的步骤都是在正常情况下的操作，但是程序的设计和优化并不仅是为了这些正常情况而去做的，也是为了那些临界区和极端情况下出现的问题去优化设计的
这个时候如果服务器宕机了，那么缓存中的数据还是丢失了。真烦，竟然数据总是丢失，那能不能不要放在内存中，直接保存到磁盘呢？很显然不行，因为在上面也已经介绍了，在内存中的操作目的是为了提高效率。
此时，如果 MySQL 真的宕机了，那么没关系的，因为 MySQL 会认为本次事务是失败的，所以数据依旧是更新前的样子，并不会有任何的影响。
好了，语句也更新好了那么需要将更新的值提交啊，也就是需要提交本次的事务了，因为只要事务成功提交了，才会将最后的变更保存到数据库，在提交事务前仍然会具有相关的其他操作
将 redo Log Buffer 中的数据持久化到磁盘中，就是将 redo log buffer 中的数据写入到 redo log 磁盘文件中，一般情况下，redo log Buffer 数据写入磁盘的策略是立即刷入磁盘（具体策略情况在下面小总结出会详细介绍）
如果 redo log Buffer 刷入磁盘后，数据库服务器宕机了，那我们更新的数据怎么办？此时数据是在内存中，数据岂不是丢失了？不，这次数据就不会丢失了，因为 redo log buffer 中的数据已经被写入到磁盘了，已经被持久化了，就算数据库宕机了，在下次重启的时候 MySQL 也会将 redo 日志文件内容恢复到 Buffer Pool 中（这边我的理解是和 Redis 的持久化机制是差不多的，在 Redis 启动的时候会检查 rdb 或者是 aof 或者是两者都检查，根据持久化的文件来将数据恢复到内存中）
到此为止，从【执行器开始调用存储引擎接口】做了哪些事情呢？
1.准备更新一条 SQL 语句
（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
3.在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
会在 Buffer Pool 中执行更新操作
5.更新后的数据会记录在 redo log buffer 中
提交事务的时候，会将 redo log buffer 中的数据写入到 redo 日志文件中 刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置
值为 0 表示不刷入磁盘
值为 1 表示立即刷入磁盘
值为 2 表示先刷到 os cache
重启的时候会将 redo 日志恢复到缓冲池中
截止到目前位置，MySQL 的【执行器】调用【存储引擎的接口】去执行【执行计划】提供的 SQL 的时候 InnoDB 做了哪些事情也就基本差不多了，但是这还没完。下面还需要介绍下 MySQL 级别的日志文件 bin log
bin log 日志文件：记录整个操作过程
上面介绍到的**redo log是 InnoDB 存储引擎特有的日志文件，而bin log属于是 MySQL 级别的日志**。redo log记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。bin log是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录做了更新操作” 两者的主要特点总结如下:
| 性质 | redo Log | bin Log |
|---|---|---|
| 文件大小 | redo log 的大小是固定的（配置中也可以设置，一般默认的就足够了） | bin log 可通过配置参数max_bin log_size设置每个bin log文件的大小（但是一般不建议修改）。 |
| 实现方式 | redo log是InnoDB引擎层实现的（也就是说是 Innodb 存储引起过独有的） |
bin log是 MySQL 层实现的，所有引擎都可以使用 bin log日志 |
| 记录方式 | redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。 | bin log 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上 |
| 使用场景 |
redo log适用于崩溃恢复(crash-safe)（这一点其实非常类似与 Redis 的持久化特征） |
bin log 适用于主从复制和数据恢复
|
bin log文件是如何刷入磁盘的?
bin log 的刷盘是有相关的策略的，策略可以通过sync_bin log来修改，默认为 0，表示先写入 os cache，也就是说在提交事务的时候，数据不会直接到磁盘中，这样如果宕机bin log数据仍然会丢失。所以建议将sync_bin log设置为 1 表示直接将数据写入到磁盘文件中。
刷入 bin log 有以下几种【模式】
1、 STATMENT
基于 SQL 语句的复制(statement-based replication, SBR)，每一条会修改数据的 SQL 语句会记录到 bin log 中
【优点】：不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能
【缺点】：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等
2、ROW
基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了
【优点】：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题
【缺点】：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨
3、MIXED
基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log
那既然bin log也是日志文件，那它是在什么记录数据的呢？
其实 MySQL 在提交事务的时候，不仅仅会将 redo log buffer 中的数据写入到redo log 文件中，同时也会将本次修改的数据记录到 bin log文件中，同时会将本次修改的bin log文件名和修改的内容在bin log中的位置记录到redo log中，最后还会在redo log最后写入 commit 标记，这样就表示本次事务被成功的提交了。
如果在数据被写入到bin log文件的时候，刚写完，数据库宕机了，数据会丢失吗？
首先可以确定的是，只要redo log最后没有 commit 标记，说明本次的事务一定是失败的。但是数据是没有丢失了，因为已经被记录到redo log的磁盘文件中了。在 MySQL 重启的时候，就会将 redo log 中的数据恢复（加载）到Buffer Pool中。
好了，到目前为止，一个更新操作我们基本介绍得差不多，但是你有没有感觉少了哪件事情还没有做？是不是你也发现这个时候被更新记录仅仅是在内存中执行的，哪怕是宕机又恢复了也仅仅是将更新后的记录加载到Buffer Pool中，这个时候 MySQL 数据库中的这条记录依旧是旧值，也就是说内存中的数据在我们看来依旧是脏数据，那这个时候怎么办呢？
其实 MySQL 会有一个【后台线程】，它会在某个时机将我们Buffer Pool中的脏数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xaea71qR-1629818634044)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\)]
到此，关于Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念以及关系就基本差不多了。
我们再回顾下
1. Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的
2. Undo log 记录的是数据操作前的样子
3. redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）
4. bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）
从准备更新一条数据到事务的提交的流程描述
1. 首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
2. 在数据被缓存到缓存池的同时，会写入 undo log 日志文件
3. 更新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redo log buffer 中
4. 完成以后就可以提交事务，在提交的同时会做以下三件事
5. （第一件事）将redo log buffer中的数据刷入到 redo log 文件中
6. （第二件事）将本次操作记录写入到 bin log文件中
7. （第三件事）将 bin log 文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记
至此表示整个更新事务已经完成
文章原文：链接
binlog
binlog，即二进制日志,它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；它可以用来查看数据库的变更历史、数据库增量备份和恢复、MySQL的复制（主从数据库的复制）。
binlog有三种格式
1、【Statement】基于SQL语句的复制(statement-based replication,SBR)，每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题。
ps：相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。
2、【Row】基于行的复制(row-based replication,RBR)，5.1.5版本的MySQL才开始支持row level的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.
缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
ps:新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。
3、【Mixed】混合模式复制(mixed-based replication,MBR)从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
查看是否开启了binlog
mysql> show variables like 'log_%';
+---------------------------------+-------------+
| Variable_name | Value |
+---------------------------------+-------------+
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | .\ |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
+---------------------------------+-------------+
mysql的配置文件添加如下配置：
# Binary Logging.
log-bin=mysql-bin
binlog-format=Row
重启MySQL服务之后验证binlog是否开启：
show variables like 'log_bin';
show binary logs;
binlog文件的位置：如果在修改的binlog时给的是全路径，那么生成的日志文件就在指定的目录下；如果如以上步骤中只给一个名字，那么生成的binlog日志的位置为（windwos为例）：
C:\ProgramData\MySQL\MySQL Server 5.5\data
服务重启之后就会在指定目录下产生mysql-bin.000001和文件。
查看指定binlog文件的内容
mysql> show binlog events in 'mysql-bin.000001';
只查看第一个binlog文件的内容
mysql> show binlog events;
查看带有序号的binlog文件内容
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+----------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+----------------------+
| mysql-bin.000002 | 4 | Format_desc|1 |107 | Server ver: 5.5.58-log, Binlog ver: 4 |
| mysql-bin.000002 |107| Query |1 | 175 | BEGIN |
| mysql-bin.000002 |175| Query |1 |268 | use `test`; delete from bean where id = 10|
| mysql-bin.000002 |268| Xid |1 |295 | COMMIT /* xid=4 */ |
+------------------+-----+-------------+-----------+-------------+----------------------+
4 rows in set (0.02 sec)
1、当停止或重启服务器时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的日志文件，文件序号递增；
2、 如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性）
3、 日志被刷新时，新生成一个日志文件。
相关参考
1、/innodb/
2、MySQL的数据存在磁盘上到底长什么样
3、阿里二面: 详解一条 SQL 的执行过程：/s?__biz=MzU4ODI1MjA3NQ==&mid=2247499508&idx=2&sn=446d53f109c058b4c389cbfe7b0c3c31&chksm=fddd2830caaaa12650240644d91aa32bbc0754c993e0bd236a520c79e41370126d2d4e7e90fb&scene=132#wechat_redirect
PostreSQL
常用命令
linux命令行直接运行pgsql命令
PGPASSWORD=password psql -h 127.0.0.1 -p 5432 -d basicframe -U basicframe -c "SELECT * FROM t_acc_user"
shell脚本连接postgresql并操作数据库
#!/bin/sh
sql="UPDATE t_acc_user SET status = 'normal'"
PGPASSWORD=pgsqlpwd psql -h 127.0.0.1 -p 5432 -d basicframe -U basicframe -c "$sql"
切换用户
su postgres
bash-4.2$ psql -d basicframe
psql (9.3.6)
Type "help" for help.
basicframe=# select * from t_acc_master;
分组后拼接多行
分组查询结果做字段拼接
| name_type | name |
|---|---|
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 1 | 张四 |
| 2 | 李五 |
| 2 | 李一 |
需求是根据name_type分组，李姓的在一组，张姓的分为一组，查询结果如下：
| name_type | names |
|---|---|
| 1 | 张三,张四 |
| 2 | 李四,李五,李一 |
| 3 | 王五 |
MySQL可以很方便的利用group_concat函数来实现，但是postgres9.0版本之前没有这样的函数，需要进行自定义函数【参考博客】。我们可以用**【array_agg()】,【string_agg()】**等函数来实现。注意string_agg()方法参数都必须为字符串。
array_agg
select
name_type,array_to_string(array_agg(name),',') as names
from
t_acc_user
group by
name_type;
string_agg
select
name_type, string_agg(name,',') as names
from
t_acc_user
group by
name_type;
根据条件修改对应字段
-- t_acc_master_mapping:映射表 name_old：旧 name_new:新
UPDATE t_acc_master t01
SET name = t02.name_new
FROM
(SELECT * FROM t_acc_master_mapping) t02
WHERE t01.name = t02.name_old AND t01.status = 'normal';
锁表问题解决
在使用pgsql删除数据库表（DROP）操作时候出现阻塞的现象，由此怀疑是锁表导致。
排查数据库表是否锁住
SELECT oid FROM pg_class WHERE relname = 't_user'; -- 可能锁表的表名
SELECT pid FROM pg_locks WHERE relation = '2523'; -- 由上面查出的oid
如果上面的SQL查询到了结果，则表示该表被锁，执行下面SQL释放锁定
SELECT pg_cancel_backend('100045'); -- 上面查到的pid
ctid的浅谈
ctid： 表示数据记录的物理行当信息，指的是 一条记录位于哪个数据块的哪个位移上面。 跟oracle中伪列 rowid 的意义一样的，只是形式不一样。例如这有个一表t_org_user，查看每行记录的ctid情况：
SELECT ctid, * FROM t_org_user;
查询结果，格式(blockid,itemid)，拿其中(0,1)来说，0表示块id，1表示在这块第一条记录。
ctid | id | name | cnname
(0,1) 1 data01 测试01
(0,2) 2 data02 测试02
(0,3) 3 data01 测试01
(0,4) 1 data01 测试01
ctid去重
我们知道rowid在oracle有个重要的作用；被用作表记录去重；同理 ctid在postgresql里面同样可以使用。例如t_org_user表id为1有两条记录
DELETE FROM t_org_user
WHERE ctid NOT IN ( SELECT min( ctid ) FROM t_org_user GROUP BY id );
根据id去重后
ctid | id | name | cnname
(0,1) 1 data01 测试01
(0,2) 2 data02 测试02
(0,3) 3 data01 测试01
刚刚我们删除了(0,4)这条记录，现在我们重新插入一条新的记录之后查询
ctid | id | name | cnname
(0,1) 1 data01 测试01
(0,2) 2 data02 测试02
(0,3) 3 data01 测试01
(0,5) 1 data01 测试01
为什么不是(0,4)，而是(0,5)？这个跟postgresql多版本事务有关，这是postgresql的特性，postgresql里面没有回滚段的概念，那怎么把(0,5)在显示呢？想这块(0,5)的空间再存放数据，postgresql里面有AUTOVACUUM进程，当然我们也可以手动回收这段空间；
删除（0,5）这条数据之后执行：
vacuum t_org_user;
再次插入之后就是从(0,4)开始的，vacuum: 回收未显示的物理位置；标明可以继续使用。
select relpages,reltuples from pg_class where relname = 't_org_user';
我们可以借助系统视图pg_class，其中relpages，reltuples分别代表块数，记录数
generate_series序列函数
INSERT INTO t_org_user
SELECT generate_series ( 1, 1000 ), 'data' || generate_series ( 1, 1000 ), '中文' || generate_series ( 1, 1000 );
generate_series为一个序列函数，例如产生1-100就是generate_series(1,100)，产生0-100直接的偶数就是generate_series(0,100,2)，其中的0表示序列开始位置；100代表结束位置；2为偏移量。
未指定字段类型问题（type “unknown”）
SELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit
FROM ( SELECT 'test-new-xx' as cmdaudit from t_acc_master ) t
postgres数据库较低版本（9.3.6）存在以下问题，这里的问题是'' as name实际上并没有为值指定类型。这是unknown类型，而 PostgreSQL 通常从诸如您将其插入到哪个列或您将其传递给哪个函数之类的东西推断出真正的类型。
Could not determine polymorphic type because input has type "unknown"
类型化定义
TEXT '' AS name
CAST
CAST('' AS text) AS name
PostgreSQL 简写
''::text
案例
SELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit
FROM ( SELECT 'test-new-xx' ::text as cmdaudit from t_acc_master ) t
SELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit FROM (
SELECT VARCHAR 'test-new-xx' as cmdaudit from t_acc_master ) t
SELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit FROM (
SELECT TEXT 'test-new-xx' as cmdaudit from t_acc_master ) t
SELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit FROM (
SELECT CAST('test-new-xx' AS text) as cmdaudit from t_acc_master ) t
自定义函数索引丢失
测试场景: t_auth_r_master_slave授权表 200w 行数据，masterId、resId有索引 ：
CREATE INDEX resid_index ON t_auth_r_master_slave (resid);
CREATE INDEX masterid_index ON t_auth_r_master_slave (masterid);
EXPLAIN ANALYZE进行分析
EXPLAIN ANALYZE
SELECT * FROM t_auth_r_master_slave LIMIT 10
SELECT count(*) FROM t_auth_r_master_slave
使用函数get_master_auth耗时11s
EXPLAIN ANALYZE
select
t.id,t.ruleid,t.masterid,t.resid
from
get_master_auth('del') t
where
t.masterid = '1140131' and t.resid = '38100217' and t.slaveid is null;
Function Scan on get_master_auth t (cost=0.25..15.25 rows=1 width=872) (actual time=9719.881..10632.622 rows=2 loops=1)
Filter: (((masterid)::text = '1140131'::text) AND ((resid)::text = '38100217'::text))
Rows Removed by Filter: 2463409
Total runtime: 10721.853 ms
使用原始sql耗时0.01s
EXPLAIN ANALYZE
select
t.id,t.ruleid,t.masterid,t.resid
from
(
SELECT
t.*
FROM
t_auth_r_master_slave t
JOIN t_acc_master m ON m.id=t.masterid AND m.status !='del'
JOIN t_auth_res r ON r.id=t.resid AND r.status !='del'
JOIN t_acc_slave s ON s.id=t.slaveid and s.status != 'del'
WHERE
t.status!='del'
UNION ALL
SELECT
t.*
FROM
t_auth_r_master_slave t
JOIN t_acc_master m ON m.id=t.masterid AND m.status !='del'
JOIN t_auth_res r ON r.id=t.resid AND r.status !='del'
WHERE
t.status!='del' and t.slaveid is null
) t
where
t.masterid = '1' and t.resid = '1' and t.slaveid is null;
匹配上索引:Bitmap Index Scan on resid_index…
Append (cost=749.97..1555.83 rows=2 width=35) (actual time=513.408..513.408 rows=0 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=749.97..782.07 rows=1 width=35) (actual time=506.057..506.057 rows=0 loops=1)
-> Nested Loop (cost=749.97..782.06 rows=1 width=1398) (actual time=506.055..506.055 rows=0 loops=1)
-> Nested Loop (cost=749.68..773.74 rows=1 width=1398) (actual time=506.053..506.053 rows=0 loops=1)
-> Nested Loop (cost=749.39..765.43 rows=1 width=1398) (actual time=506.052..506.052 rows=0 loops=1)
-> Bitmap Heap Scan on t_auth_r_master_slave t (cost=749.11..757.12 rows=1 width=1398) (actual time=506.050..506.050 rows=0 loops=1)
Recheck Cond: (((resid)::text = '38100217'::text) AND ((masterid)::text = '1140131'::text))
Rows Removed by Index Recheck: 9
Filter: ((slaveid IS NULL) AND ((status)::text <> 'del'::text))
Rows Removed by Filter: 2
-> BitmapAnd (cost=749.11..749.11 rows=2 width=0) (actual time=505.392..505.392 rows=0 loops=1)
-> Bitmap Index Scan on resid_index (cost=0.00..14.54 rows=281 width=0) (actual time=12.929..12.929 rows=70 loops=1)
Index Cond: ((resid)::text = '38100217'::text)
-> Bitmap Index Scan on masterid_index (cost=0.00..734.32 rows=21586 width=0) (actual time=492.214..492.214 rows=22169 loops=1)
Index Cond: ((masterid)::text = '1140131'::text)
-> Index Scan using t_acc_master_pkey on t_acc_master m (cost=0.28..8.30 rows=1 width=7) (never executed)
Index Cond: ((id)::text = '1140131'::text)
Filter: ((status)::text <> 'del'::text)
-> Index Scan using t_auth_res_pkey on t_auth_res r (cost=0.29..8.31 rows=1 width=9) (never executed)
Index Cond: ((id)::text = '38100217'::text)
Filter: ((status)::text <> 'del'::text)
-> Index Scan using t_acc_slave_pkey on t_acc_slave s (cost=0.29..8.31 rows=1 width=9) (never executed)
Index Cond: ((id)::text = (t.slaveid)::text)
Filter: ((status)::text <> 'del'::text)
-> Subquery Scan on "*SELECT* 2" (cost=749.68..773.75 rows=1 width=35) (actual time=7.348..7.348 rows=0 loops=1)
-> Nested Loop (cost=749.68..773.74 rows=1 width=1398) (actual time=7.346..7.346 rows=0 loops=1)
-> Nested Loop (cost=749.39..765.43 rows=1 width=1398) (actual time=7.345..7.345 rows=0 loops=1)
-> Bitmap Heap Scan on t_auth_r_master_slave t_1 (cost=749.11..757.12 rows=1 width=1398) (actual time=7.343..7.343 rows=0 loops=1)
Recheck Cond: (((resid)::text = '38100217'::text) AND ((masterid)::text = '1140131'::text))
Rows Removed by Index Recheck: 9
Filter: ((slaveid IS NULL) AND (slaveid IS NULL) AND ((status)::text <> 'del'::text))
Rows Removed by Filter: 2
-> BitmapAnd (cost=749.11..749.11 rows=2 width=0) (actual time=7.315..7.315 rows=0 loops=1)
-> Bitmap Index Scan on resid_index (cost=0.00..14.54 rows=281 width=0) (actual time=0.075..0.075 rows=70 loops=1)
Index Cond: ((resid)::text = '38100217'::text)
-> Bitmap Index Scan on masterid_index (cost=0.00..734.32 rows=21586 width=0) (actual time=7.122..7.122 rows=22169 loops=1)
Index Cond: ((masterid)::text = '1140131'::text)
-> Index Scan using t_acc_master_pkey on t_acc_master m_1 (cost=0.28..8.30 rows=1 width=7) (never executed)
Index Cond: ((id)::text = '1140131'::text)
Filter: ((status)::text <> 'del'::text)
-> Index Scan using t_auth_res_pkey on t_auth_res r_1 (cost=0.29..8.31 rows=1 width=9) (never executed)
Index Cond: ((id)::text = '38100217'::text)
Filter: ((status)::text <> 'del'::text)
Total runtime: 514.501 ms
https:///postgresql/
/u011944141/article/details/98056440
相关参考
pgsql:/lottu/category/
pgsql数据迁移：/lottu/category/
pgsql高可用：/lottu/category/