简介这是一份面向餐饮类应用开发者、数据分析学习者与菜谱网站搭建者的MySQL菜谱数据库资源可用于美食推荐系统、菜谱检索平台或数据可视化练习等场景。压缩包共4个文件以3个sql脚本和1个txt说明为主整体约52.48MB其中sql文件分别承载目录、菜谱及目录关联菜谱三类数据说明文件则用于辅助理解表间关系与导入方式。数据规模达13万条菜谱记录并配套36G图片资源图片地址与提取码已在描述中给出方便按需下载后与数据库记录对应使用。三张表通过目录与菜谱的关联设计便于实现分类浏览、关键词检索与多级目录展示等常见功能。目前已有938人学习下载适合需要真实、成规模菜谱数据来练手或搭建原型的开发者参考使用。1. 菜谱数据落地 MySQL13 万条文本加 36G 图片这套库到底怎么建拿到「菜谱食谱数据-mysql【13万条数据36G图片】」这个标题第一反应不是急着建表而是先算一笔账13 万条菜谱文本配上 36G 图片这根本不是一个「导入 SQL 文件就完事」的活。文本部分撑死几百兆图片才是真正吃资源的大头。如果你打算把这套数据落到 MySQL 里做本地菜谱检索、推荐系统训练或者小程序后端那核心矛盾从来不是「怎么把数据塞进去」而是「图片存哪、文本怎么建索引、查询怎么不拖垮数据库」。这套数据适合做垂直领域搜索、食谱推荐、营养分析类项目的从业者也适合想拿真实中文数据集练 MySQL 性能调优的人。下面按我实际处理这类图文混合数据集的路径把选型、建库、导入、查询优化和踩坑一次讲透。2. 图文混合菜谱库的表结构设计与存储选型2.1 为什么图片不能直接塞进 MySQL 的 BLOB36G 图片如果走LONGBLOB存进 MySQL你会遇到三个硬墙。第一单表体积爆炸后mysqldump备份基本不可用导出一次几个小时起步。第二InnoDB 的 buffer pool 会被图片二进制流冲垮文本查询的缓存命中率断崖下跌。第三主从复制时 binlog 体积失控网络稍抖就延迟。常见做法是文本进 MySQL、图片走文件系统或对象存储数据库里只存路径或 URL。我一般会在表里留一个image_path字段存相对路径如/data/recipes/img/001/23456.jpg应用层拼前缀。这样单条菜谱记录控制在 2KB 以内13 万条文本表体积大约 300MB 上下随便一台 2C4G 的机器都能扛。如果你确实需要图片和文本强一致比如删除菜谱必须删图那就在应用层做事务补偿而不是把图片塞进数据库。MySQL 擅长的是结构化查询和事务不是当文件服务器用。2.2 菜谱主表、食材表、步骤表的拆分逻辑一份菜谱天然是一对多结构一个菜谱有多个食材、多个步骤、可能多张图。如果全塞一张宽表食材字段用逗号拼接后面想按「含牛肉且不含香菜」筛选时只能LIKE %牛肉%索引直接失效。正确做法是三张核心表加一张图片表-- 菜谱主表存标题、简介、分类、烹饪时长等标量属性 CREATE TABLE recipe ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(200) NOT NULL COMMENT 菜谱名称, category VARCHAR(50) DEFAULT NULL COMMENT 菜系/分类, cook_time INT DEFAULT 0 COMMENT 烹饪时长(分钟), difficulty TINYINT DEFAULT 0 COMMENT 难度1-5, description TEXT COMMENT 简介, cover_image VARCHAR(255) DEFAULT NULL COMMENT 封面图相对路径, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_category (category), KEY idx_title (title(50)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 食材表一条菜谱对应多行食材便于精确筛选 CREATE TABLE recipe_ingredient ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, recipe_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL COMMENT 食材名, amount VARCHAR(50) DEFAULT NULL COMMENT 用量描述, PRIMARY KEY (id), KEY idx_recipe (recipe_id), KEY idx_name (name), CONSTRAINT fk_ing_recipe FOREIGN KEY (recipe_id) REFERENCES recipe(id) ON DELETE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 步骤表保持顺序用 step_no 排序 CREATE TABLE recipe_step ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, recipe_id BIGINT UNSIGNED NOT NULL, step_no SMALLINT NOT NULL COMMENT 步骤序号, content TEXT NOT NULL COMMENT 步骤描述, image_path VARCHAR(255) DEFAULT NULL COMMENT 该步骤配图, PRIMARY KEY (id), UNIQUE KEY uk_recipe_step (recipe_id, step_no), CONSTRAINT fk_step_recipe FOREIGN KEY (recipe_id) REFERENCES recipe(id) ON DELETE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;字符集必须用utf8mb4菜谱里出现生僻字或 emoji 的概率不低utf8三字节版本会直接报错。食材表的idx_name是后面做「按食材搜菜谱」的关键没有这个索引13 万条数据下按食材名查询会走全表扫描。步骤表的uk_recipe_step唯一键既保证顺序不重复也顺带成了查询某菜谱步骤的覆盖索引。2.3 图片目录的分片规则与路径映射36G 图片如果全放一个目录ls都能卡死文件系统 inode 压力也大。我一般按菜谱 ID 取模分片比如每 1000 个 ID 一个子目录# 假设图片原始命名是 recipe_id_step.jpg按 ID 区间归入子目录 for f in /data/raw_img/*.jpg; do base$(basename $f) rid$(echo $base | cut -d_ -f1) sub$((rid / 1000)) mkdir -p /data/recipes/img/$sub mv $f /data/recipes/img/$sub/ done这段脚本把扁平目录拆成img/0/、img/1/这样的层级每个目录最多 1000 张图单目录文件数可控。数据库里image_path存0/23456.jpg这种相对路径应用层用IMG_ROOT / image_path拼出绝对路径。分片数按总量算13 万条菜谱假设平均 3 张图约 40 万张除以 1000 就是 400 个目录完全在文件系统舒适区。如果你用对象存储分片规则可以换成按日期或哈希前缀逻辑一样。3. 13 万条菜谱数据导入 MySQL 的完整操作路径3.1 从 CSV 或 JSON 到 LOAD DATA 的预处理原始数据大概率是 CSV 或 JSON 格式。CSV 导入最快但菜谱步骤里常有换行和逗号直接LOAD DATA会错行。我一般先用 Python 把 JSON 或脏 CSV 清洗成制表符分隔的干净文件import json import csv # 读取原始 JSON 菜谱数据输出三份 TSV 供 LOAD DATA 使用 with open(recipes_raw.json, r, encodingutf-8) as f: data json.load(f) with open(recipe.tsv, w, newline, encodingutf-8) as fr, \ open(ingredient.tsv, w, newline, encodingutf-8) as fi, \ open(step.tsv, w, newline, encodingutf-8) as fs: w_recipe csv.writer(fr, delimiter\t) w_ing csv.writer(fi, delimiter\t) w_step csv.writer(fs, delimiter\t) for r in data: # 制表符和换行在字段内必须转义否则 LOAD DATA 会错行 title r[title].replace(\t, ).replace(\n, ) desc r.get(description, ).replace(\t, ).replace(\n, ) w_recipe.writerow([r[id], title, r.get(category, ), r.get(cook_time, 0), r.get(difficulty, 0), desc, r.get(cover_image, )]) for ing in r.get(ingredients, []): w_ing.writerow([r[id], ing[name], ing.get(amount, )]) for i, step in enumerate(r.get(steps, []), start1): content step[content].replace(\t, ).replace(\n, ) w_step.writerow([r[id], i, content, step.get(image, )])关键点在字段内的\t和\n必须替换成空格否则LOAD DATA会把一条记录拆成多行。recipe_id在食材和步骤文件里重复出现导入时不需要主键靠外键关联。清洗完先wc -l看行数13 万条菜谱主表应该正好 13 万行加表头 13 万零 1食材和步骤行数会多几倍这是正常的。3.2 LOAD DATA LOCAL INFILE 的参数配置与提速导入时关掉自动提交和唯一性检查能快很多但要注意这是在空表首次导入时才能用的激进手段-- 导入前临时关闭约束检查仅限首次全量导入使用 SET unique_checks 0; SET foreign_key_checks 0; SET autocommit 0; LOAD DATA LOCAL INFILE /data/recipe.tsv INTO TABLE recipe FIELDS TERMINATED BY \t LINES TERMINATED BY \n IGNORE 1 LINES (id, title, category, cook_time, difficulty, description, cover_image); LOAD DATA LOCAL INFILE /data/ingredient.tsv INTO TABLE recipe_ingredient FIELDS TERMINATED BY \t LINES TERMINATED BY \n (recipe_id, name, amount); LOAD DATA LOCAL INFILE /data/step.tsv INTO TABLE recipe_step FIELDS TERMINATED BY \t LINES TERMINATED BY \n (recipe_id, step_no, content, image_path); COMMIT; SET unique_checks 1; SET foreign_key_checks 1;LOCAL关键字要求客户端和local_infile参数都开启MySQL 8.0 默认是关的需要SET GLOBAL local_infile 1;。如果报ERROR 3948就是这个参数没开。导入 13 万条主表加几十万条从表机械盘大约 2 到 3 分钟SSD 上一分钟内能完。导入完记得ANALYZE TABLE recipe, recipe_ingredient, recipe_step;更新统计信息否则优化器可能选错执行计划。3.3 导入后的一致性校验行数、外键、图片路径导入完不能直接上线先跑三条校验。第一行数比对SELECT COUNT(*) FROM recipe;应该等于源文件行数。第二孤儿记录检查SELECT COUNT(*) FROM recipe_ingredient i LEFT JOIN recipe r ON i.recipe_id r.id WHERE r.id IS NULL;结果必须是 0否则说明有食材指向了不存在的菜谱。第三图片路径抽查随机取 100 条有图的记录在文件系统里test -f验证路径真实存在。这三步做完数据才算真正可用。我见过太多人导入完看行数对了就收工结果上线后发现图片 404 一片回头查是路径前缀拼错了。4. 菜谱检索场景下的索引与查询优化4.1 按食材搜菜谱从 LIKE 到覆盖索引最常见的查询是「我有牛肉和土豆能做什么菜」。如果写成WHERE name LIKE %牛肉%索引直接废掉。正确做法是食材名精确匹配加IN查询再按菜谱分组统计命中食材数-- 查找同时包含牛肉和土豆的菜谱按命中食材数排序 SELECT r.id, r.title, COUNT(*) AS match_cnt FROM recipe r JOIN recipe_ingredient i ON r.id i.recipe_id WHERE i.name IN (牛肉, 土豆) GROUP BY r.id, r.title HAVING match_cnt 2 ORDER BY r.id LIMIT 20;idx_name让IN走索引范围扫描idx_recipe让 join 回表快。如果食材名有别名比如「土豆」和「马铃薯」需要在应用层做同义词映射或者单独建一张同义词表查询时先展开同义词再拼IN列表。13 万条数据下这个查询响应在几十毫秒级前提是innodb_buffer_pool_size至少给到 1G让食材表常驻内存。4.2 全文索引处理菜谱标题和步骤描述标题和步骤描述的关键词搜索用LIKE %红烧%同样低效。MySQL 5.7 以后支持ngram全文索引对中文分词够用-- 给标题和步骤内容加中文全文索引 ALTER TABLE recipe ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram; ALTER TABLE recipe_step ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram; -- 按标题关键词搜索自然语言模式 SELECT id, title, MATCH(title) AGAINST(红烧 排骨 IN NATURAL LANGUAGE MODE) AS score FROM recipe WHERE MATCH(title) AGAINST(红烧 排骨 IN NATURAL LANGUAGE MODE) ORDER BY score DESC LIMIT 20;ngram默认 token size 是 2也就是按二元组切分「红烧排骨」会切成「红烧」「烧排」「排骨」。搜「排骨」能命中搜「红烧排骨」整体也能命中。注意全文索引的维护成本每次插入或更新都会重建索引片段批量导入时应该先导数据再建全文索引顺序反了导入速度会慢好几倍。4.3 分页查询的延迟关联写法菜谱列表分页如果直接LIMIT 100000, 20MySQL 会扫描前 10 万行再丢弃越翻越慢。延迟关联的写法是先走覆盖索引拿主键再回表取详情-- 延迟关联子查询只扫索引外层按主键回表 SELECT r.id, r.title, r.category, r.cover_image FROM recipe r JOIN ( SELECT id FROM recipe WHERE category 川菜 ORDER BY id LIMIT 100000, 20 ) AS t ON r.id t.id;子查询里的SELECT id走idx_category覆盖索引不需要回表读 title 等大字段扫描 10 万行索引比扫描 10 万行完整记录快一个数量级。外层只回表 20 次。这个写法在 13 万条数据下深分页也能控制在百毫秒内。如果业务允许更好的方案是用游标分页记住上一页最后一个 id彻底避开OFFSET。5. 菜谱库运维避坑从连接报错到图片丢失的排查记录5.1 现象导入到一半报 ERROR 2002 连接中断原因通常是max_allowed_packet太小单条步骤描述过长时被服务端拒绝或者客户端超时断开。解决导入前SET GLOBAL max_allowed_packet 64 * 1024 * 1024;同时把net_read_timeout和net_write_timeout调到 600 秒。如果是 socket 连接报ERROR 2002检查/tmp/mysql.sock是否存在my.cnf里socket路径是否和客户端一致。5.2 现象图片路径在数据库里对但应用读不到原因多半是相对路径的前缀拼接逻辑在开发和生产环境不一致或者分片目录权限不对。解决在数据库里统一存相对路径应用层用配置项IMG_ROOT拼绝对路径部署时只改配置不改数据。另外检查 MySQL 运行用户对图片目录有没有读权限chmod 755目录、chmod 644文件是安全底线。5.3 现象按食材查询越来越慢CPU 飙高原因通常是recipe_ingredient表数据量涨到几百万行后idx_name的选择性下降优化器改走全表扫描。解决先EXPLAIN看执行计划如果type是ALL用FORCE INDEX (idx_name)强制走索引或者把食材名做哈希分区。更彻底的做法是建一张食材倒排表把「食材名 → 菜谱 ID 列表」预计算好查询时直接查倒排表。5.4 现象全文索引搜不到短词或单字原因ngram默认ngram_token_size2单字查询无法命中。解决如果业务需要单字搜索把ngram_token_size改成 1 并重建索引但索引体积会翻倍。折中方案是应用层对单字查询走LIKE 字%前缀匹配前缀匹配能走普通 B 树索引比全文索引更合适。5.5 现象主从复制延迟从库数据落后几分钟原因批量导入或大批量更新时 binlog 写入量大从库单线程回放跟不上。解决导入期间临时把binlog_format设为ROW并开binlog_row_imageminimal减少日志体积。如果延迟持续考虑开并行复制slave_parallel_workers4。菜谱数据读多写少主从延迟对查询影响有限但导入窗口期要避开业务高峰。6. 用存储过程做菜谱数据统计与随机推荐菜谱库建好之后最实用的进阶玩法是用存储过程把统计和推荐逻辑下沉到数据库层减少应用层反复查询。比如「按分类统计菜谱数量并计算平均烹饪时长」用一条 SQL 就能出结果但封装成存储过程后应用层调用更干净DELIMITER // CREATE PROCEDURE sp_category_stats() BEGIN -- 按分类聚合输出数量、平均时长、难度分布 SELECT category, COUNT(*) AS recipe_cnt, ROUND(AVG(cook_time), 1) AS avg_cook_time, SUM(CASE WHEN difficulty 2 THEN 1 ELSE 0 END) AS easy_cnt FROM recipe WHERE category IS NOT NULL GROUP BY category ORDER BY recipe_cnt DESC; END // DELIMITER ;调用时CALL sp_category_stats();即可。DELIMITER的作用是把语句结束符临时从;改成//否则存储过程内部的;会被客户端提前截断这是新手最常翻车的地方。另一个实用存储过程是随机推荐ORDER BY RAND() LIMIT 10在 13 万行上会全表扫描加排序性能很差。更好的做法是用主键范围随机-- 随机推荐先取 ID 范围再随机偏移避免 ORDER BY RAND() SELECT r.id, r.title, r.cover_image FROM recipe r JOIN ( SELECT FLOOR(RAND() * (SELECT MAX(id) FROM recipe)) AS rand_id ) t ON r.id t.rand_id ORDER BY r.id LIMIT 10;这个写法利用主键索引做范围扫描只读 10 行左右比ORDER BY RAND()快两个数量级。缺点是 ID 有空洞时分布略不均匀但对推荐场景完全够用。我自己维护这套菜谱库时最大的教训是图片路径的规范一定要在导入前定死中途改路径规则意味着要同时改数据库和文件系统两边对不上的时候排查起来非常痛苦。另一个习惯是每次批量操作前先mysqldump一份结构加数据13 万条文本表 dump 出来也就几百兆几分钟的事但能省掉很多后悔药。希望帮到你。本文还有配套的精品资源点击获取