V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
blueaurora
V2EX  ›  MySQL

我把每日股票成交明细的 csv 文件,导入到 mysql 里,结果数据库占硬盘过大怎么处理..

  •  1
     
  •   blueaurora · 2020-07-11 11:16:46 +08:00 · 6072 次点击
    这是一个创建于 1581 天前的主题,其中的信息可能已经有所发展或是发生改变。

    我从淘宝上买了每天的股票成交明细文件, 每支股票一天的成交明细就是一个 csv,一天就是 3800 多个 csv (沪深股票总数。 打成 7z 压缩包,就是 400M 左右。一个月 30 天的话,也就是 12G 左右。 我觉得 csv 麻烦,就想把这些数据写到 mysql 里

    我用 python 的 sqlalchemy 从 csv 里 先读出 DataFrame,再把成交股数改为成交手数,再 dataFrame.toSql 写到 mysql 里。 但没想到 mysql 里一天的数据,也就是对应压缩包 400M 的数据,竟然占了 27G.. 我看一个 csv 的内容,到 mysql 里是 10M 左右..我也不知道还有什么乱七八糟的,怎么这么大

    我这个 csv 的行首, 就是下边这些了,一般股票,也就是一个 csv 也就 3 万列: TranID Time Price Volume SaleOrderVolume BuyOrderVolume Type SaleOrderID SaleOrderPrice BuyOrderID BuyOrderPrice

    我从 sqlalchemy 打印出来的 sql 语句看 TranID 、SaleOrderID 、BuyOrderID ,都是 BigInteger

    而 SaleOrderVolume BuyOrderVolume Price SaleOrderPrice BuyOrderPrice 因为有小数,从 sqlalchemy 打印出来的 sql 语句看 都是 Float(53),应该转成 double 了

    我觉得这个类型不是影响数据库占硬盘大小的关键问题吧? 我是个 mysql 新手, 想问问这事怎么优化, 我希望的是同样数据量 mysql 占硬盘比 csv 还小,哪知道大了这么多倍

    38 条回复    2020-08-06 06:16:26 +08:00
    xyjincan
        1
    xyjincan  
       2020-07-11 11:20:02 +08:00
    设计该表为压缩表
    Mysql 开发手册中指出,“In particular, compressed tables use less disk space and so require less disk I/O to read and write the data. Compression is available for all kinds of workloads with InnoDB tables, and for read-only MyISAM tables.”
    ————————————————

    关闭日志
    encro
        2
    encro  
       2020-07-11 11:34:09 +08:00
    --- 查看那张表占用空间大

    USE information_schema;

    SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    DATA_LENGTH,
    INDEX_LENGTH
    FROM
    TABLES
    GROUP BY
    TABLE_SCHEMA
    ORDER BY
    DATA_LENGTH DESC;


    -- 修改表采用压缩

    USE mydb;

    ALTER TABLE mytable ROW_FORMAT = DYNAMIC;

    OPTIMIZE TABLE mytable;

    如果没有 DYNAMIC,那么采用 COMPRESSED 也可以

    -- 修改表采用 tokudb (需要安装 tokudb 引擎)

    ALTER TABLE `mytable`
    ENGINE=tokudb;


    数据可以共享下不?
    encro
        3
    encro  
       2020-07-11 11:37:40 +08:00
    还有就是修改字段长度
    felixcode
        4
    felixcode  
       2020-07-11 11:38:11 +08:00 via Android
    可能不是数据文件占用的,是日志文件占用的,先找下是什么文件占用的空间吧
    encro
        5
    encro  
       2020-07-11 11:39:21 +08:00   ❤️ 1
    希望占用比 csv 小是不可能的。采用 gbk 存?

    ALTER TABLE `mytable`
    DEFAULT CHARACTER SET=gbk,
    encro
        6
    encro  
       2020-07-11 11:47:11 +08:00
    mysql 日志爆满,删除日志文件,定时清理日志
    https://my.oschina.net/magicalSam/blog/1143079


    以上写进了我新鲜出炉的 Blog:

    Mysql 查看以及优化表空间
    https://c4ys.com/archives/2255
    blueaurora
        7
    blueaurora  
    OP
       2020-07-11 11:56:23 +08:00   ❤️ 7
    @encro
    数据 我在 http://www.sdata.vip 买的,200 块一年吧
    网盘是 https://pan.baidu.com/s/1nAqB-fA4S9dz98znapSfNQ#list/path=%2F
    pwd 是 ascii 十进制 105 112 112 100 ,也就是四个小写字母 。
    隔一段网盘密码就换
    imn1
        8
    imn1  
       2020-07-11 12:17:15 +08:00
    分笔数据只能用服务器级机器
    实际上个人拿分笔没太大用,因为个人通过券商联网购买,比起席位号机器购买是有延时的
    除非预埋地雷单,否则抢不过人家

    如果要做深度分析,还是备台强力机器吧
    blueaurora
        9
    blueaurora  
    OP
       2020-07-11 12:36:52 +08:00
    @encro 每个表就是 10M,3800 个表, 那其实就是好几十 G 了。 这表不能再小了?
    blueaurora
        10
    blueaurora  
    OP
       2020-07-11 12:39:10 +08:00
    @felixcode 同问
    “每个表就是 10M,3800 个表, 那其实就是好几十 G 了。 这表不能再小了?”
    blueaurora
        11
    blueaurora  
    OP
       2020-07-11 12:40:47 +08:00
    现在就是 一个表的 .ibd 文件,就是 10M 的样子。 比如 600196.ibd ,有 14M
    blueaurora
        12
    blueaurora  
    OP
       2020-07-11 12:45:12 +08:00
    我没有开日志呢..
    fengyqf
        13
    fengyqf  
       2020-07-11 12:45:20 +08:00
    数据库通常不会比 csv 小,除了数据还要存索引,都要空间;字符串型字段,如果固定长度的表,还会大量浪费空间。
    laminux29
        14
    laminux29  
       2020-07-11 12:47:08 +08:00
    你可以写个程序,每次查询时,先自动从 7z 解压,自动导入 mysql,然后再进行查询。查询完毕后,自动把 mysql 数据清空。这样就能解决数据库占硬盘过大的问题。
    yzwduck
        15
    yzwduck  
       2020-07-11 13:01:30 +08:00
    如果是 mysql 新手的话,我觉得可以跳过 mysql,试试 xsv 这个工具,它能直接处理 csv 数据。
    csv 体积大的话,可以用 xz/zstd 压缩一下。
    laqow
        16
    laqow  
       2020-07-11 13:20:19 +08:00
    我能想到的直接存 csv 的 gz,写个 python 的接口,需要哪天再读哪天进数据库,数据库只维护关系表,不维护具体数据
    outoftimeerror
        17
    outoftimeerror  
       2020-07-11 13:55:08 +08:00
    写 hive,日期做分区字段,格式选 parquet
    rockyou12
        18
    rockyou12  
       2020-07-11 14:10:01 +08:00   ❤️ 1
    换 pg 试下,我现在就用 pg 分析日线周线,数据量应该比你的小但完全没碰到压力。或者考虑下 timescale ?这个是时序数据库,但基本还是 pg 用法,性能要强得多
    angryfish
        19
    angryfish  
       2020-07-11 14:14:56 +08:00 via iPhone
    要注意字段类型的设置
    ichao1214
        20
    ichao1214  
       2020-07-11 14:33:57 +08:00 via Android
    场景不太适合,不如使用时直接读 csv 。
    Jackeriss
        21
    Jackeriss  
       2020-07-11 14:35:51 +08:00 via iPhone
    看下你字段类型和索引设置
    silentsee
        22
    silentsee  
       2020-07-11 14:38:35 +08:00
    分析场景,直接上 clickhouse
    felixcode
        23
    felixcode  
       2020-07-11 14:47:17 +08:00
    @blueaurora
    要跟文本相差不大的话,那应该就差不多,mysql 不大清楚,pg 应该有数据压缩的功能或模块,我刚简单搜一下 postgresql data compression,还是能找出不少来的。
    hbolive
        24
    hbolive  
       2020-07-11 17:48:11 +08:00
    mysql 比 csv 小不太可能,但是你这也差太多了,看是不是字段不合理。。
    boyhailong
        25
    boyhailong  
       2020-07-11 18:47:32 +08:00
    压缩
    QGabriel
        26
    QGabriel  
       2020-07-11 20:03:03 +08:00
    能问问多钱买的吗?
    chenxytw
        27
    chenxytw  
       2020-07-11 21:03:55 +08:00
    OLAP 场景,选一些列式存储吧....做这些的公司就没见过用 Mysql 的.....
    blueaurora
        28
    blueaurora  
    OP
       2020-07-11 21:41:42 +08:00
    @QGabriel 看 7 楼
    blueaurora
        29
    blueaurora  
    OP
       2020-07-11 21:43:00 +08:00
    谢谢大伙,看来确实白,这场景应该不太适合用 mysql 。 我先改字段+toku 试一下
    Jafee
        30
    Jafee  
       2020-07-11 21:53:39 +08:00
    @blueaurora #7 密码换过了? 好像不对了。
    bnm965321
        31
    bnm965321  
       2020-07-11 22:09:09 +08:00
    这个是数据仓库的应用场景,可以看看 AWS 的 RedShift 。
    kelvin_fly
        32
    kelvin_fly  
       2020-07-12 10:25:01 +08:00
    这些数据应该也可以实时抓取。我本来也想程序做一下,后来感觉不如肉眼和手动操作好~
    blueaurora
        33
    blueaurora  
    OP
       2020-07-12 12:37:16 +08:00
    @kelvin_fly 这个数据是分笔真实成交,不是 3 秒或 1 秒的成交快照,实时抓取不到的, 这也不是实时数据,当天下午 4 点左右出来的
    fiht
        34
    fiht  
       2020-07-12 21:38:19 +08:00
    可以考虑用一下 clickhouse,自带压缩,兼容 SQL 语法,非常方便
    blueaurora
        35
    blueaurora  
    OP
       2020-07-13 09:38:21 +08:00
    @Jafee 目前还没有换啊..注意我给的是字母的十进制 ascii 码
    Jafee
        36
    Jafee  
       2020-07-13 09:56:05 +08:00
    @blueaurora 奇怪,当时怎么输入都不对,后来就可以了。谢谢!
    rnicrosoft
        37
    rnicrosoft  
       2020-07-14 11:21:44 +08:00 via Android
    @blueaurora 分享被取消了
    594duck
        38
    594duck  
       2020-08-06 06:16:26 +08:00
    @blueaurora 不要多想了,MYSQL 就是这么大,你裸文本不需要索引没有字段固定大小,但是你的 MYSQL 都有。我的建议是直接考虑买台二手 DELL R730 。 至强少核 高主频,128G 内存,1SSD,4 块 4T 7200 转 组 RAID10 。 @realpg 说手上有大量二手服务器,建议你问他买一台。 他的贴子在这里 https://www.v2ex.com/t/692634#reply91 。关于服务器,我的经验是大于 3 年的,故障率就非常高了。你找他买的时候记得和他签好保修。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   990 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 22:09 · PVG 06:09 · LAX 14:09 · JFK 17:09
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.