【MYSQL篇}底层原理全面解析和性能优化方法

trx_id：更新所处的事务id

roll_pointer：指向上一个版本的undo日志

如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的

如果 row 的 trx_id 落在红色部分( trx_id>max_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，意味着记录已被删除，则不返回数据。

InnoDB 是一个将表中的数据存储到磁盘上的存储引擎,而真正处理数据的过程是发生在内存中,写入修改则需要把内存中的内容刷新到磁盘上，但是磁盘效率比内存要低很多，怎么处理

将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中

概念：

行格式的创建和修改

分类：

数据溢出：

页的概念

页的类型

索引页概念

索引页示例图

索引页的存储空间，共7部分

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

示例图

 InnoDB 的内存结构和磁盘存储结构在 MySQL8.0 有所变化

概念

独立表空间结构

系统表空间

双写缓冲区/双写机制

Buffer Pool

自适应 Hash 索引

Insert语句插入的用户数据会产生额外的数据，称为元数据

InnoDB 存储引擎特意定义了一些列的内部系统表（internal system table）来记录这些这些元数据

表名：

表的记录类型：B+树形式

中 SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、SYS_FIELDS 这四个表尤其重要，称之为基本系统表

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 有一些结构是不用缓冲池的内存的(字典缓存，文件系统等)

也有一些 MySQL 文件是在 OS 缓存里的(binary 日志，relay 日志,innodb 事务日志等)

此外也必须为操作系统留出些内存

没有专人管理和实时监控的情况下，可以设置为 60%较为稳妥

有专人管理和实时监控的情况下，可以设置为 75%

并根据业务情况适度增大或者缩小。

示例图

重点说明

示例图

把所有空闲的缓存页对应的控制块作为一个节点放到一个链表，及free链表

查找缓存页是根据表空间号 + 页号来定位一个页的，也就相当于表空间号 +页号是一个 key，缓存页就是对应的 value，通过一个 key 来快速找着一个value 呢

所以我们可以用表空间号 + 页号作为 key，缓存页作为 value 创建一个哈希表

示例图

解决缓存不够的情况

BufferPool缓存淘汰机制是怎样的

所有的修改都先被写入到日志中，然后再被应用到系统中

信息包括  redo 和 undo 两部分信息

对数据的变动会写到硬盘的数据中

但是并不是直接修改原先的数据，二是现将数据拷贝一份副本，保留原数据,修改副本数据

在事务过程中，被修改的数据会同时存在两份，一份是修改前的数据，一份是修改后的数据,即影子的由来

当事务成功提交，所有数据的修改都成功持久化之后，最后一步是去修改数据的引用指针，将引用从原数据改为新复制出来修改后的副本，最后的“修改指针”这个操作将被认为是原子操作

Shadow Paging也可以保证原子性和持久性

Shadow Paging实现事务要比 Commit Logging更加简单

但涉及隔离性与并发锁时，ShadowPaging实现的事务并发能力就相对有限，所以应用不多

type：该条 redo 日志的类型，redo 日志设计大约有 53 种不同的类型日志:

space ID：表空间 ID。

page number：页号。

data：该条 redo 日志的具体内容

示例图

别称：物理日志

重点梳理

日志类型：

示例图

redo 日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来

 redo log block （日志块）：

 redo 日志缓冲区

日志从内存刷新到磁盘的时机

概念

调节配置方法

组成：

相关概念

表示刷新到磁盘中的 redo日志量的全局变量

初始值8704

随着系统的运行，redo 日志被不断写入log buffer，但是并不会立即刷新到磁盘，lsn 的值就和 flushed_to_disk_lsn 的值拉开了差距

当LSN==flushed_to_disk_lsn,说明log buffer 中的redo日志窦英杰写入到磁盘

只有当系统执行了 fsync 函数后，flushed_to_disk_lsn的值才会跟着增长

当仅仅把 log buffer 中的日志写入到操作系统缓冲区却没有显式的刷新到磁盘时，另外的一个称之为 write_lsn 的值跟着增长

相关参数

应用场景：对事务的持久性要求不是那么强烈的话，可以选择修改一个称为innodb_flush_log_at_trx_commit 的系统变量的值

相关参数

一个事务可以是一个只读事务，或者是一个读写事务：

以通过 START TRANSACTION READ ONLY 语句开启一个只读事务

可以通过 START TRANSACTION READ WRITE 语句开启一个读写事务

使用 BEGIN、START TRANSACTION 语句开启的事务默认也算是读写事务

对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话是不分配事务 id 的

对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话也是不分配事务id 的

这个事务 id 本质上就是一个数字，分配策略和隐藏列row_id大致相同

服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务 id时，就会把该变量的值当作事务 id 分配给该事务，并且把该变量自增 1

每当这个变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间的页号为 5 的页面中一个称之为 Max Trx ID 的属性处，这个属性占用 8 个字节的存储空间。

当系统下一次重新启动时，会将上边提到的 Max Trx ID 属性加载到内存中，将该值加上 256 之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于 Max Trx ID 属性值）。

分配的事务 id 值是一个递增的数字

trx_id 列就是某个对这个聚簇索引记录做改动的语句所在的事务对应
的事务 id 而已

undo 日志从0开始，称之为 undo NO。

FIL_PAGE_UNDO_LOG 类型的页面是专门用来存储 undo 日志

FIL_PAGE_UNDO_LOG 页面可以从系统表空间中分配，也可以从一种专门存放undo 日志的表空间，也就是所谓的 undo tablespace 中分配

INSERT 操作对应的 undo 日志

DELETE 操作对应的 undo 日志

UPDATE 操作对应的 undo 日志

MySQL 崩溃恢复后，首先会获取日志检查点信息，随后根据日志检查点信息使用 Redo Log 进行恢复

如果事务未提交，来使用 Undo Log 回滚数据

如果事务已提交，，则用Redo Log 恢复数据

MySQL 可以根据 redo 日志中的各种 LSN 值，来确定恢复的起点和终点

将 redo 日志中的数据，以哈希表的形式，将一个页面下的放到哈希表的一个槽中

遍历哈希表，因为对同一个页面进行修改的 redo 日志都放在了一个槽里，所以可以一次性将一个页面修复好（避免了很多读取页面的随机 IO）

通过各种机制，避免无谓的页面修复

这两者使用方式不一样

redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”，恢复的速度更快；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2这的 c 字段加 1 ”

redo log 是“循环写”的日志文件，redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是
追加日志，保存的是全量的日志。

最重要的是，当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)，哪些数据还没有

