# MySQL

# 数据库基础知识

# 数据库，数据库管理系统，数据库系统，数据库管理员

• 数据库（DataBase 简称 DB ）：就是信息的集合，或者说是由数据库管理系统管理的数据的集合。
• 数据库管理系统（Database Management System 简称 DBMS ）：是一种操纵和管理数据库的大型软件，通常用于建立、使用和维护数据库。
• 数据库系统（Data Base System，简称 DBS ）：通常由软件、数据库和数据管理员 (DBA) 组成。
• 数据库管理员（Database Administrator, 简称 DBA ）：负责全面管理和控制数据库系统。

# 元组，码，候选码，主码，外码，主属性，非主属性

• 元组（tuple）：是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。在二维表里，元组也称为行。
• 码：码就是能唯一标识实体的属性，对应表中的列。
• 候选码：若关系中的某一属性或属性组的值能唯一标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号” 是能唯一的区分学生实体的，同时又假设 “姓名”、“班级” 的属性组合足以区分学生实体，那么 {学号} 和 {姓名，班级} 都是候选码。
• 主码：主码也叫主键。主码是从候选码中选出来的。一个实体集中只能有一个主码，但可以有多个候选码。
• 外码：外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。
• 主属性：候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
• 非主属性：不包含在任何一个候选码中的属性称为非主属性。比如在关系 —— 学生（学号，姓名，年龄，性别，班级）中，主码是 “学号”，那么其他的 “姓名”、“年龄”、“性别”、“班级” 就都可以称为非主属性。

# ER 图

ER 图 全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法，用来设计数据库。

ER 图由下面 3 个要素组成：

• 实体：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示。
• 属性：即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示。
• 联系：即实体与实体之间的关系，在 ER 图中用菱形表示，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体间的联系。

下图是一个学生选课的 ER 图，每个学生可以选若干门课程，同一门课程也可以被若干人选择，所以它们之间的关系是多对多（M: N）。另外，还有其他两种实体之间的关系是：1 对 1（1:1）、1 对多（1: N）。

# 🌟数据库三大范式

数据库范式有 3 种：

• 1NF (第一范式)：属性不可再分，即每个属性都是不可分割的原子项。

• 2NF (第二范式)：满足第一范式；且 **不存在部分函数依赖**，即非主属性必须完全依赖于主属性。

  主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分；

• 3NF (第三范式)：满足第二范式；且 **不存在传递函数依赖**，即非主属性必须直接依赖于主属性。

# 不推荐使用外键与级联

对于外键和级联，阿里巴巴开发手册这样说到：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。

• 外键与级联更新适用于单机低并发，不适合分布式、高并发集群；
• 级联更新是强阻塞，存在数据库更新风暴的风险；
• 外键影响数据库的插入速度；

# 存储过程

我们可以把存储过程看成是一些 SQL 语句的集合，中间加了点逻辑控制语句。存储过程在业务比较复杂的时候是非常实用的，比如很多时候我们完成一个操作可能需要写一大串 SQL 语句，这时候我们就可以写有一个存储过程，这样也方便了我们下一次的调用。存储过程一旦调试完成通过后就能稳定运行，另外，使用存储过程比单纯 SQL 语句执行要快，因为存储过程是预编译过的。

存储过程在互联网公司应用不多，因为存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源。

阿里巴巴 Java 开发手册里要求 **禁止使用存储过程**。

# drop、delete 与 truncate 的区别

# 用法不同

• drop (丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。
• truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
• delete （删除数据） : delete from 表名 where 列名=值 ，删除某一行的数据，如果不加 where 子句和 truncate table 表名 作用类似。

注意： truncate 和 delete 只删除数据不删除表的结构 (定义)，执行 drop 语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在。

# 属于不同的数据库语言

• truncate 和 drop 属于 **DDL (数据定义语言)** 语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

• 而 delete 语句是 **DML (数据库操作语言)** 语句，这个操作会放到 rollback segement 中，可以回滚，事务提交之后才生效。

DML 语句和 DDL 语句区别：

• DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。
• DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。
• 二者最大区别： DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。

另外，由于 select 不会对表进行破坏，所以有的地方也会把 select 单独区分开叫做数据库查询语言 DQL（Data Query Language）。

# 执行速度不同

一般来说： drop > truncate > delete （这个我没有设计测试过）。

• delete 命令执行的时候会产生数据库的 binlog 日志，而日志记录是需要消耗时间的，但是也有个好处是方便数据回滚恢复。
• truncate 命令执行的时候不会产生数据库日志，因此比 delete 要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。
• drop 命令会把表占用的空间全部释放掉。

Tips：你应该更多地关注在使用场景上，而不是执行效率。

# 数据库设计通常分为哪几步？

1. 需求分析：分析用户的需求，包括数据、功能和性能需求。
2. 概念结构设计：主要采用 E-R 模型进行设计，包括画 E-R 图 。
3. 逻辑结构设计：通过将 E-R 图转换成 表 ，实现从 E-R 模型到关系模型的转换。
4. 物理结构设计：主要是为所设计的数据库选择合适的存储结构和存取路径。
5. 数据库实施：包括编程、测试和试运行
6. 数据库的运行和维护：系统的运行与数据库的日常维护。

# NoSQL（非关系型数据库）基础

# NoSQL 是什么？

NoSQL （Not Only SQL）非关系型的数据库，主要针对的是键值、文档、图形类型数据存储。并且，NoSQL 数据库天生支持分布式，数据冗余和数据分片等特性，旨在提供可扩展、高可用、高性能的数据存储解决方案。

一个常见的误解是 NoSQL 数据库或非关系型数据库不能很好地存储关系型数据。NoSQL 数据库可以存储关系型数据— 它们与关系型数据库的存储方式不同。

NoSQL 数据库代表：HBase 、Cassandra、MongoDB、Redis。

# 🌟SQL 数据库 和 NoSQL 数据库 的区别

即关系型数据库与非关系型数据库的区别：

# NoSQL 数据库的优势

NoSQL 数据库非常适合许多现代应用程序，例如移动、Web 和游戏等应用程序，它们需要灵活、可扩展、高性能和功能强大的数据库以提供卓越的用户体验。

• 灵活性： NoSQL 数据库通常提供灵活的架构，以实现更快速、更多的迭代开发。灵活的数据模型使 NoSQL 数据库成为 **半结构化和非结构化数据** 的理想之选。
• 可扩展性： NoSQL 数据库通常被设计为通过使用分布式硬件集群来横向扩展，而不是通过添加昂贵和强大的服务器来纵向扩展。
• 高性能： NoSQL 数据库针对特定的数据模型和访问模式进行了优化，这与尝试使用关系数据库完成类似功能相比可实现更高的性能。
• 强大的功能： NoSQL 数据库提供功能强大的 API 和数据类型，专门针对其各自的数据模型而构建。

# NoSQL 数据库有哪些类型？

NoSQL 数据库主要可以分为下面四种类型：

• 键值 ：键值数据库是一种较简单的数据库，其中每个项都包含键和值。这是极为灵活的 NoSQL 数据库类型，因为应用可以完全控制 value 字段中存储的内容，没有任何限制。 Redis 和 DynanoDB 是两款非常流行的键值数据库。
• 文档 ：文档数据库中的数据被存储在类似于 JSON （JavaScript 对象表示法）对象的文档中，非常清晰直观。每个文档包含成对的字段和值。这些值通常可以是各种类型，包括字符串、数字、布尔值、数组或对象等，并且它们的结构通常与开发者在代码中使用的对象保持一致。 MongoDB 就是一款非常流行的文档数据库。
• 图形 ：图形数据库旨在轻松构建和运行与高度连接的数据集一起使用的应用程序。图形数据库的典型使用案例包括社交网络、推荐引擎、欺诈检测和知识图谱。 Neo4j 和 Giraph 是两款非常流行的图形数据库。
• 宽列 ：宽列存储数据库非常适合需要存储大量的数据。Cassandra 和 HBase 是两款非常流行的宽列存储数据库。

下面这张图片来源于 微软的官方文档 | 关系数据与 NoSQL 数据。

# MySQL 常见面试题

# MySQL 基础

# 什么是关系型数据库？

顾名思义， 关系型数据库（RDBMS，Relational Database Management System） 就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性 (ACID)。

常见的关系型数据库：MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite） ......。

# 什么是 SQL？

SQL 是一种 结构化查询语言(Structured Query Language) ，专门用来与数据库打交道，目的是提供一种从数据库中读写数据的简单有效的方法。

几乎所有的主流关系数据库都支持 SQL ，适用性非常强。并且，一些非关系型数据库也兼容 SQL 或者使用的是类似于 SQL 的查询语言。

SQL 可以帮助我们：

• 新建数据库、数据表、字段；
• 在数据库中增加，删除，修改，查询数据；
• 新建视图、函数、存储过程；
• 对数据库中的数据进行简单的数据分析；
• 搭配 Hive，Spark SQL 做大数据；
• 搭配 SQLFlow 做机器学习；
• ......

# 什么是 MySQL？

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

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

# MySQL 有什么优点？

这个问题本质上是在问 MySQL 如此流行的原因。

MySQL 主要具有下面这些优点：

1. 成熟稳定，功能完善。
2. 开源免费。
3. 文档丰富，既有详细的官方文档，又有非常多优质文章可供参考学习。
4. 开箱即用，操作简单，维护成本低。
5. 兼容性好，支持常见的操作系统，支持多种开发语言。
6. 社区活跃，生态完善。
7. 事务支持优秀， InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失，并且 InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的。
8. 支持分库分表、读写分离、高可用。

# MySQL 中主键和 unique 的区别

主键和 UNIQUE 约束都能保证某个列或者列组合的唯⼀性，但是有以下不同：

• ⼀张表中只能定义⼀个主键，却可以定义多个 UNIQUE 约束！

• 主键列不允许存放 NULL，⽽声明了 UNIQUE 属性的列可以存放 NULL ，⽽且 NULL 可以重复地出现在多条记录中！

# 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 在存储时需要使用 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/DOUBLE 是浮点数，只能存储近似的小数值。

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

# 为什么不推荐使用 TEXT 和 BLOB？

TEXT 类型类似于 CHAR（0-255 字节）和 VARCHAR（0-65,535 字节），但可以存储更长的字符串，即长文本数据，例如博客内容。

BLOB 类型主要用于存储二进制大对象，例如图片、音视频等文件。

在日常开发中，很少使用 TEXT 类型，但偶尔会用到，而 BLOB 类型则基本不常用。如果预期长度范围可以通过 VARCHAR 来满足，建议避免使用 TEXT。

数据库规范通常不推荐使用 BLOB 和 TEXT 类型，这两种类型具有一些缺点和限制，例如：

• 不能有默认值
• 在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表（《高性能 MySQL》书中有提到）
• 检索效率较低
• 不能直接创建索引，需要指定前缀长度
• 会消耗大量的网络和 IO 带宽
• 可能导致表上的 DML 操作变慢
• ……

# DATETIME 和 TIMESTAMP 的区别是什么？

DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。

TIMESTAMP 只需要使用 4 个字节的存储空间，但是 DATETIME 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。

• DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

# NULL 和 '' 的区别是什么？

NULL 跟 '' (空字符串) 是两个完全不一样的值，区别如下：

• NULL 代表一个不确定的值，但占用空间。就算是两个 NULL ，它俩也不一定相等。例如， SELECT NULL=NULL 的结果为 false，但是在我们使用 DISTINCT ， GROUP BY ， ORDER BY 时， NULL 又被认为是相等的。
• '' 是长度为 0 的字符串，不占用空间。
• NULL 会影响聚合函数的结果。例如， SUM 、 AVG 、 MIN 、 MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 * ( COUNT(*) )，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名 ( COUNT(列名) )，则会忽略 NULL 值，只统计非空值的个数。
• 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而 '' 是可以使用这些比较运算符的。

因此，MySQL 不建议使用 NULL 作为列默认值。

# Boolean 类型如何表示？

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

# MySQL 基础架构

配合 SQL 语句在 MySQL 中的执行过程 这篇文章来理解 MySQL 基础架构。

一个 SQL 语句在 MySQL 中的执行流程，包括 SQL 的查询在 MySQL 内部会怎么流转，SQL 语句的更新是怎么完成的。

在分析之前先看看 MySQL 的基础架构，知道了 MySQL 由那些组件组成，以及这些组件的作用是什么，可以帮助我们理解和解决这些问题。

# 1、MySQL 基础架构分析

# 1.1、架构概览

下图是 MySQL 的一个简要架构图，可以很清晰地看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的。

从图中可以看出，MySQL 主要由下面几部分构成：

• 连接器：连接管理、身份认证和权限相关 (登录 MySQL 的时候)。
• ** 查询缓存：** 执行查询语句的时候，会先查询缓存，命中则直接返回（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器：对 SQL 语句进行词法分析、语法分析。说白了就是要先看 SQL 语句要干嘛，再检查 SQL 语句语法是否正确。
• 优化器：执行计划生产，索引选择。按照 MySQL 认为最优的方案去执行。
• ** 执行器：** 执行语句，然后从存储引擎返回数据。
• 插件式存储引擎：主要负责数据的存储和读取，采用的是 插件式架构 ，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

MySQL 主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 ** binlog 日志模块**。
• 存储引擎层：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎。其中 InnoDB 引擎有自有的日志模块 ** redolog 日志模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了**。

# 1.2、Server 层的组件介绍

# 1）连接器

连接器主要和 **身份认证和权限相关** 的功能相关，就好比一个级别很高的门卫一样。

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

# 2）查询缓存

从 MySQL8.0 后移除

查询缓存主要 **用来缓存所执行的 SELECT 语句以及该语句的结果集**。

若连接成功建立，执行查询语句的时候，会先查询缓存。

• MySQL 会先校验这个 SQL 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询语句，Value 是结果集。
• 如果缓存 key 被命中，就会直接返回给客户端。
• 如果缓存 key 没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。

MySQL 查询不建议使用缓存，因为表更新会清空表上的所有查询缓存，导致查询缓存频繁失效。假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

# 3）分析器

MySQL 没有命中缓存，那么就会进入分析器。

第一步，词法分析：一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。

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

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

# 4）优化器

优化器的作用就是 **按照它认为的最优执行方案去执行**，比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后，这个语句具体该如何执行就已经定下来。

# 5）执行器

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

# 2、SQL 执行过程分析

# 2.1、DQL 查询语句

select

针对 select 语句：

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

结合上面的说明，我们分析下这个语句的执行流程：

1. 检查该语句是否有权限：
   • 如果没有权限，直接返回错误信息。
   • 如果有权限，在 MySQL8.0 版本以前，会查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，有则直接返回，无则执行下一步。
2. 通过分析器对 SQL 语句进行词法分析、语法分析。
   • 提取关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id='1'。
   • 判断是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
3. 优化器确定执行方案。上面的 SQL 语句，可以有两种执行方案：a. 先查询学生表中姓名为 “张三” 的学生，然后判断是否年龄是 18。b. 先找出学生中年龄 18 岁的学生，然后再查询姓名为 “张三” 的学生。那么优化器根据优化算法选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
4. 执行语句之前，先进行权限校验，如果没有权限就会返回错误信息。如果有权限，执行器就会调用存储引擎接口，返回执行结果。

# 2.2、DML 更新语句

以 update 为例

针对 update 语句：

update tb_student A set A.age='19' where A.name=' 张三 ';

这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志，这就会引入日志模块了。

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

我们就以 InnoDB 模式下来探讨这个语句的执行流程：

1. 先查询到这一条数据（根据 name = 张三），如果有缓存，也是会用到缓存。

2. 然后拿到查询的语句，把 age 改为 19，然后调用存储引擎 API 接口，写入这一行数据。InnoDB 引擎把数据保存在内存中，同时 **InnoDB 引擎记录 redo log，此时 redo log 进入 prepare 状态**。然后告诉执行器，执行完成了，随时可以提交。

3. 执行器收到 InnoDB 引擎的通知后，记录 binlog。然后 **执行器调用引擎接口，提交 redo log 为提交状态**。

4. 更新完成。

为什么要用两个日志模块，用一个日志模块不行吗？

这是因为最开始 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 处于 prepare 状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了：

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

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

# 3、小结

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个归档日志模块（binlog），这个日志模块所有执行引擎都可以共用，而重做日志模块（redolog）只有 InnoDB 有。
• Server 层中各组件的功能：
  • 连接器：管理连接、权限验证；
  • 查询缓存：命中缓存则直接返回结果；
  • 分析器：对 SQL 进行词法分析、语法分析；
  • 优化器：执行计划生成、选择索引；
  • 执行器：操作引擎、返回结果；
  • 存储引擎：存储数据、提供读写接口。
• 引擎层是插件式的，目前主要包括，MyISAM、InnoDB、Memory等。
• 查询语句的执行流程如下：权限校验 ---> 查询缓存（如果命中，直接返回）---> 分析器 ---> 优化器 ---> 权限校验 ---> 执行器 ---> 引擎
• 更新语句执行流程如下：分析器 ----> 权限校验 ----> 执行器 ---> 引擎 ---redo log (prepare 状态)--->binlog--->redo log (commit 状态)
• 笼统点的回答，一条 SQL 的执行过程：
  1. 客户端请求 ->
  2. 连接器（验证用户身份，给予权限） ->
  3. 查询缓存（存在缓存则直接返回，不存在则执行后续操作）->
  4. 分析器（对 SQL 进行词法分析、语法分析） ->
  5. 优化器（选择最优的 SQL 执行方案） ->
  6. 执行器（先检查用户是否有执行权限，有的话才调用引擎接口，执行 SQL）->
  7. 从引擎层获取数据返回（如果开启查询缓存，则会缓存查询结果）

# MySQL 存储引擎

# MySQL 体系结构

1. 连接层：负责客户端和连接服务，例如连接处理、授权认证、及相关的安全方案

2. 服务层：负责 SQL 接口，SQL 的解析和优化，并完成缓存的查询，部分内置函数的执行。

   所有跨存储引擎的功能也在这一层实现，如过程、函数等。

3. 引擎层：负责 MySQL 中数据的存储和读取，根据需要选择合适的存储引擎，索引就是在这一层实现的。

4. 存储层：负责将数据持久化地存储到文件系统中，并完成与存储引擎的交互。

# 存储引擎介绍

存储引擎的特点：

• 存储引擎就是存储数据、建立索引、更新 / 查询数据的实现方式。
• 存储引擎是基于表的，而不是基于库的。
• 默认的存储引擎是 InnoDB。

与存储引擎有关的 SQL 语句：

• 建表时指定存储引擎：

  	create table 表名(
  	...
  	) engine=InnoDB;

• 查询建表语句：

  show create table 表名;

• 查看当前数据库支持的存储引擎：

  show engines;

# 常用的存储引擎

重点介绍三种存储引擎 InnoDB、MyISAM、Memory 的特点。

# InnoDB

（1）介绍

MySQL 5.5 版本开始，默认使用 InnoDB 作为存储引擎。它擅长处理事务，具有崩溃恢复的特性，是一种兼顾高可靠性、高性能的通用存储引擎。

（2）特点

• 支持事务：
  • DML 操作遵循 ACID 模型
  • 实现了 SQL 标准定义了四个隔离级别，具有提交 (commit) 和回滚 (rollback) 事务的能力
  • 提供一组用来记录事务性活动的日志文件
  • InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）
• 支持行级锁：因此 InnoDB 并发写的性能更高
• 支持外键：能维护数据的一致性和完整性（级联删除、级联更新），但对性能有一定的损耗。但阿里的《Java 开发手册》明令禁止使用外键！

（3）存储文件

• .sdi ：表结构
• .ibd 表空间文件 ：数据、索引。每张 InnoDB 表对应一个 .ibd 表空间文件

（4）逻辑存储结构

• 表空间（Tablespace）：即 ibd 文件，由多个 Segment 组成
• 段（Segment）：分为数据段、索引段、回滚段等，由 InnoDB 自身管理，由多个 Extent 组成
• 区（Extent）：固定大小为 1M，由 64 个连续的 Page 组成
• 页（Page）：固定大小为 16 KB，是 InnoDB 磁盘管理的最小单元
• 行（Row）：存放行记录数据，由最后一次事务的 id、回滚指针、各个字段的值组成

# MyISAM

（1）介绍

MyISAM 是 MySQL 早期的默认存储引擎。

（2）特点

• 不支持事务与外键，仅支持表级锁
• 灵活的 AUTO_INCREMENT 字段处理
• 可被转换为压缩、只读表来节省空间

（3）存储文件

• .sdi ：表结构
• .MYD ：数据
• .MYI ：索引

# Memory

（1）介绍

Memory 引擎表的 **数据存储在内存中**，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

（2）特点

• 数据存放在内存中
• 默认采用 hash 索引 结构

（3）文件

• .sdi ：表结构

# 特点对比

MySQL 中常用的三种存储引擎分别是：InnoDB、MyISAM、MEMORY，区别如下：

• InnoDB：支持事务处理、行级锁、外键，拥有崩溃修复能力、并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择 InnoDB 有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择 InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
• MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择 MyISAM 能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。
• MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择 MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的 Memory 引擎，MySQL 中使用该引擎 **作为临时表，存放查询的中间结果**。

# 存储引擎选择

• InnoDB：适用于较多的数据更新操作，对事务、并发、数据完整性要求较高的核心数据。
• MyISAM：适用于大量的数据读操作，常被 MongoDB 取代。
• Memory：因为访问速度快，适用于临时表、缓存，但对表大小有限制（太大的表无法缓存到内存中），并且无法保障数据的安全性，常被 Redis 取代。

# 面试题

# MySQL 支持哪些存储引擎？默认使用哪个？

MySQL 支持多种存储引擎，你可以通过 show engines 命令来查看 MySQL 支持的所有存储引擎。

从上图我们可以查看出，MySQL 5.5.5 之后，默认的存储引擎是 InnoDB。并且，只有 InnoDB 支持事务、行级锁、外键。

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

# MySQL 存储引擎架构了解吗？

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

# MyISAM 和 InnoDB 有什么区别？

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，可谓是风光一时。

虽然，MyISAM 的性能还行，各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM 不支持事务、行级锁和外键，而且最大的缺陷就是崩溃后无法自动恢复数据。

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

言归正传！咱们下面还是来简单对比一下两者：

1. 是否支持事务

• MyISAM 不支持事务，但每次查询都是原子的；

• InnoDB 支持 ACID 的事务，实现了四种隔离级别。具有提交 ( commit ) 和回滚 ( rollback ) 事务的能力；

  并且 InnoDB 默认的 RR 隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

关于 MySQL 事务的详细介绍：4 种隔离级别。

2. 是否支持行级锁

• MyISAM 只支持表级锁，即每次操作都是对整个表加锁；
• InnoDB 不仅支持表级锁，还支持行级锁（默认），因此支持并发写；

3. 是否支持外键

• MyISAM 不支持外键约束；
• InnoDB 支持外键约束；

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

4. 是否存储表的总行数

• MyISAM 存储表的总行数
• InnoDB 不存储表的总行数

5. 存储文件

• 一个 MyISAM 表有三个文件：表结构文件（.sdi)、数据文件（.MYD）、索引文件（.MYI）
• 一个 InnoDB 表有两个文件：表结构文件（.sdi)、数据和索引文件（ .ibd 表空间文件 ）

