Jaki jest koszt aktualizacji wszystkich kolumn, nawet tych, które się nie zmieniły [zamknięte]

Jeśli chodzi o aktualizację wiersza, wiele narzędzi ORM wydaje instrukcję UPDATE, która ustawia każdą kolumnę powiązaną z tym konkretnym podmiotem .

Zaletą jest to, że można łatwo grupować instrukcje aktualizacji, ponieważ UPDATEinstrukcja jest taka sama bez względu na zmieniony atrybut encji. Co więcej, możesz nawet używać buforowania instrukcji po stronie serwera i klienta.

Jeśli więc załaduję jednostkę i ustawię tylko jedną właściwość:

Post post = entityManager.find(Post.class, 1L);
post.setScore(12);

Wszystkie kolumny zostaną zmienione:

UPDATE post
SET    score = 12,
       title = 'High-Performance Java Persistence'
WHERE  id = 1

Teraz, zakładając, że mamy również indeks titlenieruchomości, czyż DB nie powinien zdawać sobie sprawy, że i tak wartość się nie zmieniła?

W tym artykule Markus Winand mówi:

Aktualizacja we wszystkich kolumnach pokazuje ten sam wzorzec, który już zaobserwowaliśmy w poprzednich sekcjach: czas odpowiedzi rośnie z każdym dodatkowym indeksem.

Zastanawiam się, dlaczego jest to narzut, ponieważ baza danych ładuje powiązaną stronę danych z dysku do pamięci, dzięki czemu może dowiedzieć się, czy należy zmienić wartość kolumny.

Nawet w przypadku indeksów nie zmienia to niczego, ponieważ wartości indeksów nie zmieniają się dla kolumn, które nie uległy zmianie, ale zostały uwzględnione w AKTUALIZACJI.

Czy to dlatego, że indeksy drzewa B + powiązane z nadmiarowymi niezmienionymi kolumnami również wymagają nawigacji, tylko po to, aby baza danych zdała sobie sprawę, że wartość liścia jest nadal taka sama?

Oczywiście niektóre narzędzia ORM umożliwiają aktualizację tylko zmienionych właściwości:

UPDATE post
SET    score = 12,
WHERE  id = 1

Ale ten typ aktualizacji może nie zawsze korzystać z aktualizacji wsadowych lub buforowania instrukcji, gdy różne właściwości są zmieniane dla różnych wierszy.

Wiem, że najbardziej martwisz się UPDATEgłównie wydajnością, ale jako opiekun „ORM” pozwól, że przedstawię Ci inne spojrzenie na problem rozróżnienia między wartościami „zmienionymi” , „zerowymi” i „domyślnymi” , które są trzy różne rzeczy w SQL, ale prawdopodobnie tylko jedna rzecz w Javie i większości ORM:

Tłumaczenie uzasadnienia na INSERTstwierdzenia

Twoje argumenty na rzecz batchowalności i buforowania instrukcji zachowują się tak samo w przypadku INSERTinstrukcji, jak i w przypadku UPDATEinstrukcji. Ale w przypadku INSERTinstrukcji pominięcie kolumny w instrukcji ma inną semantykę niż w UPDATE. Oznacza zastosowanie DEFAULT. Następujące dwa są semantycznie równoważne:

INSERT INTO t (a, b)    VALUES (1, 2);
INSERT INTO t (a, b, c) VALUES (1, 2, DEFAULT);

Nie dotyczy to sytuacji UPDATE, gdy pierwsze dwa są semantycznie równoważne, a trzeci ma zupełnie inne znaczenie:

-- These are the same
UPDATE t SET a = 1, b = 2;
UPDATE t SET a = 1, b = 2, c = c;

-- This is different!
UPDATE t SET a = 1, b = 2, c = DEFAULT;

