AI 工程师的 SQL 与数据库实战：从查询到建模到慢查询排查

训练数据提取、特征回写和报表统计都离不开 SQL。查询能运行只是第一步，还需要确认连接有没有放大行数、聚合有没有改变口径、索引是否适合实际访问方式。下面用这些问题串起基础查询和数据库实践。

这篇不讲教科书式的完整语法，聚焦我实际用到最多的部分：JOIN 的正确姿势、窗口函数（分析场景的核武器）、索引与慢查询排查、Python 连接实战、以及给 ML 场景做表设计的取舍。

前置阅读：建议先读 Python 高级编程、Pandas 数据分析与可视化。SQL 和 Pandas 是一体两面，这篇会反复对照。

先把环境搭起来

本地练习我推荐 Docker 起一个 MySQL，三十秒搞定：

docker run -d --name mysql8 -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=123456 \
  -e MYSQL_DATABASE=aidb \
  mysql:8.0

Python 连接用 SQLAlchemy + PyMySQL（别用裸 mysql-connector 到处拼字符串）：

from sqlalchemy import create_engine, text
import pandas as pd

engine = create_engine("mysql+pymysql://root:123456@localhost:3306/aidb")

# 查询直接进 DataFrame，这是日常最高频操作
df = pd.read_sql(text("SELECT * FROM orders LIMIT 100"), engine)

# 写回
df.to_sql("orders_clean", engine, if_exists="replace", index=False, chunksize=1000)

两个我踩过的坑：to_sql 大表一定要加 chunksize，否则单条 INSERT 语句大到超过 max_allowed_packet；连接串里密码有特殊字符（@、#）要 URL encode，不然报莫名其妙的认证失败。

JOIN：一张图胜过千行字

JOIN 的理解成本全在「笛卡尔积之后按条件过滤」这个心智模型上。日常 95% 的场景只需要三种：

-- INNER JOIN：两边都有才保留（订单关联用户）
SELECT o.order_id, u.name
FROM orders o
JOIN users u ON o.user_id = u.user_id;

-- LEFT JOIN：左边全保留，右边没有补 NULL（用户及其订单，含没下单的）
SELECT u.name, COUNT(o.order_id) AS order_cnt
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
GROUP BY u.name;

-- 反连接：只保留右边没有的（找出从未下单的用户）
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE o.order_id IS NULL;

LEFT JOIN + WHERE IS NULL 这个反连接模式我在数据清洗里用了无数次：找孤儿数据、找缺失关联、做增量同步比对。

多对多要小心：orders 一个用户多条、order_items 一个订单多条，直接连会「行数爆炸」。我的纪律是——JOIN 之后立刻 SELECT COUNT(*) 检查行数是否符合预期，这个习惯帮我挡住过无数次下游统计翻倍的线上事故。

窗口函数：分析场景的核武器

GROUP BY 会把明细行「吃掉」，窗口函数不会——它在保留每行的同时，在「窗口」（一组相关行）上计算。AI 工程师的高频场景：

-- 1. 分组排名：每个类目销量前三的商品
SELECT * FROM (
  SELECT category, product, sales,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM product_sales
) t
WHERE rn <= 3;

-- 2. 同比环比：本月与上月对比
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue / LAG(revenue) OVER (ORDER BY month) - 1 AS mom_growth
FROM monthly_revenue;

-- 3. 去重取最新：每个用户最新一次登录记录
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) AS rn
  FROM login_log
) t WHERE rn = 1;

-- 4. 累计与滑窗：7 日移动平均（时序特征工程常用）
SELECT date, pv,
       AVG(pv) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS pv_ma7
FROM daily_pv;

四个排序类函数的区别是面试必考题：ROW_NUMBER() 并列也强制编号（1,2,3,4）；RANK() 并列同名次、跳号（1,2,2,4）；DENSE_RANK() 并列同名次、不跳号（1,2,2,3）。去重取最新一律用 ROW_NUMBER，用 RANK 遇到并列时间会取出多条。

「分组取最新记录」这个模式在特征工程里太常用了：取用户最近一次行为、取设备最新状态。窗口函数之前要靠自连接 JOIN 最大值，又慢又绕。

索引与慢查询：我的排查三板斧

索引本质是 B+ 树（MySQL InnoDB），把「全表扫」变成「树上几次跳跃」。但索引不是免费的：写变慢、占空间。给 WHERE/JOIN/ORDER BY 里高频出现的列建索引，给低基数列（性别这种只有两个值）别建——基数太低 B+ 树退化成半表扫，优化器干脆不用。

排查慢查询我的固定流程：

-- 1. 先看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 ORDER BY created_at DESC LIMIT 10;

盯三个字段：type（ALL 全表扫是红色警报，至少要 ref/range）、key（实际用了哪个索引，NULL 就是没用）、rows（扫描行数，百万级就是问题）。

