mysql - InnoDB 插入速度非常慢并且速度变慢

Question

我最近将我的项目表切换到了 InnoDB(认为这种关系是一件好事)。我正在使用 PHP 脚本一次为大约 500 种产品编制索引。

存储单词/id 关联的表:

    CREATE TABLE `windex` (
 `word` varchar(64) NOT NULL,
 `wid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `count` int(11) unsigned NOT NULL DEFAULT '1',
 PRIMARY KEY (`wid`),
 UNIQUE KEY `word` (`word`)
) ENGINE=InnoDB AUTO_INCREMENT=324551 DEFAULT CHARSET=latin1

另一个表存储产品 id/word id 关联:

CREATE TABLE `indx_0` (
 `wid` int(7) unsigned NOT NULL,
 `pid` int(7) unsigned NOT NULL,
 UNIQUE KEY `wid` (`wid`,`pid`),
 KEY `pid` (`pid`),
 CONSTRAINT `indx_0_ibfk_1` FOREIGN KEY (`wid`) REFERENCES `windex` (`wid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `indx_0_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `product` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

该脚本使用 MyISAM 进行了测试，它对产品进行索引的速度相对较快(比 InnoDB 快得多)。第一次在 InnoDB 中运行时，速度慢得离谱，但在将更多值嵌套在一起后，我最终加快了很多速度(但还不够)。

由于行级锁，我假设 innodb 对于这种类型的事情会快得多，但事实并非如此。

我构建了一个类似于以下内容的查询:

SELECT
title,keywords,upc,...
FROM product
WHERE indexed = 0
LIMIT 500

我创建了一个循环并用需要添加到 windex 的所有单词和需要添加到 indx_0 的所有单词 ID/产品 ID 对填充一个数组。

因为每当我执行因重复值而失败的“REPLACE INTO”或“INSERT IGNORE INTO”时，innodb 会不断增加我的自动增量值，所以我需要确保我添加的值不存在。为此，我首先使用如下查询选择所有存在的值:

SELECT wid,word
FROM windex
WHERE
word = "someword1" or word = "someword2" or word = "someword3" ... ...

然后我根据存在的结果过滤掉我的数组，这样我添加的所有新词都是 100% 新的。

这大约占总执行时间的 20%。其他 80% 用于将对值添加到 indx_0，其中有更多的值。

这是我得到的示例。

0.4806 秒选择产品。 (总共 0.4807 秒)。
0.0319 秒收集 500 件元素。 (总共 0.5126 秒)。
5.2396 秒选择 windex 值进行比较。 (总共 5.7836 秒)。
更新计数需要 1.8986 秒。 (总共 7.6822 秒)。
0.0641 秒添加 832 条 windex 记录。 (总共 7.7464 秒)。
添加 3435 个 pid/wid 对的索引需要 17.2725 秒。 (总计 25.7752 秒)。
索引 500 个产品的操作耗时 26.07 秒。

3435 对都在单个查询中执行，例如:

INSERT INTO indx_0(pid,wid)
VALUES (1,4),(3,9),(9,2)... ... ...

在我的例子中，为什么 InnoDB 比 MyISAM 慢这么多？

Answer 1

InnoDB 提供了比 MyIsam 更复杂的键结构(FOREIGN KEYS)，并且在 InnoDB 中重新生成键非常慢。您应该将所有更新/插入语句包含在一个事务中(这些在 InnoDB 中实际上非常快，一旦我在具有 2 个索引的 InnoDb 表上进行了大约 300 000 次插入查询并且花了大约 30 分钟，一旦我将每 10 000 次插入包含在 BEGIN TRANSACTION 和 COMMIT 用了不到 2 分钟)。

我推荐使用:

BEGIN TRANSACTION;
SELECT ... FROM products;
UPDATE ...;
INSERT INTO ...;
INSERT INTO ...;
INSERT INTO ...;
COMMIT;

这将导致 InnoDB 只刷新一次索引而不是几百次。

让我知道它是否有效