爬虫数据落库实战：MySQL/PostgreSQL表设计、索引与Upsert

说实话，爬虫做到第十天，十个人里有八个会开始思考同一个问题：抓下来的数据到底该往哪儿放？CSV文件打开乱码、Excel卡到崩溃、重复数据堆成山，这些我都经历过。搞到后面你会发现，爬虫真正拉开差距的不只是请求和解析，而是数据到了本地之后，你用什么姿势把它留住、管好、再查出来。

这就是为什么我特别想把第五章第三节的内容展开聊聊：MySQL/PostgreSQL 入门，表设计、索引、Upsert 思想。这几个词听着像数据库课本里的名词，实际上是用在爬虫链路里最锋利的刀。这篇文章不打算给你一本字典，而是把我在实际项目里怎么选库、怎么建表、怎么用一条 SQL 把“有则更新、无则插入”落到实处，全部捋一遍。适合刚学完 requests 和 BeautifulSoup、准备认真做数据持久化的朋友，也适合把数据库只用成“能存就行”的老油条回来查漏补缺。

1. 数据落库前的第一关：MySQL 还是 PostgreSQL

1.1 两种数据库的定位差异

先别急着搜“mysql安装教程”或者“postgresql下载哪个版本”，先把选型问题想清楚，因为换库的成本远高于安装成本。MySQL 和 PostgreSQL 都是主流开源关系型数据库，但在爬虫场景下的性格差异非常明显。

MySQL 胜在“轻、快、普及”。绝大多数云服务器默认带 MySQL，文档多、招人容易、出了问题一搜就是十万条答案。它更像一辆皮卡，皮实耐用，维护成本低。PostgreSQL 则像一台多功能工程车，功能密度极高：更严谨的类型系统、原生 JSON 支持、强大的窗口函数、协作者级别的并发控制。爬虫数据里最常见的场景——同一批数据反复抓取、需要做去重和更新——PostgreSQL 的ON CONFLICT语法写起来比 MySQL 的ON DUPLICATE KEY UPDATE更直观，语义也更干净。

我的建议：如果你是自己搭库、数据量不大、希望周边生态最省心，选 MySQL；如果企业里已经有 PG 集群，或者你预感到数据会有大量 JSON 字段、复杂分析查询、地理位置查询这类高级需求，直接上 PostgreSQL，不犹豫。两个库的学习成本大约只差一个周末。

1.2 安装环节最容易踩的坑

装库本身不难，但网上教程良莠不齐，很多人卡在安装这一步就放弃了。MySQL 安装后最常见的问题是服务起不来，Windows 上多半是 my.ini 配置和权限问题，Linux 上则经常遇到error 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'。这个报错八成是 mysqld 没启动，或者 socket 文件路径不一致。先systemctl status mysql看状态，再用netstat -lnt确认 3306 端口是否监听，基本能解决九成问题。

PostgreSQL 在 Windows 上安装后容易遇到服务无法启动，十次里有八次是 data 目录权限问题，或者安装时选的 locale 与系统不一致。记住了：安装到后面那一步让你选 locale 时，别图新鲜选奇怪的语言，直接默认或者选C，省掉后面一堆乱码烦恼。macOS 用户用brew install postgresql@16装完要记得brew services start postgresql@16，不然 psql 永远提示 connection refused。

提示：装库不是攻坚重点，别在安装上耗超过半天。你装的目的是快点开始练表设计和 SQL，不是在环境配置上修炼成专家。

2. 表设计：字段想清楚，后面少受十倍的罪

2.1 从“存得下”到“查得顺”

第一次建表的人都容易犯同一个病：把所有字段塞进一个大宽表里，能放就放，需要时再拆。我早期做一个商品爬虫，把标题、价格、促销文案、卖家信息全部塞在同一张表，结果促销字段每天变、卖家信息经常更新，每次保存数据都要面对一堆 NULL 值，查询也越写越别扭。

爬虫数据的表设计第一原则是“按更新频率拆”。更新的字段和数据主体分开：核心属性（标题、链接、唯一标识）放主表，变化频繁的内容（价格、库存、状态）放子表或者用时间戳记录快照。这样你查历史价格变动时不用翻冗长的更新日志，直接查价格快照表就行。第二原则是“字段类型宁严勿宽”，价格不要用FLOAT存，用DECIMAL(10,2)，避免浮点误差；URL 不要设成VARCHAR(50)，很多真实链接超过这个长度，到时候数据插不进去才知道痛。

2.2 字符集、排序规则与主键策略

