1. 基本概念

1.1. 关系型数据库

1.2. SQL

1.3. MySQL

2. MySQL 基础架构

MySQL基础架构

简单来说 MySQL 主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，同时还有一个日志模块binlog，这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有
存储引擎：主要负责数据的存储和读取，采用插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了，可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

2.1. Server 层基本组件

2.1.1. 连接器

连接器主要负责身份认证和权限相关的功能

当用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即使管理员修改了该用户的权限，该用户也是不受影响的。

2.1.2. 查询缓存（MySQL 8.0 版本后移除）

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集，不推荐使用

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 SQL 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空

2.1.3. 分析器

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的 SQL 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

2.1.4. 优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。经过了优化器之后，这个语句具体该如何执行就已经定下来。

2.1.5. 执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果

2.2. 语句执行分析

查询语句的执行流程如下：权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎
更新语句的执行流程如下：分析器—->权限校验—->执行器—>引擎—redo log（prepare 状态）—>binlog—>redo log（commit状态）

2.3. MySQL存储引擎

show engines 命令查看 MySQL 支持的所有存储引擎

MySQL 5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎

所有的存储引擎中只有 InnoDB 支持事务

2.3.1. MySQL 存储引擎架构

插件式架构。可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库

2.3.2. MyISAM 和 InnoDB 区别

是否支持行级锁

MyISAM 只有表级锁（table-level locking），而 InnoDB 支持行级锁（row-level locking）和表级锁，默认为行级锁。

也就说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下性能比 InnoDB 差

是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，SQL 标准定义了四个隔离级别，具有提交（commit）和回滚（rollback）事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）

是否支持外键

MyISAM 不支持，而 InnoDB 支持。阿里的《Java 开发手册》明确规定禁止使用外键

外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可

是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。

索引实现不一样

虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

InnoDB 引擎中，其数据文件本身就是索引文件。而 MyISAM，索引文件和数据文件是分离的。InnoDB 引擎中，表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。

性能有差别

InnoDB 的性能比 MyISAM 更强大，不管是在读写混合模式下还是只读模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发，它的处理能力跟核数没关系

总结

MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。请无脑使用 InnoDB

3. MySQL 锁

3.1. 锁的类型

共享锁（Share Lock，S 锁）：也称为读锁（Read Lock），在读取数据时使用。多个事务可以同时持有共享锁，但是一个事务持有共享锁时，其他事务不能获取排它锁。

排它锁（Exclusive Lock，X 锁）：也称为写锁（Write Lock），在更新数据时使用。只有一个事务可以持有排它锁，其他事务不能获取共享锁和排它锁。

意向锁：快速判断是否可以对某个表使用表锁

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

行级锁（Row Lock）：是针对行数据进行的锁定，可以实现对表中某些行的读和写的互斥操作。行级锁可以是共享锁或排它锁。

表级锁（Table Lock）：是针对整张表进行的锁定，可以实现对整张表的读和写的互斥操作。表级锁可以是共享锁或排它锁。

3.2. 行锁&表锁

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

表锁

表级锁是MySQL中最基本的锁机制，它是对整张表进行加锁，可以实现对整张表的读和写的互斥操作。当一个线程获得了表级锁后，其他线程就不能再对该表进行写操作，只能进行读操作

MySQL中的表级锁分为两种：共享锁和排它锁。共享锁是读锁，用于保证多个线程可以同时读取同一张表，而不会冲突。排它锁是写锁，用于保证只有一个线程可以对表进行写操作，其他线程不能进行读或写操作。

表级锁的优点是锁定粒度大，可以保证操作的原子性，缺点是并发性能差，容易出现锁等待和死锁。

表锁可以使用以下语句进行设置：

LOCK TABLES ...;

在上面的语句中，LOCK TABLES语句用于锁定表格。当一个事务获取了某个表的表锁后，其他事务就无法修改该表数据，直到该事务释放表锁为止

行锁

行级锁是MySQL中一种更细粒度的锁机制，它是在表的行级别上进行加锁，可以实现对表中某些行的读和写的互斥操作。当一个线程获得了行级锁后，其他线程可以继续读取该表的其他行，但不能读取或修改被锁定的行

MySQL中的行级锁是基于存储引擎实现的，不同的存储引擎对行级锁的支持不同。例如，InnoDB存储引擎支持行级锁，而MyISAM存储引擎不支持行级锁。

