MySQL: delete ... where..in () vs delete..from..join и заблокированные таблицы при удалении с подзапросом

Отказ от ответственности: пожалуйста, извините, что мне не хватает знаний о внутренней базе данных. Вот оно:

Мы запускаем приложение (не написанное нами), которое имеет большую проблему с производительностью в периодическом задании очистки в базе данных. Запрос выглядит следующим образом:

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

Прямо вперед, легко читаемый и стандартный SQL. Но, к сожалению, очень медленно. Объяснение запроса показывает, что существующий индекс в VARIABLE_SUBSTITUTION.BUILDRESULTSUMMARY_ID не используется:

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 |

Это делает его очень медленным (120 секунд и более). В дополнение к этому, похоже, блокирует запросы, которые пытаются вставить в BUILDRESULTSUMMARY, вывод из 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')

Это замедляет работу системы и заставляет нас увеличивать innodb_lock_wait_timeout.

Когда мы запускаем MySQL, мы переписали запрос на удаление, чтобы использовать «delete from join»:

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

Это немного легче читать, к сожалению, нет стандартного SQL (насколько мне удалось узнать), но намного быстрее (0,02 секунды или около того), поскольку он использует индекс:

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                     |

Дополнительная информация:

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 |

(некоторые вещи опущены, это довольно широкая таблица).

Итак, у меня есть несколько вопросов по этому поводу:

  • Почему оптимизатор запросов не может использовать индекс для удаления, когда версия подзапроса, пока она используется при использовании версии соединения?
  • Есть ли какие-либо (идеально подходящие стандарты) способ обмануть его в использовании индекса? или
  • есть ли переносимый способ написать delete from join? Приложение поддерживает PostgreSQL, MySQL, Oracle и Microsoft SQL Server, используемые через jdbc и Hibernate.
  • почему удаление из VARIABLE_SUBSTITUTION блокировки вставок в BUILDRESULTSUMMARY, который используется только в подзапросе?
8 голосов | спросил 0x89 18 J0000006Europe/Moscow 2015, 11:57:02

1 ответ


2

вот ответы на два из ваших вопросов.

  • Оптимизатор не может использовать индекс, потому что предложение where изменяется для каждой строки. Операция delete будет выглядеть примерно так, как только она пройдет оптимизатор

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

, но когда вы выполните соединение, сервер может идентифицировать строки, подлежащие удалению.

  • трюк - использовать переменную для хранения BUILDRESULTSUMMARY_ID и использовать переменную вместо запроса. Обратите внимание, что оба запроса инициализации переменной и удаления должны выполняться в сеансе. Что-то вроде этого.

    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;
    

    у вас может возникнуть проблема с этим, если запрос возвращает слишком много идентификаторов, и это не стандартный способ. Это просто обходной путь.

    И у меня нет ответа для двух других вопросов:)

ответил Masoud 18 J0000006Europe/Moscow 2015, 12:37:31

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132