MySQL面试题总结

MySQL 常见面试题总结，涉及 SQL 基础、事务、锁、索引、存储引擎等内容，文章将不断更新。

1. SQL 基础

1.1 数据库的三大范式是什么？

• 第一范式（1NF）：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
• 第二范式（2NF）：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，即在 1NF 基础上消除非主属性对主码的部分函数依赖。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
• 第三范式（3NF）：任何非主属性不依赖于其它非主属性（在 2NF 基础上消除传递依赖）。第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

1.2 NOSQL 和 SQL 的区别？

SQL 指关系型数据库，主要代表：SQL Server、Oracle、MySQL（开源）、PostgreSQL（开源）。关系型数据库存储结构化数据，这些数据逻辑上以行列二维表的形式存在，每一列代表数据的一种属性，每一行代表一个数据实体。

NoSQL 指非关系型数据库，主要代表：MongoDB、Redis。NoSQL 数据库逻辑上提供了不同于二维表的存储方式，存储方式可以是 JSON 文档、哈希表或者其他方式。

选择 SQL 还是 NoSQL，考虑以下因素：

• ACID vs. BASE：关系型数据库支持 ACID 即原子性、一致性、隔离性和持续性，NoSQL 采用更宽松的模型 BASE，即基本可用，软状态和最终一致性。我们需要考虑对于面对的应用场景，ACID 是否是必须的，比如银行应用就必须保证 ACID，否则一笔钱可能被使用两次；又比如社交软件不必保证 ACID，因为一条状态的更新对于所有用户读取先后时间有数秒不同并不影响使用。
• 扩展性对比：NoSQL 数据之间无关系，这样就非常容易扩展，也无形之间在架构的层面上带来了可扩展的能力。比如 Redis 自带主从复制模式、哨兵模式、切片集群模式。相反关系型数据库的数据之间存在关联性，水平扩展较难，需要解决跨服务器 JOIN，分布式事务等问题。

1.3 超键、候选键、主键、外键分别是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
• 候选键：是最小超键，即没有冗余元素的超键。
• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（NULL）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

1.4 SQL 约束有哪几种？

• NOT NULL：用于控制字段的内容一定不能为空（NULL）。
• UNIQUE：控制字段内容不能重复，一个表允许有多个 UNIQUE 约束。
• PRIMARY KEY：也是用于控制字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY：用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK：用于控制字段的值范围。

1.5 MySQL 怎么连表查询？

数据库有四种联表查询类型：内连接（INNER JOIN）、左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）、全外连接（FULL JOIN）。

• 内连接：返回两个表中连接条件完全匹配的所有行（即两个表的交集）。
• 左外连接：返回左表的所有行，即使它们在右表中没有匹配的行，对于左表中存在而右表中没有匹配的行，右表的结果列将填充为 NULL。
• 右外连接：返回右表的所有行，即使它们在左表中没有匹配的行，对于右表中存在而左表中没有匹配的行，左表的结果列将填充为 NULL，RIGHT JOIN 本质上是 LEFT JOIN 的反向操作。
• 全外连接：返回左表和右表的所有行，当某一行在另一个表中没有匹配时，另一个表的结果列将填充为 NULL，它结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

连表查询写法如下：

1
2
3
4

SELECT e.name AS 'name', d.name AS 'department'
FROM employees e
INNER/LEFT/RIGHT JOIN departments d ON e.department_id = d.id;
-- 若写 JOIN 则默认表示 INNER JOIN

需要注意的是 MySQL 本身并不直接支持 FULL JOIN 语法，需要使用 UNION 组合 LEFT JOIN 和 RIGHT JOIN 的结果：

1
2
3
4
5
6
7

(SELECT e.name AS 'name', d.name AS 'department'
 FROM employees e
 LEFT JOIN departments d ON e.department_id = d.id)
UNION
(SELECT e.name AS 'name', d.name AS 'department'
 FROM employees e
 RIGHT JOIN departments d ON e.department_id = d.id);

1.6 MySQL 如何避免重复插入数据？

（1）使用 UNIQUE 约束

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

1
2
3
4
5

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

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

（2）使用 INSERT ... ON DUPLICATE KEY UPDATE

这种语句允许在插入记录时处理重复键的情况，如果插入的记录与现有记录冲突，可以选择更新现有记录：

1
2
3

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

（3）使用 INSERT IGNORE

该语句会在插入记录时忽略那些因重复键而导致的插入错误。例如：

1
2

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

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

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

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

1.7 MySQL 中的 VARCHAR 和 CHAR 有什么区别？

CHAR 是一个定长字段，假如申请了 CHAR(10) 的空间，那么无论实际存储多少内容，该字段都占用 10 个字符；而 VARCHAR 是变长的，也就是说申请的只是最大长度，占用的空间为实际字符长度再加一，最后一个字符存储使用了多长的空间。

在检索效率上来讲，CHAR 优于 VARCHAR，因此在使用中，如果确定某个字段的值的长度，可以使用 CHAR，否则应该尽量使用 VARCHAR 节约存储空间，例如存储用户 MD5 加密后的密码，则可以使用 CHAR。

1.8 VARCHAR 后面的数字代表字节还是字符？

VARCHAR 后面括号里的数字代表的是字符数，而不是字节数。比如 VARCHAR(10)，这里的 10 表示该字段最多可以存储 10 个字符，字符的字节长度取决于所使用的字符集：

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

1.9 INT(1) 和 INT(10) 在 MySQL 有什么不同？

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

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

1.10 IP 地址如何在数据库里存储？

IPv4 地址是一个 32 位的二进制数，通常以点分十进制表示法呈现，例如 192.168.1.1。

