MySQL 常见面试问题总结_mysql面试题

技术文档

MySQL

MySQL 基础概念
- 1. 什么是MySQL？它有哪些特点？
- 2. MySQL中的存储引擎有哪些？它们有什么区别？
- 3. InnoDB和MyISAM的主要区别是什么？
- 4. 什么是事务？MySQL如何支持事务？
- ACID原则详解
- - 1. Atomicity（原子性）
  - 2. Consistency（一致性）
  - 3. Isolation（隔离性）
  - 4. Durability（持久性）
MySQL 数据库设计
- 1. 什么是主键、外键和索引？
- - 主键（Primary Key）
  - 外键（Foreign Key）
  - 索引（Index）
- 2. 数据库范式是什么？你通常设计到第几范式？
- - 数据库范式
- 3. 如何优化数据库表结构设计？
- - 优化策略
- 4. 什么是反范式化设计？什么时候使用它？
- - 反范式化设计
MySQL SQL查询
- 1. 解释SELECT语句的执行顺序
- - SQL查询逻辑执行顺序：
  - 实际示例：
- 2. JOIN有哪些类型？它们有什么区别？
- - JOIN类型对比表：
- 3. 什么是子查询？有哪些类型的子查询？
- - 子查询定义：
  - 子查询类型：
  - - 1. WHERE子句中的子查询
    - 2. FROM子句中的子查询（派生表）
    - 3. SELECT子句中的子查询（标量子查询）
    - 4. EXISTS/NOT EXISTS子查询
    - 5. IN/NOT IN子查询
- 4. 如何优化SQL查询性能？
- - 索引优化
  - 查询优化
  - 分页优化
- 5. EXPLAIN命令是做什么的？如何使用它？
- - EXPLAIN命令作用
  - 基本使用方法
  - 关键输出字段
  - 使用案例
MySQL索引
- 1. MySQL中有哪些类型的索引？
- 2. 什么是聚簇索引和非聚簇索引？
- 3. 如何创建高效的索引？
- 4. 什么情况下索引会失效？
- 5. 什么是覆盖索引？
- 6. 如何判断一个查询是否使用了索引？
MySQL性能优化
- 1. 如何优化MySQL数据库性能？
- 2. 什么是慢查询？如何分析和优化慢查询？
- 3. 数据库分库分表的策略有哪些？
- 4. 什么是读写分离？如何实现？
- 5. 如何优化大表查询？
MySQL事务与锁
- 1. MySQL中有哪些事务隔离级别？
- 2. 什么是脏读、不可重复读和幻读？
- 3. MySQL中有哪些锁类型？
- 4. 什么是死锁？如何避免和解决死锁？
- 5. 乐观锁和悲观锁的区别是什么？
- 6. 什么是MVCC？
- - MVCC基本概念
  - MVCC核心原理
  - MVCC在隔离级别中的应用
  - MVCC操作示例
  - MVCC优缺点分析
- MVCC实现关键点
MySQL高可用与备份
- 1. MySQL有哪些高可用方案？
- 2. 如何进行数据库备份和恢复？
- 3. 什么是主从复制？如何配置？
- 4. 主从复制的延迟问题如何解决？
- 5. 什么是读写分离？如何实现？
MySQL其他特性
- 1. 什么是视图？它有什么优缺点？
- 2. 存储过程和函数有什么区别？

MySQL 基础概念

1. 什么是MySQL？它有哪些特点？

MySQL 是一个开源的关系型数据库管理系统(RDBMS)，由瑞典MySQL AB公司开发，现在属于Oracle公司。

主要特点：

✅ 开源免费（社区版）
⚡ 性能高、运行速度快
👥 支持多用户、多线程
🖥️ 跨平台支持（Windows、Linux、Mac等）
💻 支持多种编程语言接口
📊 使用标准的SQL数据语言
🔄 提供事务支持和外键约束
🏢 支持大型数据库
🛠️ 提供多种存储引擎选择
🔒 良好的安全性和连接性

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