-- 2. 开慢查询日志抓现行
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;   -- 超过 0.5s 就记录
-- 日志默认在 /var/lib/mysql/*-slow.log

-- 3. 复合索引遵循最左前缀
ALTER TABLE orders ADD INDEX idx_user_time (user_id, created_at);
-- 能命中: WHERE user_id=? / WHERE user_id=? AND created_at>?
-- 不能命中: WHERE created_at>?（跳过了最左列）

真实案例：一张 800 万行的行为日志表，运营的一个报表 SQL 跑了 4 分钟。EXPLAIN 显示全表扫，WHERE 条件里对 created_at 用了 DATE(created_at) = '2026-08-01'——对索引列套函数，索引直接失效。改成范围条件 created_at >= '2026-08-01' AND created_at < '2026-08-02'，加上索引后 0.3 秒。这个「函数杀索引」的坑我见过至少五个团队踩。

其他高频杀手：SELECT * 导致回表（二级索引拿不到全字段要回主键查）；深分页 LIMIT 100000, 10（要扫前 10 万行再丢弃，用游标 WHERE id > last_id LIMIT 10 替代）；隐式类型转换（字符串列传数字，索引失效）。

事务与锁：AI 场景的实用子集

事务 ACID 背概念没用，记住工程结论：

批量写入包事务：一万条 INSERT 逐条提交 vs 一个事务提交，性能差几十倍（每条都刷 redo log）。
隔离级别默认 RR（可重复读）够用；只有金融级账务才考虑 Serializable。
长事务是万恶之源：我在训练数据导出脚本里开着一个事务跑了 2 小时，期间 undo log 暴涨、DDL 全部排队等锁。大查询别放在事务里。

Python 侧的正确写法：

with engine.begin() as conn:   # 自动 commit / 异常 rollback
    conn.execute(text("UPDATE features SET version = 2 WHERE user_id = :uid"), {"uid": 1})
    conn.execute(text("INSERT INTO audit_log VALUES (:msg)"), {"msg": "ok"})

给 ML 场景做表设计的取舍

教科书教你三范式消灭冗余，但 ML/分析场景我经常主动反范式：

特征宽表：训练取数如果每次 JOIN 十张表，ETL 慢且容易出错。离线数仓里常规操作是把用户维、行为聚合、标签全部打平成一张宽表，冗余换查询简单。这在大数据管理那篇里展开过。
JSON 列的合理使用：MySQL 5.7+/PostgreSQL 原生支持 JSON 列。模型参数、实验配置这种 schema 不稳定的元数据存 JSON 列很香；但要进 WHERE 条件的字段必须独立成列——JSON 路径查询走不了索引。
时间戳字段的纪律：每张表都要有 created_at、updated_at，默认 CURRENT_TIMESTAMP。没有这两列，日后做增量同步和排查「这条数据什么时候进来的」会痛不欲生。
软删除：deleted 标记位代替真删。训练数据被人误删一次你就懂了。

SQL vs Pandas：什么时候用哪个

我的分界线：数据量超过内存、只需要一次过滤聚合 → SQL；需要复杂变换、迭代加工、接 sklearn → Pandas。最优组合是 SQL 在库内完成过滤聚合（利用索引和并行），把百万行收成几万行再进 Pandas 精加工。把 5 亿行原表 read_sql 进内存是新手经典事故。

性能对照直觉：Pandas 的 groupby 单机单线程，MySQL/PostgreSQL 的 GROUP BY 可以利用多核和索引；但 Pandas 的向量化变换（rolling、shift、自定义 apply）表达力远超 SQL。两者都要会，拒绝站队。

踩坑排查

现象	原因	解法
`read_sql` 内存爆炸	全表拉取无 WHERE	先 COUNT 评估量级，加 LIMIT/条件，或用 chunksize 迭代
to_sql 报 Packet too large	单批 INSERT 超 max_allowed_packet	chunksize=1000，或调大服务端配置
中文写入变问号	连接或表字符集不是 utf8mb4	连接串加 `?charset=utf8mb4`，建表指定 CHARSET
索引建了没生效	对列套函数 / 隐式类型转换 / 不符合最左前缀	EXPLAIN 验证，改写条件
JOIN 后行数翻几倍	一对多关系叠加	先 COUNT 验证，必要时先聚合再 JOIN
深分页越来越慢	LIMIT N,10 扫描前 N 行	游标式分页 WHERE id > ?
死锁报错 1213	两个事务交叉等锁	固定加锁顺序，事务缩小，重试机制

练习

造三张表 users / orders / order_items，灌 10 万行假数据（用 Python Faker），写 SQL 求「每个用户最近一笔订单的金额」——分别用窗口函数和自连接实现，对比耗时。
给上题表做实验：无索引、单列索引、复合索引三种情况下 EXPLAIN 对比同一个查询，截图记录 type/key/rows 变化。
把 Pandas 文章里的某个分析任务改写成纯 SQL 完成，再对比两边代码量和耗时，体会分工边界。
实现一个「增量同步」脚本：记录上次同步的最大 id，每次只拉新数据 append 进分析库。

面试常问

Q：索引为什么用 B+ 树不用哈希或二叉树？ 哈希只支持等值查询，不支持范围和排序；二叉树深度不可控，磁盘场景每层一次 IO 太贵。B+ 树矮胖（3~4 层撑几千万行）、叶子节点链表天然支持范围扫、非叶子只存键一个节点能放更多扇出。数据库是磁盘数据结构的艺术。

Q：覆盖索引是什么？ 查询要的列全部在索引里，不用回表。EXPLAIN 的 Extra 显示 Using index。比如高频查询只要 user_id 和 created_at，复合索引建这两列，查询只在索引树上完成，快一个量级。

Q：事务隔离级别和对应问题？ 读未提交（脏读）→ 读已提交（不可重复读）→ 可重复读（幻读，MySQL 默认，InnoDB 用 next-key lock 基本解决）→ 串行化（性能差）。记忆锚点：RR 下同一事务内两次读结果一致。

Q：分库分表什么时候做？ 单表千万级其实 MySQL 还能扛（索引设计好）；到亿级或写入 QPS 上万再考虑。顺序：先优化索引和 SQL → 读写分离 → 垂直拆分（按业务拆库）→ 水平分表（sharding）。一上来就分库分表是过度设计，分布式事务的复杂度会吃掉所有收益。

Q：窗口函数和 GROUP BY 的区别？ GROUP BY 把每组聚合成一行，明细消失；窗口函数保留所有明细行，在每行上「开窗」计算组内统计。要明细+聚合同时存在（如「每行 + 该行所属组的平均值」）用窗口函数。