mysql知识体系

mysql总结笔记

mysql的基本认识

什么是mysql

​ 关系型数据库mysql，关系型数据库其实就是关系表数据库，由表结构来存储数据与数据之间的关系，同时用sql（structured query language）翻译过来叫做 结构化查询语句，来进行数据操作。

MySQL的架构

sql语句的操作类型

​

• DQL : Data Query Language 数据检索语句 where
• DML: DataManipulation Language 添加、删除、修改语句
• DDL：data definition languate 数据库结构操作、 create alter等
• DCL：权限控制语句
• TCL：事务相关、 比如 commit/savepoint, rollback

关系型数据库与非关系型数据库的区别

1. 关系型数据都是用表来进行维护，所以格式一致，可以用统一的sql语言来进行操作。
2. 关系型数据库都是表结构，所以灵活度不够，操作复杂的海量数据性能比较差，所以我们才会有表结构、索引以及索引优化。
3. 虽然性能可能会比较慢，但是能做复杂的关联查询操作，比如一对一，一对多等等。

为什么要用MySQL

那么你们公司为什么要用Mysql，或者面试官为什么喜欢问？

相比于其他的 关系型数据库，它的优势在哪里？

官网介绍： https://www.oracle.com/mysql/what-is-mysql/

易用性：开发者可以在几分钟内安装好MySQL，数据库易于管理。

可靠性： MySQL 是最成熟、使用最广泛的数据库之一。超过 25 年，它已 经在各种场景中进行了测试，其中包括许多世界上最大的公司。由于 MySQL 的可靠性，组织依赖 MySQL 来运行关键业务应用程序。

可扩展性： MySQL 可扩展以满足最常访问的应用程序的需求。MySQL 的 本机复制架构使 Facebook 等组织能够扩展应用程序以支持数十亿用户。

性能： MySQL HeatWave比其他数据库服务更快且成本更低，多项标准行 业基准测试证明了这一点，包括 TPC-H、TPC-DS 和 CH-benCHmark。

高可用性： MySQL 为高可用性和灾难恢复提供了一套完整的本机、完全集 成的复制技术。对于关键业务应用程序，并满足服务级别协议承诺，客户 可以实现 零数据丢失以及秒级的故障转移恢复。

安全性： 数据安全需要保护和遵守行业和政府法规，包括欧盟通用数据保 护条例、支付卡行业数据安全标准、健康保险可移植性和责任法案以及国 防信息系统局的安全技术实施指南。MySQL 企业版提供高级安全功能，包 括身份验证/授权、透明数据加密、审计、数据屏蔽和数据库防火墙。

灵活性： MySQL 文档存储为用户开发传统 SQL 和 NoSQL 无模式数据库应 用程序提供了最大的灵活性。开发人员可以在同一个数据库和应用程序中 混合和匹配关系数据和 JSON 文档。

当然，些很多都是黄婆卖瓜 自卖自夸，更重要的是不要钱

mysql的简单操作和概念

关系型数据库都是以表的方式进行数据存储，所以mysql默认有很多的系统表。然后为了区分隔离粒度，比如权限隔离、业务隔离，表上面还有库的概念。

mysql 库、表

​ mysql服务器运行时所需的信息相关表

• 数据字典表（Data dictionary Tables）

• 授权表 (grant system Tables)

  • mysql库下的user、db、tables_priv, colums_priv、procs_priv等等

• 对象信息表 (Object information system tables)

  • plugin插件注册表等

• 日志系统表 Log System tables

  • general_log： 一般查询日志表
  • slow_log: 慢查询日志表

• 服务器系统帮助表（server-side htlp system tables）

  • help_category: 有关帮助类别的信息
  • help_keyword：与帮助主题关联的关键字
  • help_relation：帮助关键字和主题之间的映射
  • help_topic：帮助主题内容

• 时区系统表 （Time Zone System Tables）

  • time_zone:时区ID以及他们是否使用闰秒
  • time_zone_leap_second: 发生闰秒时
  • time_zone_name: 时区ID和名称之间的映射
  • time_zone_transition,time_zone_transition_type: 时区说明​

• 复制系统表

  • slave_master_info、slave_relay_log_info、slave_worker_info: 用于存储服务器上的复制信息

• 系统优化表 （Optimizer System tables）

  • innodb_index_stats

    字段名	描述
    database_name	数据库名
    table_name	表名
    index_name	索引名
    last_update	记录最后更新时间
    stat_name	统计项名称
    stat_value	统计项值
    sample_size	生成统计数据的采样页面数量
    stat_description	统计项描述

• 系统杂表 （Miscellaneous System Tables）

​

库

mysql库：MySQL的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、关键字、权限设置、时区信息、帮助信息、一般日志、慢日志等。还有mysql自己需要使用的控制和管理信息。(常用的:在mysq.user表中修改root用户的密码)。

information_schema 库：记录mysql的一些服务器信息，比如innoDB库表信息，插件，引擎表等

performance_schema库：监控mysql服务器性能系统库 里面包含链接信息、锁、事件等。这里面的表是内存表，不适用持久的磁盘存储。内容在服务器启动的时候重新填充，并在服务关闭时丢弃。‘

sys 库：视图将性能模式数据总结为更容易理解的形式。

mysq基础架构

​ 我们知道mysql时通过sql语句进行数据操作的，那么一条sql语句从客户端发起，到服务器端处理，再由服务器返回数据，是一个怎样的执行流程，又涉及到哪些内部服务呢？

​ 在mysql的架构中，分为两大模块：

• 服务层：就是mysql这个数据库提供的一些功能。主要负责从客户端接收sql语句，然后对sql继续解析，看sql语句是否正确，是否有权限操作。然后分析出来怎么去查询更好，知道怎么查询了后去调用存储引擎相关的接口实现，返回数据。
• 存储引擎层：可以理解为mysql的一个插件，来负责数据的存储、查询方式。有很多存储引擎可以选择，如果你有能力也可以自己写一个存储引擎。

mysql服务层中的模块

链接层

我们要链接mysql，那mysql中肯定有一个连接层来管理链接。主要负责管理链接和链接相关的配置

与连接层有关的变量

线程状态变量

mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 1 | -- 缓存的连接数 缓存的大小配置thread_cache_size 默认-1 自动调整，最大是16384
| Threads_connected | 8 | -- 当前打开的连接数
| Threads_created | 63 | -- 创建的连接总数 越多，我们的thread_cache_size 可以对应的更大，来提升线程的缓存命中率
| Threads_running | 2 | -- 正在运行的连接数
+-------------------+-------+
4 rows in set (0.00 sec)

服务器应缓存多少线程以供重用？

​ 当客户端断开链接时，如果客户端的线程少于tread_cache_size线程，则将其放入缓存中。如果可能通过重用缓存中获取的线程来满足对线程的请求，并且只有当缓存为空时才会创建新线程。

SELECT @@thread_cache_size; -- 查询thread_cache_size系统变量

查看正在运行的链接信息

show FULL PROCESSLIST;  显示用户正在运行的链接信息

字段解析：

字段	含义
user	操作的用户名
host	地址
db	操作的那个库
command	当前链接执行的命令，休眠、查询 sleep为空闲链接
time	这个状态持续的时间 单位是s
state	状态 补充介绍 需要去官网查看
info	信息，线程正在执行的语句，如果线程没有执行语句，则为null

删除阻塞线程

举个例子：我们有一张表中有500W的数据，查询很慢，我们可以通过删除processId删除这个查询操作。

1. 先查询当前的线程

   SHOW PROCESSLIST;
   

2. 关闭查询的query

   KILL QUERY 2280;  --终止查询   这条对应的查询语句的请求就会被中断
   

   也可以直接杀死这个链接

   KILL 2280;  --直接kill这个链接  这个链接就会丢失
   

   其他链接相关的配置

   show status like 'Max_used_connections%';
   

Max_used_connections 自服务启动以来最大的链接数

Max_used_connections_time 达到这个峰值的时间

SELECT @@max_connections;
SELECT @@wait_timeout;

max_connections 最大的连接数，超过此值不允许被建立， 默认151 最小1 最大 100000（10W）

wait_timeout 非交互链接等待的时间（单位s）默认28800 也就是8个小时，空闲的线程如果8小时没动静，则会断开

max_execution_time 查询超时时间，毫秒为单位。

set @@GLOBAL.max_connections=5; -- 设置最大线程数
SET @@GLOBAL.max_execution_time=1000 -- 更改会话的查询超时时间为1s 更改后，对新起的会话生效
SELECT * FROM product_new; -- 查询超过1s的数据会报错

解析器

因为mysql是不知道sql语句是什么样子的，需要使用解析器解析sql语句，判断语句是否符合我们sql的语法、查询的表、列是否存在以及后续的权限检测。

主要是保证sql可以在服务端是可以执行的。

词法解析 -> 语法解析

词法解析：将sql预计打碎，转化成一个一个关键单词

语法解析：知道sql中每个单词的含义之后，通过语法解析检查语法是否正确。比如where是不是写成了where1 from是否写成了form

如果写错了直接抛出异常，否则解析为对应的语法树。

预处理

预处理是mysql提供的功能，我们使用预处理主要是用来解决sql注入，和减少解析器的次数提升性能。

场景：语句不变，但是sql中的条件根据用户的输入经常变化

sql注入：用户传过来的数据会改变我原有的sql语义，是不可信的

sql注入demo

select * from product where product_name='$name' -- 假如在
java程序里 product_name是通过用户传进来的值拼接的

用户传入
' OR 1=1 --

那么最终解析的sql

select * from product_new where product_name = '' OR 1=1 --
-- 我们发现跟我想要的不一样 会查询出很多数据 如果是用户密码或者
登陆，那么权限相当于没有

预处理中，mysql会将参数和语句的编译分开

预处理demo  放置sql注入
prepare pre_product from 'select * from gp_teacher where
id=? and teacher_age=?';
set @id=10;
set @teacher_age=34;
execute pre_product using @id,@teacher_age;
set @id=10;
set @teacher_age=34;
execute pre_product using @id,@teacher_age;
set @id=10;
set @teacher_age=34;
execute pre_product using @id,@teacher_age; -- 执行预便宜语
句
drop prepare pre_product;

