MySQL，长文本、文本或blob哪个更有效?提高插入效率

2021-12-29 00:00:00 sql php mysql mysqli drupal

我正在将大量数据从多个数据库迁移到一个数据库中.作为中间步骤，我将数据复制到每个数据类型和源数据库的文件中，然后将其复制到新数据库中的一个大表中.

I am in the process of migrating a large amount of data from several databases into one. As an intermediary step I am copying the data to a file for each data type and source db and then copying it into a large table in my new database.

新表中的结构很简单，叫做migrate_data.它包含一个 id(主键)、一个 type_id(在数据类型集中递增)、数据(一个包含序列化 PHP 对象的字段，其中包含我正在迁移的数据)、source_db(显然是指源数据库)、data_type(标识我们正在查看的数据类型).

The structure is simple in the new table, called migrate_data. It consists of an id (primary key), a type_id (incremented within the data type set), data (a field containing a serialized PHP object holding the data I am migrating), source_db (refers to the source database, obviously), data_type (identifies what type of data we are looking at).

我已经为除数据字段之外的所有内容创建了键和键组合.目前我将数据字段设置为长文本列.用户插入平均每次需要大约 4.8 秒.我能够在桌子上使用 DELAY_KEY_WRITE=1 将时间缩短到 4.3 秒.

I have created keys and key combinations for everything but the data field. Currently I have the data field set as a longtext column. User inserts are taking about 4.8 seconds each on average. I was able to trim that down to 4.3 seconds using DELAY_KEY_WRITE=1 on the table.

我想知道的是是否有办法进一步提高性能.可能通过更改为不同的数据列类型.这就是为什么我询问 longtext vs text vs blob 的原因.对于这种插入，有没有更有效的?

What I want to know about is whether or not there is a way to improve the performance even more. Possibly by changing to a different data column type. That is why I ask about the longtext vs text vs blob. Are any of those more efficient for this sort of insert?

在你回答之前，让我再给你一点信息.我将所有数据发送到一个插入函数，该函数接受对象，通过序列化运行它，然后运行数据插入.它还使用 Drupal 6(及其 db_query 函数)完成.

Before you answer, let me give you a little more information. I send all of the data to an insert function that takes the object, runs it through serialize, then runs the data insert. It is also being done using Drupal 6 (and its db_query function).

任何效率改进都会很棒.

Any efficiency improvements would be awesome.

当前表结构:

CREATE TABLE IF NOT EXISTS `migrate_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type_id` int(10) unsigned NOT NULL DEFAULT '0',
  `data` longtext NOT NULL,
  `source_db` varchar(128) NOT NULL DEFAULT '',
  `data_type` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `migrated_data_source` (`source_db`),
  KEY `migrated_data_type_id` (`type_id`),
  KEY `migrated_data_data_type` (`data_type`),
  KEY `migrated_data_id__source` (`id`,`source_db`),
  KEY `migrated_data_type_id__source` (`type_id`,`source_db`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1;

推荐答案

各种文本/blob 类型在 PHP 中的存储要求都是相同的，并且执行方式完全相同，只是文本字段需要进行字符集转换.blob 字段不是.换句话说，blob 用于存储二进制文件，该二进制文件的输出必须与输入的完全相同.文本字段用于存储可能/可以/将会从一种字符集转换为另一种字符集的文本数据.

The various text/blob types are all identical in storage requirements in PHP, and perform exactly the same way, except text fields are subject to character set conversion. blob fields are not. In other words, blobs are for when you're storing binary that MUST come out exactly the same as it went in. Text fields are for storing text data that may/can/will be converted from one charset to another.

相关文章