MySQL#

本篇用来讲解mysql的实战开发注意点

一、为什么业务表默认用 InnoDB#

现在大多数 MySQL 业务表都会选择 InnoDB，而不是 MyISAM。

后端系统最怕数据不一致。下单、支付、扣库存、退款这些场景都需要事务和崩溃恢复能力，所以 InnoDB 更适合做业务主库。

查看表的存储引擎：

1
SHOW TABLE STATUS LIKE 'orders';

建表时也可以显式指定：

1
CREATE TABLE orders (
2
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
3
  order_no VARCHAR(64) NOT NULL
4
) ENGINE=InnoDB;

二、InnoDB 的数据按主键组织#

InnoDB 中，主键索引就是聚簇索引。可以简单理解为：整行数据就存放在主键索引的叶子节点上。

所以主键设计很重要。

比较推荐：

1
id BIGINT PRIMARY KEY AUTO_INCREMENT

或者使用趋势递增的分布式 ID。

不太推荐把 UUID 字符串直接作为主键：

1
id VARCHAR(36) PRIMARY KEY

原因是 UUID 太长、太随机，容易让 B+ 树频繁页分裂，索引也更占空间。

主键设计建议：

• 尽量短。
• 尽量稳定。
• 尽量趋势递增。
• 不要使用会变化的业务字段。

订单号这种业务字段更适合做唯一索引：

1
UNIQUE KEY uk_order_no (order_no)

三、B+ 树为什么适合索引#

InnoDB 常用 B+ 树作为索引结构。

下图为B+树示意图

B+ 树适合数据库索引，主要因为：

• 树高较低，查询磁盘 IO 次数少。
• 非叶子节点主要存索引键，可以放更多目录信息。
• 叶子节点之间有链表，适合范围查询。

例如订单列表：

1
SELECT id, order_no, total_amount
2
FROM orders
3
WHERE user_id = 1001
4
ORDER BY created_at DESC
5
LIMIT 20;

如果建立索引：

1
CREATE INDEX idx_user_created
2
ON orders(user_id, created_at);

MySQL 可以先通过 user_id 定位到这个用户的订单范围，再利用 created_at 的索引顺序取数据，比全表扫描快很多。

四、二级索引和回表#

InnoDB 的主键索引叶子节点保存完整行数据；普通索引的叶子节点保存的是主键值。

所以通过普通索引查询完整数据时，通常要走两步：

1
普通索引 -> 找到主键 id -> 回到主键索引查整行数据

这个过程叫回表。

例如：

1
CREATE INDEX idx_username ON user(username);
2

3
SELECT *
4
FROM user
5
WHERE username = 'zhangsan';

如果命中 idx_username，MySQL 先找到主键 id，再根据 id 回表查完整字段。

如果只查询索引里已有的字段，就可能避免回表：

1
SELECT id, username
2
FROM user
3
WHERE username = 'zhangsan';

这叫覆盖索引。

实际开发建议：

• 列表页不要无脑 SELECT *。
• 高频查询可以考虑覆盖索引。
• 返回字段越多，回表和网络传输成本越高。

五、联合索引怎么设计#

联合索引不是把常用字段随便堆在一起，而是要围绕真实 SQL 设计。

假设订单列表经常这样查：

1
SELECT id, order_no, total_amount, status, created_at
2
FROM orders
3
WHERE user_id = 1001
4
  AND status = 1
5
  AND deleted = 0
6
ORDER BY created_at DESC
7
LIMIT 20;

可以考虑：

1
CREATE INDEX idx_user_status_deleted_created
2
ON orders(user_id, status, deleted, created_at);

设计联合索引时可以参考：

1. 等值查询字段放前面，比如 user_id、status、deleted。
2. 排序或范围字段放后面，比如 created_at。
3. 区分度太低的字段不要单独建索引，比如 deleted。
4. 结合真实 SQL，不要凭感觉建索引。

一个常见坑：

1
CREATE INDEX idx_status_user ON orders(status, user_id);

如果 status 只有待支付、已支付、已取消几个值，区分度很低，放在最前面通常不如 user_id 合适。

六、索引失效的高频坑#

这些写法在面试和真实开发里都很常见。

1
SELECT *
2
FROM orders
3
WHERE DATE(created_at) = '2026-03-06';

1
SELECT *
2
FROM orders
3
WHERE created_at >= '2026-03-06 00:00:00'
4
  AND created_at < '2026-03-07 00:00:00';

1
SELECT *
2
FROM user
3
WHERE username LIKE '%san';

1
SELECT *
2
FROM user
3
WHERE username LIKE 'zhang%';

1
SELECT *
2
FROM user
3
WHERE phone = 13800000000;

1
SELECT *
2
FROM user
3
WHERE phone = '13800000000';

1
CREATE INDEX idx_user_status_created
2
ON orders(user_id, status, created_at);

1
SELECT *
2
FROM orders
3
WHERE user_id = 1001
4
  AND status = 1;

1
SELECT *
2
FROM orders
3
WHERE status = 1;

七、EXPLAIN 看什么#

优化 SQL 前，先用 EXPLAIN 看执行计划：

1
EXPLAIN
2
SELECT id, order_no, total_amount
3
FROM orders
4
WHERE user_id = 1001
5
  AND status = 1
6
ORDER BY created_at DESC
7
LIMIT 20;

