后端开发绕不开的 SQL：从建表、查询到索引优化，一篇讲透

先吃饱再说
101 阅读13分钟

后端开发绕不开的 SQL：从建表、查询到索引优化，一篇讲透

摘要：NestJS 代码最终都会被翻译成 SQL 发往数据库。本文从 SQL 的核心分类出发，拆解建表规范、字段类型选型、约束设计、查询执行顺序，深入 B+树索引原理与最左前缀原则，并结合博客系统的多表设计，展示后端开发中 SQL 的完整实践。

📑 目录

  • 为什么后端开发必须懂 SQL？
  • SQL 四大分类：你 95% 的时间在写哪两类？
  • 建库与建表：从字符集到字段类型
  • 约束：让数据库替你“把关”
  • CRUD 操作：两条安全红线
  • 查询语句的物理执行顺序
  • 聚合函数：数据统计的“计算器”
  • 索引：查询加速器的底层原理
  • 博客系统多表设计实战
  • 互动讨论

为什么后端开发必须懂 SQL？

你用 NestJS 写的 TypeORM、Prisma、Knex 代码，最终都会被翻译成 SQL，发往 MySQL 或 PostgreSQL 去执行。

一个形象的类比：

  • 你的 NestJS 代码（TypeScript）是  “前台点单员” （接收请求、校验参数）
  • SQL 是  “后厨的标准化语言” （告诉数据库“怎么切菜、怎么炒菜”）
  • MySQL 是  “后厨的实际操作台” （真正执行数据读写）

如果不懂 SQL，你无法理解为什么某个查询慢、为什么索引没生效、为什么联合主键的顺序会影响性能。懂 SQL 不是为了手写所有查询，而是为了在 ORM 生成低效 SQL 时，能精准定位和优化。

SQL 四大分类：你 95% 的时间在写哪两类？

分类全称作用你接触过的语句
DDLData Definition Language定义数据库结构CREATE、ALTER、DROP
DMLData Manipulation Language操作数据（增、删、改）INSERT、UPDATE、DELETE
DQLData Query Language查询数据（最常用）SELECT
DCLData Control Language权限管理GRANT、REVOKE

作为后端开发，95% 的时间都花在 DQL 和 DML 上——查数据和改数据。

建库与建表：从字符集到字段类型

建库（CREATE DATABASE）

sql

CREATE DATABASE IF NOT EXISTS `my_app_db`
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
参数含义
IF NOT EXISTS库已存在则跳过，避免重复执行报错
utf8mb4必须用这个，支持 Emoji，不要用 utf8（MySQL 的 utf8 实际只支持 3 字节，存不了 Emoji）
utf8mb4_unicode_ci大小写不敏感的排序规则，ci 即 Case Insensitive

建表（CREATE TABLE）

sql

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `email` VARCHAR(100) NOT NULL,
  `age` INT NOT NULL DEFAULT 18,
  `status` TINYINT NOT NULL DEFAULT 1,
  `bio` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

字段类型选型指南

① 数值类型

类型字节范围使用场景
TINYINT1-128 ~ 127状态码、性别、开关
INT4±21亿用户 ID、订单号（绝大多数够用）
BIGINT8极大雪花算法 ID
DECIMAL(10,2)可变精确小数金额（必须用这个！）

警告：金额绝对不要用 FLOAT 或 DOUBLE，浮点数会有精度丢失。0.1 + 0.2 在浮点数中不等于 0.3，这是金融系统的大忌。

② 字符串类型

类型最大长度存储方式使用场景
CHAR(n)255固定长度手机号、MD5 密码（性能最高）
VARCHAR(n)~65000 字符可变长度用户名、邮箱、标题
TEXT65535 字符单独存储文章正文、长评论（不能设默认值）

③ 日期时间类型

类型特点使用场景
DATETIME不受时区影响活动开始时间、固定时间点
TIMESTAMP受时区影响，更省空间created_at、updated_at（推荐）
DATE仅日期生日、日期统计