行级锁的优点是锁定粒度小，可以提高并发性能，缺点是需要占用更多的系统资源，同时也容易出现死锁等问题

行锁可以使用以下语句进行设置：

SELECT ... FOR UPDATE;

在上面的语句中，SELECT语句用于查询数据，FOR UPDATE语句用于设置行锁。当一个事务获取了某一行的行锁后，其他事务就无法修改该行数据，直到该事务释放行锁为止

MySQL行锁和表锁都可能会遇到一些问题，具体问题和解决方案如下：

行锁的死锁问题：当多个事务同时请求行级锁时，可能会出现死锁的情况。解决方案可以采用加锁顺序一致、限制事务持有锁的时间等方式来避免死锁。

表锁的并发性问题：表锁的加锁粒度比较大，可能会导致并发性能下降。解决方案可以采用分库分表、使用更细粒度的锁等方式来提高并发性能。

表锁的读写冲突问题：表锁会导致整张表的读和写互斥，可能会影响系统的并发性能和响应时间。解决方案可以采用读写分离、使用更细粒度的锁等方式来提高并发性能和响应时间。

行锁的性能问题：行级锁的加锁粒度比较细，可能会导致锁竞争频繁、锁定资源过多等性能问题。解决方案可以采用使用更细粒度的锁、优化SQL语句、增加硬件资源等方式来提高性能。

行锁和表锁的选择问题：在使用锁时需要根据具体的业务需求和系统架构选择合适的锁机制。一般来说，如果并发访问的数据比较少，可以使用表级锁；如果并发访问的数据比较频繁，可以使用行级锁

4. InnoDB 对 MVCC 的实现

MVCC（Multi-Version Concurrency Control）是一种用于保证数据库并发访问正确性的机制，常用于支持事务的数据库系统中。MVCC采用多版本的方式来控制并发，确保每个事务读取的数据是一致性的。

在MVCC机制下，每个事务在读取数据时都可以看到一个自己的“版本”或“快照”，不同的事务看到的数据版本可能是不同的。当一个事务修改数据时，不会直接覆盖原来的数据，而是创建一个新的版本。这样，其他事务在读取数据时可以继续看到旧版本的数据，而不会受到新版本数据的影响。

在MVCC机制下，每个数据行都有一个版本号或时间戳，用来标识这个数据行的版本。当一个事务需要读取数据时，它会读取该数据行的最新版本（或符合事务隔离级别的版本），如果该版本是其他事务创建的，那么该事务就会根据版本号或时间戳的不同而采取不同的对策。如果该版本是已提交的，那么该事务可以直接读取数据；如果该版本是未提交的，那么该事务就需要等待该事务的提交；如果该版本被其他事务锁定，那么该事务也需要等待锁的释放。

MVCC机制通常使用在支持快照隔离级别的数据库系统中，比如MySQL、PostgreSQL等。它可以提高数据库的并发性能和可靠性，避免了传统锁机制中的死锁、饥饿等问题。

5. MySQL 事务（重点）

5.1. 背景

我们需要插入多条相关联的数据到数据库，但是这个过程可能会遇到下面这些问题：

数据库中途突然因为某些原因挂掉了
客户端突然因为网络原因连接不上数据库了
并发访问数据库时，多个线程同时写入数据库，覆盖了彼此的更改

事务的作用是为了维护数据的一致性和完整性，即使发生故障时也是如此

事务是逻辑上的一组操作，要么都执行，要么都不执行

5.2. 数据库事务

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;

关系型数据库（例如：MySQL、SQL Server、Oracle 等）事务的 ACID 特性：

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

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

注意：MySQL事务回滚的原理是通过回滚日志 Undo log 来实现的

5.3. 并发事务带来的问题

多个事务并发运行，经常会操作相同的数据来完成各自的任务（如多个用户并发的对同一数据进行操作）

5.3.1. 脏读（Dirty read）

A事务读取了B事务中还未提交的数据，但当B事务提交失败进行回滚时，导致数据并没有被提交到数据库，那A事务读取到的就是脏数据，这就是脏读

5.3.2. 丢失修改（Lost to modify）

A事务读取一个数据时，B事务也访问了该数据，那么在A事务中修改了这个数据后，如果B事务也修改了这个数据。这样导致A事务内的修改结果被丢失，这就是丢失修改

5.3.3. 不可重复读（Unrepeatable read）

A事务内多次读同一数据。在A事务还没有结束的同时，B事务也访问该数据。则在A事务中的两次读数据之间，由于B事务的修改，可能导致A事务两次读取的数据不一样。这就发生了在一个事务内两次读到的数据是不一样的情况，这就是不可重复读

