面试题-MySQL

[TOC]

基础部分

SQL查询关键词执行的顺序

SQL执行顺序：from→on→join→where→group by→agg_func→with→having→select→distinct→order by→limit

1. FROM：指定查询的数据源，即表名或视图名。
2. ON：指定连接条件，用于连接多个表。
3. JOIN：根据连接条件，将多个表的数据进行关联。内连接（INNER）、左外连接（LEFT）、右外连接（RIGHT）
4. WHERE：指定查询的条件，只有符合条件的行才会被返回。
5. GROUP BY：按照指定的列或表达式对数据进行分组。
6. AGG_FUNC：对每个组进行聚合操作。
7. WITH：可选WITH CUBE或WITH ROLLUP，在 GROUP BY 子句中使用，生成更多的汇总行。
8. HAVING：类似于 WHERE，但是作用于已经分组的结果集，可以使用聚合函数进行筛选。
9. SELECT：选择要返回的列或计算的表达式。
10. DISTINCT：去除重复的行。不指定默认为all，表示返回所有行
11. ORDER BY：按照指定的列对结果集进行排序。升序排序（ASC）或者降序排序（DESC）。
12. LIMIT：限制返回记录的数量。

select [distinct / all] 
	聚合函数/字段列表
from
	表名称1
	 [left / right / inner] join 表名称2 on 多表的连接条件
	……
where
	条件表达式，建议不包含聚合函数的过滤条件
group by 
	分组字段
having
	分组后的条件表达式，建议包含聚合函数的过滤条件
order by
	排序字段[asc / desc]
limit
	[查询的起始行,]每页显示的条数

表跟表是怎么关联的？

表与表之间关联方式有很多种

• 自连接：自连接是指将表与自身进行连接。
• 交叉连接：叉连接也称为笛卡尔积，它返回两个表的所有可能组合。
• 合并查询：合并查询用于将两个或多个查询的结果合并成一个结果集。
• 内连接：内连接返回两个表中满足连接条件的交集。内连接可以分为隐式内连接和显示内连接。
• 外连接：外连接返回满足连接条件的行以及未匹配的行。外连接可以分为左外连接、右外连接和全外连接。

说一说内连接？

内连接返回两个表中满足连接条件的交集（A∩B）。内连接可以分为隐式内连接和显示内连接。

• 隐式内连接：隐式内连接使用WHERE子句来指定连接条件，将两个或多个表进行连接。

• 显式内连接：显式内连接使用JOIN子句来指定连接条件，并明确地声明使用哪种连接方式

# 隐式内连接
SELECT *
FROM table1, table2
WHERE table1.column = table2.column;

# 显式内连接
SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

说一说外连接？

外连接返回满足连接条件的行以及未匹配的行。外连接可以分为左外连接（A+A∩B）、右外连接（A∩B+B）和全外连接（A+A∩B+B）。

• 左外连接（LEFT JOIN）：返回包括左表中所有行和右表中匹配行的结果集。

• 右外连接（RIGHT JOIN）：返回包括右表中所有行和左表中匹配行的结果集。

• 全外连接（FULL JOIN）：返回左表和右表中所有行的结果集，如果没有匹配的行，则用NULL填充。MySQL不支持全外连接，但是可以使用UNION关键字将左外连接和右外连接的结合获取

# 左外连接（LEFT JOIN）
SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
# 右外连接（RIGHT JOIN）
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
# 全外连接（FULL JOIN）
SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

常见的聚合函数？

MySQL中常见的聚合函数包括以下几种：

• **SUM(column_name)**：用于计算指定列的总和。
• **MAX(column_name)**：用于找出指定列的最大值。
• **MIN(column_name)**：用于找出指定列的最小值。
• **AVG(column_name)**：用于计算指定列的平均值。
• **COUNT(column_name)**：用于统计指定列的非空记录数。

什么是SQL注入？

攻击者通过在用户输入中插入特殊字符或SQL命令来欺骗应用程序，使应用程序误以为这些恶意内容是合法的输入，并将其作为SQL查询的一部分执行。这样，攻击者可以控制SQL查询的逻辑，执行未经授权的操作，如删除数据、泄露敏感信息等。

怎样防止SQL注入？

为了预防SQL注入攻击，可以采取以下几种措施：

• 输入验证和过滤：对用户输入进行严格的验证和过滤，只允许合法的输入字符通过，可以通过正则表达式或白名单来实现。同时，还可以使用安全框架或库来过滤潜在的恶意输入。
• 权限控制：对用户进行分级管理，严格控制用户的权限，对于普通用户，禁止给予数据库建立、删除、修改等相关权限，只有系统管理员才具有增、删、改、查的权限。
• 使用参数化查询：使用参数化查询可以将用户输入和SQL查询逻辑分离，数据库引擎会对输入进行参数化处理，从而防止攻击者插入恶意代码。大多数编程语言和框架都支持参数化查询。

视图是什么？

视图（View）是一种虚拟的表，基于一个或多个表的查询结果所创建的，可以看作是预定义查询，并不存储实际数据

# 创建视图
create  view 视图名 as 查询语句;
# 查看视图
select * from 视图名称;

存储过程是什么？

存储起来的SQL语句，预先编译，执行快，通过指定名称调用，占资源，调试难，通常用于封装常用的业务逻辑，但不建议使用

# 创建存储过程
CREATE PROCEDURE GetEmployeeCount()
BEGIN
    SELECT COUNT(*) FROM employees;
END;
# 使用存储过程
CALL GetEmployeeCount();

存储过程的优点？

1. 一次编译多次使用：存储过程只在创建时进行编译，之后的使用都不需要重新编译， 这就提升了 SQL 的执行效率。
2. 简化SQL使用：在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可 。

存储过程的缺点？

• 可移植性差：存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
• 占用资源：存储过程需要在数据库中占用一定的空间，可能会导致数据库的数据膨胀
• 调试困难：存储过程中的SQL语句通常比较复杂，调试起来相对困难
• 维护困难：索引发生变化，可能会导致存储过程失效，存储过程本身没有版本控制，版本迭代更新的时候很麻烦

存储函数是什么？

存储函数可以理解为自定义函数，存储起来的SQL语句，是一串执行逻辑，必须有返回值

# 创建存储函数
CREATE FUNCTION GetEmployeeCount() RETURNS INT
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM employees;
    RETURN count;
END;
# 使用存储函数
SELECT GetEmployeeCount();

存储函数的优点？

1. 代码重用：存储函数可以在多个查询和存储过程中被调用，实现代码的重用，提高开发效率和维护性。
2. 扩展性：存储函数可以通过自定义函数来增强数据库的功能，满足特定的业务需求。

存储函数的缺点？

1. 性能影响：存储函数可能对数据库性能产生一定的影响。每次调用存储函数都需要与数据库进行交互，这会增加数据库的负载和响应时间。如果存储函数逻辑复杂或对大量数据进行操作，可能导致性能下降。
2. 难以调试：存储函数通常在查询语句中直接使用，而不像存储过程那样可以单独执行和调试。因此，在调试存储函数时可能需要通过调试整个查询语句来定位问题。

存储过程与存储函数的区别？

存储过程和存储函数的区别主要有以下几点：

1. 返回值类型：存储过程可以没有返回值或者返回多个结果集；存储函数必须返回一个标量值，如一个数字、一个字符串等。
2. 调用方式：存储过程使用CALL语句进行调用，而存储函数可以在SELECT语句中直接使用。
3. 参数传递方式：存储过程可以接收输入参数、输出参数和输入输出参数；而存储函数只能接收输入参数，不能有输出参数或输入输出参数。
4. 使用场景：存储过程适合于执行一系列复杂的数据库操作和事务处理，通常用于封装常用的业务逻辑；存储函数适合于进行计算和数据处理，并且可以方便地嵌入到SQL语句中使用。

共用表表达式（CTE）是什么？

公用表表达式（通用表表达式）简称为CTE（Common Table Expressions）是一种在SQL语句中定义临时查询结果的结构。

共用表表达式（CTE）是一种在SQL查询中创建临时结果集的方法，可以提高查询的可读性、重用性和性能，并且支持递归查询。可以理解成一个可以复用的子查询

# CTE语法
with CTE名称 
as (子查询) 
select|delete|update 语句;
# 使用子查询实现需求
select * from users where user_id in (select distinct user_id from user_info)
# 使用CTE实现需求
with testCTE
as (select distinct user_id from user_info)
select * from users join test on user.user_id = test.user_id;

EXISTS和IN的区别？

EXISTS 用于检查子查询是否返回结果，而 IN 用于检查某个值是否存在于给定的列表或子查询中。

• EXISTS：是一个条件表达式，返回一个布尔值（true或false），用于检查子查询是否返回结果。

• IN：是一个比较运算符，返回一个布尔值（true或false），用于检查某个值是否存在于给定的列表或子查询中。

# 检查子查询是否返回结果
SELECT column1 
FROM table1 
WHERE EXISTS (
    SELECT column2 
    FROM table2 
    WHERE condition
);
# 检查某个值是否存在于给定的列表或子查询中
SELECT column1 
FROM table1 
WHERE column2 IN (value1, value2, value3);

UNION和UNION ALL的区别？

他们都可以将两个查询的数据结合起来，区别就是union会去除并排序，UNION ALL不去重也不排序

MySQL如何做分页？

limit 起始索引,每页条数

起始索引 =（页码数-1） * 每页条数

如何将一张表的部分数据更新到另一张表？

步骤1：创建目标表

步骤2：复制源表的结构

步骤3：插入源表的数据

步骤4：更新目标表的数据

Where和Having区别？

WHERE和HAVING是SQL中的两个不同的子句，用于在查询中进行条件筛选和过滤。

• 执行时机：where用于在分组之前进行条件筛选，不满足where条件则不参与分组；having用于在分组之后对结果进行过滤
• 过滤条件有聚合函数：如果过滤条件中有聚合函数，过滤条件必须声明在having中，因为where不能使用聚合函数。
• 过滤条件无聚合函数：如果过滤条件中没有聚合函数，则此过滤条件声明在where中或having中都可以，但建议声明在where中，因为where的执行效率要高于having
• 执行顺序：where > 聚合函数 > having

数据库设计相关

数据库设计的三范式是什么？

数据库设计的三范式（3NF）是一组规则，用于规范化关系型数据库中的数据结构。

• 第一范式：确保每个列必须具有原子性，都不可以再拆分。目的是消除数据项的重复和复杂性，使数据结构更清晰和易于操作。
• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。目的是消除部分依赖，防止数据冗余和更新异常，提高数据的一致性和完整性。
• 第三范式：在第二范式的基础上，非主键列不依赖于其他非主键，只依赖于主键。目的是消除传递依赖，减少数据冗余和不一致性，提高数据的规范性和灵活性。

什么是范式和反范式？

范式和反范式都各自有其优缺点，具体应该根据业务需求和数据库性能等方面进行权衡，选择合适的数据设计方案。

• 范式（Normalization）：通过减少数据的重复和冗余来提高数据库的性能、可靠性和扩展性。范式的目标是使数据表符合特定的标准形式，也就是所谓的“范式”。
• 反范式（Denormalization）：通过增加数据冗余来提高数据库的性能、可靠性和可扩展性。反范式的目标是尽量减少数据表之间的关联操作，使查询更快速、简单和直接。

MySQL有哪数据类型？

字符数据类型：

• CHAR：固定长度字符类型。
• VARCHAR：可变长度字符类型。

整数数据类型：

• INT：整数类型，占用4字节。
• BIGINT：大整数类型，占用8字节。
• SMALLINT：小整数类型，占用2字节。

浮点数数据类型：

• FLOAT：单精度浮点数类型。
• DOUBLE：双精度浮点数类型。

日期/时间数据类型：

