我有一些相当大的 CSV 文件要加载到我的 MySQL 5.7 数据库中。这些文件有几 GB 大小,几百万行长,并且具有必须在连接中使用的大列宽(有时长达约 500 个字符)。
数据都是标准英文字符,大部分列都可以放入一个单字节字符集,如latin1
. 但是,有几列需要 unicode 来表示商标/注册/版权符号、测量符号(英寸、英尺、半径等),因此我一直utf8mb4
在所有表格上使用。
这样做的问题是双重的。它扩大了我们的索引大小,因此在某些情况下,我们无法在列上创建索引,因为宽度变得大于 3072。此外,它似乎对性能产生了重大影响,大概是因为数据大小是 4 倍。
我想做的是latin1
在表中的所有列上使用,并且只utf8mb4
在需要它的列上使用。这导致了我的问题-
确定哪些列实际存储多字节字符的最佳方法是什么?我可以在加载之前在我的 CSV 中(可能使用 python/pandas 吗?)或从数据库中以某种方式检测到这一点?文件存储为 utf8。它们当前被加载到一个utf8mb4
表中。如果我可以轻松地扫描表格并说“此列不包含多字节数据”,我可以将其更改为latin1
.
其次,如果我尝试使用不同编码的列创建复合索引,我会遇到问题吗?假设列A
是 utf8mb4,列B
是 latin1。在这两列上创建索引有什么问题吗?即:CREATE INDEX my_index
ON my_table(A, B);
。我假设这样做没有问题。
数据大小不是 4x。英文文本,即使是 utf8mb4,每个字符也只占用一个字节。商标(等)符号是多字节的。然后你提到的只有2个字节。表情符号和一些中文是需要 4 个字节的地方。
不要在大列上创建索引。在获得查询之前不要创建索引——从查询中得出最佳索引。
让一列是 latin1 而另一列是 utf8mb4 (等)是非常好的(至少在 MySQL 中)。并且它们都可以在同一个索引中。
我建议对数据进行几次传递。首先使用 utf8mb4 引入所有内容,没有索引,宽列(例如
TEXT
)。然后分析你得到了什么SELECT MAX(CHAR_LENGTH(col2)), ...
—— 测试非 latin1 等。对于第二遍,重新执行架构以更接近 max len 等。