MySQL

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

关系型数据库（RDBMS）和非关系型数据库（NoSQL）是数据存储和管理的两种不同系统。它们在数据存储结构、扩展性、事务支持等方面有着本质的区别。

数据存储结构

关系型数据库使用表格的形式存储数据，每个表由行和列组成，表之间可以通过外键关联。这种结构易于理解和使用，适合存储结构化数据。常见的关系型数据库有MySQL、Oracle、SQL Server等。

非关系型数据库则采用更灵活的数据存储方式，如键值对、文档、宽列或图形等。这使得它们能够存储结构化、半结构化甚至非结构化的数据。常用的非关系型数据库包括Redis、MongoDB、Cassandra等。

事务支持

关系型数据库提供了强大的事务支持，能够保证数据的一致性和完整性。它们支持ACID（原子性、一致性、隔离性、持久性）属性，适合需要严格事务控制的应用场景。

相比之下，非关系型数据库在事务支持方面较弱，虽然一些NoSQL数据库提供了事务功能，但通常不如关系型数据库那样强大和可靠。因此，非关系型数据库更适合对事务要求不高的大数据和实时应用。

应用场景

关系型数据库适用于需要复杂查询和事务支持的场景，如金融、电子商务和其他需要精确数据分析的领域。

非关系型数据库则适用于大数据、高并发和需要高扩展性的场景，如社交网络、大规模在线服务和实时分析。

总的来说，关系型数据库和非关系型数据库各有优势，选择哪种数据库取决于具体的应用需求和场景。在实际应用中，两者往往会结合使用，以发挥各自的长处。

基础

数据类型

• 对于长度相对固定的字符串，可以使用 char，对于长度不确定的，使用 varchar 更合适一些。

• MySQL 里记录货币用Decimal 和 Numric 类型

• MySQL 中的 in 语句是把外表和内表作 hash 连接，而 exists 语句是对外表作 loop 循环

查询语句执行顺序

数据库操作命令

①、创建数据库:

CREATE DATABASE database_name;

②、删除数据库:

DROP DATABASE database_name;

③、选择数据库:

USE database_name;

表操作命令

①、创建表:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,...);

②、删除表:

DROP TABLE table_name;

③、显示所有表:

SHOW TABLES;

④、查看表结构:

DESCRIBE table_name;

⑤、修改表（添加列）:

ALTER TABLE table_name ADD column_name datatype;

数据操作命令

①、插入数据:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

②、查询数据:

SELECT column_names FROM table_name WHERE condition;

③、更新数据:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

④、删除数据:

DELETE FROM table_name WHERE condition;

索引和约束

①、创建索引:

CREATE INDEX index_name ON table_name (column_name);

②、添加主键约束:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

③、添加外键约束:

ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name);

用户和权限管理

①、创建用户:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

②、授予权限:

GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host';

③、撤销权限:

REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host';

④、删除用户:

DROP USER 'username'@'host';

事务控制

①、开始事务:

START TRANSACTION; 或者 BEGIN

②、提交事务:

COMMIT;

③、回滚事务:

ROLLBACK;

什么是三大范式

三大范式的作用是为了减少数据冗余，提高数据完整性。

①、第一范式（1NF）：确保表的每一列都是不可分割的基本数据单元，比如说用户地址，应该拆分成省、市、区、详细信息等 4 个字段。

Ruthless：第一范式

②、第二范式（2NF）：在 1NF 的基础上，要求数据库表中的每一列都和主键直接相关，而不能只与主键的某一部分相关（主要针对联合主键）。

比如说在一个订单表中，可能会存在订单编号和商品编号，设计出来的表可能是这样的。

Ruthless：不符合第二范式

这个订单表中就存在冗余数据，比如说商品名称、单位、商品价格等，应该将其拆分为订单表、订单商品关联表、商品表。

Ruthless：订单商品关联表

③、第三范式（3NF）：在 2NF 的基础上，消除非主键列对主键的传递依赖，即非主键列只依赖于主键列，不依赖于其他非主键列。

比如说在设计订单信息表的时候，可以把客户名称、所属公司、联系方式等信息拆分到客户信息表中，然后在订单信息表中用客户编号进行关联。

内连接、外连接、交叉连接、笛卡尔积呢？

• 内连接（inner join）：inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集。

只有当两个表中都有匹配的记录时，这些记录才会出现在查询结果中。如果某一方没有匹配的记录，则该记录不会出现在结果集中，相当于两个数据集的交集。

• 外连接（outer join）：不只取得两张表中满足存在连接匹配关系的记录，还包括某张表（或两张表）中不满足匹配关系的记录，相当于两个数据集的并集。