mysql8.0提供了对服务器端准备语句的支持。这种支持利用了搞笑的客户端/服务的二进制协议。

使用带有参数值占位符的预处理语句具有以下好处：

• 每次执行语句时解析语句的开销更少。通常数据库应用程序处理大量几乎相同的语句，仅仅是更改查询的条件 删除的条件 set更新的值 values插入的值。
• 防止sql注入攻击。参数值可以包含未转义的SQL引号和定界符。

**面试题：**经常被问到的mybatis中# 和 $符号的区别？ # 符号就会去解析变成参数，然后进行预处理，能防止sql注入，并且必须传入参数。

• 在大多数情况下，推荐使用 #{} 来绑定参数值，因为它更安全、更灵活，并且有助于防止 SQL 注入攻击。
• 只有在确实需要动态构建 SQL 语句，并且你能确保参数值不会导致 SQL 注入的情况下，才应该使用 ${}。在使用 ${} 时，务必谨慎处理输入，避免潜在的安全风险。

优化器

通过解析器知道这个sql语句要做什么事情了，通过优化器可以决定我的sql该怎么样去执行，比如要不要走索引、

优化器就是让sql更快更好的去执行这条语句。

做哪些优化，那种执行方式，路径更快，通过全局变量opeimizer_switch决定

SELECT @@GLOBAL.optimizer_switch;  #查看所有支持的优化器，如果你不想用那个优化器可以关闭掉

优化器说明：https://dev.mysql.com/doc/refman/8.0/en/optimizer-hint s.html，里面包含了每个优化选项说明

优化方式官网地址：https://dev.mysql.com/doc/refman/8.0/en/optimiz ation.html；这个里面就有很多优化器的实践，比如优化sql语句等等。 SELECT @@GLOBAL.optimizer_switch;

优化后会生成一个最终的执行计划，所以这个语句到底怎么走，优化器来 决定。

执行器

通过优化器知道最重要怎么做了，最后由执行器去执行，主要是跟存储引擎交互，调用存储引擎的API得到用户想要的结果

注意: 存储引擎是和mysql解耦的，存储引擎和mysql的开发者不是同一批人，如果你有能力也可以自己写存储引擎。

存储引擎层

决定数据怎么存，存到哪里。

mysql支持不同的存储引擎，这些引擎决定了我们数据的存储方式，以及数据的可靠性，一致性，持久性，原子性。也就是我们经常说的ACID。

不同的存储引擎有不同的特点。

那么官网提供了哪些存储引擎： 官网：https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

查询下当前的服务器支持哪些存储引擎

SHOW ENGINES;  #查询当前服务器支持的存储引擎

需要知道的几个存储引擎：

InnoDB

事务型数据库的首选引擎，Mysql5.5.8之后默认使用InnoDB存储引擎，支持事务，支持行锁定和外键。

特性：

• 为Mysql 提供了提交、回滚、崩溃恢复能力，可实现并发控制。

• 遵循四个特性 ACID：原子性、一致性、隔离性、持久性。

• 对InnoDB表，自增长列必须是索引。若为组合索引，也必须是组合索引的第一列。

• Mysql 支持外键的存储引擎只有InnoDB ，在创建外键时，父表必须有对应的索引，子表在创建外键时也会自动创建对应的索引。

• 当意外断电或者重启，InnoDB能够做到奔溃恢复，撤销没有提交的数据

• InnoDB存储引擎维护自己的缓冲池，在访问数据时将表和索引数据缓存 到主存中。经常使用的数据直接从内存中处理。这种缓存适用于许多类型 的信息，并加快了处理速度。在专用数据库服务器上，高达80%的物理内 存通常分配给缓冲池

• where、groupby 、orderBy的自动优化、基于索引。

• 插入、更新和删除是通过一种称为更改缓冲的自动机制进行优化的

MyISAM

基于ISAM存储引擎，并对其进行扩展。常用在web、数据存储和其他应用环境下。MyISAM拥有较高的插入、查询速度，表锁级别，只缓存索引。但不支持事务，不支持外键。是Mysql5.5之前默认的存储引擎。

有三个文件 ：

1. .frm 定义数据表结构
2. MYD 存储数据文件
3. MYI 存储索引文件
   1. 特性：被大文件系统和操作系统支持。
   2. 每个MyISAM 表的最大索引数是64，这可通过编译来改变，每个索引最大的列数是16。
   3. 数据类型： blob text 可建立索引

memory

放在内存(缓存)中 能够快速的访问 但数据崩溃时极易损失
适合存放临时数据、数据量不大、数据不要求有较高的安全性的数据。
每个表支持多达32个索引，可在一个MEMORY表中有非唯一键。

ARCHIVE

只有插入和查询操作，支持高并发插入操作，但不是事务安全的。

数据存储在哪里，以什么样的方式存储

如果存储引擎的数据有磁盘存储，那磁盘的位置是统一的，会有数据存储地址，可以进行配置。

查看mysql配置文件、持久化地址

1. 查看mysql的安装路径

   [root@localhost mysql]# which mysql //找到mysql的安装路径，
   不需要登录shell客户端
   /usr/bin/mysql
   

2. 查看mysql的config配置地址

   [root@localhost mysql]# /usr/bin/mysql --verbose --help | grep -A 1 'Default options' //查看加载顺序
   Default options are read from the following files in the
   given order:
   /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
   
   

3. 查看数据的存储地址

   • sql语句查询

     SHOW VARIABLES LIKE '%datadir%';
     

innoDB 磁盘结构

进入到数据的存储地址目录

其中包含业务库文件夹例如 gupao_edu、还有一个redolog binlog undo日志文件。

因为我们上面说到有不同的存储引擎，不同的表可以设置不同的存储引擎

创建表时候指定存储引擎 https://dev.mysql.com/doc/refman/8.0/en/create-table.html

查看表的存储引擎信息

SHOW TABLE STATUS like ‘product_new’

进入到业务库对应的问题夹

发现myisam相关的文件有三个

.MYD文件 存储所有的数据记录

.MYI 索引文件

.sdi 系统数据文件，存储一些元数据、表结构，比如行数、空间碎片等

ibd结尾的是 InnoDB存储引擎的表数据文件

我们发现就一个ibd文件，内部存储数据与索引。

数据磁盘结构

我们知道innoDB的数据是会落盘的，那么落在哪里，是否可以更改？这些都是由表空间来决定的。文件名字是什么，是一个表一个表空间还是好几个表一个？

表空间

表空间的分类：系统表空间、通用表空间、独立表空间等等，

默认是独立表空间

是什么决定我的数据与索引文件放在哪个表空间的？

参数innodb_file_per_table参数可以设置存储到系统表空间还是独立表空间

-- 查看
SHOW VARIABLES LIKE '%innodb_file_per_table%';
-- 也可以进行关闭独立表空间
SET GLOBAL innodb_file_per_table=0;

独立文件表空间

独立文件表空间包含单个innodb表的数据和索引，并存储在文件系统中的单个数据文件中。 可以通过上面的操作关闭。

库gupao_edu文件夹下的product_new.ibd就是独立表空间

独立表空间的优点：

• 真正做到了解耦，易于管理，同时更大的利用磁盘空间。

• 当删除独立表空间时，磁盘空间会释放给操作系统。

  相比于共享表空间，共享表空间的空间只能用于InnoDB数据。表被截断或者删除之后，共享表空间数据文件的大小不会缩小。

• 性能更好，因为操作的都是独立的文件，文件更小。

• 独立表空间的每个文件的大小限制为64TB，这为单个表的数据增长提供了充足的空间。

  反而共享表空间中表的大小受到64TB表空间大小限制。(所有表共享64TB)

独立表空间的缺点：

​ 造成空间浪费、更多的碎片、文件过多，需要更多的文件句柄。

文件句柄：文件句柄（File Handle）是操作系统中用于标识和访问打开文件的一种抽象的、系统级别的资源引用。它是一个由操作系统分配给进程的唯一标识符，通常是一个整数值或者某种内部数据结构的指针，代表了进程对某个特定文件的访问权限和状态。

当一个应用程序通过系统调用（如在Unix/Linux中的open()函数，在Windows API中的相关函数）打开一个文件时，操作系统会为该文件创建一个内部记录，并返回一个文件句柄给应用程序。应用程序后续可以通过这个句柄来执行读取、写入、移动文件指针位置以及关闭文件等操作，而无需直接使用文件的实际路径名。

文件句柄的设计使得操作系统能够高效管理大量并发的文件访问请求，并确保不同进程或线程之间的文件操作互不干扰且正确无误地指向各自的目标文件。

共享表空间之系统表空间

系统表空间是InnoDB存储引擎用来组织和管理除独立表空间之外的所有表数据、索引以及其他重要内部信息的基础逻辑结构。

耦合性非常高

默认情况下，InnoDB会在MySQL数据目录下创建一个名为ibdata1的文件作为系统表空间的初始数据文件，且其大小可以通过配置选项innodb_data_file_path来定义。根据需求，可以配置多个数据文件以扩展系统表空间容量

SHOW VARIABLES LIKE '%innodb_data_file_path' # 查询系统表配置

ibdata1 : 文件名

12M：文件大小

autoextend: 自动扩容，扩容由innodb_autoextend_increment决定

SHOW VARIABLES LIKE '%innodb_autoextend_increment%'; #
查看扩容的大小，默认扩容增量为64M

如果扩容到64M还不够，可以采用多个数据文件来提升系统表空间，可以设置 innodb_data_file_path, 但是autoextend 只对最后一个文件生效

innodb_data_home_dir = – 多个文件的根目录

innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

并且不能对已经生成的文件的大小进行更改。在那时不支持减少现有系统表空间的大小。

共享表空间之通用表空间

与系统表空间类似，通用表空间也是能够为多个表存储数据的共享表空间。但是这个空间可以自己进行维护管理。

相关的表放到同一个表空间里，兼容独立和耦合的特点

创建通用表空间

