导图社区 大厂SSPoffer 复习资料 - Mysql
- 10
- 0
- 0
- 举报
大厂SSPoffer 复习资料 - Mysql
博主获取多个大厂包括 腾讯，字节，B站，超参数等ssp的mysql复习资料，希望这份资料能够为求职者提供一个全面的学习框架，帮助大家面试中脱颖而出，成功获得心仪的offer。
编辑于2024-12-08 18:02:37- mysql
- 春招
- ssp
- 大厂SSPoffer 复习资料 - Mysql
博主获取多个大厂包括 腾讯，字节，B站，超参数等ssp的mysql复习资料，希望这份资料能够为求职者提供一个全面的学习框架，帮助大家面试中脱颖而出，成功获得心仪的offer。
- 大厂SSPoffer 复习资料 - Redis
博主获取多个大厂包括 腾讯，字节，B站，超参数等ssp的redis复习资料，希望这份资料能够为求职者提供一个全面的学习框架，帮助大家面试中脱颖而出，成功获得心仪的offer。
- 大厂SSPoffer 复习资料 - 计算机网络
博主获取大厂 腾讯，美团，字节，B站，超参数等大厂ssp的计算机网络复习资料，希望这份资料能够为求职者提供一个全面的学习框架，帮助大家面试中脱颖而出，成功获得心仪的offer。
大厂SSPoffer 复习资料 - Mysql
社区模板帮助中心，点此进入>>
- 大厂SSPoffer 复习资料 - Mysql
博主获取多个大厂包括 腾讯，字节，B站，超参数等ssp的mysql复习资料，希望这份资料能够为求职者提供一个全面的学习框架，帮助大家面试中脱颖而出，成功获得心仪的offer。
- 大厂SSPoffer 复习资料 - Redis
博主获取多个大厂包括 腾讯，字节，B站，超参数等ssp的redis复习资料，希望这份资料能够为求职者提供一个全面的学习框架，帮助大家面试中脱颖而出，成功获得心仪的offer。
- 大厂SSPoffer 复习资料 - 计算机网络
博主获取大厂 腾讯，美团，字节，B站，超参数等大厂ssp的计算机网络复习资料，希望这份资料能够为求职者提供一个全面的学习框架，帮助大家面试中脱颖而出，成功获得心仪的offer。
- 相似推荐
- 大纲
Mysql
执行一条 select 语句，期间发生了什么？
Mysql架构
Server层
Server 层负责建立连接、分析和执行 SQL
存储引擎层
存储引擎层负责数据的存储和提取
Select执行流程
第一步
客户端链接连接器
第二步
客户端向Mysql服务发送sql语句，查询缓存
对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。MySQL 8.0 版本直接将查询缓存删掉了
第三步
解析SQL
词法分析
MySQL 会根据你输入的字符串识别出关键字出来，
语法分析
通过SQL构建语法树
第四步
执行SQL
prepare 阶段，也就是预处理阶段；
检查 SQL 查询语句中的表或者字段是否存在；
将 select * 中的 * 符号，扩展为表上的所有列；
optimize 阶段，也就是优化阶段；
优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
基于查询成本的考虑， 选择查询成本最小的执行计划；
案例
product 表就有主键索引（id）和普通索引（name）。假设执行了这条查询语句： select id from product where id > 1 and name like 'i%';
这条查询语句是覆盖索引，直接在二级索引就能查找到结果（因为二级索引的 B+ 树的叶子节点的数据存储的是主键值），就没必要在主键索引查找了
execute 阶段，也就是执行阶段；
根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；
Tips
链接Mysql是通过TCP协议
Mysql有空闲链接，八小时没使用会自动断开
Mysql有最大连接数默认是151个
Mysql有长连接和短连接的区别
解决长连接占用内存的问题
定期断开长连接
客户端主动重置连接
索引下推
将部分过滤条件的检查工作推迟到索引扫描过程中进行，而不是在索引扫描之后再进行
只会作用在二级索引上
在没开启索引下推的时候进行查询（已经建立了两个字段的联合索引），并且查询也是索引的两个字段
先查询第一个索引对应的条件
找到所有符合第一个条件的行信息
比如查了230条数据
就需要回表230次
再根据这些对应的行进行第二个条件的一次查询
如果开启了索引下推
就会在引擎层继续进行第二个条件的匹配
筛选出符合第二个条件的行
查完剩余30条
回表30次
进行回表查询
回表
每个数据库表分为聚簇索引和非聚簇索引
每个表只有一个聚簇索引
聚簇索引不仅包含键值，还包含表中每行数据
有多个非聚簇索引
只包含主键和索引内容
当查找字段在二级索引不存在就去聚簇索引里面查找叫做回表
覆盖索引
能在二级索引里面查到所有的内容就不用回表了
MySQL 一行记录是怎么存储的
表空间
行
每条记录都是以行存储的
页
数据库读取是以页存储的
当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。
默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。
区
为了索引分配在数据量大的时候能更好发挥作用
为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。
段
索引段：存放 B + 树的非叶子节点的区的集合；
数据段：存放 B + 树的叶子节点的区的集合；
回滚段：存放的是回滚数据的区的集合，之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。
InnoDB的行格式
Compact
第一个变长字段
存放列中变长的内容
name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；
age 列和 id 列不是变长字段，所以这里不用管。
第一条记录
第二条记录
第三条记录
逆序是为了使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。
每个数据库表的行格式都有「变长字段字节数列表」吗？
其实变长字段字节数列表不是必须的。 当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了，因为没必要，不如去掉以节省空间
第二个是NULL值列表
每个数据库表的行格式都有「NULL 值列表」吗？
NULL 值列表也不是必须的。 当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。
「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？
「NULL 值列表」的空间不是固定 1 字节的。 当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。
varchar(n) 中 n 最大取值为多少？
一行记录最大只能存储 65535 字节的数据
对于单字段
算 varchar(n) 中 n 最大值时，需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以，在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532
对于多字段
所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535
行溢出后，Mysql会将多的数据存到另外的溢出页面
当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。大致如下图所示。
索引
索引
联合索引的顺序是根据创建索引时列的顺序来确定的。例如，如果你创建了一个名为idx_abc的索引，包括列a、列b和列c，那么索引将首先按照列a排序，然后是在相同a值的情况下按照列b排序，最后是在a和b值都相同的情况下按照列c排序。
mysql有100行数据，那么索引也有100行，快速找到主键
索引的定义就是帮助存储引擎快速获取数据的一种数据结构
分类
按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
二级索引
里面存放的是索引值和主键值，根据索引值进行排序
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
主键索引
建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
唯一索引
建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...);
普通索引
普通索引就是建立在普通字段上的索引
CREATE INDEX index_name ON table_name(index_column_1,index_column_2,...);
前缀索引
对字符类型字段的前几个字符建立的索引
CREATE INDEX index_name ON table_name(column_name(length));
按「字段个数」分类：单列索引、联合索引。
联合索引
多个字段组合成一个索引，该索引就被称为联合索引
首先按照班级值来排序，如果一样则按照学生姓名来排序，如果一样，则按照科目名称来排序，所以数据页内部都是按照这三个字的值来排序的。
数据页内部与数据页之间也是有序的，数据页内部组成单向链表，数据页之间组成双向链表。
CREATE INDEX index_product_no_name ON product(product_no, name);
使用联合索引时，存在最左匹配原则
如果创建了一个 (a, b, c) 联合索引，符合联合索引，因为有查询优化器，所以 a 字段在 where 子句的顺序
where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；
不符合
where b=2；
where c=3；
where b=2 and c=3；
失效的原因
上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样
联合索引的查询范围
并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询
Q1: select * from t_table where a > 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的，所以b=2不能减少检索量
Q1 这条查询语句只有 a 字段用到了联合索引进行索引查询，而 b 字段并没有使用到联合索引
select * from t_table where a >= 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
先按照 a 字段的值排序的，所以符合 >= 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 >= 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a>= 1 条件位置
虽然在符合 a>= 1 条件的二级索引记录的范围里，b 字段的值是「无序」的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的（因为对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序）
Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？
由于 MySQL 的 BETWEEN 包含 value1 和 value2 边界值，所以类似于 Q2 查询语句，因此 Q3 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
SELECT * FROM t_user WHERE name like 'j%' and age = 22，联合索引（name, age）哪一个字段用到了联合索引的 B+Tree？
对于符合 name = j 的二级索引记录的范围里，age字段的值是「有序」的
在确定需要扫描的二级索引的范围时，当二级索引记录的 name 字段值为 ‘j’ 时，可以通过 age = 22 条件减少需要扫描的二级索引记录范围
a 和 b 字段都用到了联合索引进行索引查询。
综上所示，联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配，前面我也用了四个例子说明了
建立索引
适用索引的情况
字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。
不适用索引的情况
WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。
AVL树和红黑树
红黑树的性质
节点颜色：每个节点要么是红色，要么是黑色。
根属性：树的根节点必须是黑色的。
叶子节点：所有的叶子节点（NIL节点，空节点）都是黑色的。
红色节点规则：红色节点不能有红色的子节点（即，不能有两个连续的红色节点）。
黑色平衡：从任一节点到其每个叶子节点的所有路径上，黑色节点的数量必须相同。
为什么要红黑树而不是AVL树
有了AVL树为什么还要红黑树？
平衡严格性：
AVL树提供更加严格的平衡。每个节点的左右子树的高度差最多为1。这种严格的平衡提供了非常快的查找操作，特别是在读操作远多于写操作的场景中。
红黑树的平衡不如AVL树严格，允许更大的高度差（具体而言，最长路径不会超过最短路径的两倍）。红黑树在插入和删除操作中提供更好的性能，因为这些操作通常导致较少的树重构。
树重构和旋转操作：
在AVL树中，插入和删除可能需要多次旋转来保持树的平衡，这使得写操作（插入和删除）相对耗时。
红黑树通常在插入和删除时需要较少的旋转操作，这使得它在频繁更新的环境中表现更好。
应用场景：
AVL树由于其查找操作的高效性，通常用于读操作远多于写操作的场景，例如数据库索引。
红黑树由于其在插入和删除操作上的高效性，常用于需要频繁更新的场景，如Java的TreeMap和TreeSet，以及C++的STL中的map和set。
红黑树为什么在插入和删除有更好的性能
平衡条件的宽松性：
红黑树有五个关键性质，其中最重要的是保持树的大致平衡，而不是严格的平衡。这些性质确保了任何一条从根到叶子的路径不会比其他路径长出两倍以上。
相比之下，AVL树要求树在每个节点处都高度平衡（左右子树的高度差最多为1），这是一种更严格的平衡要求。
旋转操作的减少：
由于红黑树的平衡条件较为宽松，当执行插入或删除操作时，通常需要较少的旋转来重新平衡树。这意味着修改操作的处理速度更快。
AVL树由于其严格的平衡要求，在插入或删除节点后，可能需要更多的旋转操作来保持平衡，特别是在树较高时。
插入和删除的性能特点：
红黑树在插入和删除时，通过简单的颜色变换和少数几次旋转就可以恢复平衡。这些操作在计算上是非常高效的。
在AVL树中，由于其严格的平衡规则，即使是单一的插入或删除操作也可能导致树的多个部分需要重新平衡，导致更多的计算成本。
B+树
在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。
B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中
B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。
优点
1、B+Tree vs B Tree
B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
2、B+Tree vs 二叉树
对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。
而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
3、B+Tree vs Hash
Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。
但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因
4. B+树vs跳表
磁盘访问效率B+数更好
B+ 树
通过节点分层组织数据，所有的叶子节点都在同一层并且包含所有数据的指针或数据本身，这使得 B+ 树的查询、插入、删除操作可以尽量减少磁盘的随机读写次数。由于 B+ 树的节点较大，可以在一次磁盘读写中加载多个数据，减少了磁盘 I/O 操作，从而提高性能。
跳表
虽然在内存中性能较好，但在处理海量数据时，通常需要频繁的磁盘 I/O 操作。由于跳表的节点在结构上不如 B+ 树紧凑，在磁盘上可能需要更多的随机读写，增加了 I/O 负担。
时间复杂度稳定
B+ 树
是一种高度平衡的树结构，每次插入或删除操作都能保持树的平衡，因此它在最坏情况下的查询、插入、删除时间复杂度是 O(log n)，并且非常稳定。
跳表
是一种概率性数据结构，尽管它在期望情况下的时间复杂度也是 O(log n)，但它的最坏情况可能会退化，尤其在存在大量插入或删除操作的情况下。
redis使用跳表而不是B+数
因为B+树在磁盘存储更有优势，而Redis是一种内存数据库。B+树一次减少磁盘随机读写的优势就不存在了
B+树添加数据时候更加复杂
B+树使用跳表和哈希表可以更简洁地实现高效的增删查改操作。跳表通过随机化的方式实现高效的查找，并且代码简单易于维护。
采用跳表和哈希表，可以在确保性能的同时有效地控制内存占用。内存利用效率更好
优化索引
前缀索引优化
使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
覆盖索引优化
覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。
假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？
我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。
所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。
主键索引最好是自增
如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率
插入7导致页分裂
索引最好设置为 NOT NULL
第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表
防止索引失效
当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
总结
加了唯一索引有可能有数据重复问题
因为唯一索引不能避免null。比如num_id,course_id建立联合索引
可以传入 1，null ， 1，null是不报错的
为了避免加上 delete_status和 delete_id
B+树
B树
查找过程
与根节点的索引(4，8）进行比较，9 大于 8，那么往右边的子节点走；
然后该子节点的索引为（10，12），因为 9 小于 10，所以会往该节点的左边子节点走；
走到索引为9的节点，然后我们找到了索引值 9 的节点。
特点就是非叶子节点也会存储信息
就会导致加载的时候十分占用内存
但是 B 树的每个节点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。
缺点
们查询位于底层的某个节点（比如 A 记录）过程中，「非 A 记录节点」里的记录数据会从磁盘加载到内存，但是这些记录数据是没用的，我们只是想读取这些节点的索引数据来做比较查询，而「非 A 记录节点」里的记录数据对我们是没用的，这样不仅增多磁盘 I/O 操作次数，也占用内存资源。
B+树
叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
非叶子节点中有多少个子节点，就有多少个索引；
B+ 树中的每个节点都是一个数据页
两者差别
单点查询
在一行数据量相同的情况下，由于B树要放记录，B+树可以放更多的索引，所以B+树肯定更加矮胖，查询IO更少
大量单点查询可以考虑B树
删除查询
B+ 树的插入和删除效率更高。
B树在删除上面可能导致树更复杂
范围查询
B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助，比如说我们想知道 12 月 1 日和 12 月 12 日之间的订单，这个时候可以先查找到 12 月 1 日所在的叶子节点，然后利用链表向右遍历，直到找到 12 月12 日的节点，这样就不需要从根节点查询了
树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。
Mysql单表不超过2000w行
X表示指向下一个节点的个数（也就是当前存储的索引个数），Y表示一个页里面存储的数据，Z表示所有层数
x^z-1 *Y
计算X
一个页空间16K，掐头去尾存放数据为15K
非叶子结点主键8个字节，指向的页号为4个字节 大概12个字节
15K / 12 =1280
计算Y
假设一个数据为1KB
存储的为15
Z的值
MySQL 的表数据是以页的形式存放的，页在磁盘中不一定是连续的。
页的空间是 16K, 并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等。
在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。
索引结构不会影响单表最大行数，2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。
MySQL 使用 like “%x“，索引一定会失效吗？
题目一
这四条模糊匹配的查询语句，第一条和第二条都会走索引扫描，而且都是选择扫描二级索引（index_name）
而第三和第四条会发生索引失效，执行计划的结果 type= ALL，代表了全表扫描
题目二
表特别之处在于，只有两个字段，一个是主键索引 id，另外一个是二级索引 name
这个查询的数据都在二级索引的 B+ 树，因为二级索引的 B+ 树的叶子节点包含「索引值+主键值」，所以查二级索引的 B+ 树就能查到全部结果了，这个就是覆盖索引。
count(*) 和 count(1) 有什么区别？哪个性能最好？
count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。
如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。
不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。
优化Count
近似值
额外表保存计数值
事务隔离级别是怎么实现的？
事物有哪些特性
原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成
原子性是通过 undo log（回滚日志） 来保证的；
一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
一致性则是通过持久性+原子性+隔离性来保证；
隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，
隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
持久性是通过 redo log （重做日志）来保证的
并行事务出现的问题
脏读
如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象
如果在上面这种情况事务 A 发生了回滚，那么事务 B 刚才得到的数据就是过期的数据，这种现象就被称为脏读。
加锁
不可重复读
在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。
在这过程中如果事务 B 更新了这条数据，并提交了事务，那么当事务 A 再次读取该数据时，就会发现前后两次读到的数据是不一致的，这种现象就被称为不可重复读。
使用mvcc
这是某一条记录内容不一样了，行级别的
幻读
在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。
解决方法
针对快照读（普通 select 语句），是通过 MVCC(多版本并发控制) 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
MVCC 并不能完全避免幻读现象。
因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
因为版本号不一样了
针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
事务 A 执行了这面这条锁定读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合）。 然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入等待状态，直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。
对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。
彻底解决方法就是select时候 mvcc+间隙锁+记录锁
这是查询的结果的总数不一样了，表级别的
严重程度
四种隔离机制来规避这些现象
事务的隔离级别
在「读未提交」隔离级别下，事务 B 修改余额后，虽然没有提交事务，但是此时的余额已经可以被事务 A 看见了，于是事务 A 中余额 V1 查询的值是 200 万，余额 V2、V3 自然也是 200 万了；
在「读提交」隔离级别下，事务 B 修改余额后，因为没有提交事务，所以事务 A 中余额 V1 的值还是 100 万，等事务 B 提交完后，最新的余额数据才能被事务 A 看见，因此额 V2、V3 都是 200 万；
在「可重复读」隔离级别下，事务 A 只能看见启动事务时的数据，所以余额 V1、余额 V2 的值都是 100 万，当事务 A 提交事务后，就能看见最新的余额数据了，所以余额 V3 的值是 200 万；
在「串行化」隔离级别下，事务 B 在执行将余额 100 万修改为 200 万时，由于此前事务 A 执行了读操作，这样就发生了读写冲突，于是就会被锁住，直到事务 A 提交后，事务 B 才可以继续执行，所以从 A 的角度看，余额 V1、V2 的值是 100 万，余额 V3 的值是 200万。
读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
加锁变成读已提交
读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。
如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。
在事务 A 的 Read View 中，它的事务 id 是 51，由于它是第一个启动的事务，所以此时活跃事务的事务 id 列表就只有 51，活跃事务的事务 id 列表中最小的事务 id 是事务 A 本身，下一个事务 id 则是 52。
在事务 B 的 Read View 中，它的事务 id 是 52，由于事务 A 是活跃的，所以此时活跃事务的事务 id 列表是 51 和 52，活跃的事务 id 中最小的事务 id 是事务 A，下一个事务 id 应该是 53。
案例
事务 B 读取小林的账户余额记录，读到余额是 100 万；
事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务；
事务 B 读取小林的账户余额记录，读到余额还是 100 万；
事务 A 提交事务；
事务 B 读取小林的账户余额记录，读到余额依然还是 100 万
接着，事务 A 通过 update 语句将这条记录修改了（还未提交事务），将小林的余额改成 200 万，这时 MySQL 会记录相应的 undo log，并以链表的方式串联起来，形成版本链
然后事务 B 第二次去读取该记录，发现这条记录的 trx_id 值为 51，在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 值是否在 m_ids 范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录。而是沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录，所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录。
最后，当事物 A 提交事务后，由于隔离级别时「可重复读」，所以事务 B 再次读取记录时，还是基于启动事务时创建的 Read View 来判断当前版本的记录是否可见。所以，即使事物 A 将小林余额修改为 200 万并提交了事务， 事务 B 第三次读取记录时，读到的记录都是小林余额是 100 万的这条记录。
串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；
隔离强度
不同隔离级别会发生的内容
Mysql的锁
全局锁
分为读锁和写锁
读锁 共享锁
写锁 排它锁
使用 Flush tables with read lock 进行加锁
使用 unlock table 进行解锁
开销十分大
使用案例
备份时候里面有两张表，不加全局锁可能造成数据不一致性
表级锁
表锁
表锁是一种数据库锁定机制，用于控制对整个表的访问。它主要有以下特点：
1. 作用域：表锁会锁定整个表，影响到表中的所有行。当一个事务在表上获得锁之后，其他事务就无法对这个表执行会与之冲突的操作。
2. 锁定级别：表锁是一种较粗粒度的锁定机制。它适用于需要对整个表进行操作的场景，如大量数据的插入、删除或更新。
3. 性能影响：由于表锁会锁定整个表，它可能会对数据库的并发性能产生较大影响，尤其是在高并发的环境下。
4. 应用场景：表锁通常在数据库管理系统中自动使用，或者可以通过特定的 SQL 命令（如 `LOCK TABLES`）显式地获得。
读密集型，写操作不频繁
数据量不大的应用
全表的更新或者删除
有哪些命令会上表锁
更改表的结构的时候
删除表
Lock Tables
风险点
并发性能差
可能导致锁等待和超时
写操作影响大
操作多表可能造成死锁
元数据锁（MDL）
不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL
对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；
MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。
MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。
元数据锁是用于保护数据库对象（如表结构）的元数据的一种锁定机制。它的主要特点包括：
1. 作用域：元数据锁不是用于锁定数据本身，而是用于锁定数据结构的定义，例如表的结构。当对表结构进行更改时（如添加、删除列或重命名表），元数据锁确保在此期间不会有其他操作干扰这些更改。
2. 保护数据库一致性：元数据锁防止在执行结构更改操作（DDL）期间发生的数据定义冲突，从而维护数据库的完整性和一致性。
3. 锁定级别：元数据锁是一种更细粒度的锁定机制，它专注于数据结构的元数据，而非数据本身。
4. 性能影响：元数据锁通常只在执行结构更改操作时才会被持有，因此对正常的数据操作（如查询、插入、更新、删除）的影响较小。
问题
1. 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
2. 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
3. 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，
4. 这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。
意向锁
对某些记录加上「共享锁」之前，先在表级别加上「意向共享锁」；对某些纪录加上「独占锁」之前，在表级别加上「意向独占锁」；
在加行级锁的时候，会加上表意向锁，等后面其他表锁想进来，直接检查意向锁而不用检查所有的行是否加锁
AUTO-INC 锁
不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。
AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。
在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。
行级锁
只能在事务中使用，在非事务中使用的话在语句执行完就会立马释放
共享锁（S锁）
排它锁（X锁）
共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥
优点
高并发读写操作
可以允许多个事务并发操作不同的行
粒度小
短期锁
进行短期锁定，防止长时间阻塞其他事务
进行复杂的事务处理
风险
死锁
锁升级
当试图锁定行过多，在Innodb里面将行级锁升级成表锁，导致更多锁冲突
锁等待
资源消耗
当行数特别多，需要更多CPU处理锁的请求
难以调试和排查
锁类别
Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
缺点
性能影响
造成死锁
复杂
锁定范围过大
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身（左开右闭）
表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改和删除 id = 5 这条记录。
如何加锁
行级别加锁的对象是索引，加锁的基本单位是 next-key lock
唯一索引等值查询
当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。
唯一索引范围查询
针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。
针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中
当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，
扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁 ，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
当条件值的记录在表中，如果是「小于」条件的范围查询，
扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁 ，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
非唯一索引范围查询
索引的 next-key lock 不会有退化为间隙锁和记录锁的情况
没有加索引的查询
如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。
哪些语句加行锁
1.SELECT ... FOR UPDATE:这种查询会对选定的行添加一个排他锁（X锁),这意味着其他事务不能修改这些行，也不能对这些行添加共享锁。
2.SELECT ... LOCK IN SHARE MODE:这种查询会对选定的行添加一个共享锁(S锁)，这意味着其他事务不能修改这些行，但可以对这些行添加共享锁。
3.INSERT:插入操作会对新添加的行添加一个排他锁(X锁)。
4.UPDATE:更新操作会对被更新的行添加一个排他锁(X锁)。
5.DELETE:删除操作会对被删除的行添加一个排他锁(X锁)。
MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗？
在 MySQL 的可重复读隔离级别下，针对当前读的语句会对索引 加记录锁+间隙锁，这样可以避免其他事务执行增、删、改时导致幻读的问题。
有一点要注意的是，在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。
update没加索引会锁全表
当我们要执行 update 语句的时候，确保 where 条件中带上了索引列，并且在测试机确认该语句是否走的是索引扫描，防止因为扫描全表，而对表中的所有记录加上锁。
关键还得看这条语句在执行过程种，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。
如何避免死锁？
四个条件
互斥、占有且等待、不可剥夺、循环等待
打破循环等待
设置事务等待锁的超时时间
主动进行死锁检测
举例
1. 事务A
首先锁定了行1
然后尝试锁定行2
2. 事务B
首先锁定了行2
然后尝试锁定行1
buffer pool
Innodb设计的缓冲池
当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。
在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。
Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。
当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录
预读失效
程序是有空间局部性的，靠近当前被访问数据的数据，在未来很大概率会被访问到。
所以，MySQL 在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO。被提前加载进来的数据页，并没有被访问 ，相当于这个预读是白做了，这个就是 预读失效
解决方法
将mysql将 LRU 划分了 2 个区域：old 区域 和 young 区域。 young 区域在 LRU 链表的前半部分，old 区域则是在后半部分
划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。
change buffer
假设我们就直接执行一条 update table set name = 'yes' where id = 1，如果此时 buffer pool 里没有 id 为 1 的这条数据，那怎么办？
难道把这条数据先加载到 buffer pool 中，然后再执行修改吗？
当然不是，这时候 change buffer 就上场了。
如果当前数据页不在 buffer pool 中，那么 innodb 会把更新操作缓存到 change buffer 中，当下次访问到这条数据后，会把数据页加载到 buffer pool 中，并且应用上 change buffer 里面的变更，这样就保证了数据的一致性。
Mysql日志
日志
undo log
引擎层
用于数据回滚
redo log
引擎层
系统崩溃，断电，重启
记录的是物理日志，即每一个页的物理变化
bin log
server层
记录的是逻辑日志，但它是用于复制和数据恢复的，记录的是SQL语句层面的更改。
记录的是逻辑日志，即每一条SQL语句的执行
为了数据的持久保存、主从复制以及数据恢复
binlog和 redolog
redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。
重点在数据页上
而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。
undo log
插入 一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录 删掉 就好了；
删除 一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录 插入 到表中就好了；
更新 一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列 更新为旧值 就好了。
类似于git reflog 每一条操作具体干了什么
redo log
redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。
在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。
当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。
类似与git log 记录数据变更后的状态
有刷新参数
0
：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
每隔一秒刷一次
1
：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作 （默认值）
直接写入到磁盘中
2
：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache
提交刷到缓存页里
mysql挂了数据不会丢失
宕机了数据会丢失
redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值； undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；
redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？
写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写 ，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是 随机写
磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。
产生的 redo log 是直接写入磁盘的吗？
不是的。
实际上， 执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。
所以，redo log 也有自己的缓存——redo log buffer ，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘如下图：
redo log 刷盘(内存内容刷新到磁盘)
MySQL 正常关闭时；
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制，下面会说）。
redo log 文件写满了怎么办？
重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。
write pos 和 checkpoint 的移动都是顺时针方向；
write pos ～ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；
check point ～ write pos 之间的部分（图中蓝色部分）：待落盘的脏数据页记录；
如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。
InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。
binlog
记录所有修改数据库数据的语句（如INSERT、UPDATE、DELETE等），不会记录查询类的操作，比如 SELECT 和 SHOW 操作
redo log 和 binlog 有什么区别？
1、适用对象不同：
binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
redo log 是 Innodb 存储引擎实现的日志；
2、文件格式不同：
binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：
STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；
3、写入方式不同：
binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。
4、用途不同：
binlog 用于备份恢复、主从复制；
redo log 用于掉电等故障恢复。
如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？
不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。
因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。
binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。
两阶段提交
1. 因为redolog在引擎层，先进行redolog的修改
2. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复 回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
3. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。
4. 可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
1、prepare阶段，写redo log；
2、commit阶段，写binlog并且将redo log的状态改成commit状态；
redo-log(prepare)：在写入准备状态的redo记录时宕机，事务还未提交，不会影响一致性。
bin-log：在写bin记录时崩溃，重启后会根据redo记录中的事务ID，回滚前面已写入的数据。
redo-log(commit)：在bin-log写入成功后，写redo(commit)记录时崩溃，因为bin-log中已经写入成功了，所以从机也可以同步数据，因此重启时直接再次提交事务，写入一条redo(commit)记录即可。
Mysql开启事务
选择事务支持的存储引擎：首先，确保你使用的是支持事务的存储引擎，如InnoDB。InnoDB是MySQL默认的事务型存储引擎，支持事务处理、回滚和崩溃恢复功能。
开始事务：使用START TRANSACTION或BEGIN语句来开始一个新的事务。这标志着一个事务的开始，之后的所有语句都会被视为这个事务的一部分，直到你提交或回滚事务。
START TRANSACTION;
BEGIN;
执行事务操作：在START TRANSACTION之后，执行你的SQL语句，如插入、更新或删除操作。这些操作会在事务中进行，但在事务提交之前不会对其他用户可见。
INSERT INTO your_table (column1, column2) VALUES (value1, value2);
UPDATE your_table SET column1 = value1 WHERE condition;
提交或回滚事务：根据操作的结果，你可以选择提交事务或回滚事务。如果一切正常，使用COMMIT语句提交事务，这会使事务中的所有更改永久生效。如果遇到问题或者需要撤销在事务中所做的更改，使用ROLLBACK语句来回滚事务。
提交事务：
COMMIT;
回滚事务：
ROLLBACK;
结束事务：提交或回滚后，事务结束。接下来的SQL语句将不再属于之前的事务。
sql优化
索引（Indexing）: 创建合适的索引是最常见的优化方法之一。索引能够加速查询过程中的数据检索。但需要注意，过多或不恰当的索引可能会降低更新表时的性能。
查询分析和重写（Query Analysis and Rewriting）: 分析SQL查询，了解它如何被数据库执行。有时候，通过重写查询，例如改变连接的顺序或使用子查询，可以显著提升性能。
避免全表扫描（Avoiding Full Table Scans）: 尽量避免全表扫描，因为这会影响性能。通过使用索引或改进查询条件，可以只检索必要的数据。
批量处理（Batch Processing）: 对于大量的数据插入或更新，使用批处理而不是单条记录处理可以提高效率。
利用缓存（Leveraging Caching）: 大多数数据库管理系统提供缓存机制，合理使用缓存可以提高重复查询的响应时间。
合理使用聚合函数: 在使用如GROUP BY和聚合函数（如SUM, AVG）时，确保选择合适的索引。
Mysql架构
单体mysql
主从集群
读写分离，主从同步
数据库分片
水平拆分，将数据库拆分到多个数据库实例中
所有分片并集为整个数据库，交集为空
拆分方法
哈希取摸
优势简单
劣势不好扩展，每个水平切分之后数据库满了要新增数据库就很困难
一致性哈希
优势有优良的扩展性
成本开销过大 量级评估不一定能和业务发展相匹配
分片+主从集群
目的：
解决分片架构的高可用性问题。
提升系统的扩展性、性能和可靠性。
实现方式：
在每个分片内使用主从集群（Master-Slave）结构。
通过分片实现数据和负载的水平扩展。
通过主从集群实现数据的冗余备份和读写分离。
优势：
高可用性：主数据库故障时，通过故障转移机制将从数据库提升为主数据库，确保数据可用性。
负载均衡：分片分担数据和访问负载，主从集群分担读写负载，提升系统整体处理能力。
数据冗余和可靠性：每个分片的数据冗余存储，提高数据可靠性和容错能力。
灵活扩展性：可在不同维度上扩展，适应业务增长。
读写分离和性能提升：读请求分散到多个从数据库实例上，提升读操作性能。
一致性哈希
背景
在一个分布式系统中如分布式KV并不是每一个节点都存储了所有的key
要根据key连接到某个具体的节点上应该是确定的
可以考虑用到哈希快速确定服务器节点
如果节点数量发生了变化，也就是在对系统做扩容或者缩容时，必须迁移改变了映射关系的数据
方案
一致性哈希是指将「存储节点」和「数据」都映射到一个首尾相连的哈希环上
映射的结果值：往顺时针的方向的找到第一个节点 ，就是存储该数据的节点。
在一致哈希算法中，如果增加或者移除一个节点，仅影响该节点在哈希环上顺时针相邻的后继节点，其它数据也不会受到影响。
提高均衡度
通过添加虚拟节点
不再将真实节点映射到哈希环上，而是将虚拟节点映射到哈希环上，并将虚拟节点映射到实际节点，所以这里有「两层」映射关系
第一次映射
将节点拆分为多个虚拟节点
对节点 A 加上编号来作为虚拟节点：A-01、A-02、A-03
对节点 B 加上编号来作为虚拟节点：B-01、B-02、B-03
对节点 C 加上编号来作为虚拟节点：C-01、C-02、C-03
第二次映射将
A-n映射到A节点上 以此类推
慢查询
sql查询语句过慢
原因
可能是由于引擎选错索引导致
使用explain语句执行
由于客户端和mysql服务器的连接数过小导致查询语句阻塞
mysql修改配置使用max_connections = n
修改最大连接数
默认100
最大16635
客户端连接池国小
使用orm库设置连接池大小
也可能是buffer pool太小了
buffer pool是innodb和mysql服务器之间的缓存层
如何确定是不是buffer pool太小了
查看buffer pool的缓存命中率1
show status like 'innodb_buffer_pool%'
一般是在99%以上