6.MySQL

一、MySQL基础

1.1 什么是关系型数据库？

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

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

关系型数据库表关系

1.1.1 常见的关系型数据库

1. MySQL：由Oracle公司开发，是目前应用最广泛的开源数据库之一。
2. Oracle：由Oracle公司开发，是企业级应用中最为常用的数据库之一。
3. SQL Server：由Microsoft公司开发，是企业级应用中较为常用的数据库之一。
4. PostgreSQL：是一种功能强大的开源关系型数据库，具有良好的数据完整性和安全性。
5. DB2：由IBM公司开发，是一种可扩展的、高效的企业级关系型数据库。
6. SQLite：是一种轻型关系型数据库，适用于嵌入式设备和移动应用程序。
7. MariaDB：由MySQL的创始人开发，是一个社区驱动的关系型数据库管理系统。
8. Sybase：由SAP公司开发，是一种可扩展的、高效的企业级关系型数据库。

1.2 什么是SQL？

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

1.3 什么是MySQL？

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

1.4 MySQL的优缺点？

优点：

1. 开源免费；
2. 成熟稳定，功能完善；
3. 文档丰富，社区活跃，生态完善；
4. 开箱即用，操作简单，维护成本低；
5. 兼容性好，支持主流的常见操作系统，支持多种开发语言；
6. 事务支持优秀；
7. 支持分库分表、读写分离、高可用。

缺点：

1. 配置复杂；
2. 对大数据量的支持不够强；
3. 安全性较差，容易受到SQL注入等攻击。

1.5 MySQL三大范式了解吗？

1. 第一范式：遵循原子性，即表中字段的数据，不能再拆分。比如：一个表中地址字段为xx省xx市xx区，那么就不遵守第一范式，因为这个地址字段还可以再继续拆分为省、市、区三个字段。
2. 第二范式：遵循唯一性，即表中任意一个主键可以确定除主键外的所有非主键值。更简单讲的话就是：一个表只能描述一件事。比如一个表中如果有学号、姓名、年龄、选修课程、成绩、学分等字段，那么这个表就不遵守第二范式，因为如果用学号做主键，只能确定姓名和年龄，不能确定选修的课程和成绩。正确的做法是将这个表拆分为学生表、课程表、成绩表，使得每个表只描述一件事。
3. 第三范式：消除传递依赖，即任一主键都可以确定所有非主键字段的值，不存在通过非主键字段A确定非主键字段B。比如一个表中有学号、姓名、班级、班主任四个字段，通过主键学号可以唯一的确定其他所有字段的信息，但是在非主键字段中，我们也可以通过班级推导出唯一的班主任，所以这就不满足第三范式。正确的做法是将该表拆分为学生表和班级表，就可以消除传递依赖。

1.6 MySQL增删改查的具体语句

• 增：INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
• 删：DELETE FROM table_name WHERE condition;
• 改：UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
• 查：SELECT column1, column2, ... FROM table_name WHERE condition;

1.7 MySQL中几个连接的区别

连接主要分为三类：内连接、左连接、外连接。

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

二、MySQL基础架构

MySQL架构

从上图的架构图可以看出，MySQL主要由以下几部分构成：

• 连接器：身份认证和权限相关（登录MySQL）；
• 查询缓存：执行查询语句的时候，会先查询缓存（但是在8.0版本后移除，因为不太实用）；
• 分析器：没有命中缓存的话，SQL语句就会经过分析器，对SQL语句进行词法分析和语法分析；
• 优化器：按照MySQL认为最优的方案去执行。
• 执行器：执行语句，然后从存储引擎返回数据。执行语句之前会判断是否有权限；
• 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构。

2.1 SQL语句在MySQL中执行的过程？

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

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

2.1.1 查询语句的执行流程如下

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行 SQL：执行 SQL 共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

更新语句执行流程如下：分析器—->权限校验—->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit 状态)

2.2 MySQL读取数据的方式有哪些？

