MySQL 知识总结与面试题
MySQL 从入门到进阶的知识梳理：覆盖库表设计、增删改查、多表连接与子查询、索引原理与优化、事务隔离级别、视图与存储过程，并附高频面试题，适合后端开发日常查阅。
一、MySQL 基础概念
1. 什么是 MySQL？
- 开源的关系型数据库管理系统（RDBMS）
- 由 Oracle 公司开发和维护
- 使用 SQL 语言进行数据管理
- 广泛应用于 Web 开发、数据分析等领域
2. MySQL 架构特点
- 存储引擎架构：支持多种存储引擎（InnoDB、MyISAM、Memory等）
- 客户端/服务器架构：通过 TCP/IP 协议通信
- 支持事务：InnoDB 引擎支持 ACID 特性
- 复制功能：支持主从复制、读写分离
3. 常用存储引擎对比
| 特性 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 事务支持 | 支持 | 不支持 | 不支持 |
| 行级锁 | 支持 | 表级锁 | 表级锁 |
| 外键约束 | 支持 | 不支持 | 不支持 |
| 崩溃恢复 | 支持 | 不支持 | 数据丢失 |
| 全文索引 | 支持（5.6+） | 支持 | 不支持 |
| 适用场景 | OLTP | OLAP/只读 | 临时表 |
二、数据库和表操作
1. 创建数据库
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;2. 删除数据库
DROP DATABASE IF EXISTS mydb;3. 创建表
CREATE TABLE IF NOT EXISTS users ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID', username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名', email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱', password VARCHAR(255) NOT NULL COMMENT '密码', status TINYINT DEFAULT 1 COMMENT '状态（1：启用，0：禁用）', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';4. 修改表结构
-- 添加列ALTER TABLE users ADD COLUMN phone VARCHAR(20);-- 修改列ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL;-- 删除列ALTER TABLE users DROP COLUMN phone;-- 添加索引ALTER TABLE users ADD INDEX idx_status (status);5. 删除表
DROP TABLE IF EXISTS users;三、数据操作（DML）
1. 插入数据
-- 插入单行INSERT INTO users (username, email, password)VALUES ('admin', 'admin@example.com', '123456');-- 插入多行INSERT INTO users (username, email, password)VALUES ('user1', 'user1@example.com', '123456'), ('user2', 'user2@example.com', '123456');-- 从查询结果插入INSERT INTO backup_usersSELECT * FROM users WHERE status = 1;2. 更新数据
UPDATE users SET status = 0, updated_at = NOW() WHERE id = 1;3. 删除数据
-- 删除指定行DELETE FROM users WHERE id = 1;-- 清空表（保留结构）TRUNCATE TABLE users;四、数据查询（DQL）
1. 基础查询
SELECT id, username, email FROM users;-- 去重SELECT DISTINCT status FROM users;-- 限制行数SELECT * FROM users LIMIT 10;-- 排序SELECT * FROM users ORDER BY created_at DESC;2. 条件查询
-- 等于SELECT * FROM users WHERE status = 1;-- 模糊匹配SELECT * FROM users WHERE username LIKE '%admin%';-- 范围查询SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';-- IN 查询SELECT * FROM users WHERE id IN (1, 2, 3);-- 空值判断SELECT * FROM users WHERE email IS NOT NULL;3. 聚合函数
-- 计数SELECT COUNT(*) FROM users;SELECT COUNT(DISTINCT status) FROM users;-- 求和/平均SELECT SUM(score), AVG(score) FROM students;-- 最大/最小SELECT MAX(created_at), MIN(created_at) FROM users;4. 分组查询
SELECT status, COUNT(*) AS count FROM users GROUP BY status HAVING count > 10;5. 连接查询
-- 内连接SELECT u.username, o.order_no FROM users uINNER JOIN orders o ON u.id = o.user_id;-- 左连接SELECT u.username, o.order_no FROM users uLEFT JOIN orders o ON u.id = o.user_id;-- 右连接SELECT u.username, o.order_no FROM users uRIGHT JOIN orders o ON u.id = o.user_id;-- 自连接SELECT e.name, m.name AS manager FROM employees eLEFT JOIN employees m ON e.manager_id = m.id;6. 子查询
-- 标量子查询SELECT username FROM users WHERE id = (SELECT MAX(id) FROM users);-- 列子查询SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);-- 行子查询SELECT * FROM users WHERE (username, email) = ('admin', 'admin@example.com');-- 相关子查询SELECT * FROM users uWHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);五、索引
1. 索引类型
| 类型 | 说明 | 适用场景 |
|---|---|---|
| B-Tree 索引 | 默认索引类型，平衡二叉树 | 范围查询、排序 |
| Hash 索引 | 基于哈希表 | 等值查询 |
| 全文索引 | 全文搜索 | 文本搜索 |
| 空间索引 | 地理空间数据 | GIS 查询 |
2. 创建索引
-- 普通索引CREATE INDEX idx_username ON users(username);-- 唯一索引CREATE UNIQUE INDEX idx_email ON users(email);-- 复合索引CREATE INDEX idx_status_created ON users(status, created_at);-- 全文索引CREATE FULLTEXT INDEX idx_content ON articles(content);3. 删除索引
DROP INDEX idx_username ON users;4. 索引使用原则
- 索引加快查询速度，但会降低写入速度
- 不要在频繁更新的列上创建索引
- 复合索引遵循最左前缀原则
- 小表不需要索引
六、事务管理
1. 事务特性（ACID）
- 原子性（Atomicity）：事务是不可分割的工作单元
- 一致性（Consistency）：事务执行前后数据完整性保持一致
- 隔离性（Isolation）：事务之间相互隔离
- 持久性（Durability）：事务提交后数据永久保存
2. 事务操作
-- 开启事务START TRANSACTION;-- 执行操作UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- 提交事务COMMIT;-- 回滚事务ROLLBACK;3. 隔离级别
| 隔离级别 | 说明 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| READ UNCOMMITTED | 读取未提交数据 | 可能 | 可能 | 可能 |
| READ COMMITTED | 读取已提交数据 | 否 | 可能 | 可能 |
| REPEATABLE READ | 可重复读（MySQL 默认） | 否 | 否 | 可能 |
| SERIALIZABLE | 串行化 | 否 | 否 | 否 |
-- 设置隔离级别SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;七、视图
1. 创建视图
CREATE VIEW v_user_orders ASSELECT u.id, u.username, o.order_no, o.amountFROM users uLEFT JOIN orders o ON u.id = o.user_id;2. 使用视图
SELECT * FROM v_user_orders WHERE username = 'admin';3. 删除视图
DROP VIEW IF EXISTS v_user_orders;八、存储过程和函数
1. 存储过程
DELIMITER //CREATE PROCEDURE GetUserOrders(IN userId INT)BEGIN SELECT * FROM orders WHERE user_id = userId;END //DELIMITER ;-- 调用存储过程CALL GetUserOrders(1);2. 存储函数
DELIMITER //CREATE FUNCTION GetUserCount() RETURNS INTBEGIN DECLARE count INT; SELECT COUNT(*) INTO count FROM users; RETURN count;END //DELIMITER ;-- 使用函数SELECT GetUserCount();九、触发器
-- 创建触发器DELIMITER //CREATE TRIGGER before_user_insertBEFORE INSERT ON usersFOR EACH ROWBEGIN SET NEW.password = MD5(NEW.password);END //DELIMITER ;-- 删除触发器DROP TRIGGER IF EXISTS before_user_insert;十、性能优化
1. 索引优化
- 为经常查询的列创建索引
- 使用复合索引时遵循最左前缀原则
- 避免在索引列上使用函数或表达式
2. 查询优化
-- 避免 SELECT *SELECT id, username FROM users;-- 使用 LIMIT 限制结果集SELECT * FROM users LIMIT 10;-- 避免在 WHERE 子句中使用函数-- 不好：WHERE YEAR(created_at) = 2024-- 好：WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'-- 使用 JOIN 代替子查询SELECT u.username FROM users uJOIN orders o ON u.id = o.user_id;3. 配置优化
[mysqld]# 缓冲区大小innodb_buffer_pool_size = 4Gquery_cache_size = 256Mtmp_table_size = 64Mmax_heap_table_size = 64M# 连接数max_connections = 1000wait_timeout = 60# 日志slow_query_log = 1slow_query_log_file = /var/log/mysql/slow.loglong_query_time = 24. 分库分表
- 垂直分表：将大表按列拆分
- 水平分表：将大表按行拆分（按时间、按地区等）
- 分库：按业务模块拆分到不同数据库
十一、主从复制
1. 复制原理
- Master 记录二进制日志（binlog）
- Slave 读取 Master 的 binlog
- Slave 执行 binlog 中的 SQL 语句
2. 配置步骤
Master 配置：
[mysqld]server-id = 1log_bin = /var/log/mysql/mysql-bin.logbinlog_format = rowSlave 配置：
[mysqld]server-id = 2relay_log = /var/log/mysql/relay-bin.log启动复制：
-- 在 Slave 上执行CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;START SLAVE;3. 复制类型
- 异步复制：Master 写入 binlog 后立即返回，不等待 Slave 确认
- 半同步复制：Master 等待至少一个 Slave 确认收到 binlog
- 组复制：多节点同步，保证数据一致性
十二、常见面试题
基础概念
Q1：MySQL 有哪些存储引擎？区别是什么？
- InnoDB：支持事务、行级锁、外键，适合 OLTP
- MyISAM：不支持事务、表级锁，适合只读场景
- Memory：内存存储，速度快但数据易失
Q2：什么是事务？事务的 ACID 特性是什么？
- 事务是一组不可分割的操作
- 原子性、一致性、隔离性、持久性
Q3：MySQL 的隔离级别有哪些？
- READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE
- 默认是 REPEATABLE READ
SQL 语法
Q4：LEFT JOIN 和 INNER JOIN 的区别？
- INNER JOIN：只返回两表匹配的行
- LEFT JOIN：返回左表所有行，右表不匹配则为 NULL
Q5：GROUP BY 和 HAVING 的区别？
- GROUP BY：分组
- HAVING：过滤分组后的结果（需配合 GROUP BY 使用）
Q6：什么是子查询？有哪些类型？
- 嵌套在其他查询中的查询
- 标量子查询、列子查询、行子查询、相关子查询
索引优化
Q7：什么是索引？索引的优缺点？
- 索引是数据结构，加快查询速度
- 优点：提高查询效率
- 缺点：降低写入速度，占用存储空间
Q8：索引失效的场景有哪些？
- 在索引列上使用函数
- 使用 LIKE '%xxx' 模糊查询
- 索引列参与计算
- 使用 != 或 NOT IN
Q9：复合索引的最左前缀原则是什么？
- 复合索引 (a, b, c) 会匹配 (a)、(a,b)、(a,b,c)
- 不匹配 (b)、(b,c)
性能优化
Q10：如何优化慢查询？
- 查看慢查询日志
- 使用 EXPLAIN 分析执行计划
- 添加合适的索引
- 优化 SQL 语句
Q11：EXPLAIN 输出中各字段的含义？
- type：访问类型（ALL、index、range、ref、eq_ref）
- key：使用的索引
- rows：扫描行数
- Extra：额外信息（Using index、Using where、Using filesort）
Q12：什么是覆盖索引？
- 查询所需的所有列都在索引中，不需要回表
高可用
Q13：主从复制的原理是什么？
- Master 记录 binlog，Slave 通过 IO 线程读取，SQL 线程执行
Q14：主从复制有哪些类型？
- 异步复制、半同步复制、组复制
Q15：如何处理主从延迟？
- 使用半同步复制
- 优化网络延迟
- 减少大事务
- 使用并行复制
进阶问题
Q16：什么是死锁？如何避免？
- 两个或多个事务互相等待对方释放锁
- 避免：按顺序访问资源、缩短事务时间、使用较低隔离级别
Q17：MVCC 是什么？
- 多版本并发控制，通过保存数据的多个版本实现并发访问
- 使用 undo log 保存旧版本数据
Q18：InnoDB 的锁类型有哪些？
- 共享锁（S）：读锁
- 排他锁（X）：写锁
- 意向锁（IS、IX）：表级锁，用于判断表是否被锁定
Q19：什么是间隙锁？
- 锁定索引记录之间的间隙，防止插入幻行
- 在 REPEATABLE READ 隔离级别下自动启用
Q20：如何进行数据备份和恢复？
- 使用 mysqldump 进行逻辑备份
- 使用 xtrabackup 进行物理备份
- 使用 binlog 进行增量恢复
十三、注意事项
- SQL 注入：使用参数化查询，避免字符串拼接
- 连接池：使用连接池管理数据库连接
- 数据类型：选择合适的数据类型（如 INT 而非 VARCHAR 存储数字）
- NULL 值：合理处理 NULL 值，避免不必要的 NULL 比较
- 备份策略：定期备份数据，测试恢复流程
十四、总结
MySQL 是一个功能强大的关系型数据库，掌握其核心概念、SQL 语法、索引优化和高可用架构是成为优秀后端开发者的必备技能。