6. 是否采用聚集索引

• MyISAM 采用非聚集索引，索引文件的数据域存储的是指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性；
• InnoDB 主键索采用聚集索引（索引的数域存数据文件本身），辅助索引的数域存储主键的值。因此从索引查找数据时，需要先通过辅助索引找到主键值，再访问聚集索引。因此最好使用自增主键，防止插入数据时乱序，导致页分裂，性能低下。

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

• MyISAM 不支持

• InnoDB 支持数据库异常崩溃后的安全恢复

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

8. 是否支持 MVCC

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

• MyISAM 不支持，它连行级锁都不支持
• InnoDB 支持 MVCC

9. 索引实现不一样

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

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

• InnoDB 中，其数据文件本身就是（主）索引文件，按 B+Tree 组织的一个索引结构，树的叶节点中 key 域是主键，data 域是完整的数据记录，这被称为 **聚集索引。而其余的索引都作为辅助索引 **，树的叶节点中 key 域是非主键字段，data 域存放的是主键。

  • 在根据主索引搜索时，直接找到 key 所在的节点即可取出完整的数据记录；

  • 而在根据辅助索引查找时，则需要先取出主键的值，然后再走一遍主索引，称为二次查询（回表）。

10. 性能有差别

• MyISAM 的读写不能并发，它的处理能力跟核数没关系

• InnoDB 的性能比 MyISAM 更强大，不管是在读写混合模式下还是只读模式下。且随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。

总结 ：

• InnoDB 支持事务处理，而 MyISAM 不支持。
• InnoDB 支持行级锁，而 MyISAM 只支持表级锁。
• InnoDB 支持外键，而 MyISAM 不支持。
• InnoDB 支持 MVCC，而 MyISAM 不支持。
• 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的索引实现方式不太一样。
• InnoDB 支持数据库异常崩溃后的安全恢复，而 MyISAM 不支持。
• InnoDB 的性能比 MyISAM 更强大。

最后，再分享一张图片给你，这张图片详细对比了常见的几种 MySQL 存储引擎。

# MyISAM 和 InnoDB 如何选择？

大多数情况使用的都是 InnoDB 存储引擎。在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点（可是～我们一般都会介意啊！）。

# 🌟InnoDB 引擎

从 MySQL 5.5 版本开始默认使用 InnoDB 作为存储引擎，它擅长处理事务，具有自动崩溃恢复的特性，在日常开发中使用非常广泛。

# 逻辑存储结构

• 表空间（Tablespace）：
  • 是 InnoDB 逻辑存储结构的最高层
  • 每张表都有一个对应的表空间（.ibd 文件），前提是用户启用了参数 innodb_file_per_table（在 8.0 版本中默认开启）
  • 即 ibd 文件，由多个 Segment 组成
• 段（Segment）：
  • 分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）等
  • 数据段即 B + 树的叶子节点，索引段即 B + 树的非叶子节点
  • 由 InnoDB 自身管理，由多个 Extent 组成
• 区（Extent）：
  • 是表空间的单元结构
  • 固定大小为 1M，由 64 个连续的 Page 组成
• 页（Page）：
  • 是 InnoDB 磁盘管理的最小单元
  • 固定大小为 16 KB
  • 为了保证页的连续性，InnoDB 一次性从磁盘申请 4~5 个区
• 行（Row）：
  • InnoDB 的数据是按行存放的，Row 存放的就是行记录数据
  • 组成情况：
    • Trx_id：最后一次事务的 id。每次对某行记录改动时，都会把对应的事务 id 赋值给 Trx_id
    • Roll_pointer：回滚指针。每次对某行记录改动时，都会把旧版本写入 undo 日志中，该列相当于一个指针，指向该记录修改前的信息
    • 各个字段的值

# 🌟架构

# 概述

下面是 InnoDB 架构图，左侧为内存结构，右侧为磁盘结构。

# 内存结构

# Buffer Pool（缓冲池)

简称 BP，是主内存中的一块区域，作用是缓存表数据与索引。InnoDB 是基于磁盘文件存储的，为了弥补在物理硬盘与内存之间的 IO 访问速度差值，需要把经常使用的数据加载到 BP 中，避免每次访问都进行磁盘 IO。具体地，在执行增删改查时，先操作 BP 中的数据（若无则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘 IO，加快处理速度。

Buffer Pool 的组成：

• 缓存页（Page）：用于缓存表数据与索引
• 控制块：用来描述缓存页，与缓存页一一对应。存储着对应缓存页的所属表空间、数据页编号、以及在 Buffer Pool 中的地址等信息。

BP 以 Page 为单位，默认大小是 128 M，Page 默认大小是 16 K，而控制块的大小约为 Page 的 5%，大概是 800 byte。

注：BP 大小为 128M 指的就是缓存页（Page）的大小。而控制块则一般占 5%，所以每次会多申请 6M 的内存空间。

在专用服务器上，通常将多达 80％ 的物理内存分配给 Buffer Pool。可以通过以下参数设置：

show variables like 'innodb_buffer_pool_size';

如何判断一个页是否缓存在 BP 中？

MySQL 中有一个哈希表数据结构，它的 k-v 结构是（表空间号 + 数据页号，缓冲页对应的控制块）。

因此，当需要访问某个页的数据时，先从哈希表中根据 表空间号 + 数据页号 看看是否存在对应缓冲页的控制块。

• 如果有，则直接使用；
• 如果没有，就从free 链表中选出一个空闲的缓冲页，然后把磁盘中对应的页加载到该缓冲页的位置；

Page 分类

Buffer Pool 以 Page 为单位，底层采用 链表 来管理 Page。在 InnoDB 访问表记录和索引时会在 Page 中缓存，以后使用时，可以减少磁盘 IO 操作。

根据状态，将 Page 分为三种类型：

• free page：空闲 page，未被使用。
• clean page：被使用 page，但数据在刷盘后没有被修改过，与磁盘的数据保持一致。
• dirty page：脏页，被使用 page，但数据在刷盘后被修改过，与磁盘的数据产生了不一致。

Page 管理

InnoDB 通过三种链表结构来维护和管理上述三类 Page 对应的控制块：

• free list：**空闲** 缓冲区，管理 free page

  • Buffer Pool 的初始化过程中，先向操作系统申请连续的内存空间，然后把它划分成若干个【控制块 & 缓冲页】的键值对
  • free list 把所有空闲的缓冲页对应的控制块作为一个个的节点放到链表中
  • 基节点: free list 中只有一个基节点，它不记录任何缓存页的信息（单独申请空间），只记录当前 free list 的头节点地址、尾节点地址，以及当前 free list 的节点个数
  • 从磁盘中将数据页进行缓存的流程：
    1. 从 free list 中取出一个空闲的控制块（对应缓冲页）
    2. 根据数据页所在的表空间、页号之类的信息，填写该控制块的信息
    3. 把该缓冲页对应的 free list 节点（即控制块）从链表中移除，表示该缓冲页已经被使用了

• flush list：**需要刷盘** 的缓冲区，管理 dirty page，按修改时间排序

  注：脏页既存在于 flush list，也在 LRU list 中，但是两种互不影响。LRU list 负责管理 page 的可用性和释放，而 flush list 负责管理脏页的刷盘操作。

  • InnoDB 为了提高处理效率，在每次修改缓冲页后，并不是立刻把修改刷新到磁盘上，而是在未来的某个时间点进行刷盘操作。
  • 所以需要使用 flush list 存储脏页，凡是被修改过的缓冲页对应的控制块都会作为节点加入到 flush list 中。
  • flush list 的结构与 free list 相似

• lru list：**正在使用** 的缓冲区，管理 clean page 和 dirty page

  有点复杂，略了...

# Change Buffer（更改缓冲区）

略了...

可以在 Buffer Pool 中进行合并处理，减少磁盘 IO。

# Adaptive Hash Index（自适应 hash 索引）

参数：adaptive_hash_index

InnoDB 会监控对表上各索引页的查询，如果观察到 hash 索引可以提升索引页的查询速度，则自动建立 hash 索引，无需人工参与，称之为自适应 hash 索引。

• hash 索引的等值匹配性能高于 B + 树的：因为 hash 索引一般只需要一次 IO 即可；而 B + 树，可能需要几次匹配
• 但是，hash 索引又不适合做范围查询、模糊匹配等

# Log Buffer（日志缓冲区）

作用：

• 用来保存要写入到磁盘中的日志数据（ redo log 、 undo log ），数据会定期刷新到磁盘中，默认大小为 16MB。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O

• 用来优化每次更新操作之后都要写入 redo log 而产生的磁盘 IO 问题

• Log Buffer 空间满了后会自动写入磁盘。可以通过将 innodb_log_buffer_size 参数调大，以减少磁盘 IO 频率

# 磁盘结构

实在是太抽象了，略了。。。

# System Tablespace (系统表空间)

# File-Per-Table Tablespaces（每个表的文件表空间）

# General Tablespaces（通用表空间）

# Undo Tablespaces（撤销表空间）

# Temporary Tables（临时表空间）

# Doublewrite Buffer Files（双写缓冲区文件）

# Redo Log（重做日志）

# 后台线程

# Master Thread（核心后台线程）

负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo 页的回收。

# IO Thread（IO 线程）

在 InnoDB 存储引擎中大量使用了 AIO 来处理 IO 请求，这样可以极大地提高数据库的性能，而 IO Thread 主要负责这些 IO 请求的回调。

# Purge Thread（回收线程）

主要用于回收事务已经提交了的 undo log，在事务提交之后，undo log 可能不用了，就用它来回收。

# Page Cleaner Thread（脏页刷新线程）

协助 Master Thread 刷新脏页到磁盘，可以减轻 Master Thread 的工作压力，减少阻塞。

# 🌟事务原理

# 事务基础

关于事务的基础知识，具体可见 MySQL 事务。

• 原子性（ Atomicity ）：事务是不可分割的最小操作单元，内部的所有操作要么全部成功，要么全部失败（要么全部 commit 成功，要么全部失败 rollback）；

• 一致性（ Consistency ）：事务完成前后，数据库必须保持一致状态，即都是合理的数据状态。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

• 隔离性（ Isolation ）：数据库提供的隔离机制，可以保证事务在不受外部并发操作影响的独立环境下运行。即并发访问数据库时，一个事务不会干扰其他事务的运行，该事务所做的修改在最终提交前，对其他事务是不可见的。各并发事务之间，数据库是独立的；

• 持久性（ Durability ）：事务一旦提交，它对数据库中数据的更改就是持久的。即使数据库发生故障，这种更改也不会丢失。

那么实际上，InnoDB 引擎是如何保证事务的四大特性（ACID）的呢？而对于这四大特性，实际上分为两个部分：

• 原子性（A）、一致性（C）、持久性（D）：由 InnoDB 中的 redo log 日志和 undo log 日志来保证
• 隔离性（I）：通过数据库的锁和 MVCC来保证的

# redo log

redo log 是重做日志，记录事务提交时数据页的物理修改。当刷新脏页到磁盘发生错误时，进行数据恢复，用来实现事务的持久性。由两部分组成：

• redo log buffer：在内存中的缓冲区
• redo log file：在磁盘中的文件

如果没有 redo log，可能会存在什么问题？

在 InnoDB 中的内存结构中，主要的内存区域就是 Buffer Pool（缓冲池），在缓冲池中缓存了很多的数据页。

当我们在一个事务中，执行多个增删改的操作时，InnoDB 会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载到缓冲区中，然后修改缓冲池中的数据，修改后的数据页我们称为脏页。而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。

但缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如将脏页刷盘的过程出错了，但提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。

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

有了 redo log 之后，

• 当对缓冲区的数据进行增删改之后，会首先将对缓冲页的变化记录在 redo log buffer 中
• 在每次事务提交时，会将内存中的  redo log buffer 的数据刷新到磁盘文件  redo log file 中
• 过一段时间之后，如果刷新缓冲区的脏页到磁盘时发生错误，此时就可以借助于 redo log 进行数据恢复，这样就保证了事务的持久性
• 而如果脏页成功刷新到磁盘，或者涉及到的数据已经落盘，此时 redo log 就可以删除了，所以存在的两个 redo log 文件是循环写的

为什么每次提交事务，要刷新 redo log 到磁盘中，而不是直接将 buffer pool 中的脏页刷新到磁盘呢？

因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。 而 redo log 在向磁盘文件中写入数据，日志文件都是顺序写的。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为 WAL（Write-Ahead Logging） 。

# undo log

undo log（回滚日志）是在执行 DML 语句的时候产生的便于数据回滚的日志，用于记录数据被修改前的信息。

与 redo log 记录物理日志不一样，undo log 是逻辑日志，记录的是逻辑相反的操作信息。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录；反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。

undo log 主要有两个作用：

1. 提供事务回滚（保证事务的原子性）：当事务回滚时将数据恢复到修改前的样子

2. MVCC（多版本并发控制）：当读取记录时，若该记录被其他事务占用，或者当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

undo log 销毁：undo log 在事务执行时产生，事务提交时，并不会立即删除 undo log，因为这些日志可能还用于 MVCC。

undo log 存储：undo log 采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含 1024 个 undo log segment。

# 🌟InnoDB 如何实现事务

InnoDB 通过 Buffer Pool、Log Buffer、Redo Log、Undo Log 来实现事务，以一条 update 语句为例：

1. InnoDB 在收到一条 update 语句后，会先根据条件找到数据所在的页，并将该页缓存在 Buffer Pool 中

2. 针对 update 语句生成 Undo Log 日志，用于后续事务回滚

3. 执行器 执行 update 语句，修改 Buffer Pool 中的数据，即内存中的数据

4. 针对 update 语句生成一个 Redo Log 对象，并写入 Log Buffer 中

5. 如果事务提交，那么将 Log Buffer 中的 Redo Log 对象持久化到磁盘中的 Redo Log File 中

   后续还有其他机制将 Buffer Pool 中所修改的数据页持久化到磁盘中（脏页刷盘）

6. 如果事务回滚，则利用 Undo Log 日志进行回滚

# 🌟对 MVCC 的实现方式

# 🌟InnoDB 为什么要采用 MVCC 快照读 ？

这是因为一个事务的操作有可能成功 commit，也有可能失败 rollback。在一个事务 commit 之前，被其他事务读到还没提交的变更记录，会产生数据不一样的现象（脏读），这种情况就是 InnoDB 最低的隔离级别 READ UNCOMMITTED，可以读到没有 commit 的数据。

那么如果想要不产生脏读，容易想到的是采用锁的方式，当一个事务更改某行记录，就加上锁，其他并发事务等待该事务执行完毕才能读取到该行记录。但是这样做的话 **会产生大量的锁占用与等待，效率是非常低下的，因此 InnoDB 采用了 MVCC 的方式**。

