文章

MySQL基础深度解析

面试最高频的 MySQL 核心：CRUD 与索引怎么用、事务 ACID 与隔离级别怎么选、慢查询怎么用 EXPLAIN 排查，附可直接背的易错点。

发表于
作者 独行的风
13 分钟阅读
MySQL基础深度解析

一句话概括

MySQL 是后端用得最多的关系型数据库，也是前端转全栈必须跨过的一道坎。它把数据按”库 → 表 → 行”组织起来，用 SQL 这套标准语言来增删改查，核心就三件事：怎么把数据存进去、怎么快速查出来、怎么保证多步操作不出错。

面试里 MySQL 一般不考你背命令，而考你”懂不懂底层权衡”——为什么加了索引查询快几十倍、事务为什么能回滚、脏读不可重复读到底是什么。把索引、事务、慢查询这三点讲透，你就能接住 80% 的数据库面试题。

核心知识点

1. CRUD：把表当成”持久化的 JSON 数组”

对前端最友好的理解：一张表 = 一个永远在线的数组，INSERT 就是 push，SELECT 就是 filter，UPDATE 就是改元素，DELETE 就是 splice。区别是它落在磁盘上、支持并发、还能用索引秒查。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 建表（InnoDB 引擎 + utf8mb4，生产标准写法）
CREATE TABLE users (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  username  VARCHAR(50) NOT NULL UNIQUE,
  email     VARCHAR(100) NOT NULL,
  age       INT DEFAULT 18,
  city      VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- CREATE：插入单条 / 多条
INSERT INTO users (username, email, age, city)
VALUES ('alice', 'alice@example.com', 25, 'Beijing');

INSERT INTO users (username, email, age, city) VALUES
  ('bob',   'bob@example.com',   30, 'Shanghai'),
  ('diana', 'diana@example.com', 28, 'Shenzhen');

-- READ：条件 / 排序 / 分页
SELECT username, email FROM users WHERE city = 'Beijing' AND age > 20;
SELECT * FROM users ORDER BY age DESC LIMIT 3;
SELECT * FROM users ORDER BY id DESC LIMIT 20 OFFSET 40;   -- 第 3 页

-- UPDATE / DELETE：⚠️ 永远先想 WHERE，忘写 WHERE 就是全表受灾
UPDATE users SET age = age + 1 WHERE username = 'bob';
DELETE FROM users WHERE username = 'diana';

用 Node.js（mysql2）写查询务必用参数化占位符 ?，这是防 SQL 注入的唯一正解：

1
2
3
4
5
6
7
8

// ❌ 拼接字符串：用户输入 ' OR '1'='1 直接拖库
const sql = `SELECT * FROM users WHERE username = '${userInput}'`;

// ✅ 参数化查询：数据与指令分离，特殊字符被自动转义
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE username = ?',
  [userInput]
);

2. 索引：B+ 树 + 最左前缀

没有索引时 MySQL 只能全表扫描（一行行翻），数据量一大就慢到离谱；加了索引走 B+ 树，几层就能定位，复杂度从 O(n) 降到 O(log n)。主键索引（聚簇索引）叶子节点直接存整行；普通索引（二级索引）叶子存主键值，查到后还要”回表”再查一次。

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE products (
  id          INT AUTO_INCREMENT PRIMARY KEY,   -- 主键自动是聚簇索引
  name        VARCHAR(100),
  category_id INT,
  price       DECIMAL(10,2),
  INDEX idx_cat (category_id),                  -- 单列普通索引
  INDEX idx_cat_price (category_id, price)      -- 复合索引（注意列顺序）
);

-- 验证是否命中索引
EXPLAIN SELECT * FROM products WHERE category_id = 10;
-- 看输出里的 key 列：有值=走了索引，NULL=全表扫（type 为 ALL）

复合索引记住最左前缀法则——索引 (category_id, price) 是按列从左到右生效的：

1
2
3
4
5
6

-- ✅ 命中：用到了最左列
SELECT * FROM products WHERE category_id = 10;
SELECT * FROM products WHERE category_id = 10 AND price > 100;

-- ❌ 失效：跳过了最左列 category_id，只能全表扫
SELECT * FROM products WHERE price > 100;

索引不是越多越好：它占用磁盘、拖慢写入（每次增删改都要维护索引），只对高频出现在 WHERE / JOIN / ORDER BY 的列建索引。面试常让对比：

维度聚簇索引（主键）二级索引
叶子节点存整行数据主键值
查询路径直接拿到数据先拿主键，再回表
数量每表至多 1 个可多个

3. 事务与隔离级别：ACID 到底保什么

事务 = 一组”要么全成、要么全滚”的操作。转账就是经典例子：A 扣钱、B 加钱，中间断了钱就凭空消失，所以必须包在事务里。

1
2
3
4
5

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;      -- 都成功才提交
-- ROLLBACK; -- 任一步出错就回滚到事务前