• 交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选，它是笛卡尔积在 SQL 中的实现，如果 A 表有 m 行，B 表有 n 行，那么 A 和 B 交叉连接的结果就有 m*n 行。

• 笛卡尔积：是数学中的一个概念，例如集合 A={a,b}，集合 B={0,1,2}，那么 A✖️B={<a,0>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}。

②、left join 返回左表（FROM 子句中指定的表）的所有记录，以及右表中匹配记录的记录。如果右表中没有匹配的记录，则结果中右表的部分会以 NULL 填充。

③、right join 刚好与左联相反，返回右表（FROM 子句中指定的表）的所有记录，以及左表中匹配记录的记录。如果左表中没有匹配的记录，则结果中左表的部分会以 NULL 填充。

drop、delete 与 truncate 的区别？

三者都表示删除，但是三者有一些差别：

因此，在不再需要一张表的时候，用 drop；在想删除部分数据行时候，用 delete；在保留表而删除所有数据的时候用 truncate。

一条SQL 查询语句在 MySQL 中如何执行的？

查询语句的执行流程如下：权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->存储引擎

• 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

• 通过分析器进行词法分析，提取 SQL 语句的关键元素，比如提取这个语句是查询 select、提取需要查询的表名为、需要查询所有的列、查询条件。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

• 接下来就是优化器进行确定执行方案，优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

• 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库存储引擎接口，返回存储引擎的执行结果。

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

• 先查询到指定的这一条数据，不会走查询缓存，因为更新语句会导致与该表相关的查询缓存失效。

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

• 执行器收到通知后记录 binlog，然后调用存储引擎接口，提交 redo log 为提交状态。更新完成。

count(1)、count(*) 与 count(列名) 的区别？

都可以用来统计行数

• COUNT(1): 计算查询结果集中的行数，性能与 COUNT(*) 基本相同。

• COUNT(*): 计算查询结果集中的总行数，包括所有列，不忽略任何行，通常是最常用和推荐的方式。

• COUNT(列名): 计算查询结果集中某一列非 NULL 值的行数，适用于统计特定列中的有效数据。

执行速度：

• 列名为主键，count(列名)会比 count(1)快；列名不为主键，count(1)会比 count(列名)快

• 如果表有多个列并且没有主键，则 count（1） 的执行效率优于 count（*）

• 如果有主键，则 select count（主键）的执行效率是最优的

• 如果表只有一个字段，则 select count（*）最优。

存储引擎

表为什么不存年龄字段

1. 每个人年龄不固定，动态变化

2. 每个人变化的时间不一样

3. 已经有身份证和出生年月，可以计算

日志

MySQL 日志文件有哪些？分别介绍下作用？

MySQL 主要日志

MySQL 日志文件有很多，包括 ：

• 错误日志（error log）：错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录，能帮助定位 MySQL 问题。

• 慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。

• 一般查询日志（general log）：一般查询日志记录了所有对 MySQL 数据库请求的信息，无论请求是否正确执行。

• 二进制日志（bin log）：它记录了数据库所有执行的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句, 但是不包括数据查询语句（select、show 等）。

还有两个 InnoDB 存储引擎特有的日志文件：

• 重做日志（redo log）：记录了对于 InnoDB 存储引擎的事务日志，当事务进行写操作时，InnoDB 首先会写入 redo log，记录数据的更改，并不会立即修改数据文件。当 redo log 填满或在某些其他情况下，InnoDB 会异步将这些更改刷新到数据文件中。在系统崩溃后，redo log 可用于恢复数据。（保证事务的持久性）

• 回滚日志（undo log）： 记录了事务发生之前的数据，如果事务执行失败或调用了 rollback，导致事务需要回滚，就可以利用 undo log 中的信息将数据回滚到修改之前的样子（保证事务的原子性）

binlog 和 redo log 有什么区别？

• bin log 会记录所有与数据库有关的日志记录，而 redo log 只记 InnoDB 存储引擎的日志。

• 记录的内容不同，bin log 记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而 redo log 记录的是关于每个页（Page）的更改的物理情况。

• 写入的时间不同，bin log 仅在事务提交前进行写入。而在事务进行的过程中，redo log不断被写入 。

• 写入的方式也不相同，bin log 是追加写入，不会覆盖已经写的文件，redo log 是循环写入和擦除。

索引

索引的分类

从功能上分类：

①、主键索引: 表中每行数据唯一标识的索引，强调列值的唯一性和非空性。

id 列被指定为主键索引，同时，MySQL 会自动为这个列创建一个聚簇索引（主键索引一定是聚簇索引）。