CREATE TABLESPACE huihui_tablespace ADD DATAFILE
'/var/lib/mysql/huihui.ibd' ENGINE=InnoDB;

创建表到通用表空间

CREATE TABLE gp_student (studentId INT PRIMARY
KEY,studentName VARCHAR(500)) TABLESPACE huihui_tablespace
Engine=InnoDB;

删除通用表空间

DROP TABLESPACE huihui_tablespace; # 必须先保证通用表空间下没有表

限制：通用表空间只能创建在已知的目录，已知目录是由datadir、 innodb_data_home_dir和innodb_directories变量定义的目录。

临时表空间

在sql中，可能会用到临时表的逻辑，就是在会话中创建一个表供会话使用，会话关闭后表就失效了。

临时表空间在innodb_temp_tablespaces_dir进行配置

表空间中的数据存储

我们表中的数据是一行一行的数据。那存储在表空间中是什么样子存储的呢？

表空间结构

​ 表空间又由段、区、页组成，页中存储的就是我们的行数据，页是innodb管理磁盘的最小单位。在我们执行sql的过程中不论是查询还是修改，mysql总会把磁盘数据读取到内存中，而在读取数据时，不会单独加载一条数据，而是加载数据所在的页到内存中去，表空间本质上就是一个存放各种页的页面池。

表空间图示：

​ 每个表空间都是由相同大小的page页来组成的，默认page页大小为16kb,也可以根据innodb_page_size来设置页的大小。

​ 然后page页也不是直接放到表空间里的，而是用其他的一些数据结构（段 segement 区 extent）来封装，目的就是为了提高数据磁盘的扩展性，管理空间分配与性能以及利于磁盘的分配

segement 段

段是表空间的分区，一个表空间中，会有多个段组成，常见的段有数据段、索引段、8.0之前还有回滚段。段来管理区

extend 区

区来管理页，当

页的大小在16K以下，一个区的大小是1M，

页的大小32K的话，一个区的大小是2M，

页大小是64K的话，则为4M.

磁盘释放分配空间都是以区为单位。

所以一个extend下最少可以存储 64个page页。

page 页

page页 innodb用来加载的最小单位，即使要1k的数据也会读取整个页进到内存。

页的大小：默认大小是16kb,

对于4KB、8KB、16KB和32KB的innodb_page_size设置，页中管理的行，一行的数据最大不能超过页的一半大小。

对于innodb_page_size的值是64KB，页中最大的行数据不能超过16KB.

rows 行

表中的行格式决定了这行的物理存储方式，这反过来又会影响查询和DML操作的性能，为什么影响性能？

Compact Storage Characteristics：紧凑存储特性

Enhanced Variable-Length Column Storage：变长字段列存储 比如text 等字段，长度是不固定的

Large Index Key Prefix Support：索引键前置支持 SELECT @@innodb_page_size; //查询页大小，默认16KB

Compression Support： 压缩支持

Supported Tablespace Types：支持哪些表空间

四种行格式（不同的行格式，存储方式，空间性能都不一样）：

• REDUNDANT（rɪˈdʌndənt） 冗余行格式，主要是旧版本mysql的兼容， 数据和行索引信息分开存储，某些查询操作会快，但是需要额外的空间， 所以是之前老版本的格式设计
• COMPACT（kəmˈpækt） 减少了存储行间，官网说大约20%，但是增加 了cpu的负荷。导致一些查询的性能问题
• DYNAMIC（daɪˈnæmɪk） 动态行格式 该行格式允许长度可变，所以会根 据情况来决定是否需要更多空间，5.7后的默认行格式
• COMPRESSED（kəmˈprest） 压缩行格式 对COMPACT进行了压缩，减 少了存储空间使用，比如text 长文本 会进行压缩，但是检索的时候，必须 进行解压，牺牲了cpu。

默认行格式：

SELECT @@innodb_default_row_format; #查询默认行格式
SET GLOBAL innodb_default_row_format=DYNAMIC; -- 更改默认行
格式

定义表的行格式

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

查看表的行格式

SHOW TABLE STATUS IN dev_ibbp_canvas;

innoDB内存管理

CPU为了提高性能引入CPU高速缓存，同理 innoDB为了加载数据更快，也引入了自己的内存管理 Buffer Pool

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理，因此可将其视为基于磁盘的数据库系统（Disk-base Database）。在数据库中 CPU 速度与磁盘速度是有很大差距的，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

内存缓存结构 buffer pool

​ 缓冲池是主内存的一个区域，在InnoDB访问时缓存表和索引数据。缓冲池允许直接从内存访问频繁使用的数据，从而加快处理速度。

​ 默认大小128M.

SELECT @@innodb_buffer_pool_size; -- 默认134217728字节
/1024/1024
 

​ 设置bufferpool的大小

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

整个buffer pool的说明用一张图来表示

为了缓存管理的效率，缓冲池被实现为页链表，采用三个链表维护内存页，而内存页也因此对应 3 种状态：

Free 尚未使用； Clean 已使用但未修改； Dirty（脏页）已修改；

Free 页只位于 Free List，而 Clean 和 Dirty 页同时位于 LRU List，Dirty 页只存在于 Flush List；

内存加载数据机制

我们上面知道了buffer pool是innodb在内存中用来存储表数据和索引数据的。那bufferpool与磁盘中的数据是如何交互的呢？是怎么读取的呢？

页加载机制

innodb在内存与磁盘的交互的方式中选择了page页，所有page页是内存和磁盘交互的最小单位。

为什么要用page页来交互？

1、如果用行来交互，那么我查询200条数据，假如这200条数据都不再内存中的话，那就需要与磁盘交互200次，但是page页可能就是1-2个page页。

2、也不会用extent区来交互，因为一个区包含了64个page页。可能我需要查询1条数据，但是会加载64个page到内存中，造成空间的浪费。

所以基于内存的利用率与性能考虑，选择了page页。

取数据流程

1. 假如一条查询语句 查询出的数据有2.3.4.5.6.7 6条数据
2. 去bufferpool中查看2.3.4.5.6.7 所在的page页是否存在
3. 如果存在，直接返回
4. 如果不存在，根据2.3.4.5.6.7 的数据所在的page页，去磁盘加载，加 载完后保存到内存
5. 下次查询id=5,由于在内存中已经存在，直接返回。

预读机制

预读机制简单理解就是 数据预热。每次读取数据的时候，将对应的page页加载到内存，同时也会将一些没有读但是可能会读到的数据提前加载进内存中。

​ 预读请求时一种I/O请求，用于在缓冲池中异步预取多个页面，在请求某些页面时，预计即将需要extent的其他页面，进而把整个区的所有页面记载进内存。有两种预读算法来处理：

线性预读

​ 按照访问顺序的页来执行预加载，某个区里面的页面有多少个页按顺序访问了，那么就会预加载下一个区的所有页，放入到buffer pool中。

​ 具体多少个页被顺序访问，可以配置 innodb_read_ahead_threshold

SELECT @@innodb_read_ahead_threshold; -- 默认为56 可以配置
0-64，因为一个extent为64个页

随机预读

​ 根据缓存池中已有的页来预加载，如果缓冲池中找到了来自同一个区连续的13个页面，innodb会异步发出一个请求来预读取该区剩余的页面。

​ 默认是关闭的，因为随机预读会带来复杂性 同时性能也不稳定，在5.5之后将这种方式抛弃，默认是关闭状态。

mysql> show variables like 'innodb_random_read_ahead';

BufferPool内存管理和淘汰策略

​ bufferpool中的新老数据，是如何管理呢？

​ 老旧的数据，访问量少的数据是依据什么策略淘汰掉的呢？

​ 同理，redis中也有淘汰策略 伪LRU、LFU

​ mysql也是采用LRU算法进行page页淘汰的，只不过与传统的不相同，采用了增强版的LRU算法。

传统的LRU算法

LRU（Least Recently Used）算法是一种常用的页面置换算法，主要用于计算机系统中内存管理以及缓存淘汰策略的设计。其主要目标是在有限的存储空间中优化数据访问性能。

传统的LRU算法的特点：

• 新添加的数据都放置在链表头部
• 哪个数据被访问了，重新挪动到列的头部
• 优先淘汰最近没有访问到的数据。从链表末尾进行淘汰。

Innodb为什么不用传统的LRU算法呢?

因为预读机制的原因，会预先加载部分数据到bufferpool，但是这些数据不一定会被用到，但是又淘汰不了，即占用了内存，性能也没有得到提升。

增强版的LRU

增强版本的LRU解决了什么问题？

解决了因为预读机制导致的大量无效页面无法淘汰的问题。

是如何实现的：

通过将LRU list分成冷热区，冷热区中间的节点叫midpoint。新读取到的页，不是放到链表的顶部，而是放到minpoint的位置。如下图所示，是一个中间位置。

参数
SHOW VARIABLES LIKE'innodb_old_blocks_pct'

参数 innodb_old_blocks_pct的值为37 ，代表新插入的数据放到LRU list尾部37%的位置（差不多3/8的位置）。在innodb存储引擎中，把midpoint之后的列表称为old列表，之前的列表称为new列表。可以理解为new列表中都是最为活跃的热点数据。

淘汰流程：

• 当新的页面缓存到buffer pool，先假如到oldlist的头部（midpoint位置）包括预读的页面
• 当old list中的数据被访问时，会挪动至new list的头部，称为一个最近访问的页。
• new list中的链表数据会随着新加入的数据向后移动，同样的old list中的数据也是随着新加入的数据向后移动，直到挪动到old list的尾部被淘汰掉。

设计思想：

​ 既然我们不确定预读进来的page是否能被用到，那我们就先放到一个中间位置。当用到了之后放到头部避免淘汰。如果预读进来之后没有用到，随着时间的推移，慢慢淘汰掉，也不会占用头部的位置。变相的提高了内存的利用率。

内存与磁盘数据同步机制

​ 数据是以page的形式同步到内存中去的，在系统中同一个page页就有两份数据，一份在内存，一份在磁盘，这两个地方的数据是怎么做同步的呢？当修改了内存中的数据，什么时间持久化到磁盘，通过怎样的方式同步？