重点看这些字段：

常见 type 从差到好大致是：

1
ALL -> index -> range -> ref -> const

看到 ALL 不一定马上加索引，但大表上出现 ALL 要重点检查。

看到 Using filesort 也不一定是灾难，但如果数据量大、接口慢，就要考虑索引是否能同时满足过滤和排序。

八、Buffer Pool：为什么内存很重要#

InnoDB 不会每次查询都直接读磁盘，它会把数据页和索引页缓存在 Buffer Pool 里。

可以简单理解为：

1
应用查询数据
2
  ↓
3
先看 Buffer Pool 有没有
4
  ↓
5
有：直接从内存读
6
  ↓
7
没有：从磁盘读入 Buffer Pool

所以 MySQL 性能很大程度上依赖内存命中率。

这也解释了为什么同一条 SQL：

• 第一次查可能慢。
• 第二次查可能快。

因为第二次可能命中了缓存。

九、redo log、undo log、binlog#

MySQL 日志很多，最常见的是 redo log、undo log、binlog。

1
Write Ahead Log：先写日志，再写数据页

十、一次 UPDATE 背后发生了什么#

例如：

1
UPDATE product
2
SET stock = stock - 1
3
WHERE id = 100
4
  AND stock > 0;

大致过程可以理解为：

1. 根据主键或索引找到对应数据页。
2. 如果数据页不在 Buffer Pool，就从磁盘读入内存。
3. 对记录加锁，防止并发修改冲突。
4. 写 undo log，方便回滚。
5. 修改内存中的数据页。
6. 写 redo log，保证崩溃恢复。
7. 写 binlog，用于复制和恢复。
8. 事务提交，释放锁。

十一、锁和死锁#

InnoDB 支持行锁，但行锁不是绝对只锁一行。是否能精准锁行，和索引命中有关。

例如：

1
UPDATE orders
2
SET status = 2
3
WHERE id = 1001;

如果 id 是主键，通常只锁对应记录。

但如果条件没有索引：

1
UPDATE orders
2
SET status = 2
3
WHERE order_no = 'A202603060001';

如果 order_no 没有索引，MySQL 可能扫描更多数据，锁范围也可能扩大。

1
事务A：先锁订单1，再锁订单2
2
事务B：先锁订单2，再锁订单1

1
事务A等事务B释放订单2
2
事务B等事务A释放订单1

1
SHOW ENGINE INNODB STATUS;

十二、并发例子（库存问题）#

扣库存是 MySQL 里很经典的并发场景。

不推荐先查再改：

1
SELECT stock
2
FROM product
3
WHERE id = 100;
4

5
UPDATE product
6
SET stock = stock - 1
7
WHERE id = 100;

并发下，多个请求可能都看到库存还有，然后一起扣，导致超卖。

更稳的写法：

1
UPDATE product
2
SET stock = stock - 1
3
WHERE id = 100
4
  AND stock > 0;

然后判断影响行数：

1
影响行数 = 1：扣减成功
2
影响行数 = 0：库存不足

这个写法把“判断库存”和“扣减库存”放到一条 SQL 里，能减少并发问题。

十三、订单列表索引优化例子#

假设订单表有 500 万数据，接口 SQL 是：

1
SELECT id, order_no, total_amount, status, created_at
2
FROM orders
3
WHERE user_id = 1001
4
  AND deleted = 0
5
ORDER BY created_at DESC
6
LIMIT 20;

如果只有单列索引：

1
CREATE INDEX idx_user_id ON orders(user_id);

MySQL 可以先筛出用户订单，但排序可能还要额外处理。

更贴合这个查询的联合索引：

1
CREATE INDEX idx_user_deleted_created
2
ON orders(user_id, deleted, created_at);

这样索引同时服务于：

• user_id 过滤。
• deleted 过滤。
• created_at 排序。

如果列表只返回这些字段，还可以考虑覆盖索引：

1
CREATE INDEX idx_order_list
2
ON orders(user_id, deleted, created_at, id, order_no, total_amount, status);

但覆盖索引不是越长越好。索引字段越多，写入和维护成本越高。是否值得，要看这个接口的调用频率和性能瓶颈。

十四、深分页优化例子#

传统分页：

1
SELECT id, title, created_at
2
FROM article
3
ORDER BY id
4
LIMIT 100000, 20;

这表示 MySQL 要跳过前 100000 条，再取 20 条。页数越深越慢。

如果业务能接受“下一页”模式，可以改成游标分页：

1
SELECT id, title, created_at
2
FROM article
3
WHERE id > 100000
4
ORDER BY id
5
LIMIT 20;

接口返回时带上最后一条 id，下一页继续用这个 id 查询。

这种方式不适合所有场景，但对信息流、评论列表、后台日志很常用。

十五、MySQL 常用排查命令#

查看表结构：

1
DESC orders;

查看建表语句：

1
SHOW CREATE TABLE orders;

查看索引：

1
SHOW INDEX FROM orders;

查看当前事务隔离级别：

1
SELECT @@transaction_isolation;

查看慢查询是否开启：

1
SHOW VARIABLES LIKE 'slow_query_log';

查看慢查询阈值：

1
SHOW VARIABLES LIKE 'long_query_time';

查看 InnoDB 状态：

1
SHOW ENGINE INNODB STATUS;