MySQL

什么是mysql？

MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息。

由于 MySQL 是开源免费并且比较成熟的数据库，因此，MySQL 被大量使用在各种系统中。任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL 的默认端口号是3306。

MySQL字段类型

MySQL 字段类型可以简单分为三大类：

数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

整数类型UNSIGNED有什么用

MySQL 中的整数类型可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。

例如， TINYINT UNSIGNED 类型的取值范围是 0 ~ 255，而普通的 TINYINT 类型的值范围是 -128 ~ 127。INT UNSIGNED 类型的取值范围是 0 ~ 4,294,967,295，而普通的 INT 类型的值范围是 -2,147,483,648 ~ 2,147,483,647。

对于从 0 开始递增的 ID 列，使用 UNSIGNED 属性可以非常适合，因为不允许负值并且可以拥有更大的上限范围，提供了更多的 ID 值可用。

Char和Varchar的区别

CHAR 和 VARCHAR 是最常用到的字符串类型，两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。

VARCHAR(100)和VARCHAR(10)的区别是什么

VARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时，就需要修改表结构才可以。

虽说 VARCHAR(100)和 VARCHAR(10)能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，这也是很多人容易误解的一点。

不过，VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。

Decimal和Float/Double的区别

DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

DECIMAL 用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。

在 Java 中，MySQL 的 DECIMAL 类型对应的是 Java 类 java.math.BigDecimal。

NULL 和 ‘’ 的区别(为什么 MySQL 不建议使用 `NULL` 作为列默认值？)

NULL 和 '' (空字符串) 是两个完全不同的值，它们分别表示不同的含义，并在数据库中有着不同的行为。NULL 代表缺失或未知的数据，而 '' 表示一个已知存在的空字符串。它们的主要区别如下：

含义:
- NULL 代表一个不确定的值，它不等于任何值，包括它自身。因此，SELECT NULL = NULL 的结果是 NULL，而不是 true 或 false。 NULL 意味着缺失或未知的信息。虽然 NULL 不等于任何值，但在某些操作中，数据库系统会将 NULL 值视为相同的类别进行处理，例如：DISTINCT,GROUP BY,ORDER BY。需要注意的是，这些操作将 NULL 值视为相同的类别进行处理，并不意味着 NULL 值之间是相等的。它们只是在特定操作中被特殊处理，以保证结果的正确性和一致性。这种处理方式是为了方便数据操作，而不是改变了 NULL 的语义。
- '' 表示一个空字符串，它是一个已知的值。
存储空间:
- NULL 的存储空间占用取决于数据库的实现，通常需要一些空间来标记该值为空。
- '' 的存储空间占用通常较小，因为它只存储一个空字符串的标志，不需要存储实际的字符。
比较运算:
- 任何值与 NULL 进行比较（例如 =, !=, >, < 等）的结果都是 NULL，表示结果不确定。要判断一个值是否为 NULL，必须使用 IS NULL 或 IS NOT NULL。
- '' 可以像其他字符串一样进行比较运算。例如，'' = '' 的结果是 true。
聚合函数:
- 大多数聚合函数（例如 SUM, AVG, MIN, MAX）会忽略 NULL 值。
- COUNT(*) 会统计所有行数，包括包含 NULL 值的行。COUNT(列名) 会统计指定列中非 NULL 值的行数。
- 空字符串 '' 会被聚合函数计算在内。例如，SUM 会将其视为 0，MIN 和 MAX 会将其视为一个空字符串。

Boolean类型如何表示

MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1，分别对应 false 或 true。

MySQL的存储引擎

可以通过 SHOW ENGINES 命令来查看 MySQL 支持的所有存储引擎。

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MySQL存储引擎架构了解过吗

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

MyISAM和InnoDB的区别

InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。
MyISAM 不支持外键，而 InnoDB 支持。
MyISAM 不支持 MVCC，而 InnoDB 支持。
虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。
MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。
InnoDB 的性能比 MyISAM 更强大。

