我从淘宝上买了每天的股票成交明细文件, 每支股票一天的成交明细就是一个 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 还小,哪知道大了这么多倍
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.” ———————————————— 关闭日志 |
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; 数据可以共享下不? |
3
encro 2020-07-11 11:37:40 +08:00
还有就是修改字段长度
|
4
felixcode 2020-07-11 11:38:11 +08:00 via Android
可能不是数据文件占用的,是日志文件占用的,先找下是什么文件占用的空间吧
|
5
encro 2020-07-11 11:39:21 +08:00 1
希望占用比 csv 小是不可能的。采用 gbk 存?
ALTER TABLE `mytable` DEFAULT CHARACTER SET=gbk, |
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 |
7
blueaurora OP @encro
数据 我在 http://www.sdata.vip 买的,200 块一年吧 网盘是 https://pan.baidu.com/s/1nAqB-fA4S9dz98znapSfNQ#list/path=%2F pwd 是 ascii 十进制 105 112 112 100 ,也就是四个小写字母 。 隔一段网盘密码就换 |
8
imn1 2020-07-11 12:17:15 +08:00
分笔数据只能用服务器级机器
实际上个人拿分笔没太大用,因为个人通过券商联网购买,比起席位号机器购买是有延时的 除非预埋地雷单,否则抢不过人家 如果要做深度分析,还是备台强力机器吧 |
9
blueaurora OP @encro 每个表就是 10M,3800 个表, 那其实就是好几十 G 了。 这表不能再小了?
|
10
blueaurora OP @felixcode 同问
“每个表就是 10M,3800 个表, 那其实就是好几十 G 了。 这表不能再小了?” |
11
blueaurora OP 现在就是 一个表的 .ibd 文件,就是 10M 的样子。 比如 600196.ibd ,有 14M
|
12
blueaurora OP 我没有开日志呢..
|
13
fengyqf 2020-07-11 12:45:20 +08:00
数据库通常不会比 csv 小,除了数据还要存索引,都要空间;字符串型字段,如果固定长度的表,还会大量浪费空间。
|
14
laminux29 2020-07-11 12:47:08 +08:00
你可以写个程序,每次查询时,先自动从 7z 解压,自动导入 mysql,然后再进行查询。查询完毕后,自动把 mysql 数据清空。这样就能解决数据库占硬盘过大的问题。
|
15
yzwduck 2020-07-11 13:01:30 +08:00
如果是 mysql 新手的话,我觉得可以跳过 mysql,试试 xsv 这个工具,它能直接处理 csv 数据。
csv 体积大的话,可以用 xz/zstd 压缩一下。 |
16
laqow 2020-07-11 13:20:19 +08:00
我能想到的直接存 csv 的 gz,写个 python 的接口,需要哪天再读哪天进数据库,数据库只维护关系表,不维护具体数据
|
17
outoftimeerror 2020-07-11 13:55:08 +08:00
写 hive,日期做分区字段,格式选 parquet
|
18
rockyou12 2020-07-11 14:10:01 +08:00 1
换 pg 试下,我现在就用 pg 分析日线周线,数据量应该比你的小但完全没碰到压力。或者考虑下 timescale ?这个是时序数据库,但基本还是 pg 用法,性能要强得多
|
19
angryfish 2020-07-11 14:14:56 +08:00 via iPhone
要注意字段类型的设置
|
20
ichao1214 2020-07-11 14:33:57 +08:00 via Android
场景不太适合,不如使用时直接读 csv 。
|
21
Jackeriss 2020-07-11 14:35:51 +08:00 via iPhone
看下你字段类型和索引设置
|
22
silentsee 2020-07-11 14:38:35 +08:00
分析场景,直接上 clickhouse
|
23
felixcode 2020-07-11 14:47:17 +08:00
@blueaurora
要跟文本相差不大的话,那应该就差不多,mysql 不大清楚,pg 应该有数据压缩的功能或模块,我刚简单搜一下 postgresql data compression,还是能找出不少来的。 |
24
hbolive 2020-07-11 17:48:11 +08:00
mysql 比 csv 小不太可能,但是你这也差太多了,看是不是字段不合理。。
|
25
boyhailong 2020-07-11 18:47:32 +08:00
压缩
|
26
QGabriel 2020-07-11 20:03:03 +08:00
能问问多钱买的吗?
|
27
chenxytw 2020-07-11 21:03:55 +08:00
OLAP 场景,选一些列式存储吧....做这些的公司就没见过用 Mysql 的.....
|
28
blueaurora OP @QGabriel 看 7 楼
|
29
blueaurora OP 谢谢大伙,看来确实白,这场景应该不太适合用 mysql 。 我先改字段+toku 试一下
|
30
Jafee 2020-07-11 21:53:39 +08:00
@blueaurora #7 密码换过了? 好像不对了。
|
31
bnm965321 2020-07-11 22:09:09 +08:00
这个是数据仓库的应用场景,可以看看 AWS 的 RedShift 。
|
32
kelvin_fly 2020-07-12 10:25:01 +08:00
这些数据应该也可以实时抓取。我本来也想程序做一下,后来感觉不如肉眼和手动操作好~
|
33
blueaurora OP @kelvin_fly 这个数据是分笔真实成交,不是 3 秒或 1 秒的成交快照,实时抓取不到的, 这也不是实时数据,当天下午 4 点左右出来的
|
34
fiht 2020-07-12 21:38:19 +08:00
可以考虑用一下 clickhouse,自带压缩,兼容 SQL 语法,非常方便
|
35
blueaurora OP @Jafee 目前还没有换啊..注意我给的是字母的十进制 ascii 码
|
36
Jafee 2020-07-13 09:56:05 +08:00
@blueaurora 奇怪,当时怎么输入都不对,后来就可以了。谢谢!
|
37
rnicrosoft 2020-07-14 11:21:44 +08:00 via Android
@blueaurora 分享被取消了
|
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 年的,故障率就非常高了。你找他买的时候记得和他签好保修。
|