1. SQL查询语句：使用SELECT语句来查询数据库中的数据。这是最常见和基本的方式，可以使用不同的SELECT语句来检索所需的数据。
2. 视图：视图是虚拟的表，它们基于一个或多个表的查询结果。您可以像查询表一样查询视图，但视图本身不包含实际数据，而是动态生成。
3. JDBC（Java数据库连接）：Java编程语言可以使用JDBC来连接MySQL数据库，并通过编写Java代码来读取和处理数据。
4. 导入和导出工具：MySQL提供了导入和导出工具，如mysqldump和mysqlimport，用于将数据从文件导入数据库或将数据库数据导出到文件。

三、MySQL存储引擎

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

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

查看 MySQL 提供的所有存储引擎

从上图可以看出，默认引擎是InnoDB。并且所有的存储引擎中只有InnoDB是事务性存储引擎，也即只有InnoDB支持事务存储；

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

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

存储引擎是基于表的，而不是数据库。

3.3 MylSAM和InnoDB有什么区别？

MySQL 5.5之前，使用的默认引擎是MylSAM，但是不支持事务和行级锁，而且崩溃后无法安全恢复；

MySQL 5.5之后，使用的默认引擎是InnoDB。

• MylSAM只支持表级别的锁粒度，InnoDB支持行级别的锁粒度。
• MylSAM不提供事务支持，InnoDB提供事务支持，实现了SQL标准定义了四个隔离级别。
• MylSAM不支持外键，InnoDB支持外键。
• MylSAM不支持MVCC(多版本并发控制)，InnoDB支持。
• MyISAM 和 InnoDB 都是使用 B+Tree 作为索引结构，但是InnoDB 的数据文件本身就是索引文件，而MyISAM，索引文件和数据文件是分离的。
• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。
• InnoDB 的性能比 MyISAM 更强大。

3.4 三层的B+树能存多少数据？

假设全部存储的是int类型的数据，一个int类型占4字节，一个指针占大约6个字节，那么一个节点就是10个字节。一页默认大小是16kb，假设一行数据为1kb，而在B+树中，一页就是一个节点，那么一页就能存放16行数据。所以一个节点可以存放16kb / 10b = 1600个指针。

可知一个2层B+树，能存放16 * 1600 = 25600条int类型的数据；一个3层的B+树，能存放 16 * 1600 * 1600 = 40960000条int类型的数据。

四、MySQL索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

4.1 索引的优缺点

优点：

• 大大加快数据的检索速度，减少检索的数据量；
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

缺点：

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

4.1.1 索引一定能提高查询性能吗？

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引不一定能带来性能提升。

4.2 索引的底层数据结构

4.2.1 Hash表

为什么能通过key快速取出value？

原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

为什么MySQL没有使用其作为索引的数据结构？

主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。

4.2.2 B树和B+树

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

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

B树和B+树有什么异同？

• B树的所有节点既存放key也存放data，而B+树只有在叶子结点存放key和data，其他内节点只存放key。
• B树的叶子结点都是独立的；B+树的叶子结点有一条引用链路指向与它相邻的叶子结点。
• B树的检索过程相当于对范围内的每个节点的关键字做二分查找，有可能没有到达叶子结点，检索就结束了；而B+树的效率更稳定，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

数据库为什么使用B+树而不是B树？

• B树适用于随机检索，而B+树适用于随机检索和顺序检索；
• B+树的空间利用率更高，因为B树的每个节点要存key和data，而B+树节点只存储key，这样B+树的一个节点就可以存储更多的索引，从而使树的高度变低，减少了IO次数，检索速度更快；
• B+树的叶子节点都连在一起，所以顺序查找更方便；
• B+树的性能更稳定。

4.3 常见的索引类型

4.3.1 主键索引

数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

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

为什么要使用自增的ID作为主键索引？

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满时，就会自动开辟一个新的页。

如果使用非自增的主键（比如身份证、学号等），由于每次插入主键的值近似于随机插入，因此每次新记录就会被插到现有索引页的中间某个位置，此时 MySQL 就会移动数据，造成频繁的页分裂和页旋转，使得插入速度变慢。

因此，InnoDB 的主键应该尽量使用整型自增的 ID。这样能够在存储和查询上提高效率。

4.3.2 二级索引（辅助索引）

二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

以下四种索引都属于二级索引：

• 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

4.3.3 聚簇索引

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

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

优点：

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

缺点：

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

4.3.4 非聚簇索引

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

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

优点：

• 更新代价比聚簇索引小

缺点：

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

存储方式区别

B+树底层

4.3.5 覆盖索引

非聚簇索引一定回表查询吗？

不一定，比如用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。这种情况就称之为覆盖索引，覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

什么是索引下推？

索引下推（Index Predicate Pushdown）是数据库查询优化的一种技术，它在查询过程中将过滤条件尽可能地推送到索引层级，以减少不必要的数据读取和计算，从而提高查询性能。

索引下推的实现依赖于数据库管理系统的查询优化器，优化器会根据查询条件和索引的结构，判断哪些条件可以在索引层级进行过滤，并将这些条件下推到索引层级。这样，数据库在执行查询时，可以首先应用索引层级的过滤条件，然后再进行数据读取，减少了不必要的数据传输和处理。

4.3.6 联合索引

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

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

1

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

4.3.7 最左前缀匹配原则

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

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

4.4 正确使用索引的一些建议

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

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

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

• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。

• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

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

4.4.2 频繁更新的字段慎重建立索引

4.4.3 限制每张表的索引数量

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

4.4.5 避免冗余索引

4.4.6 字符串类型的字段使用前缀索引

4.4.7 避免索引失效（索引失效的情况）

常见的导致索引失效的情况有：

• 创建了组合索引，但查询条件未遵守最左前缀匹配原则。在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。
• 在索引列上进行计算、函数、类型转换等操作。因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
• 以 % 开头的 LIKE 查询比如 like '%abc'。因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。而这中模糊查询查的是后缀，所以不知道从哪个索引开始，只能进行全表查询；
• 查询条件中使用 or，且or的前后条件中有一个列没有索引，涉及的索引都不会被使用到。因为 or 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

4.4.8 删除长期未使用的索引

五、MySQL事务

5.1 什么是事务？

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

假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作，这两个操作必须都成功或者都失败。

1. 小明账户余额减少1000元
2. 小红账户余额增加1000元

事务会把这两个操作看成一个整体，这两个工作要么一起成功，要么一起失败。这样就不会出现小明余额减少，但小红余额没有增加的情况。

事务示意图

5.2 什么是数据库事务？

简单来说，数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

数据库事务

5.3 事务有哪些特性？

ACID

关系型数据库都具有ACID特性：

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

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

AID->C

5.4 数据库事务实现的原理是什么？

以MySQL的InnoDB引擎为例，

• 通过redo log（重做日志）来实现事务的持久性；
• 通过undo log（回滚日志）来实现事务的原子性；
• 通过锁机制、MVCC等手段来实现事务的隔离性；
• 原子性+隔离性+持久性保证了一致性；

5.4.1 redo log和undo log的区别？

• redo log记录的是物理页的物理变化，服务器宕机可以用来同步数据；
• undo log记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据；
• redo log保证了事务的持久性、undo log保证了事务的原子性；

5.4.2 undo log日志怎么实现原子性？

我们在进行数据更新操作的时候，不仅会记录redo log，还会记录undo log，如果因为某些原因导致事务回滚，那么这个时候MySQL就要执行回滚（rollback）操作，利用undo log将数据恢复到事务开始之前的状态。

当我们执行一条delete语句：

delete from user where id = 1;

此时undo log日志会记录一条相对应的insert语句，也即反向操作语句，以保证在事务回滚时，将数据还原回去。

当我们执行一条update语句：

update user set name = "李四" where id = 1; *---修改之前name=张三*

此时undo log日志会记录一条相反的update语句：

update user set name = "张三" where id = 1;

如果这个修改出现异常，可以使用undo log日志来实现回滚操作，以保证事务的一致性。

5.4.3 undo log的存储机制

