mariadb 导入 txt 数据后体积有点大

2016-02-03 17:55:32 +08:00
 algas

最近工作需要,现学现卖折腾数据库。

导入数据

use MyBase;
load data local infile '/Data.txt' into table tmp FIELDS TERMINATED BY ',' lines terminated by '\n';

文本文件 Data.txt 大小是 1.5GB ,但是导入后发现 /var/lib/mysql/MyBase 有 2GB 大小。
tmp 表中元素都有按照需求声明大小,数值部分也有用 double 、 int 类型。

这种导入后比文本文件还大的情况有点让人搞不清楚。。。

4105 次点击
所在节点    MySQL
24 条回复
algas
2016-02-03 18:02:05 +08:00
Server version: 10.0.21-MariaDB MariaDB Server
mko0okmko0
2016-02-03 19:03:14 +08:00
如果档案最大的是 ibdata1 档案,如下解
http://blog.fens.me/mysql-ibdata1/

如果不是.资料表快速瘦身 sql:
OPTIMIZE table xxxx

此篇说明资料库引擎有哪些可以选.还有该引擎支援的存档格式.
http://blog.jobbole.com/94385/
其中 compact 格式适合有读有写入.
ARCHIVE/Compressed 格式是高压缩格式.适合只写入一次之后只读.也就是写入后不再更动的.
Compressed 如果要更动.这格式会很很很很慢.
ARCHIVE 写入后只能读.不能改.可以整个表删除.不可单行删除.

TokuDB 是外挂引擎.需要较多的初始设定和安装.但使用上非常简单.
而且压缩率很高.读写速度都非常接近 myisam/innodb.
真心要优化.建议以上几个都弄.反正会增加你的经验.
mko0okmko0
2016-02-03 19:18:23 +08:00
有很完整的特性比較表
http://395469372.blog.51cto.com/1150982/1726147

infobright 介紹.我個人是沒用過.我用過 TokuDB.我建議你用 TokuDB 就順便裝上 infobright 跑性能測試.
http://ju.outofmemory.cn/entry/147507
mko0okmko0
2016-02-03 19:18:43 +08:00
忘了转码

有很完整的特性比较表
http://395469372.blog.51cto.com/1150982/1726147

infobright 介绍.我个人是没用过.我用过 TokuDB.我建议你用 TokuDB 就顺便装上 infobright 跑性能测试.
http://ju.outofmemory.cn/entry/147507
mko0okmko0
2016-02-03 19:20:19 +08:00
infobright 我不用的原因.商业版强大.免费社群版....呵呵.请详看
http://www.bitstech.net/2015/03/
algas
2016-02-03 20:28:19 +08:00
@mko0okmko0

我先尝试了以下一键优化
MariaDB [taxi]> OPTIMIZE table t20101101;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+-------------------------------------------------------------------+
| taxi.t20101101 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| taxi.t20101101 | optimize | status | OK |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (26 min 55.42 sec)

没有缩减体积,另外 ibdata1 文件并不大,只有 70 多 MB 。

我考虑先尝试一下 ARCHIVE 引擎,因为后面只有繁重的查询,
不过现在还不知道怎么换存储引擎。

tokudb 貌似需要额外安装,先暂缓吧
algas
2016-02-03 20:56:46 +08:00
不过 archive 不支持索引,不知道对查询会不会影响很大,
总之需要先干掉原来的索引
drop index sID on t20101101;
不然会出现 ERROR 1121 (42000): Table handler doesn't support NULL in given index.

有文章中说 archive 支持索引,但是每次 select 都要进行全表扫描,我就不明白这个索引还有啥用。
http://www.yoonper.com/?p=1457

看来我要换到 tokudb 了。。。
realpg
2016-02-03 23:15:12 +08:00
你说目录大,敢不敢进去目录 ls -la 之类把每个文件大小弄出来让大家看看到底是啥大
如果你是数据库新手,搞个 PHPMyAdmin 之类图形化工具,直接就能看到每个表大小,索引大小的。

