优化存储于SQLite数据库中Blob（numpy数组）的操作方法

1. 批量读取+批量反序列化，替代逐条遍历 #

• 放弃逐条处理字典记录的低效方式，一次性拉取所有目标数据，再批量转换Blob为numpy数组，减少循环带来的开销
• 示例代码：

from sqlalchemy import create_engine
import numpy as np
import pandas as pd
import sqlite3

engine = create_engine("sqlite:///your_db.db")

# 用底层cursor批量拉取，降低ORM对象实例化开销
with engine.raw_connection() as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT id, blob_col, shape_col FROM your_table WHERE ...")
    rows = cursor.fetchall()

# 批量反序列化Blob到numpy数组
ids = []
processed_arrays = []
for id_, blob, shape_str in rows:
    ids.append(id_)
    # 解析预存的数组形状（比如shape_str格式为"(100,200)"）
    shape = tuple(map(int, shape_str.strip("()").split(",")))
    # 直接从二进制解析数组，比np.load()快
    arr = np.frombuffer(blob, dtype=np.float64).reshape(shape)
    # 批量执行你的数组操作（示例：归一化）
    processed_arrays.append(arr / arr.max())

# 一次性构建DataFrame，避免循环append的O(n²)开销
df = pd.DataFrame({"id": ids, "processed_array": processed_arrays})

2. 优化Blob的序列化格式 #

• 不要用np.save()生成Blob：该方法会写入numpy文件头，反序列化时额外的解析步骤会拖慢速度
• 改用arr.tobytes()直接存储原始二进制数据，同时在表中新增shape字段（存字符串如"(100,200)"或JSON），反序列化时用np.frombuffer()+reshape()快速还原数组
• 存储示例：

arr = np.random.rand(100, 200)
# 记录数组形状
shape_str = f"({arr.shape[0]}, {arr.shape[1]})"
# 转成原始二进制
blob_data = arr.tobytes()
# 插入数据库
session.add(YourTable(id=1, blob_col=blob_data, shape_col=shape_str))
session.commit()

3. 降低SQLAlchemy与SQLite的IO开销 #

• 仅查询需要的字段：用query(YourTable.id, YourTable.blob_col, YourTable.shape_col)替代全字段查询，减少数据传输量
• 开启SQLite内存映射：在创建引擎时设置mmap_size，让SQLite直接将磁盘文件映射到内存，大幅降低大Blob的IO延迟

engine = create_engine(
    "sqlite:///your_db.db",
    connect_args={
        "mmap_size": 1024 * 1024 * 1024,  # 1GB内存映射，根据可用内存调整
        "detect_types": sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
    }
)

4. 修正分表方案（若仍需尝试） #

• 之前分表性能下降，大概率是逐条关联查询或索引缺失导致的
• 修正方案：
  • 给子表的外键字段建立索引：CREATE INDEX idx_sub_table_main_id ON sub_table(main_id);
  • 用JOIN批量拉取主表+子表数据，避免逐条查询子表：

    SELECT m.id, s.blob_col, s.shape_col 
    FROM main_table m
    JOIN sub_table s ON m.id = s.main_id
    WHERE m.xxx = ...
    

  • 若子表Blob数量极大，可按主表记录分组批量拉取，而非单条查询

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