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

[mytry]

场景：一个 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 有没有自带的类似这样的功能？或者直接使用内置的压缩功能？

[mhycy]

UA 单独分出来，用视图来聚合数据，但性能不太好，日志倒是没什么

[picone]

为啥要把 UA 入 mysql 呢？为啥不把 UA 里面有用的信息（比如区分是什么浏览器，版本号）提取出来再入库，这样会更有意义，原来的文本日志可以压缩封存。

[zhangwugui]

说实话，每天千万级别的量我还真不清楚如何处理，欢迎大佬指导。

[yc8332]

这种统计数据不是应该交给 es 来处理吗。。mysql 感觉不好弄

[batter]

不知道这么做的目的是什么，上家公司有类似的需求，但是数据量每天在百万左右

[SpartzTao]

nginx 日志 通过 logstash 写入 es 里面，业务直接在 es 中处理比较好

[xiaoxinshiwo]

ELK

[fireapp]

每天千万条直接按天存 tsv 文本啊，然后 gz 下，一千万条也就 300M 左右，查询分析啥的用 spark/flink/impala/drill 单机 8G 内存都能如丝顺滑处理😊

[swulling]

最直接的就是别用 MYSQL，场景不适合

[monsterxx03]

如果你公司有 hadoop, spark 的基础设施的话，日志存成 parquet 格式, 可以按 column 选择不同的压缩算法, web access log 这种可以达到很高的压缩比, spark 就能直接查了.

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

如果用的是 MariaDB, 可以试试 ColumnStore 存储引擎, 也是列式压缩的.

[xschaoya]

没这个级别的日志，感觉关系数据库不适合来处理日志，一般都是文本压缩，这种得上个独立的日志系统吧，欢迎大佬指导

[owenliang]

大数据可以考虑上 hadoop，如果公司允许的话，就不太用操心存储量和计算性能的问题了。

[liprais]

格式化之后提取有用的信息存 mysql,不要用 mysql 存原始日志。
原始日志压缩之后丢 s3,用的时候再处理。
或者你直接用 spark 处理之后存成 parquet,效果也比较好。
或者用 pgsql 存成 jsonb
总之 mysql 不是拿来存大文本的

[ic2y]

用 ElasticSearch