感觉你这么多发帖回帖,信息只有“占用空间大” 是有用的,其他没一点有用的
yangqi
2016-02-03 23:19:09 +08:00
体积大点有什么问题么?数据库是为了优化数据查询,又不是为了节省空间,你在这个上面纠结有什么意义?
caola
2016-02-03 23:32:36 +08:00
这很正常啊,数据库是为了性能,又不是为了帮你压缩数据来节省空间的,
再说现在硬盘也不贵,才这点空间算什么
br00k
2016-02-04 00:24:21 +08:00
数据库是为效率设计的存储方式。文件可压缩率特别高,备份几十 MB ,恢复出来都能上 GB 。
algas
2016-02-04 11:16:08 +08:00
@realpg 真的就是数据库文件大啊,下面是部分 ls -lh 结果
-rw-rw----. 1 mysql mysql 65 Feb 3 15:13 db.opt
-rw-rw----. 1 mysql mysql 785 Feb 4 05:27 t20101124.frm
-rw-rw----. 1 mysql mysql 2.1G Feb 4 06:11 t20101124.ibd
-rw-rw----. 1 mysql mysql 785 Feb 4 06:11 t20101125.frm
-rw-rw----. 1 mysql mysql 2.2G Feb 4 06:53 t20101125.ibd
-rw-rw----. 1 mysql mysql 785 Feb 4 06:53 t20101126.frm
-rw-rw----. 1 mysql mysql 2.6G Feb 4 07:38 t20101126.ibd
-rw-rw----. 1 mysql mysql 785 Feb 4 07:37 t20101127.frm
-rw-rw----. 1 mysql mysql 3.0G Feb 4 08:36 t20101127.ibd
-rw-rw----. 1 mysql mysql 785 Feb 4 08:36 t20101128.frm
-rw-rw----. 1 mysql mysql 4.4G Feb 4 10:00 t20101128.ibd


@yangqi
@caola
你们说的都对,我是担心我使用姿势不对,毕竟 2 进制文件比文本文件
还要大出 50%以上。
缩减体积也是很重要的事情嘛,当然性能第一。
realpg
2016-02-04 11:25:02 +08:00
@algas
先 show create table `表名` 发下表结构

然后,执行这个发结果,注意把尾巴的库名替换成你的库名
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE '数据库名';
id4alex
2016-02-04 12:30:02 +08:00
索引还需要占用空间哟
algas
2016-02-04 18:39:38 +08:00
@id4alex 对哟
文本文件 1.5G ,数据库文件 2.0G ,做了一个索引后变成了 2.2G
algas
2016-02-04 19:03:07 +08:00
@realpg


表的结构如下, un4 是用的默认的 bigint ,没想到这么大。。。
*************************** 1. row ***************************
Table: t20101101
Create Table: CREATE TABLE `t20101101` (
`un1` char(9) DEFAULT NULL,
`un2` char(5) DEFAULT NULL,
`id` char(12) DEFAULT NULL,
`dat` char(14) DEFAULT NULL,
`lon` double(16,10) DEFAULT NULL,
`lat` double(16,10) DEFAULT NULL,
`un3` bigint(8) DEFAULT NULL,
`un4` bigint(20) DEFAULT NULL,
`speed` float(7,3) DEFAULT NULL,
`direct` int(4) DEFAULT NULL,
`status` int(4) DEFAULT NULL,
`event` int(4) DEFAULT NULL,
`un5` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

下面的按照你写的命令输出的结果。
+----------------+----------------+-----------+------------+---------+
| Table Name | Number of Rows | Data Size | Index Size | Total |
+----------------+----------------+-----------+------------+---------+
| taxi.t20101101 | 15.8967M | 1.9131G | 0.0000G | 1.9131G |
| taxi.t20101102 | 15.1946M | 1.7744G | 0.0000G | 1.7744G |
| taxi.t20101103 | 15.8909M | 1.9199G | 0.0000G | 1.9199G |
| taxi.t20101104 | 16.8661M | 1.9072G | 0.0000G | 1.9072G |
| taxi.t20101105 | 6.5362M | 0.7852G | 0.0000G | 0.7852G |
id4alex
2016-02-04 19:05:44 +08:00
@algas 这个算正常。
algas
2016-02-04 19:07:32 +08:00
另外,
+----------------+----------------+-----------+------------+---------+
| Table Name | Number of Rows | Data Size | Index Size | Total |
+----------------+----------------+-----------+------------+---------+
| taxi.t20101119 | 16.7206M | 1.9150G | 0.0000G | 1.9150G |

这个是按照 id 做了索引的,不知道上面显示的 index size 是不是指索引的大小。
realpg
2016-02-04 20:30:45 +08:00
@algas
你这个完全正常啊。你这表结构,都是 char 而不是 varchar ,其他都是数值型,每一行占用空间大小是固定的啊。


我按照你的表结构建表,插入 1M 条记录,每一条记录每个字段都是数字或者字符串 1


近似 1M 记录就是 120M 的空间,以此类推 一点也没错啊


非必要的 char 改成 varchar 试试吧,还有那个 bigint(8)什么鬼
yangqi
2016-02-04 22:38:08 +08:00
@algas 你真要研究的话看官方文档,你表的每行大小是固定的,所以表大小能算出来的

https://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html

体积和性能二选一,肯定是性能更重要,存储很便宜。 mysql 表也是可以压缩的

https://dev.mysql.com/doc/refman/5.5/en/innodb-compression-usage.html

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/255248

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX