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

[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 类型。

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

[algas]

Server version: 10.0.21-MariaDB MariaDB Server

[mko0okmko0]

如果档案最大的是 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]

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

infobright 介紹.我個人是沒用過.我用過 TokuDB.我建議你用 TokuDB 就順便裝上 infobright 跑性能測試.
http://ju.outofmemory.cn/entry/147507

[mko0okmko0]

忘了转码

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

infobright 介绍.我个人是没用过.我用过 TokuDB.我建议你用 TokuDB 就顺便装上 infobright 跑性能测试.
http://ju.outofmemory.cn/entry/147507

[mko0okmko0]

infobright 我不用的原因.商业版强大.免费社群版....呵呵.请详看
http://www.bitstech.net/2015/03/

[algas]

@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]

不过 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]

你说目录大，敢不敢进去目录 ls -la 之类把每个文件大小弄出来让大家看看到底是啥大
如果你是数据库新手，搞个 PHPMyAdmin 之类图形化工具，直接就能看到每个表大小，索引大小的。

感觉你这么多发帖回帖，信息只有“占用空间大” 是有用的，其他没一点有用的

[yangqi]

体积大点有什么问题么？数据库是为了优化数据查询，又不是为了节省空间，你在这个上面纠结有什么意义？

[caola]

这很正常啊，数据库是为了性能，又不是为了帮你压缩数据来节省空间的，
再说现在硬盘也不贵，才这点空间算什么

[br00k]

数据库是为效率设计的存储方式。文件可压缩率特别高，备份几十 MB ，恢复出来都能上 GB 。

[algas]

@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]

@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]

索引还需要占用空间哟

[algas]

@id4alex 对哟
文本文件 1.5G ，数据库文件 2.0G ，做了一个索引后变成了 2.2G

[algas]

@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]

@algas 这个算正常。

[algas]

另外，
+----------------+----------------+-----------+------------+---------+
| Table Name | Number of Rows | Data Size | Index Size | Total |
+----------------+----------------+-----------+------------+---------+
| taxi.t20101119 | 16.7206M | 1.9150G | 0.0000G | 1.9150G |

这个是按照 id 做了索引的，不知道上面显示的 index size 是不是指索引的大小。

[realpg]

@algas
你这个完全正常啊。你这表结构，都是 char 而不是 varchar ，其他都是数值型，每一行占用空间大小是固定的啊。


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


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


非必要的 char 改成 varchar 试试吧，还有那个 bigint(8)什么鬼

[yangqi]

@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

[algas]

@realpg
bigint(8) 大概类似 Linux C 里面的 long long int 吧，当时考虑是为了压缩体积就没有用 char

[realpg]

@algas
mysql 后面那个数字是数字长度，不是 bit
int(4)的意思就是-999~9999 用 32bit 存储
bigint(4)的意思也是-999~9999 但是实际用 64bit 存储
你们需要个好歹也略懂 MySQL 的 admin

[jhdxr]

@algas 完全胡闹，还说自己『 tmp 表中元素都有按照需求声明大小』
1. char 是定长，和实际储存内容无关。变长请用 varchar ，但依然比纯文本大，因为还有存字符串长度
2. 上面也有人提到了，你数字存储用的太大了。。。

[algas]

@jhdxr

@realpg
啊，掩面.... 无颜在 V 站冒泡了

不过 un3 和 un4 确实是要用 bigint 保存的，原始数据就是 9 位左右长度的数字，例如 446943213 。
我担心后面的数据会出现更长情况，就选用了 bigint 类型，目的是压缩空间。

只能说这 2 个 unknown 数据目前没啥用，我运气还不错，没出问题～

我是物理方向的研究生，因为需要多次对这 100 多 G 的数据进行筛取分析，我认为使用数据库要更方便和快速，所以我在努力让自己略懂啊