可以通过 show index from 表名 查看索引信息

②、唯一索引: 保证数据列中每行数据的唯一性，但允许有空值。

可以通过下面的语句创建唯一索引：

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

alter table 表名 add unique(列名);

③、普通索引: 基本的索引类型，用于加速查询。

可以通过下面的语句创建普通索引：

CREATE INDEX 索引名 ON 表名(列名);

alter table 表名 add index 索引名(列名1,列名2,列名3);

④、全文索引：特定于文本数据的索引，用于提高文本搜索的效率。

假设有一个名为 articles 的表，下面这条语句在 content 列上创建了一个全文索引。

CREATE FULLTEXT INDEX idx_article_content ON 表名(列名);

alter table 表名 add fulltext(列名);

从存储位置上分类：

①、聚簇索引：聚簇索引的叶子节点中，包含了一个完整的记录行。

②、非聚簇索引：它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。

在 InnoDB 中，如果表定义了主键，那么主键索引就是聚簇索引。如果没有明确指定主键，InnoDB 会自动选择一个唯一索引作为聚簇索引。如果表没有任何唯一索引，InnoDB 将自动生成一个隐藏的行 ID 作为聚簇索引。

主键和索引的区别

1. 主键（Primary Key）

• 定义：主键是一列或多列的组合，用于唯一标识表中的每一行。一个表只能有一个主键。

• 特点：

  • 主键列的值必须是唯一的，不能有重复值。

  • 主键列的值不能为空（NOT NULL）。

  • 主键会自动创建主键索引，为主键列提供快速查找的功能，并同时保证主键的唯一性和非空约束。

• 作用：

  • 主键用于唯一标识表中的每条记录，确保数据的完整性。

  • 主键建立的唯一索引有助于快速定位数据，但主键的主要目的是保证数据的唯一性。

2. 索引（Index）

• 定义：索引是一种用于提高数据检索速度的数据结构，可以在一个表上建立多个索引。它是对表中的一列或多列进行排序的结构。

• 特点：

  • 索引列的值可以是唯一的或非唯一的。

  • 索引列的值可以为空。

  • 索引的类型多种多样，包括普通索引、唯一索引、复合索引、全文索引等。

• 作用：

  • 索引的主要作用是加快查询和数据检索速度。

  • 在 WHERE、ORDER BY、GROUP BY 等查询操作中使用索引可以大幅提高数据访问效率。

• 代价：索引占用存储空间，并且在执行数据插入、删除和更新操作时，索引需要同步更新，增加了一定的性能开销。

3. 区别总结

项目	主键	索引
作用	唯一标识每条记录，保证数据完整性	提高查询效率
唯一性	值必须唯一	可以唯一或非唯一
允许为空	不允许为空	可以为空（视索引类型而定）
数量限制	每个表只能有一个	每个表可以有多个
自动索引	自动创建唯一索引	手动创建
主要目的	数据完整性和唯一性	查询加速

为什么使用索引会加快查询？

数据库存储在磁盘上，磁盘 I/O 是数据库操作中最耗时的部分之一。没有索引时，数据库会进行全表扫描

有了索引，就可以直接跳到索引指示的数据位置，而不必扫描整张表，从而大大减少了磁盘 I/O 操作的次数。

索引文件相较于数据库文件，体积小得多，查到索引之后再映射到数据库记录，查询效率就会高很多。

MySQL 的 InnoDB 存储引擎默认使用 B+ 树来作为索引的数据结构，而 B+ 树的查询效率非常高，时间复杂度为 O(logN)。

创建索引

什么时候需要创建索引

创建索引有哪些注意点？

①、选择合适的列作为索引

• 经常作为查询条件（WHERE 子句）、排序条件（ORDER BY 子句）、分组条件（GROUP BY 子句）的列

• 区分度低的字段，例如性别，不要建索引

• 频繁更新的字段，不要作为主键或者索引

②、避免过多的索引

• 每个索引都需要占用额外的磁盘空间。

• 更新表（INSERT、UPDATE、DELETE 操作）时，所有的索引都需要被更新。

• 维护索引文件需要成本；还会导致页分裂，IO 次数增多。

③、在创建复合索引时，应该根据查询条件将最常用作过滤条件的列放在前面。

什么时候不需要创建索引

索引失效

• 查询条件包含 or，OR 条件中部分字段没有索引

• 以%开头的like 查询可能会导致索引失效。

• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

• 查询条件的类型与索引列的类型不匹配，例如索引列是字符串类型，但条件中使用数值。