​ 我们想当然的理解为，每修改一次就同步到磁盘中，这种方式不仅效率低，操作慢而且大大降低了innodb的性能。在并发编程中有个store buffer，是通过异步的方式解决缓存一致性协议导致的CPU阻塞问题。那再innodb中也有相同的异步思想。

脏页

​ 在buffer pool中被修改了的page页，这时候还没有同步到磁盘（内存的page和磁盘的page数据不一致），称为 脏页。

异步刷脏

​ 通过异步线程，将脏页中的数据同步到磁盘就叫异步刷脏。

异步刷脏的流程是怎样的？通过几个线程来刷脏？是每次修改都去刷吗？

官网地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-flushing.html

InnoDB 通过独立的线程将Buffer Pool中的脏页刷入存储中。这些线程称作Page Cleaner. Page Cleaner的线程数量通过系统参数–innodb-page-cleaners控制。刷脏是以Buffer Pool实例为单位进行的。一个Buffer Pool实例同时只能有一个Page Cleaner进行刷脏操作。因此Page Cleaner的数量最多和Buffer Pool实例的数量相同(–innodb-buffer-pool-instances)。

SELECT @@innodb_page_cleaners; -- 默认是4 但是不能超过buffer-pool的实例数
SELECT @@innodb_buffer_pool_instances;  -- 查询buffer-pool的实例数

异步刷脏根据刷脏的时机分为三小类

• 脏页自适应

  • 当内存中的脏页数达到一定的数量或者一定的比例的时候，就回去刷脏。

  • 通过两个参数来控制我内存中的脏页量：

    SELECT @@innodb_max_dirty_pages_pct_lwm; -- 当脏页数量低于特定阈值百分比时InnoDB存储引擎开始刷新脏页的行为，默认10%
    SELECT @@innodb_max_dirty_pages_pct; -- 内存中可以存在的脏页的最大百分比
    

• redoLog自适应

  • 根据RedoLog页的产生速度来自适应，后面会细讲

• 空闲时间刷新

  SELECT @@innodb_idle_flush_pct; -- 表示在空闲时刷新脏页的百分比
  默认100 将全部脏页刷新 越高 跟磁盘的IO越多，因为要刷新的脏页也会越多
  

双写缓冲区

​ 双写缓冲区（doublewrite buffer）是磁盘上的一块存储区域，双写缓冲存储区位于双写文件（doublewrite files）中，InnoDB 将页面冲刷（flush）到磁盘上的数据文件之前，会先将其写入缓冲池中的双写缓冲区。当出现问题时，有备份来达到持久性跟数据的一致性。

双写缓冲区的作用：

如果在页面从内存写入磁盘过程中，出现操作系统、存储子系统或 mysqld 进程意外退出，InnoDB 可以在崩溃恢复期间从双写缓冲区中找到这些页面的无损副本（简单理解就是备份）。

虽然数据被写入两次，但双写缓冲区不需要两倍的 I/O 开销或两倍的 I/O 操作。通过执行一次 fsync() 系统调用，就可以将一个大的连续块（sequential chunk）上的数据写入双写缓冲区。

双写配置

#是否开启双写缓冲区
SELECT @@innodb_doublewrite; -- 默认开启 会加强一致性，但是会影响一定的性能
#写几个备份
SELECT @@innodb_doublewrite_files; -- 默认为2 至少2个备份
#双写磁盘的位置配置
SELECT @@innodb_doublewrite_dir;
SELECT @@innodb_data_home_dir;
#如果以上2个都没配置，则默认用的是数据目录
SELECT @@datadir;

LRU双写链表文件，后缀为dblwr的文件。16384代表页的大小。

双写流程 同步操作

1. Doublewrite Buffer 写入：
   • 当事务修改内存中的数据页时，InnoDB 不会直接将更改写回对应的数据文件，而是首先将脏页（即已修改但未持久化的页）复制到一个称为 Doublewrite Buffer （Doublewrite缓冲区）的连续内存区域。
   • Doublewrite Buffer 位于系统表空间的特定部分，其设计为可以顺序地一次性写入多个数据页。
2. 缓冲区同步至磁盘：
   • 在将数据页复制到 Doublewrite Buffer 后，InnoDB 引擎会调用 fsync 或类似的操作以确保这些更改被物理地、原子性地写入到磁盘上的 Doublewrite 区域中。在写入到磁盘之前，数据会被缓存到磁盘的Write Cache中，确保数据能够快速写入磁盘。由于是连续的大块写操作，所以性能相对较高且更可靠。
3. 脏页的LSN 日志序列号：
   • 在数据被写入磁盘之前，Doublewrite缓冲区中的每个脏页的LSN（日 志序列号）都会被更新，以确保数据的一致性。这是因为LSN是一种用于恢复数据的唯一标识符，它可以确保在数据库出现故障时，数据可以 恢复到一致的状态
4. LSN更新，脏页变为干净页
   • 一旦数据被成功写入到磁盘上的两个不同区域并且LSN已经被更新，数 据就被标记为干净页并从缓存池中移除。此时，这个脏页的数据已经被 持久化到磁盘中，并且数据库可以确保数据的一致性和可靠性。
5. 恢复过程：
   • 如果在正常关闭之外的情况（如突然断电或操作系统崩溃）下重新启动 MySQL 服务器，InnoDB 检查点和 redo 日志会与 Doublewrite 区域进行交互来检测是否有未完全写入的数据页。
   • 发现不一致时，会使用 Doublewrite 中的完整页副本替代损坏或不完整的页，然后应用 redo 日志来更新这部分数据，从而保证了即使在最严重的故障条件下也能恢复出一致的数据状态。

通过这种双写机制，innodb能最大程度的减少因硬件故障或软件问题导致的数据丢失，并确保了事务的持久性原则得到满足。

LSN 日志序列号的流转

不理解 – 在双写的过程中，LSN应该是在脏页中生成的，传入到doublewrite文件。

写入完成之后数据就从脏页变为干净页。那代表着从bufferpool到磁盘中间有回调过程，或者说有个回馈吧，这个回馈是啥样子的？

或者这么问bufferpool怎么知道脏页已经成功写入到磁盘了的？

这里的双写缓冲操作是同步操作，顺序写，效率高。也不需要回调。

变更缓冲change buffer

​ 把改变数据的操作缓存起来–提升语句执行效率。当需要更新数据页的时候，如果数据页在内存中就直接更新。不在内存中的话，在不影响数据一致性的前提下（行锁竞争引发数据不一致）。innodb会将这些更新操作缓存在change buffer中。(减少磁盘随机读操作) 在下次需要访问到这个数据页的时候，将数据从磁盘读入到内存中做合并操作。

​ 当在buffer pool中要进行INSERT、UPDATE 或 DELETE 操作时（就是要改的操作），但是要操作的数据不再buffer pool中，change buffer会发挥作用。

只针对二级索引：

**为什么：**因为二级索引的插入和修改一般都是无序的，所以IO开销更大，更需要提升性能。而对于唯一索引的插入和修改，需要判断该更改是否满足表中的唯一性约束，也就是必须通过将数据页读取到内存中判断是否已经存在这么一条记录。

相关配置：

#哪些操作需要用到bufferpool
SELECT @@innodb_change_buffering;
如果是none，则不进行缓存区缓存

#changebuffer的空间大小
SELECT @@innodb_change_buffer_max_size; -- 默认是内存空间的25%

场景：

change buffer适合写多读少的业务场景，因为即使更新语句使用了change buffer机制，减少了更新时的随机读磁盘操作，但是如果该业务查询场景很多，亦或者需要更新完又立刻查询的话。那么又会立刻触发merge操作，这样随机访问IO的次数不仅不会减少，反而增加了change buffer的维护代价。

change buffer 和redo log的联系：

1. change buffer使得一个更新操作可以不用把数据页立刻读取到内存中（减少了随机读的操作）
2. redo log使得一个更新操作可以不用立刻把数据写入到磁盘中（减少了随机写入的操作）
3. change buffer新写入的信息也会被记录到redo log中，使得mysql断电的时候也可以从redo log中恢复change buffer（redo log未commit的话，就要看binlog有无commit了，有commit的话就可以通过binlog恢复redo log再恢复change buffer，未commit的话这部分数据就会丢失了)

redo log

redolog理解

​ redolog又称为重做日志，当mysql发生异常情况，导致数据丢失的时候，我可以从我的redolog日志中找到我想要的数据。

redolog什么时候起作用呢？

​ 当数据从bufferpool中通过异步刷脏的方式刷新到磁盘过程中发生了宕机，产生了数据丢失问题，可以从redolog中找到丢失的数据。

WAL（Write-Ahead Logging）机制

​ WAL 的全称是 Write-Ahead Logging，中文称预写式日志(日志先行)，是一种数据安全写入机制。就是先写日志，然后再写入磁盘，这样既能提高性能又可以保证数据的安全性。Mysql中的redo log就是采用WAL机制。

为什么使用WAL?

​ 磁盘的写操作是随机IO，比较耗性能，所以如果把每一次的更新操作都先写入log中，那么就成了顺序写操作，实际更新操作由后台线程再根据log异步写入。这样对于client端，延迟就降低了。并且，由于顺序写入大概率是在一个磁盘块内，这样产生的io次数也大大降低。所以WAL的核心在于 将随机写转变为了顺序写，降低了客户端的延迟，提升了吞吐量。

redolog格式

当操作数据的时候，会记录一条redolog日志，这个日志的格式如图所示：

type:操作类型 插入 删除 修改

spaceId: 表空间ID

page number: 所在得页

data 修改的前后数据

这种事记录在某个偏移量发生了什么变更的这种日志格式，我们也叫做 物理日志

redolog file存储

redolog的数据都会保存到磁盘中

8.0.30之后，磁盘文件的大小由 innodb_redo_log_capacity 配置， InnoDB尝试维护32个相同大小的重做日志文件，每个文件等于1/32。

SELECT @@innodb_redo_log_capacity;

我们去磁盘发现，InnoDB维护了32个大小为104857600/32=3276800的 redo文件