MySQL日志

日志分为哪几种

redo log 重做日志，是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复。
undo log 回滚日志，是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
bin log 二进制日志，是 Server 层生成的日志，主要用于数据备份和主从复制。
relay log 中继日志，用于主从复制场景下，slave通过io线程拷贝master的bin log后本地生成的日志
慢查询日志，用于记录执行时间过长的sql，需要设置阈值后手动开启

讲一下binlog

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写入 binlog 文件，binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用。

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志，用于备份恢复、主从复制；

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

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 模式；

Undolog

undo log 是一种用于撤销回退的日志，它保证了事务的 ACID 特性中的原子性（Atomicity）。

在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。如下图：

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。

redolog

redo log（重做日志）是 InnoDB 存储引擎独有的，它让 MySQL 拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB 存储引擎会使用 redo log 恢复数据，保证数据的持久性与完整性。

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

redolog和undolog的区别

redo log 和 undo log 这两种日志是属于 InnoDB 存储引擎的日志，他们的区别在于：

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

redolog是怎么保证持久性的

Write-ahead logging（WAL）：在事务提交之前，将事务所做的修改操作记录到redo log中，然后再将数据写入磁盘。这样即使在数据写入磁盘之前发生了宕机，系统可以通过redo log中的记录来恢复数据。
Redo log的顺序写入：redo log采用追加写入的方式，将redo日志记录追加到文件末尾，而不是随机写入。这样可以减少磁盘的随机I/O操作，提高写入性能。
Checkpoint机制：MySQL会定期将内存中的数据刷新到磁盘，同时将最新的LSN（Log Sequence Number）记录到磁盘中，这个LSN可以确保redo log中的操作是按顺序执行的。在恢复数据时，系统会根据LSN来确定从哪个位置开始应用redo log。

两阶段提交

redo log（重做日志）让 InnoDB 存储引擎拥有了崩溃恢复能力。

binlog（归档日志）保证了 MySQL 集群架构的数据一致性。

虽然它们都属于持久化的保证，但是侧重点不同。

在执行更新语句过程，会记录 redo log 与 binlog 两块日志，以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以 redo log 与 binlog 的写入时机不一样。

回到正题，redo log 与 binlog 两份日志之间的逻辑不一致，会出现什么问题？

我们以update语句为例，假设id=2的记录，字段c值是0，把字段c值更新成1，SQL语句为update T set c=1 where id=2。

假设执行过程中写完 redo log 日志后，binlog 日志写期间发生了异常，会出现什么情况呢？

由于 binlog 没写完就异常，这时候 binlog 里面没有对应的修改记录。因此，之后用 binlog 日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为 redo log 日志恢复，这一行c值是1，最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB 存储引擎使用两阶段提交方案。

原理很简单，将 redo log 的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

使用两阶段提交后，写入 binlog 时发生异常也不会有影响，因为 MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于prepare阶段，并且没有对应 binlog 日志，就会回滚该事务。
再看一个场景，redo log 设置commit阶段发生异常，那会不会回滚事务呢？并不会回滚事务，它会执行上图框住的逻辑，虽然 redo log 是处于prepare阶段，但是能通过事务id找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务恢复数据。

为什么要写redolog，而不是直接写入b+树中

因为 redolog 写入磁盘是顺序写，而 b+树里数据页写入磁盘是随机写，顺序写的性能会比随机写好，这样可以提升事务提交的效率。
最重要的是redolog具备故障恢复的能力，Redo Log 记录的是物理级别的修改，包括页的修改，如插入、更新、删除操作在磁盘上的物理位置和修改内容。例如，当执行一个更新操作时，Redo Log 会记录修改的数据页的地址和更新后的数据，而不是 SQL 语句本身。

索引

主键索引

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引

二级索引（Secondary Index）的叶子节点存储的数据是主键的值，也就是说，通过二级索引可以定位主键的位置，二级索引又称为辅助索引/非主键索引。