（1）字符串类型的存储方式：直接将 IP 地址作为字符串存储在数据库中，比如可以用 VARCHAR(15) 来存储：

1
2
3
4
5
6

CREATE TABLE ip_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(15)
);

INSERT INTO ip_records (ip_address) VALUES ('192.168.1.1');

这种存储方式的优点是直观易懂，方便直接进行数据的插入、查询和显示，不需要进行额外的转换操作。缺点是占用存储空间较大，字符串比较操作的性能相对较低，不利于进行范围查询。

（2）整数类型的存储方式：将 IPv4 地址转换为 32 位无符号整数进行存储，常用的数据类型有 INT UNSIGNED。

1
2
3
4
5
6
7
8
9
10

CREATE TABLE ip_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address INT UNSIGNED
);

-- 插入数据，需要先将 IPv4 地址转换为整数
INSERT INTO ip_records (ip_address) VALUES (INET_ATON('192.168.1.1'));

-- 查询时将整数转换回 IPv4 地址
SELECT INET_NTOA(ip_address) FROM ip_records;

其中 INET_ATON() 和 INET_NTOA() 是专门用于处理 IPv4 地址与数值格式之间转换的函数。

这种存储方式的优点是占用存储空间小，整数比较操作的性能较高，便于进行范围查询。缺点是需要进行额外的转换操作，不够直观，增加了开发的复杂度。

1.11 说一下外键约束是什么？

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

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

1
2
3
4
5
6

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 表中都存在，从而维护了数据的完整性和一致性。

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

1.12 MySQL 中 IN 和 EXISTS 区别？

MySQL 中的 IN 语句是把外表和内表作 Hash 连接，而 EXISTS 语句是对外表作 Loop 循环，每次 Loop 循环再对内表进行查询。一直大家都认为 EXISTS 比 IN 语句的效率要高，这种说法其实是不准确的。这个是要区分环境的：

• 如果查询的两个表大小相当，那么用 IN 和 EXISTS 差别不大。
• 如果两个表中一个较小，一个是大表，则子查询表大的用 EXISTS，子查询表小的用 IN。这是因为 EXISTS 一旦找到匹配项就会立即停止查询，而 IN 可能会扫描整个子查询结果集。
• NOT IN 和 NOT EXISTS：如果查询语句使用了 NOT IN，那么内外表都进行全表扫描，没有用到索引；而 NOT EXISTS 的子查询依然能用到表上的索引。所以无论哪个表大，用 NOT EXISTS 都比 NOT IN 要快。

此外 IN 能够正确处理子查询中包含 NULL 值的情况，是直接进行值比较，而 EXISTS 不受子查询结果中 NULL 值的影响，因为它关注的是行的存在性，而不是具体值。

这边首先要理解一个概念，SQL 中任何与 NULL 的比较（如 ? = NULL、? <> NULL）结果都是 UNKNOWN，而不是 TRUE 或 FALSE。而 WHERE 子句只返回条件为 TRUE 的行，UNKNOWN 和 FALSE 都会被过滤掉。

下面通过具体示例说明它们的差异：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT  -- 可能为 NULL
);

INSERT INTO employees VALUES
(1, 'Alice', 10),
(2, 'Bob', 20),
(3, 'Charlie', NULL);  -- 注意：NULL 值

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

INSERT INTO departments VALUES
(10, 'Sales'),
(20, 'Marketing'),
(NULL, 'HR');  -- 注意：NULL 值

如果子查询返回的结果包含 NULL，IN 会将 NULL 视为未知值，例如下面这个查询找出有匹配部门的员工，子查询返回 (10, 20, NULL)，但 dept_id = NULL 的比较返回 UNKNOWN，所以 Charlie 的记录被排除：

1
2

SELECT * FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments);

假设我们需要找出没有匹配部门的员工，下面这个查询即使 Charlie 的部门是 NULL 也会被过滤掉，因为 dept_id <> NULL 也是永远返回 UNKNOWN，导致整个 NOT IN 条件失败：

1
2

SELECT * FROM employees 
WHERE dept_id NOT IN (SELECT dept_id FROM departments);  -- 危险

现在再看用 EXISTS 找无匹配部门的员工：

1
2
3
4
5
6

SELECT * FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.dept_id = e.dept_id  -- 关键关联条件
);

在比较 NULL 值的时候结果为 UNKNOWN，导致子查询返回 0 行，这样使得 NOT EXISTS 为 TRUE，成功返回无匹配部门的员工 Charlie。

1.13 DROP、DELETE 与 TRUNCATE 的区别？

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

1.14 什么是存储过程？有哪些优缺点？

存储过程是一些预编译的 SQL 语句。

1. 更加直白的理解：存储过程可以说是一个记录集，它是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用它就行了。
2. 存储过程是一个预编译的代码块，执行效率比较高，一个存储过程替代大量 T-SQL 语句，可以降低网络通信量，提高通信速率，可以一定程度上确保数据安全。

但是，在互联网项目中，其实是不太推荐存储过程的，比较出名的就是阿里的《Java 开发手册》中禁止使用存储过程，我个人的理解是，在互联网项目中，迭代太快，项目的生命周期也比较短，人员流动相比于传统的项目也更加频繁，在这样的情况下，存储过程的管理确实是没有那么方便，同时，复用性也没有写在服务层那么好。

1.15 MySQL 有哪些基本函数？

（1）字符串函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- CONCAT(str1, str2, ...) 拼接字符串
SELECT CONCAT('Hello', ' ', 'World');  -- 输出：Hello World

