前言

多数来自王校长MYSQL系列
笔记优点：从调优角度去挖知识

索引

调优为主线，进行贯穿，再引申。
从mysql初期设计表开始考虑调优，控制单行数据的大小。
不能像初学者，直接来一个varchar(255)，过于浪费空间。
varchar不像char定长，它首先额外存储1或2字节的长度，再存储一个变长的数据。
存储就不得不讲B+树。

B+树是mysql索引数据的存储数据结构。
先以聚簇索引为例，以主键进行索引树，非叶子存储索引，叶子存储整行的数据，比如bigint类型自增，除了存储本身8字节整型，还加上一个6字节的向下指针，对于bigint类型主键，非叶子节点的每一条数据需要至少占用14字节，（tips：B-是开区间）比如，ID 1，真正的节点存储格式80 00 00 01，ID 3 为80 00 00 03，这是8字节，指针来说，是6字节。根节点是16kb的内存页，16kb*1024/14约等于1170索引数据，第二层有1170节点，每个节点有1170个ID+指针，那么，第三层就是1170^2，大概1百万叶子节点，叶子节点存储的是所有数据。
那么，如果1条mysql数据，没有好好规划它的大小。如果1行有16kb，那么mysql只能存储1百万条数据，就是1个内存页只有1行数据，这是非常恐怖。如果是1行1kb ，那么，1个内存页存储16行，那么，1百多万乘16，可以存储1千6百万，差不多2千万条数据。

这里，3层B+树出现一个3次IO索引。每次IO索引时间复杂度O(logn)其实就是用了二分查找，这是B+树查询快的原因之一。刚刚讲到，有一个内存页1行数据的IO，这查效率是相对慢，这就涉及到加载内存页的问题，16kb内存页加载出来，这算1次IO。首先，一层16kb内存页加载完成后，再进行二分查找，定位ID范围，然后指向二层，此时，又要加载16kb内存页，在这里边，又要定位ID范围去加载第三层，三层又要加载16kb内存页，再二分查找最终定位数据。当然，1和2层一般回缓存预热，减少查询IO。但是，如果1行16kb，查询多行，IO次数依然较大。
控制1行数据大小，这就是B+树的高扇出性。

为什么不用二叉树？不用hash表？B树也不怎么用？
B树，每一个节点都要存储数据。从根节点开始存储数据，这时候，如果1kb数据，最多15条，再加上16指针，指向16个二层节点，二层节点又16个指针，三层就大概是16的平方，369个叶子。三层B树，撑死就这么多。如果2千万条数据，那么mysql一直IO内存页，再二分查找，非常要命。
二叉树呢，原因一样。B树起码是个多叉树，还不如B树。
那HashMap呢，在不冲突情况是O(1)，而树的索引，查询是O(logn)。为什么不用hash表？
第一点，不支持模糊查询，也没有最左匹配原则
第二点，不支持范围查询，一个个离散hash，太恐怖了。
第三点，hash冲突不可避免。

为什么不用跳表？
跳表是一种链表的优化，查询O(logn)。
主要是mysql的存储介质是磁盘，链式结构是存放内存。
mysql数据是块形式，是16kb内存页，在内存页定位数据。链表是一个个node节点，节点只有1条数据，磁盘IO更多，而且磁盘无法对链表预读。
B+树数据分布更均匀，每一条查询速度一样，更平衡稳定。
跳表数据分布不均匀，查询数据经过路径长度是不一样的。

B+树有一个最左前缀原则
能够支持模糊查询，比如查找同姓的，王1，王2，王3直接遍历叶子内存页；
能够支持范围查询，比如查找ID范围，从1到100直接遍历叶子内存页；

聚簇索引，是每一张表都有的主键ID
辅助索引，是自己设的索引，每个辅助索引都有一棵叶子是索引值+主键的树

联合索引，多个字段连起来的索引，如果没有主键ID，可以是辅助索引；

如何在表里边定义索引？
要看字段的随机度，随机度越高，定义为索引的索引效率越高。
举个反例，如果存储1000万人，性别只有男女，也就是只有01两种情况，这个字段定位性太差，一下子定位到差不多500万的范围，建议不加，因为加索引优化不大，而且还会增加插入数据时的一个索引负担。
如果是日期，随机性很高了，怎么看离散呢，命令show index，然后看cardinality（扣低内了体）的值，接近1，说明离散越高，就适合索引。当然，这最终还是对应我们的业务 来说。

