mysql

基础篇

MySQL 执行流程

MySQL 的架构共分为两层：Server 层和存储引擎层，

Server 层负责建立连接、分析和执行 SQL。
存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎。InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

连接器：建立连接：

MySQL 是基于 TCP ，使用三次握手完成数据库连接，超过8 小时（28880秒）就会自动断开。（使用长连接）
长连接占用内存的问题的解决方法：
- 定期断开长连接。
- 客户端主动重置连接。
验证用户和密码，读取用户权限

查询缓存：如果是查询指令（select），则先去查询缓存里查找缓存数据。【注】查询缓存是以 key-value 形式保存在内存中的。但是如果该表有更新操作，那么该表在查询缓存的缓存就会被删除，所以查询缓存命中率很低，在MySQL 8.0 版本查询缓存被删除。
【注】这里说的查询缓存是 server 层的，也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool。

解析器解析 SQL

词法分析：识别关键字和非关键字，
语法分析：判断是否满足 MySQL 语法，如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。【问题】语法树是什么？

执行SQL：

预处理器：检查表或者字段是否存在；
优化器：优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引，如执行覆盖索引。

执行器：执行器与存储引擎以记录为单位进行交互。

主键索引查询：select * from product where id = 1;
- 优化器选择的访问类型为 const，故执行器就会调用const对应InnoDB 引擎索引查询的接口，通过次接口把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录。由于是const，所以执行一次就退出。
全表扫描：select * from product where name = 'iphone';
- 优化器选择的访问类型为 all，故执行器就会调用const对应InnoDB 引擎索引查询的接口，通过次接口把条件 name = 'iphone'交给存储引擎，让存储引擎定位符合条件的第一条记录。执行器获取到一条name = 'iphone'的记录，就将此记录发送给客户端，即一条一条发送给客户端，客户端全部收到以后才会显示出来。一条一条遍历，直到遍历完所有记录。
  一条一条返回给客户端时为了减少网络负载，提高响应速度。
索引下推：索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。如select * from t_user where age > 20 and reward = 100000;
- 不进行索引下推：会将age > 20的记录进行回表操作，然后将回表得到的记录返回给 Server 层。
  Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；
  【注】这里是一条一条返回给Server 层的。
- 进行索引下推：获取age > 20的记录，先不执行回表操作，而是先判断一下reward 是否等于 100000，如果条件不成立，则直接跳过该二级索引。如果条件成立就执行回表，然后将回表得到的记录返回给 Server 层。
  Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。

可以看到，使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。

MySQL 一行记录的存储结构

每创建一个 database（数据库）都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。如数据库my_test：

ls /var/lib/mysql/my_test
db.opt  // 用来存储当前数据库的默认字符集和字符校验规则。
t_order.frm  // t_order表的结构信息
t_order.ibd  // 表中数据和索引等信息，这个文件也称为独占表空间文件。

表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构大致如下图：

一行为一个记录
InnoDB 的数据是按「页」为单位来读写的（16KB）
在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配（1MB）。这是为了让B+树的叶子节点组成的链表的相邻的页的物理位置也相邻，就能使用顺序 I/O 了。
段（segment）：段一般分为数据段、索引段和回滚段等。
- 索引段：存放 B + 树的非叶子节点的区的集合；
- 数据段：存放 B + 树的叶子节点的区的集合；
- 回滚段：存放的是回滚数据的区的集合，之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

InnoDB 的行格式： 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。Redundant基本没人用，Compact、Dynamic和 Compressed 都是紧凑的行格式，Dynamic 和 Compressed都是在Compact 进行了一点改进。

COMPACT 行格式：
记录的额外信息：

变长字段长度列表：存放可变长类型的数据的大小，数据占用的字节数会按照列的顺序逆序存放在变长字段长度列表中。
- 逆序存放的原因：1.向左读就是记录头信息，向右读就是真实数据 2. 使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。
- 「变长字段长度」需要用多少字节表示：
  - 条件一：如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；
  - 条件二：如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；
NULL 值列表：每一列对应一位，为1代表为null，为0代表不为null。另外NULL 值列表必须是整数个字节（8bit），不足的，高位补零。
当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。
记录头信息：
- delete_mask：为1，代表此记录被删除。
- next_record：下一条记录的位置，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
- record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录的真实数据：

row_id：如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。非必需的。
trx_id：表示这个数据是由哪个事务生成的。必需的。
roll_pointer：这条记录上一个版本的指针

varchar(n) 中 n 最大取值：一行数据的最大字节数是 65535，65535包含了变长字段长度列表」、「NULL 值列表」和实际的数据。
ascii：一个字符占一个字节，UTF-8一个字符占3个字节。

行溢出：一页不够存放当前记录时，溢出的数据会存放到「溢出页」中。行中存放着指向溢出页的指针。Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于Compressed 和 Dynamic的记录中只存放指向溢出页的指针，Compact中存放了部分数据和指向溢出页的指针。

