JAVA面试题---SQL基础（MySQL面试题）

SQL基础

SQL和NOSQL的区别？（SQL 通过严格的ACID保证复杂事务与强一致性，适合结构化数据和复杂查询；NoSQL 以灵活的数据模型和水平扩展见长，适合高并发、海量数据及弱一致性场景）

对比维度	SQL（关系型数据库）	NoSQL（非关系型数据库）
数据模型	结构化数据，二维表结构，严格预定义模式（Schema）	灵活结构，支持文档、键值等无固定模式
事务支持	严格 ACID 事务（跨行、跨表）	多数仅支持单文档/单键操作，部分支持弱事务（BASE）
扩展性	垂直扩展(升级单机硬件（CPU/内存/磁盘）)为主，水平扩展复杂（需分库分表）	天生水平扩展(增加服务器节点)，支持分片（Sharding）、副本集
一致性模型	强一致性（ACID）	最终一致性（BASE）或可调一致性
性能优化方向	优化复杂查询、索引设计、事务效率	优化吞吐量、低延迟、海量数据存储
数据关联性	通过外键关联表，支持复杂关系	无关联或通过嵌套文档/冗余存储简化关系
写入/读取优先级	优先保证数据一致性与完整性	优先保证高可用性与高性能
代表产品	MySQL、PostgreSQL、Oracle	MongoDB（文档）、Redis（键值）、Cassandra（列）
典型应用场景	金融系统、ERP、复杂事务场景	高并发读写、实时分析、日志存储、社交网络

数据库三大范式是什么？（1、表的每一列必须是不可再分的原子值，不允许包含集合、数组或复合结构。2、 在满足 1NF 的基础上，所有非主属性必须完全依赖整个候选码（联合主键），而非部分依赖。3、在满足 2NF 的基础上，非主属性不能传递依赖于主键（即非主属性之间无依赖））

• 第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

• 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

  第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
  

• 第三范式（3NF）：在2NF基础上，任何非主属性 (opens new window)不依赖于其它非主属性（在2NF基础上消除传递依赖）
  第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

MySQL 怎么连表查询？（内连接（返回两表匹配行）、左外连接（保留左表所有数据+右表匹配/未匹配部分用NULL填充）、右外连接（保留右表所有数据+左表匹配/未匹配部分用NULL填充）、全外连接（保留两表所有数据，MySQL需用LEFT JOIN和RIGHT JOIN的UNION实现））

数据库有以下几种联表查询类型：

内连接 (INNER JOIN)
左外连接 (LEFT JOIN)
右外连接 (RIGHT JOIN)
全外连接 (FULL JOIN)

1. 内连接 (INNER JOIN)

内连接返回两个表中有匹配关系的行。示例:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

这个查询返回每个员工及其所在的部门名称。

2. 左外连接 (LEFT JOIN)

左外连接返回左表中的所有行，即使在右表中没有匹配的行。未匹配的右表列会包含NULL。示例:

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

这个查询返回所有员工及其部门名称，包括那些没有分配部门的员工。

3. 右外连接 (RIGHT JOIN)

右外连接返回右表中的所有行，即使左表中没有匹配的行。未匹配的左表列会包含NULL。示例:

SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

这个查询返回所有部门及其员工，包括那些没有分配员工的部门。

4. 全外连接 (FULL JOIN)

全外连接返回两个表中所有行，包括非匹配行，在MySQL中，FULL JOIN 需要使用 UNION 来实现，因为 MySQL 不直接支持 FULL JOIN。示例:

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id

UNION

SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

这个查询返回所有员工和所有部门，包括没有匹配行的记录。

MySQL如何避免重复插入数据？（1、UNIQUE约束强制唯一性（报错中断），2、INSERT…ON DUPLICATE KEY UPDATE冲突时更新，3、INSERT IGNORE静默忽略重复）

• 方式一：使用UNIQUE约束
  在表的相关列上添加UNIQUE约束，确保每个值在该列中唯一。例如：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE,
    name VARCHAR(255)
);

如果尝试插入重复的email，MySQL会返回错误。

• 方式二：使用INSERT … ON DUPLICATE KEY UPDATE
  这种语句允许在插入记录时处理重复键的情况。如果插入的记录与现有记录冲突，可以选择更新现有记录：

INSERT INTO users (email, name) 
VALUES ('example@example.com', 'John Doe')
ON DUPLICATE KEY UPDATE name = VALUES(name);

• 方式三：使用INSERT IGNORE： 该语句会在插入记录时忽略那些因重复键而导致的插入错误。例如：

INSERT IGNORE INTO users (email, name) 
VALUES ('example@example.com', 'John Doe');

如果email已经存在，这条插入语句将被忽略而不会返回错误。

选择哪种方法取决于具体的需求：

• 如果需要保证全局唯一性，使用UNIQUE约束是最佳做法。
• 如果需要插入和更新结合可以使用ON DUPLICATE KEY UPDATE。
• 对于快速忽略重复插入，INSERT IGNORE是合适的选择。

CHAR 和 VARCHAR有什么区别？（CHAR定长补空格（适合固定长度数据，如状态码），VARCHAR变长省空间（适合可变数据，如用户输入文本））

varchar后面代表字节数还是字符数？（VARCHAR(n)中n表示字符数（实际字节数由字符集决定，如UTF-8下中文占3字节/字符））

VARCHAR 后面括号里的数字代表的是字符数，而不是字节数。

比如 VARCHAR(10)，这里的 10 表示该字段最多可以存储 10 个字符。字符的字节长度取决于所使用的字符集。