尾部带有 _tmp的是备用文件，还没有写入redolog数据。

redolog 写入方式

redolog是如何保证数据不丢失的？

redolog的日志数据会不会很大？redolog的日志数据会不会被写满？

答：不会很大，我们要清楚redolog中是什么日志数据，是内存中sql语句在提交之前的数据，这些数据会先保存到redolog文件中。当在内存的数据准备写入磁盘的过程中，发生宕机刷脏失败了。这个时候需要去redolog中找到数据进行同步，保证一致性。所以并不是所有的数据都会保存到redolog中，在内存中需要同步到磁盘的数据也并不会很多（与内存的大小也相关）。

redolog的日志文件不会被写满，当redolog空间的大小满了之后会对之间的数据进行覆盖，采取的是循环写的方式。还有一个是在redolog快满的时候也会去把内存数据刷脏同步到磁盘，保证一致性。当数据同步到磁盘后，redolog就会进行删除。

异步刷脏是将page页刷到磁盘，redolog也是同步到磁盘，那么redolog的操作不是重复了吗？

答：不会重复，这个操作也是基于性能的考虑。

• bufferpool与磁盘交互的最小单位是page，只要page中改动一条数据，都要同步整个page页。所以会产生很多不必要的同步操作。而redolog只是会同步部分改动的数据记录。
• 改动的数据是随机的，不是顺序的，随机IO的性能比较慢。反而redolog是一直往上加，是一个顺序IO, 速度比page同步快很多。

RedoLog Buffer

​ redolog在保证一致性和持久性的同时，为了最大的性能，作者又在内存中申请了一块 logbuffer的区域，来缓存redolog，先写到logbuffer再从LogBuffer中同步到磁盘。

​ redo log buffer是用来缓存写入到redo log文件中的数据内容的,那么是不是每次redo log buffer产生内容就立即写入到磁盘进行持久化呢 ?

其实不需要的,就算在事务执行期间MySQL宕机了,redo log 缓冲区中的内容丢失了,也不会有损失,因为事务并没有提交(事务提交,必然写入日志完成)

redo log 三种状态:

• 存在于redo log buffer 内存区域中
• 向磁盘写入,但是没有真正写入磁盘,而是保存在文件系统缓存中
• 持久化到磁盘

redolog buffer与磁盘的同步方案

redolog提供了了不同的同步方案，能让用户在性能与数据安全两个方面自己做取舍。

SELECT @@innodb_flush_log_at_trx_commit; #RedoLog同步方案默认设置为1

• 1： 每次事务提交时，将日志刷新到磁盘，安全性高，能够保证持久性，默 认配置
• 0： 每秒从内存写到操作系统，并且刷新（fsync()）到硬盘，可能会导致数 据丢失
• 2：每次写入logbuffer 并且写到操作系统，但是每秒fsync()到磁盘，最终 刷新交给操作系统操作，只要操作系统不挂，也能保证持久性，但是操作 系统挂了，数据没刷新就会数据丢失

其他的导致redolog同步到磁盘的操作：

1. buffer空间不足：buffer是有大小限制的，可以通过innodb_log_buffer_size来进行设 置，当内存不足时，会将buffer数据刷新到磁盘。
2. 后台异步线程定时刷新
3. 正常关闭服务器
4. checkpoint 检查哪些数据没有同步到磁盘。

禁用redolog

redoLog虽然尽可能的去保证了我们数据的一致性，但是，如果为了性能考 虑，而不需要保证一致性也可以对RedoLog进行关闭。

查看RedoLog是否开启

SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled'; -- 查 看RedoLog是否开启

从8.0.21开始，可以使用指令

ALTER INSTANCE disable INNODB REDO_LOG -- 禁用RedoLog

三者之间的关系

异步刷脏 双写缓冲区 redolog直接的关系与区别？

我们从途中可以看到 （异步刷脏和doublewrite）与 redoLog 是两条线：

路线一：

异步刷脏和doublewrite是操作的page页，doublewrite是为了避免异步刷脏时page没写完整导致的数据损坏而做的备份。（MySQL的buffer一页的大小是16K，但是底层文件系统一页的大小是4K，MySQL将一页buffer数据刷入磁盘，需要写4个文件系统里的页。）

路线二：

redolog是操作一条条更改的数据。

当mysql发生异常情况，导致数据丢失的时候，可以从redolog日志中找到丢失的数据来恢复。

为什么redolog无法代替double write buffer？

​ redolog的设计之初，是“账本的作用”，是一种操作日志，用于MySQL异常崩溃恢复使用，是InnoDB引擎特有的日志，本质上是物理日志，记录的是 “ 在某个数据页上做了什么修改 ” ，但如果数据页本身已经发生了损坏，redolog来恢复已经损坏的数据块是无效的，数据块的本身已经损坏，再次重做依然是一个坏块。

​ 所以此时需要一个数据块的副本来还原该损坏的数据块，再利用重做日志进行其他数据块的重做操作，这就是double write buffer的原因作用。

innodb索引

什么是索引

​ 索引是根据表中的某个或多个字段生成单独的数据结构来提高查询效率，本质上是空间换时间，用一部分空间保存索引的数据结构，缩短查询的时间。

索引的类型

B+tree结构

树型结构支持范围查询

hash结构

k-v结构，不支持范围查询。

查询性能：如果是等值查询，hash的性能比b+tree快。

从索引存储结构划分： B Tree索引、 Hash索引、 FULLTEXT全文索引、 R Tree索引

从应用层次划分：普通索引、唯一索引、主键索引、复合索引

**从索引键值类型划分：**主键索引、辅助索引(二级索引)

**从数据存储和索引键值逻辑关系划分：**聚集索引(聚簇索引)、非聚集索引(非聚簇索引)

Clustered Index 主键/聚集/聚簇索引

​ 每个innodb存储引擎表中有且只有一个 Clustered Index索引树，默认以主键排序，如果没有主键，则会以非空的唯一唯一字段排序，如果这个也没有则会根据隐藏的row id排序。

​ Clustered Index保存了完整的行数据。

student 表 中的主键  id  就是 Clustered Index  主键索引

Secondary Indexes 二级索引

​ 除去clustered Index之外的单列、多列索引。

​ 手动去基于哪些字段建立索引时，会根据这些字段排序创建一个B+树，排序规则现根据第一个字段，第一个字段相等根据第二个，依此类推（如果字符的话根据ASCLL码）。

​ 二级索引不会有完整的行数据，只有索引的字段以及Clustered Index的排序字段。

根据student 表中的 age 建立了一个二级索引
也可以根据age  name 建立联合索引  也是二级索引

​ 最左匹配原则：

​ 最左匹配原则，是MySQL数据库查询优化中的一个重要概念。它指的是在使用联合索引时**，查询条件必须从最左边的索引列开始，并且能匹配上连续的索引列，才能充分利用索引进行查询优化。**如果查询条件中包含了范围查询（如>、<、between、like等），则停止匹配更多的索引列，即范围查询右边的列将无法使用索引进行优化查询。

回表

​ 根据二级索引查询的字段不包含在 二级索引中，需要根据对应的Clustered Index到主键索引中查询对应的字段。

select * from student where age = 10;
# age是二级索引

​ 如果以字段 age 建立了一个二级索引，根据age查询 age = 10的数据。因为建立了索引所以肯定是能走到索引的，但是二级索引中没有完整的数据，之后对应行的clustered Index ID，然后再根据 clustered Index id到主键索引中查询当前行的所有数据，这个过程叫做回表。

​

[!IMPORTANT]

回表是需要回到主键索引中查询其他的字段。

覆盖索引

​ 如果我这么查询

select age, name from student where age = 10;

​ 前面我们根据 age, name 建立了联合索引（二级索引），这次查询的两个字段都在二级索引树当中，不需要根据对应的Clustered Index回到主键索引中查询。这个叫覆盖索引。

[!IMPORTANT]

覆盖索引是不需要回到主键索引中查询。

索引下推

​ mysql5.6之后加入的索引优化，索引下推ICP。它的目的就是为了减少回表的次数以及server层跟存储层之间的数据交互。

​ 将本来要在server层执行器中过滤的数据，移动到二级索引树。

生效场景：

1. 仅对二级索引生效。 
1. 仅对InnoDB跟MyIsam生效

举例：

前提条件，已经根据age 和 name建立了二级联合索引。

使用索引下推之前：

​ 根据age 与 name 查询数据，age能走到索引，但是name是like走不到索引。只能先把age=13的两条数据取出之后回表查询所有字段，返回给server层。server层再根据name like ‘%huihui1’ 去过滤。

使用索引下推之后：

将name like ‘%huihui1’ 这个条件也在存储引擎中的二级索引树中过滤掉，减少了回表次数 减少了server层与存储引擎层的交互。

B+tree索引的原理

图示：

B+tree的特点和优势：

• **性能高：**叶子节点才会有完整的一行数据，而非叶子节点都是目录（目录就比数据占的空间小，同样的大小放的目录就越多）同层级的数能容纳的数据也就越多，同数据量需要的高度就越低。高度越低与磁盘IO的次数越少，性能越高。整体的性能比其他树更高。
• **便利次数相同：**稳定，不管查询哪个数据，因为非叶子节点没有完整的数据，都需要便利树的高度。
• **便于范围查询：**叶子节点是有序并且是链表关联的。所以可以更好的范围查询与便利。

与其他查找方式做对比

二叉树：同样的数据量，二叉树的高度更高，层级更高，磁盘IO的次数更多，性能低。

B-tree:

介绍：[https://blog.csdn.net/q769890208/article/details/114991114]

事务

ACID模型

ACID 模型：这个模型是数据库的设计原则，其目的就是尽可能的保证数据的可靠性。

A：atomicity 原子性：原子性是指一个事务就是一个不可分割的工作单位，要是全部执行成功，要么全部失败，不存在中间状态。基于事务来操作。

C: consistency 一致性 — 目的 ： 一致性是指在事务执行前后，数据的一致性，事务前后数据完整性没有被破坏，并且都是合法的数据状态。

I : isolation 隔离性 ： 原子性和持久性都是基于单个事务内部的措施，而隔离性是多个事务之间相互隔离，互不影响的特性。事务的隔离级别 innodb的锁机制

D：durability 持久性 ：持久性是指事务一旦提交，对数据库的操作就是永久性的，接下来的其他操作和异常故障不应该对它有任何影响。

原子性 隔离性 持久性 都是用来保证 一致性的。一致性是我们的目的，其他三个是手段。

innodb存储引擎是如何做到ACID的？

• 原子性：

  • innodb是依赖undo log 回滚日志来实现的，undo log能够保证在事务回滚时，能够撤销所有已经执行成功的SQL。

  • undo log是逻辑日志，它记录的是sql执行相关的信息。

  • 当事务对数据库进行修改时，innodb会生成与之对应的undo log。如果事务执行失败或者调用rollback,导致事务需要回滚， 会 根据undo log中的记录，将数据回滚到之前的样子。

    例如在执行insert语句时会生成相关的delete语句的undo log。反之执行delete语句也会生成相关的insert语句的undo log。执行update语句时也是如此，不过update语句在执行undo log回滚时有可能会涉及到MVCC。主要是为了保证在执行undo log的时候的select能看到哪个版本的数据。

• 持久性：

  • 双写机制，保证内存同步到磁盘，就算page损坏的情况下也能恢复
  • redolog 的同步机制
  • binlog

• 隔离性：

  • 事务的隔离性， 隔离级别
  • innodb锁机制（锁是隔离级别底层的一种实现方式）

• 一致性:

  • 保证原子性 持久性 隔离性，如果这些无法保证，那一致性也就无法保证了。
  • 除了在数据库层面保证外，又需要再应用层面 代码层面进行保证（字段校验）。

事务

​ 事务是可以提交或者回滚的工作原子单位。所有的提交都成功，或者所有的更改都回滚。

​ 一条语句可以是一个事务，多条语句也可以是一个事务。每个操作都会有一个隐藏的事务ID。

​ 事务是 sql 的标准，不是存储引擎级别或者是数据库级别。因为不同的数据库和不同的存储引擎都会支持sql。

操作事务

###  
#开启一个事务
START TRANSACTION;
或者
BEGIN;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;

# START TRANSACTION 后可以加参数是读写事务（READ WRITE）还是只读事务（READ ONLY）-- 默认是READ WRITE 如果参数为READ ONLY,则事务中不允许对表进行更改


START TRANSACTION READ WRITE;
-- 业务操作 多个业务操作
UPDATE gp_teacher SET teacher_age=teacher_age+1 WHERE id=2;
INSERT INTO gp_teacher(id,teacher_name,teacher_age,teacher_addr)
VALUES(18,'huihui',30,'湖南');  #当事务用READ ONLY修饰时，改操作无效
COMMIT; -- 提交该事务
ROLLBACK; -- 回滚事务

自动提交

我们平时做增删改查的时候 不会去写 start 或者是 commit，因为再MySQL中默认采用自动提交模式运行。

当没有手动开启事务的时候，默认每个语句都都用start transaction 、commit包围，并且不能用rollback来回滚数据。但是如果执行期间发生了异常，则进行回滚。

自动提交可以关闭

查看事务

那我们能不能看到系统中正在运行的事务，或者可不可以主动去操作这个事务呢？

**innodb提供了 information_schema.INNODB_TRX 表供我们查询当前执行的事务。**使用这张表可以帮助诊断在高并发负载期间发生的性能问题。例如长事务，大事务

sql语句：

SELECT * FROM information_schema.INNODB_TRX; 

[!IMPORTANT]

trx_id : 递增的事务ID，如果事务是只读事务或者非锁定事务（查询没加锁）不分配，展示的是一个比较大的随机数值。

个别字段介绍：

• trx_state: 事务的执行状态，值为 RUNNING(运行), LOCK WAIT(等待锁), ROLLING BACK(正在回滚), 和 COMMITTING(正在提交)。
• trx_query: 事务正在执行的sql语句。
• trx_isolation_level: 事务隔离级别
• trx_is_read_only: 只读事务标识，1代表只读事务

回滚

通过rollback指令可以将未提交的事务进行回滚。或者在执行过程中发生了错误也会自动回滚。

回滚点

当我的一个事务中有多条sql执行，我能不能只回滚一半的数据，或者是回滚到我指定的位置呢？

innoDB也是支持的，提供了回滚点的操作，可以回滚到指定的点位上。

START TRANSACTION;
UPDATE gp_teacher SET teacher_age=teacher_age+1 WHERE id=2;
-- 设置回滚点，如果回滚回滚点，后续内容会被回滚
SAVEPOINT huihui;
UPDATE gp_teacher SET teacher_age=teacher_age+1 WHERE id=1;
ROLLBACK TO huihui; -- 回滚到回滚点 id=1的不生效 但是不代表事务结束，事务结束还需要commit或者ROLLBACK
COMMIT; -- 提交事务

不能回滚的语句

​ 有些语句是不能回滚的，通常包括数据定于语言DDL语句，例如创建删除数据库的语句，创建删除更改表或者存储程序的语句。

因为DDL语句：

• 带有隐式的提交动作（隐式提交）
• 数据字典更新，DDL操作涉及到对数据库内部数据字典的修改，这些修改通常是立即生效且永久性的，数据字典的更改不能撤销。
• 复杂性和资源消耗：实现DDL回滚可能会带来巨大的复杂性，尤其是在处理涉及大量依赖关系的数据结构变更时。资源管理也是一大挑战，因为需要额外维护元数据变化的历史记录以支持可能的恢复操作。

回滚日志

​ undolog , 在事务提交之前，insert update delete 这三个操作都会被记录到undolog中去，同时记录的还有对应的逆向操作。

​ 执行insert语句时会生成相关的delete语句的undo log。反之执行delete语句也会生成相关的insert语句的undo log。执行update语句时也是如此

​ 在内存中undo log的表空间中保存回滚日志，简单理解通过undo log可以找到之前的数据是什么样子的。

​ undolog的每个日志，为了保证数据不丢失，也是需要记录到redolog中进行持久化。

事务并发

​ 如果同一时间有很多事务并行执行，就会涉及到事务并发问题。（innodb也是支持事务并发的，如果不支持并发，只能一个一个的事务执行，那效率就特别特别低）

​ 如果多个事务同时操作同一条sql就会产生并发问题。

事务并发产生的问题

​ 事务并发产生的问题其实都属于数据一致性问题。

脏读

​ 脏读是指事务A中读取到了事务B未提交（只在内存中更改了）的数据。 因为事务B有可能进行回滚。

不可重复读

​ 同一个事务里面，对同样数据进行多次读取操作，读到的结果是不一致的。在多次读取之间有另外的事务对相关数据进行删除、修改并且提交。

在不同的事务里面，假设事务A在操作1的数据，事务B不能去重复操作（更改和删除）。

是不是个问题？ 我感觉不是

幻读

​ 一个事务（如事务A）读取了与特定搜索条件相匹配的数据行，而另一个并发事务（如事务B)插入或删除了一些行并提交，导致事务A再次执行相同的查询时，会发现一些“幻影”般的、原本不存在的行或数据行消失了。

隔离级别–解决事务并发

​ 解决事务并发问题，其实就是解决数据一致性的问题。而说到数据一致性又涉及到数据的安全和性能两个概念。这个在并发编程中也有同样的情况。安全和性能不可兼得的思想在这里也有体现。

在sql 1992 的标准中引入了四种隔离级别来解决事务并发问题。

隔离级别	脏读	不可重复读	幻读
读未提交：read uncommitted	1	1	1
读已提交：read committed（oracle 默认）	0	1	1
可重复读：repeatable read（mysql 默认）	0	0	1
串行化：serializable	0	0	0

1 表示在该隔离级别下会出现该种问题，0反之
事务的隔离级别越低，并发能力越高，越容易出现问题

如何设置事务隔离级别？

-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别
set session|global transaction isolation level xxx;	# xxx 为隔离级别
# global 对全局隔离级别生效，后面获取的连接都会生效
# session 仅对当前会话生效

介绍：

read uncommitted : RU : 读未提交 ：

不会解决任何问题，但是并发性能最高。

read committed: RC : 读已提交：

只能读取到已经提交的数据，

解决了脏读问题，没有解决不可重复读和幻读。

repeatable read : RR : 可重复读：

解决了不可重复读问题，没有解决幻读问题（但是在innodb中，通过 MVCC LBCC解决了幻读问题。）

在一个事务 A 中，无论 B 是否提交改变，A 查询到的结果在事务 A 中都是相同的。

serializable : 串行化：

解决了所有问题，性能最慢。

如果 A 查询记录不存在，B 打算插入，B 会被阻塞。等 A 提交后 B 才会继续执行，避免幻读问题

innodb 解决幻读并发问题

​ innodb的默认隔离级别是 repeatable read。为了解决该隔离级别下的幻读问题提出了两种方案。

• LBCC 锁机制 解决当前读情况下的幻读 也叫 锁定一致性读取。

• MVCC 多版本并发控制 解决**普通读（快照读）**的幻读 非锁定一致性读取。

LBCC 锁定一致性读取

​ LBCC是Lock-Based Concurrent Control的简称，意思是基于锁的并发控制。

​ 场景：在对行数据做修改操作时，来保证并发情况下的数据一致性。简单的说就是给数据加锁，当A线程在操作数据的时候，其他线程不能再去操作。

锁的是什么

​ 在并发编程中，例如synchronized是锁的对象。在innodb中我们对数据加锁是把锁放到哪里了呢？

根据sql操作来决定：

• 如果你根据索引来查询，那就锁的是你查询时走的索引树节点。
• 如果操作条件字段没有索引，就会锁所有的行数据。
• 如果所得时二级索引的节点，也会去找到行数据对应的主键索引加锁。

加锁数据是基于索引树去进行加锁的，锁的也是索引树的节点。

锁分类

按照锁的模式分类