简单的说，在 RU 隔离级别下，若 A 事务变更某行记录，InnoDB 会产生对应的 undo log，如果接下来 A 事务进行回滚，InnoDB 可以根据 undo log 将记录回滚到事务开始之前的状态。在 A 事务没有结束时，如果 B 事务来查询该行记录，B 事务会根据 A 事务变更后的记录值（在内存中）加上 undo log “计算” 出 A 事务开始前的该行记录值，从而读取到该行记录的一个快照，其中并不会产生锁与等待。

如果是 RR 的隔离级别（默认隔离级别），B 事务进行过程中看到的始终会是 B 事务开始前的记录行快照信息，不管 B 事务进行过程中 A 事务有没有完成；

如果是 RC 的隔离级别，B 事务进行过程中，可以看到 A 事务提交对记录行修改值（即如果 A 事务没有完成，B 查询到的是 A 事务开始前的记录值，如果 A 事务完成了，B 事务查询到的是 A 事务完成后的记录值），在这种情况下会产生不可重复读的现象，即同一次事务中多次查询看到的结果会不一样。

# 基本概念

# 快照读（一致性非锁定读）

就是简单的 SELECT 语句（不加锁），是非阻塞读。

快照即记录的历史版本，每行记录可能存在多个历史版本（多版本技术），读取的是记录数据的可见版本。

快照读的情况下，如果读取的记录正在执行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照（历史数据）。

只有在事务隔离级别 RC (读取已提交) 和 RR（可重读）下，InnoDB 才会使用快照读（一致性非锁定读）：

• 在 RC 级别下，快照读总是读取被锁定行的最新一份快照数据。
• 在 RR 级别下，快照读总是 **读取本事务开始时的行数据版本（快照）**。
• 在 SERIALIZABLE 级别下，快照读退化成当前读。

快照读比较适合对数据一致性要求不是特别高，且追求极致性能的业务场景。

对于快照读的实现，通常做法是加一个 **版本号或者时间戳字段**，

• 更新数据时，版本号 + 1 或者更新时间戳。
• 查询数据时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见

在 InnoDB 中，多版本控制（multi versioning）就是对快照读的实现。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上 X 锁的释放。相反地，InnoDB 会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读（snapshot read）。

# 当前读（一致性锁定读）

读取行记录时会添加 X 锁或 S 锁，以防其他并发事务修改当前记录，因此 **读取的是记录的最新版本**，是悲观锁的一种操作。

• select ... lock in share mode ：对记录加 S 锁，其它事务也可以加 S 锁，但如果加 X 锁则会被阻塞
• select ... for update 、 insert 、 update 、 delete ：对记录加 X 锁，且其它事务不能加任何锁

当前读的一些常见 SQL 语句类型如下：

select ... for update 仅适用于 InnoDB，且必须在事务块 (BEGIN/COMMIT) 中才能生效。在进行事务操作时，通过 for update 语句，MySQL 会对查询结果集中每行数据都添加 **排他锁**，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

InnoDB 行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

select ... for share ：我选择一些记录，这些记录可以 share，其他事务也可以读，但是如果你要修改，不好意思，我加了一个 s 锁，你是不可以修改的。这个语句的应用场景之一是 **用来读取到最新的数据**。

select ... for update ：我选择一些记录，这些 select 的记录是我下一步要 update 的，你要读或者修改这些记录，不好意思，我加的是 x 锁，你读不了也改不了。只有我当前事务提交了，这些记录你才可以读到或者修改。这个语句的应用场景之一是 **为了防止更新丢失**。

# 对读的记录加一个 S 锁（MySQL 5.7 和 MySQL 8.0）

SELECT...LOCK IN SHARE MODE

# 对读的记录加一个 S 锁（MySQL 8.0）

SELECT...FOR SHARE

# 对读的记录加一个 X 锁

SELECT...FOR UPDATE

# 对修改的记录加一个 X 锁

INSERT...

UPDATE...

DELETE...

在快照读下，即时读取的记录已被其它事务加上 X 锁，这时记录也是可以被读取的，即读取的快照数据。上面说了，在 RR 隔离级别下 MVCC 防止了部分幻读。这里的 “部分” 是指在 快照读 情况下，只能读取到第一次查询之前所插入的数据（根据 Read View 判断数据可见性，Read View 在第一次查询时生成）。

但是！如果是 当前读 ，每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。

所以，InnoDB 在实现 RR 隔离级别时，如果执行的是 当前读 ，则会对读取的记录使用 临键锁（Next-key Lock） ，来防止其它并发事务在间隙间插入数据。

在测试中，即使是在默认的 RR 隔离级别下，事务 A 中依然可以读取到事务 B 最新提交的内容，因为在查询语句后面加上了 lock in share mode 共享锁，此时是当前读操作。当然，当我们加排他锁的时候，也是当前读操作。

# MVCC（多版本并发控制）

MVCC 的全称是 Multi-Version Concurrency Control ，是一种并发控制机制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。MVCC 在每个数据行上维护多个版本的数据，形成一条由 undo log 组成的版本链，使得读写操作没有冲突。

MVCC 解决的问题：读 — 写冲突的无锁并发控制。为事务分配单向增长的时间戳，为每个数据修改保存一个版本（与事务时间戳相关联）。读操作只读取该事务开始前的数据库快照。

• 能解决并发读 - 写问题：可以做到读操作不阻塞写操作，同时写操作也不会阻塞读操作

• 能解决脏读、幻读、不可重复读等一致性问题，但不能解决写 - 写中的修改丢失问题。

1、读操作（SELECT）：

当一个事务执行读操作时，它会使用快照读取。在 InnoDB 默认的 RR 隔离级别下，快照读取是基于事务开始时数据库中的状态创建的，因此事务不会读取到其他事务尚未提交的修改。具体工作情况如下：

在 RR 级别下

• 对于读取操作，事务会查找符合条件的数据行，并选择符合事务开始时间的数据版本进行读取。
• 如果某个数据行有多个版本，事务会选择不晚于事务开始时间的最新版本，确保事务只读取在它开始之前已经存在的数据。
• 事务读取的是快照数据，因此其他并发事务对数据行的修改不会影响当前事务的读取操作，当前事务对快照数据的修改也不会影响实际的数据行。

2、写操作（INSERT、UPDATE、DELETE）：

当一个事务执行写操作时，它会为要修改的数据行创建一个新的版本（快照数据），对其修改后再写入数据库。具体工作情况如下：

• 对于写操作，事务会为要修改的数据行创建一个新的版本，并将修改后的数据写入新版本。
• 新版本的数据会带有当前事务的版本号，以便其他事务能够正确读取相应版本的数据。
• 原始版本的数据仍然存在，供其他事务使用快照读取，这保证了其他事务不受当前事务的写操作影响。

3、事务的提交和回滚（COMMIT、ROLLBACK）：

• 当一个事务提交时，它所做的修改将成为数据库的最新版本，并且对其他事务可见。
• 当一个事务回滚时，它所做的修改将被撤销，对其他事务不可见。

4、版本的回收：

为了防止数据库中的版本无限增长，MVCC 会定期进行版本的回收。回收机制会删除已经不再需要的旧版本数据，从而释放空间。

MVCC 通过创建数据的多个版本和使用快照读取来实现并发控制。读操作使用旧版本数据的快照，写操作创建新版本，并确保原始版本仍然可用。这样，不同的事务可以在一定程度上并发执行，而不会相互干扰，从而提高了数据库的并发性能和数据一致性。

# 🌟InnoDB 对 MVCC 的实现方式

InnoDB 对 MVCC 的具体实现依赖于：隐藏字段、ReadView、undo log。

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

这里看不懂没关系，先看下文

# 隐藏字段

InnoDB 为每行记录添加了三个隐藏字段：

前两个字段是肯定会添加的，是否添加最后一个字段 DB_ROW_ID，得看当前表有没有主键或者唯一非空索引，若有则不会添加该隐藏字段。

• DB_TRX_ID （6 字节）：插入这行记录或者最后一次修改该记录的事务 id；

  delete 操作在内部被视为更新（即逻辑删除），会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除

• DB_ROLL_PTR （7 字节）：回滚指针，指向该行对应的 undo log，即上一个版本（存于回滚段中）

  如果该行记录未被更新，则为空；

• DB_ROW_ID （6 字节）：当前行记录隐含的自增 ID，如果当前表没有设置主键且没有唯一非空索引时，InnoDB 会使用 DB_ROW_ID 来生成一个聚集索引；

如上图，DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID 是当前操作该记录的事务 ID，而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo log，指向上一个旧版本。

# undo log

undo log 介绍

undo log（回滚日志）是在执行 DML 语句的时候产生的便于数据回滚的日志，用于记录数据被修改前的信息。

与 redo log 记录物理日志不同，undo log 是逻辑日志，记录的是逻辑相反的操作信息。因此当执行 rollback 时，就可以直接从 undo log 中的逻辑记录读取到相应的内容并进行回滚。

undo log 主要有两个作用：

1. 提供事务回滚（保证事务的原子性）：当事务回滚时将数据恢复到修改前的样子

2. MVCC（多版本并发控制）：当读取记录时，若该记录被其他事务占用，或者当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

在 InnoDB 中 undo log 分为两种：

• insert undo log ：在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，只在回滚时需要，因此 insert undo log 在事务提交后可以直接删除，不需要进行 purge 操作。

  （insert 时的数据初始状态）

• update undo log ：update 或 delete 操作中产生的 undo log。update undo log 不仅在回滚时需要，可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。事务提交时放入 undo log 链表，等待 purge 线程进行最后的删除。

  （数据第一次被修改时）
  （数据第二次被修改时）

版本链

不同事务或者相同事务对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录。

有一张表原始数据为：

DB_TRX_ID ：代表最近修改事务 ID，记录插入这条记录或最后一次修改该记录的事务 ID，是自增的。
DB_ROLL_PTR ：由于这条数据是刚刚才插入的，没有被更新过，所以该字段值为 null。

然后，有四个并发事务同时在访问这张表。

A. 第一步：

当事务 2 执行第一条修改语句时：

1. 先记录 undo log 日志，记录数据变更之前的样子；
2. 然后更新记录，并且记录本次操作的事务 ID 与回滚指针。其中回滚指针用来指定如果发生回滚，回滚到哪一个版本；

B. 第二步

当事务 3 执行第一条修改语句时：

1. 先记录 undo log 日志，记录数据变更之前的样子；
2. 然后更新记录，并且记录本次操作的事务 ID 与回滚指针；

C. 第三步

当事务 4 执行第一条修改语句时：

1. 先记录 undo log 日志，记录数据变更之前的样子；
2. 然后更新记录，并且记录本次操作的事务 ID 与回滚指针；

# Read View

Read View 说白了就是事务进行快照读操作时产生的读视图。在该事务执行快照读的那一刻，会生成一个当前数据库系统的快照，该快照记录并维护了系统当前活跃事务的 ID（当每个事务开启时，都会被分配一个 ID, 这个 ID 是递增的，所以最新的事务，ID 值越大）。

Read View 是用来做 **可见性判断的，里面保存了当前对本事务不可见的其他活跃的（未提交的）事务**。当某个事务执行快照读的时候，会对该记录创建一个 Read View，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录在 undo log 里面的某个版本的数据。

class ReadView {

  /* ... */

private:

  trx_id_t m_low_limit_id;      /* 表示目前出现过的最大的事务 ID + 1，大于等于这个 ID 的事务均不可见 */

  trx_id_t m_up_limit_id;       /* 表示活跃事务列表 m_ids 中最小的事务 ID，小于这个 ID 的事务均可见 */

  trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务 ID */

  trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */

  ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */

  m_closed;                     /* 标记 Read View 是否 close */

}

主要有以下字段：

• m_ids ：ReadView 创建时，其他活跃的（即未提交的）事务 ID 列表。创建 ReadView 时，将当前所有未提交的事务的 ID 记录下来，后续即使它们修改了行记录的值，对于当前事务也是不可见的

  不包括当前事务自己和已提交的事务（正在内存中）

• 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_creator_trx_id ：创建该 ReadView 的事务 ID

# 数据可见性算法

在 InnoDB 中，创建一个新事务后，执行每个 select 语句前，都会创建一个 Read View。Read View 中保存了当前数据库系统中正处于活跃（即没有 commit）的事务的 ID 号。

简单的说，保存的是系统中当前不应该被本事务看到的其他事务的 ID 列表（即 m_ids）。当用户在本事务中要读取某个记录行的时候，InnoDB 会将该记录行的 DB_TRX_ID 与 Read View 中的一些变量及当前事务 ID 进行比较，判断是否满足可见性条件。具体的比较算法如下：

参与比较的对象：

1. DB_TRX_ID ：表示插入这行记录或者最后一次修改该行记录的事务 id
2. m_creator_trx_id ：表示创建该 ReadView 的事务 ID
3. m_up_limit_id ：ReadView 中的一个变量，表示活跃事务列表 m_ids 中最小的事务 ID。事务 ID 小于它的事务均已提交，因此小于这个 ID 的数据版本均可见
4. m_low_limit_id ：ReadView 中的一个变量，表示下一个将被分配的事务 ID，即目前出现过的最大的事务 ID + 1。事务 ID 大于它的事务均未提交，因此大于等于这个 ID 的数据版本均不可见

changes_visible() 返回 true 代表可见， false 代表不可见。

# 🌟RC 和 RR 下 MVCC 的差异

在事务隔离级别 RC 和 RR 下，虽然 InnoDB 都通过 MVCC 来读取快照数据（一致性非锁定读），但它们生成 ReadView 的时机不同：

• RC：在事务中的每一次执行快照读时，都会生成并设置新的 Read View，所以会导致不可重复读！
• RR：仅在事务中的第一次执行快照读时，生成一个 Read View（m_ids 列表），后续复用该 ReadView。因此在一个事务中，执行两次相同的 select 语句，查询到的结果是一样的。

# RC 下 ReadView 的生成情况

具体示例略了...

# RR 下 ReadView 的生成情况

具体示例略了...

# 🌟MVCC➕Next-key Lock 防止幻读

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

InnoDB 在 RR 级别下通过 MVCC 和 临键锁（Next-key Lock）来解决幻读问题：

1、执行普通 select 时会以 MVCC 快照读的方式读取数据

理所当然能防止 “幻读”

在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询时生成 Read View ，并复用直至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”。

2、执行 select...for update/lock in share mode、insert、update、delete 等当前读

得益于临键锁（Next-key Lock）才能防止 “幻读”

在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 临键锁（Next-key Lock） 来防止幻读。当执行当前读时，会锁定读取到的记录，同时会锁定它们的间隙，防止其它并发事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读。

# 🌟MySQL 索引

# 🌟索引原理

索引（Index）是一种帮助 MySQL 高效获取数据的有序数据结构，协助 MySQL 快速查询、更新表中数据。MySQL 支持多种类型的索引，包括 B-tree 索引、哈希索引、全文索引等。

索引的基本原理如下：

1. 把创建索引列的内容进行排序
2. 对排序的结果生成倒排表
3. 在倒排表内容上拼接上数据行地址
4. 查询数据时，先拿到倒排表内容，在取出数据行地址，从而拿到具体的数据

数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址，如果没有索引的话，我们要从千万行数据里面检索一条数据，只能依次遍历这张表的全部数据， 直到找到这条数据。

但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

# 索引介绍

索引是一种帮助存储引擎快速查询和检索数据的数据结构（有序）。

索引的底层数据结构存在很多种类型，常见的索引结构有：红黑树、B 树、B + 树、Hash。

在 MySQL 中，Innodb 和 MyIsam，都使用 B + 树 作为索引结构。

# 索引的优缺点

优点：

• 使用索引可以大大加快数据的检索速度（大大减少检索的数据量），这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

• 创建和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 索引需要使用物理文件存储，也会耗费一定空间。

# 🌟索引的底层数据结构选型

不同存储引擎对索引结构的支持情况：

# Hash 表

哈希索引使用哈希算法将索引列的值映射到哈希表中，然后通过哈希表的查找算法快速定位到目标数据。哈希索引的优点是查找速度非常快，但是它不支持范围查找，只适用于等值查找。

哈希表是键值对的集合，通过键 (key) 即可快速取出对应的值 (value)，因此哈希表可以快速检索数据（接近 O（1））。

通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

hash = hashfunc(key)

index = hash % array_size

但是！哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是链地址法。链地址法就是将哈希冲的突数据存放在链表中。

比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后 HashMap 为了减少链表过长时搜索时间过长引入了红黑树。

为了减少 Hash 冲突的发生，一个好的哈希函数应该 “均匀地” 将数据分布在整个可能的哈希值集合中。

InnoDB 存储引擎不直接支持常规的哈希索引，但存在一种特殊的自适应哈希索引（Adaptive Hash Index），结合了 B+Tree 和哈希索引的特点，以便更好地适应实际应用中的数据访问模式和性能需求。自适应哈希索引的每个哈希桶实际上是一个小型的 B+Tree 结构。这个 B+Tree 结构可以存储多个键值对，而不仅仅是一个键。这有助于减少哈希冲突链的长度，提高了索引的效率。关于 Adaptive Hash Index 的详细介绍，可以查看 MySQL 各种 “Buffer” 之 Adaptive Hash Index 这篇文章。

既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？主要是因为 Hash 索引不支持排序、范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。

试想一种情况:

SELECT * FROM tb1 WHERE id < 500;

在这种范围查询中，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗？这就是 Hash 最大的缺点了。

# 全文索引（Full-text）

全文索引是一种特殊的索引类型，它可以用于对文本数据进行全文检索。全文索引的基本原理是将文本数据分词，然后将每个词作为关键字建立索引。全文索引可以用于对文本数据进行模糊匹配、关键字搜索等操作。

# 二叉查找树（BST）

二叉查找树（Binary Search Tree）是一种基于二叉树的数据结构，它具有以下特点：

1. 左子树所有节点的值均小于根节点的值。
2. 右子树所有节点的值均大于根节点的值。
3. 左右子树也分别为二叉查找树。

二叉查找树的时间复杂度：

• 当二叉查找树平衡时，也就是树的每个节点的左右子树深度相差不超过 1 的时候，查询的时间复杂度为 $O(logn)$，具有比较高的效率。
• 然而，当二叉查找树不平衡时，例如在最坏情况下（有序插入节点），树会退化成线性链表（也被称为斜树），相当于全表扫描，导致查询效率急剧下降，时间复杂退化为 $O（N）$。

也就是说，二叉查找树的性能非常依赖于它的平衡程度，这就导致其不适合作为 MySQL 底层索引的数据结构。

为了解决这个问题，并提高查询效率，人们发明了多种在二叉查找树基础上的改进型数据结构，如平衡二叉树、B-Tree、B+Tree 等。

# AVL 树（自平衡的 BST）

AVL 树的特点是保证任何节点的左右子树高度之差不超过 1，因此也被称为高度平衡二叉树。它的查找、插入和删除在平均和最坏情况下的时间复杂度都是 $O(logn)$。

AVL 树采用了旋转操作来保持平衡。主要有四种旋转操作：LL 旋转、RR 旋转、LR 旋转和 RL 旋转。其中 LL 旋转和 RR 旋转分别用于处理左左和右右失衡，而 LR 旋转和 RL 旋转则用于处理左右和右左失衡。

