1. 什么是Mysql？
- 我们在开发的时候，我们都需要对业务数据进行存储，这个时候，你们就会用到MySQL、Oracal等数据库。
- MySQL它是一个关系型数据库，这种关系型数据库就有Oracal、 MySQL，以及最近很火的PgSQL等。
那什么是关系型数据库呢？
- 就是它是基于我们的SQL语句去执行操作的。
- 其实就是关系表数据库，由表结构来存储数据与数据之间的关系，同时用SQL（Structured query language）结构化查询语句来进行数据操作。
- 关系型数据库，对应会有一个非关系型数据库，像我们用的比较多的 Redis 这种Key - Value结构数据存储、hbase这种列存储格式、MongoDB这种文档存储等等。
那么关系型数据库相比非关系型数据的区别：
- 关系型数据库都是用表来进行维护，所以格式一致，可以统一用SQL语言来进行操作
- 关系型数据库都是表结构，所以灵活度不够，操作复杂的海量数据性能比较差，所以我们才会有表结构、索引以及索引优化。
- 虽然性能可能会比较慢，但是能做复杂的关联查询操作。 比如一对一， 一对多，多对多等等。
官网介绍：
https://www.oracle.com/mysql/what-is-mysql/
MySQL 的优势：
- 易用性：开发者可以在几分钟内安装好MySQL，数据库易于管理。
- 可靠性： MySQL 是最成熟、使用最广泛的数据库之一。超过 25 年，它已经在各种场景中进行了测试，其中包括许多世界上最大的公司。由于MySQL 的可靠性，组织依赖 MySQL 来运行关键业务应用程序。
- 可扩展性： MySQL 可扩展以满足最常访问的应用程序的需求。MySQL 的本机复制架构使 Facebook 等组织能够扩展应用程序以支持数十亿用户。
- 高性能： MySQL HeatWave比其他数据库服务更快且成本更低，多项标准行业基准测试证明了这一点，包括 TPC-H、TPC-DS 和 CH-benCHmark。
- 高可用性： MySQL 为高可用性和灾难恢复提供了一套完整的本机、完全集成的复制技术。对于关键业务应用程序，并满足服务级别协议承诺，客户可以实现 零数据丢失以及秒级的故障转移恢复。
- 安全性： 数据安全需要保护和遵守行业和政府法规，包括欧盟通用数据保护条例、支付卡行业数据安全标准、健康保险可移植性和责任法案以及国防信息系统局的安全技术实施指南。MySQL 企业版提供高级安全功能，包括身份验证/授权、透明数据加密、审计、数据屏蔽和数据库防火墙。
- 灵活性： MySQL 文档存储为用户开发传统 SQL 和 NoSQL 无模式数据库应用程序提供了最大的灵活性。开发人员可以在同一个数据库和应用程序中混合和匹配关系数据和 JSON 文档。
Mysql服务安装
- 见官网：https://dev.mysql.com/doc/refman/8.0/en/installing.html
Mysql连接或者断开服务器
- 官网：https://dev.mysql.com/doc/refman/8.0/en/connecting-disconnecting.html
Mysql里面的基本库表信息
库基本操作
- 我们叫它数据库，数据库，所以，在表的前面还有库的概念，操作查询库信息，这些基本操作就不演示了。
- 官网：https://dev.mysql.com/doc/refman/8.0/en/database-use.html
查询所有的库：
创建库：
查询当前选择的库：
表基本操作
- 官网：MySQL :: MySQL 8.0 Reference Manual :: 13.1.20 CREATE TABLE Statement
系统库表
- 我们发现除了我们自己创建的库以外，还有很多系统的库、以及表来保证MySQL的系统运行。
官网：MySQL ：： MySQL 8.0 参考手册 ：： 5.3 mysql 系统架构
三个系统库：
- mysql库：这一个系统库是来保证我的数据库服务正常运行的一个系统表全部在这个库里面。
- information_schema库：包括InnoDB里面的数据、日志、事务、表；还有PROCESSLIST表：它记录了当前正在运行的数据库有多少个连接和查询进程的信息；ENGINES表：存储引擎插件表。
- performance_schema库 - 性能库：这个库里面所有的数据是当前只存在内存里面的，这个库下面所有的事件、锁(比如data_locks表) - 我会去锁哪些数据。这些数据它只针对我当前的服务，如果重启了，所有的数据会丢失，所以这个它只存在当前内存。
mysql系统库下几个重要的表：
- 数据字典表（Data Dictionary Tables）
- 授权表（Grant System Tables）
- 对象信息表（Object Information System Tables）：plugin 插件注册表 等待
- 日志系统表(Log System Tables)：
- general_log：一般查询日志表。
- slow_log：慢查询日志表。
日志配置：
show variables like 'general_log'; //一般查询日志，默认关闭
SELECT @@long_query_time;
show global variables like 'long_query_time';
show global variables like 'min_examined_row_limit'; //至少需要检索这么多行
show global variables like 'slow_query_log'; //是否开启慢日志查询 默认关闭
SET GLOBAL slow_query_log=1;
set global long_query_time=0.1; //超过100毫秒
log_output=table |file |none //设置是放在文件中，还是在mysql.slow_log表中Sql语句的执行流程
- 客户端发送一条语句给到服务器，然后服务器它能给你一个它的数据。
一. 连接器 - 跟MySQL服务器建立连接
- 只有建立连接以后，我才能够发送SQL语句给到MySQL服务器，MySQL服务器它才能够去进行接收，这样才能进行网络IO。
- 连接的过程需要先经过TCP三次握手，因为MySQL是基于TCP协议进行传输的，如果MySQL服务并没有启动，则连接会报错；如果MySQL服务正常运行，完成TCP连接的建立后，此时连接器就要开始验证你的用户名和密码，如果用户名或密码不对，就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
- 如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；
连接管理
- 首先，我们得有连接，那么Mysql里面就有一个连接层，来管理连接，我们看下跟连接有关的变量/参数
- 变量：随着我的服务的运行，它会变更的，这些变量会随着我的客户端连接的变多而变多。
MySQL的四个线程状态变量：MySQL ：： MySQL 8.0 参考手册 ：： 5.1.6 服务器状态变量参考
- Thread_cached：是MySQL的一个状态变量，表示MySQL线程缓存中当前缓存的线程数，是为了我的服务，不用每一次客户端建立连接的时候都去创建一个线程，以此减少线程的创建和销毁的开销，提高数据库性能，所以它有一个缓存的线程数。当一个客户端连接到MySQL数据库服务器时，服务器会为该连接创建一个线程来处理客户端的请求。线程缓存的作用就是在该连接请求结束后，将这些线程缓存在内存中，以便下次有新的连接请求时能够复用这些线程，而不需要重新创建。thread_cache_size是MySQL的一个静态配置参数，用来配置线程缓存的大小，默认是-1，需要手动调整(在MySQL配置文件当中配置)并重启MySQL服务才能生效，最大是16384。
- Thread_connected：我当前打开的线程数，就是我现在有多少个线程是打开的。
- Thread_created：总共创建的线程数，即创建的线程总数。创建的线程总数越多，我们的thread_cache_size 可以对应的更大，来提升线程的缓存命中率。
- Threads_running：正在运行的线程数
-- 查看MySQL的四个Thread线程状态变量
show status like 'Thread%';
-- 查询thread_cache_size系统变量
select @@thread_cache_size;
查看MySQL服务被多少个客户端连接 / 查看当前正在运行的线程：
-- 显示当前正在运行的线程
show full PROCESSLIST;
该查询将返回一个结果集，包含所有当前连接的信息。每一行代表一个连接，每个连接的信息包括
id：线程 ID
State：连接状态
User：操作的用户名
Host：主机 / IP
db：操作的数据库
command：当前连接执行的命令：Sleep-休眠、Query-查询
Time：这个状态持续的时间，单位是s
info - 信息：查询会有查询的信息，但是长度有限制，可能不全
删除阻塞线程
- 官网：https://dev.mysql.com/doc/refman/8.0/en/kill.html
使用PROCESSLIST表可以查看当前正在运行的连接-线程，假如当前有客户端连接已经阻塞了，那么此时你可以去把它KILL掉：
demo
1. 会话一：
SELECT * FROM product_new --表中有500W数据，查询很慢2. 会话二：
SHOW PROCESSLIST; -- 查看当前线程执行结果：
3. 关闭查询query：
KILL QUERY 9328; -- 终止查询
查看会话一的结果：[Err] - Query execution was interrupted 请求被中断
4. 杀死 / 关闭连接线程 - Connection Thread
KILL 2280; -- kill 连接线程查看会话一的结果：
- [Err] - Lost connection to MySQL server during query - 丢失连接Connection
其他连接相关配置参数：
show status like 'Max_used_connections%';
- Max_used_connections 自服务启动以来最大的连接数
- Max_used_connections_time 达到这个峰值的时间
几个全局系统变量：
-- 查询最大连接数 默认151
SELECT @@max_connections;
select @@GLOBAL.max_connections;
-- 手动设置最大连接数
SET @@GLOBAL.max_connections = 1000;
-- 查询服务器超时等待时间 默认28800s - 8h
SELECT @@wait_timeout;
select @@GLOBAL.wait_timeout;
-- 手动设置服务器最大等待时间
SET @@GLOBAL.wait_timeout = 1000;
- max_connections：最大的连接数，即我的服务最多能开启的连接数，超过该值不允许建立连接，默认151，最小1，最大 100000。如果开启太大，同时会有很多的客户端来进行连接操作，MySQL性能可能会跟不上；如果开启太小，可能在高并发场景下导致并发量上不来。超过默认值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。
- wait_timeout：空闲连接最大的等待时间 / 服务器超时等待时间 / 非交互连接等待的时间(单位s)，默认28800s，也就是8小时，用于指定一个连接在空闲状态下的最长等待时间。如果一个连接在8小时内没有进行任何操作，那么MySQL服务器会自动关闭该连接，以释放资源。wait_timeout => 服务器什么时候会自动关闭?
跟服务器建立完连接之后，此时就代表客户端能跟服务端去进行通信了，也就是客户端能向服务端去发送SQL语句的请求了。
MySQL Server中的第一层 - 网络连接层
-- MySQL 8.0版本以后就已经移除掉了缓存模块：查询缓存 --
- 连接器的工作完成后，客户端就可以向 MySQL 服务器发送 SQL 语句了，MySQL 服务器收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。
- 如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。
- 如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。
这么看，查询缓存还挺有用，但是其实查询缓存挺鸡肋的。
对于更新比较频繁的表，查询缓存的命中率是很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。
所以，MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。
二. 解析器 - 解析SQL
- 当建立连接后，客户端向服务端去发送SQL语句请求时需要解析SQL语句，会把一条SQL语句解析生成语法树；因为MySQL不是人，不会一眼看到SQL语句就知道要做什么事情。
- 所以它会借用解析器去把SQL语句解析出来，看是否符合我们的SQL语法，最终生成一个语法树(理解为一个数据结构)。
解析器分为词法解析跟语法解析！
词法解析(器)
- 将SQL语句打碎，转化成一个一个关键单词 => 然后交给语法解析(器)去构建语法树，判断语法是否正确
语法解析(器)
- 语法解析已经知道每个SQL语句的单词了，那么在语法解析的时候，会去判断 / 检查语法是否正确，比如，where是不是写出where1，from写成from1；
- 如果发现SQL语法错误，则MySQL直接抛出相应的错误信息，并拒绝执行该SQL语句 => You have an error in your SQL syntax；
- 否则会去解析构建出SQL语法树，以便后面模块获取SQL语句类型、表名、字段名、where条件等等。
总结：
- 先将输入的SQL语句解析为语法树，然后对语法树进行语法检查，这样可以确保在执行之前先判断SQL语句是否符合MySQL的语法规则，避免执行无效或错误的语句。
- 注意：表名不存在或者字段名不存在，并不是在解析器里做的，解析器只负责检查语法和构建语法树，但是不会去查表名或字段名是否存在。
三. 预处理器 / 预编译器（可做可不做）
预处理 / 预编译的两个作用：
- 提升性能
- 防止SQL注入，更安全
- 判断表名、字段名是否存在、用户是否有操作权限等等。
- 将 select * 中的 * 符号，扩展为表上的所有列！
MyBatis中的SQL注入是MyBatis去做的参数化，而这里的SQL注入是我们的MySQL服务器自己能支持的，预处理器它是我们的MySQL服务能支撑的。
什么是预处理？
- 以我们的工作场景为例，一个查询接口，SQL语句都是一样的，但是每次查询的参数都不一样，所以我们想只需要变更参数部分就行。
- 那么，我们就是在拼接SQL语句的时候，将用户的输入跟语句拼接成一个SQL语句给到MySQL执行。
- 但是会发生一个SQL注入问题！
什么是SQL注入？
- 因为参数是客户端传过来的，所以可以传任何值，那么就有可能传入任何值就有了SQL注入问题。
-- 要执行的SQL语句
select * from emp where password = '';
-- SQL注入演示-客户端传入查询的参数为: ' or '1' = '1
select * from emp where password = '' or '1' = '1';
SQL注入是因为客户端拼接用户传入的参数，然后拼接好语句给到MySQL，这样会导致会安全问题。
那么能不能把这个参数化的事情交给MySQL自己做呢？
- 当然可以，这个就是预处理。
- 如果你需要参数化，你只要告诉MySQL，传一个预处理语句就行，MySQL会将参数与语句编译分开。
预处理操作解决SQL注入
官网地址：
MySQL :: MySQL 8.0 Reference Manual :: 13.5 Prepared Statements
预处理语句的工作流程 / 预处理语句为什么能够防止SQL注入？
- 首先在应用程序中，应该去创建一个预处理语句，将SQL查询与占位符(通常使用问号?来表示)组合起来，形成一个带有参数占位符的SQL查询语句。
- 接着，应用程序将这个预处理语句发送到MySQL服务器进行编译。服务器会对SQL语句进行语法解析和执行计划生成，但不会执行实际的查询。
- 在执行查询之前，应用程序通过绑定参数的方式将实际的参数值与占位符关联起来，这样可以防止SQL注入攻击，因为用户输入的参数不会直接嵌入到查询语句中，而是作为参数传递给服务器，并允许在多次执行中重复使用预处理语句(因为预处理语句只需编译一次，再次执行时就无需重新编译，以此提高性能)。
- 一旦参数绑定完成，应用程序可以多次执行相同的预处理语句，通过更改参数值来获取不同的结果。
预处理Demo，防止SQL注入：
-- 创建预处理语句
-- PREPARE 预处理名字 from 'SQL语句';
PREPARE select_user from 'select * from emp where password = ?'
-- 绑定参数(设置参数值)
SET @passsword = '123456';
-- 执行预处理语句
-- EXECUTE 预处理名字 USING @绑定参数名;
EXECUTE select_user USING @passsword;
-- 清除预处理语句
-- DEALLOCATE PREPARE 预处理名字;
DEALLOCATE PREPARE select_user;预处理主要做主要做以下四个事情：
- 将语句编译、优化跟参数分开处理，当执行SQL语句相同，参数不同的场景，提升性能。
- 因为是参数化去执行的，而不是拼接参数，从而解决了SQL注入问题。
- 判断表名、字段名是否存在、用户是否有操作权限等等。
- 将 select * 中的 * 符号，扩展为表上的所有列！
比如我们经常被问的Mybatis里面#跟$符号的区别：
- #符号执行SQL时，会将#{...}替换成？，生成预编译SQL / 预处理SQL，然后进行预处理，能防止SQL注入，并且必须传入参数；
- $符号会拼接SQL，直接将${...}参数拼接在SQL语句中，存在SQL注入问题。
四. 优化器 - 决定怎么做 => 生成执行计划，确定执行方案
- 根据上面的流程，我们知道要去执行什么语句，但是具体怎么执行会有很多的方式，比如走哪个索引，要不要回表，要不要去在内存里面排序，你的语句是不是可以优化等等。
做哪些优化，有哪些优化器，通过全局变量
optimizer_switch
来决定，
控制优化器策略的一种方法是设置 optimizer_switch 系统变量。
具体参数如下：
-- 获取MySQL数据库中的全局优化器开关配置信息
SELECT @@GLOBAL.optimizer_switch;
- 优化器说明：https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html，里面包含了每个优化选项说明.
- 优化方式官网地址：https://dev.mysql.com/doc/refman/8.0/en/optimization.html；这个里面就有很多优化器的实践，比如优化sql语句等等。
- 优化器它是基于服务，觉得自己最快的一些方式去执行这个语句，优化后会生成一个最优的执行计划，并将该执行计划传递给存储引擎层，所以这个语句到底怎么走，优化器来决定。
- 它是基于内存与CPU或性能的消耗得到一个算法或者说得到哪一个执行计划它是最快的或查询成本最小的。
- 当前，优化器里面有一些东西它是可以自己设置的，比如说要不要索引下推，比如说要不要回表，比如说要不要去用联合索引，要不要用hash_join，要不要用跳跃扫描skip_scan，要不要用mrr，mrr是它底层的一个算法，能够更快的跟我的磁盘去进行交互等等。
- 优化器负责将 SQL 查询语句的执行方案确定下来！
- 要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引，比如下图的 key 为 PRIMARY 就是使用了主键索引。
- 如果查询语句的执行计划里的 key 为 null 说明没有使用索引，那就会全表扫描（type = ALL），这种查询扫描的方式是效率最低档次的。
MySQL Server中的第二层 - 核心服务层，核心服务层不牵扯到数据的存储与查询。
五. 执行器 - 去操作数据的 - 真正跟存储引擎进行交互
- 执行器根据执行计划，去调用数据存储的地方，也就来到了我们MySQL Server中的第三层 - 存储引擎层！
- 存储引擎层是真正的跟数据进行交互的，是真正的来保存以及怎么去查询数据的，所以存储引擎层决定了我这个数据以什么样子的方式来保存，比如说你是保存到磁盘，还是保存到内存，还是磁盘跟内存都有。
- 执行器去根据表设置的存储引擎，调用不同存储引擎的API接口获取数据。
- 至于这个数据是怎么存的，这个数据有哪些优化（比如内存去缓存）等等，就是每个存储引擎自己去做的事情，也就来到了我们MySQL Server中的第三层中的存储引擎层，并且存储引擎是跟MySQL解耦的，存储引擎跟MySQL的开发者都不是同一批人。
- 存储引擎它是我们MySQL的一个插件，你如果有能力，MySQL都支持你自己写存储引擎。
- 基于不同的一些场景，比如说有一些场景我要去保证性能，有一些场景我要去保证一致性，所以它会有不同的存储方案，这里就牵扯到我们不同的存储引擎。
那么官网提供了哪些存储引擎？
- 官网：MySQL :: MySQL 8.0 Reference Manual :: 16 Alternative Storage Engines
可以通过语句查询当前服务器支持哪些存储引擎：
SHOW ENGINES; -- 查询当前服务器支持的存储引擎
InnoDB
- MySQL 8.0默认的存储引擎。InnoDB是一个事务安全(兼容ACID) 的MySQL存储引擎，具有提交、回滚和崩溃恢复功能，以保护用户数据。
- InnoDB支持行级别的锁(没有升级到更粗粒度的锁)和Oracle风格一致的非锁读取提高了多用户并发性和性能。
- InnoDB将用户数据存储在聚集索引中，以减少常见的基于主键的查询的I/O。
- 为了维护数据的完整性，InnoDB还支持外键引用完整性约束。
MyISAM
- 这些表占用空间很小。
- 表级锁限制了读/写工作负载的性能，因此它经常用于Web和数据仓库配置中的只读或以读为主的工作负载中，所以它的性能要比InnoDB要高。
Memory
- Memory它只是把数据存储在内存，它不会做持久化。
不同的存储引擎会有自己不同的存储实现方式 / 存储方案，不管是什么存储引擎，它一定要做的事情是把这个数据保存起来，是用内存还是磁盘，还是都用，或者磁盘的文件格式等等都会不一样。
- 既然要保存，那么就一定要有个数据的目录，这个目录，也就是我们的一个变量，这个变量就是代表你保存到哪里。
接下来我们看下数据到底存储在哪里，以什么样的方式存储？
六. 存储层 - 数据存储地址
SQL语句查询：
-- 查询数据库的数据目录
select @@datadir;
show variables like '%datadir%';
该目录就是我们的数据库的数据目录，我们的数据保存在该目录下。
不同的存储引擎，存储的文件以及格式都不一样，我们今天来重点分析下InnoDB的内存以及磁盘结构。
·································································································································
InnoDB存储引擎
InnoDB 的主要优势
- 它的 DML 操作遵循 ACID 模型(原子性、一致性、隔离性、持久性)，事务具有提交、回滚和崩溃恢复功能以保护用户数据。请参阅MySQL ：： MySQL 8.0 参考手册 ：： 15.2 InnoDB 和 ACID 模型”。
- 行级锁定和 Oracle 风格的一致性读取提高了多用户并发性和性能。请参阅MySQL ：： MySQL 8.0 参考手册 ：： 15.7 InnoDB 锁定和事务模型。
- InnoDB 表将您的数据排列在磁盘上以优化基于主键的查询。每个InnoDB 表都有一个称为聚簇索引的主键索引，它组织数据以最小化主键查找的 I/O。
- 为了保持数据完整性，InnoDB 支持 FOREIGN KEY外键约束。对于外键，检查插入、更新和删除以确保它们不会导致相关表之间的不一致。请参阅MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.5 FOREIGN KEY Constraints。
InnoDB的架构 - 数据磁盘结构
- 首先，我们的 InnoDB 数据是肯定会落到我们的磁盘！文件后缀我们知道肯定是ibd。
- ibd文件是我们默认的InnoDB的存储文件。
那么存在什么空间？我们就有一个表空间的概念。
- 简单一点，表空间就是存储表和索引数据的位置，就是用来管理表与索引数据的。
表空间 - TableSpaces
官网地址： MySQL :: MySQL 8.0 Reference Manual :: 15.6.3 Tablespaces
- InnoDB的存储结构：InnoDB肯定会保存到磁盘，并且默认每个表都会有一个独立的ibd文件，用专有名词就叫做表空间，表空间就是我这个数据到底怎么去保存到我的磁盘的，这一个叫做表空间。表空间来决定了我这个数据是怎么存的，存在哪一个文件。
独立表空间 - File-Per-TableSpaces：
- 准确一点儿应该叫独立表空间{现在默认开启的}：就是我每个表对应着文件系统中都有个名为"表名.ibd"独立实际的ibd数据文件，好处在于数据相对来讲它是隔离的。
- InnoDB存储引擎的独立表空间为 .ibd 文件~！
独立表空间优点：
怎么决定我的数据与索引文件放在哪个表空间？
- 最关键的innodb_file_per_table参数，代表是存储到系统表空间，还是独立表空间，1 代表开启了独立表空间，数据文件会保存在file_per_table独立表空间下，然后以表名.ibd保存。
-- 查看是否启用了每个表的独立表空间 1或者ON代表开启,0或OFF表示关闭 select @@innodb_file_per_table; -- 1 show variables like '%innodb_file_per_table%'; -- ON -- 也可以设置为0或OFF进行关闭独立表空间,进而变成了系统表空间 set global innodb_file_per_table = 0; set global innodb_file_per_table = OFF;系统表空间 - System TableSpace：
- 我想把所有的表都放在一个文件里面 => 系统表空间：所有的表的数据都在一个文件或者说在多个指定的文件里面。
- 官网：MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables
-- 查询系统表空间配置 / 查询系统表空间数据文件的文件名和文件大小 select @@innodb_data_file_path; SHOW VARIABLES LIKE '%innodb_data_file_path%';
- ibdata1: 文件名
- 12M: 默认文件大小
- autoextend 自动扩容，扩容大小由innodb_autoextend_increment决定。
-- 查看扩容的大小，默认扩容增量为64M SHOW VARIABLES LIKE '%innodb_autoextend_increment%';
- 如果扩容到64M还不够，我们可以采用多个数据文件来提升系统表空间， 可以设置innodb_data_file_path，但是autoextend 只对最后一个文件生效，并且，不能对已经生成的文件的大小进行更改。
通用表空间 - General TableSpaces：
创建通用表空间：
-- 创建通用表空间 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;添加数据后，我们发现数据存储在huihui.ibd通用表空间中！删除通用表空间：-- 删除通用表空间,但是必须先保证通用表下没有表 DROP TABLESPACE huihui_tablespace;通用表空间的限制:
- 同时通用表空间只能创建在已知目录，已知目录是由datadir、innodb_data_home_dir和innodb_directories变量定义的目录。
临时表空间
- 官网：MySQL ：： MySQL 8.0 参考手册 ：： 15.6.3.5 临时表空间
Demo
会话一：CREATE TEMPORARY TABLE temp_table ( id INT, name VARCHAR(50) ); -- 创建临时表 SELECT * FROM temp_table; -- 能查询临时表会话二：SELECT * FROM temp_table; -- 会报表不存在错误临时表空间在innodb_temp_tablespaces_dir进行配置：-- 查询临时表空间的目录路径 SELECT @@innodb_temp_tablespaces_dir;
InnoDB引擎的逻辑存储结构：表空间中数据存储
官网地址：MySQL ：： MySQL 8.0 参考手册 ：： 15.11.2 文件空间管理
- 在MySQL当中，所有的数据存储都是基于一行，表中数据存储最底层或者说最原子的单位是 row 行，但是不是通过row来直接保存文件的。
表空间 - TableSpaces - .ibd文件
- 表空间是InnoDB存储引擎的逻辑存储结构的最高层，如果用户启动了参数(innodb_file_per_table)，在MySQL 8.0版本中默认开启，则每张表都会有一个表空间(xxx.ibd文件)，一个MySQL实例可以对应多个表空间，用于存储记录、索引等数据。
segement - 段
extent - 区
- 区来管理页，一个 extent - 区 会有很多的page页，一个 entent - 区默认占用1M的空间
- 当页的大小在16K，则一个区的大小是1M，即连续64个Page页就是一个extent区，如果32K是2M，64K则为4M，连续的 64 个页会被划为一个区，后面磁盘释放分配都是以区为单位。
- 一个 exnent - 区 默认有连续的 64 个页组成！(1M = 1024K，1024K / 16K = 64)
page - 页
- InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。
- 页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。
- page页可以理解为是一个数据结构，page页里面保存的是行数据！
- page页是保存到文件里面的。
- 每个表空间都是由相同大小的page页来组成的，默认page页的大小为16KB，即最多能保证16KB的连续存储空间，一个page页能保存的row行数据也是有大小的，一个表的数据可能会有很多很多的page页；
每个表中的数据分为多个页。- 为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4~5个区！
可以根据innodb_page_size来设定页的大小，可设置的值有：64KB，32KB，16KB(默认)，8KB和4KB：
-- 查询页大小,默认是16KB - 16384字节 select @@innodb_page_size;
- 然后page页又不是直接放在表空间的，而是会用其它的一些数据结构来封装，目的是为了提高数据磁盘的扩展性，管理空间分配与性能以及利于磁盘的分配，又有segement、extent的概念。
- 就比如人 假如一个一个管理很麻烦，但是给你们圈起来，比如在某个班，就很好管理了。
行 - Rows
- 官网：MySQL :: MySQL 8.0 Reference Manual :: 15.10 InnoDB Row Formats
- 数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。
- 表的行格式决定了其行的物理方式存储，这反过来会影响查询和DML的性能操作。
内存缓存结构（BufferPool - 缓冲池）
- 预读 - 提前加载数据到内存
- 不管你是怎么存储的，都需要去跟磁盘进行交互(Memory引擎除外)，因为你要去保证数据的持久性与可靠性，这样性能很慢 => 磁盘IO导致！因为每次操作查询都去跟磁盘进行交互！
- 此时InnoDB为了提升读写性能，采用了内存缓存机制，InnoDB引擎引入了一个中间层/缓存层 - BufferPoll，BufferPoll它是一个内存区间，是在内存上的一块儿连续空间，它的主要用途就是用来在内存中缓存相应的数据页 - Page，每个数据页 - Page的大小是16KB，它的目的是为了减少磁盘IO，防止每次操作数据时需要实时的跟磁盘进行交互。
- 在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等！
- 在SQL的执行过程中，无论是增删改查，都是优先在Buffer Pool中进行的，这样可以极大的保证执行效率！
缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page页分为三种类型：
- free page - 空页：空闲page，未被使用。
- clean page - 干净页：被使用page，数据没有被修改过。
- dirty page - 脏页：被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。
- 页是InnoDB做数据存储的单元，无论是在磁盘，还是Buffer Pool中，都是按照Page - 页读取的，这也是一种"预读"的思想。
- 官网：MySQL ：： MySQL 8.0 参考手册 ：： 15.5.1 缓冲池
-- 获取InnoDB缓冲池BufferPool的大小,默认大小是128M select @@innodb_buffer_pool_size; -- 134217728字节(/1024/1024=128M) -- 设置BufferPool的大小 set global innodb_buffer_pool_size = xxx;那么内存跟磁盘到底怎么交互呢？
内存与磁盘数据交互机制 - page页加载机制
- 我们知道磁盘里面存的数据首先是表空间，表空间里面的单元是page页 ，页里面的数据是行。
为什么要用page页，有几个点考虑：
- 如果用行交互，那么假如我查询200条数据，那么200条数据都不在我们内存的话，需要跟磁盘交互200次，性能低下！
- 也不会用extent - 区来交互，因为一个extent - 区包含64个page - 页。可能我只需要查一条数据，但是会加载64个页到内存，导致内存浪费。
所以，基于内存的利用率与性能考虑，InnoDB选择了page - 页！
有了Buffer Pool之后取 / 查询数据的流程
- 举例：select where id = 5 的数据，原来是每次查询都需要去磁盘进行IO交互。
- 此时有了BufferPool之后，InnoDB会先去BufferPool中查看是否存在该数据，如果存在，那么直接返回，数据就可以直接从内存中获取，避免了频繁的磁盘读取，从而提高查询性能；
- 如果不存在，需要跟磁盘进行IO交互，去磁盘中进行读取，去查，并且将查询返回的结果所在的这个Page页给到 / 保存到BufferPool中，并返回给客户端，后续如果再次读取就可以从Buffer Pool中就近读取了，所以BufferPool相当于是一个缓存。
注意：
- BufferPool跟磁盘交互的最小单位它不是一条数据，它是整个Page页，这也就意味着返回的这个Page页里面可能会有很多很多的数据，假如后面再去select查id = 4的数据，可能在内存中已经存在了，那么直接ffafan返回即可。
Page页是内存跟磁盘交互的最小单位。
磁盘预读机制
局部性原理：分为时间局部性和空间局部性
- 局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用，程序运行期间所需要的数据通常比较集中。
- 时间局部性：之前被访问过的数据有很大概率下一次又被访问到。
- 空间局部性：当程序访问一个内存位置时，它很可能在不久的将来也会访问附近的内存位置！
数据文件比较大的时候，可以考虑分块读取！
内存跟磁盘在进行交互的时候有一个最基本的逻辑单位，称之为Page页，即DataPage - 数据页！
- 这就是我们每次读取数据的时候，将对应的page页加载到内存；
那么有没有一种我就算没读，就能够提前把一些数据加载到内存呢？
- 就是我们经常讲的数据预热，Mysql提供了一种预读机制：提前加载数据到内存。
- 官网：MySQL ：： MySQL 8.0 参考手册 ：： 15.8.3.4 配置 InnoDB 缓冲池预取（预读）、
线性预读
- 可以在MySQL配置文件中设置此参数的值，或者使用SET GLOBAL语句动态更改它，该语句需要足够的权限来设置全局系统变量。
-- 获取触发异步读请求所需的连续页访问次数 select @@innodb_read_ahead_threshold; -- 56随机预读
- 随机预读是一种技术，它根据缓冲池中已经存在的页来预测何时可能需要这些页，而不管这些页的读取顺序如何。
- 如果在缓冲池中发现来自同一区段的13个连续页，InnoDB异步发出一个请求来预取 / 预加载 / 预读 区段的剩余页。
- 要启用此功能，请将配置变量innodb_random_read_ahead设置为ON。
-- 查看是否启用了随机预读技术,1或ON代表开启,0或OFF表示关闭 SELECT @@innodb_random_read_ahead; -- 0,说明默认是关闭的,可通过SET GLOBAL语句来开启
-- 获取当前MySQL数据库实例中InnoDB存储引擎的详细状态信息和SQL查询语句
show engine innodb status;既然有缓存了，那么就一定会有一个问题：内存跟磁盘的一致性问题！你改数据肯定会有个先后顺序，你是改磁盘呢还是改内存呢
数据同步机制
- 我们刚才讲了，我们的数据都会以page页的方式同步到我们的内存，在我们的系统中，就有2份数据，一份在内存，一份在磁盘，那么这2个地方是怎么去做数据同步的。
- 那么我操作数据，是不是也可以不用跟磁盘同步呢，直接去操作内存，然后通过异步刷新的方式同步到磁盘是不是更快。
- InnoDB就是这么干的。
- 比如修改操作：每次更改数据，会先去判断内中存是否存在，如果数据所在的Page页存在内存，我直接更改内存中Page页的数据，然后通过异步刷新的方式同步到磁盘。
脏页概念引出：
- 改了内存的数据，如果还没有同步到磁盘，那么这个数据所在的Page页叫做脏页。
- 脏页说白了就是我的数据跟磁盘不一致的页。
- 官网定义：脏页是指已经修改，但尚未写入磁盘上的数据文件的页。
干净页 & 空页
- 在内存里面没有被改动的页叫做干净页。
- 如果分配的Page页都还没有数据，则叫做空页。
我们看下我们InnoDB的数据结构 / 架构：
- 下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构~！
我们发现我们的Page页会有不同的颜色，这个就是代表不同的page，并且每个不同的类型会有不同的双向链表链接。
- 为了提高大容量读操作的效率，缓冲池被划分为可以容纳多行数据的页。
- 为提高缓存管理的效率，缓冲池实现为页的链表。
- 很少使用的数据使用最近最少使用(least recently used, LRU)算法的变体从缓存中老化。
- 了解如何利用缓冲池将频繁访问的数据保存在内存中是MySQL调优的一个重要方面。
InnoDB架构之内存结构
- Buffer Pool - 缓冲池
- Adaptive Hash Index：自适应哈希索引
- Change Buffer - 更改缓冲区
- Log Buffer - 日志缓冲区
InnoDB架构或体系结构之磁盘结构：
- System Tablespace - 系统表空间：系统表空间是Chage Buffer - 更改缓冲区的存储区域。系统表空间对应的文件参数：innodb_data_file_path；
- File-Per-Table Tablespaces - 每张表的独立表空间，参数：innodb_file_per_table；参数查询：show varibales like '%xxx%'；
- General Tablespaces：通用表空间，需要手动通过CREATE TABLESPACE 语法来创建通用表空间，在创建表时，可以指定该表空间。
- Undo Tablespaces - 撤销表空间：MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M）=> 撤销表空间对应的磁盘文件：undo_001和undo_002，用于存储undo log日志。
- Temporary Tablespaces - 临时表空间：InnoDB使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据！
- Doublewrite Buffer Files - 双写缓冲区（.dblwr）：InnoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据，保证数据的安全性！
- Redo Log：重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲区（redo log buffer，在Log Buffer缓冲区当中）以及重做日志文件（redo log），重做日志缓冲区是在内存中，重做日志是在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘发生错误时，进行数据恢复使用！Redo Log是循环写的，因此不会永久保存！以循环方式写入重做日志文件，涉及两个文件：ib_logfile0和ib_logfile1
我们了解了InnoDB的内存结构以及磁盘结构，那么我们在内存当中所更新的数据，是如何刷新到磁盘当中的呢？
- 此时，就涉及到了一组后台线程此时，就涉及到了一组后台线程，后台线程它的作用就是将InnoDB存储引擎的缓冲池当中的数据在合适的时机刷新到磁盘文件当中！
- 对于InnoDB存储引擎的后台线程中，分为四类，分别是：Master Thread、IO Thread、Pruge Thread、Page Cleaner Thread。
Master Thread
- 核心后台线程，负责调度其它线程，还负责将缓冲池当中的数据异步刷新到磁盘当中，保证数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收。
2. IO Thread
- 在InnoDB存储引擎当中大量使用了AIO - 异步非阻塞IO来处理IO请求，这样可以极大的提高数据库的性能，而IO Thread主要负责这些异步IO请求的回调！
线程类型 默认个数 职责 read thread 4 负责读操作 write thread 4 负责写操作 log thread 1 负责将日志缓冲区刷新到磁盘 insert buffer thread 1 负责将写缓冲区内容刷新到磁盘 3. Purge Thread
- 主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，所以需要通过Purge Thread来进行回收！
4. Page Cleaner Thread
- 协助Master Thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，从而减少阻塞！
Change Buffer - 更改缓冲区
- Change Buffer - 更改缓冲区：针对于非唯一二级索引页，也就是对唯一索引或者主键索引是不会操作更改缓冲区的。
- 更改缓冲区指的是在执行DML语句时，即增删改语句的时候，如果这些数据页在Buffer Pool当中不存在，则不会直接操作磁盘，而会将数据变更存在更改缓冲区 - Change Buffer中，在未来数据被读取时，再将数据合并然后恢复到Buffer Pool当中，然后再将Buffer Pool当中合并后的数据刷新到磁盘当中！
Change Buffer的意义是什么？
先来看一幅图，这个是二级索引的结构图：
- 与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引，同样，删除和更新可能会影响索引树种不相邻的二级索引页。如果每一次都操作磁盘，会造成大量的磁盘IO，而有了Change Buffer之后，我们可以在缓冲池当中进行合并处理，减少磁盘IO！
Adaptive Hash Index：自适应哈希索引
- InnoDB引擎默认是不支持Hash索引的，它支持的B+Tree索引！
- 我们知道Hash索引它最大的优势就是快，因为它只需要一次匹配就可以了，当前前提是不存在Hash冲突的情况下，而对于B+Tree来说，往往可能需要匹配两三次，虽然Hash索引查询速度快，但是它也有弊端，不适合范围查询，只能做等值匹配这样的操作！
- Adaptive Hash Index：自适应hash索引，用于优化对Buffer Pool的数据查询，也就是说InnoDB存储引擎会监控我们业务当中对各个表的索引页的查询，如果观察到hash索引可以提升查询速度，则InnoDB会自动建立hash索引，该功能就称之为自适应hash索引！
- 自适应hash索引，无需人工干预，是系统根据情况自动完成的！
- 自适应hash索引的开关的参数：adaptive_hash_index
Log Buffer - 日志缓冲区
- Log Buffer - 日志缓冲区：用来保存要写入到磁盘中的log日志数据（包含redo log和undo log），默认大小为16MB，日志缓冲区的日志会定期的刷新到磁盘当中。也就是说，我们在记录日志时，为了提高效率，我们可以先把日志记录在日志缓冲区当中，然后再刷新到磁盘当中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。
参数：
- innodb_log_buffer_size：日志缓冲区大小
- innodb_flush_log_at_trx_commit：控制日志缓冲区当中的数据刷新到磁盘时机
Buffer Pool的内部结构
整个Buffer Pool是由缓冲页和控制块组成的：
- 缓冲页：Buffer Pool中存放的[数据页]我们称之为[缓冲页]，和磁盘上的数据页是一一对应的，都是16KB，缓冲页的数据，是从磁盘加载到Buffer Pool当中的一个完整页！
- 控制块：它是缓冲页的[描述信息]，这一块儿区域保存的是数据页或缓冲页所属的表空间号、数据页编号、数据页地址、以及一些链表(Free链)相关的节点信息，每个控制块大小是缓冲页的5%左右，大约是800个字节！
Buffer Pool的内部结构如下，Buffer Pool的前一部分区域存储控制块，后一部分区域存储缓冲页，如果中间有未被利用的空间，应该就是内存碎片了！
Tips：Buffer Pool的初始化
- 数据库会在启动的事实，按照配置中的Buffer Pool大小，去向操作系统申请一块儿内存，作为Buffer Pool的内存区域，并在Buffer Pool中划分出一个一个的缓冲页和一个一个与其对应的描述数据(控制块)，此时的Buffer Pool就像一个干净的本子，没有书写任何内容~！
Free链
- 刚初始化的Buffer Pool，内存中都是{空白的缓冲页}，但是随着时间的推移，程序在执行过程中会不断地有新的页被缓存起来，那么怎么来判断哪些缓冲页是{闲置状态}，可以被使用呢？
- 此时就需要{控制块来进行标记和管理}了，InnoDB在设计之初，会将所有{空闲的缓冲页}所对应的{控制块}作为一个一个的节点，形成一个链表，这个链表就是Free链，翻译过来就是空闲链表，如下图：
Free链
- Free链的作用就是用来记录 或Free链表用来维护 哪些缓冲页可以被使用！
- 基节点指的就是根节点！
- 由上图可知，Free链表是一个双向链表，链表上除了控制块以外，还有一个基节点或者说根节点，它记录了链表的头节点指针、尾节点指针以及链表中的节点数量，即Free链由多少个描述信息块，也就是当前Free链表中有多少个空闲的缓冲页可以被使用！
怎么知道数据页是否被缓存？
我们知道Free链表用来保存空闲的页，但是，当下一次访问时，我们该如何知道当前要访问的页是不是已经被缓存了呢？
- 最直观的思路就是将Buffer Pool里缓存的数据{全部遍历一遍}，显然这样做并不合理，因为Buffer Pool的设计初衷就是为了提升效率的，如果有人将Buffer Pool配置的很大，比如32个G，那扫描这一片区域所浪费的时间反而成了累赘！
- 事实上，我们可以使用{表空间号 + 页号}就可以确定一个唯一的页，我们可以去设计一个Hash表，使用{表空间号 + 页号}当做Key，使用{控制块地址}作为Value，每次查询去查该对应的页是否在Hash表中有其对应的控制块地址，顺着其控制块地址就可以找到缓冲页，如果能找到对应的控制块地址，则说明该Page页已经被缓存；反之没有被缓存！
Flush链表
- 但是现在要解决一个问题，我刷脏的时候需要去找到我们的脏页，脏页现在我们知道，在LRU链表有，但是LRU也有干净的页，并且页会比较多，我们去找那些脏页会比较慢。
- 所以，在InnoDB的Buffer Pool中，将脏页信息单独放在一个链表，刷脏的时候，只需要从这个链表找就可以了，那么这个链表就叫做Flush链表。
- Flush链表和Free链表一样，同样是一个双向链表！
LRU链表：缓冲池 LRU-最近最少使用算法
LRU算法它是根据数据的历史访问记录来进行数据的淘汰，它最核心的思想就是数据如果最近被访问的话，那么将来它被访问的几率也就越高！
LRU算法它的本质是一个典型的淘汰策略！
LRU算法的由来
我们知道内存空间是有限的，Buffer Pool更是有限的，当我们的数据量很大以后，Buffer Pool仅仅只能容纳很少一部分数据，所以Buffer Pool的容量很有可能被使用完，此时如果我们还想继续缓存更多的数据页，就需要淘汰掉一部分已经缓存的数据页，我们可以将最近使用最少的缓冲页给淘汰掉，这就是典型的LRU算法（Least Recently Used）！
普通或传统的LRU算法：
普通的LRU(链表)算法：LRU = Least Recently Used（最近最少使用）：就是末尾淘汰法，新数据从链表头部加入，当某个数据被使用时，也会将其移动到链表的头部；释放空间时从末尾淘汰。
传统的LRU链表
- 当需要访问某个页时，如果不在Buffer Pool中，则需要把该页加载到Buffer Pool缓冲池，并且把该缓冲页对应的控制块作为节点添加到LRU链表的头部；
- 当要访问某个页时，如果在Buffer Pool种，则直接把该缓冲页对应的控制块移动到LRU链表的头部；
- 当需要释放空间时，从最末尾淘汰
普通LRU链表的优缺点：
优点：
- 所有最近使用的数据都在链表表头，最近未使用的数据都在链表表尾，保证热数据能最快被获取到！
缺点：MySQL为什么改进LRU算法？
- 如果发生全表扫描（比如没有建立合适的索引或查询时使用select *等），则有很大可能将真正的热i数据淘汰掉！因为SQL查询来了新的数据，根据LRU算法被置换之后的链表头部存放的是全表扫描的数据的数据页，但是全表扫描的数据可能我就用这么一次，导致热数据被移动到链表的表尾最终被淘汰，被淘汰的这部分数据才是经常被使用的数据！
- 由于MySQL中存在预读机制，很多预读的页都会被放到LRU链表的头部，如果这些预读的页都没有被用到的话，这样会导致很多末尾的缓冲页很快会被淘汰掉！
改进型LRU算法
- 缓冲池使用LRU算法的一种变体作为一个链表来管理。
- 当需要空间向缓冲池添加新页时，会将最近最少使用的页清除，并将新页添加到列表的中间。
这种中点插入策略将列表视为两个子列表：
- 在头部，是最近访问过的新("年轻")页的子链表
- 在尾部，是最近访问次数较少的旧页的子链表
缓冲池列表
- 该算法将经常使用的页保存在新的子链表中；旧的子列表包含不常用的页，这些页面是被驱逐的候选者。
默认情况下，该算法的操作如下：
- 缓冲池的3/8用于旧的子列表(冷数据区)；LRU链表的中点是新子列表的尾部与旧子列表的头部的边界。
- 当InnoDB读入一个页到缓冲池时，它首先将它插入到中间位置(旧子列表的头部)。
- 访问旧子列表中的页面会使其"年轻"，将其移动到新子列表的头部。
- 当数据库运行时，缓冲池中未被访问的页通过向链表的尾部移动来"老化"。
- 新旧子列表中的页都会随着其它页的更新而老化。
- 旧子链表中的页也会老化，因为页是在中间位置插入的。
- 最终，剩余未使用的页到达旧子列表的尾部并被移除。
异步刷脏 - 从缓冲池中刷出脏页(缓冲池的冲洗)
这个异步到底怎么去刷的呢？这些脏页怎么同步到磁盘的？
- 它是开启异步线程去做的。
是不是每次更改一条数据，我就把这个数据所在的Page页同步到磁盘呢？
- 假如每次操作都需要先跟磁盘同步，我们发现有以下几个问题：
- 所以，如果每次都先保证同步到磁盘，那么操作数据会非常非常非常慢。
因此，采用的是异步刷盘机制！
- 官网地址：MySQL :: MySQL 8.0 Reference Manual :: 15.8.3.5 Configuring Buffer Pool Flushing
首先，刷脏是由异步线程去做的。
提问，什么是刷脏？
- 刷脏就是把脏页的数据同步到磁盘！
-- 查询 页面清理线程 - page cleaner线程 的数量,默认值为4,但是不能超过Buffer-Pool的实例数 select @@innodb_page_cleaners; -- 1 -- 查询缓冲池实例的数量 select @@innodb_buffer_pool_instances; -- 1 -- 以下两个变量控制内存中的脏页量 -- 查询默认的低水位值,默认为10 -- 当脏页的百分比达到缓冲池页面的默认低水位值时,InnoDB存储引擎将开始刷新脏页的行为 select @@innodb_max_dirty_pages_pct_lwm; -- 10 -- 查询内存中可以存在脏页的最大百分比,默认值为90 select @@innodb_max_dirty_pages_pct; -- 90
1. 自适应刷脏/冲洗
- 在DML活动较多的情况下，如果刷出不够积极，则刷出可能会落后，如果刷出过于积极，则磁盘写操作可能会耗尽I/O容量。
- InnoDB采用自适应刷出/刷新算法，通过跟踪缓冲池中脏页的数量和重做日志记录的生成速度来动态调整刷新速率，其目的是通过确保刷新活动与当前工作负载保持同步来平滑整体性能。根据这些信息，它决定每秒从缓存池中刷写多少脏页。
- 动态调整刷新速率有助于避免吞吐量突然下降
- 自适应：根据脏页产生的频率来决定刷脏的频率。比如去判断脏页占比整个Buffer-Pool的大小，如果超过该大小，就会去刷脏。
2. RedoLog自适应
- 就是我发现RedoLog保存不了的时候，你要去刷盘。即RedoLog快满的时候，要去刷脏。
3. 空闲时间刷新 - 空闲的时候会去刷脏
-- 表示在空闲时刷新脏页的百分比,默认是100,表示将全部脏也刷新 -- 刷脏是要跟磁盘进行IO交互的,如果频繁刷脏,肯定是会有问题的 select @@innodb_idle_flush_pct; -- 100
- 刷脏是要跟磁盘进行IO的，如果很频繁的去刷脏，那肯定会有问题。
4. 服务正常关闭
- 肯定会保证我的脏页能够保存到磁盘，因为我要去防止它的数据丢失。
Doublewrite Buffer - 双写缓存区 - InnoDB的三大特性之一
InnoDB引擎的四大特性
- 插入缓冲 - Buffer Pool
- 二次写 - Double Write
- 自适应哈希索引
- 预读 - Read Ahead
- 双写缓冲区是InnoDB的四大特性之一，还有三个是Buffer Pool，简称BP、自适应Hash索引、预读（Read Ahead）！
- doublewrite缓冲区是一个存储区域，DoubleWrite双写就是当page页刷新到磁盘的时候，把这个page页的数据写到不同的地方去，当出现问题时，有备份的来达到持久性跟数据的一致性。
- 我们知道了，内存跟磁盘交互的最小单位是Page页，我们的page页会异步刷新到磁盘，但是page页的大小是16k，而操作系统 的是4K，一个页是需要多次同步到磁盘的，如果在页面写入过程中出现有操作系统、磁盘(存储子系统)或者意外mysqlId进程退出，所以Mysql提供了一个doubleWrite机制， InnoDB可以在崩溃恢复期间从doublewrite缓冲区找到页面的一个很好的副本。
- 尽管数据写两次，doublewrite buffer并不需要两倍的I/O开销或两倍的I/O操作，数据以一个大的顺序块的形式写入doublewrite缓冲区，只需要对操作系统进行一次fsync()调用。
在MySQL 8.0.20之前，doublewrite buffer的存储区域位于InnoDB system表空间中。在MySQL 8.0.20中，doublewrite缓冲区的存储区域位于doublewrite文件中。
doublewrite缓冲区的配置提供了下列变量：
1. innodb_doublewrite
innodb_doublewrite变量控制doublewrite buffer是否启用，大多数情况下默认启用，要禁用doublewrite缓冲区，将innodb_doublewrite设置为OFF。
如果用户更关心性能而不是数据完整性，比如在执行基准测试时，可以考虑禁用doublewrite缓冲区。
-- 查询DoubleWriter-Buffer是否启用,默认开启,会加强一致性,但是会影响一定的性能 select @@innodb_doublewrite; -- ON2. innodb_doublewrite_dir
- innodb_doublewrite_dir变量(在MySQL 8.0.20中引入)定义了InnoDB创建doublewrite文件的目录。
- 如果不指定目录，则doublewrite文件创建在innodb_data_home_dir目录下，如果未指定，默认为data目录。
双写磁盘的位置配置
如果以上2个都没配置，则默认用的是数据目录：SELECT @@innodb_doublewrite_dir; SELECT @@innodb_data_home_dir;SELECT @@datadir;3. innodb_doublewrite_files
- innodb_doublewrite_files变量定义了doublewrite文件的数量。
- 默认情况下，为每个缓冲池实例创建两个doublewrite文件:一个flush list doublewrite file和一个LRU list doublewrite file。
- 至少有两个doublewrite文件。
- doublewrite文件的最大数量是缓冲池实例数量的两倍。(缓冲池实例的数量由innodb_buffer_pool_instances变量控制。)
-- 查询doubleWrite文件的数量 - 写几个备份,默认是2,至少两个备份 select @@innodb_doublewrite_files; -- 2
DoubleWrite双写过程
- 当脏页被写入到缓存池（也称为内存池）时，它们将被写入到 Doublewrite缓冲区中，而不是直接写入到磁盘上的表空间中。
- Doublewrite缓冲区是一个位于内存中的缓冲区，用于存储即将写入磁盘的脏页的数据。在写入到Doublewrite缓冲区后，数据将被写入到两个不同的磁盘区域，这样即使其中一个磁盘出现问题，数据也可以从另一个磁盘恢复。在写入到磁盘之前，数据会被缓存到磁盘的Write Cache中，确保数据能够快速写入磁盘。
- 在数据被写入磁盘之前，Doublewrite缓冲区中的每个脏页的LSN（日志序列号）都会被更新，以确保数据的一致性。这是因为LSN是一种用于恢复数据的唯一标识符，它可以确保在数据库出现故障时，数据可以恢复到一个一致的状态。
- 一旦数据被成功写入到磁盘上的两个不同区域并且LSN已经被更新，数据就被标记为干净页并从缓存池中移除。此时，这个脏页的数据已经被持久化到磁盘中，并且数据库可以确保数据的一致性和可靠性。
所以，Doublewrite机制会占用一部分内存和磁盘的空间，同时也会导致一定的性能损失，但这是为了保证数据的安全性和可靠性而进行的权衡。
场景：
- 既然你现在内存里面有些数据跟磁盘不一致，因此我们的数据是异步刷新到磁盘的，假如：内存里面的脏数据没有同步到磁盘的时候，断电了，此时内存里面的数据会发生丢失，此时我们要去解决，那么InnoDB怎么去保证数据的一致性与持久性呢？
解决办法：RedoLog - 重做日志 - 内存跟磁盘的数据一致性保证
- RedoLog又称作重做日志，当发生异常情况，内存没有同步到磁盘的数据丢失了，此时会基于RedoLog重做日志去恢复。
- RedoLog只需要去保证内存跟磁盘不一致的数据就可以了，不需要保存所有的数据，说白了就是去保证脏页的数据不会被丢失。
- 因为InnoDB的数据操作是只会实时去操作我们的bufferpool的page页的，然后通过其他的一些异步方式将bufferpool中的数据同步到磁盘，所以， 数据丢失是很容易产生的。那么就需要我们的RedoLog，它属于InnoDB存储引擎层面实现。
- RedoLog是当数据提交成功的时候，必须要保证成功的，如果RedoLog没有写成功，你这个事务它提交不了。
RedoLog格式
- 当操作数据时，会记录一条RedoLog日志，这个日志只是一条记录，记录的是在什么表空间、什么页对数据做了什么样的更改。
- 我们看下Redolog记录的大概结构：
- type：操作类型 - 插入、修改还是删除
- spaceId：表空间ID(磁盘)
- page number：所在的页
- data：修改的前后数据，修改前是什么，修改后是什么，做了哪些更改
- 当然还有一些其他的结构，但是也只是为了内存考虑，怎么更好的节省空间，所以我们可以进行忽略。
RedoLog File - 重做日志文件存储
- 既然RedoLog要去做恢复，那RedoLog也得去保存起来，不保存起来怎么做恢复。
innodb_redo_log_capacity - 重做日志容量(RedoLog容量)
-- 查询RedoLog File的文件大小 select @@innodb_redo_log_capacity; -- 104857600
- 我们去磁盘发现，InnoDB维护了32个大小为 104857600 / 32 = 3276800 的RedoLog File文件
RedoLog写入方式
假如真的RedoLog File - innodb_redo_log_capacity都没有落盘怎么办？假如你覆盖的数据真的还没有落盘怎么办？
思考题：为啥不直接实时数据同步到磁盘？
- 为了去保证数据不丢失，RedoLog每次还是需要跟磁盘同步，还是会有磁盘IO。
既然每一次都需要跟磁盘进行IO，为什么不直接去让page页跟磁盘IO呢，让page页去同步刷新呢？都是同步到磁盘，数据页的数据是同步到磁盘、RedoLog也是同步到磁盘，那么为什么不直接将page页同步到磁盘，而要一个RedoLog呢？
Log - Buffer：日志缓冲区
- RedoLog是为了保证数据一致性跟持久性的同时，性能得到保证，虽然RedoLog是顺序IO，并且占用空间比较小，但是毕竟是跟磁盘进IO还是很慢，所以先不要直接跟磁盘同步，因此作者又在内存中申请了一个LogBuffer区间，这个内存区间用来缓存我们的RedoLog，所以RedoLog不马上写到磁盘，而是先写到LogBuffer，先同步到内存里面，然后再从Log Buffer同步到磁盘。
- 为了性能的话，也不用实时去跟磁盘同步了；如果为了一致性，还是需要跟磁盘同步。
- 所以为了不同的场景，需要你自己选择。
- 所以不管怎样，先把RedoLog写到内存，至于你要不要实时跟磁盘同步，你来决定！
既然已经有一个Log buffer空间了，那么它什么时候同步到磁盘呢？
内存跟磁盘的数据交互 - 刷盘时机 - innodb_flush_log_at_trx_commit：控制日志缓冲区当中的数据刷新到磁盘时机
1. LogBuffer空间不足时
- LogBuffer是有大小限制的，可以通过innodb_log_buffer_size配置选项来进行设置日志缓冲区的大小，当内存不足时，会将LogBuffer数据刷新到磁盘！
-- 查询LogBuffer日志缓冲区的大小,默认大小是16M select @@innodb_log_buffer_size; -- 167772162. 事务提交时
3. 后台异步线程刷新
- 后台有一个专用的日志写入线程，用于将重做日志记录RedoLog从日志缓冲区LogBuffer写入系统缓冲区SystemBuffer，并将系统缓冲区刷新到重做日志文件RedoLog File，大约每秒都会刷新一Log Buffer中的RedoLog日志到磁盘。
- MySQL 8.0.22开始，你可以使用innodb_log_writer_threads变量来启用或禁用日志写线程。专用日志写入线程可以提高高并发系统的性能，但对于低并发系统，禁用专用日志写入线程可以提供更好的性能。
-- 查询是否启用日志写入线程,默认为1代表开启 select @@innodb_log_writer_threads; -- 14. 正常关闭服务器
5. checkPoint 检查点检查我哪些数据没有同步到磁盘、哪些没有同步的时候
Log Buffer跟磁盘的同步 & RedoLog同步机制 => redo log的持久化策略 => 日志刷新到磁盘时机
redo log持久化
- 我们知道了LogBuffer什么时候刷新到磁盘了，那么只要触发刷新到磁盘，就能百分比保证数据不丢失么？如果不丢失，那么性能是不是又很慢？
- 所以作者提供不同的同步方案，能够让用户在性能与数据安全性2个方面自己做取舍。
- 缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统缓冲区(OS Buffer)。因此，redo log buffer写入redo logfile实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到磁盘上的redo log file。
Redo Buffer持久化到磁盘上的redo log file的策略，可通过Innodb_flush_log_at_trx_commit设置：
- 官网：MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables
-- 查询RedoLog的同步方案(日志的刷写操作),该变量决定了RedoLog跟磁盘的刷新机制,默认设置为1 select @@innodb_flush_log_at_trx_commit; -- 1
- 1（实时写，实时刷）：默认设置为1是完全符合ACID要求的，每次事务提交时，都会将redo log buffer中的日志都会被写入 OS Buffer，并调用fsync（）刷新到磁盘(刷新到操作系统并且刷新到磁盘)中的redo log file中，数据安全性是最高的，性能也是最慢的，能够保证持久性。
- 0（延迟写）：设置为0时，事务提交时不会将 redo log buffer中的日志写入到 os buffer，而是日志每秒从内存写入到操作系统的os buffer中，并且 调用 fsync() - 刷新一次写入到磁盘中的 redo log file中。也就是说设置为0时大约每秒刷新写入到磁盘中的，当系统崩溃时，会丢失1秒钟的数据。没有刷新日志的事务可能会在MySQL服务崩溃时丢失。
- 2（实时写，延时刷）：如果设置为2，则在每次事务提交后仅写入到OS Buffer，并每秒调用一次fsync() 将os buffer中的日志刷新或写入到磁盘中的 redo log file，即最终刷盘交给操作系统操作，只要操作系统不挂，也能保持持久性，但是如果操作系统挂了，没有刷新日志的事务可能会在崩溃时丢失。性能是最高的，数据一致性是最低的。
- 对于设置0和2，不能100%保证每秒刷新一次。
禁用RedoLog / 禁用重做日志
- 从MySQL 8.0.21开始，你可以使用ALTER INSTANCE disable INNODB REDO_LOG语句来禁用重做日志。
- 此功能旨在将数据加载到新的MySQL实例中。
- 禁用重做日志通过避免重做日志写入和doublewrite缓冲来加速数据加载。
警告：
- 此功能仅用于将数据加载到新的MySQL实例中。不要在生产系统上禁用重做日志。
- 禁用重做日志记录时意外的服务器中断可能会导致数据丢失和实例损坏。
- 当重做日志被禁用时，在一个意外的服务器停止后试图重新启动服务器，会被拒绝，错误信息如下：
- [ERROR] [MY-013598] [InnoDB] Server was killed when Innodb Redo logging was disabled. Data files could be corrupt. You can try to restart the database with innodb_force_recovery=6
[错误][MY-013598] [InnoDB]服务器在InnoDB重做时被杀死
日志被禁用。数据文件可能损坏。你可以试试
使用innodb_force_recovery=6重启数据库
-- 查看RedoLog是否开启,默认为ON代表开启 show global status like 'Innodb_redo_log_enabled'; -- ON -- 禁用RedoLog,注意:在生成环境中禁止禁用RedoLog重做日志 ALTER INSTANCE disable INNODB REDO_LOG
补充：
- 有些变量描述涉及“启用”或“禁用”变量。
- 可以使用SET语句将这些变量设置为ON或1来启用它们，也可以将它们设置为OFF或0来禁用它们。
- 布尔变量在启动时可以设置为ON、TRUE、OFF和FALSE(不区分大小写)，也可以设置为1和0。
小结：
文章来源：https://www.toymoban.com/news/detail-729062.html
文章来源地址https://www.toymoban.com/news/detail-729062.html
- MySQL整体架构分为MySQL的Server层以及存储引擎层。
- Server层主要做管理客户端的连接，然后去拿到SQL语句进行解析，解析以后可能会有预处理，预处理以后知道了我要做什么以后，接下来要决定怎么去做，所以有一个优化器，优化器以后会有一个执行器，执行器是真正的跟存储引擎进行交互。
- InnoDB是MySQL默认的存储引擎，也只支持事务的，支持ACID的存储引擎，保存ACID中的C跟D，即一致性和持久性。
- RedoLog在事务提交的时候它能确保RedoLog是成功的。
- 刷脏的时候由于是异步的，所以它不能保证数据的持久性跟一致性，因为它可能会丢失，在内存中没有同步到磁盘可能会丢失，所以在事务提交之前会记录一个RedoLog。RedoLog它只要保证内存里面的数据没有同步到磁盘的数据能够回滚就可以了。
MySQL真的能保证数据不丢失吗？
- 保证不了的，如果 innodb_flush_log_at_trx_commit 系统变量设置的是0或2，那就保证不了；如果设置的是默认值1，那就可以保证数据不丢失。
- 请说一下InnoDB存储引擎的内存以及磁盘结构
- 请你说一下你对RedoLog的理解(存储同步等方式)
到了这里，关于MySQL架构 & InnoDB存储引擎的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