索引篇

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，就是可以唯一标识当前行的key。

所谓的存储引擎，说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL 存储引擎有 MyISAM 、InnoDB、Memory，其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。

按数据结构分类

从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。InnoDB、MyISAM 和 Memory 分别支持的索引类型。

InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

InnoDB的聚簇索引（主键索引）：

有主键，则使用主键作为聚簇索引的索引键（key）
没主键，则用不包含 NULL 值且值都是唯一的列。
上面两个都没有，将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

商品表

主键索引的 B+Tree 如图所示：

我们可以把读取一个节点当作一次磁盘 I/O 操作。B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

二级索引：

首先会查找二级索引，然后找到对应的id，根据找到的id在主键索引的 B+Tree 查找到对应的叶子节点（第二次查找称为回表）。如果我们查找的目标为id，那么就不需要在主键索引的 B+Tree 查找叶子节点，这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」。

为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构：

B+Tree vs BTree相比：1.单个节点更小，一次磁盘IO查询到的节点更多；2.可以在叶子节点执行基于范围的顺序查找
B+Tree vs 二叉树：二叉树只有两个儿子，所以磁盘 I/O 次数要更多
B+Tree vs Hash： Hash在做等值查询的时候为O(1)， Hash 表不适合做范围查询

其他索引分类

按字段特性分类：主键索引、唯一索引（要求值唯一）、普通索引、前缀索引（使用前几个字符作为索引，而不是整个字段）。

按字段个数分类——联合索引：多个字段组合成一个索引。假设联合索引为(product_no, name)，那么联合索引查询的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。
【注】左匹配原则：只有在product_no相同时，才会查找name 字段。如果直接查找name是不行的，这就是最左匹配原则，即匹配完product_no，如果相同，才匹配name。
【注】并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询。

如select * from t_table where a > 1 and b = 2的b字段就不能使用联合索引进行索引查询，因为b只有在a相等时才会使用联合索引进行索引查询，所以b=2只能通过链表一个一个找。
select * from t_table where a >= 1 and b = 2，当二级索引记录的 a 字段值为 1 时，可以通过 b = 2 条件减少需要扫描的二级索引记录范围
SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2， a 和 b 字段都用到了联合索引进行索引查询。
SELECT * FROM t_user WHERE name like 'j%' and age = 22，联合索引（name, age）哪一个字段用到了联合索引的 B+Tree？：name和age字段都用到了联合索引进行索引查询。

索引下推ICP（Index Condition Pushdown）：

MySQL 5.6 之前，select * from table where a > 1 and b = 2中只有 a 字段能用到联合索引，然后会得到n条记录，然后每次取出一条记录，去主键索引上寻找完整的记录（回表）。然后存储引擎把得到的记录交给Server层去检测该记录是否满足WHERE条件。
MySQL 5.6 引入的索引下推优化：判断得到的n条记录中有哪些记录为b = 2，然后再进行回表。这样就减少了再主键索引上的查找次数，即减少了回表次数，因为在主键索引上查找需要io操作，所以也减少IO次数。这里直接在存储引擎层中判断WHERE条件是否满足，条件不满足，则处理下一行索引记录。这样就减少了回表次数和在Server层检测该记录是否满足WHERE条件的比较次数。

这篇文章索引下推说得很好：ICP把本来由Server层做的索引条件检查下推给存储引擎层来做，以降低回表和访问存储引擎的次数，提高查询效率。

索引区分度：

建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。区分度就是某个字段 column 不同值的个数「除以」表的总行数，即字段的种数越多，区分度就越大，就要排在前面。
MySQL有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（如"30%"）很高的时候，它一般会忽略索引，进行全表扫描。

联合索引进行排序：select * from order where status = 1 order by create_time asc，要利用索引的有序性，在 status 和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。
【注】上面查询语句的含义：返回满足条件的所有订单，并按照创建时间从早到晚的顺序进行排序。
【注】文件排序可能发生在以下情况下：

结果集较大：如果查询涉及大量数据，无法完全放入内存中，则MySQL会使用临时文件进行排序。
内存不足：如果系统内存不足以容纳整个排序操作所需的数据，MySQL将使用临时文件进行排序。
排序字段较大：如果排序字段的大小超出了MySQL内存排序缓冲区的限制，MySQL可能会使用临时文件进行排序。

索引的缺点：占用物理空间、创建索引和维护索引耗时，如增删改索引时需要调整B+树中索引有序性。

创建索引的时机：字段具有唯一性、经常用于 WHERE 查询条件的字段、经常用于 GROUP BY 和 ORDER BY 的字段。
不创建索引：1.WHERE 条件，GROUP BY，ORDER BY 里用不到的字段 2.字段中存在大量重复数据 3.数据太少 4.经常更新的字段，防止维护代价大，影响数据库性能