undo log的存储由InnoDB存储引擎实现，数据保存在InnoDB的数据文件中。

在InnoDB存储引擎中，undo log是采用分段(segment)的方式进行存储的。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。在MySQL5.5之前，只支持1个rollback segment，也就是只能记录1024个undo操作。在MySQL5.5之后，可以支持128个rollback segment，分别从resg slot0 - resg slot127，每一个resg slot，也就是每一个回滚段，内部由1024个undo segment 组成，即总共可以记录128 * 1024个undo操作。

5.6 并发事务带来了那些问题？

5.6.1 脏读

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

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

脏读

5.6.2 丢失修改

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

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

丢失修改

5.6.3 不可重复读

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

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

不可重复读

5.6.4 幻读

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

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

幻读

5.7 不可重复读和幻读有什么区别？

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

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

解决方案：

执行 delete 和 update 操作的时候，可以直接对记录加锁，保证事务安全，避免出现不可重复读。

执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock+Gap Lock） 进行加锁来保证不出现幻读。

5.8 并发事务的控制方式有哪些？

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

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

• 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

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

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

5.8.1 解释一下MVCC

MVCC 是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。

MVCC 在 MySQL 的底层实现所依赖的手段主要是: 隐藏字段、undo log日志、read view读视图。

• 隐藏字段
  • trx_id（事务id），用来记录每次一操作的事务id，是自增的；
  • roll_pointer（回滚指针），指向上一个版本的事务版本的地址；
• undo log
  • 主要作用是记录回滚日志，存储老版本数据；
  • undo log内部会形成一个版本链，多个事务并行操作某一行数据时，记录不同事务修改数据的版本，通过回滚指针形成一个链表；
• readView
  • 解决的是一个事务查询选择版本的问题，根据readView的匹配规则和事务id判断该访问哪个版本的数据；
  • 不同的隔离级别快照读不一样，最终的访问结果也不一样；比如读已提交的隔离级别，每一次执行快照读的时候都会生成ReadView，而可重复读的隔离级别，只会在第一次执行快照读的时候生成ReadView。

5.8.2 解决幻读的方式

虽然默认的隔离级别是可重复读，但是仍然可以很大程度上避免幻读（并不是完全解决）。

• 针对快照读（普通 select 语句），是通过 MVCC 机制解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select … for update 等特殊读语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

总结：在可重复读的隔离级别下，对于快照读来说，幻读是通过MVCC机制解决的，对于当前读来说，幻读是通过Next-key lock解决的。

5.9 有哪些事务隔离级别？

