在Jupyter Notebook中从MySQL高效提取1亿条数据的技术咨询

嘿，兄弟！你选分批提取的思路完全正确——1亿条数据一次性拉取肯定直接把内存干爆，用pymysql分批处理是最稳妥的方向。我帮你把这个方案补全，还加几个关键优化点，让你拉数据更快更稳：

核心优化思路：用主键范围分页替代OFFSET #

用LIMIT ... OFFSET ...在数据量到千万级之后会越来越慢，因为数据库得先扫描前面所有的行再返回结果。换成基于主键的范围查询，效率会高很多，前提是你的表有自增主键（比如id）或者唯一的有序字段。

完整可运行的分批提取代码 #

import pymysql.cursors
import pandas as pd

# 数据库连接配置
db_config = {
    'host': 'XXX',
    'user': 'XXX',
    'password': 'XXX',
    'db': 'XXX',
    'charset': 'utf8mb4',  # 建议用utf8mb4兼容emoji等特殊字符
    'cursorclass': pymysql.cursors.SSCursor  # 流式游标，不一次性加载所有结果到内存
}

# 分批大小，根据你的Jupyter内存调整，建议1w-10w条/批
batch_size = 10000
# 存储所有数据的列表
all_data = []

try:
    # 建立连接，关闭自动提交减少开销
    connection = pymysql.connect(**db_config)
    connection.autocommit(False)
    
    with connection.cursor() as cursor:
        # 先获取主键的最小和最大值，确定查询范围
        cursor.execute("SELECT MIN(id), MAX(id) FROM your_table_name")
        min_id, max_id = cursor.fetchone()
        
        current_id = min_id
        while current_id <= max_id:
            # 用主键范围查询，避免OFFSET的性能问题
            query = """
                SELECT col1, col2, col3  -- 只取需要的字段，别用SELECT *
                FROM your_table_name
                WHERE id >= %s AND id < %s
            """
            cursor.execute(query, (current_id, current_id + batch_size))
            
            # 批量获取当前批次的数据
            batch_data = cursor.fetchall()
            if not batch_data:
                break
            
            # 转成DataFrame（如果需要）或者直接处理
            df_batch = pd.DataFrame(batch_data, columns=['col1', 'col2', 'col3'])
            all_data.append(df_batch)
            
            # 更新下一批的起始主键
            current_id += batch_size
            print(f"已提取 {current_id - min_id} 条数据...")

    # 合并所有批次的数据
    final_df = pd.concat(all_data, ignore_index=True)
    print(f"数据提取完成，总条数：{len(final_df)}")

finally:
    # 确保连接关闭
    if connection:
        connection.close()

几个关键优化点 #

• 流式游标（SSCursor）：默认游标会把整个查询结果加载到内存，SSCursor会逐行获取，内存占用极低，适合超大批量数据
• 关闭自动提交：批量操作时关闭autocommit，减少数据库的提交开销
• 只查询需要的字段：别用SELECT *，只取你需要的列，减少数据传输量和内存占用
• 主键范围分页：相比OFFSET，这种方式利用主键索引，查询速度不会随着数据量增大而下降
• 调整分批大小：如果Jupyter内存足够，可以适当调大batch_size（比如5w或10w），减少循环次数；内存紧张就调小

额外建议 #

如果你的表没有自增主键，可以用其他有序的唯一字段（比如时间戳create_time）来做范围分页，但要确保该字段有索引，否则查询速度还是会慢。

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