由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了查询性能。并且， 在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO。 磁盘 IO 是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

实际应用中，AVL 树使用的并不多。

# 红黑树（自平衡的 BST）

与 AVL 树一样，红黑树也是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换、旋转操作，使得树始终保持平衡状态，它具有以下特点：

1. 每个节点非红即黑；
2. 根节点总是黑色的；
3. 每个叶子节点都是黑色的空节点（NIL 节点）；
4. 如果节点是红色的，则它的子节点必须是黑色的（反之不一定）；
5. 从根节点到叶节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）。

和 AVL 树不同的是，红黑树并不追求严格的平衡，而是大致的平衡，因此红黑树的查询效率稍有下降。因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。

也正因如此，红黑树的插入和删除操作效率大大提高了，因为红黑树在插入和删除节点时只需进行 O (1) 次数的旋转和变色操作，即可保持基本平衡状态，而不需要像 AVL 树一样进行 O (logn) 次数的旋转操作。

红黑树的应用还是比较广泛的，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异的。

# 🌟B 树 & B + 树

最常用

B 树全称为多路平衡查找树 ，B+ 树是 B 树的一种变体，其中 B 是 Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B-tree 索引可以在$O(log n)$ 的时间复杂度内查找数据，基本原理如下：

1. 将索引列的值按照一定的顺序存储在 B-tree 中
2. 然后通过 B-tree 的查找算法快速定位到目标数据
3. B-tree 索引的叶子节点存储了指向数据行的指针，因此可以通过 B-tree 索引快速定位到目标数据行

B 树与 B + 树的区别：

• 数据存储方式：

  • B 树中，每个节点都存放索引列值（key）和数据（data）；

  • B+ 树中，只有叶子节点才同时存放 key 和 data，其他内节点只存放 key。

    这样 B+ 树中每个内节点可以存储更多 key 值，从而降低 B + 树的高度，提高检索效率。

• 叶子节点结构：

  • B 树的叶子节点都是独立的；

  • B+ 树的所有叶子节点构成一个有序的双向链表。

    因此 B+ 树可以按照 key 有序遍历全部记录，且便于范围查询、顺序遍历。

• 数据检索方式：

  • B 树的检索的过程相当于对范围内的每个节点的 key 做二分查找，因为非叶子节点也存储了数据，所以可能还没有到达叶子节点，检索就结束了，具有更短的查询路径。

  • B+ 树的所有数据都存储在叶子节点上，因此检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

• 范围查询效率：

  • 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；
  • B+ 树的范围查询，只需要对有序的双向链表进行遍历即可。

• 适用场景：

  • B 树适合进行 **随机读写操作**，因为每个节点都包含数据；
  • B+ 树适合 **范围查询、顺序遍历**，因为数据都存储在叶子节点上，且叶子节点之间使用有序双向链表连接。

综上，B + 树与 B 树相比，具备 **更高，更稳定的查询效率、更适用于范围查询和顺序遍历** 这些优势。

# 🌟MySQL 选择 B+ 树作为索引结构的原因

1、为什么不使用二叉查找树（BST）？

二叉查找树可能因为失衡严重出现“斜树”，退化为线性链表，相当于全表扫描，高度太高了，查找效率不稳定。

2、为什么不使用平衡二叉树？

平衡二叉树解决了二叉树高度太高，查找效率不稳定的问题。但是，平衡二叉树的每个节点只存储一个键值和数据，如果数据非常的多，二叉树的结点将会非常多，高度也会很高，查找效率降低。

3、为什么不使用 B 树？

• B 树只适合随机检索，而 B + 树同时支持随机检索和顺序 / 范围检索；

• B + 树的空间利用率更高：因为 B + 树的内部节点（非叶子节点，也称索引节点）不存储数据，只存储索引值，相比较 B 树来说，B + 树一个节点可存储更多的索引值，使得整颗 B + 树变得更矮，减少 I/O 次数，磁盘读写代价更低，I/O 读写次数是影响索引检索效率的最大因素；

• B + 树查询效率更加稳定：因为在 B + 树中，顺序检索比较明显，随机检索时，由于 B + 树所有的 data 域（结点中存储数据元素的部分）都在根节点，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径相同，导致每一个关键字的查询效率基本相同，时间复杂度固定为 O (log n)；而 B 树搜索有可能会在非叶子节点结束，越靠近根节点的记录查找时间越短，其性能等价于在关键字全集内做一次二分查找，查询时间复杂度不固定，与 key 在树中的位置有关，最好情况为 O (1)；

• B + 树范围查询性能更优：因为 B + 树的叶子节点使用了指针顺序（链表）从小到大地连接在一起，B + 树叶节点两两相连可大大增加区间访问性，只要遍历叶子节点就可以实现整棵树的遍历；而 B 树的叶子节点是相互独立的，每个节点 key（索引）和 data 在一起，则无法查找区间；

  根据空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。若访问节点 key 为 50，则 key 为 55、60、62 的节点将来也可能被访问，可利用磁盘预读原理提前将这些数据读入内存，减少了磁盘 IO 的次数。当然 B + 树也能够很好的完成范围查询，比如同时也会查询 key 值在 50-70 之间的节点。

• B + 树增删文件（节点）时效率更高：因为 B + 树的叶子节点包含了所有关键字，并以有序的链表结构存储。

# 索引分类

按照数据结构分类：

• B + 树索引：MySQL 里默认的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现索引都是使用 B+Tree，但二者实现方式不一样。
• hash 索引：类似键值对的形式，一次即可定位。
• R 树索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
• 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR 、 VARCHAR ， TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

按照底层存储方式划分：

• 聚集索引：聚集索引的叶子节点存储的是整行记录。可以直接查找到数据，查询速度快。每个表只能有一个聚集索引。例如 InnoDB 引擎中的主键索引。

  在 MySQL 中，InnoDB 引擎表的 .ibd 表空间文件包含了该表的索引和数据，因此该表的索引 (B + 树) 的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

• 非聚集索引：非聚集索引的叶子节点存储的是索引列的值、指向对应行记录的指针。查询速度相对较慢。MyISAM 引擎，无论主键还是非主键，使用的都是非聚集索引。

按照应用维度划分：

• 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
• 普通索引：仅加速查询。
• 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
• 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
• 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR 、 VARCHAR ， TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

MySQL 8.x 中实现的索引新特性：

• 隐藏索引：也称为不可见索引，不会被优化器使用，但是仍然需要维护，通常会软删除和灰度发布的场景中使用。主键不能设置为隐藏（包括显式设置或隐式设置）。
• 降序索引：之前的版本就支持通过 desc 来指定索引为降序，但实际上创建的仍然是常规的升序索引。直到 MySQL 8.x 版本才开始真正支持降序索引。另外，在 MySQL 8.x 版本中，不再对 GROUP BY 语句进行隐式排序。
• 函数索引：从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值，也就是在索引中可以包含函数或者表达式。

# 主键索引

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

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

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

# 二级索引

二级索引（Secondary Index）又称为辅助索引，是一种非聚集索引，因为二级索引的叶子节点仅仅存储索引列的值、对应行记录的主键 key。也就是说，通过二级索引，可以定位主键的位置，再根据主键索引查找行记录 data，这就是二次查询（回表），因此二级索引的查询速度较慢。

唯一索引，普通索引，前缀索引、全文索引都属于二级索引：

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

# 聚集索引

聚集索引（Clustered Index）的叶子节点存储的是整行记录。可以直接查找到数据，查询速度快。每个表只能有一个聚集索引。例如 InnoDB 引擎中的主键索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd 表空间文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引 (B + 树) 的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

优点：

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

缺点：

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

# 非聚集索引

非聚集索引（Non-Clustered Index）的叶子节点存储的是索引列的值、指向对应行记录的指针。查询速度相对较慢。二级索引 (辅助索引) 就属于非聚集索引。MyISAM 引擎，无论主键还是非主键，使用的都是非聚集索引。

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

优点：

• 更新代价比聚集索引要小：因为非聚集索引的叶子节点是不存放数据的

缺点：

• 依赖于有序的数据：跟聚簇索引一样，非聚簇索引也依赖于有序的数据

• 可能会二次查询 (回表查询): 这应该是非聚簇索引最大的缺点了。当查到非聚集索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

  非聚集索引不一定回表查询。

  试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

  SELECT name FROM table WHERE name='guang19';

  那么这个索引的 key 本身就是要查询的字段，查到对应的 name 直接返回就行了，无需回表查询。

  即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表，因为它的主键索引的叶子节点存放的是指针。但是！如果 SQL 查的就是主键呢？

  SELECT id FROM table WHERE id=1;

  主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

这是 MySQL 的 InnoDB、MyISAM 表的文件截图：

InnoDB、MyISAM、Memory 这三种存储引擎的存储文件内容：

• 无论哪种引擎，表的结构都存储在 .sdi 文件中
• 对于 InnoDB，表的索引和数据都存储在 .ibd 表空间文件 中
• 对于 MyISAM，表的索引存储在 .MYI 文件中，表的数据存储在 .MYD 文件中
• 对于 Memory，表的索引和数据都存储在 内存 中

# 🌟聚集索引和非聚集索引的区别

聚集索引：

• 数据存储方式：表中的数据与索引按顺序存放在一块，找到索引也就找到了数据，即数据的物理存放顺序与索引顺序是一致的

• 叶节点的 k-v：（主键，行记录）

• 唯一性：表中只能有一个聚集索引，通常是主键（因为主键要求其值在表中唯一且非空）

• 查询性能：范围查询、排序查询的性能高，但插入速度严重依赖于插入顺序；更新主键的代价很高。

  按照主键顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键。

  更新主键会导致被更新的行移动。因此对于 InnoDB 表，一般定义主键为不可更新。

非聚集索引：

• 数据存储方式：索引和实际数据的物理存储是分离的，索引中存储了指向实际数据行的指针
• 叶节点的 k-v：（索引列，指向行记录的指针）
• 唯一性：表可以有多个非聚集索引，包括唯一索引。非聚集索引不要求索引列的值唯一
• 查询性能：插入数据的速度快，但查询可能涉及回表，速度较慢

# 🌟倒排索引

先讲正排索引，意思就是我们的所有文档都有唯一一个文档 id，根据文档里的内容算出每个文档中关键字的内容和次数，类似于通过 key 去找 value 的形式，如果正牌索引，我们每次寻找关键字查询，就得搜索所有的文档去看是否有这个关键字，这样查询效率太慢了。

于是有了倒排索引，是通过关键字去查文档，我们建立一个索引库，里面的 key 是关键字，value 是每个文档的 id，倒排在构建索引的时候较为耗时且维护成本较高，但是搜索耗时短，所以我们可以定时去更新索引库。

正排索引和倒排索引是搜索引擎中的两种索引类型。

• 正排索引：每个文档都有一个唯一的文档 ID，按照文档 ID 等有序的方式将每个文档存储在索引中，通过文档 ID 进行检索。这种方式类似于数据库表的行，可以很方便地根据文档 ID 检索到具体的文档，但是不适合处理大规模文档库的情况。
• 倒排索引：按照单词或关键字将文档进行索引，并记录包含该词汇的文档列表。

倒排索引的优势在于查找包含某个项的文档，即用于搜索查询；相反，正排索引的优势是确定哪些项是否存在单个文档里。

二者均是在 index-time 时创建，保存在 Lucene 文件中（序列化到磁盘）。

# 🌟覆盖索引

我们都知道 InnoDB 中索引分为两类：

• 聚集索引（主键索引）：（主键，除主键外的完整行记录）
• 非聚集索引（辅助索引 / 普通索引）：（索引列的值，主键）

想要搞清除什么是覆盖索引，首先得弄懂 **回表查询**：当使用普通索引（辅助索引）进行查询时，先得到行记录的主键值（但仍未得到所需的所有列数据），然后再根据主键值到聚集索引（主键索引）中找到对应的完整行记录。

由于回表导致多次扫描索引树，会降低查询效率。那么如何避免回表查询？

常用的方法是：将查询需要的字段添加到索引中，建立联合索引。比如上面提到的查询语句，可以将 (NAME,CITY) 做成一个联合索引，这样在首次扫描索引树的时候就能从索引树本身获取到需要的所有信息，从而避免了回表。这里使用的方法就是覆盖索引。

---

举个回表查询的例子，对于 InnoDB 表 EMPLOYEE，由三个字段（ID、NAME、CITY）组成，其中在 ID 上建立了主键索引（聚集索引），在 NAME 上建立了普通索引。

现在需要进行一次如下的查询：

select NAME,CITY from EMPLOYEE where NAME="Lee";

首先通过普通索引去找到 NAME="Lee" 对应的叶节点，获取到行记录的主键值（ID=8）。而该查询语句还需要获取 CITY 字段的值，于是 MySQL 要再根据该主键值（ID=8）通过主键索引查找，从而获得完整的数据。这个过程就是所谓的回表查询。

如果一个 **索引包含了所有需要查询的字段**，就称之为覆盖索引（Covering Index）。在 InnoDB 中，如果不是主键索引，叶子节点存储的是索引列值 + 主键。最终还是要 “回表”，也就是要通过主键再查找一次，这样就会比较慢。而覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！因为所查询的字段都在当前索引的叶子节点上存在，可以直接作为结果返回了。

** 覆盖索引，即需要查询的字段正好是索引的字段，那么直接根据该索引就可以查到数据了，而无需回表查询。** 因此，应该尽量使用覆盖索引，减少 select * 。

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

# 联合索引

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

以 score 和 name 两个字段建立联合索引：

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

# 🌟最左前缀匹配原则

最左前缀法则：查询条件中的各个列必须是联合索引中从最左边开始的连续子列。

• 如果跳跃了中间的某一列，那么联合索引将会部分失效（后面的字段索引都失效）。
• 如果跳跃了最左列，那么联合索引将会全部失效，转而进行全表扫描！

注意：联合索引的最左边字段必须在 SQL 的查询条件中存在，但是与 SQL 的查询条件中编写的字段先后顺序无关。

因此对于这种情况，联合索引是全部生效的！

所以，在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

在联合索引的使用中，如果 SQL 出现范围查询（如 > 、 < ），那么范围查询右侧的列索引会失效！

但对于 >= 、 <= 、 BETWEEN 、 like 前缀匹配的范围查询，并不会停止匹配。

# 索引下推

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

# 🌟索引失效

索引失效后导致使用全表扫描来查询数据，这也是慢查询的主要原因之一，查询中导致索引失效的常见情况有这些：

1. MySQL 自身评估使用全表扫描的查询速度比使用索引的更快：MySQL 在查询时，会评估全表扫描的查询效率与使用索引的效率，如果全表扫描更快，则放弃索引，走全表扫描。这种情况一般是 SELECT * 语句。

2. 创建了联合索引，但查询条件未遵循最左前缀匹配原则，或者涉及范围查询（如大于或小于）。

   指路→最左前缀匹配原则

3. 查询条件中，对索引列进行了数学运算、函数等操作：因为索引保存的是索引字段的原始值，而不是表达式计算后的值 / 函数计算后的值，自然无法走索引了，只能通过把索引字段的取值都取出来，然后依次进行表达式计算 / 函数计算来进行条件判断，因此采用的就是全表扫描的方式。

4. 查询条件中，对索引列进行以 % 开头的 LIKE 模糊查询：当我们使用左或者左右模糊匹配的时候，也就是 like % xx 或者 like % xx% 这两种方式都会造成索引失效。因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

5. 查询条件中，OR 前后存在非索引的列：因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有一个条件列不是索引列，就会进行全表扫描。要想使用 OR，又想让索引生效，只能将 OR 条件中的每个列都加上索引。

6. 查询条件中，IN /NOT IN 的取值范围较大：导致索引失效，走全表扫描；

7. 发生隐式类型转换：当索引列是字符串类型时，若查询条件中数据不加单引号，虽然对于查询结果没什么影响，但是数据库会进行隐式类型转换，导致索引列失效。

8. ……

推荐阅读这篇文章：美团暑期实习一面：MySQl 索引失效的场景有哪些？。

# 🌟索引的正确使用建议

索引设计原则

# 避免索引失效

上一节

# 针对数据量大，且查询频繁的表建立索引

基数较小的表，索引效果较差，没有必要在此列建立索引。

# 选择合适的字段创建索引

• 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据值为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

• 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。

• 常作为查询条件（where）、排序（order by）、分组（group by）操作的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

• 被经常频繁用于连接子句中的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

# 被频繁更新的字段应该慎重建立索引

因为字段的更新会导致索引的更新，而维护索引的成本也是不小的。因此如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

# 限制每张表上的索引数量

索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

建议单张表索引不超过 5 个！

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

# 尽量考虑联合索引，而不是单列索引

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

# 注意避免冗余索引

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

# 针对字符串类型的字段，使用前缀索引（短索引）

前缀索引（短索引）仅限于字符串类型，只对字符串的一部分前缀建立索引，大大节约索引空间，提高索引效率。

# 删除长期未使用的索引

不用的索引会造成不必要的性能损耗。MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。

# 不能有效区分数据的字段不适合做索引列

如性别（男 / 女 / 未知），最多也就三种，区分度实在太低。

# 利用 EXPLAIN 命令分析 SQL 是否走索引查询

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

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

EXPLAIN 的输出格式如下：

各个字段的含义如下：

篇幅问题，我这里只是简单介绍了一下 MySQL 执行计划，详细介绍请看：MySQL 执行计划分析这篇文章。

# MySQL 执行计划

# 各个字段

# id

SELECT 标识符，是查询中 SELECT 的序号，用来标识整个查询中 SELELCT 语句的顺序。

• id 如果相同，从上往下依次执行。
• id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。

# select_type

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

• SIMPLE：简单查询，不包含 UNION 或者子查询。
• PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
• SUBQUERY：子查询中的第一个 SELECT。
• UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
• DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
• UNION RESULT：UNION 查询的结果。

# table

查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

• <unionM,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果；
• <derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
• <subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。

# type（重要）

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下（按照执行效率从低到高的顺序）：

• All（全表扫描）：最坏的情况，因为采用了全表扫描的方式

• index（全索引扫描）：查询遍历了整棵索引树，和 all 差不多，只不过扫描的是索引，而索引一般在内存中，速度更快。只不过 index 对索引表进行全扫描。这样做的好处是不再需要对数据进行排序，但是开销依然很大。

  所以要尽量避免全表扫描和全索引扫描。

• range（对索引进行范围扫描）：一般在 where 子句中使用 <、>、in、between 等关键词，只检索给定范围的行，属于范围查找。执行计划中的 key 列表示哪个索引被使用了。

  从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

• ref（非唯一索引扫描）：采用了非唯一索引，或者是唯一索引的非唯一性前缀，查询结果可能返回多条符合条件的行。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

• eq_ref（唯一索引扫描）：使用主键索引或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

• const：使用了主键或者唯一索引与常量值进行比较，表中最多只有一行匹配的记录，一次查询就可以找到。比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快；而 eq_ref 通常用于多表联查中。

• system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。

下面这个不知道性能如何...

• index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。

# possible_keys

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

# key（重要）

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

# key_len

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

# rows

rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

# Extra（重要）

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

• Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
• Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
• Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
• Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
• Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
• Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

# 🌟explain 的结果有哪些？有哪些信息去告诉你怎么优化？

对于执行计划，参数有：

• possible_keys ：可能用到的索引；
• key ：实际用的索引。如果这一项为 NULL，说明没有使用索引；
• key_len ：索引的长度；
• rows ：扫描的数据行数；
• type ：数据扫描类型；
• Extra ：MySQL 解析查询的额外信息；

# type 字段

type 字段描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的 **执行效率从低到高的顺序为**：

• All（全表扫描）：最坏的情况，因为采用了全表扫描的方式

• index（全索引扫描）： 和 all 差不多，只不过 index 对索引表进行全扫描。这样做的好处是不再需要对数据进行排序，但是开销依然很大。

  所以要尽量避免全表扫描和全索引扫描。

• range（索引范围扫描）：一般在 where 子句中使用 <、>、in、between 等关键词，只检索给定范围的行，属于范围查找。

  从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

• ref（非唯一索引扫描）：采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回的数据可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

• eq_ref（唯一索引扫描）：使用主键索引或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

• const（结果只有一条的主键或唯一索引扫描）：使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快；而 eq_ref 通常用于多表联查中。

# Extra 字段

这里说几个重要的参考指标：

• Using filesort：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
• Using temporary：使用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
• Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

# MySQL 查询缓存

# 介绍

执行查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实用。

my.cnf 加入以下配置，重启 MySQL 开启查询缓存：

query_cache_type=1

query_cache_size=600000

MySQL 执行以下命令也可以开启查询缓存：

set global  query_cache_type=1;

set global  query_cache_size=600000;

如上，开启查询缓存后，在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。

查询缓存不命中的情况：

1. 任何两个查询在任何字符上的不同都会导致缓存不命中。
2. 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的系统表，其查询结果也不会被缓存。
3. 缓存建立之后，MySQL 的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么相关的所有缓存数据都将失效。

** 缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。** 因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十 MB 比较合适。

此外，还可以通过 sql_cache 和 sql_no_cache 来控制某个查询语句是否需要缓存：

select sql_no_cache count(*) from usr;

# 总结

MySQL 中的查询缓存虽然能够提升数据库的查询性能，但是查询同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。

查询缓存是一个适用较少情况的缓存机制。如果你的应用对数据库的更新很少，那么查询缓存将会作用显著。比较典型的如博客系统，一般博客更新相对较慢，数据表相对稳定不变，这时候查询缓存的作用会比较明显。

简单总结一下查询缓存的适用场景：

• 表数据修改不频繁、数据较静态
• 查询（Select）重复度高
• 查询结果集小于 1 MB

对于一个更新频繁的系统来说，查询缓存缓存的作用是很微小的，在某些情况下开启查询缓存会带来性能的下降。

简单总结一下查询缓存不适用的场景：

• 表中的数据、表结构或者索引变动频繁
• 重复的查询很少
• 查询的结果集很大

《高性能 MySQL》这样写到：

根据我们的经验，在高并发压力环境中查询缓存会导致系统性能的下降，甚至僵死。如果你一定要使用查询缓存，那么不要设置太大内存，而且只有在明确收益的时候才使用（数据库内容修改次数较少）。

确实是这样的！实际项目中，更建议使用本地缓存（比如 Caffeine）或者分布式缓存（比如 Redis），性能更好，更通用一些。

# 🌟MySQL 三大日志

binlog、redo log、undo log

# 🌟简介

MySQL 日志 主要包括错误日志、查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属：

• 二进制日志： binlog （归档日志）
• 事务日志： redo log （重做日志）、 undo log （回滚日志）

下面对这三种日志进行简介：

• binlog（二进制日志 / 归档日志）：是逻辑日志，记录了语句的原始逻辑。无论用哪种存储引擎，只要表发生了数据更新，都会产生 binlog 日志。主要用于主从复制（读写分离），在主从复制中，从库利用主库上的 binlog 进行重播，实现主从数据同步。
• redo log（重做日志）：是物理日志，记录了在某个数据页上做了什么修改。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而确保事务的持久性（D）。
• undo log（回滚日志）：是逻辑日志，记录的是逻辑相反的操作信息。可以 **用于回滚，当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚，以确保事务的原子性（A）。同时可以提供 MVCC 下的读（即非锁定读 / 快照读）**。

# redo log（重做日志）

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

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

InnoDB 表的读写流程：

1. MySQL 中数据以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 缓冲池 Buffer Pool 中。
2. 后续的查询都是先从 缓冲池 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。更新表数据的时候，也是如此，发现缓冲池 Buffer Pool 里存在要更新的数据，就直接在缓冲池 Buffer Pool 里更新。
3. 然后会把 “在某个数据页上做了什么修改” 记录到 ** 重做日志缓存（ redo log buffer ）** 里
4. 最后，清空 redo log buffer，并刷盘到 redo log file 中

# 刷盘时机

小贴士：每条 redo 记录由 “表空间号 + 数据页号 + 偏移量 + 修改数据长度 + 具体修改的数据” 组成。

InnoDB 将 redo log buffer 刷到磁盘上有几种情况：

1. 事务提交：当事务提交时，redo log buffer 里的 redo log 会被刷新到磁盘（可以通过 innodb_flush_log_at_trx_commit 参数控制，后文会提到）。

2. log buffer 空间不足时：当 log buffer 的容量被占满大约一半时，就需要把这些日志刷新到磁盘上。

3. 事务日志缓冲区（transaction log buffer）满时：InnoDB 使用一个事务日志缓冲区来暂时存储事务的重做日志条目。当缓冲区满时，会触发日志的刷新，将日志写入磁盘。

4. 定期执行 Checkpoint（检查点）操作时：InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘的数据）刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性。

5. 后台线程会周期性刷盘：InnoDB 启动了一个后台线程，负责周期性（每隔 1 秒）地把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

6. 正常关闭服务器时：MySQL 关闭的时候，redo log 都会刷入到磁盘里去。

总之，InnoDB 在多种情况下会刷新重做日志，以保证数据的持久性和一致性。

我们要注意设置正确的 **刷盘策略参数 innodb_flush_log_at_trx_commit **。根据 MySQL 配置的刷盘策略的不同，MySQL 宕机之后可能会存在轻微的数据丢失问题。该参数的取值有 3 种，也就是共有 3 种刷盘策略：

• 0：设置为 0 的时候，表示每次事务提交时不进行刷盘操作，而是等待后台线程定期（1 秒）刷盘。这种方式性能最高，但是也最不安全，因为如果 MySQL 挂了或宕机了，可能会丢失最近 1 秒内的事务。

• 1：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作。这种方式性能最低，但是也最安全，因为只要事务提交成功，redo log 记录就一定在磁盘里，不会有任何数据丢失。

• 2：设置为 2 的时候，表示每次事务提交时都只把 log buffer 里的 redo log 内容写入 page cache（文件系统缓存）。page cache 是专门用来缓存文件的，这里被缓存的文件就是 redo log 文件。这种方式的性能和安全性都介于前两者中间。

# 日志文件组

硬盘上存储的 redo log 是以一个日志文件组的形式出现的，每个日志文件的大小都一样。

比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么整个 redo log 日志文件组可以记录 4G 的内容。

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。

在日志文件组中还有两个重要的属性：

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

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

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

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

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录， MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

在 MySQL 8.0.30 之前，可以通过 innodb_log_files_in_group 和 innodb_log_file_size 配置日志文件组的文件数和文件大小。

但在 MySQL 8.0.30 及之后的版本中，这两个变量已被废弃，即使被指定也是用来计算 innodb_redo_log_capacity 的值。而日志文件组的文件数则固定为 32，文件大小则为 innodb_redo_log_capacity / 32 。

# 小结

思考一个问题：只要每次把修改后的数据页直接刷盘不就好了，还有 redo log 什么事？

它们不都是刷盘么？差别在哪里？

1 Byte = 8bit

1 KB = 1024 Byte

1 MB = 1024 KB

1 GB = 1024 MB

1 TB = 1024 GB

实际上，数据页大小是 16KB ，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，有必要把完整的数据页刷盘吗？

而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。

如果是写 redo log ，一行记录可能就占几十 Byte ，只包含表空间号、数据页号、磁盘文件偏移量、更新值，再加上是顺序写，所以刷盘速度很快。

所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

其实内存的数据页在一定时机也会刷盘，我们把这称为页合并，讲 Buffer Pool 的时候会对这块细说

# binlog（归档日志）

redo log 它是物理日志，记录内容是 “在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，会记录所有涉及更新数据的逻辑操作，并且是顺序写。类似于 “给 ID=2 这一行的 c 字段加 1”，属于 MySQL Server 层。

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

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

# 记录格式

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

• statement：记录的是 **SQL 语句原文**。比如执行一条 update T set update_time=now() where id=1 ，记录的内容如下：

  同步数据时，会执行记录的 SQL 语句，但是有个问题， update_time=now() 这里会获取当前系统时间，直接执行会导致与原库的数据不一致。为了解决这种问题，我们需要指定为 row 。

• row：防止数据不一致，记录的是 **包含具体操作数据的 SQL 语句**，需要通过工具 mysqlbinlog 解析。这样就能保证同步数据的一致性，通常情况下都是指定为 row ，这样可以为数据库的恢复与同步带来更好的可靠性。

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

• mixed：一种折中的方案，记录的是 **前两者的混合**。MySQL 会判断这条 SQL 语句是否可能引起数据不一致，如果是，就用 row 格式，否则就用 statement 格式。

# 写入机制

binlog 的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

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

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

binlog 日志刷盘流程如下：

• write：把 binlog 日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
• fsync：将数据持久化到磁盘的操作

write 和 fsync 的时机，可以由 ** 参数 sync_binlog ** 控制，默认是 1。

为 0 的时候，表示每次提交事务都只 write ，由系统自行判断什么时候执行 fsync 。

虽然性能得到提升，但是机器宕机， page cache 里面的 binlog 会丢失。

为了安全起见，可以设置为 1 ，表示每次提交事务都会执行 fsync ，就如同 redo log 日志刷盘流程 一样。

还有一种折中方式，可以设置为 N(N>1) ，表示每次提交事务都 write ，但累积 N 个事务后才 fsync 。

在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。

同样的，如果机器宕机，会丢失最近 N 个事务的 binlog 日志。

# 🌟两阶段提交

解决 redo log 和 binlog 的一致性问题

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

为了解决两份日志之间的逻辑一致问题， InnoDB 存储引擎使用 两阶段提交 方案。原理很简单，将  redo log 的写入拆成了两个步骤  prepare 和  commit 。

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

再看一个场景， redo log 设置 commit 阶段发生异常，此时不会回滚事务。它会执行下图框住的逻辑，虽然 redo log 是处于 prepare 阶段，但是能通过事务 id 找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务恢复数据。

这意味着一个事务到底有没有成功，看 redo log 里面有没有 commit 记录，如果有 commit 记录，那么 binlog 一定是持久化成功了，也就是说事务成功了。

# undo log（回滚日志）

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚。在 MySQL 中，恢复机制是通过 ** 回滚日志（undo log）** 实现的。

所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用回滚日志中的信息将数据回滚到修改之前的样子即可！

并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

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

# 总结

InnoDB 引擎使用 redo log (重做日志) 保证事务的持久性，使用 undo log (回滚日志) 来保证事务的原子性。

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

# 常见面试题

# MySQL 中常见的日志有哪些？

MySQL 中常见的日志有以下几种：

• 归档日志（binlog）：记录了所有修改了数据库结构或数据内容的操作，以便在主从复制时将这些操作同步到从库。二进制日志是逻辑日志，记录的是对数据库结构或数据内容的修改。
• 重做日志（redo log）：redo log 是 MySQL 的一种日志，是 InnoDB 存储引擎独有的，它让 MySQL 拥有了崩溃恢复能力。redo log 只记录该存储引擎中表的修改，而 binlog 是在数据库层面产生的，所有存储引擎对数据库进行修改都会产生 binlog。redo log 是物理日志，记录的是对数据页的修改，而不是对数据的修改。
• 回滚日志（undo log）：是 InnoDB 存储引擎独有的，记录了所有修改了数据的操作，以便在事务回滚时撤销这些操作。回滚日志是逻辑日志，记录的是对数据的修改，而不是对数据页的修改。
• 错误日志（errorlog）：记录了 MySQL 服务器启动、运行过程中出现的错误信息。
• 慢查询日志（slow query log）：记录了执行时间超过指定阈值的 DQL 语句，因此可以查看该日志来优化 DQL 语句。慢查询日志默认是不开启的。
• 一般查询日志（general log）：记录了 MySQL 服务器接收到的所有 SQL 语句，包括执行时间、执行结果等信息。一般查询日志可以用于调试和安全审计。
• 中继日志（relay log）：是 MySQL 主从复制结构中从节点上的日志，用于保存主节点传输过来的数据变更事件，然后将这些事件应用于从节点。

# 慢查询日志有什么用？

用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10 秒以上的 SQL 语句。慢查询日志可以帮助开发人员找出哪些语句的执行效率低，以便进行优化。

# MySQL 如何优化一个慢查询语句？

指路→慢查询语句优化

# binlog 主要记录了什么？

binlog 是 MySQL 的二进制日志，主要记录了对 MySQL 数据库进行的所有更改操作，包括插入、更新和删除操作。binlog 可以用于数据恢复、数据同步和数据备份等方面 。

# 介绍一下 redo log，以及它如何保证事务的持久性？

redo log 是 MySQL 的一种日志，是 InnoDB 存储引擎独有的，它让 MySQL 拥有了崩溃恢复能力。redo log 只记录该存储引擎中表的修改，而 binlog 是在数据库层面产生的，所有存储引擎对数据库进行修改都会产生 binlog。

redo log 是 MySQL 数据库用于保证事务的持久性的机制之一。在执行一个事务时，MySQL 会将事务所做出的修改操作先写入 redo log 而不是直接保存到磁盘，也就是 WAL（write ahead log)机制 。这是因为将数据直接写入磁盘的开销较大，而将修改操作写入 redo log 可以大幅减少磁盘 IO 的次数。

WAL（write ahead log）机制的基本思想：

在修改数据之前，先将修改操作记录到一个日志文件中，然后再将修改操作应用到数据库中。这样，即使在修改数据的过程中出现了故障，也可以通过日志文件来恢复数据 。

当一个事务提交后，MySQL 会从 redo log 中读取相应的修改操作，并将其写入磁盘。只要 redo log 已经持久化（即已经写入磁盘），那么就可以保证故障发生后能够完成数据恢复。如果 MySQL 在执行修改操作时遇到了宕机等问题，在重启后会重新应用 redo log 里未被应用的操作，从而实现事务的持久性。

# 数据页是什么？

数据页是 InnoDB 存储引擎中的一个重要概念，它是 InnoDB 存储引擎中的最小存储单位。InnoDB 存储引擎将表空间分为多个大小相等的数据页，每个数据页默认大小为 16KB。在 InnoDB 存储引擎中，每个表都有一个独立的表空间，表空间由多个数据文件组成。当表中的数据发生变化时，InnoDB 存储引擎会将修改操作记录到 redo log 中，并将修改操作应用到内存中的数据页中。当内存中的数据页被修改后，InnoDB 存储引擎会将修改操作异步刷新到磁盘上的数据文件中，从而保证了数据的持久性。

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

在数据库中，将修改的数据直接刷盘（写入磁盘）可能会产生一些性能开销。这是因为在执行随机磁盘 IO 的过程中，磁盘的寻址时间和磁道切换时间等所需的开销较大，说明磁盘 IO 时需要耗费较长的时间。因此直接刷盘会导致大量的磁盘 I/O，降低系统的性能。

为了避免这种情况，InnoDB 存储引擎采用了一种称为 “ 脏页刷盘 ” 的机制。在数据库缓存中，如果一个数据页上的数据已经被修改了，但还没有被同步写入磁盘，那么这个数据页就被称为 “ 脏页 ”。 脏页刷盘 机制是指 InnoDB 存储引擎在将内存中的数据页刷新到磁盘上时，会根据一定的策略来决定哪些数据页需要被刷新到磁盘上，哪些数据页可以暂时不刷新。

• 当内存中的数据页被修改前，InnoDB 存储引擎会将修改操作记录到 redo log 中，并将修改操作应用到内存中的数据页中。
• 当内存中的数据页被修改后，InnoDB 存储引擎会将修改操作异步刷新到磁盘上的数据文件中，从而保证了数据的持久性。在这个过程中，InnoDB 存储引擎会根据一定的策略来决定哪些数据页需要被刷新到磁盘上，哪些数据页可以暂时不刷新。这样可以避免大量的磁盘 I/O，提高系统的性能。

# binlog 和 redolog 有什么区别？

binlog 和 redolog 都是 MySQL 数据库中的日志文件，用于记录数据库的修改操作。它们的主要区别在于：

1. binlog 是 MySQL 的归档日志，用于记录所有的修改操作，包括数据的增删改操作和表结构的变更操作等。binlog 的作用是用于数据恢复、主从复制和数据同步等场景。

2. redolog 是 InnoDB 存储引擎的事务日志，用于记录事务的修改操作。redolog 的作用是用于保证事务的原子性、一致性和持久性。当一个事务提交时，会将该事务所做的所有修改操作先记录在 redo log 中并刷到磁盘上，然后再将这些修改操作同步到数据库的数据文件中。

区别:

• binlog 是整个数据库实例级别的，记录了对整个数据库实例进行的所有更改操作，而 redolog 是每个InnoDB 存储引擎实例级别的，只记录了在该存储引擎实例中发生的事务更改操作。
• binlog 是对数据库执行的语句级别的日志，redolog 是对事务级别的日志，记录了事务的具体操作信息。
• binlog 通常用于 JDBC 等客户端进行异地灾难恢复，而 redolog用于保证事务的 ACID 属性（原子性、一致性、隔离性和持久性）。

因为 binlog 记录了所有的修改操作，所以它比 redolog 更加全面，但也更加耗费磁盘空间。而 redolog 只记录了事务的修改操作，所以它比 binlog 更加高效，但也更加局限。

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

undo log 是 InnoDB 存储引擎的回滚日志，用于记录事务的回滚操作。

当一个事务需要回滚时，InnoDB 存储引擎会根据 undo log 中的信息将数据页恢复到事务开始之前的状态。因此，undo log 可以保证事务的原子性。

在 InnoDB 存储引擎中，每个事务都有一个对应的 undo log，用于记录该事务所做的修改操作。当事务提交时，InnoDB 存储引擎会将 undo log 中的信息删除，从而释放磁盘空间。如果事务回滚，则 InnoDB 存储引擎会根据 undo log 中的信息将数据页恢复到事务开始之前的状态。这样可以保证事务的原子性。

# 🌟MySQL 事务

# 事务

事务是一个完整的业务逻辑，内部的所有操作要么全部成功，要么全部失败。

# 数据库事务

对于数据库来说，事务是由批量的 DML 语句所构成的逻辑整体，要么全执行成功，要么全执行失败。

# 开启一个事务

START TRANSACTION;

# 多条 SQL 语句