• 如果字符集是 ASCII 字符集：ASCII 字符集每个字符占用 1 个字节，那么 VARCHAR(10) 最多可以存储 10 个 ASCII 字符，同时占用的存储空间最多为 10 个字节（不考虑额外的长度记录开销）。
• 如果字符集是 UTF - 8 字符集，它的每个字符可能占用 1 到 4 个字节，对于 VARCHAR(10) 的字段，它最多可以存储 10 个字符，但占用的字节数会根据字符的不同而变化。

int(1) int(10) 在mysql有什么不同？（INT(1)和INT(10)的括号数值仅控制显示宽度（如配合ZEROFILL时补零长度，例如1显示为0000000001），实际存储范围同为4字节，不影响数据类型和存储空间。）

INT(1) 和 INT(10) 的区别主要在于 显示宽度，而不是存储范围或数据类型本身的大小。以下是核心区别的总结：

本质是显示宽度，不改变存储方式：INT 的存储固定为 4 字节，所有 INT（无论写成 INT(1) 还是 INT(10)）占用的存储空间 均为 4 字节。括号内的数值（如 1 或 10）是显示宽度，用于在 特定场景下 控制数值的展示格式。
唯一作用场景：ZEROFILL 补零显示，当字段设置 ZEROFILL 时：数字显示时会用前导零填充至指定宽度。比如，字段类型为 INT(4) ZEROFILL，实际存入 5 → 显示为 0005，实际存入 12345 → 显示仍为 12345（宽度超限时不截断）。
举一个例子

– 创建一个包含 INT(1) 和 INT(10) 字段的表，并设置

ZEROFILL 属性
CREATE TABLE test_int (
    num1 INT(1) ZEROFILL,
    num2 INT(10) ZEROFILL
);

– 插入数据

INSERT INTO test_int (num1, num2) VALUES (1, 1);

– 查询数据

SELECT * FROM test_int;

结果分析：

num1 字段由于设置为 INT(1) ZEROFILL，其显示宽度为 1，插入数据 1 时会显示为 1。
num2 字段设置为 INT(10) ZEROFILL，显示宽度为 10，插入数据 1 时会在前面填充零，显示为 0000000001。

Text数据类型可以无限大吗？（MySQL的TEXT类型并非无限大：TEXT（最大64KB）、MEDIUMTEXT（最大16MB）、LONGTEXT（最大4GB），均有明确存储上限，具体最大值由类型定义决定）

MySQL 3 种text类型的最大长度如下：
TEXT：65,535 bytes ~64kb
MEDIUMTEXT：16,777,215 bytes ~16Mb
LONGTEXT：4,294,967,295 bytes ~4Gb

说一下外键约束（外键约束通过强制要求表中某字段的值必须存在于另一表的主键中，确保数据间的引用有效性和关联完整性，从而防止无效引用或数据不一致）

外键约束的作用是维护表与表之间的关系，确保数据的完整性和一致性。让我们举一个简单的例子：

假设你有两个表，一个是学生表，另一个是课程表，这两个表之间有一个关系，即一个学生可以选修多门课程，而一门课程也可以被多个学生选修。在这种情况下，我们可以在学生表中定义一个指向课程表的外键，如下所示：

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  course_id INT,
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

这里，students表中的course_id字段是一个外键，它指向courses表中的id字段。这个外键约束确保了每个学生所选的课程在courses表中都存在，从而维护了数据的完整性和一致性。

如果没有定义外键约束，那么就有可能出现学生选了不存在的课程或者删除了一个课程而忘记从学生表中删除选修该课程的学生的情况，这会破坏数据的完整性和一致性。因此，使用外键约束可以帮助我们避免这些问题。

MySQL的关键字in和exists（IN 用于静态值或子查询结果集的成员检查，适合小数据量；EXISTS 通过关联子查询验证存在性，性能更优且不依赖具体值，适合大数据量或需要关联查询的场景）

在MySQL中，IN 和 EXISTS 都是用来处理子查询的关键词，但它们在功能、性能和使用场景上有各自的特点和区别。

IN关键字

IN 用于检查左边的表达式是否存在于右边的列表或子查询的结果集中。如果存在，则IN 返回TRUE，否则返回FALSE。

语法结构：

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

或

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

例子：

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France');

EXISTS关键字

EXISTS 用于判断子查询是否至少能返回一行数据。它不关心子查询返回什么数据，只关心是否有结果。如果子查询有结果，则EXISTS 返回TRUE，否则返回FALSE。

语法结构：

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM another_table WHERE condition);

例子：

SELECT * FROM Customers
WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

区别与选择：

性能差异：在很多情况下，EXISTS 的性能优于 IN，特别是当子查询的表很大时。这是因为EXISTS 一旦找到匹配项就会立即停止查询，而IN可能会扫描整个子查询结果集。
使用场景：如果子查询结果集较小且不频繁变动，IN 可能更直观易懂。而当子查询涉及外部查询的每一行判断，并且子查询的效率较高时，EXISTS 更为合适。
NULL值处理：IN 能够正确处理子查询中包含NULL值的情况，而EXISTS 不受子查询结果中NULL值的影响，因为它关注的是行的存在性，而不是具体值。

mysql中的一些基本函数，你知道哪些？（字符串处理（如CONCAT、SUBSTRING）、数值计算（如ABS、POWER）、日期操作（如NOW、CURDATE）及聚合统计（如COUNT、SUM、AVG））

一、字符串函数

