MySQL学习笔记

连接处理过程：

处理连接->查询缓存(开销大8.0弃用)->语法解析->查询优化->存储引擎->文件系统

字符集：

ASCII：收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符，用一个字节编码。

ISO 8859-1：共收录256个字符，在ASCII的基础上扩充了128个西欧字符，用一个字节编码。(mysql5.7以前默认)

GB2312：收录了汉字、拉丁字母、希腊字母等，收录汉字6763个，其他字符682个。该字符集同时兼容ASCII字符集，编码时采用变长方式，如字符在ASCII字符集中采用一字节编码，否则采用两字节编码。

GBK：对GB2312字符集进行扩充，编码方式兼容GB2312。

UTF-8：几乎收录了各个地区使用的字符，兼容ASCII字符集，采用变长编码方式，编码一个字符时需要1-4个字节。

Unicode：采用UTF-8、UTF-16、UTF-32几种编码方案，UTF-8时使用1-4字节编码一个字符，UTF-16时使用2或4个字节编码一个字符，UTF-32时使用4个字节编码一个字符。

MySQL中的utf8和utf8mb4：

utf8mb3：“阉割”过的utf-8字符集，使用1-3个字节表示一个字符。mysql中utf8时utf8mb3的别名。

utf8mb4：正宗的utf-8，使用1-4个字节表示一个字符。Mysql8.0的默认字符集。（emoji表情占用4个字节）

MySQL字符集比较规则：

服务器级别、数据库级别、表级别、列级别

优先级从低到高

MySQL发送请求到接收响应过程发生的字符集转换：

客户端发送请求（取决于 *** 作系统使用的字符集，windows还取决于启动时设置的default-character-set）服务端接收请求（取决于character-set-client）服务器运行过程中（取决于character-set-connection）服务器向客户端返回（取决于character-set-results）客户端接收响应（取决于 *** 作系统使用的字符集，windows还取决于启动时设置的default-character-set）

MySQL支持的存储引擎（9种）：

InnoDB (默认、支持事务)：支持事务、行级锁机制和外键约束的功能。MyISAM：非事务处理存储引擎。MEMORY：多用于临时表。NDB：MySQL集群专用存储引擎。MERGE：用来管理多个MyISAM表构成的表集合。ARCHIVE：用于数据存档（记录插入后不能修改）。CSV：在存储数据时，以逗号分隔各数据项BLACKHOLE：丢弃写 *** 作，读 *** 作会返回空内容FEDERATED：用来访问远程表

存储引擎的4种推荐选择，

1. 如要提供提交、回滚、崩溃恢复能力的事务安全(ACID兼容)能力，并要求实现并发控制，推荐InnoDB。

2. 如数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率。

3. 如只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的MEMORY引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。

4. 如只有INSERT和SELECT *** 作，可选择Archive，Archive支持高并发的插入 *** 作，但本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。

InnoDB:

是一个将数据存储在磁盘上的存储引擎，支持事务、行级锁机制和外键约束的功能。以页为单位在磁盘和内存之间交互数据，一个页默认大小为16kb。

行格式：

1、COMPACT：

变长字段列表：记录varchar（m）、varbinary(m)、text、blob等类型占用的字节数，逆序存放。如果当前字符集为变长编码，char（M）也存放在变长字段列表，但至少要占用M个字节（如采用utf8时，占用字节范围为m-3m），为了在下次更新时有可能直接在本列更新，避免空间碎片。

NULL值列表：记录哪些列数据为null,主键和not null修饰的列不算在其中，必须用整个字节的位表示，不足高位补0，逆序存放。（如：00000101）

记录头信息：固定5个字节，记录删除状态、当前记录类型、在页面堆中的相对位置、下一条记录的相对位置。

记录的真实数据：

隐藏列：row_id（主键，默认为用户设置的主键，如未设置选择一个不为null的unique列，还没有才默认生成一个）、trx_id（事务ID）、roll_pointer（回滚指针）

具体列数据

REDUDANT：

MySQL5.0以前使用。

字段长度偏移列表：将记录中所有列（包括隐藏列）的长度信息按逆序记录，采用相邻两个偏移量的长度差值就算各个列的长度。

DYNAMIC

MySQL5.7默认，与COMPACT类似，对于溢出列的处理不同，不在本数据页记录数据，仅用20字节记录地址。

COMPRESSED

采用压缩算法对页面进行压缩，同样取数据需要解压缩。

列溢出（COMPACT、REDUDANT）：一个页的大小为16KB（规定一个数据页至少存储2行数据，数据页和溢出页不同），如果列数据超过这个大小则在本页存储前768个字节的数据，其他数据分散存储在其他页面，用20个字节记录指向的数据页，页面间使用链表连接起来。

