MySQL 面试题集

总题数: 60道 | 重点领域: 索引、事务、锁机制 | 难度分布: 中高级

本文档整理了 MySQL 数据库的核心面试题目，涵盖索引原理、事务特性、锁机制、性能优化等各个方面。

面试题目列表

1. MySQL的存储引擎有哪些？它们有什么区别？

MySQL支持多种存储引擎，允许针对不同的应用场景选择最适合的存储引擎。主要的存储引擎包括：

InnoDB（默认存储引擎）：

特点：
- 支持事务，遵循ACID特性
- 支持行级锁，提高并发性能
- 支持外键约束
- 实现了四种隔离级别
- 采用MVCC(多版本并发控制)来支持高并发
- 使用聚簇索引存储数据
适用场景：
- 需要事务支持的应用
- 需要外键约束的应用
- 高并发、数据一致性要求高的场景

MyISAM：

特点：
- 不支持事务
- 表级锁定，并发性能较差
- 不支持外键
- 更简单的表结构
- 拥有较高的插入和查询速度
- 支持全文索引
适用场景：
- 读密集型应用（如Web应用的只读数据）
- 需要全文索引的应用
- 不需要事务支持的简单应用

Memory（HEAP）：

特点：
- 数据存储在内存中，速度极快
- 重启后数据丢失
- 表级锁
- 不支持BLOB和TEXT类型
适用场景：
- 临时表和中间结果的存储
- 需要快速访问且无需持久化的数据

Archive：

特点：
- 高度压缩存储
- 只支持INSERT和SELECT操作
- 不支持索引（除主键外）
适用场景：
- 日志和历史数据归档
- 需要高压缩率的数据

CSV：

特点：
- 以CSV格式存储数据
- 数据可直接被电子表格软件读取
适用场景：
- 数据导入导出
- 与其他应用程序交换数据

Blackhole：

特点：
- 接收数据但不存储
- 可用于主从复制或日志收集
适用场景：
- 主从复制中的中继
- 数据丢弃但需记录binlog

Federated：

特点：
- 访问远程MySQL服务器上的表
- 本地不存储数据
适用场景：
- 分布式数据库环境
- 多数据库整合

NDB Cluster：

特点：
- 分布式存储引擎
- 高可用性和可扩展性
适用场景：
- 高可用性集群环境

对比表：

特性	InnoDB	MyISAM	Memory	Archive
事务支持	是	否	否	否
锁粒度	行级锁	表级锁	表级锁	行级锁
外键支持	是	否	否	否
全文索引	支持(5.6+)	支持	否	否
存储限制	64TB	文件系统限制	内存大小	无
数据存储	聚簇索引	独立表	内存	压缩存储
性能优势	高并发写	高速读	极速查询	高压缩率
B树索引	支持	支持	支持	不支持
哈希索引	自适应	不支持	支持	不支持

如何选择存储引擎：

需要事务和外键：使用InnoDB
只读或读多写少：考虑MyISAM
临时数据，速度优先：Memory
归档数据，压缩优先：Archive

查看和修改存储引擎：

sql

1-- 查看支持的存储引擎
2SHOW ENGINES;
3
4-- 查看表的存储引擎
5SHOW TABLE STATUS WHERE Name = 'table_name';
6
7-- 创建表时指定存储引擎
8CREATE TABLE mytable (id INT) ENGINE = InnoDB;
9
10-- 修改表的存储引擎
11ALTER TABLE mytable ENGINE = MyISAM;

2. MySQL中的索引类型有哪些？如何选择合适的索引？

MySQL中的索引是提高查询效率的关键机制，根据不同的需求和数据特点，可以选择不同类型的索引。

主要索引类型：

1. 按数据结构分类：

B+树索引：
- MySQL中最常用的索引类型
- InnoDB和MyISAM默认使用
- 多路平衡查找树，叶节点存储数据
- 适合范围查询和排序
哈希索引：
- 基于哈希表实现，只有精确匹配才有效
- Memory存储引擎默认使用
- InnoDB支持自适应哈希索引
- 等值查询性能极高，但不支持范围查询和排序
R树索引：
- 用于空间数据索引（如地理信息）
- MyISAM支持空间索引
- 适用于多维数据的范围查询
全文索引：
- 用于全文搜索
- MyISAM和InnoDB(5.6+)支持
- 适用于文本内容的模糊查询

2. 按物理实现分类：

聚簇索引(Clustered Index)：
- 索引和数据存储在一起
- 每个表只能有一个（通常是主键）
- InnoDB默认使用
- 通过主键访问数据非常快
非聚簇索引(Non-Clustered Index)：
- 索引和数据分开存储
- 可以创建多个
- 查询需要额外的回表操作

3. 按索引列数分类：

单列索引：
- 只包含一个列的索引
- 适合单列查询条件
联合索引/复合索引：
- 包含多个列的索引
- 遵循最左前缀原则
- 适合多条件查询和覆盖索引查询

4. 按应用场景分类：

普通索引：
- 基本索引类型，无特殊限制
唯一索引：
- 要求索引列值唯一
- 可以包含NULL值(MySQL中NULL不等于NULL)
主键索引：
- 特殊的唯一索引，不允许NULL
- 用于唯一标识表中的记录
前缀索引：
- 对字符串列的前几个字符建立索引
- 减少索引空间占用
覆盖索引：
- 查询的列都包含在索引中，无需回表
- 可显著提高查询效率

选择合适索引的原则：

1. 列的选择：

在WHERE、JOIN、ORDER BY、GROUP BY子句中频繁出现的列
区分度高（基数大）的列优先
更新频率低的列优先
尽量选择数据类型较小的列

2. 索引类型选择：

等值查询多：考虑哈希索引或B+树
范围查询多：使用B+树索引
文本搜索：使用全文索引
多条件查询：考虑复合索引
唯一性约束：使用唯一索引或主键

3. 复合索引设计：

把区分度高的列放在前面
考虑查询条件的顺序
遵循最左前缀原则

4. 索引优化策略：

覆盖索引：尽量使用索引覆盖查询
索引下推：利用索引过滤更多数据
前缀索引：对长字符串使用前缀索引
避免过度索引：索引会占用空间并影响写性能

5. 常见索引陷阱：

在低基数列上建立索引可能无效
索引列上使用函数会导致索引失效
隐式类型转换会导致索引失效
Like以通配符开头会导致索引失效
Or条件可能导致索引失效

实际应用示例：

sql

1-- 创建普通索引
2CREATE INDEX idx_name ON users(name);
3
4-- 创建唯一索引
5CREATE UNIQUE INDEX idx_email ON users(email);
6
7-- 创建复合索引
8CREATE INDEX idx_name_age ON users(name, age);
9
10-- 创建前缀索引
11CREATE INDEX idx_address ON users(address(10));
12
13-- 创建全文索引
14CREATE FULLTEXT INDEX idx_content ON articles(content);
15
16-- 分析索引使用情况
17EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 20;

3. 什么是回表查询？什么是覆盖索引？

回表查询和覆盖索引是理解MySQL索引使用效率的两个重要概念，它们与查询执行过程和性能息息相关。

回表查询：

定义：回表查询是指在使用非聚簇索引(也称为二级索引或辅助索引)进行查询时，需要先通过索引找到对应的主键值，然后再根据主键值到聚簇索引中查找完整的行数据的过程。

产生原因：

在InnoDB存储引擎中，非聚簇索引的叶子节点存储的是该索引列的值和对应的主键值
而不是完整的行数据(这点与聚簇索引不同)
因此需要额外的步骤"回表"查找完整数据

工作流程：

通过非聚簇索引定位到对应的叶子节点，获取主键值
使用获取到的主键值在聚簇索引中进行第二次查找
从聚簇索引中获取完整的行数据

示例：

sql

1-- 假设在name列上有索引，id是主键
2SELECT * FROM users WHERE name = 'John';

执行过程：

通过name索引找到'John'对应的记录，获取主键id值(例如id=10)
再通过主键索引查找id=10的完整行数据
这个过程就是"回表"

性能影响：

回表会增加额外的IO操作
大量回表操作会显著降低查询效率
特别是在数据量大的表上更为明显

覆盖索引：

定义：覆盖索引是指查询的所有列都包含在索引中，数据库可以直接从索引中获取所需数据，而无需回表查询的情况。

工作原理：

当索引包含查询所需的所有列时，可以直接从索引获取数据
MySQL可以在查询执行计划中通过"Using index"标识覆盖索引的使用

示例：

sql

1-- 假设有一个联合索引(name, age)
2SELECT name, age FROM users WHERE name = 'John';

执行过程：

通过name索引找到'John'对应的记录
由于索引中已包含name和age列，直接返回结果
无需回表查询

覆盖索引的优势：

减少IO操作，避免回表
索引通常比表数据更小，可以减少数据扫描量
提高缓存效率，更多索引可以加载到内存
显著提升查询性能

如何实现覆盖索引：

创建包含查询所需所有列的联合索引
调整查询，只选择索引中包含的列
合理设计索引以支持频繁查询

sql

1-- 创建覆盖常见查询需求的索引
2CREATE INDEX idx_name_age_email ON users(name, age, email);
3
4-- 现在可以覆盖以下查询
5SELECT name, age, email FROM users WHERE name = 'John';

两者对比：

特性	回表查询	覆盖索引
IO次数	至少两次(索引+数据)	一次(仅索引)
性能	较慢	较快
索引设计	普通单列索引足够	需要精心设计包含所需列
内存占用	索引较小	索引可能较大
适用场景	通用查询	针对特定查询优化
EXPLAIN特征	NULL(ref列)	Using index

实际应用中的优化策略：

识别频繁查询的列组合，为其创建覆盖索引
避免使用SELECT *，只查询必要的列
合理使用联合索引以支持更多的覆盖查询
针对大表和高频查询优先考虑覆盖索引优化

4. MySQL的事务特性（ACID）是什么？

MySQL事务的ACID特性是关系数据库管理系统保证数据完整性和可靠性的基础。ACID是四个特性的首字母缩写：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。InnoDB存储引擎完全支持ACID特性，而MyISAM等非事务存储引擎则不支持。

1. 原子性(Atomicity)：

定义：事务是不可分割的工作单位，事务中的操作要么全部完成，要么全部不执行。

实现机制：

基于重做日志(redo log)和回滚日志(undo log)
操作执行前，先写入回滚日志，记录修改前的数据
如果事务失败，使用回滚日志恢复到事务开始前的状态

示例：

sql

1START TRANSACTION;
2UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
3UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
4COMMIT;  -- 两个更新操作同时成功
5
6-- 或者
7ROLLBACK;  -- 两个更新操作都不执行

2. 一致性(Consistency)：

定义：事务执行前后，数据库从一个一致性状态转变为另一个一致性状态，不会破坏数据库的完整性约束。

实现机制：

通过完整性约束（如主键、外键、唯一性约束）
通过触发器和存储过程中的业务逻辑
应用程序层面的业务规则

示例：

sql

1-- 银行转账保持总金额不变
2START TRANSACTION;
3UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
4UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
5COMMIT;  -- 总金额保持不变，一致性得到保障

3. 隔离性(Isolation)：

定义：多个事务并发执行时，一个事务的执行不应该被其他事务干扰，就像它们是串行执行一样。

实现机制：

通过锁机制和**MVCC(多版本并发控制)**实现
不同的隔离级别提供不同程度的隔离性
- READ UNCOMMITTED（读未提交）
- READ COMMITTED（读已提交）
- REPEATABLE READ（可重复读，InnoDB默认）
- SERIALIZABLE（串行化）

示例：

sql

1-- 设置事务隔离级别
2SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3
4-- 事务A
5START TRANSACTION;
6SELECT * FROM accounts WHERE user_id = 1;  -- 读取时不受其他事务影响
7-- 中间其他事务可能修改了数据
8SELECT * FROM accounts WHERE user_id = 1;  -- 在可重复读级别下，读到的数据与第一次相同
9COMMIT;

4. 持久性(Durability)：

定义：一旦事务提交，其所做的修改就会永久保存在数据库中，即使系统崩溃也不会丢失。

实现机制：

通过**重做日志(redo log)**实现
事务提交时，确保所有修改都已写入重做日志
系统崩溃后，通过重做日志恢复未写入磁盘的数据

示例：

sql

1START TRANSACTION;
2UPDATE important_data SET value = 'new_value' WHERE id = 1;
3COMMIT;  -- 数据永久保存，即使立即发生服务器崩溃

MySQL InnoDB的ACID实现机制：

1. 原子性和持久性实现：

重做日志(redo log)：记录事务修改的页面数据，用于恢复已提交事务的数据
回滚段(undo log)：记录数据被修改前的值，用于回滚未提交的事务
两阶段提交：先写重做日志，再修改数据页
组提交：多个事务的日志一起刷入磁盘，提高性能

2. 一致性实现：

强制约束：主键、外键、唯一性约束等
回滚机制：当违反约束时回滚事务
崩溃恢复：服务器崩溃后自动恢复到一致状态

3. 隔离性实现：

锁机制：表锁、行锁、意向锁、间隙锁等
MVCC：通过回滚段实现的多版本并发控制
事务ID和版本号：每个事务有唯一ID，每次修改生成新版本
一致性读视图：事务开始时确定可见的版本范围

事务相关配置：

sql

1-- 查看当前会话隔离级别
2SELECT @@transaction_isolation;
3
4-- 查看自动提交状态
5SELECT @@autocommit;
6
7-- 设置自动提交
8SET autocommit = 1;  -- 开启
9SET autocommit = 0;  -- 关闭
10
11-- 手动控制事务
12START TRANSACTION;
13-- 执行SQL语句
14COMMIT;  -- 或 ROLLBACK;

事务的性能影响：

更高的隔离级别通常意味着更低的并发性能
事务日志会增加I/O操作
长事务会占用系统资源，应避免
合理设置隔离级别可以平衡一致性和性能

5. MySQL的四种隔离级别是什么？分别会产生什么问题？

MySQL的四种事务隔离级别定义了在多个事务并发执行时，一个事务的操作对其他事务的可见程度。不同的隔离级别解决了不同的并发问题，但也带来了性能和功能上的权衡。

1. 读未提交(READ UNCOMMITTED)：

定义：一个事务可以读取另一个未提交事务的数据。

特点：

最低的隔离级别，提供最高的并发性
不使用锁机制，不阻塞其他事务
可能读取到其他事务未提交的"脏"数据

存在的问题：

脏读(Dirty Read)：事务A读取了事务B修改但未提交的数据，如果事务B回滚，则事务A读取的数据是无效的
不可重复读(Non-repeatable Read)：同一事务内，多次读取同一数据返回的结果不同
幻读(Phantom Read)：同一事务内，同样的查询条件返回不同的结果集

示例：

sql

1-- 会话1
2SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
3START TRANSACTION;
4-- 此时会看到会话2未提交的修改
5SELECT * FROM accounts WHERE id = 1;
6COMMIT;
7
8-- 会话2
9START TRANSACTION;
10UPDATE accounts SET balance = 2000 WHERE id = 1;
11-- 此时还未提交

2. 读已提交(READ COMMITTED)：

定义：一个事务只能读取另一个已提交事务的数据。

特点：

使用行级锁和MVCC机制
每次查询都生成一个新的快照
只能读取到已提交的数据，解决了脏读问题
大多数数据库的默认隔离级别(但不是MySQL InnoDB)

存在的问题：

不可重复读(Non-repeatable Read)：同一事务内，多次读取同一数据返回的结果不同
幻读(Phantom Read)：同一事务内，同样的查询条件返回不同的结果集