唯一索引、普通索引、前缀索引等索引都属于二级索引。

唯一索引（Unique Key）：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
普通索引（Index）：普通索引的唯一作用就是为了快速查询数据。一张表允许创建多个普通索引，并允许数据重复和 NULL。
前缀索引（Prefix）：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
全文索引（Full Text）：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MyISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

二级索引：

聚簇索引

聚簇索引（Clustered Index）即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引（B+ 树）的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。（主键对应的那一行数据）

聚簇索引的优缺点

优点：

查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+ 树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引，聚簇索引少了一次读取数据的 IO 操作。
对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

依赖于有序的数据：因为 B+ 树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引

非聚簇索引（Non-Clustered Index）即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引（辅助索引）就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚簇索引的优缺点

优点：

更新代价比聚簇索引要小。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。

缺点：

依赖于有序的数据：跟聚簇索引一样，非聚簇索引也依赖于有序的数据。
可能会二次查询（回表）：这应该是非聚簇索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

非聚簇索引不一定回表查询。（覆盖查询）

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引（Covering Index）。

在 InnoDB 存储引擎中，非主键索引的叶子节点包含的是主键的值。这意味着，当使用非主键索引进行查询时，数据库会先找到对应的主键值，然后再通过主键索引来定位和检索完整的行数据。这个过程被称为“回表”。

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配

假设有一个联合索引 (column1, column2, column3)，其从左到右的所有前缀为 (column1)、(column1, column2)、(column1, column2, column3)（创建 1 个联合索引相当于创建了 3 个索引），包含这些列的所有查询都会走索引而不会全表扫描。

再来看一个常见的面试题：如果有索引 联合索引（a，b，c），查询 a=1 AND c=1 会走索引么？c=1 呢？b=1 AND c=1 呢？

先不要往下看答案，给自己 3 分钟时间想一想。

查询 a=1 AND c=1：根据最左前缀匹配原则，查询可以使用索引的前缀部分。因此，该查询仅在 a=1 上使用索引，然后对结果进行 c=1 的过滤。
查询 c=1：由于查询中不包含最左列 a，根据最左前缀匹配原则，整个索引都无法被使用。
查询 b=1 AND c=1：和第二种一样的情况，整个索引都不会使用。

索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE 字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

假设我们有一个名为 user 的表，其中包含 id、username、zipcode 和 birthdate 4 个字段，创建了联合索引 (zipcode, birthdate)。

没有索引下推之前，即使 zipcode 字段利用索引可以帮助我们快速定位到 zipcode = '431200' 的用户，但我们仍然需要对每一个找到的用户进行回表操作，获取完整的用户数据，再去判断 MONTH(birthdate) = 3。
有了索引下推之后，存储引擎会在使用 zipcode 字段索引查找 zipcode = '431200' 的用户时，同时判断 MONTH(birthdate) = 3。这样，只有同时满足条件的记录才会被返回，减少了回表次数。

索引失效的场景

索引失效的场景是指在数据库查询中，虽然我们为某些字段建立了索引，但由于某些原因，查询时索引未能被使用，导致查询性能下降。接下来我会详细讲述常见的七个索引失效场景及其原因。