SQL1,SQL2...

## 提交事务

COMMIT;

# 🌟ACID 特征

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

• 原子性（ Atomicity ）：事务是不可分割的最小操作单元，内部的所有操作要么全部成功，要么全部失败；

  由 Undo Log 日志保证，记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的 SQL 语句。

• 一致性（ Consistency ）：事务完成前后，数据库必须保持一致状态，即都是合理的数据状态。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

  由 A、I、D 共同保证

• 隔离性（ Isolation ）：数据库提供的隔离机制，可以保证事务在不受外部并发操作影响的独立环境下运行。即并发访问数据库时，一个事务所做的修改在最终提交前，对其他并发事务是不可见的。各并发事务之间，数据库是独立的；

  由 MVCC 保证

• 持久性（ Durability ）：事务一旦提交，它对数据库中数据的更改就是持久的。即使数据库发生故障，这种更改也不会丢失。

  由 内存 + Redo Log 保证，MySQL 修改数据同时在内存和 Redo Log 记录这次操作，宕机时可从 Redo Log 恢复数据。

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

# 🌟4 个一致性问题

多个事务并发运行，经常会操作相同的数据来完成各自的任务（例如多个客户端并发地访问同一个表），如果事务之间没有设置合理的隔离级别，可能会导致以下 4 种问题。

# 脏读（Dirty read）

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

例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A = 19, 事务 1 回滚导致对 A 的修改并为提交到数据库， A 的值还是 20。

# 丢失修改（Lost to modify）

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

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 先修改 A=A-1，事务 2 后来也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

# 不可重复读（Unrepeatable read）

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

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 再次读取 A =19，此时读取的结果和第一次读取的结果不同。

# 幻读（Phantom read）

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

例如：事务 2 读取某个范围的数据，事务 1 在这个范围插入了新的数据，事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。

# 不可重复读和幻读的区别

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

幻读其实可以看作是不可重复读的一种特殊情况，区分二者的原因是二者的解决方案不一样。

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

# 并发事务的控制方式（隔离性）

MySQL 中并发事务的控制方式无非就两种：锁 和 多版本并发控制（MVCC）。

锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

# 锁

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

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

读写锁可以做到读读并行，但是无法做到读写并行、写写并行。

另外，根据根据锁粒度的不同，又被分为表级锁 (table-level locking) 和行级锁 (row-level locking)。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁）。所以对于并发写入操作来说，InnoDB 的性能更高。

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

# 多版本并发控制（MVCC）

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

MVCC 在 MySQL 中实现所依赖的手段主要是：

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

关于 InnoDB 对 MVCC 的具体实现可以看这篇文章：InnoDB 存储引擎对 MVCC 的实现 。

# 🌟4 种隔离级别

SQL 标准为事务定义了四种隔离级别：

• READ-UNCOMMITTED（读未提交）：最低的隔离级别，允许当前事务读取其他并发事务尚未提交的数据，存在脏读、不可重复读、幻读问题。

  用户本来应该读取到 id=1 的用户 age 应该是 10，结果读取到了其他事务还没有提交的事务，结果读取 age=20，这就是脏读。

• READ-COMMITTED（读已提交）：允许当前事务读取其他并发事务已经提交的数据。可以避免脏读，但是存在不可重复读、幻读问题。

  用户开启事务读取 id=1 的用户，查询到 age=16，再次读取发现结果 age=20，在同一个事务里同一个查询读取到不同的结果，叫做不可重复读。

• REPEATABLE-READ（可重复读）：MySQL 的默认隔离级别，当前事务对同一数据的多次读取结果都是一样的（都是复用事务刚开始时的数据读取结果副本），除非数据被本身事务所修改。可以避免脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE（串行化 / 序列化）：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次串行执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

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

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

# MySQL（InnoDB）的默认隔离级别是 REPEATABLE-READ（可重读）

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

可以通过 SELECT @@tx_isolation; 命令来查看，MySQL 8.0 该命令改为 SELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;

+-----------------+

| @@tx_isolation  |

+-----------------+

| REPEATABLE-READ |

+-----------------+

关于 MySQL 事务隔离级别的详细介绍，可以看看我写的这篇文章：MySQL 事务隔离级别详解。

# MySQL 事务隔离级别与一致性问题的演示

SQL 脚本 1 在第一次查询工资为 500 的记录时只有一条，SQL 脚本 2 插入了一条工资为 500 的记录，提交之后；SQL 脚本 1 在同一个事务中再次使用当前读查询发现出现了两条工资为 500 的记录这种就是幻读。

幻读的解决方法：核心思想就是一个事务在操作某张表数据的时候，另外一个事务不允许新增或者删除这张表中的数据了。解决幻读的方式主要有以下几种：

1. 将事务隔离级别调整为 SERIALIZABLE 。
2. 在可重复读的事务级别下，给事务操作的这张表添加表锁。
3. 在可重复读的事务级别下，给事务操作的这张表添加 Next-key Lock（Record Lock+Gap Lock） 。

# 🌟MySQL 锁

# 概述

在数据库中，数据是一种供许多用户共享的资源，必须保证数据并发访问的一致性、有效性。

MySQL 中的锁，按照锁的粒度分，分为以下三类：

• 全局锁：锁定整个数据库。

• 表级锁：每次操作锁住整张表。

  • 表锁：
  • 元数据锁（meta data lock，MDL）：加锁过程是系统自动控制的
  • 意向锁：不与行级锁冲突，由 InnoDB 自动添加，无需用户干预。
    • 意向共享锁（IS）
    • 意向排他锁（IX）

• 行级锁：每次操作锁住对应的行数据，其实现依赖于索引，而不是记录。

  • 行锁 / 记录锁（Record Lock）：存在于包括主键索引在内的唯一索引中，锁定单条索引记录
  • 间隙锁（Gap Lock）：存在于非唯一索引中，锁定开区间范围内的一段间隔
  • 临键锁（Next-Key Lock）：存在于非唯一索引中，锁定左开右闭的索引区间

# 全局锁

# 介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 DML 的写语句，DDL 语句，已经更新操作的事务提交语句都将被阻塞。

典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

# 语法

1. 添加全局锁：

   flush tables with read lock ;

2. 数据备份（退出 mysql，在 Windows 的 cmd 窗口中执行）：

   mysqldump -uroot –p1234 itcast > itcast.sql ;

3. 释放锁：

   unlock tables ;

# 特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

为了解决上述问题，在 InnoDB 引擎中可以在备份时加上参数 --single-transaction 参数来完成 **不加锁的一致性数据备份**。

快照读

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

# 表级锁

# 介绍

表级锁，即锁住整张表，MyISAM、InnoDB、BDB 等存储引擎都支持，主要分为以下三类：

每类表级锁，都分为共享锁（S 锁 / 读锁）和排他锁（X 锁 / 写锁）

• 表锁
• 元数据锁（meta data lock，MDL）
• 意向锁

# 表锁

对于表锁，分为两类：

结论:

• 读锁不会阻塞其他客户端的读，但是会阻塞写。
• 写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

• 表锁共享锁（Shared）：又称读锁

  左侧为客户端一，对指定表加了读锁，只能读不能写。不会影响右侧客户端二的读，但是会阻塞右侧客户端的写。

• 表锁排他锁（eXclusive）：又称写锁

  左侧为客户端一，对指定表加了写锁，可以读和写。但是会阻塞右侧客户端的读和写。

语法：

• 加锁：

  lock tables 表名... read/write

• 释放锁：客户端断开连接，或者

  unlock tables

# 元数据锁

全称为 meta data lock，简写 MDL，这里的元数据，大家可以简单理解为就是一张表的表结构。

MDL 加锁过程是系统自动控制的，无需显式使用，在访问一张表的时候会自动加上。

MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免 DML 与 DDL 冲突，保证读写的正确性。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

在 MySQL5.5 中引入了 MDL，

• 当对一张表的数据进行增删改查（DML、DQL）的时候，加 MDL 共享锁
• 当对表结构进行变更操作（DDL）的时候，加 MDL 排他锁

举个例子，对于常见的 SQL 操作，所添加的 MDL 锁：

# 意向锁

# 介绍

为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，减少表锁的检查。

意向锁的工作机制如下：

1. 客户端在执行 DML 时，会对涉及的记录加行锁，同时对该表加上意向锁
2. 其他客户端在尝试对该表加表锁时，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。

# 分类

意向锁之间不会互斥。并且一旦事务提交了，意向锁就自动释放了。

• 意向共享锁 (IS): 由语句 select ... lock in share mode 添加。

  与表锁共享锁 (read) 兼容，与表锁排他锁 (write) 互斥。

• 意向排他锁 (IX): 由 insert 、 update 、 delete 、 select...for update 添加。

  与表锁共享锁 (read) 及表锁排他锁 (write) 都互斥。

# 演示

可以通过以下 SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from

performance_schema.data_locks;

# 行级锁

# 介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。

只有 InnoDB 存储引擎支持行级锁。InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

对于行级锁，主要分为以下三类：

• 行锁（Record Lock）：也称记录锁，锁定单个行记录，防止其他事务对此行进行 update 和 delete 。

  在 RC、RR 隔离级别下都支持。

  与表级锁类似，行锁也分为共享锁（S 锁 / 读锁）和排他锁（X 锁 / 写锁）

  • 行锁共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

  • 行锁排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

    两种行锁的兼容情况如下：

    对于常见的 SQL 语句，在执行时，所加的行锁如下：

• 间隙锁（Gap Lock）：锁定索引记录的间隙（不含记录），确保索引记录间隙不变，防止其他事务在这个间隙进行 insert ，产生幻读。

  在 RR 隔离级别下支持。

• 临键锁（Next-Key Lock）：行锁和间隙锁组合，锁住记录的同时也锁住数据前面的所有间隙。

  在 RR 隔离级别下支持。还记得在 MySQL 事务的隔离级别中提到的下表吗？当时之所以说 InnoDB 引擎中的 RR 隔离级别可以避免幻读现象，就是因为 InnoDB 引擎支持行级锁中的临键锁（next-key）！

# 行锁演示

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动将临键锁（next-key）优化为行锁。
• InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁。

可以通过以下 SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from

performance_schema.data_locks;

# 间隙锁 & 临键锁演示

默认情况下，InnoDB 的事务隔离级别为 REPEATABLE-READ（可重复读），InnoDB 使用 临键锁（next-key）进行搜索和索引扫描，以防出现幻读。

• 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁

• 索引上的等值查询（非唯一的普通索引），向右遍历时最后一个值不满足查询需求时，临键锁（next-key）退化为间隙锁

• 索引上的范围查询 (唯一索引)-- 会访问到不满足条件的第一个值为止

间隙锁唯一目的是防止其他事务插入间隙。

间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

# 总结

# 面试题

锁是一种常见的并发事务的控制方式。

# 表级锁和行级锁了解吗？有什么区别？

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

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

• 表级锁：

  • 是 **针对非索引字段** 加的锁，对当前操作的整张表加锁
  • 优点：加锁快，资源消耗也比较少，不会出现死锁。
  • 缺点：触发锁冲突的概率最高，高并发下效率极低。
  • MyISAM 和 InnoDB 引擎都支持表级锁。

• 行级锁：

  • MySQL 中锁定粒度最小的一种锁，是 **针对索引字段** 加的锁，只针对当前操作的行记录进行加锁。
  • 优点：能大大减少数据库操作的锁冲突。其加锁粒度最小，并发度高。
  • 缺点：加锁慢，加锁的开销大，会出现死锁。
  • 行级锁和存储引擎有关，仅有 InnoDB 引擎支持行级锁。

# 行级锁的使用有什么注意事项？

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。

行级锁退化成表级锁：当我们执行 UPDATE 、 DELETE 语句时，如果 WHERE 条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因，它认为全表扫描的效率比使用索引更高。

# InnoDB 有哪几类行级锁？

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

• 记录锁（Record Lock）：也被称为行锁，锁的是已经存在的单个行记录。
• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。为了避免插入新记录，需要依赖间隙锁。
• 临键锁（Next-Key Lock）：可以理解为行锁 + 间隙锁 的组合，锁定一个行记录，及其之前的所有间隙范围。主要目的是 **为了解决幻读问题**（MySQL 事务部分提到过）。

在 InnoDB 默认的隔离级别 REPEATABLE-READ（可重复读）下，行级锁默认使用的是临键锁（Next-Key）。

但是，如果操作的索引是唯一索引或主键，InnoDB 会将临键锁（Next-Key）降级为记录锁（Record Lock），即仅锁住索引本身，而不是范围。

# 共享锁和排他锁呢？

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

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

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

由于 MVCC（多版本并发控制）的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过，可以通过以下语句显式地加共享锁或排他锁：

• 共享锁（MySQL 5.7 和 MySQL 8.0）： SELECT ... LOCK IN SHARE MODE;
• 共享锁（MySQL 8.0）： SELECT ... FOR SHARE;
• 排他锁： SELECT ... FOR UPDATE;

# 意向锁有什么作用？

意向锁是一种表级锁，作用是快速判断是否可以对某个表使用表锁，而不用逐行检查是否有行级锁。

意向锁之间不会互斥。并且一旦事务提交了，意向锁就自动释放了。

• 意向共享锁 (IS 锁): 事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。由语句 select ... lock in share mode 添加。

  与表锁共享锁 (read) 兼容，与表锁排他锁 (write) 互斥。

• 意向排他锁 (IX 锁): 事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。由 insert 、 update 、 delete 、 select...for update 添加。

  与表锁共享锁 (read) 及表锁排他锁 (write) 都互斥。

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

意向锁之间是互相兼容的。

意向锁与表级别的共享锁和排它锁的互斥情况如下：

意向锁不会与行级别的共享锁和排他锁互斥

# 快照读和当前读有什么区别？

快照读（一致性非锁定读）就是简单的 SELECT 语句。

快照即记录的历史版本，每行记录可能存在多个历史版本（多版本技术）。

快照读的情况下，如果读取的记录正在执行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照。

只有在事务隔离级别 RC (读取已提交) 和 RR（可重读）下，InnoDB 才会使用快照读（一致性非锁定读）：

• 在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
• 在 RR 级别下，对于快照数据，一致性非锁定读总是 **读取本事务开始时的行数据版本**。

快照读比较适合对数据一致性要求不是特别高，且追求极致性能的业务场景。

当前读（一致性锁定读）就是给行记录加 X 锁或 S 锁。

当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个 X 锁

SELECT...FOR UPDATE

# 对读的记录加一个 S 锁（MySQL 5.7 和 MySQL 8.0）

SELECT...LOCK IN SHARE MODE

# 对读的记录加一个 S 锁（MySQL 8.0）

SELECT...FOR SHARE

# 对修改的记录加一个 X 锁

INSERT...

UPDATE...

DELETE...

# MySQL 性能优化

# 能用 MySQL 直接存储文件（比如图片）吗？

虽然可以直接将文件对应的二进制数据存储到 MySQL 中，不过还是建议不要在数据库中存储文件。因为会严重影响数据库性能，消耗过多存储空间。

可以选择使用云服务厂商提供的开箱即用的 **文件存储服务**，成熟稳定，价格也比较低。

也可以选择自建文件存储服务，实现起来也不难，基于 FastDFS、MinIO（推荐） 等开源项目就可以实现分布式文件服务。

数据库只存储文件地址信息，文件本身由文件存储服务负责存储。

相关阅读：Spring Boot 整合 MinIO 实现分布式文件服务 。

# MySQL 如何存储 IP 地址？

可以将 IP 地址 **转换成整形数据存储**，性能更好，占用空间也更小。

MySQL 提供了两个（互逆的）方法来处理 ip 地址：

• INET_ATON() ：把 ip 转为无符号整型（4-8 位）
• INET_NTOA() ：把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型。显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

# 常见的 SQL 优化手段

指路→SQL 优化

# 如何分析 SQL 的性能？

可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化后的具体的执行方式。

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