示例：

sql

1-- 会话1
2SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3START TRANSACTION;
4SELECT * FROM accounts WHERE id = 1;  -- balance=1000
5-- 会话2执行更新并提交后
6SELECT * FROM accounts WHERE id = 1;  -- balance=2000，出现不可重复读
7COMMIT;
8
9-- 会话2
10START TRANSACTION;
11UPDATE accounts SET balance = 2000 WHERE id = 1;
12COMMIT;  -- 提交事务

3. 可重复读(REPEATABLE READ)：

定义：一个事务执行过程中看到的数据始终是一致的，无论其他事务对数据做了什么修改。

特点：

MySQL InnoDB的默认隔离级别
使用行级锁和MVCC机制
事务开始时创建一个快照，整个事务期间都使用该快照
解决了脏读和不可重复读问题

存在的问题：

幻读(Phantom Read)：理论上会存在，但InnoDB通过间隙锁(Gap Lock)解决了大部分幻读问题
间隙锁可能导致死锁几率增加

示例：

sql

1-- 会话1
2SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3START TRANSACTION;
4SELECT * FROM accounts WHERE id = 1;  -- balance=1000
5-- 会话2执行更新并提交后
6SELECT * FROM accounts WHERE id = 1;  -- balance仍为1000，解决了不可重复读
7COMMIT;
8
9-- 会话2
10START TRANSACTION;
11UPDATE accounts SET balance = 2000 WHERE id = 1;
12COMMIT;

4. 串行化(SERIALIZABLE)：

定义：最高的隔离级别，强制事务串行执行，就像它们是一个接一个执行的一样。

特点：

使用表级读写锁或行级锁
读取数据时加共享锁，其他事务无法修改这些数据
写入数据时加排他锁，其他事务无法读取或修改这些数据
可能导致大量阻塞和超时
解决了所有并发问题

解决的问题：

完全解决了脏读、不可重复读和幻读问题

示例：

sql

1-- 会话1
2SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3START TRANSACTION;
4SELECT * FROM accounts WHERE balance > 1000;  -- 会对符合条件的行加锁
5-- 此时会话2无法插入新符合条件的记录，避免了幻读
6COMMIT;
7
8-- 会话2
9START TRANSACTION;
10INSERT INTO accounts VALUES(3, 'User3', 1500);  -- 会被阻塞直到会话1提交或超时
11COMMIT;

四种隔离级别对比：

隔离级别	脏读	不可重复读	幻读	锁机制	性能
读未提交	可能	可能	可能	最少	最高
读已提交	避免	可能	可能	较少	高
可重复读	避免	避免	基本避免*	较多	中等
串行化	避免	避免	避免	最多	最低

*注：InnoDB在可重复读级别下使用间隙锁解决了大部分幻读问题

MySQL中查看和设置隔离级别：

sql

1-- 查看全局隔离级别
2SELECT @@GLOBAL.transaction_isolation;
3
4-- 查看当前会话隔离级别
5SELECT @@SESSION.transaction_isolation;
6
7-- 设置全局隔离级别
8SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
9
10-- 设置当前会话隔离级别
11SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

实际应用中的选择：

读未提交：几乎不使用，除非对数据一致性没有要求
读已提交：适合大多数Web应用，减少锁竞争
可重复读：适合对数据一致性要求较高的场景，如金融系统
串行化：只适用于事务量极低但对一致性要求极高的场景

InnoDB特殊处理：

InnoDB在可重复读隔离级别下，通过Next-Key Lock(记录锁+间隙锁)解决了大部分幻读问题
这使得InnoDB的可重复读隔离级别比标准SQL规范中的定义更为严格

6. 什么是MVCC？它是如何工作的？

**MVCC(Multi-Version Concurrency Control，多版本并发控制)**是InnoDB实现高并发的核心机制，它通过保存数据的多个版本来实现读写不阻塞。

核心概念：

1. 版本链：

每行数据都有两个隐藏列：DB_TRX_ID(事务ID)和DB_ROLL_PTR(回滚指针)
DB_TRX_ID：记录最后修改该行的事务ID
DB_ROLL_PTR：指向undo log中该行的上一个版本
通过回滚指针形成版本链

2. Read View(读视图)：

事务开始时创建的一致性视图
包含当前活跃事务列表
决定当前事务能看到哪些版本的数据

工作原理：

1当前数据行：
2id | name | age | DB_TRX_ID | DB_ROLL_PTR
31  | Tom  | 25  | 100       | ptr1
4
5版本链(undo log):
6ptr1 -> (name=John, age=20, TRX_ID=80)
7     -> (name=Mike, age=18, TRX_ID=60)

Read View判断规则：

sql

1-- Read View包含：
2- m_ids: 当前活跃事务ID列表
3- min_trx_id: 最小活跃事务ID
4- max_trx_id: 下一个要分配的事务ID
5- creator_trx_id: 创建该Read View的事务ID
6
7-- 可见性判断：
81. 如果 DB_TRX_ID < min_trx_id：可见(已提交)
92. 如果 DB_TRX_ID >= max_trx_id：不可见(未来事务)
103. 如果 min_trx_id <= DB_TRX_ID < max_trx_id：
11   - 如果 DB_TRX_ID 在 m_ids 中：不可见(未提交)
12   - 否则：可见(已提交)

不同隔离级别的MVCC实现：

READ COMMITTED：

每次查询都创建新的Read View
能读到其他事务已提交的修改

REPEATABLE READ：

事务开始时创建Read View，整个事务期间使用同一个
保证可重复读

示例：

sql

1-- 事务A (TRX_ID=100)
2START TRANSACTION;
3SELECT * FROM users WHERE id = 1;  -- age=20
4
5-- 事务B (TRX_ID=101)
6START TRANSACTION;
7UPDATE users SET age = 25 WHERE id = 1;
8COMMIT;
9
10-- 事务A继续
11SELECT * FROM users WHERE id = 1;  
12-- RC级别：age=25 (看到新版本)
13-- RR级别：age=20 (看到旧版本)

MVCC的优势：

读不加锁，写不阻塞读
提高并发性能
实现一致性非锁定读

MVCC的局限：

只在RC和RR隔离级别下工作
不能完全避免幻读(需要配合间隙锁)
undo log可能占用大量空间

7. MySQL中的锁有哪些类型？

MySQL的锁机制用于控制并发访问，保证数据一致性。根据不同的分类维度，MySQL有多种锁类型。

按锁粒度分类：

1. 表级锁(Table Lock)：

锁定整张表
开销小，加锁快
锁粒度大，并发度低
MyISAM默认使用

sql

1-- 手动加表锁
2LOCK TABLES users READ;   -- 读锁
3LOCK TABLES users WRITE;  -- 写锁
4UNLOCK TABLES;

2. 行级锁(Row Lock)：

锁定单行数据
开销大，加锁慢
锁粒度小，并发度高
InnoDB默认使用

3. 页级锁(Page Lock)：

锁定数据页
介于表锁和行锁之间
BDB存储引擎使用

按锁类型分类：

1. 共享锁(S Lock，读锁)：

允许多个事务同时读取
阻止其他事务写入

sql

1SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

2. 排他锁(X Lock，写锁)：

阻止其他事务读取和写入
只有持锁事务可以访问

sql

1SELECT * FROM users WHERE id = 1 FOR UPDATE;

InnoDB特有锁：

1. 记录锁(Record Lock)：

锁定索引记录
防止其他事务修改该行

2. 间隙锁(Gap Lock)：

锁定索引记录之间的间隙
防止其他事务插入数据
解决幻读问题

3. Next-Key Lock：

记录锁 + 间隙锁
锁定记录及其前面的间隙
RR隔离级别默认使用

sql

1-- 假设id有索引，值为1,5,10
2SELECT * FROM users WHERE id > 5 FOR UPDATE;
3-- 锁定：(5,10]和(10,+∞)

4. 插入意向锁(Insert Intention Lock)：

插入前获取的特殊间隙锁
多个事务可以同时持有不同位置的插入意向锁

5. 自增锁(AUTO-INC Lock)：

用于自增列
保证自增值的连续性

按锁模式分类：

1. 意向锁(Intention Lock)：

表级锁，用于协调表锁和行锁
意向共享锁(IS)：事务想获取行的共享锁
意向排他锁(IX)：事务想获取行的排他锁

锁兼容性矩阵：

	X	IX	S	IS
X	✗	✗	✗	✗
IX	✗	✓	✗	✓
S	✗	✗	✓	✓
IS	✗	✓	✓	✓

查看锁信息：

sql

1-- 查看当前锁
2SHOW ENGINE INNODB STATUS;
3
4-- 查看锁等待
5SELECT * FROM information_schema.INNODB_LOCKS;
6
7-- 查看锁等待关系
8SELECT * FROM information_schema.INNODB_LOCK_WAITS;

8. 什么是死锁？如何避免死锁？

死锁是指两个或多个事务互相持有对方需要的锁，导致所有事务都无法继续执行的情况。

死锁示例：

sql

1-- 事务A
2START TRANSACTION;
3UPDATE users SET name='A' WHERE id=1;  -- 获取id=1的锁
4-- 等待获取id=2的锁
5UPDATE users SET name='A' WHERE id=2;
6
7-- 事务B
8START TRANSACTION;
9UPDATE users SET name='B' WHERE id=2;  -- 获取id=2的锁
10-- 等待获取id=1的锁
11UPDATE users SET name='B' WHERE id=1;
12
13-- 形成死锁：A等B释放id=2，B等A释放id=1

死锁的四个必要条件：

互斥：资源不能被共享
持有并等待：持有资源的同时等待其他资源
不可剥夺：资源不能被强制释放
循环等待：形成资源等待环路

MySQL的死锁检测：

InnoDB自动检测死锁
选择回滚代价最小的事务
其他事务可以继续执行

避免死锁的策略：

1. 按相同顺序访问资源：

sql

1-- 好的做法：统一按id升序访问
2UPDATE users SET name='X' WHERE id IN (1,2,3) ORDER BY id;
3
4-- 避免：不同事务以不同顺序访问

2. 缩小事务范围：

sql

1-- 不好：事务时间过长
2START TRANSACTION;
3SELECT * FROM users;  -- 大量数据
4-- 复杂业务逻辑
5UPDATE users SET status=1;
6COMMIT;
7
8-- 好：只在必要时使用事务
9-- 先查询数据
10SELECT * FROM users;
11-- 业务逻辑处理
12START TRANSACTION;
13UPDATE users SET status=1;
14COMMIT;

3. 降低隔离级别：

sql

1-- 从RR降低到RC，减少间隙锁
2SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

4. 添加合理的索引：

减少锁定的行数
避免全表扫描

5. 使用较小的事务：

减少锁持有时间
降低死锁概率

6. 设置锁等待超时：

sql

1-- 设置锁等待超时时间(秒)
2SET innodb_lock_wait_timeout = 50;

7. 使用乐观锁：

sql

1-- 使用版本号
2UPDATE users SET balance=balance-100, version=version+1 
3WHERE id=1 AND version=10;

处理死锁：

sql

1-- 查看最近的死锁信息
2SHOW ENGINE INNODB STATUS;
3
4-- 在应用层捕获死锁异常并重试
5try {
6    // 执行事务
7} catch (DeadlockException e) {
8    // 重试逻辑
9    retry();
10}

9. MySQL的主从复制原理是什么？

MySQL主从复制是实现数据库高可用、读写分离和数据备份的基础技术。

复制架构：

1Master(主库) --binlog--> Slave(从库)
2    |                        |
3  写操作                   读操作

复制原理(三个线程)：

1. Master的Binlog Dump线程：

读取binlog日志
发送给Slave的I/O线程

2. Slave的I/O线程：

连接到Master
请求binlog日志
写入本地relay log(中继日志)

3. Slave的SQL线程：

读取relay log
解析并执行SQL语句
更新从库数据

复制过程：

11. Master执行SQL -> 写入binlog
22. Slave I/O线程 -> 请求binlog
33. Master Dump线程 -> 发送binlog
44. Slave I/O线程 -> 写入relay log
55. Slave SQL线程 -> 读取relay log
66. Slave SQL线程 -> 执行SQL更新数据

配置主从复制：

Master配置：

sql

1-- my.cnf
2[mysqld]
3server-id=1
4log-bin=mysql-bin
5binlog-format=ROW
6
7-- 创建复制用户
8CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
9GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
10
11-- 查看master状态
12SHOW MASTER STATUS;

Slave配置：

sql

1-- my.cnf
2[mysqld]
3server-id=2
4relay-log=relay-bin
5read-only=1
6
7-- 配置主库信息
8CHANGE MASTER TO
9  MASTER_HOST='master_ip',
10  MASTER_USER='repl',
11  MASTER_PASSWORD='password',
12  MASTER_LOG_FILE='mysql-bin.000001',
13  MASTER_LOG_POS=154;
14
15-- 启动复制
16START SLAVE;
17
18-- 查看slave状态
19SHOW SLAVE STATUS\G

复制模式：

1. 异步复制(默认)：

Master不等待Slave确认
性能最好，但可能丢数据

2. 半同步复制：

Master等待至少一个Slave确认
平衡性能和数据安全

3. 全同步复制：

Master等待所有Slave确认
数据最安全，但性能最差

binlog格式：

1. Statement(语句)：

记录SQL语句
日志量小，但可能不一致

2. Row(行)：

记录每行的变化
日志量大，但完全一致

3. Mixed(混合)：

自动选择Statement或Row

常见问题：

1. 主从延迟：

原因：从库性能不足、网络延迟、大事务
解决：并行复制、优化SQL、增加从库

2. 数据不一致：

原因：从库写入、binlog格式问题
解决：设置read-only、使用Row格式

3. 复制中断：

原因：网络问题、SQL执行失败
解决：跳过错误、重建复制

10. 如何优化MySQL查询性能？

MySQL查询优化是一个系统工程，需要从多个维度进行优化。

1. 索引优化：

创建合适的索引：

sql

1-- 为WHERE条件列创建索引
2CREATE INDEX idx_status ON orders(status);
3
4-- 为JOIN列创建索引
5CREATE INDEX idx_user_id ON orders(user_id);
6
7-- 创建覆盖索引
8CREATE INDEX idx_cover ON orders(user_id, status, create_time);

避免索引失效：

sql

1-- 不要在索引列上使用函数
2-- 错误
3SELECT * FROM users WHERE YEAR(create_time) = 2024;
4-- 正确
5SELECT * FROM users WHERE create_time >= '2024-01-01' 
6  AND create_time < '2025-01-01';
7
8-- 避免隐式类型转换
9-- 错误(id是int类型)
10SELECT * FROM users WHERE id = '123';
11-- 正确
12SELECT * FROM users WHERE id = 123;
13
14-- Like不要以%开头
15-- 错误
16SELECT * FROM users WHERE name LIKE '%john%';
17-- 正确
18SELECT * FROM users WHERE name LIKE 'john%';

2. SQL语句优化：

**避免SELECT ***：

sql

1-- 不好
2SELECT * FROM users WHERE id = 1;
3
4-- 好
5SELECT id, name, email FROM users WHERE id = 1;

使用LIMIT：

sql

1-- 限制返回行数
2SELECT * FROM users WHERE status = 1 LIMIT 100;

优化子查询：

sql

1-- 不好：子查询
2SELECT * FROM orders WHERE user_id IN (
3  SELECT id FROM users WHERE status = 1
4);
5
6-- 好：JOIN
7SELECT o.* FROM orders o
8INNER JOIN users u ON o.user_id = u.id
9WHERE u.status = 1;

3. 表结构优化：

