MySQL 常见面试问题总结

MySQL 基础概念

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

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

主要特点：

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

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

常见存储引擎：

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

存储引擎对比：

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

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

核心区别：

1. 事务支持：

   • InnoDB：支持ACID事务
   • MyISAM：不支持事务

2. 锁级别：

   • InnoDB：行级锁
   • MyISAM：表级锁

3. 外键约束：

   • InnoDB：支持
   • MyISAM：不支持

4. 崩溃恢复：

   • InnoDB：有崩溃后安全恢复能力
   • MyISAM：崩溃后数据易损坏

5. 性能特点：

   • InnoDB：写操作性能更好
   • MyISAM：读操作性能更好

6. 存储结构：

   • 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）

📈 定义：

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

类型：

1. 普通索引
2. 唯一索引
3. 主键索引
4. 组合索引
5. 全文索引

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

数据库范式

📐 定义：

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

主要范式：

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

设计建议：

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

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

优化策略

🚀 结构优化：

1. 选择合适的数据类型

   • 用INT而非VARCHAR存储数字
   • 用DATETIME/TIMESTAMP存储时间

2. 规范化设计

   • 遵循适当的范式级别
   • 合理拆分大表

3. 命名规范

   • 使用有意义的表名和字段名
   • 保持命名风格一致

🔍 索引优化：

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

📊 其他优化：

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

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

反范式化设计

🔄 定义：

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

使用场景：

1. 读密集场景

   • 报表系统
   • 数据分析应用

2. 性能关键路径

   • 高频查询的表
   • 需要快速响应的核心业务

3. 特定技术需求

   • 数据仓库
   • OLAP系统

实现方式：

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

⚠️ 注意事项：

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

MySQL SQL查询

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

SQL查询逻辑执行顺序：

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

实际示例：

SELECT DISTINCT column1, COUNT(*) 
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE condition = 'value'
GROUP BY column1
HAVING COUNT(*) > 1
ORDER BY column1
LIMIT 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_orders
WHERE order_count > 5

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

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

4. EXISTS/NOT EXISTS子查询

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

5. IN/NOT IN子查询

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

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

索引优化

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

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

2. 避免索引失效场景
   • 避免对索引列使用函数或运算
   • 避免使用!=或<>操作符
   • 避免使用前导通配符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';

查询优化

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

-- 全表扫描的反例
SELECT * FROM order_details;

-- 优化后的正例
SELECT order_id, product_id, quantity FROM order_details WHERE order_id = 1001;

2. 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;

关键输出字段

1. type: 访问类型，从好到差：

   • system > const > eq_ref > ref > range > index > ALL

2. key: 实际使用的索引

3. rows: 预估需要检查的行数

4. 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_id
WHERE u.status = 1 AND o.amount > 1000
ORDER 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. 如何创建高效的索引？

高效索引创建原则：

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

-- 高效组合索引示例
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 低效索引示例(区分度低)
CREATE INDEX idx_gender ON users(gender);

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

索引失效常见场景：

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

-- 索引失效示例
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. 如何判断一个查询是否使用了索引？

判断方法：

1. 使用EXPLAIN命令查看执行计划
2. 检查key列是否显示使用的索引名
3. 检查type列是否为ref、range等较好的类型
4. 检查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秒）的查询
• 记录在慢查询日志中

分析优化步骤：

1. 开启慢查询日志：

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

2. 使用EXPLAIN分析慢查询：

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

3. 优化方法：

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

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

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

分库分表策略：

水平分表：

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

垂直分表：

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

分库策略：

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

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

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

读写分离概念：

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

实现方式：

1. 主从复制配置：

-- 主库配置
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW

-- 从库配置
[mysqld]
server-id=2
relay-log=mysql-relay-bin
read-only=1

2. 使用中间件：

• MySQL Router
• ProxySQL
• ShardingSphere

3. 应用层实现：

• 配置多数据源
• 根据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中有哪些事务隔离级别？

四种事务隔离级别：

1. READ UNCOMMITTED（读未提交）

   • 事务可以读取未提交的数据
   • 性能最好，但会出现脏读

2. READ COMMITTED（读已提交）

   • 只能读取已提交的数据
   • 解决脏读，但会出现不可重复读
   • Oracle默认级别

3. REPEATABLE READ（可重复读）

   • 同一事务中多次读取结果一致
   • 解决脏读和不可重复读
   • MySQL默认级别（InnoDB）

4. SERIALIZABLE（串行化）

   • 最高隔离级别
   • 完全串行执行
   • 解决所有并发问题但性能最差

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

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

并发问题说明：

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

3. MySQL中有哪些锁类型？

InnoDB锁分类：

1. 按粒度分：

   • 行锁：锁定单行记录
   • 表锁：锁定整张表
   • 间隙锁（Gap Lock）：锁定索引记录间隙
   • 临键锁（Next-Key Lock）：行锁+间隙锁组合

2. 按功能分：

   • 共享锁（S锁）：读锁，多个事务可同时持有
   • 排他锁（X锁）：写锁，独占资源

3. 意向锁：

   • 意向共享锁（IS）
   • 意向排他锁（IX）

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

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

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

避免死锁方法：

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

解决死锁：

1. MySQL自动检测并回滚代价较小的事务
2. 手动处理：
   – 查看最近死锁信息
   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核心原理

1. 版本链存储：

   • 每行记录包含两个隐藏字段：
     • DB_TRX_ID：最近修改该行的事务ID
     • DB_ROLL_PTR：回滚指针，指向undo log记录

2. ReadView机制：

   • 事务执行快照读时生成ReadView，包含：
     • m_ids：当前活跃事务ID集合
     • min_trx_id：最小活跃事务ID
     • max_trx_id：系统预分配的下个事务ID
     • creator_trx_id：创建该ReadView的事务ID

3. 可见性判断规则：

   • 如果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实现关键点

1. undo log：

   • 存储数据修改前的版本
   • 用于事务回滚和MVCC读取

2. purge机制：

   • 系统自动清理不再需要的undo log
   • 受参数innodb_purge_batch_size控制

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

MySQL高可用与备份

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

主流高可用方案：

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

   • 基础方案，配置简单
   • 一主多从架构

2. MGR（MySQL Group Replication）

   • MySQL官方提供的组复制方案
   • 支持多主写入（5.7+版本）

3. Galera Cluster

   • 多主同步复制集群
   • 适用于Percona XtraDB Cluster

4. 中间件方案

   • MHA（Master High Availability）
   • Orchestrator
   • ProxySQL+主从

5. 云数据库方案

   • AWS RDS Multi-AZ
   • 阿里云高可用版

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

备份方法：

1. 逻辑备份
   • mysqldump工具
   • 导出SQL语句

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

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

2. 物理备份
   • 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线程重放日志

配置步骤：

1. 主库配置：

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

2. 创建复制账号：

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

3. 从库配置：

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

4. 启动复制：

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

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

复制延迟解决方案：

1. 优化主库写入

   • 批量写入替代单条写入
   • 减少大事务

2. 提升从库性能

   • 从库使用更好硬件
   • 开启并行复制

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

3. 架构优化

   • 使用半同步复制
   • 考虑MGR集群

4. 监控延迟

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

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

读写分离概念：

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

实现方式：

1. 中间件方案

   • ProxySQL
   • MySQL Router
   • ShardingSphere

2. 应用层实现

   • 配置多数据源
   • 基于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 AS
SELECT c.customer_name, o.order_id, o.order_date 
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

视图优点：

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

视图缺点：

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

-- 可更新视图示例（简单视图）
CREATE VIEW active_users AS
SELECT * 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)
DETERMINISTIC
BEGIN
    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;