Co pewien czas spotykam się z sytuacją, gdy serwer bazodanowy, a konkretnie jego procesor, przeciążany jest przez zapytania typu:

UPDATE jakastabela SET cokolwiek = '4' WHERE kolumna1 = '10' AND kolumna2 = '20';

Dlaczego takie zapytanie tak bardzo obciąża CPU? Przecież zapytanie typu UPDATE to modyfikacja danych, czyli operacja dyskowa? Co tu robi procesor? Przyczyna jest zazwyczaj prozaiczna. Twórca tego zapytania zapomniał o podstawowej sprawie – o indeksach. Indeksy w UPDATE? Przecież indeksy przyspieszają wyszukiwanie danych, a nie ich modyfikację. Ba, indeks zmniejsza wydajność zapytań modyfikujących dane bo jeśli indeksu nie ma, to modyfikujemy tylko dane w tabeli. Jeśli natomiast indeks jest, to oprócz tabeli konieczna jest także modyfikacja danych z indeksu. Dodatkowa operacja dyskowa. O co więc chodzi z tymi indeksami w zapytaniach typu UPDATE?

Sprawa jest bardzo prosta – aby zmodyfikować kolumnę `cokolwiek` i przypisać do niej wartość 4, najpierw konieczne jest zlokalizowanie rekordów, w których kolumny `kolumna1` i `kolumna2` mają wartości odpowiednio 10 i 20. Czy to już bardziej wygląda na SELECT? Chcemy wykonać modyfikację niektórych rekordów. Wyszukujemy więc rekordy, które będą modyfikowane. Indeksy przyspieszają wyszukiwanie danych. Indeksy przyspieszając wyszukiwanie danych przyspieszają operację modyfikacji danych. Proste i logiczne, a z drugiej strony tak często się o tym zapomina. W efekcie UPDATE trwa kilka sekund i przez ten czas tabela MyISAM blokowana jest dla innych zapytań.

Jak sprawdzić, czy dane zapytanie UPDATE (ale też np. DELETE i dowolne inne zapytanie, które ma możliwość stosowania warunku WHERE) wykorzystuje indeksy? EXPLAIN zastosowany na UPDATE nic nam nie powie:

mysql> EXPLAIN UPDATE film SET rental_duration=5 WHERE film_id=1 AND release_year=2006;
ERROR 1064 (42000): Something is wrong in your syntax obok 'UPDATE film SET rental_duration=5 WHERE film_id=1 AND release_year=2006' w linii 1

Jest jednak na to prosty sposób. Wystarczy napisać prostego SELECTa i przekleić do niego listę warunków z UPDATE:

mysql> EXPLAIN SELECT * FROM film WHERE film_id=1 AND release_year=2006;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 2       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0,00 sec)

Jak widać, w przypadku tego UPDATE do znalezienia odpowiednich rekordów wykorzystywany jest klucz główny.

Wydawałoby się, że post ten traktuje o rzeczy zupełnie oczywistej. W praktyce jednak tego typu problemy się zdarzają. Uznałem, że zmniejszenie obciążenia procesora z  50% do 30% po dodaniu jednego tylko indeksu warte jest osobnego wpisu. Zawsze jest szansa, że ktoś też skorzysta.