CONCAT(str1, str2, ...)：连接多个字符串，返回一个合并后的字符串。

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
LENGTH(str)：返回字符串的长度（字符数）。

SELECT LENGTH('Hello') AS StringLength;
SUBSTRING(str, pos, len)：从指定位置开始，截取指定长度的子字符串。

SELECT SUBSTRING('Hello World', 1, 5) AS SubStr;
REPLACE(str, from_str, to_str)：将字符串中的某部分替换为另一个字符串。

SELECT REPLACE('Hello World', 'World', 'MySQL') AS ReplacedStr;

二、数值函数

ABS(num)：返回数字的绝对值。

SELECT ABS(-10) AS AbsoluteValue;
POWER(num, exponent)：返回指定数字的指定幂次方。

SELECT POWER(2, 3) AS PowerValue;

三、日期和时间函数

NOW()：返回当前日期和时间。

SELECT NOW() AS CurrentDateTime;
CURDATE()：返回当前日期。

SELECT CURDATE() AS CurrentDate;

四、聚合函数

COUNT(column)：计算指定列中的非NULL值的个数。

SELECT COUNT(*) AS RowCount FROM my_table;
SUM(column)：计算指定列的总和。

SELECT SUM(price) AS TotalPrice FROM orders;
AVG(column)：计算指定列的平均值。

SELECT AVG(price) AS AveragePrice FROM orders;
MAX(column)：返回指定列的最大值。

SELECT MAX(price) AS MaxPrice FROM orders;
MIN(column)：返回指定列的最小值。

SELECT MIN(price) AS MinPrice FROM orders;

SQL查询语句的顺序是怎么样的？

 SELECT 
    [DISTINCT] 列/表达式 
FROM 
    表
    [JOIN 表 ON 条件]
WHERE 
    行过滤条件
GROUP BY 
    分组列
HAVING 
    分组后过滤条件
ORDER BY 
    排序列
LIMIT 
    分页;

SELECT 
    department, 
    COUNT(*) AS emp_count 
FROM 
    employees 
WHERE 
    hire_year > 2020 
GROUP BY 
    department 
HAVING 
    COUNT(*) > 5 
ORDER BY 
    emp_count DESC 
LIMIT 10;

sql题：给学生表、课程成绩表，求不存在01课程但存在02课程的学生的成绩（方法1通过LEFT JOIN + NULL判断排除有01课程的学生并保留02课程记录，方法2则通过JOIN 02课程 + NOT EXISTS子查询确保01课程不存在）

可以使用SQL的子查询和LEFT JOIN或者EXISTS关键字来实现，这里我将展示两种不同的方法来完成这个查询。

假设我们有以下两张表：

Student 表，其中包含学生的sid（学生编号）和其他相关信息。
Score 表，其中包含sid（学生编号），cid（课程编号）和score（分数）。
方法1：使用LEFT JOIN 和 IS NULL

SELECT s.sid, s.sname, sc2.cid, sc2.score
FROM Student s
LEFT JOIN Score AS sc1 ON s.sid = sc1.sid AND sc1.cid = '01'
LEFT JOIN Score AS sc2 ON s.sid = sc2.sid AND sc2.cid = '02'
WHERE sc1.cid IS NULL AND sc2.cid IS NOT NULL;

方法2：使用NOT EXISTS

SELECT s.sid, s.sname, sc.cid, sc.score
FROM Student s
JOIN Score sc ON s.sid = sc.sid AND sc.cid = '02'
WHERE NOT EXISTS (
    SELECT 1 FROM Score sc1 WHERE sc1.sid = s.sid AND sc1.cid = '01'
);

sql题： 给定一个学生表 student_score（stu_id，subject_id，score），查询总分排名在5-10名的学生id及对应的总分（如允许两个第4名后紧跟第6名）

可以使用以下 SQL 查询来检索总分排名在 5 到 10 名的学生 ID 及对应的总分。其中我们先计算每个学生的总分，然后为其分配一个排名，最后检索排名在 5 到 10 之间的记录。

WITH StudentTotalScores AS (
    SELECT 
        stu_id,
        SUM(score) AS total_score
    FROM 
        student_score
    GROUP BY 
        stu_id
),
RankedStudents AS (
    SELECT
        stu_id,
        total_score,
        RANK() OVER (ORDER BY total_score DESC) AS ranking
    FROM
        StudentTotalScores
)
SELECT
    stu_id,
    total_score
FROM
    RankedStudents
WHERE
    ranking BETWEEN 5 AND 10;

解释：

1、子查询 StudentTotalScores 中，我们通过对 student_score 表中的 stu_id 分组来计算每个学生的总分。
2、子查询 RankedStudents 中，我们使用 RANK() 函数为每个学生分配一个排名，按总分从高到低排序。
3、最后，我们在主查询中选择排名在 5 到 10 之间的学生。

sql题：查某个班级下所有学生的选课情况

有三张表：学生信息表、学生选课表、学生班级表

学生信息表（students）结构如下：

CREATE TABLE students (
student_id INT PRIMARY KEY, //学生的唯一标识，主键。
student_name VARCHAR(50), //学生姓名。
class_id INT //学生所属班级的标识，用于关联班级表。
);
学生选课表（course_selections）结构如下：

CREATE TABLE course_selections (
selection_id INT PRIMARY KEY, //选课记录的唯一标识，主键。
student_id INT, //选课学生的标识，用于关联学生信息表。
course_name VARCHAR(50), //所选课程的名称。
);
学生班级表（classes）结构如下：