常见存储引擎：

InnoDB：支持事务、行级锁、外键约束，MySQL 5.5后的默认引擎
MyISAM：不支持事务和行级锁，但查询速度快
MEMORY：数据存储在内存中，速度快但关机后数据丢失
ARCHIVE：适合存储大量归档数据
CSV：以CSV格式存储数据
BLACKHOLE：接收数据但不存储

存储引擎对比：

特性 InnoDB MyISAM MEMORY 事务支持 ✔️ ✖️ ✖️ 锁机制行级锁表级锁表级锁外键支持 ✔️ ✖️ ✖️ 崩溃恢复 ✔️ ✖️ ✖️ 全文索引 ✔️(5.6+) ✔️ ✖️ 存储限制 64TB 256TB RAM大小

3. InnoDB和MyISAM的主要区别是什么？

核心区别：

事务支持：
- InnoDB：支持ACID事务
- MyISAM：不支持事务
锁级别：
- InnoDB：行级锁
- MyISAM：表级锁
外键约束：
- InnoDB：支持
- MyISAM：不支持
崩溃恢复：
- InnoDB：有崩溃后安全恢复能力
- MyISAM：崩溃后数据易损坏
性能特点：
- InnoDB：写操作性能更好
- MyISAM：读操作性能更好
存储结构：
- InnoDB：聚簇索引
- MyISAM：非聚簇索引

4. 什么是事务？MySQL如何支持事务？

事务定义：
事务是一组原子性的SQL查询，要么全部执行成功，要么全部失败回滚。

MySQL事务支持：

START TRANSACTION;-- SQL语句COMMIT; -- 提交事务-- 或ROLLBACK; -- 回滚事务

事务特性

MySQL事务的实现方式：

🛠️ 通过InnoDB等支持事务的存储引擎实现
🔄 默认自动提交(auto-commit)模式
⚙️ 可以使用 SET autocommit=0 关闭自动提交

ACID原则详解

ACID原则是事务的四个基本特性：

1. Atomicity（原子性）

🔹 事务是不可分割的工作单位
🔹 事务中的操作要么全部完成，要么全部不完成
🔹 失败时会自动回滚所有操作

2. Consistency（一致性）

🔹 事务执行前后，数据库从一个一致状态变到另一个一致状态
🔹 不会破坏数据库的完整性约束
🔹 例如：转账前后总金额保持不变

3. Isolation（隔离性）

🔹 多个事务并发执行时互不干扰
🔹 通过隔离级别控制：

读未提交（Read Uncommitted）
读已提交（Read Committed）
可重复读（Repeatable Read）
串行化（Serializable）
🔹 防止脏读、不可重复读和幻读问题

4. Durability（持久性）

🔹 事务提交后，修改将永久保存
🔹 即使系统崩溃，数据也不会丢失
🔹 通过事务日志和恢复机制保证

MySQL 数据库设计

1. 什么是主键、外键和索引？

主键（Primary Key）

🔑 定义：

唯一标识表中每行记录的列或列组合
不允许NULL值
每个表只能有一个主键

特点：

保证实体完整性
自动创建聚集索引(在InnoDB中)
常用自增整数(AUTO_INCREMENT)作为主键

外键（Foreign Key）

🌉 定义：

建立两个表数据之间关联的字段
引用另一个表的主键
保证引用完整性

特点：

防止无效数据插入
可设置级联操作(CASCADE)
InnoDB支持，MyISAM不支持

索引（Index）

📈 定义：

提高查询性能的数据结构
类似书籍的目录

类型：

普通索引
唯一索引
主键索引
组合索引
全文索引

2. 数据库范式是什么？你通常设计到第几范式？

数据库范式

📐 定义：

规范化数据库设计的指导原则
减少数据冗余，提高数据一致性

主要范式：

