DEV Community

Liu yu
Liu yu

Posted on • Edited on

MySQL学习

#interview #learning #mysql #sql

需要学习的内容

🧱 MySQL 面试必学内容清单（速通版）

1️⃣ 基础 SQL（能用）

  • CRUD 操作：SELECT / INSERT / UPDATE / DELETE
  • 条件过滤：WHERE / AND / OR / IN / BETWEEN / LIKE
  • 排序：ORDER BY
  • 聚合函数：COUNT / AVG / SUM / MAX / MIN
  • 分组：GROUP BY / HAVING

  • 多表查询：

    • INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL JOIN
    • 子查询（单行 / 多行 / EXISTS / IN）

  • 常用函数：IFNULL / COALESCE / CONCAT / DATE_FORMAT

2️⃣ 表设计

  • 数据类型（INT / BIGINT / VARCHAR / DATE / DATETIME / TEXT / BLOB）
  • 主键（PRIMARY KEY）
  • 外键（FOREIGN KEY）
  • 唯一约束（UNIQUE）
  • 非空约束（NOT NULL）
  • 一对多 / 多对多建表思路

  • 索引类型基础：

    • 单列索引 / 复合索引 / 唯一索引 / 全文索引

3️⃣ 索引（核心面试点）

  • B+ 树原理（树结构、叶子节点存储）
  • 聚簇索引 vs 非聚簇索引
  • 联合索引与最左前缀原则
  • 索引失效场景（函数、范围查询、类型不匹配）
  • 覆盖索引（Covering Index）

4️⃣ 事务与锁（核心面试点）

  • 事务 ACID 四特性：

    • Atomicity（原子性）
    • Consistency（一致性）
    • Isolation（隔离性）
    • Durability（持久性）

  • 隔离级别：

    • READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE

  • 脏读 / 不可重复读 / 幻读

  • 锁机制：

    • 行锁 / 表锁 / 间隙锁 / 意向锁

  • 常见死锁问题及解决思路

5️⃣ SQL 性能优化（核心面试点）

  • 使用 EXPLAIN 查看执行计划
  • 判断索引是否命中
  • 分页优化（LIMIT + OFFSET vs keyset pagination）
  • 慢查询分析与优化
  • 避免 SELECT * / 减少不必要 join / 数据量大时拆表

6️⃣ 存储引擎基础

  • InnoDB vs MyISAM

    • InnoDB：支持事务、行级锁、外键
    • MyISAM：不支持事务、表级锁、查询快

  • 主流应用场景

7️⃣ 面试题必备问答

  • “索引为什么能提高查询效率？”
  • “联合索引最左前缀原则是什么？”
  • “事务四大特性是什么？隔离级别有什么区别？”
  • “行锁和表锁有什么区别？间隙锁是干嘛的？”
  • “SQL 慢了，你会怎么排查？”
  • “InnoDB 和 MyISAM 的区别？”

💡 学习原则

  • 学会写 SQL + 能解释原理 = 面试 90% 问题搞定
  • 重点记住：索引 / 事务 / 锁 / 性能优化 / 多表 join
  • 可以先把基础写通，再用视频 + 实例快速理解索引和事务

例子1

WHERE 和 HAVING 都是 SQL 中用于“过滤数据”的关键字，但两者的执行时机、作用对象、使用场景有本质区别

WHERE 在分组前执行，HAVING 在分组后执行。

1. 执行时机与作用对象

关键字 执行阶段 作用对象 能否使用聚合函数？
WHERE 分组前（GROUP BY 之前） 原始数据中的“行”（如过滤年龄<18的用户） 不能（此时还未分组，聚合函数无意义）
HAVING 分组后（GROUP BY 之后） 分组后的“组”（如过滤订单数<2的用户组） 能（基于分组后的聚合结果过滤）

2. 直观例子对比

假设有 user 表（id、name、age）和 order 表（id、user_id、amount），需求：“查询年龄>18，且订单总金额>1000的用户”。

用 WHERE 过滤行（分组前）： 

SELECT u.name, SUM(o.amount) AS total_amount
FROM `user` u
JOIN `order` o ON u.id = o.user_id
WHERE u.age > 18  -- 分组前过滤：只保留年龄>18的用户的行
GROUP BY u.name;
Enter fullscreen mode Exit fullscreen mode
  • WHERE u.age > 18 的作用：在关联表后、分组前，先把年龄≤18的用户的所有记录过滤掉，减少后续分组的数据量。

用 HAVING 过滤组（分组后）： 

SELECT u.name, SUM(o.amount) AS total_amount
FROM `user` u
JOIN `order` o ON u.id = o.user_id
WHERE u.age > 18
GROUP BY u.name
HAVING total_amount > 1000;  -- 分组后过滤：只保留总金额>1000的用户组
Enter fullscreen mode Exit fullscreen mode
  • HAVING total_amount > 1000 的作用：在分组并计算出每个用户的总金额后，过滤掉总金额≤1000的组。

3. 核心区别总结

区别点 WHERE HAVING
作用阶段 分组前（GROUP BY 之前） 分组后（GROUP BY 之后）
过滤对象 原始数据中的“行” 分组后的“组”
聚合函数支持 不支持（不能用 SUM()、COUNT() 等） 支持（可以用分组后的聚合结果，如 total_amount）
使用场景 过滤不需要的行，减少分组的数据量 过滤不符合条件的组，基于聚合结果筛选
性能影响 能提前过滤数据，减少后续计算量，性能更高 需等待分组完成后过滤，性能相对较低

