MySQL知识点总结

SQL语法

count 主键和 count 非主键结果会不同吗？

答：

count() 函数是 返回表中某个列的非 NULL 值的数量
- 由于 主键的列不能存 NULL 值，所以 count(主键) 返回的结果，可以表示 数据库表中所有行数据的数量
- 由于 非主键的列可以存 NULL 值，那么 count(非主键) 返回表中非主键列的非NULL值的数量

MySQL的内连接、外连接有什么区别？

答：

内连接和外连接都是用于 连表查询
内连接是只返回两个表匹配的数据行，外连接可以返回两个表匹配和不匹配的数据行，外连接 主要分为 左连接 和 右连接
- 左连接：返回左表中的所有行和右表中匹配的行，如果右表中没有匹配的行，则用 NULL 值填充
  1
  select * from A left join B on A.A_id = B.B_id;
- 右连接：返回右表中的所有行和左表中匹配的行，如果左表中没有匹配的行，则用 NULL 值填充
  1
  select * from A right join B on A.A_id = B.B_id;

详细链接：MySQL JOIN

外连接时 on 和 where 过滤条件区别？

答：

在 内连接 中，on 和 where 的过滤条件等效
在 外连接 中，使用 on 和 where 过滤条件的区别在于：
- on 用于指定连接两个表的条件，通常用于指定两个表之间的关联条件，即 连接条件，在连接时进行过滤
- where 用于 指定过滤条件，对连接后的结果集进行进一步筛选

having 与 where 的区别？

答：

在 GROUP BY 分组查询过程中，where 是工作在 GROUP BY 之前，where 是对分组之前的数据进行筛选，无法使用聚合函数，having 是工作在 GROUP BY 之后，having 主要对分组之后的数据进行筛选，可以使用聚合函数

详细链接：MySQL GROUP BY

exists 和 in 的区别是什么？

答：

in 和 exists 一般用于子查询，语法如下：

1
2
3

select * from A where id in (select id from B);

select * from A where exists (select 1 from B where A.id = B.id);

内部工作原理区别：

in 是先执行内表，会把查询到内表数据缓存起来，然后会进行双重 for 循环，外层的 for 循环是遍历外表记录，内层的 for 循环是遍历内表记录，最后每一次 for 循环时在内存判断内表的记录与外表记录是否一致
exist 会 for 循环遍历外表的记录，每一次 for 循环都会进行一次内查询来判断数据是否匹配

性能区别：

如果查询的两个表大小相当，那么用 in 和 exists 性能差别不大
如果查询的两个表中一个是小表，一个是大表，in 适合于外表大而内表小的情况，exists 适合于外表小而内表大的情况

详细链接：SQL查询中 IN 与 EXISTS 的区别及性能分析

MySQL的约束有哪些？

答：

首先，约束的目的 在于 保证数据库里面数据的准确性和一致性
数据库的约束主要 有 6 大约束，分别是：主键约束（PRIMARY KEY）、外键约束（FOREIGN KEY）、唯一性约束（UNIQUE）、非空约束（NOT NULL）、默认约束（DEFAULT）、检查约束（CHECK），MySQL只支持前 5 钟约束，不支持检查约束
MySQL 支持的这些约束的作用如下：
- 主键约束的作用：唯一标识一条记录，不能重复也不能为空，即：UNIQUE + NOT NULL。一张表数据库表只能有一个主键，一般我们会针对 id 字段设置为主键
- 外键约束的作用：确保表与表之间引用的完整性，外键可以是重复的，也可以为空
- 唯一性约束的作用：保证字段在表中的数值是唯一的，如果插入相同字段值的记录，就会报唯一性约束的错误
- 非空约束的作用：保证字段不能为 NULL
- 默认约束的作用：给字段设置默认值，如果插入数据的时候，这个字段没有取值的话，就会用默认值

drop、truncate、delete的区别？

答：

delete 是删除表中的数据，我们可以 选择删除部分数据或者全部数据，delete 删除的数据是可以回滚的，delete 操作 并不是真的把数据删除掉了，而是给数据打上删除标记，目的是为了空间复用，所以 delete 删除表数据，磁盘文件的大小是不会缩减的
drop 是 删除表结构和表中所有的数据，truncate 是 只删除表中所有的记录，表结构并不会被删除，drop 和 truncate 删除的数据都是 不可以回滚的，并且删除表会立刻释放磁盘空间
从 删除表的性能 来看，drop > truncate > delete

详细链接：Delete、Drop、Truncate有什么区别？你知道吗？-truncate和delete的区别

联合查询中 union 和 union all 的区别是什么？

答：

UNION：在 合并结果集后 会 自动剔除重复的行
UNION ALL：会保留所有的重复行，不会进行去重操作

详细链接：MySQL UNION 的用法与实例

数据库三大范式是什么？

追问1：范式设计是为了解决什么问题？

追问 2：范式设计有什么缺点？

答：

问题一：

一范式 要求 所有属性都是不可分的基本数据项
- 解析：第一范式（1NF）要求 关系模式中的每个属性都是原子的，即属性不可再分。确保每个属性都是不可再分的基本数据项。比如：学生（学号，姓名，性别，出生年月日），如果认为最后一列还可以再分成（出生年，出生月，出生日），它就不是一范式了，否则就是
二范式 目的是 解决部分依赖
- 解析：第二范式（2NF）要求关系模式必须符合第一范式，并且非主属性必须完全依赖于候选关键字，而不是部分依赖。确保每个非主属性都完全依赖于候选关键字。比如：「表:学号、课程号、姓名、学分」，这个表包含了两种信息：学生信息、课程信息。由于非主键字段必须依赖主键，这里学分依赖课程号，姓名依赖与学号，所以不符合二范式。符合第二范式的做法是：「学生: student(学号,姓名)」、「课程:Course(课程号,学分)」、「选课关系: studentcourse(学号,课程号, 成绩)」
三范式 目的是 解决传递依赖
- 解析：第三范式（3NF）要求关系模式必须符合第二范式，并且不存在传递依赖，即：所有非主属性之间不能存在依赖关系。确保不存在非主属性对其他非主属性的传递依赖。举例来说：如果有一个关系模式 R(A,B,C)，其中：A→B（A 决定 B）、B→C（B 决定 C），那么存在传递依赖，因为 A 决定了 B，B 又决定了 C，从而导致 A→C 的传递依赖。比如：「表:学号,姓名,年龄,学院名称,学院电话」，上表属于第二范式，因为主键由单个属性组成（学号）。但是存在依赖传递：(学号)→(学生)→(所在学院)→(学院电话)。符合第二范式的做法是:「学生:(学号,姓名,年龄,所在学院)」、「学院:(学院，学院名称，电话)」

问题二：

数据库的范式是为了解决 数据冗余、数据不一致性、数据更新异常 和 插入异常 等问题
- 数据冗余：数据冗余是指数据库中存储了大量重复的数据，这不仅浪费了存储空间，而且可能导致数据不一致的问题。范式通过规定数据的结构和组织方式，使得每一份数据只需要被存储一次，从而避免了数据冗余
- 数据更新异常：更新异常是指当我们尝试更新一份数据时，可能需要在多个地方进行修改，而如果某一处修改被遗漏，就会导致数据不一致的问题。范式通过规定数据的组织和关联方式，使得每一份数据只需要被修改一次，从而避免了更新异常
- 插入异常：插入异常是指当我们尝试插入一份新的数据时，可能因为数据的组织和关联方式的问题，而无法进行插入。范式通过规定数据的组织和关联方式，使得任何合法的新数据都可以被顺利插入，从而避免了插入异常
- 保证数据的一致性和完整性：范式化的设计通过消除数据冗余和依赖关系，使得数据的一致性和完整性得到保证。当数据只存在于一个位置时，更新和修改数据更加简单和可靠。通过实施数据库范式，开发人员能够确保数据库架构更具可维护性和可扩展性，尤其在处理大型数据集时，范式化能够确保数据的一致性，并使数据库操作更高效

问题三：

范式化将数据分解为多个表，那么查询数据的时候，就需要进行更多的表连接操作，在应用中，进行表关联的成本是很高，也不适合分库分表的场景，所以有时候实际应用，设计表的时候会反范式的，比如说：可以通过字段冗余的设计，避免联表查询

详细链接：数据库逻辑设计之三大范式通俗理解 | Hsu Yeung 的博客

count(*)的性能比count(1)好吗？

答：

性能对比：

count(*)=count(1)>count(主键)>count(字段)

count(*) 其实等于 count(0) 处理。也就是说，当你使用 count(*)时，MySQL 会将 * 参数转化为参数 0 来处理，所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异

存储引擎

说一说执行一条SQL语句的全过程？

答：

SQL语句执行的大致流程

MySQL 执行一条查询 SQL 语句的时候，会经过 连接器、查询缓存、解析器、优化器、执行器、存储引擎 这些模块

首先 MySQL 的连接器 会 负责建立连接、校验用户身份、接收客户端的 SQL 语句
第二步 MySQL 会在 查询缓存中查找数据，如果命中直接返回数据给客户端，否则就需要继续往下查询，不过查询缓存功能在 MySQL 8.0 版本被删除了，原因是只要对这张表进行了写操作，这张表的查询缓存就会失效，所以在实际场景中，查询缓存的命中率其实不高
第三步 MySQL 的解析器 会 对 SQL 语句进行词法分析和语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型
第四步 MySQL 的优化器 会基于查询成本的考虑，会判断每个索引的执行成本，从中选择查询成本最小的执行计划
第五步 MySQL 的执行器 会 根据执行计划来执行查询语句，从存储引擎读取记录，返回给客户端

MySQL 存储引擎有哪些？

答：

MySQL 整体上分为 Server 层 和 存储引擎层，Server 层负责建立连接、分析和执行 SQL，存储引擎层负责数据的读取和存储
MySQL 常见的存储引擎有 InnoDB、MyISAM、Memory
- 我比较熟悉的是 InnoDB 引擎，它是 MySQL 默认的存储引擎，支持事务和行级锁，具有事务提交、回滚和崩溃恢复功能
- MyISAM 引擎我没有用过，但是我在学习的时候有了解过，它是 不支持事务和行级锁的，而且由于只支持表锁，锁的粒度比较大，更新性能比较差，我认为它比较适合读多写少的场景
- Memory 引擎我了解不多，大概知道 它是将数据存储在内存中，所以数据的读写还是比较快的，但是数据不具备持久性，我觉得适用于临时存储数据的场景

MyISAM 和 InnoDB 的区别？

答：

InnoDB vs MyISAM

InnoDB 引擎和 MyISAM 引擎在 数据存储上 有很大区别，InnoDB 引擎数据存储的方式采用的是 索引组织表，在索引组织表中，数据即索引，索引即数据，因此表数据和索引数据都存储在同一个文件中。MyISAM 引擎数据存储的方式采用的是堆表，在堆表的组织结构中，数据和索引分开存储，因此表数据和索引数据会分别放在两个不同的文件中存储
主键索引又叫聚簇索引，二级索引又叫非聚簇索引
- 通过二级索引 idx_name 只能定位主键值，需要额外再通过主键索引进行查询，才能得到最终的结果。这种 “二级索引通过主键索引进行再一次查询” 的操作叫作 “回表”
索引组织表有两个优势：
1. 在索引组织表 将索引和数据保存在同一个 B+ 树中，相比非聚簇索引每次查询都需要回表，因此从 聚簇索引中获取数据比非聚簇索引更快，查询数据会更快
2. 在索引组织表中，如果 记录发生了修改，则其他索引无须进行维护，除非记录的主键发生了修改，而当堆表的数据发生改变且位置发生了变更，那么所有索引中的地址都要更新，这非常影响性能。另外，InnoDB 引擎支持行级锁和事务，而 MyISAM 引擎都不支持，只支持表锁