选择合适的数据类型：

sql

1-- 使用最小的数据类型
2age TINYINT UNSIGNED  -- 而不是INT
3
4-- 使用ENUM代替字符串
5status ENUM('active', 'inactive', 'pending')
6
7-- 固定长度字符串用CHAR
8country_code CHAR(2)  -- 而不是VARCHAR(2)

垂直分表：

sql

1-- 将大字段分离
2-- users表：id, name, email
3-- user_profiles表：user_id, bio, avatar

4. 查询缓存优化：

sql

1-- 启用查询缓存(MySQL 5.7及以前)
2SET GLOBAL query_cache_type = ON;
3SET GLOBAL query_cache_size = 268435456;  -- 256MB
4
5-- 注意：MySQL 8.0已移除查询缓存

5. 配置参数优化：

sql

1-- 增加缓冲池大小
2innodb_buffer_pool_size = 8G
3
4-- 调整日志文件大小
5innodb_log_file_size = 512M
6
7-- 增加连接数
8max_connections = 1000
9
10-- 调整线程缓存
11thread_cache_size = 64

6. 硬件和架构优化：

使用SSD硬盘
增加内存
读写分离
分库分表

7. 使用EXPLAIN分析：

sql

1EXPLAIN SELECT * FROM users WHERE name = 'John';
2
3-- 关注：
4-- type: ALL(全表扫描)需要优化
5-- key: NULL表示未使用索引
6-- rows: 扫描行数，越少越好
7-- Extra: Using filesort/Using temporary需要优化

8. 慢查询优化：

sql

1-- 开启慢查询日志
2SET GLOBAL slow_query_log = ON;
3SET GLOBAL long_query_time = 2;  -- 2秒
4
5-- 分析慢查询
6mysqldumpslow /var/log/mysql/slow.log

11. 什么是慢查询日志？如何使用？

慢查询日志记录执行时间超过指定阈值的SQL语句，是定位性能问题的重要工具。

配置慢查询日志：

sql

1-- 查看慢查询配置
2SHOW VARIABLES LIKE 'slow_query%';
3SHOW VARIABLES LIKE 'long_query_time';
4
5-- 开启慢查询日志
6SET GLOBAL slow_query_log = ON;
7
8-- 设置慢查询阈值(秒)
9SET GLOBAL long_query_time = 2;
10
11-- 设置日志文件路径
12SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
13
14-- 记录未使用索引的查询
15SET GLOBAL log_queries_not_using_indexes = ON;

my.cnf配置：

ini

1[mysqld]
2slow_query_log = 1
3slow_query_log_file = /var/log/mysql/slow.log
4long_query_time = 2
5log_queries_not_using_indexes = 1

分析慢查询日志：

bash

1# 使用mysqldumpslow分析
2mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
3# -s t: 按查询时间排序
4# -t 10: 显示前10条
5
6# 常用选项
7mysqldumpslow -s c -t 10 slow.log  # 按查询次数排序
8mysqldumpslow -s r -t 10 slow.log  # 按返回记录数排序
9mysqldumpslow -s al -t 10 slow.log # 按平均锁时间排序

使用pt-query-digest分析：

bash

1# 更强大的分析工具
2pt-query-digest /var/log/mysql/slow.log > report.txt

12. Explain执行计划如何分析？

EXPLAIN显示MySQL如何执行查询，是SQL优化的核心工具。

基本用法：

sql

1EXPLAIN SELECT * FROM users WHERE name = 'John';

输出字段解析：

1. id：

查询的序列号
id相同：执行顺序从上到下
id不同：id越大越先执行

2. select_type：

SIMPLE：简单查询
PRIMARY：最外层查询
SUBQUERY：子查询
DERIVED：派生表
UNION：UNION查询

3. table：

访问的表名

4. type（重要）：访问类型，性能从好到差：

system：表只有一行
const：主键或唯一索引查询
eq_ref：唯一索引扫描
ref：非唯一索引扫描
range：范围扫描
index：索引全扫描
ALL：全表扫描（需要优化）

5. possible_keys：

可能使用的索引

6. key：

实际使用的索引
NULL表示未使用索引

7. key_len：

使用的索引长度
越短越好

8. ref：

与索引比较的列或常量

9. rows：

预计扫描的行数
越少越好

10. Extra（重要）：

Using index：使用覆盖索引（好）
Using where：使用WHERE过滤
Using filesort：需要额外排序（需优化）
Using temporary：使用临时表（需优化）
Using index condition：索引下推

优化示例：

sql

1-- 差的执行计划
2EXPLAIN SELECT * FROM orders WHERE status = 1;
3-- type: ALL, Extra: Using where
4
5-- 优化：添加索引
6CREATE INDEX idx_status ON orders(status);
7-- type: ref, key: idx_status
8
9-- 更好：使用覆盖索引
10CREATE INDEX idx_cover ON orders(status, order_no, amount);
11SELECT status, order_no, amount FROM orders WHERE status = 1;
12-- Extra: Using index

13. MySQL的分区表是什么？

分区表将一个大表的数据分散存储到多个物理分区中，提高查询性能和管理效率。

分区类型：

1. RANGE分区：按范围分区

sql

1CREATE TABLE orders (
2    id INT,
3    order_date DATE,
4    amount DECIMAL(10,2)
5) PARTITION BY RANGE (YEAR(order_date)) (
6    PARTITION p2020 VALUES LESS THAN (2021),
7    PARTITION p2021 VALUES LESS THAN (2022),
8    PARTITION p2022 VALUES LESS THAN (2023),
9    PARTITION p2023 VALUES LESS THAN (2024),
10    PARTITION p_future VALUES LESS THAN MAXVALUE
11);

2. LIST分区：按列表值分区

sql

1CREATE TABLE users (
2    id INT,
3    region VARCHAR(20)
4) PARTITION BY LIST COLUMNS(region) (
5    PARTITION p_north VALUES IN ('Beijing', 'Tianjin'),
6    PARTITION p_south VALUES IN ('Shanghai', 'Guangzhou'),
7    PARTITION p_west VALUES IN ('Chengdu', 'Chongqing')
8);

3. HASH分区：按哈希值分区

sql

1CREATE TABLE logs (
2    id INT,
3    log_time DATETIME
4) PARTITION BY HASH(YEAR(log_time))
5PARTITIONS 4;

4. KEY分区：类似HASH，使用MySQL提供的哈希函数

sql

1CREATE TABLE sessions (
2    id INT,
3    user_id INT
4) PARTITION BY KEY(user_id)
5PARTITIONS 4;

分区管理：

sql

1-- 查看分区信息
2SELECT * FROM information_schema.PARTITIONS 
3WHERE TABLE_NAME = 'orders';
4
5-- 添加分区
6ALTER TABLE orders ADD PARTITION (
7    PARTITION p2024 VALUES LESS THAN (2025)
8);
9
10-- 删除分区
11ALTER TABLE orders DROP PARTITION p2020;
12
13-- 重组分区
14ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
15    PARTITION p2024 VALUES LESS THAN (2025),
16    PARTITION p_future VALUES LESS THAN MAXVALUE
17);

分区的优势：

提高查询性能（分区裁剪）
便于数据管理（按分区删除）
提高并发性能
便于数据归档

注意事项：

分区键必须是主键或唯一键的一部分
最多支持8192个分区
分区表不支持外键

14. 什么是表锁和行锁？

表锁和行锁是MySQL中两种不同粒度的锁机制。

表锁(Table Lock)：

特点：

锁定整张表
开销小，加锁快
不会出现死锁
锁粒度大，并发度低
MyISAM默认使用

类型：

sql

1-- 读锁(共享锁)
2LOCK TABLES users READ;
3-- 当前会话和其他会话都可以读，但不能写
4
5-- 写锁(排他锁)
6LOCK TABLES users WRITE;
7-- 只有当前会话可以读写，其他会话被阻塞
8
9-- 释放锁
10UNLOCK TABLES;

行锁(Row Lock)：

特点：

锁定单行或多行
开销大，加锁慢
可能出现死锁
锁粒度小，并发度高
InnoDB默认使用

类型：

sql

1-- 共享锁(S锁)
2SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
3-- 允许其他事务读取，但不能修改
4
5-- 排他锁(X锁)
6SELECT * FROM users WHERE id = 1 FOR UPDATE;
7-- 其他事务不能读取和修改
8
9-- UPDATE/DELETE自动加排他锁
10UPDATE users SET name = 'John' WHERE id = 1;

对比：

特性	表锁	行锁
锁粒度	整表	单行
并发性	低	高
死锁	不会	可能
开销	小	大
适用场景	批量操作	高并发
存储引擎	MyISAM	InnoDB

InnoDB的行锁实现：

基于索引实现
如果没有索引，会升级为表锁
锁定的是索引记录，不是数据行

示例：

sql

1-- 有索引：行锁
2UPDATE users SET name = 'John' WHERE id = 1;  -- 只锁id=1
3
4-- 无索引：表锁
5UPDATE users SET name = 'John' WHERE email = 'a@b.com';  -- 锁全表

15. InnoDB的Buffer Pool是什么？

Buffer Pool是InnoDB最重要的内存结构，用于缓存数据和索引页。

作用：

缓存数据页和索引页
减少磁盘I/O
提高查询性能
缓冲写入操作

结构：

1Buffer Pool
2├── 数据页缓存
3├── 索引页缓存
4├── 插入缓冲(Insert Buffer)
5├── 自适应哈希索引
6├── 锁信息
7└── 数据字典信息

页面管理：

默认页大小：16KB
使用LRU算法管理
分为young区和old区

LRU链表：

1New Page -> young区(5/8) -> old区(3/8) -> 淘汰

配置：

sql

1-- 查看Buffer Pool大小
2SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
3
4-- 设置Buffer Pool大小(建议物理内存的50-80%)
5SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8GB
6
7-- Buffer Pool实例数
8SET GLOBAL innodb_buffer_pool_instances = 8;
9
10-- 查看Buffer Pool状态
11SHOW ENGINE INNODB STATUS;

监控指标：

sql

1-- 查看Buffer Pool使用情况
2SELECT 
3  POOL_ID,
4  POOL_SIZE,
5  FREE_BUFFERS,
6  DATABASE_PAGES
7FROM information_schema.INNODB_BUFFER_POOL_STATS;
8
9-- 命中率
10SHOW STATUS LIKE 'Innodb_buffer_pool%';
11-- 关注：
12-- Innodb_buffer_pool_read_requests: 读请求数
13-- Innodb_buffer_pool_reads: 从磁盘读取次数
14-- 命中率 = 1 - (reads / read_requests)

优化建议：

设置合适的大小（物理内存的50-80%）
使用多个Buffer Pool实例减少竞争
预热Buffer Pool（启动时加载热数据）
监控命中率（应>99%）

16. 什么是binlog、redo log和undo log？

MySQL的三种日志是保证数据一致性和持久性的核心机制。

binlog（二进制日志）：

作用：

记录所有DDL和DML语句
用于主从复制
用于数据恢复

特点：

Server层实现，所有存储引擎都可用
逻辑日志，记录SQL语句
追加写入，不会覆盖

格式：

sql

1-- Statement：记录SQL语句
2-- Row：记录每行的变化（推荐）
3-- Mixed：混合模式
4
5-- 查看binlog格式
6SHOW VARIABLES LIKE 'binlog_format';
7
8-- 查看binlog文件
9SHOW BINARY LOGS;
10
11-- 查看binlog内容
12SHOW BINLOG EVENTS IN 'mysql-bin.000001';

redo log（重做日志）：

作用：

保证事务持久性
崩溃恢复
实现WAL（Write-Ahead Logging）

特点：

InnoDB特有
物理日志，记录数据页的修改
循环写入，固定大小
先写redo log，再写数据文件

工作原理：

11. 事务提交时，先写redo log
22. redo log写入成功，事务提交成功
33. 后台线程异步将脏页刷入磁盘
44. 崩溃恢复时，重放redo log

配置：

sql

1-- redo log文件大小
2innodb_log_file_size = 512M
3
4-- redo log文件数量
5innodb_log_files_in_group = 2
6
7-- 刷盘策略
8innodb_flush_log_at_trx_commit = 1
9-- 0: 每秒刷盘
10-- 1: 每次事务提交刷盘（最安全）
11-- 2: 每次提交写入OS缓存，每秒刷盘

undo log（回滚日志）：

作用：

保证事务原子性
实现MVCC
事务回滚

特点：

InnoDB特有
逻辑日志，记录相反操作
存储在回滚段中

工作原理：

sql

1-- INSERT操作：记录DELETE
2INSERT INTO users VALUES (1, 'John');
3-- undo log: DELETE FROM users WHERE id = 1;
4
5-- UPDATE操作：记录旧值
6UPDATE users SET name = 'Tom' WHERE id = 1;
7-- undo log: UPDATE users SET name = 'John' WHERE id = 1;
8
9-- DELETE操作：记录INSERT
10DELETE FROM users WHERE id = 1;
11-- undo log: INSERT INTO users VALUES (1, 'John');

三种日志对比：

特性	binlog	redo log	undo log
层次	Server层	InnoDB层	InnoDB层
类型	逻辑日志	物理日志	逻辑日志
作用	复制、恢复	崩溃恢复	回滚、MVCC
写入方式	追加写	循环写	随机写
持久化	是	是	否

两阶段提交：

11. 准备阶段：写入redo log，状态为prepare
22. 提交阶段：写入binlog
33. 完成阶段：redo log状态改为commit

17. MySQL如何实现高可用？

MySQL高可用方案确保数据库服务的连续性和数据安全。

1. 主从复制（Master-Slave）：

架构：

1Master（主库）
2  ├── Slave1（从库）
3  ├── Slave2（从库）
4  └── Slave3（从库）

优点：

简单易实现
读写分离
数据备份

缺点：

主库单点故障
需要手动切换

2. 主主复制（Master-Master）：

架构：

1Master1 <--> Master2

配置：

sql

1-- Master1
2auto_increment_increment = 2
3auto_increment_offset = 1
4
5-- Master2
6auto_increment_increment = 2
7auto_increment_offset = 2

优点：

双向复制
互为备份

缺点：

可能数据冲突
复杂度高

3. MHA（Master High Availability）：

特点：

自动故障切换
监控主库状态
提升从库为主库

工作流程：

11. 监控主库心跳
22. 检测主库故障
33. 选择最新的从库
44. 应用差异日志
55. 提升为新主库
66. 修改其他从库配置

4. MGR（MySQL Group Replication）：

特点：

MySQL官方方案
多主模式
自动故障转移
强一致性

架构：

1Node1 <--> Node2 <--> Node3
2（任意节点可读写）

5. Galera Cluster：

特点：

多主同步复制
真正的多主架构
无延迟复制

6. 使用中间件：

ProxySQL：

1应用 -> ProxySQL -> MySQL集群
2- 读写分离
3- 负载均衡
4- 故障切换

MaxScale：

1应用 -> MaxScale -> MySQL集群
2- 自动路由
3- 查询缓存
4- 监控管理

7. 云服务方案：

阿里云RDS：

自动备份
一键切换
读写分离

AWS RDS：

Multi-AZ部署
自动故障转移
只读副本

高可用架构示例：

1VIP
2                  |
3            +-----+-----+
4            |           |
5         Master      Slave
6            |           |
7      (主库写入)   (从库读取)
8            |           |
9        MHA Manager
10       (监控和切换)

最佳实践：

使用半同步复制
配置监控告警
定期演练切换
保持数据一致性
使用VIP实现透明切换

18. 什么是读写分离？