4. 面试高频考点

  • 问：“WHERE 和 HAVING 都能过滤数据，什么时候必须用 HAVING？”

    答：当过滤条件需要用到聚合函数（如 SUM()、COUNT()）时，必须用 HAVING（因为 WHERE 不支持聚合函数）。例如“筛选订单数>5的用户”，必须用 HAVING COUNT(order_id) > 5。

  • 问：“HAVING 可以替代 WHERE 吗？”

    答：语法上可以（比如 HAVING age > 18），但不推荐。因为 HAVING 在分组后执行，会导致更多数据参与分组计算，性能更低；而 WHERE 提前过滤，效率更高。

  • 问：“WHERE 里为什么不能用 SELECT 中的别名？而 HAVING 可以？”

    答：因为 WHERE 在 SELECT 之前执行（此时别名还未定义），而 HAVING 在 SELECT 之后执行（能识别别名）。

一句话记住

WHERE 是“先筛行，再分组”，HAVING 是“先分组，再筛组”；聚合条件用 HAVING，普通条件用 WHERE。

例子2：SQL 语句的执行顺序（与书写顺序不同），理解它能帮你搞清楚“为什么有些语法能生效，有些会报错”，以及“如何写出更高效的 SQL”。

先看一个直观的例子

假设你写了这样一条 SQL：

SELECT username, COUNT(*) AS order_count  -- 步骤6：选择最终要显示的字段
FROM `user` u                            -- 步骤1：确定主表
LEFT JOIN `order` o ON u.id = o.user_id   -- 步骤2：关联其他表
WHERE u.age > 18                         -- 步骤3：过滤行（分组前）
GROUP BY u.username                      -- 步骤4：按用户名分组
HAVING order_count > 2                   -- 步骤5：过滤分组（分组后）
ORDER BY order_count DESC                -- 步骤7：排序
LIMIT 10;                                -- 步骤8：限制结果数量
Enter fullscreen mode Exit fullscreen mode

书写顺序是：SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

但执行顺序是：FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

为什么执行顺序是这样？

SQL 的执行逻辑类似“流水线加工数据”：先准备原材料（表数据），再一步步过滤、加工，最后输出结果。

  1. FROM + JOIN：先确定“数据源”

    数据库首先需要知道“从哪些表取数据”，以及“如何关联这些表”。这一步会生成一个“临时中间表”，包含所有关联后的原始数据（类似把多张表“合并”成一张大表）。

  2. WHERE：过滤“不合格的行”

    在分组之前，先对中间表的数据进行过滤（比如排除年龄<18的用户）。这一步能减少后续处理的数据量（比如分组时不需要处理被过滤掉的行）。

  3. GROUP BY：按规则“分组”

    把过滤后的行按指定字段分组（比如按用户名分组），每组会聚合为一行（后续聚合函数如 COUNT() 就是基于分组计算的）。

  4. HAVING：过滤“不合格的组”

    分组后，对每个组的聚合结果进行过滤（比如只保留订单数>2的用户组）。注意：HAVING 只能用在分组后，且可以使用 GROUP BY 中定义的聚合函数（如 order_count）。

  5. SELECT：选择“最终要显示的字段”

    到这一步，数据已经经过过滤和分组，数据库才会确定“最终返回哪些字段”（比如只显示用户名和订单数）。

    为什么 SELECT 在后面？

    因为前面的步骤（WHERE、GROUP BY）需要基于原始字段处理，而 SELECT 可能会重命名字段（如 COUNT(*) AS order_count），如果 SELECT 先执行，前面的步骤就无法识别原始字段了。

  6. ORDER BY：对结果“排序”

    排序需要基于 SELECT 确定的最终字段（比如按 order_count 降序），所以必须在 SELECT 之后。

  7. LIMIT：限制“返回的行数”

    最后一步截取结果（比如只返回前10条），因为排序后才能确定“哪些行是前10”。

这个顺序对实际开发的影响

  1. WHERE 比 HAVING 更高效

    WHERE 在分组前过滤，能减少参与分组的数据量；HAVING 在分组后过滤，所有数据都要先分组计算。

    例：同样是“筛选年龄>18的用户”，用 WHERE age>18 比 GROUP BY ... HAVING age>18 效率高（后者会先对所有用户分组，再过滤）。

  2. ORDER BY 可以用 SELECT 中的别名，WHERE 不能

    因为 ORDER BY 在 SELECT 之后执行（能识别别名），而 WHERE 在 SELECT 之前（无法识别别名）。

    错误示例：WHERE order_count > 2（order_count 是 SELECT 中定义的别名，WHERE 不认识）。

  3. SELECT 中的字段必须在 GROUP BY 中，或用聚合函数

    因为 GROUP BY 会将每组聚合为一行，如果 SELECT 中的字段不在 GROUP BY 中，也没有聚合（如 SELECT username, age ... GROUP BY username），数据库无法确定 age 取组内哪一行的值（MySQL 有非标准扩展允许这种写法，但不推荐，其他数据库如 PostgreSQL 会直接报错）。

  4. 问：“WHERE 里为什么不能用 SELECT 中的别名？而 HAVING 可以？”

    答：因为 WHERE 在 SELECT 之前执行（此时别名还未定义），而 HAVING 为MySQL 特例：允许用 SELECT 中的别名，其他数据库不行（需写原始聚合函数）。

事务

事务指逻辑上的一组操作，要么都执行要么都不执行

-- 1. 开启事务（关闭自动提交）
START TRANSACTION;  -- 或 BEGIN;

-- 2. 执行SQL操作（如增删改）
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- A扣钱
UPDATE account SET balance = balance + 100 WHERE id = 2;  -- B加钱

-- 3. 提交事务（所有操作生效，持久化到磁盘）
COMMIT;

-- 4. 若出错，回滚事务（所有操作撤销，恢复到开启前状态）
ROLLBACK;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)