数十亿记录快速插入与即时检索方案咨询：子父文件关联查询

数据结构说明 #

• 存在一个包含100万行的「祖父文件」，每行是唯一的父文件绝对路径，示例：

%: cat input.txt - grand parent file
/root/a/b/c/1.txt  -- parent file1
/root/a/b/c/2.txt  -- parent file2 ......
...
/root/a/b/d/3.txt
......
.....
upto 1 million files.

• 每个父文件内记录子文件的绝对路径及对应行号，同一子文件可能出现在多个父文件中，行号可相同或不同，示例：

%: cat /root/a/b/c/1.txt -- parent file
s1.c,1,2,3,4,5 -- child file and its line numbers
s2.c,1,2,3,4,5....
... 
upto thousands of files

%: cat /root/a/b/c/2.txt
s1.c,3,4,5
s2.c,1,2,3,4,5....
... 
upto thousands of files

核心需求 #

• 查询：给定子文件名和行号，需在1分钟内返回所有包含该记录的父文件
• 插入：所有关联记录需在1天内完成导入

关系型数据表结构设计 #

设计了ParentChildMapping表存储关联关系：

ParentChildMapping - Contains the required relation
    ID AUTOINCREMENT PRIMARY KEY
    ParentFileName TEXT
    ChildFileName TEXT
    LNumber INT

查询语句：

SELECT ParentFileName from ParentChildMapping where ChildFileName="s1.txt" and LNumber=1;

插入方案与遇到的问题 #

将祖父文件拆分为1000个分组（每组1000条父文件路径），计划用Python启动1000个进程并行解析父文件并插入数据库，但遇到以下问题：

• 尝试SQLite时，因不支持并发插入出现数据库锁错误，考虑改用MySQL
• 不确定百万级数据并行插入对MySQL的性能影响
• 不确定关系型数据库是否为当前场景的最优选择

给出的MySQL并行插入示例代码：

import MySQLDb
connection = MySQLDb.connect(host, username,...)
cursor = connection.cursor()
with open(some_set) as fd:
    for each_parent_file in fd:
        with open(each_parent_file) as parent_fd:
            for each_line in parent_fd:
                child_file_name, *line_numbers = each_line.strip().split(",") 
                insert_items = [(each_parent_file, child_file_name, line_num) for line_num in line_numbers]
                cursor.executemany("INSERT INTO ParentChildMapping (ParentFileName, ChildFileName, LineNumber) VALUES %s" %insert_items)
    cursor.commit()
cursor.close()
connection.close()

一、MySQL并行插入的优化方向 #

1. 数据库配置调整 #

• 增大innodb_buffer_pool_size（建议设为服务器内存的50%-70%），提升缓存命中率
• 关闭autocommit，采用批量提交（示例已实现，但建议每处理50-100个父文件提交一次，平衡提交开销）
• 调大innodb_log_file_size和innodb_log_buffer_size，减少日志刷盘频率
• 若业务允许，设置innodb_flush_log_at_trx_commit=2，牺牲部分持久性换取写入性能

2. 插入逻辑优化 #

• 合并批量插入数据：将多个父文件的插入条目合并为更大的批量，减少executemany的调用次数
• 控制进程数量：1000个进程会耗尽系统资源（CPU、文件句柄、数据库连接），建议根据服务器CPU核心数设置为2-4倍（如32核服务器设为64-128个进程）
• 移除无用列：ID自增主键对查询无意义，会增加写入开销，可改用(ChildFileName, LNumber, ParentFileName)作为复合主键，同时满足唯一性约束

3. 索引优化 #

• 必须创建复合索引(ChildFileName, LNumber)，这是核心查询的条件，能让查询直接命中索引，大幅提升响应速度
• 无需单独为ParentFileName建索引，除非有反向查询需求

二、替代技术方案评估 #

1. Elasticsearch #

• 适合多维精准查询，将ChildFileName和LNumber作为关键字段，ParentFileName作为存储字段
• 支持批量并行写入，分布式架构可水平扩展，查询响应速度快，完全满足1分钟内返回结果的需求
• 劣势：运维成本略高于关系型数据库

2. ClickHouse（列式数据库） #

• 针对大规模数据写入和分析查询优化，写入吞吐量极高，适合百万级数据导入
• 将ChildFileName和LNumber作为查询维度，ParentFileName作为聚合字段，查询时能快速返回结果
• 优势：写入和查询性能都远超传统关系型数据库，适合此类批量导入+精准查询的场景

3. Redis（键值存储） #

• 若查询模式固定为(ChildFileName, LNumber)→父文件列表，可将组合键作为Redis的Key，父文件列表存入集合或字符串
• 插入时直接往对应Key的集合中添加父文件名，查询时直接读取集合即可，响应速度可达毫秒级
• 劣势：若部分组合键对应上万父文件，会占用大量内存，需评估服务器内存容量是否足够

三、通用代码优化 #

• 增加异常捕获：单个进程崩溃时不影响整体任务，可记录失败的父文件路径后续重试
• 预读取父文件：采用批量读取或异步IO减少磁盘IO等待时间，提升解析速度

内容的提问来源于stack exchange，提问作者SH47