-- SUBSTRING(str, start, length) 截取子字符串
SELECT SUBSTRING('MySQL', 3, 3);  -- 输出：'SQL'（从第 3 字符起取 3 位）

-- LENGTH(str) 返回字符串字节数（中文通常占 3 字节）
SELECT LENGTH('数据库');  -- 输出：9（UTF8 编码下）

-- CHAR_LENGTH(str) 返回字符个数（中文算 1 个字符）
SELECT CHAR_LENGTH('数据库');  -- 输出：3

-- REPLACE(str, from_str, to_str) 替换字符串
SELECT REPLACE('I like Java', 'Java', 'Python');  -- 输出：'I like Python'

（2）数值函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- ROUND(X, D) 四舍五入（D 为小数位数）
SELECT ROUND(3.14159, 2);  -- 输出：3.14

-- CEIL(X) / FLOOR(X) 向上/向下取整
SELECT CEIL(2.3), FLOOR(2.7);  -- 输出：3, 2

-- ABS(X) 取绝对值
SELECT ABS(-15);  -- 输出：15

-- RAND() 生成 0~1 的随机数
SELECT RAND();  -- 输出：0.123456（随机值）

-- POWER(X, Y) 计算 X 的 Y 次方
SELECT POWER(2, 3);  -- 2³ = 8

（3）日期时间函数

1
2
3
4
5
6
7
8
9
10
11

-- NOW() 返回当前日期和时间
SELECT NOW();  -- 输出：'2025-07-13 14:30:45'

-- CURDATE() / CURTIME() 返回当前日期/时间
SELECT CURDATE();  -- 输出：'2025-07-13'

-- DATE_FORMAT(date, format) 格式化日期
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');  -- 输出：'2025年07月13日'

-- DATEDIFF(date1, date2) 计算两个日期相差的天数
SELECT DATEDIFF('2025-12-31', NOW()); -- 输出：170天（示例值）

（4）聚合函数

1
2
3
4
5
6
7
8
9
10
11

-- COUNT() 统计行数
SELECT COUNT(*) FROM users;  -- 返回总用户数

-- SUM() 求和
SELECT SUM(salary) FROM employees;  -- 计算工资总和

-- AVG() 求平均值
SELECT AVG(age) FROM students;  -- 平均年龄

-- MAX() / MIN() 最大/最小值
SELECT MAX(price) FROM products;  -- 最贵商品价格

（5）条件判断函数

1
2
3
4
5
6
7
8
9
10
11

-- IF(expr, true_val, false_val) 条件判断
SELECT IF(10 > 5, 'Yes', 'No');  -- 输出：'Yes'

-- CASE WHEN 多条件分支
SELECT 
  CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
  END AS grade
FROM students;

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

所有的查询语句都是从 FROM 开始执行，在执行过程中，每个步骤都会生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入，最后一个步骤产生的虚拟表即为输出结果。

1
2
3
4
5
6
7
8
9
10
11
12

(09) SELECT  -- 选择最终返回的列
(10) DISTINCT <column>,  -- 去重操作
(06) AGG_FUNC <column> or <expression>, ...  -- 聚合计算
(01) FROM <left_table>  -- 首先确定数据来源，包括主表和所有连接表
    (03) <join_type> JOIN <right_table>  -- 表的连接操作
    (02) ON <join_condition>
(04) WHERE <where_condition>  -- 对 FROM/JOIN 生成的虚拟表进行行级过滤
(05) GROUP BY <group_by_list>  -- 根据指定列对数据进行分组
(07) WITH {CUBE|ROLLUP}
(08) HAVING <having_condtion>  -- 对分组后的结果进行过滤（针对分组或聚合值）
(11) ORDER BY <order_by_list>  -- 对结果集排序
(12) LIMIT <limit_number>;  -- LIMIT/OFFSET 限制返回行数或分页

看一个样例：统计 2023 年每个部门的销售额，仅显示销售额超过 10 万的部门，按销售额降序排列返回前 10 条记录：

1
2
3
4
5
6
7
8
9
10

SELECT
    department_id AS dept,
    SUM(sales) AS total_sales
FROM orders
JOIN departments USING (dept_id)
WHERE order_date >= '2023-01-01'
GROUP BY department_id
HAVING SUM(sales) > 100000  -- 注意：这里不应该用 total_sales，因为 HAVING 在 SELECT 前执行
ORDER BY total_sales DESC
LIMIT 10;

执行步骤如下：

1. FROM orders JOIN departments USING (dept_id)：加载 orders 和 departments 表，根据 dept_id 连接；
2. WHERE order_date >= '2023-01-01'：过滤出 2023 年之后的订单；
3. GROUP BY department_id：按部门分组；
4. HAVING SUM(sales) > 100000：过滤掉销售额 ≤10 万的部门（注意：不能直接使用别名 total_sales）
5. SELECT ...：计算表达式 department_id 和 SUM(sales)，并定义别名 dept 和 total_sales；
6. ORDER BY total_sales DESC：使用别名 total_sales 降序排序；
7. LIMIT 10：限制返回前 10 条记录。

1.17 SQL 题：给学生表、课程成绩表，求不存在 01 课程但存在 02 课程的学生的成绩？

假设我们有以下两张表：

• Student 表，其中包含学生的 sid（学生编号）和其他相关信息。
• Score 表，其中包含 sid（学生编号），cid（课程编号）和 score（分数）。

（1）使用 LEFT JOIN 和 IS NULL：

1
2
3
4
5

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

1
2
3
4
5
6

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';
);

2. 事务

2.1 什么是数据库事务？

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

2.2 事务具有的四个特征？