• 在索引列上使用了函数，列运算（如，+、-、*、/），索引失效。

• 索引字段上使用负向条件 != 、<>、not in、not exists、not like 等，可能会导致索引失效。

• 索引字段上使用 is null， is not null，可能导致索引失效。

• MySQL 优化器估计使用全表扫描要比使用索引快，则不使用索引。

建立联合索引（a, b, c）

-- 使用了a列
where a = 3
 
-- 使用了a b列
where a = 3 and b = 5
 
-- 使用了a b c列
where a = 3 and c = 4 and b = 5
 
-- 没有使用索引
where b = 3
 
-- 使用了a列 
where a = 3 and c = 4
 
-- 使用了a b列 
where a = 3 and b > 10 and c = 7
 
-- 使用了a b 列
where a = 3 and b like 'xx%' and c = 7

explain 中的 type

EXPLAIN SELECT * FROM order_info WHERE order_status = 'FINISHED';

至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

（1）consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

（2）ref：使用普通的索引，非主键非唯一索引等值扫描，返回数据不唯一的等值查找就可能出现。

（3）range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中

（4）index: 索引树全表扫描，常见于使用索引列就可以处理的查询，如覆盖索引

（5）all: 全表扫描(full table scan)

null > system > const > eq_ref > ref > range > index > all;

• 索引树扫描可以从树根往下 做类似的 二分查找， 时间复杂度是 o（logn）； 全表扫描 是扫描所有的 叶子节点， 时间复杂度是 o（n）；

• 全表扫描不仅仅需要扫描索引列，还需要扫描每个索引列中指向的实际数据，这里包含了所有的非索引列数据。

为什么用B+树

MySQL 的默认存储引擎是 InnoDB，它采用的是 B+树索引

B +树是一种自平衡的多路查找树，B+树的每个节点可以有 m 个子节点。

B+树的非叶子节点只存储键值，不存储数据，而叶子节点存储了所有的数据，并且构成了一个有序链表。

这样做的好处是，非叶子节点上由于没有存储数据，就可以存储更多的键值对

再加上叶子节点构成了一个有序链表，范围查询时就可以直接通过叶子节点间的指针顺序访问整个查询范围内的所有记录，而无需对树进行多次遍历。

（红黑树和二叉平衡树都只有 2 个）

为什么用 B+ 树而不用 B 树呢？

聚簇索引与非聚簇索引的区别？

聚簇索引不是一种新的索引，而是一种数据存储方式。

聚簇索引直接将数据存储在 B+树的叶子节点中

非聚簇索引的叶子节点上存储的并不是真正的行数据，而是主键 ID

所以当我们使用非聚簇索引进行查询时，首先会得到一个主键 ID，然后再使用主键 ID 去聚簇索引上找到真正的行数据，我们把这个过程称之为回表查询。

InnoDB 回表

覆盖索引

使用非主键索引查询数据时需要回表，但如果索引的叶节点中已经包含要查询的字段，那就不会再回表查询了，这就叫覆盖索引。

举个例子，现在要从 test 表中查询 city 为上海的 name 字段。

select name from test where city='上海'

如果仅在 city 字段上添加索引，那么这条查询语句会先通过索引找到 city 为上海的行，然后再回表查询 name 字段，这就是回表查询。

为了避免回表查询，可以在 city 和 name 字段上建立联合索引，这样查询结果就可以直接从索引中获取。

alter table test add index index1(city,name);

数据库是先建立索引还是先插入数据？

在 InnoDB 中，数据插入和索引创建（更新）是密不可分的。从数据库的视角看，插入操作包括向聚簇索引添加记录和更新所有相关的次级索引。这些步骤在一个事务中原子地执行，以确保数据的一致性和完整性。

在 InnoDB 中，如果表定义了主键，那么主键索引就是聚簇索引。如果没有明确指定主键，InnoDB 会自动选择一个唯一索引作为聚簇索引。如果表没有任何唯一索引，InnoDB 将自动生成一个隐藏的行 ID 作为聚簇索引。

这意味着当插入新数据时，InnoDB 首先将数据插入到聚簇索引中。这一步骤实质上是创建索引的一部分，因为数据存放在索引结构中。

InnoDB 还需要更新表的所有次级索引。这些索引中的每一个都包含指向聚簇索引记录的指针。

订单表建索引

订单表通常是数据库中的一个关键表，经常会有大量的查询、插入和更新操作。在合适的地方添加索引可以显著提高查询效率，但过度的索引可能会给插入和更新操作带来负担。所以需要根据实际需求和访问模式来决定哪些字段需要添加索引。