读写分离是将数据库的读操作和写操作分散到不同的数据库服务器上，提高系统性能和可用性。

架构：

1应用层
2  |
3中间件/代理
4  |
5  ├── Master（写）
6  └── Slave1, Slave2, Slave3（读）

实现方式：

1. 应用层实现：

java

1public class DataSourceRouter {
2    private DataSource masterDS;
3    private List<DataSource> slaveDS;
4    
5    public DataSource getDataSource(boolean isRead) {
6        if (isRead) {
7            // 负载均衡选择从库
8            return selectSlave();
9        }
10        return masterDS;
11    }
12    
13    private DataSource selectSlave() {
14        // 轮询、随机、权重等策略
15        int index = random.nextInt(slaveDS.size());
16        return slaveDS.get(index);
17    }
18}
19
20// 使用
21@Transactional(readOnly = true)
22public User getUser(Long id) {
23    // 自动路由到从库
24    return userMapper.selectById(id);
25}
26
27@Transactional
28public void updateUser(User user) {
29    // 自动路由到主库
30    userMapper.updateById(user);
31}

2. 中间件实现：

MyCAT：

xml

1<dataHost name="localhost1" maxCon="1000" minCon="10" 
2          balance="1" writeType="0" dbType="mysql">
3    <!-- 写主机 -->
4    <writeHost host="master" url="192.168.1.1:3306" 
5               user="root" password="123456">
6        <!-- 读主机 -->
7        <readHost host="slave1" url="192.168.1.2:3306" 
8                  user="root" password="123456"/>
9        <readHost host="slave2" url="192.168.1.3:3306" 
10                  user="root" password="123456"/>
11    </writeHost>
12</dataHost>

ProxySQL：

sql

1-- 配置读写分离规则
2INSERT INTO mysql_query_rules (rule_id, active, match_pattern, 
3                                destination_hostgroup, apply)
4VALUES 
5(1, 1, '^SELECT.*FOR UPDATE$', 1, 1),  -- 写
6(2, 1, '^SELECT', 2, 1);                -- 读
7
8-- 配置服务器组
9INSERT INTO mysql_servers (hostgroup_id, hostname, port)
10VALUES 
11(1, '192.168.1.1', 3306),  -- 写组
12(2, '192.168.1.2', 3306),  -- 读组
13(2, '192.168.1.3', 3306);  -- 读组

3. Spring Boot实现：

java

1@Configuration
2public class DataSourceConfig {
3    
4    @Bean
5    public DataSource routingDataSource() {
6        Map<Object, Object> targetDataSources = new HashMap<>();
7        targetDataSources.put("master", masterDataSource());
8        targetDataSources.put("slave", slaveDataSource());
9        
10        DynamicDataSource dataSource = new DynamicDataSource();
11        dataSource.setTargetDataSources(targetDataSources);
12        dataSource.setDefaultTargetDataSource(masterDataSource());
13        return dataSource;
14    }
15}
16
17// 动态数据源
18public class DynamicDataSource extends AbstractRoutingDataSource {
19    @Override
20    protected Object determineCurrentLookupKey() {
21        return DataSourceContextHolder.getDataSource();
22    }
23}
24
25// AOP拦截
26@Aspect
27public class DataSourceAspect {
28    @Around("@annotation(readOnly)")
29    public Object setReadDataSource(ProceedingJoinPoint point, 
30                                    ReadOnly readOnly) {
31        DataSourceContextHolder.setDataSource("slave");
32        try {
33            return point.proceed();
34        } finally {
35            DataSourceContextHolder.clearDataSource();
36        }
37    }
38}

负载均衡策略：

1. 轮询（Round Robin）：

java

1int index = counter++ % slaves.size();

2. 随机（Random）：

java

1int index = random.nextInt(slaves.size());

3. 权重（Weighted）：

java

1// slave1: 权重3, slave2: 权重1
2// slave1被选中概率75%, slave2被选中概率25%

4. 最少连接（Least Connections）：

java

1// 选择当前连接数最少的从库

注意事项：

1. 主从延迟问题：

java

1// 写后立即读可能读不到
2user.setName("Tom");
3userService.update(user);  // 写主库
4User u = userService.get(id);  // 读从库，可能还是旧数据
5
6// 解决方案：
7// 1. 强制读主库
8@ReadMaster
9public User getUser(Long id);
10
11// 2. 延迟读取
12Thread.sleep(100);
13
14// 3. 使用缓存

2. 事务一致性：

java

1@Transactional
2public void transfer() {
3    // 事务内的所有操作都应该在主库
4    accountService.deduct(fromId, amount);
5    accountService.add(toId, amount);
6}

优势：

提高查询性能
降低主库压力
提高系统可用性

19. 如何进行MySQL备份和恢复？

MySQL备份和恢复是保证数据安全的重要手段。

备份类型：

1. 逻辑备份：

mysqldump：

bash

1# 备份单个数据库
2mysqldump -u root -p database_name > backup.sql
3
4# 备份多个数据库
5mysqldump -u root -p --databases db1 db2 > backup.sql
6
7# 备份所有数据库
8mysqldump -u root -p --all-databases > all_backup.sql
9
10# 备份表结构
11mysqldump -u root -p -d database_name > schema.sql
12
13# 备份数据（不含结构）
14mysqldump -u root -p -t database_name > data.sql
15
16# 压缩备份
17mysqldump -u root -p database_name | gzip > backup.sql.gz
18
19# 备份时添加选项
20mysqldump -u root -p \
21  --single-transaction \  # 一致性备份（InnoDB）
22  --master-data=2 \       # 记录binlog位置
23  --flush-logs \          # 刷新日志
24  --routines \            # 备份存储过程
25  --triggers \            # 备份触发器
26  database_name > backup.sql

恢复：

bash

1# 恢复数据库
2mysql -u root -p database_name < backup.sql
3
4# 恢复压缩文件
5gunzip < backup.sql.gz | mysql -u root -p database_name
6
7# 在MySQL中执行
8mysql> source /path/to/backup.sql;

2. 物理备份：

直接复制文件：

bash

1# 停止MySQL
2systemctl stop mysqld
3
4# 复制数据目录
5cp -r /var/lib/mysql /backup/mysql_$(date +%Y%m%d)
6
7# 启动MySQL
8systemctl start mysqld

使用XtraBackup：

bash

1# 全量备份
2xtrabackup --backup --target-dir=/backup/full
3
4# 增量备份
5xtrabackup --backup --target-dir=/backup/inc1 \
6  --incremental-basedir=/backup/full
7
8# 准备恢复
9xtrabackup --prepare --target-dir=/backup/full
10xtrabackup --prepare --target-dir=/backup/full \
11  --incremental-dir=/backup/inc1
12
13# 恢复
14xtrabackup --copy-back --target-dir=/backup/full
15chown -R mysql:mysql /var/lib/mysql
16systemctl start mysqld

3. 二进制日志备份：

bash

1# 刷新日志
2mysqladmin -u root -p flush-logs
3
4# 备份binlog
5cp /var/lib/mysql/mysql-bin.* /backup/binlog/
6
7# 使用binlog恢复（时间点恢复）
8mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
9            --stop-datetime="2024-01-01 23:59:59" \
10            mysql-bin.000001 | mysql -u root -p
11
12# 使用binlog恢复（位置恢复）
13mysqlbinlog --start-position=1000 \
14            --stop-position=2000 \
15            mysql-bin.000001 | mysql -u root -p

备份策略：

1. 全量备份 + 增量备份：

bash

1# 周日：全量备份
2mysqldump --all-databases > full_backup_sunday.sql
3
4# 周一到周六：增量备份（binlog）
5mysqladmin flush-logs
6cp mysql-bin.* /backup/incremental/

2. 定时备份脚本：

bash

1#!/bin/bash
2DATE=$(date +%Y%m%d_%H%M%S)
3BACKUP_DIR="/backup/mysql"
4DB_NAME="mydb"
5
6# 创建备份目录
7mkdir -p $BACKUP_DIR
8
9# 执行备份
10mysqldump -u root -p'password' \
11  --single-transaction \
12  --master-data=2 \
13  --flush-logs \
14  $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz
15
16# 删除7天前的备份
17find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
18
19# 记录日志
20echo "Backup completed at $(date)" >> $BACKUP_DIR/backup.log

3. 定时任务：

bash

1# 添加到crontab
2crontab -e
3
4# 每天凌晨2点执行备份
50 2 * * * /path/to/backup.sh

恢复场景：

1. 完全恢复：

bash

1mysql -u root -p < full_backup.sql

2. 时间点恢复：

bash

1# 1. 恢复全量备份
2mysql -u root -p < full_backup.sql
3
4# 2. 应用binlog到指定时间点
5mysqlbinlog --stop-datetime="2024-01-01 10:00:00" \
6  mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

3. 跳过错误恢复：

bash

1# 跳过一个错误
2SET GLOBAL sql_slave_skip_counter = 1;
3START SLAVE;

备份验证：

bash

1# 定期验证备份可用性
2mysql -u root -p test_db < backup.sql

20. MySQL的字符集和排序规则是什么？

**字符集（Character Set）**定义了字符的编码方式，**排序规则（Collation）**定义了字符的比较和排序规则。

常用字符集：

1. latin1：

MySQL默认字符集（5.7及以前）
单字节编码
不支持中文

2. utf8：

最多3字节
不完整的UTF-8实现
不支持emoji等4字节字符

3. utf8mb4（推荐）：

完整的UTF-8实现
最多4字节
支持emoji
MySQL 8.0默认字符集

4. gbk：

中文编码
双字节

查看字符集：

sql

1-- 查看支持的字符集
2SHOW CHARACTER SET;
3
4-- 查看当前字符集
5SHOW VARIABLES LIKE 'character%';
6
7-- 查看数据库字符集
8SELECT DEFAULT_CHARACTER_SET_NAME 
9FROM information_schema.SCHEMATA 
10WHERE SCHEMA_NAME = 'mydb';
11
12-- 查看表字符集
13SHOW CREATE TABLE users;

设置字符集：

sql

1-- 服务器级别（my.cnf）
2[mysqld]
3character-set-server=utf8mb4
4collation-server=utf8mb4_unicode_ci
5
6[client]
7default-character-set=utf8mb4
8
9-- 数据库级别
10CREATE DATABASE mydb 
11CHARACTER SET utf8mb4 
12COLLATE utf8mb4_unicode_ci;
13
14ALTER DATABASE mydb 
15CHARACTER SET utf8mb4 
16COLLATE utf8mb4_unicode_ci;
17
18-- 表级别
19CREATE TABLE users (
20  id INT,
21  name VARCHAR(50)
22) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
23
24ALTER TABLE users 
25CHARACTER SET utf8mb4 
26COLLATE utf8mb4_unicode_ci;
27
28-- 列级别
29ALTER TABLE users 
30MODIFY name VARCHAR(50) 
31CHARACTER SET utf8mb4 
32COLLATE utf8mb4_unicode_ci;

排序规则：

常用Collation：

1. utf8mb4_general_ci：

不区分大小写
性能较好
准确性稍差

2. utf8mb4_unicode_ci：

不区分大小写
准确性好
性能稍差

3. utf8mb4_bin：

区分大小写
二进制比较

4. utf8mb4_0900_ai_ci：

MySQL 8.0默认
支持更多语言
ai: accent insensitive（不区分重音）
ci: case insensitive（不区分大小写）

示例：

sql

