MySQL: delete… where..in () vs delete..from..join i zablokowane tabele przy usuwaniu z podselekcją

Oświadczenie: przepraszam za brak wiedzy na temat wewnętrznych elementów bazy danych. Oto jest:

Uruchamiamy aplikację (nie napisaną przez nas), która ma duży problem z wydajnością podczas okresowego zadania czyszczenia w bazie danych. Zapytanie wygląda następująco:

delete from VARIABLE_SUBSTITUTION where BUILDRESULTSUMMARY_ID in (
       select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY
       where BUILDRESULTSUMMARY.BUILD_KEY = "BAM-1");

Prosty, łatwy do odczytania i standardowy SQL. Ale niestety bardzo wolno. Wyjaśnienie zapytania pokazuje, że istniejący indeks VARIABLE_SUBSTITUTION.BUILDRESULTSUMMARY_IDnie jest używany:

mysql> explain delete from VARIABLE_SUBSTITUTION where BUILDRESULTSUMMARY_ID in (
    ->        select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY
    ->        where BUILDRESULTSUMMARY.BUILD_KEY = "BAM-1");
| id | select_type        | table                 | type            | possible_keys                    | key     | key_len | ref  | rows    | Extra       |
+----+--------------------+-----------------------+-----------------+----------------------------------+---------+---------+------+---------+-------------+
|  1 | PRIMARY            | VARIABLE_SUBSTITUTION | ALL             | NULL                             | NULL    | NULL    | NULL | 7300039 | Using where |
|  2 | DEPENDENT SUBQUERY | BUILDRESULTSUMMARY    | unique_subquery | PRIMARY,key_number_results_index | PRIMARY | 8       | func |       1 | Using where |

To sprawia, że ​​jest bardzo wolny (120 sekund i więcej). Ponadto wydaje się, że blokuje zapytania, które próbują wstawić BUILDRESULTSUMMARY, dane wyjściowe z show engine innodb status:

---TRANSACTION 68603695, ACTIVE 157 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 127964, OS thread handle 0x7facd0670700, query id 956555826 localhost 127.0.0.1 bamboosrv updating
update BUILDRESULTSUMMARY set CREATED_DATE='2015-06-18 09:22:05', UPDATED_DATE='2015-06-18 09:22:32', BUILD_KEY='BLA-RELEASE1-JOB1', BUILD_NUMBER=8, BUILD_STATE='Unknown', LIFE_CYCLE_STATE='InProgress', BUILD_DATE='2015-06-18 09:22:31.792', BUILD_CANCELLED_DATE=null, BUILD_COMPLETED_DATE='2015-06-18 09:52:02.483', DURATION=1770691, PROCESSING_DURATION=1770691, TIME_TO_FIX=null, TRIGGER_REASON='com.atlassian.bamboo.plugin.system.triggerReason:CodeChangedTriggerReason', DELTA_STATE=null, BUILD_AGENT_ID=199688199, STAGERESULT_ID=230943366, RESTART_COUNT=0, QUEUE_TIME='2015-06-18 09:22:04.52
------- TRX HAS BEEN WAITING 157 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 30140 n bits 112 index `PRIMARY` of table `bamboong`.`BUILDRESULTSUMMARY` trx id 68603695 lock_mode X locks rec but not gap waiting
------------------
---TRANSACTION 68594818, ACTIVE 378 sec starting index read
mysql tables in use 2, locked 2
646590 lock struct(s), heap size 63993384, 3775190 row lock(s), undo log entries 117
MySQL thread id 127845, OS thread handle 0x7facc6bf8700, query id 956652201 localhost 127.0.0.1 bamboosrv preparing
delete from VARIABLE_SUBSTITUTION  where BUILDRESULTSUMMARY_ID in   (select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY.BUILD_KEY = 'BLA-BLUBB10-SON')

Spowalnia to system i zmusza nas do wzrostu innodb_lock_wait_timeout.

Podczas uruchamiania MySQL przepisaliśmy zapytanie usuwania, aby użyć polecenia „usuń z przyłączenia”:

delete VARIABLE_SUBSTITUTION from VARIABLE_SUBSTITUTION join BUILDRESULTSUMMARY
   on VARIABLE_SUBSTITUTION.BUILDRESULTSUMMARY_ID = BUILDRESULTSUMMARY.BUILDRESULTSUMMARY_ID
   where BUILDRESULTSUMMARY.BUILD_KEY = "BAM-1";

Jest to nieco mniej czytelne, niestety nie ma standardowego SQL (o ile mogłem się dowiedzieć), ale o wiele szybsze (około 0,02 sekundy), ponieważ używa indeksu:

