MySQL数据库进阶知识大全-InnoDB存储引擎-索引B+Tree数据结构-sql调优-InnoDB底层原理深入-锁-MVCC机制-事务CAID底层实现

本文链接：https://blog.csdn.net/2301_77557950/article/details/142757249

无基础的小白码友们可以先看看基础篇笔记：

“MySQL基础大全学习笔记分享”=超级简单易懂-无废话-增删改查-约束-函数-多表查询-事务基础介绍-CSDN博客

MySQL数据库进阶

一、存储引擎

1.MySQL体系结构

连接层 -- 验证用户名密码、客户端的数据操作权限。

服务层 -- 包括SQL接口（封装DDL、DML等语句）、解析器、缓存等功能，跨存储引擎的操作都在服务层完成。

引擎层 -- 可插拔式存储引擎（MySQL5.5之后默认存储引擎为InnoDB），引擎控制数据存储与读取方式，服务器通过API与其交互。

存储层 -- 存储引擎把数据最终存入与磁盘当中，包含各种数据及日志文件。

！index索引是在存储引擎层进行实现的，不同引擎索引结构不同。

=========================================================================

2.存储引擎简介：

存储引擎就是存储数据、建立索引、更新\查询数据等技术的实现方式。

存储引擎是基于表的，不同表可以使用不同引擎，所以存储引擎也可被称为表类型。

-- 我们在建表时可以"create table 表(字段...)ENGINE = 引擎 [comment 注释]"

-- 来指定本张表使用的存储引擎，默认为InnoDB。

-- 使用语句 "show ENGINES;" 查看当前数据库支持的所有存储引擎。

-- 其中Engine表示存储引擎、Support是否支持该引擎、Comment简要介绍、

-- Transaction是否支持事务、XA是否支持XA协议、Savepoints是否支持临时存储点。

-- InnoDB支持事务、行级锁和外键；MyISAM为MySQL早期存储引擎；

-- MEMORY数据存储在内存中，通常做临时表及缓存（但是现在缓存都用Redis了。

=========================================================================

3.存储引擎特点：

1.InnoDB存储引擎：

兼顾高可靠性和高性能的存储引擎，在MySQL5.5后作为MySQL默认的存储引擎。

特点： a.DML操作遵循ACID模型，支持事务； b.行级锁，提高并发访问性能；

c. 支持外键 foreign key约束。

-- InnoDB引擎的每张表对应一个“xxx.ibd”表空间文件，存储其表结构、数据和索引。

-- 其中表结构原先为frm文件，在8.0后改为sdi，最终都被包含在了ibd文件之中。

-- 此外还有个系统参数：innodb_file_per_table，决定一个ibd对应多少表文件（MySQL8.0是默认开启的，每张表对应一个文件若关闭，则多张表对应同一个表空间文件）。

-- 在cmd窗口处使用 "ibd2sdi xxx.ibd" 即可从ibd文件中解析出表结构的json数据。

InnoDB的逻辑存储结构：表空间中包含很多段、段中包含很多区、区中包含很多页、

Page包含数据页和索引页、数据页中包含一行一行的数据、

而每行包含一些数据信息，及各个字段的具体数据。（详细的后面会补充）

=========================================================================

2.MyISAM存储引擎：

Mysql早期默认存储引擎: 不支持事务不支持外键；支持表锁不支持行锁；访问速度快。

对应磁盘文件包括：MYD数据文件、MYI索引文件、sdi表结构文件。

sdi文件可以直接打开，里面是表结构的json格式数据。

=========================================================================

3.Memory：

存储于内存当中，容易因硬件故障、断电等原因数据丢失。一般作为临时表或缓存使用。

内存存放访问速度快、支持Hash索引。其数据存放于内存，硬盘中只有sdi文件。

=========================================================================

面试可能会问：InnoDB、MyISAM的区别主要就是行级锁、事务及外键。

=========================================================================

4.存储引擎选择：

大多数场景使用InnoDB引擎即可；数据完整安全要求不高且在读操作插入操作较多时可以使用MyISAM，如日志、用户评论等；缓存或者临时表可以使用Memory。

但是一些noSQL系列的数据库可以完全上位的代替这些方案：

MyISAM被MongoDB替代、Memory被Redis替代。

=========================================================================

二、索引

我们在日常工作、开发环境等情况下我们一般使用的都是Linux版本的MySQL。

安装完成后，使用navicat远程连接Linux的MySQL即可。

（一、索引概述、

索引是帮助MySQL高效获取的数据的数据结构 (有序)。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，

这些数据结构以某种方式引用 (指向) 数据，

这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

=========================================================================

比如说根据where的条件检索时，如果不加索引会全表扫描一遍，效率非常低。

如果加上索引的话，例如把某个字段的数据映射到二叉查找树上，检索就非常快速了。

索引优势：1. 提高数据检索的效率，降低数据库的IO成本。

2. 通过索引列对数据的排序，降低数据排序的成本，降低CPU的消耗。

索引劣势：1. 索引列也是要占用空间的。

(但是这个空间消耗基本可以忽略因为磁盘空间一般是充足的、是不值钱的 )

2. 索引提高了查询效率，同时却也降低了更新表 (增删改) 的速度。

(在一般的业务系统中增删改的比例很小，主要还是查询)

由于 '磁盘空间充足' 及 '增删改比例一般很小' 故这两点劣势几乎可以忽略不计。

=========================================================================

（二、索引结构、

索引结构在存储引擎层实现，我们主要学习 B+Tree 和 Hash 索引，另外两种用得不多。

-- InnoDB和MyISAM和Memory都支持B+Tree索引，

-- 只有Memory支持Hash索引。只有MyISAM支持R-tree索引。

-- InnoDB在5.6版本之后跟MyISAM支持Full-text索引。

=========================================================================

对于二叉树或红黑树：将每个节点的访问都看做一次磁盘读入，将数据读入内存（节点是需要读入内存才能让CPU进行分析比较的）分析。

数据节点在n层则要访问n次，因此二叉树高度是与磁盘访问次数正相关的。

数据量越大，则二叉树越高，效率越低。

那么我们能不能通过给节点多些数据，让CPU每次多读入点数据来提高效率呢？

1.硬盘读取物理地址连续的多个字节和读取单个字节耗时几乎没有区别

2.访问节点是在硬盘上进行的，节点内数据操作是在内存中进行的

=========================================================================

B-Tree(多路平衡查找树):

有个概念：最大度数或者是阶，一个节点最多能挂多少子节点。每个key各自携带数据。

子节点指针需要key来判断放哪边，比key小往左边、大往右边，key最多为 '度数-1' 个。

插入数据时根据key的大小选择走哪个指针到下层，直到最下层，才把该key加入节点。

当添加数据后节点的key数量超过最大值时（我们或者是称之为上溢出下溢出则是非根结点至少要有[度数/2]的指针数量删除导致下溢出,需要找左右或者拿父来合并。），该节点会分裂为两部分。

然后把处于中间位置的key提升上去：

若有上层则key替换掉上层的指针的位置；若没有上层则该key自己作为一个节点。

此时该key左右的结点指针，即为原来分裂成两部分形成的新节点。

（查找到最后遇到的null值指针可以看做是逻辑上的失败节点（类似于红黑树的Nil节点），遇到失败节点表示未找到

=========================================================================

-- 二叉树红黑树：数据过多的话往下延展，造成不平衡的话还需要你进行旋转。

-- B树：若某边数据过多，向上请求，将本节点分裂为左右两边，向左右延展。

数据一直增加的话，则一直向上请求，每次都把下层分为两半，自动保持平衡。

=========================================================================

B树的进化版：B+树: 参考这里

1.B树在查找时是按树的结构查找性能很好，但对于顺序遍历只能时只能中序遍历，

而中序遍历对于一个节点来说，需先进入下层遍历再回来，不断往复，把节点的所有指针

和数据都读取完毕。每个节点都要反复经过多次，效率低。

2.此外B+树非叶子节点不存储数据，能存储的索引更多降低树高，减少磁盘IO。

3.而且B+树数据都位于叶子节点，所有数据的查找时间均等，不像B树时间随深度变化。

=========================================================================

-- 而B+树节点内，只在叶子结点保存数据。且在上升时保留中间节点的值。

-- 其中非叶子节点只负责索引到下一层的节点。

-- 而叶子节点保存数据，并使用指针连接在一起，形成链表。

B+树不仅 "随机查找"、"顺序遍历" 效率高，B+树还支持 "范围查找"。

随机查找：通过root指针以类似于二叉查找树的方式进行查找。

顺序遍历：通过head直接顺序访问叶子节点。

范围查找：先依据最小值随机查找到叶子节点，再根据链表结构往后获取直到最大值。

对于B+树MySQL数据库还对其进行了优化，把单向链表换为双向链表，方便逆序遍历

=========================================================================

B+树的存储原理深度：参考这里

磁盘上存储的最小单位，由扇区和磁道共同确定（在操作系统中，当从磁盘读取数据到内存时，数据会按扇区为单位从磁盘读取，然后可能被缓存到内存中，这时候数据会被组织成页的形式进行管理。但是，扇区和页是两个不同层级的概念，它们在存储设备和内存管理中分别发挥作用。），一般为521B或者1024B。

每行数据占的空间越大，一个磁盘单位包含的数据就越少，检索数据磁盘IO就需要越多。

所以我们使用仅包含关键字与数据地址条目建立索引表，可以减少检索时磁盘IO。

索引表占的磁盘单位也很多时，还可以进一步建立二级索引表。