• DATE：日期类型，格式为’YYYY-MM-DD’。
• TIME：时间类型，格式为’HH:MM:SS’。
• DATETIME：日期和时间类型，格式为’YYYY-MM-DD HH:MM:SS’。
• TIMESTAMP：时间戳类型，记录从1970-01-01 00:00:00到当前时间的秒数。

二进制和文本数据类型：

• BLOB：二进制大对象类型，用于存储二进制数据。
• TEXT：文本类型，用于存储大量的字符数据。

表中主键为什么要设置自增？

在MySQL表中，设置主键为自增具有以下几个好处：

• 保证了唯一性：每次插入新记录时，数据库会自动分配一个比之前最大主键值更大的值，从而保证主键的唯一性，避免了主键冲突。
• 提高插入效率：自增ID在插入数据时会按照顺序递增，插入新数据时会直接在表的末尾添加，不会造成数据的移动和调整（减少了页分裂和页合并），能够提高插入效率。使用UUID和雪花算法生成的ID无序，可能导致页分裂和页合并等性能问题。
• 快速定位和检索：Innodb中主键一定是聚集索引，聚集索引下的数据记录是根据ID排序存储，主键索引的有序性使得范围查询、排序和连接等操作更加高效。

主键使用自增 ID 还是 UUID？

对于 MySQL 数据库的主键选择，推荐使用自增 ID

• 插入效率：自增ID在插入数据时会按照顺序递增，插入新数据时会直接在表的末尾添加，不会造成数据的移动和调整（减少了页分裂和页合并），能够提高插入效率。使用UUID和雪花算法生成的ID无序，可能导致页分裂和页合并等性能问题。
• 存储空间：整型的自增 ID 占用的存储空间相对较小，通常为 4 字节或 8 字节，而 UUID 类型通常需要占用 16 字节。
• 数据排序和分页查询：自增 ID 的顺序性使得数据按照插入顺序进行物理排序，方便执行范围查询和分页操作。
• 避免精度损失问题：前端可以接收的长整型数据（number）限制为16位，如果后端ID大于这个限制，那么在传递给前端时可能会丢失其中的一部分数据。使用UUID和雪花算法生成的ID一般会超过16位，导致前端精度损失问题，而自增主键可以选择适当的整数类型（如INT）作为自增主键，确保其长度不超过16位，避免精度损失问题。
• 易用性：使用自增主键在插入新数据时，不需要显式指定主键的值，数据库会自动为其生成一个唯一的自增 ID。而使用UUID需要使用工具类生成然后显式插入。

NULL和空字符串””的区别？

• 本质区别：NULL 代表一个未知或不适用的值，而字符串''的是一个具体的值
• 空间占用：NULL 需要占用空间；空字符串''的长度是 0，不占用空间

字段为什么要求定义为 NOT NULL？

占用空间：存储NULL需要占用空间

聚合函数不准确：使用聚合函数的时候会忽略NULL值，例如count(列名)是对表中非NULL的列进行统计

索引性能：由于NULL值可能分布在不同的记录中，索引的选择性可能会降低，从而影响查询的效率。

固定长度的字符串应该什么数据类型存储？

推荐使用CHAR类型，可以节省空间且提高检索效率

CHAR 和 VARCHAR 的区别？

CHAR和VARCHAR是MySQL中常见的字符数据类型，它们之间有以下区别：

• 字符串长度：CHAR是固定长度的字符类型，它会在存储时按照定义的长度进行填充；VARCHAR是可变长度的字符类型，它只会占用实际存储的字符串长度所需的空间。
• 存储空间：CHAR的存储空间始终等于定义的长度乘以每个字符的字节数；VARCHAR的存储空间则取决于实际存储的字符串长度
• 空格处理：当存储CHAR类型的数据时，MySQL会自动在字符串后面填充空格，以保持字段长度的一致性。而VARCHAR类型则不会自动填充空格。
• 查询性能：CHAR类型通常略优于VARCHAR类型，因为CHAR是固定长度的，查询引擎可以更好地优化查询计划。然而，在大多数实际场景中，这种性能差异并不明显。

CHAR(10) 和 VARCHAR(10) 的区别？

CHAR(10) 和 VARCHAR(10) 的区别在于存储方式和占用的存储空间

• CHAR(10) ：如果定义一个CHAR(10)字段并插入一个长度为5的字符串，它将被存储为长度为10的字符串(5个字符+5个空格)
• **VARCHAR(10)**：如果定义一个VARCHAR(10)字段并插入一个长度为5的字符串，它将只占用5个字符的存储空间。

CHAR 和 VARCHAR 如何选择？

选择使用CHAR还是VARCHAR主要取决于长度是否确定：

• 字段长度确定：如果字符串长度是固定的，并且所有值都确保具有相同的长度，可以使用CHAR。比如电话号码等固定长度的数据，可以提高存储效率和查询性能。
• 字段长度不确定：在不能确定字段需要多少字符时，可以使用 VARCHAR。由于VARCHAR只占用实际数据所需的空间，可以避免浪费额外的存储空间。

Text 与 Blob 的区别？

Text和Blob是用于存储大文本数据和二进制数据的数据类型，它们之间的区别如下：

1. 数据类型：Text类型用于存储文本数据，Blob类型用于存储二进制数据，例如图像、音频或视频文件。
2. 存储方式：Text类型存储数据时会进行对字符串的编码操作，通常使用字符集（如UTF-8）来编码文本数据。而Blob类型存储数据时不对其进行编码，直接以二进制形式存储。
3. 查询和检索：Text类型支持文本处理操作，可以使用各种字符串函数和模式匹配进行查询和检索。而Blob类型一般不能直接进行字符串处理操作，需要通过应用程序进行解析和处理。
4. 存储空间：在大多数数据库中，Text类型通常需要占用较多的存储空间，因为它存储的是经过编码的文本数据。而Blob类型通常只占用实际二进制数据所需的存储空间。
5. 索引和排序：Text类型可以使用索引并进行排序，可以在查询时利用索引提高性能。而Blob类型一般不适合建立索引，也无法进行排序操作。

Datetime和Timestamp的区别？

DateTime 类型没有时区信息，Timestamp 和时区有关。

• Datetime：是固定时区的日期和时间类型，它包括年、月、日、时、分、秒和微秒。Datetime表示的时间不会随着时区的改变而改变。在MySQL数据库中，Datetime类型占用8个字节的存储空间。
• Timestamp：也是日期和时间类型，但它与时区无关，它是基于UTC时间（即格林威治标准时间）的。Timestamp存储的是自1970年1月1日以来经过了多少秒的时间戳。在MySQL数据库中，Timestamp类型占用4个字节的存储空间。

什么是数据库约束？

数据库约束用于保证数据库、表数据的完整性（正确性和一致性）

数据库的约束有哪些？

常见的约束有如下几种：

• 主键约束：非空且唯一
• 外键约束：定义表之间的关系
• 默认约束：未指定值则采用默认值
• 非空约束：不能为空
• 唯一约束：不能重复
• 检查约束：检查某个字段的值是否满足某一个条件

事务

数据库的事务是什么?

事务就是将一系列的操作看做一个整体，要么全部执行成功，要么全部失败回滚，确保数据的完整性和一致性。

事务的四大特性（ACID）?

ACID 是指事务的四个关键特性，分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）：

1. 原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部回滚，不允许部分执行。
2. 一致性（Consistency）：事务在执行之前和执行之后，数据库都必须保持一致的状态。
3. 隔离性（Isolation）：多个事务同时执行时，每个事务都应该被隔离开来，互不干扰。一个事务的执行不应该受到其他事务的影响，以此来避免并发执行时可能出现的问题，例如脏读、不可重复读和幻读等。
4. 持久性（Durability）：一旦事务提交成功，对数据库的修改就是永久性的，即使系统发生故障或重启，数据也不会丢失。

事务隔离级别有哪些?

MySQL 支持四种隔离级别：读未提交、读已提交、可重复读和串行化，不同的隔离级别提供了不同程度的数据隔离和并发控制能力，默认隔离级别是可重复读（RR）：

• 读未提交（RC）：允许一个事务读取另一个事务尚未提交的数据。可能会导致脏读（Dirty Read）。
• 读已提交（RC）：保证一个事务只能读取到已经提交的数据。避免了脏读，但可能导致不可重复读（Non-Repeatable Read）。
• 可重复读（RR）：确保事务执行期间多次读取同一数据时，得到的结果都是一致的。可以防止不可重复读，但是可能会出现幻读（Phantom Read）
• 串行化（S）：将所有事务串行执行，确保了强一致性。可以避免脏读、不可重复读和幻读的问题，但也降低了并发性能，可能导致性能下降。

默认隔离级别是什么？

默认隔离级别是可重复读（RR）：确保事务执行期间多次读取同一数据时，得到的结果都是一致的。

隔离级别为什么使用RC不使用RR？

用隔离级别RC（Read Committed）而不是RR（Repeatable Read）可以在一定程度上提高并发性能。

• 死锁概率：在Repeatable Read（RR）隔离级别下，MySQL使用了间隙锁（Gap Lock）来解决幻读问题。间隙锁是在范围的间隙上设置的锁，防止其他事务在此范围内插入新数据。然而，这也增加了死锁的潜在风险。当多个事务同时尝试往同一个范围插入新数据时，可能会发生死锁情况，导致事务无法继续执行。而在Read Committed（RC）隔离级别下，并不使用间隙锁，因此避免了这种死锁风险。
• 锁的颗粒度：在Repeatable Read隔离级别下，当条件列未命中索引时，MySQL会锁住整个表（Table Lock）。这是由于Repeatable Read要求创建一致的快照，以便保证读取的数据一致性。因此，为了防止其他事务修改已读取的数据，MySQL会锁住整个表，对于大型表来说，这可能导致性能问题。而在Read Committed隔离级别下，只锁住涉及到的行，而不是整个表，这减少了锁的粒度，提高了并发性能。

并发事务是什么？

并发事务是指多个事务同时执行的情况，当多个用户或应用程序同时访问和操作数据库时，可能会发生并发事务，常见的并发事务的场景如下：

• 读-读：多个事务同时读取同一个数据，互不干扰，所以不会出现问题
• 读-写：一个事务读取数据，另一个事务更新数据，可能出现脏读、不可重复读、幻读的问题
• 写-写：多个事务同时更新同一个数据，可能出现数据丢失问题

并发事务会带来哪些问题?

事务并发可能会出现：脏读（Dirty read）、不可重复读（Non-repeatable read）、幻读（Phantom read）、数据丢失（Lost update）等问题

• 脏读（Dirty read）：一个事务读取到另一个事务尚未提交的数据，如果另一个事务回滚，则读取到的数据就是是无效或错误的。
• 不可重复读（Non-repeatable read）：在同一个事务内，多次读取同一数据，由于其他事务的修改或删除操作，先后得到的结果不一致。
• 幻读（Phantom read）：一个事务在多次查询同一范围内的数据时，由于其他事务的插入或删除操作，结果集中出现了新的行或者消失了原有的行
• 数据丢失（Lost update）：两个或多个事务修改同一条记录，后提交的事务覆盖了先提交的事务的修改结果，导致覆盖丢失的情况。

并发事务问题怎么解决？

处理事务并发问题，可以采取以下一些策略和技术，需要根据具体场景和需求选择适当的方法：

方式一：使用事务隔离级别

使用事务隔离级别，选择适当的隔离级别来控制事务之间的相互影响。

• 读未提交：不能解决并发问题，可能导致脏读、不可重复读、幻读、数据丢失等问题。
• 读已提交：可以解决脏读的问题，但仍可能出现不可重复读、幻读、数据丢失等问题。
• 可重复读：可以解决脏读和不可重复读的问题，但仍可能出现幻读和数据丢失等问题。
• 串行化：可以解决脏读、不可重复读、幻读和数据丢失等问题，但会牺牲并发性能。

使用事务隔离级别的优缺点：