第一个是，当查询条件中使用了函数或表达式操作时，索引会失效。例如，在查询中对索引列使用了 UPPER()、LOWER() 或其他函数操作，数据库无法直接利用索引，因为索引存储的是原始值，而不是经过函数处理后的值。类似的，如果在索引列上进行数学运算（如 age + 1），也会导致索引失效。(原因：索引保存的是索引字段的原始值，而不是经过函数计算后的值)
第二个是，当查询条件中使用了类型隐式转换时，索引会失效。例如，如果索引列是字符串类型，而查询条件中传入的是数字类型，数据库会尝试将字符串列转换为数字进行比较，这种隐式转换会导致索引失效。正确的做法是确保查询条件的数据类型与索引列的数据类型一致。（原因：在类型转换时对索引字段使用了类型转换的函数，所以理由同第一个）
第三个是，当查询条件中使用了 LIKE 并以通配符 % 开头时，索引会失效。例如，LIKE ‘%abc’ 这种查询方式会让数据库无法利用索引，因为通配符 % 在开头意味着需要扫描整个表来匹配数据。而如果通配符出现在末尾，如 LIKE ‘abc%’，索引仍然可以生效。（原因：索引字段是排序存储的，而以%为开头无法知道从哪开始查找，只能全表扫描）
第四个是，当查询条件中使用了 OR 且部分条件未命中索引时，索引可能失效。例如，如果查询条件是 WHERE indexed_column = ‘value1’ OR non_indexed_column = ‘value2’，即使 indexed_column 上有索引，但由于 non_indexed_column 没有索引，数据库可能会选择全表扫描，从而导致索引失效。
第五个是，当查询中使用了 NOT 或 != 操作符时，索引可能会失效。例如，WHERE column != ‘value’ 或 WHERE NOT column = ‘value’ 这样的查询条件通常会导致数据库放弃使用索引，因为这类操作需要扫描大量数据来排除不符合条件的记录。
第六个是，当查询中使用了复合索引但未遵循最左前缀原则时，索引会失效。例如，假设有一个复合索引 (A, B, C)，如果查询条件只包含 B 或 C，而没有包含 A，那么这个复合索引将无法被使用。只有从最左边的列开始，并按顺序使用索引列，才能有效利用复合索引。
第七个是，当查询中使用了 IS NOT NULL 时，索引可能会失效。虽然 MySQL 支持对 IS NULL 使用索引，但在很多情况下，特别是 IS NOT NULL 的查询，数据库可能会选择全表扫描，从而导致索引失效。

B+树B树和红黑树的区别

B+树、B树和红黑树是常见的平衡树数据结构，尤其在数据库、文件系统和内存中应用的十分广泛。接下来，我将从十一个方面详细讲解这三者的特点和区别。

第一个方面是数据存储位置。B+树将所有数据存储在叶子节点，非叶子节点只存储索引键；B树则将数据存储在叶子节点和非叶子节点中，所有节点都存储数据；而红黑树则在每个节点中都存储数据。
第二个方面是叶子节点结构。B+树通过链表连接叶子节点，这使得范围查询和顺序遍历非常高效；B树的叶子节点不一定通过链表连接，缺乏直接支持范围查询的结构；红黑树没有专门的叶子节点结构，所有节点通过指针连接。
第三个方面是索引查找效率。B+树的查找操作最终都在叶子节点完成，查找路径统一，效率较高；B树的查找操作可能在非叶子节点完成，查找路径不统一，效率稍低；红黑树的查找路径统一，时间复杂度为O(log n)，每次操作通过旋转和重新染色来保持平衡。
第四个方面是树的高度。B+树由于扇出较高（每个节点存储多个键），树的高度通常较低，查询效率较高；B树由于非叶子节点也存储数据，扇出较小，树的高度较高；红黑树作为二叉查找树，树的高度较高，每个节点最多有两个子节点，树的深度较大。
第五个方面是顺序访问效率。B+树通过链表连接叶子节点，支持高效的顺序访问，尤其在范围查询时表现出色；B树没有直接的顺序访问机制，顺序访问效率较低；红黑树也缺乏顺序访问机制，顺序遍历效率较低。
第六个方面是磁盘I/O效率。B+树由于非叶子节点只存储索引，扇出高，可以减少磁盘访问次数，因此磁盘I/O效率非常优秀；B树稍逊于B+树，因为非叶子节点也存储数据，导致扇出较小，磁盘访问次数略多；红黑树的磁盘I/O效率较差，因为树的高度较高，每次查找可能需要频繁访问磁盘。
第七个方面是适用场景。B+树常用于数据库索引、文件系统索引，适合大规模数据的存储和检索，尤其在需要高效范围查询时；B树适用于数据库索引和文件系统索引，但相比B+树，查找效率稍低；红黑树适用于内存中的数据结构，如Java中的TreeMap和TreeSet，适合存储符号表、集合、关联数组等内存数据。
第八个方面是平衡性。B+树将所有叶子节点置于同一层，平衡性非常好；B树平衡性较好，但查找路径不统一，效率稍逊；红黑树通过旋转和染色来保持平衡，查找路径统一，操作保持平衡。
第九个方面是插入/删除操作。B+树的插入和删除操作可能会引发节点分裂和合并，操作较复杂；B树的插入和删除操作也可能引发节点分裂和合并，操作较复杂；红黑树通过旋转和染色保持平衡，操作相对简单。
第十个方面是实现复杂度。B+树的实现较复杂，尤其是在处理链表结构和节点分裂/合并时；B树的实现也较复杂，涉及节点分裂/合并，但相对较为简单；红黑树的实现相对简单，通过旋转和染色来保持平衡。
第十一个方面是空间利用率。B+树的空间利用率较高，非叶子节点只存储索引，存储效率较好；B树的空间利用率较低，非叶子节点存储数据和索引，存储效率较低；红黑树的空间利用率较高，所有节点都存储数据，且由于树的平衡性，内存利用效率较好。