1-- 创建测试表
2CREATE TABLE test (
3  name VARCHAR(50)
4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
5
6INSERT INTO test VALUES ('A'), ('a'), ('B'), ('b');
7
8-- 不区分大小写
9SELECT * FROM test WHERE name = 'a';
10-- 返回：A, a
11
12-- 区分大小写
13SELECT * FROM test 
14WHERE name = 'a' COLLATE utf8mb4_bin;
15-- 返回：a
16
17-- 排序
18SELECT * FROM test ORDER BY name;
19-- utf8mb4_general_ci: A, a, B, b
20-- utf8mb4_bin: A, B, a, b

字符集转换：

sql

1-- 转换表字符集
2ALTER TABLE users 
3CONVERT TO CHARACTER SET utf8mb4 
4COLLATE utf8mb4_unicode_ci;
5
6-- 只修改表定义，不转换数据
7ALTER TABLE users 
8DEFAULT CHARACTER SET utf8mb4 
9COLLATE utf8mb4_unicode_ci;

连接字符集：

sql

1-- 设置连接字符集
2SET NAMES utf8mb4;
3
4-- 等价于
5SET character_set_client = utf8mb4;
6SET character_set_results = utf8mb4;
7SET character_set_connection = utf8mb4;

最佳实践：

统一使用utf8mb4
使用utf8mb4_unicode_ci或utf8mb4_0900_ai_ci
在my.cnf中配置默认字符集
创建数据库时明确指定字符集
应用程序连接时设置字符集

21. 什么是联合索引的最左前缀原则？

最左前缀原则是联合索引使用的核心规则，决定了索引是否能被有效利用。

原理：

联合索引按照索引列的顺序进行排序和查找，只有从最左边的列开始连续匹配，索引才能生效。

示例：

sql

1-- 创建联合索引
2CREATE INDEX idx_abc ON users(a, b, c);
3
4-- 索引结构（逻辑上）
5a | b | c | 主键
61 | 1 | 1 | id1
71 | 1 | 2 | id2
81 | 2 | 1 | id3
92 | 1 | 1 | id4

能使用索引的情况：

sql

1-- 1. 使用a（✓）
2SELECT * FROM users WHERE a = 1;
3
4-- 2. 使用a, b（✓）
5SELECT * FROM users WHERE a = 1 AND b = 2;
6
7-- 3. 使用a, b, c（✓）
8SELECT * FROM users WHERE a = 1 AND b = 2 AND c = 3;
9
10-- 4. 使用a, c（✓ 部分使用，只用到a）
11SELECT * FROM users WHERE a = 1 AND c = 3;
12
13-- 5. 顺序无关（✓ 优化器会调整）
14SELECT * FROM users WHERE b = 2 AND a = 1;

不能使用索引的情况：

sql

1-- 1. 跳过a，从b开始（✗）
2SELECT * FROM users WHERE b = 2;
3
4-- 2. 跳过a，从c开始（✗）
5SELECT * FROM users WHERE c = 3;
6
7-- 3. 只用b和c（✗）
8SELECT * FROM users WHERE b = 2 AND c = 3;

范围查询的影响：

sql

1-- 1. a使用等值，b使用范围，c失效
2SELECT * FROM users WHERE a = 1 AND b > 2 AND c = 3;
3-- 使用索引：a, b
4-- 未使用：c
5
6-- 2. a使用范围，b和c失效
7SELECT * FROM users WHERE a > 1 AND b = 2 AND c = 3;
8-- 使用索引：a
9-- 未使用：b, c

索引设计建议：

1. 区分度高的列放前面：

sql

1-- 好的设计
2CREATE INDEX idx_status_create_time ON orders(status, create_time);
3-- status区分度低，但查询频繁
4-- create_time区分度高
5
6-- 如果主要查询是：
7WHERE status = 1 AND create_time > '2024-01-01'
8-- 这个索引很合适

2. 考虑查询频率：

sql

1-- 查询1：WHERE a = 1 AND b = 2（频繁）
2-- 查询2：WHERE a = 1（偶尔）
3-- 查询3：WHERE b = 2（很少）
4
5-- 推荐索引
6CREATE INDEX idx_ab ON table(a, b);
7-- 可以覆盖查询1和查询2

3. 覆盖索引优化：

sql

1-- 查询
2SELECT a, b, c FROM users WHERE a = 1 AND b = 2;
3
4-- 索引
5CREATE INDEX idx_abc ON users(a, b, c);
6-- 覆盖索引，无需回表

验证索引使用：

sql

1EXPLAIN SELECT * FROM users WHERE a = 1 AND b = 2;
2-- 查看key_len判断使用了几个列
3-- a(INT): 4字节
4-- a+b(INT): 8字节
5-- a+b+c(INT): 12字节

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

InnoDB选择B+树作为索引结构是经过深思熟虑的设计决策。

B树 vs B+树结构对比：

B树：

1[10, 20]
2       /    |    \
3   [5,8]  [15]  [25,30]
4   / | \   |    /  |  \
5  数据节点（每个节点都存数据）

B+树：

1[10, 20]
2       /    |    \
3   [5,8]  [15]  [25,30]
4   / | \   |    /  |  \
5  叶子节点（只有叶子节点存数据）
6  [5]→[8]→[10]→[15]→[20]→[25]→[30]
7  （叶子节点通过指针连接）

B+树的优势：

1. 更高的扇出度（Fan-out）：

1B树：
2- 非叶子节点存储键和数据
3- 每个节点能存储的键较少
4- 树的高度较高
5
6B+树：
7- 非叶子节点只存储键
8- 每个节点能存储更多的键
9- 树的高度较低
10
11示例（假设页大小16KB）：
12B树：一个节点可能存100个键
13B+树：一个节点可能存1000个键
14
15查找效率：
163层B+树可以存储：1000 * 1000 * 1000 = 10亿条记录
173层B树可能只能存储：100 * 100 * 100 = 100万条记录

2. 范围查询效率高：

sql

1-- 查询范围数据
2SELECT * FROM users WHERE id BETWEEN 100 AND 200;
3
4-- B树：
5需要多次随机访问不同的节点
6
7-- B+树：
81. 定位到起始叶子节点（id=100）
92. 顺序遍历叶子节点链表到结束（id=200）
103. 所有数据都在叶子节点，且有序连接

3. 全表扫描效率高：

sql

1SELECT * FROM users;
2
3-- B树：
4需要遍历所有节点（包括非叶子节点）
5
6-- B+树：
7只需遍历叶子节点链表

4. 稳定的查询性能：

1B树：
2- 数据分布在各层
3- 查询性能不稳定
4- 最好情况：O(1)（根节点）
5- 最坏情况：O(log n)（叶子节点）
6
7B+树：
8- 所有数据都在叶子节点
9- 查询性能稳定
10- 所有查询都是：O(log n)

5. 更好的磁盘预读：

1操作系统的磁盘预读：
2- 一次读取一个页（通常4KB或16KB）
3- B+树的节点大小通常设计为页的整数倍
4
5B+树优势：
6- 非叶子节点只存键，可以在一个页中存更多键
7- 减少磁盘I/O次数
8- 提高缓存命中率

6. 支持数据库的顺序访问：

sql

1-- ORDER BY查询
2SELECT * FROM users ORDER BY id;
3
4-- B+树：
5直接遍历叶子节点链表，数据已排序
6
7-- B树：
8需要中序遍历整棵树

实际案例：

sql

1-- 假设InnoDB页大小16KB，主键INT(4字节)，指针6字节
2
3-- B+树非叶子节点：
4每个键值对：4 + 6 = 10字节
5一个节点可存：16KB / 10B ≈ 1600个键
6
7-- 3层B+树：
8第1层：1个节点（根）
9第2层：1600个节点
10第3层：1600 * 1600 = 256万个节点
11可存储：256万条记录
12
13-- 查询任意记录：
14最多3次磁盘I/O

为什么不用其他数据结构：

哈希表：

不支持范围查询
不支持排序
不支持模糊查询

二叉搜索树：

树的高度太高
磁盘I/O次数多

红黑树：

树的高度仍然较高
不适合磁盘存储

23. MySQL的JOIN查询有哪些类型？

MySQL支持多种JOIN类型，用于连接多个表的数据。

JOIN类型：

1. INNER JOIN（内连接）：

sql

1-- 只返回两表都匹配的记录
2SELECT u.name, o.order_no
3FROM users u
4INNER JOIN orders o ON u.id = o.user_id;
5
6-- 简写（等价）
7SELECT u.name, o.order_no
8FROM users u, orders o
9WHERE u.id = o.user_id;

结果：

1users:        orders:       结果:
2id | name     user_id | no  name | no
31  | Alice   1       | A1  Alice| A1
42  | Bob     1       | A2  Alice| A2
53  | Carol   2       | B1  Bob  | B1

2. LEFT JOIN（左连接）：

sql

1-- 返回左表所有记录，右表匹配的记录
2SELECT u.name, o.order_no
3FROM users u
4LEFT JOIN orders o ON u.id = o.user_id;

结果：

1users:        orders:       结果:
2id | name     user_id | no  name  | no
31  | Alice   1       | A1  Alice | A1
42  | Bob     1       | A2  Alice | A2
53  | Carol   2       | B1  Bob   | B1
6                          Carol | NULL

3. RIGHT JOIN（右连接）：

sql

1-- 返回右表所有记录，左表匹配的记录
2SELECT u.name, o.order_no
3FROM users u
4RIGHT JOIN orders o ON u.id = o.user_id;

4. FULL OUTER JOIN（全外连接）：

sql

1-- MySQL不直接支持，需要用UNION模拟
2SELECT u.name, o.order_no
3FROM users u
4LEFT JOIN orders o ON u.id = o.user_id
5UNION
6SELECT u.name, o.order_no
7FROM users u
8RIGHT JOIN orders o ON u.id = o.user_id;

5. CROSS JOIN（交叉连接）：

sql

1-- 笛卡尔积，返回所有可能的组合
2SELECT u.name, o.order_no
3FROM users u
4CROSS JOIN orders o;
5
6-- 结果：3个用户 × 3个订单 = 9条记录

6. SELF JOIN（自连接）：

sql

1-- 表与自己连接
2-- 查找同一部门的员工
3SELECT e1.name AS employee, e2.name AS colleague
4FROM employees e1
5JOIN employees e2 ON e1.dept_id = e2.dept_id
6WHERE e1.id != e2.id;

JOIN优化技巧：

1. 使用小表驱动大表：

sql

1-- 好：小表在前
2SELECT *
3FROM small_table s
4JOIN large_table l ON s.id = l.small_id;
5
6-- 不好：大表在前
7SELECT *
8FROM large_table l
9JOIN small_table s ON l.small_id = s.id;

2. 确保JOIN列有索引：

sql

1-- 为JOIN列创建索引
2CREATE INDEX idx_user_id ON orders(user_id);
3
4-- 查询会使用索引
5SELECT *
6FROM users u
7JOIN orders o ON u.id = o.user_id;

3. 避免JOIN过多表：

sql

1-- 不好：JOIN太多表
2SELECT *
3FROM t1
4JOIN t2 ON t1.id = t2.t1_id
5JOIN t3 ON t2.id = t3.t2_id
6JOIN t4 ON t3.id = t4.t3_id
7JOIN t5 ON t4.id = t5.t4_id;  -- 性能差
8
9-- 好：分步查询或使用中间表

4. 使用STRAIGHT_JOIN强制顺序：

sql

1-- 强制MySQL按指定顺序JOIN
2SELECT *
3FROM small_table
4STRAIGHT_JOIN large_table ON small_table.id = large_table.small_id;

实际应用示例：

查找没有订单的用户：

sql

1SELECT u.*
2FROM users u
3LEFT JOIN orders o ON u.id = o.user_id
4WHERE o.id IS NULL;

查找有订单的用户：

sql

1SELECT DISTINCT u.*
2FROM users u
3INNER JOIN orders o ON u.id = o.user_id;

统计每个用户的订单数：

sql

1SELECT u.name, COUNT(o.id) AS order_count
2FROM users u
3LEFT JOIN orders o ON u.id = o.user_id
4GROUP BY u.id, u.name;

24. 什么是临时表？

临时表是MySQL中用于存储临时数据的特殊表，会话结束后自动删除。

临时表类型：

1. 用户创建的临时表：

sql

1-- 创建临时表
2CREATE TEMPORARY TABLE temp_users (
3    id INT,
4    name VARCHAR(50)
5);
6
7-- 插入数据
8INSERT INTO temp_users VALUES (1, 'Alice');
9
10-- 查询
11SELECT * FROM temp_users;
12
13-- 会话结束后自动删除

特点：

只对当前会话可见
会话结束自动删除
可以与普通表同名（临时表优先）
存储在tmpdir目录

2. MySQL内部临时表：

MySQL在执行某些查询时会自动创建内部临时表：

触发场景：

sql

1-- 1. UNION查询
2SELECT id FROM users
3UNION
4SELECT id FROM orders;
5
6-- 2. DISTINCT + ORDER BY不同列
7SELECT DISTINCT name FROM users ORDER BY age;
8
9-- 3. GROUP BY + ORDER BY不同列
10SELECT name, COUNT(*) FROM users 
11GROUP BY name ORDER BY age;
12
13-- 4. 子查询在FROM子句
14SELECT * FROM (
15    SELECT * FROM users WHERE age > 18
16) AS t;
17
18-- 5. 使用SQL_BUFFER_RESULT
19SELECT SQL_BUFFER_RESULT * FROM users;

内部临时表类型：

Memory引擎临时表：

存储在内存中
速度快
受限于tmp_table_size和max_heap_table_size

InnoDB引擎临时表：

存储在磁盘
当内存临时表超过限制时转换
性能较差

查看临时表使用：

sql

1-- 查看临时表统计
2SHOW STATUS LIKE 'Created_tmp%';
3-- Created_tmp_tables: 创建的临时表数
4-- Created_tmp_disk_tables: 磁盘临时表数
5
6-- 使用EXPLAIN查看
7EXPLAIN SELECT DISTINCT name FROM users ORDER BY age;
8-- Extra: Using temporary

优化临时表：

1. 增加内存临时表大小：

sql

1SET tmp_table_size = 64M;
2SET max_heap_table_size = 64M;

2. 避免产生临时表：

sql

1-- 不好：产生临时表
2SELECT DISTINCT name FROM users ORDER BY age;
3
4-- 好：使用索引避免临时表
5CREATE INDEX idx_name_age ON users(name, age);
6SELECT DISTINCT name FROM users ORDER BY name;

3. 使用索引覆盖：

sql

1-- 创建覆盖索引
2CREATE INDEX idx_cover ON users(name, age);
3
4-- 查询可以使用覆盖索引
5SELECT name, age FROM users WHERE name = 'Alice';

临时表应用场景：

1. 复杂查询的中间结果：

sql

1-- 分步处理复杂查询
2CREATE TEMPORARY TABLE temp_result AS
3SELECT user_id, SUM(amount) AS total
4FROM orders
5WHERE create_time > '2024-01-01'
6GROUP BY user_id;
7
8-- 使用临时表继续处理
9SELECT u.name, t.total
10FROM users u
11JOIN temp_result t ON u.id = t.user_id
12WHERE t.total > 1000;

2. 数据去重：

sql

1CREATE TEMPORARY TABLE temp_unique AS
2SELECT DISTINCT * FROM users;
3
4DELETE FROM users;
5INSERT INTO users SELECT * FROM temp_unique;

3. 批量数据处理：

sql

1-- 导入数据到临时表
2LOAD DATA INFILE 'data.csv'
3INTO TABLE temp_import;
4
5-- 验证和清洗
6DELETE FROM temp_import WHERE invalid_data;
7
8-- 导入到正式表
9INSERT INTO users SELECT * FROM temp_import;

25. 如何处理大表的DDL操作？

大表DDL操作（如ALTER TABLE）可能导致长时间锁表，影响业务。

问题：

sql

1-- 传统DDL会锁表
2ALTER TABLE large_table ADD COLUMN new_col VARCHAR(50);
3-- 可能锁表数小时，期间无法读写

解决方案：

1. 使用Online DDL（MySQL 5.6+）：

sql

1-- 在线添加列
2ALTER TABLE users 
3ADD COLUMN phone VARCHAR(20),
4ALGORITHM=INPLACE, LOCK=NONE;
5
6-- ALGORITHM选项：
7-- COPY: 创建临时表，复制数据（锁表）
8-- INPLACE: 原地修改（不锁表或短暂锁表）
9
10-- LOCK选项：
11-- NONE: 不锁表
12-- SHARED: 允许读，不允许写
13-- EXCLUSIVE: 完全锁表

支持Online DDL的操作：

sql

1-- 添加索引（INPLACE）
2ALTER TABLE users ADD INDEX idx_name(name);
3
4-- 删除索引（INPLACE）
5ALTER TABLE users DROP INDEX idx_name;
6
7-- 添加列（INPLACE，末尾）
8ALTER TABLE users ADD COLUMN age INT;
9
10-- 修改列默认值（INPLACE）
11ALTER TABLE users ALTER COLUMN status SET DEFAULT 1;
12
13-- 重命名列（INPLACE）
14ALTER TABLE users CHANGE old_name new_name VARCHAR(50);

不支持Online DDL的操作：

sql

1-- 修改列类型（需要COPY）
2ALTER TABLE users MODIFY COLUMN name VARCHAR(100);
3
4-- 添加主键（需要重建表）
5ALTER TABLE users ADD PRIMARY KEY (id);

2. 使用pt-online-schema-change：

Percona Toolkit工具，适用于所有DDL操作。

bash

1pt-online-schema-change \
2  --alter "ADD COLUMN phone VARCHAR(20)" \
3  --execute \
4  D=mydb,t=users
5
6# 工作原理：
7# 1. 创建新表（带新结构）
8# 2. 创建触发器（同步数据变更）
9# 3. 分批复制数据
10# 4. 切换表名
11# 5. 删除旧表

3. 使用gh-ost：

GitHub开源工具，无触发器方案。

bash

1gh-ost \
2  --user=root \
3  --password=password \
4  --host=localhost \
5  --database=mydb \
6  --table=users \
7  --alter="ADD COLUMN phone VARCHAR(20)" \
8  --execute
9
10# 工作原理：
11# 1. 创建ghost表（新结构）
12# 2. 通过binlog同步数据变更
13# 3. 分批复制数据
14# 4. 切换表名

4. 分批处理：

对于某些操作，可以分批执行：

sql

1-- 删除大量数据
2-- 不好：一次删除
3DELETE FROM users WHERE create_time < '2020-01-01';
4
5-- 好：分批删除
6DELIMITER $$
7CREATE PROCEDURE batch_delete()
8BEGIN
9    DECLARE rows INT DEFAULT 1;
10    WHILE rows > 0 DO
11        DELETE FROM users 
12        WHERE create_time < '2020-01-01'
13        LIMIT 1000;
14        
15        SET rows = ROW_COUNT();
16        SELECT SLEEP(0.1);  -- 避免影响业务
17    END WHILE;
18END$$
19DELIMITER ;
20
21CALL batch_delete();

5. 使用新表替换：

sql

1-- 1. 创建新表
2CREATE TABLE users_new LIKE users;
3ALTER TABLE users_new ADD COLUMN phone VARCHAR(20);
4
5-- 2. 复制数据
6INSERT INTO users_new SELECT *, NULL FROM users;
7
8-- 3. 切换表名
9RENAME TABLE users TO users_old, users_new TO users;
10
11-- 4. 删除旧表
12DROP TABLE users_old;

6. 在从库执行：

sql

1-- 1. 在从库停止复制
2STOP SLAVE;
3
4-- 2. 执行DDL
5ALTER TABLE users ADD COLUMN phone VARCHAR(20);
6
7-- 3. 主从切换
8-- 提升从库为主库
9
10-- 4. 在新从库（原主库）执行DDL

最佳实践：

1. 选择低峰期：

sql

1-- 在凌晨或业务低峰期执行

2. 提前测试：

sql

1-- 在测试环境验证DDL时间
2-- 评估对业务的影响

3. 监控执行进度：

sql

1-- 查看DDL进度（MySQL 5.7+）
2SELECT * FROM performance_schema.events_stages_current;

4. 准备回滚方案：

sql

1-- 保留旧表备份
2-- 准备快速回滚脚本

5. 通知相关人员：

sql

1-- 提前通知运维和开发
2-- 准备应急预案

26. MySQL的分库分表策略有哪些？

分库分表是应对海量数据和高并发的核心解决方案。

为什么需要分库分表：

单表数据量过大（>1000万）
并发压力大
磁盘I/O瓶颈
单库连接数限制

拆分方式：

1. 垂直拆分：

垂直分库：

1原来：
2user_db
3├── users（用户表）
4├── orders（订单表）
5└── products（商品表）
6
7拆分后：
8user_db（用户库）
9└── users
10
11order_db（订单库）
12└── orders
13
14product_db（商品库）
15└── products

垂直分表：

sql

1-- 原表：users（字段很多）
2id, name, email, phone, address, avatar, bio, settings...
3
4-- 拆分后：
5-- users（常用字段）
6id, name, email, phone
7
8-- user_profiles（不常用字段）
9user_id, address, avatar, bio, settings

2. 水平拆分：

水平分库：

1user_db_0
2└── users（id % 4 = 0）
3
4user_db_1
5└── users（id % 4 = 1）
6
7user_db_2
8└── users（id % 4 = 2）
9
10user_db_3
11└── users（id % 4 = 3）

水平分表：

sql

1-- 同一个库，多个表
2users_0（id % 4 = 0）
3users_1（id % 4 = 1）
4users_2（id % 4 = 2）
5users_3（id % 4 = 3）

分片策略：

1. 范围分片（Range）：

sql

1-- 按ID范围
2users_0: id 1-1000000
3users_1: id 1000001-2000000
4users_2: id 2000001-3000000
5
6-- 按时间范围
7orders_2024_01
8orders_2024_02
9orders_2024_03

优点：

扩容简单
范围查询效率高

缺点：

数据分布可能不均匀
热点问题

2. 哈希分片（Hash）：

java

1// 按用户ID取模
2int shardIndex = userId % 4;
3String tableName = "users_" + shardIndex;

优点：

数据分布均匀
无热点问题

缺点：

扩容困难
范围查询需要查所有分片

3. 一致性哈希：

java

1// 使用一致性哈希环
2int hash = consistentHash(userId);
3String shardKey = getShardByHash(hash);

优点：

扩容时数据迁移少

4. 地理位置分片：

sql

1-- 按地区
2users_beijing
3users_shanghai
4users_guangzhou

5. 时间分片：

sql

1-- 按月份
2orders_202401
3orders_202402
4orders_202403

分库分表中间件：

1. ShardingSphere（推荐）：

yaml

1# 配置示例
2shardingSphere:
3  dataSources:
4    ds0: # 数据源0
5    ds1: # 数据源1
6  shardingRule:
7    tables:
8      users:
9        actualDataNodes: ds${0..1}.users_${0..3}
10        databaseStrategy:
11          inline:
12            shardingColumn: user_id
13            algorithmExpression: ds${user_id % 2}
14        tableStrategy:
15          inline:
16            shardingColumn: user_id
17            algorithmExpression: users_${user_id % 4}

2. MyCAT：

xml

1<table name="users" dataNode="dn1,dn2,dn3,dn4" rule="mod-long">
2    <childTable name="orders" joinKey="user_id" parentKey="id"/>
3</table>

分库分表带来的问题：

1. 跨库JOIN：

java

1// 解决方案：
2// 1. 应用层JOIN
3List<User> users = userDao.getUsers(ids);
4List<Order> orders = orderDao.getOrders(ids);
5// 在应用层关联
6
7// 2. 数据冗余
8// 在订单表中冗余用户信息
9
10// 3. 全局表
11// 字典表在每个库都有一份

2. 分布式事务：

java

1// 解决方案：
2// 1. 避免跨库事务
3// 2. 使用最终一致性
4// 3. 使用分布式事务框架（Seata）

3. 全局唯一ID：

java

1// 解决方案：
2// 1. 雪花算法（Snowflake）
3long id = snowflake.nextId();
4
5// 2. 数据库号段模式
6// 3. Redis自增
7// 4. UUID

4. 分页查询：

sql

1-- 问题：跨分片分页
2SELECT * FROM users ORDER BY create_time LIMIT 0, 20;
3
4-- 解决方案：
5-- 1. 每个分片查询，应用层合并排序
6-- 2. 使用ES等搜索引擎

最佳实践：

优先考虑优化，再考虑分库分表
选择合适的分片键（高频查询字段）
避免跨库JOIN和事务
提前规划扩容方案
做好数据迁移工具

27. 什么是SQL注入？如何防止？

SQL注入是最常见的Web安全漏洞，攻击者通过在输入中插入恶意SQL代码来攻击数据库。

SQL注入示例：

场景1：登录绕过：

java

1// 危险代码
2String sql = "SELECT * FROM users WHERE username = '" + username 
3           + "' AND password = '" + password + "'";
4
5// 攻击输入
6username: admin' OR '1'='1
7password: anything
8
9// 实际执行的SQL
10SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = 'anything'
11// 永远返回true，绕过登录

场景2：数据泄露：

java

1// 危险代码
2String sql = "SELECT * FROM products WHERE id = " + productId;
3
4// 攻击输入
5productId: 1 UNION SELECT username, password FROM users
6
7// 实际执行的SQL
8SELECT * FROM products WHERE id = 1 UNION SELECT username, password FROM users
9// 泄露用户数据

场景3：数据破坏：

java

1// 危险代码
2String sql = "UPDATE users SET email = '" + email + "' WHERE id = " + userId;
3
4// 攻击输入
5email: test@test.com'; DROP TABLE users; --
6
7// 实际执行的SQL
8UPDATE users SET email = 'test@test.com'; DROP TABLE users; --' WHERE id = 1
9// 删除整个表

防止SQL注入的方法：

1. 使用预编译语句（PreparedStatement）：

java

1// 安全代码
2String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
3PreparedStatement pstmt = conn.prepareStatement(sql);
4pstmt.setString(1, username);
5pstmt.setString(2, password);
6ResultSet rs = pstmt.executeQuery();
7
8// 原理：参数会被正确转义，不会被当作SQL代码执行

2. 使用ORM框架：

java

1// MyBatis（安全）
2@Select("SELECT * FROM users WHERE username = #{username}")
3User findByUsername(String username);
4
5// 注意：使用#{}而不是${}
6// #{} 会预编译
7// ${} 会直接拼接（危险）

3. 输入验证和过滤：

java

1// 白名单验证
2public boolean isValidUsername(String username) {
3    return username.matches("^[a-zA-Z0-9_]{3,20}$");
4}
5
6// 过滤特殊字符
7public String escapeSQL(String input) {
8    return input.replace("'", "''")
9                .replace("\\", "\\\\")
10                .replace(";", "");
11}

4. 最小权限原则：

sql

1-- 应用程序使用的数据库账号只授予必要权限
2GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_user'@'localhost';
3-- 不要授予DROP、CREATE等危险权限

5. 错误信息处理：

java

1// 不好：暴露数据库信息
2catch (SQLException e) {
3    return "Error: " + e.getMessage();
4}
5
6// 好：返回通用错误信息
7catch (SQLException e) {
8    logger.error("Database error", e);
9    return "操作失败，请稍后重试";
10}

6. 使用存储过程：

sql

1-- 创建存储过程
2DELIMITER $$
3CREATE PROCEDURE getUserByUsername(IN p_username VARCHAR(50))
4BEGIN
5    SELECT * FROM users WHERE username = p_username;
6END$$
7DELIMITER ;
8
9-- 调用存储过程
10CALL getUserByUsername('admin');

7. Web应用防火墙（WAF）：

使用ModSecurity等WAF
检测和阻止SQL注入攻击

检测SQL注入：

bash

1# 使用sqlmap工具
2sqlmap -u "http://example.com/product?id=1" --dbs
3
4# 手动测试
5# 1. 单引号测试
6id=1'
7
8# 2. 逻辑测试
9id=1 OR 1=1
10
11# 3. 时间延迟测试
12id=1 AND SLEEP(5)

28. MySQL的存储过程和函数有什么区别？

**存储过程（Procedure）和函数（Function）**都是预编译的SQL代码块，但有重要区别。

主要区别：

特性	存储过程	函数
返回值	可以有多个（OUT参数）	只能返回一个值
调用方式	CALL procedure_name()	SELECT function_name()
事务	可以包含事务	不能包含事务
DML操作	可以执行	受限制
使用场景	复杂业务逻辑	计算和转换

存储过程示例：

sql

1-- 创建存储过程
2DELIMITER $$
3CREATE PROCEDURE transfer_money(
4    IN from_account INT,
5    IN to_account INT,
6    IN amount DECIMAL(10,2),
7    OUT result VARCHAR(50)
8)
9BEGIN
10    DECLARE from_balance DECIMAL(10,2);
11    
12    -- 开启事务
13    START TRANSACTION;
14    
15    -- 检查余额
16    SELECT balance INTO from_balance 
17    FROM accounts WHERE id = from_account FOR UPDATE;
18    
19    IF from_balance < amount THEN
20        SET result = '余额不足';
21        ROLLBACK;
22    ELSE
23        -- 扣款
24        UPDATE accounts SET balance = balance - amount 
25        WHERE id = from_account;
26        
27        -- 入账
28        UPDATE accounts SET balance = balance + amount 
29        WHERE id = to_account;
30        
31        SET result = '转账成功';
32        COMMIT;
33    END IF;
34END$$
35DELIMITER ;
36
37-- 调用存储过程
38CALL transfer_money(1, 2, 100.00, @result);
39SELECT @result;

函数示例：

sql

1-- 创建函数
2DELIMITER $$
3CREATE FUNCTION calculate_age(birth_date DATE)
4RETURNS INT
5DETERMINISTIC
6BEGIN
7    DECLARE age INT;
8    SET age = YEAR(CURDATE()) - YEAR(birth_date);
9    
10    IF DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(birth_date, '%m%d') THEN
11        SET age = age - 1;
12    END IF;
13    
14    RETURN age;
15END$$
16DELIMITER ;
17
18-- 使用函数
19SELECT name, calculate_age(birth_date) AS age FROM users;
20
21-- 在WHERE子句中使用
22SELECT * FROM users WHERE calculate_age(birth_date) > 18;

存储过程的优点：

sql

1-- 1. 可以返回多个值
2CREATE PROCEDURE get_user_stats(
3    IN user_id INT,
4    OUT order_count INT,
5    OUT total_amount DECIMAL(10,2)
6)
7BEGIN
8    SELECT COUNT(*), SUM(amount) 
9    INTO order_count, total_amount
10    FROM orders WHERE user_id = user_id;
11END;
12
13-- 2. 可以执行复杂逻辑
14CREATE PROCEDURE process_orders()
15BEGIN
16    DECLARE done INT DEFAULT 0;
17    DECLARE order_id INT;
18    DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status = 'pending';
19    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
20    
21    OPEN cur;
22    
23    read_loop: LOOP
24        FETCH cur INTO order_id;
25        IF done THEN
26            LEAVE read_loop;
27        END IF;
28        
29        -- 处理订单
30        UPDATE orders SET status = 'processing' WHERE id = order_id;
31    END LOOP;
32    
33    CLOSE cur;
34END;

函数的限制：

sql

1-- 函数不能执行以下操作：
2-- 1. 不能修改表数据（在某些配置下）
3-- 2. 不能使用事务
4-- 3. 不能调用存储过程
5-- 4. 不能使用动态SQL

何时使用：

使用存储过程：

复杂的业务逻辑
需要事务处理
需要多个返回值
批量数据处理

使用函数：

简单的计算
数据转换
需要在SELECT中使用
返回单个值

29. 什么是触发器？

**触发器（Trigger）**是在特定数据库事件发生时自动执行的存储过程。

触发器类型：

按时机分类：

BEFORE：在事件发生前触发
AFTER：在事件发生后触发

按事件分类：

INSERT：插入数据时
UPDATE：更新数据时
DELETE：删除数据时

创建触发器：

1. BEFORE INSERT触发器：

sql

1-- 自动设置创建时间
2DELIMITER $$
3CREATE TRIGGER before_user_insert
4BEFORE INSERT ON users
5FOR EACH ROW
6BEGIN
7    SET NEW.create_time = NOW();
8    SET NEW.update_time = NOW();
9END$$
10DELIMITER ;
11
12-- 测试
13INSERT INTO users (name, email) VALUES ('Alice', 'alice@test.com');
14-- create_time和update_time会自动设置

2. AFTER INSERT触发器：

sql

1-- 插入订单后更新统计
2DELIMITER $$
3CREATE TRIGGER after_order_insert
4AFTER INSERT ON orders
5FOR EACH ROW
6BEGIN
7    UPDATE user_stats 
8    SET order_count = order_count + 1,
9        total_amount = total_amount + NEW.amount
10    WHERE user_id = NEW.user_id;
11END$$
12DELIMITER ;

3. BEFORE UPDATE触发器：

sql

1-- 更新时自动记录修改时间
2DELIMITER $$
3CREATE TRIGGER before_user_update
4BEFORE UPDATE ON users
5FOR EACH ROW
6BEGIN
7    SET NEW.update_time = NOW();
8    
9    -- 防止某些字段被修改
10    IF OLD.id != NEW.id THEN
11        SIGNAL SQLSTATE '45000'
12        SET MESSAGE_TEXT = '不能修改ID';
13    END IF;
14END$$
15DELIMITER ;

4. AFTER DELETE触发器：

sql

1-- 删除用户时记录日志
2DELIMITER $$
3CREATE TRIGGER after_user_delete
4AFTER DELETE ON users
5FOR EACH ROW
6BEGIN
7    INSERT INTO user_delete_log (user_id, username, delete_time)
8    VALUES (OLD.id, OLD.name, NOW());
9END$$
10DELIMITER ;

实际应用场景：

1. 审计日志：

sql

1CREATE TRIGGER audit_user_changes
2AFTER UPDATE ON users
3FOR EACH ROW
4BEGIN
5    INSERT INTO audit_log (
6        table_name, 
7        record_id, 
8        old_value, 
9        new_value, 
10        change_time
11    ) VALUES (
12        'users',
13        NEW.id,
14        CONCAT('name:', OLD.name, ',email:', OLD.email),
15        CONCAT('name:', NEW.name, ',email:', NEW.email),
16        NOW()
17    );
18END;

2. 数据验证：

sql

1CREATE TRIGGER validate_order
2BEFORE INSERT ON orders
3FOR EACH ROW
4BEGIN
5    IF NEW.amount <= 0 THEN
6        SIGNAL SQLSTATE '45000'
7        SET MESSAGE_TEXT = '订单金额必须大于0';
8    END IF;
9    
10    IF NEW.quantity <= 0 THEN
11        SIGNAL SQLSTATE '45000'
12        SET MESSAGE_TEXT = '订单数量必须大于0';
13    END IF;
14END;

3. 数据同步：

sql

1CREATE TRIGGER sync_to_cache
2AFTER UPDATE ON products
3FOR EACH ROW
4BEGIN
5    -- 标记缓存需要更新
6    INSERT INTO cache_invalidation (table_name, record_id)
7    VALUES ('products', NEW.id);
8END;

查看和管理触发器：

sql

1-- 查看所有触发器
2SHOW TRIGGERS;
3
4-- 查看特定表的触发器
5SHOW TRIGGERS WHERE `Table` = 'users';
6
7-- 查看触发器定义
8SHOW CREATE TRIGGER trigger_name;
9
10-- 删除触发器
11DROP TRIGGER IF EXISTS trigger_name;

触发器的注意事项：

优点：

自动执行，无需应用层干预
保证数据完整性
集中管理业务规则

缺点：

隐藏的业务逻辑，难以调试
可能影响性能
触发器链可能导致复杂问题
不易维护

最佳实践：

避免复杂逻辑
不要在触发器中调用存储过程
避免触发器链
做好文档记录
考虑使用应用层逻辑替代

30. MySQL的视图是什么？

**视图（View）**是基于SQL查询结果的虚拟表，不存储实际数据。

创建视图：

sql

1-- 简单视图
2CREATE VIEW active_users AS
3SELECT id, name, email
4FROM users
5WHERE status = 'active';
6
7-- 使用视图
8SELECT * FROM active_users;
9
10-- 复杂视图（多表JOIN）
11CREATE VIEW order_details AS
12SELECT 
13    o.id AS order_id,
14    o.order_no,
15    u.name AS user_name,
16    p.name AS product_name,
17    o.quantity,
18    o.amount
19FROM orders o
20JOIN users u ON o.user_id = u.id
21JOIN products p ON o.product_id = p.id;

视图的优点：

1. 简化复杂查询：

sql

1-- 不用每次都写复杂的JOIN
2SELECT * FROM order_details WHERE user_name = 'Alice';
3
4-- 而不是
5SELECT o.*, u.name, p.name
6FROM orders o
7JOIN users u ON o.user_id = u.id
8JOIN products p ON o.product_id = p.id
9WHERE u.name = 'Alice';

2. 数据安全性：

sql

1-- 只暴露部分字段
2CREATE VIEW public_users AS
3SELECT id, name, email
4FROM users;
5-- 隐藏了password、phone等敏感字段
6
7-- 授权给应用
8GRANT SELECT ON public_users TO 'app_user'@'localhost';

3. 逻辑独立性：

sql

1-- 表结构变化时，只需修改视图
2-- 应用代码不需要改动

可更新视图：

sql

1-- 简单视图可以更新
2CREATE VIEW simple_view AS
3SELECT id, name, email FROM users;
4
5-- 可以执行INSERT、UPDATE、DELETE
6UPDATE simple_view SET name = 'Bob' WHERE id = 1;
7
8-- 复杂视图（包含JOIN、聚合等）通常不可更新

WITH CHECK OPTION：

sql

1-- 确保通过视图的更新符合视图条件
2CREATE VIEW active_users AS
3SELECT * FROM users WHERE status = 'active'
4WITH CHECK OPTION;
5
6-- 这个更新会失败，因为违反了视图条件
7UPDATE active_users SET status = 'inactive' WHERE id = 1;

视图管理：

sql

1-- 查看所有视图
2SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';
3
4-- 查看视图定义
5SHOW CREATE VIEW view_name;
6
7-- 修改视图
8ALTER VIEW active_users AS
9SELECT id, name, email, phone
10FROM users
11WHERE status = 'active';
12
13-- 删除视图
14DROP VIEW IF EXISTS view_name;

物化视图：

MySQL不直接支持物化视图，但可以模拟：

sql

1-- 创建实体表存储视图数据
2CREATE TABLE mv_order_stats AS
3SELECT 
4    user_id,
5    COUNT(*) AS order_count,
6    SUM(amount) AS total_amount
7FROM orders
8GROUP BY user_id;
9
10-- 创建索引
11CREATE INDEX idx_user_id ON mv_order_stats(user_id);
12
13-- 定期刷新（使用事件调度器）
14CREATE EVENT refresh_order_stats
15ON SCHEDULE EVERY 1 HOUR
16DO
17BEGIN
18    TRUNCATE TABLE mv_order_stats;
19    INSERT INTO mv_order_stats
20    SELECT user_id, COUNT(*), SUM(amount)
21    FROM orders
22    GROUP BY user_id;
23END;

视图的性能考虑：

问题：

视图每次查询都会执行底层SQL
复杂视图可能性能较差

优化：

sql

1-- 1. 为视图涉及的表创建索引
2CREATE INDEX idx_status ON users(status);
3
4-- 2. 避免嵌套视图
5-- 不好
6CREATE VIEW view1 AS SELECT * FROM table1;
7CREATE VIEW view2 AS SELECT * FROM view1;
8
9-- 3. 使用物化视图（实体表）代替

最佳实践：

用于简化复杂查询
用于数据安全控制
避免过度使用
注意性能影响
做好文档记录

31. 如何进行MySQL性能监控？

MySQL性能监控帮助及时发现和解决性能问题。

监控工具和方法：

1. SHOW STATUS：

sql

1-- 查看服务器状态
2SHOW GLOBAL STATUS;
3
4-- 关键指标
5SHOW STATUS LIKE 'Threads_connected';  -- 当前连接数
6SHOW STATUS LIKE 'Threads_running';    -- 活跃连接数
7SHOW STATUS LIKE 'Queries';            -- 查询总数
8SHOW STATUS LIKE 'Slow_queries';       -- 慢查询数
9SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';  -- Buffer Pool读请求
10SHOW STATUS LIKE 'Innodb_buffer_pool_reads';          -- 磁盘读取次数

2. Performance Schema：

sql

1-- 启用Performance Schema
2UPDATE performance_schema.setup_instruments 
3SET ENABLED = 'YES', TIMED = 'YES';
4
5-- 查看最耗时的SQL
6SELECT 
7    DIGEST_TEXT,
8    COUNT_STAR,
9    AVG_TIMER_WAIT/1000000000 AS avg_ms
10FROM performance_schema.events_statements_summary_by_digest
11ORDER BY AVG_TIMER_WAIT DESC
12LIMIT 10;
13
14-- 查看表的I/O统计
15SELECT * FROM performance_schema.table_io_waits_summary_by_table
16ORDER BY sum_timer_wait DESC LIMIT 10;

3. 慢查询日志：

sql

1-- 启用慢查询日志
2SET GLOBAL slow_query_log = ON;
3SET GLOBAL long_query_time = 1;
4
5-- 分析慢查询
6mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

4. SHOW PROCESSLIST：

sql

1-- 查看当前执行的查询
2SHOW FULL PROCESSLIST;
3
4-- 杀死长时间运行的查询
5KILL QUERY 12345;

5. 监控工具：

Prometheus + Grafana：开源监控方案
Percona Monitoring and Management (PMM)：专业MySQL监控
MySQL Enterprise Monitor：官方商业监控
Zabbix：通用监控平台

关键性能指标（KPI）：

11. QPS（每秒查询数）
22. TPS（每秒事务数）
33. 连接数
44. 慢查询数量
55. Buffer Pool命中率
66. 锁等待时间
77. 复制延迟
88. 磁盘I/O

32. 什么是MySQL的连接池？

连接池是预先创建并维护一定数量的数据库连接，供应用程序重复使用。

为什么需要连接池：

java

1// 不使用连接池（性能差）
2for (int i = 0; i < 1000; i++) {
3    Connection conn = DriverManager.getConnection(url, user, password);
4    // 执行SQL
5    conn.close();  // 每次都创建和关闭连接，开销大
6}
7
8// 使用连接池（性能好）
9for (int i = 0; i < 1000; i++) {
10    Connection conn = dataSource.getConnection();  // 从池中获取
11    // 执行SQL
12    conn.close();  // 归还到池中，不是真正关闭
13}

常用连接池：

1. HikariCP（推荐）：

java

1HikariConfig config = new HikariConfig();
2config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
3config.setUsername("root");
4config.setPassword("password");
5config.setMaximumPoolSize(10);          // 最大连接数
6config.setMinimumIdle(5);               // 最小空闲连接数
7config.setConnectionTimeout(30000);     // 连接超时
8config.setIdleTimeout(600000);          // 空闲超时
9config.setMaxLifetime(1800000);         // 最大生命周期
10
11HikariDataSource dataSource = new HikariDataSource(config);

2. Druid：

java

1DruidDataSource dataSource = new DruidDataSource();
2dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
3dataSource.setUsername("root");
4dataSource.setPassword("password");
5dataSource.setInitialSize(5);           // 初始连接数
6dataSource.setMaxActive(20);            // 最大连接数
7dataSource.setMinIdle(5);               // 最小空闲连接数
8dataSource.setMaxWait(60000);           // 获取连接最大等待时间
9dataSource.setValidationQuery("SELECT 1");  // 验证查询

连接池参数配置：

properties

1# 核心参数
2maximumPoolSize=20        # 最大连接数（CPU核心数 * 2 + 磁盘数）
3minimumIdle=5             # 最小空闲连接数
4connectionTimeout=30000   # 获取连接超时时间（毫秒）
5idleTimeout=600000        # 空闲连接超时时间（10分钟）
6maxLifetime=1800000       # 连接最大生命周期（30分钟）
7
8# 连接测试
9connectionTestQuery=SELECT 1
10validationTimeout=5000

连接池监控：

java

1// HikariCP监控
2HikariPoolMXBean poolMXBean = dataSource.getHikariPoolMXBean();
3System.out.println("Active: " + poolMXBean.getActiveConnections());
4System.out.println("Idle: " + poolMXBean.getIdleConnections());
5System.out.println("Total: " + poolMXBean.getTotalConnections());
6System.out.println("Waiting: " + poolMXBean.getThreadsAwaitingConnection());

33. InnoDB和MyISAM的索引实现有什么区别？

核心区别：聚簇索引 vs 非聚簇索引

InnoDB（聚簇索引）：

1主键索引（聚簇索引）：
2    索引和数据存储在一起
3    叶子节点存储完整的行数据
4    
5    [主键索引树]
6         10
7       /    \
8      5      15
9     / \    /  \
10   [完整行数据]
11
12二级索引（非聚簇索引）：
13    叶子节点存储主键值
14    需要回表查询
15    
16    [name索引树]
17       'Bob'
18      /     \
19   'Alice'  'Carol'
20     |        |
21    [主键5]  [主键15]

MyISAM（非聚簇索引）：

1主键索引：
2    索引和数据分开存储
3    叶子节点存储数据文件指针
4    
5    [主键索引树]
6         10
7       /    \
8      5      15
9     / \    /  \
10   [指针] [指针]
11     ↓      ↓
12   [数据文件]
13
14二级索引：
15    与主键索引结构相同
16    都存储数据文件指针

对比：

特性	InnoDB	MyISAM
索引类型	聚簇索引	非聚簇索引
主键查询	一次查询	两次查询（索引+数据）
二级索引查询	两次查询（索引+回表）	两次查询（索引+数据）
数据存储	按主键顺序	按插入顺序
表文件	.ibd（索引+数据）	.MYI（索引）+ .MYD（数据）

性能影响：

sql

1-- InnoDB主键查询（快）
2SELECT * FROM users WHERE id = 100;  -- 一次B+树查找
3
4-- InnoDB二级索引查询（需回表）
5SELECT * FROM users WHERE name = 'Alice';  
6-- 1. name索引查找 -> 得到主键
7-- 2. 主键索引查找 -> 得到完整数据
8
9-- MyISAM所有查询都需要两次
10SELECT * FROM users WHERE id = 100;
11-- 1. 索引查找 -> 得到指针
12-- 2. 根据指针读取数据

34. 什么是自增主键？为什么推荐使用？

自增主键是数据库自动生成的递增整数主键。

定义自增主键：

sql

1CREATE TABLE users (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(50)
4);
5
6-- 插入数据时不需要指定id
7INSERT INTO users (name) VALUES ('Alice');
8INSERT INTO users (name) VALUES ('Bob');
9-- id自动为1, 2, 3...

为什么推荐使用：

1. 顺序插入，减少页分裂：

1使用自增主键：
2[1][2][3][4][5] -> 插入6 -> [1][2][3][4][5][6]
3顺序插入，不需要移动数据
4
5使用UUID：
6[uuid1][uuid3][uuid5] -> 插入uuid2 -> [uuid1][uuid2][uuid3][uuid5]
7需要移动数据，页分裂频繁

2. 索引效率高：

sql

1-- 自增主键：B+树紧凑
2-- 范围查询效率高
3SELECT * FROM users WHERE id BETWEEN 100 AND 200;
4
5-- UUID：B+树稀疏
6-- 范围查询效率低

3. 占用空间小：

sql

1-- INT：4字节
2id INT AUTO_INCREMENT
3
4-- BIGINT：8字节
5id BIGINT AUTO_INCREMENT
6
7-- UUID：36字节（字符串）或16字节（BINARY）
8id CHAR(36)

4. 性能对比：

1插入性能：
2- 自增主键：顺序写入，快
3- UUID：随机写入，慢
4
5索引大小：
6- 自增主键：小
7- UUID：大（约3-4倍）
8
9查询性能：
10- 自增主键：快
11- UUID：慢

自增主键的问题：

1. 分布式环境ID冲突：

java

1// 解决方案1：雪花算法
2long id = snowflake.nextId();
3
4// 解决方案2：数据库号段
5// 每个服务器分配不同的起始值和步长
6// 服务器1：1, 3, 5, 7...
7// 服务器2：2, 4, 6, 8...

2. 数据迁移：

sql

1-- 设置起始值
2ALTER TABLE users AUTO_INCREMENT = 10000;

3. 安全问题：

1暴露数据量：
2/user/1
3/user/2
4/user/3
5可以看出有3个用户
6
7解决：使用业务ID（订单号）而不是自增ID

35. MySQL的COUNT(*)和COUNT(1)有什么区别？

结论：在InnoDB中，COUNT(*) 和 COUNT(1) 性能基本相同。

COUNT的几种形式：

sql

1-- 1. COUNT(*)：统计所有行
2SELECT COUNT(*) FROM users;
3
4-- 2. COUNT(1)：统计所有行
5SELECT COUNT(1) FROM users;
6
7-- 3. COUNT(列名)：统计非NULL的行
8SELECT COUNT(name) FROM users;
9
10-- 4. COUNT(DISTINCT 列名)：统计不重复的非NULL行
11SELECT COUNT(DISTINCT name) FROM users;

性能对比：

InnoDB：

sql

1-- COUNT(*) 和 COUNT(1) 性能相同
2-- 都会选择最小的索引进行扫描
3EXPLAIN SELECT COUNT(*) FROM users;
4EXPLAIN SELECT COUNT(1) FROM users;
5-- 执行计划相同
6
7-- COUNT(列名) 需要判断NULL
8SELECT COUNT(name) FROM users;
9-- 稍慢，因为需要检查每行的name是否为NULL

MyISAM：

sql

1-- MyISAM维护了行数，COUNT(*)非常快
2SELECT COUNT(*) FROM users;  -- O(1)
3
4-- 但有WHERE条件时，仍需扫描
5SELECT COUNT(*) FROM users WHERE age > 18;  -- O(n)

优化COUNT查询：

1. 使用覆盖索引：

sql

1-- 创建索引
2CREATE INDEX idx_status ON users(status);
3
4-- 使用覆盖索引
5SELECT COUNT(*) FROM users WHERE status = 1;
6-- 只扫描索引，不回表

2. 使用近似值：

sql

1-- 使用EXPLAIN估算
2EXPLAIN SELECT * FROM users;
3-- rows列显示估算行数
4
5-- 查询统计信息
6SELECT TABLE_ROWS 
7FROM information_schema.TABLES 
8WHERE TABLE_NAME = 'users';

3. 维护计数表：

sql

1-- 创建计数表
2CREATE TABLE user_count (
3    count INT
4);
5
6-- 使用触发器维护
7CREATE TRIGGER after_user_insert
8AFTER INSERT ON users
9FOR EACH ROW
10UPDATE user_count SET count = count + 1;
11
12-- 查询时直接读取
13SELECT count FROM user_count;

4. 使用Redis缓存：

java

1// 插入用户时
2userDao.insert(user);
3redis.incr("user:count");
4
5// 查询总数
6long count = redis.get("user:count");

实际应用建议：

sql

1-- 1. 小表（<10万）：直接COUNT
2SELECT COUNT(*) FROM small_table;
3
4-- 2. 大表无WHERE：使用统计表或缓存
5SELECT count FROM count_cache;
6
7-- 3. 大表有WHERE：优化索引
8CREATE INDEX idx_condition ON big_table(condition_column);
9SELECT COUNT(*) FROM big_table WHERE condition_column = value;
10
11-- 4. 分页场景：使用游标分页代替COUNT
12-- 不要：SELECT COUNT(*) + LIMIT offset
13-- 使用：WHERE id > last_id LIMIT 20

36. 如何处理MySQL的大数据量分页？

传统分页问题：

sql

1-- 深度分页性能差
2SELECT * FROM users ORDER BY id LIMIT 1000000, 20;
3-- 需要扫描1000020行，丢弃前1000000行

优化方案：

1. 使用子查询优化：

sql

1-- 先查ID，再关联
2SELECT * FROM users 
3WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 1000000, 1)
4LIMIT 20;