• READ-UNCOMMITTED(读未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读，但是性能比较低。

5.10 隔离级别是怎么实现的？

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

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

• 读已提交隔离级别：在每次读取事务时，生成一个新的ReadView，意味着事务期间多次读取同一条数据可能出现不一致情况；
• 可重复读隔离级别：在启动事务时生成一个ReadView，然后整个事务期间都使用这个ReadView，保证了事务期间读取的数据都是事务启动前的记录；

5.11 MySQL的默认隔离级别是什么？

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

六、MySQL锁

MySQL中根据锁的范围，可以分为全局锁、表级锁和行级锁三类。

6.1 说一说全局锁

命令：flush tables with read lock；

执行后，整个数据库就处于只读状态了，增删改操作都会被阻塞。

主要的应用场景是做全库逻辑备份，这样在备份期间，就不会因为表中数据或结构更新，出现备份的数据与预期不一致的情况。

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

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性能非常差。

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

表级锁和行级锁对比：

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

6.3 InnoDB有哪几类行级锁？

• 记录锁（Record Lock） ：属于单个行记录上的锁。

• 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。

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

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

6.4 意向锁有什么作用？

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

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

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

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

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

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

快照读（一致性非锁定读）：是指读取一个事务在开始执行之前数据库中的某个时间点的数据版本。

这种读取方式可以保证读取的数据是一致的，即读取的所有数据都是从同一时刻的数据库状态中获取的。在快照读的过程中，如果其他事务对数据进行了修改，读取操作也不会受到影响，因为读取的是之前的数据版本。

当前读（一致性锁定读）：是指读取数据库中最新的数据版本，也就是说，当前读会读取最新提交的事务对数据所做的修改。

当前读可以分为共享锁和排他锁两种方式。共享锁可以让其他事务也可以读取数据，但不能对其进行修改，而排他锁则可以独占该数据，并且其他事务不能读取或修改该数据。

总的来说，快照读保证了读取的数据是一致的，而当前读则可以读取到最新的数据版本，但会存在并发问题，需要通过锁等机制来保证数据的一致性和正确性。

6.5.1 当前读和快照读对应的语句有哪些？

当前读：

• update
• delete
• insert
• select（共享读锁）

快照读：不同的隔离级别都不同

• 读已提交：每次select都会生成一个快照读；
• 可重复读：开启事务后第一个select语句才会快照读。

6.6 了解自增锁吗？

关系型数据库设计表的时候，通常会有一列作为自增主键。InnoDB 中的自增主键会涉及一种比较特殊的表级锁——— 自增锁（AUTO-INC Locks）。

6.7 讲一讲间隙锁

比如有A、B、C三个人在排队，现在来了个D，怎么样让新来的D不站在B的旁边呢？

只需要把A和B、B和C之间的间隙锁住，这样D就不能站在B的旁边了。而这个例子中的A、B、C、D就相当于数据库里的一条条记录。

锁住他们之间间隙的操作就叫间隙锁。

间隙锁的目的就是为了防止幻读：

1. 防止间隙内有新的数据被插入；
2. 防止已经存在的数据，更新成为间隙内的数据。

七、MySQL性能优化

7.1 MySQL能直接存储文件吗？

可以将存储的文件转换成对应的二进制数据即可存储。不过不建议这样操作，因为会严重影响数据库性能。可以使用互联网厂商提供的云服务存储功能。

7.2 MySQL如何存储IP地址？

可以将IP地址转换成整型数据存储，性能更好，占用空间也更小。

MySQL中提供了两个方式来处理IP地址：

• INET_ATON()：将IP地址转为无符号整型（4-8位）。
• INET_NTOA()：将整型IP转为地址。

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

7.3 常见的SQL调优方式

7.3.1 创建索引

1. 要尽量避免全表扫描。首先应该考虑在where及order by涉及的列上建立索引。
2. 在进场需要进行检索的字段上创建索引。比如要按照表字段username进行检索，那么就应该在用户名这个字段上创建索引。
3. 一个表的索引数最好不要超过6个。

7.3.2 避免在索引上使用计算

7.3.3 使用预编译查询

尽量使用参数化SQL，这样不仅可以避免SQL注入漏洞攻击，最重要的是数据库会对这些参数化SQL进行预编译。这样可以大大提高执行的速度。

7.3.4 调整where字句中的连接顺序

数据库管理系统一般采用自下而上的顺序解析where字句，所以表连接最好写在其他where条件之间，这样可以过滤掉最大数量的记录。

7.3.5 尽量将多条SQL语句压缩到一句SQL中

每次执行SQL语句的过程都是很耗时的，所以尽量避免过多的执行SQL语句，能够压缩到一句执行就不要用多条来执行。

7.3.6 用where字句替换having字句

7.3.7 使用表的别名

7.3.8 优化select查询语句

1. 任何地方都不要用select * from t，用具体的字段列表代替“*”，不要返回用不到的任何字段。
2. 尽量避免在where字句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。

7.3.9 优化update更新语句

如果只更新几个字段，不要用update更新全部字段，否则频繁调用会引起明显的性能消耗，同时会带来大量日志。

7.3.10 优化insert插入语句

在新建临时表时，如果一次性插入的数据量很大，那么可以使用select into代替create table，避免造成大量log，提高速度；如果插入的数据量不大，为了缓和系统表的资源，应该先create table，然后insert。

7.3.11 尽量使用union all代替union

因为union会多一次过滤的过程，效率比较低；

7.4 MySql的分页了解吗？

MySql的分页技术可以通过limit和offset子句实现。

• limit：用于限制查询结果返回的行数，可以指定要返回的最大行数；
• offset：用于指定从结果集的起始位置开始返回的行数，可以指定要跳过的行数；

通过结合使用limit和offset可以实现分页查询，比如，limit 10 offset 20表示查询结果从第20行开始，返回十行数据，即返回第21到30行数据。

注：对于小的偏移量，直接使用limit来查询没有什么问题，但随着数据量的增大，越往后分页，limit语句的偏移量就会越大，速度也会明显变慢。

7.5 MySQL超大分页怎么处理？（Limit有什么性能问题？如何处理海量数据的分页？）

超大分页一般是处理数据量比较大的情况，如果只是用limit和offset进行分页查询，需要对数据进行排序，会导致效率很低。

所以可以采用覆盖索引和子查询解决超大分页的情况。

1. 先分页查询数据的id字段，确定id列表。因为查询id的时候走的是覆盖索引，所以效率会高很多；
2. 通过子查询过滤数据，只查询id列表中的数据。

7.6 MySQL怎么定位慢查询？

MySQL中也提供了慢日志查询的功能，可以用来定位慢查询。

1. 可以在MySQL的系统配置文件中通过set global slow_query_log=1;命令开启这个慢日志的功能，但是这个命令只对当前数据库生效，如果MySQL重启后会失效。
2. 然后可以通过set global long_query_time=2;命令设置慢查询的阈值，时间是秒，也即只要SQL执行的时间超过了2秒就会记录到日志文件中。

这样一来，我们就可以在日志文件找到执行比较慢的SQL了。

7.7 找到执行慢的SQL语句后，如何分析呢？

如果一条sql执行很慢的话，我们通常会使用MySQL自动的执行计划explain来去查看这条sql的执行情况。

1. 可以通过key和key_len检查这个SQL语句是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况；
2. 可以通过type字段查看SQL是否有进一步的优化空间，该字段代表访问类型，在实际开发场景中，比较常见的几种类型从好到坏如下：const > eq_ref > ref > range > index > ALL，通常优化至少在range级别或以上，比如ref算是比较不错的；
3. 可以通过extra建议来判断，是否出现回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。

7.8 explain中有哪些常见的字段？

• id：表示查询中执行select子句或者操作表的顺序，**id的值越大，代表优先级越高，越先执行**。
• select_type：表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。
• table： 查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表。
• partitions：查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。
• type：重要。查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，下文将会展开来说。
• possible_keys： 表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引。
• key: 区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL
• key_len: 表示查询用到的索引长度（字节数），原则上长度越短越好。
• ref： 可以忽略。
• rows：以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。
• filtered：这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。
• extra：重要。不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。

八、数据库优化

8.1 大表数据查询如何进行优化？

• 索引优化
• SQL语句优化
• 水平拆分
• 垂直拆分
• 建立中间表
• 使用缓存技术
• 固定长度的表访问起来更快
• 越小的列访问越快

8.2 主键一般用自增的ID还是UUID？

自增ID的优点：

1. 字段长度比UUID小很多
2. 数据库自动编号，按顺序存放，利于检索。
3. 无须担心主键重复问题。

自增ID的缺点：

1. 因为是自增，所以在某些业务场景下，容易被其他人查到业务量。
2. 发生数据迁移或表合并时会非常麻烦。
3. 在高并发场景下，竞争自增锁会降低数据库的吞吐能力。

UUID的优点：

1. 唯一标识，不会存在重复的问题，在数据拆分、合并时也能保持全局的唯一性。
2. 可以在应用层生成，提高数据库的吞吐能力。
3. 无须担心业务量泄漏的问题。

UUID的缺点：

1. 因为是随机生成，所以会发生随机IO，影响插入速度，并且会造成磁盘的使用率较低。
2. UUID占用空间较大，建立的索引越多，造成的影响越大。
3. UUID之间比较大小的速度比自增ID慢，影响查询速度。

总结：一般情况 MySQL 推荐使用自增 ID。因为在 MySQL 的 InnoDB 存储引擎中，主键索引是一种聚簇索引，主键索引的 B+树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增 ID，只需要按顺序往后排列即可， 如果是 UUID，ID 是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降。

8.3 什么是垂直拆分和水平拆分？

8.3.1 垂直拆分

垂直拆分包括垂直分表和垂直分库。

垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放在另一个表中。

优势：避免IO竞争，减少锁表的概率。更好提升热门数据的查询效率。

垂直分库：按照业务对表进行分类，部署到不同的数据库上，不同的数据库可以放到不同的服务器上。

优势：降低业务中的耦合，方便对不同的业务进行分级管理。提升IO、数据库连接数、解决单机硬件资源的瓶颈问题。

垂直拆分的缺点：主键出现冗余，需要管理冗余的列、事务的处理变得复杂、仍然存在单表数据量过大的问题。

8.3.2 水平拆分

水平拆分包括水平分表和水平分库。

水平分表：将一个表的数据按照一定规则拆分到多个表中。

优势：解决了单表数据量过大的问题、避免IO竞争并减少锁表的概率。

水平分库：把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。

优势：解决了单库大数据量的瓶颈问题、IO冲突减少，锁的竞争减少，某个数据库出问题不影响其他数据库，提高了系统的稳定性和可用性。

水平拆分的缺点：分片事务一致性难以解决，逻辑会变得更复杂、数据扩展难度大，不易维护。

8.4 分库分表后，ID键如何处理？

分库分表后不能每个表的 ID 都是从 1 开始，所以需要一个全局ID，设置全局ID 主要有以下几种方法：

• UUID
• 自增ID
• Redis生成ID
• Twitter的snowflake算法

8.5 MySQL主从复制原理

MySQL复制：为保证主服务器和从服务器的数据一致性，在向主服务器插入数据后，从服务器会自动将主服务器中修改的数据同步过来。

主从复制的原理：主从复制主要有三个线程，binlog 线程，I/O 线程，SQL 线程。

binlog 线程：负责将主服务器上的数据更改写入到二进制日志（Binary log）中。

I/O 线程：负责从主服务器上读取二进制日志（Binary log），并写入从服务器的中继日志（Relay log）中。

SQL 线程：负责读取中继日志，解析出主服务器中已经执行的数据更改并在从服务器中重放。

MySQL主从一致性原理

• Master 在每个事务更新数据完成之前，将操作记录写入到 binlog 中。
• Slave 从库连接 Master 主库，并且 Master 有多少个， Slave 就会创建多少个 binlog dump 线程。当 Master 节点的 binlog 发生变化时，binlog dump 会通知所有的 Slave，并将相应的 binlog 发送给 Slave。
• I/O 线程接收到 binlog 内容后，将其写入到中继日志（Relay log）中。
• SQL 线程读取中继日志，并在从服务器中重放。

主从复制的作用：高可用和故障转移、负载均衡、数据备份、升级测试

8.5.1 除了这种binlog方式，还有其他方式吗？

数据同步模型

8.6 读写分离

读写分离主要依赖于主从复制，主从复制为读写分离服务。

读写分离的优势：

• 主服务器负责写，从服务器负责读，缓解了锁的竞争
• 从服务器可以使用 MyISAM，提高查询性能、节约系统开销
• 提高数据可用性

8.7 分库分表之后如何进行数据迁移？

• 停机迁移，该方案比较简单，也比较常用，具体操作就是在系统使用人数非常少的时候，挂个公告说系统需要维护升级1小时，然后进行停机迁移；
• 双写方案，该方案主要针对的是不能停机迁移的场景，实现起来比较麻烦。具体实现原理：
  • 对老库进行增删改操作，同时也要写入新库，也即双写。如果操作的数据不存在于新库的话，需要插入到新库中，这样就能保证新库的数据是最新的；
  • 在迁移过程中，双写操作只会让被更新操作的老库数据同步到新库，还需要将老库中的数据与新库的数据做对比，将老库有，新库没有的数据插入到新库；