约束：让数据库替你“把关”

约束是 MySQL 自动帮你检查的规则，不需要你写额外的 if 判断。数据在写入时如果违反约束，MySQL 直接拒绝并报错。

常用约束速查

约束SQL 写法作用
NOT NULLemail VARCHAR(100) NOT NULL必须填，不能为空
DEFAULTage INT DEFAULT 18不填时自动填充默认值
UNIQUEusername VARCHAR(50) UNIQUE不允许重复值
AUTO_INCREMENTid INT AUTO_INCREMENT自增主键
PRIMARY KEYPRIMARY KEY (id)主键（唯一标识）

CONSTRAINT：给约束起名字（强烈推荐）

作用：报错时能直接看到是哪个约束出了问题，而不是看到一个自动生成的神秘名字。

sql

CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_no VARCHAR(32) NOT NULL,
  user_id INT NOT NULL,
  CONSTRAINT uk_orders_no UNIQUE (order_no),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);

对比：

  • ❌ 没起名字：Duplicate entry 'ORD-001' for key 'order_no_2'
  • ✅ 起了名字：Duplicate entry 'ORD-001' for key 'uk_orders_no'

外键（FOREIGN KEY）与级联策略

sql

CONSTRAINT fk_orders_user 
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
策略含义适用场景
RESTRICT（默认）子表有引用时禁止删除父表财务、核心订单数据
CASCADE父表删/改，子表自动同步删除用户时清理购物车、评论
SET NULL父表删/改，子表外键置空保留订单记录但断开关联

CRUD 操作：两条安全红线

sql

-- 插入
INSERT INTO users (username, email) VALUES ('大许', 'daxu@email.com');

-- 查询
SELECT id, username, email FROM users;

-- 更新（⚠️ 必须加 WHERE！）
UPDATE users SET age = 19 WHERE id = 1;

-- 删除（⚠️ 必须加 WHERE！）
DELETE FROM users WHERE id = 2;

安全红线：UPDATE 和 DELETE 不加 WHERE 会操作全表。在 NestJS 的 ORM 中，忘记加 where 条件同样危险。

查询语句的物理执行顺序

书写顺序 ≠ 执行顺序。这是 SQL 中最容易踩的坑。

sql

SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent
FROM orders
WHERE status = 'paid' 
  AND created_at >= '2026-01-01'
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER BY total_spent DESC
LIMIT 10 OFFSET 0;

真正的执行顺序：

执行顺序关键词作用
①FROM确定数据来源表
②WHERE筛选行（分组前过滤）
③GROUP BY分组
④HAVING筛选组（分组后过滤）
⑤SELECT计算聚合函数，取最终列
⑥ORDER BY排序
⑦LIMIT / OFFSET分页

关键区别：WHERE 过滤行，HAVING 过滤组。

sql

-- ❌ 错误：WHERE 里不能写聚合函数
SELECT user_id, COUNT(*) FROM orders 
WHERE COUNT(*) > 3 GROUP BY user_id;

-- ✅ 正确：用 HAVING
SELECT user_id, COUNT(*) FROM orders 
GROUP BY user_id 
HAVING COUNT(*) > 3;

聚合函数：数据统计的“计算器”

函数作用示例
COUNT(*)统计总行数SELECT COUNT(*) FROM users;
COUNT(列名)统计该列非空行数COUNT(email)
COUNT(DISTINCT 列名)统计不重复值个数COUNT(DISTINCT user_id)
SUM(列)求和SELECT SUM(amount) FROM orders;
AVG(列)平均值SELECT AVG(age) FROM users;
MAX(列)最大值SELECT MAX(amount) FROM orders;
MIN(列)最小值SELECT MIN(created_at) FROM users;

实战示例：

sql

SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders 
WHERE status = 'paid';

索引：查询加速器的底层原理

索引为什么快？

没有索引 → 全表扫描（逐行翻找）
有索引 → B+树多级目录（跳着找）