2. 使用游标分页（推荐）：

sql

1-- 记录上次最后一条的ID
2SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 20;

3. 延迟关联：

sql

1SELECT * FROM users u
2INNER JOIN (
3    SELECT id FROM users ORDER BY id LIMIT 1000000, 20
4) AS t ON u.id = t.id;

37. 什么是MySQL的半同步复制？

半同步复制是介于异步复制和全同步复制之间的方案。

工作原理：

11. 主库执行事务
22. 写入binlog
33. 等待至少一个从库确认接收binlog
44. 返回客户端成功

配置：

sql

1-- 主库安装插件
2INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
3
4-- 从库安装插件
5INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
6
7-- 启用半同步
8SET GLOBAL rpl_semi_sync_master_enabled = 1;
9SET GLOBAL rpl_semi_sync_slave_enabled = 1;

38. 如何优化INSERT语句？

优化方法：

1. 批量插入：

sql

1-- 不好
2INSERT INTO users VALUES (1, 'A');
3INSERT INTO users VALUES (2, 'B');
4
5-- 好
6INSERT INTO users VALUES (1, 'A'), (2, 'B'), (3, 'C');

2. 使用LOAD DATA：

sql

1LOAD DATA INFILE 'data.csv'
2INTO TABLE users
3FIELDS TERMINATED BY ','
4LINES TERMINATED BY '\n';