常见优化索引的方法：

前缀索引
覆盖索引：我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。
主键索引最好是自增的
索引最好设置为 NOT NULL：1.索引统计变复杂 2.NULL 值列表占用空间
避免写出索引失效的查询语句

防止索引失效的情况：

当我们使用左或者左右模糊匹配的时候，也就是 like %xx（以xx为后缀的字符串）或者 like %xx%（包含xx的字符串）这两种方式都会造成索引失效；
- 如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。这是因为优化器认为走二级索引就行了。（from：MySQL 使用 like “%x“，索引不一定会失效）
- 我们都知道联合索引要遵循最左匹配才能走索引，但是如果数据库表中的字段都是索引的话，即使查询过程中，没有遵循最左匹配原则，也是走全扫描二级索引树(type=index)
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效，如select * from t_user where length(name)=6;。MySQL 8.0 开始，可以针对函数计算后的值建立一个索引，如alter table t_user add key idx_name_length ((length(name)));对 length(name) 的计算结果建立一个名为 idx_name_length 的索引。alter table t_user add key idx_name_length ((length(name)))和 alter table t_user add index idx_name_length ((length(name))) 是等效的，
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中使用or，那么or的两边只要有条件列不是索引列，就会进行全表扫描。

对于执行计划，参数有，即explain中结果的参数：

possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 描述了找到所需数据时使用的扫描方式是什么，即mysql执行查询的方式称为访问方法或者访问类型，也有人叫做访问级别，常见扫描类型的执行效率从低到高的顺序为：（参考链接和小林coding）
- All（全表扫描）：对应InnoDB来说就是直接扫描全部的聚簇索引记录。
- index（全索引扫描）：扫描全部二级索引记录的访问方法称为index访问方法，比如由于建立了key1作为普通二级索引，所以select key1 from demo_table;可以直接从二级索引列中找出所有的key1。
- range（索引范围扫描）：一般在 where 子句中使用 < 、>、in、between 等关键词
- ref（非唯一索引扫描）：二级索引列是非唯一索引，或者是唯一索引的非唯一性前缀，那么比较二级索引列与常数是否相等，可能返回数据返回可能是多条。可能还需要对这些数据进行回表操作。
- ref_or_null：如果ref中是找是否为等于'abc'的行，那么ref_or_null就是找为'abc'或null的行。
- eq_ref（唯一索引扫描）：用在多表联查中，比如SELECT * FROM tableA JOIN tableB ON tableA.user_id = tableB.user_id;中在tableA和tableB中找到user_id相等的行，将其拼接起来进行返回。
- const：比较主键或者唯一二级索引列与常数是否相等。

extra：
- Using filesort：无法利用索引完成排序操作的时候，发生了文件排序。
- Using temporary：排序时，可能需要临时表保存中间结果，order by 和group by经常发生排序。
- Using index：发生了覆盖索引

从数据页的角度看 B+ 树

InnoDB 的数据是按「页」为单位（16kB）来读写的

数据页包括七个部分：

页目录与记录的关系如下图：

InnoDB 里的 B+ 树中的每个节点都是一个数据页：

只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。
所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；

上图中，在页中我们可以通过槽来查找组，然后在分组内查找元素，然后定位到下一页，直到找到对应元素。

聚簇索引与二级索引：

聚簇索引的叶子节点存放的是所有完整的用户记录
二级索引的叶子节点存放的是主键值，而不是实际数据。

采用 B+ 树作为索引的原因：

MySQL的数据（索引+记录）是保存在磁盘看，所以适合 MySQL 索引的数据结构为：

少的磁盘的 I/O
高效进行等值查找和范围查找

二叉查找树由于存在退化成链表的可能性，会使得查询操作的时间复杂度从 O(logn) 升为 O(n)。不能范围查询

自平衡二叉树，比如红黑树、平衡二叉查找树，随着插入的元素增多，而导致树的高度变高，读取一个节点就需要一次磁盘IO。

B树与B+树：

B 树的每个节点都包含数据（索引+记录），更多的磁盘 I/O 。
B+ 树：叶子节点包含所有实际数据（索引+记录）、叶子节点之间构成一个有序双向链表、父节点元素为子节点中的最大（小）值
B+ 树非叶子节点存放的索引数更多。
B+ 树插入和删除效率比B树高（没看懂）
B+ 树支持范围查找

B+树一般只有三四层（包括叶子节点），因为一个页为16kB，可以存储1000左右的索引，第二层就有1000*1000，即一百万个索引，假设第三层的叶子节点中只能存储15条记录，那么三层B+树对应的记录就有1000*1000*15，即一千万五百万条记录。
B+树太多层也会导致磁盘IO的次数过多，所以MySQL 单表最好不要超过 2000W。