索引需求通常会基于以下几种场景：

1. 频繁查询的列：如果有某些字段经常被用在 WHERE 子句中，例如订单号(order_id)，用户ID(user_id)，或者订单状态(status)等，这些列都是需要考虑添加索引的。

2. ORDER BY 操作：如果你的应用经常需要对某个字段进行排序展示，例如按照订单的创建时间(create_time)排序，那么在该字段上添加索引能加快排序的速度。

选择性（Cardinality） 是指列中不同值的数量与总记录数的比例。简单来说，选择性越高（即字段的不同值越多、重复率越低），索引的效果就越好。

• 如果 status 字段的值非常少且重复率高（例如只有 "下单"、"已支付"、"已取消" 这几种状态），那么普通索引可能不会显著提升查询效率，因为数据库可能会选择进行全表扫描。这种情况下，索引的选择性太低，查询时即便走索引，命中的行数也会很多，反而可能导致查询性能变差。

那我们怎么判断,这个选择性呢? 有个方法

判断方法：可以通过 MySQL 的 SHOW INDEX 命令查看索引的 Cardinality 值，它表示索引的选择性。值越大，索引越有效。sql SHOW INDEX FROM order_info WHERE Key_name = 'idx_order_status'; 如果 Cardinality 很低，说明创建的普通索引对性能提升不大。

锁

MySQL 中有哪几种锁，列举一下？

MySQL 中的锁

如果按锁粒度划分，有以下 3 种：

• 表锁：开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁。（意向锁）

• 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。（记录锁，间隙锁，临键锁）

• 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

如果按照兼容性，有两种

• 共享锁（S Lock）,也叫读锁（read lock），相互不阻塞。大家可以一起读，但谁也不能改

如果一个事务对数据对象A加上S锁，则这个事务可以读A但不能修改A，其他事务只能再对A加读锁，而不能加写锁。这保证了其他事务可以读取A，但在事务T释放对象A上的S锁之前不能对A做任何修改。

-- 加共享锁
LOCK TABLE product_comment READ;
-- 解共享锁
UNLOCK TABLE product_comment;

• 排他锁（X Lock），也叫写锁（write lock），排它锁是阻塞的，在一定时间内，只有一个请求能执行写入，并阻止其它事务读取和写入。

一个事务对数据对象A加上X锁，这个事务可以读A也可以修改A，其他事务不能再对A加任何锁，直到这个事务释放A上的锁。这保证了其他事务在这期间不能在读取和修改A。

当我们对数据进行更新的时候，也就是INSERT、DELETE或者UPDATE的时候，数据库也会自动使用排它锁，防止其他事务对该数据行进行操作。

-- 加写锁
LOCK TABLE product_comment WRITE;
-- 解锁
UNLOCK TABLE product_comment;

InnoDB 里的行锁实现?

• Record Lock 记录锁

记录锁就是直接锁定某行记录。当我们使用唯一性的索引(包括唯一索引和聚簇索引)进行等值查询且精准匹配到一条记录时，此时就会直接将这条记录锁定。例如select * from t where id =6 for update;就会将id=6的记录锁定。

• Gap Lock 间隙锁

间隙锁(Gap Locks) 的间隙指的是两个记录之间逻辑上尚未填入数据的部分,是一个左开右开空间。

间隙锁

间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。例如select * from t where id > 1 and id < 6 for update;就会将(1,6)区间锁定。

• Next-key Lock 临键锁

临键指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,6]、(6,8]等。

临键锁

临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。注意，临键锁锁住的区间会包含最后一个 record 的右边的临键区间。例如select * from t where id > 5 and id <= 7 for update;会锁住(4,7]、(7,+∞)。

mysql 默认行锁类型就是临键锁(Next-Key Locks)。当使用唯一性索引，等值查询匹配到一条记录的时候，临键锁(Next-Key Locks)会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁。

如果没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

间隙锁(Gap Locks)和临键锁(Next-Key Locks)都是用来解决幻读问题的，在读已提交（READ COMMITTED）隔离级别下，间隙锁(Gap Locks)和临键锁(Next-Key Locks)都会失效！

• 幻读：事务 A 查询一个范围的结果集，另一个并发事务 B 往这个范围中插入 / 删除了数据，然后事务 A 再次查询相同的范围，两次读取得到的结果集不一样了

意向锁是什么知道吗？

意向锁是一个表级锁。它解决的是表锁和行锁共存的问题。

有了意向锁这个表级锁之后，我们直接判断一次就知道表中是否有数据行被锁定。

要执行的事务 A 在申请行锁（写锁）之前，数据库会自动先给事务 A 申请表的意向排他锁。