• 优点：可以灵活地处理并发问题，控制读写操作之间的隔离程度
• 缺点：不同的隔离级别会在一定程度上影响并发性能。

方式二：使用锁机制来保护共享资源

确保在一个事务修改共享资源时，其他事务无法同时访问或修改该资源。

• 共享锁（读锁）：允许多个事务同时读取同一数据，但是不能进行写操作。适用于读多写少的情况，可以提高并发性能。
• 排他锁（写锁）：在一个事务进行写操作时，其他事务不能进行任何读写操作。适用于写多读少的情况，保证数据的一致性。

使用锁机制的优缺点：

• 优点：能够精确控制对数据的访问，保证高并发下的数据一致性和有效性。
• 缺点：锁的粒度过大或者过小都容易导致死锁和饥饿等问题，加锁操作对于 CPU 的负载也是一个不可忽视的问题。

方式三：使用MVCC多版本并发控制

使用MVCC多版本并发控制，为每个事务创建快照，事务读取的是快照而不是实际数据。

• 读取操作：通过为每个事务创建快照，事务读取的是快照而不是实际数据，避免了并发修改导致的读取问题。
• 更新操作：更新操作会生成新版本的数据，不会破坏已经存在的数据版本，旧版本的用户仍然可以读取到上一次提交前的数据。

使用MVCC多版本并发控制的优缺点：

• 优点：读操作不会被写操作所阻塞，提高了并发性能，处理并发时能够降低锁的粒度，避免锁竞争和死锁等问题
• 缺点：需要对于数据版本进行管理，因此会对存储空间带来一定的开销。

什么是当前读与快照读？

当前读（Current Read）和快照读（Snapshot Read）是数据库中的两种读取数据的方式。

• 当前读（锁定读）：读取的是记录数据的最新版本，显式加锁的都是当前读，保证其他事务不能并发的修改数据记录。
• 快照读（普通读）：读取的是记录数据的可见版本（有旧的版本），普通的select语句都是快照读，不需要加锁，因此可以提高并发性能。

MVCC是什么？

MVCC（Multi-Version Concurrency Control）是MySQL中一种多版本并发控制机制。它用于在数据库系统中实现并发访问和事务隔离。具体来说，MVCC允许并发执行的事务访问同一个数据表中的不同版本数据，而不会相互干扰或产生冲突。每个事务在读取数据时，会看到一个一致性的快照，并且不受其他事务的影响。这样可以提高并发性能和事务隔离级别。

• 读取操作：通过为每个事务创建快照，事务读取的是快照而不是实际数据，避免了并发修改导致的读取问题。
• 更新操作：更新操作会生成新版本的数据，不会破坏已经存在的数据版本，旧版本的用户仍然可以读取到上一次提交前的数据。

MVCC的实现原理？

MVCC的实现原理主要涉及以下几个关键点：

• 隐藏字段：在InnoDB存储引擎中，当创建一张表后，每一行数据都会增加额外的隐藏字段，例如事务ID和回滚指针。事务ID用于记录最后一次修改该数据行的事务ID，回滚指针用于指向该数据行的前一个版本。
• Undo Log版本链：MVCC使用Undo Log来实现数据行的版本管理。当事务对数据行进行修改时，不会直接在原始数据行上进行更新，而是将旧版本的数据保存到Undo Log中。Undo Log中的版本形成了一个链表，通过回滚指针连接。
• Read View读视图：每个事务在开始时会创建一个Read View，作为其读取数据的视图。Read View包含了事务开始时已提交的事务ID列表。当事务执行读取操作时，它会根据自己的事务ID与数据行的版本号进行比较，判断数据行是否对当前事务可见。
• 可见性判断：如果数据行的版本号小于等于当前事务的事务ID，则表示该数据行是可见的。事务可以读取这个版本的数据，因为它是在事务开始之前创建的。如果数据行的版本号大于当前事务的事务ID，则表示该数据行是不可见的。这意味着该版本是在事务开始之后创建的，所以事务不能读取这个版本的数据。如果数据行的版本号在当前事务的事务ID范围内，但不包含在事务的读视图（Read View）中的已提交事务列表中，则表示该版本已被其他事务修改。事务需要查找Undo Log中的旧版本数据来保证读取的一致性。如果数据行的版本号在当前事务的事务ID范围内，并且在事务的读视图中的已提交事务列表中，则表示该版本是可见的。事务可以读取这个版本的数据。
• 事务的提交和回滚：当事务提交时，它所做的修改操作将被应用到数据库中，并且新版本的数据行将变为当前版本。如果事务回滚，则将撤销事务所做的修改，并将数据行恢复到之前的版本。

RC和RR隔离级别MVCC有什么不同？

在MySQL中，当事务进行快照读（SELECT）的时候，都会生成一个记录快照（Read View），用来做事务的可见性判断

• 读已提交（RC）隔离级别：事务中每次进行快照读（SELECT）时，都会创建一个新的记录快照（Read View）。这意味着每个快照读都可以看到其他事务已提交的最新数据，但是不能看到其他事务未提交的数据。
• 可重复读（RR）隔离级别：事务中第一次快照读（SELECT）才创建记录快照（Read View），并在整个事务期间复用这个记录快照（Read View）。保证了事务期间对同一数据的多次快照读结果是一致的，即可重复读。

锁

MySQL 数据库的锁有哪些？

按照实现方式划分，可以分为乐观锁和悲观锁

• 乐观锁：认为并发环境下数据不一定会被并发修改，因此只在提交时判断数据是否发生了变化
• 悲观锁：认为数据在并发环境下总是会被修改，因此需要先加锁再操作数据

按照按颗粒度划分，可以分为全局锁、表级锁、行级锁

• 全局锁：锁定数整个数据库
• 表级锁：每次操作锁住整张表
• 行级锁：每次操作锁住对应的行数据或行间隙

按类型划分，可以分为共享锁（读锁）和排它锁（写锁）

• 共享锁（读锁）：用于保护读操作，多个事务都可以共享同一个锁
• 排它锁（写锁）：用于保护写操作，同一时间只能有一个事务持有锁

乐观锁和悲观锁是什么？

乐观锁和悲观锁是并发控制机制中的两种不同策略，用于处理多个事务同时访问和修改相同数据时可能引发的数据不一致性问题。

• 乐观锁：乐观锁认为并发环境下数据不一定会被并发修改，不需要借助数据库的锁机制，每次去拿数据的时候不会上锁，因此只在提交时判断数据是否发生了变化，如果没被修改过，说明只有自己操作过，正常去修改数据，如果被修改过，则不对数据进行修改，选择放弃、报错重试等策略
• 悲观锁：认为数据在并发环境下总是会被修改，因此需要先加锁再操作数据，对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，会对该数据进行加锁，拿到锁才能进行操作，否则需要等待释放锁，直到拿到锁才能进行操作

乐观锁和悲观锁怎么实现？

乐观锁的实现方式：

1. 版本号机制：在表中添加一个版本号列，每次更新操作都会对版本号进行递增或修改。在更新时，比较当前版本号与最初读取时的版本号是否一致，如果一致则可以执行更新，否则表示有冲突。
2. 时间戳机制：在表中添加一个时间戳列，记录数据的最后修改时间。在更新时，比较当前时间戳与最初读取时的时间戳是否一致，如果一致就可以执行更新，否则表示有冲突。
3. CAS（Compare and Swap）机制：某些数据库和编程语言提供了原子操作的支持，如MySQL的SELECT ... FOR UPDATE语句和Java的AtomicReference类，可以使用CAS操作来实现乐观锁。

悲观锁的实现方式：

1. 数据库锁机制：通过数据库提供的锁机制实现，如行级锁、表级锁或页级锁等，对需要修改的数据先加锁，其他事务无法修改该数据，直到释放锁。
2. 应用锁：在应用层代码中使用锁机制来控制并发访问，例如使用Java中的synchronized关键字或分布式锁工具（如Redis的分布式锁）。
3. 锁表：直接对整个表进行加锁，确保任何事务在操作表时都无法进行并发修改。

共享锁和排它锁是什么？

共享锁（Shared Lock）和排它锁（Exclusive Lock）是数据库中常用的两种锁类型，用于控制并发事务对数据的访问。

• 共享锁：共享锁又称为读锁，如果加了读锁，当前事务可以读，但不能写，其他事物可以读，但写入会阻塞
• 排它锁：排它锁又称为写锁，如果加了写锁，当前事务可读可写，其他事务不能读写

全局锁是什么？

全局锁是对整个 MySQL 数据库实例加锁，添加全局锁后，数据库处于只读状态，增删改操作会处于阻塞状态，防止其他客户端对数据库中任何表进行修改操作，一般用于备份数据库或者调整数据库参数等场景。

表级锁是什么？

表锁是悲观锁机制中的一种实现方式，用于控制对整个表的并发访问。在表锁中，可以包括表级共享锁、表级排它锁、元数据锁和意向锁等不同类型。

1. 表级共享锁（Table-level Shared Lock）： 表级共享锁允许多个事务同时对表进行读操作，但不允许任何事务进行写操作。也就是说，多个事务可以并发地共享读取表的数据，互不干扰。
2. 表级排它锁（Table-level Exclusive Lock）： 表级排它锁在某个事务对表进行写操作时，会阻塞其他事务对同一表的读写操作。也就是说，在有事务持有表级排它锁的情况下，其他事务无法对该表进行读取或写入操作，直到持有排它锁的事务释放锁。
3. 元数据锁（Metadata Lock）： 元数据锁用于保护数据库对象的元数据（如表结构、索引等）不被修改或删除。当一个事务正在对数据库对象进行修改时，会持有元数据锁，这样其他事务就无法对该对象进行同类的修改操作，以避免冲突。
4. 意向锁（Intent Lock）： 意向锁是表级锁的辅助锁，用于表示事务对表的意向操作。当一个事务请求对表加锁时，会先请求意向锁。意向锁分为意向共享锁和意向排它锁两种，用于协调不同事务对同一表的并发访问。

表级锁有什么特点？

表级锁是对某个表加锁，防止其他客户端对该表进行修改操作有以下特点：

1. 表锁定范围：对整个表进行锁定，意味着其他事务无法对该表进行读取或写入操作。可以确保数据的一致性和完整性，但也限制了其他并发访问的能力。
2. 锁粒度较大：表级锁是对整个表进行锁定，而不是对单个行或记录进行锁定。这意味着当一个事务持有表级锁时，其他事务无法对表的任何部分进行读取或写入操作，限制了并发访问的能力。
3. 锁开销较小：相比于行级锁，表级锁的管理开销较小。因为只需要维护少量的锁对象，减少了锁的粒度和数量，从而降低了锁开销。
4. 锁冲突少：由于表级锁的粒度较大，不同事务之间的冲突较少。只有当两个事务都需要修改同一张表时才会发生冲突。相比于行级锁，表级锁对冲突的潜在可能性较小。
5. 并发性较低：由于表级锁的粒度较大，当多个事务同时访问同一张表时，会出现阻塞和竞争的情况。当一个事务持有表级排它锁时，其他事务无法读取或修改表中的任何数据，直到排它锁被释放。这降低了并发性能。
6. InnoDB支持：InnoDB 支持表锁和行锁，MyISAM 仅支持表锁，不支持行锁

元数据锁是什么？

元数据锁用于保护数据库对象的元数据（如表结构、索引等）不被修改或删除。当一个事务正在对数据库对象进行修改时，会持有元数据锁，这样其他事务就无法对该对象进行同类的修改操作，以避免冲突。

意向锁（Intent Lock）

意向锁是表级锁的辅助锁，用于表示事务对表的意向操作。当一个事务请求对表加锁时，会先请求意向锁。意向锁分为意向共享锁和意向排它锁两种，用于协调不同事务对同一表的并发访问。

行级锁是什么？