Większość interfejsów API klienta bazy danych, w tym JDBC, aw konsekwencji JPA, nie pozwala na wiązanie DEFAULTwyrażenia ze zmienną powiązania - głównie dlatego, że serwery też na to nie pozwalają. Jeśli chcesz ponownie użyć tej samej instrukcji SQL z wyżej wymienionych powodów wsadowości i buforowania instrukcji, możesz użyć następującej instrukcji w obu przypadkach (zakładając, że (a, b, c)są to wszystkie kolumny t):

INSERT INTO t (a, b, c) VALUES (?, ?, ?);

A ponieważ cnie jest ustawiony, prawdopodobnie powiążesz Javę nullz trzecią zmienną powiązania, ponieważ wiele ORM również nie może rozróżnić między NULLi DEFAULT( jOOQ , na przykład będący tutaj wyjątkiem). Widzą tylko Javę nulli nie wiedzą, czy to oznacza NULL(jak w nieznanej wartości) czy DEFAULT(jak w niezainicjowanej wartości).

W wielu przypadkach to rozróżnienie nie ma znaczenia, ale jeśli kolumna c używa jednej z następujących funkcji, stwierdzenie jest po prostu błędne :

• Ma DEFAULTklauzulę
• Może być generowany przez wyzwalacz

Powrót do UPDATEwyciągów

Chociaż powyższe dotyczy wszystkich baz danych, zapewniam cię, że problem z wyzwalaczem dotyczy również bazy danych Oracle. Rozważ następujący kod SQL:

CREATE TABLE x (a INT PRIMARY KEY, b INT, c INT, d INT);

INSERT INTO x VALUES (1, 1, 1, 1);

CREATE OR REPLACE TRIGGER t
  BEFORE UPDATE OF c, d
  ON x
BEGIN
  IF updating('c') THEN
    dbms_output.put_line('Updating c');
  END IF;
  IF updating('d') THEN
    dbms_output.put_line('Updating d');
  END IF;
END;
/

SET SERVEROUTPUT ON
UPDATE x SET b = 1 WHERE a = 1;
UPDATE x SET c = 1 WHERE a = 1;
UPDATE x SET d = 1 WHERE a = 1;
UPDATE x SET b = 1, c = 1, d = 1 WHERE a = 1;

Po uruchomieniu powyższego zobaczysz następujące dane wyjściowe:

table X created.
1 rows inserted.
TRIGGER T compiled
1 rows updated.
1 rows updated.
Updating c

1 rows updated.
Updating d

1 rows updated.
Updating c
Updating d

Jak widać, instrukcja, która zawsze aktualizuje wszystkie kolumny, zawsze wyzwala wyzwalacz dla wszystkich kolumn, natomiast instrukcje aktualizujące tylko kolumny, które uległy zmianie, uruchamiają tylko te wyzwalacze, które oczekują takich konkretnych zmian.

Innymi słowy:

Obecne zachowanie Hibernacji, które opisujesz, jest niekompletne i może nawet zostać uznane za niewłaściwe w obecności wyzwalaczy (i prawdopodobnie innych narzędzi).

Osobiście uważam, że argument optymalizacji pamięci podręcznej zapytania jest przereklamowany w przypadku dynamicznego SQL. Jasne, w takiej pamięci podręcznej będzie jeszcze kilka zapytań i trochę więcej analizowania, ale zwykle nie stanowi to problemu dla UPDATEinstrukcji dynamicznych , a znacznie mniej niż dla SELECT.

Batching jest z pewnością problemem, ale moim zdaniem pojedyncza aktualizacja nie powinna być znormalizowana, aby zaktualizować wszystkie kolumny tylko dlatego, że istnieje niewielka możliwość, że instrukcja będzie batchowalna. Możliwe, że ORM może zbierać podgrupy kolejnych identycznych instrukcji i grupować je zamiast „całej partii” (w przypadku, gdy ORM jest w stanie nawet śledzić różnicę między „zmienioną” , „zerową” i „domyślną”

Myślę, że odpowiedź brzmi - to skomplikowane . Próbowałem napisać szybki dowód przy użyciu longtextkolumny w MySQL, ale odpowiedź jest trochę niejednoznaczna. Dowód pierwszy:

# in advance:
set global max_allowed_packet=1024*1024*1024;

CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` char(255) NOT NULL,
  `c` LONGTEXT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> insert into t2 (a, b, c) values (null, 'b', REPEAT('c', 1024*1024*1024));
Query OK, 1 row affected (38.81 sec)

mysql> UPDATE t2 SET b='new'; # fast
Query OK, 1 row affected (6.73 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  UPDATE t2 SET b='new'; # fast
Query OK, 0 rows affected (2.87 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE t2 SET b='new'; # fast
Query OK, 0 rows affected (2.61 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE t2 SET c= REPEAT('d', 1024*1024*1024); # slow (changed value)
Query OK, 1 row affected (22.38 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE t2 SET c= REPEAT('d', 1024*1024*1024); # still slow (no change)
Query OK, 0 rows affected (14.06 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Tak więc istnieje niewielka różnica czasu między wolną + zmienioną wartością, a wolną + niezmienioną wartością. Postanowiłem więc spojrzeć na inną metrykę, którą były napisane strony:

mysql> show global status like 'innodb_pages_written';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| Innodb_pages_written | 198656 |
+----------------------+--------+
1 row in set (0.00 sec)

mysql> show global status like 'innodb_pages_written';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| Innodb_pages_written | 198775 | <-- 119 pages changed in a "no change"
+----------------------+--------+
1 row in set (0.01 sec)

mysql> show global status like 'innodb_pages_written';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| Innodb_pages_written | 322494 | <-- 123719 pages changed in a "change"!
+----------------------+--------+
1 row in set (0.00 sec)

Wygląda więc na to, że czas się wydłużył, ponieważ musi istnieć porównanie, aby potwierdzić, że sama wartość nie została zmodyfikowana, co w przypadku długiego tekstu 1G wymaga czasu (ponieważ jest podzielony na wiele stron). Ale sama modyfikacja nie wydaje się przesuwać w dzienniku powtórzeń.

Podejrzewam, że jeśli wartości są zwykłymi kolumnami, które znajdują się na stronie, porównanie powoduje tylko niewielki narzut. I przy założeniu, że obowiązuje ta sama optymalizacja, nie ma żadnych przestojów, jeśli chodzi o aktualizację.

Dłuższa odpowiedź

Myślę, że ORM nie powinien eliminować kolumn, które zostały zmodyfikowane ( ale nie zmienione ), ponieważ ta optymalizacja ma dziwne skutki uboczne.

W pseudo-kodzie rozważ następujące kwestie:

# Initial Data does not make sense
# should be either "Harvey Dent" or "Two Face"

id: 1, firstname: "Two Face", lastname: "Dent"

session1.start
session2.start

session1.firstname = "Two"
session1.lastname = "Face"
session1.save

session2.firstname = "Harvey"
session2.lastname = "Dent"
session2.save

Wynik, jeśli ORM miałby „zoptymalizować” modyfikację bez zmian:

id: 1, firstname: "Harvey", lastname: "Face"

Wynik, jeśli ORM wyśle ​​wszystkie modyfikacje do serwera:

id: 1, firstname: "Harvey", lastname: "Dent"

Przypadek testowy tutaj opiera się na repeatable-readizolacji (domyślny MySQL), ale istnieje również okno czasowe dla read-committedizolacji, w której odczyt sesji2 następuje przed zatwierdzeniem sesji1.

Innymi słowy: optymalizacja jest bezpieczna tylko wtedy, gdy wydasz a, SELECT .. FOR UPDATEaby odczytać wiersze, a następnie UPDATE. SELECT .. FOR UPDATEnie używa MVCC i zawsze czyta najnowszą wersję wierszy.

Edycja: Upewnij się, że zestaw danych przypadku testowego ma 100% pamięci. Skorygowane wyniki czasowe.