cardinality的值，多说一下，它是通过采样算法，会实时变化的。

覆盖索引，它其实不是索引，它是一种效果，没有树，指查询无需回表的索引。关键在于select的字段，是否都在辅助索引的叶子中。如果是select * ，那它回经历辅助索引三层，然后聚簇索引三层
，这个过程就是回表，发送了6次IO，差距就上来了。

索引前缀：把文本前缀作为索引，增加长文本查询效率。

Q：什么情况索引失效？

记忆：LOL+-*/ not null function convert join
Like 不匹配最左前缀的模糊查询，比如，以%开头
Or 不匹配最左前缀的Or，or作为condition的连接符，当前后两个条件都是索引不失效，但凡有一个不为索引就会失效。

Lian联合查询，不匹配最左前缀的and联合查询，ab联合索引，如果不以a为首部作为查询，就会索引失效。

+-*/ 条件字段加减乘除计算，索引失效，比如，age年龄-1=10，失效。

not 非不等于，!=，<>，is not，取反集就会失效。

null 字段is null 和 is not null，不一定失效，索引允许null，但是B+树索引不会存有bull值，比如 age is not null，可以走索引，而且还有优化器可优化。为避免失效，字段尽可能设置not null，如果需表示null，尽量设默认值，使索引完全能够构建到B+树。对于联合索引，左边不为null右侧为null，也有索引节点。

function 对字段使用内置函数，比如dateadd(propName, -1) =2022.1.28，失效。

convert 字段转换，比如：员工ID是varchar类型，查询用了 id = 12345，这就发生varchar转int，因此，要加上单引号。

join 连表看情况，如果id类型不一样，那就会失效。

我是就是我，不一样的版本不一样的我。

select * from table where A>3。A是辅助索引。
mysql5.5，一般是全表扫描，不会用A辅助索引，三次IO。这里考虑了离散读的问题，从辅助索引三次IO，拿到所有ID，再从聚簇索引发生超过三次IO拿数据，至少6次IO，有特别离散的5个ID，就有3 + 1+5+5=11次IO。
它有一个阈值衡量，比如小于20w数据或小于总量一半，辅助索引失效，聚集索引进行一次三次IO之后，从叶子全表扫描。大于20W数据，利用辅助索引进行离散读。

mysql5.6+，引入MRR优化，解决离散读。立马辅助索引3次IO找ID，存入缓存后排序，再进入聚集索引。如果ID间隔不大，就进行叶子顺序扫描，如果ID间隔大，就二分查找。
顺序磁盘IO，比随机IO，可能提高10倍。

在计算机中，随机io的速度比顺序的io的速度慢很多，因为在一个柱面中，随机io必然会造成磁头的随机旋转，从而产生一定量的磁盘io，而顺序io则可降低到最低。

输入图片描述

输入图片描述

索引下推：先在辅助索引过滤掉所有能够过滤的，再一次回表。

Mysql锁

MVCC概念

一致性非锁定读，也叫多版本并发控制。
一句话，基于自己的回滚机制，为并发场景读的优化，为了达到读操作不需要被锁定的效果，加快mysql读取。
底层基于undo log回滚日志和一致性视图。
接下来，讲一下mysql不同隔离级别的MVCC表现。
从低到高分别是 read uncommitted读未提交，read committed 读已提交，repeatable read可重复读，串行化读。
默认是可重复读。
已三条线程来举例子，A，B修改线程，C读线程。

A，C同时对1条数据读写，C开启事务，A去修改，A在修改前生成一版快照，就是undo log，A修改完成，并且提交。目前又两个版本，B又对数据改，又对生成一版快照提交，此时有三个版本。
C线程再读，C线程访问哪部分？
在可重复读情况：
C读的是最原始版本。

在读已提交情况：
C读的是最新B提交的版本。

在读未提交情况：
A线程改，生成版本undo log 后，未提交。
C读的是未提交的A中间状态的数据。
这是无所谓版本控制了，是一种即时读取。
读取没有持久化的脏数据，是脏读。

在串行化情况：
理解为无论读写操作，都会锁表，操作是严格串行，无所谓版本控制。

读已提交，事务隔离了吗？
没有隔离，AB的修改，影响了C的读取，违背了事务隔离性。

那么，可重复读，C的读不会收到其他事务影响，实现事务的隔离性。

