php - MySQL:优化索引的添加

Question

注意:

PHP 的片段仅有助于说明此问题的架构，该问题主要针对 MySQL。

问题:

我在加速将数据插入数据库时​​遇到问题。数据量从几千行到几百万行不等。这些数据需要尽快插入。

这是背景:

我创建了一个小型库，它加载一个包含文件系统详细信息的文件。该文件作为 CSV 文件逐行读取，然后处理各列并将其插入到生成的表中。

创建表格:

这是使用 Doctrine2 的创作，但它应该是相当不言自明的。

$schema = new Schema();
$table = $schema->createTable($tableName);
$table->addOption('engine', 'MyISAM');

$table->addColumn('id',       'integer', array('unsigned' => true, 'autoincrement' => true));
$table->addColumn('path',     'string',  array('length' => 255));
$table->addColumn('name',     'string',  array('length' => 255));
$table->addColumn('pathname', 'string',  array('length' => 255));
$table->addColumn('atime',    'integer');
$table->addColumn('mtime',    'integer');
$table->addColumn('is_dir',   'boolean');
$table->addColumn('length',   'integer');

$table->setPrimaryKey(array('id'));

$queries = $schema->toSql($this->conn->getDatabasePlatform());

foreach($queries as $query) {
    $this->conn->executeQuery($query);
}

第一次尝试:

在我优化插入过程之前，表创建也包含这些索引。这些是我需要添加的索引。

$table->addUniqueIndex(array('pathname'), 'IDX_PATHNAME');

$table->addIndex(array('path'),   'IDX_PATH');
$table->addIndex(array('name'),   'IDX_NAME');
$table->addIndex(array('atime'),  'IDX_ACCESSED_TIME');
$table->addIndex(array('mtime'),  'IDX_MODIFIED_TIME');
$table->addIndex(array('is_dir'), 'IDX_IS_DIR');
$table->addIndex(array('length'), 'IDX_LENGTH');

$queries = $schema->toSql($this->conn->getDatabasePlatform());

foreach($queries as $query) {
    $this->conn->executeQuery($query);
}

$stmt = $this->conn->prepare(sprintf('CREATE FULLTEXT INDEX IDX_PATHNAME_FULLTEXT ON %s (pathname)', $this->table));
$stmt->execute();

当我插入超过 200,000 行的内容时，通常 Mysql 会慢得像爬行一样，PHP 甚至可能会耗尽内存(不知道为什么)。

插入后的索引:

我在这里读到:Is it better to create an index before filling a table with data, or after the data is in place?在插入后创建索引可以加快插入速度。

对我来说幸运的是，一旦数据被插入一次，它就不需要再次修改(除了不再需要时删除表)，所以在插入后添加索引非常适合我。

$diff = new TableDiff($this->table);
$indexes = array(
    new Index('IDX_PATHNAME',      array('pathname'), true),
    new Index('IDX_PATH',          array('path')),
    new Index('IDX_NAME',          array('name')),
    new Index('IDX_ACCESSED_TIME', array('atime')),
    new Index('IDX_MODIFIED_TIME', array('mtime')),
    new Index('IDX_IS_DIR',        array('is_dir')),
    new Index('IDX_LENGTH',        array('length')),
);

$diff->addedIndexes = $indexes;

$this->schemaManager->alterTable($diff);

$stmt = $this->conn->prepare(sprintf('CREATE FULLTEXT INDEX IDX_PATHNAME_FULLTEXT ON %s (pathname)', $this->table));
$stmt->execute();

一线希望:

我对此进行了测试，插入过程非常快，即使有超过 200 万行，PHP 内存甚至不超过 2.5%，Mysql 仅使用大约 8%(4GB RAM Xubuntu 64 位)。

砖墙:

一旦我添加了插入后用索引更新表的脚本，我的努力就失败了。尽管它一直持续到完成(因为它没有崩溃或卡住，这是一个优点)，但它仍然花费与开始添加索引时大致相同的时间。

我现在正在寻找优化架构或插入顺序的方法，希望更快地为表建立索引。

Answer 1

只需使用:加载数据INFILE

在此处了解更多信息:http://dev.mysql.com/doc/refman/5.1/en/load-data.html

这是加载 CSV 的最佳方法。