范式要求示例问题解决 1NF 属性原子性，无重复组消除重复列 2NF 满足1NF，且非主属性完全依赖主键消除部分依赖 3NF 满足2NF，且消除传递依赖消除非主属性间的依赖 BCNF 更强的3NF 消除主属性对候选键的部分依赖

设计建议：

通常设计到第三范式(3NF)
根据实际性能需求考虑反范式化
关联查询多的场景可适当降低范式级别

3. 如何优化数据库表结构设计？

优化策略

🚀 结构优化：

选择合适的数据类型
- 用INT而非VARCHAR存储数字
- 用DATETIME/TIMESTAMP存储时间
规范化设计
- 遵循适当的范式级别
- 合理拆分大表
命名规范
- 使用有意义的表名和字段名
- 保持命名风格一致

🔍 索引优化：

为常用查询条件创建索引
避免过度索引(影响写性能)
使用组合索引时注意最左前缀原则

📊 其他优化：

适当使用垂直/水平分表
考虑使用分区表
为常用查询创建视图

4. 什么是反范式化设计？什么时候使用它？

反范式化设计

🔄 定义：

故意增加冗余数据的设计方法
违反范式原则以提高查询性能

使用场景：

读密集场景
- 报表系统
- 数据分析应用
性能关键路径
- 高频查询的表
- 需要快速响应的核心业务
特定技术需求
- 数据仓库
- OLAP系统

实现方式：

增加冗余字段
使用预计算字段
创建汇总表

⚠️ 注意事项：

需要额外维护数据一致性
适合读多写少的场景
需权衡查询性能与数据一致性

MySQL SQL查询

1. 解释SELECT语句的执行顺序

SQL查询逻辑执行顺序：

FROM 和 JOIN：确定数据来源表
WHERE：筛选符合条件的行
GROUP BY：对数据进行分组
HAVING：筛选分组后的数据
SELECT：选择要返回的列
DISTINCT：去除重复行
ORDER BY：对结果排序
LIMIT/OFFSET：限制返回行数

实际示例：

SELECT DISTINCT column1, COUNT(*) FROM table1JOIN table2 ON table1.id = table2.idWHERE condition = \'value\'GROUP BY column1HAVING COUNT(*) > 1ORDER BY column1LIMIT 10;

2. JOIN有哪些类型？它们有什么区别？

JOIN类型对比表：

JOIN类型语法描述结果 INNER JOIN A INNER JOIN B ON A.id=B.id 只返回两表中匹配的行两表的交集 LEFT JOIN A LEFT JOIN B ON A.id=B.id 返回左表所有行+匹配的右表行左表全集+匹配部分 RIGHT JOIN A RIGHT JOIN B ON A.id=B.id 返回右表所有行+匹配的左表行右表全集+匹配部分 FULL JOIN A FULL JOIN B ON A.id=B.id 返回两表所有行(MySQL用UNION实现) 两表的并集 CROSS JOIN A CROSS JOIN B 返回两表的笛卡尔积所有可能的组合

3. 什么是子查询？有哪些类型的子查询？

子查询定义：

🔍 嵌套在另一个查询中的SELECT语句

子查询类型：

1. WHERE子句中的子查询

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products)

2. FROM子句中的子查询（派生表）

SELECT * FROM (SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id) as user_ordersWHERE order_count > 5

3. SELECT子句中的子查询（标量子查询）

SELECT product_name, (SELECT COUNT(*) FROM orders WHERE product_id = p.id) as order_countFROM products p

4. EXISTS/NOT EXISTS子查询

SELECT * FROM customers cWHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id)

5. IN/NOT IN子查询