行锁是悲观锁机制中的一种实现方式，用于控制对数据库表中行级数据的并发访问。在行锁中，包括了行级共享锁、行级排它锁、插入意向锁、记录锁、间隙锁和临键锁等不同类型。

1. 行级共享锁（Row-level Shared Lock）： 行级共享锁允许多个事务同时对一行数据进行读操作，但不允许进行写操作。多个事务可以并发地共享读取同一行的数据，互不干扰。
2. 行级排它锁（Row-level Exclusive Lock）： 行级排它锁在某个事务对一行数据进行写操作时，会阻塞其他事务对同一行的读写操作。也就是说，在有事务持有行级排它锁的情况下，其他事务无法对该行进行读取或写入操作，直到持有排它锁的事务释放锁。
3. 插入意向锁（Insert Intention Lock）： 插入意向锁是为了在同一时间内防止多个事务同时往一个页中插入行而引起的问题。当一个事务要往表中插入一行时，会请求插入意向锁。意向锁分为插入意向共享锁和插入意向排它锁，用于协调事务之间的并发插入操作。
4. 记录锁（Record Lock）： 记录锁是对单个记录或行进行的锁定操作，用于保护事务对行的读取或修改操作。当一个事务请求对某行记录加锁时，会获得记录锁，其他事务在此期间无法对该行记录进行写操作。
5. 间隙锁（Gap Lock）： 间隙锁是指在索引上的一个范围（间隙）上设置的锁，用于防止其他事务在这个范围内插入新的记录。可以防止幻读问题的发生。
6. 临键锁（Next-Key Lock）： 临键锁是一种结合记录锁和间隙锁的锁机制，主要用于解决幻读问题。当事务需要读取范围内的数据时，会对范围内的每个记录加上临键锁，确保其他事务无法插入新的记录或修改已有记录。

行级锁有什么特点？

行锁也称为记录锁，只针对数据表中的一行数据加锁，是最小粒度的锁，有以下特点：

1. 行锁定范围：只针对表中的单行或记录进行锁定，其他事务可以并发地访问表中的其他行数据。这提供了更高的并发性能，但也需要更细粒度的锁管理。
2. 锁粒度较小：行级锁是对表中的单行或记录进行锁定，而不是对整个表进行锁定。这意味着当一个事务持有行级锁时，其他事务仍然可以并发地访问表中的其他行数据，提高了并发性能。
3. 锁开销较大：相比于表级锁，行级锁的管理开销较大。需要为每一行数据维护锁状态和锁信息，增加了锁的粒度和数量，从而增加了锁管理的开销。
4. 锁冲突较多：由于行级锁的粒度较小，不同事务之间的冲突较多。当多个事务需要修改同一行数据时，会发生锁冲突，其中一些事务需要等待其他事务释放锁才能继续执行。
5. 并发性较高：由于行级锁的粒度较小，当多个事务同时访问同一张表时，它们可以同时对不同行进行读取或写入操作，减少了阻塞和竞争的情况，提高了并发性能。
6. InnoDB支持：InnoDB 支持表锁和行锁，MyISAM 仅支持表锁，不支持行锁

行锁是如何实现的？

在 MySQL 中，InnoDB 支持表锁和行锁，MyISAM 仅支持表锁，不支持行锁。

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

如果在数据表中没有适当的索引，MySQL 在实现行锁时会使用基于表的锁来代替基于索引的行级锁。基于表的锁相比基于索引的行级锁需要更多的计算资源和时间。

插入意向锁是什么？

插入意向锁是为了在同一时间内防止多个事务同时往一个页中插入行而引起的问题。当一个事务要往表中插入一行时，会请求插入意向锁。意向锁分为插入意向共享锁和插入意向排它锁，用于协调事务之间的并发插入操作。

自增锁是什么？

AUTO_INCREMENT是一种用于创建自动增长数字主键列的机制，如果表中的主键列被设置为AUTO_INCREMENT，每当插入一个新记录时，MySQL会自动为该列分配一个比前一个最大值大1的数值。在多用户并发访问的情况下，使用AUTO_INCREMENT可能会导致锁竞争问题。为了避免这种情况，MySQL引入了自增锁机制，可以保证在同一个事务内插入的记录的AUTO_INCREMENT列的值是连续的。当一个事务进行插入操作时，MySQL会自动获取一个自增锁，任何其他尝试插入带有自增主键的表的操作都必须等待该锁被释放。自增锁只有在原始事务提交或回滚后，才会被释放

记录锁、间隙锁和临键锁是什么？

记录锁、间隙锁和临键锁是数据库中用于并发事务控制的不同锁机制

1. 记录锁（Record Lock）： 记录锁是对单个记录或行进行的锁定操作，用于保护事务对行的读取或修改操作。当一个事务请求对某行记录加锁时，会获得记录锁，其他事务在此期间无法对该行记录进行写操作。
2. 间隙锁（Gap Lock）： 间隙锁是指在索引上的一个范围（间隙）上设置的锁，用于防止其他事务在这个范围内插入新的记录。可以防止幻读问题的发生。
3. 临键锁（Next-Key Lock）： 临键锁是一种结合记录锁和间隙锁的锁机制，主要用于解决幻读问题。当事务需要读取范围内的数据时，会对范围内的每个记录加上临键锁，确保其他事务无法插入新的记录或修改已有记录。

记录锁、间隙锁和临键锁的区别？

记录锁、间隙锁和临键锁是数据库中用于并发事务控制的不同锁机制

• 记录锁（Record Lock）：锁定一个单行记录，其他事务无法修改或插入该行记录，用于保护这个记录的读写操作。
• 间隙锁（Gap Lock）：锁定范围区间，不包含记录本身，用于防止并发事务在该范围内插入数据或更新已存在的数据。
• 临键锁（Next-Key Lock）：临键锁是记录锁和间隙锁的结合形式，既锁定了记录，又锁定了记录之前的间隙，用于避免幻读问题。

临键锁是如何防止幻读的发生？

默认情况下，InnoDB工作在可重复读（RR）隔离级别下，并且会以临键锁（Next-Key Lock）的方式对数据行进行加锁，以防止幻读的发生。

1. 当一个事务执行查询时，在查询过程中，InnoDB会为满足查询条件的索引记录加上行锁，以防止其他事务修改或删除这些记录。
2. 同时，InnoDB还会对查询的索引记录两边的间隙加上间隙锁，以防止其他事务无法在这个范围内插入新的记录，避免了新插入记录导致幻读的问题。

表锁与行锁的区别？

在MySQL中，表锁（Table Lock）和行锁（Row Lock）是用于并发控制的两种不同的锁机制。

• 表锁：行锁定范围，表锁定范围，锁粒度较大，锁开销较小，锁冲突少，并发性较低。InnoDB 支持表锁和行锁，MyISAM 仅支持表锁，不支持行锁
• 行锁：行锁定范围，锁粒度较小，锁开销较大、锁冲突较多，并发性较高。InnoDB 支持表锁和行锁，MyISAM 仅支持表锁，不支持行锁

死锁是什么？

死锁是指两个或多个事务相互等待对方释放资源而陷入无限等待的状态，从而导致数据库无法操作

锁等待是什么？

锁等待是指当一个事务获取了锁并开始执行时，其他事务需要等待上一个事务释放它的锁，才能占用相应的资源

在MySQL中，innodb_lock_wait_timeout参数用于控制InnoDB存储引擎获取锁的等待超时时间，单位是秒，默认值为50秒。当一个事务等待获取锁的时间超过了该参数指定的值，系统会自动回滚该事务，以避免长时间的锁等待可能导致的性能问题和数据不一致性。可以根据具体的业务需求合理地调整事务的等待超时时间。

• 较小的超时时间：可以增加锁冲突的风险，因为如果一个事务等待获取锁的时间超过了超时时间，那么它将被回滚并释放锁资源，这样可能会导致其他事务需要等待更长的时间才能获取锁。但是，较小的超时时间能够更快地释放锁资源，适用于高并发场景，可以减少长时间的锁等待，提高系统的并发性能。
• 较大的超时时间：可以减少锁冲突，因为事务有更长的时间来等待获取锁，这样可以降低其他事务发生锁等待的概率。较大的超时时间适用于对数据一致性要求较高的场景，因为事务有更多的时间来等待其他事务释放锁，这样可以避免数据不一致的可能性。

架构

MySQL的逻辑架构？

• 第一层：处理客户端连接、授权认证，安全校验等。（连接器）
• 第二层：服务器server层，负责对SQL解析、优化、执行操作引擎等。（查询缓存、分析器、优化器、执行器）
• 第三层：存储引擎，负责MySQL中数据的存储和提取。（存储引擎）

说说重要组件？

MySQL的逻辑架构主要由以下几个组件组成：

连接器： 身份认证和权限相关(登录 MySQL 的时候)。

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

分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

优化器： 按照 MySQL 认为最优的方案去执行。

执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。

插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎

SQL语句执行过程？

执行顺序：连接器→查询缓存→解析器→优化器→执行器→存储引擎

1. 连接器建立连接：当客户端请求连接时，连接器会进行身份验证，并建立与服务器的连接。
2. 查询缓存：如果查询被缓存，那么直接返回缓存结果集，避免了后面的解析、优化和执行步骤。
3. 解析器生成执行计划：如果查询没有被缓存或者缓存被清空，那么解析器会对查询进行语法和语义分析，确定查询要执行的操作和涉及的表和列，根据表的结构信息生成一个初始的执行计划，包含哪些表需要连接、如何连接、使用哪些索引等。
4. 优化器优化执行计划：执行计划是根据查询语句生成的，可能并不是最优的执行方式，优化器的目标是找到最优的执行计划，并生成一个优化后的执行计划。
5. 执行器执行执行计划：执行器根据优化器生成的执行计划，调用存储引擎对数据进行操作。
6. 访问存储引擎返回结果：MySQL的存储引擎负责实际的数据存储和访问，根据执行计划，存储引擎会读取所需的数据块，并返回给MySQL服务器，最后MySQL将执行结果返回给客户端

什么是存储引擎？

在MySQL中，存储引擎是负责数据存储和检索的组件，具有以下特点：

• 基于表：存储引擎是基于表级别的，而不是基于整个数据库。
• 可插拔：存储引擎采用插拔式，不同的存储引擎具有不同的特性和功能，可以根据不同的需求和场景进行更换。

常见的存储引擎有哪些？

最常见的存储引擎如下：

• InnoDB：支持事务，提供表级锁定、行级锁定和外键约束等功能
• MyISAM：不支持事务和外键约束，提供了表级锁定。
• Memory：将数据存储在内存中，访问速度较快，但系统奔溃数据会丢失，适用于需要快速读写的临时表或缓存。
• CSV：将数据存储为逗号分隔的值（CSV）格式文件，适用于导入/导出数据。

InnoDB的逻辑存储结构？

InnoDB存储引擎的逻辑存储结构如下

• 表空间（多个段组成）：表空间是由多个段组成的逻辑存储单位，每个数据库至少有一个表空间，用于存储表和索引的数据
• 段（数据段、索引段、回滚段等）：用于组织和存储数据，一个段由多个区组成。
• 区（每个区大小为1M）：InnoDB存储引擎管理磁盘空间的基本单元。
• 页（每个页大小默认为16KB）：数据和索引都以页的形式存储在磁盘上，并且按需从磁盘加载到内存中进行访问。
• 行（存储了表的字段和两个隐藏字段）：行是表中的一个记录，也称为记录或元组。每一行存储了表的字段值以及两个隐藏字段，包括事务ID和回滚指针。

InnoDB与MyISAM的区别？

InnoDB和MyISAM是MySQL中两种不同的存储引擎，在 MySQL 5.5 版本之前，MySQL 的默认存储引擎是 MyISAM，从 MySQL 5.5 版本开始，MySQL 的默认存储引擎变为了 InnoDB，他们主要的区别如下：

