几百万条记录后，mysql插入索引表需要很长时间

Question

我有一张这样的 table

CREATE TABLE IF NOT EXISTS `dnddata` (
  `numbers` varchar(10) NOT NULL,
  `opstype` char(1) NOT NULL,
  PRIMARY KEY (`numbers`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (numbers)
PARTITIONS 25 */;

我必须插入 3 亿条记录。我每次使用 load data in file 从 csv 文件插入 1000 万条记录。

第一次插入 1000 万条记录需要将近 5 分钟。每次时间都在逐渐增加。在 3000 万条记录后，它停止插入并且内存使用 100% 服务器没有响应。

在我的 my.cnf 文件设置下面

bulk_insert_buffer_size = 100M
key_buffer = 100M
sort_buffer_size = 50M
read_buffer = 50M

我正在使用 2 G 内存的 cpu。

3000 万条记录的详细信息

    Space usage
Type    Usage
Data    545.3   MiB
Index   694.8   MiB
Total   1,240.1 MiB

MySQL client version: 5.5.14

如果没有索引，它会在 50 秒内插入罚款 1000 万。

请告诉我需要更改什么样的设置。

Answer 1

尝试:

ALTER TABLE dnddata DISABLE KEYS;

LOAD DATA INFILE...

ALTER TABLE dnddata ENABLE KEYS;

同时将 my.cnf 配置为:

key_buffer_size = 1G
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 2M
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 256M

修改MySQL配置文件后重启MySQL服务器。

下面的公式可能会帮助您正确配置参数:

key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory (< 2GB)

您真的需要数字作为 varchar(10) 吗？将其设为 char(10) 或 int 将有助于提高性能。

在 varchar 列上创建 PRIMARY KEY 是一项开销，因此如果不需要，您可以将其删除。