• 共享锁（读锁）

  也称为S锁，加了读锁，其他事务能够再次加读锁。当我读取一个数据后，我不希望其他事务对数据进行更改，就可以采用读锁。

  BEGIN;
  SELECT * FROM gp_teacher where id=10 FOR SHARE; -- 读锁 FORSHARE代替了LOCK IN SHARE MODE，但是LOCK IN SHARE MODE向后兼容,加锁后，其他事务在提交之前不能对数据加排他锁，但是能加读锁。
  COMMIT;
  
  

• 排他锁（写锁）

  也称为X锁，加上排他锁后，其他事务不饿能再去加其他的读锁或者写锁，我们操作数据默认会加排他锁。

  BEGIN;
  SELECT * FROM gp_teacher where id=10 FOR UPDATE; -- 索引扫描到id=10的数据，那么会锁id=10的数据，其他事务不能进行操作
  COMMIT;
  

• 意向锁

  为什么会有意向锁？

  ​ 在innodb中，锁的粒度又分为表锁、行锁。表锁是对整个表进行加锁，行锁是加到某些行上面。

  ​ 如果一个表加了表锁，就不能再去加其他的表锁。如果一个表中只有某条数据加了行锁，我想去给这个表加表锁的时候，就需要遍历表中的数据来判断有没有行锁才能决定可不可以对这个表加表锁。但是如果表中的数据量过大的话，这种遍历也会消耗性能。

  ​ 这个时候引入了意向锁，如果表中有某条数据加了行锁，我就在表上做个标记，代表表中已经有数据加锁了，这样就不需要遍历了，直接判断就行，提高了性能。

  ​ 加行锁的时候默认加意向锁。

按照算法分类

• 记录锁 唯一索引或主键索引

  • SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;
    

  • 记录锁是针对单个数据索引记录（行）的锁。也叫行锁，当一个事务需要对某一记录进行修改或删除操作时，他会申请获取该索引记录的锁。一旦获得锁，其他事务将无法修改或删除该记录，直到当前事务完成。

  • id列必须为唯一索引列或主键列，否则上述语句加的锁就会变临建锁

  • 这种锁机制可以确保数据的完整性，防止在并发操作时对同意记录进行同时修改。

• 间隙锁 非唯一索引

  • SELECT * FROM emp WHERE empid > 100 FOR UPDATE
    # 举例来说，假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101，
    #当我们用条件检索数据，并请求共享或排他锁时，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
    

  • 间隙锁 是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。是行锁的一种。

  • 使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

  • 间隙锁，在RC级别下时禁用的，仅用于外键约束检查和重复键检查。因而RC是没有解决幻读问题的。

• 临键锁 非唯一索引

  • 临建锁 = 记录锁 + 间隙锁，它指的是加在某条记录以及这条记录前面间隙上的锁。 是一种特殊的间隙锁。

    InnoDB，RR隔离级别：id主键, age 普通索引

    该表中 age 列潜在的临键锁有：
    (-∞, 10],(10, 24],
    (24, 32],
    (32, 45],
    (45, +∞],
    在事务 A 中执行如下命令：

    -- 根据非唯一索引列 UPDATE 某条记录 
    UPDATE table SET name = Vladimir WHERE age = 24; 
    -- 或根据非唯一索引列 锁住某条记录 
    SELECT * FROM table WHERE age = 24 FOR UPDATE;
    
    
    #不管执行了上述 SQL 中的哪一句，之后如果在事务 B 中执行以下命令，则该命令会被阻塞：
    INSERT INTO table VALUES(100, 26, 'tianqi'); 
    很明显，事务 A 在对 age 为 24 的列进行 UPDATE 操作的同时，也获取了 (24, 32] 这个区间内的临键锁。
    

• 也可以理解为一种特殊的间隙锁，通过临建锁可以解决幻读问题。

  每个数据行上的非唯一索引列都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

• 临键锁只与非唯一索引有关，在唯一 索引列（包括主键列）上不存在临键锁。

查看加锁记录

查看加锁记录

执行sql语句，给id = 10 的数据加排他锁

BEGIN;
SELECT * FROM gp_teacher where id=10 FOR UPDATE; -- 索引扫
描到id=10的数据，那么会锁id=10的数据，其他事务不能进行操作
COMMIT;

通过性能库 performance_schema中的data_locks进行查看

我们可以发现，必定会加一个IX锁，意向排它锁。 同时，基于Primary 主键索引加锁，

LOCK_MODE为X,REC_NOT_GAP，

• X 代表exclusive 排它锁

• REC_NOT_GAP 代表是个记录锁

LOCK_DATA为10 代表锁的记录是10.

如果在另外的事务中对10进行更改，会进行等待，直到锁等待超时。

查看等待锁的线程

通过sys库下面的innodb_lock_waits 查看哪些线程在等待。

SELECT
waiting_trx_id,
waiting_pid,
waiting_query,
blocking_trx_id,
blocking_pid,
blocking_query
FROM sys.innodb_lock_waits;

锁超时设置

SELECT @@innodb_lock_wait_timeout;

官网地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-paramet ers.html#sysvar_innodb_lock_wait_timeout

单位s，默认50s。最小1s

MVCC 非锁定一致性读取

​ LBCC是基于锁的并发控制，因为锁的粒度过大，会导致性能下降，因此提出了比LBCC性能更优越的方法MVCC.

​ MVCC是Multi-Version Concurrent Control的简称，意思是基于多版本的并发控制协议，通过版本号，避免同一数据在不同事务间的竞争，只存在于InnoDB引擎下。它主要是为了提高 数据库 的并发读写性能，不用加锁就能让多个事务并发读写。

原理：**使用多个版本的快照，来向查询数据库的请求提供数据。**只能查询到在该节点之前提交事务所作的更改，而查不到节点之后事务所作的更改。

快照 readView

​ 快照也叫读视图，是事务在进行读取数据的时候产生的读视图。数据库中会创建一个视图，访问的时候以视图的逻辑结果为准。

不同的隔离级别产生快照的时间不同：

• 在 可重复读 RR 隔离级别下： 这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。所以在RR隔离级别下也解决了幻读问题。
• 在 读提交 RC 隔离级别下：这个视图是在每个SQL语句开始执行的时候创建的，就是每次读取都会有新的快照。

注意：在 读未提交 隔离级别下，直接返回记录上的最新值，没有视图概念。

生成的快照信息

​ 每条sql都有一个默认的事务ID，根据当前的事务ID去生成的快照信息如下：

class ReadView {

trx_id_t m_low_limit_id; //即将要分配的下一个事务ID

trx_id_t m_up_limit_id; // 所有存活的（没有提交的）事务ID中最小值

trx_id_t m_creator_trx_id; // 当前的事务ID

ids_t m_ids; //存活的事务ID集合 就是在创建readView 没有提交的事务的ID集合

}

​ 在事务的并发状态下，随着时间的变化事务也是不断变化的。而我们有了ReadView的信息（不同时间节点获取），就可以在执行当前sql的时候去判断，哪些事务可见，哪些事务不可见。

判断规则：

1. 如果数据的DB_TRX_ID< m_up_limit_id, 小于没有提交的最小的事务ID，说 明在创建ReadView的时候已经提交了，可见。
2. 如果数据的DB_TRX_ID>=m_low_limit_id, 大于等于我即将分配的事务ID， 那么表明修改这条数据的事务是在创建了ReadView之后开启的，不可 见。
3. 如果 m_up_limit_id <= DB_TRX_ID < m_low_limit_id, 表明修改这条数据 的事务在第一次快照之前就创建好了，但是不确定提没提交，判断有没 有提交，直接可以根据活跃的事务列表 m_ids判断
   1. DB_TRX_ID如果在m_ids中，表明在创建ReadView之时还没提交， 不可见
   2. DB_TRX_ID如果不在m_ids，表明在创建ReadView之时已经提交，可见

举例

https://www.processon.com/view/link/5fc8f9391e08534f6cd2ac31

mysql日志与数据同步

binlog

​ 在保证mysql高可用时，一般情况下会采用主从部署，读写分离的方案。主库只负责写，从库只负责读。那主库的数据是通过什么方式来同步到从库的？

​ mysql引入了binLog 来承担这个角色，binlog是数据的载体。就像redis里面有rdb文件。binlog也是相同的作用。

​ binlog又被称为二进制文件，属于mysql server层去记录，所以不管是什么存储引擎进行的数据存储，如果binlog开启都会被记录。

二进制日志包含描述数据库更改的事件（如表创建操作或表数据的修改）。他还包含可能发生更改的语句的事件（如不匹配任何行的DELETE），二进制日志还包含关于每条语句花费更新数据的时间的信息。

并且只有完整的事件或事务才会被记录或回读。

binlog的用途

• 主从复制
• 数据恢复

binlog的格式

statement

基于语句记录

示例：

update table set time=now() where id=1;  # binlog会记录这条语句，然后拿这条语句执行

优点：格式简单，占用空间少。

带来的问题：

​ sql语句中的函数或者表达式，在不同的时间执行的结果不同，代表着使用同一条sql语句，不同时间执行的结果不同，影响数据的一致性。例如上面的 now()。在再次执行这条语句的时候时间结果不同。

​ row格式解决了这个问题

row

基于行格式记录，binlog可以记录单个表行是如何更改的。

update table set time=1675778373 where @1=1 and @2=.. and
@3=...; # 直接记录id = 1的这条数据修改的字段值，结果不产生变化

缺点：格式复杂，占用空间大，恢复起来相对比较慢

mixed

混合模式，默认是statement，但是在某些情况下会切换成row格式。

mixed是一种折中的模式，混合了statement与row的优点，如果statement格式下会导致一致性问题，就用row格式，如果不会有一致性问题的话，还是statement格式。

具体什么情况下转换为行格式，参考：

https://dev.mysql.com/doc/refman/8.0/en/binary-log-mixed.html

binlog的配置

BinLog会有很多相关的配置，这些配置决定了要不要开启、以什么方式存 储、存储在哪里等等！

我们可以通过下列查询找到相关的配置

