Skip to main content
Version: 1.0

MySQL 索引、事务与运维

本篇覆盖 MySQL 面试的进阶层：索引原理与失效场景、事务 ACID、服务运维与备份恢复（mysqldump），最后附 10 道 MySQL 高频面试题逐题详解。

1. 索引​

1.1 索引的作用与代价​

  • 作用：加快查询速度，类似书本目录——没有目录就得一页页翻（全表扫描），有目录直接定位。
  • 缺点：
    1. 占用磁盘空间；
    2. 表数据新增、修改、删除时，索引需要同步更新，降低写入速度。

1.2 索引类型​

类型特点
主键索引主键自带，唯一且非空，一表只能有一个
唯一索引字段值不能重复，但可以为 NULL
普通索引单纯加速查询，无唯一性约束
-- 创建索引
CREATE INDEX idx_student_name ON student (name);

-- 删除索引
DROP INDEX idx_student_name ON student;

1.3 索引失效场景（高频）​

  • LIKE 以 % 开头：LIKE '%张三' 无法使用索引（左模糊破坏了索引有序性）；LIKE '张%' 可以用。
  • 对索引字段做运算或函数处理：WHERE age + 1 = 20 不会走索引，应写成 WHERE age = 19。
  • 其他常见场景（加分项）：隐式类型转换（字符串列用数字比较）、OR 两侧有非索引列、不满足最左前缀原则（联合索引）。

1.4 建索引建议​

  • 该建：经常用于 WHERE 查询、ORDER BY 排序、GROUP BY 分组的字段。
  • 不该建：数据量很小的表、更新频繁的字段（每次更新都要维护索引）、区分度极低的字段（如性别）。

2. 事务​

事务：一组 SQL 语句组成的逻辑单元，要么全部成功执行，要么全部失败回滚。

2.1 ACID 四个特性（必背）​

特性含义通俗理解
原子性（Atomicity）事务不可拆分，全成功或全失败转账的扣款和到账必须同时发生
一致性（Consistency）事务执行前后数据状态合法总金额不变、约束不被破坏
隔离性（Isolation）多个事务之间互相隔离、互不干扰并发事务互相看不见对方的中间状态
持久性（Durability）事务提交后数据永久保存提交后即使宕机数据也不丢

2.2 手动事务语法​

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;      -- 提交，数据生效
-- ROLLBACK; -- 回滚，放弃所有修改

MySQL InnoDB 默认每条语句自动提交（autocommit=1），显式开启事务后需手动 COMMIT。

3. MySQL 基础运维、备份与恢复​

3.1 服务管理​

systemctl start mysqld       # 启动
systemctl enable mysqld      # 开机自启
systemctl status mysqld      # 状态
  • 服务名 mysqld；默认端口 3306，防火墙放行端口后才能远程访问。
  • 配置文件：/etc/my.cnf 或 /etc/mysql/my.cnf。

3.2 备份与恢复：mysqldump（必会）​

# 备份单个数据库
mysqldump -uroot -p testdb > testdb_20261027.sql

# 恢复数据库
mysql -uroot -p testdb < testdb_20261027.sql

与 Linux 定时任务结合（综合题加分）： 用 crontab 写定时备份脚本，每天凌晨 mysqldump 备份数据库，并按文件名时间戳删除过期备份文件：

# crontab：每天 2 点备份
0 2 * * * /opt/scripts/mysql_backup.sh

# mysql_backup.sh 核心逻辑
mysqldump -uroot -p'密码' testdb > /backup/testdb_$(date +%Y%m%d).sql
find /backup -name "testdb_*.sql" -mtime +7 -delete   # 删 7 天前的备份

3.3 慢查询与 EXPLAIN（了解即可）​

  • 慢查询日志：记录执行时间超过阈值的 SQL，是优化慢 SQL 的入口。
  • EXPLAIN：放在 SQL 前查看执行计划——是否用到索引（type、key 字段）、扫描行数等。基础阶段知道「慢 SQL 先 EXPLAIN 看执行计划、重点看有没有走索引」即可。