CREATE TABLE classes (
class_id INT PRIMARY KEY, //班级的唯一标识，主键。
class_name VARCHAR(50) //班级名称。
);
要查询某个班级（例如班级名称为 ‘Class A’）下所有学生的选课情况，可以使用以下 SQL 查询语句：

SELECT
s.student_id,
s.student_name,
cs.course_name
FROM
students s
JOIN
course_selections cs ON s.student_id = cs.student_id
JOIN
classes c ON s.class_id = c.class_id
WHERE
c.class_name = ‘Class A’;

如何用 MySQL 实现一个可重入的锁？（通过一张锁表配合事务的 SELECT … FOR UPDATE 实现，记录锁持有者与重入次数，从而支持同线程多次加锁与逐步释放）

创建一个保存锁记录的表：

CREATE TABLE `lock_table` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    //该字段用于存储锁的名称，作为锁的唯一标识符。
    `lock_name` VARCHAR(255) NOT NULL, 
    // holder_thread该字段存储当前持有锁的线程的名称，用于标识哪个线程持有该锁。
    `holder_thread` VARCHAR(255),   
    // reentry_count 该字段存储锁的重入次数，用于实现锁的可重入性
    `reentry_count` INT DEFAULT 0
);

加锁的实现逻辑

1、开启事务

2、执行 SQL SELECT holder_thread, reentry_count FROM lock_table WHERE lock_name =? FOR UPDATE，查询是否存在该记录：

• 如果记录不存在，则直接加锁，执行 INSERT INTO lock_table (lock_name, holder_thread, reentry_count) VALUES (?,?, 1)

• 如果记录存在，且持有者是同一个线程，则可冲入，增加重入次数，执行 UPDATE lock_table SET reentry_count = reentry_count + 1 WHERE lock_name =?

3、提交事务

解锁的逻辑：

1、开启事务

2、执行 SQL SELECT holder_thread, reentry_count FROM lock_table WHERE lock_name =? FOR UPDATE，查询是否存在该记录：

• 如果记录存在，且持有者是同一个线程，且可重入数大于 1 ，则减少重入次数 UPDATE lock_table SET reentry_count = reentry_count - 1 WHERE lock_name =?

• 如果记录存在，且持有者是同一个线程，且可重入数小于等于 0 ，则完全释放锁，DELETE FROM lock_table WHERE lock_name =?

3、提交事务

存储引擎

执行一条SQL请求的过程是什么？（连接器验证身份→解析器生成语法树→预处理器检查表/字段→优化器选择执行计划→执行器调用存储引擎读取数据并返回结果（查询缓存已被 8.0 移除））

先来一个上帝视角图，下面就是 MySQL 执行一条 SQL 查询语句的流程，也从图中可以看到 MySQL 内部架构里的各个功能模块。

1、连接器：建立连接，管理连接、校验用户身份；

查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；

2、解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

3、执行 SQL：执行 SQL 共有三个阶段：

• 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。

• 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；

• 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

讲一讲mysql的引擎吧，你有什么了解？（**InnoDB默认引擎支持事务、行级锁、聚簇索引（数据与索引绑定），适合高并发与数据一致性场景；MyISAM 不支持事务，仅支持表级锁、非聚簇索引（数据与索引分离）COUNT(*)高效，适用于读密集、低并发的轻量级场景。）（聚簇索引的叶子节点直接存储数据行（物理有序，唯一），查询高效无需回表；非聚簇索引的叶子节点存储主键或数据指针，需二次回表查询且可存在多个）

• 事务：InnoDB 支持事务，MyISAM 不支持事务，这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。

• 索引结构：InnoDB 是聚簇索引，MyISAM 是非聚簇索引。
  聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
  而 MyISAM 是非聚簇索引，数据文件是分离的，索引保存的是数据文件的指针,主键索引和辅助索引是独立的。

• 锁粒度：InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

• count 的效率：InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。

MySQL为什么InnoDB是默认引擎？（因其支持事务（ACID）、行级锁（高并发性能）、崩溃恢复（通过 redo log 保障数据持久性），全面满足现代应用对数据一致性与高可用的核心需求）

InnoDB引擎在事务支持、并发性能、崩溃恢复等方面具有优势，因此被MySQL选择为默认的存储引擎。

• 事务支持：InnoDB引擎提供了对事务的支持，可以进行ACID（原子性、一致性、隔离性、持久性）属性的操作。Myisam存储引擎是不支持事务的。
• 并发性能：InnoDB引擎采用了行级锁定的机制，可以提供更好的并发性能，Myisam存储引擎只支持表锁，锁的粒度比较大。
• 崩溃恢复：InnoDB引引擎通过 redolog 日志实现了崩溃恢复，可以在数据库发生异常情况（如断电）时，通过日志文件进行恢复，保证数据的持久性和一致性。Myisam是不支持崩溃恢复的。

索引

索引是什么？有什么好处？(索引是数据库的“目录”，基于B+树结构实现；通过有序存储键值快速定位数据，将查询复杂度从全表扫描的O(n)降至O(log n))

索引类似于书籍的目录，可以减少扫描的数据量，提高查询效率。

• 如果查询的时候，没有用到索引就会全表扫描，这时候查询的时间复杂度是On
• 如果用到了索引，那么查询的时候，可以基于二分查找算法，通过索引快速定位到目标数据， mysql 索引的数据结构一般是 b+树，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

讲讲索引的分类是什么？

MySQL可以按照四个角度来分类索引。

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。
接下来，按照这些角度来说说各类索引的特点。