password_history 修改密码不允许与最近几次使用或的密码重复，默认是0，即不限制；

password_reuse_interval 修改密码不允许与最近多少天的使用过的密码重
复，默认是 0,即不限制；

password_require_current 修改密码是否需要提供当前的登录密码，默认是OFF,即不需要；如果需要，则设置成 ON。

JSON_MERGE_PATCH()

JSON_MERGE_PRESERV()

数据在磁盘中的位置肯定是随机的

磁盘IO效率低，减少I/O的次数，加快查询速率

提高数据检索的效率，降低数据库的IO成本，最主要的原因

创建唯一索引，保证数据库表中每一行数据的唯一性

加速表和表之间的连接

在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

创建索引和维护索引要耗费时间

索引需要占磁盘空间，大量的索引下，索引文件就可能比数据文件更快达到最大文件尺寸

虽然索引大大提高了查询速度，但是同时也会降低更新表的速度

特点

示意图

特点

特点

示意图

特点：

示例图

特点：

特点总结

示例图

.frm(数据表结构)、.MYD(数据)、.MYI(索引)

索引文件按照B+Tree组织，叶节点存储数据行的地址，按照地址查找数据

表数据文件本身就是按B+Tree组织的一个索引结构文件

聚集索引-叶节点包含了完整的数据记录

节省资源，加快查询过程

说明：：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

SQL语句：explain extended select * from film where id = 1;

查询结果：

说明：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分
区。

id列的编号是 select 的序列号，有几个 select 就有几个id，

id的顺序是按 select 出现的顺序增长的。

id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type 表示对应行是简单还是复杂的查询

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询达到range级别，最好达到ref

参数

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果

这一列显示mysql实际采用哪个索引来优化对该表的访问

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 forceindex、ignore index

这一列显示了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;

计算规则：

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

这一列展示的是额外信息。常见的重要值如下：

给hire_time增加一个普通索引：

转化为日期范围查询，有可能会走索引：

还原最初索引状态

问题：解决like'%字符串%'索引不被使用的方法？

1.给年龄添加单值索引

2.没走索引原因

3.优化方法

4.还原最初索引状态

连接器

查询缓存

分析器

优化器

执行器

读写磁盘，结构数据化存储的实现

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。

默认引擎：InnoDB（MySQL 5.5.5 版本之后）

bin-log是Server层实现的二进制日志,他会记录我们的cud操作

binlog里的内容不具备可读性，所以需要我们自己去判断恢复的逻辑点位

要使用binlog归档，首先我们得记录binlog，因此需要先开启MySQL的binlog功能。

归档操作

如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

长链接：接成功后，如果客户端持续有请求，则一直使用同一个连接（大多数情况）

短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

 问题：MySQL 异常重启

问题：如何解决

如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

查询缓存的失效非常频繁，表的更新会导致表的查询缓存失效

对于更新压力大的数据库来说，查询缓存的命中率会非常低

故建议静态表里使用查询缓存

MySQL 需要识别出SQL里面的字符串分别是什么，代表什么。

例子：MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

报错语句：You have an error in your SQL syntax

调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

第一次调用的是“取满足条件的第一行”这个接口

循环取“满足条件的下一行”这个接口

联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描

虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过
滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 likeKK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

1. > EXPLAIN select * from employees where name > 'a';

2.> EXPLAIN select * from employees where name > 'zzz' ; /> EXPLAIN select name,age,position from employees where name > 'a' ;

order by语句使用索引最左前列

 使用where子句与order by子句条件列组合满足索引最左前列

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；

如何选择

排序详细过程

事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行

在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性

数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

开启：set tx_isolation='read-uncommitted'

开启：set tx_isolation='read-committed

开启：set tx_isolation='repeatable-read';

开启：set tx_isolation='serializable';

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

概念

操作：

加读锁

加些锁

结论

概念

InnoDB与MYISAM的最大不同有两点

行锁演示

无索引行锁会升级为表锁

间隙锁，锁的就是两个值之间的空隙

在某些情况下可以解决幻读问题。

间隙锁是在可重复读隔离级别下才会生效。

Next-Key Locks是行锁与间隙锁的组合