把这个索引表翻转一下其实就是B+树的结构：(部分索引结构叶子节点直接存数据

行格式为Compact，数据行存在record_type记录类型: 0普通数据、2最小值、3最大值

行和行之间逻辑相邻故用next_record记录下一行数据的地址。页与页之间页逻辑联系。

=========================================================================

hash索引：memory

直接或者间接通过Hash算法计算出数据访问的地址，一步即可查询出需要的数据。

出现hash碰撞的话，就挂在后面形成链表。

效率高，但只支持等值查找，没法进行排序或者范围查询。

（InnoDB中具有自适应功能，hash索引是引擎根据B+Tree索引在指定条件下自动构建的

=========================================================================

（三、索引分类、

-- 当你给字段添加唯一约束时，会自动为该字段创建个唯一索引。

-- 二级索引又称之为 "辅助索引" 或者是 "非聚集索引" 。

-- 聚集即B+树的叶子结点，直接保存数据，而不是保存地址值。

=========================================================================

-- 如果存在主键，则主键索引就是聚集索引。

-- 如果一张表没有主键，则会使用第一个唯一索引作为聚集索引。

-- 若没有主键于唯一索引，则InnoDB自动生成一个rowid作为隐藏的聚集索引。

-- 在二级索引处的查找最后是保存了聚集索引键值，接着才使用该值去访问聚集索引。

-- 所有二级索引的查询最终都会回到聚集索引处，聚集索引键值决定物理行结构顺序。

-- 先访问二级索引再访问聚集索引的这种方式我们称之为 "回表查询" 。

-- 此外，二级索引中还有联合索引，即在挂的关键字下面再挂一个其他字段的关键字。

-- 索引按第一个字段的关键字排序，能提高查询速度，

-- 但第二个字段只在第一个字段重复时起作用，按第二个字段查询时该联合索引无用。

-- 除非你同时包含第一个字段，如果你查询的字段仅有这两甚至无需进行回表操作。

=========================================================================

-- 为什么聚集索引使用挂数据的形式？

-- 不用多访问一次磁盘读取数据。

-- 那为什么二级索引不一样使用挂数据的形式呢？

-- 二级索引只能挂地址，若二级索引挂数据，不仅是数据的重复存储的问题，

-- 在数据更新时，所有二级索引的数据都要更新，且还要求得数据一致性。

-- 那为什么二级索引不使用挂数据地址的形式而是再走一遍聚集索引呢？

-- 聚集索引的数据地址是会发生变动的，如页分裂页合并等情况。

-- 此时如果保存的是聚集索引关键字，那么就不需要把每个二级索引都更新维护了。

-- 为什么要求聚集索引唯一？

-- 而且二级索引指向某聚集索引时，该索引对应两条数据行，就无法确定是哪个了。

-- 聚集索引和二级索引哪个增删改速度快？

-- 二级索引更快，聚集索引不仅需要把整个数据都移动修改，还需更新全部二级索引。

-- 而二级索引更新时仅需移动聚集索引的关键字，而且不用动不相关索引。

-- 为了保证效率最好还是给主键设置个auto_incerment，让它默认自增即可。

-- 聚集索引和二级索引哪个范围查询速度快？

-- 聚集索引决定物理位置，所以按聚集索引排序和范围查询物理位置连续，顺序IO更加快速。

-- 二级索引，顺序IO其叶子结点后，若无法索引覆盖，还需要多次回表查询，效率低。

=========================================================================

计算InnoDB主键索引的B+Tree高度：

一个数据页的大小固定为16K，

我们用一行数据的大小可估算出一页能存储多少行数据。

而再通过关键字与InnoDB指针的大小(一般占用6B)，可估算出索引页能包含多少指针。

高度为2的B+Tree能存放= 索引页指针数 * 数据行数的数据。

高度为3的B+Tree能存放= 索引页指针数 * 数索引页指针 * 数数据行数的数据。

高度为n的B+Tree能存放= (索引页指针数)^(n-1) * 数据行数的数据。

=========================================================================

（四、索引语法、

1.创建索引（或者可以在创建表的字段部分加上index 表(关联的字段)来创建索引）:

"create [unique | fulltext] index 索引名 on 表名(索引关联字段...);"

-- 一个索引关联单个字段--单列索引、一个索引关联多个字段--联合索引 (组合索引)。

-- 多个关联字段之间使用逗号隔开，各个字段的顺序不同有不同效果。

-- create index idx_user_name on tb_user(name);

-- create unique index idx_user_phone on tb_user(phone);

-- create index idx_user_pro_age_sta on tb_user(profession,age,status);

=========================================================================

2.查看索引: "show index from 表名;"

-- 上面的创建索引语句执行完毕之后，查询效果如下：其中主键索引默认已经存在。

=========================================================================

3.删除索引: "drop index 索引名 on 表名;"

-- drop index idx_user_phone on tb_user;

=========================================================================

（五、SQL性能分析、

-- sql的优化主要是针对查询操作，其中索引优化占据sql优化主要地位。

状态变量

show [session|global] status; 查询【当前会话|全局】的状态变量

show status like '变量名'; 模糊匹配系统状态变量

flush status；重置状态变量，重新开始统计系统的信息

系统变量

show [session|global] variables；查询所有系统变量

show [session|global] variables like '变量名'；模糊匹配系统变量

select [@@][session|global] '变量名'；精确查询系统变量

set [session|global] '变量名' = '值'；设置修改系统变量的值

但是系统变量的值的修改在数据库重启之后会重置的，需要永久修改的话需要修改配置文件的内容

对于Liunx系统而言，其配置文件位于 /etc/my.cnf 处。

1. sql执行频率，判断当前数据库中是增删改占比较高，还是查询占比较高。

-- 使用" show global status like 'Com_ _ _ _ _ _ _' "查看当前数据库增删改查次数。

-- 7个下划线为模糊匹配 Com_insert、Com_delete、Com_select 等信息都能查到。

=========================================================================

2. 慢查询日志：记录所有执行时间超过指定参数的sql语句的日志。

-- 确定了数据库业务以查询为主之后，还需要确定哪些sql语句较慢、需要优化。

-- mysql的慢查询日志默认关闭，需要在mysql的配置文件(/etc/my.cnf)中配置（到Linux对应的文件夹中使用Vim来编辑）：

-- 使用 slow_query_log =1 开启慢查询日志、

-- 使用 long_query_time = 2 设置查询时间，单位为秒，超过该时间视作慢查询、

-- 此外 slow_query_log_file = ... 指定慢查询日志文件存放位置。

-- 配置完成需要重启服务器，在/var/lib/mysql/localhost-slow.log查看慢查询日志（使用 "cat 文件名" 直接查看文件内容或者使用 "tail -f 文件名" 来实时跟踪文件）。

-- 在日志文件中会记录查询的用户、主机、数据库及时间和查询语句等信息：

=========================================================================

3. profile：查看语句各阶段的执行耗时及cpu成本。

-- 通过"select @@have_profiling"判断mysql是否支持profile：

-- 若支持，则再通过"set profiling = 1;" 开启profile操作；

-- 最后我们就可以使用show profile查看sql语句执行耗时，及耗时主要耗费在哪里。

-- "show profiles;"查询每条sql耗时及id。

-- "show profile [cpu] for query query_id;"根据id查询语句各个阶段耗时及cpu情况

=========================================================================

4. explain执行计划：抛去时间，真正评判sql性能的工具。

-- 真正要看sql语句的性能，我们需要使用explain，在性能判断中有非常重要的地位。

-- 我们直接在查询语句前加上explain指令或describe指令即可，效果一致。

-- Explain不考虑各种Cache、Explain不显示mysql执行查询所做的优化操作、

-- Explain不显示自定义函数触发器存储过程等对查询的影响情况、

-- Explain部分统计信息是估算的，并非精确值。

-- 即可语句的获取执行计划列表：

=========================================================================

table：获取到的执行计划每条记录都对应一个表，表示该次执行获取的数据是哪个表

的字段，例如多表查询，两条记录不同table。此外union之类临时表也会算入。

id：一个select对应一个id，相同id在前面先执行、不同id大的先执行。

（相同id位于前面的那个table我们称之为’驱动表‘ 其后面的我们称之为’被驱动表‘ 有时优化器会对调语句中表位置，以更换驱动表进而提高效率）

每个id表示一趟独立的查询，一个sql的查询越少越好。

数据库对于部分子查询语句有优化，把子查询语句优化为多表联查(减少独立查询的趟数)。

如select * from s1 where key1 in(select key1 from s2) or key3='a'

这种就不会优化但去掉or后面的条件就可以优化为s1 s2外查询

union的去重操作会建立一个临时表，此时该表的id列为null。

select_type：为每个select代表的小查询（即每个id）定义，表示其在大查询中起到什么作用。

'simple（不含union、子查询) '、

'primary

（union或子查询的左一表 (对于union来说就是左边那张表对于子查询来说就是外部表)) '

'union (union除了左一表以外的表)'、'union result (union去重时创建的临时表)'、

'subquery (子查询中除了主表的表 (非相关) 记得考虑优化为多表连接的情况)'、

'dependent subquery

(子查询非左一且为相关查询相关查询即为子查询中包含大查询的字段

需要考虑 IN-->EXISTS 的情况：如 WHERE c_id IN (SELECT c_id FROM o);

优化为 WHERE EXISTS (SELECT 1 FROM o WHERE o.c_id = c.c_id); 的情况

只要c表有关联o表的字段，则返回，in是全找到、exists是找到则停止)'、

'dependent union (子查询中的union除左一外其他查询都相关到外面的字段的话，

使用该类型也要考虑in优化为exists的情况 )'、

'derived (表子查询被当作表来使用我们称作派生表，使用该类型)'、

'materialized

(派生表非持久化，而物化表是持久化的。是查询结果的物理副本，可重复使用。

部分情况优化器会进行物化，防止多次查询性能降低。

而物化的表是从哪个表中读取出来的，则哪个表类型为此。 )'。

partitions：匹配的分区信息，不涉及分区的话直接为null。

type：代表一条记录对某个表执行查询时的访问方法，又称访问类型。性能从高到低为

system(对于统计精确的引擎，即存在变量记录数据行数如Myisam或者Memory。进行单行数据查询，则是用system方法。生产环境下一般达不到)、

const(使用主键或者唯一的二级索引与常数进行等值匹配时，对单表的访问方法就是const。)、

eq_ref