按数据结构分类（B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型）

按物理存储分类（聚簇索引的叶子节点直接存储实际数据，二级索引的叶子节点存储主键值）

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

按字段特性分类（主键索引（唯一且非空）、唯一索引（值唯一但允许空）、普通索引（无约束）以及前缀索引（基于字段前几个字符优化存储与查询效率））

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

• 主键索引
  主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

在创建表时，创建主键索引的方式如下：

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引
唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

在创建表时，创建唯一索引的方式如下：

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

建表后，如果要创建唯一索引，可以使用这面这条命令：

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,…);
普通索引
普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

在创建表时，创建普通索引的方式如下：

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

建表后，如果要创建普通索引，可以使用这面这条命令：

CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

前缀索引
前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

在创建表时，创建前缀索引的方式如下：

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

建表后，如果要创建前缀索引，可以使用这面这条命令：

CREATE INDEX index_name
ON table_name(column_name(length));

按字段个数分类（单列索引（单个字段）和联合索引（多列组合，需遵循最左匹配原则以高效查询（在where中的顺序无关），范围查询可能中断后续字段匹配））

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）

建立在单列上的索引称为单列索引，比如主键索引；
建立在多列上的索引称为联合索引；
通过将多个字段组合成一个索引，该索引就被称为联合索引。

比如，将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)，创建联合索引的方式如下：

CREATE INDEX index_product_no_name ON product(product_no, name);

联合索引(product_no, name) 的 B+Tree 示意图如下（图中叶子节点之间我画了单向链表，但是实际上是双向链表，原图我找不到了，修改不了，偷个懒我不重画了，大家脑补成双向链表就行）。

可以看到，联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。

也就是说，联合索引查询的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。

因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

注意事项：

• 范围查询（如 >、<、BETWEEN）会中断联合索引的后续字段匹配。
• 等值查询（如 =、IN）允许继续匹配后续字段。
• 设计联合索引时，将等值查询字段放在范围查询字段之前，以最大化索引利用率。

如果聚簇索引的数据更新，它的存储要不要变化？（若更新的是索引字段，需调整B+树结构以维护有序性；若仅更新非索引字段，则原地修改数据，存储结构不变）

MySQL主键是聚簇索引吗？（主键默认作为聚簇索引，数据按主键顺序存储于B+树叶子节点；若无主键则依次选择唯一非空列或隐式生成ID作为聚簇索引，二级索引则通过存储主键值实现非主键字段的快速查询）

什么字段适合当做主键？（ 唯一性、非空性及递增趋势（避免页分裂），通常优先使用自增字段（单机适用），但分布式系统需用全局唯一ID方案替代业务字段（如订单号等），以防未来业务变更导致重复风险）

字段具有唯一性，且不能为空的特性
字段最好的是有递增的趋势的，如果字段的值是随机无序的，可能会引发页分裂的问题，造型性能影响。
不建议用业务数据作为主键，比如会员卡号、订单号、学生号之类的，因为我们无法预测未来会不会因为业务需要，而出现业务字段重复或者重用的情况。
通常情况下会用自增字段来做主键，对于单机系统来说是没问题的。但是，如果有多台服务器，各自都可以录入数据，那就不一定适用了。因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题，这时候就需要考虑分布式 id 的方案了。

性别字段能加索引么？为啥？（性别字段因区分度过低（如男/女各占50%），导致索引查询需大量回表，性能反而不如全表扫描且需要占用空间，且优化器会主动放弃使用，故不建议加索引）

不建议针对性别字段加索引。

实际上与索引创建规则之一区分度有关，性别字段假设有100w数据，50w男、50w女，区别度几乎等于 0 。

区分度的计算方式 ：select count(DISTINCT sex)/count(*) from sys_user

实际上对于性别字段不适合创建索引，是因为select * 操作，还得进行50w次回表操作，根据主键从聚簇索引中找到其他字段 ，这一部分开销从上面的测试来说还是比较大的，所以从性能角度来看不建议性别字段加索引，加上索引并不是索引失效，而是回表操作使得变慢的。

既然走索引的查询的成本比全表扫描高，优化器就会选择全表扫描的方向进行查询，这时候建立的性别字段索引就没有启到加快查询的作用，反而还因为创建了索引占用了空间。

表中十个字段，你主键用自增ID还是UUID，为什么？（自增ID保持主键连续递增，避免因UUID随机插入导致的页分裂）

用的是自增 id。

因为 uuid 相对顺序的自增 id 来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以 innodb 无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间。

这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：

写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb 在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机 IO。
因为写入是乱序的，innodb 不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，影响性能。
由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片。
结论：使用 InnoDB 应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行。

说说B+树和B树的区别（B+树非叶节点仅存索引，数据全存于叶节点(双向链表相连)，确保范围查询高效与查找稳定；而B树非叶节点存数据且叶节点独立，查询可能在中间终止，性能波动较大）

• 在B+树中，数据都存储在叶子节点上，而非叶子节点只存储索引信息；而B树的非叶子节点既存储索引信息也存储部分数据。
• B+树的叶子节点使用链表相连，便于范围查询和顺序访问；B树的叶子节点没有链表连接。
• B+树的查找性能更稳定，每次查找都需要查找到叶子节点；而B树的查找可能会在非叶子节点找到数据，性能相对不稳定。

联合索引

创建联合索引时需要注意什么？（优先将区分度高且查询频率高的字段置于前列，以最大化索引过滤效率，同时避免区分度过低的字段导致查询优化器弃用索引而转向全表扫描）