指路→[利用 EXPLAIN 命令分析 SQL 是否走索引查询](# 利用 EXPLAIN 命令分析 SQL 是否走索引查询)

# 🌟读写分离、分库分表

# 读写分离

解决的问题：数据库读并发

# 什么是读写分离？

见名思意，根据读写分离的名字，我们就可以知道：读写分离主要是为了 **将对数据库的读写操作分散到不同的数据库节点上。** 这样的话，就能够小幅提升写性能，大幅提升读性能。

我简单画了一张图来帮助不太清楚读写分离的小伙伴理解。

一般情况下，我们都会选择 **一主多从**，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据实时同步（主从复制），以保证从库中数据的准确性。这样的架构实现起来比较简单，并且也符合系统的写少读多的特点。

# 如何实现读写分离？

不论是使用哪一种读写分离具体的实现方案，想要实现读写分离一般包含如下几步：

1. 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
2. 保证主数据库和从数据库之间的数据是实时同步的（主从复制）。
3. 系统将写请求交给主数据库处理，读请求交给从数据库处理。

落实到项目本身的话，常用的方式有两种：

1. 代理方式

   代理方式实现读写分离

   我们可以在应用和数据中间加了一个 **代理层**。

   应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。

   提供类似功能的中间件有 MySQL Router（官方）、Atlas（基于 MySQL Proxy）、MaxScale、MyCat。

2. 第三方组件方式

   推荐的方式

   在这种方式中，我们可以通过引入第三方组件来帮助我们读写请求。

   这也是我比较推荐的一种方式。这种方式目前在各种互联网公司中用的最多的，相关的实际的案例也非常多。

   如果你要采用这种方式的话，推荐使用 sharding-jdbc ，直接引入 jar 包即可使用，非常方便。同时，也节省了很多运维的成本。

   你可以在 shardingsphere 官方找到 sharding-jdbc 关于读写分离的操作。

# 🌟主从复制的原理

MySQL binlog （二进制日志文件）主要记录了 MySQL 数据库中数据的所有变化（数据库执行的所有 DDL 和 DML 语句）。

binlog 主要有两个作用：

1. 主从复制
2. 数据恢复

因此，可以根据主库的 MySQL binlog 日志就能够将主库的数据同步复制到从库中。

1. master 将数据库中数据的变化写入到 binlog 中

2. slave 连接 master

3. slave 会创建一个 I/O 线程 向 master 请求更新的 binlog

4. master 会创建一个 binlog dump 线程 来发送 binlog，由 slave 中的 I/O 线程负责接收

5. slave 的 I/O 线程将接收的 binlog 写入到 relay log （中继日志）中

6. slave 的 SQL 线程 读取 relay log 同步数据本地（也就是再执行一遍 SQL ）

扩展：阿里开源的一个叫做 canal 的工具可以帮助我们同步 MySQL 数据到其他数据源（例如 Elasticsearch/MySQL）。其原理就是模拟 MySQL 主从复制的过程，解析 binlog 将数据同步到其他的数据源。

另外，像咱们常用的分布式缓存组件 Redis 也是通过主从复制实现的读写分离。

小结一下：

MySQL 主从复制依赖于 binlog。另外，常见的一些同步 MySQL 数据到其他数据源的工具（比如 canal）的底层一般也是依赖 binlog 。

# 如何避免主从同步延迟？

读写分离对于提升数据库的并发非常有效，但也同时引来一个问题：主库和从库的数据存在延迟，比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。这也就是我们经常说的 **主从同步延迟** 。

如果我们的业务场景无法容忍主从同步延迟的话，应该如何避免呢？

这里提供两种方案：

（1）强制将读请求路由到 master 处理

使用最多的一种方式

既然 slave 的数据过期了，那就直接从 master 读取呗！这种方案虽然会增加 master 的压力，但是实现起来比较简单，也是我了解到的 **使用最多的一种方式**。

比如 Sharding-JDBC 就是采用的这种方案。通过使用 Sharding-JDBC 的 HintManager 分片键值管理器，我们可以强制使用 master。

HintManager hintManager = HintManager.getInstance();

hintManager.setMasterRouteOnly();

// 继续 JDBC 操作

对于这种方案，可以将那些必须获取最新数据的读请求都交给 master 处理。

（2）延迟读取

没办法完全避免主从延迟，只能说可以减少出现延迟的概率而已，实际项目中一般不会使用

还有一些朋友肯定会想既然主从同步存在延迟，那我就在延迟之后读取啊，比如主从同步延迟 0.5s, 那我就 1s 之后再读取数据。这样多方便啊！方便是方便，但是也很扯淡。

不过，如果你是这样设计业务流程就会好很多：对于一些对数据比较敏感的场景，可以在完成写请求之后，避免立即进行请求操作。比如你支付成功之后，跳转到一个支付成功的页面，当你点击返回之后才返回自己的账户。

# 主从同步延迟的原因？如何尽量减少延迟？

MySQL 主从同步延迟指的是从库的数据落后于主库的数据。这种情况可能有以下2 个原因：

1. 从库 I/O 线程接收 binlog 的速度跟不上主库写入 binlog 的速度，导致从库 relay log 的数据滞后于主库 binlog 的数据；
2. 从库 SQL 线程执行 relay log 的速度跟不上从库 I/O 线程接收 binlog 的速度，导致从库的数据滞后于从库 relay log 的数据。

与主从同步有关的3 个时间点：

1. 主库执行完一个事务，写入 binlog，将这个时刻记为 T1；
2. 从库 I/O 线程接收到 binlog 并写入 relay log 的时刻记为 T2；
3. 从库 SQL 线程读取 relay log 同步数据本地的时刻记为 T3。

结合我们上面讲到的主从复制原理，可以得出2 个结论：

• T2 和 T1 的差值反映了从库 I/O 线程的性能和网络传输的效率，这个差值越小说明从库 I/O 线程的性能和网络传输效率越高。
• T3 和 T2 的差值反映了从库 SQL 线程执行的速度，这个差值越小，说明从库 SQL 线程执行速度越快。

这里列举主从同步延迟的几种常见情况：

1. 从库机器性能比主库差：从库接收 binlog 并写入 relay log 以及执行 SQL 语句的速度会比较慢（也就是 T2-T1 和 T3-T2 的值会较大），进而导致延迟。解决方法是选择与主库一样规格或更高规格的机器作为从库，或者对从库进行性能优化，比如调整参数、增加缓存、使用 SSD 等。
2. 从库处理的读请求过多：从库需要执行主库的所有写操作，同时还要响应读请求，如果读请求过多，会占用从库的 CPU、内存、网络等资源，影响从库的复制效率（也就是 T2-T1 和 T3-T2 的值会较大，和前一种情况类似）。解决方法是引入缓存（推荐）、使用一主多从的架构，将读请求分散到不同的从库，或者使用其他系统来提供查询的能力，比如将 binlog 接入到 Hadoop、Elasticsearch 等系统中。
3. 大事务：运行时间比较长，长时间未提交的事务就可以称为大事务。由于大事务执行时间长，并且从库上的大事务会比主库上的大事务花费更多的时间和资源，因此非常容易造成主从延迟。解决办法是避免大批量修改数据，尽量分批进行。类似的情况还有执行时间较长的慢 SQL，实际项目遇到慢 SQL 应该进行优化。
4. 从库太多：主库需要将 binlog 同步到所有的从库，如果从库数量太多，会增加同步的时间和开销（也就是 T2-T1 的值会比较大，但这里是因为主库同步压力大导致的）。解决方案是减少从库的数量，或者将从库分为不同的层级，让上层的从库再同步给下层的从库，减少主库的压力。
5. 网络延迟：如果主从之间的网络传输速度慢，或者出现丢包、抖动等问题，那么就会影响 binlog 的传输效率，导致从库延迟。解决方法是优化网络环境，比如提升带宽、降低延迟、增加稳定性等。
6. 单线程复制：MySQL5.5 及之前，只支持单线程复制。为了优化复制性能，MySQL 5.6 引入了 多线程复制，MySQL 5.7 还进一步完善了多线程复制。
7. 复制模式：MySQL 默认的复制是异步的，必然会存在延迟问题。全同步复制不存在延迟问题，但性能太差了。半同步复制是一种折中方案，相对于异步复制，半同步复制提高了数据的安全性，减少了主从延迟（还是有一定程度的延迟）。MySQL 5.5 开始，MySQL 以插件的形式支持 semi-sync 半同步复制。并且，MySQL 5.7 引入了 增强半同步复制 。
8. ……

《MySQL 实战 45 讲》这个专栏中的读写分离有哪些坑？这篇文章也有对主从延迟解决方案这一话题进行探讨，感兴趣的可以阅读学习一下。

# 分库分表

解决的问题：数据库存储

读写分离主要应对的是数据库读并发，没有解决数据库存储问题。试想一下：** 如果 MySQL 一张表的数据量过大怎么办？** 换言之，我们该如何解决 MySQL 的存储压力呢？

答案之一就是 **分库分表**。

# 分库

分库就是将数据库中的数据分散到不同的数据库上，可以分为两种：

• 垂直分库

  就是把单一数据库 **按照业务进行划分**，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

  举个例子：说你将数据库中的用户表、订单表和商品表分别单独拆分为用户数据库、订单数据库和商品数据库。

  垂直分库

• 水平分库

  就是把同一个表 **按一定规则拆分** 到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

  举个例子：订单表数据量太大，你对订单表进行了水平切分（水平分表），然后将切分后的 2 张订单表分别放在两个不同的数据库。

  水平分库

# 分表

分表就是对单表的数据进行拆分，也可以分为两种：

• 垂直分表

  是 **对列的拆分**，把一张列比较多的表拆分为多张表。

  举个例子：我们可以将用户信息表中的一些列单独抽出来作为一个表。

• 水平分表

  是 **对行的拆分**，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

  举个例子：我们可以将用户信息表拆分成多个用户信息表。

  水平拆分只能解决单表数据量大的问题，为了提升性能，我们通常会选择将拆分后的多张表放在不同的数据库中。也就是说，水平分表通常和水平分库同时出现。

# 什么情况下需要分库分表？

遇到下面几种场景可以考虑分库分表：

• 单表的数据达到千万级别以上，数据库读写速度比较缓慢
• 数据库中的数据占用的空间越来越大，备份时间越来越长
• 应用的并发量太大

# 常见的分片算法

分片算法主要 **解决了数据被水平分片之后，数据究竟该存放在哪个表的问题**。

• 哈希分片：求指定 key（比如 id） 的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。
• 范围分片：按照特性的范围区间（比如时间区间、ID 区间）来分配数据，比如 将 id 为 1~299999 的记录分到第一个库， 300000~599999 的分到第二个库。范围分片适合需要经常进行范围查找的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。
• 地理位置分片：很多 NewSQL 数据库都支持地理位置分片算法，也就是根据地理位置（如城市、地域）来分配数据。
• 融合算法：灵活组合多种分片算法，比如将哈希分片和范围分片组合。
• ……

# 分库分表会带来什么问题？

记住，你在公司做的任何技术决策，不光是要考虑这个技术能不能满足我们的要求，是否适合当前业务场景，还要重点考虑其带来的成本。

引入分库分表之后，会给系统带来什么挑战呢？

• join 操作：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。不过，很多大厂的资深 DBA 都是建议尽量不要使用 join 操作。因为 join 的效率低，并且会对分库分表造成影响。对于需要用到 join 操作的地方，可以采用多次查询业务层进行数据组装的方法。不过，这种方法需要考虑业务上多次查询的事务性的容忍度。
• 事务问题：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。这个时候，我们就需要引入分布式事务了。关于分布式事务常见解决方案总结，网站上也有对应的总结：https://javaguide.cn/distributed-system/distributed-transaction.html 。
• 分布式 ID：分库之后，数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键呢？这个时候，我们就需要为我们的系统引入分布式 ID 了。关于分布式 ID 的详细介绍 & 实现方案总结，网站上也有对应的总结：https://javaguide.cn/distributed-system/distributed-id.html 。
• 跨库聚合查询问题：分库分表会导致常规聚合查询操作，如 group by，order by 等变得异常复杂。这是因为这些操作需要在多个分片上进行数据汇总和排序，而不是在单个数据库上进行。为了实现这些操作，需要编写复杂的业务代码，或者使用中间件来协调分片间的通信和数据传输。这样会增加开发和维护的成本，以及影响查询的性能和可扩展性。
• ……

另外，引入分库分表之后，一般需要 DBA 的参与，同时还需要更多的数据库服务器，这些都属于成本。

# 分库分表有没有什么比较推荐的方案？

Apache ShardingSphere 是一款分布式的数据库生态系统，可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

ShardingSphere 项目（包括 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar）是当当捐入 Apache 的，目前主要由京东数科的一些巨佬维护。

**ShardingSphere 绝对可以说是当前分库分表的首选！**ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能。

ShardingSphere 提供的功能如下：

ShardingSphere 的优势如下：

• 极致性能：驱动程序端历经长年打磨，效率接近原生 JDBC，性能极致。
• 生态兼容：代理端支持任何通过 MySQL/PostgreSQL 协议的应用访问，驱动程序端可对接任意实现 JDBC 规范的数据库。
• 业务零侵入：面对数据库替换场景，ShardingSphere 可满足业务无需改造，实现平滑业务迁移。
• 运维低成本：在保留原技术栈不变前提下，对 DBA 学习、管理成本低，交互友好。
• 安全稳定：基于成熟数据库底座之上提供增量能力，兼顾安全性及稳定性。
• 弹性扩展：具备计算、存储平滑在线扩展能力，可满足业务多变的需求。
• 开放生态：通过多层次（内核、功能、生态）插件化能力，为用户提供可定制满足自身特殊需求的独有系统。

另外，ShardingSphere 的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

艿艿之前写了一篇分库分表的实战文章，各位朋友可以看看：《芋道 Spring Boot 分库分表入门》 。

# 分库分表后，数据怎么迁移呢？

分库分表之后，我们如何将老库（单库单表）的数据迁移到新库（分库分表后的数据库系统）呢？

比较简单同时也是非常常用的方案就是 **停机迁移**，写个脚本将老库的数据写到新库中。比如你在凌晨 2 点，系统使用的人数非常少的时候，挂一个公告说系统要维护升级预计 1 小时。然后，你写一个脚本将老库的数据都同步到新库中。

如果你不想停机迁移数据的话，也可以考虑 **双写方案**。双写方案是针对那种不能停机迁移的场景，实现起来要稍微麻烦一些。具体原理是这样的：

• 我们对老库的更新操作（增删改），同时也要写入新库（双写）。如果操作的数据不存在于新库的话，需要插入到新库中。这样就能保证，咱们新库里的数据是最新的。
• 在迁移过程，双写只会让被更新操作过的老库中的数据同步到新库，我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。
• 重复上一步的操作，直到老库和新库的数据一致为止。

想要在项目中实施双写还是比较麻烦的，很容易会出现问题。我们可以借助上面提到的数据库同步工具 Canal 做增量数据迁移（还是依赖 binlog，开发和维护成本较低）。

# 总结

• 读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。这样的话，就能够小幅提升写性能，大幅提升读性能。

• 读写分离基于主从复制，而 MySQL 主从复制依赖于 binlog 。

• 分库就是将数据库中的数据分散到不同的数据库上。分表就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

• 引入分库分表之后，需要系统解决事务、分布式 id、无法 join 操作问题。

• ShardingSphere 绝对可以说是当前分库分表的首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理等功能。另外，ShardingSphere 的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

# MySQL 高性能优化规范建议

# 命名规范

• 所有数据库对象名称必须使用小写字母并用下划线分割

• 所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）

• 数据库对象的命名要能做到见名识意，并且最后不要超过 32 个字符

• 临时库表必须以 tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀

• 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，造成列上的索引失效，导致查询效率降低）

# 基本设计规范

# 所有表必须使用 InnoDB 存储引擎

没有特殊要求（即 InnoDB 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用 InnoDB 存储引擎（MySQL5.5 之前默认使用 MyISAM，5.6 以后默认的为 InnoDB）。

InnoDB 支持事务、行级锁、外键约束，有更好的恢复性，高并发下性能更好。

# 数据库和表的字符集统一使用 UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效。

如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。

# 所有表和字段都需要添加注释

使用 comment 从句添加表和列的备注，从一开始就进行数据字典的维护。

# 尽量控制单表数据量的大小，建议控制在 500 万以内

500 万并不是 MySQL 数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。

可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小。

# 谨慎使用 MySQL 分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表；

谨慎选择分区键，跨分区查询效率可能更低；

建议采用物理分表的方式管理大数据。

# 经常一起使用的列放到一个表中

避免更多的关联操作。

# 禁止在表中建立预留字段

• 预留字段的命名很难做到见名识义。
• 预留字段无法确认存储的数据类型，所以无法选择合适的类型。
• 对预留字段类型的修改，会对表进行锁定。

# 禁止在数据库中存储文件（比如图片）这类大的二进制数据

在数据库中存储文件会严重影响数据库性能，消耗过多存储空间。

这类大的二进制数据文件（比如图片）通常存储于文件服务器，数据库只存储文件地址信息。

# 不要被数据库范式所束缚

一般来说，设计关系数据库时需要满足第三范式，但为了满足第三范式，我们可能会拆分出多张表。而在进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度。

# 禁止在线上做数据库压力测试

# 禁止从开发环境、测试环境直接连接生产环境数据库

安全隐患极大，要对生产环境抱有敬畏之心！

# 字段设计规范

# 优先选择符合存储需要的最小的数据类型

存储字节越小，占用也就空间越小，性能也越好。

a. 某些字符串可以转换成数字类型存储，比如可以将 IP 地址转换成整型数据。

数字是连续的，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

• INET_ATON() ：把 ip 转为无符号整型 (4-8 位)
• INET_NTOA() : 把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

b. 对于非负型的数据 (如自增 ID、整型 IP、年龄) 来说，要优先使用无符号整型来存储。

无符号相对于有符号可以多出一倍的存储空间

SIGNED INT -2147483648~2147483647

UNSIGNED INT 0~4294967295

c. 小数值类型（比如年龄、状态表示如 0/1）优先使用 TINYINT 类型。

# 避免使用 TEXT、BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据

a. 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中。

MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，MySQL 还是要进行二次查询，会使 sql 性能变得很差，但是不是说一定不能使用这样的数据类型。

如果一定要使用，建议把 BLOB 或是 TEXT 列分离到单独的扩展表中，查询时一定不要使用 select * 而只需要取出必要的列，不需要 TEXT 列的数据时不要对该列进行查询。

2、TEXT 或 BLOB 类型只能使用前缀索引

因为 MySQL 对索引字段长度是有限制的，所以 TEXT 类型只能使用前缀索引，并且 TEXT 列上是不能有默认值的。

# 避免使用 ENUM 类型

• 修改 ENUM 值需要使用 ALTER 语句；
• ENUM 类型的 ORDER BY 操作效率低，需要额外操作；
• ENUM 数据类型存在一些限制，比如建议不要使用数值作为 ENUM 的枚举值。

相关阅读：是否推荐使用 MySQL 的 enum 类型？ - 架构文摘 - 知乎

# 尽可能把所有列定义为 NOT NULL

除非有特别的原因使用 NULL 值，应该总是让字段保持 NOT NULL。

• 索引 NULL 列需要额外的空间来保存，所以要占用更多的空间；
• 进行比较和计算时要对 NULL 值做特别的处理。

相关阅读：技术分享 | MySQL 默认值选型（是空，还是 NULL）

# 一定不要用字符串存储日期

对于日期类型来说，一定不要用字符串存储日期。可以考虑 DATETIME、TIMESTAMP、数值型时间戳。

这三种方式都有各自的优势，根据实际场景选择最合适的才是王道。下面再对这三种方式做一个简单的对比，以供大家实际开发中选择正确的存放时间的数据类型：

MySQL 时间类型选择的详细介绍请看这篇：MySQL 时间类型数据存储建议。

# 财务相关的金额类数据必须使用 decimal 类型

• 非精准浮点：float、double
• 精准浮点：decimal

decimal 类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节。并且，decimal 可用于存储比 bigint 更大的整型数据。

不过，由于 decimal 需要额外的空间和计算开销，应该尽量只在需要对数据进行精确计算时才使用 decimal 。

# 单表不要包含过多字段

如果一个表包含过多字段的话，可以考虑将其垂直分表，必要时增加中间表进行关联。

# 索引设计规范

# 限制每张表上的索引数量，建议单张表索引不超过 5 个

索引并不是越多越好！索引可以提高效率同样可以降低效率。

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

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

# 禁止使用全文索引

全文索引不适用于 OLTP（联机事务处理）场景。

# 禁止给表中的每一列都建立单独的索引

5.6 版本之前，一个 sql 只能使用到一个表中的一个索引；5.6 以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。

# 每个 InnoDB 表必须有个主键

InnoDB 是一种索引组织表：数据存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

InnoDB 是 **按照主键索引的顺序来组织表** 的

• 不要使用更新频繁的列作为主键，不使用多列主键（相当于联合索引）
• 不要使用 UUID,MD5,HASH, 字符串列作为主键（无法保证数据的顺序增长）
• 主键建议使用自增 ID 值

# 常见索引列建议

• 出现在 WHERE 子句中的列

• 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段

• 通常将上述两种情况的字段建立联合索引，效果更好

• 多表 join 的关联列

# 如何选择联合索引中的列顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

• 区分度最高的列放在联合索引的最左侧

  区分度 = 列中不同值的数量 / 列的总行数

• 尽量把字段长度小的列放在联合索引的最左侧

  因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好

• 使用最频繁的列放到联合索引的左侧

  这样可以比较少的建立一些索引

# 避免建立冗余索引、重复索引

增加了查询优化器生成执行计划的时间

• 重复索引示例：primary key (id)、index (id)、unique index (id)
• 冗余索引示例：index (a,b,c)、index (a,b)、index (a)

# 对于频繁的查询，优先考虑覆盖索引

覆盖索引：包含了所有查询字段 (where,select,order by,group by 包含的字段) 的索引。

覆盖索引的好处：

• 避免二次查询（回表）：InnoDB 是以聚集索引的顺序来存储的，对于 InnoDB 来说，二级索引在叶子节点中所保存的是行的主键信息。如果是用二级索引查询数据的话，在查找到相应的主键后，还要通过聚集索引进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询，减少了 IO 操作，提升了查询效率。
• 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多。因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。

# 索引 SET 规范

尽量避免使用外键约束

• 不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引
• 外键可用于保证数据的参照完整性，但建议在业务端实现外键约束
• 外键会影响父表和子表的写操作，从而降低性能

# SQL 开发规范

# 尽量不在数据库做运算，复杂运算需移到业务应用里完成

避免数据库的负担过重，影响数据库的性能和稳定性。数据库的主要作用是存储和管理数据，而不是处理数据。

# 优化对性能影响较大的 SQL 语句

要找到最需要优化的 SQL 语句。要么是使用最频繁的语句，要么是优化后提高最明显的语句，可以通过查询 MySQL 的 **慢查询日志** 来发现需要进行优化的 SQL 语句。

# 充分利用表上已经存在的索引

避免使用双 % 号的查询条件。如： a like '%123%' ，（如果无前置 %, 只有后置 %，是可以用到列上的索引的）

一个 SQL 只能利用到复合索引中的一列进行范围查询。如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到。

在定义联合索引时，如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧，使用 left join 或 not exists 来优化 not in 操作，因为 not in 也通常会使用索引失效。

# 禁止使用 SELECT * 查询

• SELECT * 会消耗更多的 CPU

• SELECT * 无用字段增加网络带宽资源消耗，增加数据传输时间，尤其是大字段（如 varchar、blob、text）

• SELECT * 无法使用 MySQL 优化器覆盖索引的优化（基于 MySQL 优化器的 “覆盖索引” 策略又是速度极快，效率极高，业界极为推荐的查询优化方式）

• SELECT <字段列表> 可减少表结构变更带来的影响

# 禁止使用不含字段列表的 INSERT 语句

如：

insert into t values ('a','b','c');

应使用：

insert into t(c1,c2,c3) values ('a','b','c');

# 建议使用预编译语句进行数据库操作

• 预编译语句可以重复使用这些计划，减少 SQL 编译所需要的时间，还可以解决动态 SQL 所带来的 SQL 注入的问题

• 只传参数，比传递 SQL 语句更高效

• 相同语句可以一次解析，多次使用，提高处理效率

# 避免数据类型的隐式转换

隐式转换会导致索引失效如:

select name,phone from customer where id = '111';

详细解读可以看：MySQL 中的隐式转换造成的索引失效 这篇文章。

# 避免使用子查询，可以把子查询优化为 join 操作

通常子查询在 in 子句中，且子查询中为简单 SQL (不包含 union、group by、order by、limit 从句) 时，才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：子查询的结果集通常被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

# 避免使用 JOIN 关联太多的表

• 对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由 join_buffer_size 参数进行设置。

• 在 MySQL 中，对于同一个 SQL，多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。

• 如果程序中大量的使用了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

• 同时对于关联操作来说，会产生临时表操作，影响查询效率，MySQL 最多允许关联 61 个表，建议不超过 5 个。

# 减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

# 对应同一列进行 or 判断时，使用 in 代替 or

in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

# 禁止使用 order by rand () 进行随机排序

order by rand () 会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源。

推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

# WHERE 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致索引失效

不推荐：

where date(create_time)='20190101'

推荐：

where create_time >= '20190101' and create_time < '20190102'

# 在明显不会有重复值时，使用 UNION ALL 而不是 UNION

• UNION：把两个结果集的所有数据放到临时表中，再进行去重操作
• UNION ALL：不会再对结果集进行去重操作

# 拆分复杂的大 SQL 为多个小 SQL

• 大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL
• MySQL 中，一个 SQL 只能使用一个 CPU 进行计算
• SQL 拆分后可以通过并行执行来提高处理效率

# 程序连接不同的数据库使用不同的账号，禁止跨库查询

• 为数据库迁移和分库分表留出余地
• 降低业务耦合度
• 避免权限过大而产生的安全风险

# 操作行为规范

# 超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作，要分批多次进行操作

大批量操作可能会造成严重的主从延迟

主从环境中，大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况

binlog 日志为 row 格式时会产生大量的日志

大批量写操作会产生大量日志，特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因

避免产生大事务操作

大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对 MySQL 的性能产生非常大的影响。

特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批

# 对于大表使用 pt-online-schema-change 修改表结构

• 避免大表修改产生的主从延迟
• 避免在对表字段进行修改时进行锁表

对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。

pt-online-schema-change 首先会建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。把原来一个 DDL 操作，分解成多个小的批次进行。

# 禁止为程序使用的账号赋予 super 权限

• 当达到最大连接数限制时，还运行 1 个有 super 权限的用户连接
• super 权限只能留给 DBA 处理问题的账号使用

# 对于程序连接数据库账号，遵循权限最小原则

• 程序使用数据库账号只能在一个 DB 下使用，不准跨库
• 程序使用的账号原则上不准有 drop 权限

# SQL

# SQL 语法基础

# 基本概念

# 数据库术语

• 数据库（database） - 保存有组织的数据的容器（通常是一个文件或一组文件）。
• 数据表（table） - 某种特定类型数据的结构化清单。
• 模式（schema） - 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。
• 列（column） - 表中的一个字段。所有表都是由一个或多个列组成的。
• 行（row） - 表中的一个记录。
• 主键（primary key） - 一列（或一组列），其值能够唯一标识表中每一行。

# SQL 语法

SQL（Structured Query Language) ，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。