没有索引时，MySQL 只能一行行读完整个表，直到找到符合条件的记录——这就是  “全表扫描（Full Table Scan）” 。

有了索引，就像有了一本书的目录。索引（B+树）本质上就是一个  “多级目录” ，存放了 索引字段的值 + 指向完整数据行的指针。

主键索引 vs 普通索引

索引类型叶子节点存什么？说明
主键索引（聚集索引）整行完整数据主键自带的索引，权重最高
普通索引（非聚集索引）索引字段的值 + 主键值（ID）查到后还需“回表”

什么是“回表”？

先查普通索引找到匹配记录的主键 ID，再通过主键索引找到最终记录的完整数据。这个“两步走”的过程就是  “回表（Back to Table）” 。

联合索引与“最左前缀原则”

联合索引 INDEX idx_name_age (name, age) 的物理存储顺序是：先按 name 排序，在 name 组内再按 age 排序。

SQL 写法是否走索引？原因
WHERE name = '大许'✅ 命中索引第一列是 name
WHERE name = '大许' AND age = 18✅ 完全命中先定位 name，再定位 age
WHERE age = 18❌ 不命中跳过了 name，索引无法定位

核心结论：联合索引不是“只有第一列建索引”，而是“第二列依赖第一列排序”。跳过第一列，整个索引失去意义。

优化建议：联合主键中，非最左字段需要单独建普通索引。例如联合主键 (user_id, order_id)，单独查 order_id 用不上这个索引，需要单独给 order_id 建索引。

覆盖索引

如果查询的所有字段都在索引里，MySQL 就不需要回表了。

sql

-- 覆盖索引：不需要回表（name 和 age 都在索引里）
SELECT name, age FROM users WHERE name = '大许';

-- 非覆盖索引：需要回表取 email
SELECT name, age, email FROM users WHERE name = '大许';

索引设计原则

  1. 给 WHERE、ORDER BY、JOIN 中频繁出现的字段加索引
  2. 联合索引的顺序：最常用的过滤条件放最左边
  3. 不要滥用索引（索引会拖慢写入速度，每次 INSERT/UPDATE 都要更新索引）
  4. 联合主键中，非最左字段需要单独建普通索引

博客系统多表设计实战

以一个经典的个人博客为场景，涉及文章、点赞、收藏、评论、用户、标签等核心模块。

用户表（user）

用户表是系统的核心，只存储最关键的字段，有利于快速查询和分布式扩展。

sql

CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

设计说明：

  • id 是自增主键，用作用户唯一标识
  • name 同时加 UNIQUE 约束，确保用户名不重复，且作为高频查询条件（搜索用户、登录）利用索引加速
  • 头像、个性签名等扩展信息独立建表关联，保持 user 表小而精

头像表（avatar）

头像等静态资源一般存储在静态服务器或云 OSS 上，数据库中只存储文件的元信息。

sql

CREATE TABLE `avatar` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `mimetype` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `filename` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `size` int(11) NOT NULL,
    `userId` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `userId` (`userId`),
    CONSTRAINT `avatar_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

设计说明：

  • userId 建立普通索引是因为查询用户头像时高频使用
  • 外键约束确保头像记录始终对应一个存在的用户

文章表（post）

sql

CREATE TABLE `post` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `content` longtext COLLATE utf8mb4_unicode_ci,
    `userId` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `userId` (`userId`),
    CONSTRAINT `post_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

设计说明：

  • content 用 longtext 存储长文章内容
  • title 不允许为空，但 content 可以为空（允许用户先创建标题再写内容）

点赞表（user_like_post）

多对多关系表，记录哪个用户点赞了哪篇文章。

sql