为什么B+树比B树更矮？

一个数据页（以一个数据页为一个节点）为固定的16KB，里面包含页头，页尾，数据行等等，由于b+树非叶子节点的数据行中只包含了key，每一行占用空间比b树小，大概可以容纳200-300行，这样2000w的数据最多也就3层。

B+查询时间更稳定带来的好处？

性能预测更容易：对于数据库管理员和开发者而言，稳定的查询时间让系统性能更可预测，能够更好地预估响应时间，并且减少出现性能瓶颈的风险。

一致性体验：对于用户来说，数据库的响应时间更加一致，不会出现时快时慢的情况，尤其在面对大规模数据时，保证用户的体验更加平稳。

慢查询优化

慢查询优化的关键在于启用慢查询日志并进行深入分析，这能帮助我们识别和优化那些执行效率低下的SQL语句。总体上是通过三个步骤来进行，先捕获低效SQL，然后使用工具进行分析，最后采用一些方法和原则进行优化，接下来我会进行详细讲述。

首先，是捕获低效SQL，在此时我们需要确保慢查询日志已启用。通过设置全局变量slow_query_log为’ON’来开启此功能，并设定long_query_time参数以确定什么程度的查询延迟被视为“慢”。可以使用SHOW VARIABLES命令来检查这两个设置的状态，确保配置正确无误。

接下来，我们要分析慢查询日志，这是找出问题根源的关键步骤。利用工具如mysqldumpslow可以帮助我们总结日志信息，比如按出现频率或总执行时间排序，从而聚焦于最需要关注的查询语句。

对于每一个慢查询，我们可以使用EXPLAIN命令查看MySQL如何执行这些查询。EXPLAIN提供的输出能够揭示查询执行计划中的细节，例如是否进行了全表扫描（ALL），以及哪些索引被使用等。根据这些信息，我们可以做出更明智的决策来优化查询性能。

最后是，采用一些方法和原则进行优化，常用的方法有4种。

第一种是，建立和优化索引。合理的索引设计可以让查询更加高效，特别是对于经常出现在查询条件、连接条件、排序和分组操作中的字段。同时，删除不再需要的索引也能减少维护开销。
第二种是，对查询本身进行优化，避免不必要的列选择，尽量减少数据传输量，并考虑使用批量或分页查询来处理大数据集。此外，尽量用JOIN替代子查询，因为JOIN通常具有更好的性能表现。
第三种是，调整数据库和系统的配置参数，例如，适当增大InnoDB缓冲池大小、调整临时表的大小限制等，都可以带来显著的性能改进。
第四种是，对于特别大的表，可以考虑采用水平切分或垂直切分策略，通过将数据分散到多个表或数据库实例中，进一步减轻单个实例的压力，改善整体查询性能。