SELECT * FROM employeesWHERE department_id IN (SELECT id FROM departments WHERE location = \'NY\')

4. 如何优化SQL查询性能？

索引优化

合理创建索引
- 为WHERE、JOIN、ORDER BY字段建立索引
- 组合索引字段顺序：高频查询字段在前，高选择性字段在前

-- 创建组合索引示例CREATE INDEX idx_user_status ON users(status, register_date);

避免索引失效场景
- 避免对索引列使用函数或运算
- 避免使用!=或操作符
- 避免使用前导通配符LIKE查询

-- 索引失效的反例SELECT * FROM users WHERE DATE(create_time) = \'2023-01-01\';-- 优化后的正例SELECT * FROM users WHERE create_time >= \'2023-01-01\' AND create_time < \'2023-01-02\';

查询优化

避免全表扫描
- 使用LIMIT限制返回行数
- 避免SELECT *，只查询需要的列

-- 全表扫描的反例SELECT * FROM order_details;-- 优化后的正例SELECT order_id, product_id, quantity FROM order_details WHERE order_id = 1001;

JOIN优化
- 确保JOIN字段有索引
- 小表驱动大表

-- JOIN优化示例SELECT * FROM small_table s JOIN large_table l ON s.id = l.id;

分页优化

-- 低效的分页查询SELECT * FROM large_table LIMIT 100000, 10;-- 高效的分页查询(使用覆盖索引)SELECT * FROM large_table WHERE id > 100000 ORDER BY id LIMIT 10;

5. EXPLAIN命令是做什么的？如何使用它？

EXPLAIN命令作用

分析SQL查询的执行计划
查看MySQL如何使用索引
识别查询性能瓶颈

基本使用方法

-- 基本语法EXPLAIN SELECT * FROM users WHERE id = 1;-- 查看详细执行计划(MySQL 8.0+)EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 100;

关键输出字段

type: 访问类型，从好到差：
- system > const > eq_ref > ref > range > index > ALL
key: 实际使用的索引
rows: 预估需要检查的行数
Extra: 额外信息
- Using index: 使用覆盖索引
- Using temporary: 使用临时表
- Using filesort: 需要额外排序

使用案例

-- 分析查询执行计划EXPLAIN SELECT u.name, o.order_no FROM users u JOIN orders o ON u.id = o.user_idWHERE u.status = 1 AND o.amount > 1000ORDER BY o.create_time DESC;-- 优化后添加索引ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount);ALTER TABLE users ADD INDEX idx_status (status);

MySQL索引

1. MySQL中有哪些类型的索引？

主要索引类型：

普通索引：最基本的索引类型，没有唯一性限制
唯一索引：索引列的值必须唯一，允许NULL值
主键索引：特殊的唯一索引，不允许NULL值
组合索引：多个列组合创建的索引
全文索引：用于全文搜索
空间索引：用于地理空间数据类型
前缀索引：对字符串前几个字符建立的索引

-- 创建各类索引示例CREATE INDEX idx_name ON users(name); -- 普通索引CREATE UNIQUE INDEX idx_email ON users(email); -- 唯一索引ALTER TABLE users ADD PRIMARY KEY (id); -- 主键索引CREATE INDEX idx_name_age ON users(name, age); -- 组合索引

2. 什么是聚簇索引和非聚簇索引？

聚簇索引(Clustered Index)：

索引和数据存储在一起
InnoDB的主键索引就是聚簇索引
一个表只能有一个聚簇索引
物理存储顺序与索引顺序一致

非聚簇索引(Non-clustered Index)：

索引和数据分开存储
MyISAM使用的都是非聚簇索引
一个表可以有多个非聚簇索引
通过指针指向实际数据位置

3. 如何创建高效的索引？

高效索引创建原则：

选择区分度高的列建立索引
频繁作为查询条件的列应该建立索引
组合索引遵循最左前缀原则
避免创建过多索引(影响写性能)
尽量使用数据量小的数据类型作为索引

-- 高效组合索引示例CREATE INDEX idx_status_created ON orders(status, created_at);-- 低效索引示例(区分度低)CREATE INDEX idx_gender ON users(gender);

