如何实现Sqlite存储Apache日志自动压缩且支持只读SQL查询

方案1：开源透明列压缩扩展（优先推荐） #

使用开源免费的sqlite-zstd扩展实现透明压缩，完全兼容现有SELECT查询语法，无需修改业务查询代码。

• 操作说明：
  • 编译安装sqlite-zstd扩展（MIT开源协议，无使用成本）
  • 调整建表语句，给重复率高的文本字段开启压缩：

    cur.execute("""CREATE TABLE IF NOT EXISTS logs (
        server TEXT COMPRESSION ZSTD, 
        port INTEGER, 
        ip TEXT COMPRESSION ZSTD, 
        time TEXT, 
        url TEXT COMPRESSION ZSTD, 
        status INTEGER, 
        bytes INTEGER, 
        referer TEXT COMPRESSION ZSTD, 
        useragent TEXT COMPRESSION ZSTD
    )""")
    

  • 查询时先加载扩展即可，后续查询逻辑完全不变：

    conn = sqlite3.connect('logs.db')
    conn.enable_load_extension(True)
    conn.execute('SELECT load_extension("libsqlite_zstd.so")')
    # 原有SELECT语句直接运行即可
    

  日志类数据文本重复率极高，该方案压缩率可达5:1以上，单月200MB日志导入后仅占30-40MB，1年总存储量可控制在500MB以内，和你原有压缩日志的存储成本基本持平。

方案2：分库归档压缩 #

利用日志冷数据只读的特性，按月份分库管理，兼顾查询效率和存储成本：

• 当月正在写入的日志使用未压缩SQLite库，支持高频查询和写入
• 上月日志写入完成后，执行VACUUM命令优化冗余空间，再用gzip压缩存储，单月压缩后仅占10MB左右，和你原有logrotate压缩后的体积一致
• 查询历史冷数据时，临时解压对应月份的压缩库到临时目录，查询完成后删除临时文件即可，无需长期占用存储空间

方案3：原生SQLite sqlar压缩归档 #

如果不想编译第三方扩展，可以使用SQLite 3.31.0及以上版本原生支持的sqlar压缩归档格式，完全兼容标准SELECT查询：

• 日志导入临时SQLite库后，执行以下命令生成只读压缩归档：

  sqlite3 temp_logs.db ".archive --create logs.sqlar --compress"
  

• 查询时直接打开logs.sqlar文件即可，所有现有查询语句无需修改即可运行
  该方案使用zlib压缩，压缩率略低于zstd，但无需额外依赖，使用门槛极低。

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