对于读未提交，没有持久化的中间状态的脏数据也能读取，这就是脏读。
现在我们几乎不会碰到脏读，除非，隔离级别改为读未提交。

对于读已提交，如果C一个事务中在A修改前后读取两次，发现两次读取结果不一样，这就是不可重复读。
如果A改为删除，C一个事务中在A删除前后的读取两次，发现第二次读不了，这就幻读。

我认为，不可重复和幻读本质相邻。

对于重复读，通过行锁解决不可重读，再配合间隙锁解决幻读。

对于串行化，用表锁，不会出现以上问题。

锁

一种是锁本条记录，一种是锁范围。
共享锁：事务共享读。
排他锁：限制一个事务改
多个事务能同时获得某一行的共享税，这叫锁兼容。
若事务需要获得某一行的排他锁，就要等待所有共享锁及排他锁的释放，这是锁不兼容。

InnoDB有三种行锁的算法：
Record Lock：普通的单个行锁，总会锁住索引记录，如果没有索引，就会锁隐式主。

Gap Lock 间隙锁，锁一个范围，不包括记录本身。
next-key lock：左右两边的GAP Lock + Record Lock，锁一个包括本身的范围，对于行查询都采用这种方式。

InnoDB的[next-key lock]本身的设计方案就是左开右闭的，例如

1
select * from t where c2 = 10 for update;

next-key lock则锁定的是 c2=10 这条记录本身，以及其[索引节点]上 c2=10 前面的那个gap。

但在RR隔离级别中，为了避免发生幻读，需要把所有可能插入 c2=10 这个值的位置都加上锁，所以对 c2 =10 后面的gap也会加锁。

当查询的索引含有唯一标识属时，Next key Lock 会降级为Record Lock，仅锁本身。

索引上的等值查询，未命中，所在的Net-Key Lock，退化为GAP Lock 。

日志系统

从插入场景开始讲，
mysql 插入会 记录持久化日志redolog，如果mysql非常高并发插入，会有什么性能瓶颈？
首先，面临最大的性能瓶颈，就是ID自增长。ID不重复，就是数据安全，说明有锁，就涉及到block阻塞。
5.1.22之前，所有插入有个auto increase locking，它是一个轻量级锁，innodb提供了是自增长计数器，它是保证计数器线程安全。插入sql提交前进行锁的释放，他不需要等事务提交后才锁释放，这算提高mysql性能：事务提交涉及到mysql持久化的问题，一旦持久化，就要消耗磁盘写入的时间，如果提交后才释放，就是要等待加上持久化时间。5.1.22之后，把id子增长，调整为三种模式。一种是原始的轻量级锁，二是默认策略，分两种情况：如果已知插入数据数量，比如10条，采用了更轻量的互斥量，如果自增长计数器是0，可知最终是10，只需要把1-10分配给10条数据，中间无需block阻塞。对于未知的插入数量，则采用原始的一插入一block的方式；三是无论如何都用互斥量，这会导致ID不连续，将威胁到mysql主从同步问题，不同节点ID不一样了。如果不考虑主从同步，可以用。

主从同步，有两个策略 ：一是rbr，row形式，记录每一行最后更变样子的语句，二是sbr，statement形式。如果用statement，会记录插入语句，那么在从服务器，执行未知插入数量的语句，互斥量形式插入，ID不连续，可能ID不一致。

在持久化redo log之前，会存入redobuff log 一个缓冲型日志，在commit时才会fsync磁盘刷新，flush同步到磁盘，然后最终到磁盘。

问题来了，1000万条每一commit一次造成刷盘，有性能问题，也会影响磁盘寿命。这个盘刷其实做了3种策略：一是每次提交刷盘，redo log立即刷盘，这是最好的，二是每一秒定期刷盘，是master thread的线程在做，相当于1000万条只有一次刷盘，有一定延迟，可能存在数据丢失；
三是从不刷新（交给os刷新），一时间内没有redo log，宕机就无法恢复。

redo log 记录的是内存页的实际数据，bin log 只是语句层面的数据，它不是用于恢复逻辑。

我们最好是在代码层面，将多条数据变成同一条语句进行插入，一次性提交。如果数据过大，可以分批batch每2000条插入。这样commit的量小，性能提升可达10倍以上，因为盘刷有性能瓶颈，磁盘1秒撑不住1千次刷。

binlog，用于主从数据同步，包括pit同步。

