MySQL何时尝试更新列的索引？ [英] When does MySQL attempt to update an index for a column?

问题描述

我想要确定MySQL更新索引的情况。说我有下面的表：

I'm trying to determine what situations MySQL updates an index. Say I have the following table:

CREATE TABLE MyTable (
  ID INT NOT NULL AUTO_INCREMENT,
  MyIndexedColumn VARCHAR NOT NULL,
  MyNonIndexedColumn VARCHAR,
  PRIMARY KEY (ID),
  INDEX MyNewIndex(MyIndexedColumn)
)

然后我运行以下SQL插入一行：

Then I run the following SQL to insert a row:

INSERT INTO MyTable (MyIndexedColumn, MyNonIndexedColumn) 
VALUES ('MyTestValue', 'MyTestValue');

我理解这个查询会在MySQL中为B树索引添加某种哈希键值MyTestValue。

I understand that this query will add some sort of hash key to a B-Tree index in MySQL for the value 'MyTestValue'.

现在，如果我运行下面的语句，将强制更新B-Tree索引，即使我没有更改

Now, if I run the following statement, will that force that B-Tree index to be updated, even if I haven't changed the value of the column?

UPDATE MyTable SET MyIndexedColumn = 'MyTestValue', 
MyNonIndexedColumn = 'A New Value' WHERE ID = 1;

MySQL是否足够聪明地确定？或者只是让列的一部分更新语句，我告诉MySQL可能已经改变了，它应该做更新索引的工作。

Is MySQL smart enough to determine that? Or by just making that column part of the update statement, am I telling MySQL that possibly something has changed, and it should do the work to update the index?

推荐答案

如果您在MySQL客户端中运行该查询，您会看到类似

If you run that query in the MySQL client, you'll see something like

行匹配：1 ，Rows Updated：0

Rows matches: 1, Rows Updated: 0

所以MySQL肯定知道一行何时改变了 - 我想从那里，足够聪明，不能从那里更新索引。

So MySQL definitely knows when a row has changed or not - I'd assume from there that they're smart enough not to update the index from there.

这篇关于MySQL何时尝试更新列的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！