更新用于更新的索引中的列的效率有多低

Question

我们正在考虑执行以下SQL查询：

UPDATE tableA SET columnA = columnB WHERE columnA = 0 LIMIT 1000

在columnA上，tableA有10亿行和一个索引。columnB是一种高度选择性的数据。

1)性能会有多差？它对正在更新的索引进行查询这一事实是否会对索引造成某种阻塞？还是先完成查询，然后再进行更新？

2)由于该表有十亿行，并且我们将其限制为一次1000行，因此该查询将有多次迭代。columnA最初是0，我确定对于开始部分，它将执行表扫描。但是，随着更新的进行，当columnA中填充了更具选择性的columnB数据时，索引是否会帮助查询，或者更好的做法是删除索引并让它执行表扫描？

Answer 1

假设为InnoDB，被更新的行将获得排他(写)锁。这意味着其他SELECT查询仍然可以读取记录，但是任何需要排他锁的记录查询(update查询)都将阻塞，直到锁被释放(事务/查询完成)。

此外，在查询(事务)完成之前，任何SELECT查询都将看到旧值。

关于索引用于选择的用法，您可能是对的。如果您有一个较新的MySQL版本，您应该能够对更新查询执行一个EXPLAIN，以查看它是否将使用索引。在查询优化中通常不会考虑LIMIT子句，因此直到0的记录数量显著减少时，才会使用索引来满足搜索。

但是，由于您比MySQL了解更多关于查询的信息，并且知道实际需要的查找次数有限( LIMIT子句)，因此可以在UPDATE中强制索引

UPDATE tableA FORCE INDEX (idx_columnA)
SET columnA = columnB WHERE columnA = 0 LIMIT 1000