Как лучше всего оптимизировать объединенное обновление?
У меня есть длительный запрос MySQL, который обновляет записи одной таблицы по соответствующим записям в другой таблице. В этом случае поля из производной записи назначения продавца устанавливаются в исходную запись транзакции:
update transaction t
inner join assignment a on a.transaction_id = t.id
set
t.merchant_name = a.name,
t.merchant_org_id = a.merchant_org_id,
t.category_id = a.category_id
t.assignment_pending = false
where t.id in ('...', '...', '...')
Этот запрос занимает... очень много времени. У меня есть индекс в столбце transaction_id задания... но есть ли другой способ оптимизации этого запроса?
Ниже приведена соответствующая схема и поясняющий план:
CREATE TABLE `transaction` (
`id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`org_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`service_connection_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`amount` bigint NOT NULL,
`currency_code` varchar(3) NOT NULL,
`amount_usd` bigint NOT NULL DEFAULT '0',
`merchant_name` varchar(255) NOT NULL,
`mcc` char(4) DEFAULT NULL,
`transaction_type` varchar(255) NOT NULL,
`category_id` char(36) NOT NULL,
`merchant_org_id` char(36) DEFAULT NULL,
`user_id` char(36) DEFAULT NULL,
`mask` varchar(255) DEFAULT NULL,
`assignment_pending` tinyint(1) NOT NULL DEFAULT '0',
`state` varchar(255) NOT NULL DEFAULT 'posted',
`assignment_id` varchar(255) DEFAULT NULL,
`epoch_authorized` bigint NOT NULL DEFAULT '0',
`epoch_posted` bigint DEFAULT NULL,
`raw_description` varchar(255) NOT NULL DEFAULT '',
`created` bigint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_provider_tx_id` (`org_id`,`provider_tx_id`),
KEY `idx_tx_merchant` (`merchant_org_id`),
KEY `idx_tx_assignment` (`assignment_id`),
KEY `idx_tx_org_authorized` (`org_id`,`epoch_authorized`),
KEY `idx_tx_org_posted` (`org_id`,`epoch_posted`),
KEY `idx_tx_org_merchant` (`org_id`,`merchant_org_id`),
KEY `idx_tx_org_amount_usd` (`org_id`,`amount_usd`),
KEY `idx_tx_assignment_pending` (`org_id`,`assignment_pending`,`epoch_authorized`,`id`),
KEY `idx_tx_org_vendor_by_range` (`org_id`,`merchant_org_id`,`epoch_authorized`)
)
CREATE TABLE `assignment` (
`id` char(36) NOT NULL,
`org_id` char(36) NOT NULL,
`user_id` char(36) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`date` date DEFAULT NULL,
`factor_id` char(36) DEFAULT NULL,
`merchant_org_id` char(36) DEFAULT NULL,
`category_id` char(36) NOT NULL,
`transaction_id` char(36) DEFAULT NULL,
`assignment_method` varchar(255) NOT NULL,
`created` bigint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_assignment_transaction` (`transaction_id`),
KEY `idx_org` (`org_id`),
KEY `idx_user` (`user_id`),
KEY `idx_date` (`date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Объяснить план
Задавая вопросы о производительности, всегда включайте описание таблицы и план выполнения.
@tcmoore, похоже, вы используете свой первичный ключ и уникальный ключ, поэтому у вас есть индексы. Я вижу разные сопоставления между id char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, и transaction_id char(36) DEFAULT NULL из таблицы COLLATE=utf8mb4_0900_ai_ci. Можете ли вы использовать ту же сортировку и посмотреть, имеет ли это какое-либо значение?
Разве в вашем SQL отсутствует слово SET?
Ответы 1
@ErgestBasha правильно догадался. Проблема была в сопоставлении. Для контекста: я не использую внешние ключи по причинам приложения (не хочу думать об упорядочении при массовом создании записей), так что, вероятно, именно поэтому я и оказался в этой ситуации.
Как только я убедился, что поля идентификаторов сопоставлены правильно и так же, как и соответствующие им ключевые столбцы, время запроса на обновление сократилось с десятков секунд до долей секунды.
-- Example of manually setting the collation method on the desired columns
ALTER TABLE transaction MODIFY COLUMN id CHAR(36) NOT NULL COLLATE utf8mb4_bin;
ALTER TABLE transaction MODIFY COLUMN assignment_id CHAR(36) NULL COLLATE utf8mb4_bin;
ALTER TABLE assignment MODIFY COLUMN id CHAR(36) NOT NULL COLLATE utf8mb4_bin;
ALTER TABLE assignment MODIFY COLUMN transaction_id CHAR(36) NULL COLLATE utf8mb4_bin;
Я рад, что это помогло тебе
Вы можете выполнить обновление только в том случае, если какое-либо из значений было изменено с добавлением где (t.merchant_name != a.name OR t.merchant_org_id != a.merchant_org_id...)