5.3.4. 幻读（Phantom read）

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

幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

举个例子：执行 delete 和 update 操作的时候，可以直接对记录加锁，保证事务安全。而执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock+Gap Lock）进行加锁来保证不出现幻读

5.4. 并发事务的控制方式

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

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

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 InnoDB 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

undo log : undo log 用于记录某行数据的多个版本的数据。
read view 和隐藏字段 : 用来判断当前版本数据的可见性。

5.5. 事务隔离级别

事务隔离级别是指在多个事务并发执行时，各个事务之间相互隔离的程度。数据库管理系统通过实现不同的隔离级别来平衡并发性和数据一致性之间的关系

5.5.1. 四个隔离级别

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。可以通过SELECT @@tx_isolation;命令来查看

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

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

注意：隔离级别越高，会对并发性能产生越大的影响，因此需要根据具体情况进行选择。

5.5.2. MySQL 隔离级别的实现

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

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

6. MySQL 索引（重点）

6.1. 索引介绍

索引是一种用于快速查询和检索数据的数据结构。索引，我们也称之为 index 或者 key

MySQL 的索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎的索引工作方式都不一样，甚至相同类型的索引，在不同的存储引擎中实现方案都不同

减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和创建临时表。
索引将随机 IO 变为了顺序 IO

6.2. 索引的底层数据结构

常见的索引结构有: B 树， B+树和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构

6.2.1. B树&B+树的异同

B 树的所有节点既存放键(key) 也存放数据(data)

b-tree

B+树只有叶子节点存放 key 和 data，其他内节点只存放 key

B+Tree 非叶子节点只保存关键字的索引和指向下一个节点的指针

B+Tree 叶子节点的关键字从小到大按顺序排列

b+tree

6.2.2. 树高问题

在 InnoDB 存储引擎中，B+Tree 的高度一般为 2-4 层，这就可以满足千万级的数据的存储，查找数据的时候，一次页的查找代表一次 IO，那我们通过主键索引查询的时候，其实最多只需要 2-4 次 IO 操作就可以了

6.3. MyISAM&InnoDB 索引的区别

InnoDB 引擎中，其数据文件本身就是索引文件。而 MyISAM，索引文件和数据文件是分离的

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为非聚簇索引（非聚集索引）

InnoDB 引擎中，其数据文件本身就是索引文件。其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为聚簇索引（聚集索引）。其余的索引都作为 辅助索引 ，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方

InnoDB 引擎中，在根据主索引搜索时，直接找到 key 所在的节点即可取出数据。如果不是主键索引，叶子节点存储的是主键+列值。最终还是要回表，也就是要通过主键再查找一次，即需要先取出主键的值，再走一遍主索引

因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用无序的字段作为主键，这样会造成主索引频繁分裂

6.4. 索引的类型

按照底层存储方式角度划分：

聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引（辅助索引）就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引

按照应用维度划分：

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个
普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有 NULL）
覆盖索引：一个索引包含所有需要查询的字段的值
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替
6.5. 索引的使用场景
全值匹配。如果我们要搜索 username 为 ac 且 age 为 98 的用户，就可以直接使用索引精确定位到
最左匹配。
前缀匹配。如果我们搜索的关键字只是 username 字段的前半部分，那么很明显，也是可以使用索引的，例如搜索所有以 a 开始的 username。
范围匹配。如果我们的搜索条件是一个范围，很明显也可以使用到上述索引，例如搜索姓名介于 ab～cc 之间的用户，只需要先从索引树的根节点开始，先找到 ab，然后根据叶子节点之间的指针顺藤摸瓜，找到 cc 之后的第一个数据（不满足条件的第一个数据）结束。
前面全值匹配，后面范围匹配。
覆盖索引。有的时候，我们搜索的数据都在索引树中了，例如上图中的索引，我们想搜索 username 为 bw 的用户的 age，由于 age 就在索引树中，直接返回即可，这就是覆盖索引了。

6.6. 主键索引（重点）

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

一张数据表有只能有一个主键，并且主键不能为 null，不能重复（即只能有一个字段被设为主键）

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

6.7. 二级索引

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

唯一索引，普通索引，前缀索引等索引属于二级索引

6.8. 自适应哈希索引

InnoDB 索引有一个特性叫做自适应哈希索引，整个过程是全自动的，不需要开发者配置。