undo log，有两个功能，一个是回滚，有个undo段，理解为有几个阶段的回滚快照，以实现MVCC。

总结
MVCC结合undo来说，以及不同事物级别的表现，提到redo log，结合mysql插入性能，就要联系ID自增和提交刷盘。

其他的问题

Q：执行计划？

我们一一来介绍,并说明每个属性有哪些可选值,以及每个可选值的意思。

  • id: 表示查询中select操作表的顺序,按顺序从大到依次执行

  • select_type :
    该表示选择的类型,常见可选值有: SIMPLE(简单的), PRIMARY(最外层) ，SUBQUERY(子查询中的第一个select查询)

  • type :
    该属性表示访问类型,有很多种访问类型。
    最常见的其中包括以下几种: ALL(全表扫描), index(索引扫描),range(范围扫描),ref (非唯一索引扫描),eq_ref(唯一索引扫描,),(const)常数引用, 访问速度依次由慢到快。

    其中 : range(范围)常见与 between and …, 大于 and 小于这种情况。
    提示 : 慢SQL是否走索引,走了什么索引,也就可以通过该属性查看了。

  • table :
    表示输出行数据所在表

  • possible_keys :
    顾名思义,该属性给出了,该查询语句,可能走的索引,(如某些字段上索引的名字)这里提供的只是参考,而不是实际走的索引,也就导致会有possible_Keys不为null,key为空的现象。

  • key : 显示MySQL实际使用的索引,其中就包括主键索引(PRIMARY),或者自建索引的名字。

  • key_len : 表示索引所使用的字节数，

  • ref :
    连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值

  • rows :
    扫描行数,也就是说，需要扫描多少行,采能获取目标行数,一般情况下会大于返回行数。通常情况下,rows越小,效率越高, 大部分SQL优化，都是在减少这个值的大小。

    注意: 理想情况下扫描的行数与实际返回行数理论上是一致的,但这种情况及其少,如关联查询,扫描的行数就会比返回行数大大增加)

  • Extra 这个属性非常重要,该属性中包括执行SQL时的真实情况信息,如上面所属,使用到的是”using where”，表示使用where筛选得到的值,常用的有: “Using temporary”: 使用临时表 “using filesort”: 使用文件排序

Q：为什么要做主从同步？

  1. 读写分离，使数据库能支撑更大的并发。
  2. 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。
  3. 数据备份，保证数据的安全。

Q: 用过processlist吗？
show processlist 或 show full processlist 可以查看当前 MySQL 是否有压力，正在运行的 sql，有没有慢 SQL 正在执行。

Q: 讲一下MySQL架构？
MySQL主要分为 Server 层和存储引擎层：
Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都 在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模 块。
存储引擎： 主要负责数据的存储和读取。 server 层通过api与存储引擎进行通信。
Server 层基本组件
连接器：
当客户端连接 MySQL 时，server层会对其进行身份认证和权限校验。
查询缓存:
执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。
分析器:
没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先 看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。
优化器：
优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成 执行计划。
执行器：
首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限， 就会根据执行计划去调用引擎的接口，返回结果。

Q：大表怎么优化？

某个表有近千万数据，查询比较慢，如何优化？
当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下： 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内； 读写分离： 经典的数据库拆分方案，主库负责写，从库负责读； 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分。

Q:一条语句的执行过程？

查询语句：

  • 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

  • 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=’1’。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

  • 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

    a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18`。`

    b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

    那么优化器根据自己的优化[算法]进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

  • 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

  • 更新语句执行过程

  • 更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log(prepare 状态)、binlog、redo log(commit状态)

    更新语句：

    update user set name=’大彬’where id = 1;

    1. 先查询到 id 为1的记录，有缓存会使用缓存
    2. 拿到查询结果，将 name 更新为 大彬，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
    3. 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
    4. 更新完成。

Q ：重做日志刷新到磁盘的策略有几种，有什么优劣？

innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略。 n 该参数的默认值为1，表示事务提交时必须调用一次fsync操作。还可以设置该参数的值为0和2。 n 0表示事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，而在master thread中每1秒会进行 一次重做日志文件的fsync操作。 n 2表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。在这个设置下， 当MySQL数据库发生宕机而操作系统不发生宕机时，并不会导致事务的丢失。而当操作系统宕机时，重启数据库后 会丢失未从文件系统缓存刷新到重做日志文件那部分事务