show variables like '%log_bin%'; -- log_bin相关配置
log_bin -- 默认on 开启 可以对binlog进行关闭
log_bin_basename -- bin文件前缀 默认
/var/lib/mysql/mysql-bin
log_bin_index -- bin文件索引 /var/lib/mysql/mysqlbin.index
binlog_cache_size -- binlog日志 事务缓存大小
binlog_encryption -- 内容是否加密 我们的内容为了安全性可能需要加
密
binlog_format -- binlog格式
binlog_expire_logs_seconds -- 多久后binlog删除 默认2592000s
也就是30天

binlog同步机制

1. 开启事务后，事务语句中的二进制日志先放入内存缓存，这个内存缓存是存储事务没有提交的数据。

   具体的缓存大小由 binlog_cache_size 设置（如果超过这个值，就会暂存到磁盘）

   show variables like '%binlog_cache_size%'; -- 事务期间用于保存二进制日志更改的内存缓冲区的大小。
   

2. 事务提交commit的时候，会同步到文件系统缓存。

   为了性能与数据一致性方面的考虑，会有不同的同步策略sync_binlog让文件系统缓存同步到磁盘。

   策略	描述
   sync_binlog = 0	不同步刷新到磁盘，交给操作系统去做，断电或者操作系统异常，可能导致数据丢失
   sync_binlog = 1	能保证数据的一致性，每次提交都必须保存到磁盘，但是对性能有影响。
   sync_binlog = N	N是binlog的数据条数，默认是1，最大4294967295，代表达到N条binlog后，再同步到磁盘，能够灵活的来设置数据一致性与性能之间的平衡。

   [!IMPORTANT]

   为了数据的一致性和持久性，官网建议 设置

   sync_binlog=1

   innodb_flush_log_at_trx_commit=1.

   innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数。

   innodb_flush_log_at_trx_commit

   如果innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。

   如果innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.

   如果innodb_flush_log_at_trx_commit设置为2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

   binlog查询

   mysql> show master status; //当前在写哪个binlog
   +------------------+-----------+--------------+-----------
   -------+-------------------+
   | File | Position | Binlog_Do_DB |
   Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+-----------+--------------+-----------
   -------+-------------------+
   | mysql-bin.000002 | 192889304 | |
   | |
   +------------------+-----------+--------------+-----------
   -------+-------------------+
   1 row in set (0.00 sec)
   

三大日志区别

Bin log、Redo log 和 Undo log 都是 MySQL 中最为重要的日志机制。

MySQL 实现事务、崩溃恢复、集群的主从复制等，底层都离不开日志，可以说日志是 MySQL 的内核所在。

作用：

• Redo log （重做日志）是innodb在系统异常宕机的情况下保证事务持久性的方式。
• Undo log （回滚日志)）来保证事务的原子性，原子操作发生异常需要回滚或者主动回滚的时候，通过undoLog来寻找操作前的数据。
• bin log 是MySQL 数据库的数据备份、主备、主主、主从都需要依靠 Bin log 来同步数据，保证数据一致性。

记录内容：

• Bin log：以二进制格式记录数据库的修改操作，只记录提交的完整事务日志。
• Redo log：通过顺序IO的方式记录没有持久化到磁盘中的内存数据，已经持久化到磁盘中的记录会删除。
• Undo log：以逻辑方式记录事务执行过程中旧值的备份。

应用场景：

• Bin log：用于数据恢复、主从复制、数据审计和数据备份等。
• Redo log：用于保证事务的持久性和故障恢复。
• Undo log：用于事务的回滚和并发控制。

三种日志的方式不同作用不同应用场景不同，无法相互替代。

binlog与redolog二阶段提交

​ 对于innodb而言，每次修改数据后，不仅要记录redolog 还要记录 binlog, 而且这两个操作必须保证同时成功或失败。

​ 所以为了保证数据的一致性，也保证表数据与日志数据的一致性，也保证binlog与redolog这两个事务都尽快成功。引入二阶段提交的方式。

二阶段提交：

1. 在更新数据，还没提交事务的时候，先写入redolog中，提交的redolog为prepare状态。
2. 当commit事务后，会将binlog cache缓存的binlog，同步到磁盘。
3. 将redolog状态更改为commit状态，流程结束

如果发生异常如何处理？

1. 如果操作①失败，数据回滚，RedoLog跟binlog都不会有
2. 如果②失败，有RedoLog的prepare状态，但是没有binlog落盘，数据 回滚，操作失败
3. 当③失败，这个时候，有RedoLog并且有binlog，数据都会有，并且数 据是一致的，成功。

为什么会有二阶段提交？

没有二阶段提交的话，我们写到两个log中的数据就是，先写binlog再写redolog，或者先写redolog再写binlog。

• 如果先写redolog再写binlog

• 如果先写binlog再写redolog

  

mysql的主从同步机制

复制原理

​ mysql提供了三种线程来帮助主从之间数据同步，其中replica/slave 2 个，master 1个。

replica/slave中的线程

• I/O receiver thread IO接收线程

  负责从master里面获取binlog日志，并将日志加载到replica本地的文件，这个文件也叫做 replica’s relay log(中继日志)，

• sql applier thread sql执行线程

  slave接收到binlog日志后，需要去执行到replica数据库，就是依靠sql applier thread执行的。

master 中的线程

• Binary log dump thread线程 发送binlog数据线程

  master收到I/O receiver thread线程发起的同步指令后，master会创建一个binary log dump thread线程，将binlog内容发送给slave。

同步方式

​ mysql默认采用异步方式进行同步binlog，但是在传输过程中有可能失败或者因为网络原因导致没有进行同步，就会导致数据不一致问题。

​ 所以，mysql不仅有异步同步的方式还有半同步的方式。

异步复制

​ 异步复制方式是采用额外的线程去dump我们的binlog然后传送给slave，并且master是不会等待同步结果，就是不会管slave是不是成功的。

​ **优缺点：**这种方式性能比较高，主要是不会影响主的性能。但是数据一致性低，如果主挂了，没有通知到从，那么这个从就不会有新的数据。

异步同步会存在一定的数据丢失，并且会有延迟，所以mysql引入了另外一种半同步的方式。

半同步复制

普通半同步与增强半同步之间的区别在于等待ack返回的节点是在commit之前还是之后。

由rpl_semi_sync_master_wait_point 进行设置

rpl_semi_sync_master_wait_point 变量有两个可选值：

1. AFTER_COMMIT：这是默认值，表示主库在事务提交后等待至少一个从库的确认。

2. AFTER_SYNC：表示主库在事务写入binlog并完成sync_binlog操作后等待从库确认。

   [如果sync_binlog设置为0（异步刷盘），这个设置与AFTER_COMMIT相同。]

普通半同步复制

​ 普通半同步，是指在master处理事务的过程中，提交完事务后，必须等待至少一个slave将收到的binlog写入relay log返回ack，才能继续执行后续任务。

配置

rpl_semi_sync_master_wait_point = AFTER_COMMIT //（什么时间点开始等ack）【这里MySQL 5.5并没有这个配置，MySQL5.7 为了解决半同步的问题而设置的，下文有讲解】

rpl_semi_sync_master_wait_for_slave_count = 1 //（最低必须收到多少个slave的ack）

rpl_semi_sync_master_timeout = 100//（等待ack的超时时间）

产生的问题：

• 一旦ack超时，将退化为异步同步的模式，那么异步同步产生的问题也将出现

• 性能下降，增多了至少一个ack的等待时间

• 也有可能产生数据不一致问题

  因为等待ack 的点是在commit之后，此时master已经完成了数据变更，用户可以看到最新的数据。当binlog还未同步到slave时，发生主从切换，那么此时从库是没有这个最新的数据的，用户又看到了老的数据。

增强半同步复制

​ 增强半同步是指，在master处理事务的过程中，必须等待至少一个slave将收到的binlog写入relay log返回ack之后，才提交事务。

​ 增强半同步将等待ack的节点放在提交commit之前，此时数据还未被提交，外界还看不到数据变更，此时如果发生主从切换，新库依然还是老的数据，数据可以保持一致。

配置

rpl_semi_sync_master_wait_point = AFTER_SYNC //（什么时间点开始等ack）【这里MySQL 5.5并没有这个配置，MySQL5.7 为了解决半同步的问题而设置的，下文有讲解】

rpl_semi_sync_master_wait_for_slave_count = 1 //（最低必须收到多少个slave的ack）

rpl_semi_sync_master_timeout = 100//（等待ack的超时时间）

产生的问题：

​ 如果超时时间设置很大，因为网络原因长时间收不到ack，用户的提交是被挂起的。可用性收到打击。

主从数据不一致问题

主从数据不一致或者同步延时的原因？从这些原因上可以找到对于的解决方案。

• 网络延迟

  检查网络 优化网络能够让网络支撑数据量的传输。

  可以采用半同步的方式，确保数据不会丢失，或者最少有一个slave能同步到数据。

• 主库负载很高

  当主库有大量操作任务的时候，就会产生大量的需要同步给slave的数据。

  数据量大也会导致延迟，可以做负载、缓存减少master的压力。

• 大事务导致

  binlog 太大太多，从库需要执行的时间越久，也会导致延迟，尽量减少大事务。

• 从库的机器性能低

  从库的CPU 内存要与主库的服务器匹配，不然处理性能有差异也会产生问题。

思考如何选择异步复制或者半同步复制

​ 业务场景

​ 数据一致性要求高还是低

主从同步配置

​ 因为主从搭建不同版本可能会不同，这里详细记录的意义不大，也没有实际操作。只说几个比较关键的。

• 数据源必须开启binlog

  如果是副本不需要开启BinLog，除非这个副本想成为另外一个实例的数据 源，也就是A->B->C的架构。A同步给B,B同步给C。

  SELECT @@log_bin;
  

• 确保有唯一的server_id

  -- vim /etc/my.cnf
  server-id=128  也叫实例ID
  

• 开启主从同步

  start replica; -- 开启主从同步
  

• 从库信息中的 中继线程（Replica_IO_Running以及Replica_SQL_Running）这两个配置都必须是yes代表从跟主建立关系，并且能进行主从同步。