首页 注册 登录
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 · 6131 次点击
    这是一个创建于 1630 天前的主题，其中的信息可能已经有所发展或是发生改变。

    我从淘宝上买了每天的股票成交明细文件， 每支股票一天的成交明细就是一个 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 还小，哪知道大了这么多倍

  • csv
  • MySQL
  • SQLAlchemy
  • SQL
    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   ·   实用小工具   ·   2736 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 12:38 · PVG 20:38 · LAX 04:38 · JFK 07:38
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.