举例： 插入50万条数。正确方法：innodb_flush_log_at_trx_commit = 1，将50万条数据在一个事务或者多个事务中分派提交，减少fsync次 数

一般是双1策略，innodb_flush_log_at_trx_commit = 1；sync_binlog = 1。

write+fsync 直接刷盘，write不直接刷盘，只写到os cache当中。

Q：purge 操作有什么作用 MySQL 篇 delete和update操作可能并不直接删除原有的数据。

delete和update操作可能并不直接删除原有的数据。

例如， DELETE FROM t WHERE a=1; 表t上列a有聚集索引，列b上有辅助索引。对于上述的delete操作，仅是将主键列等于1的记录delete flag设 置为1，记录并没有被删除，即记录还是存在于B+树中。其次，对辅助索引上a等于1，b等于1的记录同样 没有做任何处理。而真正删除这行记录的操作其实被“延时”了，最终在purge操作中完成。 purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事 务提交时立即进行处理。这时其他事物可能正在引用这行，故InnoDB存储引擎需要保存记录之前的版本。 而是否可以删除该条记录通过purge来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真 正的delete操作。可见，purge操作是清理之前的delete和update操作，将上述操作“最终”完成。而实际执行 的操作为delete操作，清理之前行记录的版本。

Q：说说主从复制的工作原理

1）主服务器（master）把数据更改记录到二进制日志（binlog）中。

2）从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。

3）从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性

复制的工作原理并不复杂，其 实就是一个完全备份加上二进 制日志备份的还原。不同的是 这个二进制日志的还原操作基 本上实时在进行中。这里特别 需要注意的是，复制不是完全 实时地进行同步，而是异步实 时。这中间存在主从服务器之 间的执行延时，如果主服务器 的压力很大，则可能导致主从 服务器延时较大。

从服务器有2个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL 线程，复制执行中继日志。

Q：主从复制bin log 日志有几种记录方式

变更操作将根据选定的格式类型写入 binlog 文 件，目前支持三种 format：

statement-based Replication（SBR） ：master将SQL statements语句写入binlog，slave 也将 statements 复制到本地执行；简单 而言，就是在 master 上执行的 SQL 变更语句，也同样在 slaves 上执行。SBR 模式是 MySQL 最早支持的类型，也是 Replication 默 认类型。

row-based Replication（RBR）： master将每行数据的变更信息写入binlog，每条 binlog 信息表示一行（row）数据的变更内容， 对于 slaves 而言将会复制 binlog 信息，然后单条或者批量执行变更操作；

mix-format Replication：混合模式，在这种模式下，master将根据根据存储引擎、变更操作类型等，从SBR、RBR中来选择更合 适的日志格式，默认为 SBR；具体选择那种格式，这取决于变更操作发生的存储引擎、statement 的类型以及特征，优先选择 “数 据一致性” 最好的方式（RBR），然后才兼顾性能，比如 statement 中含有 “不确定性” 方法或者批量变更，那么将选择 RBR 方式， 其他的将选择 SBR 以减少 binlog 的大小。我们建议使用 mix 方式。

SBR 和 RBR 都有各自的优缺点，对于大部分用而言，mix 方式在兼顾数据完整性和性能方面是最佳的选择

Q： 主从复制有几种方式？

异步复制

MySQL 默认的复制策 略，Master 处理事务过 程中，将其写入 Binlog 就会通知 Dump thread 线程处理，然后完成事 务的提交，不会关心是 否成功发送到任意一个 slave 中。

半同步复制

Master处理事务过程中，提交完事务后，必须 等至少一个 Slave 将收到的 binlog 写入 relay log 返回 ack 才能继续执行处理用户的事务。

增强半同步复制

强半同步和半同步不同是，等待 ACK 时 间不同 rpl_semi_sync_master_wait_point = AFTER_SYNC（唯一区别） 半同步的问题是因为等待 ACK 的点是 Commit 之后，此时 Master 已经完成数据 变更，用户已经可以看到最新数据，当 Binlog 还未同步到 Slave 时，发生主从切 换，那么此时从库是没有这个最新数据的， 用户又看到老数据。 增强半同步将等待 ACK 的点放在提交 Commit 之前，此时数据还未被提交，外 界看不到数据变更，此时如果发送主从切 换，新库依然还是老数据，不存在数据不 一致的问题。