当事务 B 去申请表的互斥锁时就会失败，因为表上有意向排他锁之后事务 B 申请表的互斥锁时会被阻塞。

MySQL 的乐观锁和悲观锁了解吗？

• 悲观锁（Pessimistic Concurrency Control）：

悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能被改动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。

数据库中的行锁，表锁，读锁，写锁均为悲观锁。

• 乐观锁（Optimistic Concurrency Control）

乐观锁认为数据的变动不会太频繁。

它通常是通过在表中增加一个版本(version)或时间戳(timestamp)来实现，其中，版本最为常用。

事务在从数据库中取数据时，会将该数据的版本也取出来(v1)，当事务对数据变动完毕想要将其更新到表中时，会将之前取出的版本 v1 与数据中最新的版本 v2 相对比，如果 v1=v2，那么说明在数据变动期间，没有其他事务对数据进行修改，此时，就允许事务对表中的数据进行修改，并且修改时 version 会加 1，以此来表明数据已被变动。

如果，v1 不等于 v2，那么说明数据变动期间，数据被其他事务改动了，此时不允许数据更新到表中，一般的处理办法是通知用户让其重新操作。

死锁问题，如何解决

在 MySQL 中可以使用命令：SHOW ENGINE INNODB STATUS;

该命令将返回当前 InnoDB 存储引擎的状态信息，包括最近的死锁情况。

排查死锁的一般步骤是这样的：

（1）查看死锁日志 show engine innodb status;

（2）找出死锁 sql

（3）分析 sql 加锁情况

（4）模拟死锁案发

（5）分析死锁日志

（6）分析死锁结果

解决死锁

• 优化事务：尽量减少事务的持续时间和锁的持有时间。

• 控制锁顺序：确保多个事务以相同的顺序请求锁。

• 适当的隔离级别：在可接受的范围内，降低事务的隔离级别，减少锁的竞争。

死锁产生的四个必要条件

1. 互斥条件：资源是独占的且排他使用，进程互斥使用资源，即任意时刻一个资源只能给一个进程使用，其他进程若申请一个资源，而该资源被另一进程占有时，则申请者等待直到资源被占有者释放。

2. 不可剥夺条件：进程所获得的资源在未使用完毕之前，不被其他进程强行剥夺，而只能由获得该资源的进程资源释放。

3. 请求和保持条件：进程每次申请它所需要的一部分资源，在申请新的资源的同时，继续占用已分配到的资源。

4. 循环等待条件：在发生死锁时必然存在一个进程等待队列{P1,P2,…,Pn},其中P1等待P2占有的资源，P2等待P3占有的资源，…，Pn等待P1占有的资源，形成一个进程等待环路，环路中每一个进程所占有的资源同时被另一个申请，也就是前一个进程占有后一个进程所深情地资源。

以上给出了导致死锁的四个必要条件，只要系统发生死锁则以上四个条件至少有一个成立。

死锁预防

我们可以通过破坏死锁产生的4个必要条件来 预防死锁，由于资源互斥是资源使用的固有特性是无法改变的。

• 破坏 不可剥夺 条件：一个进程不能获得所需要的全部资源时便处于等待状态，等待期间他占有的资源将被隐式的释放重新加入到 系统的资源列表中，可以被其他的进程使用，而等待的进程只有重新获得自己原有的资源以及新申请的资源才可以重新启动，执行。

• 破坏 请求与保持 条件：第一种方法静态分配即每个进程在开始执行时就申请他所需要的全部资源。第二种是动态分配即每个进程在申请所需要的资源时他本身不占用系统资源。

• 破坏 循环等待 条件：采用资源有序分配其基本思想是将系统中的所有资源顺序编号，将紧缺的，稀少的采用较大的编号，在申请资源时必须按照编号的顺序进行，一个进程只有获得较小编号的进程才能申请较大编号的进程。

事务

BEGIN, ROLLBACK, COMMIT

MySQL 事务的四大特性说一下？（ACID）

原子性（Atomicity） 一致性（Consistency） 隔离性 （Isolation）持久性（Durability）

那 ACID 靠什么保证的呢？

MySQL 通过事务管理和持久性存储机制来确保 ACID（原子性、一致性、隔离性和持久性）。

1. 原子性（Atomicity）

undo log 是 InnoDB 存储引擎来确保事务原子性的关键机制，undo log 记录了事务发生之前的数据，如果事务失败，InnoDB 会根据 undo log 回滚数据。

• 一致性（Consistency）