(多表连接时，被驱动表通过主键或者唯一索引与驱动表某个字段进行等值连接时，

该被驱动表使用的就是eq_ref方式。

(驱动表依次获取每条数据与被驱动表比较、于被驱动表而言也相当于常量。

ref (使用普通二级索引与常量进行等值匹配的话使用的就是ref。

（但是常量要求与字段同类型，

（否则调用函数类型转换，索引失效

（联合索引的话需要出现的字段都进行等值匹配

fulltext、全文索引

ref_or_null(在ref普通二级索引进行常量等值匹配的同时在允许字段为空，即OR key IS NULL的话使用的是ref_or_null方式。 )、

index_merge(若优化器判定语句后进行了索引合并即对于用or连接的两个带索引字段的查询来说会各自按索引查聚集索引key，再一同回表查询此时我们使用index_merge表示。（and不会合并而只按照单索引进行查询，（所以对于and的话推荐使用联合索引)、

unique_subquery(包含IN子查询的语句优化器将IN优化为EXISTS时若子查询可以使用到主键等值匹配的话使用unique_subquery.)、

index_subquery、

range (使用索引对固定区间范围的查询如IN、大于、小于等，使用range方式.)、

index (查询涉及字段都在同一联合索引上，即索引覆盖但是又需要全表扫描，则使用index. （即不能通过首个字段来利用索引来优化查询

（相当于是在少了些字段的表中全表扫描

（有覆盖且利用到了索引则为ref、

ALL (无索引的全表扫描为ALL)。

精确单行: system、主键唯一常量等值: const、多表被驱动主键唯一等值: eq_ref

其余二级索引常量等值: ref、ref再或者null: ref_or_null、索引合并: index_merge

优化为exists情况下主键等值unique_subquery、范围查询range、

索引覆盖但没利用到索引index、索引不参与的全表扫描: ALL

possible_keys：可能用到的索引，查询语句涉及该索引则都会列出。

key：真正使用到的索引是哪个，查询优化器会从可能的索引（预选索引并不是越多越好因为优化器筛选索引的步骤也有消耗）中挑选适合的索引（也不一定是从可能的索引中比方说优化器对语句进行优化使其可能用到的索引又多出来一些）。

key_len：实际上使用到的索引长度为多少(字节数)，可以检查是否充分利用到索引。

主要是针对于联合索引，用到多少字段则该值为这些字段的关键字长度...

关键字长度受数据本身长度（对于char类型 utf-8一个字符占3个字节 gbk一个字符占2个字节）、数据为空标记（大小为1个字节）、varchar实际长度标记（大小为2个字节）等影响，

同一语句通过优化使该值越大越好、不同索引利用长度间使用逗号隔开。

ref：表示等值匹配时匹配对象的信息，如ref方式ref的值为const，const表示常量。

又比如eq_ref的方式ref的值为 "库.驱动表.驱动表字段 "，表示与该字段等值匹配。

若匹配的字段被函数包裹的话，ref的值则为func等。

rows：预估要读取的条目数，该数越少越好。rows是根据索引和查询条件得出的结果，

如果查询条件包含非索引列，那么filtered值就会低于100.

filtered：经过搜索条件后，剩余条目数占预估条目数的百分比，越高越好。

对于单表（对于单表而言这个值其实没有什么意义我们更需要关注连接查询）: 就是 ‘真正要找到的条目’ 在 ‘利用到索引检索到的条目’ 中占比多少。

对于连接查询：驱动表中该值决定了被驱动表要执行的次数 rows * filtered

驱动表将其全表扫描并且满足条件筛选的数据一个个交给被驱动表查询。

被驱动表可以把驱动表的字段作为常量看待，条目中驱动表只表示一次执行（具体执行次数由驱动表rows和filtered决定）。

Extra：说明一些额外的信息：

Using index(使用了索引覆盖)、Using where(存在无索引条件)、

Using index condition(索引未覆盖但有利用到)、

使用了索引筛选条件但是需要回表查询。

通过在索引层就尽量把可判断条件筛选完成以减少回表后，数据查找与条件筛选。

Mysql把该改进称之为"索引条件推送". 即尽量把条件推送到先执行的索引层。

Using filesort(无索引字段排序（文件少时可以全部放入内存排序，文件过多的情况下甚至需在磁盘中排序我们称这种方式为"文件排序"。）)、

Using temporary(建立内部临时表（例如distinct、group by等临时表的创建成本很大，推荐使用索引来优化如给分组字段、查询字段建立索引将Using temporary转化为Using index ）)、

Using [sort_]union(索引1,索引2...)(表示发生了索引合并)、

Zero limit(分页0条数据)、Not exists(一旦找到被驱动表所有匹配的行，就不再继续搜索。如where要求被驱动表字段为空而读取一行即发现其字段为非空。)、

Impossible WHERE(不可能的过滤条件)、No tables used(没有任何表被使用)、

Using join buffer(hash join)(基于块的嵌套循环算法（连接查询被驱动表不能有效利用索引或无索引， mysql则会为其分配一块叫'join buffer'的内存块来加快查询速度）)、

No matching min/max row(无满足条件的列可进行min或max聚合)、

select tables optimized away(优化器确定只返回一行，通常发生在聚合函数（只有一行数据，max、min聚合没有意义直接通过索引查找到对应数据行即可结束查询）)、

=========================================================================

-- (a.) 此外Explain还能有四种输出格式：

-- 普通模式、json模式、Tree模式及使用软件的可视化格式。

-- 在explain和查询语句之间加上’format=JSON‘或‘format=Tree’即可。

-- json格式多了新字段：成本'query_cost'(总体上查询的成本)、'prefix_cost'(对于驱动表该值为read_cost与eval_cost相加： read_cost包含io成本与被筛选掉数据cpu成本； eval_cost就是未被筛选的数据的检测成本。而对于被驱动表该值还需要多加上驱动表该值；其read_cost与eval_cost是多次查询累计的。)、等。

-- (b.) 使用explain语句查看执行计划后，紧接着还可执行"SHOW WARNINGS\G(Linux的指令 \G是纵向展示数据的意思)"

-- 用来查看语句执行计划的一些拓展信息，其中当Code为1003时，

-- Message展示的信息就类似查询优化器帮我们重写后的语句。

=========================================================================

5.trace追踪优化器执行计划：列出MySQL对各索引的性能评分、优化操作等信息。

（六、索引使用规则、

1.最左前缀法则

-- 如果索引关联多列即 ’联合索引‘，要遵守最左前缀法则（这个法则只与索引中列的顺序有关而与查询语句中列出现的顺序无关）。

-- 最左前缀法则：查询从索引的最左列开始，不能跳过索引中的列。

-- 跳过了最左列则不使用该索引。若跳过了其他列，则该列其后面的列索引失效。

-- 若使用联合索引进行范围查询，如大于小于，则其右侧的索引也会失效。

-- 但是可以通过大于改为大于等于来规避索引部分失效的情况。

=========================================================================

2.索引失效

-- 在索引列上进行函数运算操作（例如说subString取一部分会直接全表对所有数据进行函数操作）可能使索引失效。

-- 字符串不加单引号，自动进行隐式类型转换（如果省略单引号， MySQL会全表扫描把索引的每个数值都转换类型。），索引失效。

-- 使用范围查询可能会使MySQL评分认为需要读取大量数据，故而使最左前缀法则失效。

-- 仅仅尾部进行模糊匹配索引不失效、如果头部进行模糊匹配则索引失效（字符串从头开始比较如果头部不确定的话不方便查找）。

-- 在or条件连接时，若存在一边无索引可能不会使用索引（相当于你走完索引，还得对数据进行判断此时mysql就会判断是直接全表扫描快还是走索引，从索引获取的字段中扫描快），而在两边都有时使用（触发index_merge索引合并）。

-- mysql如果评估使用索引比全表扫描更慢（一般出现在字段的条件涵盖大部分数据的情况。此时按索引把大部分数据找出来不如直接全部扫描一遍），则不使用索引。取决于表中数据的分布。

=========================================================================

（前面两个小点为通过where条件的变动，尽量避免索引失效的情况。

-- SQL提示：mysql有时使用的索引不是我们想要的，则我们可以使用sql提示来更改。

-- 在查询语句表名的后面加上"use index(索引名)"表示建议mysql使用该索引、

-- 加上"ignore index(索引名)"表示让mysql排除某个索引、

-- 加上"force index(索引名)"表示让mysql强制使用某个索引。

（后面的索引覆盖，则是通过select后字段，来优化查询

=========================================================================

3.覆盖索引

-- 在我们的查询中建议使用覆盖索引（（查询使用了索引，并且需要返回的列，（在该索引中已经全部能够找到。），减少select * 这样的查询。

-- 索引条件推送：在需要回表操作时，把条件近多的推送给索引层执行，以提高效率

-- 其中using index condition消息就表示使用了索引条件推送。

=========================================================================

4.前缀索引（查询索引时Sub_part字段为前缀长度）

-- 当字段类型为字符串(varchar、text)时，有时索引为很长的字符串，浪费磁盘IO。

-- 此时可只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，提高效率。

-- 语法为"create index 索引 on 表(字段(前缀))"，来指定索引的字符串前缀长度。

-- 对于前缀长度的选择，尽量到达能通过索引的前缀来区分所有不同数据即可：

-- 我们使用"select count(distinct substring(email,1,5)) / count(*) from tb_user"

-- 来查询email字段取前缀长度为5时的索引选择性（即使整个字段都截取，也可能会因为数据重复而达不到1. 唯一字段的索引选择性为1。），该值越接近1越好。

-- （对于叶子结点而言，其索引仍然不是完整值，故无法实现索引覆盖

-- （在查询到索引值相同后，回表后为了避免前缀外字符未匹配（尽管只有一个索引你也不能确保该索引后面部分能够完全匹配上条件），还需要额外一步判断

-- （对效率影响还是挺大的，在字段数据特别大情况下才考虑建立前缀索引

=========================================================================

5.单列联合索引

-- 在业务场景中若存在多个查询条件(and)(如果为or的话直接索引合并 index_merge)，考虑针对于查询字段建立索引时

-- 建议建立联合索引，而非单列索引.

-- 建立联合索引的话，推荐越常用的索引放越左边。

=========================================================================

（七、索引设计原则

-- 数据量较大，指的是百万级别数据。

-- 区分度不高，如性别、状态、逻辑删除的字段，建立索引了效率也不高。

三、SQL语句调优

1.插入数据

a. 小批量数据的插入（不到百万级别）：

（1）执行批量操作（建立连接就一次性插入完成，建议500到1000条，超过则分多次）

（2）手动事务提交（防止频繁的事务开启与提交，在全部语句插入完成后一次性提交）

（3）主键顺序插入（顺序插入主键，对于聚集索引结构方便调整）

b. 超大批量数据插入，推荐使用load指令，把整个本地磁盘文件直接插入数据库：

（1）客户端连接服务器时："mysql --local-infile -u root -p"

（2）开启本地加载文件开关："set global local_infile = 1;"

（3）执行load指令将准备好的数据，加载到表结构中：(主键顺序还是高于乱序插入)

"load data local infile '/.../xxx.sql(文件路径)' into table '表名'

fields terminated by ',(分隔符)' lines terminated by '\n(换行符)' "

=========================================================================

2.主键优化

-- 在InnoDB存储引擎中，表数据是根据主键顺序组织存放的，

-- 这种存储方式的表我们称为索引组织表(index organized table IOT)。

-- 其中一个页的大小为16KB(一般为磁盘块的整数倍现在磁盘块主流大小为4KB)，一个区的大小为1M即可存储64个页。

页不同于B+树结点，其度非不明确，在页中数据过多时才表示度过大。
页分裂：且新插入数据能填充到已存在页的空闲空间中，而不需要将数据页分裂成新的页。除非数据确实要插入到已满的页中。
页合并：对于删除下溢出的情况，直接合并左右数据页，左右数据页多出来的部分直接保留在原来的空间中，也不用考虑该情况下的下溢出问题。

在插入数据时触发的 “页分裂”：插入数据超过页上限16K时

在删除数据时触发的 “页合并”：删除数据导致低于合并页阈值50%时

-- 记录在删除时并不是真的清空，而是打上删除标记，等待被其他记录声明、覆盖。

-- MERGE_THRESHOLD：合并页的阈值，可以在创建表或者索引时自己设置。

故主键的设计原则：

-- 1.尽可能降低主键的长度 (减少二级索引空间，磁盘io）

-- 2.插入主键建议顺序插入 (可以减少页分裂现象）

-- 3.故不推荐UUID或者其他自然主键，如身份证号作为主键，推荐auto_incerment

-- 4.业务操作时尽量避免对主键的修改 (结构变动代价大）

=========================================================================

3.order by优化

-- 单个或多个字段可使用联合索引排序，需保证其排序字段顺序与索引顺序一致。

-- 通过在索引字段后面 "空格再加asc或者desc(查询索引时Collection字段为A则表示升序排、D表示为倒序排)" ，即可设置索引排序规则。

多个字段同时升序同时降序，需要索引同时升序或者同时降序。
多个字段一个升序一个降序，需要索引一个升序、一个降序。
若查询字段无法索引覆盖的话，可能导致效率低，mysql直接优化为fileSort方式。

-- 若无法避免fileSort，大量数据排序，可以适当增大sort_buffer_size(默认256K）

-- 表示排序缓冲区的大小，若占满则会在磁盘文件中进行排序，效率很低。

=========================================================================

4.group by优化

执行分组操作时，如果无法直接通过索引来完成分组，就可能需要使用临时表来辅助操作。

无索引：因为不知道何时能统计完相同分组的数据，所以只能全表扫描，在遇到同组字段时对临时表（就跟union的临时表类似不知道是否还有重复数据只能先把数据先存于临时表中）中的属性进行聚合、累加等操作。
有索引：同一分组数据相邻，统计完直接就下一行数据，不用担心还有同分组数据未统计，所以不需要使用临时表。

使用临时表信息为'Using Temporsry'、使用索引时信息为'Using index'(索引覆盖时也是这个信息比如说count函数就不需要回表也能统计)。

使用联合索引进行分组时需要保证最左前缀法则，否则索引不会用于分组，产生临时表。

（其中左边的索引用于等值匹配，右边用于分组时也能用到索引（因为左边确定了某值则右边的字段即为递增或者减一样不用临时表（其实也是最左前缀法则）。

=========================================================================

5.limit优化

limit的排序是把‘从索引0到指定分页范围内的数据’全部获取出来，然后仅返回分页范围内数据

效率不高，一般使用覆盖索引加子查询的方式优化。

覆盖索引：把查询的字段从 * 改为主键id ，这样不需要查询数据行能加快limit速度。
子查询：接着再拿查询到的id来匹配数据行，但是mysql不允许等值匹配limit分页子查询
所以我们采用把limit获取的id作为派生表，多表连接该表即可。

由于效率依然缓慢，在业务中一般会限制分页的数量，

如果需要查询日期较晚的数据一般是提供按日期进行查询数据的方式。

=========================================================================

6.count优化

myISAM引擎把一个表的总行数记录在磁盘中，无where时count(*)直接返回，效率高。
InnoDB引擎不管有没有where，都是把数据一条一条读取出来，然后累积计数。

不同形式count函数的效率：count(字段)(统计的是对应字段非null数量相比于主键可能多一步判空) < count(主键) < count(*)(InnoDB对这种统计进行了优化不取数据行，直接进行累加) = count(1)

所以在count统计数据时推荐使用count(*)或者count(1)

其他优化思路：通过某些内存级别如Redis缓存来自己计数，不过比较繁琐

=========================================================================

7.update优化

更新操作时，尽量使用索引字段进行更新，如果用非索引字段会导致行锁升级为表锁。

行锁：并发事务修改被锁定的行会阻塞等待其他事务释放锁。并发性能较高。
表锁：整张表的修改都会被锁定阻塞，因为不使用索引是进行全表遍历的。

8.其余注意点

select尽量不使用*号，一是减少sql解析通配符的工作、二是简单易看
连接查询小表驱动大表（驱动表决定被驱动表全表扫描次数还有驱动表本身数据读取次数故优先选择小表作为驱动表），有索引表优先被驱动（被驱动表为全表扫描尽量选取有索引表作为被驱动表提高效率）。驱动表的数据进入joinBuffer，然后再拿被驱动表的数据与其进行逐个对比，小表可减少读入缓冲区次数。(无索引全表情况BNL)
连接查询取代子查询，非绝对，要看是否能更有效地利用到更适合的索引。
多个查询语句建议使用union all而不是union，降低去重操作的cpu、临时表耗费。
join不宜过多，关联表进行分库或者拆分时耦合性高，可能需要去变动join的sql语句。

四、视图\存储过程\触发器

数据库常见对象统计：

（一、视图

-- 例如说对于某用户，期待其只能获取更改表中部分字段，而对敏感字段信息无法访问

-- 创建子表的话浪费空间，且不好维护数据的一致性，则可使用视图来代替该表。

-- create子表的语句中使用view替换table，然后仅分配该视图权限给对应用户即可。

-- 即"CREATE VIEW 视图名 [(字段列表)] AS 查询语句"。

-- 视图直接映射到数据的物理地址上的，原本数据所在的表叫做基表。

-- 视图是逻辑上的表，可理解为存储起来的SELECT语句，视图本身的删除不影响基表。

=========================================================================

-- 完整的视图创建SQL语句为：

"CREATE [OR REPLACE(确保视图已存在的情况下新建视图能将原来的视图覆盖)] VIEW 视图名（视图我们使用vu作为简写） [(字段列表)] AS 查询语句

[WITH 【CASCADED | LOCAL】 CHECK OPTION];"

-- 查询语句中的别名或者通过创建时的字段列表可以决定视图字段名。

-- 此外查询语句的主体不一定是表格，也可以基于视图作为查询语句来创建视图。

=========================================================================

-- 使用"SHOW TABLES;"在查看全部表的同时，同时也展示所有视图信息。

-- 使用"DESC 视图名;"查看某个视图的结构。

-- 使用"SHOW CREATE VIEW 视图名;"查看视图的创建语句。

-- 使用"SELECT * FROM 视图名...;"查看视图内字段数据信息。

=========================================================================

-- 对于视图的字段的增删改直接把视图作为表来使用即可：

-- 即使用DELETE、UPDATE、INSERT关键字来操作。

-- 但是这增删改操作无法对视图基表中不存在的字段 (如聚合函数) 进行更新。

-- 需要保证视图与基表中的字段保持 '一对一' 的关系，才支持增删改。

-- 所以说视图作为虚拟的表主要是用来查询用的，还是不建议拿来对字段增删改。

=========================================================================

-- "create or replace view 视图 as 查询语句[with...];"通过创建时覆盖来修改视图。

-- "alter view 视图 as 查询语句[with...];"通过alter关键字直接修改视图。

-- 而删除视图则使用"drop view if exists 视图1,视图2...;"。

-- 需要注意的是若视图基于视图创建，则删除了其依赖的视图则本视图也无法使用。

=========================================================================

-- 视图优点：1.操作简单、2.减少数据冗余、3.数据安全具备隔离性、

4.适应多变需求, 无需改动基表结构、5.分解复杂的查询逻辑

-- 视图不足：视图基于表，表结构变动时需要及时修改视图，维护成本高。

一般小型项目直接使用表即可，对于大型项目再考虑视图。

=========================================================================

check option检查设置选项：更改视图的数据（如插入、更新、删除操作）时，如何对数据进行符合视图定义的检查操作（如定义视图时要求age<30，则不允许>=30的数据插入）。

不加检查选项：不检查当前视图的定义条件。如果该视图依赖其他视图的话，还需要看其他视图是否有加检查选项，有加的话则需要按其规则来。
检查选项选择cascade级联：检查当前视图的定义条件。如果该视图依赖其他视图的话，不管其他视图是否有加检查选项，都需要检查其定义条件。
检查选项选择local：检查当前视图的定义条件。如果该视图依赖其他视图的话，还需要看其他视图是否有加检查选项，有加的话则需要按其规则来。

（二、存储过程与存储函数

存储过程与存储函数的最大区别就是过程可以没有返回值、函数必需要返回值。

存储过程为预先编译的sql语句的封装。存储过程存储在mysql服务器上客户端需要调用时，向服务器发送指令即可。简化操作、避免操作失误、减少网络传输、减少sql语句暴露的风险。

特点：1.封装复用、2.可接收参数也可返回数据、3.减少网络交互, 效率提高

存储过程基本语法：

使用"CREATE PROCEDURE 存储过程名([参数列表])BEGIN SQL语句 END；"创建存储过程。

--对于存储体的语句MySQL执行到分号即结束了，故需要我们临时改变结束符号：

--创建存储过程前调用"DELIMITER $"，创建完毕END后加上"$"表示结束。

--在创建完毕后再使用"DELIMITER ; "，将结束符改回分号即可。( $符可换为其他
使用"CALL 存储过程名(参数列表);"调用存储过程。
使用"SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='XXX'；"查询指定数据库的存储过程及状态信息。
使用"SHOW CREATE PROCEDURE 存储过程名称；"查询某个存储过程的定义。
使用"DROP PROCEDURE [IF EXISTS] 存储过程名称；"删除存储过程。

=========================================================================

变量的使用：系统变量、用户自定义变量、局部变量

系统变量分为全局变量(GLOBAL)和会话变量(SESSION)，MySQL提供。

系统变量查看及操作语法：（不指定默认为session级别）
- SHOW [SESSION | GLOBAL] VARIABLES ; 查看所有系统变量
- SHOW [SESSION | GLOBAL] VARIABLES LIKE '......' ; 模糊匹配系统变量
- SELECT @@[SESSION | GLOBAL] 系统变量名 ; 查看指定系统变量的值
- SET [SESSION | GLOBAL] 系统变量名 = 值 ; 设置系统变量的值
- SET @@[SESSION | GLOBAL] 系统变量名 = 值 ; 设置系统变量的值
数据库重启全局参数也会失效，要想永久有效的话可在etc/my.cnf配置文件处修改
用户自定义变量即用户自己定义的变量，使用"@变量名"获取，作用域为当前连接。

用户自定义变量的查看及操作语法：
- SET @变量名 = 值 [,@变量名 = 值]... ; 赋值变量
- SET @变量名 := 值 [,@变量名 = 值]... ; 赋值变量
- SELECT @变量名 := 值 [,@变量名 = 值]... ; 赋值变量
- SELECT 字段名 INTO @变量名 FROM 表 ; 通过查询的字段值赋值变量
- SELECT @变量名 ; 获取使用变量
在MySQL中等值匹配语句也是=号，所以推荐对于变量赋值使用:=号。

在MySQL中不需要提前定义变量，若未赋值或不存在变量到只是获取到NULL而已。
局部变量是根据需要定义的在局部生效的变量，访问之前是需要提前使用DECLARE声明的，这一点与用户定义的变量有所不同。可用作存储过程内部的局部变量和输入参数，局部变量的作用域是在其声明的BEGIN...END块内。

局部变量的声明及赋值语法：
- DECLARE 变量名变量类型 [DEFAULT ...] ; 声明局部变量
- SET 变量名 = 值; 与 SET 变量名 := 值; 为局部变量赋值
- SELECT 字段名 INTO 变量名 FROM 表名... ; 使用查询到的字段赋值给局部变量

=========================================================================

if条件判断语法结构：

"IF 条件1 THEN 执行语句 ELSEIF 条件2 THEN 执行语句 ELSE 执行语句 END IF;"

存储过程参数传递：

在存储过程参数列表中每个参数以"IN/OUT/INOUT 参数名参数类型"的形式定义。

不同参数之间使用逗号隔开，in变量即传入的参数，在存储过程中直接使用即可。

out变量为返回的参数，在存储过程中把最后的运行结果保存在该变量处即可，不需要SELECT等额外操作，最后在存储过程外部可以直接获取到该值。

参数传入与接收：所有类型的变量都需要传入，其中in变量为输入变量，可以为常量或者赋值过后的变量。out变量为用户自定义变量比如"@result"，在存储过程运行完毕时其结果已经返回到该变量中。inout变量也是用户自定义变量，但是其值会被传入存储过程中，所以需要在传入该变量前需要对其进行set赋值。

=========================================================================

case语法：

case 变量 when 匹配值1 then 语句1 when 匹配值2 then 语句2

else 语句3 end case; 不加变量则视匹配值作条件，与之前的流程控制函数很类似。

不同的是结束标记多了个case、返回结果变成了将执行的语句。

while循环语法：满足条件则继续进行循环

while 条件 do 循环体 end while；

repeat循环语法：与while循环相反，一旦满足条件退出循环（至少执行一次

repeat 循环体 until 条件 end repeat；

loop循环语法：默认为死循环，需要配合两个语句使用：

LEAVE配合循环使用，退出循环。ITERATE必须在循环中，跳过当前循环剩下的语句，到下一次。

标记: LOOP 循环体 END LOOP 标记；配合使用 LEAVE 标记；和 ITERATE 标记；语句。

=========================================================================

游标CURSOR：局部变量只能操作单行单列数据，所以在获取整表的数据时存在局限性。游标正是为了弥补这方面的局限而存在的。

DECLARE 游标名称 CURSOR FOR 查询语句；获取整个查询的结果集。

OPEN 游标名称；打开游标。

FETCH 游标名称 INTO 变量[,变量...]；获取游标封装的结果集的数据，一次获取一整行的数据，所以我们需要使用多个变量来接收。

CLOSE 游标名称；关闭游标。

游标变量的声明需要在普通变量的声明之后。一般是采用无限循环读取游标中数据行的形式，对于已经将游标读取完毕后继续读取的问题，我们进一步采用handler处理器解决。

=========================================================================

条件处理程序handler：通过处理方式+出错码或者说出错类型的形式进行处理错误。

DECLAER 处理方式 HANDLER FOR 错误类型1 [,错误类型2...] 处理语句；

处理方式：1.CONTINUE继续运行当前程序、2.EXIT终止执行当前程序

错误类型：1.SQLSTATE '状态码' 指定状态码、2.SQLWARNING 01开头状态码

3.NOT FOUND 02开头状态码、4.SQLEXCEPTION 非01、02开头状态码

=========================================================================

存储函数function：与存储过程procedure的区别在于，function声明时需要声明return返回值的类型、然后在定义参数时只允许定义IN类型参数、最后在存储过程中手动返回数据。

CREATE FUNCTION 存储函数名称([参数列表]) RETURNS 返回值类型 [函数所属类型...]

BEGIN ...SQL语句...RETURN ...; END；

(mysql8.0开启binlog的话要求必须指定characteristic，声明该函数所属的类型）：

1.deterministic 相同输入相同结果、2.no sql 存储函数内无sql语句、

3.reads sql data 只包含读sql语句而不包含写sql语句

（三、触发器

数据行在发生变化(insert\delete\update)时，自动触发对应的sql语句。

现在的数据库只支持行级触发，即每变动一个数据行执行一次，而非语句级触发。

CREATE TRIGGER 触发器名 BEFORE/AFTER(变动执行前触发/变动执行后触发) INSERT/UPDATE/DELETE ON 表名 FOR EACH ROW(表明其为行级触发器（也只有行级) BEGIN ...sql语句... END；创建触发器。

SHOW TRIGGERS；查看当前数据库的触发器。

DROP TRIGGER 数据库名.表名.触发器名；删除触发器。

使用变更前字段使用OLD.字段名即可、变更后字段为NEW.字段名。

五、锁

锁是保证数据并发访问时的一致性、有效性的方案，按照锁的粒度来划分(从大到小)：

全局锁：锁定数据库中的所有表，由MySQL的SQL Layer层（核心服务层）实现。
表级锁：锁定整张表，由MySQL的SQL Layer层实现。
行级锁：锁定行数据的索引, 也可锁定行索引之间的间隙, 由存储引擎实现[InnoDB]

按照锁功能划分：读锁不会阻塞读锁（事务A持有资源的读锁时，事务B也可以获取该资源的读锁。），但会阻塞写锁（如果事务A持有资源的读锁，事务B尝试获取该资源的写锁，事务B将被阻塞，直到事务A释放读锁。）；写锁会阻塞其他所有锁（如果事务A持有资源的写锁，任何其他事务尝试获取该资源的读锁或写锁都将被阻塞。）。

读锁（共享锁、S锁）:

阻止其他用户更新数据，但允许读取数据。可保持一段时间的数据一致性。
写锁（排他锁、X锁）:

阻止其他用户更新和读取数据，可以防止大量修改数据时，其他用户的干扰。

=========================================================================

（一、全局锁：

对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续DML的写语句，DDL语句，以及更新操作的事务提交语句都会被阻塞。

典型场景就是进行”数据库全库逻辑备份“，从而获取一致性视图（不加锁或者表锁的话：先备份表1、表1插入数据、表2变动数据并关联表1、再备份表2。此时表2对应的数据关联着表1，但是备份中表1中的数据并没有对应变动。），保证数据的完整性。

使用语句 "flush tables with read lock;" 给当前数据库加上一个全局读锁。
使用语句 "unlock tables;" 为当前数据库解除全局锁。

执行 "mysqldump -h 192.168.0.1 -uroot -p1234 itcast > D:/itcast.sql(登录 192.168.0.1 地址的数据库使用用户名 root 密码 1234 将数据库 itcast 备份为 D:/itcast.sql 文件（同一台主机的话可以不用ip地址) (使用用户名root密码1234 登录192.168.0.1地址的数据库将数据库itcast 备份为D:/itcast.sql文件)" 备份数据库，但需注意的是该语句并非sql语句，而是mysql提供的工具，在终端处执行即可。

=========================================================================

但在数据库中添加全局锁是一个比较重的操作，存在以下问题：

如果在主库上全局锁，那么备份期间都无法写入，业务基本就得停摆。
如果在从库上全局锁，那么备份期间从库无法同步主库的二进制日志，主从延迟。

可使用引擎InnoDB提供的不加锁方式，以MVCC的机制来保证备份时的数据一致性。

但对于不支持事务的MyISAM来说，只能使用全局锁的方式。

在dump后可加上参数: MVCC:"--single-transaction" 全局锁:"--lock-all-table"

=========================================================================

（二、表级锁

表锁按照功能可分为以下四种：

表锁：全表数据操作 (如全表更新删除) 时维护数据一致性，分为表读锁、表写锁
意向锁：自动添加，优化行锁和表锁的共享和互斥行为，通过意向锁来兼容这二者。
元数据锁：自动添加，在访问表时会自动加上，来保证DML和DDL的一致性。

=========================================================================

表锁的锁定粒度大、发生锁冲突的概率最高、并发度最低。分为以下两种：

表共享读锁：又称为表读锁，允许本事务对锁定的表进行读取操作，不允许其他事务对其进行写操作，但可以进行读操作。
表独占写锁：又称为表写锁，允许本事务对锁定的表进行读取和写入操作，但其他任何事务都不能再对该表进行任何操作，必须等待表写锁结束。

只要在锁定了某张表的事务内，都无法查询或者更新其他未锁定表的数据，除非同时获取这些表的锁。而在没有锁定其他表的事务中，可以查询或者更新其他未被锁定的表。

=========================================================================

表锁操作：通过 "lock tables t1 write，t2 read;" 给t1表加写锁、t2表加读锁。

"unlock tables;" 来释放表锁， "show open tables;" 查看表锁情况。

表级锁争用变量：通过"show tatus like 'table%';"来查看；

table_locks_immediate：产生表级锁定的次数；
table_locks_waited：出现表级锁定争用而发生等待的次数;

=========================================================================

对于MyISAM来说因为没有事务，其读操作会自动加上表读锁，写操作自动加上表写锁。

而对于InnoDB来说，只有在必要情况（全表更新或者删除等）使用表锁，但主要是使用行锁来实现多版本并发控制。

=========================================================================

意向锁（优化行锁与表锁的共存互斥行为，不必全表扫描行锁，即可判断是否可以添加表锁。）就相当于一个虚拟的锁，代表着当前表是否存在行被锁定。“意向” 即表明本事务是否 ”有意向” 去持有锁或者说是准备去持有锁。

在添加表锁时会从头至尾扫描表中字段，判断是否有行锁及其对应类型，效率低。

InnoDB加入了意向锁，只要存在行锁即会自动添加一个意向锁，可直接去检查意向锁。

在添加行锁的同时，会给这张表加上意向锁（包括意向共享锁IS、意向排他锁IX）。

即表共享锁与意向共享锁可兼容（表读锁行读锁可以同时存在）、表排他锁、意向排他锁与任何锁都不兼容（只要存在行写锁或者表写锁那就无法再加任何锁（意向层面的））。

查看意向锁及行锁的加锁情况：select * from performance_schema.data_locks;

=========================================================================

元数据锁(MDL) 的加锁过程由系统自动控制，在访问表时元数据锁会自动加上。主要作用是维护表结构与数据的一致性、正确性，其中表结构我们也可以说是表的元数据。

mysql5.5版本中引入了元数据锁，避免DML与DDL的冲突，保证读写正确。

表的增删改查操作会自动加上MDL读锁(共享)、包含DML读锁、DML写锁。
表的结构变更会自动加上MDL写锁(排他)、即DDL写锁。

’MDL读锁‘ 看似与行锁类似（行锁使用data_locks查看元数据锁使用metadata_locks查看且在单select时加MDL读锁，但不会加行锁），但却是以不同于行锁，是对更高一级层次上加的锁。

行锁是维护数据的一致完整，元数据锁是维护表结构DDL 与表数据DML 的一致。

MDL读锁之间兼容互相不会阻塞，MDL写锁是排他的同时与MDL读锁和写锁互斥。

查看元数据锁：select * from performance_schema.metadata_locks;

=========================================================================

（三、行级锁

行锁是由存储引擎来实现的，而对于InnoDB(InnoDB的数据是基于索引组织的)，其行锁是通过对索引上的索引项进行加锁（即对B+树结构的索引加上锁插入删除更新都是经过二级索引的查询是聚集索引）来实现的，而不是对记录加的锁。

记录锁 (Record Lock）：锁定单行记录的锁，防止其他事务的update和delete。在RC、RR隔离级别下都支持。
间隙锁 (Gap Lock）：锁定索引记录间隙(不含记录两端)，确保索引记录间隙不变，防止其他事务在这个间隙内进行insert，防止幻读。在RR隔离级别下都支持。
临键锁 (Next-Key Lock）：行锁和间隙锁的组合，同时锁住数据（包含该记录，及该记录之前的间隙。（但不会包进间隙之前的那个记录），并锁住数据前面的间隙。在RR隔离级别下都支持。
插入意向锁 (Insert Intention Locks）：做insert操作时添加的对记录ID的锁。

=========================================================================

相比于表锁和页锁，行级锁的粒度更小，有更好的并发性能和更少的锁冲突。

然而，行级锁也要更多的内存和cpu资源，因为需要对每一行都进行管理。

共享锁(S锁) : 读锁，与前面类似，但行级锁的粒度是针对某条数据行来说的。
排他锁(X锁) : 写锁，与前面类似，但行级锁的粒度是针对某条数据行来说的。

行锁只能在事务之中使用，事务一旦结束就会自动释放所有的锁。

=========================================================================

触发行锁的操作：（对于普通select语句，InnoDb不会加任何锁

insert、update、delete：InnoDB会自动地给被操作的数据行添加一个排他锁。

select...for update：手动地对选定的行添加一个排它锁。

select...lock in share mode：手动地对选定的行添加一个共享锁。

查看行锁：select * from performance_schema.data_locks;

行级锁争用变量：通过"show tatus like 'innodb_row_lock%';"来查看；

Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花费的时间
Innodb_row_lock_time：从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg：每次等待所花费的平均时间
Innodb_row_lock_waits：系统启动到现在总共等待的次数

=========================================================================

默认在RR级别下运行，InnoDB使用next-key锁进行搜索和索引扫描（即给你查找的范围进行锁定防止查询范围内被插入新数据），以防止幻读。

InnoDB的行锁是针对索引的锁，若不通过索引条件检索数据，那么InnoDB将对表中所有的记录加锁，此时就会行锁升级为表锁。
针对唯一索引进行检索时，对已经存在的记录进行等值匹配时，将会自动优化为行锁（“S，REC_NOT_GAP” 行共享，非间隙锁）
索引上的等值查询 (唯一索引), 给不存在的记录加锁时，优化为间隙锁。

"即我们使用update等语句生成的排他或者共享锁指向的数据行为空时，

我们会把这段间隙加上间隙锁（“S，GAP” 共享锁，间隙锁），包含后面的数据、不含前面的数据"
索引上的等值查询 (普通索引（因为普通索引非唯一，所以不仅要给其加锁还要给其前后加锁，防止重复数据插入）), 遍历到最后一个值不满足查询需求时，

next-key锁退化为间隙锁。（即不要把尾部的记录也包含住了）

"当前记录加上行锁、临键锁（“S” 共享锁，临键锁），其后面一条 (不满足查询) 的数据加上间隙锁

即加锁：包含本数据，包含本数据前的间隙+本数据，包含本数据其后的间隙"
索引上的范围查询 (唯一索引), 会加锁到不满足条件的第一个值为止。

"即第一个数据加行锁，其后的每个数据加上临键锁（包本记录及之前间隙）(即为除了最前面的数据，所有间隙和数据都被临键锁锁定。而最前面的数据交给行锁。)"

间隙锁唯一目的是防止其他事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁。

其实就是唯一索引锁定本身及内部间隙，左右外部的间隙不锁

普通索引不仅要锁住本身及内部间隙，为了防止重复数据插入还要锁定左右外部的间隙

间隙锁一是为了等值不存在、二是补充右部间隙（不含记录）

=========================================================================

非索引 -- 表锁
- - 唯一索引等值存在 -- 行锁
  - 普通索引等值存在 -- 行锁+临键锁+间隙锁
  - 任意索引等值不存在 -- 间隙
- 索引范围 -- 行锁+临键锁

=========================================================================

（四、乐观锁与悲观锁

非mysql中具体体现的锁，没有内置的实现。更像是两种思想的体现：

乐观锁：直接select，提交数据时更新版本号并且检查版本号是否非被修改（where version=old_version; 被修改直接返回0，回滚事务。）。

悲观锁：使用select...for update或select...lock in share mode，更新数据直接提交。

乐观锁：

乐观锁操作数据时非常乐观。认为其他事务不会在我们操作数据的同时修改数据，故不会在操作时（(比如获取数据行数据进行运算得到新数据) 在这个操作时不锁定数据行）为数据行上锁，而是在提交数据更改时检查是否有其他事务修改了这条数据。没有就提交更改，否则就回滚事务。

实现方式：

我们一般是使用版本号(或时间戳) 字段来实现乐观锁，修改时增加版本号(更新时间戳)，更新时检查版本号(或时间戳) 和读取记录时是否一致，一致则更新，否则回滚。

在Java中java.util.concurrent.atomic包下的原子变量，使用了乐观锁的CAS(Compare-And-Swap "比较并交换“ 比较：检查内存位置V中的值是否与预期原值A相同。交换：如果内存位置V中的值与A相同，那么将该位置更新为新值B。返回：返回操作结果，通常是一个表示成功或失败的布尔值。)实现方式。

=========================================================================

在冲突较少时可以获得较高的并发性能（在使用数据运算时允许其他事务访问共享数据内容这样其他事务的访问不会被阻塞），但冲突较多可能导致大量回滚。

适用场景：低冲突环境、读多写少的场景、短事务操作（在数据库的事务都特别短的情况下，使用乐观锁期间其他事务修改数据而发生冲突的情况也会很少。）、分布式系统（对于分布式系统，由于网络延迟等原因事务冲突的可能性是较低的，故乐观锁是一个合适的选择。）、互联网应用（互联网应用中，不仅用户读取操作较多，且用户的修改操作发生冲突的概率也很小，因为一般只对本账号数据、订单进行修改。）。

缺点：冲突检测（乐观锁可能产生大量冲突）、处理开销（对于冲突回滚行为，会增加系统开销。）、版本管理、编程复杂性。

悲观锁：

悲观锁操作数据时比较悲观。认为每次获取数据时，其他事务都会改动数据，故每次拿数据时都会上锁，这样别的线程想拿到该数据行时都会阻塞。

实现方式：

传统的关系型数据库用的锁机制：比如行锁、表锁、读锁、写锁等，都是操作前先上锁。比如，使用select...for update或select...lock in share mode等。

或者是在Java中使用synchronized、ReentrantLock等独占锁。

=========================================================================

在冲突较多时能保证数据的一致性、完整性，防止并发冲突时多次回滚事务问题，但是悲观锁在读取数据行时已经加锁，对于其他查询事务性能有影响。

适用场景：写操作多场景、并发冲突高的场景、业务要求强一致性而低要求并发。

缺点：性能开销、并发度降低、死锁、锁超时。

=========================================================================

六、InnoDB引擎

（一、逻辑存储结构-表空间内部结构

1、表空间内部结构：

表空间内部结构层次依次为：表空间》段》区》页》数据行

为什么要有区？

页和页之间使用双向链表连接，可能导致页与页之间的物理距离太远，造成我们获取连续的页时产生随机IO（进行寻道旋转等操作）效率很低、所以我们需要尽量让页的物理位置相邻，形成顺序IO。

一个区就是物理位置上连续的64个页，页默认大小为16KB、所以一个区的大小为64*16KB=1MB。在数据量大的情况下，为某个索引分配空间时就不再按页为单位分配，而是按照区为单位分配（一次直接给整个区，而不是等索引填充满了再分配多的页）。

这样虽然可能导致多读取空闲的区空间到内存、分配给某个二级索引或者数据页，索引无法填充满整个区（空间浪费）。但是却提高了从硬盘到内存的效率（因为一次读取整个区是顺序IO，然后内存里面处理页的指针就不需要磁盘随机IO的参与了）

区与区之间也是双向链表，如果数据量真的特别大，甚至可以让区与区之间物理相邻

为什么要用段？

为了使需求的页尽量能在同一个区，我们可以让一些区专门存放数据页、而另外一些区专门存放非数据页（叶子结点链表连接、容易被同时访问）

其中存放叶子结点区的集合为一个段，我们称之为“叶子结点段”（数据段）、存放非叶子结点区的集合也为一个段，我们称之为“非叶子结点段”（索引段）

此外还有些专门存放特殊数据的段，如“回滚段”

段并不对应表空间中连续的物理区域，而是一个逻辑上的概念。由若完整的区以及干零散的页面组成（其中零散的页即“碎片区”

为什么要用碎片区?

对于数据量很小的表，由于聚集索引的存在至少占掉2MB的空间、每加一次索引又会占掉2MB的空间。所以我们可以让索引页、数据页去共用一个区的空间，这个区就叫做碎片区。

ibd文件代表一张表，每个索引对应一个或几个数据区和索引区

多个索引对应的多个数据区放在同一个数据段内、索引区放在索引段

如果索引的数据都不够填满一个区的，很浪费空间，所以可以把多个索引的数据放入同一个数据区、索引放入同一个索引区

甚至可以把数据放入索引区、索引放入数据区（区中的页可以作用于不同段）

所以碎片区不能属于某个段，我们直接让碎片区直属于表空间。碎片区有些页给数据段，有些给索引段，所以在段的组成部分里才有零散的碎片页。

即开始时不同索引树之间、索引页和数据页之间不区分都混在碎片区内

数据量太多时，才给它们完整专门的区、划分不同的段来管理这些数据。

=========================================================================

查询缓存（以sql语句为键，结果为值查找）但是太过鸡肋（因为要求查询语句完全一致，空格注释大小写一致，故命中率太低）在mysql8.0之后抛弃了

相同函数如now相同语句执行结果不一致也没必要缓存，被删或者更改的数据未及时更新到缓存也可能导致数据不一致。

分析器词法分析--把语句分析为一个个token关键字、

语法分析--根据语法规则判断其是否满足sql语法规则、最终生成语法树

优化器确定sql语句的执行方式、如全表扫描还是利用索引，利用哪个索引

执行器判断用户权限、根据执行计划调用存储引擎API、并将结果保存到查询缓存(8.0以下）

2、页内部结构：

页内部记录间是单向链表，从最小记录指针开始，到指向最大记录的指针结束。

在查找时有数组结构的页目录来辅助我们进行二分查找。

而页和页之间使用双向链表关联，因为无法任何时候都保证页空间的连续性。

一个页内部一般包含以下几种数据信息：

文件头-》页间的指针、页的信息，如页类型等、校验和（通过对数据的某些部分进行数学计算如异或、循环冗余校验等）来生成一个较小的数值）

（1.此校验和与尾部的校验和起到乐观锁的作用（防止在页的读取过程中被修改）、2.校验数据传输是否出问题）

文件尾-》与文件头一致的校验和（主要是校验文件完整性）、最后修改时对应日志序列的位置

页头-》保存页目录槽数量、本页中的记录数量、还有第一条被删除数据的地址等信息

页目录-》通过目录二分+分块快速找到页中的数据

最小最大记录-》记录最大的用户记录和最小的用户记录

用户记录-》记录按指定的“行格式”存放到用户记录空间，由行格式来组织数据形成链表等结构

空闲空间-》除了用户记录的空间，即未被使用的空闲空间

其中最大最小记录并不是真正的数据最大值最小值，而是特殊的数据行，与数据无关所以其并不是被包含在用户记录之中的。

最大最小记录的记录头与其他用户记录类似，但其数据位置保存的是单词infimum\supremum(即最小值，最大值的英文)

3、InnoDB的行格式：

在表定义最后面使用ROW_FORMAT可以指定行格式。

行格式：compact(紧凑的)、dynamic和Compressed(动态的、被压缩的)、Redundant(冗余的)，默认为dynamic

compact行格式：

行记录结构包含：1.变长字段长度列表、2.NULL值列表、3.记录头信息、4.真实数据

变长字段长度列表-》会把varchar这样的变长类型的长度，倒序存储到该列表上

NULL值列表-》数据连续存储，如果null值不标记的话，可能把后面的数据认作前面的数据。

故使用1表示null 0非null，每列数据使用一个二进制位标记，再把该二进制

倒序存储到Null值列表处。非空列无需保存二进制位（只标记，不占位）

记录头信息-》delete_mask 标记删除，可防止后面数据前移性能下降，可被覆盖

过多的删除记录会组成垃圾链表，由页头中指向首条垃圾记录

-》min_rec_mask 值1表示非叶子结点的最小记录、值0表示正常记录

-》record_type 最小记录2最大记录3、索引页中其余记录为1、数据页其余记录为0

-》heap_no 标记记录的序号，其中0和1是给了最小和最大的两个虚拟纪录

（record_type为2和3的那两不存数据的记录，是在页存储数据前即存在的）

-》n_owned 页目录是需要分组的，其中每组的最后一条记录，

会负责使用该n_owned字段来记录该组的总数据条目数。

-》next_record 标记记录和记录间的偏移量即有正又有负，起到指针的作用

从页结构中的最小记录指出，到用户记录空间，再回到页结构中的最大记录结束

真实数据-》存在隐藏列：row_id在无主键无唯一时作为聚集索引字段添加

此外，还有transaction_id(事务id)、roll_pointer(指向之前版本的数据行)等事务日志相关隐藏列

=========================================================================

删除时对本记录进行删除标记、并记录下一个删除记录的偏移量（形成一个删除数据列表）

前一个记录偏移量跳过该记录、然后分组的最后一条记录最大记录的n_owned数量减少

（分组为最小记录一组、用户记录空间+最大记录一组）

页目录的结构并不是直接存储每一条数据的位置信息，而是将数据分组，

只存储分组中最大的记录信息作为代表，此记录的n_owned为该分组的条目数量。

既可以利用数组结构实现快速的二分查找，又可以通过分块来避免消耗过多存储空间。

硬盘中数据页之间以B+树的形式组织与查询，而数据页内部需要读入内存的数据是单向链表的方式组织，所以需要二分查找+分块查询的方式提升查找效率。

其中位置信息即为对应数据条目的位置偏移量，其中页目录存储代表条目的空间我们称之为槽solt，槽按先后顺序存储
分组的条目包含最大最小条目，但是不包含被删除的条目
第一组即最小数据单独一组、其余分组数量保持在4到8条之间，最后一组即包含最大数据的那组，能接收多余数据，所以数量保持在1到8条之间
n_owned保存本分组内的总条目数量

对槽进行二分查找，先找到槽对应的那个分组，我们查询的数据就在这个分组内

但分组内条目是小的指向大的，此时我们可以利用其前面的一个槽来找到分组的首个条目

（为了保证始终可以找到前面的槽，所以我们拿最小值单独为一组）

=========================================================================

其他行格式（dynamic和compressed格式）

行溢出：一行的某列数据过大，导致行记录超过一个页的存储范围，这种现象叫做行溢出

对于部分存储空间过大的数据记录，我们在某页中只存储其部分数据，在本页使用指针指向剩余数据所在的页及其信息，即把剩下的数据存储在其他页中

compact、dynamic和compressed格式差不多，只在处理行溢出问题时存在分别：

区别在于dynamic和compressed处理BLOB、TEXT等不存储部分数据，直接全放在外部页

而且compressed会对存储的行数据进行zlib算法压缩处理，能有效存储BLOB、TEXT、VARCHAR等大长度类型的数据。

Redundant格式为mysql5.0之前的行记录存储方式：

与其他行格式不同的是每个字段都会记录一次”字段长度偏移列表“每个字段都记录冗余严重

且具体长度需要通过前后偏移量作差才能获取，不够直观

由于所有的偏移量都记录了，所有没有NULL值列表、此外记录头信息也有点差异

（二、逻辑存储结构-内存磁盘架构

内存结构：

对于数据库的辅助索引页的变更操作，如果没有被查询而读取进Buffer Pool的话，会把变更操作先存入Change Buffer里面。此时是没有具体数据进入的只知道变更行为。

等待辅助索引页被读取入Buffer Pool时就能把这些变更操作与索引融合了。

如果还没等到访问数据库就不小心宕机的话，索引更改不会丢失，而是直接缓冲到磁盘的system tablespace的Change Buffer里面。

为什么只对辅助索引生效？

比如插入对聚集索引的话，一般是按聚集索引递增顺序的。所以在把索引页读取进入的时候一般是物理位置相邻的比如在同一个区的、甚至是在同一页中，直接可以顺序取出

而对于辅助索引而言其物理位置并没有像聚集索引那么严格，所以我们直接读取辅助索引的索引页的话，可能是在不同的区，造成随机磁盘IO等问题

（独立的索引结构：辅助索引不改变数据行的物理存储顺序。它只是提供了一个额外的查找路径，通过索引键值快速定位到数据行。因此，辅助索引的插入不会影响表的数据页布局。索引页的分配：当向辅助索引中插入新键值时， mysql会在需要时动态地分配新的索引页。这些页可以分布在表空间的不同区域，而不是必须连续。这种分配方式使得辅助索引的增长更加灵活，但也是离散的。）

而且我们对辅助索引是缓存到change buffer中的，有时还可以把多个同一索引页的插入操作一起与辅助索引页融合，不必多次重复IO读取。

自适应哈希索引自动检测建立，一旦建立的话直接通过关键字查找到对应叶子结点页，不需要查找非叶子结点页，但是是针对于等值查询的，范围查询还是使用B+树结构进行

（即在查询条件相同时立即找到对应叶子结点页的位置

innodb_adaptive_hash_index：这个参数控制是否启用自适应哈希索引，默认情况下是启用的。可以通过设置SET GLOBAL innodb_adaptive_hash_index = ON;或OFF来开启或关闭这个特性。SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';查看是否开启。

SHOW VARIABLES LIKE 'innodb_log_buffer_size';查询缓冲区大小。

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';查询日志刷新时机策略。

SET GLOBAL innodb_flush_log_at_trx_commit = 1;修改日志刷新时机策略。(默认1）

LogBuffer -- MySQL系统层面出问题时数据丢失

OS cache -- 操作系统层面出问题时数据丢失

由于MySQL出问题概率比操作系统出问题概率大得多，一般就是及时写入OS cache然后再慢慢从OS cache中刷盘到磁盘当中：

所以我们一般就是选择策略2 及时写入cache，但在cache处不要求及时写入硬盘。

磁盘结构：

表空间：分为系统表空间（ibdata1文件）、临时表空间、常规表空间、Undo表空间以及file-per-table表空间。系统表空间又包括双写缓冲区（Doublewrite Buffer）、Change Buffer等

Redo log：存储的是log buffer刷到磁盘的数据。

Doublewrite buffer：innodb 将数据页写到文件之前存放的位置。8.0.20版本之前，doublewrite buffer存放在InnoDB系统表空间中，8.0.20版本后存放在doublewrite中。

Undo log：存在于global临时表空间中，用于事务的回滚。

innodb_data_file_path变量指定系统表空间文件（ibdata1）的文件存放地址。

innddb_file_per_table变量（默认为开启）开启则数据存储于单表单文件表空间，如果关闭的话，则所有表数据都存储于系统表空间中。

手动指定（通用）表空间的情况除外，可通过下述语法指定表空间。多个表可以指定相同的表空间，即相同的idb文件。（但是用得不多）

Undo表空间：存储Undo日志，在MySQL初始化的时候会创建两个大小一致的默认undo表空间，文件名为undo_001和undo_002。

临时表空间分为session临时表空间和全局临时表空间。Session临时表空间在session请求时候创建的，最大分配2个，一个是用户创建的临时表空间，一个是优化器创建的临时表空间。全局临时表空间默认是数据目录的ibtmp1文件，所有临时表共享。

由于操作系统和mysql数据页的大小不一致的问题，导致在buffer pool中数据页刷新入磁盘的过程中要分成多次写入，所以可能导致因意外只写入了一部分页的情况(部分写失效)

（如linux操作系统的页大小为4KB 而Mysql的页大小为16KB）

为什么有了redo，还要Doublewrite Buffer机制?数据库双写的好处是什么？

Redo log记录了数据页的物理修改操作，确保在发生故障时可通过重做这些操作恢复数据。然而，因为Redo log记录的是页的修改操作，若一个数据页在写入磁盘的过程中只写了一部分，Redo log并不知道页的原始内容，故无法仅凭Redo log恢复损坏的页。

双写缓冲区（Doublewrite Buffer）正是为了解决这个问题而设计的。当InnoDB需要将一个脏页写入磁盘时，它会先将页写入到双写缓冲区。

如果在将数据页写入最终位置的过程中发生崩溃，InnoDB可以在重启时利用双写缓冲区中的数据页副本来恢复数据，确保数据的完整性和一致性。

即数据写入之前-》redo可恢复、数据写入了部分-》Doublewrite Buffer可恢复

后台线程：

show engine innodb status;查看innodb引擎状态信息，线程信息。

（三、事务原理

事务（原子性、一致性、持久性）--》通过redo log和undo log来维护

事务（隔离性）--》通过锁机制与MVCC机制来维护

1、Redo Log 重做日志：

redo log 重做日志，记录事务提交时数据页的物理修改（例如对某页中的某个字节进行了什么什么修改），是用来维护事务的持久性（主要是依赖redolog、wal、 doubleWrite buffer、checkpoint等机制）。

分为重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）前者是在内存中，后者是在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘发生错误时，进行数据恢复使用。

即我们只需要通过将redo日志成功刷新到磁盘当中，我们就不需要担心数据的持久化问题。脏页刷盘成功持久化成功、脏页刷盘前（脏页刷盘过程中出现意外部分写失效的话可通过刷盘前写入的双写缓冲区恢复数据）出现意外使用redo日志恢复持久化成功。

所以我们提交事务时只需要保证redo日志成功提交到磁盘即可。而在脏页刷盘成功后，其redo日志也就可以逐步清理（其为一个循环文件组写入的过程其中要求写入点要在(checkPoint)检查点之前检查点即为位于最前面的、且对应脏页未被刷新到磁盘的redoLog记录。）掉了。

这种持久化数据前先写日志的方式，我们又称之为WAL（write-Ahead Logging）

=========================================================================

其中内存的redolog buffer是在buffer pool中的数据页变更结束后，才将相应修改变更记录到redolog buffer当中的。

除了数据持久性外，相对于buffer pool缓冲池中的数据直接刷新到磁盘，是一个随机IO，效率较差，把buffer pool中的数据记录到redo log，是一个顺序IO，可以提高事务提交的速度（这也是为什么需要同步刷新Redolog 而异步刷新bufferPool的主要原因。）。

=========================================================================

相关参数innodb_flush_log_at_trx_commit：

控制commit动作是否刷新log buffer到磁盘。该变量有3种值：0、1、2，默认为1。

取值0：每次提交事务都只把redo log留在redo log buffer中，在故障时可能会丢失最近一秒的事务
      （延迟写）
取值1：每次提交事务都将redo log 持久化到磁盘上，也就是write+fsync，最安全、默认设置
      （实时写、实时刷）
取值2：每次都把redo log写到系统的page cache中，也就是只write，不fsync
      （实时写、延时刷）

=========================================================================

redoLog的持久化并不只在事务提交时发生，只是事务在提交时要求事务的所有redoLog都必须进行持久化，以此来实现、维护事务的持久性。

2、Undo Log 回滚日志：

undo log 回滚日志，用于记录数据被修改前的信息，可用来维护事务的原子性。

利用undo日志我们可以来实现回滚操作与MVCC(多版本并发控制)机制。

数据库的每次DML都会记录相应的UndoLog日志记录。如事务内进行update的话，首先会记录”UndoLog回滚日志“，该日志其实就是把原来数据行中的数据复制一份。然后才对数据行进行修改、更新，这时数据行中除了rowid、事务id外，还会记录回滚指针指向刚刚的回滚日志，即我们刚刚复制的那份数据，以便undo进行事务回滚。

=========================================================================

相比于undoLog与BinLog来说UndoLog并没有对应的日志文件，与其说undoLog为一种日志不如说其为一种起到日志记录作用的特殊页类型 "undo页" ，最终是存储到ibd文件内的。

如果事务在提交之前其脏页就已经被刷盘了，此时脏页被读取的问题就是我们之前所讲的脏读，而如果事务最终出现问题回滚，这时就需要使用undo日志了。

一是处理已经刷盘的脏页、二是要处理还在内存中的脏页。

就通过这样的WAL机制保证事务的原子性，提交一定全部完成、回滚一定全部没完成。

3、Bin Log 二进制日志：(补充)

与UndoLog、RedoLog的存储引擎级别不同，BinLog是Mysql服务器级别的。

记录我们执行的写操作，可以在my.ini配置文件处修改log-bin参数来设置binlog的文件前缀，设置之后即等同于开启了binlog日志文件。

通过语句show binlog events来查询记录的写操作语句，如事务开启、插入等。

通过下面所示指令，将BinLog记录文件保存到sql文件当中。

Binlog是记录索引的数据库表结构变更以及表数据修改的二进制日志，不会记录select和show这类操作，主要用于数据恢复和主从同步，写入实际是事务提交的时候。

=========================================================================

BinLog日志记录的格式有三种：statement、roe和mixed。分别为记录具体sql语句、记录变更后的行数据、或者这二者结合起来。

第一种方式最为常见，日志小，但是可能出现如now函数或者交叉式自增插入这样的”sql有毒“问题。可能导致恢复的数据与原来的数据不一致的问题。

针对这种问题也存在解决方案：在now的sql之前设置一下时间戳等

=========================================================================

RedoLog和BinLog区别？

（1）日志的层面不同、记录的信息不同：

Redo Log 是 InnoDB 存储引擎层面的日志，记录了数据页的物理修改，即具体的字节变化，比如在某个数据页的特定位置插入、更新或删除数据，确保事务的持久性和数据的完整性。
Binlog 是 MySQL 服务器层面的日志，记录了对数据库执行的每个操作的逻辑信息，即所有更改数据库状态的 SQL 语句和存储过程的调用，不仅限于 InnoDB 存储引擎，也包括其他存储引擎的操作。

（2）日志总量不同、日志作用不同：

Redo Log 会循环利用文件组覆盖。目的是用于数据库服务器崩溃后恢复未刷盘的事务，只要记录所有脏页即可。
Binlog 会不断的累积记录数量。需记录所有的写sql，用于归档、审计、复制和数据同步等，可恢复到任意时间点。

=========================================================================

RedoLog与Binlog能否相互替代？

（1）复制支持不同：

Redo Log 不支持复制，它只用于单个数据库事务的恢复。
Binlog 支持复制，是实现主从复制的基础。

（2）审计和数据同步：

Redo Log 不适合用于审计和数据同步，因其只记录物理变化，难以直接用于这些场景。
Binlog 可用于审计和数据同步，因其记录的是逻辑变化，更易用于这些场景。

（3）性能影响：

Redo Log 写入对性能影响小，因其为顺序写入，并且通常写入到专门的日志文件中。
Binlog 写入可能对性能影响较大，尤其在高并发环境下，因其需记录每个操作详细信息。

（四、MVCC

事务的隔离性就是事务的执行不能被其他事务干扰，我们一般使用锁和多版本并发控制来实

Multi-Version Concureency Control（MVCC）多版本并发控制：

对于MySQL的InnoDB存储引擎而言，实现的是基于MVCC，而不是基于锁机制。

MVCC优势：读不加锁、读写不冲突。

常规微服务分类：(1)读服务-》缓存索引库数据库、(2)写服务-》先缓存再数据库、

(3)扣减服务-》秒杀服务对某个热点产品不断update、

而对于常规的单体架构而言，一般就是适合使用MVCC的读多写少场景

事务相关的三个隐式字段及undoLog的版本链维护

隐含ID(DB_ROW_ID): 如果由InnoDB自动生成聚集索引时产生该隐藏列。
事务ID(DB_TRX_ID): 标记更新该行记录的事务id，每个事务都有分配有序递增事务id。
回滚指针(DB_ROLL_PT): 指向当前记录的Rollback Segment的UndoLog记录，我们就是依靠该指针才形成多版本数据链

如图如果修改过程中有一个事务进行快照读，此时不会发生阻塞。而是读取数据修改前的历史版本，即读取数据为1,2,3...的数据行。

此时事务1已经提交了，再次进行快照读的话读取的数据便是10,20,30...的版本了。

快照读时使用ReadView保证其读取到其应读的数据：

m_ids：活跃事务id集合、min_trx_id：最小活跃事务id、max_trx_id：预分配事务id、craetor_trx_id：该ReadView创建者的事务id。

通过ReadView可以定位到UndoLog历史版本的数据，然后从版本链表中读取最新的数据，

对于读取到的每条记录进行判断：（四步判断）(保证能读到最新的已提交事务或者为本查询事务修改的数据版本)

该记录事务id 等于本查询事务的id ：表明本记录为该事务更新的可读。
该记录事务id 小于 MIN_TRX_ID：表明本记录快照前已经提交可读。
该记录事务id 大于等于 MAX_TRX_ID：表明本记录为快照后的事务更新的读下条记录。
该记录在min_trx_id和max_trx_id之间的话，需要判断：
1. 该记录事务id 处在活跃事务的集合内，表示该记录未提交继续读下条记录。
2. 该记录事务id 不在活跃事务的集合内（关于为什么大于最小事务还能不在活跃事务内：如活跃事务2.3.4.6.7，我们能确定的只是<2一定已经提交而无法确定>2的事务比如5一定是未提交的（主要是事务按序分配id但不一定是按序提交（所以6还在活跃不能确定5也在活跃），表示该记录已提交可读。

对于不同隔离级别的实现方式：

读未提交：读未提交无需锁无需MVCC，因为修改数据直接改数据源，会出现脏读。
读已提交：每次查询都会创建ReadView读取数据。
可重复读：同样的查询只会使用第一次创建的ReadView(不管是首次未提交的事务还是新事务都不会被读取到，只会读取到首次已提交或者该查询事务本身提交的数据 )读取数据。
串行化：直接表锁，锁定整个数据表。