MYSQL Update query using a temporary table when a equivalent select query does not

Question

我有一个 MYSQL 表 Foo，它在 id 上有主键，在不同的列上有 2 个其他非主键。 Fiddle "select" example

我的实际表包含数百万行，因此解释的行为不同，即。它在 2 个非主索引上使用 Index_Merge。

当我运行以下 Explain Update 语句时:

explain UPDATE Foo
SET readyState = 1 
WHERE readyState = 0
AND productName = 'OpenAM'
LIMIT 30;

Extra 列包含“Using Temporary”。

当我运行等效的 explain Select 语句时:

Explain Select id, productName, readyState
FROM Foo
WHERE readyState = 0
AND productName = 'OpenAM'
Limit 30;

Extra 列不包含“Using Temporary”。

这对我的实际表的影响是，当我更新时，会创建一个包含数百万行的临时表，因为它们在 Limit 30 开始之前都符合更新的条件。更新需要 4- 5 秒，而选择只需要 ~0.001 秒，因为它不会创建合并索引的临时表。我知道 Update 还需要更新所有 3 个索引(查询中使用的主索引 + 2 个非主索引)，但如果更新 3 个索引中的 30 个索引行需要 4 秒，我会感到震惊。

问题有没有办法强制更新不使用不必要的临时表？我的印象是 MYSQL 以与选择相同的方式处理更新查询计划。

如果更新需要临时表而不是选择，为什么？

编辑:

Show Create Table (removed a heap of columns since it is a very wide table):
    CREATE TABLE Item (
      ID int(11) NOT NULL AUTO_INCREMENT,
      ImportId int(11) NOT NULL,
      MerchantCategoryName varchar(200) NOT NULL,
      HashId int(11) DEFAULT NULL,
      Processing varchar(36) DEFAULT NULL,
      Status int(11) NOT NULL,
      AuditWho varchar(200) NOT NULL,
      AuditWhen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (ID),
      KEY idx_Processing_Item (Processing),
      KEY idx_Status_Item (Status),
      KEY idx_MerchantCategoryName_Item (MerchantCategoryName),
      KEY fk_Import_Item (ImportId),
      KEY fk_Hash_Item (HashId),
      CONSTRAINT fk_Hash_Item FOREIGN KEY (HashId) REFERENCES Hash (ID),
      CONSTRAINT fk_Import_Item FOREIGN KEY (ImportId) REFERENCES Import (ID)
) ENGINE=InnoDB AUTO_INCREMENT=12004589 DEFAULT CHARSET=utf8

更新声明

    explain UPDATE Item
    SET Processing = 'd53dbc91-eef4-11e5-a3a6-06f88beef4f3',
        Status = 2,
        AuditWho = 'name',
        AuditWhen = now()
    WHERE EventId = 1
    AND Processing is null
    AND Status = 1
    LIMIT 30;

结果:

'id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra',
'1','SIMPLE','Item','index_merge','idx_Processing_Item,idx_Status_Item,fk_Import_Item','idx_Processing_Item,idx_Status_Item,fk_Import_Item','111,4,4',\N,'1362610','Using intersect(idx_Processing_Item,idx_Status_Item,fk_Import_Item); Using where; Using temporary',

选择查询

    explain select ID from Item where Status = 1 and Processing is null and ImportId = 1 limit 30;

结果:

'id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra',
'1','SIMPLE','Item','index_merge','idx_Processing_Item,idx_Status_Item,fk_ImportEvent_Item','idx_Processing_Item,idx_Status_Item,fk_Import_Item','111,4,4',\N,'1362610','Using intersect(idx_Processing_ItemPending,idx_Status_ItemPending,fk_ImportEvent_ItemPending); Using where; Using index',

Answer 1

一个猜测:

UPDATE 正在更改索引值 (readyState)，对吗？这意味着相关索引正在随着 UPDATE 使用它而改变？因此，UPDATE 可能通过获取行(显然以一种低效的方式)来“保护”自己，将它们扔到 tmp 表中，然后才执行操作。

“Index merge intersect”几乎总是比复合索引效率低:INDEX(readyState, productName)(任意顺序)。建议您添加。

由于您没有ORDER BY，因此无法预测哪个“30”。建议您添加 ORDER BY the-primary-key。