• 事务支持：InnoDB支持事务；MyISAM不支持事务。
• 锁定级别：InnoDB支持行级锁定和表级锁定，颗粒度更细，可以在并发访问时提供更好的性能和并发控制；MyISAM仅支持表级锁定，颗粒度更粗，可能会导致并发访问性能下降。
• 外键支持：InnoDB支持外键约束；MyISAM不支持外键约束。
• 索引方式：InnoDB支持聚集索引和非聚集索引；而MyISAM仅支持非聚簇索引。
• 磁盘文件：InnoDB的磁盘文件包括*.frm和*.ibd两个文件，其中*.frm文件存放表的结构定义信息，*.ibd文件存放表中的数据和索引信息。而MyISAM的磁盘文件包括*.frm、*.MYD和*.MYI三个文件，其中*.frm文件存放表的结构定义信息，*.MYD文件存放表中的数据，*.MYI文件存放表的索引信息。
• 使用场景：如果应用需要支持事务，保证数据的完整性和一致性，需要使用InnoDB；如果应用主要以读操作为主，对事务支持没有严格要求，可以考虑选择MyISAM；

InnoDB一颗B+树可以存放多少数据？

在InnoDB中，一颗B+树可以存放多少行数据是由多个因素决定的，包括页大小，数据行大小以及树的层级等，一个三层的B+树大约可存2200万条数据。

• 页大小：在InnoDB的B+树结构中，数据是以页（Page）为单位进行存储和管理的，每个页的默认大小为16KB（16 * 1024字节）。
• 数据行大小：每行数据的大小取决于所存储的列以及其数据类型，不同的列会占用不同的空间。假设一条数据占 1 kb 的空间，那么一个页可以存放 16 条这样的数据。

每个B+树节点都对应一个页（page），这些页通常被称为数据页或索引页，它们存储了数据库中的索引和数据。

• B+树叶子节点：每个叶子节点对应一个数据页，用来存放实际的数据行。假设一条数据占 1 kb 的空间，那么一个叶子节点可以存放 16 条这样的数据。
• B+树非叶子节点：每个非叶子节点都对应一个索引页，用来存放索引信息，由指向下一层地址的指针和主键值组成。指针大小在InnoDB源码中设置为6字节，如果主键是bigint类型，会占8个字节。

根据上述信息，可以计算出一个非叶子节点（索引页）中可以存储的索引信息（主键+指针）数量：(16 * 1024) / (6 + 8) ≈ 1170，就是说一个非叶子节点（索引页）中可以存放大约1170个指向下一层地址的指针，下一层大约有1170个叶子节点（数据页）。

综合上述信息，可以得出不同B+树层级的数据存储情况：

• 如果B+树有1层，那么根节点就是叶子节点（数据页），最多能存放 16 条记录，大约占用16 KB空间；
• 如果B+树有2层，那么第一层是非叶子节点（索引页），指向下一层地址的指针有1170个，第二层都是叶子节点（数据页）会有1170个，最多能存放 1170 × 16 = 18720 条记录，大约占用18.72 MB空间；
• 如果B+树有3层，那么第二层是非叶子节点（索引页），指向下一层地址的指针有1170 x 1170个，第三层都是叶子节点（数据页）会有1170 x 1170个，最多能存放 1170 x 1170 x 16 = 21902400 条记录，大约占用21.9 GB空间；

MySQL中常见的日志有哪些？

在MySQL中，常见的日志包括以下几种：

1. 错误日志（Error Log）：记录MySQL服务器启动和运行过程中的错误和警告信息。
2. 查询日志（Query Log）：记录所有客户端发起的查询语句，包括SELECT、INSERT、UPDATE、DELETE等操作。
3. 慢查询日志（Slow Query Log）：记录执行时间超过设定阈值的查询语句，用于分析和优化性能低下的查询。
4. 回滚日志（Undo Log）：undo log 记录了对数据进行修改操作的逆操作，主要用于实现事务的回滚和MVCC（多版本并发控制）。当事务执行过程中需要进行数据修改操作时，除了将修改写入数据文件，还会先将原始数据的副本写入 undo log 中。如果事务回滚或者其他需要还原数据的操作发生，可以利用 undo log 中的信息来撤销或还原事务所做的修改。
5. 重写日志（Redo Log）：redo log 是用于持久化数据修改操作的日志，主要用于崩溃恢复。当事务提交时，相关的数据修改操作被先记录在 redo log 中，然后再由后台线程将这些操作应用到磁盘上的数据文件中。在发生故障时，通过 redo log 可以重新执行未能完成的事务，从而达到数据的一致性和完整性。
6. 二进制日志（Binary Log）：记录数据库的更改操作，包括数据修改语句和表结构的变更，用于数据恢复、主从复制等场景。
7. 事务日志（Transaction Log）：记录每个事务的开始、提交和回滚等操作，在崩溃恢复和故障保护方面起到重要作用。
8. 中继日志（Relay Log）：在主从复制中，从服务器上记录主服务器上二进制日志的内容，用于实现数据同步。

慢查询日志有什么用？

用来记录在 MySQL 中执行时间超过指定时间的查询语句的日志，通常用于分析性能问题。通过慢查询日志，可以查找出哪些查询语句的执行时间较长，从而找到性能瓶颈所在。针对这些慢查询语句，可以进行优化操作

Undo Log有什么用？

回滚日志（Undo Log）记录了对数据进行修改操作的逆操作，主要用于实现事务的回滚和MVCC（多版本并发控制）。当事务执行过程中需要进行数据修改操作时，除了将修改写入数据文件，还会先将原始数据的副本写入 undo log 中。如果事务回滚或者其他需要还原数据的操作发生，可以利用 undo log 中的信息来撤销或还原事务所做的修改。

Redo Log有什么用？

重写日志（Redo Log）是用于持久化数据修改操作的日志，主要用于崩溃恢复。当事务提交时，相关的数据修改操作被先记录在 redo log 中，然后再由后台线程将这些操作应用到磁盘上的数据文件中。在发生故障时，通过 redo log 可以重新执行未能完成的事务，从而达到数据的一致性和完整性。

Binary Log有什么用？

二进制日志（Binary Log）记录了所有的 DDL语句和 DML语句，但不包括数据查询（SELECT、SHOW）语句。二进制日志常用于数据恢复、主从复制等场景。

• 数据修改记录：Binary Log 记录了数据库中的数据修改操作，包括插入、更新和删除等。每个数据修改操作都会被记录为一个事件（Event），并以二进制格式存储在 Binary Log 文件中。
• 数据恢复：Binary Log 可以记录数据库中所有的数据修改操作，包括插入、更新和删除等。通过定期备份 Binary Log，可以实现对数据库的增量备份，当数据库出现故障或数据丢失时，可以使用备份的 Binary Log 进行数据恢复，减少数据损失。
• 数据复制，Binary Log 是实现主从复制的核心组件。主服务器将所有的数据修改操作记录到 Binary Log 中，并将其发送给从服务器。从服务器通过解析和应用 Binary Log 中的数据修改操作，实现与主服务器的数据同步，达到数据复制和冗余的目的。

Binary Log和Redo Log有什么区别？

Binary Log是MySQL级别的日志，也就是说所有的存储引擎都会产生bin log，而redo log或者undo log事务日志只有innoDB存储引擎才有。

索引

什么是索引？

在MySQL中，索引是建立某个或多个列上的数据结构，用于提高数据库表的查询性能。它类似于书籍的目录，可以快速定位到特定数据的位置，减少了数据库的扫描和搜索工作。

索引在查询中起到什么作用？

索引在查询中起到提高查询速度和减少磁盘IO次数的作用。数据是存储在磁盘上的，在查询时有索引和没索引的区别如下：

• 没有索引的查询：查询数据需要进行将整个表的数据加载到内存中，然后逐行进行检索和读取。这样会导致磁盘IO次数较多，查询速度较慢。
• 有索引后的查询：查询数据可以利用索引的数据结构（如B+树）来快速定位到符合查询条件的数据行，而不再需要加载所有数据，大大减少了磁盘IO次数，从而提高了查询速度。

索引的优缺点？

MySQL索引的优点包括：

1. 提高查询性能：索引可以加快数据库的查询速度，特别是对于大型表和复杂查询，使用合适的索引可以显著提高查询效率。
2. 加速分组和排序：在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
3. 加速连接：在进行表之间的连接操作时，使用索引可以加快连接的速度，减少查询时间。
4. 减少IO操作：索引可以帮助数据库引擎更快地定位需要查询的数据，从而减少了对硬盘的IO操作次数。
5. 保证数据的唯一性：通过在列上创建唯一索引，可以确保数据的唯一性，避免重复插入相同的值。

索引也有以下缺点：

1. 占用空间：索引需要额外的存储空间，特别是对于大型表和复杂索引，会增加存储成本。
2. 增删改操作的性能下降：当对表进行增、删、改操作时，索引需要随之更新，这会导致写操作的性能下降。
3. 索引维护开销：随着数据的增加和修改，索引需要进行维护，包括重新构建、重新组织等操作，这些维护操作会增加数据库的负载。
4. 索引选择困难：设计合适的索引需要根据具体的查询需求和数据特点进行综合考虑，选错索引可能导致查询性能下降。

索引类型有哪些？

根据索引的字段特性分类

• 普通索引：快速定位特定数据
• 唯一索引：针对于表中主键创建的索引
• 主键索引：索引列的值必须唯一，但允许有空值。
• 全文索引：全文索引用于对文本类型的列进行全文搜索
• 前缀索引：对索引列的前缀进行索引，可以节省索引存储空间
• 空间索引：对空间数据类型的字段建立的索引

根据索引的字段个数分类

• 单值索引：单值索引（也称为单列索引）一个索引只包含单个列，每张表可以创建多个单列索引。
• 联合索引：联合索引（也称为复合索引或多列索引）一个索引包含多个列，每张表可以创建多个联合索引。

根据索引的存储形式分类

• 聚集索引：聚集（簇）索引是基于表中的主键或唯一约束来创建的索引，每张表只能有一个。聚集索引将表的数据按照索引的顺序进行物理排序，数据行的存储顺序与索引的顺序一致。在InnoDB存储引擎中，使用主键索引就是创建了一个聚集索引。
• 非聚集索引：非聚集索引（也称为辅助索引或二级索引）则是基于表中非主键列创建的索引，每张表可以有多个。非聚集索引保存了指向数据行的指针，而不是直接存储数据行本身，数据行的实际存储顺序与索引的顺序可能不一致。在InnoDB存储引擎中，辅助索引就是非聚集索引。

根据数据结构分类

• B-Tree索引：最常见的索引类型，采用B-Tree数据结构进行存储和检索。
• 哈希索引：使用哈希表数据结构存储索引值，并通过哈希算法进行快速的查找。适用于精确匹配查询。
• 全文索引：使用全文索引算法构建索引，支持文本类型的全文搜索。
• 空间索引：采用特定的空间数据结构（如R-Tree、Quadtree等）对地理空间数据进行索引和查询。

什么情况推荐使用索引？

推荐使用索引的情况：

1. 频繁进行查询操作：当表中的数据需要频繁地进行查询时，可以考虑使用索引。索引可以加快查询速度，提高数据库系统的响应性能。
2. 数据量大或表较大：当表中的数据量较大或表本身很大时，使用索引可以显著减少查询的时间复杂度，提高查询效率。
3. 经常被用于连接操作：如果某一列经常被用于连接操作（如JOIN语句），在该列上创建索引可以加快连接操作的执行效率。
4. 主键约束唯一约束：通过在主键或唯一约束列上创建索引，可以确保数据的唯一性和完整性，防止重复数据的插入或更新。
5. 排序字段或分组字段：当需要对某一列进行排序或进行分组操作时，可以考虑在该列上创建索引。索引可以加速排序操作，并提高分组操作的效率。

什么情况不推荐使用索引？