事务就是一组原子性的操作，这些操作要么全部发生，要么全部不发生。事务把数据库从一种一致性状态转换成另一种一致性状态。

• 原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样，就好比买一件商品，购买成功时，则给商家付了钱，商品到手；购买失败时，则商品在商家手中，消费者的钱也没花出去。
• 一致性（Consistency）：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
• 隔离性（Isolation）：一个事务的执行不能被其它事务干扰，即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。也就是说，消费者购买商品这个事务，是不影响其他消费者购买的。
• 持续性（Durability）：也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

2.3 什么是脏读、不可重复读与幻读？

MySQL 服务端是允许多个客户端连接的，这意味着 MySQL 会出现同时处理多个事务的情况（并发）。那么在同时处理多个事务的时候，就可能出现脏读、不可重复读以及幻读的问题。

• 脏读（Dirty Read）：事务 A 读取了事务 B 更新但还没有提交的数据，如果 B 发生了回滚操作，那么 A 读取到的数据是脏数据。
• 不可重复读（Non-repeatable Read）：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，出现前后两次读到的数据不一致的情况。
• 幻读（Phantom Read）：在一个事务内多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了幻读现象。例如事务 A 查询年龄大于 30 岁的用户发现共有 5 条记录，然后事务 B 也按相同的搜索条件也是查询出了 5 条记录，接着事务 A 插入了一个年龄大于 30 岁的用户，并提交了事务，此时数据库年龄大于 30 岁的用户个数就变为 6，事务 B 再次查询年龄大于 30 岁的用户，此时查询到的记录数量有 6 条，发现和前一次读到的记录数量不一样了，就感觉发生了幻觉一样。

总结：不可重复读侧重于修改，幻读侧重于新增或删除（多了或少了行），脏读是一个事务回滚影响另外一个事务。

2.4 MySQL 的四种隔离级别是什么？

• Read Uncommitted（读取未提交内容）：在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读。
• Read Committed（读取提交内容）：这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交的事务所做的改变。这种隔离级别会发生所谓的不可重复读，因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 SELECT 可能返回不同结果。
• Repeatable Read（可重读）：这是 MySQL InnoDB 引擎的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读。
• Serializable（可串行化）：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁（对记录加 S 型的 Next-Key 锁）。在这个级别，可能导致大量的超时现象和锁竞争。

MySQL 默认采用的是 REPEATABLE-READ 隔离级别，Oracle 默认采用的是 READ-COMMITTED 隔离级别。

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是 MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED（读取提交内容），但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE（可串行化）隔离级别。

2.5 四种隔离级别具体是如何实现的呢？

• 对于读未提交隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了。
• 对于串行化隔离级别的事务来说，通过加读写锁的方式来避免并行访问。
• 对于读提交和可重复读隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同：读提交隔离级别是在每个语句执行前都会重新生成一个 Read View，而可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

2.6 举个例子说一下可重复读下的幻读问题？怎么保证不发生幻读？

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。假如当前数据表中没有 ID 为 3 的用户记录，事务 A 第一次执行普通的 SELECT 语句时生成了一个 Read View，此时事务 A 查不到 ID 为 3 的记录，接着事务 B 插入一条 ID 为 3 的记录，并且提交了事务，假如这时事务 A 在看不到记录的情况下去更新了这条 ID 为 3 的记录（场景确实很违和），然后事务 A 再次查询 ID 为 3 的记录，事务 A 就能看到事务 B 插入的记录了，幻读就是发生在这种违和的场景。因为这种特殊现象的存在，所以我们认为 MySQL InnoDB 中的 MVCC 并不能完全避免幻读现象。

我们可以尽量在开启事务之后，马上执行 SELECT ... FOR UPDATE 这类锁定读的语句，因为它会对记录加临键锁（Next-Key Locks）这是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁，从而避免其他事务插入一条新记录，就避免了幻读的问题。

2.7 MySQL 是怎么解决并发问题的？

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

2.8 事务的实现原理？

事务是基于重做日志文件（redo log）和回滚日志（undo log）实现的。

每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性。

每当有修改事务时，还会产生 undo log，如果需要回滚，则根据 undo log 的反向语句进行逻辑操作，比如 insert 一条记录就 delete 一条记录。undo log 主要实现数据库的一致性。

2.9 介绍一下 MySQL 事务日志？

InnoDB 事务日志包括 redo log 和 undo log。

undo log 指事务开始之前，在操作任何数据之前，首先将需操作的数据备份到一个地方。redo log 指事务中操作的任何数据，将最新的数据备份到一个地方。

事务日志的目的：实例或者介质失败，事务日志文件就能派上用场。

• redu log：redo log 不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo log 中。具体的落盘策略可以进行配置。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 MySQL 服务的时候，根据 redo log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。redo log 是为了实现事务的持久性而出现的产物。
• undo log：undo log 用来回滚行记录到某个版本。事务未提交之前，undo log 保存了未提交之前的版本数据，undo log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。是为了实现事务的原子性而出现的产物，在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。

2.10 什么是 MySQL 的 binlog？

MySQL 的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（例如 INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。

MySQL binlog 以事件形式记录，还包含语句执行所消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog 有三种格式，各有优缺点：

• statement：基于 SQL 语句的模式，某些语句和函数如 UUID、LOAD DATA INFILE 等在复制过程中可能导致数据不一致甚至出错。
• row：基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。
• mixed：混合模式，根据语句来选用是 statement 还是 row 模式。

2.11 在事务中可以混合使用存储引擎吗？

尽量不要在同一个事务中使用多种存储引擎，MySQL 服务器层不管理事务，事务是由下层的存储引擎实现的。