变长字段列表、NULL值列表逆序存放：因为next_record（当前记录的真实数据到下一条记录真实数据的距离）指向记录头信息和真实数据之间的位置，向左读取是记录头信息，向右读取是真实数据，可以使记录中靠前的字段和他们对应的字段长度信息在内存中距离更近，提高高速缓存命中率。

InnoDB数据页结构：

数据间按主键从小到大串联成一个单向链表（next_recored指向距离，可能为负数）。Infinum->.......->suprenum

每个数据页得File Header部分都有上一页和下一页的编号，所有数据页组成一个双向链表。

Page Directory(页目录)：

按照页内数据的单向链表划分槽，infimum单独占一个槽，supremum内1-8个数据占一个槽，其余4-8个数据占用一个槽，当新增数据时，如槽内已达到数据上限，则新增一个槽进行拆分，一边4个，一边5个。槽内记录分组中最大记录的偏移量。

根据主键如何查找一条记录：

通过二分查找确定分组所在的槽（算上目录页的话，先定位到页，再定位槽），找到该槽所在分组最小的记录（找到上一个槽的偏移量对应的数据，下一个数据即是）。通过记录的next_record遍历槽所在组数据。

索引分类：

聚簇索引：根据主键值大小进行排序，目录页记录主键+页号，数据页存储了完整的信息。--索引即数据，数据即索引。

二级索引：按照某一列大小排序，列值相同时按主键排序（列+主键的联合索引），目录页记录列+主键+页号，数据页仅记录列+主键信息。

联合索引：按照多列排序，第一列相同时按第二列大小，目录页记录索引列+页号，数据也仅记录索引列+主键信息。

覆盖索引（不算一类）：要查询的列均在二级索引内，不需回表。

索引的代价：

空间代价，占用存储空间时间代价，增删改需要修改各个B+树索引，还可能进行页面分裂、回收等；同时执行查询时，需要分析查询成本，选择成本最低的执行。

*的代价：

空间占用大时间长，如果没有通过聚簇索引查询，还需要回表。

注：一个B+树索引的根节点创建后便不会移动（页号不变），初始作为数据页时如数据满，则赋值到一个新页面里，该页面仍为根节点（目录页）。

B树与B+树？？

MySQL系统数据库：

Mysql:存储用户账号、权限信息、存储过程和事件的定义信息，运行过程产生的日志、帮助信息、时区信息等。

Information_scheme：mysql维护的其他数据库信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等。

Performance_schema：mysql服务器运行过程中的状态信息，性能监控。

Sys：通过视图的方式把nformation_scheme、Performance_schema的信息结合起来，便于开发人员查看。

Buffer Pool:

一片连续的内存，用户缓存磁盘中的页，减少磁盘I/O，由控制块（记录页所属表空间编号、页号、缓冲页在buffer pool中的地址、链表节点信息等；与缓冲页一一对应）、缓冲页、碎片组成。默认大小128M可调节。

Free链表：

Buffer pool申请完内存空间后，划分成若干空闲的控制块、缓冲页，将这些控制块组成一个双向链表，由一个基节点（40字节，不占用buffer pool内存，指向头控制块、尾控制块，记录节点数量count）开始。需要加载页时从free链表通过控制块找一个缓冲页，将该控制块从链表上移除。

如何判断buffer pool中是否存在需要加载的页：

hash,根据表空间号+页号可以定位一个页，故根据空间号+页号生成key判断。

结构：key（空间号+页号），value（控制块地址）

Flush链表：

页面数据发生变化的控制块链表，结构与free链表一致。

LRU链表：（最少使用原则，核心：提高Buffer Pool命中率）

解决Buffer Pool空间不足时，决定移除哪些页。

按照常规新增或使用将控制块移动到头部，会因buffer pool的预读机制（顺序访问某个区的xx个（默认56个）页面触发异步加载下一个区的所有页面；连续加载某个区的13个页面，异步将这个区的所有页面加载（默认不开启））及全表扫描导致加载的页不一定使用，大量使用频率低的页同时加载，将使用频率高的页从缓冲区淘汰掉，降低Buffer Pool的命中率。

优化：

将LRU链接分为热数据、冷数据，默认比例为3/8，页面首次加载时放在冷数据区的头部，当下次访问（每去页面读一次数据都算访问一次）该页面的时间间隔与第一次访问的时间间隔大于设置时间(默认1s)时，将当前页的控制块移动到热数据区的头部，同时访问热数据区的前1/4时，控制块在链表的位置不移动。

刷新脏页到磁盘：

1、后台线程定时从LRU链表的尾部扫描页面，检测页面是否修改，修改则刷新到磁盘释放空间。

2、后台线程定时从flush链表刷新页面到磁盘。

3、资源紧张时，会检测LRU链表尾部的页面是否有更新，没更新则会释放；特别紧张时也会出现用户线程从flush链表刷新页面。