不推荐使用索引的情况：

1. 数据量较小或表结构简单：当数据量较小或表结构简单，并且查询操作并不频繁时，索引可能带来的性能提升相对较小，不值得额外的索引维护开销。
2. 经常进行大批量的插入、更新或删除操作：在进行大量的数据插入、更新或删除操作时，索引会增加额外的维护开销，降低这些操作的执行效率。
3. 列值重复率较高：如果要创建索引的列值重复率非常高，那么索引可能不会对查询性能产生显著影响，甚至可能导致查询变慢。
4. 不稳定的查询模式：如果查询模式频繁变化，每次查询所需的列或条件都不同，使用索引可能对性能产生负面影响，因为索引的选择可能不适用于不同的查询模式。

索引的数据结构？

索引的数据结构常见的就两种，一种是b+树，另一种是哈希索引，默认是采用了b+树的数据结构

1. Hash索引：一种key value键值对结构，可能出现hash冲突，不支持范围查询和排序，不支持模糊查询，只适合在精确条件下的等值查询。
2. B+树索引：全称为 多路平衡查找树 ，B+ 树是InnoDB采用的索引结构，查询性能稳定。

Hash索引和B+树索引的区别？

Hash索引和B+树索引是两种常见的索引结构，它们在实现原理和适用场景上存在一些区别。

• 实现原理：Hash索引使用哈希算法将索引键映射到哈希表中，直接定位到对应数据行。B+树索引使用B+树数据结构，通过比较索引键的大小逐层定位到目标数据行。
• 查询性能：Hash索引只支持等值查询，平均查找时间复杂度为O(1)。B+树索引则适用于各种类型的查询，平均查找时间复杂度为O(log N)。
• 内存占用：Hash索引可能由于哈希冲突导致内存占用较高。B+树结构较为平衡，节点的利用率高，相对而言占用的内存较少。
• 适用场景：Hash索引只支持等值查询，频繁的场景，不支持范围查询和排序。B+树索引适用于各种类型的查询，包括范围查询和排序。

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

MySQL选择B+树的一些重要原因：

1. 减少磁盘 I/O 操作：B-树叶子节点和非叶子节点都会存储数据，要找到具体的数据，需要遍历，磁盘I/O操作更多；B+树只有叶子节点存储数据，要找到具体的数据，只需要扫一遍叶子结点，磁盘I/O操作更少。
2. 顺序访问和范围查询：B树需要在内部节点和叶子节点之间频繁切换，难以实现顺序访问和范围查询；B+树的叶子节点通过链表连接，范围查询只需要遍历一段连续的叶子节点即可完成

索引的失效情况？

索引的失效情况是指在数据库查询中，索引无法有效地加速查询操作，导致性能下降。以下是几种常见的索引失效情况：

• 对索引列使用不等于匹配
• 对索引列使用IS NOT NULL
• 对索引列进行模糊查询like以%开头
• 对索引列进行运算操作
• 对索引列进行函数操作
• 使用运算符OR前后存在非索引的列
• 复合索引违反最左前缀法则

怎么确定索引失效？

确定索引失效可以在查询语句前加上explin命令查看SQL的执行计划，主要关注执行计划中的以下参数：

1. Type：查询类型。如果Type显示为ALL，说明没有使用到索引，而是进行全表扫描，表示索引失效。
2. Key：实际使用的索引列。如果Key为空，或者显示为NULL，表示索引未被使用。
3. key_len：索引使用的字节数。如果key_len很小，可能意味着只使用了部分索引，导致索引失效。
4. Extra：额外的信息。特别关注是否出现Using where、Using join buffer、Using temporary、Using filesort等关键词，这些都可能表示索引失效。

什么是聚集索引和非聚集索引？

聚集索引（Clustered Index）：

• 每张表只能有一个聚集索引。
• 聚集索引是基于表的主键或唯一约束创建的一种索引结构，决定了表中数据行的物理存储顺序。
• 叶子节点存储的是数据行本身，对于使用聚集索引的表，查询时可以通过聚集索引快速定位到所需的数据行。
• 对于频繁基于范围查询或顺序访问的操作性能好，因为相关的数据行存储在物理上相邻的位置。
• 在InnoDB存储引擎中，使用主键索引就是创建了一个聚集索引。

非聚集索引（Non-clustered Index）：

• 每张表可以有多个非聚集索引。
• 非聚集索引是基于表的非主键列或辅助索引创建的索引结构。
• 叶子节点存储的是指向数据行的指针，查询时先根据非聚集索引定位到数据行的位置，然后再通过指针走聚集索引回表查询找到实际的数据行。
• 对于快速定位和查找特定值或范围的查询性能更好，但在进行涉及大量数据行的查询时，可能需要额外的IO操作来获取实际的数据行。
• 在InnoDB存储引擎中，辅助索引就是非聚集索引。

什么是联合索引？

联合索引（Composite Index）是指在数据库表中，通过组合多个字段创建的索引。它是由多列构成的索引，可以包含两个或更多的表列。

为什么使用联合索引？

使用联合索引的主要原因有以下几点：

1. 减少索引数量：通过将多个索引合并成一个联合索引，可以减少索引的数量，降低磁盘空间的消耗，并提高索引的更新和维护效率。
2. 覆盖索引：使用联合索引可以实现覆盖索引，即索引本身包含了查询所需要的所有字段，不需要回表到数据页中查找，从而提高查询性能。

什么是覆盖索引？

覆盖索引是指执行查询时，查询所需的数据可以直接从索引中获取，而无需去访问数据所在的表，覆盖索引不是一种索引结构，而是一种优化手段，用于提高查询性能。

为什么使用覆盖索引？

当使用了覆盖索引时，MySQL可以直接使用索引中的数据满足查询的需求，而无需回表读取实际的数据行。

• 不使用覆盖索引：查询非聚集索引时，先查询到指向数据行的指针，然后再通过指针走聚集索引回表查询找到实际的数据行。
• 使用覆盖索引：查询非聚集索引时，通过索引索引值直接找到要查询字段的值，然后直接走聚集索引查询找到实际的数据行。

什么是回表查询？

回表查询就是在使用非聚集索引查询数据时，在索引层面只能得到部分数据信息（例如主键或行指针），然后需要通过这些信息回到表中，利用聚集索引或堆结构找到对应的完整数据行。

1. 首先，通过非聚集索引找到满足查询条件的行指针或主键值。
2. 根据行指针或主键值回到表中，参照聚集索引或堆结构来获取完整的数据行。
3. 返回查询结果，包括满足查询条件的完整数据行。

什么是最左前缀原则？

最左匹配原则，就是在建立了联合索引的情况下，会优先从最边的索引，往右匹配，当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

为什么要遵循最左匹配?

不能遵循的话，查询就不走索引了，索引失效可能造成回表查询，影响效率。

什么是前缀索引？

前缀索引是一种在数据库中用于优化查询效率的技术，它只对某一列的前缀部分建立索引，而不是对整个列进行索引。

为什么使用前缀索引？

对于较长的列值或者包含大量不同值的列，完整索引可能会占用大量的存储空间和索引构建时间，而前缀索引则是通过仅使用列值的前缀来创建索引，从而减少索引的大小和构建时间，并且可以在一定程度上提高查询的性能。

什么是索引下推？

索引下推是一种数据库查询优化技术，可以在索引层面对查询条件进行处理，减少不必要的数据读取和处理（减少回表操作次数），提高查询性能。

假设我们有一个名为”users”的表，包含100万行数据，表中包含列：id、name、age，在该表上创建了一个索引idx_age（age）。我们想查询年龄大于等于30的用户记录。

SELECT id, name, age FROM users WHERE age >= 30;

没有使用索引下推时的查询过程：

1. 数据库引擎遍历非聚集索引idx_age，找到所有满足条件的索引项（age >= 30）对应的数据行的指针。
2. 对于每个匹配的索引行，数据库引擎需要通过指针回表查找实际的数据行，存放到内存中。
3. 在内存中对每一行数据，检查年龄是否大于等于30，并返回满足条件的记录结果给用户。

使用了索引下推时的查询过程：

1. 数据库引擎遍历非聚集索引idx_age，找到满足条件的索引项（age >= 30）对应的数据行的指针。
2. 对于每个匹配的索引行，数据库引擎需要通过指针回表查找实际的数据行，存放到内存中。
3. 由于已经在索引层面过滤过，无需再对内存中的数据进行条件判断，直接返回满足条件的记录结果给用户。

性能优化

谈谈你对数据库优化的经验？

数据库优化是一个综合性的工作，需要从多个方面进行考虑和改进，以下是一些经验和技巧：

• SQL语句的优化：编写高效的 SQL 语句是数据库优化的关键。
• 索引的优化：索引可以加速查询操作，提高数据库的读取性能。确保常用查询条件的字段上有索引，并避免创建过多的索引，因为索引也会增加写入操作的开销。定期检查和更新索引的统计信息，以保持索引的最佳运行状态。
• 表字段优化：合理设计表的字段，避免过多冗余字段或者不必要的字段。减少字段长度和使用合适的数据类型，以节省存储空间和提高查询速度。根据业务需求和数据特点，对字段进行垂直分割和水平分割，以降低对不必要数据的访问和IO开销。
• 存储引擎优化：不同的存储引擎有不同的特点和适用场景。例如，InnoDB适合处理事务和并发性能，MyISAM适合读取密集型应用。根据具体需求选择合适的存储引擎，以获得更好的性能和可靠性。
• 配置优化：数据库服务器的配置对性能有很大影响。根据硬件资源和应用需求，调整数据库的缓冲区大小、连接数限制、日志级别等参数。合理分配内存和磁盘空间，避免过度分配或不足的情况。
• 硬件优化：数据库服务器的硬件配置可以对性能产生直接影响。增加内存容量可以提升查询性能，减少磁盘IO操作。使用专用的网络设备和存储设备也可以提升数据库的性能和可靠性。

常见的查询优化?

• 避免使用SELECT * 查询：使用 SELECT * 会查询表中的所有列，包括不需要的列，这会增加数据传输量和内存消耗。明确指定需要查询的列可以减少不必要的资源消耗。
• 避免使用不必要的DISTINCT去重：使用 SELECT DISTINCT 去重会增加查询的开销，因为它需要在结果集上执行排序和比较操作。如果不必要，尽量避免使用 DISTINCT，而是通过其他方式进行去重操作。
• 避免使用不必要的ORDER BY排序：如果只关心查询结果的数据，而不需要按特定顺序排列，那么可以删除 ORDER BY 子句，提高查询性能。
• 避免使用多个JOIN联表查询：多个 JOIN 联表查询会引入更多的关联和数据扫描成本。在实际应用中，可以考虑拆分查询进行优化，以减少联接操作的复杂性。
• 避免使用子查询：子查询可能会执行多次，每次执行都会引入额外的开销。通过使用 JOIN 或临时表进行联接，可以将子查询转换为更高效的连接操作，从而提高查询性能。
• 使用INNER JOIN代替LEFT JOIN或RIGHT JOIN：INNER JOIN 只返回匹配的行，而 LEFT JOIN 或 RIGHT JOIN 还会返回左表或右表中未匹配的行。如果不需要这些额外的数据，尽量使用 INNER JOIN，可以减少不必要的数据扫描和连接操作。
• 使用WHERE替换HAVING子句：HAVING 子句用于对分组后的结果进行过滤，而 WHERE 子句用于对原始数据进行过滤。将过滤条件放在 WHERE 子句中可以减少分组的数据量，提高查询效率。
• 使用UNION ALL代替UNION：UNION 操作会对结果集进行去重操作，而 UNION ALL 不会。如果不需要去重，可以使用 UNION ALL 替代 UNION，从而减少排序和比较的开销。
• 使用LIMIT子句限制查询结果：LIMIT 子句允许你限制查询结果的数量。当只需要获取部分查询结果时，通过使用 LIMIT 可以减少数据传输和处理的开销。
• 使用NOT EXISTS替代NOT IN：NOT IN子句和 NOT EXISTS 子句都可以用于子查询，判断某个值是否不存在。但是 NOT EXISTS 更高效，因为它只需要判断是否不存在结果，而不需要检索具体的值。
• 使用EXISTS替代IN：IN 子句和 EXISTS 子句都可以用于子查询，判断某个值是否存在。但是 EXISTS 更高效，因为它只需要判断是否存在结果，而不需要检索具体的值。