3. 禁用索引：

sql

1ALTER TABLE users DISABLE KEYS;
2-- 批量插入
3ALTER TABLE users ENABLE KEYS;

4. 调整参数：

sql

1SET autocommit = 0;
2-- 批量插入
3COMMIT;

39. MySQL的GROUP BY和HAVING有什么区别？

区别：

特性	WHERE	HAVING
作用时机	分组前	分组后
作用对象	行	组
可用函数	不能用聚合函数	可以用聚合函数

示例：

sql

1SELECT 
2    department,
3    COUNT(*) as emp_count,
4    AVG(salary) as avg_salary
5FROM employees
6WHERE salary > 5000          -- 过滤行
7GROUP BY department
8HAVING COUNT(*) > 10;        -- 过滤组

40. 什么是外键约束？

外键约束保证引用完整性。

创建外键：

sql

1CREATE TABLE orders (
2    id INT PRIMARY KEY,
3    user_id INT,
4    FOREIGN KEY (user_id) REFERENCES users(id)
5        ON DELETE CASCADE
6        ON UPDATE CASCADE
7);

级联操作：

CASCADE：级联删除/更新
SET NULL：设置为NULL
RESTRICT：拒绝操作
NO ACTION：不做任何操作

41-60题简要答案

41. MySQL的UNION和UNION ALL有什么区别？