4. MySQL 面试题精讲（10 题）​

1. InnoDB 和 MyISAM 的主要区别？生产为什么默认用 InnoDB？​

答： ① 事务：InnoDB 支持，MyISAM 不支持；② 锁粒度：InnoDB 行级锁、并发写好，MyISAM 表级锁、写操作会锁全表；③ 崩溃恢复：InnoDB 有 redo log 支持崩溃恢复，MyISAM 容易丢数据。生产默认 InnoDB 是因为互联网业务读写并发高、对数据安全要求高，事务和行级锁是刚需；MyISAM 只剩读多场景查询略快的优势，但数据安全性差，新项目基本不用。

2. delete、truncate、drop 三者的区别？​

答： DELETE 是 DML，按条件删除行数据，可回滚；TRUNCATE 是 DDL，清空整张表并把自增主键重置，速度快但不可回滚；DROP 是 DDL，整张表（结构+数据）删除。一句话：delete 删行、truncate 清表重置自增、drop 连表都不要了。

3. where 和 having 的区别？​

答： WHERE 在分组之前过滤原始行数据，不能用聚合函数；HAVING 在分组之后过滤聚合结果，可以使用聚合函数（如 HAVING COUNT(*) > 3）。执行顺序上 WHERE 先于 GROUP BY，HAVING 后于 GROUP BY。

4. 什么是数据库索引？索引的优缺点？​

答： 索引是帮助 MySQL 高效获取数据的排好序的数据结构，类似书的目录，把全表扫描变成按结构快速定位。优点：大幅加快查询、排序。缺点：① 占用额外磁盘空间；② 增删改数据时要同步维护索引，降低写入速度。

5. 主键索引、普通索引、唯一索引的区别？​

答： 主键索引：主键自带，唯一 + 非空，一张表只能有一个；唯一索引：字段值不能重复但可以为 NULL，可以有多个；普通索引：没有唯一性约束，单纯为了加速查询，可以有多个。

6. 什么是事务？ACID 四个特性分别是什么？​

答： 事务是一组 SQL 逻辑单元，要么全部成功、要么全部回滚。ACID：原子性——不可拆分，全成功或全失败；一致性——事务前后数据处于合法状态；隔离性——并发事务之间互不干扰；持久性——提交后数据永久保存。

7. 内连接 INNER JOIN 和左连接 LEFT JOIN 有什么区别？​

答： INNER JOIN 只返回两表都匹配上的行；LEFT JOIN 返回左表全部行，右表匹配不上的字段显示 NULL。例如查「所有学生及其班级」用 LEFT JOIN（没分班的学生也列出来），查「已分班的学生」用 INNER JOIN。

8. union 和 union all 的区别？​

答： UNION 会自动去重，需要排序比较所以性能较差；UNION ALL 不去重、直接拼接结果，速度更快。确认结果集不需要去重（或业务上保证不重复）时用 UNION ALL。

9. mysqldump 备份数据库，备份和恢复的流程是什么？​

答： 备份：mysqldump -uroot -p 库名 > 备份文件.sql，导出建表和插入语句的 SQL 文本。恢复：mysql -uroot -p 库名 < 备份文件.sql，把 SQL 重新执行一遍。生产上配合 crontab 定时执行备份脚本，并按时间戳清理过期备份文件。

10. 哪些情况容易造成 MySQL 索引失效？（说出 2-3 种）​

答： ① LIKE 以 % 开头（左模糊），如 LIKE '%张'；② 对索引字段做运算或函数，如 WHERE age + 1 = 20；③ 隐式类型转换（varchar 列用数字比较）；④ OR 连接了非索引列；⑤ 联合索引不满足最左前缀。

本篇速记​

  • 索引 = 目录：加快读、拖慢写、占空间；左模糊 %xx 和字段运算会让索引失效。
  • ACID：原子、一致、隔离、持久，转账例子最好讲。
  • 备份恢复就两条命令：mysqldump > 备份、mysql < 恢复。
  • 慢 SQL 排查入口：慢查询日志 + EXPLAIN 看是否走索引。