建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

区分度就是某个字段 column 不同值的个数「除以」表的总行数，计算公式如下：

比如，性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。

因为如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是"30%"）很高的时候，它一般会忽略索引，进行全表扫描。

联合索引ABC，现在有个执行语句是A = XXX and C < XXX，索引怎么走(A可以走联合索引，C不会走联合索引)

根据最左匹配原则，A可以走联合索引，C不会走联合索引，但是C可以走索引下推

联合索引(a,b,c) ，查询条件 where b > xxx and a = x 会生效吗 （a 和 b 字段都能利用联合索引（在where中的顺序无关，中断的是之后的字段））

索引会生效，a 和 b 字段都能利用联合索引，符合联合索引最左匹配原则。

联合索引 (a, b，c)，where条件是 a=2 and c = 1，能用到联合索引吗？（只有 a 才能走索引，c 无法走索引）

会用到联合索引，但是只有 a 才能走索引，c 无法走索引，因为不符合最左匹配原则。虽然 c 无法走索引， 但是 c 字段在 5.6 版本之后，会有索引下推的优化，能减少回表查询的次数。

索引失效有哪些？（1、模糊查询（如LIUSE %xx%）、2、对索引列使用函数/表达式、3、隐式类型转换、4、违反联合索引最左匹配原则，5、以及OR条件中egor前索引列or后不是索引列）

6 种会发生索引失效的情况：

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列使用函数，就会导致索引失效。
当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

什么情况下会回表查询（使用二级索引时，若所需数据未被索引覆盖，需根据主键值回到聚簇索引中查询完整记录）

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

它们的主要区别如下：

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。
所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。

如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

什么是覆盖索引？（索引包含查询所需的所有字段，使得查询无需回表即可直接获取数据）

覆盖索引是指一个索引包含了查询所需的所有列，因此不需要访问表中的数据行就能完成查询。

换句话说，查询所需的所有数据都能从索引中直接获取，而不需要进行回表查询。覆盖索引能够显著提高查询性能，因为减少了访问数据页的次数，从而减少了I/O操作。

假设有一张表 employees，表结构如下：

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  department VARCHAR(100),
  salary DECIMAL(10, 2)
);

CREATE INDEX idx_name_age_department ON employees(name, age, department);

如果我们有以下查询：

SELECT name, age, department FROM employees WHERE name = 'John';

在这种情况下，idx_name_age_department 是一个覆盖索引，因为它包含了查询所需的所有列：name、age 和 department。查询可以完全在索引层完成，而不需要访问表中的数据行。

如果一个列即使单列索引，又是联合索引，单独查它的话先走哪个？（当查询条件涉及.联合索引中的全部或最左前缀列.且能覆盖查询字段时，MySQL 优化器优先选择联合索引以避免回表，从而降低查询成本）

mysql 优化器会分析每个索引的查询成本，然后选择成本最低的方案来执行 sql。

如果单列索引是 a，联合索引是（a ，b），那么针对下面这个查询：

select a, b from table where a = ? and b =?
优化器会选择联合索引，因为查询成本更低，查询也不需要回表，直接索引覆盖了。

索引已经建好了，那我再插入一条数据，索引会有哪些变化？（索引的B+树结构会通过节点分裂或调整自动维护平衡，确保数据正确性和查询效率）

插入新数据可能导致B+树结构的调整和索引信息的更新，以保持B+树的平衡性和正确性，这些变化通常由数据库系统自动处理，确保数据的一致性和索引的有效性。

如果插入的数据导致叶子节点已满，可能会触发叶子节点的分裂操作，以保持B+树的平衡性。

索引字段是不是建的越多越好？（并非越多越好，过多的索引会占用额外存储空间，并在写入时因维护B+树产生性能损耗，尤其在频繁更新的场景下）

不是，建的的越多会占用越多的空间，而且在写入频繁的场景下，对于B+树的维护所付出的性能消耗也会越大

索引的优缺点？（优点：显著提升查询效率；缺点：1、需要权衡存储成本，2、维护开销，3、对增删改操作的影响）

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

• 需要占用物理空间，数量越大，占用空间越大；
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
• 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。
  所以，索引不是万能钥匙，它也是根据场景来使用的。

怎么决定建立or不建立索引?（索引适用于唯一性强、高频查询/排序/分组的字段，而无需索引的情况包括低区分度字段、极少使用的列、小数据量表或频繁更新的字段）

什么时候适用索引？

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改

索引优化详细讲讲（结构优化：前缀/覆盖索引、自增主键优化存储结构；查询优化：避免索引失效）

常见优化索引的方法：

• 前缀索引优化：使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
• 覆盖索引优化：覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。
• 主键索引最好是自增的：
  如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
  如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
• 防止索引失效：
  当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
  当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
  联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
  在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

事务

事务的特性是什么？如何实现的？（原子性（undo log回滚日志）、一致性（AID 合力）、隔离性（MVCC多版本并发控制/锁）、持久性（redo log重做日志））

• 原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样，就好比买一件商品，购买成功时，则给商家付了钱，商品到手；购买失败时，则商品在商家手中，消费者的钱也没花出去。
• 一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。
• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。也就是说，消费者购买商品这个事务，是不影响其他消费者购买的。
• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
  MySQL InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志） 来保证的；
隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

mysql可能出现什么和并发相关问题？（脏读、不可重复读、幻读）事务隔离级别？（读未提交、读已提交、可重复读、串行化）

脏读(dirty read)（读未提交数据）

