框架源码专题-MySQL-1、基本原理
1. SQL 分类
https://www.jianshu.com/p/671a2d54dca0
DQL：查询
DML：增删改
DDL： (Data Definition Language 数据定义语言）用于操作对象及对象本身，这种对象包括数据库,表对象，及视图对象
DCL：（Data Control Language 数据控制语句） 用于操作数据库对象的权限，比如：
greate: 分配权限给用户
revoke: 废除数据库中某用户的权限
2. MySQL 结构
3. 内部原理
[[Mysql面试题#^jerwaj]]
https://blog.csdn.net/weixin_40471676/article/details/119732738
3.1. 查询过程
[[执行一条 select 语句，期间发生了什么？ 小林coding]]
- 连接器：建立连接，管理连接、校验用户身份；
- 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
- 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
- 执行 SQL：执行 SQL 共有三个阶段：
- 预处理阶段：检查表或字段是否存在；将
select *中的*符号扩展为表上的所有列。 - 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
- 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；
- 预处理阶段：检查表或字段是否存在；将
3.2. 更新过程
https://xiaolincoding.com/mysql/log/how_update.html#%E4%B8%BA%E4%BB%80%E4%B9%88%E9%9C%80%E8%A6%81-undo-log
查询语句的那一套流程，更新语句也是同样会走一遍：
- 客户端先通过连接器建立连接，连接器自会判断用户身份；
- 因为这是一条 update 语句，所以不需要经过查询缓存，但是表上有更新语句，是会把整个表的查询缓存清空的，所以说查询缓存很鸡肋，在 MySQL 8.0 就被移除这个功能了；
- 解析器会通过词法分析识别出关键字 update，表名等等，构建出语法树，接着还会做语法分析，判断输入的语句是否符合 MySQL 语法；
- 预处理器会判断表和字段是否存在；
- 优化器确定执行计划，因为 where 条件中的 id 是主键索引，所以决定要使用 id 这个索引；
- 执行器负责具体执行，找到这一行，然后更新。
不过，更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志） 、binlog （归档日志）这三种日志：
MySQL-3、redolog-undolog-binlog
- undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的 原子性，主要 用于事务回滚和 MVCC。
- redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的 持久性，主要 用于掉电等故障恢复；
- binlog （归档日志）：是 Server 层生成的日志，主要 用于数据备份和主从复制
具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:
- 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
- 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
- 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
- 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
- 如果一样的话就不进行后续更新流程；
- 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
- 开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
- InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log buffer(prepare 状态) 里面，这个时候更新就算完成了。为了减少磁盘 I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
- 至此，一条记录更新完了。
- 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
- 事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：
- prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
- commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；
- 至此，一条更新语句执行完成
3.3. 查询缓存
https://www.modb.pro/db/325897
https://www.zhihu.com/question/565486816/answer/2822879610
如果查询缓存中存在这条 SQL 的结果集缓存，直接取出返回客户端，前面说过，表上有更新的时候，这个表相关的查询缓存都会失效，所以查询缓存不建议使用，在 MySQL 8.0 版本把查询缓存删除了
对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。
所以，MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。
https://www.bilibili.com/video/BV1zJ411M7TB?p=74&vd_source=c5b2d0d7bc377c0c35dbc251d95cf204
3.4. Buffer Pool
❕ ^w5k1hd
3.4.1. Buffer Pool 有多大
Buffer Pool 是在 MySQL 启动的时候，向操作系统申请的一片连续的内存空间，默认配置下 Buffer Pool 只有 128MB 。
可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小，一般建议设置成可用物理内存的 60%~80%。
3.4.2. 缓存内容
InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。
在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的 16KB 的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。
所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，申请物理内存，接着将虚拟地址和物理地址建立映射关系。
Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。
Undo 页是记录什么？
开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。
查询一条记录，就只需要缓冲一条记录吗？当然不是！
当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。
3.4.3. 与 redo log 关系
MySQL-3、redolog-undolog-binlog4. 事务原理
MySQL-2、事务-MVCC-LBCC6. 实战经验
7. 参考与感谢
https://blog.csdn.net/J080624/article/details/128112914
7.1. 马士兵
8. Old
8.1. page
8.2. 一行数据超过 16k
https://blog.csdn.net/star_xing123/article/details/107380438
8.3. 双写落盘
8.3.1. CheckPoint
https://www.cnblogs.com/chenpingzhao/p/5107480.html
https://www.cnblogs.com/wy123/p/8353245.html
思考一下这个场景：如果重做日志可以无限地增大，同时缓冲池也足够大，那么是不需要将缓冲池中页的新版本刷新回磁盘。因为当发生宕机时，完全可以通过重做日志来恢复整个数据库系统中的数据到宕机发生的时刻。
因此 Checkpoint（检查点）技术就诞生了，目的是解决以下几个问题：1、缩短数据库的恢复时间；2、缓冲池不够用时，将脏页刷新到磁盘；3、重做日志不可用时，刷新脏页。
脏页落盘的时机采用 CheckPoint 检查点机制以下机制都可通过参数控制
sharp checkpoint：强制落盘。把内存中所有的脏页都执行落盘操作。只有当关闭数据库之前才会执行。
fuzzy checkpoint：没落落盘。把一部分脏页执行落盘操作
1.Master Thrad Checkpoint 主线程定时将脏页写入磁盘每秒或每 10s 执行一次脏页。
2.FLUSH_LRU_LIST buffer pool 有脏页换出，执行落盘
3.Async/Sync Flush checkpoint 当 redo log 快写满的时候执行落盘
a.当 redo log 超过 75% 小于 90% 会执行异步落盘
b.当 redo log 超过 90%，会执行同步落盘操作。会阻塞写操作。
4.Dirty Page too much checkpoint 如果 buffer pool 中脏页太多，脏页率超过 75% 执行落盘
- 那么其实建立普通索引是不合适的，因为写的过程，虽然利用了 change buffer 暂时提高了写的性能，但是在读的时候还是需要磁盘 IO。可以考虑建立唯一索引，在索引写的时候，就提前读取数据到缓冲池中，提高读的性能。
- 适合建立二级非唯一性索引：利用普通索引的 change buffer 特性，当业务场景中的写远大于读时，常见场景为日志表，当某些列必须建立索引时，可以考虑建立普通索引，提高写入性能。
- 适合建立二级唯一性索引：如果业务场景的写之后立即伴随读，如果列的值是唯一的
8.3.2. 业务实践
- 被动：在后续的真正需要读这个非唯一索引时，把索引的数据页从磁盘读取到内存中，再通过 change buffer 做一个 merge 操作，merge 操作以后，内存中的数据页就是最新的了。
- 主动：innoDB 引擎中有线程会主动的定期做 merge 操作
那么什么时候会真正更新数据页呢？有两种情况会触发：
关于 change buffer 的一些思考 1. 既然是缓存索引更新操作，那也就是说，索引数据并不是最新的，那不是会造成数据不一致的情况？ 当需要用到的索引在 change buffer 里有尚未应用的更新动作，会马上进行更新。 2. 为什么 change buffer 只对二级非唯一索引起效？ 因为对于唯一索引，所有的索引维护更新，都需要到索引上去判断唯一性，而判断唯一性，就需要把索引块读到 buffer pool 里，这也就跳过了 change buffer 的使用前提：索引块不在 buffer pool 里。 3.change buffer 的低效或瓶颈场景或弊端？ 1)change buffer 会占用 buffer pool 的空间，也就是直接减少了 buffer pool 的可用空间； 2) 如果 buffer pool 命中率很高，也就是说数据块和索引块都能在 buffer pool 里找到，也就没有 change buffer 什么事了； 3) 如果经常操作的表只有少量的二级非唯一索引，也就是说索引更新所带来的压力并不明显，那么 change buffer 的作用也微乎其微； 4)change buffer 里的数据也是脏数据，同样受到 redo log 的保护，同时在推进 redo log check point 时，除了会先将 buffer pool 的数据刷盘，同时也会先将 change buffer 的数据合并和应用到索引上，所以如果 redo log 文件大小设置不合理，导致 redo 文件经常写满，频繁触发写盘，那么 change buffer 的数据也就相当于还没捂热就写出了，不仅没有起到很好的缓存作用，反而因为多了一道先缓存到 change buffer 的开销; 5) 如果一个二级索引做了 DML 以后，马上又对操作的数据进行读操作，那么就等于需要将数据读入 buffer pool，这会触发 change buffer 的 merge 并且应用在二级索引上，那这种场景下还不如不要 change buffer，直接将索引数据读入 buffer pool。
针对随机 IO 的情况，MySQL 利用 change buffer 来避免维护二级非唯一索引时的随机 IO 读。原理是这样的：当对一个拥有二级非唯一索引的列做 DML 时，如果该列值对应的索引块不在 buffer pool 里，就先把这个索引块维护的动作先存储在 change buffer 里，而不去采用随机 IO 读的方式去对应的索引块读到 buffer pool 里。而索引块维护操作，是由后台线程在一定时间间隔来循环地将 change buffer 里的索引维护动作合并，再进行批量的索引更新。合并可以将邻接的块一起操作，以及批量的更新，明显是比每次 DML 都更新索引要高效。 change buffer 属于 buffer pool，空间从 buffer pool 分配，分配百分比由参数 innodb_change_buffer_max_size 控制，默认是 25，即 25%，最大值可取 50；而为了防止 crash 而丢失 change buffer 的数据，在 system 表空间上也分配空间来存 change buffer 的数据，所以重启以后，可以将 system 表空间的 change buffer 相关数据读回 buffer 里。同时，change buffer 的数据也会写 redo，同样具有 crash safe 的特性。而 change buffer 在较低版本的 MySQL 里，雏形是 insert buffer，也就是只缓存 insert 相关的二级非唯一索引的维护动作；而 MySQL5.6 以后，change buffer 可以缓存所有 DML 的二级非唯一索引维护动作，可以通过设置 innodb_change_buffering 来选择缓存的操作，可取值 all,none,inserts,deletes,changes,purges, 各取值含义是： all: 缓存所有二级非唯一索引的维护动作，当然是相关索引块不在 buffer pool 的情况下。 none: 不使用 change buffer 的任何功能。 inserts,deletes,changes: 分别代表 insert,delete,update。 purges: 后台的物理删除操作所触发的二级非唯一索引的维护动作，也可以先缓存下来。
MySQL 的二级索引分为唯一和非唯一两种，而对一个拥有二级索引的列做 DML，同时需要维护更新这个列上的索引。维护更新索引的这个操作，也就是根据列上的值，按照索引排序规则，将该列对应的索引块进行相关维护。而在做维护的时候，自然是要把索引块从磁盘读到 buffer pool 里。那么最理想的情况就是需要维护的索引块已经在 buffer pool 里了，就不需要再去磁盘里找出相应的索引块，也就节省了一些随机 IO。
微信
支付宝