常见的插入优化?

• 批量插入：假如需要插入多条数据，可以使用多条 INSERT 语句一次性插入多条数据，减少与数据库的交互次数，提高效率
• 使用事务：假如需要插入多条数据，可以将多个插入操作放在一个事务中，可以控制数据的一致性，同时也可以提高插入效率
• 禁用索引：如果需要插入大量数据，而且这些数据并不需要立即查询，可以在插入前暂时禁用索引，插入完毕后再重新启用索引，可以提高插入效率
• 使用LOAD DATA INFILE导入：如果数据源是外部文件，可以使用LOAD DATA INFILE命令可以高效地将外部文件中的数据导入到表中，这种方式比逐行插入更高效，但文件格式需要符合MySQL的要求。

什么是页分裂与页合并？

页分裂（Page Split）和页合并（Page Merge）是数据库中常见的两种操作，用于管理表的物理存储结构。

• 页分裂：当一个表中需要插入新的行时，如果该表已经没有空闲的页可用于存储新行，则数据库系统会自动创建一个新的页，这个过程就是页分裂
• 页合并：当一个表中已有的行被删除时，如果该页中已有的记录行数量小于页容量的一半，则数据库系统会将该页与邻近的页合并成一个新的页

常见的表设计优化？

• 使用合适的存储引擎：InnoDB支持事务和行级锁定，适用于事务处理；MyISAM适用于读写较少的情况。
• 单表不要包含过多字段：当表的列过多时，会增加查询和写入的复杂度，并且会占用更多的磁盘空间。可以将相关的字段组织在一起，将不常用的字段拆分成其他表，以减少冗余和提高查询效率。
• 禁止存储较大二进制数据：存储较大的二进制数据（如图片、音频等）可能会导致数据库表的膨胀，影响查询和更新的性能。
• 正确选择合适的数据类型：选择合适的数据类型可以减少存储空间的占用并提高查询效率。
• 主键使用自增ID：自增ID保证了主键的唯一性，避免了重复插入数据的情况；由于自增ID在插入数据时会按照顺序递增，插入新数据时会直接在表的末尾添加，不会造成数据的移动和调整（减少了页分裂和页合并），能够提高插入效率

常见的索引优化？

通过合理选择字段添加索引、使用联合索引、避免过多索引以及定期维护和优化索引，可以最大程度地提高数据库的查询性能，并确保索引的有效性和一致性。

1. 频繁访问的字段适合添加索引：通过为频繁使用的字段添加索引，可以加快查询操作的速度。这样可以减少数据库的扫描次数，提高查询效率。
2. 使用联合索引来覆盖查询：在某些情况下，单个索引无法满足复杂的查询需求。使用联合索引可以在一个索引中包含多个列，以便更好地支持复杂的查询操作。联合索引能够通过覆盖查询（Covering Index）的方式，直接从索引中获取所需数据，而不必再去查询表的行数据，从而提高查询性能。
3. 避免过多的索引：过多的索引会使数据库写操作的性能下降，并且会占用更多的存储空间。每个索引都需要维护和更新，因此过多的索引会增加数据库维护的成本。因此，在创建索引时，需要评估每个索引的必要性，并避免创建不必要的索引。
4. 维护索引并避免索引失效：随着数据的插入、更新和删除，索引的数据也会发生变化。为了保证索引的有效性和性能，需要定期维护索引。维护索引可以包括重建索引、重新组织索引等操作。此外，还需要避免使用不符合索引定义的查询，以免触发索引失效。

有个SQL执行很慢怎么优化？

导致SQL执行慢的常见原因有SQL语句问题、索引失效、表数据过多、事务和锁问题：

• SQL语句有问题：查询语句可能存在一些问题，例如写得太复杂、嵌套子查询过多，或者使用了不必要的函数和操作符，导致查询效率低下。可以通过重新设计查询语句，减少数据访问次数和计算量。
• SQL语句导致索引失效：查询语句的条件与索引列不匹配，导致数据库无法有效利用索引来加速查询。可以通过分析查询语句，调整查询条件，优化索引设计或创建新索引来提高查询性能。
• 表数据过多：如果表中的数据量非常庞大，查询的执行时间就可能较长。可以考虑对表进行分区、分片或者使用分布式数据库来处理大量数据，将查询任务分散到多个节点上，以提高查询速度和并发性能。
• 事务和锁问题：并发事务操作可能导致锁竞争，从而影响查询性能。当多个事务同时访问同一数据时，可能会发生锁等待的情况，导致查询执行变慢。可以考虑调整事务隔离级别，减少锁竞争，或者重新设计事务的逻辑，避免长时间占用锁资源。

首先，需要找到这条执行慢的SQL，可以通过慢查询日志定位出执行效率比较低的SQL

然后，进一步分析是什么原因导致的，可以更好地了解问题所在，并采取相应的优化策略。

• 通过 Show Profile 查看SQL执行信息分析 SQL 执行性能
• 通过 EXPLAIN 查看执行计划分析索引是否失效、全表扫描等问题

说一说慢查询日志？

慢查询日志记录了所有执行时间超过指定参数的所有 SQL语句的日志，通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

说一说怎样查看SQL执行信息?

在MySQL中，”Profile”是一个用于性能分析和调优的特殊功能。Profile允许捕获并分析查询语句的执行情况，以便确定潜在的性能问题和瓶颈。

# 查看每一条SQL的耗时基本情况，结果的三个字段：查询的唯一标识符（Query_ID）查询的执行时间（Duration）被执行的查询语句（Query）
show profiles;

说一说执行计划？

每条SQL语句执行之前，都会先计算该SQL语句需要调用的相关资源，再决定该SQL语句是否要最终执行，该行为被称为“执行计划”。执行计划主要用于分析 SQL 语句的执行情况，包括索引的使用情况、表的访问方式、连接操作的顺序等，并不实际执行查询。MySQL 提供了一个内置的查询执行计划分析工具，称为 EXPLAIN。使用EXPLAIN查看 SQL 语句的执行计划，语法如下

explain SQL语句;

主要关注执行计划中的以下参数：

1. Type：查询类型。如果Type显示为ALL，说明没有使用到索引，而是进行全表扫描，表示索引失效。
2. Key：实际使用的索引列。如果Key为空，或者显示为NULL，表示索引未被使用。
3. key_len：索引使用的字节数。如果key_len很小，可能意味着只使用了部分索引，导致索引失效。
4. Extra：额外的信息。特别关注是否出现Using where、Using join buffer、Using temporary、Using filesort等关键词，这些都可能表示索引失效。

超大分页或深度分页如何处理？

准备数据

创建学生表，并在创建时间列添加索引