不可重复读(nonrepeatable read)（数据变更）

幻读(phantom read)（记录增减）

四种隔离级别具体是如何实现的呢？（默认隔离级别：可重复读隔离级别）

• 读未提交直接读取最新数据；
• 读提交和可重复读基于 MVCC 的 Read View（前者每条语句生成新视图，后者事务全程复用同一视图）；
• 串行化通过读写锁强制串行执行。

mysql的是怎么解决并发问题的？（1、锁机制（行锁/表锁）、2、事务隔离级别（控制数据可见性）和3、MVCC（多版本并发控制））

锁机制：Mysql提供了多种锁机制来保证数据的一致性，包括行级锁、表级锁、页级锁等。通过锁机制，可以在读写操作时对数据进行加锁，确保同时只有一个操作能够访问或修改数据。
事务隔离级别：Mysql提供了多种事务隔离级别，包括读未提交、读已提交、可重复读和串行化。通过设置合适的事务隔离级别，可以在多个事务并发执行时，控制事务之间的隔离程度，以避免数据不一致的问题。
MVCC（多版本并发控制）：Mysql使用MVCC来管理并发访问，它通过在数据库中保存不同版本的数据来实现不同事务之间的隔离。在读取数据时，Mysql会根据事务的隔离级别来选择合适的数据版本，从而保证数据的一致性。

可重复读隔离级别下，A事务提交的数据，在B事务能看见吗？（B事务无法看到A事务提交的新数据，因为其基于事务启动时生成的 Read View 读取数据版本，始终与事务开始时的数据状态保持一致）

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是开始事务后（执行 begin 语句后），在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的。

介绍MVCC实现原理（通过维护数据的「版本链」实现并发事务的高效读写协作）

MVCC允许多个事务同时读取同一行数据，而不会彼此阻塞，每个事务看到的数据版本是该事务开始时的数据版本。这意味着，如果其他事务在此期间修改了数据，正在运行的事务仍然看到的是它开始时的数据状态，从而实现了非阻塞读操作。

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。

• 「读提交」隔离级别是在「每个select语句执行前」都会重新生成一个 Read View；
• 「可重复读」隔离级别是执行第一条select时，生成一个 Read View，然后整个事务期间都在用这个 Read View。

滥用事务，或者一个事务里有特别多sql的弊端？（滥用事务或大事务会导致锁持有时间过长，引发资源争用、死锁风险激增，并因锁范围扩大严重影响数据库并发性能）

事务的资源在事务提交之后才会释放的，比如存储资源、锁。

如果一个事务特别多 sql，那么会带来这些问题：

如果一个事务特别多 sql，锁定的数据太多，容易造成大量的死锁和锁超时。
回滚记录会占用大量存储空间，事务回滚时间长。在MySQL (opens new window)中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值，sql 越多，所需要保存的回滚数据就越多。
执行时间长，容易造成主从延迟，主库上必须等事务执行完成才会写入binlog，再传给备库。所以，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟

锁

讲一下mysql里有哪些锁？（全局锁、表级锁和行锁）

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

数据库的表锁和行锁有什么作用？（表锁通过锁定整表保证批量操作的一致性但牺牲并发性，而行锁以细粒度锁定单行提升高并发场景下的访问效率与资源利用率）

表锁的作用：

• 整体控制：表锁可以用来控制整个表的并发访问，当一个事务获取了表锁时，其他事务无法对该表进行任何读写操作，从而确保数据的完整性和一致性。
• 粒度大：表锁的粒度比较大，在锁定表的情况下，可能会影响到整个表的其他操作，可能会引起锁竞争和性能问题。
• 适用于大批量操作：表锁适合于需要大批量操作表中数据的场景，例如表的重建、大量数据的加载等。

行锁的作用：

• 细粒度控制：行锁可以精确控制对表中某行数据的访问，使得其他事务可以同时访问表中的其他行数据，在并发量大的系统中能够提高并发性能。
• 减少锁冲突：行锁不会像表锁那样造成整个表的锁冲突，减少了锁竞争的可能性，提高了并发访问的效率。
• 适用于频繁单行操作：行锁适合于需要频繁对表中单独行进行操作的场景，例如订单系统中的订单修改、删除等操作。

两条update语句处理一张表的不同的主键范围的记录，一个<10，一个>15，会不会遇到阻塞？底层是为什么的？（不会阻塞，因为锁住的范围不一样）

不会，因为锁住的范围不一样，不会形成冲突。

第一条 update sql 的话（ id<10），锁住的范围是（-♾️，10）
第二条 update sql 的话（id >15），锁住的范围是（15，+♾️）

如果2个范围不是主键或索引？还会阻塞吗？（会阻塞。非索引字段的更新触发全表扫描时，InnoDB会对所有扫描到的行及间隙加锁（等效锁表））

如果2个范围查询的字段不是索引的话，那就代表 update 没有用到索引，这时候触发了全表扫描，全部索引都会加行级锁，这时候第二条 update 执行的时候，就会阻塞了。

因为如果 update 没有用到索引，在扫描过程中会对索引加锁，所以全表扫描的场景下，所有记录都会被加锁，也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁，相当于锁住了全表。

日志

日志文件是分成了哪几种？（ redo log（保障事务持久性） 和 undo log（实现事务原子性/MVCC），Server层的 bin log（数据备份/主从复制），以及 慢查询日志）

• redo log 重做日志，是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；

• undo log 回滚日志，是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。

• bin log 二进制日志，是 Server 层生成的日志，主要用于数据备份和主从复制；

