mysql - 文件大小:CSV 与 MySQL

Question

我正在尝试针对 3GB CSV 文件优化我的 MySQL 表结构。到目前为止，我已经成功地导入了 1900 万行中的 60%，MySQL 表大小为 5.5GB。我如何优化我的表结构以减少数据库表的大小？ (因为我的磁盘空间快用完了!)

CSV 文件中的示例行是

"{0C7ADEF5-878D-4066-B785-0000003ED74A}","163000","2003-02-21 00:00","UB5 4PJ","T","N","F","106","","READING ROAD","NORTHOLT","NORTHOLT","EALING","GREATER LONDON","A"

...我的数据库结构是:

(
`transaction_id` int(10) unsigned NOT NULL,
  `reference` varchar(100) COLLATE utf32_unicode_ci NOT NULL,
  `price` int(10) unsigned NOT NULL,
  `sale_date` date COLLATE utf32_unicode_ci NOT NULL,
  `postcode` varchar(8) COLLATE utf32_unicode_ci NOT NULL,
  `type` varchar(1) COLLATE utf32_unicode_ci NOT NULL,
  `new_build` varchar(1) COLLATE utf32_unicode_ci NOT NULL,
  `tenure` varchar(1) COLLATE utf32_unicode_ci NOT NULL,
  `property_number` varchar(10) COLLATE utf32_unicode_ci NOT NULL,
  `property_name` varchar(100) COLLATE utf32_unicode_ci NOT NULL,
  `street` varchar(100) COLLATE utf32_unicode_ci NOT NULL,
  `area` varchar(100) COLLATE utf32_unicode_ci NOT NULL,
  `city` varchar(100) COLLATE utf32_unicode_ci NOT NULL,
  `county1` varchar(100) COLLATE utf32_unicode_ci NOT NULL,
  `county2` varchar(100) COLLATE utf32_unicode_ci NOT NULL,
  `unknown` varchar(1) COLLATE utf32_unicode_ci NOT NULL
)

Answer 1

让我们看看字段的大小。

您的数据库结构主要由 varchars 组成。在正常情况下，CSV 文件中每个字符应该大约一个字节。考虑到长度的开销，它们的大小应该大致相同或稍大(长度为两个字节，逗号为一个字节)。您可能会在数据库中存储 10% 的软糖因素。

整数可以任意选择。它们可以是 CSV 文件中的单个数字(带逗号的两个字符)或多个数字。它们在 MySQL 中将占用 4 个字节。 MySQL 中的日期可能比 CSV 文件中的日期小。

索引会产生额外的开销，特别是如果您的填充因子会在数据页上留下空间用于额外存储。数据页上的其他内容会产生额外的开销。但是，您的 table 似乎比预期的要大得多。

我的猜测是，出于 utf32 方面的考虑，您的表要大得多。如果您没有很好的理由，请切换到 utf8。

注意:通常 varchar(1) not null 可以替换为 char(1) 或 char(1) not null .这节省了长度的编码，这对于这么小的字段来说是一个很大的节省。这对于其他字段也是一种节省 如果您知道邮政编码是 8 个字符，则将其定义为 char(8) 而不是 varchar(8)。