事务篇

事务隔离级别

四大特性：事务是在 MySQL 引擎层实现的，我们常见的 InnoDB 引擎是支持事务的，事务的四大特性是原子性、一致性（没看懂？）、隔离性（事务的执行不会互相干扰）、持久性（修改是持久的，系统故障也不会丢失），我们这次主要讲的是隔离性。
【注】事务：多个操作组成的原子性操作

持久性是通过 redo log （重做日志）来保证的，即重新执行一遍日志
原子性是通过 undo log（回滚日志）来保证的；等事务中所有数据库操作执行完成后，才提交事务，事务在执行过程中发生错误，会被回滚到事务开始前的状态。
隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

并行事务会引发的问题：脏读 > 不可重复读 > 幻读（严重程度）

脏读：读到其他事务未提交的修改数据；
不可重复读：前后读取的数据不一致；
幻读：当同一个查询在不同的时间产生不同的结果集，如前后读取的记录数量不一致。不可重复读针对的是一个数据的内部，幻读针对的是数据的数量。

四种事务隔离级别（级别越来越高），隔离级别越高，性能效率就越低：

读未提交（read uncommitted），未提交的事务，可以被读；可能发生脏读、不可重复读和幻读现象；
读提交（read committed），已提交的事务，才能被读；可能发生不可重复读和幻读现象
可重复读（repeatable read），事务的前后两次读取是一样的；可能发生幻读现象
串行化（serializable ），会对记录加上读写锁，有人在读a时，其他人就不能写a，有人在写a，其他人应该也不能读a（我猜的）；脏读、不可重复读和幻读现象都不可能会发生。

「读提交」和「可重复读」的实现：
-「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，

而「可重复读」隔离级别是「启动事务时」生成一个 Read View（快照），然后整个事务期间都在用这个 Read View（快照）。

Read View 中四个字段：通过Read View可以知道创建当前是事务时哪些事务已提交、未提交、未开始

m_ids：创建 Read View，启动了但还没提交的事务的id组成的列表。启动了但还没提交的事务称为“活跃事务”。
min_trx_id：m_ids 的最小值。
max_trx_id：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

聚簇索引记录中的两个隐藏列：使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：

trx_id：改动此记录的事务id为trx_id
roll_pointer：指向修改前的历史记录。

【问题】聚簇索引是什么？

trx_id与Read View的关系：

一个事务去访问记录的时候，会比较记录的trx_id和事务的Read View的四个字段判断记录是否可见：

trx_id < min_trx_id：代表当前记录已经提交，故对当前事务可见
trx_id > max_trx_id：代表记录是在创建 Read View 后才启动的事务生成的，故对当前事务不可见
min_trx_id < trx_id < max_trx_id：
- trx_id 在 m_ids：表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
- trx_id 不在 m_ids：表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

可重复读：使用Read View和trx_id实现可重复读。可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

上图中，由于事务 A 修改了该记录，以前的记录就变成旧版本记录了，使用链表将旧记录串起来。
事务B法发现第二次读时，发现记录min_trx_id < trx_id < max_trx_id，这时事务 B 沿着 undo log 链条往下第一个小于min_trx_id的旧记录。这样就保证了可重复读。
即使事务A提交了事务，由于隔离级别，事务B是不知道，所以事务B读取的还是旧记录。

读提交：使用Read View和trx_id实现读提交。读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。即读提交不保证可重复读。

在事务A提交以后，事务B再次读取数据时，会重新创建 Read View，新的Read View中认为事务A已经提交了，所以事务A已提交的修改就会被事务B看到。
在可重复读中，事务B再次读取数据，并不会重新创建Read View，所以认为事务A是未提交的，所以不会读取到事务A的修改。

MySQL 解决幻读

MySQL 解决幻读：MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。即使事务中途有其他事务插入了一条数据，select也不会查询到此数据。【不读取新插入的，解决幻读】
具体为：在执行第一个查询语句后，会创建一个 Read View。由于后续插入的数据的trx_id > max_trx_id，所以对当前事务不可见，从而解决了幻读。
针对当前读（select ... for update、update、delete 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入。【阻止插入，解决幻读】
【注】MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete
不解决：
- 1.对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
- 2.对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。
  所以 MySQL Innodb 中的 MVCC和锁不能完全避免幻读现象，为了防止以上两个问题：开启事务之后，尽量马上执行 select ... for update 这类当前读的语句，即加上锁，尽量防止插入等操作。

从上面的描述可以看出，事务的隔离级别是针对普通的select的，而不针对select ... for update、update、delete 等语句，为了解决幻读问题，下面引入了锁。当然锁好像不止用来解决幻读问题。

next-key lock：

事务 A 执行了这面这条锁定读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合）。
然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入等待状态，直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。

锁