如果在事务中混合使用了事务型和非事务型的表（例如 InnoDB 和 MyISAM 表），在正常提交的情况下不会有什么问题。

但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。

2.12 什么是 MVCC？

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

2.13 MVCC 的实现原理是什么？

MVCC 的实现，是通过保存数据在某个时间点的快照来实现的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

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

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

对于 InnoDB，聚簇索引记录中包含 3 个隐藏的列：

• ROW ID：隐藏的自增 ID，如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树。
• 事务 ID：记录最后一次修改该记录的事务 ID。
• 回滚指针：指向这条记录的上一个版本。

我们举个例子，假如现在有两个事务：

• 事务 1：INSERT INTO t1(a, b) VALUES (1, 1);
• 事务 2：UPDATE t1 SET b = 666 WHERE a = 1;

如图，首先 INSERT 语句向表 t1 中插入了一条数据，a 字段为 1，b 字段为 1，ROW ID 也为 1，事务 ID 假设为 1，回滚指针假设为 null。当执行 UPDATE t1 SET b = 666 WHERE a = 1 时，大致步骤如下：

• 数据库会先对满足 a = 1 的行加排他锁；
• 然后将原记录复制到 undo 表空间中；
• 修改 b 字段的值为 666，修改事务 ID 为2；
• 并通过隐藏的回滚指针指向 undo log 中的历史记录；
• 事务提交，释放前面对满足 a = 1 的行所加的排他锁。

因此可以总结出 MVCC 实现的原理大致是：InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本。

MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性，通过 MVCC 能够保证事务 ACID 中的隔离性。

3. 锁

3.1 为什么要加锁？

当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况，若对并发操作不加控制就可能读取和存储不正确的数据，破坏数据库的一致性，因此需要加锁使得在多用户环境下保证数据库的完整性和一致性。

3.2 按照锁的粒度能分为哪些锁？

在关系型数据库中，可以按照锁的粒度把数据库锁分为全局锁、行级锁（InnoDB 引擎）、表级锁（MyISAM 引擎）和页级锁（BDB 引擎）。

• 全局锁：通过 FLUSH TABLES WITH READ LOCK 语句会让整个数据库处于只读状态，这时其他线程执行增删改或者表结构修改操作都会阻塞。全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。
• 表级锁
  • 表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MyISAM 与 InnoDB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
  • 特点是开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 通过 LOCK TABLES 语句可以对表加表锁，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。
  • 当我们对数据库表进行操作时，会自动给这个表加上 MDL（元数据锁），对一张表进行 CRUD 操作时，加的是 MDL 读锁；对一张表做结构变更操作的时候，加的是 MDL 写锁；MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。
  • 当执行插入、更新、删除操作，需要先对表加上意向独占锁，然后对该记录加独占锁。意向锁的目的是为了快速判断表里是否有记录被加锁。
• 行级锁
  • 行级锁是 MySQL 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
  • 特点是开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。
• 页级锁
  • 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁之间的一种锁。表级锁速度快，但冲突多，行级锁冲突少，但速度慢。所以取了折衷的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。
  • 开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM 和 InnoDB 存储引擎使用的锁：

• MyISAM 采用表级锁（table-level locking）。
• InnoDB 支持行级锁（row-level locking）和表级锁，默认为行级锁。

3.3 数据库的表锁和行锁有什么作用？

表锁的作用如下：

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

行锁的作用如下：

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

3.4 MySQL 两个线程的 UPDATE 语句同时处理一条数据，会不会有阻塞？

如果两个事务同时更新了 ID 为 1 的记录，比如 UPDATE ... WHERE id = 1;，那么是会阻塞的，因为 InnoDB 存储引擎实现了行级锁。当 A 事务对 ID 为 1 的这行记录进行更新时，会对主键 ID 为 1 的记录加 X 类型的记录锁，这样另一个事务 B 对 ID 为 1 的记录进行更新时，发现已经有记录锁了，就会陷入阻塞状态。

3.5 从锁的类别上分 MySQL 都有哪些锁呢？

从锁的类别上来讲，有共享锁和排他锁。

• 共享锁：又叫做读锁，当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
• 排他锁：又叫做写锁，当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，它和其它的排他锁，共享锁都相斥。

用上面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

锁的粒度取决于具体的存储引擎，InnoDB 实现了行级锁，页级锁，表级锁。他们的加锁开销从大到小，并发能力也是从大到小。

3.6 数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过 version 的方式来进行锁定。实现方式：乐观锁一般会使用版本号机制或 CAS 算法实现。

两种锁的使用场景：

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行 retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

3.7 InnoDB 引擎的行锁是怎么实现的？

InnoDB 是基于索引来完成行锁的。

例如：SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE;

FOR UPDATE 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么 InnoDB 将完成表锁，并发将无从谈起。

3.8 什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。常见的解决死锁的方法有：

• 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
• 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

如果业务处理不好可以用分布式事务锁或者使用乐观锁。

3.9 隔离级别与锁的关系？

• 在 Read Uncommitted 级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突。
• 在 Read Committed 级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁。
• 在 Repeatable Read 级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
• Serializable 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

3.10 优化锁方面的意见？