# SQL 语法结构

SQL 语法结构包括：

• 子句 - 是语句和查询的组成成分。（在某些情况下，这些都是可选的。）
• 表达式 - 可以产生任何标量值，或由列和行的数据库表
• 谓词 - 给需要评估的 SQL 三值逻辑（3VL）（true/false/unknown）或布尔真值指定条件，并限制语句和查询的效果，或改变程序流程。
• 查询 - 基于特定条件检索数据。这是 SQL 的一个重要组成部分。
• 语句 - 可以持久地影响纲要和数据，也可以控制数据库事务、程序流程、连接、会话或诊断。

# SQL 语法要点

• SQL 语句不区分大小写，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置。例如： SELECT 与 select 、 Select 是相同的。
• 多条 SQL 语句必须以分号（ ; ）分隔。
• 处理 SQL 语句时，所有空格都被忽略。

SQL 语句可以写成一行，也可以分写为多行。

-- 一行 SQL 语句

UPDATE user SET username='robot', password='robot' WHERE username = 'root';

-- 多行 SQL 语句

UPDATE user

SET username='robot', password='robot'

WHERE username = 'root';

SQL 支持三种注释：

## 注释 1

-- 注释 2

/* 注释 3 */

# SQL 分类

# 数据定义语言（DDL）

数据定义语言（Data Definition Language，DDL）是 SQL 语言集中负责数据结构定义与数据库对象定义的语言。

DDL 的主要功能是定义数据库对象。

DDL 的核心指令是 CREATE 、 ALTER 、 DROP 。

# 数据操纵语言（DML）

数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句。

DML 的主要功能是 访问数据，因此其语法都是以读写数据库为主。

DML 的核心指令是 INSERT 、 DELETE 、 UPDATE 、 SELECT 。这四个指令合称 CRUD (Create, Read, Update, Delete)，即增删改查。

# 事务控制语言（TCL）

事务控制语言 (Transaction Control Language, TCL) 用于管理数据库中的事务。这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。

TCL 的核心指令是 COMMIT 、 ROLLBACK 。

# 数据控制语言（DCL）

数据控制语言 (Data Control Language, DCL) 是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。

DCL 的核心指令是 GRANT 、 REVOKE 。

DCL 以控制用户的访问权限为主，因此其指令作法并不复杂，可利用 DCL 控制的权限有： CONNECT 、 SELECT 、 INSERT 、 UPDATE 、 DELETE 、 EXECUTE 、 USAGE 、 REFERENCES 。

根据不同的 DBMS 以及不同的安全性实体，其支持的权限控制也有所不同。

我们先来介绍 DML 语句用法。 DML 的主要功能是读写数据库实现增删改查。

# 增删改查

增删改查，又称为 CRUD，数据库基本操作中的基本操作。

# 插入数据

INSERT INTO 语句用于向表中插入新记录。

插入完整的行

# 插入一行

INSERT INTO user

VALUES (10, 'root', 'root', 'xxxx@163.com');

# 插入多行

INSERT INTO user

VALUES (10, 'root', 'root', 'xxxx@163.com'), (12, 'user1', 'user1', 'xxxx@163.com'), (18, 'user2', 'user2', 'xxxx@163.com');

插入行的一部分

INSERT INTO user(username, password, email)

VALUES ('admin', 'admin', 'xxxx@163.com');

插入查询出来的数据

INSERT INTO user(username)

SELECT name

FROM account;

# 更新数据

UPDATE 语句用于更新表中的记录。

UPDATE user

SET username='robot', password='robot'

WHERE username = 'root';

# 删除数据

• DELETE 语句用于删除表中的记录。
• TRUNCATE TABLE 可以清空表，也就是删除所有行。

删除表中的指定数据

DELETE FROM user

WHERE username = 'robot';

清空表中的数据

TRUNCATE TABLE user;

# 查询数据

SELECT 语句用于从数据库中查询数据。

DISTINCT 用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同。

LIMIT 限制返回的行数。可以有两个参数，第一个参数为起始行从 0 开始；第二个参数为返回的总行数。

• ASC ：升序（默认）
• DESC ：降序

查询单列

SELECT prod_name

FROM products;

查询多列

SELECT prod_id, prod_name, prod_price

FROM products;

查询所有列

SELECT *

FROM products;

查询不同的值

SELECT DISTINCT vend_id 

FROM products;

限制查询结果

-- 返回前 5 行

SELECT * FROM mytable LIMIT 5;

SELECT * FROM mytable LIMIT 0, 5;

-- 返回第 3 ~ 5 行

SELECT * FROM mytable LIMIT 2, 3;

# 排序

order by 用于对结果集按照一个列或者多个列进行排序。 默认升序 对记录进行排序，如果需要按照降序对记录进行排序，可以使用 desc 关键字。

order by 对多列排序的时候，先排序的列放前面，后排序的列放后面。并且，不同的列可以有不同的排序规则。

SELECT * FROM products

ORDER BY prod_price DESC, prod_name ASC;

# 分组

group by ：

• group by 子句将记录分组到汇总行中。
• group by 为每个组返回一个记录。
• group by 通常还涉及聚合 count ， max ， sum ， avg 等。
• group by 可以按一列或多列进行分组。
• group by 按分组字段进行排序后， order by 可以以汇总字段来进行排序。

分组

SELECT cust_name, COUNT(cust_address) AS addr_num

FROM Customers GROUP BY cust_name;

分组后排序

SELECT cust_name, COUNT(cust_address) AS addr_num

FROM Customers GROUP BY cust_name

ORDER BY cust_name DESC;

having ：

• having 用于对汇总的 group by 结果进行过滤。
• having 一般都是和 group by 连用。
• where 和 having 可以在相同的查询中。

使用 WHERE 和 HAVING 过滤数据

SELECT cust_name, COUNT(*) AS num

FROM Customers

WHERE cust_email IS NOT NULL

GROUP BY cust_name

HAVING COUNT(*) >= 1;

having vs where ：

• where ：
  • 过滤指定的行
  • 后面不能加聚合函数（分组函数）
  • 在 group by 前使用
• having ：
  • 过滤分组
  • 一般都是和 group by 连用，不能单独使用
  • 在 group by 之后使用

# 子查询

子查询是嵌套在较大查询中的 SQL 查询，也称内部查询或内部选择，包含子查询的语句也称为外部查询或外部选择。简单来说，子查询就是指将一个 select 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。

子查询可以嵌入 SELECT 、 INSERT 、 UPDATE 和 DELETE 语句中，也可以和 = 、 < 、 > 、 IN 、 BETWEEN 、 EXISTS 等运算符一起使用。

子查询常用在 WHERE 子句和 FROM 子句后边：

• 当用于 WHERE 子句后边时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。

  用于 WHERE 子句的子查询的基本语法如下：

  	select column_name [, column_name ]
  	from table1 [, table2 ]
  	where column_name operator
  	(select column_name [, column_name ]
  	from table1 [, table2 ]
  	[where])

  • 子查询需要放在括号 ( ) 内。
  • operator 表示用于 where 子句的运算符。

• 当用于 FROM 子句后边时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。

  用于 FROM 子句的子查询的基本语法如下：

  	select column_name [, column_name ]
  	from (select column_name [, column_name ]
  	from table1 [, table2 ]
  	[where]) as temp_table_name
  	where condition

  用于 FROM 的子查询返回的结果相当于一张临时表，所以需要使用 AS 关键字为该临时表起一个名字。

注意：MYSQL 数据库从 4.1 版本才开始支持子查询，早期版本是不支持的。

子查询的子查询

SELECT cust_name, cust_contact

FROM customers

WHERE cust_id IN (SELECT cust_id

                  FROM orders

                  WHERE order_num IN (SELECT order_num

                                      FROM orderitems

                                      WHERE prod_id = 'RGAN01'));

内部查询首先在其父查询之前执行，以便可以将内部查询的结果传递给外部查询。执行过程可以参考下图：

# WHERE

• WHERE 子句用于过滤记录，即缩小访问数据的范围。
• WHERE 后跟一个返回 true 或 false 的条件。
• WHERE 可以与 SELECT ， UPDATE 和 DELETE 一起使用。
• 可以在 WHERE 子句中使用的操作符。

SELECT 语句中的 WHERE 子句

SELECT * FROM Customers

WHERE cust_name = 'Kids Place';

UPDATE 语句中的 WHERE 子句

UPDATE Customers

SET cust_name = 'Jack Jones'

WHERE cust_name = 'Kids Place';

DELETE 语句中的 WHERE 子句

DELETE FROM Customers

WHERE cust_name = 'Kids Place';

# IN 和 BETWEEN

• IN 操作符在 WHERE 子句中使用，作用是在指定的几个特定值中任选一个值。
• BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于某个范围内的值。

IN 示例

SELECT *

FROM products

WHERE vend_id IN ('DLL01', 'BRS01');

BETWEEN 示例

SELECT *

FROM products

WHERE prod_price BETWEEN 3 AND 5;

# AND、OR、NOT

• AND 、 OR 、 NOT 是用于对过滤条件的逻辑处理指令。
• AND 优先级高于 OR ，为了明确处理顺序，可以使用 () 。
• AND 操作符表示左右条件都要满足。
• OR 操作符表示左右条件满足任意一个即可。
• NOT 操作符用于否定一个条件。

AND 示例

SELECT prod_id, prod_name, prod_price

FROM products

WHERE vend_id = 'DLL01' AND prod_price <= 4;

OR 示例

SELECT prod_id, prod_name, prod_price

FROM products

WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

NOT 示例

SELECT *

FROM products

WHERE prod_price NOT BETWEEN 3 AND 5;

# LIKE

• LIKE 操作符在 WHERE 子句中使用，作用是确定字符串是否匹配模式。
• 只有字段是文本值时才使用 LIKE 。
• LIKE 支持两个通配符匹配选项： % 和 _ 。
• 不要滥用通配符，通配符位于开头处匹配会非常慢。
• % 表示任何字符出现任意次数。
• _ 表示任何字符出现一次。

% 示例

SELECT prod_id, prod_name, prod_price

FROM products

WHERE prod_name LIKE '%bean bag%';

_ 示例

SELECT prod_id, prod_name, prod_price

FROM products

WHERE prod_name LIKE '__ inch teddy bear';

# 连接

# 简介

JOIN 是 “连接” 的意思，顾名思义，SQL JOIN 子句用于将两个或者多个表联合起来进行查询。

连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。

# 语法

使用 JOIN 连接两个表的基本语法如下：

select table1.column1, table2.column2...

from table1 join table2

on table1.common_column1 = table2.common_column2;

table1.common_column1 = table2.common_column2 是连接条件，只有满足此条件的记录才会合并为一行。您可以使用多个运算符来连接表，例如 =、>、<、<>、<=、>=、!=、 between 、 like 或者 not ，但是最常见的是使用 =。

当两个表中有同名的字段时，为了帮助数据库引擎区分是哪个表的字段，在书写同名字段名时需要加上表名。当然，如果书写的字段名在两个表中是唯一的，也可以不使用以上格式，只写字段名即可。

另外，如果两张表的关联字段名相同，也可以使用 USING 子句来代替 ON ，举个例子：

# join....on

select c.cust_name, o.order_num

from Customers c inner join Orders o

on c.cust_id = o.cust_id

order by c.cust_name;

# 如果两张表的关联字段名相同，也可以使用 USING 子句：join....using ()

select c.cust_name, o.order_num

from Customers c inner join Orders o

using(cust_id)

order by c.cust_name;

ON 和 WHERE 的区别：

• 连接表时，SQL 会根据连接条件生成一张新的临时表。 ON 是连接条件，它决定临时表的生成。
• WHERE 是在临时表生成以后，再对临时表中的数据进行过滤，生成最终的结果集，这个时候已经没有 JOIN-ON 了。

所以总结来说就是：SQL 先根据 ON 生成一张临时表，然后再根据 WHERE 对临时表进行筛选。

# 分类

SQL 允许在 JOIN 左边加上一些修饰性的关键词，从而形成不同类型的连接，如下表所示：

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

对于 (INNER) JOIN 来说，还有一种隐式的写法，称为 “隐式内连接”，也就是没有 (INNER) JOIN 关键字，使用 WHERE 语句实现内连接的功能

# 隐式内连接

select c.cust_name, o.order_num

from Customers c, Orders o

where c.cust_id = o.cust_id

order by c.cust_name;

# 显式内连接

select c.cust_name, o.order_num

from Customers c inner join Orders o

using(cust_id)

order by c.cust_name;

# 组合

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

UNION 基本规则：

• 所有查询的列数和列顺序必须相同。
• 每个查询中涉及表的列的数据类型必须相同或兼容。
• 通常返回的列名取自第一个查询。

默认地， UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL 。

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

JOIN vs UNION ：

• JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。
• UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。

# 函数

不同数据库的函数往往各不相同，因此不可移植。本节主要以 MysSQL 的函数为例。

# 文本处理

其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。

SELECT *

FROM mytable

WHERE SOUNDEX(col1) = SOUNDEX('apple')

# 日期和时间处理

• 日期格式： YYYY-MM-DD
• 时间格式： HH:MM:SS

# 数值处理

# 汇总

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以让汇总函数值汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col

FROM mytable

接下来，我们来介绍 DDL 语句用法。DDL 的主要功能是定义数据库对象（如：数据库、数据表、视图、索引等）

# 数据定义

# 数据库（DATABASE）

# 创建数据库

CREATE DATABASE test;

# 删除数据库

DROP DATABASE test;

# 选择数据库

USE test;

# 数据表（TABLE）

# 创建数据表

普通创建

CREATE TABLE user (

  id int(10) unsigned NOT NULL COMMENT 'Id',

  username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',

  password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',

  email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'

) COMMENT='用户表';

根据已有的表创建新表

CREATE TABLE vip_user AS

SELECT * FROM user;

# 删除数据表

DROP TABLE user;

# 修改数据表

添加列

ALTER TABLE user