Q：Innodb 和 mylsam 存储引擎的区别

  1. 是否支持行级锁 : MyISAM 只有表级锁 (table-level locking)，而 InnoDB 支持行级锁 (rowlevel locking) 和表级锁 , 默认为行级锁。

  2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性 , 其执 行速度比 InnoDB 类型更快，但是不提供事务支持。但是 InnoDB 提供事务支持事务，外部 键等高级数据库功能。具有事务 (commit)、回滚 (rollback) 和崩溃修复能力 (crash recovery capabilities) 的事务安全 (transaction-safe (ACID compliant)) 型表。

  3. 是否支持外键： MyISAM 不支持，而 InnoDB 支持。
  4. 是否支持 MVCC：仅 InnoDB 支持。应对高并发事务 , MVCC 比单纯的加锁更高效 ;MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作 ;MVCC 可以使用乐观 (optimistic) 锁和悲观 (pessimistic) 锁来实现 ; 各数据库中 MVCC 实现并不统一

来源：河北王校长

手动用begin开启事务，然后执行update语句，再然后执行commit语句，那上面的update更新流程之前 哪些是update语句执行之后做的，哪些是commit语句执行之后做的?

事实上，redo log在内存中有一个redo log buffer,binlog 也有一个binlog cache.所以在手动开启的事务中，你执行sql语句，其实是写到redo log buffer和binlog cache中去的(肯定不可能是直接写磁盘日志，一个是性能差一个是回滚的时候不可能去回滚磁盘日志吧)，然后当你执行commit的时候，首先要将redo log的提交状态由prepare改为commit状态，然后就要把binlog cache刷新到binlog日志(可能也只是flush到操作系统的page cache，这个就看你的mysql配置)，redo log buffer刷新到redo log 日志(刷新时机也是可以配置的一共有三种)。 如果你回滚的话，就只用把binlog cache和redo log buffer中的数据清除就行了。

在update过程中，mysql突然宕机，会发生什么情况?

  1. 如果redolog写入了，处于prepare状态，binlog还没写入，那么宕机重启后，redolog中的这个事务就直接回滚了。
  2. 如果redolog写入了，binlog也写入了，但redolog还没有更新为commit状态，那么宕机重启以后，mysql会去检查对应事务在binlog中是否完整。如果是，就提交事务；如果不是，就回滚事务。 (redolog处于prepare状态，binlog完整启动时就提交事务，为啥要这么设计? 主要是因为binlog写入了，那么就会被从库或者用这个binlog恢复出来的库使用，为了数据一致性就采用了这个策略)，redo log和binlog是通过xid这个字段关联起来的。

作者：暂停丶算不算放弃
链接：https://www.nowcoder.com/discuss/810539?source_id=profile_create_nctrack&channel=-1
来源：牛客网

大事务有什么问题？如何排查定位大事务？使用事务时，哪些需要注意来避免大事务？

并发下，DB连接池容易被撑爆；锁定太多的数据，造成大量的阻塞和锁超时
执行时间长，容易造成主从延迟；undo log膨胀，回滚所需要的时间比较长。这是大事务的影响。

排查是取binlog，用shell脚本去获取和筛选event的事务信息，即可找到大事务

事务使用时，避免一次处理太多数据，避免不必要的查询，还有， 在事务里做避免阻塞操作，比如第三方http，rpc调用，mq发送，尽量放到事务之外。

Innodb体系结构

Buffer Pool
缓冲池，简称BP。BP以Page页为单位，默认大小16K，BP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率。

Page管理机制，Page根据状态可以分为三种类型：
free page ： 空闲page，未被使用
clean page：被使用page，数据没有被修改过
dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致

针对上述三种page类型，InnoDB通过三种链表结构来维护和管理：
free list ：表示空闲缓冲区，管理free page
flush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作。
lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少数据，占37%。
改进型LRU算法维护

普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰。

改性LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。

每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到lru list中。没有空闲页，就会根据LRU算法淘汰lru list默认的页，将内存空间释放分配给新的页。

Buffer Pool配置参数

建议：将innodb_buffer_pool_size设置为总内存大小的60%-80%，innodb_buffer_pool_instances可以设置为多个，这样可以避免缓存争夺。

Change Buffer
Change Buffer：写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并恢复到BP中。

ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。参数innodb_change_buffer_max_size; 【命令set global innodb_change_buffer_max_size=30;】