• 使用较低的隔离级别。
• 设计索引，尽量使用索引去访问数据，加锁更加精确，从而减少锁冲突。
• 选择合理的事务大小，给记录显示加锁时，最好一次性请求足够级别的锁。例如，修改数据的话最好申请排他锁，而不是先申请共享锁，修改时再申请排他锁，这样会导致死锁。
• 不同的程序访问一组表的时候，应尽量约定一个相同的顺序访问各表，对于一个表而言，尽可能固定顺序地获取表中的行，这样将大大减少死锁的机会。
• 尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
• 不要申请超过实际需要的锁级别。
• 数据查询的时候不是必要，不要使用加锁。MySQL 的 MVCC 可以实现事务中的查询不用加锁，优化事务性能：MVCC 只在 Read Committed（读提交）和 Repeatable Read（可重复读）两种隔离级别。
• 对于特定的事务，可以使用表锁来提高处理速度或者减少死锁的可能。

4. 索引

4.1 索引是什么？

索引是一种特殊的文件/数据结构（InnoDB 数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。类似于书籍的目录，可以减少扫描的数据量，提高查询效率。但是索引是一个文件，它是要占据物理空间的。索引的实现通常使用 B 树及其变种 B+ 树。

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，打开字典的页数就可以知道我们要搜索的某一个 Key 的全部值的信息了。

4.2 索引有哪些优缺点？

索引的优点：

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

• 时间方面：创建索引和维护索引要耗费时间，所需的时间开销随着数据量的增加而增大。此外还会降低表的增删改的效率，当对表中的数据进行增删改的时候，索引也要动态地维护（B+ 树维护索引有序性）。
• 空间方面：索引需要占用物理空间，索引数量越多，占用空间越大。

4.3 MySQL 有哪几种索引类型？

• 从存储结构（数据结构）上来划分：BTree 索引（B-Tree 或 B+Tree 索引）、Hash 索引、Full-index 全文索引、R-Tree 索引。这里所描述的是索引存储时保存的形式。
• 从应用层次来分：普通索引、唯一索引、复合索引。
  • 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。
  • 唯一索引：索引列的值必须唯一，但允许有空值。
  • 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
• 根据表中数据的物理顺序与键值的逻辑（索引）顺序关系：聚簇索引、非聚簇索引。
  • 聚簇索引（主键索引）：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB 的聚簇索引其实就是在同一个结构中保存了 B-Tree 索引（技术上来说是 B+Tree）和数据行。
  • 非聚簇索引：不是聚簇索引，就是非聚簇索引。

4.4 如何创建索引？

创建索引有以下三种方式：

（1）在执行 CREATE TABLE 时创建索引：

1
2
3
4
5
6
7
8
9
10

CREATE TABLE table_name (
    id INT auto_increment PRIMARY KEY,  -- 在 id 字段上建立主键索引
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information) ,  -- 在 information 字段上建立全文索引
    UNIQUE KEY (id_card)  -- 在 id_card 字段上建立唯一索引
);

（2）使用 ALTER TABLE 命令添加索引：

1

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE 用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。

其中 table_name 是要增加索引的表名，column_list 指出对哪些列进行索引，如果索引多列则各列之间用逗号分隔。

索引名 index_name 可自己命名，缺省时，MySQL 将根据第一个索引列赋一个名称。另外，ALTER TABLE 允许在单个语句中更改多个表，因此可以同时创建多个索引。

（3）使用 CREATE INDEX 命令创建索引：

1

CREATE INDEX index_name ON table_name (column_list);

不同类型索引的具体创建方式如下：

（1）主键索引

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

1
2
3

CREATE TABLE table_name (
    PRIMARY KEY (id) USING BTREE
);

（2）唯一索引

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

1
2
3

CREATE TABLE table_name (
    UNIQUE KEY (column_1, column_2, ...)
);

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

1
2

CREATE UNIQUE INDEX index_name
ON table_name (column_1, column_2, ...);

（3）普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。在创建表时，创建普通索引的方式如下：

1
2
3

CREATE TABLE table_name (
    INDEX (column_1, column_2, ...)
);

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

1
2

CREATE INDEX index_name
ON table_name (column_1, column_2, ...);

（4）前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 CHAR、VARCHAR、BINARY、VARBINARY 的列上。使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。在创建表时，创建前缀索引的方式如下：

1
2
3

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

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

1
2

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

4.5 创建索引时需要注意什么？

• 非空字段：应该指定列为 NOT NULL，除非你想存储 NULL。在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0 或者一个特殊的值或者一个空串代替空值；
• 取值离散（变量各个取值之间的差异程度）大的字段的列放到联合索引的前面，可以通过 COUNT() 函数查看字段的差异值，返回值越大说明字段的唯一值越多，字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位，一页存储的数据越多则一次 I/O 操作获取的数据越多，效率越高。

4.6 讲一讲聚簇索引与非聚簇索引？

在 InnoDB 里，B+ 树的叶子节点存储了整行数据为主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。一张表只能有一个聚簇索引，那了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。

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

• 数据存储：聚簇索引中，数据行按照索引键值的顺序存储，即索引的叶子节点包含了实际的数据行，这意味着索引结构本身就是数据的物理存储结构；非聚簇索引的叶子节点不存储表中的数据，而是存储该列对应的主键（包含指向数据行的指针或主键值）。
• 索引与数据关系：由于数据与索引紧密相连，当通过聚簇索引查找数据时，可以直接从索引中获得数据行，而不需要额外的步骤去查找数据所在的位置。当通过非聚簇索引查找数据时，首先在非聚簇索引中找到对应的主键值，然后通过这个主键值回溯到聚簇索引中查找实际的数据行，这个过程称为回表。
• 唯一性：聚簇索引通常是基于主键构建的，因此每个表只能有一个聚簇索引，因为数据只能有一种物理排序方式。一个表可以有多个非聚簇索引，因为它们不直接影响数据的物理存储位置。
• 效率：通常情况下，聚簇索引查询只会查一次，对于范围查询和排序查询，聚簇索引通常更有效率，因为它避免了额外的寻址开销，而非聚簇索引需要回表查询多次，当然，非聚簇索引在使用覆盖索引进行查询时效率更高，因为它不需要读取完整的数据行。