当 InnoDB 监控到某些索引值被频繁的访问时，那么它就会在 B+Tree 索引之上，构建一个 Hash 索引，进而通过 Hash 查找来快速访问数据。

默认情况下，自适应哈希索引是开启的状态

6.9. 聚簇索引&非聚簇索引

6.9.1. 聚簇索引的优缺点

优点：

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

缺点：

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

6.9.2. 非聚簇索引的优缺点

优点：

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

缺点：

依赖于有序的数据 ：跟聚簇索引一样，非聚簇索引也依赖于有序的数据
可能会二次查询（回表）：非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据

6.10. 覆盖索引&联合索引

6.10.1. 覆盖索引

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

如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引，那么直接根据这个索引就可以查到数据，也无需回表

6.10.2. 联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引，它可以加速基于多个字段的联合查询操作

例如，在一个订单表中，如果需要根据“订单日期”和“订单状态”两个字段进行查询，那么可以在这两个字段上创建一个复合索引

需要注意的是，复合索引的字段顺序非常重要。例如，如果在上面的订单表中，复合索引的字段顺序是“订单状态”，“订单日期”，那么当查询操作执行时，数据库引擎就无法使用这个复合索引来加速查询，因为索引的第一个字段不是查询条件中的字段。因此，在创建复合索引时，需要根据实际的查询需求和字段组合来选择合适的字段顺序

最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据

6.11. 索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数

6.12. 正确使用索引的一些建议

6.12.1. 选择合适的字段创建索引

不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0、1、true、false 这样语义较为清晰的短值或短字符作为替代
被频繁查询的字段 ：创建索引的字段应该是查询操作非常频繁的字段
被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引
频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
被经常频繁用于连接的字段 ：该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率
被频繁更新的字段不建议使用索引

6.12.2. 限制每张表上的索引数量

索引并不是越多越好，建议单张表索引不超过 5 个

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能

6.12.3. 尽可能的建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升

6.12.4. 注意避免冗余索引

如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的。在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引

6.12.5. 删除长期未使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗

MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用

6.12.6. 分析语句的执行计划

使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息

6.13. 索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

使用 SELECT * 进行查询
创建了复合索引，但查询条件未遵守最左匹配原则
在索引列上进行计算、函数、类型转换等操作
以 % 开头的 LIKE 查询比如 like '%abc'
查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到
数据库表中的数据量过大，导致索引失效。当一张表中的数据量很大时，查询操作可能需要扫描大量的数据，这时索引就可能无法发挥作用。解决方法可以是增加索引、优化查询语句，或者对数据进行分区处理
使用了大量的JOIN操作，导致索引失效。当进行多表JOIN操作时，如果每个表都有自己的索引，那么数据库可能会选择错误的执行计划，导致索引失效。解决方法可以是增加联合索引、优化查询语句，或者对数据进行分区处理
发生隐式转换
7. MySQL 日志（重点）

MySQL日志主要包含：错误日志、查询日志、慢查询日志、事务日志、二进制日志。其中比较重要的有二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）

MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志）

MySQL日志的存在是为了事务执行前、执行时、执行后服务

MySQL日志

7.1. redo log

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

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

每条 redo 记录由 表空间号+数据页号+偏移量+修改数据长度+具体修改的数据 组成

Buffer Pool ：InnoDB查询一条记录，会从硬盘中把一页的数据加载出来，加载出来的数据叫数据页放在 Buffer Pool 中。后续的查询都是先从中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

redo log buffer：更新数据时，将在某个数据页上做了什么修改，记录到重做日志缓存里（redo log buffer），接着刷盘到 redo log 文件里

刷盘：清空 redo log buffer，将其中的内容写到 redo log 文件里

7.1.1. 刷盘时机

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync 对 redo log 进行刷盘

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

页修改之后为什么不直接刷盘呢？

在MySQL数据库中，为了提高性能，数据的修改通常会先被写入到内存中，而不是直接刷盘。这种方式被称为 脏页，表示该页的内容已经被修改但还没有被写入磁盘。直到满足以下条件之一才会被刷盘：

该页被替换出内存：当内存空间不足时，系统会将一些脏页替换出内存，将其写入磁盘。
调度程序触发：MySQL的调度程序会定期检查内存中的脏页数量，如果达到一定阈值，就会触发将这些脏页写入磁盘。
事务提交：在事务提交之前，会将该事务产生的所有脏页写入磁盘