锁的种类

MySQL 的锁：全局锁、表级锁和行锁

全局锁:flush tables with read lock，整个数据库就处于只读状态了。全局锁主要应用于做全库逻辑备份。InnoDB可以不使用全局锁来进行全库逻辑备份，InnoDB在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。
flush tables with read lock

【注】读锁（共享锁）：多个线程可以同时进行读，但是不能有线程获取写锁进行写操作；写锁（独占锁）：只能有一个线程进行写操作。

表级锁

表锁：读锁（共享锁）和写锁（独占锁）。加了「共享表锁」，则本线程和其他线程都不能进行写操作。
lock tables t_student read/write;
元数据锁（MDL）：对数据库表进行操作时，会自动给这个表加上 MDL。MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。
- 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
- 对一张表做结构变更操作的时候，加的是 MDL 写锁；
  说明：
- 当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。
- 反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。
  问题：MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。当线程 A执行了事务并执行了select，此时线程 C 想要修改表字段，由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞。那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞。
  解决：为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。
意向锁：意向锁用于快速判断表里是否有记录被加锁
- 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」（行级锁）之前，需要先在表级别加上一个「意向共享锁」；
- 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」（行级锁）之前，需要先在表级别加上一个「意向独占锁」；
- 当执行插入、更新、删除操作，会自动先对表加上「意向独占锁」，然后对该记录加独占锁。而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

select ... lock in share mode; // 此语句会执行两个锁，先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... for update; // 先表上加上意向独占锁，然后对读取的记录加独占锁

AUTO-INC 锁：主键通常都会设置成自增的，不需要显示自定义主键值，数据库会自动给主键赋值递增的值，这个自增的过程中需要AUTO-INC 锁。AUTO-INC锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。
InnoDB 存储引擎提供了一种轻量级的锁来实现自增：给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。当一个条语句插入大量的数据（类似 insert … select ）时，需要使用AUTO-INC 锁，否则使用轻量级的锁来实现自增。

行级锁：共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。当事务提交了，锁就会被释放。行级锁的类型主要有三类：

Record Lock，记录锁，也就是仅仅把一条记录锁上；
- 共享锁(读锁S锁)：一个事务lock in share mode，其他事务只能读数据不能更新数据
- 排他锁(独占锁、写锁X锁)：一个事务for update加上排他锁，其他事务不能对相关数据加其他锁
Gap Lock，间隙锁，锁定一个范围，不能再此范围内执行插入数据等操作；只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象，即防止在范围内插入，从而防止幻读。
【注】间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，即不存在互斥，因为间隙锁的目的是防止插入幻影记录而提出的。
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定范围的右边界记录。next-key lock 即能保护右边界记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。
说明：next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。
插入意向锁：一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁。如果有，则生成一个插入意向锁，等待间隙锁释放时再进行插入。
插入意向锁不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

MySQL 是怎么加锁的

在读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上。我猜这是因为读已提交隔离级别不保证避免幻读。
在可重复读隔离级别下为了避免幻读，所以行级锁的种类除了有记录锁，还有间隙锁，所以行级锁

加行级锁的SQL 语句

普通的 select 语句是不会对记录加锁的（除了串行化隔离级别），因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。

如果要在查询时对记录加行级锁，可以使用下面这两个方式，这两种查询会加锁的语句称为锁定读。

//先在表上加上意向共享锁，然后对读取的记录加共享锁(S型锁)
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁(X型锁)
select ... for update;

上面两条语句主要是为了在事务开始之前对记录进行加锁操作，从而防止幻读。
除了上面这两条锁定读语句会加行级锁之外，update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。

//对操作的记录加独占锁(X型锁)
update table .... where id = 1;

//对操作的记录加独占锁(X型锁)
delete from table where id = 1;

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。

MySQL 是怎么加行级锁的？

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。也就是说mysql用的都是next-key lock，但是next-key lock会自动根据当前的执行命令来进行退化操作，比如退化成记录锁或间隙锁。
退化原则：在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。
【注】用二级索引进行锁定读查询的时候，除了会对二级索引项加行级锁，而且还会对查询到的记录的主键索引项上加锁。

唯一索引等值查询

当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。因为记录锁就可以保证了无法插入和删除该id的记录，从而保证不幻读。
当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。因为「间隙锁」就可以保证了无法插入该id的记录，从而保证不幻读。比如id=2不存在，那么可能会产生(1,5)的间隙锁去防止id=2的记录的插入。