注意：MyISAM 无论主键索引还是二级索引都是非聚簇索引，而 InnoDB 的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建立的索引基本都是非聚簇索引。

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键。

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

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为覆盖索引。举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行 SELECT age FROM employee WHERE age > 30 的查询时，在索引的叶子节点上，已经包含了 age 信息，不会再次进行回表查询。

4.8 什么字段适合当做主键？

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

4.9 性别字段能加索引么？

不建议针对性别字段加索引，因为索引创建规则之一需要考虑区分度，性别字段假设有 100w 条数据，50w 男、50w 女，区分度几乎等于 0。区分度的计算方式为 SELECT COUNT(DISTINCT sex)/COUNT(*) FROM users。

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

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

4.10 你主键用自增 ID 还是 UUID，为什么？

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

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

• 写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，InnoDB 在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机 I/O。
• 因为写入是乱序的，InnoDB 不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，影响性能。
• 由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片。

因此使用 InnoDB 应该尽可能地按主键的自增顺序插入，并且尽可能使用单调增加的聚簇键的值来插入新行。

4.11 MySQL 中的索引是怎么实现的？

MySQL InnoDB 引擎是用了 B+ 树作为索引的数据结构。B+ 树是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作。B+ 树存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以 B+ 树相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4 次。

4.12 B+ 树的特性是什么？

• 所有叶子节点都在同一层：这是 B+ 树的一个重要特性，确保了所有数据项的检索都具有相同的 I/O 延迟，提高了搜索效率。每个叶子节点都包含指向相邻叶子节点的指针，形成一个链表，由于叶子节点之间的链接，B+ 树非常适合进行范围查询和排序扫描。可以沿着叶子节点的链表顺序访问数据，而无需进行多次随机访问。
• 非叶子节点存储键值：非叶子节点仅存储键值和指向子节点的指针，不包含数据记录。这些键值用于指导搜索路径，帮助快速定位到正确的叶子节点。并且由于非叶子节点只存放键值，当数据量比较大时，相对于 B 树，B+ 树的层高更少，查找效率也就更高。
• 叶子节点存储数据记录：与 B 树不同，B+ 树的叶子节点存储实际的数据记录或指向数据记录的指针，这意味着每次搜索都会到达叶子节点，才能找到所需数据。
• 自平衡：B+ 树在插入、删除和更新操作后会自动重新平衡，确保树的高度保持相对稳定，从而保持良好的搜索性能。每个节点最多可以有 M 个子节点，最少可以有 ceil(M / 2) 个子节点（除了根节点），这里的 M 是树的阶数。

4.13 B+ 树和 B 树的区别是什么？

• 在 B+ 树中，数据都存储在叶子节点上，而非叶子节点只存储索引信息；而 B 树的非叶子节点既存储索引信息也存储部分数据。在数据量相同的情况下，B+ 树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更“矮胖”，查询底层节点的磁盘 I/O 次数会更少。
• B+ 树的叶子节点使用链表相连，便于范围查询和顺序访问；B 树的叶子节点没有链表连接，因此 B 树只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。
• B+ 树的查找性能更稳定，每次查找都需要查找到叶子节点；而 B 树的查找可能会在非叶子节点找到数据，性能相对不稳定。
• B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化。

4.14 说一说索引的底层实现？

• Hash 索引：基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且 Hash 索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。
• B 树索引：B 树能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。
• B+ 树索引：B 树的改进版本，同时也是数据库索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比 B 树来说，进行范围查找时只需要查找两个节点，进行遍历即可。而 B 树需要获取所有节点，相比之下 B+ 树效率更高。B+ 树性质如下：
  • N 棵子树的节点包含 N 个关键字，不用来保存数据而是保存数据的索引。
  • 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身根据关键字的大小自小而大顺序链接。
  • 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
  • B+ 树中，数据对象的插入和删除仅在叶节点上进行。
  • B+ 树有 2 个头指针，一个是树的根节点，一个是最小关键码的叶节点。

4.15 为什么索引结构默认使用 B+ 树，而不是 B 树、Hash、二叉树、红黑树？

• B 树：B+ 树只在叶子节点存储数据，分支结点均为索引，而 B 树的非叶子节点也要存储数据，所以 B+ 树的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。另外，B+ 树叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
• Hash：哈希索引基于哈希表实现，只有 Memory 存储引擎显式支持哈希索引，哈希索引在做等值查询（如 =、IN()、<=>）的时候效率贼快，搜索复杂度为常数级，但是哈希表没有顺序，I/O 复杂度高，不适合做范围查询，这也是 B+ 树索引要比哈希索引有着更广泛的适用场景的原因。此外因为哈希索引始终索引所有列的全部内容，所以不支持部分索引列的匹配查找，并且如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。
• 二叉树：树的高度不均匀，不能自平衡，且二叉树的每个父节点的儿子节点个数只能是 2 个，树的高度比 B+ 树高很多，由于查找效率跟树的高度相关，即使数据达到千万级别时，B+ 树的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据，比二叉树效率高不少。
• 红黑树：树的高度随着数据量增加而增加，I/O 代价高。

4.16 联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL 可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。具体原因为：

• MySQL 使用索引时需要索引有序，假设现在建立了 name, age, school 的联合索引，那么索引的排序为：先按照 name 排序，如果 name 相同，则按照 age 排序，如果 age 的值也相等，则按照 school 进行排序。
• 当进行查询时，此时索引仅仅按照 name 严格有序，因此必须首先使用 name 字段进行等值查询，之后对于匹配到的列而言，其按照 age 字段严格有序，此时可以使用 age 字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高（区分度大）的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

