MySQL，这是更有效的longtext，text或blob？提高插入效率 [英] MySQL, which is more efficient longtext, text, or blob? Improving insert efficiency

问题描述

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

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).

除了数据字段之外，我已经创建了所有的键和键组合。目前我将数据字段设置为longtext列。用户插入平均每个大约需要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.

我想知道的是，是否有办法改善表现甚至更多。可能通过更改为不同的数据列类型。这就是为什么我问一下长文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?

在回答之前，让我再给你一些更多的信息。我将所有数据发送到一个插入函数，它接受对象，通过serialize运行它，然后运行数据插入。它也正在使用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.

这篇关于MySQL，这是更有效的longtext，text或blob？提高插入效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！