MySQL 建表时明确指定字符集，这句话我强调多少次都不嫌多。使用utf8mb4而不是utf8，因为utf8在 MySQL 里最多只支持三个字节，遇到 emoji 或生僻字直接报错，utf8mb4才真正覆盖完整的 Unicode。PostgreSQL 这边通常用UTF8，但注意不同库的排序规则（collation）会影响中文排序和查询性能，默认的就行，不要乱改。

主键选择是另一个容易后悔的决策。爬虫数据往往有天然的业务唯一键，比如商品 ID、文章 ID、用户 ID，但我不建议直接拿它当主键，而是用一个自增整数或 UUID 作为代理主键，业务唯一键单独加唯一索引。这么做的原因是业务键可能因为上游改规则而变动，代理主键不随业务变，外键引用也更稳。PostgreSQL 里我习惯用BIGINT GENERATED ALWAYS AS IDENTITY，MySQL 就是BIGINT AUTO_INCREMENT，简单可靠。

2.3 顺手建好约束，等于给数据上了保险

约束在爬虫表里不是摆设。唯一约束保障去重底线，外键约束防止孤儿数据，CHECK 约束拦截明显非法的数据。举个例子：你抓一个评分字段，范围是 1 到 5，写一个CHECK (rating BETWEEN 1 AND 5)就能在入库层挡住脏数据，否则你写一万行 if 判断也不一定能挡全。

还有一点要提醒：爬虫有时候抓回来的字段是空字符串''而不是NULL，这两个在 SQL 里行为完全不同，空字符串参与唯一约束不生效，排序和统计也会出现莫名其妙的结果。清洗入库前最好统一规范，要么全转 NULL，要么全转空字符串，别混着来。

3. 索引不是装饰品：新手必须掌握的建索引思路

3.1 索引的本质到底是什么

你把索引理解成书的目录，就抓住了本质。数据库查询数据默认是一页页翻全书（全表扫描），索引则是先翻目录锁定页码，然后直接跳过去取内容。爬虫表一旦数据量过万，没有索引的查询慢到让你怀疑人生也毫不夸张。

但别一听索引有用就疯狂建。索引是额外存储空间，也是写入时的额外维护成本——每次 INSERT 或 UPDATE，数据库都要同步更新索引。爬虫是典型的写多读少场景，你每抓一批数据都要写库，索引数量一旦失控，写入瓶颈立刻出现。所以建索引的正确姿势是：先知道查询长什么样，再决定索引建在哪几列上。

3.2 爬虫场景里最值得建的几种索引

第一种是唯一索引，用来保证业务唯一键不重复，比如UNIQUE KEY uk_item_id (item_id)。这东西既是约束也是索引，查询时还能走索引加速。第二种是等值查询索引，爬虫经常要查“这个商品是否存在”，那么在item_id、url_hash这些列上建单列索引就够了。第三种是组合索引，如果你的查询常以(platform_id, update_time)为条件，把这两列做成组合索引比两个单列索引更高效。

组合索引还有一个叫“最左前缀原则”的坑：索引(a, b)可以加速WHERE a = ?和WHERE a = ? AND b = ?，但单独WHERE b = ?用不上这个索引。新手最容易在这里踩空，以为建了组合索引就万事大吉。你的查询条件顺序设计要跟索引列顺序对齐，否则索引白建。

3.3 实测经验：一张爬虫表的索引配置参考

拿我一直在做的资讯类爬虫举例，表结构大致像这样：

CREATE TABLE article ( id BIGSERIAL PRIMARY KEY, article_id VARCHAR(128) NOT NULL, source VARCHAR(64) NOT NULL, title TEXT NOT NULL, url TEXT NOT NULL, url_hash CHAR(64) NOT NULL, publish_time TIMESTAMP, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_article_source_id UNIQUE (source, article_id), CONSTRAINT uk_article_url_hash UNIQUE (url_hash) );

这里用了两个唯一索引：一个在(source, article_id)上，配合业务来源标识做全局唯一；一个在url_hash上，用于快速判断链接是否已抓过。url_hash是我特意加的字段，用 SHA-256 哈希 URL，避免直接用超长 TEXT 做索引导致索引膨胀。实际跑了三个月，数据量六百万行，按source + publish_time查询统计时毫秒级返回，全靠组合索引撑住。

注意：千万、千万、千万不要对所有字段都加索引。我见过有人给一张 20 列的表建了 15 个索引，最后每次插入耗时从 2 毫秒涨到 80 毫秒，完全得不偿失。索引是给查询准备的，不是给建表仪式感准备的。

4. Upsert 思想：你以为的插入，其实大部分是更新

4.1 爬虫为什么离不开 Upsert

“Upsert”是UPDATE和INSERT的合成词，核心语义就一句话：有则更新，无则插入。这听起来简单，却是爬虫数据落库最重要的思想，没有之一。