• 慢查询日志，用于记录执行时间过长的sql，需要设置阈值后手动开启

UndoLog日志的作用是什么？（Undo Log 记录数据变更前的状态，支持事务回滚保障原子性）

有了undolog为啥还需要redolog呢？

redoLog是在内存里吗？（事务执行在内存，事务提交写入磁盘）

事务执行过程中，生成的 redolog 会在 redolog buffer 中，也就是在内存中，等事务提交的时候，会把 redolog 写入磁盘。

性能调优

mysql的explain有什么作用？（EXPLAIN 用于分析 SQL 的执行计划）

explain 是查看 sql 的执行计划，主要用来分析 sql 语句的执行过程，比如有没有走索引，有没有外部排序，有没有索引覆盖等等。

如下图，就是一个没有使用索引，并且是一个全表扫描的查询语句。

对于执行计划，参数有：

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
• key_len 表示索引的长度；
• rows 表示扫描的数据行数。
• type 表示数据扫描类型，我们需要重点看这个。

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

• All（全表扫描）：在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。
• index（全索引扫描）：index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。
• range（索引范围扫描）：range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
• ref（非唯一索引扫描）：ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
• eq_ref（唯一索引扫描）：eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
• const（结果只有一条的主键或唯一索引扫描）：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。效率低，要避免这种问题的出现。

给你张表，发现查询速度很慢，你有那些解决方案?（通过 EXPLAIN 分析执行计划进而优化索引（如覆盖索引、联合索引）、避免全表扫描与索引失效，结合 SQL 重写（分页/字段精简）、表结构拆分（分库分表）及缓存策略）

• 分析查询语句：使用EXPLAIN命令分析SQL执行计划，找出慢查询的原因，比如是否使用了全表扫描，是否存在索引未被利用的情况等，并根据相应情况对索引进行适当修改。
• 创建或优化索引：根据查询条件创建合适的索引，特别是经常用于WHERE子句的字段、Orderby 排序的字段、Join 连表查询的字典、 group by的字段，并且如果查询中经常涉及多个字段，考虑创建联合索引，使用联合索引要符合最左匹配原则，不然会索引失效
• 避免索引失效：比如不要用左模糊匹配、函数计算、表达式计算等等。
• 查询优化：避免使用SELECT *，只查询真正需要的列；使用覆盖索引，即索引包含所有查询的字段；联表查询最好要以小表驱动大表，并且被驱动表的字段要有索引，当然最好通过冗余字段的设计，避免联表查询。
• 分页优化：针对 limit n,y 深分页的查询优化，可以把Limit查询转换成某个位置的查询：select * from tb_sku where id>20000 limit 10，该方案适用于主键自增的表，
• 优化数据库表：如果单表的数据超过了千万级别，考虑是否需要将大表拆分为小表，减轻单个表的查询压力。也可以将字段多的表分解成多个表，有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。
• 使用缓存技术：引入缓存层，如Redis，存储热点数据和频繁查询的结果，但是要考虑缓存一致性的问题，对于读请求会选择旁路缓存策略，对于写请求会选择先更新 db，再删除缓存的策略。

如果Explain用到的索引不正确的话，有什么办法干预吗？（可以使用 force index，强制走索引）

可以使用 force index，强制走索引。

比如：

EXPLAIN SELECT 
    productName, buyPrice
FROM
    products 
FORCE INDEX (idx_buyprice)
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice; 

输出：

架构

分表和分库是什么？有什么区别？（ 分库是对数据库实例的拆分（解决并发/存储瓶颈），分表是对单表结构的拆分（优化查询性能），两者均通过垂直（按业务/字段划分）或水平（按数据行规则分布））

分库与分表可以从：垂直（纵向）和 水平（横向）两种纬度进行拆分。下边我们以经典的订单业务举例，看看如何拆分。

• 垂直分库：一般来说按照业务和功能的维度进行拆分，将不同业务数据分别放到不同的数据库中，核心理念 专库专用。按业务类型对数据分离，剥离为多个数据库，像订单、支付、会员、积分相关等表放在对应的订单库、支付库、会员库、积分库。垂直分库把一个库的压力分摊到多个库，提升了一些数据库性能，但并没有解决由于单表数据量过大导致的性能问题，所以就需要配合后边的分表来解决。
• 垂直分表：针对业务上字段比较多的大表进行的，一般是把业务宽表中比较独立的字段，或者不常用的字段拆分到单独的数据表中，是一种大表拆小表的模式。数据库它是以行为单位将数据加载到内存中，这样拆分以后核心表大多是访问频率较高的字段，而且字段长度也都较短，因而可以加载更多数据到内存中，减少磁盘IO，增加索引查询的命中率，进一步提升数据库性能。
• 水平分库：是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，以此实现水平扩展，是一种常见的提升数据库性能的方式。这种方案往往能解决单库存储量及性能瓶颈问题，但由于同一个表被分配在不同的数据库中，数据的访问需要额外的路由工作，因此系统的复杂度也被提升了。
• 水平分表：是在同一个数据库内，把一张大数据量的表按一定规则，切分成多个结构完全相同表，而每个表只存原表的一部分数据。水平分表尽管拆分了表，但子表都还是在同一个数据库实例中，只是解决了单一表数据量过大的问题，并没有将拆分后的表分散到不同的机器上，还在竞争同一个物理机的CPU、内存、网络IO等。要想进一步提升性能，就需要将拆分后的表分散到不同的数据库中，达到分布式的效果。