ACID 四个字母要能张口就来：

  • Atomicity 原子性：要么全做要么全不做（靠 undo log 回滚）
  • Consistency 一致性：数据从一个合法状态变到另一个合法状态
  • Isolation 隔离性：并发事务互不干扰（靠锁 + MVCC）
  • Durability 持久性：提交后断电也不丢（靠 redo log）

隔离级别解决的是”并发读写互相看到什么”，级别越高越安全越慢：

隔离级别脏读不可重复读幻读说明
READ UNCOMMITTED❌ 可能❌ 可能❌ 可能几乎不用
READ COMMITTED✅ 避免❌ 可能❌ 可能Oracle 默认
REPEATABLE READ✅ 避免✅ 避免❌ 大部分避免MySQL 默认
SERIALIZABLE✅ 避免✅ 避免✅ 避免串行执行，最慢
1
2
3
4
5
6
7
8
9

SELECT @@transaction_isolation;                        -- 查看当前隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 悲观锁：先锁住这行，别人改不了（直到事务结束）
SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE;

-- 乐观锁：靠版本号，改失败时 affected_rows = 0，由业务重试
UPDATE inventory SET quantity = quantity - 1, version = version + 1
WHERE product_id = 1 AND version = 3;

4. 慢查询排查：EXPLAIN 是标尺

线上接口慢，第一步不是猜，是开慢查询日志 + 看 EXPLAIN。

1
2
3
4
5
6

-- 开启慢查询（默认 long_query_time = 10 秒，生产建议调到 1~2 秒）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 用 EXPLAIN 看执行计划
EXPLAIN SELECT * FROM products WHERE category_id = 10 ORDER BY price;

看 EXPLAIN 重点盯这几列：

  • type：从好到坏 const > ref > range > index > ALL，见到 ALL 就是全表扫，要警惕
  • key：实际用到的索引，NULL 说明没走索引
  • rows：预估扫描行数，越大越慢
  • Extra：出现 Using filesort / Using temporary 通常要优化

优化套路（按性价比排序）：加对索引 → 别在 WHERE 列上套函数（会失效）→ 避免 SELECT * → 深分页用游标代替 OFFSET。

1
2
3
4
5

-- ❌ 深分页：OFFSET 越大越慢，要扫过前面所有行
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100000;

-- ✅ 游标分页：记住上一页最大 id，直接定位
SELECT * FROM posts WHERE id < 100020 ORDER BY id DESC LIMIT 20;

其实你每天都在用

  • 注册登录：SELECT * FROM users WHERE username = ? 校验账号，背后就是索引在加速
  • 商品列表分页：ORDER BY ... LIMIT 20 OFFSET ?，你翻到第 10 页时 MySQL 正在扫前 200 条
  • 点赞 / 收藏计数：UPDATE posts SET likes = likes + 1 其实被包在事务里保证不丢
  • 下单扣库存：-100 和 +100 必须同一事务，否则钱会”消失”
  • 前端表单防注入：你写的 fetch('/api?name=' + input) 拼到 SQL 里就是注入漏洞，mysql2 的 ? 占位符救你
  • 订单状态筛选：WHERE status = 'paid' AND created_at > ? 走复合索引最左前缀
  • 后台看板：GROUP BY city 统计各地区用户数，就是聚合查询

常见误解（FAQ）

❌ 误区一：”加了索引查询就一定快”

不一定。索引失效的常见坑：违反最左前缀（跳过了复合索引最左列）、在列上套函数（WHERE YEAR(created_at)=2024 用不上索引）、用 != / OR 连不相关的列、隐式类型转换（字符串字段传了数字）。索引也要维护成本，写多读少的表加太多索引反而拖慢写入。

❌ 误区二：”事务隔离级别越高越好，直接上 SERIALIZABLE”

级别越高并发性能越差。MySQL 默认 REPEATABLE READ 已经靠 MVCC 解决了绝大部分脏读、不可重复读问题，没必要无脑拉满。绝大多数业务用默认级别 + 必要的 FOR UPDATE 悲观锁就够；只有强一致的计数/扣款场景才考虑更严的级别。

❌ 误区三：”MyISAM 和 InnoDB 差不多，随便选”

现在默认且几乎只该用 InnoDB。MyISAM 不支持事务、不支持行锁（只有表锁）、崩溃后可能丢数据；InnoDB 支持事务、行级锁、外键、崩溃恢复（redo/undo log）。新项目别再纠结，直接 InnoDB。

❌ 误区四：”参数化查询只是风格问题，拼接字符串也能跑”

拼接字符串是 SQL 注入的根源，属于生产事故级问题。参数化查询不是”美观”，而是把数据与指令彻底分离，数据库会对输入做转义，从根本上堵死 ' OR '1'='1 这类攻击。任何用户输入进 SQL 都必须走占位符。

一句话总结

MySQL 的核心就一句话：索引决定查得快不快，事务决定数据稳不稳，EXPLAIN 决定你排查问题靠不靠谱——把这三件事讲明白，数据库面试这一关你就站住了。

服务端, 数据库
MySQL 索引 事务 隔离级别 慢查询 SQL注入
本文由作者按照 CC BY 4.0 进行授权