想想看你的爬虫每天都在干什么：同一个商品、同一篇文章、同一条公告，会反复被抓取。今天价格 100，明天变 120，后天又变 90。如果用普通 INSERT，每次保存都会产生新记录，数据冗余与主键冲突齐飞；如果先查再判断再更新，查询就要多走一遍，还要处理并发中间态的脏数据。Upsert 就是为这个场景量身定做的原子操作，它把“检查记录是否存在”和“插入或更新”合并成一步，数据库内部保证原子性，哪怕两个爬虫进程同时写同一条数据也不会出乱子。

4.2 MySQL 里的写法与背后逻辑

MySQL 的 Upsert 语法是INSERT ... ON DUPLICATE KEY UPDATE，实现逻辑基于“遇到唯一键或主键冲突就改更新”。我实际项目里经常这么用：

INSERT INTO product (product_id, name, price, detail, update_time) VALUES ('P1001', '机械键盘', 299.00, '青轴版', NOW()) ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price), detail = VALUES(detail), update_time = NOW();

稍微解释一下执行过程：INSERT 先尝试插入记录，如果product_id撞了唯一索引，数据库就不再报错，而是执行后面的 UPDATE 子句，把新抓到的内容覆盖进去。注意VALUES()函数在这里表示“当前这条 INSERT 试图写入的值”，在新的 MySQL 8.0.20 以上版本可能提示废弃，官方推荐改用别名写法，不过大多数生产环境里两种写法都能正常工作。

4.3 PostgreSQL 里的写法与真正的 Upsert 姿势

PostgreSQL 的 Upsert 是INSERT ... ON CONFLICT DO UPDATE，语义比 MySQL 更清晰，因为它可以明确指定冲突目标。看例子：

INSERT INTO product (product_id, name, price, detail, update_time) VALUES ('P1001', '机械键盘', 299.00, '青轴版', NOW()) ON CONFLICT (product_id) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price, detail = EXCLUDED.detail, update_time = EXCLUDED.update_time;

这里的EXCLUDED是一个虚拟行，代表“本应该插入但因冲突被挡下来的新数据”。这个写法最大的好处是可以给ON CONFLICT指定具体的唯一约束或索引，写起来完全显式化：冲突在哪儿、怎么处理、更新哪几列，一眼就看明白。如果业务上只想忽略冲突而不更新，也可以写成ON CONFLICT DO NOTHING，在去重场景里尤其好用，比如你只需要确认“这条链接我抓过”，不需要更新任何字段。

4.4 Upsert 不是银弹：什么时候不应该用它

Upsert 这么好，但有一种典型场景不建议用：想保留每次抓取的历史明细时。如果你需要对价格变化做分析、出趋势图，Upsert 会把旧数据直接覆盖掉，历史就丢了——这时候正确的做法是普通 INSERT 一张流水表，再配一条 UPDATE 主表当前价。所以设计时先问自己一句：这列数据是“当前状态”还是“历史事件”？当前状态用 Upsert，历史事件用 INSERT，两者不要混。

5. 实操过程：从建库到用 Python 跑通数据入库

5.1 用哪套方案连接数据库

Python 里操作 MySQL 和 PostgreSQL 的方案非常多，我建议别直接裸用mysql-connector-python或psycopg2写 SQL，而是用 SQLAlchemy 做统一抽象。原因很简单：SQLAlchemy 屏蔽了数据库方言差异，还能提供连接池。爬虫写入频繁，连接池是刚需中的刚需，否则每次插入都新建连接，光握手延迟都够拖垮你。很多新手搜“mysql的数据库连接池”其实就是想解决这个问题，SQLAlchemy 的create_engine自带连接池，不用额外搞复杂的配置。

5.2 一份可以直接抄的入库框架

下面这个例子是我综合多个爬虫项目后整理的最小框架，用 SQLAlchemy 连接 PostgreSQL，核心逻辑和 MySQL 只差一个连接串和方言：

from sqlalchemy import create_engine, text engine = create_engine( "postgresql+psycopg2://user:password@localhost:5432/spider_db", pool_size=10, max_overflow=20, pool_pre_ping=True, pool_recycle=1800 ) def upsert_article(session, record: dict): sql = text(""" INSERT INTO article (article_id, source, title, url, url_hash, update_time) VALUES (:article_id, :source, :title, :url, :url_hash, NOW()) ON CONFLICT (source, article_id) DO UPDATE SET title = EXCLUDED.title, url = EXCLUDED.url, url_hash = EXCLUDED.url_hash, update_time = EXCLUDED.update_time; """) session.execute(sql, record) session.commit() # 使用示例 with engine.begin() as conn: upsert_article(conn, { "article_id": "12345", "source": "tech_site", "title": "标题", "url": "https://example.com/12345", "url_hash": "a" * 64, })