4.17 索引失效的情况有哪些？

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

4.18 MySQL 的最左前缀原则是什么？

最左前缀原则就是最左优先，当使用联合索引时，MySQL 只能从索引的最左列开始并向右连续使用索引列，不能跳过中间列，如果查询条件不包含联合索引的最左列，或中间出现断裂，则索引会部分失效（甚至完全失效）。此外如果某列使用了范围查询（>、<、BETWEEN、LIKE '%xx'），其右侧所有列的索引失效。

例如我们有以下表结构：

1
2
3
4
5
6
7

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_name_age_city (name, age, city)  -- 联合索引
);

通过几个例子来看看如何满足最左前缀原则的条件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- 有效：使用了索引的前两列（name + age）
SELECT * FROM users 
WHERE name = 'Bob' AND age = 30;

-- 失效：未使用最左列 name
SELECT * FROM users WHERE age = 25;

-- 失效：跳过了 age 列（索引断裂）
SELECT * FROM users 
WHERE name = 'David' AND city = 'Guangzhou';  -- age 列缺失

-- 部分有效：name 生效，age 范围查询导致 city 失效
SELECT * FROM users 
WHERE name = 'Eva' AND age > 20 AND city = 'Shenzhen';  -- city 无法用索引

-- 有效：查询列被索引覆盖（即使跳过 age）
SELECT name, city FROM users  -- 所有列都在索引中，触发覆盖索引
WHERE name = 'Grace' AND city = 'Wuhan';

-- 有效：排序遵循索引顺序
SELECT * FROM users 
WHERE name = 'Henry' 
ORDER BY age;  -- 索引第二列

-- 失效：排序未从最左开始
SELECT * FROM users 
WHERE name = 'Ivy' 
ORDER BY city;  -- 跳过了 age 列

4.19 前缀索引是什么？

因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是我们需要注意，ORDER BY 不支持前缀索引。

创建前缀索引的流程如下：

• 先计算完整列的选择性：SELECT COUNT(DISTINCT col_1)/COUNT(1) FROM table_1；
• 再计算不同前缀长度的选择性：SELECT COUNT(DISTINCT LEFT(col_1, 4))/COUNT(1) FROM table_1；
• 找到最优长度之后，创建前缀索引：CREATE INDEX idx_front ON table_1 (col_1(4))。

4.20 怎么决定建立哪些索引？

（1）什么时候适用索引？

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

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

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

5. 存储引擎

5.1 MySQL 执行查询的过程是什么？

1. 客户端通过 TCP 连接发送连接请求到 MySQL 连接器，连接器会对该请求进行权限验证及连接资源分配，建立并管理连接。
2. 查缓存：当判断缓存是否命中时，MySQL 不会进行解析查询语句，而是直接使用 SQL 语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。MySQL 8.0 已删除该模块。
3. 解析 SQL：通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型。
4. 执行 SQL：执行 SQL 共有三个阶段：
   • 预处理阶段：检查表或字段是否存在，将 SELECT * 中的 * 符号扩展为表上的所有列；
   • 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
   • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端。

更新语句执行会复杂一点，需要检查表是否有排它锁，写 binlog、刷盘、是否执行 commit。

5.2 MySQL 支持哪些存储引擎？为什么 InnoDB 是默认引擎？

MySQL 支持多种存储引擎，比如 InnoDB、MyISAM、Memory、Archive 等：

• InnoDB：InnoDB 是 MySQL 的默认存储引擎，具有 ACID 事务支持、行级锁、外键约束等特性。它适用于高并发的读写操作，支持较好的数据完整性和并发控制。
• MyISAM：MyISAM 是 MySQL 的另一种常见的存储引擎，具有较低的存储空间和内存消耗，适用于大量读操作的场景。然而，MyISAM 不支持事务、行级锁和外键约束，因此在并发写入和数据完整性方面有一定的限制。
• Memory：Memory 引擎将数据存储在内存中，适用于对性能要求较高的读操作，但是在服务器重启或崩溃时数据会丢失。它不支持事务、行级锁和外键约束。

在大多数的情况下，直接选择使用 InnoDB 引擎都是最合适的，因为 InnoDB 引擎在事务支持、并发性能、崩溃恢复等方面具有优势，所以 InnoDB 也是 MySQL 的默认存储引擎。

MyISAM 和 InnoDB 的区别有哪些：

• InnoDB 支持事务，可以进行 ACID（原子性、一致性、隔离性、持久性）属性的操作，MyISAM 不支持。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。
• MyISAM 只支持表级锁（table-level locking），锁的粒度比较大，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限；InnoDB 支持行级锁（row-level locking）和表级锁，默认为行级锁，可以提供更好的并发性能。
• InnoDB 引引擎通过 redolog 日志实现了崩溃恢复，可以在数据库发生异常情况（如断电）时，通过日志文件进行恢复，保证数据的持久性和一致性，MyISAM 是不支持崩溃恢复的。
• InnoDB 支持外键，MyISAM 不支持。
• InnoDB 是聚簇索引，数据文件是和索引绑在一起的，文件存放在主键索引的叶子节点上，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据，因此主键不应该过大，因为主键太大，其他索引也都会很大；MyISAM 是非聚簇索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
• InnoDB 不支持全文索引，MyISAM 支持全文索引，查询效率上 MyISAM 更高。
• InnoDB 不保存表的具体行数，执行 SELECT COUNT(*) FROM <table> 时需要全表扫描，MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。