一致性是 ACID 的目的，也就是说，只要保证原子性、隔离性、持久性，自然也就保证了数据的一致性。

• 隔离性 (Isolation)

InnoDB 存储引擎使用 MVCC (多版本并发控制) 机制来处理并发事务，确保每个事务都有自己的数据版本。

事务查看数据时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

• 持久性 (Durability)

InnoDB 使用“redo log”来记录数据的更改，在系统崩溃后，redo log 可用于恢复数据。

当事务进行写操作时，InnoDB 首先会写入 redo log，并不会立即修改数据文件。

当 redo log 填满或在某些其他情况下，InnoDB 会异步将这些更改刷新到数据文件中。

系统崩溃时，由于数据可能还没有被真正写入数据文件，但已经在 redo log 中，因此系统可以在启动时使用这些日志来重新执行或“重做”这些更改，确保数据的持久性。

即使数据库在事务提交后立即崩溃，由于事务的更改已经记录在 redo log 中，这些更改在数据库恢复时仍然是安全的。

事务的隔离级别有哪些？默认：可重复读

MySQL 默认的事务隔离级别是可重复读 (Repeatable Read)。

脏读，幻读，不可重复读

1. 脏读（Dirty Read）

• 定义：一个事务读取了另一个未提交事务所修改的数据。如果另一个事务回滚，这个读到的数据就是不正确的（脏数据）。

• 场景：事务 A 更新一条记录，但未提交；事务 B 读取了此未提交的更新数据。如果事务 A 回滚，则事务 B 读取到的数据是无效的。

• 不可重复读（Non-repeatable Read）

• 定义：同一个事务中多次读取同一条记录，却得到了不同的结果，因为在两次读取之间，另一事务修改了数据。

• 场景：事务 A 在读取了一条数据后，事务 B 更新了该数据；当事务 A 再次读取时发现数据发生了变化，导致数据不一致。

• 幻读（Phantom Read）

• 定义：一个事务在读取某个范围的数据时，另一个事务插入或删除了该范围内的记录，导致前一个事务再次读取该范围数据时，结果集发生变化。

• 场景：事务 A 读取了满足某个条件的多条记录，事务 B 插入了一条新记录或删除了一条符合条件的记录，事务 A 再次读取时发现记录数发生了变化。

事务的各个隔离级别都是如何实现的？

读未提交，采取的是读不加锁原理。

读取已提交和可重复读级别利用了MVCC，也就是每个事务只能读取它能看到的版本

串行化的实现采用的是读写都加锁的原理。

MVCC 了解吗？怎么实现的？

SQL优化

判断慢查询

• 设置慢查询日志：大多数数据库允许启用慢查询日志功能（如 MySQL 的 slow_query_log），记录所有执行时间超过指定阈值的查询。

在 MySQL 中，可以使用以下命令启用慢查询日志：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 记录执行时间超过 2 秒的查询

再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志，找出问题的根源。

• 查询性能分析：使用 EXPLAIN 命令分析 SQL 查询的执行计划，查看查询是否合理，是否存在全表扫描等性能问题。

在select 语句前加上explain关键字，执行的时候并不会真正执行sql语句，而是返回sql查询语句对应的执行计划信息。

至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

（1）consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。

（2）ref：使用普通的索引，非主键非唯一索引等值扫描，返回数据不唯一的等值查找就可能出现。

（3）range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中

（4）index: 索引树全表扫描，常见于使用索引列就可以处理的查询，如覆盖索引

（5）all: 全表扫描(full table scan)

null > system > const > eq_ref > ref > range > index > all;

• 索引树扫描可以从树根往下 做类似的 二分查找， 时间复杂度是 o（logn）； 全表扫描 是扫描所有的 叶子节点， 时间复杂度是 o（n）；

• 全表扫描不仅仅需要扫描索引列，还需要扫描每个索引列中指向的实际数据，这里包含了所有的非索引列数据。

优化慢 SQL？

1. 如何避免不必要的列？

比如说尽量避免使用 select *，只查询需要的列，减少数据传输量。

SELECT * FROM employees WHERE department_id = 5;

改成：

SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 5;

• 如何进行分页优化？

书签（Seek Method）

书签方法通过记住上一次查询返回的最后一行的某个值，然后下一次查询从这个值开始，避免了扫描大量不需要的行。

假设需要对用户表进行分页，根据用户 ID 升序排列。

SELECT id, name
FROM users
ORDER BY id
LIMIT 1000, 20;

书签方式：

SELECT id, name
FROM users
WHERE id > last_max_id  -- 假设last_max_id是上一页最后一行的ID
ORDER BY id
LIMIT 20;