唯一索引范围查询

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。
- 对于大于，比如select * from user where id > 15 for update;：每扫描到一个大于15的数，就加上一个next-key 锁，比如(15,20]、(20,30]、(30,+∞]。
- 针对「大于等于」，比如select * from user where id >= 15 for update;：如果遇到id = 15，就加上一个记录锁（next-key 锁会退化成记录锁）。由于是范围查找，就会继续往后找存在的记录，每扫描到一个大于15的数，就加上一个next-key 锁，比如(15,20]、(20,30]、(30,+∞]。
  【注】在 Innodb 存储引擎中，会用一个特殊的记录来标识最后一条记录。比如id=30为最后一个元素，那么遇到此特殊记录时，就会产生next-key 锁(30,+∞]。
  【问题】如果此时如果id=15的记录不存在，那么会出现什么情况？
  答：那就都是next-key lock，没有记录锁。
情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：
- 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，最后一个锁，肯定是间隙锁。如select * from user where id < 6 for update;，可能会获取到如下锁：(-∞, 1]、(1, 5]、(5, 10)
- 当条件值的记录在表中，如果是「小于」条件的范围查询，最后一个锁为间隙锁；如果「小于等于」条件的范围查询，最后一个next-key 锁不会退化成间隙锁。

非唯一索引等值查询：

当查询的记录「存在」时，
- 二级索引中符合条件的记录加上next-key 锁，第一个不符合条件的二级索引记录使用间隙锁，在符合查询条件的记录的主键索引上加记录锁。
  如果主键索引不加记录锁，那么其他事务可能会更新此记录。主键索引加记录锁就可以防止幻读，所以就加了记录锁，而不是其他锁。
- 假设得到锁为(21,22]和(22,39)，那么插入age=21或age=39时要判断id，从而判断插入的记录是否处于锁内，处于锁内则不能插入，不处于锁内则可以插入。
当查询的记录「不存在」时，
- 扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，此时对主键索引加锁没有意义，所以不会对主键索引加锁。比如select * from user where age = 25 for update;，则最后一个锁可能为 (22, 39)，其他事务无法插入 age 值为 23、24、25、26、....、38 这些新记录，插入 age = 22 和 age = 39 记录的语句可能成功也可能不成功。
  【注】如果全部查找完了才发现，记录不存在，那么是next-key 锁，而不是间隙锁。如(1006, +∞]，这可能是因为(1006, +∞]和(1006, +∞)没差别。参考：链接
- 插入age = 22或 age = 39的记录时，需要判断id，从而判断插入的记录是否处于间隙锁之内，处于间隙锁之内则不能插入，不处于间隙锁之内则可以插入。也就是说(22, 39)虽然两边都是开区间，但是age = 22或 age = 39的记录也不一定可以插入。

非唯一索引范围查询：对二级索引记录加锁都是加 next-key 锁，对于一级索引加的是记录锁。
age >= 22如果对age = 22 的记录采用记录锁，那么不能阻止新的age = 22的记录的插入操作。
小林coding并没有说明为什么不能使用间隙锁，以后再来想吧。

没有加索引的查询：执行 update、delete、select ... for update 等具有加锁性质的语句，如果没有使用索引进行查找而是使用全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了。如果不锁住整个表，那么其他事务可以对表中内容进行修改，那么记录的值可能和我们预期的不一致。比如预期是事务1先修改记录a，然后事务2再修改记录a，但如果不锁住整个表顺序可能相反。
解决：当 sql_safe_updates 设置为 1 ，防止全表扫描

死锁

下面会产生死锁，因为select id from t_order where order_no = 1007 for update;会产生next-key 锁 (1006, +∞]，select id from t_order where order_no = 1008 for update;也会产生next-key 锁 (1006, +∞]。事务A发现事务B加了锁(1006, +∞]，此时事务A会产生插入意向锁，然后阻塞等待，直到事务B释放锁。事务B同样等待事务A释放锁，从而形成死锁。
【注】所以可以看到：

1.个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙
1. 而插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。

下面会产生幻读：

避免死锁的方法：

事务等待超过一定时间就是回滚。
开启主动死锁检测。发现死锁后，主动回滚死锁链条中的某一个事务。

Insert 语句是怎么加行级锁的？

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。隐式锁就是在 Insert 过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显示锁，这里我们列举两个场景。

如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的，此时会产生一个插入意向锁；
如果 Insert 的记录和已有记录存在唯一键冲突，此时也不能插入记录；
- 如果主键索引重复，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁（读锁）。其他事务就无法获取此记录的X型锁（写锁）
- 如果唯一二级索引重复，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁。

insert实例：事务 A 插入的 order_no 值为 1006 的记录上的「隐式锁」会变「显示锁」且锁类型为 X 型的记录锁，所以事务 B 向获取 S 型 next-key 锁时会遇到锁冲突，事务 B 进入阻塞状态，从而避免幻读。

从这个实验可以得知，并发多个事务的时候，第一个事务插入的记录，并不会加锁，而是会用隐式锁保护唯一二级索引的记录。
如果 order_no 不是唯一二级索引，那么两个事务，前后执行相同的 Insert 语句，是不会发生阻塞的。

