V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
mytry
V2EX  ›  程序员

MySQL 存储大量重复数据有什么好的优化方案?

  •  
  •   mytry · 2018-10-24 14:46:07 +08:00 · 3605 次点击
    这是一个创建于 1982 天前的主题,其中的信息可能已经有所发展或是发生改变。

    场景:一个 Web 反向代理设备,日志定时同步到 MySQL 数据库里,每天数量千万级,占用不少空间。

    为了节省硬盘空间,打算做一些优化。比如 HTTP Method 总共才 GET、POST 等几种,可以从字符改成 enum,只需 1 字节。当然这个节省不大,占用空间最多的是 UserAgent、URL、Referrer 这个字段,并且重复率很高。

    但是这些值是不固定的,没法像 enum 那样初始就能设置,需要后期统计才能知道哪些重复最多。

    目前设计了一个方案,比如 UserAgent,把它存在一个单独的表里,这个表的值是唯一的,并且对应一个 ID,起到字典的作用:

    table_useragent_dict:

    |   val  |   id    |
    |------------------|
    |  uax   |  0      |
    |  uay   |  1      |
    |  uaz   |  2      |
    

    这样原始表 UserAgent 无需字符串,只记录 ID 就可以,占用 2 或者 3 字节。其他 URL、Referrer 也同样用这种方式。

    不过这种方式总觉得有些麻烦,特别是后期维护。如果数据库底层能自动实现就好了。不知 MySQL 有没有自带的类似这样的功能?或者直接使用内置的压缩功能?

    14 条回复    2018-10-24 18:47:16 +08:00
    mhycy
        1
    mhycy  
       2018-10-24 14:52:52 +08:00
    UA 单独分出来,用视图来聚合数据,但性能不太好,日志倒是没什么
    picone
        2
    picone  
       2018-10-24 15:49:32 +08:00   ❤️ 1
    为啥要把 UA 入 mysql 呢?为啥不把 UA 里面有用的信息(比如区分是什么浏览器,版本号)提取出来再入库,这样会更有意义,原来的文本日志可以压缩封存。
    zhangwugui
        3
    zhangwugui  
       2018-10-24 16:04:17 +08:00
    说实话,每天千万级别的量我还真不清楚如何处理,欢迎大佬指导。
    yc8332
        4
    yc8332  
       2018-10-24 16:07:48 +08:00
    这种统计数据不是应该交给 es 来处理吗。。mysql 感觉不好弄
    batter
        5
    batter  
       2018-10-24 16:17:28 +08:00
    不知道这么做的目的是什么,上家公司有类似的需求,但是数据量每天在百万左右
    SpartzTao
        6
    SpartzTao  
       2018-10-24 17:44:55 +08:00
    nginx 日志 通过 logstash 写入 es 里面,业务直接在 es 中处理比较好
    xiaoxinshiwo
        7
    xiaoxinshiwo  
       2018-10-24 17:47:03 +08:00
    ELK
    fireapp
        8
    fireapp  
       2018-10-24 17:51:38 +08:00 via iPhone
    每天千万条直接按天存 tsv 文本啊,然后 gz 下,一千万条也就 300M 左右,查询分析啥的用 spark/flink/impala/drill 单机 8G 内存都能如丝顺滑处理😊
    swulling
        9
    swulling  
       2018-10-24 17:53:56 +08:00 via iPhone
    最直接的就是别用 MYSQL,场景不适合
    monsterxx03
        10
    monsterxx03  
       2018-10-24 18:00:31 +08:00
    如果你公司有 hadoop, spark 的基础设施的话,日志存成 parquet 格式, 可以按 column 选择不同的压缩算法, web access log 这种可以达到很高的压缩比, spark 就能直接查了.

    只有 MySQL 你试试 innodb 默认的压缩,看看压缩之后的大小能不能达到你的预期.

    如果用的是 MariaDB, 可以试试 ColumnStore 存储引擎, 也是列式压缩的.
    xschaoya
        11
    xschaoya  
       2018-10-24 18:17:35 +08:00 via Android
    没这个级别的日志,感觉关系数据库不适合来处理日志,一般都是文本压缩,这种得上个独立的日志系统吧,欢迎大佬指导
    owenliang
        12
    owenliang  
       2018-10-24 18:28:19 +08:00
    大数据可以考虑上 hadoop,如果公司允许的话,就不太用操心存储量和计算性能的问题了。
    liprais
        13
    liprais  
       2018-10-24 18:29:03 +08:00
    格式化之后提取有用的信息存 mysql,不要用 mysql 存原始日志。
    原始日志压缩之后丢 s3,用的时候再处理。
    或者你直接用 spark 处理之后存成 parquet,效果也比较好。
    或者用 pgsql 存成 jsonb
    总之 mysql 不是拿来存大文本的
    ic2y
        14
    ic2y  
       2018-10-24 18:47:16 +08:00
    用 ElasticSearch
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5338 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 05:52 · PVG 13:52 · LAX 22:52 · JFK 01:52
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.