数据库基础04-数据库 SQL 进阶
数据库 SQL 进阶：索引、关联、聚合与事务
目录
进入进阶
增删改查是单表基本功。现实中业务数据分散在多张表、量大、查询复杂，因此需要以下进阶能力：索引（让查询快）、JOIN（跨表关联）、聚合/分组（统计）、事务（保证一致）、视图（封装复用）、EXPLAIN（排查慢查询）。这些都是运维排查与调优的高频技能。
下文沿用前一篇的 user 表，并新增一张 order 表做多表示例。
一、索引：让查询变快
1.1 为什么需要索引
没有索引时，数据库要查某行只能全表扫描（一行行比对），表越大越慢。有了索引，数据库能像查目录一样快速定位到数据，查询时间可以从 O(n) 降到接近 O(log n)。索引的本质是用空间（额外存储）换时间（查询速度）。
1.2 创建与删除索引
-- 建表后给某列加索引
CREATE INDEX idx_email ON user(email);
-- 等价写法（ALTER）
ALTER TABLE user ADD INDEX idx_email (email);
-- 唯一索引（保证不重复且加速查找）
CREATE UNIQUE INDEX idx_username ON user(username);
-- 组合索引（多列，注意列顺序影响效果）
CREATE INDEX idx_age_username ON user(age, username);
-- 查看某表的索引
SHOW INDEX FROM user;
-- 删除索引
DROP INDEX idx_email ON user;
1.3 什么时候该建索引
- WHERE 条件里经常出现的列（如按
email查账号）； - JOIN 的关联列（见第二章）；
- ORDER BY / GROUP BY 用的列；
- 主键、唯一约束列会自动建索引，无需手动建。
1.4 索引不是越多越好
每个索引都要占用存储，且写入（INSERT/UPDATE）时要同步维护索引，索引过多会拖慢写入。给「少查询、多写入」的表加太多索引反而得不偿失。原则：只给真正高频查询的列加索引。
用 EXPLAIN SELECT ... 可以查看一条查询是否用到了索引、是否全表扫描（详见第七章）。
二、多表关联：JOIN
2.1 为什么要 JOIN
现实业务中数据分散在多张表，例如订单表里只有 user_id，要拿用户名就得关联用户表。JOIN 就是把多张表按某列关联起来查询。
先建一张关联表做示例（order 是 SQL 保留字，作表名要加反引号转义）：
CREATE TABLE `order` (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `order` (user_id, amount) VALUES (1, 99.00), (1, 59.50), (2, 199.00);
2.2 INNER JOIN（内连接）：只取两表都匹配的行
SELECT u.username, o.amount
FROM `order` o
INNER JOIN user u ON o.user_id = u.id;
结果只包含「订单」和「用户」都存在的行（user_id=1、2 能关联上；若某个 user_id 在 user 表里没有，则这行不出现）。
2.3 LEFT JOIN（左连接）：左表全保留，右表无匹配则补 NULL
SELECT u.username, o.amount
FROM user u
LEFT JOIN `order` o ON o.user_id = u.id;
结果以左表 user 为准，每个用户都出现；没有订单的用户其 amount 为 NULL。LEFT JOIN 常用于「以某表为主，查出有没有关联数据」。
表别名（u、o）让 SQL 更短；ON 指定关联条件。JOIN 的关联列（o.user_id、u.id）建议有索引，否则大表 JOIN 会很慢。
三、聚合函数
聚合函数对一组行做统计，返回单个值：
| 函数 | 作用 | 示例 |
|---|---|---|
COUNT(*) |
计数（行数） | SELECT COUNT(*) FROM user; |
SUM(列) |
求和 | SELECT SUM(amount) FROM \order`;` |
AVG(列) |
平均值 | SELECT AVG(age) FROM user; |
MAX(列) |
最大值 | SELECT MAX(age) FROM user; |
MIN(列) |
最小值 | SELECT MIN(age) FROM user; |
-- 统计订单总数与总金额
SELECT COUNT(*) AS 订单数, SUM(amount) AS 总金额 FROM \`order\`;
-- 配合 WHERE 统计某类数据
SELECT COUNT(*) FROM user WHERE age >= 18;
四、分组：GROUP BY 与 HAVING
GROUP BY 把数据按某列分组，再对每组做聚合。例如按用户统计每个人的订单总额：
SELECT user_id, COUNT(*) AS 单数, SUM(amount) AS 总额
FROM `order`
GROUP BY user_id;
HAVING 用于对分组后的结果过滤（WHERE 过滤的是分组前的行）：
-- 找出订单总额超过 100 的用户
SELECT user_id, SUM(amount) AS 总额
FROM `order`
GROUP BY user_id
HAVING SUM(amount) > 100;
两者的区别：WHERE 在分组之前过滤行，HAVING 在分组之后过滤组。WHERE 里不能用聚合函数，HAVING 可以。
五、事务：ACID 与提交回滚
5.1 什么是事务
事务（transaction） 把一组操作打包成「要么全成、要么全不成」的整体，保证数据一致。经典的转账例子：A 扣 100、B 加 100，必须同时成功或同时失败，不能只做一半。
5.2 ACID 四特性
InnoDB 支持事务，事务有四个特性，合称 ACID：
| 特性 | 含义 |
|---|---|
| Atomicity 原子性 | 事务内操作要么全成功要么全回滚，不可分割 |
| Consistency 一致性 | 事务前后数据都满足约束，从一致到一致 |
| Isolation 隔离性 | 并发事务互不干扰 |
| Durability 持久性 | 提交后数据永久保存，即使宕机也不丢 |
5.3 手动控制事务
START TRANSACTION; -- 开启事务
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 全部成功，提交
-- 或发现问题时
ROLLBACK; -- 全部撤销
日常运维一般不用手写事务（应用层会处理），但理解 ACID 有助于排查「为什么这笔数据没生效」「为什么并发下数据乱了」等问题。默认 autocommit 开启，单条语句自动提交。
六、视图：VIEW
视图（view） 可以理解成「虚拟表」——它把一条 SELECT 查询封装成一个可复用的表，本身不存数据，数据仍来自底层真实表。适合：隐藏复杂查询、给不同角色提供受限的字段、复用常用查询。
-- 创建一个视图：每个用户的订单总额
CREATE VIEW user_order_total AS
SELECT u.username, SUM(o.amount) AS total
FROM user u
LEFT JOIN `order` o ON o.user_id = u.id
GROUP BY u.id, u.username;
-- 用视图就像查表一样
SELECT * FROM user_order_total;
-- 查看视图定义
SHOW CREATE VIEW user_order_total\G
-- 删除视图
DROP VIEW user_order_total;
视图简化查询，但不能过度依赖：复杂的视图嵌套太多会变慢、难以调优。运维排查时多用 SHOW CREATE VIEW 看清它背后到底查了什么。
七、慢查询排查：EXPLAIN
7.1 EXPLAIN 是什么
EXPLAIN SELECT ... 让数据库不真正执行，而是告诉你它「打算怎么执行」这条查询：走没走索引、扫了多少行、用哪种连接方式。这是排查慢查询的第一利器。
EXPLAIN SELECT * FROM user WHERE email = 'a@ex.com';
7.2 重点看哪几列
| 列 | 含义 | 怎么看 |
|---|---|---|
type |
访问类型 | 出现 ALL（全表扫描）就说明没走索引，要警惕 |
key |
实际用到的索引 | 为 NULL 说明没用到索引 |
rows |
预估扫描的行数 | 越小越快 |
Extra |
额外信息 | 出现 Using filesort 或 Using temporary 常意味着有优化空间 |
7.3 排查思路
-- 开启慢查询日志（记录超过阈值的查询）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒的查询记入日志
-- 对可疑 SQL 用 EXPLAIN 看是否走了索引
EXPLAIN SELECT * FROM user WHERE email = 'a@ex.com';
-- 若 type=ALL、key=NULL，给该列加索引即可
CREATE INDEX idx_email ON user(email);
排查慢查询最常用的套路：先开慢查询日志定位慢 SQL → 用 EXPLAIN 看是否全表扫描/缺索引 → 加索引或改写 SQL。这是运维优化数据库性能的日常动作。
八、总结
- 索引：用空间换时间，让 WHERE/JOIN/ORDER BY 变快；只给高频查询列加，不是越多越好；
- JOIN：
INNER JOIN取两表都匹配的行，LEFT JOIN左表全保留、右表无匹配补 NULL；关联列要有索引； - 聚合/分组：
COUNT/SUM/AVG/MAX/MIN+GROUP BY做统计，HAVING过滤分组结果； - 事务：ACID 保证「全成或全不成」，
START TRANSACTION/COMMIT/ROLLBACK； - 视图：把查询封装成虚拟表，复用与简化，但别过度嵌套；
- EXPLAIN：看
type/key/rows/Extra，结合慢查询日志定位并优化慢 SQL。
用户权限、备份恢复与常见排错等运维内容在《数据库运维实践》中展开。
参考资料
- MySQL 官方手册（EXPLAIN 输出格式）：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
- MySQL 官方手册（JOIN 语法）：https://dev.mysql.com/doc/refman/8.0/en/join.html
- MySQL 官方手册（事务）：https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html
- 菜鸟教程·SQL 连接 JOIN：https://www.runoob.com/sql/sql-join.html
- 菜鸟教程·SQL 排序：https://www.runoob.com/sql/sql-orderby.html
- 菜鸟教程·SQL 分组：https://www.runoob.com/sql/sql-groupby.html
- 菜鸟教程·SQL 主键：https://www.runoob.com/sql/sql-primarykey.html