死锁实例：字节面试：加了什么锁，导致死锁的？

MySQL 日志

update语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志）、binlog （归档日志）这三种日志：

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

undo log：在执行执行一条“增删改”语句，如果没有输入begin，会隐式开启事务来执行。如果事务没有执行完成，则会回滚，这保证了事务的原子性。回滚时需要的信息都记录到 undo log 里：

插入：记下主键值，方便回滚时删除。
删除：记下整个记录，方便回滚时插入。
更新：所有版本的记录串成一条链，方便回滚。每条记录中的trx_id表示事务id，roll_pointer代表下一个节点。

undo log的作用：1.事务回滚 2.MVCC（多版本并发控制）

redo log：redo log中记录了对某个数据页做了什么修改。更新记录时，会更新内存（设置为脏页），然后对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。

redo log 保证了事务四大特性中的持久性。redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的。在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。
写入 redo log 的方式使用了追加操作，所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。
redo log 的作用：1.崩溃后，保证已提交记录不会丢失 2.将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。
redo log 也是先写到缓存再写到磁盘，写到磁盘的时机（刷盘时机）：MySQL 正常关闭、redo log buffer被占用超过一半、后台线程每隔 1 秒、事务提交时
innodb_flush_log_at_trx_commit的作用：事务提交时不刷盘（0）、事务提交时刷盘（1）、事务提交时 redo log 文件（2），用在允许少量丢失的场景。
redo log由两个文件组成，如下图，write pos ～ checkpoint 之间的部分用来记录日志。当write pos 追上了 checkpoint，就意味着 redo log 文件满了，此时就会产生阻塞，需要先将Buffer Pool 中的脏页刷新到磁盘中，然后擦除redo log中相应的记录，从而腾出空间。
很多人疑问 undo log 是如何刷盘（持久化到磁盘）的？
undo log 和数据页的刷盘策略是一样的，都需要通过 redo log 保证持久化。buffer pool 中有 undo 页，对 undo 页的修改也都会记录到 redo log。

undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。

binlog：

概念：binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作。binlog 是写满一个文件就创建一个新文件。binlog 用于对整个数据库库的备份恢复、主从复制。
binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：
- STATEMENT：保存SQL 语句，如果SQL语句中使用到了 uuid 或者 now 这些函数时，那么主从复制中，从机根据 SQL 语句重现时，得到的结果会和主机不一致。
- ROW：记录行数据最终被修改成什么样了，使 binlog 文件过大。
- MIXED：它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
主从复制：
- 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
- 同步 Binlog：把 binlog 复制到所有从库上，并返回“复制成功”。
- 回放 Binlog：回放 binlog，并更新存储引擎中的数据。
  MySQL 主从复制模型：同步（等待复制成功响应才给客户端返回结果）、异步（不等待复制成功，主库宕机，数据丢失）、半同步（等待一个从库复制成功响应才给客户端返回结果）
binlog三种模式的区别（row，statement，mixed）:
- row：修改的数据，缺点：日志多
- statement：记录sql语句，缺点：在「主从复制的场景」中会发生数据不一致的问题。比如链接中提到使用自增id，可能会产生不一致。
- Mixed：上面两种方式的混合，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。
binlog 什么时候刷盘：一个事务的 binlog 是不能被拆开的，因此无论这个事务有多大（比如有很多条语句），也要保证一次性写入。一个线程只能同时有一个事务在执行，每个线程有一个缓存区用于缓存binlog，缓存不够时，会写入磁盘。
- binglog会写入到缓存中，然后写入到文件系统的page cache中，最后从page cache中刷新到磁盘中。刷新的方法：1.让操作系统自己刷新 2.每次写入到page cache都进行刷新 3.写入到n次到page cache再进行刷新

下面是redo log 和 binlog的区别，用于理解，没必要记：

binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
redo log 是 Innodb 存储引擎实现的日志；记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；
binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。
binlog 用于备份恢复、主从复制；
redo log 用于掉电等故障恢复。redo log 不能用于恢复整个数据库：

总结三种日志：update时，

首先记录undo log用于回退， undo log 会写入Buffer Pool 中的 Undo 页，修改Undo 页后，需要记录对应的 redo log。
然后InnoDB 层更新内存中的记录，标记为脏，将记录写到 redo log 里，redo log会在适合的时机写到磁盘中。
提交事务前，会将binlog保存到binlog cache，binlog cache会在适合的时机写到磁盘中。

事务提交后，redo log 和 binlog 都要持久化到磁盘，如果其中一个成功一个失败，就会组成主从不一致的情况：

redo log成功，binlog 失败：主库成功修改。binlog丢失，所以从库无法获取最新的修改。
redo log失败，binlog成功：主库修改失败，从库获取到最新的修改，从而进行修改。

两阶段提交：MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交