# 创建学生表，并在创建时间列添加索引
CREATE TABLE `student` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
    `name` varchar(255) DEFAULT NULL COMMENT '姓名',
    `age` int DEFAULT NULL COMMENT '年龄',
    `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB COMMENT='学生表';

使用存储过程往学生表中插入100万条测试数据

# 删除存储过程
DROP PROCEDURE IF EXISTS insertData;

# 设置分隔符为 $$
DELIMITER $$

# 创建插入数据的存储过程
CREATE PROCEDURE insertData()
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i <= 100000 DO
    INSERT INTO student (name, age, create_time) VALUES (CONCAT("name",i), i, now());
    SET i = i + 1; 
  END WHILE;
END $$

# 调用插入数据的存储过程
CALL insertData() $$

深分页问题

MySQL的分页查询语法一般使用LIMIT语句来实现，参数offset指定从第几条记录开始获取数据，参数count指定获取多少条记录。

SELECT * FROM 表名 LIMIT offset, count;

假设student表有100万条数据，当每页10条查询第一页的时候，速度很快

select * from student limit 0, 10;

当翻页过多的时候，就会产生深分页，越往后翻，查询效率会越低

select * from student limit 10000, 10;

LIMIT深分页变慢的原因

LIMIT深分页会变慢的主要原因是在执行查询时，MySQL需要扫描并跳过大量的记录，偏移量（offset）越大，需要扫描并跳过的记录数量就越多

# 会先扫描 offset + count 条记录，然后丢弃掉前offset条记录，返回后count条记录。
SELECT * FROM 表名 LIMIT offset, count;
# 会先扫描10条记录，然后丢弃掉前0条记录，返回后10条记录。
SELECT * FROM student LIMIT 0, 10;
# 会扫描100010条记录，然后丢弃掉前10000条记录，返回后10条记录。
SELECT * FROM student LIMIT 10000, 10;

LIMIT深分页问题解决方案

方案一：使用标签记录法（推荐）

标签记录法是指在每次查询时，记录当前页最后一条数据的标识（比如主键值），下次查询时直接根据这个标识进行查询，避免了大量的扫描弃掉操作。这种方式有一个局限性，需要一种类似连续自增的字段来作为标记。

# 假设上一页最后一条数据的id为last_id
SELECT * FROM student WHERE id < last_id ORDER BY id LIMIT 10;

方案二：使用BETWEEN…AND…范围扫描

很多时候，可以将LIMIT查询转换为已知位置的查询，这样MySQL通过BETWEEN...AND...进行范围扫描，获得到对应的结果。

SELECT * FROM student WHERE id BETWEEN 100000 AND 100010;

方案三：使用子查询优化

当根据非聚簇索引字段进行分页查询时，可以使用子查询来优化，将条件转移到主键索引树，减少回表查询，加快查询效率

# 由于create_time是非聚簇索引，需要先查询出主键ID，再回表查询，通过主键ID查询出所有字段
SELECT * FROM student WHERE create_time > '2022-02-02' LIMIT 100000, 10;
# 使用子查询时，会使用覆盖索引，避免回表查询，加快查询效率。
SELECT * FROM student WHERE id IN (
    SELECT id FROM (
        SELECT id FROM student WHERE create_time > '2022-02-02' LIMIT 100000, 10;
    ) AS stu
);

方案四：使用 INNER JOIN 延迟关联

使用内连接和延迟关联的方式，将条件转移到主键索引树，减少回表操作，加快查询效率。思路跟子查询的优化思路类似

# 由于create_time是非聚簇索引，需要先查询出主键ID，再回表查询，通过主键ID查询出所有字段
SELECT * FROM student WHERE create_time > '2022-02-02' LIMIT 100000, 10;
# 使用 INNER JOIN 延迟关联
SELECT * FROM student INNER JOIN (
    SELECT id FROM student create_time > '2022-02-02' LIMIT 100000, 10;
) AS stu ON student.id = stu.id;

高可用

主从复制是什么？

MySQL主从复制是一种常用的数据库复制技术，通过复制主服务器（Master）上的数据复制到另一个或多个从服务器（Slave）上，实现数据的实时同步和备份。

主从复制的作用？

主从复制在数据库系统中有以下几个主要作用：

• 读写分离：主服务器（Master）负责处理写操作（写入数据），从服务器（Slave）负责处理读操作（查询数据），可以有效分担主服务器的负载，提高系统性能和扩展性。
• 高可用性：从服务器（Slave）作为主服务器的备份，可以在主服务器（Master）发生故障时，提供数据的备份和恢复功能。

主从复制的原理？

主从复制中的每个连接通常会涉及三个线程：

1. Binlog输出线程：在主库上，为每个连接到主库的从库创建一个binlog输出线程。这个线程负责将主库上的数据更改操作记录（binlog）发送给相应的从库。它将binlog事件推送到对应的从库的I/O线程。
2. I/O线程：在从库上，每个连接到主库的从库都有一个独立的I/O线程。这个线程负责与主库建立连接，接收并读取主库上的binlog事件，并将其存储到从库的本地中继日志（relay log）中。
3. SQL线程：在从库上，每个连接到主库的从库都有一个独立的SQL线程。这个线程负责从本地中继日志中读取存储的binlog事件，并将其解析并执行在从库上重放这些事件，从而实现与主库的数据同步。

具体的主从复制过程如下：

1. 主服务器记录二进制日志（Binary Log）：当主服务器接收到客户端的写请求时，会在执行写操作前将该操作记录到二进制日志（Binary Log）。
2. 从服务器尝试连接主服务器：从服务器尝试通过网络连接到主服务器
3. 主服务器接受从服务器连接请求：主服务器接收到从服务器的连接请求，并响应该请求
4. 从服务器请求同步数据：从服务器向主服务器发送一个数据同步请求，请求复制主服务器上的数据。
5. 主服务器发送二进制日志事件：如果主服务器允许该从服务器进行复制，则主服务器将存储在二进制日志中的写操作事件发送给从服务器。
6. 从服务器接收并写入中继日志（Relay log）：从服务器启动一个I/O线程，接收到并解析主服务器发来的二进制日志事件后，会将事件写入自己本地的中继日志（Relay log）。
7. 从服务器读取中继日志（Relay log）并应用：从服务器会创建一个SQL线程，按照从主服务器接收到的二进制日志事件的顺序依次读取中继日志（Relay log）中的事件，并且将这些事件应用到自己的数据库中，实现数据的同步。
8. 从服务器向主服务器发送确认消息：从服务器在完成了一轮数据同步后，会向主服务器发送一个确认消息，表示已经成功完成数据同步。
9. 主服务器和从服务器之间保持心跳连接：为了保证连接的可靠性和稳定性，主服务器和从服务器之间需要不断地保持心跳连接，并且监测连接的状态，如果发现连接异常，则会进行重连。
10. 重复执行复制过程：上述步骤将不断地重复执行，以实现主从服务器之间的数据同步。

主从复制的架构方式?

单主单从架构

单主多从架构

多主单从架构

主-主同步架构

级联复制架构

主从复制有哪些模式？

MySQL主从复制有以下几种常见的模式：

• 异步复制（Asynchronous Replication）：异步复制是MySQL默认的复制方式。主服务在执行完用户提交的事务后，将结果写入 binlog 日志并立即返回给客户端，并不等待从服务接收和处理复制事件。提高主服务的性能，但在主服务宕机而从服务未备份到新的 binlog 时可能会导致数据丢失。
• 半同步复制（Semi-Synchronous Replication）：半同步复制是MySQL主从复制的另一种模式。主服务在执行完事务后，需要至少等待一个从服务确认已经接收并写入复制事件才能返回给客户端。提供了更高的数据一致性和可靠性，但也增加了主服务器的延迟。
• 全同步复制（Synchronous Replication）：全同步复制要求主服务执行完客户端提交的事务后，等待所有从服务都接收并写入relay log后才能返回给客户端。提供了最高的数据一致性和安全性保障，但也会引入更高的延迟和性能开销。

并行复制是什么？

并行复制是MySQL中用于加快主从复制速度的一种机制，允许在从库上同时应用多个来自主库的事务，以减少复制延迟。

• 传统主从复制：在传统主从复制中，从库使用 IO 线程（io_thread）接收二进制日志（Binary Log）并保存到中继日志（Relay log），当主库并行写入压力较大时，因为是顺序写入中继日志（Relay log），所以从库 IO 线程（io_thread）一般不会产生延迟。但是只有单个 SQL 线程（sql_thread）来读取和应用中继日志（Relay log），导致在高并发场景下主库严重延迟。
• 并行复制机制：在采用并行复制机制后，从库的IO线程（io_thread）仍然负责接收主库的二进制日志（Binary log）并保存到中继日志（Relay log），但会将单个SQL线程（sql_thread）演化成多个Worker线程，并行读取和应用中继日志（Relay log）中的事务，充分利用了硬件资源，提高了复制的效率。

GTID复制是什么？

GTID复制是MySQL 5.6版本引入的复制方式，它使用全局唯一的事务标识符（GTID）来追踪和标识主从服务器上的事务。GTID复制简化了配置和管理，可以确保每个事务在主从服务器上只执行一次，并自动处理主从服务器之间的不一致性。

什么是分库分表？

分库：将一个大型数据库拆分成多个小型数据库（分库）

分表：将一个大型表拆分成多个较小的表（分表）

为什么分库分表?

分库的原因如下：

• 提高存储能力：随着业务的发展，数据量会不断增长，单个数据库的存储容量可能无法满足需求。通过将数据拆分到多个数据库中，从而实现更高的存储能力。
• 提高并发能力：随着业务的发展，在高并发的场景下大量请求访问数据库，单个数据库实例可能无法处理这么多的连接，导致性能下降或响应时间延长。通过分库，可以将数据在多个数据库中分散存储，将并发压力均匀分布到多个数据库上，每个数据库负责处理一部分并发请求，提高整个系统的并发处理能力。
• 提高可用性：随着业务的发展，系统的可用性变得尤为重要，当数据集中存储在单个数据库中时，如果该数据库出现故障，整个系统都将不可用。通过将数据分散存储到多个数据库中，即使一个数据库发生故障，其他数据库仍然可以正常运行，确保业务的连续性和可用性。

分表的原因如下：

• 提高查询性能：当一个表的数据量过大时（达到几千万），B+树的高度会增高，数据库需要扫描更多的数据页来查找所需的数据，导致查询速度下降。通过分表，可以将数据分散到多个小表中，每个小表的数据量变小，从而提高查询性能。
• 减少锁冲突：在高并发环境下，当多个用户同时对同一张表进行写操作时，可能会引发锁冲突，降低系统的并发性能。通过分表，可以将数据分散到多个小表中，减少锁冲突的可能性，提高并发性能。
• 方便维护和备份：当数据量较大时，整张表的维护和备份可能会变得困难。通过分表，可以将数据分散到多个小表中，便于对每个小表进行维护和备份。这样可以提高维护和备份的效率，并降低出错的风险。

什么时候分库？

分库的时机主要取决于以下几个因素：

• 单库容量达到上限：MySQL单库容量的上限具体取决于数据库类型和配置，通常是在几十到几百亿条数据之间。当单个数据库的存储容量达到极限，无法再容纳更多数据时，需要考虑进行分库操作。
• 单库并发达到上限：MySQL单库一般可以支持1万到10万的并发读写，当并发量达到2万时，可以考虑分库来分担负载，提高并发处理能力。
• 业务拆分需求：当业务规模扩大或需要在不同地域或数据中心部署时，可以考虑根据业务功能或地理位置等因素进行数据库的拆分。这样可以提高系统的灵活性、可伸缩性和可用性。

什么时候分表？

对于MySQL的InnoDB存储引擎而言，单表最多可以存储10亿级别的数据量。单表数据量在500万左右，性能会处于最佳状态，根据阿里巴巴的《Java开发手册》的建议，当单表行数超过500万行或者单表容量超过2GB时，推荐考虑进行分表操作。

InnoDB存储引擎最小储存单元是页，一页大小就是16k，假设一行记录占用1KB空间，一棵高度为1的B+树能存放16条数据，大约占用16 KB空间；一棵高度为2的B+树能存放18720条数据，大约占用18.72 MB空间；一棵高度为3的B+树能存放21902400条数据，大约占用21.9 GB空间；B+树高度一般为1-3层，如果B+到了4层，查询的时候会增加磁盘交互的次数，SQL就会变慢。

什么时候分库分表？

分库分表方案可以分为下面3种：

• 只分库不分表：单库并发达到上限，但是单表数据量不大；
• 只分表不分库：单表数据量过大，但是单库并发没达到上限；
• 即分库又分表：单库并发达到上限，同时单表数据量过大。

分库分表的策略?

分库和分表都可以从垂直（纵向）和水平（横向）两个维度进行拆分。

• 垂直分库：将不同的业务模块或功能模块的数据存储在不同的数据库中，每个数据库负责一个或多个特定的模块。
• 垂直分表：将一个大表根据字段的关联性进行拆分，每个拆分出来的小表只包含一部分字段。
• 水平分库：将一个数据库中的数据水平划分到多个独立的数据库实例中，每个数据库实例负责一部分数据。
• 水平分表：将一个大表按照行进行拆分，将不同的行分散存储在多个物理表中。

分库分表带来的问题怎么解决？

分布式事务问题

• 问题描述：分库分表导致执行一次事务所需的数据分布在不同服务器上，传统的单库事务无法直接应用。
• 解决思路：使用分布式事务机制来保证事务的原子性和一致性。可以采用两阶段提交（2PC）、补偿事务、Saga模式等。另外，可以利用数据库中间件或分布式事务框架来简化事务管理和实现分布式事务。

分布式ID问题

• 问题描述：在分库分表环境中，由于数据被分散到不同的节点上，生成全局唯一的ID变得更加困难，可能会导致ID冲突或者无法满足全局唯一性的需求。
• 解决思路：使用全局唯一标识符（UUID或GUID）作为分布式ID，或者使用数据库自增ID来生成局部唯一的ID，还可以借助第三方中间件（如Snowflake、Twitter的分布式ID生成器等）来生成全局唯一的ID。

数据同步问题

• 问题描述：分库分表后，数据的一致性和同步变得复杂。当涉及到数据更新、删除或新增时，需要确保所有相关的分片上的数据保持一致。
• 解决思路：使用数据同步机制，如主从复制、发布/订阅模式、异步消息队列等，将数据的变更操作传播到所有的分片中，保证数据的一致性和同步。

数据迁移问题

• 问题描述：当需要对现有的分库分表进行扩容、重构或合并时，需要进行数据迁移，这可能会引发数据一致性问题和系统停机时间长等挑战。
• 解决思路：采用逐步迁移的方式，将部分数据从老的分片迁移到新的分片上。可以使用增量迁移或者全量迁移的策略，并且要做好数据一致性和验证工作。同时，可以利用数据同步技术来减少迁移的停机时间和对业务的影响。

跨节点关联查询问题

• 问题描述：分库分表之后，数据可能分布在不同的节点上，此时使用关联查询（JOIN）跨多个数据库实例或表的数据时，会增加复杂性和性能开销。
• 解决思路：可以分多次查询进行数据组装实现业务，避免使用关联查询（JOIN）。或者在分片中的表中增加冗余字段，避免关联查询（JOIN）用空间换时间。

跨节点分页问题

• 问题描述：在分库分表的环境下，进行跨节点的分页查询需要从多个分片获取数据，并对结果进行合并和排序，增加了网络传输和计算开销。
• 解决思路：使用全局排序键，将排序键作为分片键的一部分，以保证全局有序。可以通过预取数据、并行查询等方式提高分页查询性能。同时，可以采用分页缓存技术，在应用层缓存整个分页数据，减少跨分片查询的次数。

跨节点排序问题

• 问题描述：在分库分表环境中，对全局数据进行排序比较困难，需要合并多个分片的结果集，增加了计算和网络开销。
• 解决思路：使用分布式排序算法，如归并排序、快速排序等。可以将排序操作下推至分片进行局部排序，然后通过合并操作得到全局有序结果。另外，还可以在应用层进行排序，将排序操作由数据库转移到应用程序中。

跨节点函数计算问题

• 问题描述：在分库分表环境中，当需要进行跨节点的函数计算时，可能会面临调用多个节点的函数和结果合并等挑战。
• 解决思路：可以将函数计算下推至分片进行局部计算，然后通过合并操作得到最终的结果。另外，还可以在应用层进行函数计算，将计算操作由数据库转移到应用程序中。