优化后的查询不再使用OFFSET，而是直接从上一页最后一个用户的 ID 开始查询。这里的last_max_id是上一次查询返回的最后一行的用户 ID。这种方法有效避免了不必要的数据扫描，提高了分页查询的效率。

• 索引优化

• 如何进行 JOIN 优化？

对于 JOIN 操作，可以通过优化子查询、小表驱动大表、适当增加冗余字段、避免 join 太多表等方式来进行优化。

①、优化子查询

子查询，特别是在 select 列表和 where 子句中的子查询，往往会导致性能问题，因为它们可能会为每一行外层查询执行一次子查询。

使用子查询：

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

使用 JOIN 代替子查询：

select A.name from A join B on A.id=B.id;

②、小表驱动大表

在执行 JOIN 操作时，应尽量让行数较少的表（小表）驱动行数较多的表（大表），这样可以减少查询过程中需要处理的数据量。

比如 left join，左表是驱动表，所以 A 表应小于 B 表，这样建立连接的次数就少，查询速度就快了。

select name from A left join B;

③、在表中适当增加冗余字段来避免 JOIN 操作

在某些情况下，通过在表中适当增加冗余字段来避免 JOIN 操作，可以提高查询效率，尤其是在高频查询的场景下。

比如，我们有一个订单表和一个商品表，查询订单时需要显示商品名称，如果每次都通过 JOIN 操作查询商品表，会降低查询效率。这时可以在订单表中增加一个冗余字段，存储商品名称，这样就可以避免 JOIN 操作。

select order_id, product_name from orders;

④、避免使用 JOIN 关联太多的表

《阿里巴巴 Java 开发手册》上就规定，不要使用 join 关联太多的表，最多不要超过 3 张表。

因为 join 太多表会降低查询的速度，返回的数据量也会变得非常大，不利于后续的处理。

如果业务逻辑允许，可以考虑将复杂的 JOIN 查询分解成多个简单查询，然后在应用层组合这些查询的结果。

• 如何进行排序优化？

MySQL 生成有序结果的方式有两种：一种是对结果集进行排序操作，另外一种是按照索引顺序扫描得出的自然有序结果。

因此在设计索引的时候要充分考虑到排序的需求。

select id, name from users order by name;

如果 name 字段上有索引，那么 MySQL 可以直接利用索引的有序性，避免排序操作。

高性能

分库分表

数据库读写分离了解吗？

读写分离的基本原理是将数据库读写操作分散到不同的节点上，下面是基本架构图：

主从复制是怎么实现？

MySQL 的主从复制依赖于 binlog ，复制的过程就是将 binlog 中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

具体详细过程如下：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应, 主库会创建 log dump 线程通知从库, 主库中存在数据更新。

• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 中继日志里，再返回给主库“复制成功”的响应。

• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

从库是不是越多越好？

不是的。

因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。

所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

MySQL 主从复制还有哪些模型？

主要有三种：

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。

• 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。

• 半同步复制：事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

对于不同的业务需求，有不同的策略方案，但是一般都会采用 最终一致性，不会要求 强一致性，毕竟强一致性会严重影响性能。

最终一致性：

• 不保证在任意时刻任意节点上的同一份数据都是相同的，但是随着时间的迁移，不同节点上的同一份数据总是在向趋同的方向变化

• 最终两个字用得很微妙，因为从写入主库到反映至从库之间的延迟，可能仅仅是几分之一秒，也可能是几个小时。

百万级别以上的数据如何删除？

1. 所以我们想要删除百万数据的时候可以先删除索引

2. 然后删除其中无用数据

百万千万级大表如何添加字段？

避免长时间锁表。

• 通过中间表转换过去

创建一个临时的新表，把旧表的结构完全复制过去，添加字段，再把旧表数据复制过去，删除旧表，新表命名为旧表的名称，这种方式可能会丢掉一些数据。

• 先在从库添加 再进行主从切换

如果一张表数据量大且是热表（读写特别频繁），则可以考虑先在从库添加，再进行主从切换，切换后再将其他几个节点上添加字段。

MySQL 数据库 cpu 飙升的话，要怎么处理呢？

排查过程：

（1）使用 top ps 命令观察，确定是 mysql导致还是其他原因。

（2）如果是 mysql 导致的，show processlist，查看 session 情况，确定是不是有消耗资源的 sql 在运行。

（3）找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

（1）kill 掉这些线程 (同时观察 cpu 使用率是否下降)，

（2）进行相应的调整 (比如说加索引、改 sql、改内存参数)

（3）重新跑这些 SQL。

其他情况：

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等