有几个点值得展开讲。pool_size=10, max_overflow=20意思是核心连接 10 个，峰值允许膨胀到 30 个；pool_pre_ping=True会在每次连接使用前自动 ping 一下，防止 MySQL 或 PG 的空闲连接超时被杀；pool_recycle=1800是半小时回收一次连接，避开数据库端 wait_timeout 的默认值。这些参数我全部是在生产环境实测排坑后加的，一个都不能省。

5.3 进阶：让写入速度起飞

如果数据量特别大，逐条 Upsert 是不够的，要改成批量提交。SQLAlchemy 里用session.bulk_insert_mappings或者直接用 PostgreSQL 的COPY命令能快一个数量级。粗略测过：单条 INSERT 一万条数据大约需要几十秒，批量合并成每条事务只提交一次，能压到一两秒，差异是数量级的。核心原理很简单——每条 commit 都涉及一次磁盘同步，批量提交只需要一次。

我自己的做法是：爬虫解析完一批结果后，先放进 Python 内存列表，凑够 500 条或 1000 条再一次性批量 Upsert。这样既减少了事务开销，也能在内存里先做一轮去重清洗，减少无效 SQL。

6. 常见问题与排查技巧实录

6.1 连接数据库时疯狂报错，怎么办

列一个我亲手踩过、也帮别人解决过的速查表：

排错的时候还有个顺手的小技巧：先把 SQL 复制到 Navicat、DataGrip 这类 GUI 工具里手动执行一遍。如果 GUI 能跑通而 Python 报错，问题大概率在连接参数或事务提交逻辑上，别对着代码瞎改。

6.2 Python 侧常见的几个低智错误

第一个是忘记 commit。SQLAlchemy 默认是事务包裹的，你session.execute()之后不session.commit()，数据不会真正落库。我见过太多新手在数据库里看不到新数据，到处怀疑配置，结果就是少写了一行 commit。

第二个错误是用session.add_all()存对象时，没有处理好唯一约束冲突。批量插入 1000 条，里面有 3 条重复，整体事务回滚，前面 997 条也白插了。这种情况应该在批量操作前做一次去重，或者改用insert ... on_conflict_do_nothing实现“部分失败不拖垮整体”。

第三个错误是想当然地认为连接串里的密码包含特殊字符没问题。密码里带@或#时，必须在连接串里做 URL 编码，否则 SQLAlchemy 解析连接串直接报错。写代码前用urllib.parse.quote_plus(password)转一下，省一小时排查时间。

6.3 性能崩了：数据量上来之后的排雷方向

爬虫数据到几十万行以后，你会发现原本“还挺快”的查询渐渐卡顿。先看执行计划，MySQL 用EXPLAIN SELECT ...，PostgreSQL 用EXPLAIN ANALYZE，重点看有没有Seq Scan（全表扫描）和是否命中索引。如果查询条件列没有索引，赶紧补上；如果走索引了还是很慢，检查是不是被%LIKE%这种无法用索引的前缀模糊查询卡住，这时要么改用全文检索，要么加专门的搜索字段。

另一个隐藏雷区是表膨胀。PostgreSQL 的 MVCC 机制会导致频繁更新后表文件不收缩，表空间越来越大但实际有效数据占比低。解决方法是定期执行VACUUM ANALYZE，或者用 autovacuum 配置调优。MySQL 的 InnoDB 也有碎片问题，OPTIMIZE TABLE可以整理回收空间。爬虫表更新的频率高，这两个维护动作建议写成定时任务。

写在后面：数据入库只是起点，不是终点

从 CSV 切到 MySQL/PostgreSQL，最直观的感受不是“能存了”，而是“能查了”。我实际动手做第一个正经爬虫项目时，花了好几天才真正理解ON CONFLICT的底层逻辑，也因为这个走了不少弯路。每次抓取的 attr 数据、价格快照、状态变化，我都记录在案，事后分析用户行为、价格波动时才有的放矢。

如果你现在正卡在“数据存下来了但不会查”“不会更新”这个节点上，别焦虑，把上面这些步骤拆开练：今天建表，明天练索引，后天写一个 Upseert 入库函数，最后一口气跑通全流程。等你习惯了用数据库管理爬虫数据，再回头看 CSV 时代，你会由衷觉得，这才是数据该有的样子。

下一节我会继续聊查询优化的深度实践，包括怎么用窗口函数做去重和分组统计、怎么把爬虫采集和数据库写入拆成生产消费者模式。这篇先把落库的地基打牢，后面才飞得起来。