详细链接：[09 索引组织表：万物皆索引](https://learn.lianglianglee.com/专栏/MySQL实战宝典/09 索引组织表：万物皆索引.md)

MySQL 为什么选择 InnoDB 作为默认引擎？

答：

最重要原因是 InnoDB 支持事务，其他存储引擎都不支持
InnoDB 引擎在 事务支持、并发性能、崩溃恢复 等方面具有优势，因此被 MySQL 选择为默认的存储引擎
- 事务支持：InnoDB 引擎提供了对事务的支持，可以进行 ACID（原子性、一致性、隔离性、持久性）属性的操作。MyISAM 存储引擎是不支持事务的
- 并发性能：InnoDB 引擎采用了 行级锁定 的机制，可以提供更好的并发性能，MyISAM 存储引擎只支持表锁，锁的粒度比较大
- 崩溃恢复：InnoDB 引擎通过 **redo log 日志 **实现了崩溃恢复，可以在数据库发生异常情况（如断电）时，通过日志文件进行恢复，保证数据的持久性和一致性。MyISAM 是不支持崩溃恢复的

用 count(*) 哪个存储引擎会更快？

答：

如果查询语句 没有 where 查询条件的话，用 MyISAM 引擎会比较快，因为 MyISAM 引擎的每张表会用一个变量存储表的总记录个数，执行 count 函数的时候，直接读这个变量就行了，而 InnoDB 引擎执行 count 函数的时候，需要通过遍历的方式来统计记录个数
如果查询语句 有 where 查询条件的话，MyISAM 和 InnoDB 引擎执行 count 函数的时候，性能都差不多，都需要根据查询条件一行行的进行统计

NULL 值是如何存储的？

答：

MySQL 存储一行数据的时候，会使用下面这个格式进行存储，其中 NULL 值列表就是用来保存 NULL 值的：

MySQL存储格式

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列：
- 二进制位的值为 1 时，代表该列的值为 NULL
- 二进制位的值为 0 时，代表该列的值不为 NULL
MySQL 行格式中会用 「NULL值列表」 来标记值为 NULL 的列，每个列对应一个二进制位，如果列的值为NULL，就会标记二进制位为 1，否则为 0，所以 NULL 值并不会存储在行格式中的真实数据部分
NULL 值列表 最少会占用 1 字节空间，当表中所有列都定义成 NOT NULL，行格式中就不会有 NULL 值列表这样可以至少节省 1 字节的空间

char 和 varchar 有什么区别？

追问：哪个性能更好？

答：

char 是 固定长度的字符串类型，它在数据库中占用固定的存储空间，无论实际存储的数据长度是多少，都会占用定义时指定的固定长度，如果实际存储的字符串长度小于定义的长度，系统会自动用空格填充
- 比如：如果定义一个 char(10) 类型的字段，即使实际数据只使用 5 字节，会自动填充 5 字节的空格，使得存储空间固定占用 10 字节
varchar 是 可变长度的字符串类型。实际存储时只占用实际字符串长度的空间，不会进行空格填充
- 比如：如果定义一个 varchar(10) 类型的字段，并存储了一个长度为5的字符串，那么它只会占用5个字节的存储空间，并且 还会额外用 1-2 字节存储「可变长字符串长度」的空间

追问：

站在 CPU 角度来看，理论上 CHAR 比 VARCHAR 更快，因为 CHAR 是固定长度的，而 VARCHAR 需要增加一个长度标识，处理时需要多一次运算
但性能是综合各种因素后的最终结果，当 Innodb buffer pool 小于表大小时，“磁盘读写” 成为了性能的关键因素，而 VARCHAR 更短，因此性能反而比 CHAR 高。但是当 Innodb buffer pool 足够大时，CHAR 和 VARCHAR 性能没有太大的差别了

详细链接：MySQL Innodb数据库性能实践——VARCHAR vs CHAR_char和varchar 在innodb中哪个索引效率高-CSDN博客

假如说一个字段是 varchar(10)，但它其实只有6个字节，那他在内存中占的存储空间是多少？在文件中占的存储空间是多少？

答：

内存会占用 10 字节，文件存储空间会占用 6 字节，并且还会额外用 1-2 字节存储 「可变长字符串长度」 的空间

如果硬件内存特别大，MySQL 缓存能否替代 redis？

答：

我觉得还是不能替代
MySQL 所有模块，比如：bufer pool 、日志技术、事务并发模块，都是 面向磁盘页 而设计的，因此其首要目标不是减少内存访问的代价，而是 I/O 代价，所以内存访问代价却并不是最优的选择，而 Redis 是 面向内存 而设计的数据库
MySQL在内存查询一个数据页的时候，都需要先查页表，也就是需要走 B+ 树的搜索过程，时间复杂度是O(logdN)，而 Redis 提供了很多种的数据类型，比如：用 Hash 数据对象的时候，可以在 O(1) 时间复杂度查到数据
MySQL 在更新数据的时候，MySQL 为了保证事务的隔离性，是需要加锁的，而 Redis 更新操作都是不需要加锁的，还有 MySQL 为了保证事务的持久性，还需要刷盘 redolog 日志和 binlog 日志，Redis 可以选择不持久化数据
因此，即使 buffer pool 无限大，MySQL 缓存的性能还是没有 Redis 好的

索引结构

MySQL有哪些索引类型？

答：

索引类型

我了解到 MySQL 支持 B+ 树索引、哈希索引、全文索引这三种索引类型。我比较常用的是 B+ 树索引，因为它是 InnoDB 引擎默认使用的 索引类型，支持排序、分组、范围查询、模糊查询 等功能
B+ 树索引、哈希索引、全文索引的区别：
- B+ 树索引：B+ 树索引是一种平衡树数据结构，它将数据按照索引键值有序地存储在树的叶子节点上，非叶子节点只存储索引键值的范围和指向下一层节点的指针，适合于范围查找、排序查询、等值查询的情况，并且性能稳定，因为 B+ 树保存千万级别的数据，树的高度依然维持在 3~4 层左右，也就是从千万级数据查询一条数据只需要 3~4 次的磁盘 I/O 操作就能查询到目标数据
- 哈希索引：哈希索引是通过哈希算法将键值（key-value）映射到哈希表中，再根据哈希表进行索引操作。哈希索引适合于等值查询，例如：根据主键查询某条记录，查询时间复杂度为 O(1)，效率非常高，但不支持排序、范围查询及模糊查询等
- 全文索引：全文索引是一种用于全文搜索的索引技术，可以对文本内容进行索引，支持关键词的模糊匹配和搜索。一般用于查找文本中的关键字，而不是直接比较是否相等，主要是用来解决 WHERE name LIKE "%aaaa%" 等针对文本的模糊查询效率低的问题

InnoDB 引擎的索引数据结构是什么？

答：

我了解到 InnoDB 引擎 是采用了 B+ 树 作为索引的数据结构。它的一些特性:
- 数据组织形式：InnoDB 存储引擎的主键索引 B+ 树的非叶子节点只存放索引键值和指向子节点的指针，不存储实际的数据，这里对应到MySQL中就是索引，叶子节点存储索引键值和行数据，所以 InnoDB 存储引擎的主键索引属于聚簇索引
- 叶子节点链表：所有叶子节点通过指针相连，形成一个双向链表，支持快速的顺序访问和范围查询
- 平衡树结构：所有叶子节点在同一层上，树的高度平衡，保证任何数据记录的查找、插入、删除和更新操作的路径长度相同，稳定性好

B+ 树的特性是什么？

答：

B+树的结构

B+ 树是一个多叉树，一个父节点，可以有多个子节点，主要的特性有三个：

B+ 树的中间节点不会存储数据，而只有叶子节点才会存储，中间节点只用于存储到叶子节点的路由信息（即索引），而且每个节点里的数据都是根据索引的值来顺序存放的
B+ 树的所有的叶子节点之间会通过双向指针串联在一起，构成一个双向链表，可以方便扫表和范围查询
B+ 查询性能稳定，因为所有叶子节点都在同一层，确保了所有数据项的检索都具有相同的 I/O 延迟，而且 B+ 树保存千万级别的数据，树的高度依然维持在 3~4 层左右，也就是 从千万级数据查询一条数据只需要 3~4 次的磁盘 I/O 操作就能查询到目标数据

B+ 和 B 树有什么区别？

答：

B+ 和 B 树都是多叉平衡树，每个节点包含多个键和多条链，主要的区别有这些：
- 数据存储：B 树所有节点都会 存储索引+数据，而 B+ 树只有叶子节点才会 存储数据，中间节点则只有索引，因此存储相同数据量的情况下， B+ 树可以比 B 树更矮胖，查询叶子节点的磁盘 I/O 次数会更少
- 范围查询：B+ 树叶子节点之间会通过双向指针串联在一起，构成一个双向链表，这种设计对范围查找非常有帮助，而 B树没有将所有叶子节点用链表串联起来的结构，只能通过中序遍历来完成范围查询，这会比 B+ 树范围查询涉及更多个节点的磁盘 I/O 操作，因此范围查询效率不如 B+ 树
- 查找效率：B树的优势是当你要查找的值恰好处在一个非叶子节点时，由于该节点也包含数据，查找到该节点就会成功并结束查询，最快可以在 O(1) 的时间代价内就查到，而 B+树 由于数据只在叶子节点，所以每次查询都需要从根节点搜索到叶子节点，从平均时间代价来看，会比 B+ 树稍快一些，但是 B+ 树的查询会更稳定，因为每次查询都是相同的 I/O 延迟

MySQL 为什么使用 B+ 树？

答：

B+ 树是多叉树，而平衡二叉树、红黑树是二叉树，在同等数据量下，平衡二叉树、红黑树高度更高，磁盘I/O 次数更多，性能更差，而且它们会频繁执行再平衡过程，来保证树形结构平衡（B+ 树 vs 平衡二叉树 vs 红黑树）
跳表和 B+树相比，跳表在极端情况下会退化为链表，平衡性差，而数据库查询需要一个可预期的查询时间，并且跳表需要更多的内存（跳表 vs B+ 树）
B 树和 B+树相比，B 树的数据存储在全部节点中，对范围查询不友好。非叶子节点存储了数据，导致内存中难以放下全部非叶子节点。如果内存放不下非叶子节点，那么就意味着查询非叶子节点的时候都需要磁盘 I/O（B 树 vs B+ 树）

为什么索引用 B+树？而不用红黑树？

答：

我觉得主要原因是随着数据量的增多，红黑树的树高会比 B+ 树的树高，这样查询数据的时候会面临更多的磁盘 I/O，查询性能没那么好。因为红黑树本质上是二叉树，而 B+ 树是多叉树，存储相同数量量的情况下，红黑树的树高会比 B+ 树的树高，由于 InnoDB 引擎的数据都是存储在磁盘上的，如果树的高度越高意味着磁盘 I/O 就越多，这样就会影响查询性能
另外，B+树叶子节点是通过双向链表组织的，可以很好的实现范围查询，而红黑树要实现范围查询需要通过中序遍历，这会比 B+树范围查询涉及更多个节点的磁盘 I/O 操作，因此范围查询效率不如 B+ 树
所以， B+ 树相比红黑树有两个优势
1. 第一个优势是 B+ 树随着数据的增多的时候树的高度会比红黑树低
2. 第二个优势是 B+树范围查询很方便，直接通过叶子节点的链表就能完成了，因此 InnoDB 存储引擎选择了 B+ 树作为索引

为什么索引用 B+ 树？而不用 B 树？

答：

我觉得主要有三个原因

B+树的磁盘读写代价更低：B+ 树只有叶子节点才会存放索引和数据，非节点只存放索引，而 B树所有节点都会存放索引和数据，因此存储相同数据量的情况下，B+ 树可以比 B树更矮胖，查询叶子节点的磁盘 I/O 次数会更少
B+树便于范围查询：MySQL是需要经常使用范围查询的，B+树所有叶子节点间会用链表进行连接，这种设计对范围查找非常有帮助，而 B树没有将所有叶子节点用链表串联起来的结构，只能通过中序遍历来完成范围查询，这会比 B+树范围查询涉及更多个节点的磁盘 I/O 操作，因此范围查询效率不如 B+ 树
B+树增删查改效率更加稳定：B+ 树有大量的冗余节点，这些冗余数据可以让 B+ 树在插入、删除的效率都更高，比如：删除根节点的时候，不会像 B树那样会发生复杂的树的变化。另外，B+树把所有的用户记录都放到了叶子节点这一层，因此查询、插入、删除数据都需要走到最后一层，这不同于 B 树可能在任意一层找到数据，所以 B+树更为稳定

所以，InnoDB 引擎的索引选择了 B+ 树

为什么索引用 B+ 树？而不用哈希表？

答：

MySQL 会有很多范围查询和排序的场景，虽然哈希表的搜索时间复杂度是 O(1)，但是由于哈希表的数据都是通过哈希函数计算后散列分布的，所以 哈希表索引不支持范围查询和排序操作，不支持联合索引最左匹配原则，如果重复键值比较多，还容易造成哈希碰撞导致效率进一步降低。而 B+ 树可以满足这些应用场景，因此选择了用 B+ 树索引

B+ 树有什么优点和缺点？

答：

优点：B+树有一个最大的好处是方便范围查询，B+树的叶子节点之间有链表，直接通过叶子节点链表就能方便的完成范围查询的工作，而 B树必须用中序遍历的方法来实现范围查询，这会比B+树范围查询涉及更多个节点的磁盘 I/O 操作，因此范围查询效率不如 B+ 树
缺点：B+树最大的性能问题是会产生大量的随机I/O，随着新数据的插入，叶子节点会慢慢分裂，逻辑上连续的叶子节点在物理上往往不连续，甚至分离的很远，但做范围查询时，会产生大量读随机IO。对于大量的随机写也一样，举一个插入key跨度很大的例子，如：7->1000->3->2000 新插入的数据存储在磁盘上相隔很远，会产生大量的随机写I/O

聚簇索引和非聚簇索（二级索引）有什么区别？

答：

聚簇索引和非聚簇索（二级索引）

聚簇索引和非聚簇索引（二级索引）最主要的区别：B+树叶子节点存放的内容不同
- 聚簇索引的 B+树叶子节点 存放的是 主键值 + 完整的记录
- 非聚簇索引的 B+树叶子节点 存放的是 索引值 + 主键值
如果查询语句的查询条件用了二级索引，但是 查询的数据不是主键值，也不是二级索引值，这时在二级索引找到主键值后，就需要回表才能查找到数据，需要扫描两次 B+ 树。如果查询的数据是主键值 时，因为只在二级索引就能查询到，这时候就会用到 覆盖索引，不需要回表，只需要扫描一次B+树

什么是覆盖索引？

答：

二级索引的叶子节点存放的是 索引+主键 id，如果查询的列能够在二级索引中全部查询到，那就不需要回到主键索引去查行记录了，这种不需要回表的过程，就叫覆盖索引。这种查询方式效率会比较高，只需要查二级索引这一棵 B+ 树

eg：假设有联合索引 (a, b)，当执行下列查询时会发生覆盖索引：

1
2
3

select a, b, id from table where a = ? and b = ?;
select a, b from table where a = ? and b = ?;
select a from table where a = ? and b = ?;

详细链接：MySQL 的覆盖索引与回表两大类索引聚簇索引普通索引示例建表填充数据索引存储结构聚簇索引（ClusteredIndex - 掘金

什么情况下会回表？

答：

在使用二级索引进行查询的时候，如果查询的列，不能在二级索引中全部查询到，那么就会发生回表的过程，先通过二级索引的值查到聚簇索引值（即主键 id），再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低

eg：

需要回表的情况

需要查询两次B+树才能得到结果

insert 操作对 B+ 树结构的改变是怎么样的？

答：

B+ 树的数据都是 有序的，所以：

如果我们 使用主键是顺序递增，那么 每次插入的新数据就会顺序插入到叶子节点最右边的节点里，如果该页面满了，就会自动开辟一个新页面，将新数据插入到新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高
如果我们 使用主键不是顺序递增，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这时候为了保证B+树的有序性，要移动其它数据来满足新数据的插入。如果该页面满了，就发生页分裂，这时候要从一个页面复制数据到另外一个页面，目的是保证后一个数据页中的所有行主键值比前一个数据页中主键值大，页分裂可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率

所以，我们在设计主键的时候，最好采用自增的方式，或者顺序递增主键值

假设有一张表有两千万的数据，B+树的高度是多少？怎么算的？

（了解）

答：

假设：

非叶子节点内指向其他页的数量为 x
叶子节点内能容纳的数据行数为 y
B+ 数的层数为 z

表总数会等于 x 的 z-1 次方与 y 的乘积：Total=(x^(z-1))*y

具体要看数据库表的字段多不多，以及字段类型，假设一行记录是 1KB 大小，那么 2000 万的数据表， B+树大概是三层高度
MySQL 数据页的大小是 16 KB，去掉一些头信息，大概有 15KB 是可以存储数据
在索引页中主要记录的是主键与页号，假设是主键 id 类型是 bigint，那就是 8 字节，页号固定为 4字节，那么索引页中的一条数据也就是 12byte。那么一个索引页可以存储 15*1024/12x1280 个页号
叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如：字字段的类型，字段的数量。每行数据占用空间越大，页中所放的行数量就会越少，假设按一条行数据 1KB 来算，那一页就能存下 15 条，15KB/1Kb = 15
根据的公式， Total = (x^(z-1))*y，已知 x = 1280，y = 15，假设 B+ 树是三层，那就是 z = 3，Total =(1280^2)*15=24576000（约 2.45kw）

索引应用

MySQL有哪些索引？

答：

我了解到 MySQL 有 主键索引、唯一索引、普通索引、前缀索引、联合索引 这几种索引。InnoDB 引擎会要求每一张数据库表都必须要有一个主键索引，比如：表里的 id 字段就是主键索引，然后针对查询比较频繁的字段，我们可以对这个字段建立普通索引，如果是多个字段的话，可以考虑建立联合索引，利用索引覆盖的特性提高查询效率
对于长文本、字符串等类型的字段，比如：文章标题、商品名称等，我们可以只对这些字段的前缀部分建立索引，也就是建立前缀索引，这样可以减少索引的存储空间

MySQL主键是聚簇索引吗？

答：

是的，主键是聚簇索引，我们创建数据库表的时候，通常都会 对 id 字段设置为主键索引，InnoDB 在创建聚簇索引时，默认会使用主键作为聚簇索引的索引键

主键为什么不推荐有业务含义？

答：

我觉得原因两个：

第一个业务会有变动的可能性，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现,等需要变动的时候，去更改主键是成本很高的一件事情，不如设计阶段就规避不用有业务含义的主键
第二个是业务含义的主键可能不是顺序自增的，有可能会发生页分裂问题，从而影响性能

主键是用自增还是UUID？

答：

用自增 id 比较好，因为 UUID 是随机值，在数据插入的过程中，会导致索引树发生页分裂的问题，会影响性能，而且 UUID 是字符串类型，长度比较长，占用内存比较大，而页的大小是固定的，这样会导致索引树的高度越高，查询的时候会发生的磁盘 IO 次数也越多，性能也就更低
但是 自增 id 在分库分表环境下就不适用了，因为没办法保证全局唯一，这时候就需要考虑用 雪花算法 来作为主键了

普通索引和唯一索引有什么区别？哪个更新性能更好？

答：

普通索引列的值是可以重复的，而唯一索引列的值是必须唯一的，当我们对唯一索引插入了一条重复的值，会因为唯一性约束而报错
我认为普通索引的更新性能会更好，因为普通索引在更新的时候，如果更新的数据页不在内存的话，可以直接把更新操作缓存在 change buffer 中，更新操作就结束了，但是，唯一索引因为需要有唯一性约束，如果更新的数据页不在内存的话，需要从磁盘读取对应的数据页到内存，判断到没有冲突，这里会涉及磁盘随机 I/O 的访问
- 普通索引因为能使用 change buffer 特性，所以普通索引的更新相比于唯一索引，减少了随机磁盘访问，所以更新性能更好

主键怎么设置？

追问：假如你不设置会怎么样？

答：

在创建表的时候，对 id 列设置为 PRIMARY KEY，那么 id 列就是主键索引了

create table table_name (
	id int PRIMARY KEY,
    column1 datatype,
    column2 datatype,
    ...
);

追问：

如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键，如果这个条件也没有达成的话，InnoDB 将自动生成一个隐式 rowid 列作为聚簇索引的索引键

介绍一下什么是外键约束？

答：

外键约束

外键就是 「从表」 中用来引用 「主表」 中数据的那个公共字段，外键约束确保了数据的引用完整性，也就是「从表」 中的外键必须存在于 「主表」 的主键中，如果发现要删除的主表记录，正在被 「从表」 中某条记录的外键字段所引用，MySQL 就会提示错误，从而保证了两个表中数据的一致性

外键有什么优劣势？

答：

外键能够 保证数据的一致性和完整性，通过设置外键，数据库就会判断数据的完整性，不需要在应用代码里实现
有了外键之后，每次增删改都需要额外检查外键约束，会占用数据库的计算资源，影响增删改的性能，而且还需要额外获取锁，在高并发场景下很容易发生死锁的问题，另外，外键也不适合分库分表的场景，外键难以跨越不同数据库来建立关系
因此，基于 性能开销、锁竞争、分库分表 的考虑，一般项目中，很少用外键约束，都是在应用层面完成检查数据一致性的逻辑，就像大厂会使用RC（读已提交隔离级别）来替代RR（可重复读隔离级别）一样，会尽可能的降低锁的发生，一方面提升性能，一方面降低死锁概率

为什么要建索引？

答：

如果没有建立索引，我们查询数据的话，搜索时间复杂度是 O(n)，这样的查询效率还是比较低的，为了提高查询效率，我们可以建立索引
建立了索引后数据都会按照顺序存储，这时候我们可以利用类似二分查找的方式快速查找数据，B+ 树索引是多叉树，搜索时间复杂度是 O(logdN) ，这样就提高了查询速度，除此之外还可以 避免外部排序和使用临时表等问题，以及将随机 I/O 变为顺序 I/O
- 解释：
  - B+ 树索引避免外部排序，因为数据天然有序，查询时不需要额外的排序过程
  - 避免临时表的使用，是因为索引扫描和顺序访问减少了中间结果存储的需求
  - 随机 I/O 转为顺序 I/O，得益于 B+ 树叶子节点的顺序存储和链表连接

我们一般选择什么样的字段来建立索引？

答：

可以对频繁用于 WHERE 查询条件的字段建立索引，这样能够提高整张表的查询速度，如果查询条件不是个字段，可以考虑建立联合索引
对于经常用于排序、分组的字段建立索引，这样在查询的时候就不需要再去做一次排序了，因为建立索引之后在 B+树中的数据都是排序好的
不过，对于一些区分度不高的字段，比如：性别字段，只有男女，不建议建立索引，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据，在这种情况下，MySQL的优化器发现某个值在表中出现的比例很高的时候，它一般会忽略索引，进行全表扫描，这时候建立的索引就没有起到作用，反而还占用了存储空间

索引越多越好吗？

答：

不是的，索引虽然能提高查询效率，但是多建立一个索引，就意味着新生成一个 B+ 树索引，是需要占用存储空间的，特别是在表数据量非常大的时候，索引占用的空间越大
还有，索引越多数据库的写入性能会下降，因为每次对表进行增删改操作的时候，都需要去维护各个 B+ 树索引的有序性

什么时候不用索引更好？

答：

建立了索引，虽然能提升查询效率，但是它带来了两个代价：
1. 第一个是 空间代价，因为需要多构建一颗 B+ 树，会占用磁盘空间
2. 第二个是 更新时间代价，每次增删改索引，都需要动态维护 B+ 树，以满足 B+ 树的有序性
所以，我认识到如果一张表经常被增删改的话，也就是 写多读少的场景下，不建立索引会更好，因为这时候维护索引的开销可能会超过索引带来的性能提升
还有一点，如果表中某个列的值高度重复，那么建了索引也没有用，优化器会选择全表扫描，这样建立的索引会占用存储空间，也会影响增删改的效率，选择不用索引会更好

字段为什么要定义为NOT NULL？

答：

如果查询中包含可为 NULL 的列，对 MySQL 的优化器来说更难优化，因为可为 NULL 的列使得 索引、索引统计和值 比较都更复杂
如果某列存在 NULL 的情况，可能导致 count() 等函数执行不准确，因为 count 不会统计值为 NULL 列
NULL 值是一个没意义的值，但是它会占用物理空间，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表

索引怎么优化？

答：

我用过这几种优化的方式：

对于只需要查询几个字段数据的 SQL 来说，我们可以对这些字段建立联合索引，这样查询方式就变成了覆盖索引，避免了回表，减少了大量的 I/O 操作
我们的主键索引最好是递增的值，因为我们索引是按顺序存储数据的，如果主键的值是随机的值，可能会引发页分裂的现象，页分裂会导致大量的内存碎片，这样索引结构不紧凑了，就会影响查询效率
我们要避免写出发生索引失效的 SQL的语句，比如不要对索引进行计算、函数、类型转换操作，联合索引要能正确使用需要遵循最左匹配原则等等
对于一些大字符串的索引，我们可以考虑用前缀索引只对索引列的前缀部分建立索引，节省索引的存储空间，提高查询性能

建立了索引，查询的时候一定会用到索引吗？

答：

不是的
我了解到即使查询使用到了索引，也是可能不走索引的，比如：
- 当我们查询语句对索引字段进行左模糊匹配、表达式计算、函数、隐式类型转换操作，这时候查询语句就无法走索引了，查询方式就变成了全表扫描的方式。还有 我们使用联合索引进行查询的时候，如果没有遵循最左匹配原则，也是会发生索引失效的
- 优化器是基于成本考虑来选择查询的方式，在使用二级索引进行查询的时候，优化器会计算回表的成本和全表扫描的成本，如果回表的代价太高，优化器会选择不走索引，而是走全表扫描

如果我定义了一个 varchar 类型的日期字段，并且有一个数据是 '20230922’，如果这个日期字段上有索引，那如果我查询的 where 条件是 where time=20230922 不加单引号，还会命中索引吗？为什么？

答：

不会命中索引
- 因为 MySQL 在遇到字符串和数字比较的时候，会发生隐式类型转换，会将字符串的对象转为数字，这个转换的过程实际上会涉及到函数。你说的这个查询，日期字段是字符串，那么发生隐式类型转换的时候，就会作用在日期这个索引字段上，对索引进行函数计算的话，是会发生索引失效的

MySQL的最新版本解决了索引失效的哪些情况了吗？

答：

我了解到 MySQL 8.0 可以给字段增加函数索引，这个新特性可以解决对索引使用函数的时候，索引失效的问题
还有一个新特性是索引跳跃式扫描，5.7 版本之前，使用联合索引的时候，如果不满足最左匹配原则，就会发生索引失效，而 8.0出了索引跳跃式扫描特性之后，即使没有遵循最左匹配原则，部分场景下，依然可以使用联合索引

什么是最左匹配原则？

答：

假设有一个 (a，b，c) 联合索引，它的存储顺序是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。由于这个的特性，在使用联合索引时，存在最左匹配原则
具体的规则：
- MySQL 会从联合索引从最左边的索引列开始匹配查询条件，然后依次从从左到右的顺序匹配，如果查询条件没有使用到某个列，那么该列右边的所有列都无法使用走索引
- 当查询条件中使用了某个列，但是该列的值包含范围查询，范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引
- 所以，我们在使用联合索引的时候，要遵守最左匹配原则，否则可能会出现部分索引字段走不了索引

建立联合索引有什么需要注意的？

答：

最好把 区分度比较大 的字段放在联合索引最左侧，有助于提高索引的过滤效果，比如：UUID 这类字段就比较适合排在联合索引列的靠前的位置
如果 区分度很低 的字段放在了联合索引最左侧，有可能会导致查询优化器会选择全表扫描，而不走索引了

了解索引下推吗？什么情况下会下推到引擎去处理？

答：

索引下推：索引下推能够 减少二级索引在查询时的回表操作，提高查询的效率，因为它 将 Server 层部分负责的事情，交给存储引擎层去处理了
举个例子：联合索引 (a，b，c)，查询条件为 a=? and c=? 的时候，由于联合索引的最左匹配原则，c 是无法走索引的，在没有索引下推机制之前，查询语句走二级索引的时候，需要回表读取 c 的值，然后在 server层进行过滤，有了索引下推机制后，即使 c 无法走索引，但是由于 c 在二级索引里，那么将过滤 c的工作从 server 层下推到存储引擎层，这样直接在二级索引里过滤满足 c 条件的记录，减少了回表的次数
使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引 (age,reward) 里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作

联合索引 (a, b, c)，下面的查询语句会不会走索引？如果走具体是哪些字段能走？

1. select * from T where a=1 and b=2 and c=3;

2. select * from T where a=1 and b>2 and c=3;

3. select * from T where c=1 and a=2 and b=3;

4. select * from T where a=2 and c=3;

5. select * from T where b=2 and c=3,

6. select (a,b) from T where a=1 and b>2

答：

遵循最左匹配原则，所以 a、b、c 三个字段都可以走索引，查询方式是在联合索引找到主键值后，会回主键索引找完整的数据行
根据最左匹配原则，范围查询后面的字段无法使用索引，所以 a、b 可以走索引，c 无法走索引，不过 c 可以进行索引下推
a、b、c 都能走索引，因为 where 查询条件字段的顺序并不会影响，MySQL 优化器会帮我们调整字段的查询顺序，所以也是符合最左匹配原则的
a 能走索引，根据最左匹配原则，c 无法走索引，但是 c 可以被索引下推
根据最左匹配原则，b、c都无法走索引
a 和 b 都能走索引，查询方式是 覆盖查询，不需要回表

where a>1 and b=2 and c<3 怎么建立索引？

答：

我会创建（b、a、c）联合索引或者（b、c、a）联合索引，因为这两种联合索引都可以有 2 个字段走索引
比如：
- 创建（b、a、c）联合索引，b和a都能走索引，c字段虽然无法走索引，但是可以进行索引下推，这样会减少回表的次数
- 创建（b、c、a）联合索引，b和c都能走索引，a字段虽然无法走索引，但是可以进行索引下推，这样会减少回表的次数

where a=? and b=? order by c 怎么建立索引？

答：

这里有 order by 排序，我们尽量要用索引来避免额外排序的操作，可以建立（a，b，c）联合索引，这样 c 排序的时候，就不需要额外排序了，天然利用了索引的有序性，避免 filesort

filesort 通常出现在需要排序结果但无法直接利用索引顺序的情况下。它是一个逻辑上的排序算法，而不是一个专门的文件存储方法

where a > 100 and b = 100 and c = 123 order by d 怎么建立联合索引？

答：

我觉得建立（b、c、d、a）顺序的联合索引比较好，这时候 b 和 c 字段都能走索引，而且 d 能利用索引有序性，避免 filesort，最后的 a 字段虽然无法走索引，但是可以利用索引下推，减少回表的次数

select b from table where a = 10 and c > 20 怎么创建索引？

答：

可以考虑 创建（a，c，b）顺序的联合索引，这时候查询的时候，a 和 c 既能都走索引，也能利用索引覆盖的特性，避免了回表

select id, name from XX where age > 10 and name like ‘xx%’，有联合索引（name, age），说一下查询过程？

答：

联合索引的顺序是先 name，再age，结构上是先根据 name 排序，name 相等的情况下再根据 age 排序。所以优化器需要先匹配 name，name 这时候是 右模糊查询，并不会发生索引失效，所以这条 SQL 是能走联合索引的、具体的话，只有 name 能走索引，这是因为由于 name 右模糊查询后，age 字段的值并不是有序的，因此 age 无法走索引，但是 age 可以进行索引下推
最后查询的字段是 id 和 name，这两个字段都能在联合索引上查找到，所以不需要回表，是索引覆盖查询

where id NOT IN (?,?,?) 会走索引吗？

答：

要看查询成本，如果走某个索引花费的随机 I/O 从聚簇索引顺序查（顺序I/O）的成本都还要高，那还不如直接去全表扫描
举例：num 字段（非唯一二级索引）只包含 3个值，1、2、3，3 只有几行，而 1、2 各有 100w 行，如果查询条件是 NOT IN(1, 2) 会走索引，如果查询条件是 NOT IN(3) 不会走索引

如果查询条件中包含索引列和非索引列，MySQL的具体查询流程是什么样的？

答：

查询过程先按索引去二级索引 B+树查，然后拿到主键 id，回表到主键索引 B+ 树再过滤非索引列，查询过程会查 2 个 B+树，涉及回表的过程

事务

MySQL事务有什么特性？

答：

MySQL 事务有 ACID 四大特性，分别是 原子性、一致性、隔离性、持久性

原子性 的意思是 事务中的所有操作要么全部完成，要么全部不完成，不会结束在中间某个环节，原子性是由 undo log 日志保证的
一致性 的意思是 事务执行前后，数据库的状态必须保持一致性，一致性是由通过持久性 + 原子性 + 隔离性这三个共同保证的
隔离性 的意思是 许多个事务并发读写数据库，可以防止多个事务并发读写同一个数据的时候，导致数据不一致问题的发生，隔离性是由 MVCC 和锁保证的
持久性 的意思是 保证事务完成后对数据的修改就是永久的，不会因为系统故障而丢失，持久性是由 redo log 日志保证的

事务的隔离性如何保证？

答：

事务的隔离性是由 MVCC 和锁保证的
- 可重复读隔离级别下的 快照读（普通select），是通过 MVCC 来保证事务隔离性的，当前读（update、select … for update）是通过 行级锁 来保证事务隔离性的

注：

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

详细链接：MySQL 日志：undo log、redo log、binlog 有什么用？ | 小林coding

事务的持久性如何保证？

答：

事务的持久性是由 redo log 保证的，因为 MySQL 通过 WAL（先写日志再写数据）机制，在修改数据的时候，会将本次对数据页的修改以 redo log 的形式记录下来，这个时候更新就算完成了，buffer pool 的脏页会通过后台线程刷盘，即使在脏页还没刷盘的时候发生了数据库重启，由于修改操作都记录到了 redo log，之前已提交的记录都不会丢失，重启后就通过 redo log，恢复脏页数据，从而保证了事务的持久性

事务的原子性如何保证？

答：

事务的原子性是通过 undo log 实现的，在事务还没提交前，历史数据会记录在 undo log 中，如果事务执行过程中，出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据，将数据恢复到事务开始之前的状态，从而保证了事务的原子性

MySQL 事务和 Redis 事务有什么区别？

答：

MySQL 事务 能够实现 ACID 四大特性，而 Redis 事务没保证原子性和持久性
Redis 事务没有回滚功能，没办法实现跟 MySQL 事务一样的原子性，就是没办法保证事务执行期间，要不全部失败，要不全部成功，Redis 事务执行过程中，如果中途有命令执行出错了，不会停止和回滚，而是继续执行，那么就可能出现半成功的状态
Redis 不管是 AOF 模式，还是 RDB 快照，都没办法保证数据不丢失，所以 Redis 事务不具有持久性
- Redis 的 AOF（Append Only File）模式和 RDB（Redis Database）快照是两种持久化机制，用于将 Redis 中的数据保存到磁盘，以防止数据因服务器重启或宕机而丢失
  - RDB 持久化会将 Redis 中的内存数据在某个时间点完整地保存为二进制快照文件（.rdb 文件）
  - AOF 持久化会将每次对 Redis 数据的修改操作（写入、更新、删除）以日志形式追加写入到一个文件中（默认是 appendonly.aof 文件）

MySQL事务隔离级别有哪些？分别解决哪些问题？

答：

MySQL 默认隔离级别是 可重复读，除此之外， MySQL 还支持 读未提交、读提交、串行化 这三个隔离级别
- 脏读：读到其他事务未提交的数据
- 不可重复读：前后读取的数据不一致
- 幻读：前后读取的记录数量不一致
按隔离水平从高到低：
我了解到事务并发问题存在 脏读、不可重复读、幻读 这三种，不同的隔离级别，解决的问题也各不同的
1. 读未提交：一个问题都没有解决
  - 指一个事务还没提交时，它做的变更就能被其他事务看到
2. 读已提交：避免了脏读问题，但是还存在不可重复读和幻读这两个问题
  - 指一个事务提交之后，它做的变更才能被其他事务看到
3. 可重复读：避免了脏读和不可重复读的问题，不过对于幻读问题是很大程度上避免了，没有完全避免
  - 指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL 的 InnoDB 引擎的默认隔离级别
4. 串行化：是所有问题都可以避免，但是事务的并发性能是最差的
  - 会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

串行化隔离级别是通过什么实现的？

答：

串行化隔离级别所有 SQL 都会加行级锁，包括：普通的 select 查询，都会加 S 型的 next-key 锁。其他事务就没办法对这些已经加锁的记录进行增删改操作了，从而 避免了脏读、不可重复读和幻读现象，性能是隔离级别中最差的，没有 MVCC 机制，读写操作没办法并发

脏读和幻读有什么区别？

答：

脏读：是一个事务读到了另一个未提交事务修改过的数据，如果另外一个事务回滚了，刚才读到的数据就与数据库里的数据不一致了
幻读：是前后两次的查询的结果集的数量是不同，比如，如果 select 执行了两次，但第二次返回了第一次没有返回的行数据，则该行是 “幻像” 行

MySQL默认的隔离级别是什么？怎么实现的？

答：

MySQL 默认的隔离级别 是 可重复读
select 查询是通过 MVCC 实现的，在 MVCC 实现中，每条记录都会保存多个版本，每个版本都有一个版本号，事务在读取数据时，会根据事务开始时的版本号来读取数据，从而保证了事务的隔离性。可重复读隔离级别是在开启事务后，执行一条 select 语句的时候，会生成一个 Read View，后续事务查询数据的时候都在复用 Read View，所以保证了事务期间多次读到的数据都是一致的

介绍一下 MVCC？

答：

MVCC 是 多版本并发控制，是 通过记录历史版本数据，解决读写并发冲突问题，避免了读数据时加锁，提高了事务的并发性能
MySQL 将历史数据存储在 undo log 中，结构逻辑上类似一个链表，MySQL数据行上有两个隐藏列，一个是事务ID，一个就是指向 undo log 的指针
事务开启后，执行第一条 select 语句的时候，会创建 ReadView，ReadView 记录了当前未提交的事务，通过与历史数据的事务 ID 比较，就可以根据可见性规则进行判断，判断这条记录是否可见，如果可见就直接将这个数据返回给客户端，如果不可见就继续往 undo log 版本链查找第一个可见的数据

MVCC是如何判断行记录对某一个事务是否可见？

答：

Read View 有四个重要的字段：
- m_ids：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务
- min_trx id：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值
- max_trx_id：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是：全局事务中最大的事务 id 值 +1
- creator_trx_id：指的是 创建该 Read View 的事务的事务 id
我们每一条记录都有 两个隐藏列，一个是 事务 id，一个是 指向历史数据 undo log 的指针，然后 Read View 有四个字段，分别是：创建 Read View 的事务 id、活跃事务 id 列表、活跃事务 id 列表中最小的 id、下一个事务的 id。主要有这几种判断规则:
1. 如果 记录的事务 id 小于活跃事务 id 列表中最小的 id，就说明该记录是在创建 Read View 前就生成好了，所以该记录是当前事务是可见的
2. 如果 记录的事务 id 大于下一个事务的 id，就说明该记录是在创建 Read View 后才生成的，所以该记录是当前事务是不可见的
3. 如果 记录隐藏列的事务 id 在最小的 id 和下一个事务的 id 之间，这时候就需要看记录的事务 id 是否在活跃事务 id 列表中：
  1. 如果 记录的事务 id 在活跃事务 id 列表中，说明修改该记录的事务还没提交，所以该记录是不可见的
  2. 如果 记录的事务 id 不在活跃事务 id 列表中，说明修改该记录的事务已经提交了，那么该记录就是可见

读已提交和可重复读隔离级别实现 MVCC 的区别？

答：

读已提交和可重复读隔离级别都是由 MVCC 实现的，它们的区别在于 创建 Read View 的时机不同
- 读已提交隔离级别在事务开启后，每次执行 select 都会生成一个新的 Read View，所以每次 select 都能读已提交隔离级别在事务开启后，看到其他事务最近提交的数据
- 可重复读隔离级别在事务开启后，执行第一条 select 时生成一个 Read View，然后整个事务期间都在复用用这个 Read View，所以一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的

为什么互联网公司用读已提交隔离级别？

答：

读已提交的 并发性能更好，因为读已提交没有间隙锁，只有记录锁，发生死锁的概率比较低。然后互联网业务对于幻读和不可重复读的问题都是能接受的，所以 为了降低死锁的概率，提高事务的并发性能，都会选择使用读已提交隔离级别

可重复读隔离级别是如何解决不可重复读的？

答：

MySQL 提供了两种查询方式，一种是 快照读，就是 普通 select 语句，另外一种是 当前读，比如 select for update 语句。不同的查询方式，解决不可重复读问题的方式是不一样的
- 针对快照读的话，是通过 MVCC 机制来解决的，在可重复读隔离级别下，第一次 select 查询的时候，会生成 readview，在第二次执行 select 查询的时候，会复用这个 readview，这样前后两次查询的记录都是一样的，不会读到其他事务更新的操作，这样就不会发生不可重复读的问题了
- 针对当前读的话，是靠行级锁中的记录锁来实现的，在可重复读隔离级别下，第一次 select for update 语句查询的时候，会对记录加 next-key 锁，这个锁包含记录锁，这时候如果其他事务更新了加了锁的记录，都会被阻塞住，这样就不会发生不可重复读的问题了

可重复读隔离级别是怎么解决幻读的？

答：

MySQL 提供了两种查询方式，一种是快照读，就是普通 select 语句，另外一种是当前读，比如 select for update 语句。不同的查询方式，解决幻读问题的方式是不一样的
针对快照读的话，是通过 MVCC 机制 来解决的，在可重复读隔离级别下，第一次 select 查询的时候，会生成 readview，在第二次执行 select 查询的时候，会复用这个 readview，这样前后两次查询的结果集都是一样的，不会读到其他事务新插入的记录，这样就不会发生幻读的问题了
针对当前读的话，是靠 行级锁中的间隙锁 来实现的，在可重复读隔离级别下，第一次 select for update 语句查询的时候，会对记录加 next-key 锁，这个锁包含间隙锁，这时候如果其他事务往这个间隙插入新记录的话，都会被阻塞住，这样就不会发生幻读的问题了

可重复读隔离级别解决了什么问题？有没有完全解决幻读？

答：

可重复读隔离级别解决了脏读、不可重复读问题，幻读也很大程度上避免了，但是我觉得并没有完全解决幻读，在一些特殊的场景，还是会发生幻读的问题

可重复读隔离级别为什么不能完全避免幻读？什么情况下出现幻读？

答：

在可重复读隔离级别场景下，当 先快照读再当前读 的场景下 可能会出现幻读的问题
比如说：这个场景，事务 A 通过快照读的方式查询 id=5 的记录，此时数据库没有这条记录，然后事务 B 向这张表中新插入了一条 id =5 的记录并提交了事务。接着，事务 A 对 id=5 这条记录进行了更新操作，在这个时刻，这条新记录隐藏列中的事务 id 就变成了事务 A 的事务 id，这时候事务 A 再使用 select 语句去查询这条记录时就可以看到这条记录了，这里事务 A前后两次查询的结果集合数不一样了，于是就发生了幻读
我还知道另外一个场景，事务 A 通过快照读的方式查询 id 大于 100 的记录，假设这时候有1条记录，然后事务 B 插入了 id = 200 的记录并提交了事务，接着事务 A 通过当前读的方式查询 id 大于 100 的记录，这时候就会得到 2 条记录，事务 A 前后两次查询的结果集合数不一样了，就发生了幻读
我觉得上面这两种发生幻读的场景，也是可以避免的，就是 尽量在开启事务之后，马上执行 select … for update 语句，因为它会对记录加临键锁（next-key 锁），这样就可以避免其他事务插入一条新记录，就避免了幻读的问题

可重复读隔离级别，MVCC 完全解决了不可重复读问题吗？

答：

不可重复读：代表前后两次查询的记录的值不一样了
如果前后两次查询都是快照读，就是普通的 select 的话，那就不会产生不可重复读的问题的。但是如果第一次查询是快照读，第二次查询是当前读，那么就可能会发生不可重复读的问题

一个事务中有特别多SQL的弊端？

答：

锁是事务提交的时候才释放的，那么长事务会导致锁持久的时间过长，容易导致大量的死锁和锁超时的问题
执行事务中每条增删改SQL会产生 undo log 日志，那么长事务就会导致 undo log 日志堆积很多，占用存储空间，也会导致回滚的时间过长
长事务执行时间长，容易造成主从延迟，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟
在长事务中，连接可能会被持续打开，这会占用数据库连接池的资源，可能导致连接池被占满

锁

详细说一下MySQL数据库中锁的分类？

答：

根据锁粒度的不同， MySQL 的锁可以分为 全局锁、表级锁、行级锁
我比较熟悉的是表级锁和行级锁，比如：我们对一张表结构进行修改的时候，MySQL 就会对这张表加一个元数据锁，元数据锁是属于表级锁
行级锁目前只有 InnoDB 存储引擎实现了，MyISAM 存储引擎是不支持行级锁的，只有表锁。InnoDB 存储引擎实现的行级锁主要有记录锁、间隙锁、临键锁、插入意向锁这些，当我们对表记录进行 select for update，或者 增删改 的时候，都会对记录加 行级锁

MySQL怎么实现乐观锁？

答：

可以在数据库表增加一个 版本号 字段，利用这个 版本号字段 在数据库中实现乐观锁
- 具体的实现：每次更新数据的时候，都要带上版本号，同时将版本 + 1，比如：现在要更新 id = 1，版本号为 2 的记录。这时候先要获取 id = 1 的版本号，然后更新语句写成 update table set name = "小明" , version = version + 1 where id = 1 and version = 2;
就能更新成功，如果不相等则不进行更新，然后需要重新获 如果这个版本号与表记录中的版本号一致的话，取该记录的最新版本号，然后再尝试更新数据

在线上修改表结构，会发生什么？

答：

线上环境可能存在很多事务都在读写这张表，如果对这张表进行了表结构修改，就会发生阻塞
原因是：有事务对这张表进行读写操作的时候，会生成元数据读锁，而修改表结构的时候，会生成元数据写锁，这时候就产生了读写冲突，所以修改表结构的操作就会阻塞，并且后续事务的增删改操作都会阻塞

创建索引的时候会锁表吗？

答：

会的，创建索引的时候会加 MDL写锁（元数据写锁），如果这时候有其他事务对这张表进行增删查改的话，这些事务就都会被阻塞
原因是：有事务对这张表进行读写操作的时候，会生成MDL读锁，这时候就产生了读写冲突

InnoDB存储引擎中的行级锁有哪些？

答：

InnoDB 实现的行级锁有 记录锁、间隙锁、临键锁、插入意向锁。我们在使用 增删改或者锁定读语句 的时候，都会对记录加行级锁
- 记录锁：可以避免其他事务对该记录进行删除和更新操作
  - Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：
    - 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与S 锁兼容），但是不可以对该记录加 X型记录锁（S 型与 X 锁不兼容）
    - 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁 (S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）
- 间隙锁：可以避免其他事务往间隙里插入新记录
  - Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象
  - 假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生
- 临键锁：是 记录锁和间隙锁的组合，所以它既可以免其他事务对该记录进行删除和更新操作，也可以避免其他事务往间隙里插入新记录
  - 就是 锁定一个范围，并锁定记录本身
- 插入意向锁：插入意向锁和间隙锁是互斥的关系，其他事务插入的时候，发现插入位置的下一条记录有间隙锁的话，才会生成的插入意向锁，并且这时候锁的状态是阻塞状态，目的是告诉用户插入的位置存在间隙锁
  - 一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）
    - 如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态

间隙锁的工作原理是什么？

答：

间隙锁是 防止其他事务往间隙插入新记录，从而可以避免幻读的问题
- 间隙锁是 可重复读隔离级别 下才有的锁（读已提交隔离级别只有记录锁，没有间隙锁），主要是为了 防止幻读的问题，它可以阻止其他事务往间隙里插入新记录
具体的原理 是：当其他事务插入记录的时候，当发现插入位置的下一条记录有间隙锁，就会生成插入意向锁，然后锁设置为阻塞状态，目的是告诉用户插入的位置存在间隙锁

一条update语句没有带where条件，加的是什么锁？

答：

可重复读级别 下，更新没有带 where 条件，会全表扫描，会对每一条记录都加 next-key 锁，相当于锁住了全表
- next-key 锁 = 记录锁（Record Lock）+ 间隙锁（Gap Lock）
- InnoDB 加锁是索引加锁，可重复读级别下，加锁的基本单位是 next-key 锁。读已提交隔离级别下，加锁的基本单位是记录锁
读已提交隔离级别 下，没有间隙锁，更新没有带 where 条件，是全表扫描，那么会对每一条记录都加记录锁

带了where条件没有命中索引，加的是什么锁？

答：

没有命中索引，是 全表扫描，那么：
- 在 可重复读 级别下，全表扫描的话，会对每一条记录都加 next-key 锁
- 在 读已提交 隔离级别下，因为没有间隙锁，全表扫描的时候，会对每一条记录都加 记录锁

两条更新语句更新的同一条记录，加的是什么锁？

答：

在 可重复读级别 下，可能有这些情况：
- 如果更新条件的字段是 唯一索引，还要看 更新的记录是否存在：
  - 如果存在，那么这条记录 加的记录锁，只锁住该条记录
  - 如果这条记录 不存在，则 加间隙锁
- 如果更新条件的字段是 非唯一索引，还要看 更新的记录是否存在：
  - 如果存在，由于非唯一索引会存在相同值的记录，所以非唯一索引等值查询，实际上是一个扫描的过程，那么会针对符合更新条件的二级索引记录，加 next-key 锁，最后扫描到第一个不符合更新条件的二级索引记录就会停止扫描，然后对第一个不符合更新条件的记录加间隙锁，同时，在符合更新条件的记录的主键索引上加记录锁
  - 如果 不存在，会对第一个不符合更新条件的二级索引记录加间隙锁。
- 如果更新条件的字段是 没有索引或者没有命中索引，那么就是全表扫描，会对每一条记录都加 next-key 锁

可重复读的场景下，下面的场景会发生什么？

题目描述

答：

事务 A 和事务 B 在执行完后 update 语句后都持有范围为 (20，30) 的间隙锁，而接下来的插入操作为了获取到 插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了 循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁

了解过MySQL的死锁问题吗？

答：

了解过，在 并发事务 中，当两个事务出现 循环资源依赖，这两个事务都在等待别的事务释放资源时，就会导致这两个事务都进入 无限等待 的状态，这时候就发生了死锁

MySQL 怎么排查死锁问题？

答：

在遇到线上死锁问题时，我们应该 第一时间获取相关的死锁日志，我们可以通过 show engine innodb status 命令来 获取死锁信息。
然后就分析死锁日志。死锁日志通常分为两部分，上半部分说明了事务1 在等待什么锁，下半部分说明了事务2 当前持有的锁和等待的锁
通过阅读死锁日志，我们可以清楚地知道两个事务形成了怎样的循环等待，然后根据当前各个事务执行的SQL分析出加锁类型以及顺序，逆向推断出如何形成循环等待，这样就能找到死锁产生的原因了

MySQL怎么避免死锁？

答：

实际上死锁是不能完全避免的，只要会加锁，在并发的场景就会发生死锁，但是我们可以通过一些手段，降低发生死锁的概率
MySQL的锁是在事务提交的时候才会释放的，所以可以通过缩短锁持久的时间，来降低死锁的概率，比如：
- 如果事务中需要锁多个行，要把最可能造成锁冲突的锁的申请时机尽量往后放，这样事务的持久锁的时间就会比较短
- 避免大事务，尽量将大事务拆成多个小事务来处理，因为大事务占用耗时长，意味着占用锁占用时间长，与其他事务冲突的概率也会变高
可以通过减少间隙锁，来降低死锁的概率：
- 如果能确定幻读和不可重复读对应用的影响不大，可以考虑将隔离级别改成 RC，因为 RC 隔离级别没有间隙锁，可以避免间隙锁导致的死锁
可以通过减少加锁范围，来降低死锁的概率:
- 给表添加合理的索引，如果不走索引将会为表的每一行记录加行级锁，死锁的概率就会大大增大
可以通过 MySQL 参数设置，来降低死锁的概率:
- 设置合适的锁等待超时阈值，当一个事务的等待时间超过该值后，将回滚当前语句（而不是整个事务），如果要回滚整个事务，请使用 innodb rollback on timeout 开启值为：ON，开启这个参数之后，锁超时就会对这个事务进行回滚，于是锁就释放了
- 开启主动死锁检测，主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行

日志

MySQL三大日志是什么？

答：

undo log 是 InnoDB 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。在事务没提交之前，InnoDB 会先记录更新前的数据记录 undo log 中，回滚时利用 undo log 来进行回滚
redo log 也是 InnoDB 存储引擎层的日志，属于物理日志，记录了某个数据页做了什么修改，实现了事务的持久性，主要用于掉电等故障恢复。比如：某个事务提交了，脏页数据还没有刷盘，如果 MySQL 机器断电了，脏页的数据就丢失了，MySQL 重启后可以通过 redo log 日志，可以将已提交事务的数据恢复回来
binlog 是 Server 层生成的日志，主要用于 数据备份和主从复制。在完成一条 更新操作 后，Server 层会生成一条 binlog，等之后事务提交的时候，会将该事务执行过程中产生的所有 binlog 统一写入 binlog 文件。binlog 文件是 记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作

注：

redo log 记录了此次事务 「修改后」 的数据状态，记录的是更新之后的值，主要用于事务崩溃恢复，保证事务的持久性
undo log 记录了此次事务 「修改前」 的数据状态，记录的是更新之前的值，主要用于事务回滚，保证事务的原子性

redo log 和 undo log 的联系

详细链接：MySQL 日志：undo log、redo log、binlog 有什么用？ | 小林coding

redo log 和 binlog 的区别和应用场景？

答：

redo log 是 InnoDB 引擎实现的日志，属于物理日志，记录了 InnoDB 存储引擎对数据页所做的修改操作，主要用于崩溃恢复，比如：某个事务提交了，脏页数据还没有刷盘，如果 MySQL 机器断电了，脏页的数据就丢失了，MySQL 重启后可以通过重做日志，可以将已提交事务的数据恢复回来
binlog 是 server 层实现的日志，保存了所有对数据库的增删改操作，binlog 有三种日志格式日志的内容可能是 SQL 语句、数据本身或两者的混合，主要用于 数据库备份和归档，也用于 主从复制
写入方式不同：
- binlog 是 追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志
- redo log 是 循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志

为什么崩溃恢复不用 binlog 而用 redo log？

答：

binlog 是 server 层的日志，不会记录 innoDB 存储引擎层中有哪些数据页没有被刷盘，redo log 是 innoDB 层的日志，可以记录哪些脏页没有被刷盘，崩溃恢复的时候，恢复的粒度更细粒，可以精确到需要恢复的数据页，而 binlog 保存的是全量日志，没办法做到这一点，所以崩溃恢复用的是 redo log

binlog的三种格式是什么？

答：

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、MIXED，区别如下：
- STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中，主从复制中 slave 端再根据 SQL 语句重现
  - 在数据库的主从复制（Master-Slave Replication）架构中，Slave 端（从节点）指的是负责接收和应用主节点（Master）发送过来的数据更改的数据库实例
  - 缺陷：STATEMENT 有动态函数的问题，比如用了 uuid 或者 now 这些函数，在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致
- ROW：记录行数据最终被修改成什么样了，不会出现 STATEMENT 下动态函数的问题
  - 缺陷：但 ROW的缺点是每行数据的变化结果都会被记录，比如：执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句
- MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式

redo log 是怎么实现持久化的？

答：

事务执行过程更新的数据，并不是在事务提交的时候，就把修改的数据刷入磁盘的，而是修改 buffer pool 中数据页，并标记为脏页，然后后台再找合适的时间刷盘
- 脏页：当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Bufer Pool 中数据所在的页，然后将其页设置为脏页(该页的内存数据和磁盘上的数据已经不一致)，
如果事务提交了，脏页数据没有刷盘时，数据库发生宕机，这就会导致事务修改的数据丢失了
所以 MySQL 就引入了 redo log，redo log 保存的内容是物理日志，主要是记录 InnoDB 对某个数据页的修改操作，当事务提交的时候，redo log 会刷入磁盘，因为 redo log 保存了数据页的修改操作，即使脏页数据没有刷盘时数据库发生宕机了，重启后 MySQL 通过重放 redo log ，就能恢复未刷盘的脏页，保证了数据的持久化

redo log 除了崩溃恢复还有什么其他作用？

答：

写 redo log 日志是追加的形式，所以 redo log 写磁盘是一个顺序写的过程，而 数据页写磁盘是一个随机写的过程，顺序写的性能是比随机写性能高的，事务在提交的时候，是 先写日志再写数据 的机制，相当于把 MySQL 写入磁盘的操作从磁盘随机写变成了顺序写，所以 redo log 还可以起到提升 MySQL 写入磁盘性能的作用

先写日志再写数据 的机制：是因为 MySQL的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上

为什么需要两阶段提交？

答：

两阶段提交是为了保证 redo log 和 binlog 逻辑一致，从而保证主从复制的时候不会出现数据不一致的问题
事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，比如：在主从复制的场景下，如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入磁盘，这时候主库是最新的数据，而从库是旧数据，这样就造成两份日志之间的逻辑不一致

两阶段提交的过程？

答：

两阶段提交的过程

两阶段提交把事务的提交拆分成了 2 个阶段，分别是 准备阶段 和 提交阶段：
- 准备阶段 会将 redo log 状态设置为 prepare 状态，然后将 redo log 刷入磁盘
- 提交阶段 会将 binlog 刷入磁盘，然后设置 redo log 设置为 commit 状态，到这里两阶段就已经完成了
在两阶段提交中，是以 binlog 刷入磁盘时机作为事务提交成功的标志的：
- 如果 binlog 还没刷入磁盘的时候，MySQL 就发生了崩溃，MySQL重启的时候就需要回滚事务
- 如果 binlog 刷入磁盘，即使 redo log 没有设置 commit 状态，MySQL 就发生了崩溃，MySQL 重启的时候就会提交事务

redo log 刷盘策略有哪三种？

答：

redo log 刷盘策略主要有三种：

当刷盘策略配置为参数 0 的时候，表示每次事务提交时，还是将 redo log 留在 redo log buffer 中该模式下在事务提交时不会主动触发写入磁盘的操作，后续由 innoDB 后台线程把缓存在 redo log buffer 中的 redo log，写入到操作系统 pagecache 缓存并持久化到磁盘
当刷盘策略配置为参数 1 的时候，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘
当刷盘策略配置为参数 2 的时候，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到操作系统的 pagecache 缓存，但是 并不会执行刷盘操作，后续由 innoDB 后台线程来执行刷盘操作

redo log 刷盘策略

这三种刷盘策略，参数1的模式是数据安全性最高的，但是也是写入性能最差的，而参数0是数据安全性最差的，但是是写入性能最好的

性能调优

怎么查看一条语句是否走了索引？

答：

可以通过 explain 查看 SQL 的执行计划，关注 type 字段，这个字段表明 SQL 扫描的方式，如果 type 字段不是 all （全表扫描）或者 index（全索引扫描）就代表是索引扫描的方式，这种情况就代表 SQL 走了索引，并且我们还可以通过 key 字段，看这条查询用了哪个索引字段来走索引，如果 key 为 null，也代表没有走索引

explain的结构

extra 字段中的 using index 和 using where 的区别？

答：

using index 表示查询使用了 索引覆盖，不会回表，这个可以提高查询效率
using where 表示 MySQL 的存储引擎返回给 server 层的数据并不一定满足 where 子句的条件，所以MySQL从存储引擎拿到的数据，还得在 server 层进行了 where 子句的条件判断，来过滤出最终 SQL 所需要查询的数据

怎么找到慢 SQL？

答：

可以 开启慢查询日志，MySQL 就会自动将执行比较慢的 SQL 语句记录在慢查询日志中，具体多慢我们可以自己设置的，比如：设置 3秒，那么 MySQL 就会将执行超过 3 秒的 SQL语句记录在慢查询日志中

如何优化慢 SQL？

答：

常见 SQL 优化的方法：

优化数据访问：limit 子句缩减数据行数、避免 select *
拆分查询：分而治之的思想，将一个大查询拆分多个小查询，每个小查询只返回一部分查询结果
覆盖索引：当索引中的列包含所有查询中需要使用的列的时候，可以避免回表
避免索引失效：检查 SQL 是否因为写的不合理，导致索引失效
分解联表查询：让业务层分多个查询来聚合，或者增加余字段减少联表查询
排序优化：对于有排序场景，如果 extra 显示 filesort，这时候就需要考虑对排序的字段建立索引，避免文件排序

深分页场景如何优化？

答：

分页最简单的实现是使用 limit 字节句，比如：每页显示10条内容的话，第一页就是limit 10、第二页就是 limit 10,10、第三页 20,10。但是这种方式，在深分页的场景，存在严重的性能问题，比如：limit 10000,20 这样的查询，这时候 MySQL 最左叶子节点开始向右扫描 10020 条记录，时间复杂度为O(n)，然后只返回 20条给客户端，前面 10000 条记录都将被抛弃。如果是使用了二级索引，这种场景性能损失会加剧，因为对于前10000个不需要的数据，MySQL每次也要回表去查找，这就导致了10000次随机I/O
- 注：
  - LIMIT offset, row_count 是通用格式：
    - offset：起始的偏移量（从 0 开始计数）
    - row_count：返回的行数
  - 如果只写一个参数，例如 LIMIT 20，则表示返回前 20 行，从第 0 行开始
我能想到这两种优化方式：
1. 可以在业务上改进，将 “第几页” 改成 “下一页”，先记录上一页的最后一条记录的 id，然后下次就直接从该记录的位置开始扫描，这样就避免 MySQL 扫描大量不需要的行然后再抛弃掉的问题
2. 如果要遵循第几页的方案，可以通过 覆盖索引+子查询方式 改进。子查询语句主要查询分页数据对应的数据库唯一 id 值，因为主键在辅助索引上就有，所以子查询可以不用回表。然后主查询再根据子查询返回的 id，进行索引查询完整的数据行

如果 SQL 和索引都没问题，查询还是很慢怎么办？

答：

分批查询：针对一个大查询可以拆分多个小查询，每个小查询只返回一部分查询数据
增加缓存：针对频繁读取的热点数据，我们可以放到 Redis 缓存，避免每次都要请求
MySQL分表：如果 表的数据量很大，比如：表数据千万级别了，这时候可以考虑分表了，通过减少每次查询数据总量来解决数据查询缓慢的问题
主从复制：针对 读多写少 的场景，我们可以搭建 MySQL 主从模式来分摊读请求的流量
- 主节点通常负责处理写操作（INSERT、UPDATE、DELETE），并将变更推送到从节点
- 从节点通常负责处理读操作（SELECT），从而减轻主节点的负载
分库：针对 写多读少 的场景，单库的性能无法抗住高并发流量，就需要进行分库，把并发请求分散到多个实例中去

数据库选型

SQL和NoSQL有什么区别？

答：

SQL 和 NoSQL 数据库区别主要有：
1. 数据存储的区别：SQL 数据库是关系型数据库，主要代表的数据库是 MySQL，数据是严格按照二维表格的形式存储的，表与表之间可以建立连接来查询数据。NoSQL 是非关系型数据库，主要代表的数据库是 Redis、MongoDB 等，数据是灵活存储的，对数据的存储格式没有约束，可以在 NoSQL 中存储各式各样的数据，比如：Json文档、图、键值对等等
2. 事务的区别：SQL 数据库具备 ACID 四大特性的事务，而 NoSQL 数据库是不具备满足 ACID 特性的事务，因为 NoSQL 数据库都是通过牺牲了 ACID 特性来获取更高性能的
3. 扩展性的区别：SQL数据库的数据之间存在关联性，一般会选择垂直扩展，也就是增加服务器的性能，虽然也可以通过分库分表的方式实现水平扩展，但是水平扩展之后，会带来很多新的问题，比如需要解决跨库跨表的查询、分布式事务、全局唯一ID 等问题。NoSQL 数据库的数据相当于独立的个人，数据之间的联系很少，因此在进行水平扩展的时候更方便，不用考虑复杂的数据关联问题
我觉得 NoSQL 并非是为了取代 SQL，相反它们是需要相互结合的，SQL 数据库提供 ACID 事务，NoSQL 数据库提供更好的扩展性和性能，所以NoSQL一般是作为传统关系型数据库的补充而存在，弥补关系型数据库在性能、扩展性和某些场景下的不足

MySQL和MongoDB之间怎么选型？

答：

MySQL 是关系型数据库，支持 ACID 特性的事务，而 MongoDB 是 NoSQL 类型的数据库，不支持事务，如果业务需要通过事务保证数据一致性的话，是需要选择 MySQL 的。如果业务上没有强一致性的要求，那么可以根据下面这两种场景来考虑：
- MongoDB 是灵活的文档模型。也就是说，如果我预计我的数据可以被一个稳定的模型来描述，那么我会倾向于使用 MySQL 等关系型数据库。而一旦我认为我的数据模型会经常变动，比如说：我很难预料到用户会输入什么数据，这种情况下我就更加倾向于使用 MongoDB
- MongoDB 属于 NoSQL，更容易进行横向扩展。虽然关系型数据库也可以通过分库分表来达成横向扩展的目标，但是比 MongoDB 要困难很多，后期运维也要复杂很多，而这一切在 MongoDB 里面都是自动的运维成本低

高可用

MySQL的主从复制的过程是什么样的？

答：

主从复制过程

主从复制主要有 3 个阶段：

主库修改数据后，会写入 binlog 日志，，从库连接到主库之后，主库会创建一个 log dump 线程，用于发送 bin log 的内容
- 写入 binlog：主库写 binlog 日志，提交事务，并更新本地存储数据
从库会创建一个专门的 I/O 线程来连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库 “复制成功” 的响应
- 同步 binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中
接着从库还会创建一个用于回放 binlog 的 SQL 线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性
- 回放 binlog：回放 binlog，并更新存储引擎中的数据

MySQL 提供了几种复制模式？默认的复制模式是什么？

答：

主要有三种 同步复制、半同步复制、异步复制，MySQL 默认的复制模型是异步复制

同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式是性能最差的复制模式，但是能保证数据的安全性，如果对数据安全性比较高的业务，可以考虑采用同步复制的模式
异步复制：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果，这种模式性能是最高的，但是一旦主库宕机，数据就会发生丢失
半同步复制：介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如：一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险

详细链接：MySQL 主从复制 —— 全同步复制、异步复制、半同步复制主从复制方式有：全同步复制、异步复制、半同步复制、增强半同步 - 掘金

MySQL 主从复制的数据延迟怎么解决？

答：

使用缓存解决：可以在写入数据主库的同时，把数据写到 Redis 缓存里，这样其他线程再获取数据时会优先查询缓存，也可以保证数据的一致性。不过这种方式会带来缓存和数据库的一致性问题
直接查询主库：对于数据延迟敏感的业务，可以强制读主库。但是我们要提前明确查询的数据量不大，不然会出现主库写请求锁行，影响读请求的执行，最终对主库造成比较大的压力

MySQL 主从架构中，读写分离怎么实现？

答：

一种简单的做法是：提前把所有数据源配置在工程中，每个数据源对应一个主库或者从库，然后改造代码，在代码逻辑中进行判断，将 SQL 语句发送给某一个指定的数据源来处理。这个方案简单易实现，但SQL路由规则侵入代码逻辑，在复杂的工程中不利于代码的维护
另一个做法是：独立部署的代理中间件，如：MyCat，这一类中间件部署在独立的服务器上，一般使用标准的 MySQL 通信协议，可以代理多个数据库。该方案的优点是隔离底层数据库与上层应用的访问复杂度,比较适合有独立运维团队的公司选型；缺陷是所有的 SQL 语句都要跨两次网络传输，有一定的性能损耗，再就是运维中间件是一个专业且复杂的工作，需要一定的技术沉淀

MySQL 主库挂了怎么办？

答：

MySQL 主从复制 没有实现发现主服务器宕机和处理故障迁移的功能，要实现自动主从故障迁移的话，我简单了解过，可以使用开源的 MySQL 高可用套件 MHA，MHA 可以在主数据库发生宕机时，可以剔除原有主机选出新的主机，然后对外提供服务，保证业务的连续性

什么是分库分表？什么时候需要分表？什么时候需要分库？

答：

分库分表的意思把原本存储于单个数据库上的数据拆分到多个数据库，把原来存储在单张数据表的数据拆分到多张数据表中，实现数据切分
分库分表使用的场景不一样：
- 当单张数据表的数据量太大的时候，经验值是 500W 以上的数据量，就会影响了事务的执行效率，这时候就要考虑分表了，通过减少每次查询数据总量来解决数据查询缓慢的问题
- 当单台 MySQL 扛不住高并发流量的时候，就要考虑分库了，把并发请求分散到多台 MySQL 实例中

分库分表后会产生什么问题？怎么解决？

答：

分布式事务问题：对业务进行分库之后，同一个操作会分散到多个数据库中，涉及跨库执行SQL语句，也就出现了分布式事务问题
- 解决方式：如果对一致性要求比较高的业务，比如：金融类的业务，可以使用分布式事务中间件，实现 TCC 事务模型；互联网的业务通常对一致性要求低，会使用基于本地消息表来实现分布式事务，达到最终一致性的效果，这个分布式事务的方案性能会好一些
全局 ID 唯一性问题：在单库单表时，业务 ID 可以依赖数据库的自增主键实现，进行分库分表之后，如果还是用数据库的自增主键，可能会导致主键重复
- 我们可以通过雪花算法或者美团leaf算法生成唯一主键ID
跨库跨表关联查询问题：分库分表后，跨库和跨表的查询操作实现起来会比较复杂
- 我们可以通过冗余额外字段避免跨库关联，或者交给数据库分库分表中间件来实现，也可以将数据全量存储到 ES 中去，通过 ES 进行查询
跨库跨表的排序问题：分库分表以后，数据分散存储到不同的数据库和表中，如果需要对数据列表进行排序时，就变得异常复杂
- 我们可以通过业务代码或者数据库分库分表中间件分别查询每个子表中的数据。然后汇总进行排序，也可以将数据全量存储到 ES 中去，通过 ES 进行查询
跨库跨表 COUNT 查询的问题：分库分表以后，数据分散存储到不同的数据库和表中，如果需要对表进行COUNT 查询就会很复杂
- 我们可以将计数的数据单独存储在一张表里，或者将聚合查询的数据同步到 ES 中，交给 ES 进行查询