当更新一条记录时，该记录在BufferPool存在，直接在BufferPool修改，一次内存操作。如果该记录在BufferPool不存在（没有命中），会直接在ChangeBuffer进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进性磁盘读取，然后再从ChangeBuffer中读取信息合并，最终载入BufferPool中。

写缓冲区，仅适用于非唯一普通索引页，为什么？

如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在ChangeBuffer操作。

Adaptive Hash Index
Adaptive Hash Index：自适应哈希索引，用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

Log Buffer
Log Buffer：日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。【innodb_log_buffer_size 默认大小为8M】

LogBuffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。LogBuffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大，减少磁盘IO频率。

innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1。

2、InnoDB磁盘结构

表空间（Tablespaces）
用于存储表结构和数据。表空间又分为系统表空间、独立表空间、通用表空间、临时表空间、Undo表空间等多种类型；

系统表空间（The System Tablespace）
包含InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。

独立表空间（File-Per-Table Tablespaces）
默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当innodb_file_per_table选项开启时，表将被创建于独立表空间中。否则，innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。表空间的表文件支持动态（dynamic）和压缩（commpressed）行格式。【默认是存储在独立表空间中的】

通用表空间（General Tablespaces）
通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。

撤销表空间（Undo Tablespaces）
撤销表空间由一个或多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。

临时表空间（Temporary Tablespaces）
分为session temporary tablespaces 和global temporary tablespace两种。1存储的是用户创建的临时表和磁盘内部的临时表。2储存用户临时表的回滚段（rollback segments ）。mysql服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。

数据字典（InnoDB Data Dictionary）
InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。元数据物理上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在一定程度上与InnoDB表元数据文件（.frm文件）中存储的信息重叠。

双写缓冲区（Doublewrite Buffer）
位于系统表空间，是一个存储区域。在BufferPage的page页刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区。如果在page页写入过程中出现操作系统、存储子系统或mysqld进程崩溃，InnoDB可以在崩溃恢复期间从Doublewrite 缓冲区中找到页面的一个好备份。在大多数默认情况下启用双写缓冲区，要禁用Doublewrite 缓冲区，可以将innodb_doublewrite设置为0。使用Doublewrite 缓冲区时建议将innodb_flush_method设置为O_DIRECT。出现不能修改的配置文件时，直接在my.ini配置文件中进行修改。

重做日志（Redo Log）
重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。MySQL以循环方式写入重做日志文件，记录InnoDB中所有对Buffer Pool修改的日志。当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数据更新到数据文件。读写事务在执行的过程中，都会不断的产生redo log。默认情况下，重做日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件物理表示。

撤销日志（Undo Logs）
撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤消日志属于逻辑日志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中。

数据丢失。那么，有什么解决方法避免数据损坏？

InnoDB 的页和操作系统的页大小不一致，InnoDB 页大小一般为 16K，操作系统页大小为 4K，InnoDB 的页写入到磁盘时，一个页需要分 4 次写。
如果正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了 4K，就宕机了，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。那么，有什么解决方法避免数据损坏？

redo log 是物理+逻辑日志。物理日志部分，可以不依赖内存页进行恢复，逻辑日志部分，需要借助内存页进行恢复，比如插入数据，需要借助原来的内存页的page header行记录进行加一操作，如果原来的内存页损坏，则无法恢复。一句话，就是这个页失效，想只依靠redolog 恢复，是不可行的。

redo log日志在commit的时候已经完成了，数据脏页的持久化还需要定期执行。

数据页，也就是我们的b+树的叶子，会存储在buffer pool内存上。修改数据时，先查buffer pool内存，如果有对应数据，直接在更新在内存；如果没有在内存，则两种可能，一个是从磁盘IO拉取，再更新内存；另一个是进一步的优化，直接更新在change buffer内存，没有IO，等下一次用时才IO查出来完成合并。更新数据都在内存中，内存与磁盘不一致的页，就是脏页，会定期持久化到磁盘。

change buffer 以前叫insert buffer，对插入数据缓存，现在扩展为不仅是插入，增删改都有。但是，不是所有增删改适用，要求是非唯一索引的增删改，也就是说为数据量多的增删改操作服务。

答案：
数据恢复一般确实是靠redo log，对于数据页失效问题，是在持久化时已经避免，具体是，数据页先持久化到磁盘中的double write buffer 作为一个副本，再真正持久化到磁盘的独立表空间优化。这就是数据双写，保证数据在磁盘有一份完整的副本，以便恢复。