【MYSQL篇}底层原理全面解析和性能优化方法
2024-05-09 15:26:50 0 举报AI智能生成
登录查看完整内容
深入理解MySQL索引数据结构与算法，理解MySQL底层原理和索引优化等等机制，深入理解事务隔离级别与锁机制等
作者其他创作
大纲/内容
对一行数据的读和写两个操作默认，是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操，作加锁互斥来实现的
保证同样的查询sql语句在一个事务里多册查询结果相同，其他事务的数据变更不会影响查询结果
Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制
Multi-Version Concurrency Control：多版本并发控制机制
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志
trx_id：更新所处的事务id
roll_pointer：指向上一个版本的undo日志
两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链
示例图
undo（日志版本链）
一致性视图
在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图（read-view）
该视图在事务结束之前都不会变化
该视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果
在是读已提交隔离级别中，每次执行查询sql时都会重新生成
如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的
如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若row 的 trx_id 就是当前自己的事务是可见的）
若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；、
若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。
如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。
版本链比对规则
read-view（一致性视图）
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。
MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。
总结：
MVCC
示意图
Buffer Pool中是以Page为单位进行数据读取
如果事务提交失败要回滚数据，可以用undo日志里的数据恢复Buffer Pool里的缓存数据
binlogg主要用来恢复数据库磁盘里的数据
重点梳理
1.加载缓存数据，加载id为1的记录所在的整页数据到Buffer Pool缓存池
2.从Buffer Pool缓存池写入更新数据的旧值到undo日志文件（Innodb引擎特有），便于回滚
3.从执行器更新内存数据到Buffer Pool缓存池
4.从执行器写入redo日志到Redo Log Buffer
5.从Redo Log Buffer 准备提交事务，rodo日志写入磁盘，redo日志文件（Innodb引擎特有）记录
6.从执行器准备提交事务，binlog日志写入磁盘（binlog文件记录（属于Server层））
7.从binlog文件写入commit标记到redo日志文件里，提交事务完成，改标记为了保证事务提交后redo和binlog数据一致
8.修改的数据从IO线程随机写入磁盘，以page为单位写入，这步做完磁盘里的数据修改成功
执行步骤
因为磁盘文件的读写性能非常差，直接更新磁盘文件是不能让数据库抗住很高并发的，同时，如果没来一个请求就对磁盘文件进行随机读写，那么更新磁盘文件里的数据性能可能相当差
Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。
更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求。
思考问题：为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？
Innodb引擎SQL执行的BufferPool缓存机制
深入理解MVCC与BufferPool缓存机制
问题引入
将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中
采取方法：
以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式
概念：
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
行格式的创建和修改
如果该可变字段允许存储的最大字节数超过 255 字节并且真实存储的字节数超过 127 字节，则使用 2 个字节，否则使用 1 个字节
可变字段：
Compact 行格式把这些值为 NULL 的列统一管理起来，存储到 NULL 值列表，每个允许存储 NULL 的列对应一个二进制位，二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL。
NULL值：
预留位 1 1 没有使用
预留位 2 1 没有使用
delete_mask 1 标记该记录是否被删除
min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记录在页的位置信息
record_type 3 表示当前记录的类型，0 表示普通记录，1 表示 B+树非叶子节点记录，2 表示最小记录，3 表示最大记录
next_record 16 表示下一条记录的相对位置
记录的真实数据
DB_ROW_ID(row_id)：非必须，6 字节，表示行 ID，唯一标识一条记录（在没有自定义主键以及 Unique 键的情况下才会添加该列）
DB_TRX_ID：必须，6 字节，表示事务 ID
DB_ROLL_PTR：必须，7 字节，表示回滚指针
记录默认列（隐藏列）
记录头信息用于描述记录，有固定的五个字节组成，也就是40个二进制位
记录头信息
优先使用用户自定义主键作为主键
如果没有自定义主键，则选取一个Unique作为主键
如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为 row_id 的隐藏列作为主键
主键生成策略：
Compact
Redundant 行格式是 MySQL5.0 之前用的一种行格式
Redundant
MySQL5.7 的默认行格式就是 Dynami，类似于Compact 行格式
Dynamic
类似于Compact 行格式，处理溢出数据时，Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间
Compressed
分类：
MySQL 中磁盘和内存交互的基本单位是页，以页为基本单位来管理存储空间的
一个页的大小一般是 16KB，也就是 16384 字节，而一个 VARCHAR(M)类型的列就最多可以存储 65532 个字节，这样就可能造成一个页存放不了一条记录的情况
引入：
在 Compact 和 Redundant 行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的该列的前 768 个字节的数据，然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用 20 个字节存储指向这些页的地址。这个过程也叫做行溢出，存储超出 768 字节的那些页面也被称为溢出页
Dynamic 和 Compressed 行格式，不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。
解决方法：
数据溢出：
行格式
页的概念
Undo 日志页
段信息节点
Insert Buffer 空闲列表
Insert Buffer位图
系统页
事务系统数据
表空间头部信息
扩展描述页
溢出页
索引页
页的类型
索引列是存放我们表中记录的那种类型的页，又称数据页
因为聚簇索引这种索引和数据混合的东西
索引页概念
索引页示例图
对各种类型的页都通用
页结构的第一部分
比方说页的类型，这个页的编号是多少，它的上一个页、下一个页是谁，页的校验和等等，
描述了一些针对各种页都通用的一些信息
占用固定的38字节
File Header 文件头部 38 字节 页的一些通用信息
记录一个数据页中存储的记录的状态信息
页结构的第二部分
占用固定的 56 个字节，专门存储各种状态信息。
Page Header 数据页专有的一些信息
heap_no 值为 0 和 1 的记录是 InnoDB 自动给每个页增加的两个记录，称为伪记录或者虚拟记录
两个伪记录一个代表最小记录，一个代表最大记录，这两条存放在页的 User Records 部分，他们被单独放在一个称为 Infimum+ Supremum 的部分
记录头信息中 next_record 记录了从当前记录的真实数据到下一条记录的真实数据的地址偏移量，格式是链表格式
可以通过 next_record 查找下一条记录，记录的顺序根据主键值从小到大
Infimum记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录
主键值最大的用户记录的下一条记录就是 Supremum 记录（也就是最大记录）
Infimum+ Supremum 示例图
Infimum + Supremum 最小记录和最大记录 26 字节 两个虚拟的行记录
存储的记录会按照我们指定的行格式存储
当Free Space 部分的空间全部被 User Records部分替代掉之后，意味着该页已经用完了，新数据需要申请新的一天存储
当插入记录时，会从Free Space(空闲空间)里申请一部分空间划分到User Records
因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗
所有被删除的记录会组成垃圾链表，占用的空间即可重用空间，新数据会优先替换可重用空间
当数据删除时，修改记录头信息中的delete_mask为1，即打一个删除标识，并不是直接从磁盘移除数据
记录按照主键从小到大的顺序形成了一个单链表，记录被删除，则从这个链表上摘除。
同时插入的数据会记录自己在本页中的位置，写入记录头信息中heap_no部分
User Records 用户记录 大小不确定 实际存储的行记录内容
Free Space 空闲空间 大小不确定 页中尚未使用的空间
Page Directory 主要是解决记录链表的查找问题
如果按链表查找的办法：从 Infimum 记录（最小记录）开始，沿着链表一直往后找，总会找到或者找不到。但是时间复杂度不低
将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组
每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned 属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的 Page Directory，也就是页目录页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的。
每个分组中的记录条数是有规定的：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。如下图：
优化
一个数据页中查找指定主键值的记录的过程分为两步
通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录
通过记录的 next_record 属性遍历该槽所在的组中的各个记录。
Page Directory 页面目录 大小不确定 页中的某些记录的相对位置
前 4 个字节代表页的校验和
后 4 个字节代表页面被最后修改时对应的日志序列位置（LSN），这个也和校验页的完整性有关。
8 个字节组成，可以分成 2 个小部分
所有类型的页通用
File Trailer 文件尾部 8 字节 校验页是否完整
索引页的存储空间，共7部分
索引页格式
InnoDB 记录存储结构和索引页结构
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
MySQL 官方文档
官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html
InnoDB 的内存结构和磁盘存储结构在 MySQL8.0 有所变化
关键点：Buffer 和 Tabelspace（表空间）
表空间是一个抽象的概念
对于系统表空间来说，对应着文件系统中一个或多个实际文件，一般是（ibdata1）
对于每个独立表空间（也就是上图的File-Per-Table Tablespaces）来说，对应着文件系统中一个名为表名.ibd 的实际文件
任何类型的页都有专门的地方保存页属于哪个表空间
表空间中的每一个页都对应着一个页号，这个页号由 4 个字节组成，也就是 32 个比特位，所以一个表空间最多可以拥有 232 个页，如果按照页的默认大小 16KB 来算，一个表空间最多支持 64TB 的数据
概念
一个区默认占用 1MB 空间大小，也就是连续64页
256个区又被划分成一个组
每当插入数据时，实质就是向该表的聚簇索引以及所有二级索引代表的 B+树的节点中插入数据，而 B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。
如果根据链表遍历查询，如果表中相邻的两个页物理位置离得非常远，即随机I/O，随机I/O的查询效率很低
一个区就是在物理位置上连续的 64 个页。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据十分非常特别多的时候，可以一次性分配多个连续的区，从性能角度看，可以消除很多的随机 I/O。
引入区的主要目的是什么
区（extent）
extent 0 ~ extent 255 这 256个区，整个表空间只有一个 FSP_HDR
FSP_HDR 类型的页面还会额外存储一些表空间的属性
第一个组最开始的 3 个页面的类型是固定的，用来登记整个表空间的一些整体属性以及本组所有的区被称为 FSP_HDR
用来登记本组256 个区的属性
类似FSP_HDR 类型
其余各组最开始的 2 个页面的类型是固定的，称为 XDES 类型
组
一个索引会生成 2 个段，一个叶子节点段，一个非叶子节点段
段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念
段（segment）
独立表空间结构
系统表空间的结构和独立表空间基本类似
整个 MySQL 进程只有一个系统表空间
系统表空间中会额外记录一些有关整个系统信息的页面，当于是表空间之首
表空间 ID（Space ID）是 0
整体结构
InnoDB 的三大特性之一
它是一种特殊文件 flush 技术，带给 InnoDB 存储引擎的是数据页的可靠性
在把页写到数据文件之前，InnoDB 先把它们写到一个叫 doublewrite buffer（双写缓冲区）的连续区域内
在写 doublewrite buffer 完成后，InnoDB 才会把页写到数据文件的适当的位置
如果在写页的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在 doublewrite buffer 中找到完好的 page 副本用于恢复
作用：
缓冲区不仅在内存中有，多的是属于MySQL 的系统表空间，属于磁盘文件的一部分
思考：什么要引入一个双写机制呢
双写缓冲区/双写机制
系统表空间
InnoDB 的表空间
Buffer Pool
自适应 Hash 索引
InnoDB 的三大特性
Insert语句插入的用户数据会产生额外的数据，称为元数据
InnoDB 存储引擎特意定义了一些列的内部系统表（internal system table）来记录这些这些元数据
SYS_TABLES 整个 InnoDB 存储引擎中所有的表的信息
SYS_COLUMNS 整个 InnoDB 存储引擎中所有的列的信息
SYS_INDEXES 整个 InnoDB 存储引擎中所有的索引的信息
SYS_FIELDS 整个 InnoDB 存储引擎中所有的索引对应的列的信息
SYS_FOREIGN 整个 InnoDB 存储引擎中所有的外键的信息
SYS_FOREIGN_COLS 整个 InnoDB 存储引擎中所有的外键对应列的信息
SYS_TABLESPACES整个 InnoDB 存储引擎中所有的表空间信息
SYS_DATAFILES 整个 InnoDB 存储引擎中所有的表空间对应文件系统的文件路径信息
SYS_VIRTUAL 整个 InnoDB 存储引擎中所有的虚拟生成列的信息
表名：
表的记录类型：B+树形式
中 SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、SYS_FIELDS 这四个表尤其重要，称之为基本系统表
InnoDB 数据字典(Data Dictionary Header)
InnoDB 的体系结构
InnoDB 引擎底层存储
数据说到底还是存储在磁盘上的，但是磁盘的速度慢
当InnoDB 存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中
整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘 IO 的开销了。
缓存的重要性
InnoDB 为了缓存磁盘中的页，在 MySQL 服务器启动的时候就向操作系统申请了一片连续的内存
大小默认128M
查看大小：SQL: show variables like 'innodb_buffer_pool_size';
配置缓存池空间：innodb_buffer_pool_size = 268435456
更大的缓冲池只需更少的磁盘 I/O 来多次访问相同的表数据。在专用数据库服务器上，您可以将缓冲池大小设置为机器物理内存大小的 80%。配置缓冲池大小时请注意以下潜在问题，并准备在必要时缩减缓冲池的大小
InnoDB 为缓冲区和控制结构保留了额外的内存，因此分配的总空间比指定缓冲池大小大约大 10%。
缓存池空间
每个查询至少需要几 K 的内存(有时候是几 M)
有各种其它内部的 MySQL 结构和缓存
InnoDB 有一些结构是不用缓冲池的内存的(字典缓存，文件系统等)
此外也必须为操作系统留出些内存
分配缓存空间需考虑
没有专人管理和实时监控的情况下，可以设置为 60%较为稳妥
有专人管理和实时监控的情况下，可以设置为 75%
并根据业务情况适度增大或者缩小。
Buffer Pool 分配的空间建议
默认缓存也大小 16KB
控制块：每个缓存页对应的控制信息占用的内存大小是相同的，称为控制块
控制块和缓存页是一一对应的，都被存放到 Buffer Pool 中
控制块被存放到 Buffer Pool 的前边
缓存页被存放到 Buffer Pool 后边
每个控制块大约占用缓存页大小的 5%
设置的 innodb_buffer_pool_size并不包含这部分控制块占用的内存空间大小
这片连续的内存空间一般会比innodb_buffer_pool_size 的值大 5%左右
重点说明
Buffer Pool 内部组成
把所有空闲的缓存页对应的控制块作为一个节点放到一个链表，及free链表
free 链表的管理
查找缓存页是根据表空间号 + 页号来定位一个页的，也就相当于表空间号 +页号是一个 key，缓存页就是对应的 value，通过一个 key 来快速找着一个value 呢
所以我们可以用表空间号 + 页号作为 key，缓存页作为 value 创建一个哈希表
缓存页的哈希处理
flush 链表的管理
解决缓存不够的情况
3/8的list信息是作为old list，这些信息是被驱逐的对象。
list的中点就是我们所谓的old list头部和new list尾部的连接点，相当于一个界限
新数据的读入首先会插入到old list的头部，
如果是old list的数据被访问到了，这个页信息就会变成new list，变成young page，就会将数据页信息移动到new sublist的头部。
在数据库的buffer pool里面，不管是new sublist还是old sublist的数据如果不会被访问到，最后都会被移动到list的尾部作为牺牲者
主要使用了LRU算法
BufferPool缓存淘汰机制是怎样的
LRU 链表的管理
Buffer Pool（缓存池）
InnoDB 的缓存原理（ Buffer Pool）
InnoDB引擎底层原理
一个事务中的操作要么全部成功，要么全部失败
通过 undo log 来实现的
原子性
数据库总是从一个一致性的状态转换到另外一个一致性的状态
通过读写锁+MVCC 来实现
一致性：
一个事务的修改在最终提交前，对其他事务是不可见的
隔离性：
一旦事务提交，所做的修改就会永久保存到数据库中
通过 redo log 来实现的
持久性：
事务的 ACID 特性
所有的修改都先被写入到日志中，然后再被应用到系统中
每当有操作时，在数据变更之前将操作写入 redo log
这样当发生掉电之类的情况时系统可以在重启后继续操作
redo log 来在系统 Crash 重启之类的情况时修复数据（事务的持久性）
redo log（重做日志）
当一些变更执行到一半无法完成时，可以根据撤销日志恢复到变更之间的状态
undo log 来保证事务的原子性
undo log （撤销日志）
信息包括 redo 和 undo 两部分信息
MySQL 采用的是 WAL（Write-ahead logging，预写式日志）机制来实现的
WAL（Write-ahead logging，预写式日志）
只有在日志记录全部都安全落盘，数据库在日志中看到代表事务成功提交的“提交记录”（Commit Record）后，
才会根据日志上的信息对真正的数据进行修改，修改完成后，再在日志中加入一条“结束记录”（End Record）表示事务已完成持久化
Commit Logging（提交日志）
轻量级数据库 SQLite Version 3 采用的事务机制就是 Shadow Paging
对数据的变动会写到硬盘的数据中
当事务成功提交，所有数据的修改都成功持久化之后，最后一步是去修改数据的引用指针，将引用从原数据改为新复制出来修改后的副本，最后的“修改指针”这个操作将被认为是原子操作
执行过程
Shadow Paging也可以保证原子性和持久性
Shadow Paging实现事务要比 Commit Logging更加简单
但涉及隔离性与并发锁时，ShadowPaging实现的事务并发能力就相对有限，所以应用不多
特点
Shadow Paging（影子分页）
WAL中有undo日志，而Commit Logging没有
阿里的 OceanBase则是使用的 Commit Logging来实现事务。
WAL和Logging区别
事务的实现机制
引入
记录了一下事务对数据库做了哪些修改
本质：
type：该条 redo 日志的类型，redo 日志设计大约有 53 种不同的类型日志:
space ID：表空间 ID。
page number：页号。
data：该条 redo 日志的具体内容
详解
别称：物理日志
当InnoDB没有为某个表定义主键和Unique键时，InnoDB会自动的为表添加row_id作为主键，那么如何赋值呢
服务器会在内存中维护一个全局变量，每当向某个包含隐藏的 row_id 列的表中插入一条记录时，就会把该变量的值当作新记录的 row_id 列的值，并且把该变量自增 1
每当这个变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间的页号为 7 的页面中一个称之为 Max Row ID 的属性处
系统启动时，会将上边提到的 Max Row ID 属性加载到内存中，将该值加上256 之后赋值给我们前边提到的全局变量
赋值写入实际上是在 Buffer Pool 中完成的，我们需要为这个页面的修改记录一条 redo 日志，以便在系统崩溃后能将已经提交的该事务对该页面所做的修改恢复出来
redo 日志中只需要记录一下在某个页面的某个偏移量处修改了几个字节的值以及具体的修改内容
划分方式：根据在页面中写入数据的多少
offset 代表在页面中的偏移量
表示在页面的某个偏移量处写入 1 个字节的 redo 日志类型。
MLOG_1BYTE（type 字段对应的十进制数字为 1）
表示在页面的某个偏移量处写入 2 个字节的 redo 日志类型。
MLOG_2BYTE（type 字段对应的十进制数字为 2）
表示在页面的某个偏移量处写入 4 个字节的 redo 日志类型
MLOG_4BYTE（type 字段对应的十进制数字为 4）
表示在页面的某个偏移量处写入 8 个字节的 redo 日志类型
MLOG_8BYTE（type 字段对应的十进制数字为 8）
表示在页面的某个偏移量处写入一串数据
MLOG_WRITE_STRING（type 字段对应的十进制数字为 30）
日志类型：
redo 简单日志格式
redo 复杂日志格式
redo 日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来
redo 日志 格式
redo log block edo 日志都放在了大小为 512 字节的块（block）中
redo log block （日志块）：
redo log buffer 的连续内存空间
redo 日志缓冲区 分为若干连续的 redo log block （日志块）
启动参数 innodb_log_buffer_size 来指定 log buffer的大小，该启动参数的默认值为 16MB
向 log buffer 中写入 redo 日志的过程是顺序的
redo 日志缓冲区
redo log block 和日志缓冲区
空间不足时：当log buffer 空间不足时，InnoDB 认为如果当前写入 log buffer 的 redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上
事务提交时：为了保证持久性，必须要把修改这些页面对应的 redo 日志刷新到磁盘
后台有一个线程，大约每秒都会刷新一次 log buffer 中的 redo 日志到磁盘
正常关闭服务器时等
日志从内存刷新到磁盘的时机
redo 日志刷盘时机
MySQL 的数据目录下的默认两个文件，即 ib_logfile0 和 ib_logfile1 的文件
innodb_log_group_home_dir，该参数指定了 redo 日志文件所在的目录，默认值就是当前的数据目录
innodb_log_file_size，该参数指定了每个 redo 日志文件的大小，默认值为 48MB
innodb_log_files_in_group，该参数指定 redo 日志文件的个数，默认值为 2，最大值为 100。
调节配置方法
redo 日志文件组
redo 日志文件其实也是由若干个 512 字节大小的block 组成
前 2048 个字节，也就是前 4 个 block 是用来存储一些管理信息的
从第 2048 字节往后是用来存储 log buffer 中的 block 镜像
redo 日志文件组中的每个文件大小都一样，格式也一样，由两部分组成
组成：
redo 日志文件格式
redo 日志的写入过程
日志序列号，简称 LSN
用于记录已经写入的 redo 日志量的全局变量
初始的 lsn值为 8704（也就是一条 redo 日志也没写入时，LSN 的值为 8704）。
redo 日志都有一个唯一的 LSN 值与其对应，LSN 值越小，说明 redo 日志产生的越早
相关概念
LSN
表示刷新到磁盘中的 redo日志量的全局变量
初始值8704
随着系统的运行，redo 日志被不断写入log buffer，但是并不会立即刷新到磁盘，lsn 的值就和 flushed_to_disk_lsn 的值拉开了差距
只有当系统执行了 fsync 函数后，flushed_to_disk_lsn的值才会跟着增长
当仅仅把 log buffer 中的日志写入到操作系统缓冲区却没有显式的刷新到磁盘时，另外的一个称之为 write_lsn 的值跟着增长
flushed_to_disk_lsn
Log sequence number：代表系统中的 lsn 值，也就是当前系统已经写入的 redo日志量，包括写入 log buffer 中的日志。
Log flushed up to：代表 flushed_to_disk_lsn 的值，也就是当前系统已经写入磁盘的 redo 日志量。
Pages flushed up to：代表 flush 链表中被最早修改的那个页面对应的oldest_modification 属性值。
Last checkpoint at：当前系统的 checkpoint_lsn 值。
相关参数
查看系统的LSN：SHOW ENGINE INNODB STATUS
应用场景：对事务的持久性要求不是那么强烈的话，可以选择修改一个称为innodb_flush_log_at_trx_commit 的系统变量的值
这样很明显会加快请求处理速度，但是如果事务提交后服务器挂了，后台线程没有及时将 redo 日志刷新到磁盘，那么该事务对页面的修改会丢失
0：当该系统变量值为0时，表示在事务提交时不立即向磁盘中同步redo日志，这个任务是交给后台线程做的
1：当该系统变量值为 1 时，表示在事务提交时需要将 redo 日志同步到磁盘，可以保证事务的持久性。1 也是 innodb_flush_log_at_trx_commit 的默认值
这种情况下如果数据库挂了，操作系统没挂的话，事务的持久性还是可以保证的，但是操作系统也挂了的话，那就不能保证持久性了。
2：当该系统变量值为 2 时，表示在事务提交时需要将 redo 日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘
innodb_flush_log_at_trx_commit 的用法
Log Sequence Number（日志序列号）
redo 日志
为了回滚而记录的这些东西称之为撤销日志，英文名为 undo log/undo 日志
一个事务可以是一个只读事务，或者是一个读写事务：
只读事务中不可以对普通的表（其他事务也能访问到的表）进行增、删、改操作，但可以对用户临时表做增、删、改操作
以通过 START TRANSACTION READ ONLY 语句开启一个只读事务
可以通过 START TRANSACTION READ WRITE 语句开启一个读写事务
在读写事务中可以对表执行增删改查操作
使用 BEGIN、START TRANSACTION 语句开启的事务默认也算是读写事务
给事务分配 id 的时机
对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话是不分配事务 id 的
对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话也是不分配事务id 的
事务id的分配方式
这个事务 id 本质上就是一个数字，分配策略和隐藏列row_id大致相同
服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务 id时，就会把该变量的值当作事务 id 分配给该事务，并且把该变量自增 1
每当这个变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间的页号为 5 的页面中一个称之为 Max Trx ID 的属性处，这个属性占用 8 个字节的存储空间。
当系统下一次重新启动时，会将上边提到的 Max Trx ID 属性加载到内存中，将该值加上 256 之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于 Max Trx ID 属性值）。
分配的事务 id 值是一个递增的数字
事务id生成机制
trx_id 列就是某个对这个聚簇索引记录做改动的语句所在的事务对应的事务 id 而已
trx_id 隐藏列
事务id
undo 日志从0开始，称之为 undo NO。
FIL_PAGE_UNDO_LOG 类型的页面是专门用来存储 undo 日志
FIL_PAGE_UNDO_LOG 页面可以从系统表空间中分配，也可以从一种专门存放undo 日志的表空间，也就是所谓的 undo tablespace 中分配
类型为 TRX_UNDO_INSERT_REC 的 undo 日志
INSERT 操作对应的 undo 日志
删除会产生一种称之为TRX_UNDO_DEL_MARK_REC 类型的 undo 日志
正常记录链表中包含了 3 条正常记录，垃圾链表里包含了 2 条已删除记录
页面的 PageHeader 部分的 PAGE_FREE 属性的值代表指向垃圾链表头节点的指针
初始示例图
阶段一示例图
阶段一：将记录的 delete_mask 标识位设置为 1，这个阶段称之为 delete mark。
阶段二示例图
阶段二：当该删除语句所在的事务提交之后，会有专门的线程后来真正的把记录删除掉
当删除最后一条记录时，要经历两个阶段
在删除语句所在的事务提交之前，只会经历阶段一，也就是 delete mark 阶段（提交之后我们就不用回滚了，所以只需考虑对删除操作的阶段一做的影响进行回滚）
删除过程
DELETE 操作对应的 undo 日志
在执行 UPDATE 语句时，InnoDB 对更新主键和不更新主键这两种情况有截然不同的处理方案。
细分更新的列占用的存储空间不发生变化和发生变化的情况
就地更新（in-place update）
先删除掉旧记录，再插入新记录
不更新主键的情况
将旧记录进行 delete mark 操作
创建一条新记录
更新主键的情况
UPDATE 操作对应的 undo 日志
类型
undo 日志的格式
undo 日志
事务主要主要是通过 Redo Log 和 Undo Log 实现
事务执行流程
如果 MySQL 由于某种原因崩溃或者宕机，当然进行数据的恢复或者回滚操作
MySQL 崩溃恢复后，首先会获取日志检查点信息，随后根据日志检查点信息使用 Redo Log 进行恢复
如果事务未提交，来使用 Undo Log 回滚数据
如果事务已提交，，则用Redo Log 恢复数据
恢复方式
MySQL 可以根据 redo 日志中的各种 LSN 值，来确定恢复的起点和终点
将 redo 日志中的数据，以哈希表的形式，将一个页面下的放到哈希表的一个槽中
遍历哈希表，因为对同一个页面进行修改的 redo 日志都放在了一个槽里，所以可以一次性将一个页面修复好（避免了很多读取页面的随机 IO）
通过各种机制，避免无谓的页面修复
恢复机制
这两者使用方式不一样
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”，恢复的速度更快；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2这的 c 字段加 1 ”
redo log 是“循环写”的日志文件，redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。
最重要的是，当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)，哪些数据还没有
思考：崩溃后的恢复为什么不用 binlog？
事务恢复流程
当事务提交时 InnoDB 存储引擎进行 prepare 操作。
MySQL 上层会将数据库、数据表和数据表中的数据的更新操作写入 BinLog文件
InnoDB 存储引擎将事务日志写入 Redo Log 文件中。
同时写 Redo 和 Binlog 怎么保持一致？
事务的流程
InnoDB 引擎底层事务的原理
在 MySQL8.0 用户的创建与授权需要两条单独的 SQL 语句执行
用户的创建与授权
password_history 修改密码不允许与最近几次使用或的密码重复，默认是0，即不限制；
MySQL8.0 的密码管理策略提供了 3 个变量
可针对某一个用户单独设置密码管理策略
密码管理
认证插件更新；角色管理也有调整
其他
隐藏索引，被隐藏的索引不会被优化器使用，但依然真实存在，主要用于软删除，可以根据需要后续真正删除或者重新可视化
开始真正支持降序索引
不再对 group by 操作进行隐式排序
索引
账户与安全
原子 ddl 操作
每次在变化的时候，都会将自增计数器的最大值写入 redo log，同时在每次检查点将其写入引擎私有的系统表。则不会出现自增主键重复的问题。
自增列持久化
改进 SQL 分析器。旧的分析器由于其语法复杂性和自顶向下的分析方式从而有严重的限制，导致难以维护和扩展。
重构 SQL 分析器
新的系统字典表
支持资源管理
系统方面
JSON_OBJECTAGG()，用于生成 json 对象
聚合函数
JSON_PRETTY() 输出 json 数据的时候，格式化
JSON_STORAGE_SIZE() json 数据所占用的存储空间(单位：字节)
JSON_STORAGE_FREE() json 数据更新后所释放的空间(单位：字节)
JSON 实用函数
MySQL8.0 废弃了 JSON_MERGE()函数
JSON_MERGE_PATCH()
JSON_MERGE_PRESERV()
合并 JSON 数据
JSON 合并函数
了 JSON_TABLE()函数，将 JSON 数据转换成关系表
JSON 表函数
更好支持文档型数据库和 JSON
正则表达式增强
新增备份锁
默认字符集由 latin1 变为 utf8mb4
默认字符集
全局参数持久化
支持会话级别动态调整部分参数
默认参数的调整
配置参数
innodb 增强
并发
行缓存
改进扫描性能
改进成本模型
InnoDB 性能提升
InnoDB
移除了一些功能，例如 query cache
废弃特性
Mysql 8.0新增特性详解
数据在磁盘中的位置肯定是随机的
磁盘IO效率低，减少I/O的次数，加快查询速率
索引是帮助MySQL高效获取数据的排好序的数据结构
提高数据检索的效率，降低数据库的IO成本，最主要的原因
创建唯一索引，保证数据库表中每一行数据的唯一性
加速表和表之间的连接
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。
索引的优点：
创建索引和维护索引要耗费时间
索引需要占磁盘空间，大量的索引下，索引文件就可能比数据文件更快达到最大文件尺寸
虽然索引大大提高了查询速度，但是同时也会降低更新表的速度
索引的缺点
索引是什么
二叉树的右子节点比父节点大，左子节点比父节点小。通过这一特点进行查询时，通过比对可以快速定位节点位置。
缺点：数据形成的是满二叉树或者完全二叉树的时候去查找才能很好的发挥索引的优势。在极端情况下，例如形成的是一颗线型的二叉树，其实跟没有构建索引是一样的。
二叉树
二叉平衡
红黑树又称二叉平衡树，在新增节点时可以自动调整节点的位置。
缺点：当数据的数据量大时，树的高度会变得很高，而且树的高度不可控。如果查询的数据恰好在叶子节点，而树的查询都是从父节点开始的，执行的IO次数也会很多。
红黑树
对索引的key进行依次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+ 树索引更高效
仅能满足 “=”，“IN”，不支持范围查询
hash冲突问题
构建一个HASH桶，将计算好HASH的元素以及地址放到桶的指定位置，再有相同的则追加在元素后面。查询则先计算好在桶的哪个位置，再遍历链表。在某种情况下要比B+树更高效。
Hash表
叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列
B树的结构是在一个节点里面放很多的小节点元素，通过节点的横向扩展来解决树的高度问题。小节点元素是从左到右递增排列的。
特点：
B-Tree（B树）
非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
叶子节点包含所有索引字段
叶子节点用指针连接，提高区间访问的性能
从左到右依次递增，自动排序
叶子节点：最后一层，其他层为非叶子节点
层数最多3层
特点总结
B+Tree(B-Tree 变种)（B+树）
索引的数据结构
数据结构网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
千万级数据表如何用B+数索引快速查找
索引文件和数据文件是分离的(非聚集索引/稀疏索引)
.frm(数据表结构)、.MYD(数据)、.MYI(索引)
索引文件按照B+Tree组织，叶节点存储数据行的地址，按照地址查找数据
MyISAM存储引擎索引实现
MyISAM
表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据记录
InnoDB索引实现(聚集索引)
节省资源，加快查询过程
为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
聚集索引效率更高
聚集索引和非聚集索引比较
聚集索引&聚族索引&稀疏索引到底是什么
自增效率更高
格式 B+树，依次排序
保持索引一致性
节约存储空间
为什么DBA总推荐使用自增主键做索引
多个字段共同组成一个索引
B+树结构
最左前缀优化原则，依次执行，不能跳过
联合索引底层数据结构又是怎样的
按索引键先后顺序建立，自左而右
索引按B+树结构按顺序排序
Mysql最左前缀优化原则是怎么回事（左链原理）
深入理解Mysql索引底层数据结构与算法
参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
Mysql安装文档参考：https://blog.csdn.net/yougoule/article/details/56680952
SQL语句：explain select * from actor;
结果：
常例
说明：：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。
SQL语句：explain extended select * from film where id = 1;
查询结果：
explain extended：
说明：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。
explain partitions：
变种
id列的编号是 select 的序列号，有几个 select 就有几个id，
id的顺序是按 select 出现的顺序增长的。
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
id列
simple：简单查询。查询不包含子查询和union
primary：复杂查询中最外层的 select
subquery：包含在 select 中的子查询（不在 from 子句中）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
select_type 表示对应行是简单还是复杂的查询
select_type列
这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
table列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref
mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
SQL:explain select min(id) from film;
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
SQL: explain select * from film where name = 'film1';
简单 select 查询，name是普通索引（非唯一索引）
SQL:explain select film_id from film left join film_actor on film.id = film_actor.film_id;
关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
SQL:explain select * from actor where id > 1;
range：
扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
SQL:explain select * from film;
index：
即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
SQL: explain select * from actor;
ALL：
参数
type列
这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果
possible_keys列
这一列显示mysql实际采用哪个索引来优化对该表的访问
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 forceindex、ignore index
key列
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。
SQL:explain select * from film_actor where film_id = 2;
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
字符串：
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
数值类型
date：3字节
timestamp：4字节
datetime：8字节
时间类型
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
计算规则：
key_len列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
ref列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
rows列
覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
SQL: explain select film_id from film_actor where film_id = 1;
Using index：使用覆盖索引
使用 where 语句来处理结果，并且查询的列未被索引覆盖
SQL: explain select * from actor where name = 'a';
Using where：
查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
SQL: explain select * from film_actor where film_id > 1;
Using index condition：
mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
SQL: explain select distinct name from actor;
1. actor.name没有索引，此时创建了张临时表来distinct
SQL: explain select distinct name from film;
Using temporary：
将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的
SQL:explain select * from actor order by name;
1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录
Using filesort：
使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是
SQL: explain select min(id) from film;
Select tables optimized away：
这一列展示的是额外信息。常见的重要值如下：
Extra列
表格列说明：
1. Expain使用和详解
示例表
1.全值匹配
2.最左前缀法则
给hire_time增加一个普通索引：
转化为日期范围查询，有可能会走索引：
还原最初索引状态
3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句
6. mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描；< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
1. 使用覆盖索引，查询字段必须是建立覆盖索引字段
2. 如果不能使用覆盖索引则可能需要借助搜索引擎
问题：解决like'%字符串%'索引不被使用的方法？
8. like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
9.字符串不加单引号索引失效
10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化
1.给年龄添加单值索引
mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
可能是由于单次数据量查询过大导致优化器最终选择不走索引
2.没走索引原因
可以将大的范围拆分成多个小范围‘
3.优化方法
4.还原最初索引状态
11. 范围查询优化
实践类型
索引使用总结：
2. MySQL索引最佳实践
Explain详解与索引最佳实践
图例
管理连接和校验权限
连接器
查询缓存
词法分析，语法分析
分析器
执行计划并生成索引选择
优化器
调用引擎接口获取查询结果
执行器
Server 层
读写磁盘，结构数据化存储的实现
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。
默认引擎：InnoDB（MySQL 5.5.5 版本之后）
存储引擎层（Store层）
1、Binlog在MySQL的Server层实现（引擎共用）
binlog里的内容不具备可读性，所以需要我们自己去判断恢复的逻辑点位
show variables like '%log_bin%'; 查看bin-log是否开启
flush logs; 会多一个最新的bin-log日志
show master status; 查看最后一个bin-log日志的相关信息
reset master; 清空所有的bin-log日志
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 查看binlog内容
要使用binlog归档，首先我们得记录binlog，因此需要先开启MySQL的binlog功能。
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库名)
恢复全部数据：
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position=\"408\" --stop-position=\"731\" /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库)
恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= \"2018-03-02 12:00:00\" --start-date= \"2019-03-02 11:55:00\"|mysql -uroot -p test(数据库)
恢复指定时间段数据
归档操作
bin-log归档
层级：
MySQL的内部组件结构
你会先连接到这个数据库上，这时候接待你的就是连接器
如果用户名或密码不对，你就会收到一个\"Access denied for user\"的错误，然后客户端程序结束执行。
如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
完成经典的 TCP 握手后，连接器就要开始认证你的身份，需要输入的用户名和密码。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。
客户端如果长时间不发送command到Server端，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。
长链接：接成功后，如果客户端持续有请求，则一直使用同一个连接（大多数情况）
短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
问题：MySQL 异常重启
定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
问题：如何解决
链接方式：
1. 你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。（连接器）
mysql8.0已经移除了查询缓存功能
如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。
查询缓存的失效非常频繁，表的更新会导致表的查询缓存失效
对于更新压力大的数据库来说，查询缓存的命中率会非常低
故建议静态表里使用查询缓存
问题：大多数情况查询缓存就是个鸡肋，为什么呢？
将my.cnf参数 query_cache_type 设置成 DEMAND，默认的 SQL 语句都不使用查询缓存
确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定
2. 连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。（查询缓存）
MySQL 需要识别出SQL里面的字符串分别是什么，代表什么。
例子：MySQL 从你输入的\"select\"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
1.词法分析
根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。
报错语句：You have an error in your SQL syntax
2.语法分析
3、语义分析
4、构造执行树
5、生成执行计划
6、计划的执行
3. 如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。（分析器）
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
4.经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。（优化器）
如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
如果ID 字段没有索引，执行流程
第一次调用的是“取满足条件的第一行”这个接口
循环取“满足条件的下一行”这个接口
如果有索引
数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行；数值表示执行器每次调用引擎获取数据行的时候累加的
存在场景：执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。
5.开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限（执行器）
MySQl执行原理和顺序
SQL底层执行原理详解
1. 索引下推优化详解
2. MySQL优化器索引选择研究
3. 索引优化Order by 与Group by
4. Using filesort文件排序详解
5. 索引设计原则与实战
课程链接：http://note.youdao.com/noteshare?id=d2e8a0ae8c9dc2a45c799b771a5899f6&sub=C1831C9ABBE84A29829DE1891B06EF5A
课程提纲
联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描
联合索引第一个字段用范围不会走索引
虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高
强制走索引
覆盖索引优化
in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
like KK% 一般情况都会走索引（索引下推）
索引案例
现象：
在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。
MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。
使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。
索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果
解决方式：
估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 likeKK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。
问题：为什么范围查找Mysql没有用索引下推优化？
索引下推：
如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果
1. > EXPLAIN select * from employees where name > 'a';
可以用trace工具 确定mysql最终是否选择走索引或者一张表涉及多个索引
trace工具
案例：
Mysql如何选择合适的索引
MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低
order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列
order by满足两种情况会使用Using index。
尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
如果order by的条件不在索引列上，就会产生Using filesort。
能用覆盖索引尽量用覆盖索引
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于groupby的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了
常见sql深入优化（Order by与Group by优化）
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
如果 字段的总长度小于max_length_for_sort_data (默认1024字节)，那么使用 单路排序模式；
如果 字段的总长度大于max_length_for_sort_data (默认1024字节)，那么使用 双路排序模∙式。
如何选择
从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
重复步骤 2、3 直到不满足 name = ‘zhuge’
对 sort_buffer 中的数据按照字段 position 进行排序
返回结果给客户端
单路排序
从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
重复 3、4 直到不满足 name = ‘zhuge’
对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端
双路排序
单路排序会把所有需要查询的字段都放到 sort buffer 中，
而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段
排序对比
排序详细过程
filesort文件排序方式
Using filesort文件排序原理详解
代码先行，索引后上
联合索引尽量覆盖条件
不要在小基数字段上建立索引
长字符串我们可以采用前缀索引
where与order by冲突时优先where
基于慢sql查询做优化
索引设计原则
课程内容
MyQL索引优化实战一
为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制
概述
原子性(Atomicity)
一致性(Consistent)
隔离性(Isolation)
持久性(Durable)
事务的ACID属性
事务及其ACID属性
最后的更新覆盖了由其他事务所做的更新。
更新丢失(Lost Update)或脏写
脏读（Dirty Reads）
事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
不可重读（Non-Repeatable Reads）
事务A读取到了事务B提交的新增数据，不符合隔离性
幻读（Phantom Reads）
并发事务处理带来的问题
级别详解
开启：set tx_isolation='read-uncommitted'
读未提交（read-uncommitted）
开启：set tx_isolation='read-committed
读已提交（read-committed）
开启：set tx_isolation='repeatable-read';
可重复读（repeatable-read）
开启：set tx_isolation='serializable';
串行化（serializable）
事务隔离级别分析
“脏读”、“不可重复读”和“幻读”
解决问题
查看当前数据库的事务隔离级别: show variables like 'tx_isolation';
设置事务隔离级别：set tx_isolation='REPEATABLE-READ';
事务常见命令
Mysql默认的事务隔离级别是可重复读
用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别
事务隔离级别
锁是计算机协调多个进程或线程并发访问某一资源的机制
从性能上分为乐观锁(用版本对比来实现)和悲观锁
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
从对数据操作的粒度分，分为表锁和行锁
分类
每次操作锁住整张表。开销小，加锁快；不会出现死锁
锁定粒度大，发生锁冲突的概率最高，并发度最低
应用在整表数据迁移的场景
手动增加表锁
show open tables;
查看表上加过的锁
unlock tables;
删除表锁
操作：
表锁
开启加读锁
当前session和其他session都可以读该表
当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
加读锁
开启加写锁
当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞
加些锁
结论
加读锁和加写锁
每次操作锁住一行数据。开销大，加锁慢；会出现死锁
锁定粒度最小，发生锁冲突的概率最低，并发度最高
InnoDB支持事务（TRANSACTION）
InnoDB支持行级锁
InnoDB与MYISAM的最大不同有两点
一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞
行锁演示
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。
无索引行锁会升级为表锁
行锁
间隙锁，锁的就是两个值之间的空隙
在某些情况下可以解决幻读问题。
间隙锁是在可重复读隔离级别下才会生效。
间隙锁(Gap Lock)
Next-Key Locks是行锁与间隙锁的组合
临键锁(Next-key Locks)
锁的详解级分析
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
结论：
锁详解
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
锁优化建议
深入理解Mysql事务隔离级别与锁机制
MYSQL
0 条评论
回复 删除
下一页