UNION：合并结果集并去重 UNION ALL：合并结果集不去重（性能更好）

sql

1SELECT id FROM users
2UNION          -- 去重，慢
3SELECT id FROM orders;
4
5SELECT id FROM users
6UNION ALL      -- 不去重，快
7SELECT id FROM orders;

42. 如何查看MySQL的执行计划？

使用EXPLAIN或EXPLAIN ANALYZE：

sql

1EXPLAIN SELECT * FROM users WHERE name = 'Alice';
2EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'Alice';

43. 什么是MySQL的全文索引？

全文索引用于文本搜索：

sql

1CREATE FULLTEXT INDEX idx_content ON articles(content);
2
3SELECT * FROM articles 
4WHERE MATCH(content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);

44. 如何处理MySQL的乱码问题？

原因：字符集不一致

解决：

sql

1-- 统一使用utf8mb4
2ALTER DATABASE mydb CHARACTER SET utf8mb4;
3ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;
4SET NAMES utf8mb4;

45. MySQL的LEFT JOIN和RIGHT JOIN有什么区别？

LEFT JOIN：保留左表所有记录 RIGHT JOIN：保留右表所有记录

sql

1SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id;
2-- 返回所有用户，包括没有订单的用户

46. 什么是MySQL的复合索引？

复合索引是包含多个列的索引：

sql

1CREATE INDEX idx_name_age ON users(name, age);
2-- 遵循最左前缀原则

47. 如何进行MySQL的容量规划？

评估因素：

数据增长速度
查询QPS
存储空间需求
备份策略
扩展性需求

48. 什么是MySQL的查询缓存？

查询缓存（MySQL 8.0已移除）：

sql

1-- MySQL 5.7
2SET GLOBAL query_cache_type = ON;
3SET GLOBAL query_cache_size = 256M;

49. 如何优化MySQL的配置参数？

关键参数：

ini

1innodb_buffer_pool_size = 8G    # 物理内存的50-80%
2innodb_log_file_size = 512M
3max_connections = 1000
4innodb_flush_log_at_trx_commit = 1

50. 什么是MySQL的事件调度器？

事件调度器用于定时任务：

sql

1CREATE EVENT clean_old_data
2ON SCHEDULE EVERY 1 DAY
3DO
4DELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);

51. 如何处理MySQL的锁等待超时？

配置超时时间：

sql

1SET innodb_lock_wait_timeout = 50;  -- 秒

52. 什么是MySQL的游标？

游标用于逐行处理结果集：

sql

1DECLARE cur CURSOR FOR SELECT id FROM users;
2OPEN cur;
3FETCH cur INTO user_id;
4CLOSE cur;

53. 如何进行MySQL的版本升级？

步骤：

备份数据
测试环境验证
检查兼容性
灰度升级
监控观察

54. 什么是MySQL的分布式事务？

XA事务支持分布式事务：

sql

1XA START 'xid1';
2UPDATE accounts SET balance = balance - 100 WHERE id = 1;
3XA END 'xid1';
4XA PREPARE 'xid1';
5XA COMMIT 'xid1';

55. 如何处理MySQL的主键冲突？

方法：

sql

1-- 忽略冲突
2INSERT IGNORE INTO users VALUES (1, 'Alice');
3
4-- 更新冲突
5INSERT INTO users VALUES (1, 'Alice')
6ON DUPLICATE KEY UPDATE name = 'Alice';
7
8-- 替换
9REPLACE INTO users VALUES (1, 'Alice');

56. 什么是MySQL的存储函数？

存储函数返回单个值：

sql

1CREATE FUNCTION get_user_name(uid INT)
2RETURNS VARCHAR(50)
3BEGIN
4    DECLARE uname VARCHAR(50);
5    SELECT name INTO uname FROM users WHERE id = uid;
6    RETURN uname;
7END;

57. 如何进行MySQL的安全加固？

措施：

最小权限原则
禁用root远程登录
使用SSL连接
定期更新密码
启用审计日志

58. 什么是MySQL的复制延迟？

复制延迟是主从数据同步的时间差。

监控：

sql

1SHOW SLAVE STATUS\G
2-- 查看Seconds_Behind_Master

59. 如何优化MySQL的磁盘I/O？

优化方法：

使用SSD
RAID配置
调整innodb_flush_method
增加Buffer Pool
优化SQL减少I/O

60. MySQL 8.0有哪些新特性？

主要新特性：

窗口函数：ROW_NUMBER(), RANK()
CTE（公用表表达式）：WITH子句
JSON增强：JSON_TABLE()
隐藏索引：测试索引影响
降序索引：真正的降序
默认utf8mb4
原子DDL
角色管理

sql

1-- 窗口函数
2SELECT 
3    name,
4    salary,
5    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
6FROM employees;
7
8-- CTE
9WITH high_salary AS (
10    SELECT * FROM employees WHERE salary > 10000
11)
12SELECT * FROM high_salary;

总结

本文档涵盖了MySQL面试的60个核心问题，包括：

存储引擎和索引：InnoDB、MyISAM、B+树、索引优化
事务和锁：ACID、隔离级别、MVCC、死锁
性能优化：慢查询、EXPLAIN、分库分表、缓存
高可用：主从复制、读写分离、备份恢复
安全：SQL注入、权限管理、字符集
高级特性：存储过程、触发器、视图、分区表

掌握这些知识点，能够应对大部分MySQL相关的面试问题。

forum

评论区 / Comments

share分享文章 / Share

面试题目列表​

1. MySQL的存储引擎有哪些？它们有什么区别？​

2. MySQL中的索引类型有哪些？如何选择合适的索引？​

3. 什么是回表查询？什么是覆盖索引？​

4. MySQL的事务特性（ACID）是什么？​

5. MySQL的四种隔离级别是什么？分别会产生什么问题？​

6. 什么是MVCC？它是如何工作的？​

7. MySQL中的锁有哪些类型？​

8. 什么是死锁？如何避免死锁？​

9. MySQL的主从复制原理是什么？​

10. 如何优化MySQL查询性能？​

11. 什么是慢查询日志？如何使用？​

12. Explain执行计划如何分析？​

13. MySQL的分区表是什么？​

14. 什么是表锁和行锁？​

15. InnoDB的Buffer Pool是什么？​

16. 什么是binlog、redo log和undo log？​

17. MySQL如何实现高可用？​

18. 什么是读写分离？​

19. 如何进行MySQL备份和恢复？​

20. MySQL的字符集和排序规则是什么？​

21. 什么是联合索引的最左前缀原则？​

22. 为什么InnoDB使用B+树而不是B树？​

23. MySQL的JOIN查询有哪些类型？​

24. 什么是临时表？​

25. 如何处理大表的DDL操作？​

26. MySQL的分库分表策略有哪些？​

27. 什么是SQL注入？如何防止？​

28. MySQL的存储过程和函数有什么区别？​

29. 什么是触发器？​

30. MySQL的视图是什么？​

31. 如何进行MySQL性能监控？​

32. 什么是MySQL的连接池？​

33. InnoDB和MyISAM的索引实现有什么区别？​

34. 什么是自增主键？为什么推荐使用？​

35. MySQL的COUNT(*)和COUNT(1)有什么区别？​

36. 如何处理MySQL的大数据量分页？​

37. 什么是MySQL的半同步复制？​

38. 如何优化INSERT语句？​

39. MySQL的GROUP BY和HAVING有什么区别？​

40. 什么是外键约束？​

41-60题简要答案​

41. MySQL的UNION和UNION ALL有什么区别？​

42. 如何查看MySQL的执行计划？​

43. 什么是MySQL的全文索引？​

44. 如何处理MySQL的乱码问题？​

45. MySQL的LEFT JOIN和RIGHT JOIN有什么区别？​

46. 什么是MySQL的复合索引？​

47. 如何进行MySQL的容量规划？​

48. 什么是MySQL的查询缓存？​

49. 如何优化MySQL的配置参数？​

50. 什么是MySQL的事件调度器？​

51. 如何处理MySQL的锁等待超时？​

52. 什么是MySQL的游标？​

53. 如何进行MySQL的版本升级？​

54. 什么是MySQL的分布式事务？​

55. 如何处理MySQL的主键冲突？​

56. 什么是MySQL的存储函数？​

57. 如何进行MySQL的安全加固？​

58. 什么是MySQL的复制延迟？​

59. 如何优化MySQL的磁盘I/O？​

60. MySQL 8.0有哪些新特性？​

总结​

评论区 / Comments

面试题目列表

1. MySQL的存储引擎有哪些？它们有什么区别？

2. MySQL中的索引类型有哪些？如何选择合适的索引？

3. 什么是回表查询？什么是覆盖索引？

4. MySQL的事务特性（ACID）是什么？

5. MySQL的四种隔离级别是什么？分别会产生什么问题？

6. 什么是MVCC？它是如何工作的？

7. MySQL中的锁有哪些类型？

8. 什么是死锁？如何避免死锁？

9. MySQL的主从复制原理是什么？

10. 如何优化MySQL查询性能？

11. 什么是慢查询日志？如何使用？

12. Explain执行计划如何分析？

13. MySQL的分区表是什么？

14. 什么是表锁和行锁？

15. InnoDB的Buffer Pool是什么？

16. 什么是binlog、redo log和undo log？

17. MySQL如何实现高可用？

18. 什么是读写分离？

19. 如何进行MySQL备份和恢复？

20. MySQL的字符集和排序规则是什么？

21. 什么是联合索引的最左前缀原则？

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

23. MySQL的JOIN查询有哪些类型？

24. 什么是临时表？

25. 如何处理大表的DDL操作？

26. MySQL的分库分表策略有哪些？

27. 什么是SQL注入？如何防止？

28. MySQL的存储过程和函数有什么区别？

29. 什么是触发器？

30. MySQL的视图是什么？

31. 如何进行MySQL性能监控？

32. 什么是MySQL的连接池？

33. InnoDB和MyISAM的索引实现有什么区别？

34. 什么是自增主键？为什么推荐使用？

35. MySQL的COUNT(*)和COUNT(1)有什么区别？

36. 如何处理MySQL的大数据量分页？

37. 什么是MySQL的半同步复制？

38. 如何优化INSERT语句？

39. MySQL的GROUP BY和HAVING有什么区别？

40. 什么是外键约束？

41-60题简要答案

41. MySQL的UNION和UNION ALL有什么区别？

42. 如何查看MySQL的执行计划？

43. 什么是MySQL的全文索引？

44. 如何处理MySQL的乱码问题？

45. MySQL的LEFT JOIN和RIGHT JOIN有什么区别？

46. 什么是MySQL的复合索引？

47. 如何进行MySQL的容量规划？

48. 什么是MySQL的查询缓存？

49. 如何优化MySQL的配置参数？

50. 什么是MySQL的事件调度器？

51. 如何处理MySQL的锁等待超时？

52. 什么是MySQL的游标？

53. 如何进行MySQL的版本升级？

54. 什么是MySQL的分布式事务？

55. 如何处理MySQL的主键冲突？

56. 什么是MySQL的存储函数？

57. 如何进行MySQL的安全加固？

58. 什么是MySQL的复制延迟？

59. 如何优化MySQL的磁盘I/O？

60. MySQL 8.0有哪些新特性？

总结