将数据写入磁盘是一种昂贵的操作，频繁地刷盘会导致数据库性能下降。因此，使用脏页机制可以将磁盘写入操作的次数减少，从而提高数据库的性能。

7.1.2. 日志文件组

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写

日志文件组

在个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

write pos 是当前记录的位置，一边写一边后移
checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。

每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录

7.1.3. 总结

redo log 如何保证事务的持久性？

具体来说，当一个事务需要更新数据时，MySQL会将更新操作记录到redo log中，同时将数据缓存在内存中。在数据被写入磁盘之前，MySQL会先将redo log写入磁盘，确保数据更新操作已经被持久化

7.2. binlog

binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于给 ID=2 这一行的 c 字段加 1，属于MySQL的Server层

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志

binlog 会记录所有涉及更新数据的逻辑操作，并且是顺序写

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性

7.2.1. 记录格式

binlog 日志有三种格式，可以通过binlog_format参数指定

statement

记录的内容是SQL语句原文

row

记录的内容不再是简单的SQL语句了，还包含操作的具体数据

通常情况下都是指定为row，这样可以为数据库的恢复与同步带来更好的可靠性

但是这种格式，需要更大的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度

mixed

记录的内容是前两者的混合

MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式

引起数据不一致的情况：

如：执行 update T set update_time=now() where id=1 ，update_time=now() 这里会获取当前系统时间，直接执行会导致与原库的数据不一致

7.2.2. 写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache

可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）

7.3. 两阶段提交

binlog和redo log对比

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

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

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

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

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

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

由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后从库用binlog日志恢复数据时，就会少这一次更新，而主库使用 redo log日志恢复，最终数据不一致

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

两阶段提交

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

场景一：使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务

两阶段提交-回滚

场景二：redo log设置commit阶段发生异常，那会不会回滚事务呢？

并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

7.4. undo log

保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚

MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的

7.4.1. 总结

undo log 如何保证事务的原子性？

当一个事务执行更新操作时，MySQL会将原始数据记录到undo log中，并将新数据写入磁盘。并且，回滚日志会先于数据持久化到磁盘上。如果事务执行过程中发生错误，MySQL可以通过undo log将数据回滚到事务开始之前的状态，从而保证事务的原子性

7.5. 小结

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性

MySQL 使用 undo log(回滚日志) 来保证事务的原子性

MySQL 依靠binlog来同步数据，保证数据一致性

7.6. 慢查询日志

MySQL慢查询日志是MySQL数据库的一种功能，用于记录执行时间超过预设阈值的SQL语句，以便进行性能分析和优化

慢查询日志有什么用

优化查询语句：通过分析慢查询日志，可以找到执行时间较长的SQL语句，然后进行优化，如增加索引、调整查询语句等，从而提高查询性能。

监控数据库负载：MySQL慢查询日志记录了执行时间超过阈值的SQL语句，可以用于监控数据库的负载情况，及时发现数据库负载过高的情况，采取相应的措施避免系统崩溃。

调整系统配置：MySQL慢查询日志的分析还可以帮助DBA或开发人员调整系统配置，如调整缓存大小、调整连接池大小等，从而进一步提高数据库的性能

7.7. 利用日志解决数据一致性问题

这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没有 InnoDB 引擎（InnoDB 引擎是其他公司以插件形式插入 MySQL 的），MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

判断 redo log 是否完整，如果判断是完整的，就立即提交。
如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题

8. MySQL 查询缓存

从 MySQL 5.7.20 开始，就已经默认弃用查询缓存了。在 MySQL 8.0及之后，更是直接删除了查询缓存的功能

-- （query_cache_type 为 ON 表示已经开启）
show variables like '%query_cache%'

9. MySQL 底层知识

9.1. InnoDB 读&写

计算机在存储数据的时候，最小存储单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如 XFS/EXT4）最小单元是块，一个块的大小是 4KB

但是 InnoDB 在进行磁盘操作的时候，并不是以扇区或者块为依据的，是以页为单位的，有时候也称作逻辑页，每个逻辑页的大小默认是 16KB，即四个块。这就意味着，InnoDB 在实际操作磁盘的时候，每次从磁盘上读取数据，至少读取 16KB，每次向磁盘上写数据，也至少写 16KB，并不是你需要 1KB 就读取 1KB，即使你只需要 1KB 的数据，InnoDB 也会从磁盘中将 16KB 的数据读取到内存中。

参考文章

文章1

文章2