4. 什么情况下索引会失效？

索引失效常见场景：

对索引列使用函数或运算
使用!=或操作符
使用前导通配符的LIKE查询
隐式类型转换
OR条件未全部使用索引
不符合最左前缀原则的组合索引

-- 索引失效示例SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 使用函数SELECT * FROM products WHERE name LIKE \'%apple%\'; -- 前导通配符SELECT * FROM orders WHERE amount+100 > 500; -- 对列运算

5. 什么是覆盖索引？

覆盖索引(Covering Index)：

查询的列都包含在索引中
不需要回表查询数据行
显著提高查询性能

-- 覆盖索引示例CREATE INDEX idx_user_order ON orders(user_id, order_date, amount);-- 使用覆盖索引的查询SELECT user_id, order_date FROM orders WHERE user_id = 1001 AND order_date > \'2023-01-01\';

6. 如何判断一个查询是否使用了索引？

判断方法：

使用EXPLAIN命令查看执行计划
检查key列是否显示使用的索引名
检查type列是否为ref、range等较好的类型
检查Extra列是否有\"Using index\"提示

-- 检查索引使用情况EXPLAIN SELECT * FROM users WHERE id = 1001;-- 强制使用/忽略索引SELECT * FROM users USE INDEX(idx_name) WHERE name = \'John\';SELECT * FROM users IGNORE INDEX(idx_name) WHERE name = \'John\';

MySQL性能优化

1. 如何优化MySQL数据库性能？

数据库性能优化策略：

服务器配置优化：

调整innodb_buffer_pool_size（通常设为物理内存的50-70%）
优化query_cache_size（MySQL 8.0已移除查询缓存）
配置合理的max_connections

SQL优化：

避免SELECT *，只查询需要的列
使用预处理语句防止SQL注入
合理使用JOIN替代子查询

架构优化：

实现读写分离
考虑分库分表
使用缓存层（如Redis）

-- 查看当前配置SHOW VARIABLES LIKE \'innodb_buffer_pool_size\';SHOW STATUS LIKE \'Threads_connected\';

2. 什么是慢查询？如何分析和优化慢查询？

慢查询定义：

执行时间超过long_query_time阈值（默认10秒）的查询
记录在慢查询日志中

分析优化步骤：

开启慢查询日志：

SET GLOBAL slow_query_log = \'ON\';SET GLOBAL long_query_time = 2; -- 设置为2秒

使用EXPLAIN分析慢查询：

EXPLAIN SELECT * FROM orders WHERE create_time < \'2023-01-01\';

优化方法：

为慢查询中的条件字段添加索引
重写复杂查询
优化表结构

-- 查看慢查询日志位置SHOW VARIABLES LIKE \'slow_query_log_file\';

3. 数据库分库分表的策略有哪些？

分库分表策略：

水平分表：

按行拆分到多个结构相同的表
常用拆分方式：
- 按ID范围分表
- 按哈希值分表
- 按时间分表

垂直分表：

按列拆分到不同的表
将常用字段和不常用字段分开

分库策略：

按业务模块分库
读写分离主从库

-- 水平分表示例CREATE TABLE orders_2023 (...);CREATE TABLE orders_2024 (...);

4. 什么是读写分离？如何实现？

读写分离概念：

读操作（SELECT）在从库执行
写操作（INSERT/UPDATE/DELETE）在主库执行

实现方式：

主从复制配置：

-- 主库配置[mysqld]server-id=1log-bin=mysql-binbinlog-format=ROW-- 从库配置[mysqld]server-id=2relay-log=mysql-relay-binread-only=1

使用中间件：

MySQL Router
ProxySQL
ShardingSphere

应用层实现：

配置多数据源
根据SQL类型路由到不同数据源

5. 如何优化大表查询？

大表查询优化方案：

索引优化：

为查询条件创建合适的索引
使用覆盖索引减少回表

查询优化：

