说实话爬虫做到第十天十个人里有八个会开始思考同一个问题抓下来的数据到底该往哪儿放CSV文件打开乱码、Excel卡到崩溃、重复数据堆成山这些我都经历过。搞到后面你会发现爬虫真正拉开差距的不只是请求和解析而是数据到了本地之后你用什么姿势把它留住、管好、再查出来。这就是为什么我特别想把第五章第三节的内容展开聊聊MySQL/PostgreSQL 入门表设计、索引、Upsert 思想。这几个词听着像数据库课本里的名词实际上是用在爬虫链路里最锋利的刀。这篇文章不打算给你一本字典而是把我在实际项目里怎么选库、怎么建表、怎么用一条 SQL 把“有则更新、无则插入”落到实处全部捋一遍。适合刚学完 requests 和 BeautifulSoup、准备认真做数据持久化的朋友也适合把数据库只用成“能存就行”的老油条回来查漏补缺。1. 数据落库前的第一关MySQL 还是 PostgreSQL1.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): Cant 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 postgresql16装完要记得brew services start postgresql16不然 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 IDENTITYMySQL 就是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( postgresqlpsycopg2://user:passwordlocalhost:5432/spider_db, pool_size10, max_overflow20, pool_pre_pingTrue, pool_recycle1800 ) 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_size10, max_overflow20意思是核心连接 10 个峰值允许膨胀到 30 个pool_pre_pingTrue会在每次连接使用前自动 ping 一下防止 MySQL 或 PG 的空闲连接超时被杀pool_recycle1800是半小时回收一次连接避开数据库端 wait_timeout 的默认值。这些参数我全部是在生产环境实测排坑后加的一个都不能省。5.3 进阶让写入速度起飞如果数据量特别大逐条 Upsert 是不够的要改成批量提交。SQLAlchemy 里用session.bulk_insert_mappings或者直接用 PostgreSQL 的COPY命令能快一个数量级。粗略测过单条 INSERT 一万条数据大约需要几十秒批量合并成每条事务只提交一次能压到一两秒差异是数量级的。核心原理很简单——每条 commit 都涉及一次磁盘同步批量提交只需要一次。我自己的做法是爬虫解析完一批结果后先放进 Python 内存列表凑够 500 条或 1000 条再一次性批量 Upsert。这样既减少了事务开销也能在内存里先做一轮去重清洗减少无效 SQL。6. 常见问题与排查技巧实录6.1 连接数据库时疯狂报错怎么办列一个我亲手踩过、也帮别人解决过的速查表报错场景常见原因排查命令 / 解法MySQL 连不上报Cant connect ... socketmysqld 未启动或 socket 路径不一致systemctl status mysql确认/tmp/mysql.sock是否存在MySQL 连接超时 /Lost connection连接池连接被 DB 端超时回收加pool_pre_pingTrue设置pool_recyclePostgreSQL 连不上报Connection refused服务没启动或 pg_hba.conf 配置不允许远程pg_isready检查监听地址是否 0.0.0.0插入数据报Data too long for columnVARCHAR 长度不够或者字符集宽度超限改用 TEXT 或增大 VARCHAR确认使用 utf8mb4插入 emoji 报错MySQL 字符集不是 utf8mb4建库建表显式指定DEFAULT CHARSETutf8mb4Upsert 不回更新反而一直报主键冲突用了 INSERT 但没用 ON CONFLICT / ON DUPLICATE确认 SQL 中是否携带 Upsert 子句排错的时候还有个顺手的小技巧先把 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 时代你会由衷觉得这才是数据该有的样子。下一节我会继续聊查询优化的深度实践包括怎么用窗口函数做去重和分组统计、怎么把爬虫采集和数据库写入拆成生产消费者模式。这篇先把落库的地基打牢后面才飞得起来。