在多线程环境下，访问buffre pool的链表需要加锁，为了提高处理速度将buffer pool拆分为若干个小的buffer pool（buffer pool内存分配小于1GB时，无法拆分），同时为了在运行过程中调整buffer pool的大小，且不经过复制，将每个buffer pool再细分为chunk（默认一个128M），通过动态添加chunk调整大小。

事务：

        特性：

        1、原子性： *** 作要么不做要么都做，不存在中间状态。

        2、隔离性：指在并发环境中，并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰。

        3、一致性：事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。（原子性、一致性都是保证一致性的手段）

        4、持久性：持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

        事物的状态：

        1、活动的：事务对应的数据库 *** 作正在执行。

        2、部分提交的：事务的最后一个 *** 作执行完成，但 *** 作都在内存中并没有刷新到磁盘。

        3、失败的：事务处于活动、部分提交状态时，遇到了某些错误无法继续执行。

        4、中止的：事务执行半截变为失败状态，回滚到执行前的状态。

        5、提交的：处于部分提交状态的事务将修改过的数据都刷新到磁盘后。

        保存点：使用savepoint + 保存点名称在事务对应的数据库语句中打点，可以在rollback时指定回到特定的点，否则将回滚到事务开始时。

redo log:

        保证系统因崩溃而重启时恢复到奔溃前的状态。有物理层面（指明对哪个表空间的哪个页进行修改）、逻辑层面（调用事先准备好的函数，执行函数后方可恢复，如删除一系列记录）。

        如何保证redo log的原子性（一次 *** 作可能有多条日志，如何确保为一组完整的）：

                以组的形式记录日志，必须以一条类型为MLOG_MULTI_REC_END的日志结尾；type为1的日志表示 *** 作只有这一条日志即可满足原子性（如更新MAX ROW ID，每次到256倍数时会更新页面7）。

       redo log buffer：

        为了解决记录redo log直接写入磁盘效率问题引入redo log buffer（一片连续的内存，由多个block组成），将每个MTR（底层页面一次原子访问）运行产生的日子暂存到一个地方，当MTR结束时复制到log buffer。

        redo log刷盘时机：

                1、log buffer空间不足时，达到50%左右。

                2、事务提交时。

                3、脏页刷新到磁盘前会现将对应的redo日志刷盘。

                4、后台线程大约1/s每次将log buffer中的redo日志刷盘。

                5、正常关闭服务器。

                6、checkpoint时。

        redo日志是先写到log buffer中再刷新到磁盘的redo日志文件中，由ib_logfile[x]记录，默认2个每个48MB，循环写入，为避免追尾引入lsn记录当前已经写入的redo日志量（包括写入log buffer还未刷盘的日志），buf_next_to_write记录当前log buffer有哪些日志被刷新到磁盘，flushed_to_disk_lsn表示刷新到磁盘的redo日志量。checkpoint_lsn表示数据已持久化节点，之前的redo日志都可被覆盖。

        一次MTR结束后除了写入log buffer中，还需要更新buffer pool的flush链表，记录更改的页及页初始的lsn以及更新后的lsn。

        redo log如何启动时恢复数据：

                通过checkpoint_lsn找到文件组中的偏移量checkpoint_offset为起点，顺序扫描redo日志文件的block，直到某个block的LOG_BLOCK_HDR_DATA_LEN不为512为止。同时引入两点优化措施：1、通过hash将对同一个页面的修改放到一个槽内，一次性修复一个页面减少随机IO；2、跳过已经刷新到磁盘的页面（file header内的file_page_lsn值大于checkpoint_lsn的代表页面已经刷新到磁盘）。

        

undo log:

        为了遇到异常或者执行回滚记录的日志。

        事务ID全局维护变量，需要事务时将当前值分配同时将变量+1，变量值为256的整数倍时刷新到页面为5的Max Trx IDshang。

        新增 *** 作：记录undo log时，只针对聚簇索引记录一条日志。

        删除 *** 作：先将delete_flag从0改为1，此时为中间状态记录仍在记录链上，待事务提交后加入垃圾链表头部，新增数据时先判断垃圾链表头节点空间是否够用（不会向后遍历），不够用向页面申请新的空间，当页面快满时，检查整个页剩余空间是否够用，够用则将记录顺序插入一个临时页再复制回本页面，清理掉碎片重新分配。

        更新 *** 作：1、就地更新--不更新主键且更新前后每个列占用的空间大小完全一致，则执行就地更新；2、先删除再插入--不更新主键且更新前后至少有一列占用空间不一致，则先将记录从记录链表移除到垃圾链表，再执行新增 *** 作。3、更新主键时先将记录进行delete_mark *** 作，然后创建一条新的记录插入聚簇索引，事务提交后将原记录移入垃圾链表。