添加LIMIT限制返回行数
避免OFFSET大数值的分页

-- 优化大表分页-- 传统低效分页SELECT * FROM large_table LIMIT 1000000, 10;-- 优化后分页SELECT * FROM large_table WHERE id > 1000000 ORDER BY id LIMIT 10;

架构优化：

考虑分区表（PARTITION）
历史数据归档
使用物化视图

-- 创建分区表示例CREATE TABLE logs ( id INT, log_date DATE) PARTITION BY RANGE (YEAR(log_date)) ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025));

MySQL事务与锁

1. MySQL中有哪些事务隔离级别？

四种事务隔离级别：

READ UNCOMMITTED（读未提交）
- 事务可以读取未提交的数据
- 性能最好，但会出现脏读
READ COMMITTED（读已提交）
- 只能读取已提交的数据
- 解决脏读，但会出现不可重复读
- Oracle默认级别
REPEATABLE READ（可重复读）
- 同一事务中多次读取结果一致
- 解决脏读和不可重复读
- MySQL默认级别（InnoDB）
SERIALIZABLE（串行化）
- 最高隔离级别
- 完全串行执行
- 解决所有并发问题但性能最差

-- 查看和设置隔离级别SELECT @@transaction_isolation;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. 什么是脏读、不可重复读和幻读？

并发问题说明：

问题类型描述示例场景脏读读取到其他事务未提交的数据事务A读取了事务B修改但未提交的数据，B回滚后A读到的是脏数据 不可重复读 同一事务内多次读取同一数据结果不同事务A两次读取同一行数据，期间事务B修改了该行并提交幻读同一事务内多次查询返回不同行数事务A查询符合条件的数据行数，期间事务B插入新行并提交

3. MySQL中有哪些锁类型？

InnoDB锁分类：

按粒度分：
- 行锁：锁定单行记录
- 表锁：锁定整张表
- 间隙锁（Gap Lock）：锁定索引记录间隙
- 临键锁（Next-Key Lock）：行锁+间隙锁组合
按功能分：
- 共享锁（S锁）：读锁，多个事务可同时持有
- 排他锁（X锁）：写锁，独占资源
意向锁：
- 意向共享锁（IS）
- 意向排他锁（IX）

-- 手动加锁示例SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- X锁SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- S锁

4. 什么是死锁？如何避免和解决死锁？

死锁定义：
两个或多个事务互相持有对方需要的锁，导致所有事务都无法继续执行

避免死锁方法：

保持事务短小精悍
按固定顺序访问表和行
合理设置锁等待超时时间
使用较低的隔离级别

解决死锁：

MySQL自动检测并回滚代价较小的事务
手动处理：
– 查看最近死锁信息
SHOW ENGINE INNODB STATUS;

– 设置锁等待超时
SET innodb_lock_wait_timeout = 50;

5. 乐观锁和悲观锁的区别是什么？

对比说明：

特性悲观锁乐观锁 实现方式 数据库原生锁机制版本号或时间戳 并发性能 较差较好 适用场景 写多读少读多写少 锁时机 操作前先加锁提交时检查冲突示例 SELECT…FOR UPDATE UPDATE…WHERE version=old_version

-- 悲观锁实现BEGIN;SELECT * FROM products WHERE id = 1 FOR UPDATE;UPDATE products SET stock = stock - 1 WHERE id = 1;COMMIT;-- 乐观锁实现UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 5;

6. 什么是MVCC？

MVCC基本概念

**MVCC（Multi-Version Concurrency Control，多版本并发控制）**是InnoDB实现高并发的重要机制，通过保存数据的多个版本实现：

📌 读操作不阻塞写操作
📌 写操作不阻塞读操作
📌 解决读写冲突的非阻塞并发控制

MVCC核心原理

版本链存储：
- 每行记录包含两个隐藏字段：
  - DB_TRX_ID：最近修改该行的事务ID
  - DB_ROLL_PTR：回滚指针，指向undo log记录
