Pyodbc查询MariaDB亿级大表execute耗时过长求助

问题根源 #

你遇到的execute耗时极久的核心原因是：pyodbc默认使用客户端游标，执行查询时会尝试将整个结果集一次性拉取到本地内存，1亿行数据的体量直接导致这个过程耗时巨大，完全违背了“迭代时逐行获取”的预期。

针对性解决方案 #

1. 启用服务器端游标，实现流式读取 #

通过指定服务器端向前游标（SQL_CURSOR_FORWARD_ONLY），强制驱动在迭代时才从服务器分批获取数据，避免一次性加载全量数据。同时设置arraysize调整每次拉取的行数，平衡网络开销和内存占用：

import pyodbc

# 连接MariaDB
cnxn = pyodbc.connect(
    'DRIVER=/usr/lib/libmaodbc.so;'
    'socket=/var/run/mysqld/mysqld.sock;'
    'Database=101m;'
    'User=root;'
    'Password=123;'
    'Option=3;'
)
# 创建服务器端向前游标，禁止客户端缓存全量结果
cursor = cnxn.cursor(pyodbc.SQL_CURSOR_FORWARD_ONLY)
# 设置每次从服务器拉取的行数，建议根据内存调整（比如10000）
cursor.arraysize = 10000

# 执行查询，此时不会立即拉取全量数据
cursor.execute("SELECT * FROM vat")

# 迭代处理数据（替换为写入SQLite的逻辑，不要用print拖慢速度）
for row in cursor:
    # 这里写SQLite插入操作
    pass

cnxn.close()

2. 针对SQLite迁移的批量优化 #

直接逐行插入SQLite会产生巨大的事务开销，结合分批次读取+批量插入能大幅提升迁移效率：

import pyodbc
import sqlite3

# 配置参数
BATCH_SIZE = 10000
MARIADB_CONN_STR = (
    'DRIVER=/usr/lib/libmaodbc.so;'
    'socket=/var/run/mysqld/mysqld.sock;'
    'Database=101m;'
    'User=root;'
    'Password=123;'
    'Option=3;'
)
SQLITE_DB_PATH = 'target.db'

# 初始化MariaDB连接和游标
maria_cnxn = pyodbc.connect(MARIADB_CONN_STR)
maria_cursor = maria_cnxn.cursor(pyodbc.SQL_CURSOR_FORWARD_ONLY)
maria_cursor.arraysize = BATCH_SIZE

# 初始化SQLite连接，启用WAL模式提升写入性能
sqlite_cnxn = sqlite3.connect(SQLITE_DB_PATH)
sqlite_cursor = sqlite_cnxn.cursor()
# 先确保目标表结构与源表一致（自行替换为实际表结构）
# sqlite_cursor.execute("""
# CREATE TABLE IF NOT EXISTS vat (
#     id INT PRIMARY KEY,
#     col1 VARCHAR(255),
#     col2 INT,
#     ... -- 其他字段
# )
# """)

# 按主键分页读取（假设源表有自增主键`id`，避免OFFSET的性能问题）
last_id = 0
while True:
    # 分页查询数据
    maria_cursor.execute(
        "SELECT * FROM vat WHERE id > ? ORDER BY id LIMIT ?",
        (last_id, BATCH_SIZE)
    )
    rows = maria_cursor.fetchall()
    
    if not rows:
        break  # 数据处理完毕
    
    # 批量插入SQLite
    # 注意：占位符数量要与字段数一致，比如有5个字段就用(?,?,?,?,?)
    sqlite_cursor.executemany(
        "INSERT INTO vat VALUES (?,?,?,?,?)",
        rows
    )
    sqlite_cnxn.commit()  # 手动提交事务
    
    # 更新最后一条数据的id，用于下一页查询
    last_id = rows[-1][0]

# 关闭连接
maria_cnxn.close()
sqlite_cnxn.close()

额外注意事项 #

• 绝对不要用print输出1亿行数据，同步IO会严重拖慢处理速度；
• 如果源表没有自增主键，可以用其他有序字段（如时间戳）进行分页，避免使用OFFSET（大偏移量会导致MariaDB全表扫描）；
• 调整BATCH_SIZE时，根据服务器内存和网络情况平衡，过大可能导致内存溢出，过小会增加网络交互次数。

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