Когда MySQL пытается обновить индекс для столбца?
Я пытаюсь определить, в каких ситуациях MySQL обновляет индекс. Скажем, у меня есть следующая таблица:
CREATE TABLE MyTable (
ID INT NOT NULL AUTO_INCREMENT,
MyIndexedColumn VARCHAR NOT NULL,
MyNonIndexedColumn VARCHAR,
PRIMARY KEY (ID),
INDEX MyNewIndex(MyIndexedColumn)
)
Затем я запускаю следующий SQL, чтобы вставить строку:
INSERT INTO MyTable (MyIndexedColumn, MyNonIndexedColumn)
VALUES ('MyTestValue', 'MyTestValue');
Я понимаю, что этот запрос добавит какой-то хеш-ключ к индексу B-Tree в MySQL для значения MyTestValue.
Теперь, если я выполню следующую инструкцию, приведет ли это к обновлению индекса B-Tree, даже если я не изменил значение столбца?
UPDATE MyTable SET MyIndexedColumn = 'MyTestValue',
MyNonIndexedColumn = 'A New Value' WHERE ID = 1;
Достаточно ли умен MySQL, чтобы это определить? Или просто сделав этот столбец частью оператора обновления, я сообщаю MySQL, что, возможно, что-то изменилось, и он должен выполнить работу по обновлению индекса?
Ответы 4
Если вы запустите этот запрос в клиенте MySQL, вы увидите что-то вроде
Rows matches: 1, Rows Updated: 0
Таким образом, MySQL определенно знает, изменилась строка или нет - я бы предположил, что они достаточно умны, чтобы не обновлять индекс оттуда.
Его ответ остается неизменным до тех пор, пока вы фактически не меняете какие-либо поля. При первом запуске запроса, если данные изменены, вы получите измененную строку. Запустите его второй раз, и "ничего" не произойдет.
Когда вы выполняете ОБНОВЛЕНИЕ, MySQL сообщает количество совпавших строк и количество измененных. Выполнение вашего примера запроса дает результат:
Запрос в порядке, затронуты 0 строк (0,00 сек) Совпадение строк: 1 Изменено: 0 Предупреждений: 0
Я был бы очень удивлен, если бы MySQL не использовал эту информацию для определения необходимости обновления индекса.
MySQL не только достаточно умен, чтобы не обновлять индекс, если значение не изменилось, но и достаточно умен, чтобы не перезаписывать значение столбца с тем же значением.
Я провел небольшое тестирование с mysql 5.0.41, сравнивая обновления с двумя идентичными таблицами innodb (7 столбцов, все целые числа), за исключением того, что одна таблица имела 5 индексов (пара из которых была двухколоночной), а другая таблица индексов не было. (Однако у каждой таблицы был свой индекс первичного ключа.)
Вот что у меня получилось (таблица без индексов - A, таблица с индексами - B):
10k updates of an indexed column with a new value:
A: 76.8 seconds
B: 126.7 seconds
10k updates of a non-indexed column with a new value:
A: 27.6 seconds
B: 22.0 seconds
10k updates of a random column with its same value:
A: 1.4 seconds
B: 1.2 seconds
10k updates of a random column with an incremented value:
A: 12.2 seconds
B: 50.0 seconds
10k updates of an indexed column=>same value, non-indexed column=>new value:
A: 7.0 seconds
B: 10.5 seconds
Я предполагаю, что отчасти причина того, что те же / увеличенные значения работают быстрее, заключается в том, что мне пришлось искать строку перед выполнением обновления, чтобы она была в какой-то форме кэширована в mysql.
Все это в значительной степени воспроизводит то, что говорят другие, но дает некоторое представление о том, насколько на вещи влияют индексы. Однако в конкретном случае, о котором спрашивал Джим, похоже, что он может быть на 50% медленнее.
Хорошо, поэтому я просто изменил обновление на «ОБНОВЛЕНИЕ MyTable SET MyIndexedColumn = 'MyTestValue', MyNonIndexedColumn = 'Новое значение' WHERE ID = 1;» Это меняет ваш ответ?