CREATE TABLE `user_like_post` (
    `userId` int(11) NOT NULL,
    `postId` int(11) NOT NULL,
    PRIMARY KEY (`userId`, `postId`),
    KEY `postId` (`postId`),
    CONSTRAINT `user_like_post_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
    CONSTRAINT `user_like_post_ibfk_2` FOREIGN KEY (`postId`) REFERENCES `post` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

索引设计分析：

  • PRIMARY KEY (userId, postId) 是联合主键，也是索引
  • 单独给 postId 建普通索引，因为查询“哪些用户点赞了某篇文章”时以 postId 为条件，不走联合主键（不满足最左前缀）
  • 不需要单独给 userId 建索引，因为 (userId, postId) 联合主键已经覆盖了以 userId 为条件的查询（最左前缀原则：userId 在最左边，命中）

评论表（comment）

支持多级评论（楼中楼），通过 parentId 实现自关联。

sql

CREATE TABLE `comment` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `content` longtext COLLATE utf8mb4_unicode_ci,
    `postId` int(11) NOT NULL,
    `userId` int(11) NOT NULL,
    `parentId` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `postId` (`postId`),
    KEY `userId` (`userId`),
    KEY `parentId` (`parentId`),
    CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
    CONSTRAINT `comment_ibfk_2` FOREIGN KEY (`parentId`) REFERENCES `comment` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `comment_ibfk_3` FOREIGN KEY (`postId`) REFERENCES `post` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

设计说明：

  • parentId 指向同表的另一条评论，实现“楼中楼”结构
  • parentId 的外键使用 ON DELETE SET NULL——如果父评论被删除，子评论的 parentId 置为空（保留子评论）

标签表（tag）与文章-标签关联表

sql

CREATE TABLE `tag` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `post_tag` (
    `postId` int(11) NOT NULL,
    `tagId` int(11) NOT NULL,
    PRIMARY KEY (`postId`, `tagId`),
    KEY `tagId` (`tagId`),
    CONSTRAINT `post_tag_ibfk_1` FOREIGN KEY (`postId`) REFERENCES `post` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `post_tag_ibfk_2` FOREIGN KEY (`tagId`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

文件表（file）

上传文件元信息存储，支持图片尺寸记录。

sql

CREATE TABLE `file` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `originalname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `mimetype` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `filename` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `size` int(11) NOT NULL,
    `postId` int(11) DEFAULT NULL,
    `userId` int(11) NOT NULL,
    `width` smallint(6) NOT NULL,
    `height` smallint(6) NOT NULL,
    `metadata` json DEFAULT NULL,
    KEY `postId` (`postId`),
    KEY `userId` (`userId`),
    CONSTRAINT `file_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
    CONSTRAINT `file_ibfk_2` FOREIGN KEY (`postId`) REFERENCES `post` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

设计说明：

  • metadata 使用 json 类型存储扩展信息（如图片 EXIF、压缩参数等）
  • width 和 height 单独列出来便于列表展示，避免每次都要解析 JSON

互动讨论

💬 为什么金额必须用 DECIMAL 而不用 FLOAT？

FLOAT 和 DOUBLE 是近似值类型，存在精度丢失。0.1 + 0.2 在浮点数中不等于 0.3。金融系统要求绝对精确，必须用 DECIMAL（定点数）。

💬 WHERE 和 HAVING 有什么区别？

WHERE 在分组前过滤行，HAVING 在分组后过滤组。WHERE 不能使用聚合函数（如 COUNT()），HAVING 可以。

💬 联合索引的最左前缀原则是什么意思？

联合索引 (a, b, c) 的物理存储顺序是“先按 a 排序，再按 b，再按 c”。查询条件如果从 a 开始连续匹配（如 WHERE a=1 AND b=2）则命中索引；如果跳过了 a（如 WHERE b=2）则索引失效。

💬 普通索引为什么需要“回表”？

主键索引（聚集索引）的叶子节点存的是整行数据。普通索引（非聚集索引）的叶子节点存的是“索引字段的值 + 主键值”。用普通索引查到主键后，还要再到主键索引里取完整数据，这就是“回表”。

💬 覆盖索引怎么理解？

如果 SELECT 的字段全部都在索引里，MySQL 就不需要回表了，直接从索引中返回数据。这就是“覆盖索引”，效率最高。

avatar
文章
阅读
粉丝