mysql> explain delete VARIABLE_SUBSTITUTION from VARIABLE_SUBSTITUTION join BUILDRESULTSUMMARY
    ->    on VARIABLE_SUBSTITUTION.BUILDRESULTSUMMARY_ID = BUILDRESULTSUMMARY.BUILDRESULTSUMMARY_ID
    ->    where BUILDRESULTSUMMARY.BUILD_KEY = "BAM-1";
| id | select_type | table                 | type | possible_keys                    | key                      | key_len | ref                                                    | rows | Extra                    |
+----+-------------+-----------------------+------+----------------------------------+--------------------------+---------+--------------------------------------------------------+------+--------------------------+
|  1 | SIMPLE      | BUILDRESULTSUMMARY    | ref  | PRIMARY,key_number_results_index | key_number_results_index | 768     | const                                                  |    1 | Using where; Using index |
|  1 | SIMPLE      | VARIABLE_SUBSTITUTION | ref  | var_subst_result_idx             | var_subst_result_idx     | 8       | bamboo_latest.BUILDRESULTSUMMARY.BUILDRESULTSUMMARY_ID |   26 | NULL                     |

Dodatkowe informacje:

mysql> SHOW CREATE TABLE VARIABLE_SUBSTITUTION;
| Table                 | Create Table |
| VARIABLE_SUBSTITUTION | CREATE TABLE `VARIABLE_SUBSTITUTION` (
  `VARIABLE_SUBSTITUTION_ID` bigint(20) NOT NULL,
  `VARIABLE_KEY` varchar(255) COLLATE utf8_bin NOT NULL,
  `VARIABLE_VALUE` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  `VARIABLE_TYPE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `BUILDRESULTSUMMARY_ID` bigint(20) NOT NULL,
  PRIMARY KEY (`VARIABLE_SUBSTITUTION_ID`),
  KEY `var_subst_result_idx` (`BUILDRESULTSUMMARY_ID`),
  KEY `var_subst_type_idx` (`VARIABLE_TYPE`),
  CONSTRAINT `FK684A7BE0A958B29F` FOREIGN KEY (`BUILDRESULTSUMMARY_ID`) REFERENCES `BUILDRESULTSUMMARY` (`BUILDRESULTSUMMARY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

mysql> SHOW CREATE TABLE BUILDRESULTSUMMARY;
| Table              | Create Table |
| BUILDRESULTSUMMARY | CREATE TABLE `BUILDRESULTSUMMARY` (
  `BUILDRESULTSUMMARY_ID` bigint(20) NOT NULL,
....
  `SKIPPED_TEST_COUNT` int(11) DEFAULT NULL,
  PRIMARY KEY (`BUILDRESULTSUMMARY_ID`),
  KEY `FK26506D3B9E6537B` (`CHAIN_RESULT`),
  KEY `FK26506D3BCCACF65` (`MERGERESULT_ID`),
  KEY `key_number_delta_state` (`DELTA_STATE`),
  KEY `brs_build_state_idx` (`BUILD_STATE`),
  KEY `brs_life_cycle_state_idx` (`LIFE_CYCLE_STATE`),
  KEY `brs_deletion_idx` (`MARKED_FOR_DELETION`),
  KEY `brs_stage_result_id_idx` (`STAGERESULT_ID`),
  KEY `key_number_results_index` (`BUILD_KEY`,`BUILD_NUMBER`),
  KEY `brs_agent_idx` (`BUILD_AGENT_ID`),
  KEY `rs_ctx_baseline_idx` (`VARIABLE_CONTEXT_BASELINE_ID`),
  KEY `brs_chain_result_summary_idx` (`CHAIN_RESULT`),
  KEY `brs_log_size_idx` (`LOG_SIZE`),
  CONSTRAINT `FK26506D3B9E6537B` FOREIGN KEY (`CHAIN_RESULT`) REFERENCES `BUILDRESULTSUMMARY` (`BUILDRESULTSUMMARY_ID`),
  CONSTRAINT `FK26506D3BCCACF65` FOREIGN KEY (`MERGERESULT_ID`) REFERENCES `MERGE_RESULT` (`MERGERESULT_ID`),
  CONSTRAINT `FK26506D3BCEDEEF5F` FOREIGN KEY (`STAGERESULT_ID`) REFERENCES `CHAIN_STAGE_RESULT` (`STAGERESULT_ID`),
  CONSTRAINT `FK26506D3BE3B5B062` FOREIGN KEY (`VARIABLE_CONTEXT_BASELINE_ID`) REFERENCES `VARIABLE_CONTEXT_BASELINE` (`VARIABLE_CONTEXT_BASELINE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

(niektóre rzeczy pominięto, jest to dość szeroki stół).

Mam więc kilka pytań na ten temat:

• dlaczego optymalizator kwerend nie może używać indeksu do usuwania, gdy wersja subkwerendy jest w trakcie korzystania z wersji dołączania?
• czy istnieje jakiś (najlepiej zgodny ze standardami) sposób, aby oszukać go przy użyciu indeksu? lub
• czy istnieje przenośny sposób na napisanie delete from join? Aplikacja obsługuje PostgreSQL, MySQL, Oracle i Microsoft SQL Server, używane przez jdbc i Hibernate.
• dlaczego jest usuwane z VARIABLE_SUBSTITUTIONblokujących wstawień do BUILDRESULTSUMMARY, które jest używane tylko w podselekcji?

• dlaczego optymalizator kwerend nie może używać indeksu do usuwania, gdy wersja subkwerendy jest w trakcie korzystania z wersji dołączania?

Ponieważ optymalizator jest / był trochę głupi pod tym względem. Nie tylko dla DELETEi UPDATEale dla SELECTsprawozdań, a także, jak coś WHERE column IN (SELECT ...)nie zostało w pełni zoptymalizowane. Plan wykonania zwykle obejmował uruchomienie podkwerendy dla każdego wiersza tabeli zewnętrznej ( VARIABLE_SUBSTITUTIONw tym przypadku). Jeśli ten stół jest mały, wszystko jest w porządku. Jeśli jest duży, nie ma nadziei. W nawet starszych wersjach INpodkwerenda z INpodkwerendą sprawi, że EXPLAINuruchomi się nawet na wieki.

Co możesz zrobić - jeśli chcesz zachować to zapytanie - skorzystaj z najnowszych wersji, które zaimplementowały kilka optymalizacji i przetestuj ponownie. Najnowsze wersje oznaczają: MySQL 5.6 (i 5.7, jeśli chodzi o wersję beta) i MariaDB 5.5 / 10.0

(aktualizacja) Używasz już wersji 5.6, która ma usprawnienia optymalizacji, a ta jest istotna: Optymalizacja podkwerend z transformacjami częściowego łączenia
Sugeruję dodanie (BUILD_KEY)samego indeksu . Jest złożony, ale to nie jest bardzo przydatne dla tego zapytania.

• czy istnieje jakiś (najlepiej zgodny ze standardami) sposób, aby oszukać go przy użyciu indeksu?

Brak, o którym mogę myśleć. Moim zdaniem nie warto próbować używać standardowego SQL. Jest tak wiele różnic i drobnych dziwactw, że każdy DBMS ma ( UPDATEa DELETEinstrukcje są dobrymi przykładami takich różnic), że kiedy próbujesz użyć czegoś, co działa wszędzie, wynikiem jest bardzo ograniczony podzbiór SQL.

• czy istnieje przenośny sposób na napisanie skreślenia z dołączenia? Aplikacja obsługuje PostgreSQL, MySQL, Oracle i Microsoft SQL Server, używane przez jdbc i Hibernate.

Ta sama odpowiedź jak w poprzednim pytaniu.

• dlaczego usunięcie z VARIABLE_SUBSTITUTION blokuje wstawki do BUILDRESULTSUMMARY, który jest używany tylko w podselekcji?

Nie jestem w 100% pewien, ale myślę, że ma to związek z wielokrotnym uruchamianiem podkwerendy i rodzajem blokad, jakie przyjmuje na stole.

oto odpowiedzi na dwa pytania

• Optymalizator nie może używać indeksu, ponieważ klauzula where zmienia się dla każdego wiersza. Instrukcja delete będzie wyglądać mniej więcej tak, jak przejdzie przez optymalizator

  delete from VARIABLE_SUBSTITUTION where EXISTS (
  select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY
  where BUILDRESULTSUMMARY.BUILD_KEY = BUILDRESULTSUMMARY_ID AND BUILDRESULTSUMMARY.BUILD_KEY = "BAM-1");

ale po wykonaniu połączenia serwer jest w stanie zidentyfikować wiersze, które zostaną usunięte.

• Sztuką jest użycie zmiennej do przechowywania zmiennej BUILDRESULTSUMMARY_IDi użycie zmiennej zamiast zapytania. Zauważ, że inicjalizacja zmiennej i kwerenda usuwająca muszą być uruchamiane w ramach sesji. Coś takiego.

  SET @ids = (SELECT GROUP_CONCAT(BUILDRESULTSUMMARY_ID) 
          from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY.BUILD_KEY = "BAM-1" ); 
  delete from VARIABLE_SUBSTITUTION where FIND_IN_SET(BUILDRESULTSUMMARY_ID,@ids) > 0;

  możesz mieć z tym problem, jeśli zapytanie zwróci zbyt wiele identyfikatorów i nie jest to standardowy sposób. To tylko obejście.

  I nie mam odpowiedzi na twoje pozostałe dwa pytania :)