prepare 阶段：将 XID（内部 XA 事务的 ID）写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘。
commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

异常重启时， redo log肯定处于 prepare 状态，这时候只要判断binlog 中有没有对应的XID，如果有，那么则提交事务，如果没有，则回滚事务。

两阶段提交问题：

磁盘 I/O 次数高：两阶段提交要求每次提交事务时，binlog和redo log都要持久化到磁盘
锁竞争激烈：两阶段提交需要前需要获取到锁prepare_commit_mutex 。

组提交本质：等等其他事务一起刷盘，从而减少磁盘IO。
组提交：当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数。引入了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个过程：

flush 阶段：第一个事务成为leader，Leader 对 redo log执行write + fsync。并将binlog 写入到 binlog 文件（ page cache ）；如果此步完成后崩溃，那么重启后会回滚。
sync 阶段：等一段时间或者binlog 达到一定量时，才将所有binlog文件中的 fsync 操作（刷盘）；如果此步完成后崩溃，那么重启后会继续提交，应该binlog 中已经有了事务记录。
commit 阶段：将 redo log 状态设置为 commit，各个事务按顺序做 InnoDB commit 操作；

下面的每个阶段都有一个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。
对每个阶段引入了队列后，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程，可以看的出来，锁粒度减小了，这样就使得多个阶段可以并发执行，从而提升效率。

MySQL 磁盘 I/O 很高，有什么优化的方法：

控制binlog的刷盘时机：等一段时间再binlog 刷盘，等累计N个事务以后再刷盘（fsync）
控制redo log的刷盘时间：每次写入到 redo log 文件，而有操作系统决定什么时候写入到磁盘中。

内存篇——Buffer Pool

Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

读取：数据存在于Buffer Pool中，则直接从Buffer Pool中读取
修改：数据存在于Buffer Pool中，则修改从Buffer Pool中的数据，并设置数据为脏。使用后台线程将脏页写入磁盘。

Buffer Pool按「页」来划分，以页作为磁盘和内存交互，每一页中有多行数据。Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

Undo 页：更新操作产生的undo log链表会放在Undo 页中
当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，而不是加载一条记录到Buffer Pool中。

Buffer Pool空间管理： MySQL 启动会申请一块连续的内存空间作为Buffer Pool，下图中每个缓存页都对应一个控制块，控制块和缓存页之间灰色部分称为碎片空间。

管理空闲页：空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表。
管理脏页：缓存中的记录被修改，此页就为脏页。使用Flush 链表将脏页连接起来。
脏页什么时候会被刷入磁盘：redo log 日志、淘汰的是脏页，需要先将脏页同步到磁盘、MySQL 认为空闲时、MySQL 正常关闭之前。
LRU 链表：将最近访问的节点放在链表头部，淘汰节点时，淘汰链表末尾的节点。脏页同时存在于 LRU 链表和 Flush 链表中。简单的 LRU 算法的问题：
- 预读失效：根据空间局部性，加载数据页时会将相邻的页也加载进来。但是如果相邻页没有被访问，就白加载了。解决方法：预读的页放在old区域，也就是靠近尾部的区域。
- Buffer Pool 污染：当某一个 SQL 语句扫描了大量的数据时， Buffer Pool 里的大量页都替换出去，导致大量热数据被淘汰了。比如索引失效导致全表扫描，每个扫描的数据都会放到LUR链表中。
  【注】全表扫描的过程：从磁盘将页加入到old区域的头部，当页被访问时才将页加入到young区域的头部。所以mysql提高了进入young区域的门槛，从而减少Buffer Pool 污染而导致缓存命中率下降的问题：
  - 1.「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool 污染的问题。【我的理解】 old 区域停留时间超过 1 秒的：因为短时间内页不会从old区被淘汰，所以可以先不放在young区。如果页在old 区域停留时间超过 1 秒的，那么将此页放在young区，从而使该页获得更加长的生命。
  - 2.为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会。

其他

count()

性能：count(*) = count(1) > count(主键字段) > count(字段)

count(主键字段)：server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。
「优化器」优先选择的是二级索引，因为二级索引的叶子节点小。
count(1)和count(*) ：InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，直接将count加一。
- 优化 count(*)：1.explain 命令估算行数 2.额外表保存计数值。当我们在数据表插入一条记录的同时，将计数表中的计数字段 + 1。
  「优化器」优先选择的是最小的二级索引，因为二级索引的叶子节点小。
count(字段)：全表扫描

Innodb 为什么要通过遍历的方式来计算count的结果：同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的，所以无法像 MyISAM一样，只维护一个 row_count 变量。

AUTO-INC 锁没看完，看完日志再看它。。。

posted @ 2023-09-17 14:12 好人~ 阅读(49) 评论(0) 编辑收藏举报

刷新页面返回顶部

codingbigdog