关系型数据库事务的ACID特性

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

这里要额外补充一点：只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

并发事务带来了哪些问题

脏读（dirty read）

一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。

丢失数据（lost to modify）

在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

不可重复读（UnrepeatableRead）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读（Phantom Read）

幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读的区别

不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

并发事务的控制方式

MySQL 中并发事务的控制方式无非就两种：锁和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

锁控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

InnoDB对MVCC的实现

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

隐藏字段

在内部，InnoDB 存储引擎为每行数据添加了三个隐藏字段：
- DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
- DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
- DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引
undolog

undo log 主要有两个作用：
- 当事务回滚时用于将数据恢复到修改前的样子
- 另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读
在 InnoDB 存储引擎中 undo log 分为两种：insert undo log 和 update undo log：
1. insert undo log：指在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除。不需要进行 purge 操作
2. update undo log：update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge线程 进行最后的删除
ReadView

Read View主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

主要有以下字段：
- m_low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
- m_up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见
- m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
- m_creator_trx_id：创建该 Read View 的事务 ID
根据readview的匹配规则和当前的一些事务id判断该访问哪个版本的数据

不同隔离级别快照读不一样，最终访问结果不一样：
- RC(读已提交):每一次快照读的时候生成readview
- RR（可重复读）：仅在第一次快照读时生成readview，后面复用

事务的隔离级别

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MYSQL 隔离级别是基于什么实现的

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

MYSQL锁

表级锁和行级锁的区别

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。

行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比：

表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

InnoDB有哪几类行锁

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

记录锁（Record Lock）：属于单个行记录上的锁。
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

共享锁和排他锁呢

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

意向锁有什么用

如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB 会先获取该数据行所在在数据表的对应意向锁。

分库和分表

分库是一种水平扩展数据库的技术，将数据根据一定规则划分到多个独立的数据库中。每个数据库只负责存储部分数据，实现了数据的拆分和分布式存储。分库主要是为了解决并发连接过多，单机 mysql扛不住的问题。
分表指的是将单个数据库中的表拆分成多个表，每个表只负责存储一部分数据。这种数据的垂直划分能够提高查询效率，减轻单个表的压力。分表主要是为了解决单表数据量太大，导致查询性能下降的问题。

垂直分库：一般来说按照业务和功能的维度进行拆分，将不同业务数据分别放到不同的数据库中，核心理念专库专用。按业务类型对数据分离，剥离为多个数据库，像订单、支付、会员、积分相关等表放在对应的订单库、支付库、会员库、积分库。垂直分库把一个库的压力分摊到多个库，提升了一些数据库性能，但并没有解决由于单表数据量过大导致的性能问题，所以就需要配合后边的分表来解决。

垂直分表：针对业务上字段比较多的大表进行的，一般是把业务宽表中比较独立的字段，或者不常用的字段拆分到单独的数据表中，是一种大表拆小表的模式。数据库它是以行为单位将数据加载到内存中，这样拆分以后核心表大多是访问频率较高的字段，而且字段长度也都较短，因而可以加载更多数据到内存中，减少磁盘IO，增加索引查询的命中率，进一步提升数据库性能。

水平分库：是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，以此实现水平扩展，是一种常见的提升数据库性能的方式。这种方案往往能解决单库存储量及性能瓶颈问题，但由于同一个表被分配在不同的数据库中，数据的访问需要额外的路由工作，因此系统的复杂度也被提升了。

水平分表：同一个数据库中，把一张大数据量的表按一定规则，切分成多个结构完全相同表，而每个表只存原表的一部分数据。水平分表尽管拆分了表，但子表都还是在同一个数据库实例中，只是解决了单一表数据量过大的问题，并没有将拆分后的表分散到不同的机器上，还在竞争同一个物理机的CPU、内存、网络IO等。要想进一步提升性能，就需要将拆分后的表分散到不同的数据库中，达到分布式的效果。