ReadView机制：
- 事务执行快照读时生成ReadView，包含：
  - m_ids：当前活跃事务ID集合
  - min_trx_id：最小活跃事务ID
  - max_trx_id：系统预分配的下个事务ID
  - creator_trx_id：创建该ReadView的事务ID
可见性判断规则：
- 如果DB_TRX_ID < min_trx_id：可见（事务已提交）
- 如果DB_TRX_ID >= max_trx_id：不可见（事务后启动）
- 如果min_trx_id <= DB_TRX_ID < max_trx_id：
  - 在m_ids中则不可见（事务未提交）
  - 不在m_ids中则可见（事务已提交）

MVCC在隔离级别中的应用

隔离级别 MVCC实现特点 READ UNCOMMITTED 不使用MVCC，直接读取最新版本（含未提交数据） READ COMMITTED 每次读取都生成新ReadView，看到其他事务已提交的修改 REPEATABLE READ 第一次读取时生成ReadView，后续读取使用相同ReadView（默认级别） SERIALIZABLE 退化为基于锁的并发控制，不使用MVCC

MVCC操作示例

-- 事务1（更新数据）BEGIN;UPDATE accounts SET balance = balance - 100 WHERE id = 1;-- 事务2（同时查询，使用MVCC）BEGIN;SELECT * FROM accounts WHERE id = 1; -- 读取的是更新前的版本COMMIT;

MVCC优缺点分析

优点：

🚀 高并发：读写互不阻塞
⏱️ 一致性读：保证事务内看到的数据一致性
🔄 非阻塞：避免大量锁等待

缺点：

💾 存储开销：需要维护多个数据版本
🗑️ 清理成本：需要定期purge过期版本
⏳ 长事务问题：可能导致版本链过长

MVCC实现关键点

undo log：
- 存储数据修改前的版本
- 用于事务回滚和MVCC读取
purge机制：
- 系统自动清理不再需要的undo log
- 受参数innodb_purge_batch_size控制

-- 查看MVCC相关信息SHOW ENGINE INNODB STATUS\\GSELECT * FROM information_schema.INNODB_TRX;

MySQL高可用与备份

1. MySQL有哪些高可用方案？

主流高可用方案：

主从复制（Master-Slave Replication）
- 基础方案，配置简单
- 一主多从架构
MGR（MySQL Group Replication）
- MySQL官方提供的组复制方案
- 支持多主写入（5.7+版本）
Galera Cluster
- 多主同步复制集群
- 适用于Percona XtraDB Cluster
中间件方案
- MHA（Master High Availability）
- Orchestrator
- ProxySQL+主从
云数据库方案
- AWS RDS Multi-AZ
- 阿里云高可用版

2. 如何进行数据库备份和恢复？

备份方法：

逻辑备份
- mysqldump工具
- 导出SQL语句

-- 全量备份mysqldump -uroot -p --all-databases > full_backup.sql

-- 单库备份mysqldump -uroot -p dbname > db_backup.sql

物理备份
- Percona XtraBackup
- MySQL Enterprise Backup

-- XtraBackup热备份xtrabackup --backup --target-dir=/backup/

恢复方法：

-- 逻辑备份恢复mysql -uroot -p < full_backup.sql-- 物理备份恢复xtrabackup --copy-back --target-dir=/backup/

3. 什么是主从复制？如何配置？

主从复制原理：

主库记录binlog
从库IO线程拉取binlog
从库SQL线程重放日志

配置步骤：

主库配置：

[mysqld]server-id=1log-bin=mysql-binbinlog-format=ROW

创建复制账号：

CREATE USER \'repl\'@\'%\' IDENTIFIED BY \'password\';GRANT REPLICATION SLAVE ON *.* TO \'repl\'@\'%\';

从库配置：

[mysqld]server-id=2relay-log=mysql-relay-binread-only=1

启动复制：

CHANGE MASTER TOMASTER_HOST=\'master_host\',MASTER_USER=\'repl\',MASTER_PASSWORD=\'password\',MASTER_LOG_FILE=\'mysql-bin.000001\',MASTER_LOG_POS=154;START SLAVE;

4. 主从复制的延迟问题如何解决？

复制延迟解决方案：

优化主库写入
- 批量写入替代单条写入
- 减少大事务
提升从库性能
- 从库使用更好硬件
- 开启并行复制

-- 设置并行复制SET GLOBAL slave_parallel_workers=4;

架构优化
- 使用半同步复制
- 考虑MGR集群
监控延迟

-- 查看复制状态SHOW SLAVE STATUS\\G

5. 什么是读写分离？如何实现？

读写分离概念：

读操作路由到从库
写操作路由到主库

实现方式：

中间件方案
- ProxySQL
- MySQL Router
- ShardingSphere
应用层实现
- 配置多数据源
- 基于Spring AOP实现

-- ProxySQL配置示例INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,\'master\',3306),(20,\'slave1\',3306),(20,\'slave2\',3306);

-- 读写分离规则INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES(1,1,\'^SELECT.*FOR UPDATE\',10,1),(2,1,\'^SELECT\',20,1),(3,1,\'^INSERT\',10,1),(4,1,\'^UPDATE\',10,1),(5,1,\'^DELETE\',10,1);

MySQL其他特性

1. 什么是视图？它有什么优缺点？

视图定义：
视图（View）是基于SQL查询结果的虚拟表，不实际存储数据，每次查询时动态生成结果集。

-- 创建视图示例CREATE VIEW customer_orders ASSELECT c.customer_name, o.order_id, o.order_date FROM customers cJOIN orders o ON c.customer_id = o.customer_id;

视图优点：

简化复杂查询：封装复杂SQL逻辑
数据安全：隐藏敏感列
权限控制：可单独授权视图访问权限
逻辑独立性：不影响底层表结构

视图缺点：

性能开销：每次查询都需要实时计算
更新限制：复杂视图通常不可更新
维护成本：底层表结构变更可能导致视图失效

-- 可更新视图示例（简单视图）CREATE VIEW active_users ASSELECT * FROM users WHERE status = \'active\';-- 更新视图数据（实际更新基表）UPDATE active_users SET last_login = NOW() WHERE user_id = 101;

2. 存储过程和函数有什么区别？

主要区别对比：

特性存储过程(Procedure) 函数(Function) 返回值 可通过OUT参数返回多个值必须返回单个值 调用方式 CALL procedure_name() SELECT function_name() SQL中使用 不能直接在SELECT中使用可在SELECT中直接调用 事务控制 可以包含事务语句(COMMIT/ROLLBACK) 通常不包含事务控制 主要用途 执行复杂业务逻辑计算并返回特定值

存储过程示例：

-- 创建存储过程DELIMITER //CREATE PROCEDURE transfer_funds( IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2), OUT status VARCHAR(50)BEGIN START TRANSACTION; UPDATE accounts SET balance = balance - amount WHERE account_id = from_account; UPDATE accounts SET balance = balance + amount WHERE account_id = to_account; SET status = \'Transfer completed\'; COMMIT;END //DELIMITER ;-- 调用存储过程CALL transfer_funds(101, 102, 500.00, @status);SELECT @status;

函数示例：

-- 创建函数DELIMITER //CREATE FUNCTION get_customer_balance(c_id INT) RETURNS DECIMAL(10,2)DETERMINISTICBEGIN DECLARE balance DECIMAL(10,2); SELECT account_balance INTO balance FROM customers WHERE customer_id = c_id; RETURN balance;END //DELIMITER ;-- 调用函数SELECT customer_name, get_customer_balance(customer_id) FROM customers WHERE get_customer_balance(customer_id) > 1000;